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