fix after pybabel compile

[cavote.git] / main.py

diff --git a/main.py b/main.py
index 5cc7e32..7df6af3 100755 (executable)
--- a/main.py
+++ b/main.py
@@ -3,24 +3,34 @@
 
 from flask import Flask, request, session, g, redirect, url_for, abort, \
     render_template, flash
 
 from flask import Flask, request, session, g, redirect, url_for, abort, \
     render_template, flash

-from flaskext.openid import OpenID
+from gettext import translation
+import locale
+import os
+
+current_locale, encoding = locale.getdefaultlocale()
+locale_path = 'translations/'
+language = translation('messages', locale_path, [current_locale])
+gettext = language.ugettext
+
+#from flask_openid import OpenID
+#from flaskext.babel import Babel, gettext, ngettext

 import sqlite3
 from datetime import date, time, timedelta, datetime
 import time
 from contextlib import closing
 import sqlite3
 from datetime import date, time, timedelta, datetime
 import time
 from contextlib import closing

-import locale
-locale.setlocale(locale.LC_ALL, '')
-import os

 import hashlib
 import smtplib
 import string
 import hashlib
 import smtplib
 import string

+import re
+from collections import OrderedDict

 
 from settings import *
 
 app = Flask(__name__)
 app.config.from_object(__name__)
 
 
 from settings import *
 
 app = Flask(__name__)
 app.config.from_object(__name__)
 

-oid = OpenID(app)
+#oid = OpenID(app)
+#babel = Babel(app)

 
 def connect_db():
     return sqlite3.connect(app.config['DATABASE'])
 
 def connect_db():
     return sqlite3.connect(app.config['DATABASE'])


@@ -85,15 +95,15 @@ def keygen():
 
 def get_userid():
     user = session.get('user')
 
 def get_userid():
     user = session.get('user')

-    if user is None: 
+    if user is None:

         return -1
     elif user.get('id') < 0:
         return -1
     else:
         return user.get('id')
 
         return -1
     elif user.get('id') < 0:
         return -1
     else:
         return user.get('id')
 

+#@oid.loginhandler

 @app.route('/login', methods=['GET', 'POST'])
 @app.route('/login', methods=['GET', 'POST'])

-@oid.loginhandler

 def login():
     if request.method == 'POST':
         user = valid_login(request.form['username'], request.form['password'])
 def login():
     if request.method == 'POST':
         user = valid_login(request.form['username'], request.form['password'])


@@ -110,12 +120,12 @@ def login():
             return redirect(url_for('home'))
     return render_template('login.html')
 
             return redirect(url_for('home'))
     return render_template('login.html')
 

-@oid.after_login
+#@oid.after_login

 def create_or_login(resp):
     openid_url = resp.identity_url
     user = query_db('select * from users where openid = ?', [openid_url], one=True)
     if user is not None:
 def create_or_login(resp):
     openid_url = resp.identity_url
     user = query_db('select * from users where openid = ?', [openid_url], one=True)
     if user is not None:

-        flash(u'Successfully signed in')
+        flash(gettext(u'Successfully signed in'))

         connect_user(user)
         return redirect(oid.get_next_url())
     return redirect(url_for('home'))
         connect_user(user)
         return redirect(oid.get_next_url())
     return redirect(url_for('home'))


@@ -146,21 +156,22 @@ def password_lost():
             BODY = string.join((
                 "From: %s" % EMAIL,
                 "To: %s" % user['email'],
             BODY = string.join((
                 "From: %s" % EMAIL,
                 "To: %s" % user['email'],

-                "Subject: [Cavote] Password lost",
+                "Subject: [Cavote] %s" % gettext(u"Lost password"),

                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
                 "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),

+                "Content-type: text/plain; charset=utf-8",

                 "X-Mailer: %s" % VERSION,
                 "",
                 "X-Mailer: %s" % VERSION,
                 "",

-                "You have lost your password.",
-                "This link will log you without password.",
-                "Don't forget to define a new one as soon a possible!",
-                "This link will only work one time.",
+                gettext(u"It seems that you have lost your password."),
+                gettext(u"This link will log you without password."),
+                gettext(u"Don't forget to define a new one as soon a possible!"),
+                gettext(u"This link will only work one time."),

                 "",
                 link,
                 "",
                 "",
                 link,
                 "",

-                "If you think this mail is not for you, please ignore and delete it."
+                gettext(u"If you think this mail is not for you, please ignore and delete it.")

                 ), "\r\n")
             server = smtplib.SMTP(SMTP_SERVER)
                 ), "\r\n")
             server = smtplib.SMTP(SMTP_SERVER)

-            server.sendmail(EMAIL, [user['email']], BODY)
+            server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))

             server.quit()
             flash(u"Un mail a été envoyé à " + user['email'], 'info')
     return render_template('password_lost.html')
             server.quit()
             flash(u"Un mail a été envoyé à " + user['email'], 'info')
     return render_template('password_lost.html')


@@ -182,7 +193,7 @@ def login_key(userid, key):
 def user(userid):
     if int(userid) != get_userid():
         abort(401)
 def user(userid):
     if int(userid) != get_userid():
         abort(401)

-    groups = query_db('select * from groups join user_group on id=id_group where id_user = ?', userid)
+    groups = query_db('select * from groups join user_group on id=id_group where id_user = ?', (userid,))

     return render_template('user.html', groups=groups)
 
 @app.route('/user/settings/<userid>', methods=['GET', 'POST'])
     return render_template('user.html', groups=groups)
 
 @app.route('/user/settings/<userid>', methods=['GET', 'POST'])


@@ -217,9 +228,7 @@ def user_password(userid):
             # new (invalid) key
             key = 'i%s' % keygen() # start with i: invalid key
             print "\n\nchange key for %s\n" % key # FIXME TMP
             # new (invalid) key
             key = 'i%s' % keygen() # start with i: invalid key
             print "\n\nchange key for %s\n" % key # FIXME TMP

-            g.db.execute('update users set password = ?, key = ? where id = ?',
-                    [crypt(request.form['password'], key),
-                        key, session['user']['id']])
+            g.db.execute('update users set password = ?, key = ? where id = ?', [crypt(request.form['password'], key), key, session['user']['id']])

             g.db.commit()
             flash(u'Votre mot de passe a été mis à jour.', 'success')
         else:
             g.db.commit()
             flash(u'Votre mot de passe a été mis à jour.', 'success')
         else:


@@ -233,7 +242,10 @@ def user_password(userid):
 def admin_users():
     if not session.get('user').get('is_admin'):
         abort(401)
 def admin_users():
     if not session.get('user').get('is_admin'):
         abort(401)

-    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')
+    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')

     users = dict()
     for t in tuples:
         if t['userid'] in users:
     users = dict()
     for t in tuples:
         if t['userid'] in users:


@@ -245,7 +257,6 @@ def admin_users():
             users[t['userid']]['username'] = t['username']
             users[t['userid']]['is_admin'] = t['is_admin']
             users[t['userid']]['groups'] = [t['groupname']]
             users[t['userid']]['username'] = t['username']
             users[t['userid']]['is_admin'] = t['is_admin']
             users[t['userid']]['groups'] = [t['groupname']]

-

     return render_template('admin_users.html', users=users.values())
 
 @app.route('/admin/users/add', methods=['GET', 'POST'])
     return render_template('admin_users.html', users=users.values())
 
 @app.route('/admin/users/add', methods=['GET', 'POST'])


@@ -258,10 +269,11 @@ def admin_user_add():
                 if request.form['username']:
                     if query_db('select * from users where name=?', [request.form['username']], one=True) is None:
                         admin = 0
                 if request.form['username']:
                     if query_db('select * from users where name=?', [request.form['username']], one=True) is None:
                         admin = 0

-                        if 'admin' in request.form.keys():
+                        if 'admin' in request.form:

                             admin = 1
                         key = 'v%s' % keygen()
                             admin = 1
                         key = 'v%s' % keygen()

-                        g.db.execute('insert into users (email, openid, name, organization, password, is_admin, key) values (?, ?, ?, ?, ?, ?, ?)',
+                        g.db.execute('insert into users (email, openid, name, organization, password, is_admin, key) \
+                                      values (?, ?, ?, ?, ?, ?, ?)',

                                 [request.form['email'],
                                     request.form['openid'],
                                     request.form['username'],
                                 [request.form['email'],
                                     request.form['openid'],
                                     request.form['username'],


@@ -282,20 +294,21 @@ def admin_user_add():
                             BODY = string.join((
                             "From: %s" % EMAIL,
                             "To: %s" % user['email'],
                             BODY = string.join((
                             "From: %s" % EMAIL,
                             "To: %s" % user['email'],

-                            "Subject: [Cavote] Welcome",
+                            "Subject: [Cavote] %s" % gettext(u"Welcome"),

                             "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
                             "Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),

+                            "Content-type: text/plain; charset=utf-8",

                             "X-Mailer: %s" % VERSION,
                             "",
                             "X-Mailer: %s" % VERSION,
                             "",

-                            "Hi %s!" % user['name'],
-                            "Welcome on %s." % TITLE,
-                            "Your account's adresse is : %s." % user['email'],
+                            "%(text)s %(user)s!" % {"text": gettext(u"Hi"), "user": user['name']},
+                            "%(text2)s %(title)s." % {"text2": gettext(u"Welcome on"), "title": TITLE},
+                            "%(text3)s %(email)s." % {"text3": gettext(u"Your account address is"), "email": user['email']},

                             "",
                             "",

-                            "To log in for the first time and set your password, please follow this link :",
+                            gettext(u"To log in for the first time and set your password, please follow this link :"),

                             link,
                             ""
                             ), "\r\n")
                             server = smtplib.SMTP(SMTP_SERVER)
                             link,
                             ""
                             ), "\r\n")
                             server = smtplib.SMTP(SMTP_SERVER)

-                            server.sendmail(EMAIL, [user['email']], BODY)
+                            server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))

                             server.quit()
                             flash(u'Le nouvel utilisateur a été créé avec succès', 'success')
                             return redirect(url_for('admin_users'))
                             server.quit()
                             flash(u'Le nouvel utilisateur a été créé avec succès', 'success')
                             return redirect(url_for('admin_users'))


@@ -309,7 +322,7 @@ def admin_user_add():
                 flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
         else:
             flash(u"Vous devez spécifier une adresse email.", 'error')
                 flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
         else:
             flash(u"Vous devez spécifier une adresse email.", 'error')

-    groups = query_db('select * from groups where system=0') 
+    groups = query_db('select * from groups where system=0')

     return render_template('admin_user_new.html', groups=groups)
 
 @app.route('/admin/users/edit/<iduser>', methods=['GET', 'POST'])
     return render_template('admin_user_new.html', groups=groups)
 
 @app.route('/admin/users/edit/<iduser>', methods=['GET', 'POST'])


@@ -324,7 +337,7 @@ def admin_user_edit(iduser):
         if query_db('select * from users where email=? and id!=?', [request.form['email'], iduser], one=True) is None:
             if query_db('select * from users where name=? and id!=?', [request.form['name'], iduser], one=True) is None:
                 admin = 0
         if query_db('select * from users where email=? and id!=?', [request.form['email'], iduser], one=True) is None:
             if query_db('select * from users where name=? and id!=?', [request.form['name'], iduser], one=True) is None:
                 admin = 0

-                if 'admin' in request.form.keys():
+                if 'admin' in request.form:

                     admin = 1
                 g.db.execute('update users set email = ?, name = ?, organization = ?, openid= ?, is_admin = ? where id = ?',
                         [request.form['email'], request.form['name'], request.form['organization'], request.form['openid'], admin, iduser])
                     admin = 1
                 g.db.execute('update users set email = ?, name = ?, organization = ?, openid= ?, is_admin = ? where id = ?',
                         [request.form['email'], request.form['name'], request.form['organization'], request.form['openid'], admin, iduser])


@@ -336,7 +349,7 @@ def admin_user_edit(iduser):
                         g.db.execute('delete from user_group where id_user = ? and id_group = ?', [iduser, group['id']])
                         g.db.commit()
                 for group in groups:
                         g.db.execute('delete from user_group where id_user = ? and id_group = ?', [iduser, group['id']])
                         g.db.commit()
                 for group in groups:

-                    group = query_db('select id from groups where id = ?', group, one=True)
+                    group = query_db('select id from groups where id = ?', [group], one=True)

                     if group is None:
                         flash(u'Le groupe portant l\'id %s n\'existe pas.' % group, 'warning')
                     else:
                     if group is None:
                         flash(u'Le groupe portant l\'id %s n\'existe pas.' % group, 'warning')
                     else:


@@ -344,13 +357,15 @@ def admin_user_edit(iduser):
                             g.db.execute('insert into user_group values (?, ?)', [user['id'], group['id']])
                             g.db.commit()
                 user = query_db('select * from users where id = ?', [iduser], one=True)
                             g.db.execute('insert into user_group values (?, ?)', [user['id'], group['id']])
                             g.db.commit()
                 user = query_db('select * from users where id = ?', [iduser], one=True)

-                user['groups'] = query_db('select groups.* from groups join user_group on groups.id = user_group.id_group where id_user = ?', [iduser])
+                user['groups'] = query_db('select groups.* from groups \
+                                           join user_group on groups.id = user_group.id_group \
+                                           where id_user = ?', [iduser])

                 flash(u'Le profil a été mis à jour !', 'success')
             else:
                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
         else:
             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')
                 flash(u'Le profil a été mis à jour !', 'success')
             else:
                 flash(u'Le nom ' + request.form['name'] + u' est déjà pris ! Veuillez en choisir un autre.', 'error')
         else:
             flash(u'Il existe déjà un compte pour cette adresse e-mail : ' + request.form['email'], 'error')

-    groups = query_db('select * from groups where system=0') 
+    groups = query_db('select * from groups where system=0')

     return render_template('admin_user_edit.html', user=user, groups=groups)
 
 @app.route('/admin/users/delete/<iduser>')
     return render_template('admin_user_edit.html', user=user, groups=groups)
 
 @app.route('/admin/users/delete/<iduser>')


@@ -371,7 +386,11 @@ def admin_user_del(iduser):
 def admin_groups():
     if not session.get('user').get('is_admin'):
         abort(401)
 def admin_groups():
     if not session.get('user').get('is_admin'):
         abort(401)

-    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')
+    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')

     return render_template('admin_groups.html', groups=groups)
 
 @app.route('/admin/groups/add', methods=['POST'])
     return render_template('admin_groups.html', groups=groups)
 
 @app.route('/admin/groups/add', methods=['POST'])


@@ -406,26 +425,37 @@ def admin_group_del(idgroup):
 def votes(votes):
     today = date.today()
     active_button = votes
 def votes(votes):
     today = date.today()
     active_button = votes

-    max_votes ='select id_group, count(*) as max_votes from user_group group by id_group'
-    basequery = 'select votes.*, max_votes from votes left join (' + max_votes + ') as max_votes on votes.id_group = max_votes.id_group'
-    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'
-    basequery = 'select * from (' + basequery + ') left join (' + nb_votes + ') on id = id_vote'
-    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'
+    max_votes = 'select id_group, count(*) as max_votes from user_group group by id_group'
+    basequery = 'select votes.*, max_votes from votes \
+                 left join (' + max_votes + ') as max_votes on votes.id_group = max_votes.id_group'
+    nb_votes = 'select id_vote, count(*) as nb_votes \
+                from (select id_user, id_vote \
+                  from user_vote \
+                  group by id_user, id_vote) \
+                group by id_vote'
+    basequery = 'select * from (' + basequery + ') \
+                 left join (' + nb_votes + ') on id = id_vote'
+    basequery = 'select *, votes.id as voteid, groups.name as groupname from (' + basequery + ') as votes \
+                 join groups on groups.id = id_group \
+                 where is_hidden=0'

     if votes == 'all':
     if votes == 'all':

-        votes = query_db(basequery + ' order by date_end')
+        votes = query_db(basequery + ' and is_open=1 order by date_end')

     elif votes == 'archive':
         votes = query_db(basequery + ' and is_terminated=1 order by date_end desc')
     elif votes == 'current':
         votes = query_db(basequery + ' and is_terminated=0 order by date_end')
     elif votes == 'waiting':
     elif votes == 'archive':
         votes = query_db(basequery + ' and is_terminated=1 order by date_end desc')
     elif votes == 'current':
         votes = query_db(basequery + ' and is_terminated=0 order by date_end')
     elif votes == 'waiting':

-        basequery = 'select votes.* from user_group join (' + basequery + ') as votes on votes.id_group = user_group.id_group where user_group.id_user = ?'
-        already_voted = 'select id_vote from user_choice join choices on user_choice.id_choice = choices.id where id_user = ?'
+        basequery = 'select votes.* from user_group \
+                     join (' + basequery + ') as votes on votes.id_group = user_group.id_group \
+                     where user_group.id_user = ?'
+        already_voted = 'select id_vote from user_vote \
+                         where id_user = ?'

         votes = query_db(basequery + ' and votes.id not in (' + already_voted + ') and is_terminated=0', [get_userid(), get_userid()])
     else:
         abort(404)
     for vote in votes:
         if not vote.get('nb_votes'):
         votes = query_db(basequery + ' and votes.id not in (' + already_voted + ') and is_terminated=0', [get_userid(), get_userid()])
     else:
         abort(404)
     for vote in votes:
         if not vote.get('nb_votes'):

-            vote['nb_votes'] = 0 
+            vote['nb_votes'] = 0

         if vote.get('max_votes'):
             vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
     return render_template('votes.html', votes=votes, active_button=active_button)
         if vote.get('max_votes'):
             vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
     return render_template('votes.html', votes=votes, active_button=active_button)


@@ -448,66 +478,177 @@ def can_vote(idvote, iduser=-1):
     if vote is None:
         return False
     if vote['is_terminated'] == 0:
     if vote is None:
         return False
     if vote['is_terminated'] == 0:

-        if iduser > 0: 
-            if can_see_vote(idvote, iduser): 
+        if iduser > 0:
+            if can_see_vote(idvote, iduser):

                 if not has_voted(idvote, iduser):
                     if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id_group']], one=True):
                         return True
     return False
 
 def has_voted(idvote, iduser=-1):
                 if not has_voted(idvote, iduser):
                     if query_db('select * from user_group where id_user = ? and id_group = ?', [iduser, vote['id_group']], one=True):
                         return True
     return False
 
 def has_voted(idvote, iduser=-1):

-    vote = query_db('select * from user_choice join choices on id_choice=choices.id where id_vote = ? and id_user = ?', [idvote, iduser], one=True)
+    vote = query_db('select * from user_vote \
+                     where id_vote = ? and id_user = ?', [idvote, iduser], one=True)

     return (vote is not None)
 
 @app.route('/vote/<idvote>', methods=['GET', 'POST'])
 def vote(idvote):
     return (vote is not None)
 
 @app.route('/vote/<idvote>', methods=['GET', 'POST'])
 def vote(idvote):

-    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)
+    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)

     if vote is None:
         abort(404)
     if can_see_vote(idvote, get_userid()):
     if vote is None:
         abort(404)
     if can_see_vote(idvote, get_userid()):

+        choices = query_db('select name, id from choices where id_vote=?', [idvote])
+        values = query_db('select weight,name from values_ where id_cardinal=? order by weight ASC', [vote['id_cardinal']])
+        values.insert(0, {'weight':None,'name':u'=X̄ (Pas d’Opinion)'})
+        for v in values:
+            v['class'] = "value_" + re.sub('[^a-zA-Z0-9-]', '_', v['name'])
+        weights = OrderedDict([(v['weight'], v) for v in values])

         if request.method == 'POST':
             if can_vote(idvote, get_userid()):
         if request.method == 'POST':
             if can_vote(idvote, get_userid()):

-                if vote['is_multiplechoice'] == 0:
-                    if query_db('select * from choices where id = ?', [request.form['choice']], one=True) is not None:
-                        g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)', 
-                                [session.get('user').get('id'), request.form['choice']])
-                        g.db.commit()
-                else:
-                    choices = query_db('select name, id from choices where id_vote=?', [idvote])
-                    for choice in choices:
-                        if str(choice['id']) in request.form.keys():
-                            g.db.execute('insert into user_choice (id_user, id_choice) values (?, ?)', 
-                                    [session.get('user').get('id'), choice['id']])
-                            g.db.commit()
+                userid = session.get('user').get('id') if not vote['is_anonymous'] else None
+                # ACTION: store user's choices
+                for choice in choices:
+                    if choice['name'] is None:
+                        continue
+                    weight = request.form.get('value-'+str(choice['id']),None)
+                    if weight is not None and len(weight) is 0:
+                        weight = None
+                    if weight is not None:
+                        weight = int(weight)
+                    g.db.execute('insert into user_choice (id_user, id_choice, id_cardinal, weight) \
+                                  values (?, ?, ?, ?)', [userid, choice['id'], vote['id_cardinal'], weight])
+                    g.db.commit()
+                # ACTION: randomize storage when anonymous votes
+                if vote['is_anonymous']:
+                    g.db.execute('delete from user_choice_buffer_anonymous')
+                    g.db.execute('insert into user_choice_buffer_anonymous select * \
+                                  from user_choice where id_choice in (%s)'
+                                  % ','.join(['?'] * len(choices))
+                                , tuple(c['id'] for c in choices))
+                    g.db.execute('delete from user_choice where id_choice in (%s)'
+                                 % ','.join(['?'] * len(choices))
+                                , tuple(c['id'] for c in choices))
+                    g.db.execute('insert into user_choice select * \
+                                  from user_choice_buffer_anonymous \
+                                  order by random()')
+                    g.db.execute('delete from user_choice_buffer_anonymous')
+                    g.db.commit()
+                comment = request.form.get('comment', None)
+                g.db.execute('insert into user_vote (id_user, id_vote, comment) \
+                              values (?, ?, ?)'
+                            , [session.get('user').get('id'), vote['id'], comment])
+                g.db.commit()

             else:
                 abort(401)
             else:
                 abort(401)

-        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])
-        users = dict()
-        for t in tuples:
-            if t['userid'] in users:
-                users[t['userid']]['choices'].append(t['choiceid'])
-            else:
-                users[t['userid']] = dict()
-                users[t['userid']]['userid'] = t['userid']
-                users[t['userid']]['username'] = t['username']
-                users[t['userid']]['choices'] = [t['choiceid']]
-        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])
-        attachments = query_db('select * from attachments where id_vote=?', [idvote])
-        tmp = query_db('select id_group, count(*) as nb from user_group where id_group = ? group by id_group', [vote['id_group']], one=True)
-        if tmp is None:
+        # ACTION: count quorum numbers
+        max_votes = query_db('select id_group, count(*) as nb \
+                              from user_group where id_group = ? \
+                              group by id_group', [vote['id_group']], one=True)
+        if max_votes is None:

             vote['percent'] = 0
             vote['percent'] = 0

+            vote['nb_votes'] = 0

         else:
         else:

-            vote['max_votes'] = tmp['nb']
-            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)
-            if tmp is None:
+            vote['max_votes'] = max_votes['nb']
+            votes = query_db('select id_vote, count(*) as nb \
+                              from (select id_user, id_vote from user_vote \
+                                    group by id_user, id_vote) \
+                              where id_vote = ? group by id_vote', [idvote], one=True)
+            if votes is None:

                 vote['percent'] = 0
                 vote['nb_votes'] = 0
             else:
                 vote['percent'] = 0
                 vote['nb_votes'] = 0
             else:

-                vote['nb_votes'] = tmp['nb']
+                vote['nb_votes'] = votes['nb']

                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)
                 vote['percent'] = int((float(vote['nb_votes']) / float(vote['max_votes'])) * 100)

-        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']:
+        # ACTION: query users' choices joined with users' identity if not anonymous
+        user_choices = query_db('select user_choice.id_user as userid, users.name as username, \
+                                        choices.id as choiceid, choices.name as choice_name, \
+                                        user_choice.weight as weight, user_vote.comment as comment \
+                                 from choices \
+                                 join user_choice on choices.id = user_choice.id_choice \
+                                 left join users on userid = users.id \
+                                 left join user_vote on userid = user_vote.id_user and choices.id_vote = user_vote.id_vote \
+                                 where choices.id_vote = ? \
+                                 order by user_vote.date,choices.id', [idvote])
+        # ACTION: aggregate user choices per vote
+        vote['blank'] = 0
+        results = OrderedDict()
+        for c in choices:
+            choice_values = [(w, { 'nb':0
+                                 , 'idx':i
+                                 , 'name':weights[w]['name']
+                                 , 'class':weights[w]['class'] }
+                             ) for (i,w) in enumerate(weights, start=0)]
+            choice_values = OrderedDict(choice_values)
+            results[c['id']] = { 'id':c['id']
+                               , 'name':c['name']
+                               , 'sum':0
+                               , 'average':0.0
+                               , 'nb':0
+                               , 'blank':0
+                               , 'values_':choice_values }
+        for uc in user_choices:
+            results[uc['choiceid']]['nb'] += 1
+            results[uc['choiceid']]['values_'][uc['weight']]['nb'] += 1
+            if uc['weight'] is None:
+                results[uc['choiceid']]['blank'] += 1
+                vote['blank'] += 1
+            else:
+                results[uc['choiceid']]['sum'] += uc['weight']
+        for c in results:
+            if results[c]['nb'] - results[c]['blank'] != 0:
+                results[c]['average'] = results[c]['average'] + (float(results[c]['sum']) / float(results[c]['nb'] - results[c]['blank']))
+            previous_percent = 0
+            for w in weights:
+                if results[c]['nb'] > 0:
+                    percent = float(results[c]['values_'][w]['nb'] * 100) / results[c]['nb']
+                else:
+                    percent = 0.
+                results[c]['values_'][w]['percent'] = percent
+                results[c]['values_'][w]['previous_percent'] = previous_percent
+                previous_percent += percent
+            results[c]['values_'] = results[c]['values_'].values()
+        results = sorted(results.values(), key=lambda c: c['average'], reverse=True)
+        len_results = len(results)
+        if len_results % 2 == 0:
+            medians = results[len_results/2-1:len_results/2+1]
+        else:
+            medians = [results[len_results/2]]
+        results = { 'list':results
+                  , 'medians':[m['id'] for m in medians]
+                  , 'average':sum([r['sum'] for r in results])/len_results }
+        # ACTION: list user results per user
+        users = OrderedDict()
+        if vote['is_anonymous']:
+            user_votes = query_db('select users.name, id_user as userid, comment \
+                                   from user_vote \
+                                   join users on users.id = id_user where id_vote = ?', [idvote])
+            for uc in user_votes:
+                users[uc['userid']] = { 'username':uc['name']
+                                      , 'comment':uc['comment']
+                                      , 'choices':{}
+                                      , 'userid':uc['userid'] }
+        else:
+            for uc in user_choices:
+                weight = uc['weight']
+                value = { 'weight':weight
+                        , 'name':weights[weight]['name']
+                        , 'class':weights[weight]['class'] }
+                if uc['userid'] in users:
+                    users[uc['userid']]['choices'][uc['choiceid']] = value
+                else:
+                    users[uc['userid']] = { 'userid':uc['userid']
+                                          , 'username':uc['username']
+                                          , 'comment':uc['comment']
+                                          , 'choices':{uc['choiceid']:value} }
+        attachments = query_db('select * from attachments where id_vote=?', [idvote])
+        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']:

             flash(u'Ce vote vous concerne !', 'info')
             flash(u'Ce vote vous concerne !', 'info')

-        return render_template('vote.html', vote=vote, attachments=attachments, choices=choices, users=users.values(), can_vote=can_vote(idvote, get_userid()))
+        return render_template('vote.html', vote=vote, attachments=attachments
+                              , values=values, choices=choices, results=results, users=users.values()
+                              , can_vote=can_vote(idvote, get_userid()))

     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
     return redirect(url_for('home'))
 
     flash(u'Vous n\'avez pas le droit de voir ce vote, désolé.')
     return redirect(url_for('home'))
 


@@ -515,9 +656,17 @@ def vote(idvote):
 def vote_deletechoices(idvote, iduser):
     if int(iduser) != get_userid():
         abort(401)
 def vote_deletechoices(idvote, iduser):
     if int(iduser) != get_userid():
         abort(401)

-    g.db.execute('delete from user_choice where id_user = ? and id_choice in (select id from choices where id_vote = ?)',
-        [iduser, idvote])
-    g.db.commit()
+    vote = query_db('select votes.* from votes \
+                     where votes.id=?', [idvote], one=True)
+    if not vote['is_terminated'] and not vote['is_anonymous']:
+        g.db.execute('delete from user_choice where id_user = ? and id_choice \
+                      in (select id from choices where id_vote = ?)'
+                    , [iduser, idvote])
+        g.db.commit()
+        g.db.execute('delete from user_vote where id_user = ? and id_vote \
+                      in (select id_vote from choices where id_vote = ?)'
+                    , [iduser, idvote])
+        g.db.commit()

     return redirect(url_for('vote', idvote=idvote))
 
 #-------------
     return redirect(url_for('vote', idvote=idvote))
 
 #-------------


@@ -527,13 +676,19 @@ def vote_deletechoices(idvote, iduser):
 def admin_votes():
     if not session.get('user').get('is_admin'):
         abort(401)
 def admin_votes():
     if not session.get('user').get('is_admin'):
         abort(401)

-    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')
-    return render_template('admin_votes.html', votes=votes, today=date.today().strftime("%Y-%m-%d"))
+    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')
+    return render_template('admin_votes.html', votes=votes
+      , today=date.today().strftime("%Y-%m-%d")
+      , can_delete_votes=CAN_DELETE_VOTES
+      )

 
 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 def admin_vote_add():
     if not session.get('user').get('is_admin'):
         abort(401)
 
 @app.route('/admin/votes/add', methods=['GET', 'POST'])
 def admin_vote_add():
     if not session.get('user').get('is_admin'):
         abort(401)

+    cardinals= OrderedDict([(len(values), {'name':name,'values':values,'first':first}) for (name, first, values) in CARDINALS])

     if request.method == 'POST':
         if request.form['title']:
             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:
     if request.method == 'POST':
         if request.form['title']:
             if query_db('select * from votes where title = ?', [request.form['title']], one=True) is None:


@@ -541,26 +696,51 @@ def admin_vote_add():
                 date_end = date.today() + timedelta(days=int(request.form['days']))
                 transparent = 0
                 public = 0
                 date_end = date.today() + timedelta(days=int(request.form['days']))
                 transparent = 0
                 public = 0

-                multiplechoice = 0
-                if 'transparent' in request.form.keys():
+                anonymous = 0
+                if 'transparent' in request.form:

                     transparent = 1
                     transparent = 1

-                if 'public' in request.form.keys():
+                if 'public' in request.form:

                     public = 1
                     public = 1

-                if 'multiplechoice' in request.form.keys():
-                    multiplechoice = 1
+                if 'anonymous' in request.form:
+                    anonymous = 1
+                try: quorum = float(request.form.get('quorum'))
+                except ValueError:
+                    quorum = 0
+                if not (0 <= quorum and quorum <= 1):
+                    flash(u'Une erreur est survenue !', 'error')

                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
                 if group is None:
                     group[id] = 1
                 group = query_db('select id from groups where name = ?', [request.form['group']], one=True)
                 if group is None:
                     group[id] = 1

-                g.db.execute('insert into votes (title, description, category, date_begin, date_end, is_transparent, is_public, is_multiplechoice, id_group, id_author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
-                        [request.form['title'], request.form['description'], request.form['category'], date_begin, date_end, transparent, public, multiplechoice, group['id'], session['user']['id']])
+                try: cardinal = int(request.form.get('cardinal'))
+                except ValueError:
+                    cardinal = None
+                if cardinal in cardinals:
+                    cardinal_name   = cardinals[cardinal]['name']
+                    cardinal_values = cardinals[cardinal]['values']
+                    weight = cardinals[cardinal]['first'] if not cardinals[cardinal]['first'] is None else -(cardinal/2)
+                    if query_db('select * from cardinals where id = ?', [cardinal], one=True) is None:
+                        g.db.execute('insert into cardinals (id, name, first) values (?, ?, ?)', [len(cardinal_values), cardinal_name, weight])
+                        g.db.commit()
+                        for name in cardinal_values:
+                            g.db.execute('insert into values_ (id_cardinal, name, weight) values (?, ?, ?)'
+                                        , [cardinal, name, weight])
+                            g.db.commit()
+                            weight += 1
+                g.db.execute('insert into votes (title, description, category, \
+                              date_begin, date_end, quorum, is_transparent, is_public, \
+                              is_anonymous, id_group, id_author, id_cardinal) \
+                              values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
+                        [ request.form['title'], request.form['description'], request.form['category']
+                        , date_begin, date_end, quorum, transparent, public, anonymous
+                        , group['id'], session['user']['id'], cardinal ])

                 g.db.commit()
                 g.db.commit()

-                vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc', 
-                        [request.form['title'], date_begin], one=True)
+                vote = query_db('select * from votes where title = ? and date_begin = ? order by id desc'
+                               , [request.form['title'], date_begin], one=True)

                 if vote is None:
                     flash(u'Une erreur est survenue !', 'error')
                     return redirect(url_for('home'))
                 else:
                 if vote is None:
                     flash(u'Une erreur est survenue !', 'error')
                     return redirect(url_for('home'))
                 else:

-                    if request.form['pattern'] in PATTERNS.keys():
+                    if request.form['pattern'] in PATTERNS:

                         pattern = PATTERNS[request.form['pattern']]
                         for choice in pattern:
                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])
                         pattern = PATTERNS[request.form['pattern']]
                         for choice in pattern:
                             g.db.execute('insert into choices (name, id_vote) values (?, ?)', [choice, vote['id']])


@@ -572,7 +752,8 @@ def admin_vote_add():
         else:
             flash(u'Vous devez spécifier un titre.', 'error')
     groups = query_db('select * from groups') 
         else:
             flash(u'Vous devez spécifier un titre.', 'error')
     groups = query_db('select * from groups') 

-    return render_template('admin_vote_new.html', groups=groups, patterns=PATTERNS)
+    return render_template('admin_vote_new.html', groups=groups, cardinals=cardinals
+                          , quorums=QUORUMS, patterns=PATTERNS)

 
 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 def admin_vote_edit(voteid):
 
 @app.route('/admin/votes/edit/<voteid>', methods=['GET', 'POST'])
 def admin_vote_edit(voteid):


@@ -588,37 +769,86 @@ def admin_vote_edit(voteid):
                 date_end = date_end.strftime("%Y-%m-%d")
             transparent = 0
             public = 0
                 date_end = date_end.strftime("%Y-%m-%d")
             transparent = 0
             public = 0

-            if 'transparent' in request.form.keys():
+            if 'transparent' in request.form:

                 transparent = 1
                 transparent = 1

-            if 'public' in request.form.keys():
+            if 'public' in request.form:

                 public = 1
             isopen = 0
             isterminated = 0
                 public = 1
             isopen = 0
             isterminated = 0

+            print "POST"

             if request.form['status'] == 'Ouvert':
             if request.form['status'] == 'Ouvert':

-                choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
-                if choices is not None and choices['nb'] >= 2:
+                choices = query_db('select id_vote, count(*) as nb, groups.name as group_name \
+                                    from choices \
+                                    join votes on votes.id = choices.id_vote \
+                                    join groups on groups.id = votes.id_group \
+                                    where id_vote = ? \
+                                    group by id_vote', [voteid], one=True)
+                if choices is not None and choices['nb'] >= 1:

                     isopen = 1
                     isopen = 1

+                    previousvote = query_db('select id, is_open, id_group from votes where id = ?', [voteid], one=True)
+                    if (previousvote is None or previousvote['is_open'] == 0) and 'mail_notice' in request.form:
+                        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']])
+                        for user in users_to_vote:
+                            link = request.url_root + url_for('vote', idvote=voteid)
+                            BODY = string.join((
+                                u"From: %s" % EMAIL,
+                                u"To: %s" % user['email'],
+                                u"Subject: [vote] [%s] %s" % (choices['group_name'], request.form['title']),
+                                u"Date: %s" % time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.gmtime()).decode('utf-8'),
+                                u"Content-type: text/plain; charset=utf-8",
+                                u"X-Mailer: %s" % VERSION,
+                                "",
+                                u"%(text0)s%(group)s" % \
+                                  { "text0":gettext(u"A new vote concerns you within the group: ") \
+                                  , "group":choices['group_name'] }, \
+                                link,
+                                "",
+                                gettext(u"If you think this mail is not for you, please ignore and delete it."),
+                                gettext(u"For more informations, you can contact:"),
+                                EMAIL
+                                ), "\r\n")
+                            server = smtplib.SMTP(SMTP_SERVER)
+                            server.sendmail(EMAIL, [user['email']], BODY.encode('utf-8'))
+                            server.quit()

                 else:
                 else:

-                    flash(u'Vous devez proposer au moins deux choix pour ouvrir le vote.', 'error')
+                    flash(u'Vous devez proposer au moins un choix pour ouvrir le vote.', 'error')

             elif request.form['status'] == u'Terminé':
                 isterminated = 1
                 if vote['is_open']:
                     isopen = 1
             elif request.form['status'] == u'Terminé':
                 isterminated = 1
                 if vote['is_open']:
                     isopen = 1

-            g.db.execute('update votes set title = ?, description = ?, category = ?, is_transparent = ?, is_public = ?, is_open = ?, is_terminated = ?, date_end = ? where id = ?',
-                    [request.form['title'], request.form['description'], request.form['category'], transparent, public, isopen, isterminated, date_end, voteid])
+            g.db.execute('update votes set title = ?, description = ?, category = ?, quorum = ?, \
+                          is_transparent = ?, is_public = ?, is_open = ?, is_terminated = ?, \
+                          date_end = ?, reminder_last_days = ? where id = ?',
+                          [ request.form['title'], request.form['description'], request.form['category'], request.form['quorum']
+                          , transparent, public, isopen, isterminated, date_end, request.form['reminder'], voteid ])

             g.db.commit()
             vote = query_db('select * from votes where id = ?', [voteid], one=True)
             flash(u"Le vote a bien été mis à jour.", "success")
         else:
             flash(u'Vous devez spécifier un titre.', 'error')
             g.db.commit()
             vote = query_db('select * from votes where id = ?', [voteid], one=True)
             flash(u"Le vote a bien été mis à jour.", "success")
         else:
             flash(u'Vous devez spécifier un titre.', 'error')

-    vote['duration'] = (datetime.strptime(vote['date_end'], "%Y-%m-%d") 
-            - datetime.strptime(vote['date_begin'], "%Y-%m-%d")).days
-    group = query_db('select name from groups where id = ?', [vote['id_group']], one=True) 
+    vote['duration'] = (datetime.strptime(vote['date_end'], "%Y-%m-%d") - datetime.strptime(vote['date_begin'], "%Y-%m-%d")).days
+    group = query_db('select name from groups where id = ?', [vote['id_group']], one=True)

     choices = query_db('select * from choices where id_vote = ?', [voteid])
     choices = query_db('select * from choices where id_vote = ?', [voteid])

+    values_ = query_db('select * from cardinals where id = ?', [vote['id_cardinal']], one=True)['name']

     attachments = query_db('select * from attachments where id_vote = ?', [voteid])
     attachments = query_db('select * from attachments where id_vote = ?', [voteid])

-    if date.today().strftime("%Y-%m-%d") > vote['date_end']:
+    if date.today().strftime("%Y-%m-%d") > vote['date_end'] and not vote['is_terminated']:

         flash(u'La deadline du vote est expirée, vous devriez terminer le vote.')
         flash(u'La deadline du vote est expirée, vous devriez terminer le vote.')

-    return render_template('admin_vote_edit.html', vote=vote, group=group, choices=choices, attachments=attachments)
+    return render_template('admin_vote_edit.html', vote=vote, group=group, values_=values_, choices=choices, attachments=attachments, quorums=QUORUMS)
+
+@app.route('/admin/votes/delete/<idvote>')
+def admin_vote_del(idvote):
+    if not session.get('user').get('is_admin'):
+        abort(401)
+    if not CAN_DELETE_VOTES:
+        flash(u'La configuration interdit la suppression des votes.', 'error')
+    else:
+      if vote is None:
+          abort(404)
+      g.db.execute('update votes set is_hidden=1 where id = ?', [idvote])
+      g.db.commit()
+    return redirect(url_for('admin_votes'))

 
 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 def admin_vote_addchoice(voteid):
 
 @app.route('/admin/votes/addchoice/<voteid>', methods=['POST'])
 def admin_vote_addchoice(voteid):


@@ -652,7 +882,9 @@ def admin_vote_deletechoice(voteid, choiceid):
         abort(404)
     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
     g.db.commit()
         abort(404)
     g.db.execute('delete from choices where id = ? and id_vote = ?', [choiceid, voteid])
     g.db.commit()

-    choices = query_db('select id_vote, count(*) as nb from choices where id_vote = ? group by id_vote', [voteid], one=True)
+    choices = query_db('select id_vote, count(*) as nb \
+                        from choices where id_vote = ? \
+                        group by id_vote', [voteid], one=True)

     if choices is None or choices['nb'] < 2:
         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
         g.db.commit()
     if choices is None or choices['nb'] < 2:
         g.db.execute('update votes set is_open=0 where id = ?', [voteid])
         g.db.commit()


@@ -686,4 +918,3 @@ def admin_vote_deleteattachment(voteid, attachmentid):
 
 if __name__ == '__main__':
     app.run()
 
 if __name__ == '__main__':
     app.run()

-