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         else:
 546             vote['max_votes'] = max_votes['nb']
 547             votes = query_db('select id_vote, count(*) as nb \
 548                               from (select id_user, id_vote from user_vote \
 549                                     group by id_user, id_vote) \
 550                               where id_vote = ? group by id_vote', [idvote], one=True)
 551             if votes is None:
 552                 vote['percent'] = 0
 553                 vote['nb_votes'] = 0
 554             else:
 555                 vote['nb_votes'] = votes['nb']
 556                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 557         # ACTION: query users' choices joined with users' identity if not anonymous
 558         user_choices = query_db('select user_choice.id_user as userid, users.name as username, \
 559                                         choices.id as choiceid, choices.name as choice_name, \
 560                                         user_choice.weight as weight \
 561                                  from choices \
 562                                  join user_choice on choices.id = user_choice.id_choice \
 563                                  left join users on userid = users.id \
 564                                  left join user_vote on userid = user_vote.id_user and choices.id_vote = user_vote.id_vote \
 565                                  where choices.id_vote = ? \
 566                                  order by user_vote.date,choices.id', [idvote])
 567         # ACTION: aggregate user choices per vote
 568         vote['blank'] = 0
 569         results = OrderedDict()
 570         for c in choices:
 571             choice_values = [(w, { 'nb':0
 572                                  , 'idx':i
 573                                  , 'name':weights[w]['name']
 574                                  , 'class':weights[w]['class'] }
 575                              ) for (i,w) in enumerate(weights, start=0)]
 576             choice_values = OrderedDict(choice_values)
 577             results[c['id']] = { 'id':c['id']
 578                                , 'name':c['name']
 579                                , 'sum':0
 580                                , 'average':0.0
 581                                , 'nb':0
 582                                , 'blank':0
 583                                , 'values_':choice_values }
 584         for uc in user_choices:
 585             results[uc['choiceid']]['nb'] += 1
 586             results[uc['choiceid']]['values_'][uc['weight']]['nb'] += 1
 587             if uc['weight'] is None:
 588                 results[uc['choiceid']]['blank'] += 1
 589                 vote['blank'] += 1
 590             else:
 591                 results[uc['choiceid']]['sum'] += uc['weight']
 592         for c in results:
 593             if results[c]['nb'] - results[c]['blank'] != 0:
 594                 results[c]['average'] = results[c]['average'] + (float(results[c]['sum']) / float(results[c]['nb'] - results[c]['blank']))
 595             previous_percent = 0
 596             for w in weights:
 597                 if results[c]['nb'] > 0:
 598                     percent = float(results[c]['values_'][w]['nb'] * 100) / results[c]['nb']
 599                 else:
 600                     percent = 0.
 601                 results[c]['values_'][w]['percent'] = percent
 602                 results[c]['values_'][w]['previous_percent'] = previous_percent
 603                 previous_percent += percent
 604             results[c]['values_'] = results[c]['values_'].values()
 605         results = sorted(results.values(), key=lambda c: c['average'], reverse=True)
 606         len_results = len(results)
 607         if len_results % 2 == 0:
 608             medians = results[len_results/2-1:len_results/2+1]
 609         else:
 610             medians = [results[len_results/2]]
 611         results = { 'list':results
 612                   , 'medians':[m['id'] for m in medians]
 613                   , 'average':sum([r['sum'] for r in results])/len_results }
 614         # ACTION: list user results per user
 615         users = OrderedDict()
 616         if vote['is_anonymous']:
 617             user_votes = query_db('select users.name, id_user as userid \
 618                                    from user_vote \
 619                                    join users on users.id = id_user where id_vote = ?', [idvote])
 620             for uc in user_votes:
 621                 users[uc['userid']] = { 'username':uc['name']
 622                                       , 'choices':{}
 623                                       , 'userid':uc['userid'] }
 624         else:
 625             for uc in user_choices:
 626                 weight = uc['weight']
 627                 value = { 'weight':weight
 628                         , 'name':weights[weight]['name']
 629                         , 'class':weights[weight]['class'] }
 630                 if uc['userid'] in users:
 631                     users[uc['userid']]['choices'][uc['choiceid']] = value
 632                 else:
 633                     users[uc['userid']] = { 'userid':uc['userid']
 634                                           , 'username':uc['username']
 635                                           , 'choices':{uc['choiceid']:value} }
 636         attachments = query_db('select * from attachments where id_vote=?', [idvote])
 637         if query_db('select * from user_group where id_group = ? and id_user = ?'
 638                    , [vote['id_group'], get_userid()], one=True) and not vote['is_terminated']:
 639             flash(u'Ce vote vous concerne !', 'info')
 640         return render_template('vote.html', vote=vote, attachments=attachments
 641                               , values=values, choices=choices, results=results, users=users.values()
 642                               , can_vote=can_vote(idvote, get_userid()))
 643     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
 644     return redirect(url_for('home'))
 645
 646 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 647 def vote_deletechoices(idvote, iduser):
 648     if int(iduser) != get_userid():
 649         abort(401)
 650     vote = query_db('select votes.* from votes \
 651                      where votes.id=?', [idvote], one=True)
 652     if not vote['is_terminated'] and not vote['is_anonymous']:
 653         g.db.execute('delete from user_choice where id_user = ? and id_choice \
 654                       in (select id from choices where id_vote = ?)'
 655                     , [iduser, idvote])
 656         g.db.commit()
 657         g.db.execute('delete from user_vote where id_user = ? and id_vote \
 658                       in (select id_vote from choices where id_vote = ?)'
 659                     , [iduser, idvote])
 660         g.db.commit()
 661     return redirect(url_for('vote', idvote=idvote))
 662
 663 #-------------
 664 # Votes admin
 665
 666 @app.route('/admin/votes/list')
 667 def admin_votes():
 668     if not session.get('user').get('is_admin'):
 669         abort(401)
 670     votes = query_db('select *, votes.id as voteid, groups.name as groupname from votes \
 671                       join groups on groups.id=votes.id_group \
 672                       where is_hidden=0 order by id desc')
 673     return render_template('admin_votes.html', votes=votes, today=date.today().strftime("%Y-%m-%d"))
 674
 675 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 676 def admin_vote_add():
 677     if not session.get('user').get('is_admin'):
 678         abort(401)
 679     cardinals= OrderedDict([(len(values), {'name':name,'values':values,'first':first}) for (name, first, values) in CARDINALS])
 680     if request.method == 'POST':
 681         if request.form['title']:
 682             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 683                 date_begin = date.today()
 684                 date_end = date.today() + timedelta(days=int(request.form['days']))
 685                 transparent = 0
 686                 public = 0
 687                 anonymous = 0
 688                 if 'transparent' in request.form:
 689                     transparent = 1
 690                 if 'public' in request.form:
 691                     public = 1
 692                 if 'anonymous' in request.form:
 693                     anonymous = 1
 694                 try: quorum = float(request.form.get('quorum'))
 695                 except ValueError:
 696                     quorum = 0
 697                 if not (0 <= quorum and quorum <= 1):
 698                     flash(u'Une erreur est survenue !', 'error')
 699                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 700                 if group is None:
 701                     group[id] = 1
 702                 try: cardinal = int(request.form.get('cardinal'))
 703                 except ValueError:
 704                     cardinal = None
 705                 if cardinal in cardinals:
 706                     cardinal_name   = cardinals[cardinal]['name']
 707                     cardinal_values = cardinals[cardinal]['values']
 708                     weight = cardinals[cardinal]['first'] if not cardinals[cardinal]['first'] is None else -(cardinal/2)
 709                     if query_db('select * from cardinals where id = ?', [cardinal], one=True) is None:
 710                         g.db.execute('insert into cardinals (id, name) values (?, ?)', [len(cardinal_values), cardinal_name])
 711                         g.db.commit()
 712                         for name in cardinal_values:
 713                             g.db.execute('insert into values_ (id_cardinal, name, weight) values (?, ?, ?)'
 714                                         , [cardinal, name, weight])
 715                             g.db.commit()
 716                             weight += 1
 717                 g.db.execute('insert into votes (title, description, category, \
 718                               date_begin, date_end, quorum, is_transparent, is_public, \
 719                               is_anonymous, id_group, id_author, id_cardinal) \
 720                               values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 721                         [ request.form['title'], request.form['description'], request.form['category']
 722                         , date_begin, date_end, quorum, transparent, public, anonymous
 723                         , group['id'], session['user']['id'], cardinal ])
 724                 g.db.commit()
 725                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc'
 726                                , [request.form['title'], date_begin], one=True)
 727                 if vote is None:
 728                     flash(u'Une erreur est survenue !', 'error')
 729                     return redirect(url_for('home'))
 730                 else:
 731                     if request.form['pattern'] in PATTERNS:
 732                         pattern = PATTERNS[request.form['pattern']]
 733                         for choice in pattern:
 734                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])
 735                             g.db.commit()
 736                     flash(u"Le vote a été créé", 'info')
 737                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 738             else:
 739                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 740         else:
 741             flash(u'Vous devez spécifier un titre.', 'error')
 742     groups = query_db('select * from groups')
 743     return render_template('admin_vote_new.html', groups=groups, cardinals=cardinals
 744                           , quorums=QUORUMS, patterns=PATTERNS)
 745
 746 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 747 def admin_vote_edit(voteid):
 748     if not session.get('user').get('is_admin'):
 749         abort(401)
 750     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 751     if vote is None:
 752         abort(404)
 753     if request.method == 'POST':
 754         if request.form['title']:
 755             if request.form['days'] > 0:
 756                 date_end = datetime.strptime(vote['date_begin'], "%Y-%m-%d") + timedelta(days=int(request.form['days']))
 757                 date_end = date_end.strftime("%Y-%m-%d")
 758             transparent = 0
 759             public = 0
 760             if 'transparent' in request.form:
 761                 transparent = 1
 762             if 'public' in request.form:
 763                 public = 1
 764             isopen = 0
 765             isterminated = 0
 766             if request.form['status'] == 'Ouvert':
 767                 choices = query_db('select id_vote, count(*) as nb \
 768                                     from choices where id_vote = ? \
 769                                     group by id_vote', [voteid], one=True)
 770                 if choices is not None and choices['nb'] >= 1:
 771                     isopen = 1
 772                     previousvote = query_db('select id, is_open, id_group from votes where id = ?', [voteid], one=True)
 773                     if previousvote is None or previousvote['is_open'] == 0:
 774                         users_to_vote = query_db('select users.email, users.name from users \
 775                                                   join user_group on users.id=user_group.id_user \
 776                                                   where user_group.id_group = ?', [previousvote['id_group']])
 777                         for user in users_to_vote:
 778                             link = request.url_root + url_for('vote', idvote=voteid)
 779                             BODY = string.join((
 780                                 "From: %s" % EMAIL,
 781                                 "To: %s" % user['email'],
 782                                 "Subject: [Cavote] %s" % gettext(u"A vote has been opened for your group"),
 783                                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 784                                 "Content-type: text/plain; charset=utf-8",
 785                                 "X-Mailer: %s" % VERSION,
 786                                 "",
 787                                 "%(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']},
 788                                 "",
 789                                 gettext(u"This link will bring you to the form where you will be able to vote :"),
 790                                 link,
 791                                 "",
 792                                 gettext(u"If you think this mail is not for you, please ignore and delete it.")
 793                                 ), "\r\n")
 794                             server = smtplib.SMTP(SMTP_SERVER)
 795                             server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))
 796                             server.quit()
 797                 else:
 798                     flash(u'Vous devez proposer au moins un choix pour ouvrir le vote.', 'error')
 799             elif request.form['status'] == u'Terminé':
 800                 isterminated = 1
 801                 if vote['is_open']:
 802                     isopen = 1
 803             g.db.execute('update votes set title = ?, description = ?, category = ?, quorum = ?, \
 804                           is_transparent = ?, is_public = ?, is_open = ?, is_terminated = ?, \
 805                           date_end = ?, reminder_last_days = ? where id = ?',
 806                           [ request.form['title'], request.form['description'], request.form['category'], request.form['quorum']
 807                           , transparent, public, isopen, isterminated, date_end, request.form['reminder'], voteid ])
 808             g.db.commit()
 809             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 810             flash(u"Le vote a bien été mis à jour.", "success")
 811         else:
 812             flash(u'Vous devez spécifier un titre.', 'error')
 813     vote['duration'] = (datetime.strptime(vote['date_end'], "%Y-%m-%d") - datetime.strptime(vote['date_begin'], "%Y-%m-%d")).days
 814     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 815     choices = query_db('select * from choices where id_vote = ?', [voteid])
 816     values_ = query_db('select * from cardinals where id = ?', [vote['id_cardinal']], one=True)['name']
 817     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 818     if date.today().strftime("%Y-%m-%d") > vote['date_end']:
 819         flash(u'La deadline du vote est expirée, vous devriez terminer le vote.')
 820     return render_template('admin_vote_edit.html', vote=vote, group=group, values_=values_, choices=choices, attachments=attachments, quorums=QUORUMS)
 821
 822 @app.route('/admin/votes/delete/<idvote>')
 823 def admin_vote_del(idvote):
 824     if not session.get('user').get('is_admin'):
 825         abort(401)
 826     vote = query_db('select * from votes where id = ?', [idvote], one=True)
 827     if vote is None:
 828         abort(404)
 829     g.db.execute('update votes set is_hidden=1 where id = ?', [idvote])
 830     g.db.commit()
 831     return redirect(url_for('admin_votes'))
 832
 833 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 834 def admin_vote_addchoice(voteid):
 835     if not session.get('user').get('is_admin'):
 836         abort(401)
 837     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 838     if vote is None:
 839         abort(404)
 840     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 841     g.db.commit()
 842     return redirect(url_for('admin_vote_edit', voteid=voteid))
 843
 844 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 845 def admin_vote_editchoice(voteid, choiceid):
 846     if not session.get('user').get('is_admin'):
 847         abort(401)
 848     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 849     if choice is None:
 850         abort(404)
 851     if request.method == 'POST':
 852         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 853         g.db.commit()
 854     return redirect(url_for('admin_vote_edit', voteid=voteid))
 855
 856 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 857 def admin_vote_deletechoice(voteid, choiceid):
 858     if not session.get('user').get('is_admin'):
 859         abort(401)
 860     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 861     if choice is None:
 862         abort(404)
 863     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 864     g.db.commit()
 865     choices = query_db('select id_vote, count(*) as nb \
 866                         from choices where id_vote = ? \
 867                         group by id_vote', [voteid], one=True)
 868     if choices is None or choices['nb'] < 2:
 869         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 870         g.db.commit()
 871         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 872     return redirect(url_for('admin_vote_edit', voteid=voteid))
 873
 874 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 875 def admin_vote_addattachment(voteid):
 876     if not session.get('user').get('is_admin'):
 877         abort(401)
 878     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 879     if vote is None:
 880         abort(404)
 881     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 882     g.db.commit()
 883     return redirect(url_for('admin_vote_edit', voteid=voteid))
 884
 885 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 886 def admin_vote_deleteattachment(voteid, attachmentid):
 887     if not session.get('user').get('is_admin'):
 888         abort(401)
 889     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 890     if attachment is None:
 891         abort(404)
 892     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 893     g.db.commit()
 894     return redirect(url_for('admin_vote_edit', voteid=voteid))
 895
 896 #------
 897 # Main
 898
 899 if __name__ == '__main__':
 900     app.run()