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 import sqlite3
   7 from datetime import date, time, timedelta
   8 import time
   9 from contextlib import closing
  10 import locale
  11 locale.setlocale(locale.LC_ALL, '')
  12 import os
  13 import hashlib
  14 import smtplib
  15 import string
  16
  17 DATABASE = '/tmp/cavote.db'
  18 SECRET_KEY = '{J@uRKO,xO-PK7B,jF?>iHbxLasF9s#zjOoy=+:'
  19 DEBUG = True
  20 TITLE = u"Cavote FFDN"
  21 EMAIL = '"' + TITLE + '"' + ' <' + u"cavote@ffdn.org" + '>'
  22 BASEURL = "http://localhost:5000"
  23 VERSION = "cavote 0.0.1"
  24 SMTP_SERVER = "10.33.33.30"
  25
  26 app = Flask(__name__)
  27 app.config.from_object(__name__)
  28
  29 def connect_db():
  30     return sqlite3.connect(app.config['DATABASE'])
  31
  32 @app.before_request
  33 def before_request():
  34     g.db = connect_db()
  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(username, password):
  60     return query_db('select * from users where email = ? and password = ?', [username, crypt(password)], one=True)
  61
  62 def connect_user(user):
  63     session['user'] = user
  64     del session['user']['password']
  65     del session['user']['key']
  66
  67 def disconnect_user():
  68     session.pop('user', None)
  69
  70 def crypt(passwd):
  71     return hashlib.sha1(passwd).hexdigest()
  72
  73 def keygen():
  74     return hashlib.sha1(os.urandom(24)).hexdigest()
  75
  76 def get_userid():
  77     user = session.get('user')
  78     if user is None:
  79         return -1
  80     elif user.get('id') < 0:
  81         return -1
  82     else:
  83         return user.get('id')
  84
  85 @app.route('/login', methods=['GET', 'POST'])
  86 def login():
  87     if request.method == 'POST':
  88         user = valid_login(request.form['username'], request.form['password'])
  89         if user is None:
  90             flash('Invalid username/password', 'error')
  91         else:
  92             connect_user(user)
  93             flash('You were logged in', 'success')
  94             return redirect(url_for('home'))
  95     return render_template('login.html')
  96
  97 @app.route('/logout')
  98 def logout():
  99     disconnect_user()
 100     flash('You were logged out', 'info')
 101     return redirect(url_for('home'))
 102
 103 #-----------------
 104 # Change password
 105
 106 @app.route('/password/lost', methods=['GET', 'POST'])
 107 def password_lost():
 108     info = None
 109     if request.method == 'POST':
 110         user = query_db('select * from users where email = ?', [request.form['email']], one=True)
 111         if user is None:
 112             flash('Cet utilisateur n\'existe pas !', 'error')
 113         else:
 114             key = keygen()
 115             g.db.execute('update users set key = ? where id = ?', [key, user['id']])
 116             g.db.commit()
 117             link = BASEURL + url_for('login_key', userid=user['id'], key=key)
 118             BODY = string.join((
 119                 "From: %s" % EMAIL,
 120                 "To: %s" % user['email'],
 121                 "Subject: [Cavote] Password lost",
 122                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()),
 123                 "X-Mailer: %s" % VERSION,
 124                 "",
 125                 "You have lost your password.",
 126                 "This link will log you without password.",
 127                 "Don't forget to define a new one as soon a possible!",
 128                 "This link will only work one time.",
 129                 "",
 130                 link,
 131                 "",
 132                 "If you think this mail is not for you, please ignore and delete it."
 133                 ), "\r\n")
 134             server = smtplib.SMTP(SMTP_SERVER)
 135             server.sendmail(EMAIL, [user['email']], BODY)
 136             server.quit()
 137             flash(u"Un mail a été envoyé à " + user['email'], 'info')
 138     return render_template('password_lost.html')
 139
 140 @app.route('/login/<userid>/<key>')
 141 def login_key(userid, key):
 142     user = query_db('select * from users where id = ? and key = ?', [userid, key], one=True)
 143     if user is None or user['key'] == "invalid":
 144         abort(404)
 145     else:
 146         connect_user(user)
 147         g.db.execute('update users set key = "invalid" where id = ?', [user['id']])
 148         g.db.commit()
 149         flash(u"Veuillez mettre à jour votre mot de passe", 'info')
 150         return redirect(url_for('user_password', userid=user['id']))
 151
 152 #---------------
 153 # User settings
 154
 155 @app.route('/user/<userid>')
 156 def user(userid):
 157     if int(userid) != get_userid():
 158         abort(401)
 159     groups = query_db('select * from groups join user_group on id=id_group where id_user = ?', userid)
 160     return render_template('user.html', groups=groups)
 161
 162 @app.route('/user/settings/<userid>', methods=['GET', 'POST'])
 163 def user_edit(userid):
 164     if int(userid) != get_userid():
 165         abort(401)
 166     if request.method == 'POST':
 167         if query_db('select * from users where email=? and id!=?', [request.form['email'], userid], one=True) is None:
 168             if query_db('select * from users where name=? and id!=?', [request.form['name'], userid], one=True) is None:
 169                 g.db.execute('update users set email = ?, name = ?, organization = ? where id = ?',
 170                         [request.form['email'], request.form['name'], request.form['organization'], session['user']['id']])
 171                 g.db.commit()
 172                 disconnect_user()
 173                 user = query_db('select * from users where id=?', [userid], one=True)
 174                 if user is None:
 175                     flash(u'Une erreur s\'est produite.', 'error')
 176                     return redirect(url_for('login'))
 177                 connect_user(user)
 178                 flash(u'Votre profil a été mis à jour !', 'success')
 179             else:
 180                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
 181         else:
 182             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
 183     return render_template('user_edit.html')
 184
 185 @app.route('/user/password/<userid>', methods=['GET', 'POST'])
 186 def user_password(userid):
 187     if int(userid) != get_userid():
 188         abort(401)
 189     if request.method == 'POST':
 190         if request.form['password'] == request.form['password2']:
 191             g.db.execute('update users set password = ? where id = ?', [crypt(request.form['password']), session['user']['id']])
 192             g.db.commit()
 193             flash(u'Votre mot de passe a été mis à jour.', 'success')
 194         else:
 195             flash(u'Les mots de passe sont différents.', 'error')
 196     return render_template('user_edit.html')
 197
 198 #------------
 199 # User admin
 200
 201 @app.route('/admin/users')
 202 def admin_users():
 203     if not session.get('user').get('is_admin'):
 204         abort(401)
 205     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')
 206     users = dict()
 207     for t in tuples:
 208         if t['userid'] in users:
 209             users[t['userid']]['groups'].append(t["groupname"])
 210         else:
 211             users[t['userid']] = dict()
 212             users[t['userid']]['userid'] = t['userid']
 213             users[t['userid']]['email'] = t['email']
 214             users[t['userid']]['username'] = t['username']
 215             users[t['userid']]['is_admin'] = t['is_admin']
 216             users[t['userid']]['groups'] = [t['groupname']]
 217
 218     return render_template('admin_users.html', users=users.values())
 219
 220 @app.route('/admin/users/add', methods=['GET', 'POST'])
 221 def admin_user_add():
 222     if not session.get('user').get('is_admin'):
 223         abort(401)
 224     if request.method == 'POST':
 225         if request.form['email']:
 226             # :TODO:maethor:120528: Check fields
 227             password = "toto" # :TODO:maethor:120528: Generate password
 228             admin = 0
 229             if 'admin' in request.form.keys():
 230                 admin = 1
 231             g.db.execute('insert into users (email, name, organization, password, is_admin, key) values (?, ?, ?, ?, ?, "invalid")',
 232                     [request.form['email'], request.form['username'], request.form['organization'], password, admin])
 233             g.db.commit()
 234             user = query_db('select * from users where email = ?', [request.form["email"]], one=True)
 235             if user:
 236               for group in request.form.getlist('groups'):
 237                   if query_db('select id from groups where id = ?', group, one=True) is None:
 238                       abort(401)
 239                   g.db.execute('insert into user_group values (?, ?)', [user['id'], group])
 240                   g.db.commit()
 241               # :TODO:maethor:120528: Send mail
 242               flash(u'Le nouvel utilisateur a été créé avec succès', 'success')
 243               return redirect(url_for('admin_users'))
 244             else:
 245                 flash(u'Une erreur s\'est produite.', 'error')
 246         else:
 247             flash(u"Vous devez spécifier une adresse email.", 'error')
 248     groups = query_db('select * from groups where system=0')
 249     return render_template('admin_user_new.html', groups=groups)
 250
 251 #-------------
 252 # Roles admin
 253
 254 @app.route('/admin/groups')
 255 def admin_groups():
 256     if not session.get('user').get('is_admin'):
 257         abort(401)
 258     groups = query_db('select * from groups')
 259     return render_template('admin_groups.html', groups=groups)
 260
 261 @app.route('/admin/groups/add', methods=['POST'])
 262 def admin_group_add():
 263     if not session.get('user').get('is_admin'):
 264         abort(401)
 265     if request.method == 'POST':
 266         if request.form['name']:
 267             g.db.execute('insert into groups (name) values (?)', [request.form['name']])
 268             g.db.commit()
 269         else:
 270             flash(u"Vous devez spécifier un nom.", "error")
 271     return redirect(url_for('admin_groups'))
 272
 273 @app.route('/admin/groups/delete/<idgroup>')
 274 def admin_group_del(idgroup):
 275     if not session.get('user').get('is_admin'):
 276         abort(401)
 277     group = query_db('select * from groups where id = ?', [idgroup], one=True)
 278     if group is None:
 279         abort(404)
 280     if group['system']:
 281         abort(401)
 282     g.db.execute('delete from groups where id = ?', [idgroup])
 283     g.db.commit()
 284     return redirect(url_for('admin_groups'))
 285
 286 #------------
 287 # Votes list
 288
 289 @app.route('/votes/<votes>')
 290 def votes(votes):
 291     today = date.today()
 292     active_button = votes
 293     max_votes ='select id_group, count(*) as max_votes from user_group group by id_group'
 294     basequery = 'select votes.*, max_votes from votes join (' + max_votes + ') as max_votes on votes.id_group = max_votes.id_group'
 295     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'
 296     basequery = 'select * from (' + basequery + ') left join (' + nb_votes + ') on id = id_vote'
 297     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'
 298     if votes == 'all':
 299         votes = query_db(basequery + ' order by id desc')
 300     elif votes == 'archive':
 301         votes = query_db(basequery + ' and date_end < (?) order by id desc', [today])
 302     elif votes == 'current':
 303         votes = query_db(basequery + ' and date_end >= (?) order by id desc', [today])
 304     else:
 305         abort(404)
 306     for vote in votes:
 307         if not vote.get('nb_votes'):
 308             vote['nb_votes'] = 0
 309         vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 310     return render_template('votes.html', votes=votes, active_button=active_button)
 311
 312 #------
 313 # Vote
 314
 315 def can_see_vote(idvote, iduser=-1):
 316     vote = query_db('select * from votes where id=?', [idvote], one=True)
 317     if vote is None:
 318         return False
 319     if not vote['is_public']:
 320         user = query_db('select * from users where id=?', [iduser], one=True)
 321         if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id']], one=True) is None:
 322             return False
 323     return True
 324
 325
 326
 327 def can_vote(idvote, iduser=-1):
 328     vote = query_db('select * from votes where id=?', [idvote], one=True)
 329     if vote is None:
 330         return False
 331     if iduser > 0:
 332         if can_see_vote(idvote, iduser):
 333             if not has_voted(idvote, iduser):
 334                 if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id']], one=True):
 335                     return True
 336     return False
 337
 338 def has_voted(idvote, iduser=-1):
 339     vote = query_db('select * from user_choice join choices on id_choice=choices.id where id_vote = ? and id_user = ?', [idvote, iduser], one=True)
 340     return (vote is not None)
 341
 342 @app.route('/vote/<idvote>', methods=['GET', 'POST'])
 343 def vote(idvote):
 344     vote = query_db('select votes.*, groups.name as groupname from votes join groups on groups.id=votes.id_group where votes.id=?', [idvote], one=True)
 345     if vote is None:
 346         abort(404)
 347     if can_see_vote(idvote, get_userid()):
 348         if request.method == 'POST':
 349             if can_vote(idvote, get_userid()):
 350                 choices = query_db('select name, id from choices where id_vote=?', [idvote])
 351                 for choice in choices:
 352                     if str(choice['id']) in request.form.keys():
 353                         g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 354                                 [session.get('user').get('id'), choice['id']])
 355                         g.db.commit()
 356                         if vote['is_multiplechoice'] == 0:
 357                             break
 358             else:
 359                 abort(401)
 360         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])
 361         users = dict()
 362         for t in tuples:
 363             if t['userid'] in users:
 364                 users[t['userid']]['choices'].append(t['choiceid'])
 365             else:
 366                 users[t['userid']] = dict()
 367                 users[t['userid']]['userid'] = t['userid']
 368                 users[t['userid']]['username'] = t['username']
 369                 users[t['userid']]['choices'] = [t['choiceid']]
 370         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])
 371         attachments = query_db('select * from attachments where id_vote=?', [idvote])
 372         tmp = query_db('select id_group, count(*) as nb from user_group where id_group = ? group by id_group', [vote['id_group']], one=True)
 373         if tmp is None:
 374             vote['percent'] = 0
 375         else:
 376             vote['max_votes'] = tmp['nb']
 377             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)
 378             if tmp is None:
 379                 vote['percent'] = 0
 380             else:
 381                 vote['nb_votes'] = tmp['nb']
 382                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
 383         return render_template('vote.html', vote=vote, attachments=attachments, choices=choices, users=users.values(), can_vote=can_vote(idvote, get_userid()))
 384     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
 385     return redirect(url_for('home'))
 386
 387 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 388 def vote_deletechoices(idvote, iduser):
 389     if int(iduser) != get_userid():
 390         abort(401)
 391     g.db.execute('delete from user_choice where id_user = ? and id_choice in (select id from choices where id_vote = ?)',
 392         [iduser, idvote])
 393     g.db.commit()
 394     return redirect(url_for('vote', idvote=idvote))
 395
 396 #-------------
 397 # Votes admin
 398
 399 @app.route('/admin/votes/list')
 400 def admin_votes():
 401     if not session.get('user').get('is_admin'):
 402         abort(401)
 403     votes = query_db('select *, votes.id as voteid, groups.name as groupname from votes join groups on groups.id=votes.id_group order by id desc')
 404     return render_template('admin_votes.html', votes=votes)
 405
 406 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 407 def admin_vote_add():
 408     if not session.get('user').get('is_admin'):
 409         abort(401)
 410     if request.method == 'POST':
 411         if request.form['title']:
 412             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 413                 date_begin = date.today()
 414                 date_end = date.today() + timedelta(days=int(request.form['days']))
 415                 transparent = 0
 416                 public = 0
 417                 multiplechoice = 0
 418                 if 'transparent' in request.form.keys():
 419                     transparent = 1
 420                 if 'public' in request.form.keys():
 421                     public = 1
 422                 if 'multiplechoice' in request.form.keys():
 423                     multiplechoice = 1
 424                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 425                 if group is None:
 426                     group[id] = 1
 427                 g.db.execute('insert into votes (title, description, category, date_begin, date_end, is_transparent, is_public, is_multiplechoice, id_group, id_author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 428                         [request.form['title'], request.form['description'], request.form['category'], date_begin, date_end, transparent, public, multiplechoice, group['id'], session['user']['id']])
 429                 g.db.commit()
 430                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc',
 431                         [request.form['title'], date_begin], one=True)
 432                 if vote is None:
 433                     flash(u'Une erreur est survenue !', 'error')
 434                     return redirect(url_for('home'))
 435                 else:
 436                     flash(u"Le vote a été créé", 'info')
 437                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 438             else:
 439                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 440         else:
 441             flash(u'Vous devez spécifier un titre.', 'error')
 442     groups = query_db('select * from groups')
 443     return render_template('admin_vote_new.html', groups=groups)
 444
 445 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 446 def admin_vote_edit(voteid):
 447     if not session.get('user').get('is_admin'):
 448         abort(401)
 449     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 450     if vote is None:
 451         abort(404)
 452     if request.method == 'POST':
 453         if request.form['title']:
 454             # :TODO:maethor:120529: Calculer date_begin pour pouvoir y ajouter duration et obtenir date_end
 455             transparent = 0
 456             public = 0
 457             if 'transparent' in request.form.keys():
 458                 transparent = 1
 459             if 'public' in request.form.keys():
 460                 public = 1
 461             isopen = 0
 462             if request.form['status'] == 'Ouvert':
 463                 choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 464                 if choices is not None and choices['nb'] >= 2:
 465                     isopen = 1
 466                 else:
 467                     flash(u'Vous devez proposer au moins deux choix pour ouvrir le vote.', 'error')
 468             g.db.execute('update votes set title = ?, description = ?, category = ?, is_transparent = ?, is_public = ?, is_open = ? where id = ?',
 469                     [request.form['title'], request.form['description'], request.form['category'], transparent, public, isopen, voteid])
 470             g.db.commit()
 471             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 472             flash(u"Le vote a bien été mis à jour.", "success")
 473         else:
 474             flash(u'Vous devez spécifier un titre.', 'error')
 475
 476     # :TODO:maethor:120529: Calculer la durée du vote (différence date_end - date_begin)
 477     vote['duration'] = 15
 478     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 479     choices = query_db('select * from choices where id_vote = ?', [voteid])
 480     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 481     return render_template('admin_vote_edit.html', vote=vote, group=group, choices=choices, attachments=attachments)
 482
 483 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 484 def admin_vote_addchoice(voteid):
 485     if not session.get('user').get('is_admin'):
 486         abort(401)
 487     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 488     if vote is None:
 489         abort(404)
 490     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 491     g.db.commit()
 492     return redirect(url_for('admin_vote_edit', voteid=voteid))
 493
 494 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 495 def admin_vote_editchoice(voteid, choiceid):
 496     if not session.get('user').get('is_admin'):
 497         abort(401)
 498     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 499     if choice is None:
 500         abort(404)
 501     if request.method == 'POST':
 502         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 503         g.db.commit()
 504     return redirect(url_for('admin_vote_edit', voteid=voteid))
 505
 506 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 507 def admin_vote_deletechoice(voteid, choiceid):
 508     if not session.get('user').get('is_admin'):
 509         abort(401)
 510     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 511     if choice is None:
 512         abort(404)
 513     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 514     g.db.commit()
 515     choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 516     if choices is None or choices['nb'] < 2:
 517         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 518         g.db.commit()
 519         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 520     return redirect(url_for('admin_vote_edit', voteid=voteid))
 521
 522 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 523 def admin_vote_addattachment(voteid):
 524     if not session.get('user').get('is_admin'):
 525         abort(401)
 526     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 527     if vote is None:
 528         abort(404)
 529     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 530     g.db.commit()
 531     return redirect(url_for('admin_vote_edit', voteid=voteid))
 532
 533 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 534 def admin_vote_deleteattachment(voteid, attachmentid):
 535     if not session.get('user').get('is_admin'):
 536         abort(401)
 537     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 538     if attachment is None:
 539         abort(404)
 540     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 541     g.db.commit()
 542     return redirect(url_for('admin_vote_edit', voteid=voteid))
 543
 544 #------
 545 # Main
 546
 547 if __name__ == '__main__':
 548     app.run()
 549