def getAdmitRoomList(ward, roomType):
connection = connect()
cursor = connection.cursor()
query = '''SELECT R.ROOM_NO, R.TYPE, R.CHARGE FROM ROOM R, BLOCK B
WHERE R.BLOCK_ID = B.BLOCK_ID AND
R.TYPE = (:roomType) AND
B.CATEGORY = (:ward) AND
R.ROOM_NO NOT IN (
SELECT RA.ROOM_NO
FROM ROOM_ADMISSION RA
GROUP BY(RA.ROOM_NO)
HAVING COUNT(RA.ROOM_NO) =
(SELECT R.NO_OF_BEDS FROM ROOM R
WHERE R.ROOM_NO = RA.ROOM_NO))'''
cursor.execute(query, [roomType, ward])
resultTemp = cursor.fetchall()
cursor.close()
roomData = []
for R in resultTemp:
roomData.append({
'room_no': R[0],
'room_type': R[1],
'charge': R[2],
})
result = {'roomData': roomData, 'alertMesage': "Okay", 'success': True}
return result
def updateSurgeryResult(data):
connection = connect()
cursor = connection.cursor()
try:
query = '''
UPDATE SURGERY_RESULTS SET RESULT = :result, COMPLETED = 'T', STATUS = :status, SURGERY_DATE = TO_DATE(:surgery_date,'DD-MM-YYYY')
WHERE SURGERY_RESULT_ID = :surgery_result_id
'''
cursor.execute(query, [data['comment'], data['status'],
data['date'], data['surgery_result_id']])
connection.commit()
query = '''UPDATE ROOM_ADMISSION SET RELEASE_DATE = ADMISSION_DATE WHERE ADMISSION_SL =
(SELECT RA.ADMISSION_SL FROM ROOM_ADMISSION RA JOIN
(SELECT SS.PATIENT_ID, SS.ROOM_NO, SR.SURGERY_DATE
FROM
SURGERY_RESULTS SR JOIN SURGERY_SCHEDULE SS ON
(SR.SUR_SCHE_NO = SS.SUR_SCHE_NO AND SR.SURGERY_RESULT_ID = :surgery_result_id)) S
ON (S.PATIENT_ID = RA.PATIENT_ID AND S.ROOM_NO = RA.ROOM_NO AND S.SURGERY_DATE = RA.ADMISSION_DATE))'''
cursor.execute(query, [data['surgery_result_id']])
connection.commit()
response = {'success': True, 'errorMessage': ''}
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def getPatientDetails(patientID):
connection = connect()
cursor = connection.cursor()
query = '''SELECT (FIRST_NAME||' '||LAST_NAME) AS pNAME, EMAIL AS pEMAIL, PHONE_NUM AS pPHONE, GENDER AS pGENDER,
(ROUND(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH)/12)||' years '||FLOOR(MOD(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH), 12))||' months') as pAGE,
ID AS pID FROM PERSON
WHERE USER_ID = (:patientID)'''
# print(query, patientID)
cursor.execute(query, [patientID])
resultTemp = cursor.fetchall()
cursor.close()
# print(resultTemp)
result = {
'patientData': {
'name': resultTemp[0][0],
'email': resultTemp[0][1],
'phone': resultTemp[0][2],
'gender': "Male" if resultTemp[0][3] == "M" else "Female",
'age': resultTemp[0][4],
'id': resultTemp[0][5]
},
'success': True,
}
return result
def getFreeDocOnDate(docID, selectDate):
# print(docID, selectDate)
connection = connect()
cursor = connection.cursor()
query = '''SELECT E.ID, E.NAME FROM (
SELECT D.ID, (P.FIRST_NAME||' '||P.LAST_NAME) AS NAME FROM DOCTOR D JOIN PERSON P
ON D.DEPARTMENT =
(SELECT D.DEPARTMENT FROM DOCTOR D
JOIN PERSON P ON (D.ID = P.ID) AND P.ID = :docID) AND D.ID=P.ID) E
WHERE E.ID NOT IN
(SELECT INCHARGE_DOC FROM SURGERY_SCHEDULE
WHERE TO_CHAR(SUR_DATE) = :selectDate)'''
cursor.execute(query, [docID, selectDate])
resultTemp = cursor.fetchall()
docData = []
for R in resultTemp:
docData.append({
'id': R[0],
'name': R[1],
})
result = {
'docData': docData,
'alertMessage': "Okay",
'success': True,
}
return result
def getAllTestsUnderDoc(docID):
connection = connect()
cursor = connection.cursor()
response = {}
query = '''SELECT * FROM TABLE(RETURN_DOC_ALL_TEST_TABLE((:docID)))'''
try:
cursor.execute(query, [docID])
resultTemp = cursor.fetchall()
cursor.close()
headers = [["Test Result ID", "Patient Name", "Appointment Serial No", "Service Name",
"Test Completed On", "Test Result", "Completion Status"]]
result = []
for R in resultTemp:
result_row = []
for elements in R:
result_row.append(elements)
result.append(result_row)
response['tableData'] = result
response['tableHeader'] = headers
response['success'] = True
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'alertMessage': errorObj.message}
return response
def getAllDoctorsInADeptartment(deptName):
cursor = connect().cursor()
try:
cursor.execute(
f"SELECT P.ID, (P.FIRST_NAME || ' ' || P.LAST_NAME) AS NAME, D.DESIGNATION, D.QUALIFICATION FROM PERSON P JOIN DOCTOR D ON(P.ID = D.ID) WHERE D.DEPARTMENT = '{deptName}'"
)
result = cursor.fetchall()
doctorsName = []
if (len(result) > 0):
for doctor in result:
doctorsName.append({
'id': doctor[0],
'name': doctor[1],
'designation': doctor[2],
'qualification': doctor[3]
})
response = {
'success': True,
'errorMessage': '',
'doctors': doctorsName
}
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def addSchedule(UserID, userCategory, timeID, date, blockID):
connection = connect()
cursor = connection.cursor()
response = {}
# print(date, docID)
query = f"SELECT * FROM PANACEA.SCHEDULE WHERE SCHEDULE_DATE = TO_DATE('{date}','dd/MM/yyyy') AND ID = (SELECT ID FROM PANACEA.PERSON WHERE USER_ID = '{UserID}') AND TIME_ID = {timeID}"
cursor.execute(query)
result = cursor.fetchall()
# print(len(result))
if (len(result) != 0):
response['success'] = False
response[
'errorMessage'] = 'Duplicate schedule entry. Please insert a new value'
response['scheduleData'] = None
response['docData'] = None
response['empData'] = None
return response
query = '''INSERT INTO PANACEA.SCHEDULE(SCHEDULE_ID,SCHEDULE_DATE,ID,TIME_ID, BLOCK_ID)
VALUES(TO_NUMBER((SELECT MAX(SCHEDULE_ID) FROM SCHEDULE))+1, TO_DATE(''' + "'" + date + "'" ''', 'dd/MM/yyyy'),
(SELECT ID FROM PERSON WHERE USER_ID = ''' + "'" + UserID + "'" + ")," + str(
timeID) + ",\'" + str(blockID) + "\')"
cursor.execute(query)
connection.commit()
return getScheduleTable(userCategory, UserID)
def getPatientAppointments(userID):
connection = connect()
cursor = connection.cursor()
query = '''SELECT * FROM TABLE(RETURN_APPNT_DETAILS_TABLE(:userID))'''
response = {}
try:
cursor.execute(query, [userID])
resultTemp = cursor.fetchall()
appointment_data = []
for data in resultTemp:
appointment_data.append({
'doc_name': data[1],
'department': data[2],
'appnt_date': data[3],
'schedule_date': data[4],
'prob_desc': data[5],
'medicine': data[6],
'test': data[7],
})
response['appointment_data'] = appointment_data
response['success'] = True
response['alertMessage'] = "All okay"
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'alertMessage': errorObj.message}
return response
def getTimeTable():
connection = connect()
cursor = connection.cursor()
query = '''SELECT * FROM PANACEA.TIME_TABLE'''
cursor.execute(query)
result = cursor.fetchall()
# print(result)
timeTableData = []
response = {}
if (result != None):
for timeTable in result:
timeTableData.append({
"TIME_ID": timeTable[0],
"START_TIME": timeTable[1],
"END_TIME": timeTable[2],
"SHIFT_TITLE": timeTable[3]
})
response['success'] = True
response['errorMessage'] = ''
response['timeTableData'] = timeTableData
return response
def scheduleOnWardDate(wardCategory, sch_on_date):
connection = connect()
cursor = connection.cursor()
response = {}
query = '''SELECT SCH.SCHEDULE_ID, (P.FIRST_NAME||' '||P.LAST_NAME) AS NAME, EMPLOYEE_TYPE(P.USER_ID) AS ROLE,
(SELECT SHIFT_TITLE FROM TIME_TABLE WHERE TIME_ID = SCH.TIME_ID) AS SHIFT, SCH.BLOCK_ID
FROM PERSON P JOIN SCHEDULE SCH ON (
SCH.SCHEDULE_DATE = TO_DATE(:sch_on_date, 'DD/MM/YYYY') AND
P.ID = SCH.ID AND
SCH.BLOCK_ID IN (SELECT BLOCK_ID FROM BLOCK WHERE CATEGORY=(:wardCategory)))
ORDER BY SCH.SCHEDULE_ID DESC'''
try:
# cursor.execute(query, [sch_on_date, wardCategory])
cursor.execute(query, [sch_on_date, wardCategory])
resultTemp = cursor.fetchall()
cursor.close()
headers = [["Schedule ID", "Name", "Category", "Shift", "Block ID"]]
result = []
for R in resultTemp:
result_row = []
for elements in R:
result_row.append(elements)
result.append(result_row)
response['tableData'] = result
response['tableHeader'] = headers
response['success'] = True
return response
except cx_Oracle.Error as error:
response = {'success': False, 'alertMessage': "Failed"}
return response
def condition_analyst(patient_id, heartbeat, sys_bp, dia_bp, body_temp, oxygen_level, breathing_rate):
score = score_calculator(heartbeat, sys_bp, dia_bp,
body_temp, oxygen_level, breathing_rate)
if score >= 3.5:
query = '''SELECT * FROM (SELECT * FROM MONITORING_DATA WHERE PATIENT_ID = (:patient_id) ORDER BY SL_NO DESC) WHERE ROWNUM<=6'''
connection = connect()
cursor = connection.cursor()
cursor.execute(query, [patient_id])
last_five = cursor.fetchall()
list_hrate = []
list_sys_bp = []
list_dia_bp = []
list_body_temp = []
list_olevel = []
list_brate = []
for data in last_five:
list_hrate.append(data[2])
list_brate.append(data[7])
list_olevel.append(data[6])
list_sys_bp.append(data[3])
list_dia_bp.append(data[4])
list_body_temp.append(data[5])
avg_hrate = sum(list_hrate)/5
avg_sys_bp = sum(list_sys_bp)/5
avg_dia_bp = sum(list_dia_bp)/5
avg_body_temp = sum(list_body_temp)/5
avg_oxy_level = sum(list_olevel)/5
avg_brate = sum(list_brate)/5
score_avg = score_calculator(
avg_hrate, avg_sys_bp, avg_dia_bp, avg_body_temp, avg_oxy_level, avg_brate)
if score_avg >= 1.0:
query = '''SELECT B.INCHARGE_ID, R.BLOCK_ID, RA.ROOM_NO, (P.FIRST_NAME||' '||P.LAST_NAME) AS NAME, P.USER_ID
FROM ROOM_ADMISSION RA JOIN ROOM R
ON (RA.PATIENT_ID = (:patient_id) AND RA.RELEASE_DATE IS NULL AND R.ROOM_NO=RA.ROOM_NO)
JOIN BLOCK B
ON (R.BLOCK_ID = B.BLOCK_ID) JOIN PERSON P ON P.ID = (:patient_id)'''
cursor.execute(query, [patient_id])
result = cursor.fetchall()
name = result[0][3]
block_id = result[0][1]
room_no = result[0][2]
patient_userID = result[0][4]
doctor_id = result[0][0]
message = f'''Emergency! The patient {patient_userID}-{name} in block-{block_id}, room-{room_no} needs emergency attention.
Seriousness Level-{score_avg}/5'''
print(message)
query = '''
INSERT INTO NOTIFICATION(NOTIFICATION_ID,USER_ID, STATUS, MESSAGE)
VALUES((SELECT MAX(NOTIFICATION_ID)+1 FROM NOTIFICATION),:doctor_id, 'I', :message)
'''
cursor.execute(query, [doctor_id, message])
connection.commit()
cursor.close()
def getPendingSurgeries(data):
connection = connect()
cursor = connection.cursor()
try:
if data['all_surgery']:
query = '''
SELECT SURGERY_RESULT_ID, SURGERY_DESC, COMPLETED,TO_CHAR(SURGERY_DATE), STATUS, RESULT
FROM TABLE(RETURN_SURGERY_RESULT_TABLE((SELECT ID FROM PERSON WHERE USER_ID = :docID)))
WHERE COMPLETED = 'A' OR COMPLETED = 'T'
'''
cursor.execute(query, [data['doc_id']])
result = cursor.fetchall()
elif data['surgery_id'] != None:
query = '''
SELECT SURGERY_RESULT_ID, SURGERY_DESC, COMPLETED,TO_CHAR(SURGERY_DATE), STATUS,RESULT
FROM TABLE(RETURN_SURGERY_RESULT_TABLE((SELECT ID FROM PERSON WHERE USER_ID = :docID)))
WHERE SURGERY_RESULT_ID = :surgery_id AND COMPLETED = 'A'
'''
cursor.execute(query, [data['doc_id'], data['surgery_id']])
result = cursor.fetchall()
else:
query = '''
SELECT SURGERY_RESULT_ID, SURGERY_DESC, COMPLETED,TO_CHAR(SURGERY_DATE), STATUS,RESULT
FROM TABLE(RETURN_SURGERY_RESULT_TABLE((SELECT ID FROM PERSON WHERE USER_ID = :docID)))
WHERE COMPLETED = 'A'
'''
cursor.execute(query, [data['doc_id']])
result = cursor.fetchall()
surgeries = []
for surg in result:
if surg[5] == None:
comment = 'N/A'
else:
comment = surg[5]
if surg[3] == None:
surg_date = 'N/A'
else:
surg_date = surg[3]
surgeries.append({'surgery_result_id': surg[0], 'surgery_desc': surg[1], 'completed': surg[2],
'surgery_date': surg_date, 'status': surg[4], 'result': comment})
response = {}
response['success'] = True
response['errorMessage'] = ''
response['surgeries'] = surgeries
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def deleteSchedule(selectedSchedules, UserCategory, UserID):
connection = connect()
cursor = connection.cursor()
# print(selectedSchedules)
query = '''DELETE FROM PANACEA.SCHEDULE WHERE SCHEDULE_ID = :ID'''
cursor.executemany(query, [(i, ) for i in selectedSchedules])
connection.commit()
return getScheduleTable(UserCategory, UserID)
def serviceResultsTable(diagnosisID):
connection = connect()
cursor = connection.cursor()
response = {}
try:
query = '''
SELECT T.TEST_RESULT_ID, S.SERVICE_NAME, S.COST
FROM TEST_RESULTS T JOIN SERVICE S ON(T.SERVICE_ID = S.SERVICE_ID)
WHERE T.TEST_RESULT_ID IN (select regexp_substr((SELECT SERVICE_RESULTS FROM DIAGNOSIS WHERE DIAGNOSIS_ID = :diagnosisID),'[^-]+', 1, level) AS "SERVICE_ID"
from dual
connect BY regexp_substr((SELECT SERVICE_RESULTS FROM DIAGNOSIS WHERE DIAGNOSIS_ID = :diagnosisID), '[^-]+', 1, level)
is not null)
AND T.COMPLETED = 'F'
'''
cursor.execute(query, [diagnosisID, diagnosisID])
result = cursor.fetchall()
pendingTests = []
for test in result:
pendingTests.append(
{'test_result_id': test[0], 'service_name': test[1], 'cost': test[2]})
response['pending_tests'] = pendingTests
query = '''
SELECT S.SURGERY_RESULT_ID, S.SURGERY_DESC, S1.SERVICE_NAME, S1.COST, S1.DEPARTMENT
FROM SURGERY_RESULTS S LEFT OUTER JOIN SERVICE S1 ON(S.SERVICE_ID = S1.SERVICE_ID)
WHERE S.SURGERY_RESULT_ID IN (select regexp_substr((SELECT SERVICE_RESULTS FROM DIAGNOSIS WHERE DIAGNOSIS_ID = :diagnosisID),'[^-]+', 1, level) AS "SERVICE_ID"
from dual
connect BY regexp_substr((SELECT SERVICE_RESULTS FROM DIAGNOSIS WHERE DIAGNOSIS_ID = :diagnosisID), '[^-]+', 1, level)
is not null)
AND S.COMPLETED = 'F'
'''
cursor.execute(query, [diagnosisID, diagnosisID])
result = cursor.fetchall()
pendingSurgery = []
for surgery in result:
pendingSurgery.append({'surgery_result_id': surgery[0], 'surgery_desc': surgery[1],
'service_name': surgery[2], 'cost': surgery[3], 'department': surgery[4]})
print(pendingSurgery)
response['pending_surgeries'] = pendingSurgery
response['success'] = True
response['errorMessage'] = ''
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def getAppointmentsOfPatient(patientID, dateRange):
connection = connect()
cursor = connection.cursor()
response = {}
if dateRange == -1:
query = '''SELECT A.APP_SL_NO,(P.FIRST_NAME||' '||P.LAST_NAME) AS PATIENT_NAME, (D.FIRST_NAME||' '||D.LAST_NAME) AS DOCTOR_NAME,
TO_CHAR(A.APPNT_DATE,'DD/MM/YYYY') AS APPNT_DATE, TO_CHAR(SCH.SCHEDULE_DATE, 'DD/MM/YYYY') AS VISITING_DATE, A.STATUS
FROM APPOINTMENT A JOIN PERSON P
ON (A.PATIENT_ID= P.ID AND P.ID =(SELECT ID FROM PERSON WHERE USER_ID = (:patientID))
AND A.APPNT_DATE<(SYSDATE))
JOIN PERSON D
ON (A.DOCTOR_ID = D.ID)
JOIN SCHEDULE SCH ON
(A.SCHEDULE_ID = SCH.SCHEDULE_ID) ORDER BY A.APP_SL_NO DESC'''
else:
query = '''SELECT A.APP_SL_NO,(P.FIRST_NAME||' '||P.LAST_NAME) AS PATIENT_NAME, (D.FIRST_NAME||' '||D.LAST_NAME) AS DOCTOR_NAME,
TO_CHAR(A.APPNT_DATE,'DD/MM/YYYY') AS APPNT_DATE, TO_CHAR(SCH.SCHEDULE_DATE, 'DD/MM/YYYY') AS VISITING_DATE, A.STATUS
FROM APPOINTMENT A JOIN PERSON P
ON (A.PATIENT_ID= P.ID AND P.ID =(SELECT ID FROM PERSON WHERE USER_ID = (:patientID))
AND A.APPNT_DATE>(SYSDATE-(:dateRange)))
JOIN PERSON D
ON (A.DOCTOR_ID = D.ID)
JOIN SCHEDULE SCH ON
(A.SCHEDULE_ID = SCH.SCHEDULE_ID) ORDER BY A.APP_SL_NO DESC'''
try:
if dateRange == -1:
cursor.execute(query, [patientID])
else:
cursor.execute(query, [patientID, dateRange])
resultTemp = cursor.fetchall()
cursor.close()
headers = [[
"Appointment Serial No", "Patient Name", "Doctor Name",
"Appointment Made On", "Visiting Date", "Status"
]]
result = []
for R in resultTemp:
result_row = []
for elements in R:
result_row.append(elements)
result.append(result_row)
response['tableData'] = result
response['tableHeader'] = headers
response['success'] = True
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'alertMessage': errorObj.message}
return response
def getAllDocAppointments(docId, todaysAppointment):
connection = connect()
cursor = connection.cursor()
if todaysAppointment:
query = '''
SELECT A.APP_SL_NO, (SELECT (FIRST_NAME || ' ' || LAST_NAME) FROM PERSON WHERE ID = A.PATIENT_ID) AS "NAME",
TO_CHAR(A.APPNT_DATE) AS "SUBMISSION DATE", A.PROB_DESC, TO_CHAR(S.SCHEDULE_DATE) AS "APPOINTMENT DATE", T.SHIFT_TITLE,
(T.START_TIME || '-' || T.END_TIME) AS "TIME"
FROM APPOINTMENT A JOIN SCHEDULE S ON(A.SCHEDULE_ID = S.SCHEDULE_ID)
JOIN TIME_TABLE T ON (S.TIME_ID = T.TIME_ID)
WHERE A.DOCTOR_ID = (SELECT ID FROM PERSON WHERE USER_ID = :docId)
AND TRUNC(S.SCHEDULE_DATE) = TRUNC(SYSDATE)
AND A.STATUS = 'accepted'
ORDER BY A.APP_SL_NO
'''
else:
query = '''
SELECT A.APP_SL_NO, (SELECT (FIRST_NAME || ' ' || LAST_NAME) FROM PERSON WHERE ID = A.PATIENT_ID) AS "NAME",
TO_CHAR(A.APPNT_DATE) AS "SUBMISSION DATE", A.PROB_DESC, TO_CHAR(S.SCHEDULE_DATE) AS "APPOINTMENT DATE", T.SHIFT_TITLE,
(T.START_TIME || '-' || T.END_TIME) AS "TIME"
FROM APPOINTMENT A JOIN SCHEDULE S ON(A.SCHEDULE_ID = S.SCHEDULE_ID)
JOIN TIME_TABLE T ON (S.TIME_ID = T.TIME_ID)
WHERE A.DOCTOR_ID = (SELECT ID FROM PERSON WHERE USER_ID = :docId)
AND A.STATUS = 'accepted'
ORDER BY A.APP_SL_NO
'''
response = {}
try:
cursor.execute(query, [docId])
result = cursor.fetchall()
appointments = []
print(len(result))
for appointment in result:
appointments.append({
'app_sl_no': appointment[0],
'patient_name': appointment[1],
'submission_date': appointment[2],
'problem_desc': appointment[3],
'appointment_date': appointment[4],
'shift_title': appointment[5],
'time': appointment[6]
})
response['appointments'] = appointments
response['success'] = True
response['errorMessage'] = ''
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def getAppntWithSl(diagnosisID):
connection = connect()
cursor = connection.cursor()
query = '''
SELECT APP_SL_NO FROM CHECKUP WHERE DIAGNOSIS_ID = :diagnosisID
'''
cursor.execute(query, [diagnosisID])
result = cursor.fetchone()
appntSerial = result[0]
query = '''SELECT * FROM
(
SELECT (FIRST_NAME||' '||LAST_NAME) AS pNAME, EMAIL AS pEMAIL, PHONE_NUM AS pPHONE, GENDER AS pGENDER,
(ROUND(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH)/12)||' years '||FLOOR(MOD(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH), 12))||' months') as pAGE,
ID AS pID
FROM PERSON
WHERE ID =
(SELECT PATIENT_ID FROM APPOINTMENT WHERE
APP_SL_NO = :appntSerial)) P NATURAL JOIN
(SELECT (P.FIRST_NAME||' '||P.LAST_NAME) AS dNAME, D.DEPARTMENT, P.ID FROM
DOCTOR D JOIN PERSON P
ON( D.ID = (SELECT DOCTOR_ID FROM APPOINTMENT WHERE
APP_SL_NO = :appntSerial) AND D.ID = P.ID)) D'''
cursor.execute(query, [appntSerial, appntSerial])
resultTemp = cursor.fetchall()
cursor.close()
# print(appntSerial)
result = {
'appointmentData': {
'app_sl_no': appntSerial,
'patientData': {
'name': resultTemp[0][0],
'email': resultTemp[0][1],
'phone': resultTemp[0][2],
'gender': "Male" if resultTemp[0][3] == "M" else "Female",
'age': resultTemp[0][4],
'id': resultTemp[0][5]
},
'appntDocData': {
'name': resultTemp[0][6],
'department': resultTemp[0][7],
'id': resultTemp[0][8]
},
},
'success': True,
'errorMessage': '',
'ok': True
}
print(result)
return result
def getRoomTypesForWard(ward):
connection = connect()
cursor = connection.cursor()
query = '''SELECT DISTINCT(R.TYPE) FROM ROOM R, BLOCK B
WHERE R.BLOCK_ID = B.BLOCK_ID AND B.CATEGORY = (:ward)'''
cursor.execute(query, [ward])
resultTemp = cursor.fetchall()
cursor.close()
roomTypes = []
for R in resultTemp:
roomTypes.append({
'room_type': R[0],
})
result = {'roomTypes': roomTypes, 'alertMesage': "Okay", 'success': True}
return result
def getReceptionistAppointments():
connection = connect()
cursor = connection.cursor()
query = '''
SELECT A.APP_SL_NO,
(SELECT (FIRST_NAME || ' ' || LAST_NAME) FROM PERSON WHERE ID = A.PATIENT_ID) AS "PAT_NAME",
(SELECT (FIRST_NAME || ' ' || LAST_NAME) FROM PERSON WHERE ID = A.DOCTOR_ID) AS "DOC_NAME",
TO_CHAR(A.APPNT_DATE), A.PROB_DESC, A.STATUS, TO_CHAR(S.SCHEDULE_DATE), T.START_TIME, T.END_TIME
FROM APPOINTMENT A JOIN SCHEDULE S ON(A.SCHEDULE_ID = S.SCHEDULE_ID)
JOIN TIME_TABLE T ON(S.TIME_ID = T.TIME_ID)
WHERE S.SCHEDULE_DATE = TRUNC(SYSDATE)
AND A.STATUS = 'pending'
'''
response = {}
try:
cursor.execute(query)
result = cursor.fetchall()
print(result)
appointments = []
if (result != None):
for appointment in result:
appointments.append({
'app_sl_no': appointment[0],
'patient_name': appointment[1],
'doctor_name': appointment[2],
'appointment_date': appointment[3],
'problem_desc': appointment[4],
'status': appointment[5],
'schedule_date': appointment[6],
'start_time': appointment[7],
'end_time': appointment[8]
})
response['success'] = True
response['errorMessage'] = ''
response['appointments'] = appointments
print(response)
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def addSurSchedule(appnt_serial_no, inchargeDocID, patient_id, room_no, timeID,
duration, selectedDate, surgery_result_id):
connection = connect()
cursor = connection.cursor()
# perform check if ref appnt id already exists...
# selectedDate = f"'{selectedDate}'"
# print(appnt_serial_no, inchargeDocID, patient_id, room_no, timeID, duration, selectedDate)
query = f'''
SELECT COUNT(*)
FROM SURGERY_SCHEDULE
WHERE ROOM_NO = {room_no} AND TIME_ID = {timeID} AND SUR_DATE = TO_DATE('{selectedDate}', 'DD/MM/YYYY')
'''
print(room_no, timeID, selectedDate)
cursor.execute(query)
result = cursor.fetchone()[0]
print(result)
if result > 0:
return {
'success':
False,
'errorMessage':
'There is already a surgery scheduled at the time slot. Please select a different one'
}
query = '''
DECLARE
ID NUMBER;
BEGIN
INSERT INTO SURGERY_SCHEDULE(APP_REF_NO, INCHARGE_DOC, PATIENT_ID, ROOM_NO, TIME_ID, DURATION_HRS, SUR_DATE)
VALUES (:appnt_serial_no, :inchargeDocID, :patient_id, :room_no, :timeID, :duration, TO_DATE(:selectedDate, 'DD/MM/YYYY'));
SELECT MAX(SUR_SCHE_NO) INTO ID FROM SURGERY_SCHEDULE;
UPDATE SURGERY_RESULTS SET COMPLETED = 'A', SUR_SCHE_NO = ID
WHERE SURGERY_RESULT_ID = :surgery_result_id;
END;
'''
# try (try-catch) block
cursor.execute(query, [
appnt_serial_no, inchargeDocID, patient_id, room_no, timeID, duration,
selectedDate, surgery_result_id
])
connection.commit()
return {'success': True, 'message': "Surgery Schedule Added Successfully"}
def technicianPendingTests(data):
connection = connect()
cursor = connection.cursor()
try:
if data['all_tests']:
query = '''
SELECT T.TEST_RESULT_ID,S.SERVICE_NAME, (P.FIRST_NAME || ' ' || P.LAST_NAME) AS "PATIENT NAME"
FROM TEST_RESULTS T JOIN SERVICE S ON(T.SERVICE_ID = S.SERVICE_ID)
JOIN PERSON P ON(T.PATIENT_ID = P.ID)
WHERE T.COMPLETED = 'T' OR T.COMPLETED = 'A' ORDER BY T.TEST_RESULT_ID DESC
'''
cursor.execute(query)
result = cursor.fetchall()
elif data['test_result_id'] != None:
query = '''
SELECT T.TEST_RESULT_ID,S.SERVICE_NAME, (P.FIRST_NAME || ' ' || P.LAST_NAME) AS "PATIENT NAME"
FROM TEST_RESULTS T JOIN SERVICE S ON(T.SERVICE_ID = S.SERVICE_ID)
JOIN PERSON P ON(T.PATIENT_ID = P.ID)
WHERE T.TEST_RESULT_ID = :test_result_id AND (T.COMPLETED = 'A' OR T.COMPLETED = 'T')
'''
cursor.execute(query, [data['test_result_id']])
result = cursor.fetchall()
else:
query = '''
SELECT T.TEST_RESULT_ID,S.SERVICE_NAME, (P.FIRST_NAME || ' ' || P.LAST_NAME) AS "PATIENT NAME"
FROM TEST_RESULTS T JOIN SERVICE S ON(T.SERVICE_ID = S.SERVICE_ID)
JOIN PERSON P ON(T.PATIENT_ID = P.ID)
WHERE T.COMPLETED = 'A' ORDER BY T.TEST_RESULT_ID DESC
'''
cursor.execute(query)
result = cursor.fetchall()
pendingTests = []
for test in result:
pendingTests.append(
{'test_result_id': test[0], 'service_name': test[1], 'patient_name': test[2]})
response = {'success': True, 'errorMessage': '',
'pending_tests': pendingTests}
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def addScheduleRange(userID, userCategory, timeID, days, blockID,
scheduleLength):
connection = connect()
cursor = connection.cursor()
query = '''SELECT ID FROM PANACEA.PERSON WHERE USER_ID = \'''' + userID + "\'"
cursor.execute(query)
id_pk = cursor.fetchone()
# print(id_pk[0], userCategory, timeID, days, blockID, scheduleLength)
query = '''BEGIN
SCHEDULE_RANGE(:userID , :timeID, :days, :blockID, :scheduleLength);
END;'''
cursor.execute(query, [id_pk[0], timeID, days, blockID, scheduleLength])
connection.commit()
cursor.close()
return getScheduleTable(userCategory, userID)
def sellMedicine(patientID, quantity, medID):
connection = connect()
cursor = connection.cursor()
quantity = int(quantity)
response = {}
query = '''INSERT INTO DISPENSARY(MED_ID, ASSIGNED_TO, QUANTITY_PCS)
VALUES(:medID, (SELECT ID FROM PERSON WHERE USER_ID = (:patientID)), :quantity)'''
try:
cursor.execute(query, [medID, patientID, quantity])
connection.commit()
response['success'] = True
response['Message'] = "Successfully Saved"
return response
except cx_Oracle.Error:
response = {'success': False, 'alertMessage': 'DataBase Failure'}
return response
def getPatientMonitorData(data):
connection = connect()
cursor = connection.cursor()
response = {}
try:
query = '''
SELECT HEARTBEAT, SYS_BP,DIAS_BP,TEMPERATURE, OXY_LEVEL,BREATHING_RATE, FLOOR((SYSDATE - CAST(TIME AS DATE))*24) AS "HOUR",
(ROUND((SYSDATE - CAST(TIME AS DATE))*24,2)-FLOOR((SYSDATE - CAST(TIME AS DATE))*24))*60 AS "MINUTE", TO_CHAR(CAST(TIME AS DATE),'MM/DD/YYYY HH:MI:SS') AS "TIME"
FROM MONITORING_DATA
WHERE PATIENT_ID = (SELECT ID FROM PERSON WHERE USER_ID = :pat_id)
ORDER BY SL_NO DESC
'''
cursor.execute(query, [data['patient_id']])
result = cursor.fetchall()
monitroData = []
for dat in result:
monitroData.append({'heart_beat': dat[0], 'sys_bp': dat[1], 'dias_bp': dat[2],
'temperature': dat[3], 'oxygen_level': dat[4], 'breathing_rate': dat[5], 'hours_past': dat[6],
'minutes_past': dat[7], 'time_of_data': dat[8]})
query = '''
SELECT (SELECT USER_ID FROM PERSON WHERE ID = RA.PATIENT_ID),
(SELECT (FIRST_NAME|| ' ' || LAST_NAME) AS NAME FROM PERSON WHERE ID = RA.PATIENT_ID) AS "NAME",
TO_CHAR(RA.ADMISSION_DATE) AS "ADMISSION_DATE", RA.ADMISSION_SL
FROM ROOM_ADMISSION RA
WHERE RA.PATIENT_ID = (SELECT ID FROM PERSON WHERE USER_ID = :pat_id)
'''
cursor.execute(query, [data['patient_id']])
pat_result = cursor.fetchone()
response['monitor_data'] = monitroData
response['patient_info'] = {'patient_id': pat_result[0], 'patient_name': pat_result[1], 'admission_date': pat_result[2],
'admission_sl': pat_result[3]}
response['success'] = True
response['errorMessage'] = ''
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def getAdmittedPatient(data):
connection = connect()
cursor = connection.cursor()
response = {}
try:
query = '''
SELECT R.ROOM_NO, R.BLOCK_ID,B.CATEGORY
FROM ROOM R JOIN BLOCK B ON(R.BLOCK_ID = B.BLOCK_ID)
WHERE B.INCHARGE_ID = (SELECT ID FROM PERSON WHERE USER_ID = :doc_id)
ORDER BY R.ROOM_NO
'''
cursor.execute(query, [data['userID']])
result = cursor.fetchall()
rooms = []
for room in result:
query = '''
SELECT (SELECT USER_ID FROM PERSON WHERE ID = RA.PATIENT_ID),
(SELECT (FIRST_NAME|| ' ' || LAST_NAME) AS NAME FROM PERSON WHERE ID = RA.PATIENT_ID) AS "NAME",
RA.ADMISSION_DATE, RA.ADMISSION_SL
FROM ROOM_ADMISSION RA
WHERE RA.ROOM_NO = :room_no AND RA.RELEASE_DATE IS NULL
'''
cursor.execute(query, [room[0]])
pat_result = cursor.fetchall()
patients = []
for pat in pat_result:
patients.append(
{'patient_id': pat[0], 'patient_name': pat[1], 'admission_date': pat[2], 'admission_sl': pat[3]})
rooms.append({'room_no': room[0], 'block_id': room[1],
'block_category': room[2], 'patients': patients})
response['rooms'] = rooms
response['success'] = True
response['errorMessage'] = ''
return response
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def confirmPayment(patientID, total_amount):
connection = connect()
cursor = connection.cursor()
response = {}
query = '''INSERT INTO BILL(TOTAL_AMOUNT, PATIENT_ID, PAYMENT_CATEGORY) VALUES
(:total_amount, (SELECT ID FROM PERSON WHERE USER_ID = (:patientID)), 'hosp_services') '''
try:
cursor.execute(query, [total_amount, patientID])
connection.commit()
cursor.close()
return {'success': True, 'message': 'Bill Payment Successfully saved'}
except cx_Oracle.Error as error:
errorObj, = error.args
print(errorObj)
response = {'success': False, 'alertMessage': 'Failed'}
return response
def getWardTable(category):
connection = connect()
cursor = connection.cursor()
# print(category)
query = f'select BLOCK_ID from PANACEA.BLOCK where CATEGORY=\'{category}\''
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
blockList = []
response = {}
if (result):
for block in result:
blockList.append({'block': block[0]})
response['success'] = True
response['errorMessage'] = ''
response['blockList'] = blockList
return response
def getRoomList(searchDate, roomType, timeID):
connection = connect()
cursor = connection.cursor()
query = '''SELECT R.ROOM_NO, R.TYPE FROM ROOM R JOIN BLOCK B
ON R.BLOCK_ID = B.BLOCK_ID
AND LOWER(B.CATEGORY) IN :roomType
AND R.ROOM_NO NOT IN
(SELECT ROOM_NO FROM SURGERY_SCHEDULE
WHERE TO_CHAR(SUR_DATE, 'DD/MM/YYYY') = :searchDate
AND TIME_ID = :timeID)'''
cursor.execute(query, [roomType, searchDate, timeID])
resultTemp = cursor.fetchall()
cursor.close()
roomData = []
for R in resultTemp:
roomData.append({'room_no': R[0], 'room_name': R[1]})
result = {'roomData': roomData, 'alertMesage': "Okay", 'success': True}
return result
def receptionistApproveSurgery(diagnosisID, surgery_result_id):
connection = connect()
cursor = connection.cursor()
try:
query = '''
UPDATE SURGERY_RESULTS SET COMPLETED = 'A'
WHERE SURGERY_RESULT_ID = :surgery_result_id
'''
cursor.execute(query, [surgery_result_id])
connection.commit()
return serviceResultsTable(diagnosisID)
except cx_Oracle.Error as error:
errorObj, = error.args
response = {'success': False, 'errorMessage': errorObj.message}
print(response)
return response
def getBlocksPerCategory(block_category):
connection = connect()
cursor = connection.cursor()
query = '''SELECT BLOCK_ID FROM BLOCK WHERE CATEGORY=(:block_category)'''
try:
cursor.execute(query, [block_category])
resultTemp = cursor.fetchall()
cursor.close()
roomTypes = []
for R in resultTemp:
roomTypes.append({
'block_id': R[0],
})
result = {'blocks': roomTypes, 'alertMesage': "Okay", 'success': True}
return result
except cx_Oracle.Error as err:
result = {'success': False, 'alertMessage': err}
return result
