def assign_to_managers(project_id, manager_ids):
with connection.cursor() as cursor:
for manager_id in manager_ids:
sql = "REPLACE INTO managerproject (project_id, manager_id) VALUES (%(project_id)s, %(manager_id)s)"
cursor.execute(sql, {
'project_id': project_id,
'manager_id': manager_id
})
connection.commit()
def get_all_assigned_for_project(project_id):
with connection.cursor() as cursor:
sql = """
SELECT m.*
FROM managerproject mp LEFT JOIN manager m ON m.id = mp.manager_id
WHERE mp.project_id = %(project_id)s
"""
cursor.execute(sql, {'project_id': project_id})
return cursor.fetchall()
def get_assigned_projects(id):
with connection.cursor() as cursor:
sql = """
SELECT p.*
FROM managerproject mp LEFT JOIN projectview p ON p.id = mp.project_id
WHERE mp.manager_id = %(manager_id)s
"""
cursor.execute(sql, {'manager_id': id})
return cursor.fetchall()
def get_incomplete_for(manager_id):
with connection.cursor() as cursor:
cursor.callproc('incomplete_projects', (manager_id, ))
return cursor.fetchall()
def get_all():
with connection.cursor() as cursor:
sql = "SELECT * FROM project"
cursor.execute(sql)
return cursor.fetchall()
def delete(id):
with connection.cursor() as cursor:
sql = "DELETE FROM project WHERE id = %s"
cursor.execute(sql, (id, ))
connection.commit()
def get(id):
with connection.cursor() as cursor:
sql = "SELECT * FROM project WHERE id = %s"
cursor.execute(sql, (id, ))
return cursor.fetchone()
def add(name, start_date='', est_duration=''):
with connection.cursor() as cursor:
sql = "INSERT INTO project(name) VALUES (%s)"
cursor.execute(sql, (name, ))
connection.commit()
def get_by_name(name):
with connection.cursor() as cursor:
sql = "SELECT * FROM manager WHERE name = %(name)s"
cursor.execute(sql, {'name': name})
return cursor.fetchone()
def get(id):
with connection.cursor() as cursor:
sql = "SELECT * FROM manager WHERE id = %(id)s"
cursor.execute(sql, {'id': id})
return cursor.fetchone()
def delete(id):
with connection.cursor() as cursor:
sql = "DELETE FROM manager WHERE id = %(id)s"
cursor.execute(sql, {'id': id})
connection.commit()
def add(name, password_hash):
with connection.cursor() as cursor:
sql = "INSERT INTO manager (name, password_hash) VALUES (%s, %s)"
cursor.execute(sql, (name, password_hash))
connection.commit()
def assign_to_employees(task_id, employee_ids):
with connection.cursor() as cursor:
for employee_id in employee_ids:
sql = "REPLACE INTO employeetask (employee_id, task_id) VALUES (%s, %s)"
cursor.execute(sql, (employee_id, task_id))
connection.commit()
def get_all_for_project(project_id):
with connection.cursor() as cursor:
sql = "SELECT * FROM task WHERE project_id = %s"
cursor.execute(sql, (project_id, ))
return cursor.fetchall()
def add(project_id, name, start_date, end_date):
with connection.cursor() as cursor:
sql = "INSERT INTO task(name, project_id, start_date, end_date) VALUES (%s, %s, %s, %s)"
cursor.execute(sql, (name, project_id, start_date, end_date))
connection.commit()
