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