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