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
FROM hrapp_employee e;
""")
return db_cursor.fetchall()
def get_computer(computer_id):
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.purchase_date,
c.decommission_date,
c.manufacturer
FROM hrapp_computer c
WHERE c.id = ?
""", (computer_id,))
return db_cursor.fetchone()
def get_employee_computer():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Computer)
db_cursor = conn.cursor()
db_cursor.execute('''
SELECT
c.id AS computer_id,
c.make,
c.manufacturer,
ec.employee_id,
e.first_name,
e.last_name
FROM hrapp_computer c
JOIN hrapp_employeecomputer ec
JOIN hrapp_employee e
WHERE ec.computer_id = c.id;
''')
return db_cursor.fetchall()
def get_training_program(training_program_id):
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
WHERE tp.id = ?
""",
(training_program_id, ),
)
return db_cursor.fetchone()
def computer_list(request):
if request.method == 'GET':
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;
''')
all_computers = db_cursor.fetchall()
db_cursor.execute('''
SELECT
c.id,
c.make,
c.purchase_date,
c.manufacturer,
c.decommission_date,
ec.employee_id,
ec.computer_id,
e.id,
e.first_name,
e.last_name
FROM hrapp_computer c
JOIN hrapp_employee e
JOIN hrapp_employeecomputer ec ON e.id = ec.employee_id
WHERE ec.computer_id = c.id;
''')
employee_computer_list = db_cursor.fetchall()
all_computer_keys = list(dict.fromkeys(employee_computer_list))
template = 'computers/list.html'
context = {
'all_computer_keys': all_computer_keys,
'all_computers': all_computers
}
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_computer
(
make,
purchase_date,
manufacturer
)
VALUES (?, ?, ?);
""", (form_data['make'], form_data['purchase_date'],
form_data['manufacturer']))
new_computer_id = db_cursor.lastrowid
today = date.today()
db_cursor.execute(
"""
INSERT INTO hrapp_employeecomputer
(
computer_id,
employee_id,
assign_date
)
VALUES (?, ?, ?);
""", (new_computer_id, form_data['employee_id'], today))
return redirect(reverse('hrapp:computers'))