def delete_votation_by_id(votation_id):
"""Delete the votation from the DB"""
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("delete from votation where votation_id = ?", (votation_id,))
c.close()
conn.close()
def delete_dto(o):
"""delete guarantor_dto """
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("delete from guarantor where votation_id = ? and user_id = ?",(o.votation_id, o.u.user_id) )
c.close()
conn.close()
def set_passphrase_ok(user_id,votation_id):
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("""update guarantor set passphrase_ok = 1 where
votation_id = ? and
user_id = ?""",(votation_id, user_id) )
c.close()
conn.close()
def delete_dto(o):
"""Delete the candidate_dto from the DB"""
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("delete from candidate where votation_id = ? and user_id = ?",
(o.votation_id, o.u.user_id))
c.close()
conn.close()
def insert_user_dto(u):
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("insert into voting_user(user_name,pass_word) values(?,?)",
(u.user_name, u.pass_word))
u.user_id = c.lastrowid
c.close()
conn.close()
def insert_dto(o):
"""Insert the guarantor_dto into the DB"""
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("""insert into guarantor(
votation_id,
user_id, hash_ok, passphrase_ok,order_n)
select ?,?,0,0,count(*)+1 from guarantor a where a.votation_id = ?""",(o.votation_id, o.u.user_id,o.votation_id) )
c.close()
conn.close()
def insert_dto(o):
"""Insert the candidate_dto into the DB"""
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute(
"""insert into candidate(
votation_id,
user_id, passphrase_ok,order_n) select ?,?,0,count(*)+1 from candidate where votation_id = ?""",
(o.votation_id, o.u.user_id, o.votation_id))
c.close()
conn.close()
def check_for_duplicate(o):
"""Returns true/false"""
result = False
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("select 1 from guarantor where votation_id = ? and user_id=?", (o.votation_id,o.u.user_id) )
row = c.fetchone()
if row:
result = True
c.close()
conn.close()
return result
def load_guarantor(votation_id, user_id):
"""Returns a guarantor_dto"""
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("select * from guarantor c where c.votation_id = ? and user_id = ?", (votation_id,user_id) )
row = c.fetchone()
g = guarantor_dto()
g.votation_id = row['votation_id']
g.hash_ok = row['hash_ok']
g.passphrase_ok = row['passphrase_ok']
g.order_n = row['order_n']
g.u = user.load_user_by_id(row['user_id'])
c.close()
conn.close()
return g
def load_user_by_id(user_id):
"""Returns a user_dto object or None"""
u = None
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("select * from voting_user where user_id = ?", (user_id, ))
row = c.fetchone()
if row:
u = user_dto()
u.user_id = row['user_id']
u.user_name = row['user_name']
u.pass_word = row['pass_word']
c.close()
conn.close()
return u
def load_candidate(votation_id, user_id):
"""Returns a candidate_dto """
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute(
"select * from candidate c where c.votation_id = ? and c.user_id = ?",
(votation_id, user_id))
row = c.fetchone()
o = candidate_dto()
o.votation_id = row['votation_id']
o.passphrase_ok = row['passphrase_ok']
o.order_n = row['order_n']
o.u = user.load_user_by_id(row['user_id'])
c.close()
conn.close()
return o
def load_guarantor_by_votation(votation_id):
"""Returns a guarantor_dto array"""
ar = []
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("select * from guarantor c where c.votation_id = ? order by order_n", (votation_id,) )
row = c.fetchone()
while row:
g = guarantor_dto()
g.votation_id = row['votation_id']
g.hash_ok = row['hash_ok']
g.passphrase_ok = row['passphrase_ok']
g.order_n = row['order_n']
g.u = user.load_user_by_id(row['user_id'])
ar.append(g)
row = c.fetchone()
c.close()
conn.close()
return ar
def load_votation_by_id(votation_id):
"""Returns a votation_dto object or None"""
v = None
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("select * from votation where votation_id = ?", (votation_id,))
row = c.fetchone()
if row:
v = votation_dto()
v.votation_id = row['votation_id']
v.promoter_user = user.load_user_by_id( row['promoter_user_id'] )
v.votation_description = row['votation_description']
v.begin_date = row['begin_date']
v.end_date = row['end_date']
v.votation_type = row['votation_type']
v.votation_status = row['votation_status']
c.close()
conn.close()
return v
def insert_votation_dto(v):
"""Insert the votation_dto into the DB"""
result = True
#try:
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("""insert into votation(
promoter_user_id,
votation_description,
begin_date,
end_date,
votation_type,
votation_status) values(?,?,?,?,?,?)""", (v.promoter_user.user_id, v.votation_description, v.begin_date, v.end_date, v.votation_type, v.votation_status))
v.votation_id = c.lastrowid
c.close()
conn.close()
#except sqlite3.IntegrityError::
# print(e)
# result = False
return result
def load_candidate_by_votation(votation_id):
"""Returns a candidate_dto array"""
ar = []
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute(
"select * from candidate c where c.votation_id = ? order by order_n",
(votation_id, ))
row = c.fetchone()
while row:
o = candidate_dto()
o.votation_id = row['votation_id']
o.passphrase_ok = row['passphrase_ok']
o.order_n = row['order_n']
o.u = user.load_user_by_id(row['user_id'])
ar.append(o)
row = c.fetchone()
c.close()
conn.close()
return ar
def load_user_by_username(user_name):
u = None
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("select * from voting_user where user_name = ?", (user_name, ))
row = c.fetchone()
if row:
u = user_dto()
u.user_id = row['user_id']
u.user_name = row['user_name']
u.pass_word = row['pass_word']
c.close()
conn.close()
if config.AUTH_METHOD == 'ldap':
if u == None:
u = user_dto()
u.user_name = user_name
u.pass_word = 'ldap user'
insert_user_dto(u)
return u
def load_votations():
"""Returns a votation_dto array"""
ar = []
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("select * from votation")
row = c.fetchone()
while row:
v = votation_dto()
v.votation_id = row['votation_id']
v.promoter_user = user.load_user_by_id( row['promoter_user_id'] )
v.votation_description = row['votation_description']
v.begin_date = row['begin_date']
v.end_date = row['end_date']
v.votation_type = row['votation_type']
v.votation_status = row['votation_status']
ar.append(v)
row = c.fetchone()
c.close()
conn.close()
return ar
def update_status(votation_id, new_status):
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("update votation set votation_status=? where votation_id = ?", (new_status,votation_id,))
c.close()
conn.close()
def delete_user_by_username(user_name):
conn = dbmanager.get_connection()
c = conn.cursor()
c.execute("delete from voting_user where user_name = ?", (user_name, ))
c.close()
conn.close()
