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"
  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/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 * from groups')
 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 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         vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 346     return render_template('votes.html', votes=votes, active_button=active_button)
 347
 348 #------
 349 # Vote
 350
 351 def can_see_vote(idvote, iduser=-1):
 352     vote = query_db('select * from votes where id=?', [idvote], one=True)
 353     if vote is None:
 354         return False
 355     if not vote['is_public']:
 356         user = query_db('select * from users where id=?', [iduser], one=True)
 357         if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id']], one=True) is None:
 358             return False
 359     return True
 360
 361 def can_vote(idvote, iduser=-1):
 362     vote = query_db('select * from votes where id=?', [idvote], one=True)
 363     if vote is None:
 364         return False
 365     if vote['is_terminated'] == 0:
 366         if iduser > 0:
 367             if can_see_vote(idvote, iduser):
 368                 if not has_voted(idvote, iduser):
 369                     if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id_group']], one=True):
 370                         return True
 371     return False
 372
 373 def has_voted(idvote, iduser=-1):
 374     vote = query_db('select * from user_choice join choices on id_choice=choices.id where id_vote = ? and id_user = ?', [idvote, iduser], one=True)
 375     return (vote is not None)
 376
 377 @app.route('/vote/<idvote>', methods=['GET', 'POST'])
 378 def vote(idvote):
 379     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)
 380     if vote is None:
 381         abort(404)
 382     if can_see_vote(idvote, get_userid()):
 383         if request.method == 'POST':
 384             if can_vote(idvote, get_userid()):
 385                 if vote['is_multiplechoice'] == 0:
 386                     if query_db('select * from choices where id = ?', [request.form['choice']], one=True) is not None:
 387                         g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 388                                 [session.get('user').get('id'), request.form['choice']])
 389                         g.db.commit()
 390                 else:
 391                     choices = query_db('select name, id from choices where id_vote=?', [idvote])
 392                     for choice in choices:
 393                         if str(choice['id']) in request.form.keys():
 394                             g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 395                                     [session.get('user').get('id'), choice['id']])
 396                             g.db.commit()
 397             else:
 398                 abort(401)
 399         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])
 400         users = dict()
 401         for t in tuples:
 402             if t['userid'] in users:
 403                 users[t['userid']]['choices'].append(t['choiceid'])
 404             else:
 405                 users[t['userid']] = dict()
 406                 users[t['userid']]['userid'] = t['userid']
 407                 users[t['userid']]['username'] = t['username']
 408                 users[t['userid']]['choices'] = [t['choiceid']]
 409         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])
 410         attachments = query_db('select * from attachments where id_vote=?', [idvote])
 411         tmp = query_db('select id_group, count(*) as nb from user_group where id_group = ? group by id_group', [vote['id_group']], one=True)
 412         if tmp is None:
 413             vote['percent'] = 0
 414         else:
 415             vote['max_votes'] = tmp['nb']
 416             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)
 417             if tmp is None:
 418                 vote['percent'] = 0
 419             else:
 420                 vote['nb_votes'] = tmp['nb']
 421                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 422         return render_template('vote.html', vote=vote, attachments=attachments, choices=choices, users=users.values(), can_vote=can_vote(idvote, get_userid()))
 423     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
 424     return redirect(url_for('home'))
 425
 426 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 427 def vote_deletechoices(idvote, iduser):
 428     if int(iduser) != get_userid():
 429         abort(401)
 430     g.db.execute('delete from user_choice where id_user = ? and id_choice in (select id from choices where id_vote = ?)',
 431         [iduser, idvote])
 432     g.db.commit()
 433     return redirect(url_for('vote', idvote=idvote))
 434
 435 #-------------
 436 # Votes admin
 437
 438 @app.route('/admin/votes/list')
 439 def admin_votes():
 440     if not session.get('user').get('is_admin'):
 441         abort(401)
 442     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')
 443     return render_template('admin_votes.html', votes=votes)
 444
 445 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 446 def admin_vote_add():
 447     if not session.get('user').get('is_admin'):
 448         abort(401)
 449     if request.method == 'POST':
 450         if request.form['title']:
 451             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 452                 date_begin = date.today()
 453                 date_end = date.today() + timedelta(days=int(request.form['days']))
 454                 transparent = 0
 455                 public = 0
 456                 multiplechoice = 0
 457                 if 'transparent' in request.form.keys():
 458                     transparent = 1
 459                 if 'public' in request.form.keys():
 460                     public = 1
 461                 if 'multiplechoice' in request.form.keys():
 462                     multiplechoice = 1
 463                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 464                 if group is None:
 465                     group[id] = 1
 466                 g.db.execute('insert into votes (title, description, category, date_begin, date_end, is_transparent, is_public, is_multiplechoice, id_group, id_author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 467                         [request.form['title'], request.form['description'], request.form['category'], date_begin, date_end, transparent, public, multiplechoice, group['id'], session['user']['id']])
 468                 g.db.commit()
 469                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc',
 470                         [request.form['title'], date_begin], one=True)
 471                 if vote is None:
 472                     flash(u'Une erreur est survenue !', 'error')
 473                     return redirect(url_for('home'))
 474                 else:
 475                     if request.form['pattern'] in PATTERNS.keys():
 476                         pattern = PATTERNS[request.form['pattern']]
 477                         for choice in pattern:
 478                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])
 479                             g.db.commit()
 480                     flash(u"Le vote a été créé", 'info')
 481                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 482             else:
 483                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 484         else:
 485             flash(u'Vous devez spécifier un titre.', 'error')
 486     groups = query_db('select * from groups')
 487     return render_template('admin_vote_new.html', groups=groups, patterns=PATTERNS)
 488
 489 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 490 def admin_vote_edit(voteid):
 491     if not session.get('user').get('is_admin'):
 492         abort(401)
 493     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 494     if vote is None:
 495         abort(404)
 496     if request.method == 'POST':
 497         if request.form['title']:
 498             # :TODO:maethor:120529: Calculer date_begin pour pouvoir y ajouter duration et obtenir date_end
 499             transparent = 0
 500             public = 0
 501             if 'transparent' in request.form.keys():
 502                 transparent = 1
 503             if 'public' in request.form.keys():
 504                 public = 1
 505             isopen = 0
 506             isterminated = 0
 507             if request.form['status'] == 'Ouvert':
 508                 choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 509                 if choices is not None and choices['nb'] >= 2:
 510                     isopen = 1
 511                 else:
 512                     flash(u'Vous devez proposer au moins deux choix pour ouvrir le vote.', 'error')
 513             elif request.form['status'] == u'Terminé':
 514                 isterminated = 1
 515                 if vote['is_open']:
 516                     isopen = 1
 517             g.db.execute('update votes set title = ?, description = ?, category = ?, is_transparent = ?, is_public = ?, is_open = ?, is_terminated = ? where id = ?',
 518                     [request.form['title'], request.form['description'], request.form['category'], transparent, public, isopen, isterminated, voteid])
 519             g.db.commit()
 520             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 521             flash(u"Le vote a bien été mis à jour.", "success")
 522         else:
 523             flash(u'Vous devez spécifier un titre.', 'error')
 524
 525     # :TODO:maethor:120529: Calculer la durée du vote (différence date_end - date_begin)
 526     vote['duration'] = 15
 527     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 528     choices = query_db('select * from choices where id_vote = ?', [voteid])
 529     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 530     return render_template('admin_vote_edit.html', vote=vote, group=group, choices=choices, attachments=attachments)
 531
 532 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 533 def admin_vote_addchoice(voteid):
 534     if not session.get('user').get('is_admin'):
 535         abort(401)
 536     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 537     if vote is None:
 538         abort(404)
 539     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 540     g.db.commit()
 541     return redirect(url_for('admin_vote_edit', voteid=voteid))
 542
 543 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 544 def admin_vote_editchoice(voteid, choiceid):
 545     if not session.get('user').get('is_admin'):
 546         abort(401)
 547     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 548     if choice is None:
 549         abort(404)
 550     if request.method == 'POST':
 551         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 552         g.db.commit()
 553     return redirect(url_for('admin_vote_edit', voteid=voteid))
 554
 555 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 556 def admin_vote_deletechoice(voteid, choiceid):
 557     if not session.get('user').get('is_admin'):
 558         abort(401)
 559     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 560     if choice is None:
 561         abort(404)
 562     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 563     g.db.commit()
 564     choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 565     if choices is None or choices['nb'] < 2:
 566         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 567         g.db.commit()
 568         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 569     return redirect(url_for('admin_vote_edit', voteid=voteid))
 570
 571 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 572 def admin_vote_addattachment(voteid):
 573     if not session.get('user').get('is_admin'):
 574         abort(401)
 575     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 576     if vote is None:
 577         abort(404)
 578     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 579     g.db.commit()
 580     return redirect(url_for('admin_vote_edit', voteid=voteid))
 581
 582 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 583 def admin_vote_deleteattachment(voteid, attachmentid):
 584     if not session.get('user').get('is_admin'):
 585         abort(401)
 586     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 587     if attachment is None:
 588         abort(404)
 589     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 590     g.db.commit()
 591     return redirect(url_for('admin_vote_edit', voteid=voteid))
 592
 593 #------
 594 # Main
 595
 596 if __name__ == '__main__':
 597     app.run()
 598