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         if request.method == 'POST':
 471             if can_vote(idvote, get_userid()):
 472                 if vote['is_multiplechoice'] == 0:
 473                     if query_db('select * from choices where id = ?', [request.form['choice']], one=True) is not None:
 474                         g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 475                                 [session.get('user').get('id'), request.form['choice']])
 476                         g.db.commit()
 477                 else:
 478                     choices = query_db('select name, id from choices where id_vote=?', [idvote])
 479                     for choice in choices:
 480                         if str(choice['id']) in request.form.keys():
 481                             g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 482                                     [session.get('user').get('id'), choice['id']])
 483                             g.db.commit()
 484             else:
 485                 abort(401)
 486         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])
 487         users = dict()
 488         for t in tuples:
 489             if t['userid'] in users:
 490                 users[t['userid']]['choices'].append(t['choiceid'])
 491             else:
 492                 users[t['userid']] = dict()
 493                 users[t['userid']]['userid'] = t['userid']
 494                 users[t['userid']]['username'] = t['username']
 495                 users[t['userid']]['choices'] = [t['choiceid']]
 496         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])
 497         attachments = query_db('select * from attachments where id_vote=?', [idvote])
 498         tmp = query_db('select id_group, count(*) as nb from user_group where id_group = ? group by id_group', [vote['id_group']], one=True)
 499         if tmp is None:
 500             vote['percent'] = 0
 501         else:
 502             vote['max_votes'] = tmp['nb']
 503             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)
 504             if tmp is None:
 505                 vote['percent'] = 0
 506                 vote['nb_votes'] = 0
 507             else:
 508                 vote['nb_votes'] = tmp['nb']
 509                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 510         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']:
 511             flash(u'Ce vote vous concerne !', 'info')
 512         return render_template('vote.html', vote=vote, attachments=attachments, choices=choices, users=users.values(), can_vote=can_vote(idvote, get_userid()))
 513     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
 514     return redirect(url_for('home'))
 515
 516 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 517 def vote_deletechoices(idvote, iduser):
 518     if int(iduser) != get_userid():
 519         abort(401)
 520     g.db.execute('delete from user_choice where id_user = ? and id_choice in (select id from choices where id_vote = ?)',
 521         [iduser, idvote])
 522     g.db.commit()
 523     return redirect(url_for('vote', idvote=idvote))
 524
 525 #-------------
 526 # Votes admin
 527
 528 @app.route('/admin/votes/list')
 529 def admin_votes():
 530     if not session.get('user').get('is_admin'):
 531         abort(401)
 532     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')
 533     return render_template('admin_votes.html', votes=votes, today=date.today().strftime("%Y-%m-%d"))
 534
 535 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 536 def admin_vote_add():
 537     if not session.get('user').get('is_admin'):
 538         abort(401)
 539     if request.method == 'POST':
 540         if request.form['title']:
 541             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 542                 date_begin = date.today()
 543                 date_end = date.today() + timedelta(days=int(request.form['days']))
 544                 transparent = 0
 545                 public = 0
 546                 multiplechoice = 0
 547                 if 'transparent' in request.form.keys():
 548                     transparent = 1
 549                 if 'public' in request.form.keys():
 550                     public = 1
 551                 if 'multiplechoice' in request.form.keys():
 552                     multiplechoice = 1
 553                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 554                 if group is None:
 555                     group[id] = 1
 556                 g.db.execute('insert into votes (title, description, category, date_begin, date_end, is_transparent, is_public, is_multiplechoice, id_group, id_author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 557                         [request.form['title'], request.form['description'], request.form['category'], date_begin, date_end, transparent, public, multiplechoice, group['id'], session['user']['id']])
 558                 g.db.commit()
 559                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc',
 560                         [request.form['title'], date_begin], one=True)
 561                 if vote is None:
 562                     flash(u'Une erreur est survenue !', 'error')
 563                     return redirect(url_for('home'))
 564                 else:
 565                     if request.form['pattern'] in PATTERNS.keys():
 566                         pattern = PATTERNS[request.form['pattern']]
 567                         for choice in pattern:
 568                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])
 569                             g.db.commit()
 570                     flash(u"Le vote a été créé", 'info')
 571                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 572             else:
 573                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 574         else:
 575             flash(u'Vous devez spécifier un titre.', 'error')
 576     groups = query_db('select * from groups')
 577     return render_template('admin_vote_new.html', groups=groups, patterns=PATTERNS)
 578
 579 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 580 def admin_vote_edit(voteid):
 581     if not session.get('user').get('is_admin'):
 582         abort(401)
 583     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 584     if vote is None:
 585         abort(404)
 586     if request.method == 'POST':
 587         if request.form['title']:
 588             if request.form['days'] > 0:
 589                 date_end = datetime.strptime(vote['date_begin'], "%Y-%m-%d") + timedelta(days=int(request.form['days']))
 590                 date_end = date_end.strftime("%Y-%m-%d")
 591             transparent = 0
 592             public = 0
 593             if 'transparent' in request.form.keys():
 594                 transparent = 1
 595             if 'public' in request.form.keys():
 596                 public = 1
 597             isopen = 0
 598             isterminated = 0
 599             if request.form['status'] == 'Ouvert':
 600                 choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 601                 if choices is not None and choices['nb'] >= 2:
 602                     isopen = 1
 603                     previousvote = query_db('select id, is_open, id_group from votes where id = ?', [voteid], one=True)
 604                     if previousvote is None or previousvote['is_open'] == 0:
 605                         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']])
 606                         for user in users_to_vote:
 607                             link = request.url_root + url_for('vote', idvote=voteid)
 608                             BODY = string.join((
 609                                 "From: %s" % EMAIL,
 610                                 "To: %s" % user['email'],
 611                                 "Subject: [Cavote] %s" % gettext(u"A vote has been opened for your group"),
 612                                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 613                                 "Content-type: text/plain; charset=utf-8",
 614                                 "X-Mailer: %s" % VERSION,
 615                                 "",
 616                                 "%(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']},
 617                                 "",
 618                                 gettext(u"This link will bring you to the form where you will be able to vote :"),
 619                                 link,
 620                                 "",
 621                                 gettext(u"If you think this mail is not for you, please ignore and delete it.")
 622                                 ), "\r\n")
 623                             server = smtplib.SMTP(SMTP_SERVER)
 624                             server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))
 625                             server.quit()
 626                 else:
 627                     flash(u'Vous devez proposer au moins deux choix pour ouvrir le vote.', 'error')
 628             elif request.form['status'] == u'Terminé':
 629                 isterminated = 1
 630                 if vote['is_open']:
 631                     isopen = 1
 632             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])
 633             g.db.commit()
 634             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 635             flash(u"Le vote a bien été mis à jour.", "success")
 636         else:
 637             flash(u'Vous devez spécifier un titre.', 'error')
 638     vote['duration'] = (datetime.strptime(vote['date_end'], "%Y-%m-%d") - datetime.strptime(vote['date_begin'], "%Y-%m-%d")).days
 639     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 640     choices = query_db('select * from choices where id_vote = ?', [voteid])
 641     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 642     if date.today().strftime("%Y-%m-%d") > vote['date_end']:
 643         flash(u'La deadline du vote est expirée, vous devriez terminer le vote.')
 644     return render_template('admin_vote_edit.html', vote=vote, group=group, choices=choices, attachments=attachments)
 645
 646 @app.route('/admin/votes/delete/<idvote>')
 647 def admin_vote_del(idvote):
 648     if not session.get('user').get('is_admin'):
 649         abort(401)
 650     vote = query_db('select * from votes where id = ?', [idvote], one=True)
 651     if vote is None:
 652         abort(404)
 653     g.db.execute('update votes set is_hidden=1 where id = ?', [idvote])
 654     g.db.commit()
 655     return redirect(url_for('admin_votes'))
 656
 657 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 658 def admin_vote_addchoice(voteid):
 659     if not session.get('user').get('is_admin'):
 660         abort(401)
 661     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 662     if vote is None:
 663         abort(404)
 664     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 665     g.db.commit()
 666     return redirect(url_for('admin_vote_edit', voteid=voteid))
 667
 668 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 669 def admin_vote_editchoice(voteid, choiceid):
 670     if not session.get('user').get('is_admin'):
 671         abort(401)
 672     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 673     if choice is None:
 674         abort(404)
 675     if request.method == 'POST':
 676         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 677         g.db.commit()
 678     return redirect(url_for('admin_vote_edit', voteid=voteid))
 679
 680 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 681 def admin_vote_deletechoice(voteid, choiceid):
 682     if not session.get('user').get('is_admin'):
 683         abort(401)
 684     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 685     if choice is None:
 686         abort(404)
 687     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 688     g.db.commit()
 689     choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 690     if choices is None or choices['nb'] < 2:
 691         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 692         g.db.commit()
 693         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 694     return redirect(url_for('admin_vote_edit', voteid=voteid))
 695
 696 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 697 def admin_vote_addattachment(voteid):
 698     if not session.get('user').get('is_admin'):
 699         abort(401)
 700     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 701     if vote is None:
 702         abort(404)
 703     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 704     g.db.commit()
 705     return redirect(url_for('admin_vote_edit', voteid=voteid))
 706
 707 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 708 def admin_vote_deleteattachment(voteid, attachmentid):
 709     if not session.get('user').get('is_admin'):
 710         abort(401)
 711     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 712     if attachment is None:
 713         abort(404)
 714     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 715     g.db.commit()
 716     return redirect(url_for('admin_vote_edit', voteid=voteid))
 717
 718 #------
 719 # Main
 720
 721 if __name__ == '__main__':
 722     app.run()