def __queryDb(self, _json : dict):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = 'SELECT day_of_week, start, "end", cabinet FROM schedule s WHERE s.employee_id={};'.format(_json['staff_id'])
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__to_json(result, query.get_column_names())
def __queryDb(self):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT * FROM position;'''
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__construct_json(result, query.get_column_names())
def __queryDb(self, id: int):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = 'SELECT anim_id, name, species, birth, rel_path_to_photo FROM animals_medical_records;'
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, to_json(result, query.get_column_names())
def __queryDb(self, id: int):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = 'SELECT cli_id, p.surname, p.name, p.patronymic, p.birth FROM clients LEFT JOIN passports p ON passport=p.pass_id;'
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, to_json(result, query.get_column_names())
def __table_query(self, tablename):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = 'SELECT * FROM {}'.format(tablename)
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, None
else:
result = query.get_values()
column_names = query.get_column_names()
access_manager.disconnect(conn_name)
return True, result, column_names
def get_request(self, id: int):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = 'SELECT * FROM contract WHERE owner = {};'.format(id)
query = DBQuery(conn)
if not query.exec_query(str_query):
print(query.get_error())
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__to_json(result, query.get_column_names())
def __query_visits_from_db(self, date: str):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = """SELECT a.name, a.species FROM visits v JOIN animals_medical_records a ON v.animal = a.anim_id WHERE v.next_visit = '{}';""".format(
date)
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self._to_json(result, query.get_column_names())
def __queryDb(self):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT * FROM staff st
JOIN position pos ON st.position=pos.pos_id
JOIN passports pas ON st.passport=pas.pass_id;'''
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__construct_json(result, query.get_column_names())
def __query_staff_schedule(self, uid):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = \
"""SELECT * FROM schedule WHERE employee_id={}""".format(uid)
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, None
else:
schedule = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__to_json_schedule(schedule,
query.get_column_names())
def __queryDb(self):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT v.vis_id, v.visit_date, p.surname, p.name, p.patronymic, a.name, a.species
FROM visits v JOIN staff s ON v.doctor=s.staff_id
JOIN passports p ON s.passport=p.pass_id
JOIN animals_medical_records a ON v.animal=a.anim_id;'''
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__construct_json(result, query.get_column_names())
def __queryDb(self, id: int):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT * FROM animals_medical_records a
LEFT JOIN contract ON contract=contr_id
LEFT JOIN microchips m ON a.chip_id=m.chip_id
WHERE anim_id={};'''.format(id)
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__to_json(result, query.get_column_names())
def __query_client(self, id: int):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT * FROM clients c
LEFT JOIN passports ON passport=pass_id
LEFT JOIN addresses ON address=addr_id
WHERE cli_id={};'''.format(id)
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__to_json_client(result, query.get_column_names())
def __queryDb(self):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT sch.shed_id, sch.day_of_week, sch.start, sch."end", sch.cabinet, st.staff_id, pos.pos_id, pos.title, pass.pass_id, pass.surname, pass.name, pass.patronymic
FROM schedule sch JOIN staff st ON sch.employee_id=st.staff_id
JOIN position pos ON st.position=pos.pos_id
JOIN passports pass ON st.passport=pass.pass_id;'''
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__construct_json(result, query.get_column_names())
def __queryDb(self):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT acc_id, login, password, surname, name,
patronymic, access_level, title, employ_date, fire_date
FROM access a JOIN staff s ON a.employee=s.staff_id
JOIN position p ON s.position=p.pos_id
JOIN passports pas ON s.passport=pas.pass_id;
'''
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__construct_json(result, query.get_column_names())
def __queryDb(self):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT pas.name, pas.surname, pas.patronymic, pos.title, s.fire_date, s.employ_date, s.staff_id
FROM staff s JOIN passports pas ON pas.pass_id=s.passport
JOIN position pos ON s.position=pos.pos_id
WHERE s.staff_id NOT IN
(SELECT acc_id FROM access)
AND s.fire_date is NULL;
'''
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__construct_json(result, query.get_column_names())
def __queryDb(self, vis_id):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT vis.*, stat.*, anim.anim_id, anim.name, anim.species, s.staff_id,
pos.title, pass.name, pass.surname, pass.patronymic
FROM visits vis JOIN animals_medical_records anim ON vis.animal=anim.anim_id
JOIN staff s ON vis.doctor=s.staff_id
JOIN position pos ON s.position=pos.pos_id
JOIN passports pass ON s.passport=pass.pass_id
JOIN animal_states stat ON vis.cur_state=stat.state_id
WHERE vis.vis_id = {i};'''.format(i=vis_id)
query = DBQuery(conn)
if not query.exec_query(str_query):
print(query.get_error())
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__construct_json(result, query.get_column_names())
def __query_staff_data_from_db(self, login, passw):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = \
"""SELECT a.login, a.access_level, s.*, p.*, ps.*
FROM staff s LEFT JOIN access a ON s.staff_id=a.employee
JOIN position p ON position=p.pos_id
JOIN passports ps ON s.passport=ps.pass_id
WHERE a.login='******' AND a.password='******';""".format(login, passw)
query = DBQuery(conn)
if not query.exec_query(str_query):
return False, None
else:
result = query.get_values()
if len(result) == 0:
return False, None
access_manager.disconnect(conn_name)
return True, self.__to_json_staff_schedule(result,
query.get_column_names())