def delete_project_by_id(project_id):
conn = make_conn()
cur = conn.cursor()
cur.execute(f'''
DELETE
FROM project
WHERE id = {project_id}
''')
conn.commit()
cur.close()
return '', 204
def add_employee_to_project(employee_id, project_id):
conn = make_conn()
cur = conn.cursor()
cur.execute(f'''
INSERT
INTO employee_project (employee_id, project_id)
VALUES ({employee_id}, {project_id})
''')
conn.commit()
cur.close()
return '', 201
def update_employee_by_id(employee_id, name):
conn = make_conn()
cur = conn.cursor()
cur.execute(f'''
UPDATE employee
SET name = '{name}'
WHERE id = {employee_id}
''')
conn.commit()
cur.close()
return '', 201
def delete_employee_by_id(employee_id):
conn = make_conn()
cur = conn.cursor()
cur.execute(f'''
DELETE
FROM employee
WHERE id = {employee_id}
''')
conn.commit()
cur.close()
return '', 204
def delete_company_by_id(company_id):
conn = make_conn()
cur = conn.cursor()
cur.execute(f'''
DELETE
FROM company
WHERE id = {company_id}
''')
conn.commit()
cur.close()
return '', 204
def create_company(user):
conn = make_conn()
cur = conn.cursor()
cur.execute(f'''
INSERT
INTO company (name, username, password)
VALUES ('{user['name']}', '{user['username']}', '{user['password']}')
''')
conn.commit()
cur.close()
return '', 201
def update_company_by_id(company_id, name):
conn = make_conn()
cur = conn.cursor()
cur.execute(f'''
UPDATE company
SET name = '{name}'
WHERE id = {company_id}
''')
conn.commit()
cur.close()
return '', 201
def get_employees_by_project_id(project_id):
conn = make_conn()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f'''
SELECT *
FROM employee_project
WHERE project_id = {project_id}
''')
projects = cur.fetchall()
cur.close()
return projects
def get_sub_projects(project_id):
conn = make_conn()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f'''
SELECT *
FROM project
WHERE parent_project_id = {project_id}
''')
projects = cur.fetchall()
cur.close()
return jsonify(projects)
def update_project_by_id(project_id, name, cost):
conn = make_conn()
cur = conn.cursor()
cur.execute(f'''
UPDATE project
SET name = '{name}',
cost = {cost}
WHERE id = {project_id}
''')
conn.commit()
cur.close()
return '', 201
def get_project_by_id(project_id):
conn = make_conn()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f'''
SELECT *
FROM project
WHERE id = {project_id}
''')
project = cur.fetchone()
cur.close()
return project
def get_company_by_username(username):
conn = make_conn()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f'''
SELECT *
FROM company
WHERE username = '******'
''')
company = cur.fetchone()
cur.close()
return company
def get_all_projects_by_company_id(company_id):
conn = make_conn()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f'''
SELECT *
FROM project
WHERE company_id = {company_id}
''')
projects = cur.fetchall()
cur.close()
return jsonify(projects)
def get_company_by_id(company_id):
conn = make_conn()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f'''
SELECT *
FROM company
WHERE id = {company_id}
''')
company = cur.fetchone()
cur.close()
return company
def create_project(name, cost, company_id, parent_project_id):
conn = make_conn()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f'''
INSERT
INTO project (name, cost, company_id, parent_project_id)
VALUES ('{name}', {cost}, {company_id}, {parent_project_id})
RETURNING id
''')
project_id = cur.fetchone()
conn.commit()
cur.close()
return project_id, 201
def create_employee(company_id, name):
conn = make_conn()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f'''
INSERT
INTO employee (name, company_id)
VALUES ('{name}', {company_id})
RETURNING id
''')
employee_id = cur.fetchone()
conn.commit()
cur.close()
return employee_id, 201
