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