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