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 create_employee_computers(cursor, row):
_row = sqlite3.Row(cursor, row)
computer = Computer()
computer.manufacturer = _row["comp_manufacturer"]
computer.make = _row["comp_make"]
computer.id = _row["comp_id"]
employee.computer = computer
return computer
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 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_computer(computer_id):
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,
ec.unassigned_date,
e.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 ec.unassigned_date is NULL
AND c.id = ?
""", (str(computer_id), ))
computer_data = db_cursor.fetchone()
computer = Computer()
computer.id = computer_data['id']
computer.make = computer_data['make']
computer.purchase_date = computer_data['purchase_date']
computer.decommission_date = computer_data['decommission_date']
if computer_data['decommission_date'] is None:
computer.decommission_date = 'Still in Use'
if computer_data['unassigned_date'] is None:
if computer_data["first_name"] is not None:
computer.current_user = f"{computer_data['first_name']} {computer_data['last_name']}"
else:
computer.current_user = "******"
else:
computer.current_user = "******"
return computer
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.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.purchase_date = row['purchase_date']
computer.decommission_date = row['decommission_date']
all_computers.append(computer)
template = 'computers/list.html'
context = {
'all_computers': all_computers
}
return render(request, template, context)
def create_computer(cursor, row):
_row = sqlite3.Row(cursor, row)
computer = Computer()
computer.id = _row["id"]
computer.manufacturer = _row["manufacturer"]
computer.make = _row["make"]
computer.purchase_date = _row["purchase_date"]
computer.decommission_date = _row["decommission_date"]
return computer
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.manufacturer,
c.model,
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.manufacturer = row['manufacturer']
computer.model = row['model']
computer.purchase_date = row['purchase_date']
computer.decommission_date = row['decommission_date']
all_computers.append(computer)
template = 'computers/list.html'
context = {
'computers': all_computers
}
#In DJANGO you have to manually wire up URLs
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
(
purchase_date, manufacturer, model
)
VALUES (?, ?, ?)
""",
(form_data['purchase_date'], form_data['manufacturer'], form_data['model']))
return redirect(reverse('hrapp:computers'))
def create_computer(cursor, row):
_row = sqlite3.Row(cursor, row)
computer = Computer()
computer.id = _row["id"]
computer.make = _row["make"]
computer.model = _row["model"]
computer.purchase_date = _row["purchase_date"]
return computer
def create_computer(cursor, row):
_row = sqlite3.Row(cursor, row)
computer = Computer()
computer.id = _row['id']
computer.make = _row['make']
computer.purchase_date = _row['purchase_date']
computer.manufacturer = _row['manufacturer']
return computer
def create_computer(cursor, row):
_row = sqlite3.Row(cursor, row)
computer = Computer()
computer.id = _row['id']
computer.manufacturer = _row['manufacturer']
computer.model = _row['model']
computer.purchase_date = _row['purchase_date']
computer.decommission_date = _row['decommission_date']
# employee = Employee()
# employee.id = _row['id']
# employee.first_name = _row['first_name']
# employee.last_name = _row['last_name']
# computer.employee = employee
return computer
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 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.manufacturer,
c.model,
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.manufacturer = row['manufacturer']
computer.model = row['model']
computer.purchase_date = row['purchase_date']
computer.decommission_date = row['decommission_date']
all_computers.append(computer)
template = 'computers/computer_list.html'
context = {'all_computers': all_computers}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
last_id = None
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
start_date = datetime.today().strftime("%Y/%m/%d")
nothing = None
db_cursor.execute(
"""
INSERT INTO hrapp_computer
(
manufacturer, model, purchase_date,
decommission_date
)
VALUES (?, ?, ?, ?)
""", (form_data['manufacturer'], form_data['model'], start_date,
nothing))
db_cursor.execute("""
select last_insert_rowid()
""")
last_id = db_cursor.fetchone()
if form_data['employee'] != 'Null':
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
start_date = datetime.today().strftime("%Y/%m/%d")
nothing = None
db_cursor.execute(
"""
INSERT INTO hrapp_employeecomputer
(
assigned_date, unassigned_date, computer_id,
employee_id
)
VALUES (?, ?, ?, ?)
""", (start_date, nothing, last_id[0], form_data['employee']))
return redirect(reverse('hrapp:computer_list'))
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)
def get_computer(computer_id):
"""
Queries the database to get information about a computer
Returns a Computer instance. Calls never_assigned and currently_assigned to
determine how to instantiate the Computer instance that will be returned.
Arugments:
computer_id: integer
Author: Ryan Crowley
"""
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.model,
c.purchase_date,
c.decommission_date,
e.first_name,
e.last_name,
ec.unassigned_date
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, ))
data_set = db_cursor.fetchall()
for row in data_set:
if currently_assigned(row['id']):
if not row['unassigned_date']:
computer = Computer()
computer.id = row['id']
computer.make = row['make']
computer.model = row['model']
computer.purchase_date = row['purchase_date']
computer.decommission_date = row['decommission_date']
computer.first_name = row['first_name']
computer.last_name = row['last_name']
computer.never_assigned = never_assigned(computer.id)
return computer
else:
computer = Computer()
computer.id = row['id']
computer.make = row['make']
computer.model = row['model']
computer.purchase_date = row['purchase_date']
computer.decommission_date = row['decommission_date']
computer.never_assigned = never_assigned(computer.id)
return computer
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
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.purchase_date = row['purchase_date']
# computer.decommission_date = row['decommission_date']
# if row['unassigned_date'] is None:
# if row["first_name"] is not None:
# computer.current_user = f"{row['first_name']} {row['last_name']}"
# else:
# computer.current_user = "******"
# else:
# computer.current_user = "******"
all_computers.append(computer)
if request.user.is_authenticated:
template = 'computers/list.html'
else:
template = 'computers/list_view_only.html'
context = {'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()
today = datetime.datetime.today()
print(form_data)
employee_id = form_data['employee']
db_cursor.execute(
"""
INSERT INTO hrapp_computer
(
make, purchase_date
)
VALUES (?, ?)
""", (form_data['make'], today))
computer_id = str(db_cursor.lastrowid)
db_cursor.close()
print(computer_id, employee_id)
assign_computer(computer_id, employee_id)
return redirect(reverse('hrapp:computers'))