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         publisher = query_db("select name from users where id = ?", [vote['id_author']], one=True)
 482         if publisher is None:
 483             vote['author'] = "NDN"
 484         else:
 485             vote['author'] = publisher['name']
 486         return render_template('vote.html', vote=vote, attachments=attachments, choices=choices, users=users.values(), can_vote=can_vote(idvote, get_userid()))
 487     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
 488     return redirect(url_for('home'))
 489
 490 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 491 def vote_deletechoices(idvote, iduser):
 492     if int(iduser) != get_userid():
 493         abort(401)
 494     g.db.execute('delete from user_choice where id_user = ? and id_choice in (select id from choices where id_vote = ?)',
 495         [iduser, idvote])
 496     g.db.commit()
 497     return redirect(url_for('vote', idvote=idvote))
 498
 499 #-------------
 500 # Votes admin
 501
 502 @app.route('/admin/votes/list')
 503 def admin_votes():
 504     if not session.get('user').get('is_admin'):
 505         abort(401)
 506     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')
 507     return render_template('admin_votes.html', votes=votes, today=date.today().strftime("%Y-%m-%d"))
 508
 509 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 510 def admin_vote_add():
 511     if not session.get('user').get('is_admin'):
 512         abort(401)
 513     if request.method == 'POST':
 514         if request.form['title']:
 515             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 516                 date_begin = date.today()
 517                 date_end = date.today() + timedelta(days=int(request.form['days']))
 518                 transparent = 0
 519                 public = 0
 520                 multiplechoice = 0
 521                 if 'transparent' in request.form.keys():
 522                     transparent = 1
 523                 if 'public' in request.form.keys():
 524                     public = 1
 525                 if 'multiplechoice' in request.form.keys():
 526                     multiplechoice = 1
 527                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 528                 if group is None:
 529                     group[id] = 1
 530                 g.db.execute('insert into votes (title, description, category, date_begin, date_end, is_transparent, is_public, is_multiplechoice, id_group, id_author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 531                         [request.form['title'], request.form['description'], request.form['category'], date_begin, date_end, transparent, public, multiplechoice, group['id'], session['user']['id']])
 532                 g.db.commit()
 533                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc',
 534                         [request.form['title'], date_begin], one=True)
 535                 if vote is None:
 536                     flash(u'Une erreur est survenue !', 'error')
 537                     return redirect(url_for('home'))
 538                 else:
 539                     if request.form['pattern'] in PATTERNS.keys():
 540                         pattern = PATTERNS[request.form['pattern']]
 541                         for choice in pattern:
 542                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])
 543                             g.db.commit()
 544                     flash(u"Le vote a été créé", 'info')
 545                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 546             else:
 547                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 548         else:
 549             flash(u'Vous devez spécifier un titre.', 'error')
 550     groups = query_db('select * from groups')
 551     return render_template('admin_vote_new.html', groups=groups, patterns=PATTERNS)
 552
 553 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 554 def admin_vote_edit(voteid):
 555     if not session.get('user').get('is_admin'):
 556         abort(401)
 557     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 558     if vote is None:
 559         abort(404)
 560     if request.method == 'POST':
 561         if request.form['title']:
 562             if request.form['days'] > 0:
 563                 date_end = datetime.strptime(vote['date_begin'], "%Y-%m-%d") + timedelta(days=int(request.form['days']))
 564                 date_end = date_end.strftime("%Y-%m-%d")
 565             transparent = 0
 566             public = 0
 567             if 'transparent' in request.form.keys():
 568                 transparent = 1
 569             if 'public' in request.form.keys():
 570                 public = 1
 571             isopen = 0
 572             isterminated = 0
 573             if request.form['status'] == 'Ouvert':
 574                 choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 575                 if choices is not None and choices['nb'] >= 2:
 576                     isopen = 1
 577                 else:
 578                     flash(u'Vous devez proposer au moins deux choix pour ouvrir le vote.', 'error')
 579             elif request.form['status'] == u'Terminé':
 580                 isterminated = 1
 581                 if vote['is_open']:
 582                     isopen = 1
 583             g.db.execute('update votes set title = ?, description = ?, category = ?, is_transparent = ?, is_public = ?, is_open = ?, is_terminated = ?, date_end = ? where id = ?',
 584                     [request.form['title'], request.form['description'], request.form['category'], transparent, public, isopen, isterminated, date_end, voteid])
 585             g.db.commit()
 586             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 587             flash(u"Le vote a bien été mis à jour.", "success")
 588         else:
 589             flash(u'Vous devez spécifier un titre.', 'error')
 590     vote['duration'] = (datetime.strptime(vote['date_end'], "%Y-%m-%d")
 591             - datetime.strptime(vote['date_begin'], "%Y-%m-%d")).days
 592     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 593     choices = query_db('select * from choices where id_vote = ?', [voteid])
 594     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 595     if date.today().strftime("%Y-%m-%d") > vote['date_end']:
 596         flash(u'La deadline du vote est expirée, vous devriez terminer le vote.')
 597     return render_template('admin_vote_edit.html', vote=vote, group=group, choices=choices, attachments=attachments)
 598
 599 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 600 def admin_vote_addchoice(voteid):
 601     if not session.get('user').get('is_admin'):
 602         abort(401)
 603     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 604     if vote is None:
 605         abort(404)
 606     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 607     g.db.commit()
 608     return redirect(url_for('admin_vote_edit', voteid=voteid))
 609
 610 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 611 def admin_vote_editchoice(voteid, choiceid):
 612     if not session.get('user').get('is_admin'):
 613         abort(401)
 614     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 615     if choice is None:
 616         abort(404)
 617     if request.method == 'POST':
 618         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 619         g.db.commit()
 620     return redirect(url_for('admin_vote_edit', voteid=voteid))
 621
 622 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 623 def admin_vote_deletechoice(voteid, choiceid):
 624     if not session.get('user').get('is_admin'):
 625         abort(401)
 626     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 627     if choice is None:
 628         abort(404)
 629     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 630     g.db.commit()
 631     choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 632     if choices is None or choices['nb'] < 2:
 633         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 634         g.db.commit()
 635         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 636     return redirect(url_for('admin_vote_edit', voteid=voteid))
 637
 638 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 639 def admin_vote_addattachment(voteid):
 640     if not session.get('user').get('is_admin'):
 641         abort(401)
 642     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 643     if vote is None:
 644         abort(404)
 645     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 646     g.db.commit()
 647     return redirect(url_for('admin_vote_edit', voteid=voteid))
 648
 649 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 650 def admin_vote_deleteattachment(voteid, attachmentid):
 651     if not session.get('user').get('is_admin'):
 652         abort(401)
 653     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 654     if attachment is None:
 655         abort(404)
 656     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 657     g.db.commit()
 658     return redirect(url_for('admin_vote_edit', voteid=voteid))
 659
 660 #------
 661 # Main
 662
 663 if __name__ == '__main__':
 664     app.run()
 665