def get_role(id): sql = "Select * from roles where id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) record = cursor.fetchone() role = Role(id=record[0], name=record[1]) return role
def get_reimbursements_total_for(employee_id): sql = "Select sum(amount) from reimbursements where employee_id=%s and date_part('year', date_submitted) = " \ "date_part('year', now()) and status_id != 6;" cursor = connection.cursor() cursor.execute(sql, [employee_id]) record = cursor.fetchone() total = record[0] return total
def get_department(id): sql = "Select * from departments where id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) record = cursor.fetchone() department = Department(id=record[0], name=record[1], head=record[2]) return department
def get_course_type(id): sql = "Select * from course_types where id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) record = cursor.fetchone() course_type = CourseType(id=record[0], name=record[1], reimbursement_percent=record[2]) return course_type
def update_account(changing_account): sql = "Update accounts set account_type=%s, balance=%s where id=%s" cursor = connection.cursor() cursor.execute(sql, [ changing_account.account_type, changing_account.balance, changing_account.id ]) connection.commit() return cursor.rowcount
def create_course(course, commit=True): sql = "insert into courses values (default, %s, %s, %s, %s, %s, %s) Returning id" cursor = connection.cursor() cursor.execute(sql, [ course.name, course.type.id, course.start_date, course.end_date, course.grading_format.id, course.cost ]) connection.commit() if commit else connection.rollback() return cursor.fetchone()
def get_format(id): sql = "Select * from grading_formats where id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) record = cursor.fetchone() format = GradingFormat(id=record[0], type=record[1], requires_presentation=record[2]) return format
def get_client(id): sql = "Select id, name from clients where id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) record = cursor.fetchone() try: client = Client(id=record[0], name=record[1], accounts=AccountsDAO.get_accounts_by_client(record[0])) return client except TypeError as e: return False
def get_account(client_id, account_id): try: sql = "Select * from accounts where id = %s and client_id = %s" cursor = connection.cursor() cursor.execute(sql, [account_id, client_id]) record = cursor.fetchone() return Account(account_type=record[2], id=record[0], balance=record[3]) except TypeError: return False
def get_all_clients(): sql = "Select id, name from clients" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() clients = [] for record in records: clients.append(Client(id=record[0], name=record[1], accounts=AccountsDAO.get_accounts_by_client(record[0]))) return clients
def create_account(account, client_id): try: sql = "Insert into accounts values (default, %s, %s, %s)" cursor = connection.cursor() cursor.execute(sql, [client_id, account.account_type, account.balance]) connection.commit() return "Successfully added Account" except: return "No client found", 404 finally: connection.commit()
def get_course(id): sql = "Select * from courses where id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) record = cursor.fetchone() course = Course(id=record[0], name=record[1], start_date=record[3], end_date=record[4], cost=record[6]) course.type = CourseTypeDao.get_course_type(record[2]) course.grading_format = GradingFormatDao.get_format(record[5]) return course
def get_employee(id): sql = "Select * from employees where id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) record = cursor.fetchone() employee = Employee(id=record[0], first_name=record[1], last_name=record[2], login_id=record[3]) employee.department = DepartmentDao.get_department(record[4]) employee.role = RoleDao.get_role(record[5]) return employee
def get_accounts_by_client(id): sql = "Select * from accounts where client_id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) records = cursor.fetchall() accounts = [] for record in records: accounts.append( Account(account_type=record[2], id=record[0], balance=record[3])) return accounts
def get_all_accounts(): sql = "Select * from accounts" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() accounts = [] for record in records: accounts.append( Account(account_type=record[2], id=record[0], balance=record[3])) return accounts
def get_accounts_by_client_between(id, less_than, greater_than): sql = "Select * from accounts where client_id = %s and balance between %s and %s" cursor = connection.cursor() cursor.execute(sql, [id, greater_than, less_than]) records = cursor.fetchall() accounts = [] for record in records: accounts.append( Account(account_type=record[2], id=record[0], balance=record[3])) return accounts
def get_employees_by_department(department_id): sql = "Select * from employees where department_id = %s" cursor = connection.cursor() cursor.execute(sql, [department_id]) records = cursor.fetchall() employees = [] for record in records: employee = Employee(id=record[0], first_name=record[1], last_name=record[2], login_id=record[3]) employee.role = RoleDao.get_role(record[5]) employees.append(employee) return employees
def create_reimbursement(reimbursement, commit=True): if reimbursement.course.id is None: reimbursement.course.id = CourseDao.create_course( reimbursement.course) status = 1 if not reimbursement.employee.supervisor: status = 2 sql = "insert into reimbursements values (default, %s, %s, %s, %s, %s, %s)" cursor = connection.cursor() cursor.execute(sql, [ reimbursement.employee.id, status, reimbursement.date_submitted, reimbursement.course.id, reimbursement.amount, reimbursement.message ]) connection.commit() if commit else connection.rollback() return True
def login(login_id): sql = "Select * from employees where login_id = %s" cursor = connection.cursor() cursor.execute(sql, [login_id]) record = cursor.fetchone() if record: employee = Employee(id=record[0], first_name=record[1], last_name=record[2], login_id=record[3]) employee.department = DepartmentDao.get_department(record[4]) employee.role = RoleDao.get_role(record[5]) if record[6]: employee.supervisor = EmployeeDao.get_employee(record[6]) return employee else: return False
def get_reimbursements_awaiting_approval(employee_id, status_id): sql = "Select * from reimbursements where employee_id=%s and status_id = %s" cursor = connection.cursor() cursor.execute(sql, [employee_id, status_id]) records = cursor.fetchall() reimbursements = [] for record in records: reimbursement = Reimbursement(id=record[0], date_submitted=record[3], amount=record[5], message=record[6]) reimbursement.course = CourseDao.get_course(record[4]) reimbursement.employee = EmployeeDao.get_employee(record[1]) reimbursement.status = ReimbursementStatusDao.get_status(record[2]) reimbursements.append(reimbursement) return reimbursements
def get_all_reimbursements_for_benco(): sql = "Select * from reimbursements where status_id = 3" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() reimbursements = [] for record in records: reimbursement = Reimbursement(id=record[0], date_submitted=record[3], amount=record[5], message=record[6]) reimbursement.course = CourseDao.get_course(record[4]) reimbursement.employee = EmployeeDao.get_employee(record[1]) reimbursement.status = ReimbursementStatusDao.get_status(record[2]) reimbursements.append(reimbursement) return reimbursements
def update_reimbursement(id, status_id, message, amount, commit=True): sql = "update reimbursements set status_id = %s, message=%s, amount=%s where id=%s" cursor = connection.cursor() cursor.execute(sql, [status_id, message, amount, id]) connection.commit() if commit else connection.rollback() return str(cursor.rowcount)
def update_client(changing_client): sql = "Update clients set name=%s where id=%s" cursor = connection.cursor() cursor.execute(sql, [changing_client.name, changing_client.id]) connection.commit() return cursor.rowcount
def delete_account(client_id, account_id): sql = "Delete from accounts where id = %s and client_id = %s" cursor = connection.cursor() cursor.execute(sql, [account_id, client_id]) connection.commit() return cursor.rowcount
def delete_client(id): sql = "Delete from clients where id = %s" cursor = connection.cursor() cursor.execute(sql, [id]) connection.commit() return cursor.rowcount
def create_client(client): sql = "Insert into clients values (default, %s)" cursor = connection.cursor() cursor.execute(sql, [client.name]) connection.commit() return True