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