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