def _fetch_all_and_make_objects(cls, sql):
cursor = connection.cursor()
cursor.execute(sql)
keys = [x[0].lower() for x in cursor.description]
rows = cursor.fetchall()
return [cls(keys, x) for x in rows]
def _fetch_all_and_make_objects(cls, sql):
cursor = connection.cursor()
cursor.execute(sql)
keys = [x[0].lower() for x in cursor.description]
rows = cursor.fetchall()
return [cls(keys, x) for x in rows]
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_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, 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, position_name):
sql = "DELETE FROM permissions WHERE position_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [position_name])
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_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, department_name):
sql = "DELETE FROM departments WHERE department_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [department_name])
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 delete_record(self, username):
sql = "DELETE FROM employees WHERE username=%s"
cursor = connection.cursor()
cursor.execute(sql, [username])
connection.commit()
return ""
def _get_quality(self):
cursor = connection.cursor()
cursor.execute("SELECT Quality FROM [AWR].[dbo].[FLOW_QUALITY] WHERE Station ='"+str(self.station)+"'")
data = cursor.fetchall()
if len(data) > 0 and type(data[0][0]) == float:
return data[0][0]
else:
return 4.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 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 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 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 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 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 _get_quality(self):
cursor = connection.cursor()
cursor.execute(
"SELECT Quality FROM [AWR].[dbo].[FLOW_QUALITY] WHERE Station ='" +
str(self.station) + "'")
data = cursor.fetchall()
if len(data) > 0 and type(data[0][0]) == float:
return data[0][0]
else:
return 4.0
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 get_record(self, username, fk1=None, fk2=None):
sql = "SELECT * FROM employees WHERE username=%s"
cursor = connection.cursor()
cursor.execute(sql, [username])
record = cursor.fetchone()
if record:
return Employee(record[0])
else:
raise ResourceNotFound(f"Employee with user {username} not found.")
def get_record(self, position_name, fk1=None, fk2=None):
sql = "SELECT * FROM permissions WHERE position_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [position_name])
record = cursor.fetchone()
if record:
return Permissions(record[0], record[1], record[2])
else:
raise ResourceNotFound(f"Permissions for position {position_name} not found.")
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 get_all_records(self):
sql = "SELECT * FROM departments"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
department_list = []
for record in records:
department = Department(record[0], record[1])
department_list.append(department.json())
return department_list
def get_record(self, department_name, fk1=None, fk2=None):
sql = "SELECT * FROM departments WHERE department_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [department_name])
record = cursor.fetchone()
if record:
return Department(record[0], record[1])
else:
raise ResourceNotFound(
f"Department with name {department_name} not found.")
def get_all_records(self):
sql = "SELECT * FROM employees"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
employee_list = []
for record in records:
employee = Employee(record[0])
employee_list.append(employee.json())
return employee_list
def get_all_records(self):
sql = "SELECT * FROM permissions"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
permission_list = []
for record in records:
permission = Permissions(record[0], record[1], record[2])
permission_list.append(permission.json())
return permission_list
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 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 get_record(self, tuition_name, fk1=None, fk2=None):
sql = "SELECT * FROM tuition_types WHERE tuition_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [tuition_name])
record = cursor.fetchone()
if record:
return TuitionType(record[0], record[1], record[2],
float(record[3]))
else:
raise ResourceNotFound(
f"Tuition by name {tuition_name} not found.")
def get_all_records(self):
sql = "SELECT * FROM tuition_types"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
tuitions_list = []
for record in records:
tuition = TuitionType(record[0], record[1], record[2],
float(record[3]))
tuitions_list.append(tuition.json())
return tuitions_list
def __init__(self):
self.stations = nx.Graph()
cursor = connection.cursor()
cursor.execute("SELECT code, next_station, name, municipality FROM [AWR].[dbo].[HSY_TARGETS]"
# "WHERE target_type = 'JVP'"
"WHERE next_station <> 'NULL'"
"AND next_station <> ''")
rows = cursor.fetchall()
for i in rows:
fr = PumpStation(i[0])
to = PumpStation("VKM") if "VKM" in i[1] else PumpStation(i[1])
self.stations.add_edge(fr, to)
