def create_department(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["department_id"]
department.dept_name = _row["dept_name"]
department.budget = _row["budget"]
# Note: You are adding a blank employees list to the department object
# This list will be populated later (see below)
department.employees = []
employee = Employee()
employee.id = _row["employee_id"]
employee.first_name = _row["first_name"]
employee.Last_name = _row["last_name"]
employee.department_id = _row["department_id"]
# Return a tuple containing the department and the
# employee built from the data in the current row of
# the data set
return (
department,
employee,
)
def create_list_employees(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["id"]
department.name = _row["name"]
department.budget = _row["budget"]
department.employees = []
employee = Employee()
employee.department_id = _row["department_id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
department_employees = {}
for (department, employee) in department:
if department.id not in department_employees:
department_employees[department.id] = department
department_employees[department.id].employees.append(employee)
else:
department_employees[department.id].employees.append(employee)
return (
department,
employee,
)
def get_department(department_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = create_employee
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
d.id,
d.department_name,
d.budget,
e.first_name,
e.last_name,
e.id AS e_id
FROM hrapp_department d
LEFT JOIN hrapp_employee e
ON e.department_id = d.id
WHERE d.id = ?
""", (department_id, ))
department_employees = db_cursor.fetchall()
department = Department()
department.employees = []
department.name = department_employees[0]["department_name"]
for employee in department_employees:
department.employees.append(employee["employee"])
return department
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_department(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["id"]
department.dept_name = _row["dept_name"]
department.budget = _row["budget"]
department.employees = []
employee = Employee()
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
employee.is_supervisor = _row["is_supervisor"]
employee.start_date = _row["start_date"]
return (department, employee,)
def create_department(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["id"]
department.name = _row["name"]
department.budget = _row["budget"]
department.employees = []
employee = Employee()
employee.id = _row["id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
employee.department_id = _row["department_id"]
return (department, employee)
def get_department(department_name):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
d.name,
d.budget,
e.first_name,
e.last_name,
e.is_supervisor
FROM hrapp_department AS d
LEFT JOIN hrapp_employee AS e ON e.department_id = d.id
WHERE d.name = ?
""", (department_name, ))
response = db_cursor.fetchall()
for row in response:
if row == response[0]:
department = Department()
department.employees = []
department.name = row['name']
department.budget = row['budget']
if not row['first_name'] == None:
employee = Employee()
employee.first_name = row['first_name']
employee.last_name = row['last_name']
employee.is_supervisor = row['is_supervisor']
department.employees.append(employee)
else:
employee = Employee()
employee.first_name = row['first_name']
employee.last_name = row['last_name']
employee.is_supervisor = row['is_supervisor']
department.employees.append(employee)
print(department.employees)
return department