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 import sqlite3
   7 from datetime import date, time, timedelta, datetime
   8 import time
   9 from contextlib import closing
  10 import locale
  11 locale.setlocale(locale.LC_ALL, '')
  12 import os
  13 import hashlib
  14 import smtplib
  15 import string
  16
  17 DATABASE = '/tmp/cavote.db'
  18 SECRET_KEY = '{J@uRKO,xO-PK7B,jF?>iHbxLasF9s#zjOoy=+:'
  19 DEBUG = True
  20 TITLE = u"Cavote FFDN"
  21 EMAIL = '"' + TITLE + '"' + ' <' + u"cavote@ffdn.org" + '>'
  22 VERSION = "cavote 0.1.0"
  23 SMTP_SERVER = "10.33.33.30"
  24 PATTERNS = {u'Oui/Non': [u'Oui', u'Non'], u'Oui/Non/Blanc': [u'Oui', u'Non', u'Blanc'], u'Oui/Non/Peut-être': [u'Oui', u'Non', u'Peut-être']}
  25
  26 app = Flask(__name__)
  27 app.config.from_object(__name__)
  28
  29 def connect_db():
  30     return sqlite3.connect(app.config['DATABASE'])
  31
  32 @app.before_request
  33 def before_request():
  34     g.db = connect_db()
  35     g.db.execute("PRAGMA foreign_keys = ON")
  36
  37 @app.teardown_request
  38 def teardown_request(exception):
  39     g.db.close()
  40
  41 @app.route('/')
  42 def home():
  43     return render_template('index.html', active_button="home")
  44
  45 def query_db(query, args=(), one=False):
  46     cur = g.db.execute(query, args)
  47     rv = [dict((cur.description[idx][0], value)
  48         for idx, value in enumerate(row)) for row in cur.fetchall()]
  49     return (rv[0] if rv else None) if one else rv
  50
  51 def init_db():
  52     with closing(connect_db()) as db:
  53         with app.open_resource('schema.sql') as f:
  54             db.cursor().executescript(f.read())
  55         db.commit()
  56
  57 #----------------
  58 # Login / Logout
  59
  60 def valid_login(username, password):
  61     return query_db('select * from users where email = ? and password = ?', [username, crypt(password)], one=True)
  62
  63 def connect_user(user):
  64     session['user'] = user
  65     del session['user']['password']
  66     del session['user']['key']
  67
  68 def disconnect_user():
  69     session.pop('user', None)
  70
  71 def crypt(passwd):
  72     return hashlib.sha1(passwd).hexdigest()
  73
  74 def keygen():
  75     return hashlib.sha1(os.urandom(24)).hexdigest()
  76
  77 def get_userid():
  78     user = session.get('user')
  79     if user is None:
  80         return -1
  81     elif user.get('id') < 0:
  82         return -1
  83     else:
  84         return user.get('id')
  85
  86 @app.route('/login', methods=['GET', 'POST'])
  87 def login():
  88     if request.method == 'POST':
  89         user = valid_login(request.form['username'], request.form['password'])
  90         if user is None:
  91             flash(u'Email ou mot de passe invalide.', 'error')
  92         else:
  93             connect_user(user)
  94             flash(u'Vous êtes connecté. Bienvenue, %s !' % user['name'], 'success')
  95             if request.args.get('continue'):
  96                 return redirect(request.args['continue'])
  97             return redirect(url_for('home'))
  98     return render_template('login.html')
  99
 100 @app.route('/logout')
 101 def logout():
 102     disconnect_user()
 103     flash(u'Vous avez été déconnecté.', 'info')
 104     if request.args.get('continue') and not "admin" in request.args.get('continue'):
 105         return redirect(request.args['continue'])
 106     return redirect(url_for('home'))
 107
 108 #-----------------
 109 # Change password
 110
 111 @app.route('/password/lost', methods=['GET', 'POST'])
 112 def password_lost():
 113     info = None
 114     if request.method == 'POST':
 115         user = query_db('select * from users where email = ?', [request.form['email']], one=True)
 116         if user is None:
 117             flash('Cet utilisateur n\'existe pas !', 'error')
 118         else:
 119             key = keygen()
 120             g.db.execute('update users set key = ? where id = ?', [key, user['id']])
 121             g.db.commit()
 122             link = request.url_root + url_for('login_key', userid=user['id'], key=key)
 123             BODY = string.join((
 124                 "From: %s" % EMAIL,
 125                 "To: %s" % user['email'],
 126                 "Subject: [Cavote] Password lost",
 127                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 128                 "X-Mailer: %s" % VERSION,
 129                 "",
 130                 "You have lost your password.",
 131                 "This link will log you without password.",
 132                 "Don't forget to define a new one as soon a possible!",
 133                 "This link will only work one time.",
 134                 "",
 135                 link,
 136                 "",
 137                 "If you think this mail is not for you, please ignore and delete it."
 138                 ), "\r\n")
 139             server = smtplib.SMTP(SMTP_SERVER)
 140             server.sendmail(EMAIL, [user['email']], BODY)
 141             server.quit()
 142             flash(u"Un mail a été envoyé à " + user['email'], 'info')
 143     return render_template('password_lost.html')
 144
 145 @app.route('/login/<userid>/<key>')
 146 def login_key(userid, key):
 147     user = query_db('select * from users where id = ? and key = ?', [userid, key], one=True)
 148     if user is None or user['key'] == "invalid":
 149         abort(404)
 150     else:
 151         connect_user(user)
 152         g.db.execute('update users set key = "invalid" where id = ?', [user['id']])
 153         g.db.commit()
 154         flash(u"Veuillez mettre à jour votre mot de passe", 'info')
 155         return redirect(url_for('user_password', userid=user['id']))
 156
 157 #---------------
 158 # User settings
 159
 160 @app.route('/user/<userid>')
 161 def user(userid):
 162     if int(userid) != get_userid():
 163         abort(401)
 164     groups = query_db('select * from groups join user_group on id=id_group where id_user = ?', userid)
 165     return render_template('user.html', groups=groups)
 166
 167 @app.route('/user/settings/<userid>', methods=['GET', 'POST'])
 168 def user_edit(userid):
 169     if int(userid) != get_userid():
 170         abort(401)
 171     if request.method == 'POST':
 172         if query_db('select * from users where email=? and id!=?', [request.form['email'], userid], one=True) is None:
 173             if query_db('select * from users where name=? and id!=?', [request.form['name'], userid], one=True) is None:
 174                 g.db.execute('update users set email = ?, name = ?, organization = ? where id = ?',
 175                         [request.form['email'], request.form['name'], request.form['organization'], session['user']['id']])
 176                 g.db.commit()
 177                 disconnect_user()
 178                 user = query_db('select * from users where id=?', [userid], one=True)
 179                 if user is None:
 180                     flash(u'Une erreur s\'est produite.', 'error')
 181                     return redirect(url_for('login'))
 182                 connect_user(user)
 183                 flash(u'Votre profil a été mis à jour !', 'success')
 184             else:
 185                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 186         else:
 187             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 188     return render_template('user_edit.html')
 189
 190 @app.route('/user/password/<userid>', methods=['GET', 'POST'])
 191 def user_password(userid):
 192     if int(userid) != get_userid():
 193         abort(401)
 194     if request.method == 'POST':
 195         if request.form['password'] == request.form['password2']:
 196             g.db.execute('update users set password = ? where id = ?', [crypt(request.form['password']), session['user']['id']])
 197             g.db.commit()
 198             flash(u'Votre mot de passe a été mis à jour.', 'success')
 199         else:
 200             flash(u'Les mots de passe sont différents.', 'error')
 201     return render_template('user_edit.html')
 202
 203 #------------
 204 # User admin
 205
 206 @app.route('/admin/users')
 207 def admin_users():
 208     if not session.get('user').get('is_admin'):
 209         abort(401)
 210     tuples = query_db('select *, groups.name as groupname from (select *, id as userid, name as username from users join user_group on id=id_user order by id desc) join groups on id_group=groups.id')
 211     users = dict()
 212     for t in tuples:
 213         if t['userid'] in users:
 214             users[t['userid']]['groups'].append(t["groupname"])
 215         else:
 216             users[t['userid']] = dict()
 217             users[t['userid']]['userid'] = t['userid']
 218             users[t['userid']]['email'] = t['email']
 219             users[t['userid']]['username'] = t['username']
 220             users[t['userid']]['is_admin'] = t['is_admin']
 221             users[t['userid']]['groups'] = [t['groupname']]
 222
 223     return render_template('admin_users.html', users=users.values())
 224
 225 @app.route('/admin/users/add', methods=['GET', 'POST'])
 226 def admin_user_add():
 227     if not session.get('user').get('is_admin'):
 228         abort(401)
 229     if request.method == 'POST':
 230         if request.form['email']:
 231             if query_db('select * from users where email=?', [request.form['email']], one=True) is None:
 232                 if request.form['username']:
 233                     if query_db('select * from users where name=?', [request.form['username']], one=True) is None:
 234                         password = keygen()
 235                         admin = 0
 236                         if 'admin' in request.form.keys():
 237                             admin = 1
 238                         key = keygen()
 239                         g.db.execute('insert into users (email, name, organization, password, is_admin, key) values (?, ?, ?, ?, ?, ?)',
 240                                 [request.form['email'], request.form['username'], request.form['organization'], password, admin, key])
 241                         g.db.commit()
 242                         user = query_db('select * from users where email = ?', [request.form["email"]], one=True)
 243                         if user:
 244                             groups = request.form.getlist('groups')
 245                             groups.append('1')
 246                             for group in groups:
 247                                 if query_db('select id from groups where id = ?', group, one=True) is None:
 248                                     flash(u'Le groupe portant l\'id %s n\'existe pas.' % group, 'warning')
 249                                 else:
 250                                     g.db.execute('insert into user_group values (?, ?)', [user['id'], group])
 251                                     g.db.commit()
 252                             link = request.url_root + url_for('login_key', userid=user['id'], key=user['key'])
 253                             BODY = string.join((
 254                             "From: %s" % EMAIL,
 255                             "To: %s" % user['email'],
 256                             "Subject: [Cavote] Welcome",
 257                             "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 258                             "X-Mailer: %s" % VERSION,
 259                             "",
 260                             "Hi %s!" % user['name'],
 261                             "Welcome on %s." % TITLE,
 262                             "Your account's adresse is : %s." % user['email'],
 263                             "",
 264                             "To log in for the first time and set your password, please follow this link :",
 265                             link,
 266                             ""
 267                             ), "\r\n")
 268                             server = smtplib.SMTP(SMTP_SERVER)
 269                             server.sendmail(EMAIL, [user['email']], BODY)
 270                             server.quit()
 271                             flash(u'Le nouvel utilisateur a été créé avec succès', 'success')
 272                             return redirect(url_for('admin_users'))
 273                         else:
 274                             flash(u'Une erreur s\'est produite.', 'error')
 275                     else:
 276                         flash(u'Le nom ' + request.form['username'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 277                 else:
 278                     flash(u"Vous devez spécifier un nom d'utilisateur.", 'error')
 279             else:
 280                 flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 281         else:
 282             flash(u"Vous devez spécifier une adresse email.", 'error')
 283     groups = query_db('select * from groups where system=0')
 284     return render_template('admin_user_new.html', groups=groups)
 285
 286 @app.route('/admin/users/edit/<iduser>', methods=['GET', 'POST'])
 287 def admin_user_edit(iduser):
 288     if not session.get('user').get('is_admin'):
 289         abort(401)
 290     user = query_db('select * from users where id = ?', [iduser], one=True)
 291     user['groups'] = query_db('select groups.* from groups join user_group on groups.id = user_group.id_group where id_user = ?', [iduser])
 292     if user is None:
 293         abort(404)
 294     if request.method == 'POST':
 295         if query_db('select * from users where email=? and id!=?', [request.form['email'], iduser], one=True) is None:
 296             if query_db('select * from users where name=? and id!=?', [request.form['name'], iduser], one=True) is None:
 297                 admin = 0
 298                 if 'admin' in request.form.keys():
 299                     admin = 1
 300                 g.db.execute('update users set email = ?, name = ?, organization = ?, is_admin = ? where id = ?',
 301                         [request.form['email'], request.form['name'], request.form['organization'], admin, iduser])
 302                 g.db.commit()
 303                 groups = request.form.getlist('groups')
 304                 groups.append('1')
 305                 for group in user['groups']:
 306                     if not group['id'] in groups:
 307                         g.db.execute('delete from user_group where id_user = ? and id_group = ?', [iduser, group['id']])
 308                         g.db.commit()
 309                 for group in groups:
 310                     group = query_db('select id from groups where id = ?', group, one=True)
 311                     if group is None:
 312                         flash(u'Le groupe portant l\'id %s n\'existe pas.' % group, 'warning')
 313                     else:
 314                         if not group in user['groups']:
 315                             g.db.execute('insert into user_group values (?, ?)', [user['id'], group['id']])
 316                             g.db.commit()
 317                 user = query_db('select * from users where id = ?', [iduser], one=True)
 318                 user['groups'] = query_db('select groups.* from groups join user_group on groups.id = user_group.id_group where id_user = ?', [iduser])
 319                 flash(u'Le profil a été mis à jour !', 'success')
 320             else:
 321                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 322         else:
 323             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 324     groups = query_db('select * from groups where system=0')
 325     return render_template('admin_user_edit.html', user=user, groups=groups)
 326
 327 @app.route('/admin/users/delete/<iduser>')
 328 def admin_user_del(iduser):
 329     if not session.get('user').get('is_admin'):
 330         abort(401)
 331     user = query_db('select * from users where id = ?', [iduser], one=True)
 332     if user is None:
 333         abort(404)
 334     g.db.execute('delete from users where id = ?', [iduser])
 335     g.db.commit()
 336     return redirect(url_for('admin_users'))
 337
 338 #-------------
 339 # Roles admin
 340
 341 @app.route('/admin/groups')
 342 def admin_groups():
 343     if not session.get('user').get('is_admin'):
 344         abort(401)
 345     groups = query_db('select groups.*, count(user_group.id_user) as nb_users from (select groups.*, count(votes.id) as nb_votes from groups left join votes on votes.id_group = groups.id group by groups.id) as groups left join user_group on user_group.id_group = groups.id group by groups.id')
 346     return render_template('admin_groups.html', groups=groups)
 347
 348 @app.route('/admin/groups/add', methods=['POST'])
 349 def admin_group_add():
 350     if not session.get('user').get('is_admin'):
 351         abort(401)
 352     if request.method == 'POST':
 353         if request.form['name']:
 354             g.db.execute('insert into groups (name) values (?)', [request.form['name']])
 355             g.db.commit()
 356         else:
 357             flash(u"Vous devez spécifier un nom.", "error")
 358     return redirect(url_for('admin_groups'))
 359
 360 @app.route('/admin/groups/delete/<idgroup>')
 361 def admin_group_del(idgroup):
 362     if not session.get('user').get('is_admin'):
 363         abort(401)
 364     group = query_db('select * from groups where id = ?', [idgroup], one=True)
 365     if group is None:
 366         abort(404)
 367     if group['system']:
 368         abort(401)
 369     g.db.execute('delete from groups where id = ?', [idgroup])
 370     g.db.commit()
 371     return redirect(url_for('admin_groups'))
 372
 373 #------------
 374 # Votes list
 375
 376 @app.route('/votes/<votes>')
 377 def votes(votes):
 378     today = date.today()
 379     active_button = votes
 380     max_votes ='select id_group, count(*) as max_votes from user_group group by id_group'
 381     basequery = 'select votes.*, max_votes from votes left join (' + max_votes + ') as max_votes on votes.id_group = max_votes.id_group'
 382     nb_votes = 'select id_vote, count(*) as nb_votes from (select id_user, id_vote from user_choice join choices on id_choice = choices.id group by id_user, id_vote) group by id_vote'
 383     basequery = 'select * from (' + basequery + ') left join (' + nb_votes + ') on id = id_vote'
 384     basequery = 'select *, votes.id as voteid, groups.name as groupname from (' + basequery + ') as votes join groups on groups.id = id_group where is_open=1'
 385     if votes == 'all':
 386         votes = query_db(basequery + ' order by date_end')
 387     elif votes == 'archive':
 388         votes = query_db(basequery + ' and is_terminated=1 order by date_end desc')
 389     elif votes == 'current':
 390         votes = query_db(basequery + ' and is_terminated=0 order by date_end')
 391     elif votes == 'waiting':
 392         basequery = 'select votes.* from user_group join (' + basequery + ') as votes on votes.id_group = user_group.id_group where user_group.id_user = ?'
 393         already_voted = 'select id_vote from user_choice join choices on user_choice.id_choice = choices.id where id_user = ?'
 394         votes = query_db(basequery + ' and votes.id not in (' + already_voted + ') and is_terminated=0', [get_userid(), get_userid()])
 395     else:
 396         abort(404)
 397     for vote in votes:
 398         if not vote.get('nb_votes'):
 399             vote['nb_votes'] = 0
 400         if vote.get('max_votes'):
 401             vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 402     return render_template('votes.html', votes=votes, active_button=active_button)
 403
 404 #------
 405 # Vote
 406
 407 def can_see_vote(idvote, iduser=-1):
 408     vote = query_db('select * from votes where id=?', [idvote], one=True)
 409     if vote is None:
 410         return False
 411     if not vote['is_public']:
 412         user = query_db('select * from users where id=?', [iduser], one=True)
 413         if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id']], one=True) is None:
 414             return False
 415     return True
 416
 417 def can_vote(idvote, iduser=-1):
 418     vote = query_db('select * from votes where id=?', [idvote], one=True)
 419     if vote is None:
 420         return False
 421     if vote['is_terminated'] == 0:
 422         if iduser > 0:
 423             if can_see_vote(idvote, iduser):
 424                 if not has_voted(idvote, iduser):
 425                     if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id_group']], one=True):
 426                         return True
 427     return False
 428
 429 def has_voted(idvote, iduser=-1):
 430     vote = query_db('select * from user_choice join choices on id_choice=choices.id where id_vote = ? and id_user = ?', [idvote, iduser], one=True)
 431     return (vote is not None)
 432
 433 @app.route('/vote/<idvote>', methods=['GET', 'POST'])
 434 def vote(idvote):
 435     vote = query_db('select votes.*, groups.name as groupname from votes join groups on groups.id=votes.id_group where votes.id=?', [idvote], one=True)
 436     if vote is None:
 437         abort(404)
 438     if can_see_vote(idvote, get_userid()):
 439         if request.method == 'POST':
 440             if can_vote(idvote, get_userid()):
 441                 if vote['is_multiplechoice'] == 0:
 442                     if query_db('select * from choices where id = ?', [request.form['choice']], one=True) is not None:
 443                         g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 444                                 [session.get('user').get('id'), request.form['choice']])
 445                         g.db.commit()
 446                 else:
 447                     choices = query_db('select name, id from choices where id_vote=?', [idvote])
 448                     for choice in choices:
 449                         if str(choice['id']) in request.form.keys():
 450                             g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 451                                     [session.get('user').get('id'), choice['id']])
 452                             g.db.commit()
 453             else:
 454                 abort(401)
 455         tuples = query_db('select choiceid, choicename, users.id as userid, users.name as username from (select choices.id as choiceid, choices.name as choicename, id_user as userid from choices join user_choice on choices.id = user_choice.id_choice where id_vote = ?) join users on userid = users.id', [idvote])
 456         users = dict()
 457         for t in tuples:
 458             if t['userid'] in users:
 459                 users[t['userid']]['choices'].append(t['choiceid'])
 460             else:
 461                 users[t['userid']] = dict()
 462                 users[t['userid']]['userid'] = t['userid']
 463                 users[t['userid']]['username'] = t['username']
 464                 users[t['userid']]['choices'] = [t['choiceid']]
 465         choices = query_db('select choices.name, choices.id, choices.name, choices.id_vote, count(id_choice) as nb from choices left join user_choice on id_choice = choices.id where id_vote = ? group by id_choice, name, id_vote order by id', [idvote])
 466         attachments = query_db('select * from attachments where id_vote=?', [idvote])
 467         tmp = query_db('select id_group, count(*) as nb from user_group where id_group = ? group by id_group', [vote['id_group']], one=True)
 468         if tmp is None:
 469             vote['percent'] = 0
 470         else:
 471             vote['max_votes'] = tmp['nb']
 472             tmp = query_db('select id_vote, count(*) as nb from (select id_user, id_vote from user_choice join choices on id_choice = choices.id group by id_user, id_vote) where id_vote = ? group by id_vote', [idvote], one=True)
 473             if tmp is None:
 474                 vote['percent'] = 0
 475                 vote['nb_votes'] = 0
 476             else:
 477                 vote['nb_votes'] = tmp['nb']
 478                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 479         if query_db('select * from user_group where id_group = ? and id_user = ?', [vote['id_group'], get_userid()], one=True) and not vote['is_terminated']:
 480             flash(u'Ce vote vous concerne !', 'info')
 481         return render_template('vote.html', vote=vote, attachments=attachments, choices=choices, users=users.values(), can_vote=can_vote(idvote, get_userid()))
 482     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
 483     return redirect(url_for('home'))
 484
 485 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 486 def vote_deletechoices(idvote, iduser):
 487     if int(iduser) != get_userid():
 488         abort(401)
 489     g.db.execute('delete from user_choice where id_user = ? and id_choice in (select id from choices where id_vote = ?)',
 490         [iduser, idvote])
 491     g.db.commit()
 492     return redirect(url_for('vote', idvote=idvote))
 493
 494 #-------------
 495 # Votes admin
 496
 497 @app.route('/admin/votes/list')
 498 def admin_votes():
 499     if not session.get('user').get('is_admin'):
 500         abort(401)
 501     votes = query_db('select *, votes.id as voteid, groups.name as groupname from votes join groups on groups.id=votes.id_group order by id desc')
 502     return render_template('admin_votes.html', votes=votes, today=date.today().strftime("%Y-%m-%d"))
 503
 504 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 505 def admin_vote_add():
 506     if not session.get('user').get('is_admin'):
 507         abort(401)
 508     if request.method == 'POST':
 509         if request.form['title']:
 510             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 511                 date_begin = date.today()
 512                 date_end = date.today() + timedelta(days=int(request.form['days']))
 513                 transparent = 0
 514                 public = 0
 515                 multiplechoice = 0
 516                 if 'transparent' in request.form.keys():
 517                     transparent = 1
 518                 if 'public' in request.form.keys():
 519                     public = 1
 520                 if 'multiplechoice' in request.form.keys():
 521                     multiplechoice = 1
 522                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 523                 if group is None:
 524                     group[id] = 1
 525                 g.db.execute('insert into votes (title, description, category, date_begin, date_end, is_transparent, is_public, is_multiplechoice, id_group, id_author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 526                         [request.form['title'], request.form['description'], request.form['category'], date_begin, date_end, transparent, public, multiplechoice, group['id'], session['user']['id']])
 527                 g.db.commit()
 528                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc',
 529                         [request.form['title'], date_begin], one=True)
 530                 if vote is None:
 531                     flash(u'Une erreur est survenue !', 'error')
 532                     return redirect(url_for('home'))
 533                 else:
 534                     if request.form['pattern'] in PATTERNS.keys():
 535                         pattern = PATTERNS[request.form['pattern']]
 536                         for choice in pattern:
 537                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])
 538                             g.db.commit()
 539                     flash(u"Le vote a été créé", 'info')
 540                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 541             else:
 542                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 543         else:
 544             flash(u'Vous devez spécifier un titre.', 'error')
 545     groups = query_db('select * from groups')
 546     return render_template('admin_vote_new.html', groups=groups, patterns=PATTERNS)
 547
 548 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 549 def admin_vote_edit(voteid):
 550     if not session.get('user').get('is_admin'):
 551         abort(401)
 552     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 553     if vote is None:
 554         abort(404)
 555     if request.method == 'POST':
 556         if request.form['title']:
 557             if request.form['days'] > 0:
 558                 date_end = datetime.strptime(vote['date_begin'], "%Y-%m-%d") + timedelta(days=int(request.form['days']))
 559                 date_end = date_end.strftime("%Y-%m-%d")
 560             transparent = 0
 561             public = 0
 562             if 'transparent' in request.form.keys():
 563                 transparent = 1
 564             if 'public' in request.form.keys():
 565                 public = 1
 566             isopen = 0
 567             isterminated = 0
 568             if request.form['status'] == 'Ouvert':
 569                 choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 570                 if choices is not None and choices['nb'] >= 2:
 571                     isopen = 1
 572                 else:
 573                     flash(u'Vous devez proposer au moins deux choix pour ouvrir le vote.', 'error')
 574             elif request.form['status'] == u'Terminé':
 575                 isterminated = 1
 576                 if vote['is_open']:
 577                     isopen = 1
 578             g.db.execute('update votes set title = ?, description = ?, category = ?, is_transparent = ?, is_public = ?, is_open = ?, is_terminated = ?, date_end = ? where id = ?',
 579                     [request.form['title'], request.form['description'], request.form['category'], transparent, public, isopen, isterminated, date_end, voteid])
 580             g.db.commit()
 581             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 582             flash(u"Le vote a bien été mis à jour.", "success")
 583         else:
 584             flash(u'Vous devez spécifier un titre.', 'error')
 585     vote['duration'] = (datetime.strptime(vote['date_end'], "%Y-%m-%d")
 586             - datetime.strptime(vote['date_begin'], "%Y-%m-%d")).days
 587     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 588     choices = query_db('select * from choices where id_vote = ?', [voteid])
 589     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 590     if date.today().strftime("%Y-%m-%d") > vote['date_end']:
 591         flash(u'La deadline du vote est expirée, vous devriez terminer le vote.')
 592     return render_template('admin_vote_edit.html', vote=vote, group=group, choices=choices, attachments=attachments)
 593
 594 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 595 def admin_vote_addchoice(voteid):
 596     if not session.get('user').get('is_admin'):
 597         abort(401)
 598     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 599     if vote is None:
 600         abort(404)
 601     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 602     g.db.commit()
 603     return redirect(url_for('admin_vote_edit', voteid=voteid))
 604
 605 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 606 def admin_vote_editchoice(voteid, choiceid):
 607     if not session.get('user').get('is_admin'):
 608         abort(401)
 609     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 610     if choice is None:
 611         abort(404)
 612     if request.method == 'POST':
 613         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 614         g.db.commit()
 615     return redirect(url_for('admin_vote_edit', voteid=voteid))
 616
 617 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 618 def admin_vote_deletechoice(voteid, choiceid):
 619     if not session.get('user').get('is_admin'):
 620         abort(401)
 621     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 622     if choice is None:
 623         abort(404)
 624     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 625     g.db.commit()
 626     choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 627     if choices is None or choices['nb'] < 2:
 628         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 629         g.db.commit()
 630         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 631     return redirect(url_for('admin_vote_edit', voteid=voteid))
 632
 633 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 634 def admin_vote_addattachment(voteid):
 635     if not session.get('user').get('is_admin'):
 636         abort(401)
 637     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 638     if vote is None:
 639         abort(404)
 640     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 641     g.db.commit()
 642     return redirect(url_for('admin_vote_edit', voteid=voteid))
 643
 644 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 645 def admin_vote_deleteattachment(voteid, attachmentid):
 646     if not session.get('user').get('is_admin'):
 647         abort(401)
 648     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 649     if attachment is None:
 650         abort(404)
 651     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 652     g.db.commit()
 653     return redirect(url_for('admin_vote_edit', voteid=voteid))
 654
 655 #------
 656 # Main
 657
 658 if __name__ == '__main__':
 659     app.run()
 660