def get_employees():
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.department_id,
d.name,
ec.assign_date
FROM hrapp_employee AS e
JOIN hrapp_department AS d ON e.department_id = d.id
LEFT JOIN hrapp_employeecomputer AS ec ON ec.employee_id = e.id
WHERE ec.employee_id IS NULL
""")
all_employees = []
dataset = db_cursor.fetchall()
print(len(dataset))
for row in dataset:
employee = Employee()
employee.id = row['id']
employee.first_name = row['first_name']
employee.last_name = row['last_name']
employee.department_name = row['name']
all_employees.append(employee)
return all_employees
def get_employee(employee_id):
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
e.id,
e.first_name,
e.last_name,
e.start_date,
e.is_supervisor,
d.id,
d.name
FROM hrapp_employee AS e
JOIN hrapp_department AS d ON e.department_id = d.Id
WHERE e.id = ?
""", (employee_id, ))
response = db_cursor.fetchone()
print(response)
employee = Employee()
employee.id = response[0]
employee.first_name = response[1]
employee.last_name = response[2]
employee.start_date = response[3]
employee.is_supervisor = response[4]
employee.department_id = response[5]
employee.department_name = response[6]
return employee
def get_assigned_employee(computer_id):
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.first_name,
e.last_name,
d.name,
ec.assign_date
FROM hrapp_employeecomputer AS ec
JOIN hrapp_employee AS e ON ec.employee_id = e.id
JOIN hrapp_department AS d ON e.department_id = d.id
WHERE ec.computer_id = ?
""", (computer_id,))
response = db_cursor.fetchone()
print(response)
if response:
employee = Employee()
employee.first_name = response[0]
employee.last_name = response[1]
employee.department_name = response[2]
employee.assigned_computer_date = response[3]
return employee
else:
return None
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()
# TODO: Add to query: e.department,
db_cursor.execute("""
select
e.id,
e.first_name,
e.last_name,
e.start_date,
e.is_supervisor,
e.department_id,
d.id as department_id,
d.department_name
from hrapp_employee e
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.start_date = row['start_date']
employee.is_supervisor = row['is_supervisor']
employee.department_name = row['department_name']
all_employees.append(employee)
template = 'employees/employees_list.html'
context = {'employees': all_employees}
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_employee
(
first_name, last_name, start_date, is_supervisor, department_id
)
VALUES (?, ?, ?, ?, ?)
""", (form_data['first_name'], form_data['last_name'],
form_data['start_date'], form_data['is_supervisor'],
form_data["department_id"]))
return redirect(reverse('hrapp:employee_list'))
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
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.department_name = _row["department_name"]
return employee
def department_details(request, department_id):
if request.method == 'GET':
employees = get_employees(department_id)
employee_list = []
for row in employees:
employee = Employee()
employee.first_name = row['first_name']
employee.last_name = row['last_name']
employee.department_name = row['department_name']
employee_list.append(employee)
template = 'departments/department_detail.html'
context = {
'employees': employee_list,
'dept_name': employee_list[0].department_name
}
return render(request, template, context)
def computer_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
c.id,
c.make,
c.manufacturer,
c.purchase_date,
c.decommission_date,
ec.assign_date
FROM hrapp_computer AS c
LEFT JOIN hrapp_employeecomputer AS ec ON c.id = ec.computer_id
ORDER BY c.manufacturer
""")
all_computers = []
dataset = db_cursor.fetchall()
for i, row in enumerate(dataset):
computer = Computer()
computer.id = row['id']
computer.make = row['make']
computer.manufacturer = row['manufacturer']
computer.purchase_date = row['purchase_date']
computer.decommission_date = row['decommission_date']
computer.is_assigned = row['assign_date']
all_computers.append(computer)
db_cursor.execute("""
SELECT
e.id,
e.first_name,
e.last_name,
e.department_id,
d.name,
ec.assign_date
FROM hrapp_employee AS e
JOIN hrapp_department AS d ON e.department_id = d.id
LEFT JOIN hrapp_employeecomputer AS ec ON ec.employee_id = e.id
WHERE ec.employee_id IS NULL
""")
all_employees = []
dataset = db_cursor.fetchall()
print(len(dataset))
for row in dataset:
employee = Employee()
employee.id = row['id']
employee.first_name = row['first_name']
employee.last_name = row['last_name']
employee.department_name = row['name']
all_employees.append(employee)
template = 'computers/list.html'
context = {'all_computers': all_computers, 'employees': all_employees}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
select
c.id,
c.make,
c.manufacturer,
c.purchase_date,
c.decommission_date
from hrapp_computer c
""")
all_computers = []
dataset = db_cursor.fetchall()
for row in dataset:
computer = Computer()
computer.id = row['id']
computer.make = row['make']
computer.manufacturer = row['manufacturer']
computer.purchase_date = row['purchase_date']
computer.decommission_date = row['decommission_date']
if computer.make.upper() == form_data['searchfield'].upper(
) or computer.manufacturer.upper(
) == form_data['searchfield'].upper():
all_computers.append(computer)
elif form_data['searchfield'] == "":
all_computers.append(computer)
template = 'computers/list.html'
context = {'all_computers': all_computers}
return render(request, template, context)