main.py

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