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     basequery = 'select *, votes.id as voteid, groups.name as groupname from votes join groups on groups.id = id_group where is_open=1'
 294     if votes == 'all':
 295         votes = query_db(basequery + ' order by id desc')
 296     elif votes == 'archive':
 297         votes = query_db(basequery + ' and date_end < (?) order by id desc', [today])
 298     elif votes == 'current':
 299         votes = query_db(basequery + ' and date_end >= (?) order by id desc', [today])
 300     else:
 301         abort(404)
 302     return render_template('votes.html', votes=votes, active_button=active_button)
 303
 304 #------
 305 # Vote
 306
 307 def can_see_vote(idvote, iduser=-1):
 308     vote = query_db('select * from votes where id=?', [idvote], one=True)
 309     if vote is None:
 310         abort(404)
 311     if not vote['is_public']:
 312         user = query_db('select * from users where id=?', [iduser], one=True)
 313         if user is None: # :TODO:maethor:120604: Check others things (groups)
 314             return False
 315     return True
 316
 317 def can_vote(idvote, iduser=-1):
 318     if iduser > 0:
 319         if can_see_vote(idvote, iduser):
 320             if not has_voted(idvote, iduser):
 321                 return True # :TODO:maethor:120529: Check others things (groups)
 322     return False
 323
 324 def has_voted(idvote, iduser=-1):
 325     vote = query_db('select * from user_choice join choices on id_choice=choices.id where id_vote = ? and id_user = ?', [idvote, iduser], one=True)
 326     return (vote is not None)
 327
 328 @app.route('/vote/<idvote>', methods=['GET', 'POST'])
 329 def vote(idvote):
 330     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)
 331     if vote is None:
 332         abort(404)
 333     if can_see_vote(idvote, get_userid()):
 334         if request.method == 'POST':
 335             if can_vote(idvote, get_userid()):
 336                 choices = query_db('select name, id from choices where id_vote=?', [idvote])
 337                 for choice in choices:
 338                     if str(choice['id']) in request.form.keys():
 339                         g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)',
 340                                 [session.get('user').get('id'), choice['id']])
 341                         g.db.commit()
 342                         if vote['is_multiplechoice'] == 0:
 343                             break
 344             else:
 345                 abort(401)
 346         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])
 347         users = dict()
 348         for t in tuples:
 349             if t['userid'] in users:
 350                 users[t['userid']]['choices'].append(t['choiceid'])
 351             else:
 352                 users[t['userid']] = dict()
 353                 users[t['userid']]['userid'] = t['userid']
 354                 users[t['userid']]['username'] = t['username']
 355                 users[t['userid']]['choices'] = [t['choiceid']]
 356         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])
 357         attachments = query_db('select * from attachments where id_vote=?', [idvote])
 358         return render_template('vote.html', vote=vote, attachments=attachments, choices=choices, users=users.values(), can_vote=can_vote(idvote, get_userid()))
 359     flash('Vous n\'avez pas le droit de voir ce vote, désolé.')
 360     return(url_for('home'))
 361
 362 @app.route('/vote/deletechoices/<idvote>/<iduser>')
 363 def vote_deletechoices(idvote, iduser):
 364     if int(iduser) != get_userid():
 365         abort(401)
 366     g.db.execute('delete from user_choice where id_user = ? and id_choice in (select id from choices where id_vote = ?)',
 367         [iduser, idvote])
 368     g.db.commit()
 369     return redirect(url_for('vote', idvote=idvote))
 370
 371 #-------------
 372 # Votes admin
 373
 374 @app.route('/admin/votes/list')
 375 def admin_votes():
 376     if not session.get('user').get('is_admin'):
 377         abort(401)
 378     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')
 379     return render_template('admin_votes.html', votes=votes)
 380
 381 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 382 def admin_vote_add():
 383     if not session.get('user').get('is_admin'):
 384         abort(401)
 385     if request.method == 'POST':
 386         if request.form['title']:
 387             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
 388                 date_begin = date.today()
 389                 date_end = date.today() + timedelta(days=int(request.form['days']))
 390                 transparent = 0
 391                 public = 0
 392                 multiplechoice = 0
 393                 if 'transparent' in request.form.keys():
 394                     transparent = 1
 395                 if 'public' in request.form.keys():
 396                     public = 1
 397                 if 'multiplechoice' in request.form.keys():
 398                     multiplechoice = 1
 399                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
 400                 if group is None:
 401                     group[id] = 1
 402                 g.db.execute('insert into votes (title, description, category, date_begin, date_end, is_transparent, is_public, is_multiplechoice, id_group, id_author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
 403                         [request.form['title'], request.form['description'], request.form['category'], date_begin, date_end, transparent, public, multiplechoice, group['id'], session['user']['id']])
 404                 g.db.commit()
 405                 vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc',
 406                         [request.form['title'], date_begin], one=True)
 407                 if vote is None:
 408                     flash(u'Une erreur est survenue !', 'error')
 409                     return redirect(url_for('home'))
 410                 else:
 411                     flash(u"Le vote a été créé", 'info')
 412                     return redirect(url_for('admin_vote_edit', voteid=vote['id']))
 413             else:
 414                 flash(u'Le titre que vous avez choisi est déjà pris.', 'error')
 415         else:
 416             flash(u'Vous devez spécifier un titre.', 'error')
 417     groups = query_db('select * from groups')
 418     return render_template('admin_vote_new.html', groups=groups)
 419
 420 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 421 def admin_vote_edit(voteid):
 422     if not session.get('user').get('is_admin'):
 423         abort(401)
 424     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 425     if vote is None:
 426         abort(404)
 427     if request.method == 'POST':
 428         if request.form['title']:
 429             # :TODO:maethor:120529: Calculer date_begin pour pouvoir y ajouter duration et obtenir date_end
 430             transparent = 0
 431             public = 0
 432             if 'transparent' in request.form.keys():
 433                 transparent = 1
 434             if 'public' in request.form.keys():
 435                 public = 1
 436             isopen = 0
 437             if request.form['status'] == 'Ouvert':
 438                 choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 439                 if choices is not None and choices['nb'] >= 2:
 440                     isopen = 1
 441                 else:
 442                     flash(u'Vous devez proposer au moins deux choix pour ouvrir le vote.', 'error')
 443             g.db.execute('update votes set title = ?, description = ?, category = ?, is_transparent = ?, is_public = ?, is_open = ? where id = ?',
 444                     [request.form['title'], request.form['description'], request.form['category'], transparent, public, isopen, voteid])
 445             g.db.commit()
 446             vote = query_db('select * from votes where id = ?', [voteid], one=True)
 447             flash(u"Le vote a bien été mis à jour.", "success")
 448         else:
 449             flash(u'Vous devez spécifier un titre.', 'error')
 450
 451     # :TODO:maethor:120529: Calculer la durée du vote (différence date_end - date_begin)
 452     vote['duration'] = 15
 453     group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)
 454     choices = query_db('select * from choices where id_vote = ?', [voteid])
 455     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
 456     return render_template('admin_vote_edit.html', vote=vote, group=group, choices=choices, attachments=attachments)
 457
 458 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 459 def admin_vote_addchoice(voteid):
 460     if not session.get('user').get('is_admin'):
 461         abort(401)
 462     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 463     if vote is None:
 464         abort(404)
 465     g.db.execute('insert into choices (name, id_vote) values (?, ?)', [request.form['title'], voteid])
 466     g.db.commit()
 467     return redirect(url_for('admin_vote_edit', voteid=voteid))
 468
 469 @app.route('/admin/votes/editchoice/<voteid>/<choiceid>', methods=['POST', 'DELETE'])
 470 def admin_vote_editchoice(voteid, choiceid):
 471     if not session.get('user').get('is_admin'):
 472         abort(401)
 473     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 474     if choice is None:
 475         abort(404)
 476     if request.method == 'POST':
 477         g.db.execute('update choices set name=? where id = ? and id_vote = ?', [request.form['title'], choiceid, voteid])
 478         g.db.commit()
 479     return redirect(url_for('admin_vote_edit', voteid=voteid))
 480
 481 @app.route('/admin/votes/deletechoice/<voteid>/<choiceid>')
 482 def admin_vote_deletechoice(voteid, choiceid):
 483     if not session.get('user').get('is_admin'):
 484         abort(401)
 485     choice = query_db('select * from choices where id = ? and id_vote = ?', [choiceid, voteid], one=True)
 486     if choice is None:
 487         abort(404)
 488     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
 489     g.db.commit()
 490     choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
 491     if choices is None or choices['nb'] < 2:
 492         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
 493         g.db.commit()
 494         flash(u'Attention ! Il y a moins de deux choix. Le vote a été fermé.', 'error')
 495     return redirect(url_for('admin_vote_edit', voteid=voteid))
 496
 497 @app.route('/admin/votes/addattachment/<voteid>', methods=['POST'])
 498 def admin_vote_addattachment(voteid):
 499     if not session.get('user').get('is_admin'):
 500         abort(401)
 501     vote = query_db('select * from votes where id = ?', [voteid], one=True)
 502     if vote is None:
 503         abort(404)
 504     g.db.execute('insert into attachments (url, id_vote) values (?, ?)', [request.form['url'], voteid])
 505     g.db.commit()
 506     return redirect(url_for('admin_vote_edit', voteid=voteid))
 507
 508 @app.route('/admin/votes/deleteattachment/<voteid>/<attachmentid>')
 509 def admin_vote_deleteattachment(voteid, attachmentid):
 510     if not session.get('user').get('is_admin'):
 511         abort(401)
 512     attachment = query_db('select * from attachments where id = ? and id_vote = ?', [attachmentid, voteid], one=True)
 513     if attachment is None:
 514         abort(404)
 515     g.db.execute('delete from attachments where id = ? and id_vote = ?', [attachmentid, voteid])
 516     g.db.commit()
 517     return redirect(url_for('admin_vote_edit', voteid=voteid))
 518
 519 #------
 520 # Main
 521
 522 if __name__ == '__main__':
 523     app.run()
 524