def insert_account_subscription(purchase_date, expire_date, account_id,
subscription_id):
sql = "INSERT INTO account_subscription(purchase_date, expire_date, account_id, subscription_id) VALUES(%s,%s,%s,%s)"
values = (str(purchase_date), str(expire_date), str(account_id),
str(subscription_id))
cursor.execute(sql, values)
connection.commit()
def delete_record(self, department_name):
sql = "DELETE FROM departments WHERE department_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [department_name])
connection.commit()
return ""
def delete_record(self, tuition_name):
sql = "DELETE FROM tuition_types WHERE tuition_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [tuition_name])
connection.commit()
return ""
def delete_record(self, password):
sql = "DELETE FROM employee_information WHERE password=%s"
cursor = connection.cursor()
cursor.execute(sql, [password])
connection.commit()
return ""
def update_record(self, change):
sql = "UPDATE permissions SET department_head_access=%s, ben_co_access=%s WHERE position_name=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (change.department_head_access, change.ben_co_access, change.position_name))
connection.commit()
return ""
def update_reimbursement(self, password, change):
sql = "UPDATE reimbursement_funds_remaining=%s WHERE password=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (change, password))
connection.commit()
return ""
def delete_record(self, position_name):
sql = "DELETE FROM permissions WHERE position_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [position_name])
connection.commit()
return ""
def create_record(self, permission, fk1=None, fk2=None):
sql = "INSERT INTO permissions VALUES (%s, %s, %s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (permission.position_name, permission.department_head_access, permission.ben_co_access))
connection.commit()
record = cursor.fetchone()
return Permissions(record[0], record[1], record[2])
def delete_record(self, username):
sql = "DELETE FROM employees WHERE username=%s"
cursor = connection.cursor()
cursor.execute(sql, [username])
connection.commit()
return ""
def create_record(self, employee, fk1=None, fk2=None):
sql = "INSERT INTO employees VALUES (%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, [employee.username])
connection.commit()
record = cursor.fetchone()
return Employee(record[0])
def update_reimbursement_amount(case_id, value):
sql = "UPDATE trainings SET reimbursement_amount=%s WHERE case_id=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (value, case_id))
connection.commit()
return ""
def update_query(change):
sql = "UPDATE trainings SET query=%s WHERE case_id=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (change.query, change.case_id))
connection.commit()
return ""
def delete_record(self, case_id):
sql = "DELETE FROM trainings WHERE case_id=%s"
cursor = connection.cursor()
cursor.execute(sql, [case_id])
connection.commit()
return ""
def update_record(self, change):
sql = "UPDATE departments SET department_head=%s WHERE department_name=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (change.department_head, change.department_name))
connection.commit()
return ""
def update_approval(case_id, level):
sql = "UPDATE trainings SET " + level + "=TRUE WHERE case_id=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, [case_id])
connection.commit()
return ""
def create_record(self, department, fk1=None, fk2=None):
sql = "INSERT INTO departments VALUES (%s, %s) RETURNING *"
cursor = connection.cursor()
cursor.execute(
sql, (department.department_name, department.department_head))
connection.commit()
record = cursor.fetchone()
return Department(record[0], record[1])
def create_record(self, tuition, fk1=None, fk2=None):
sql = "INSERT INTO tuition_types (%s,%s,%s,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql,
(tuition.tuition_name, tuition.grading_format,
tuition.grade_cutoff, tuition.reimbursement_percent))
connection.commit()
record = cursor.fetchone()
return TuitionType(record[0], record[1], record[2], float(record[3]))
def update_record(self, change):
sql = "UPDATE tuition_types SET grading_format=%s, grade_cutoff=%s, reimbursement_percent=%s " \
"WHERE tuition_name=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql,
(change.grading_format, change.grade_cutoff,
change.reimbursement_percent, change.tuition_name))
connection.commit()
return ""
def update_record(self, change):
sql = "UPDATE employee_information SET last_name=%s,first_name=%s,dob=%s,department=%s," \
"position_name=%s,reimbursement_funds_remaining=%s WHERE password=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql,
(change.last_name, change.first_name, change.dob,
change.department, change.position_name,
change.reimbursement_funds_remaining, change.password))
connection.commit()
return ""
def update_record(self, change):
sql = "UPDATE trainings SET ds_approval=%s, dh_approval=%s, benco_approval=%s, final_grade=%s, " \
"reimbursement_amount=%s, query=%s, query_whom=%s, answer=%s WHERE case_id=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(
sql,
(change.ds_approval, change.dh_approval, change.benco_approval,
change.final_grade, change.reimbursement_amount, change.query,
change.query_whom, change.answer, change.case_id))
connection.commit()
return ""
def create_record(self, employee, fk1=None, fk2=None):
sql = "INSERT INTO employee_information VALUES (%s,%s,%s,%s,%s,%s,%s,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(
sql,
(employee.last_name, employee.first_name, employee.username,
employee.password, employee.dob, employee.department,
employee.position_name, employee.reimbursement_funds_remaining))
connection.commit()
record = cursor.fetchone()
return EmployeeInformation(record[0], record[1], record[2], record[3],
record[4], record[5], record[6], record[7])
def create_record(self, training, fk1=None, fk2=None):
sql = "INSERT INTO trainings VALUES (DEFAULT,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql,
(training.employee_user, training.tuition_type,
training.date_submitted, training.training_date,
training.direct_supervisor, training.ds_approval,
training.department_head, training.dh_approval,
training.benco_approval, training.final_grade,
training.justification, training.reimbursement_amount))
connection.commit()
record = cursor.fetchone()
return Training(record[0], record[1], record[2], record[3], record[4],
record[5], record[6], record[7], record[8], record[9],
record[10], record[11], record[12])
def insert_discount(name, mod_id):
sql = "INSERT INTO discounts(name, modifier_id) VALUES(%s,%s)"
values = (name, str(mod_id))
cursor.execute(sql, values)
connection.commit()
def insert_room(number, branch_id):
sql = "INSERT INTO rooms(number, branch_id) VALUES(%s,%s)"
values = (str(number), str(branch_id))
cursor.execute(sql, values)
connection.commit()
def insert_row(number, room_id):
sql = "INSERT INTO rows(number, room_id) VALUES(%s,%s)"
values = (str(number)[:1], str(room_id))
cursor.execute(sql, values)
connection.commit()
def insert_seat(number, vip, row_id):
sql = "INSERT INTO seats(number, vip, rows_id) VALUES(%s,%s,%s)"
values = (str(number), str(vip), str(row_id))
cursor.execute(sql, values)
connection.commit()
def insert_show(show_date, show_time, price, movie_id):
sql = "INSERT INTO shows(show_date, show_time, price, movie_id) VALUES(%s,%s,%s,%s)"
values = (str(show_date), str(show_time), str(price), str(movie_id))
cursor.execute(sql, values)
connection.commit()
def insert_room_show(room_id, show_id):
sql = "INSERT INTO room_show(room_id, show_id) VALUES(%s,%s)"
values = (str(room_id), str(show_id))
cursor.execute(sql, values)
connection.commit()
def insert_account(login, password, email):
sql = "INSERT INTO accounts(login, password,email) VALUES(%s,%s,%s)"
values = (login, password, email)
cursor.execute(sql, values)
connection.commit()
def insert_subscription(name, description, mod_id):
sql = "INSERT INTO subscriptions(name, description, modifier_id) VALUES(%s,%s,%s)"
values = (name, description, str(mod_id))
cursor.execute(sql, values)
connection.commit()
