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                 "X-Mailer: %s" % VERSION,
 154                 "",
 155                 gettext(u"It seems that you have lost your password."),
 156                 gettext(u"This link will log you without password."),
 157                 gettext(u"Don't forget to define a new one as soon a possible!"),
 158                 gettext(u"This link will only work one time."),
 159                 "",
 160                 link,
 161                 "",
 162                 gettext(u"If you think this mail is not for you, please ignore and delete it.")
 163                 ), "\r\n")
 164             server = smtplib.SMTP(SMTP_SERVER)
 165             server.sendmail(EMAIL, [user['email']], BODY)
 166             server.quit()
 167             flash(u"Un mail a été envoyé à " + user['email'], 'info')
 168     return render_template('password_lost.html')
 169
 170 @app.route('/login/<userid>/<key>')
 171 def login_key(userid, key):
 172     user = query_db('select * from users where id = ? and key = ?', [userid, key], one=True)
 173     if user is None or user['key'][0] != "v":
 174         abort(404)
 175     else:
 176         connect_user(user)
 177         flash(u"Veuillez mettre à jour votre mot de passe", 'info')
 178         return redirect(url_for('user_password', userid=user['id']))
 179
 180 #---------------
 181 # User settings
 182
 183 @app.route('/user/<userid>')
 184 def user(userid):
 185     if int(userid) != get_userid():
 186         abort(401)
 187     groups = query_db('select * from groups join user_group on id=id_group where id_user = ?', (userid,))
 188     return render_template('user.html', groups=groups)
 189
 190 @app.route('/user/settings/<userid>', methods=['GET', 'POST'])
 191 def user_edit(userid):
 192     if int(userid) != get_userid():
 193         abort(401)
 194     if request.method == 'POST':
 195         if query_db('select * from users where email=? and id!=?', [request.form['email'], userid], one=True) is None:
 196             if query_db('select * from users where name=? and id!=?', [request.form['name'], userid], one=True) is None:
 197                 g.db.execute('update users set email = ?, openid = ?, name = ?, organization = ? where id = ?',
 198                         [request.form['email'], request.form['openid'], request.form['name'], request.form['organization'], session['user']['id']])
 199                 g.db.commit()
 200                 disconnect_user()
 201                 user = query_db('select * from users where id=?', [userid], one=True)
 202                 if user is None:
 203                     flash(u'Une erreur s\'est produite.', 'error')
 204                     return redirect(url_for('login'))
 205                 connect_user(user)
 206                 flash(u'Votre profil a été mis à jour !', 'success')
 207             else:
 208                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 209         else:
 210             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 211     return render_template('user_edit.html')
 212
 213 @app.route('/user/password/<userid>', methods=['GET', 'POST'])
 214 def user_password(userid):
 215     if int(userid) != get_userid():
 216         abort(401)
 217     if request.method == 'POST':
 218         if request.form['password'] == request.form['password2']:
 219             # new (invalid) key
 220             key = 'i%s' % keygen() # start with i: invalid key
 221             print "\n\nchange key for %s\n" % key # FIXME TMP
 222             g.db.execute('update users set password = ?, key = ? where id = ?', [crypt(request.form['password'], key), key, session['user']['id']])
 223             g.db.commit()
 224             flash(u'Votre mot de passe a été mis à jour.', 'success')
 225         else:
 226             flash(u'Les mots de passe sont différents.', 'error')
 227     return render_template('user_edit.html')
 228
 229 #------------
 230 # User admin
 231
 232 @app.route('/admin/users')
 233 def admin_users():
 234     if not session.get('user').get('is_admin'):
 235         abort(401)
 236     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')
 237     users = dict()
 238     for t in tuples:
 239         if t['userid'] in users:
 240             users[t['userid']]['groups'].append(t["groupname"])
 241         else:
 242             users[t['userid']] = dict()
 243             users[t['userid']]['userid'] = t['userid']
 244             users[t['userid']]['email'] = t['email']
 245             users[t['userid']]['username'] = t['username']
 246             users[t['userid']]['is_admin'] = t['is_admin']
 247             users[t['userid']]['groups'] = [t['groupname']]
 248
 249     return render_template('admin_users.html', users=users.values())
 250
 251 @app.route('/admin/users/add', methods=['GET', 'POST'])
 252 def admin_user_add():
 253     if not session.get('user').get('is_admin'):
 254         abort(401)
 255     if request.method == 'POST':
 256         if request.form['email']:
 257             if query_db('select * from users where email=?', [request.form['email']], one=True) is None:
 258                 if request.form['username']:
 259                     if query_db('select * from users where name=?', [request.form['username']], one=True) is None:
 260                         admin = 0
 261                         if 'admin' in request.form.keys():
 262                             admin = 1
 263                         key = 'v%s' % keygen()
 264                         g.db.execute('insert into users (email, openid, name, organization, password, is_admin, key) values (?, ?, ?, ?, ?, ?, ?)',
 265                                 [request.form['email'],
 266                                     request.form['openid'],
 267                                     request.form['username'],
 268                                     request.form['organization'],
 269                                     '', admin, key])
 270                         g.db.commit()
 271                         user = query_db('select * from users where email = ?', [request.form["email"]], one=True)
 272                         if user:
 273                             groups = request.form.getlist('groups')
 274                             groups.append('1')
 275                             for group in groups:
 276                                 if query_db('select id from groups where id = ?', group, one=True) is None:
 277                                     flash(u'Le groupe portant l\'id %s n\'existe pas.' % group, 'warning')
 278                                 else:
 279                                     g.db.execute('insert into user_group values (?, ?)', [user['id'], group])
 280                                     g.db.commit()
 281                             link = request.url_root + url_for('login_key', userid=user['id'], key=user['key'])
 282                             BODY = string.join((
 283                             "From: %s" % EMAIL,
 284                             "To: %s" % user['email'],
 285                             "Subject: [Cavote] %s" % gettext(u"Welcome"),
 286                             "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 287                             "X-Mailer: %s" % VERSION,
 288                             "",
 289                             "%(text)s %(user)s!" % {"text": gettext(u"Hi"), "user": user['name']},
 290                             "%(text2)s %(title)s." % {"text2": gettext(u"Welcome on"), "title": TITLE},
 291                             "%(text3)s %(email)s." % {"text3": gettext(u"Your account address is"), "email": user['email']},
 292                             "",
 293                             gettext(u"To log in for the first time and set your password, please follow this link :"),
 294                             link,
 295                             ""
 296                             ), "\r\n")
 297                             server = smtplib.SMTP(SMTP_SERVER)
 298                             server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))
 299                             server.quit()
 300                             flash(u'Le nouvel utilisateur a été créé avec succès', 'success')
 301                             return redirect(url_for('admin_users'))
 302                         else:
 303                             flash(u'Une erreur s\'est produite.', 'error')
 304                     else:
 305                         flash(u'Le nom ' + request.form['username'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 306                 else:
 307                     flash(u"Vous devez spécifier un nom d'utilisateur.", 'error')
 308             else:
 309                 flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 310         else:
 311             flash(u"Vous devez spécifier une adresse email.", 'error')
 312     groups = query_db('select * from groups where system=0')
 313     return render_template('admin_user_new.html', groups=groups)
 314
 315 @app.route('/admin/users/edit/<iduser>', methods=['GET', 'POST'])
 316 def admin_user_edit(iduser):
 317     if not session.get('user').get('is_admin'):
 318         abort(401)
 319     user = query_db('select * from users where id = ?', [iduser], one=True)
 320     user['groups'] = query_db('select groups.* from groups join user_group on groups.id = user_group.id_group where id_user = ?', [iduser])
 321     if user is None:
 322         abort(404)
 323     if request.method == 'POST':
 324         if query_db('select * from users where email=? and id!=?', [request.form['email'], iduser], one=True) is None:
 325             if query_db('select * from users where name=? and id!=?', [request.form['name'], iduser], one=True) is None:
 326                 admin = 0
 327                 if 'admin' in request.form.keys():
 328                     admin = 1
 329                 g.db.execute('update users set email = ?, name = ?, organization = ?, openid= ?, is_admin = ? where id = ?',
 330                         [request.form['email'], request.form['name'], request.form['organization'], request.form['openid'], admin, iduser])
 331                 g.db.commit()
 332                 groups = request.form.getlist('groups')
 333                 groups.append('1')
 334                 for group in user['groups']:
 335                     if not group['id'] in groups:
 336                         g.db.execute('delete from user_group where id_user = ? and id_group = ?', [iduser, group['id']])
 337                         g.db.commit()
 338                 for group in groups:
 339                     group = query_db('select id from groups where id = ?', group, one=True)
 340                     if group is None:
 341                         flash(u'Le groupe portant l\'id %s n\'existe pas.' % group, 'warning')
 342                     else:
 343                         if not group in user['groups']:
 344                             g.db.execute('insert into user_group values (?, ?)', [user['id'], group['id']])
 345                             g.db.commit()
 346                 user = query_db('select * from users where id = ?', [iduser], one=True)
 347                 user['groups'] = query_db('select groups.* from groups join user_group on groups.id = user_group.id_group where id_user = ?', [iduser])
 348                 flash(u'Le profil a été mis à jour !', 'success')
 349             else:
 350                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 351         else:
 352             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 353     groups = query_db('select * from groups where system=0')
 354     return render_template('admin_user_edit.html', user=user, groups=groups)
 355
 356 @app.route('/admin/users/delete/<iduser>')
 357 def admin_user_del(iduser):
 358     if not session.get('user').get('is_admin'):
 359         abort(401)
 360     user = query_db('select * from users where id = ?', [iduser], one=True)
 361     if user is None:
 362         abort(404)
 363     g.db.execute('delete from users where id = ?', [iduser])
 364     g.db.commit()
 365     return redirect(url_for('admin_users'))
 366
 367 #-------------
 368 # Roles admin
 369
 370 @app.route('/admin/groups')
 371 def admin_groups():
 372     if not session.get('user').get('is_admin'):
 373         abort(401)
 374     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')
 375     return render_template('admin_groups.html', groups=groups)
 376
 377 @app.route('/admin/groups/add', methods=['POST'])
 378 def admin_group_add():
 379     if not session.get('user').get('is_admin'):
 380         abort(401)
 381     if request.method == 'POST':
 382         if request.form['name']:
 383             g.db.execute('insert into groups (name) values (?)', [request.form['name']])
 384             g.db.commit()
 385         else:
 386             flash(u"Vous devez spécifier un nom.", "error")
 387     return redirect(url_for('admin_groups'))
 388
 389 @app.route('/admin/groups/delete/<idgroup>')
 390 def admin_group_del(idgroup):
 391     if not session.get('user').get('is_admin'):
 392         abort(401)
 393     group = query_db('select * from groups where id = ?', [idgroup], one=True)
 394     if group is None:
 395         abort(404)
 396     if group['system']:
 397         abort(401)
 398     g.db.execute('delete from groups where id = ?', [idgroup])
 399     g.db.commit()
 400     return redirect(url_for('admin_groups'))
 401
 402 #------------
 403 # Votes list
 404
 405 @app.route('/votes/<votes>')
 406 def votes(votes):
 407     today = date.today()
 408     active_button = votes
 409     max_votes ='select id_group, count(*) as max_votes from user_group group by id_group'
 410     basequery = 'select votes.*, max_votes from votes left join (' + max_votes + ') as max_votes on votes.id_group = max_votes.id_group'
 411     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'
 412     basequery = 'select * from (' + basequery + ') left join (' + nb_votes + ') on id = id_vote'
 413     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'
 414     if votes == 'all':
 415         votes = query_db(basequery + ' order by date_end')
 416     elif votes == 'archive':
 417         votes = query_db(basequery + ' and is_terminated=1 order by date_end desc')
 418     elif votes == 'current':
 419         votes = query_db(basequery + ' and is_terminated=0 order by date_end')
 420     elif votes == 'waiting':
 421         basequery = 'select votes.* from user_group join (' + basequery + ') as votes on votes.id_group = user_group.id_group where user_group.id_user = ?'
 422         already_voted = 'select id_vote from user_choice join choices on user_choice.id_choice = choices.id where id_user = ?'
 423         votes = query_db(basequery + ' and votes.id not in (' + already_voted + ') and is_terminated=0', [get_userid(), get_userid()])
 424     else:
 425         abort(404)
 426     for vote in votes:
 427         if not vote.get('nb_votes'):
 428             vote['nb_votes'] = 0
 429         if vote.get('max_votes'):
 430             vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 431     return render_template('votes.html', votes=votes, active_button=active_button)
 432
 433 #------
 434 # Vote
 435
 436 def can_see_vote(idvote, iduser=-1):
 437     vote = query_db('select * from votes where id=?', [idvote], one=True)
 438     if vote is None:
 439         return False
 440     if not vote['is_public']:
 441         user = query_db('select * from users where id=?', [iduser], one=True)
 442         if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id']], one=True) is None:
 443             return False
 444     return True
 445
 446 def can_vote(idvote, iduser=-1):
 447     vote = query_db('select * from votes where id=?', [idvote], one=True)
 448     if vote is None:
 449         return False
 450     if vote['is_terminated'] == 0:
 451         if iduser > 0:
 452             if can_see_vote(idvote, iduser):
 453                 if not has_voted(idvote, iduser):
 454                     if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id_group']], one=True):
 455                         return True
 456     return False
 457
 458 def has_voted(idvote, iduser=-1):
 459     vote = query_db('select * from user_choice join choices on id_choice=choices.id where id_vote = ? and id_user = ?', [idvote, iduser], one=True)
 460     return (vote is not None)
 461
 462 @app.route('/vote/<idvote>', methods=['GET', 'POST'])
 463 def vote(idvote):
 464     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)
 465     if vote is None:
 466         abort(404)
 467     if can_see_vote(idvote, get_userid()):
 468         if request.method == 'POST':
 469             if can_vote(idvote, get_userid()):
 470                 if vote['is_multiplechoice'] == 0:
 471                     if query_db('select * from choices where id = ?', [request.form['choice']], one=True) is not None:
 472                         g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 473                                 [session.get('user').get('id'), request.form['choice']])
 474                         g.db.commit()
 475                 else:
 476                     choices = query_db('select name, id from choices where id_vote=?', [idvote])
 477                     for choice in choices:
 478                         if str(choice['id']) in request.form.keys():
 479                             g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 480                                     [session.get('user').get('id'), choice['id']])
 481                             g.db.commit()
 482             else:
 483                 abort(401)
 484         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])
 485         users = dict()
 486         for t in tuples:
 487             if t['userid'] in users:
 488                 users[t['userid']]['choices'].append(t['choiceid'])
 489             else:
 490                 users[t['userid']] = dict()
 491                 users[t['userid']]['userid'] = t['userid']
 492                 users[t['userid']]['username'] = t['username']
 493                 users[t['userid']]['choices'] = [t['choiceid']]
 494         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])
 495         attachments = query_db('select * from attachments where id_vote=?', [idvote])
 496         tmp = query_db('select id_group, count(*) as nb from user_group where id_group = ? group by id_group', [vote['id_group']], one=True)
 497         if tmp is None:
 498             vote['percent'] = 0
 499         else:
 500             vote['max_votes'] = tmp['nb']
 501             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)
 502             if tmp is None:
 503                 vote['percent'] = 0
 504                 vote['nb_votes'] = 0
 505             else:
 506                 vote['nb_votes'] = tmp['nb']
 507                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 508         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']:
 509             flash(u'Ce vote vous concerne !', 'info')
 510         return render_template('vote.html', vote=vote, attachments=attachments, choices=choices, users=users.values(), can_vote=can_vote(idvote, get_userid()))
 511     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
 512     return redirect(url_for('home'))
 513
 514 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 515 def vote_deletechoices(idvote, iduser):
 516     if int(iduser) != get_userid():
 517         abort(401)
 518     g.db.execute('delete from user_choice where id_user = ? and id_choice in (select id from choices where id_vote = ?)',
 519         [iduser, idvote])
 520     g.db.commit()
 521     return redirect(url_for('vote', idvote=idvote))
 522
 523 #-------------
 524 # Votes admin
 525
 526 @app.route('/admin/votes/list')
 527 def admin_votes():
 528     if not session.get('user').get('is_admin'):
 529         abort(401)
 530     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')
 531     return render_template('admin_votes.html', votes=votes, today=date.today().strftime("%Y-%m-%d"))
 532
 533 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 534 def admin_vote_add():
 535     if not session.get('user').get('is_admin'):
 536         abort(401)
 537     if request.method == 'POST':
 538         if request.form['title']:
 539             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 540                 date_begin = date.today()
 541                 date_end = date.today() + timedelta(days=int(request.form['days']))
 542                 transparent = 0
 543                 public = 0
 544                 multiplechoice = 0
 545                 if 'transparent' in request.form.keys():
 546                     transparent = 1
 547                 if 'public' in request.form.keys():
 548                     public = 1
 549                 if 'multiplechoice' in request.form.keys():
 550                     multiplechoice = 1
 551                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 552                 if group is None:
 553                     group[id] = 1
 554                 g.db.execute('insert into votes (title, description, category, date_begin, date_end, is_transparent, is_public, is_multiplechoice, id_group, id_author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 555                         [request.form['title'], request.form['description'], request.form['category'], date_begin, date_end, transparent, public, multiplechoice, group['id'], session['user']['id']])
 556                 g.db.commit()
 557                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc',
 558                         [request.form['title'], date_begin], one=True)
 559                 if vote is None:
 560                     flash(u'Une erreur est survenue !', 'error')
 561                     return redirect(url_for('home'))
 562                 else:
 563                     if request.form['pattern'] in PATTERNS.keys():
 564                         pattern = PATTERNS[request.form['pattern']]
 565                         for choice in pattern:
 566                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])
 567                             g.db.commit()
 568                     flash(u"Le vote a été créé", 'info')
 569                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 570             else:
 571                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 572         else:
 573             flash(u'Vous devez spécifier un titre.', 'error')
 574     groups = query_db('select * from groups')
 575     return render_template('admin_vote_new.html', groups=groups, patterns=PATTERNS)
 576
 577 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 578 def admin_vote_edit(voteid):
 579     if not session.get('user').get('is_admin'):
 580         abort(401)
 581     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 582     if vote is None:
 583         abort(404)
 584     if request.method == 'POST':
 585         if request.form['title']:
 586             if request.form['days'] > 0:
 587                 date_end = datetime.strptime(vote['date_begin'], "%Y-%m-%d") + timedelta(days=int(request.form['days']))
 588                 date_end = date_end.strftime("%Y-%m-%d")
 589             transparent = 0
 590             public = 0
 591             if 'transparent' in request.form.keys():
 592                 transparent = 1
 593             if 'public' in request.form.keys():
 594                 public = 1
 595             isopen = 0
 596             isterminated = 0
 597             if request.form['status'] == 'Ouvert':
 598                 choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 599                 if choices is not None and choices['nb'] >= 2:
 600                     isopen = 1
 601                     previousvote = query_db('select id, is_open, id_group from votes where id = ?', [voteid], one=True)
 602                     if previousvote is None or previousvote['is_open'] == 0:
 603                         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']])
 604                         for user in users_to_vote:
 605                             link = request.url_root + url_for('vote', idvote=voteid)
 606                             BODY = string.join((
 607                                 "From: %s" % EMAIL,
 608                                 "To: %s" % user['email'],
 609                                 "Subject: [Cavote] %s" % gettext(u"A vote has been opened for your group"),
 610                                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
 611                                 "X-Mailer: %s" % VERSION,
 612                                 "",
 613                                 "%(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']},
 614                                 "",
 615                                 gettext(u"This link will bring you to the form where you will be able to vote :"),
 616                                 link,
 617                                 "",
 618                                 gettext(u"If you think this mail is not for you, please ignore and delete it.")
 619                                 ), "\r\n")
 620                             server = smtplib.SMTP(SMTP_SERVER)
 621                             server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))
 622                             server.quit()
 623                 else:
 624                     flash(u'Vous devez proposer au moins deux choix pour ouvrir le vote.', 'error')
 625             elif request.form['status'] == u'Terminé':
 626                 isterminated = 1
 627                 if vote['is_open']:
 628                     isopen = 1
 629             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])
 630             g.db.commit()
 631             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 632             flash(u"Le vote a bien été mis à jour.", "success")
 633         else:
 634             flash(u'Vous devez spécifier un titre.', 'error')
 635     vote['duration'] = (datetime.strptime(vote['date_end'], "%Y-%m-%d") - datetime.strptime(vote['date_begin'], "%Y-%m-%d")).days
 636     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 637     choices = query_db('select * from choices where id_vote = ?', [voteid])
 638     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 639     if date.today().strftime("%Y-%m-%d") > vote['date_end']:
 640         flash(u'La deadline du vote est expirée, vous devriez terminer le vote.')
 641     return render_template('admin_vote_edit.html', vote=vote, group=group, choices=choices, attachments=attachments)
 642
 643 @app.route('/admin/votes/delete/<idvote>')
 644 def admin_vote_del(idvote):
 645     if not session.get('user').get('is_admin'):
 646         abort(401)
 647     vote = query_db('select * from votes where id = ?', [idvote], one=True)
 648     if vote is None:
 649         abort(404)
 650     g.db.execute('update votes set is_hidden=1 where id = ?', [idvote])
 651     g.db.commit()
 652     return redirect(url_for('admin_votes'))
 653
 654 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 655 def admin_vote_addchoice(voteid):
 656     if not session.get('user').get('is_admin'):
 657         abort(401)
 658     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 659     if vote is None:
 660         abort(404)
 661     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 662     g.db.commit()
 663     return redirect(url_for('admin_vote_edit', voteid=voteid))
 664
 665 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 666 def admin_vote_editchoice(voteid, choiceid):
 667     if not session.get('user').get('is_admin'):
 668         abort(401)
 669     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 670     if choice is None:
 671         abort(404)
 672     if request.method == 'POST':
 673         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 674         g.db.commit()
 675     return redirect(url_for('admin_vote_edit', voteid=voteid))
 676
 677 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 678 def admin_vote_deletechoice(voteid, choiceid):
 679     if not session.get('user').get('is_admin'):
 680         abort(401)
 681     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 682     if choice is None:
 683         abort(404)
 684     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 685     g.db.commit()
 686     choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 687     if choices is None or choices['nb'] < 2:
 688         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 689         g.db.commit()
 690         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 691     return redirect(url_for('admin_vote_edit', voteid=voteid))
 692
 693 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 694 def admin_vote_addattachment(voteid):
 695     if not session.get('user').get('is_admin'):
 696         abort(401)
 697     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 698     if vote is None:
 699         abort(404)
 700     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 701     g.db.commit()
 702     return redirect(url_for('admin_vote_edit', voteid=voteid))
 703
 704 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 705 def admin_vote_deleteattachment(voteid, attachmentid):
 706     if not session.get('user').get('is_admin'):
 707         abort(401)
 708     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 709     if attachment is None:
 710         abort(404)
 711     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 712     g.db.commit()
 713     return redirect(url_for('admin_vote_edit', voteid=voteid))
 714
 715 #------
 716 # Main
 717
 718 if __name__ == '__main__':
 719     app.run()