def __update_position_in_db(self, json):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = self.__get_str_query(json)
sched_q = self.__get_str_query_sched(json)
query = DBQuery(conn)
query.begin_transaction()
if not query.exec_query(str_query):
print(query.get_error())
query.rollback_transaction()
access_manager.disconnect(conn_name)
return False
if not query.exec_query(sched_q):
print(query.get_error())
query.rollback_transaction()
access_manager.disconnect(conn_name)
return False
query.commit_transaction()
access_manager.disconnect(conn_name)
return True
def __update_schedule(self, json_data):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
del_q = '''DELETE FROM schedule WHERE employee_id={i};'''\
.format(i=json_data['employee_id'])
query = DBQuery(conn)
query.begin_transaction()
if not query.exec_query(del_q):
print(query.get_error())
query.rollback_transaction()
access_manager.disconnect(conn_name)
return False
schedule = json_data['schedule']
for s in schedule:
str_query = self.__get_str_query_sched(s, json_data['employee_id'])
if not query.exec_query(str_query):
print(query.get_error())
query.rollback_transaction()
access_manager.disconnect(conn_name)
return False
query.commit_transaction()
access_manager.disconnect(conn_name)
return True
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 __queryDb(self, acc_id : str):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''DELETE FROM access WHERE acc_id = {id}
'''.format(id=acc_id)
query = DBQuery(conn)
if not query.exec_query(str_query):
print(query.get_error())
return False
access_manager.disconnect(conn_name)
return True
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 __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 __add_position_to_db(self, json): conn_name = str(uuid.uuid4()) conn = access_manager.connect(conn_name) str_query = self.__get_str_query(json) query = DBQuery(conn) if not query.exec_query(str_query): print(query.get_error()) return False access_manager.disconnect(conn_name) return True
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 __update_db(self, str_query: str):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
query = DBQuery(conn)
query.begin_transaction()
if not query.exec_query(str_query):
print(query.get_error())
query.rollback_transaction()
access_manager.disconnect(conn_name)
return False
query.commit_transaction()
access_manager.disconnect(conn_name)
return True
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 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 __insert_visit_to_json(self, json):
animal_state = json['cur_state']
vis = json
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query_state = \
'''INSERT INTO animal_states (general, pulse, weight, ap, temperature, cfr, resp_rate) VALUES ('{}', {}, {}, '{}', {}, {}, {}) RETURNING state_id;'''.format(animal_state['general'], animal_state['pulse'], animal_state['weight'], animal_state['ap'],
animal_state['temperature'], animal_state['cfr'], animal_state['resp_rate'])
query = DBQuery(conn)
query.begin_transaction()
if not query.exec_query(str_query_state):
print(query.get_error())
return False, None
else:
result = query.get_values()
state_id = str(result[0][0])
if vis['next_visit'] == None:
next_date = 'NULL'
else:
next_date = "'" + vis['next_visit'] + "'"
str_query_visit = \
'''INSERT INTO visits (doctor, animal, visit_date, owner_dynamics, history_disease, cur_state, diagnosis, recommendations, next_visit, prescribings, note) VALUES ({}, {}, '{}', '{}', '{}', '{}', '{}', '{}', {}, '{}', '{}');'''.format(
vis['doctor']['staff_id'],
vis['animal']['anim_id'],
vis['visit_date'],
vis['owner_dynamics'],
vis['history_disease'],
state_id,
vis['diagnosis'],
vis['recommendations'],
next_date,
str(vis['prescribings']).replace("'", '"'),
vis['note'])
if not query.exec_query(str_query_visit):
print(query.get_error())
query.rollback()
return False
query.commit_transaction()
access_manager.disconnect(conn_name)
return True
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())
def __insert_staff_to_db(self, json):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query_passport = self.__get_str_query_passport(json['passport'])
query = DBQuery(conn)
query.begin_transaction()
if not query.exec_query(str_query_passport):
print(query.get_error())
query.rollback_transaction()
return False, None
else:
result = query.get_values()
pass_id = str(result[0][0])
print(pass_id)
str_query_staff = self.__get_str_query_staff(json, pass_id)
if not query.exec_query(str_query_staff):
print(query.get_error())
query.rollback_transaction()
return False
else:
res = query.get_values()
staff_id = str(res[0][0])
for sched_item in json['schedule']:
str_query_sched = self.__get_str_query_sched(sched_item, staff_id)
if not query.exec_query(str_query_sched):
print(query.get_error())
query.rollback_transaction()
return False
query.commit_transaction()
access_manager.disconnect(conn_name)
return True
def __queryInsertDb(self, data: dict, owner: int):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
query = DBQuery(conn)
chip = data["chip_id"]
query.begin_transaction()
contract = data["contract"]
chip = data["chip_id"]
last_update_str = contract['last_update_date']
if len(last_update_str) == 0:
last_update_str = 'NULL'
else:
last_update_str = "'{}'::date".format(last_update_str)
str_query = '''INSERT INTO contract (code, conclusion_date, last_update_date, owner, valid_until) VALUES('{code}', '{conclusion_date}'::date,
{last_update_date}, '{owner}', '{valid_until}'::date) RETURNING contr_id;'''.format(
code=contract['code'],
conclusion_date=contract['conclusion_date'],
last_update_date=last_update_str,
valid_until=contract['valid_until'],
owner=owner)
contr_id: int
if not query.exec_query(str_query):
query.rollback_transaction()
print(query.get_error())
return False
else:
contr_id = query.get_values()[0][0]
str_query = '''INSERT INTO microchips (chip_num, impl_date, country, location)
VALUES('{chip_num}', '{impl_date}'::date, '{country}', '{location}') RETURNING chip_id;'''\
.format(chip_num=chip["chip_num"], impl_date=chip["impl_date"], country=chip["country"], location=chip["location"])
chip_id: int
if not query.exec_query(str_query):
query.rollback_transaction()
print(query.get_error())
return False
else:
chip_id = query.get_values()[0][0]
str_query = '''INSERT INTO animals_medical_records (name, breed, species, sex, castrated, birth, other_data,
color, special_signs, registr_date, chip_id, contract, rel_path_to_photo)
VALUES ('{name}', '{breed}', '{species}', '{sex}', '{castrated}', '{birth}'::date, '{other}', '{color}', '{special_signs}',
'{registr_date}'::date, {chip_id}, {contract}, '{rel_path_to_photo}') RETURNING anim_id;'''\
.format(name=data["name"], breed=data["breed"], species=data["species"],
sex=data["sex"], castrated=data["castrated"], birth=data["birth"],
other=data["other_data"], color=data["color"], special_signs=data["special_signs"],
registr_date=data["registr_date"], contract=contr_id,
chip_id=chip_id, rel_path_to_photo=data["rel_path_to_photo"])
anim_id: int
if not query.exec_query(str_query):
query.rollback_transaction()
print(query.get_error())
return False, 0
else:
anim_id = query.get_values()[0][0]
query.commit_transaction()
access_manager.disconnect(conn_name)
return True, anim_id
def __queryUpdateDb(self, data: dict):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
query = DBQuery(conn)
chip = data["chip_id"]
query.begin_transaction()
contract = data["contract"]
chip = data["chip_id"]
str_queries = []
last_update_str = contract['last_update_date']
if len(last_update_str) == 0:
last_update_str = 'NULL'
else:
last_update_str = "'{}'::date".format(last_update_str)
str_queries.append('''UPDATE animals_medical_records
SET name='{name}',
breed='{breed}',species='{species}', sex='{sex}', castrated='{castrated}', birth='{birth}'::date,
other_data='{other}', color='{color}', special_signs='{special_signs}',
registr_date='{registr_date}'::date, chip_id={chip_id}, contract={contract},
rel_path_to_photo='{rel_path_to_photo}' WHERE anim_id={anim_id};'''.
format(anim_id=data["anim_id"],
name=data["name"],
breed=data["breed"],
species=data["species"],
sex=data["sex"],
castrated=data["castrated"],
birth=data["birth"],
other=data["other_data"],
color=data["color"],
special_signs=data["special_signs"],
registr_date=data["registr_date"],
contract=contract["contr_id"],
chip_id=chip["chip_id"],
rel_path_to_photo=data["rel_path_to_photo"]))
str_queries.append('''UPDATE contract
SET code='{code}', conclusion_date='{conclusion_date}'::date,
last_update_date={last_update_date}, valid_until='{valid_until}'::date WHERE contr_id={contr_id};'''
.format(code=contract['code'],
conclusion_date=contract['conclusion_date'],
last_update_date=last_update_str,
valid_until=contract['valid_until'],
contr_id=contract['contr_id']))
str_queries.append('''UPDATE microchips
SET chip_num='{chip_num}', impl_date='{impl_date}'::date,
country='{country}', location='{location}' WHERE chip_id={chip_id}'''.
format(chip_num=chip["chip_num"],
impl_date=chip["impl_date"],
country=chip["country"],
location=chip["location"],
chip_id=chip["chip_id"]))
for str_query in str_queries:
if not query.exec_query(str_query):
query.rollback_transaction()
print(query.get_error())
return False
query.commit_transaction()
access_manager.disconnect(conn_name)
return True
