def department_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# TODO: Add dept total
db_cursor.execute("""
select
d.id,
d.department_name,
d.budget,
count(e.id) as dept_total
from hrapp_department d
left join hrapp_employee e
on d.id = e.department_id
group by d.id
""")
all_departments = []
dataset = db_cursor.fetchall()
for row in dataset:
department = Department()
department.id = row['id']
department.department_name = row['department_name']
department.budget = row['budget']
department.dept_total = row['dept_total']
all_departments.append(department)
template = 'departments/department_list.html'
context = {'departments': all_departments}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO hrapp_department
(
department_name, budget
)
VALUES (?, ?)
""", (
form_data['department_name'],
form_data['budget'],
))
return redirect(reverse('hrapp:department_list'))
def department_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
Count(e.id) employees,
d.department_name,
d.id,
d.budget,
e.id
FROM hrapp_department d
Left JOIN hrapp_employee e ON e.department_id = d.id
GROUP BY d.department_name
""")
all_departments = []
dataset = db_cursor.fetchall()
for row in dataset:
department = Department()
department.id = row['id']
department.department_name = row['department_name']
department.budget = row['budget']
department.employees = row['employees']
all_departments.append(department)
template = 'departments/departments_list.html'
context = {'departments': all_departments}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO hrapp_department
(
department_name, budget
)
VALUES (?, ?)
""", (form_data['department_name'], form_data['budget']))
return redirect(reverse('hrapp:departments'))
def create_departmant(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row['id']
department.department_name = _row['department_name']
department.department_budget = _row['department_budget']
department.employees = []
employee = Employee()
employee.id = _row["id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
employee.start_date = _row["start_date"]
employee.is_supervisor = _row["is_supervisor"]
employee.department_id = _row["department_id"]
return (
department,
employee,
)