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