def add(d):
del d['id']
sql = "INSERT INTO assignments (%s) VALUES (%s);" % (
','.join(d.keys()), '?' + ',?' * (len(d) - 1)
)
vals = list(d.values())
return Dao.execute(sql, vals)
def update_user(d):
sql = ("UPDATE users "
"SET username=?, password=?, role_id=? "
"WHERE id=?;")
vals = (d['username'], User.__hash_pw(d['password']), d['role_id'],
d['id'])
return Dao.execute(sql, vals)
def update(d):
empid = d['id']
del d['id']
sql = ("UPDATE employees "
"SET %s "
"WHERE id=?;") % (','.join(f + '=?' for f in d.keys()))
vals = list(d.values()) + [empid]
return Dao.execute(sql, vals)
def update(d):
asnid = d['id']
del d['id']
sql = ("UPDATE assignments "
"SET %s "
"WHERE id=?;") % (','.join(f + '=?' for f in d.keys()))
vals = list(d.values()) + [asnid]
return Dao.execute(sql, vals)
def update(d):
asnid = d['id']
del d['id']
sql = ("UPDATE assignments "
"SET %s "
"WHERE id=?;") % (
','.join(f + '=?' for f in d.keys()))
vals = list(d.values()) + [asnid]
return Dao.execute(sql, vals)
def update(d):
empid = d['id']
del d['id']
sql = ("UPDATE employees "
"SET %s "
"WHERE id=?;") % (
','.join(f + '=?' for f in d.keys()))
vals = list(d.values()) + [empid]
return Dao.execute(sql, vals)
def login(username, password):
sql = "SELECT * FROM users WHERE username=?;"
vals = (username, )
rex = Dao.execute(sql, vals)
if not rex or len(rex) != 1:
raise Exception('Invalid login!')
if not User.__verify_pw(password, rex[0]['password']):
raise Exception('Invalid login!')
return rex[0]
def login(username, password):
sql = "SELECT * FROM users WHERE username=?;"
vals = (username, )
rex = Dao.execute(sql, vals)
if not rex or len(rex) != 1:
raise Exception('Invalid login!')
if not User.__verify_pw(password, rex[0]['password']):
raise Exception('Invalid login!')
return rex[0]
def get_for_timeframe(first_month, last_month):
sql = ("SELECT a.id AS id, "
"a.employee_id AS employee_id, "
"a.project_id AS project_id, "
"a.first_month AS first_month, "
"a.last_month AS last_month, "
"a.effort AS effort, "
"e.name AS employee, "
"p.nickname AS project "
"FROM assignments AS a "
"JOIN employees AS e ON a.employee_id=e.id "
"JOIN projects AS p ON a.project_id=p.id "
"WHERE a.first_month BETWEEN ? AND ? "
"OR a.last_month BETWEEN ? AND ?;")
vals = (first_month, last_month, first_month, last_month)
return Dao.execute(sql, vals)
def get_for_timeframe(first_month, last_month):
sql = ("SELECT a.id AS id, "
"a.employee_id AS employee_id, "
"a.project_id AS project_id, "
"a.first_month AS first_month, "
"a.last_month AS last_month, "
"a.effort AS effort, "
"e.name AS employee, "
"p.nickname AS project "
"FROM assignments AS a "
"JOIN employees AS e ON a.employee_id=e.id "
"JOIN projects AS p ON a.project_id=p.id "
"WHERE a.first_month BETWEEN ? AND ? "
"OR a.last_month BETWEEN ? AND ?;")
vals = (first_month, last_month, first_month, last_month)
return Dao.execute(sql, vals)
def get_for_project(prjid, month=None):
sql = ("SELECT a.id AS id, "
"a.employee_id AS employee_id, "
"a.first_month AS first_month, "
"a.last_month AS last_month, "
"a.effort AS effort, "
"a.notes AS notes, "
"e.name AS employee "
"FROM assignments AS a "
"JOIN employees AS e ON a.employee_id= e.id "
"WHERE a.project_id=? ")
vals = [prjid]
if month:
sql += "AND a.last_month >= ? "
vals += [month]
sql += "ORDER BY e.name;"
return Dao.execute(sql, vals)
def get_for_project(prjid, month=None):
sql = ("SELECT a.id AS id, "
"a.employee_id AS employee_id, "
"a.first_month AS first_month, "
"a.last_month AS last_month, "
"a.effort AS effort, "
"a.notes AS notes, "
"e.name AS employee "
"FROM assignments AS a "
"JOIN employees AS e ON a.employee_id= e.id "
"WHERE a.project_id=? ")
vals = [prjid]
if month:
sql += "AND a.last_month >= ? "
vals += [month]
sql += "ORDER BY e.name;"
return Dao.execute(sql, vals)
def get_for_employee(empid, month=None):
sql = ("SELECT a.id AS id, "
"a.project_id AS project_id, "
"a.first_month AS first_month, "
"a.last_month AS last_month, "
"a.effort AS effort, "
"a.notes AS notes, "
"p.nickname AS project "
"FROM assignments AS a "
"JOIN projects AS p "
"ON a.project_id= p.id "
"WHERE a.employee_id=? ")
vals = [empid]
if month:
sql += ("AND a.first_month <= ? " "AND a.last_month >= ? ")
vals += [month, month]
sql += "ORDER BY p.nickname;"
return Dao.execute(sql, vals)
def get_for_employee(empid, month=None):
sql = ("SELECT a.id AS id, "
"a.project_id AS project_id, "
"a.first_month AS first_month, "
"a.last_month AS last_month, "
"a.effort AS effort, "
"a.notes AS notes, "
"p.nickname AS project "
"FROM assignments AS a "
"JOIN projects AS p "
"ON a.project_id= p.id "
"WHERE a.employee_id=? ")
vals = [empid]
if month:
sql += ("AND a.first_month <= ? "
"AND a.last_month >= ? ")
vals += [month, month]
sql += "ORDER BY p.nickname;"
return Dao.execute(sql, vals)
def get_roles():
sql = 'SELECT id, name AS value, description FROM roles;'
return Dao.execute(sql)
def change_password(user_id, new_password):
sql = ("UPDATE users " "SET password=? " "WHERE id=?")
vals = (User.__hash_pw(new_password), user_id)
return Dao.execute(sql, vals)
def delete_user(user_id):
sql = "DELETE FROM users WHERE id=?;"
vals = (user_id, )
return Dao.execute(sql, vals)
def update_role(d):
sql = ("UPDATE roles "
"SET name=?, description=? "
"WHERE id=?;")
vals = (d['name'], d['description'], d['id'])
return Dao.execute(sql, vals)
def delete(asnid):
sql = "DELETE FROM assignments WHERE id=?;"
vals = [asnid]
return Dao.execute(sql, vals)
def delete_role(role_id):
sql = "DELETE FROM roles WHERE id=?;"
vals = (role_id, )
return Dao.execute(sql, vals)
def update_user(d):
sql = ("UPDATE users "
"SET username=?, password=?, role_id=? "
"WHERE id=?;")
vals = (d['username'], User.__hash_pw(d['password']), d['role_id'], d['id'])
return Dao.execute(sql, vals)
def delete_user(user_id):
sql = "DELETE FROM users WHERE id=?;"
vals = (user_id,)
return Dao.execute(sql, vals)
def change_password(user_id, new_password):
sql = ("UPDATE users "
"SET password=? "
"WHERE id=?")
vals = (User.__hash_pw(new_password), user_id)
return Dao.execute(sql, vals)
def add(d):
del d['id']
sql = "INSERT INTO employees (%s) VALUES (%s);" % (','.join(
d.keys()), '?' + ',?' * (len(d) - 1))
vals = list(d.values())
return Dao.execute(sql, vals)
def get_roles():
sql = 'SELECT id, name AS value, description FROM roles;'
return Dao.execute(sql)
def delete_role(role_id):
sql = "DELETE FROM roles WHERE id=?;"
vals = (role_id,)
return Dao.execute(sql, vals)
def add_role(d):
sql = ("INSERT INTO roles " "(name, description) " "VALUES (?,?);")
vals = (d['name'], d['description'])
return Dao.execute(sql, vals)
def update_role(d):
sql = ("UPDATE roles " "SET name=?, description=? " "WHERE id=?;")
vals = (d['name'], d['description'], d['id'])
return Dao.execute(sql, vals)
def get_all():
sql = "SELECT * FROM employees ORDER BY name;"
return Dao.execute(sql)
def get_users():
sql = "SELECT * FROM users;"
return Dao.execute(sql)
def get_users():
sql = "SELECT * FROM users;"
return Dao.execute(sql)
def add_user(d):
sql = ("INSERT INTO users "
"(username, password, role_id) "
"VALUES (?,?,?);")
vals = (d['username'], User.__hash_pw(d['password']), d['role_id'])
return Dao.execute(sql, vals)
def add_user(d):
sql = ("INSERT INTO users "
"(username, password, role_id) "
"VALUES (?,?,?);")
vals = (d['username'], User.__hash_pw(d['password']), d['role_id'])
return Dao.execute(sql, vals)
def get_all():
sql = "SELECT * FROM employees ORDER BY name;"
return Dao.execute(sql)
def add_role(d):
sql = ("INSERT INTO roles "
"(name, description) "
"VALUES (?,?);")
vals = (d['name'], d['description'])
return Dao.execute(sql, vals)
def delete(asnid):
sql = "DELETE FROM assignments WHERE id=?;"
vals = [asnid]
return Dao.execute(sql, vals)
def get_all():
sql = "SELECT * FROM projects ORDER BY nickname;"
return Dao.execute(sql)
