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