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(department_id) as employee_count,
dep.id,
dep.name,
dep.budget
from hrapp_employee emp
inner join hrapp_department dep
on emp.department_id = dep.id
group by department_id
""")
all_departments = []
dataset = db_cursor.fetchall()
for row in dataset:
dep = Department()
dep.id = row["id"]
dep.employee_count = row["employee_count"]
dep.name = row["name"]
dep.budget = row["budget"]
all_departments.append(dep)
template_name = 'departments/list.html'
context = {'all_departments': all_departments}
return render(request, template_name, context)
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 to query: e.department,
db_cursor.execute("""
SELECT
d.id,
d.name,
d.budget
FROM hrapp_department AS d
ORDER BY d.name
""")
all_departments = []
dataset = db_cursor.fetchall()
for row in dataset:
department = Department()
department.id = row['id']
department.name = row['name']
department.budget = row['budget']
all_departments.append(department)
template = 'departments/department_list.html'
context = {
'departments': all_departments
}
return render(request, template, context)
def department_list(request):
"""Show all departments include budget and department size"""
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# TODO: Add to query: e.department,
db_cursor.execute("""
select
d.id,
d.department,
d.budget,
e.department_id
from hrapp_department d
join hrapp_employee e on e.department_id = d.id;
""")
all_departments = []
dataset = db_cursor.fetchall()
for row in dataset:
department = Department()
department.id = row['id']
department.department = row['department']
department.budget = row['budget']
# e.department_id = row['department_id']
all_departments.append(department)
template = 'departments/departments_list.html'
context = {'departments': all_departments}
return render(request, template, context)
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
employee = Employee()
employee.id = _row["employee_id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
employee.start_date = _row["start_date"]
employee.department_id = _row["department_id"]
employee.is_supervisor = _row["is_supervisor"]
department = Department()
department.id = _row["department_id"]
department.dept_name = _row["dept_name"]
computer = Computer()
computer.manufacturer = _row["manufacturer"]
computer.make = _row["make"]
computer.id = _row["computer_id"]
# training_program = TrainingProgram()
# training_program.title = _row["title"]
employee.department = department
employee.computer = computer
# employee.training_program = training_program
return employee
def employee_details(request, employee_id):
if request.method == 'GET':
employee = get_employee(employee_id)
template = 'employees/detail.html'
context = {'employee': employee[employee_id]}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
if ("actual_method" in form_data
and form_data["actual_method"] == "PUT"):
employee = Employee.objects.get(pk=employee_id)
employee.first_name = form_data['first']
employee.last_name = form_data['last']
employee.start_date = form_data['start_date']
department = Department()
department.id = form_data['department']
employee.department = department
employee.is_supervisor = form_data['is_supervisor']
employee.save()
elif ("actual_method" in form_data
and form_data["actual_method"] == "DELETE"):
employee = Employee.objects.get(pk=employee_id)
employee.delete()
return redirect(reverse('hrapp:employee_list'))
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 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_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
employee = Employee()
employee.id = _row['employee_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']
department = Department()
department.dept_name = _row['dept_name']
department.id = _row['department_id']
computer = Computer()
computer.make = _row['computer_make']
computer.id = _row['computer_id']
employee.employeecomputer_id = _row['employeecomputer_id']
employee.employee_computer_id = _row['employee_computer_id']
employee.training_programs = []
training_program = Training_program()
training_program.name = _row['training_program_name']
training_program.id = _row['training_program_id']
return (employee, training_program,)
def create_employee_with_department(cursor, row):
_row = sqlite3.Row(cursor, row)
department = Department()
department.id = _row["dept_id"]
department.dept_name = _row["dept_name"]
employee.department = department
return department
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"]
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()
# 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_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 employee_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
e.id,
e.first_name,
e.last_name,
e.is_supervisor,
e.start_date,
e.department_id,
d.name
FROM hrapp_employee e LEFT JOIN hrapp_department d ON e.department_id = d.id;
""")
all_employees = []
dataset = db_cursor.fetchall()
for row in dataset:
employee = Employee()
employee.id = row['id']
employee.first_name = row['first_name']
employee.last_name = row['last_name']
employee.is_supervisor = row['is_supervisor']
employee.start_date = row['start_date']
employee.department_id = row['department_id']
department = Department()
department.id = row['id']
department.name = row['name']
# department.budget = row['budget']
employee.department = department
all_employees.append(employee)
template = 'employees/employees_list.html'
context = {
'employees': all_employees
}
return render(request, template, context)
def all_departments(department_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
d.id,
d.dept_name,
d.budget,
e.id,
e.first_name,
e.last_name,
e.department_id
FROM hrapp_department d
LEFT JOIN hrapp_employee e ON d.id = e.department_id
where d.id = ?
""", (department_id, ))
data = db_cursor.fetchall()
department_list = []
employee_list = []
for row in data:
if (len(department_list) == 0):
department = Department()
department.id = row['department_id']
department.name = row['dept_name']
department.budget = row['budget']
department_list.append(department)
employee = Employee()
employee.id = row['id']
employee.first_name = row['first_name']
employee.last_name = row['last_name']
employee.department_id = row['department_id']
employee_list.append(employee)
department_obj = {}
department_obj["department"] = department_list[0]
department_obj["employees"] = employee_list
return department_obj
def employee_form(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# TODO: Add to query: e.department,
db_cursor.execute("""
select id, dept_name
from hrapp_department
""")
all_departments = []
dataset = db_cursor.fetchall()
for row in dataset:
department = Department()
department.id = row['id']
department.dept_name = row['dept_name']
all_departments.append(department)
template = 'employees/form.html'
context = {'all_departments': all_departments}
return render(request, template, context)
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
d.id,
d.name,
d.budget,
e.id AS 'employee_id'
FROM hrapp_department d
LEFT JOIN hrapp_employee e
ON d.id = e.department_id
""")
all_departments = []
dataset = db_cursor.fetchall()
# creates a list of all unique department objects
for row in dataset:
department = Department()
department.id = row['id']
department.name = row['name']
department.budget = row['budget']
if (department not in all_departments):
all_departments.append(department)
# creates a dictionary of all unique department names with values representing employee count
department_sizes = dict()
for row in dataset:
if (row['name'] not in department_sizes):
name = row['name']
department_sizes[name] = 0
# and here we set the employee count for each department
for row in dataset:
if (row['employee_id'] is not None):
name = row['name']
department_sizes[name] += 1
# transfer the department sizes to the department objects in all_departments
for department in all_departments:
department.size = department_sizes[department.name]
if request.user.is_authenticated:
template = 'departments/department_list.html'
else:
template = 'departments/department_list_view_only.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
(
name, budget
)
VALUES (?, ?)
""", (form_data['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
d.id,
d.dept_name,
d.budget,
e.id AS 'employee_id'
FROM hrapp_department d
LEFT JOIN hrapp_employee e
ON d.id = e.department_id
""")
all_departments = []
dataset = db_cursor.fetchall()
for row in dataset:
department = Department()
department.id = row['id']
department.name = row['dept_name']
department.budget = row['budget']
if(department not in all_departments):
all_departments.append(department)
department_sizes = dict()
for row in dataset:
if(row['dept_name'] not in department_sizes):
name = row['dept_name']
department_sizes[name] = 0
for row in dataset:
if(row['employee_id'] is not None):
name = row['dept_name']
department_sizes[name] += 1
for department in all_departments:
department.size = department_sizes[department.name]
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
(
dept_name, budget
)
VALUES (?, ?)
""",
(form_data['dept_name'], form_data['budget']))
return redirect(reverse('hrapp:department_list'))
