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