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