def past_programs(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
today = datetime.datetime.today()
conn.row_factory = model_factory(Training)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
t.id,
t.title,
t.start_date
FROM hrapp_training t
""")
all_training_programs = db_cursor.fetchall()
past = list()
for program in all_training_programs:
if today >= datetime.datetime.strptime(program.start_date, '%Y/%m/%d'):
past.append(program)
template = 'training_programs/past_list.html'
context = {
'all_training_programs': past
}
return render(request, template, context)
def get_all_employees():
"""
This function gets all of the employee data from hrapp_employee
"""
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Employee)
db_cursor = conn.cursor()
'''
Joins employee table with department table
returns
- Employee Id
- Employee First Name
- Employee Last Name
- Employee Start Date
- Employee is supervisor
- Employee Department
'''
# Got rid of join
db_cursor.execute("""
SELECT
*
FROM
hrapp_employee e
""")
return db_cursor.fetchall()
def get_computer(computer_id):
# connect to the database
with sqlite3.connect(Connection.db_path) as conn:
# set row parameters and then set up the database cursor
conn.row_factory = model_factory(Computer)
db_cursor = conn.cursor()
# database select
db_cursor.execute(
"""
SELECT c.id,
c.make,
c.manufacturer,
c.purchase_date,
c.decommission_date,
ec.assign_date,
ec.unassign_date,
e.first_name,
e.last_name,
COUNT(e.first_name) count
from hrapp_computer c
left join hrapp_employeecomputer ec on ec.computer_id = c.id
left join hrapp_employee e on ec.employee_id = e.id
where c.id = ?
""", (computer_id, ))
# return the results from the fetch call
data = db_cursor.fetchall()[-1]
return data
def get_employee(employee_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Employee)
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,
d.dept_name,
ec.id comp_join_id,
ec.computer_id,
ec.employee_id,
c.manufacturer,
c.model
from hrapp_employee e
left join hrapp_department d on d.id = e.department_id
left join hrapp_employeecomputer ec on ec.employee_id = e.id
left join hrapp_computer c on c.id = ec.computer_id
where e.id = ?
""", (employee_id, ))
return db_cursor.fetchone()
def department_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Department)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT COUNT(e.id) emp_count, dept_name, budget, d.id
FROM hrapp_department d
LEFT JOIN hrapp_employee e
ON e.department_id = d.id
GROUP BY d.id;
""")
all_departments = db_cursor.fetchall()
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'))
def employee_attendees(request, training_program_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(TrainingProgramEmployee)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
tp.id,
tp.title,
tp.start_date,
tp.end_date,
tp.capacity,
e.id employee_id,
e.first_name,
e.last_name,
e.start_date,
etp.id,
etp.employee_id,
etp.training_program_id
FROM hrapp_trainingprogramemployee etp
JOIN hrapp_trainingprogram tp ON tp.id = etp.training_program_id
JOIN hrapp_employee e ON etp.employee_id = e.id
WHERE tp.id = ?
""", (training_program_id, ))
data = db_cursor.fetchall()
template_name = 'training_programs/training_programs_attendees.html'
context = {'attendees': data}
return render(request, template_name, context)
def get_employee(employee_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Employee)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.first_name,
e.last_name,
e.start_date,
e.is_supervisor,
d.dept_name,
d.budget,
d.id department_id,
c.id computer_id,
c.manufacturer,
c.model,
etp.employee_id,
tp.id training_program_id,
tp.title,
tp.start_date training_start_date,
tp.end_date
from hrapp_employee e
left join hrapp_department d on e.department_id = d.id
left join hrapp_employeecomputer ec on e.id = ec.employee_id
left join hrapp_computer c on c.id = ec.computer_id
left join hrapp_employeetrainingprogram etp on e.id = etp.employee_id
left join hrapp_trainingprogram tp on tp.id = etp.training_program_id
where e.id = ?
""", (employee_id,))
return db_cursor.fetchone()
def computer_search(request):
if request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Computer)
db_cursor = conn.cursor()
db_cursor.execute("""
select
c.id,
c.make,
c.model,
c.purchase_date,
c.decommission_date
from hrapp_computer c
""")
all_computers = db_cursor.fetchall()
computer_results = []
for computer in all_computers:
if form_data["search"].lower() in computer.make.lower(
) or form_data["search"].lower() in computer.model.lower():
computer_results.append(computer)
template = 'computers/computer_list.html'
context = {'all_computers': computer_results}
return render(request, template, context)
def get_availible_computers():
"""
This function grabs all computers that are not assigned to an employee
"""
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Computer)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
*
FROM
hrapp_computer ec
""")
dataset = db_cursor.fetchall()
computer_dict = dict()
for row in dataset:
computer_dict[row.id] = row
new_dataset = []
conn.row_factory = model_factory(EmployeeComputer)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
*
FROM
hrapp_employeecomputer ec
""")
dataset = db_cursor.fetchall()
for row in dataset:
computer_dict[row.computer_id] = row
new_dataset = []
for id, relationship in computer_dict.items():
if hasattr(relationship, "unassign_date"):
if relationship.unassign_date:
new_dataset.append(computer_dict[id].computer)
else:
new_dataset.append(computer_dict[id])
return new_dataset
def get_training_program_employees():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(TrainingProgramEmployee)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT *
FROM hrapp_trainingprogramemployee tpe
""")
return db_cursor.fetchall()
def employee_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Employee)
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,
d.dept_name
from hrapp_employee e
join hrapp_department d on e.department_id = d.id
""")
all_employees = 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.dept_name = row['dept_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:employees'))
def get_computers():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Department)
db_cursor = conn.cursor()
db_cursor.execute("""
select
c.id,
c.make
from hrapp_computer c
""")
return db_cursor.fetchall()
def get_departments():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Department)
db_cursor = conn.cursor()
db_cursor.execute("""
select
d.id,
d.dept_name,
d.budget
from hrapp_department d
""")
return db_cursor.fetchall()
def get_departments():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Department)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
d.id,
d.name
FROM hrapp_department d
""")
return db_cursor.fetchall()
def get_avail_computers():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Computer)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
c.id,
c.make
FROM hrapp_computer c
""")
return db_cursor.fetchall()
def get_last_computer():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Computer)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT *
from hrapp_computer c
""")
data = db_cursor.fetchall()[-1]
return data
def get_employee():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Employee)
db_cursor = conn.cursor()
db_cursor.execute("""
select
e.id,
e.first_name,
e.last_name
from hrapp_employee e
""")
return db_cursor.fetchall()
def get_training_programs():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(TrainingProgram)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
tp.id,
tp.title,
tp.start_date,
tp.end_date,
tp.capacity
FROM hrapp_trainingprogram tp
""")
return db_cursor.fetchall()
def get_employees():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Employee)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.first_name,
e.last_name,
e.start_date,
e.is_supervisor
FROM hrapp_employee e
""")
return db_cursor.fetchall()
def get_department(department_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Department)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
d.id department_id,
d.dept_name,
d.budget
FROM hrapp_department d
WHERE d.id = ?
""", (department_id,))
return db_cursor.fetchone()
def get_employee(employee_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Employee)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT * FROM hrapp_employee e
WHERE e.id = ?
""", (employee_id, ))
dataset = db_cursor.fetchone()
return dataset
def get_employeecomputers():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(EmployeeComputer)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
ec.id,
ec.computer_id,
ec.employee_id,
ec.unassign_date
FROM hrapp_employeecomputer ec
""")
return db_cursor.fetchall()
def get_all_departments():
"""
This function gets all of the department data from hrapp_department
"""
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Department)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
*
FROM
hrapp_department
""")
return db_cursor.fetchall()
def employee_form(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Department)
db_cursor = conn.cursor()
db_cursor.execute("""
select
d.id,
d.dept_name,
d.budget
from hrapp_department d
""")
dataset = db_cursor.fetchall()
template = "employees/form.html"
context = {"departments": dataset}
return render(request, template, context)
def get_computers():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Computer)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
c.id,
c.manufacturer,
c.model,
c.purchase_date,
c.decommission_date
FROM hrapp_computer c;
""")
return db_cursor.fetchall()
def get_programs():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(TrainingProgram)
db_cursor = conn.cursor()
db_cursor.execute("""
select
t.id,
t.title,
t.start_date,
t.end_date,
t.capacity
from hrapp_trainingprogram t
""")
return db_cursor.fetchall()
def get_employee_programs(employee_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(EmployeeTrainingProgram)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT * FROM hrapp_employeetrainingprogram etp
WHERE etp.employee_id = ?
""", (employee_id, ))
dataset = db_cursor.fetchall()
program_list = []
for item in dataset:
program_list.append(item)
return program_list
def get_employees(program_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Employee)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.first_name,
e.last_name
FROM hrapp_employee e
JOIN hrapp_employeetraining et
ON et.employee_id_id = e.id
WHERE et.training_id_id = ?
""", (program_id,))
return db_cursor.fetchall()
def training_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(TrainingProgram)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
tp.id,
tp.title,
tp.description,
tp.start_date,
tp.end_date,
tp.capacity
from hrapp_trainingprogram tp
""")
all_trainings = db_cursor.fetchall()
template = 'training_programs/training_list.html'
context = {
'all_trainings': all_trainings
}
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_trainingprogram
(
title, description, start_date,
end_date, capacity
)
VALUES (?, ?, ?, ?, ?)
""",
(form_data['title'], form_data['description'],
form_data['start_date'], form_data['end_date'],
form_data['capacity']))
return redirect(reverse('hrapp:training_list'))
def get_programs(program_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Training)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
t.id,
t.title,
t.start_date,
t.end_date,
t.capacity
FROM hrapp_training t
WHERE t.id = ?
""", (program_id,))
return db_cursor.fetchone()
