def __create_base_account(data):
account_id = None
connection = get_db_connection()
try:
password_hash = generate_password_hash(data['password'])
account_id = None
with connection.cursor() as cursor:
sql = ('INSERT INTO account('
'username, password, '
'firstName, middleInitial, lastName, '
'isActive, accountType'
') VALUES(%s, %s, %s, %s, %s, %s, %s)')
cursor.execute(
sql, (data['username'], password_hash, data['first_name'],
data['middle_initial'], data['last_name'],
data['active'], data['account_type']))
account_id = cursor.lastrowid
if account_id:
connection.commit()
finally:
connection.close()
return account_id
def get_employee_details(id):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = (
"SELECT e.id as employeeId, a.id as accountId, ep.positionTitle, sd.name, "
"a.firstName, a.lastName, a.middleInitial from employee e INNER JOIN account a "
"ON a.id = e.accountId INNER JOIN employeePosition ep ON e.employeePosition = ep.id "
"INNER JOIN sectionDivision sd ON ep.sectionDivision "
"WHERE e.id = %s")
cursor.execute(sql, (id))
result = cursor.fetchall()
for r in result:
employee = {
'id': r[0],
'accountId': r[1],
'position': r[2],
'division': r[3],
'firstName': r[4],
'lastName': r[5],
'middleInitial': r[6]
}
finally:
connection.close()
return employee
def get_chief_employees(id):
connection = get_db_connection()
employees = []
try:
with connection.cursor() as cursor:
sql = (
"SELECT e.id as employeeId, accountId, a.firstName as firstName, a.lastName as lastName, "
"a.middleInitial as middleInitial, ep.positionTitle as position, sd.name as division "
"FROM employee e INNER JOIN account a ON e.accountId = a.id INNER JOIN employeePosition ep on ep.id = e.employeePosition "
"INNER JOIN sectionDivision sd ON sd.id = ep.sectionDivision WHERE ep.sectionDivision = %s"
)
cursor.execute(sql, (id))
result = cursor.fetchall()
for r in result:
employee = {
'employeeId': r[0],
'id': r[1],
'firstName': r[2],
'lastName': r[3],
'middleInitial': r[4],
'position': r[5],
'division': r[6]
}
employees.append(employee)
finally:
connection.close()
return employees
def get_report_details(id):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = (
"SELECT a.firstName, a.middleInitial, a.lastName, r.attendancePeriod, sd.id "
"FROM account a INNER JOIN employee e ON e.accountId = a.id INNER JOIN report r ON r.employeeId = e.id INNER JOIN employeePosition ep ON "
"e.employeePosition = ep.id INNER JOIN sectionDivision sd ON ep.sectionDivision = sd.id "
"WHERE r.id = %s")
cursor.execute(sql, (id))
result = cursor.fetchall()
for r in result:
report = {
'firstName': r[0],
'middleInitial': r[1],
'lastName': r[2],
'period': r[3],
'divisionId': r[4]
}
finally:
connection.close()
return report
def get_employees_period(data):
connection = get_db_connection()
employees = []
try:
with connection.cursor() as cursor:
sql = (
'SELECT e.id as employeeId, a.firstName as firstName, a.lastName as lastName, '
'a.middleInitial as middleInitial, r.id as reportId, e.employeePosition as position '
'FROM employee e INNER JOIN account a ON e.accountId = a.id '
'INNER JOIN report r ON r.employeeId = e.id '
'INNER JOIN employeePosition ep ON ep.id = e.employeePosition '
'WHERE r.attendancePeriod = %s AND ep.id = %s')
cursor.execute(sql, (data['period'], data['position']))
result = cursor.fetchall()
for r in result:
employee = {
'employeeId': r[0],
'firstName': r[1],
'lastName': r[2],
'middleInitial': r[3],
'reportId': r[4]
}
employees.append(employee)
finally:
connection.close()
return employees
def __load_details(self):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = ('SELECT '
'p.positionTitle, e.contactNumber, '
'e.isWorkingSunday, e.isWorkingMonday, e.isWorkingTuesday, e.isWorkingWednesday, '
'e.isWorkingThursday, e.isWorkingFriday, e.isWorkingSaturday, sd.name, e.employeePosition, e.id '
'FROM employee e '
'INNER JOIN employeePosition p '
'ON e.employeePosition = p.id '
'INNER JOIN sectionDivision sd '
'ON p.sectionDivision = sd.id '
'WHERE e.accountId=%s'
)
cursor.execute(sql, self.id)
result = cursor.fetchone()
if result:
self.position = result[0]
self.contact_number = result[1]
self.is_working_sunday = result[2]
self.is_working_monday = result[3]
self.is_working_tuesday = result[4]
self.is_working_wednesday = result[5]
self.is_working_thursday = result[6]
self.is_working_friday = result[7]
self.is_working_saturday = result[8]
self.section_division = result[9]
self.position_id = result[10]
self.employeeId = result[11]
finally:
connection.close()
def __create_chief_account(data):
status = None
exists = __check_username(data['username'])
if exists:
status = 'User already exists.'
return status
account_id = __create_base_account(data)
if not account_id:
status = 'Failed to create acount.'
return status
connection = get_db_connection()
try:
chief_id = None
with connection.cursor() as cursor:
sql = (
'INSERT INTO chief('
'accountId, sectionDivision'
') VALUES(%s, (SELECT id FROM sectionDivision WHERE name=%s)'
')')
cursor.execute(sql, (account_id, data['section_division']))
chief_id = cursor.lastrowid
if chief_id:
status = 'Successfully created Chief account!'
connection.commit()
else:
status = 'Failed to create Chief account.'
finally:
connection.close()
return status
def nullify_positions_in_employee_accounts(sec_div = None, pos = None):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
if pos:
sql = (
'UPDATE employee SET employeePosition = %s '
'WHERE employeePosition = ('
'SELECT id FROM employeePosition '
'WHERE positionTitle = %s AND sectionDivision = ('
'SELECT id FROM sectionDivision WHERE name = %s'
')'
')'
)
cursor.execute(sql, (None, pos, sec_div))
else:
sql = (
'UPDATE employee SET employeePosition = %s '
'WHERE employeePosition IN ('
'SELECT id FROM employeePosition WHERE sectionDivision = '
'(SELECT id FROM sectionDivision WHERE name=%s)'
')'
)
cursor.execute(sql, (None, sec_div))
connection.commit()
finally:
connection.close()
def get_verifiers():
verifiers = []
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = (
'SELECT username, firstName, lastName, middleInitial FROM account '
'WHERE accountType = %s OR accountType = %s ORDER BY lastName')
cursor.execute(sql, ('Admin', 'Chief'))
result = cursor.fetchall()
for r in result:
account = {
'username': r[0],
'first_name': r[1],
'last_name': r[2],
'middle_initial': r[3]
}
verifiers.append(account)
finally:
connection.close()
return verifiers
def delete_position_record(sec_div, pos):
nullify_positions_in_employee_accounts(sec_div, pos)
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = 'DELETE FROM employeePosition WHERE positionTitle = %s'
cursor.execute(sql, pos)
connection.commit()
finally:
connection.close()
def delete_secdiv_positions(sec_div):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = ('DELETE FROM employeePosition WHERE sectionDivision = '
'(SELECT id FROM sectionDivision WHERE name=%s)'
)
cursor.execute(sql, sec_div)
connection.commit()
finally:
connection.close()
def get_section_divisions_and_positions():
data = []
connection = get_db_connection()
try:
section_divisions = []
with connection.cursor() as cursor:
sql = 'SELECT id, name FROM sectionDivision'
cursor.execute(sql)
result = cursor.fetchall()
for r in result:
sd = {
'id': r[0],
'name': r[1]
}
section_divisions.append(sd)
positions = {}
with connection.cursor() as cursor:
sql = 'SELECT positionTitle, sectionDivision FROM employeePosition'
cursor.execute(sql)
result = cursor.fetchall()
for r in result:
p = r[0]
sd = r[1]
pos = {
'title': p,
'id': '-'.join(p.split())
}
if sd in positions.keys():
positions[sd].append(pos)
else:
positions[sd] = [pos]
for sd in section_divisions:
d = {
'name': sd['name'],
'positions': positions[sd['id']] if sd['id'] in positions.keys() else [],
'id_prepend': '-'.join(sd['name'].split())
}
data.append(d)
finally:
connection.close()
return data
def create_position(data):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = ('INSERT INTO employeePosition(positionTitle, sectionDivision'
') VALUES(%s, (SELECT id FROM sectionDivision WHERE name=%s)'
')'
)
cursor.execute(sql, (data['title'], data['section_division']))
connection.commit()
finally:
connection.close()
def create_section_division(data):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = ('INSERT INTO sectionDivision(name, verifier'
') VALUES(%s, (SELECT id FROM account WHERE username=%s)'
')'
)
cursor.execute(sql, (data['name'], data['verifier']))
connection.commit()
finally:
connection.close()
def approve_report(id):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = ("UPDATE report SET status = 'APPROVED' where id = %s ")
result = cursor.execute(sql, (id))
connection.commit()
finally:
connection.close()
return result
def create_taskInstance(data):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = ('INSERT INTO taskInstance(taskId, weekId, reportId)'
'VALUES(%s, %s, %s)'
)
cursor.execute(sql, (data['taskId'], data['week'], data['reportId']))
connection.commit()
finally:
connection.close()
return 'success'
def update_position_record(current, new, secdiv):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = (
'UPDATE employeePosition SET positionTitle = %s, '
'sectionDivision = (SELECT id FROM sectionDivision WHERE name = %s) '
'WHERE positionTitle = %s'
)
cursor.execute(sql, (new, secdiv, current))
connection.commit()
finally:
connection.close()
def update_secdiv_record(current, new, verifier):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = (
'UPDATE sectionDivision SET name = %s, '
'verifier = (SELECT id FROM account WHERE username = %s) '
'WHERE name = %s'
)
cursor.execute(sql, (new, verifier, current))
connection.commit()
finally:
connection.close()
def get_all_divisions():
connection = get_db_connection()
divisions = []
try:
with connection.cursor() as cursor:
sql = ('SELECT * from sectionDivision')
cursor.execute(sql)
result = cursor.fetchall()
for r in result:
division = {'id': r[0], 'name': r[1]}
divisions.append(division)
finally:
connection.close()
return divisions
def get_position_list():
connection = get_db_connection()
positions = []
try:
with connection.cursor() as cursor:
sql = ('SELECT positionTitle FROM employeePosition')
cursor.execute(sql)
result = cursor.fetchall()
for r in result:
positions.append(r[0])
finally:
connection.close()
return positions
def nullify_secdivs_in_admin_accounts(sec_div):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = (
'UPDATE admin SET sectionDivision = %s '
'WHERE sectionDivision = ('
'SELECT id FROM sectionDivision WHERE name = %s'
')'
)
cursor.execute(sql, (None, sec_div))
connection.commit()
finally:
connection.close()
def create_weeklyEntry(data):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = ('INSERT INTO weeklyEntry(dateStart_week, dateFinish_week, reportId) '
'VALUES(%s, %s, %s)'
)
cursor.execute(sql, (data['startDate'], data['endDate'], data['reportId']))
weekId = connection.insert_id()
connection.commit()
finally:
connection.close()
return weekId
def delete_secdiv_record(sec_div):
nullify_secdivs_in_admin_accounts(sec_div)
nullify_secdivs_in_chief_accounts(sec_div)
nullify_positions_in_employee_accounts(sec_div=sec_div)
delete_secdiv_positions(sec_div)
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = 'DELETE FROM sectionDivision WHERE name=%s'
cursor.execute(sql, sec_div)
connection.commit()
finally:
connection.close()
def get_section_division_list():
connection = get_db_connection()
section_divisions = []
try:
with connection.cursor() as cursor:
sql = ('SELECT name FROM sectionDivision')
cursor.execute(sql)
result = cursor.fetchall()
for r in result:
section_divisions.append(r[0])
finally:
connection.close()
return section_divisions
def __load_user(self):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = 'SELECT username, password, isActive, accountType FROM account WHERE username=%s'
cursor.execute(sql, self.username)
result = cursor.fetchone()
if result:
self.username, self.password_hash, self.is_active, self.account_type = result
else:
self.username = None
finally:
connection.close()
def create_task(data):
connection = get_db_connection()
try:
with connection.cursor() as cursor:
sql = ('INSERT INTO task(taskDescription, isRecurring, hasQuantity, employeePosition, accountId)'
'VALUES(%s, %s, %s, %s, %s)'
)
cursor.execute(sql, (data['taskDescription'], data['isRecurring'], data['hasQuantity'], data['employeePosition'], data['accountId']))
taskId = connection.insert_id()
connection.commit()
finally:
connection.close()
return taskId
def get_employees_position(position):
connection = get_db_connection()
employees = []
try:
with connection.cursor() as cursor:
sql = ('SELECT * from employee where employeePosition = %s')
cursor.execute(sql, (position))
result = cursor.fetchall()
for r in result:
employee = {'id': r[0], 'accountId': r[1]}
employees.append(employee)
finally:
connection.close()
return employees
def get_positions(division):
connection = get_db_connection()
positions = []
try:
with connection.cursor() as cursor:
sql = ('SELECT * from employeePosition where sectionDivision = %s')
cursor.execute(sql, (division))
result = cursor.fetchall()
for r in result:
position = {'id': r[0], 'title': r[1]}
positions.append(position)
finally:
connection.close()
return positions
def get_divisions(id):
connection = get_db_connection()
divisions = []
try:
with connection.cursor() as cursor:
sql = ('SELECT * FROM sectionDivision where id = %s')
cursor.execute(sql, (id))
result = cursor.fetchall()
for r in result:
division = {'id': r[0], 'name': r[1], 'verfier': r[2]}
divisions.append(division)
finally:
connection.close()
return divisions
def get_weeks_report(id):
connection = get_db_connection()
weeks = []
try:
with connection.cursor() as cursor:
sql = ('SELECT * from weeklyEntry where reportId = %s')
cursor.execute(sql, (id))
result = cursor.fetchall()
for r in result:
week = {'id': r[0], 'dateStart': r[1], 'dateEnd': r[2]}
weeks.append(week)
finally:
connection.close()
return weeks
