def create_employee_training_programs(cursor, row):
_row = sqlite3.Row(cursor, row)
training_program = TrainingProgram()
training_program.title = _row["title"]
return training_program
def get_training_programs():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
tp.id,
tp.title,
tp.start_date,
tp.end_date,
tp.capacity
FROM hrapp_trainingprogram tp
""")
dataset = db_cursor.fetchall()
past_training_programs = []
future_training_programs = []
for row in dataset:
training_program = TrainingProgram()
training_program.id = row["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"]
if (datetime.strptime(training_program.start_date, "%Y-%m-%d") >
datetime.now()):
future_training_programs.append(training_program)
else:
past_training_programs.append(training_program)
return future_training_programs
def create_training_program(cursor, row):
_row = sqlite3.Row(cursor, row)
t = TrainingProgram()
t.title = _row["title"]
return t
def training_programs_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
tp.id,
tp.title,
tp.description,
tp.start_date,
tp.end_date,
tp.capacity
from hrapp_trainingprogram tp
""")
all_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['id']
training_program.title = row['title']
training_program.description = row['description']
# training_program.start_date = row['start_date']
training_program.start_date = datetime.strptime(
row['start_date'], '%Y-%m-%d')
training_program.end_date = row['end_date']
training_program.capacity = row['capacity']
today = datetime.today()
# if (training_program.start_date > today):
all_programs.append(training_program)
template = 'training_programs/training_programs_list.html'
context = {'programs': all_programs, 'time': today}
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_programs'))
def training_program_past(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
etp.id,
etp.title,
etp.start_date,
etp.end_date,
etp.capacity
FROM hrapp_trainingprogram etp
WHERE etp.start_date < DATE()
""")
all_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
program = TrainingProgram()
program.id = row['id']
program.title = row['title']
program.start_date = row['start_date']
program.end_date = row['end_date']
program.capacity = row['start_date']
all_programs.append(program)
template = 'trainingprograms/list.html'
context = {'programs': all_programs, 'past': True}
return render(request, template, context)
def past_training_list(request):
pass
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
*
from hrapp_trainingprogram ht
""")
past_programs = []
dataset = db_cursor.fetchall()
d = datetime.datetime.today()
current_date = str(d).split(' ')[0]
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['id']
training_program.title = row['title']
training_program.description = row['description']
training_program.start_date = row['start_date']
training_program.end_date = row['end_date']
training_program.capacity = row['capacity']
if (row['start_date'] < current_date):
past_programs.append(training_program)
template = 'training_programs/past_training_list.html'
context = {'trainings': past_programs}
return render(request, template, context)
def training_program_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
t.id,
t.title,
t.start_date,
t.end_date,
t.capacity
from hrapp_trainingprogram t
""")
all_training_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['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']
all_training_programs.append(training_program)
template = 'training_programs/list.html'
upcoming_training_programs = []
for program in all_training_programs:
end_date = datetime.strptime(program.end_date, '%Y-%m-%d')
if end_date > datetime.today():
upcoming_training_programs.append(program)
context = {'upcoming_training_programs': upcoming_training_programs}
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, start_date, end_date, capacity
)
VALUES (?, ?, ?, ?)
""", (form_data['title'], form_data['start_date'],
form_data['end_date'], form_data['capacity']))
return redirect(reverse('hrapp:training_programs'))
def training_program_past(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
t.id,
t.training_title,
t.start_date,
t.end_date,
t.max_capacity
from hrapp_trainingprogram t
where t.start_date < CURRENT_DATE
""")
all_training_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['id']
training_program.training_title = row['training_title']
training_program.start_date = row['start_date']
training_program.end_date = row['end_date']
training_program.max_capacity = row['max_capacity']
all_training_programs.append(training_program)
template = 'training_programs/training_program_past.html'
context = {'training_programs': all_training_programs}
return render(request, template, context)
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 training_program_assign_employee(request, employee_id):
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
*
from hrapp_trainingprogram ht
EXCEPT
SELECT tr.*
FROM hrapp_trainingprogram tr
LEFT JOIN hrapp_trainingprogramemployee te
ON tr.id = te.training_program_id
WHERE te.employee_id = ?
""", (str(employee_id), ))
upcoming_programs = []
dataset = db_cursor.fetchall()
d = datetime.datetime.today()
current_date = str(d).split(' ')[0]
for row in dataset:
if (row['start_date'] > current_date):
training_program = TrainingProgram()
training_program.id = row['id']
training_program.title = row['title']
training_program.start_date = row['start_date']
training_program.end_date = row['end_date']
upcoming_programs.append(training_program)
template = 'employees/employee_assign_training.html'
context = {
'trainings': upcoming_programs,
'employee_id': employee_id
}
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_trainingprogramemployee
(
employee_id, training_program_id
)
VALUES (?, ?)
""", (employee_id, form_data['training']))
return redirect(reverse('hrapp:employee_list'))
def training_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
*
from hrapp_trainingprogram ht
""")
all_programs = []
dataset = db_cursor.fetchall()
d = datetime.datetime.today()
current_date = str(d).split(' ')[0]
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['id']
training_program.title = row['title']
training_program.description = row['description']
training_program.start_date = row['start_date']
training_program.end_date = row['end_date']
training_program.capacity = row['capacity']
if (row['start_date'] > current_date):
all_programs.append(training_program)
if request.user.is_authenticated:
template = 'training_programs/training_list.html'
else:
template = 'training_programs/training_list_view_only.html'
context = {'trainings': all_programs}
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 create_program(cursor, row):
_row = sqlite3.Row(cursor, row)
programslist = list()
program = TrainingProgram()
program.id = _row["id"]
program.title = _row["title"]
program.start_date = _row["start_date"]
program.end_date = _row["end_date"]
programslist.append(program)
return programslist
def training_program_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
t.id,
t.training_title,
t.start_date,
t.end_date,
t.max_capacity
from hrapp_trainingprogram t
where t.start_date >= CURRENT_DATE
""")
all_training_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['id']
training_program.training_title = row['training_title']
training_program.start_date = row['start_date']
training_program.end_date = row['end_date']
training_program.max_capacity = row['max_capacity']
all_training_programs.append(training_program)
template = 'training_programs/training_program_list.html'
context = {'training_programs': all_training_programs}
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
(
training_title, start_date, end_date, max_capacity
)
VALUES (?, ?, ?, ?)
""", (form_data['training_title'], form_data['start_date'],
form_data['end_date'], form_data['max_capacity']))
return redirect(reverse('hrapp:training_program_list'))
def training_program_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
etp.id,
etp.title,
etp.start_date,
etp.end_date,
etp.capacity
FROM hrapp_trainingprogram etp
WHERE etp.start_date >= DATE()
""")
all_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
program = TrainingProgram()
program.id = row['id']
program.title = row['title']
program.start_date = row['start_date']
program.end_date = row['end_date']
program.capacity = row['start_date']
all_programs.append(program)
template = 'trainingprograms/list.html'
context = {'programs': all_programs}
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, start_Date, end_date, capacity
)
VALUES (?, ?, ?, ?)
""", (form_data['title'], form_data['start_date'],
form_data['end_date'], form_data['capacity']))
return redirect(reverse('hrapp:training'))
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_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 past_program_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
tp.id,
tp.title,
tp.start_date,
tp.end_date,
tp.capacity,
tp.description
from hrapp_trainingprogram tp
""")
future_training_programs = []
past_training_programs = []
todaydate = datetime.today().strftime("%Y-%m-%d")
dataset = db_cursor.fetchall()
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['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']
if(training_program.start_date > todaydate):
future_training_programs.append(training_program)
else:
past_training_programs.append(training_program)
template = 'trainingprograms/past.html',
context = {
'past_training_programs': past_training_programs,
'future_training_programs': future_training_programs
}
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, start_date, end_date,
capacity, description
)
VALUES (?, ?, ?, ?, ?)
""",
(form_data['title'], form_data['start_date'],
form_data['end_date'], form_data['capacity'],
form_data["description"]))
return redirect(reverse('hrapp:trainingprograms'))
def training_program_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
tp.id,
tp.title,
tp.start_date,
tp.end_date,
tp.capacity
from hrapp_trainingprogram tp
order by tp.end_date asc
""")
all_training_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['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.is_future = is_future_training(
training_program.start_date)
all_training_programs.append(training_program)
template = 'training_programs/training_programs_list.html'
context = {'training_programs': all_training_programs}
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, start_date, end_date, capacity
)
values
(
?, ?, ?, ?
)
""",
(
form_data["title"],
form_data["start_date"],
form_data["end_date"],
form_data["capacity"],
),
)
return redirect(reverse("hrapp:training_program_list"))
def training_program_details(request, training_program_id):
if request.method == "GET":
training_program_details = get_training_program_details(
training_program_id)
employee_training = TrainingProgram()
for row in training_program_details:
if employee_training.title == "":
employee_training.id = row['id']
employee_training.title = row['title']
employee_training.start_date = row['start_date']
employee_training.end_date = row['end_date']
employee_training.capacity = row['capacity']
employee_training.is_future = is_future_training(
employee_training.start_date)
employee_training.attendees = []
if row['employee_id']:
employee_training.attendees.append(
(row['employee_id'],
f"{row['first_name']} {row['last_name']}"))
else:
employee_training.attendees.append(
(row['employee_id'],
f"{row['first_name']} {row['last_name']}"))
template = "training_programs/training_programs_detail.html"
context = {"training_details": employee_training}
return render(request, template, context)
if request.method == 'POST':
form_data = request.POST
if ("actual_method" in form_data
and form_data["actual_method"] == "DELETE"):
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
DELETE FROM hrapp_trainingprogram
WHERE id = ?
""", (training_program_id, ))
return redirect(reverse('hrapp:training_program_list'))
# Check if this POST is for editing a book
if "actual_method" in form_data and form_data["actual_method"] == "PUT":
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
UPDATE hrapp_trainingprogram
SET title = ?,
start_date = ?,
end_date = ?,
capacity = ?
WHERE id = ?
""",
(
form_data["title"],
form_data["start_date"],
form_data["end_date"],
form_data["capacity"],
training_program_id,
),
)
return redirect(
reverse("hrapp:training_program_details",
kwargs={'training_program_id': training_program_id}))
def training_program_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
tp.id,
tp.title,
tp.start_date,
tp.end_date,
tp.capacity
FROM hrapp_trainingprogram tp;
""")
all_training_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
training_program = TrainingProgram()
training_program.id = row['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']
all_training_programs.append(training_program)
template = 'training_programs/list.html'
# SORTING PROGRAMS INTO PAST AND FUTURE
future_training_programs = []
past_training_programs = []
for training_program in all_training_programs:
end_date = datetime.strptime(training_program.end_date, '%Y-%m-%d')
if end_date > datetime.today():
future_training_programs.append(training_program)
else:
past_training_programs.append(training_program)
context = {
'past_training_programs': past_training_programs,
'future_training_programs': future_training_programs
}
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_programs'))
def training_program_list(request):
if request.method == 'GET':
# Connection.py holds db_path to specific db
with sqlite3.connect(Connection.db_path) as conn:
# Row method makes into row factory
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
select
tp.id,
tp.title,
tp.start_date,
tp.end_date,
tp.capacity
from hrapp_trainingprogram tp
ORDER BY tp.start_date
""")
past_training_programs = []
future_training_programs = []
dataset = db_cursor.fetchall()
for row in dataset:
training_program = TrainingProgram()
training_program.id = row["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"]
if (datetime.strptime(training_program.start_date, "%Y-%m-%d")
> datetime.now()):
future_training_programs.append(training_program)
else:
past_training_programs.append(training_program)
template = 'training_programs/training_program_list.html'
context = {
'past_training_programs': past_training_programs,
'future_training_programs': future_training_programs
}
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, start_date, end_date, capacity
)
VALUES (?, ?, ?, ?)
""", (form_data['title'], form_data['start_date'],
form_data['end_date'], form_data['capacity']))
return redirect(reverse('hrapp:training_program_list'))