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
