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