def get_program(program_id):
with sqlite3.connect(Connection.db_path) as conn:
# conn.row_factory = create_program
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
p.id,
p.title,
p.start_date,
p.end_date,
p.capacity,
e.first_name,
e.last_name,
etp.id AS etpid,
etp.employee_id,
etp.training_program_id
FROM hrapp_trainingprogram p
LEFT JOIN hrapp_employeetrainingprogram etp ON etp.training_program_id = p.id
LEFT JOIN hrapp_employee e ON e.id = etp.employee_id
WHERE p.id = ?
""", (program_id, ))
resp = db_cursor.fetchall()
tprogram = TrainingProgram()
tprogram.employees_names = []
for tuples in resp:
if tprogram.id == None:
tprogram.id = tuples[0]
tprogram.title = tuples[1]
tprogram.start_date = tuples[2]
tprogram.end_date = tuples[3]
tprogram.capacity = tuples[4]
if datetime.strptime(tprogram.start_date,
"%Y-%m-%d") > datetime.now():
tprogram.past = True
else:
tprogram.past = False
employee = Employee()
employee.id = tuples[7]
employee.first_name = tuples[5]
employee.last_name = tuples[6]
tprogram.employees_names.append(employee)
else:
employee = Employee()
employee.id = tuples[7]
employee.first_name = tuples[5]
employee.last_name = tuples[6]
tprogram.employees_names.append(employee)
return tprogram
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 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.dept_name = _row['dept_name']
if _row['model'] is not None:
employee.computer = _row['manufacturer'] + ' ' + _row['model']
else:
employee.computer = "Not Assigned"
employee.start_date = _row['start_date']
employee.is_supervisor = _row['is_supervisor']
if _row["training"] is not None:
employee.training = _row["training"]
else:
employee.training = "Not Assigned"
employee.trainings = []
return (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_employees(department_id):
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.start_date,
e.department_id
FROM hrapp_employee e
WHERE e.department_id = ?
""", (department_id, ))
dep_employees = []
data = db_cursor.fetchall()
for row in data:
employee = Employee()
employee.id = row['id']
employee.first_name = row['first_name']
employee.last_name = row['last_name']
dep_employees.append(employee)
return dep_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_employees():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.first_name,
e.last_name,
e.id
from hrapp_employee e
EXCEPT
SELECT
e.first_name,
e.last_name,
e.id
from hrapp_employee e
LEFT JOIN hrapp_employeecomputer ec
WHERE ec.employee_id = e.id
AND unassigned_date is NULL
""")
employee_data = db_cursor.fetchall()
unassigned_employees = []
for employee in employee_data:
single_employee = Employee()
single_employee.first_name = employee['first_name']
single_employee.last_name = employee['last_name']
single_employee.id = employee['id']
unassigned_employees.append(single_employee)
return unassigned_employees
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.start_date,
e.is_supervisor,
e.department_id
FROM hrapp_employee e
""")
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_id = row['department_id']
all_employees.append(employee)
template = 'employees/employees_list.html'
context = {'employees': all_employees}
return render(request, template, context)
def get_employees(employee_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
select
e.id employee_id,
e.first_name,
e.last_name,
e.start_date,
e.is_supervisor,
e.department_id
from hrapp_employee e
WHERE e.id = ?
""", (employee_id,))
row = db_cursor.fetchall()
print('thee dataset', row)
employee = Employee()
employee.id = row[0]['employee_id']
employee.first_name = row[0]['first_name']
employee.last_name = row[0]['last_name']
employee.start_date = row[0]['start_date']
employee.is_supervisor = row[0]['is_supervisor']
employee.department_id = row[0]['department_id']
return 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 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 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 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 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,
d.department,
e.department_id
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_thing = row['department']
all_employees.append(employee)
template = 'employees/employees_list.html'
context = {
'all_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'], False, form_data['department']))
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']
return employee
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 create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
department_employee = {}
employee = Employee()
employee.id = _row["e_id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
department_employee["employee"] = employee
department_employee["department_name"] = _row["department_name"]
return department_employee
def search_results(request):
if 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.purchase_date,
c.decommission_date,
c.manufacturer,
ec.computer_id,
ec.employee_id,
e.first_name,
e.last_name
FROM hrapp_computer c
LEFT JOIN hrapp_employeecomputer ec
ON c.id = ec.computer_id
LEFT JOIN hrapp_employee e
ON ec.employee_id = e.id
WHERE c.make like ? OR c.manufacturer like ?
""", (
form_data['search_term'],
form_data['search_term'],
))
all_computers = []
dataset = db_cursor.fetchall()
for row in dataset:
computer = Computer()
computer.id = row['id']
computer.make = row['make']
computer.purchase_date = row['purchase_date']
computer.decommission_date = row['decommission_date']
computer.manufacturer = row['manufacturer']
employee = Employee()
employee.first_name = row['first_name']
employee.last_name = row['last_name']
computer.employee = employee
all_computers.append(computer)
template = 'computers/list.html'
context = {'all_computers': all_computers}
return render(request, template, context)
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
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
e = Employee()
e.id = _row["id"]
e.first_name = _row["first_name"]
e.last_name = _row["last_name"]
d = Department()
d.name = _row["department"]
e.department = d
return e
def create_computer(cursor, row):
_row = sqlite3.Row(cursor, row)
computer = Computer()
computer.id = _row["computer_id"]
computer.make = _row["make"]
computer.purchase_date = _row["purchase_date"]
computer.decommission_date = _row["decommission_date"]
employee = Employee()
employee.id = _row["employee_id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
return (computer, employee)
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 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 create_training_program(cursor, row):
_row = sqlite3.Row(cursor, row)
training_program = TrainingProgram()
training_program.id = _row["training_program_id"]
training_program.title = _row["title"]
training_program.start_date = _row["start_date"]
training_program.end_date = _row["end_date"]
training_program.capacity = _row["capacity"]
training_program.description = _row["description"]
employee = Employee()
employee.id = _row["employee_id"]
employee.first_name = _row["first_name"]
employee.last_name = _row["last_name"]
training_program.employee = employee
return training_program
def create_employee(cursor, row):
_row = sqlite3.Row(cursor, row)
e = Employee()
e.id = _row["id"]
e.first_name = _row["first_name"]
e.last_name = _row["last_name"]
d = Department()
d.name = _row["department"]
c = Computer()
c.id = _row["computer_id"]
c.make = _row["computer"]
e.department = d
e.computer = c
return e
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 create_employee_training(cursor, row):
_row = sqlite3.Row(cursor, row)
tp = TrainingProgram()
tp.id = _row["id"]
tp.title = _row["title"]
tp.start_date = _row["start_date"]
tp.end_date = _row["end_date"]
tp.capacity = _row["capacity"]
tp.employees = []
employee = Employee()
if _row["employee_first_name"] is not None:
employee.first_name = _row["employee_first_name"]
employee.last_name = _row["employee_last_name"]
return (
tp,
employee,
)
def employee_list(request):
if request.method == 'GET':
with sqlite3.connect(
"/Users/joeshep/workspace/python/bangazon-workforce-boilerplate/bangazonworkforcemgt/db.sqlite3"
) 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
from hrapp_employee e
""")
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 = row['department']
all_employees.append(employee)
template = 'employees/employees_list.html'
context = {'employees': all_employees}
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)