main.py

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3
   4 from flask import Flask, request, session, g, redirect, url_for, abort, \
   5     render_template, flash
   6 from gettext import gettext
   7 #from flask_openid import OpenID
   8 #from flaskext.babel import Babel, gettext, ngettext
   9 import sqlite3
  10 from datetime import date, time, timedelta, datetime
  11 import time
  12 from contextlib import closing
  13 import locale
  14 locale.setlocale(locale.LC_ALL, '')
  15 import os
  16 import hashlib
  17 import smtplib
  18 import string
  19 import re
  20 from collections import OrderedDict
  21
  22 from settings import *
  23
  24 app = Flask(__name__)
  25 app.config.from_object(__name__)
  26
  27 #oid = OpenID(app)
  28 #babel = Babel(app)
  29
  30 def connect_db():
  31     return sqlite3.connect(app.config['DATABASE'])
  32
  33 @app.before_request
  34 def before_request():
  35     g.db = connect_db()
  36     g.db.execute("PRAGMA foreign_keys = ON")
  37
  38 @app.teardown_request
  39 def teardown_request(exception):
  40     g.db.close()
  41
  42 @app.route('/')
  43 def home():
  44     return render_template('index.html', active_button="home")
  45
  46 def query_db(query, args=(), one=False):
  47     cur = g.db.execute(query, args)
  48     rv = [dict((cur.description[idx][0], value)
  49         for idx, value in enumerate(row)) for row in cur.fetchall()]
  50     return (rv[0] if rv else None) if one else rv
  51
  52 def init_db():
  53     with closing(connect_db()) as db:
  54         with app.open_resource('schema.sql') as f:
  55             db.cursor().executescript(f.read())
  56         db.commit()
  57
  58 #----------------
  59 # Login / Logout
  60
  61 def valid_login(email, password):
  62     # get user key
  63     user_key = query_db('select key from users where email = ?', (email,),
  64             one=True)
  65     if not user_key:
  66         # no such user
  67         return None
  68     user_key = user_key['key']
  69     # try password
  70     return query_db('select * from users where email = ? and password = ?',
  71             [email, crypt(password, user_key)], one=True)
  72
  73 def connect_user(user):
  74     session['user'] = user
  75     del session['user']['password']
  76     del session['user']['key']
  77
  78 def disconnect_user():
  79     session.pop('user', None)
  80
  81 def crypt(passwd, user_key):
  82     # the per-user salt should not be stored in the db
  83     # storing the passwd... but this is better than nothing
  84     per_user_salt = hashlib.sha1(user_key).hexdigest()
  85     salt_passwd = '%s%s%s' % (app.config['PASSWD_SALT'], per_user_salt, passwd)
  86     return hashlib.sha1(salt_passwd).hexdigest()
  87
  88 def keygen():
  89     return hashlib.sha1(os.urandom(24)).hexdigest()
  90
  91 def get_userid():
  92     user = session.get('user')
  93     if user is None:
  94         return -1
  95     elif user.get('id') < 0:
  96         return -1
  97     else:
  98         return user.get('id')
  99
 100 #@oid.loginhandler
 101 @app.route('/login', methods=['GET', 'POST'])
 102 def login():
 103     if request.method == 'POST':
 104         user = valid_login(request.form['username'], request.form['password'])
 105         if user is None:
 106             if request.form['openid']:
 107                 return oid.try_login(request.form['openid'], ask_for=['email', 'fullname', 'nickname'])
 108             else:
 109                 flash(u'Email ou mot de passe invalide.', 'error')
 110         else:
 111             connect_user(user)
 112             flash(u'Vous êtes connecté. Bienvenue, %s !' % user['name'], 'success')
 113             if request.args.get('continue'):
 114                 return redirect(request.args['continue'])
 115             return redirect(url_for('home'))
 116     return render_template('login.html')
 117
 118 #@oid.after_login
 119 def create_or_login(resp):
 120     openid_url = resp.identity_url
 121     user = query_db('select * from users where openid = ?', [openid_url], one=True)
 122     if user is not None:
 123         flash(gettext(u'Successfully signed in'))
 124         connect_user(user)
 125         return redirect(oid.get_next_url())
 126     return redirect(url_for('home'))
 127
 128 @app.route('/logout')
 129 def logout():
 130     disconnect_user()
 131     flash(u'Vous avez été déconnecté.', 'info')
 132     if request.args.get('continue') and not "admin" in request.args.get('continue'):
 133         return redirect(request.args['continue'])
 134     return redirect(url_for('home'))
 135
 136 #-----------------
 137 # Change password
 138
 139 @app.route('/password/lost', methods=['GET', 'POST'])
 140 def password_lost():
 141     info = None
 142     if request.method == 'POST':
 143         user = query_db('select * from users where email = ?', [request.form['email']], one=True)
 144         if user is None:
 145             flash('Cet utilisateur n\'existe pas !', 'error')
 146         else:
 147             key = 'v%s' % keygen() # start with v: valid key
 148             g.db.execute('update users set key = ? where id = ?', [key, user['id']])
 149             g.db.commit()
 150             link = request.url_root + url_for('login_key', userid=user['id'], key=key)
 151             BODY = string.join((
 152                 "From: %s" % EMAIL,
 153                 "To: %s" % user['email'],
 154                 "Subject: [Cavote] %s" % gettext(u"Lost password"),
 155                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 156                 "Content-type: text/plain; charset=utf-8",
 157                 "X-Mailer: %s" % VERSION,
 158                 "",
 159                 gettext(u"It seems that you have lost your password."),
 160                 gettext(u"This link will log you without password."),
 161                 gettext(u"Don't forget to define a new one as soon a possible!"),
 162                 gettext(u"This link will only work one time."),
 163                 "",
 164                 link,
 165                 "",
 166                 gettext(u"If you think this mail is not for you, please ignore and delete it.")
 167                 ), "\r\n")
 168             server = smtplib.SMTP(SMTP_SERVER)
 169             server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))
 170             server.quit()
 171             flash(u"Un mail a été envoyé à " + user['email'], 'info')
 172     return render_template('password_lost.html')
 173
 174 @app.route('/login/<userid>/<key>')
 175 def login_key(userid, key):
 176     user = query_db('select * from users where id = ? and key = ?', [userid, key], one=True)
 177     if user is None or user['key'][0] != "v":
 178         abort(404)
 179     else:
 180         connect_user(user)
 181         flash(u"Veuillez mettre à jour votre mot de passe", 'info')
 182         return redirect(url_for('user_password', userid=user['id']))
 183
 184 #---------------
 185 # User settings
 186
 187 @app.route('/user/<userid>')
 188 def user(userid):
 189     if int(userid) != get_userid():
 190         abort(401)
 191     groups = query_db('select * from groups join user_group on id=id_group where id_user = ?', (userid,))
 192     return render_template('user.html', groups=groups)
 193
 194 @app.route('/user/settings/<userid>', methods=['GET', 'POST'])
 195 def user_edit(userid):
 196     if int(userid) != get_userid():
 197         abort(401)
 198     if request.method == 'POST':
 199         if query_db('select * from users where email=? and id!=?', [request.form['email'], userid], one=True) is None:
 200             if query_db('select * from users where name=? and id!=?', [request.form['name'], userid], one=True) is None:
 201                 g.db.execute('update users set email = ?, openid = ?, name = ?, organization = ? where id = ?',
 202                         [request.form['email'], request.form['openid'], request.form['name'], request.form['organization'], session['user']['id']])
 203                 g.db.commit()
 204                 disconnect_user()
 205                 user = query_db('select * from users where id=?', [userid], one=True)
 206                 if user is None:
 207                     flash(u'Une erreur s\'est produite.', 'error')
 208                     return redirect(url_for('login'))
 209                 connect_user(user)
 210                 flash(u'Votre profil a été mis à jour !', 'success')
 211             else:
 212                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 213         else:
 214             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 215     return render_template('user_edit.html')
 216
 217 @app.route('/user/password/<userid>', methods=['GET', 'POST'])
 218 def user_password(userid):
 219     if int(userid) != get_userid():
 220         abort(401)
 221     if request.method == 'POST':
 222         if request.form['password'] == request.form['password2']:
 223             # new (invalid) key
 224             key = 'i%s' % keygen() # start with i: invalid key
 225             print "\n\nchange key for %s\n" % key # FIXME TMP
 226             g.db.execute('update users set password = ?, key = ? where id = ?', [crypt(request.form['password'], key), key, session['user']['id']])
 227             g.db.commit()
 228             flash(u'Votre mot de passe a été mis à jour.', 'success')
 229         else:
 230             flash(u'Les mots de passe sont différents.', 'error')
 231     return render_template('user_edit.html')
 232
 233 #------------
 234 # User admin
 235
 236 @app.route('/admin/users')
 237 def admin_users():
 238     if not session.get('user').get('is_admin'):
 239         abort(401)
 240     tuples = query_db('select *, groups.name as groupname \
 241                        from (select *, id as userid, name as username \
 242                          from users join user_group on id=id_user order by id desc) \
 243                        join groups on id_group=groups.id')
 244     users = dict()
 245     for t in tuples:
 246         if t['userid'] in users:
 247             users[t['userid']]['groups'].append(t["groupname"])
 248         else:
 249             users[t['userid']] = dict()
 250             users[t['userid']]['userid'] = t['userid']
 251             users[t['userid']]['email'] = t['email']
 252             users[t['userid']]['username'] = t['username']
 253             users[t['userid']]['is_admin'] = t['is_admin']
 254             users[t['userid']]['groups'] = [t['groupname']]
 255     return render_template('admin_users.html', users=users.values())
 256
 257 @app.route('/admin/users/add', methods=['GET', 'POST'])
 258 def admin_user_add():
 259     if not session.get('user').get('is_admin'):
 260         abort(401)
 261     if request.method == 'POST':
 262         if request.form['email']:
 263             if query_db('select * from users where email=?', [request.form['email']], one=True) is None:
 264                 if request.form['username']:
 265                     if query_db('select * from users where name=?', [request.form['username']], one=True) is None:
 266                         admin = 0
 267                         if 'admin' in request.form:
 268                             admin = 1
 269                         key = 'v%s' % keygen()
 270                         g.db.execute('insert into users (email, openid, name, organization, password, is_admin, key) \
 271                                       values (?, ?, ?, ?, ?, ?, ?)',
 272                                 [request.form['email'],
 273                                     request.form['openid'],
 274                                     request.form['username'],
 275                                     request.form['organization'],
 276                                     '', admin, key])
 277                         g.db.commit()
 278                         user = query_db('select * from users where email = ?', [request.form["email"]], one=True)
 279                         if user:
 280                             groups = request.form.getlist('groups')
 281                             groups.append('1')
 282                             for group in groups:
 283                                 if query_db('select id from groups where id = ?', group, one=True) is None:
 284                                     flash(u'Le groupe portant l\'id %s n\'existe pas.' % group, 'warning')
 285                                 else:
 286                                     g.db.execute('insert into user_group values (?, ?)', [user['id'], group])
 287                                     g.db.commit()
 288                             link = request.url_root + url_for('login_key', userid=user['id'], key=user['key'])
 289                             BODY = string.join((
 290                             "From: %s" % EMAIL,
 291                             "To: %s" % user['email'],
 292                             "Subject: [Cavote] %s" % gettext(u"Welcome"),
 293                             "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 294                             "Content-type: text/plain; charset=utf-8",
 295                             "X-Mailer: %s" % VERSION,
 296                             "",
 297                             "%(text)s %(user)s!" % {"text": gettext(u"Hi"), "user": user['name']},
 298                             "%(text2)s %(title)s." % {"text2": gettext(u"Welcome on"), "title": TITLE},
 299                             "%(text3)s %(email)s." % {"text3": gettext(u"Your account address is"), "email": user['email']},
 300                             "",
 301                             gettext(u"To log in for the first time and set your password, please follow this link :"),
 302                             link,
 303                             ""
 304                             ), "\r\n")
 305                             server = smtplib.SMTP(SMTP_SERVER)
 306                             server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))
 307                             server.quit()
 308                             flash(u'Le nouvel utilisateur a été créé avec succès', 'success')
 309                             return redirect(url_for('admin_users'))
 310                         else:
 311                             flash(u'Une erreur s\'est produite.', 'error')
 312                     else:
 313                         flash(u'Le nom ' + request.form['username'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 314                 else:
 315                     flash(u"Vous devez spécifier un nom d'utilisateur.", 'error')
 316             else:
 317                 flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 318         else:
 319             flash(u"Vous devez spécifier une adresse email.", 'error')
 320     groups = query_db('select * from groups where system=0')
 321     return render_template('admin_user_new.html', groups=groups)
 322
 323 @app.route('/admin/users/edit/<iduser>', methods=['GET', 'POST'])
 324 def admin_user_edit(iduser):
 325     if not session.get('user').get('is_admin'):
 326         abort(401)
 327     user = query_db('select * from users where id = ?', [iduser], one=True)
 328     user['groups'] = query_db('select groups.* from groups join user_group on groups.id = user_group.id_group where id_user = ?', [iduser])
 329     if user is None:
 330         abort(404)
 331     if request.method == 'POST':
 332         if query_db('select * from users where email=? and id!=?', [request.form['email'], iduser], one=True) is None:
 333             if query_db('select * from users where name=? and id!=?', [request.form['name'], iduser], one=True) is None:
 334                 admin = 0
 335                 if 'admin' in request.form:
 336                     admin = 1
 337                 g.db.execute('update users set email = ?, name = ?, organization = ?, openid= ?, is_admin = ? where id = ?',
 338                         [request.form['email'], request.form['name'], request.form['organization'], request.form['openid'], admin, iduser])
 339                 g.db.commit()
 340                 groups = request.form.getlist('groups')
 341                 groups.append('1')
 342                 for group in user['groups']:
 343                     if not group['id'] in groups:
 344                         g.db.execute('delete from user_group where id_user = ? and id_group = ?', [iduser, group['id']])
 345                         g.db.commit()
 346                 for group in groups:
 347                     group = query_db('select id from groups where id = ?', group, one=True)
 348                     if group is None:
 349                         flash(u'Le groupe portant l\'id %s n\'existe pas.' % group, 'warning')
 350                     else:
 351                         if not group in user['groups']:
 352                             g.db.execute('insert into user_group values (?, ?)', [user['id'], group['id']])
 353                             g.db.commit()
 354                 user = query_db('select * from users where id = ?', [iduser], one=True)
 355                 user['groups'] = query_db('select groups.* from groups \
 356                                            join user_group on groups.id = user_group.id_group \
 357                                            where id_user = ?', [iduser])
 358                 flash(u'Le profil a été mis à jour !', 'success')
 359             else:
 360                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 361         else:
 362             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 363     groups = query_db('select * from groups where system=0')
 364     return render_template('admin_user_edit.html', user=user, groups=groups)
 365
 366 @app.route('/admin/users/delete/<iduser>')
 367 def admin_user_del(iduser):
 368     if not session.get('user').get('is_admin'):
 369         abort(401)
 370     user = query_db('select * from users where id = ?', [iduser], one=True)
 371     if user is None:
 372         abort(404)
 373     g.db.execute('delete from users where id = ?', [iduser])
 374     g.db.commit()
 375     return redirect(url_for('admin_users'))
 376
 377 #-------------
 378 # Roles admin
 379
 380 @app.route('/admin/groups')
 381 def admin_groups():
 382     if not session.get('user').get('is_admin'):
 383         abort(401)
 384     groups = query_db('select groups.*, count(user_group.id_user) as nb_users \
 385                        from (select groups.*, count(votes.id) as nb_votes \
 386                          from groups left join votes on votes.id_group = groups.id \
 387                          group by groups.id) as groups \
 388                        left join user_group on user_group.id_group = groups.id group by groups.id')
 389     return render_template('admin_groups.html', groups=groups)
 390
 391 @app.route('/admin/groups/add', methods=['POST'])
 392 def admin_group_add():
 393     if not session.get('user').get('is_admin'):
 394         abort(401)
 395     if request.method == 'POST':
 396         if request.form['name']:
 397             g.db.execute('insert into groups (name) values (?)', [request.form['name']])
 398             g.db.commit()
 399         else:
 400             flash(u"Vous devez spécifier un nom.", "error")
 401     return redirect(url_for('admin_groups'))
 402
 403 @app.route('/admin/groups/delete/<idgroup>')
 404 def admin_group_del(idgroup):
 405     if not session.get('user').get('is_admin'):
 406         abort(401)
 407     group = query_db('select * from groups where id = ?', [idgroup], one=True)
 408     if group is None:
 409         abort(404)
 410     if group['system']:
 411         abort(401)
 412     g.db.execute('delete from groups where id = ?', [idgroup])
 413     g.db.commit()
 414     return redirect(url_for('admin_groups'))
 415
 416 #------------
 417 # Votes list
 418
 419 @app.route('/votes/<votes>')
 420 def votes(votes):
 421     today = date.today()
 422     active_button = votes
 423     max_votes = 'select id_group, count(*) as max_votes from user_group group by id_group'
 424     basequery = 'select votes.*, max_votes from votes \
 425                  left join (' + max_votes + ') as max_votes on votes.id_group = max_votes.id_group'
 426     nb_votes = 'select id_vote, count(*) as nb_votes \
 427                 from (select id_user, id_vote \
 428                   from user_vote \
 429                   group by id_user, id_vote) \
 430                 group by id_vote'
 431     basequery = 'select * from (' + basequery + ') \
 432                  left join (' + nb_votes + ') on id = id_vote'
 433     basequery = 'select *, votes.id as voteid, groups.name as groupname from (' + basequery + ') as votes \
 434                  join groups on groups.id = id_group \
 435                  where is_open=1 and is_hidden=0'
 436     if votes == 'all':
 437         votes = query_db(basequery + ' order by date_end')
 438     elif votes == 'archive':
 439         votes = query_db(basequery + ' and is_terminated=1 order by date_end desc')
 440     elif votes == 'current':
 441         votes = query_db(basequery + ' and is_terminated=0 order by date_end')
 442     elif votes == 'waiting':
 443         basequery = 'select votes.* from user_group \
 444                      join (' + basequery + ') as votes on votes.id_group = user_group.id_group \
 445                      where user_group.id_user = ?'
 446         already_voted = 'select id_vote from user_vote \
 447                          where id_user = ?'
 448         votes = query_db(basequery + ' and votes.id not in (' + already_voted + ') and is_terminated=0', [get_userid(), get_userid()])
 449     else:
 450         abort(404)
 451     for vote in votes:
 452         if not vote.get('nb_votes'):
 453             vote['nb_votes'] = 0
 454         if vote.get('max_votes'):
 455             vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 456     return render_template('votes.html', votes=votes, active_button=active_button)
 457
 458 #------
 459 # Vote
 460
 461 def can_see_vote(idvote, iduser=-1):
 462     vote = query_db('select * from votes where id=?', [idvote], one=True)
 463     if vote is None:
 464         return False
 465     if not vote['is_public']:
 466         user = query_db('select * from users where id=?', [iduser], one=True)
 467         if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id']], one=True) is None:
 468             return False
 469     return True
 470
 471 def can_vote(idvote, iduser=-1):
 472     vote = query_db('select * from votes where id=?', [idvote], one=True)
 473     if vote is None:
 474         return False
 475     if vote['is_terminated'] == 0:
 476         if iduser > 0:
 477             if can_see_vote(idvote, iduser):
 478                 if not has_voted(idvote, iduser):
 479                     if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id_group']], one=True):
 480                         return True
 481     return False
 482
 483 def has_voted(idvote, iduser=-1):
 484     vote = query_db('select * from user_vote \
 485                      where id_vote = ? and id_user = ?', [idvote, iduser], one=True)
 486     return (vote is not None)
 487
 488 @app.route('/vote/<idvote>', methods=['GET', 'POST'])
 489 def vote(idvote):
 490     vote = query_db('select votes.*, groups.name as groupname, users.name as author from votes \
 491                      join groups on groups.id=votes.id_group \
 492                      join users on users.id=votes.id_author \
 493                      where votes.id=?', [idvote], one=True)
 494     if vote is None:
 495         abort(404)
 496     if can_see_vote(idvote, get_userid()):
 497         choices = query_db('select name, id from choices where id_vote=?', [idvote])
 498         values = query_db('select weight,name from values_ where id_cardinal=? order by weight ASC', [vote['id_cardinal']])
 499         values.insert(0, {'weight':None,'name':u'=X̄ (Pas d’Opinion)'})
 500         for v in values:
 501             v['class'] = "value_" + re.sub('[^a-zA-Z0-9-]', '_', v['name'])
 502         weights = OrderedDict([(v['weight'], v) for v in values])
 503         if request.method == 'POST':
 504             if can_vote(idvote, get_userid()):
 505                 userid = session.get('user').get('id') if not vote['is_anonymous'] else None
 506                 # ACTION: store user's choices
 507                 for choice in choices:
 508                     if choice['name'] is None:
 509                         continue
 510                     weight = request.form.get('value-'+str(choice['id']),None)
 511                     if weight is not None and len(weight) is 0:
 512                         weight = None
 513                     if weight is not None:
 514                         weight = int(weight)
 515                     g.db.execute('insert into user_choice (id_user, id_choice, id_cardinal, weight) \
 516                                   values (?, ?, ?, ?)', [userid, choice['id'], vote['id_cardinal'], weight])
 517                     g.db.commit()
 518                 # ACTION: randomize storage when anonymous votes
 519                 if vote['is_anonymous']:
 520                     g.db.execute('delete from user_choice_buffer_anonymous')
 521                     g.db.execute('insert into user_choice_buffer_anonymous select * \
 522                                   from user_choice where id_choice in (%s)'
 523                                   % ','.join(['?'] * len(choices))
 524                                 , tuple(c['id'] for c in choices))
 525                     g.db.execute('delete from user_choice where id_choice in (%s)'
 526                                  % ','.join(['?'] * len(choices))
 527                                 , tuple(c['id'] for c in choices))
 528                     g.db.execute('insert into user_choice select * \
 529                                   from user_choice_buffer_anonymous \
 530                                   order by random()')
 531                     g.db.execute('delete from user_choice_buffer_anonymous')
 532                     g.db.commit()
 533                 g.db.execute('insert into user_vote (id_user, id_vote) \
 534                               values (?, ?)'
 535                             , [session.get('user').get('id'), vote['id']])
 536                 g.db.commit()
 537             else:
 538                 abort(401)
 539         # ACTION: count quorum numbers
 540         max_votes = query_db('select id_group, count(*) as nb \
 541                               from user_group where id_group = ? \
 542                               group by id_group', [vote['id_group']], one=True)
 543         if max_votes is None:
 544             vote['percent'] = 0
 545             vote['nb_votes'] = 0
 546         else:
 547             vote['max_votes'] = max_votes['nb']
 548             votes = query_db('select id_vote, count(*) as nb \
 549                               from (select id_user, id_vote from user_vote \
 550                                     group by id_user, id_vote) \
 551                               where id_vote = ? group by id_vote', [idvote], one=True)
 552             if votes is None:
 553                 vote['percent'] = 0
 554                 vote['nb_votes'] = 0
 555             else:
 556                 vote['nb_votes'] = votes['nb']
 557                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 558         # ACTION: query users' choices joined with users' identity if not anonymous
 559         user_choices = query_db('select user_choice.id_user as userid, users.name as username, \
 560                                         choices.id as choiceid, choices.name as choice_name, \
 561                                         user_choice.weight as weight \
 562                                  from choices \
 563                                  join user_choice on choices.id = user_choice.id_choice \
 564                                  left join users on userid = users.id \
 565                                  left join user_vote on userid = user_vote.id_user and choices.id_vote = user_vote.id_vote \
 566                                  where choices.id_vote = ? \
 567                                  order by user_vote.date,choices.id', [idvote])
 568         # ACTION: aggregate user choices per vote
 569         vote['blank'] = 0
 570         results = OrderedDict()
 571         for c in choices:
 572             choice_values = [(w, { 'nb':0
 573                                  , 'idx':i
 574                                  , 'name':weights[w]['name']
 575                                  , 'class':weights[w]['class'] }
 576                              ) for (i,w) in enumerate(weights, start=0)]
 577             choice_values = OrderedDict(choice_values)
 578             results[c['id']] = { 'id':c['id']
 579                                , 'name':c['name']
 580                                , 'sum':0
 581                                , 'average':0.0
 582                                , 'nb':0
 583                                , 'blank':0
 584                                , 'values_':choice_values }
 585         for uc in user_choices:
 586             results[uc['choiceid']]['nb'] += 1
 587             results[uc['choiceid']]['values_'][uc['weight']]['nb'] += 1
 588             if uc['weight'] is None:
 589                 results[uc['choiceid']]['blank'] += 1
 590                 vote['blank'] += 1
 591             else:
 592                 results[uc['choiceid']]['sum'] += uc['weight']
 593         for c in results:
 594             if results[c]['nb'] - results[c]['blank'] != 0:
 595                 results[c]['average'] = results[c]['average'] + (float(results[c]['sum']) / float(results[c]['nb'] - results[c]['blank']))
 596             previous_percent = 0
 597             for w in weights:
 598                 if results[c]['nb'] > 0:
 599                     percent = float(results[c]['values_'][w]['nb'] * 100) / results[c]['nb']
 600                 else:
 601                     percent = 0.
 602                 results[c]['values_'][w]['percent'] = percent
 603                 results[c]['values_'][w]['previous_percent'] = previous_percent
 604                 previous_percent += percent
 605             results[c]['values_'] = results[c]['values_'].values()
 606         results = sorted(results.values(), key=lambda c: c['average'], reverse=True)
 607         len_results = len(results)
 608         if len_results % 2 == 0:
 609             medians = results[len_results/2-1:len_results/2+1]
 610         else:
 611             medians = [results[len_results/2]]
 612         results = { 'list':results
 613                   , 'medians':[m['id'] for m in medians]
 614                   , 'average':sum([r['sum'] for r in results])/len_results }
 615         # ACTION: list user results per user
 616         users = OrderedDict()
 617         if vote['is_anonymous']:
 618             user_votes = query_db('select users.name, id_user as userid \
 619                                    from user_vote \
 620                                    join users on users.id = id_user where id_vote = ?', [idvote])
 621             for uc in user_votes:
 622                 users[uc['userid']] = { 'username':uc['name']
 623                                       , 'choices':{}
 624                                       , 'userid':uc['userid'] }
 625         else:
 626             for uc in user_choices:
 627                 weight = uc['weight']
 628                 value = { 'weight':weight
 629                         , 'name':weights[weight]['name']
 630                         , 'class':weights[weight]['class'] }
 631                 if uc['userid'] in users:
 632                     users[uc['userid']]['choices'][uc['choiceid']] = value
 633                 else:
 634                     users[uc['userid']] = { 'userid':uc['userid']
 635                                           , 'username':uc['username']
 636                                           , 'choices':{uc['choiceid']:value} }
 637         attachments = query_db('select * from attachments where id_vote=?', [idvote])
 638         if query_db('select * from user_group where id_group = ? and id_user = ?'
 639                    , [vote['id_group'], get_userid()], one=True) and not vote['is_terminated']:
 640             flash(u'Ce vote vous concerne !', 'info')
 641         return render_template('vote.html', vote=vote, attachments=attachments
 642                               , values=values, choices=choices, results=results, users=users.values()
 643                               , can_vote=can_vote(idvote, get_userid()))
 644     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
 645     return redirect(url_for('home'))
 646
 647 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 648 def vote_deletechoices(idvote, iduser):
 649     if int(iduser) != get_userid():
 650         abort(401)
 651     vote = query_db('select votes.* from votes \
 652                      where votes.id=?', [idvote], one=True)
 653     if not vote['is_terminated'] and not vote['is_anonymous']:
 654         g.db.execute('delete from user_choice where id_user = ? and id_choice \
 655                       in (select id from choices where id_vote = ?)'
 656                     , [iduser, idvote])
 657         g.db.commit()
 658         g.db.execute('delete from user_vote where id_user = ? and id_vote \
 659                       in (select id_vote from choices where id_vote = ?)'
 660                     , [iduser, idvote])
 661         g.db.commit()
 662     return redirect(url_for('vote', idvote=idvote))
 663
 664 #-------------
 665 # Votes admin
 666
 667 @app.route('/admin/votes/list')
 668 def admin_votes():
 669     if not session.get('user').get('is_admin'):
 670         abort(401)
 671     votes = query_db('select *, votes.id as voteid, groups.name as groupname from votes \
 672                       join groups on groups.id=votes.id_group \
 673                       where is_hidden=0 order by id desc')
 674     return render_template('admin_votes.html', votes=votes, today=date.today().strftime("%Y-%m-%d"))
 675
 676 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 677 def admin_vote_add():
 678     if not session.get('user').get('is_admin'):
 679         abort(401)
 680     cardinals= OrderedDict([(len(values), {'name':name,'values':values,'first':first}) for (name, first, values) in CARDINALS])
 681     if request.method == 'POST':
 682         if request.form['title']:
 683             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 684                 date_begin = date.today()
 685                 date_end = date.today() + timedelta(days=int(request.form['days']))
 686                 transparent = 0
 687                 public = 0
 688                 anonymous = 0
 689                 if 'transparent' in request.form:
 690                     transparent = 1
 691                 if 'public' in request.form:
 692                     public = 1
 693                 if 'anonymous' in request.form:
 694                     anonymous = 1
 695                 try: quorum = float(request.form.get('quorum'))
 696                 except ValueError:
 697                     quorum = 0
 698                 if not (0 <= quorum and quorum <= 1):
 699                     flash(u'Une erreur est survenue !', 'error')
 700                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 701                 if group is None:
 702                     group[id] = 1
 703                 try: cardinal = int(request.form.get('cardinal'))
 704                 except ValueError:
 705                     cardinal = None
 706                 if cardinal in cardinals:
 707                     cardinal_name   = cardinals[cardinal]['name']
 708                     cardinal_values = cardinals[cardinal]['values']
 709                     weight = cardinals[cardinal]['first'] if not cardinals[cardinal]['first'] is None else -(cardinal/2)
 710                     if query_db('select * from cardinals where id = ?', [cardinal], one=True) is None:
 711                         g.db.execute('insert into cardinals (id, name, first) values (?, ?, ?)', [len(cardinal_values), cardinal_name, weight])
 712                         g.db.commit()
 713                         for name in cardinal_values:
 714                             g.db.execute('insert into values_ (id_cardinal, name, weight) values (?, ?, ?)'
 715                                         , [cardinal, name, weight])
 716                             g.db.commit()
 717                             weight += 1
 718                 g.db.execute('insert into votes (title, description, category, \
 719                               date_begin, date_end, quorum, is_transparent, is_public, \
 720                               is_anonymous, id_group, id_author, id_cardinal) \
 721                               values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 722                         [ request.form['title'], request.form['description'], request.form['category']
 723                         , date_begin, date_end, quorum, transparent, public, anonymous
 724                         , group['id'], session['user']['id'], cardinal ])
 725                 g.db.commit()
 726                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc'
 727                                , [request.form['title'], date_begin], one=True)
 728                 if vote is None:
 729                     flash(u'Une erreur est survenue !', 'error')
 730                     return redirect(url_for('home'))
 731                 else:
 732                     if request.form['pattern'] in PATTERNS:
 733                         pattern = PATTERNS[request.form['pattern']]
 734                         for choice in pattern:
 735                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])
 736                             g.db.commit()
 737                     flash(u"Le vote a été créé", 'info')
 738                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 739             else:
 740                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 741         else:
 742             flash(u'Vous devez spécifier un titre.', 'error')
 743     groups = query_db('select * from groups')
 744     return render_template('admin_vote_new.html', groups=groups, cardinals=cardinals
 745                           , quorums=QUORUMS, patterns=PATTERNS)
 746
 747 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 748 def admin_vote_edit(voteid):
 749     if not session.get('user').get('is_admin'):
 750         abort(401)
 751     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 752     if vote is None:
 753         abort(404)
 754     if request.method == 'POST':
 755         if request.form['title']:
 756             if request.form['days'] > 0:
 757                 date_end = datetime.strptime(vote['date_begin'], "%Y-%m-%d") + timedelta(days=int(request.form['days']))
 758                 date_end = date_end.strftime("%Y-%m-%d")
 759             transparent = 0
 760             public = 0
 761             if 'transparent' in request.form:
 762                 transparent = 1
 763             if 'public' in request.form:
 764                 public = 1
 765             isopen = 0
 766             isterminated = 0
 767             if request.form['status'] == 'Ouvert':
 768                 choices = query_db('select id_vote, count(*) as nb \
 769                                     from choices where id_vote = ? \
 770                                     group by id_vote', [voteid], one=True)
 771                 if choices is not None and choices['nb'] >= 1:
 772                     isopen = 1
 773                     previousvote = query_db('select id, is_open, id_group from votes where id = ?', [voteid], one=True)
 774                     if previousvote is None or previousvote['is_open'] == 0:
 775                         users_to_vote = query_db('select users.email, users.name from users \
 776                                                   join user_group on users.id=user_group.id_user \
 777                                                   where user_group.id_group = ?', [previousvote['id_group']])
 778                         for user in users_to_vote:
 779                             link = request.url_root + url_for('vote', idvote=voteid)
 780                             BODY = string.join((
 781                                 "From: %s" % EMAIL,
 782                                 "To: %s" % user['email'],
 783                                 "Subject: [Cavote] %s" % gettext(u"A vote has been opened for your group"),
 784                                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 785                                 "Content-type: text/plain; charset=utf-8",
 786                                 "X-Mailer: %s" % VERSION,
 787                                 "",
 788                                 "%(text)s %(title)s" % {"text": gettext(u"A vote has been opened and you are in a group concerned by it :"), "title": request.form['title']},
 789                                 "",
 790                                 gettext(u"This link will bring you to the form where you will be able to vote :"),
 791                                 link,
 792                                 "",
 793                                 gettext(u"If you think this mail is not for you, please ignore and delete it.")
 794                                 ), "\r\n")
 795                             server = smtplib.SMTP(SMTP_SERVER)
 796                             server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))
 797                             server.quit()
 798                 else:
 799                     flash(u'Vous devez proposer au moins un choix pour ouvrir le vote.', 'error')
 800             elif request.form['status'] == u'Terminé':
 801                 isterminated = 1
 802                 if vote['is_open']:
 803                     isopen = 1
 804             g.db.execute('update votes set title = ?, description = ?, category = ?, quorum = ?, \
 805                           is_transparent = ?, is_public = ?, is_open = ?, is_terminated = ?, \
 806                           date_end = ?, reminder_last_days = ? where id = ?',
 807                           [ request.form['title'], request.form['description'], request.form['category'], request.form['quorum']
 808                           , transparent, public, isopen, isterminated, date_end, request.form['reminder'], voteid ])
 809             g.db.commit()
 810             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 811             flash(u"Le vote a bien été mis à jour.", "success")
 812         else:
 813             flash(u'Vous devez spécifier un titre.', 'error')
 814     vote['duration'] = (datetime.strptime(vote['date_end'], "%Y-%m-%d") - datetime.strptime(vote['date_begin'], "%Y-%m-%d")).days
 815     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 816     choices = query_db('select * from choices where id_vote = ?', [voteid])
 817     values_ = query_db('select * from cardinals where id = ?', [vote['id_cardinal']], one=True)['name']
 818     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 819     if date.today().strftime("%Y-%m-%d") > vote['date_end']:
 820         flash(u'La deadline du vote est expirée, vous devriez terminer le vote.')
 821     return render_template('admin_vote_edit.html', vote=vote, group=group, values_=values_, choices=choices, attachments=attachments, quorums=QUORUMS)
 822
 823 @app.route('/admin/votes/delete/<idvote>')
 824 def admin_vote_del(idvote):
 825     if not session.get('user').get('is_admin'):
 826         abort(401)
 827     vote = query_db('select * from votes where id = ?', [idvote], one=True)
 828     if vote is None:
 829         abort(404)
 830     g.db.execute('update votes set is_hidden=1 where id = ?', [idvote])
 831     g.db.commit()
 832     return redirect(url_for('admin_votes'))
 833
 834 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 835 def admin_vote_addchoice(voteid):
 836     if not session.get('user').get('is_admin'):
 837         abort(401)
 838     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 839     if vote is None:
 840         abort(404)
 841     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 842     g.db.commit()
 843     return redirect(url_for('admin_vote_edit', voteid=voteid))
 844
 845 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 846 def admin_vote_editchoice(voteid, choiceid):
 847     if not session.get('user').get('is_admin'):
 848         abort(401)
 849     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 850     if choice is None:
 851         abort(404)
 852     if request.method == 'POST':
 853         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 854         g.db.commit()
 855     return redirect(url_for('admin_vote_edit', voteid=voteid))
 856
 857 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 858 def admin_vote_deletechoice(voteid, choiceid):
 859     if not session.get('user').get('is_admin'):
 860         abort(401)
 861     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 862     if choice is None:
 863         abort(404)
 864     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 865     g.db.commit()
 866     choices = query_db('select id_vote, count(*) as nb \
 867                         from choices where id_vote = ? \
 868                         group by id_vote', [voteid], one=True)
 869     if choices is None or choices['nb'] < 2:
 870         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 871         g.db.commit()
 872         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 873     return redirect(url_for('admin_vote_edit', voteid=voteid))
 874
 875 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 876 def admin_vote_addattachment(voteid):
 877     if not session.get('user').get('is_admin'):
 878         abort(401)
 879     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 880     if vote is None:
 881         abort(404)
 882     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 883     g.db.commit()
 884     return redirect(url_for('admin_vote_edit', voteid=voteid))
 885
 886 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 887 def admin_vote_deleteattachment(voteid, attachmentid):
 888     if not session.get('user').get('is_admin'):
 889         abort(401)
 890     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 891     if attachment is None:
 892         abort(404)
 893     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 894     g.db.commit()
 895     return redirect(url_for('admin_vote_edit', voteid=voteid))
 896
 897 #------
 898 # Main
 899
 900 if __name__ == '__main__':
 901     app.run()