Query Alerts Technical Documentation

1) Query Processing

2)

Step 1:
Take excels from client.
Step 2:
Insert data in related database tables manually.
Step 3:
Run the process with Java application.
Step 4:
Select database facility name.
Step 5:
Select Executetimes or Accountno
Step 6:
If executetimes selected,GetCurrentCensusAcc stored procedure will be executed,it takes the accounts from different tables like AnnotationList, AddDiagnoses, deletedicdByDocLog, DeletedICDPriority, Icdcodelog, LabResults, DRGChangeLog, POAChangeLog, Medications, VitalSigns with QAExecuteTimes starttime condition.
Step 7:
Then AnalysisByAccNo() is called,it executes InsertQueryAlerts stored procedure,first targetcodes will be checked with icd10icdpriority table ,if not satisfied then all the threshold conditions like icds,keywords, labs, vitals, medications, drg conditions will be checked and final results will be stored in facilitywise QueryAlertResults table.

Step 8:
getThresholdAlertIds() is called, it executes getQueryAlertIds stored procedure which returns all target condition not matched alertids,based on alertids manually handling(like BMI,PO2 PCO2 difference,creatinine increase/decrease) threshold condition methods will be called.

Step 9:
Then suggestiveProcedures() is called,it executes all the suggestive related procedures getsuggestivekeyICD SP,getSuggestiveLabs SP, getSuggestiveVsigns SP,getSuggestiveMedications SP.
Step 10:
getDistinctAlertIds() is called,it executes GetDistinctAlertIdFromQR stored procedure which returns all threshold condition matched alertids,based on alertids manually handling(like systolic BP,diastolic BP,age) suggestive condition methods will be called.
Step 11:
GetDataFromQR() is called,it executes GetDataFromQR stored procedure which returns results from QueryAlertResults table,then calling GetICDContent, GetProcedureContent stored procedures for corresponding doctor name, date, reporttype and inserting queries into Diagnosiscritera, FieldDescription tables.

3) Query Processing

4) Query Excel

5) Tables

6)

Step 1:
QueryAlertNames--Insert an entry with new alertid,alertname,activestatus 1,poastatus 0.
Step 2:
QueryICD--Insert alertid,icdcode,Tier,status 0,condition(if required),keywordcondition(if required) If condition mentioned like icd+icd then we need to insert same value in condition column and in ICDConditions table condition column. If condition mentioned like icd+keyword or keyword+icd then we need to insert same value in keywordcondition column and in keywordconditions table keywordcondition column.
Step 3:

QueryWords--Insert alertid,keyword,Tier,status 0,condition(if required),keyword2(if required) If condition mentioned like keyword+keyword then we need to insert same value in keyword2 column and in keywordconditions table keywordcondition column.
Step 4:
QueryAlertsInLabs--Insert alertid,Tier,status 0,subtype as LABS(for procedure handling labs) subtype as MLab(for manually handling labs),fieldid(take from html_clinicalfields),fieldname,numlow(min. value),numhigh(max. value),cdiname(take from html_clinicalfields),units


If value > 50 then numlow =0 ,numhigh=50(not in between numlow and numhigh)
If value >= 50 then numlow =0 ,numhigh=49
If value < 50 then numlow =50 ,numhigh=999
If value <= 50 then numlow =51 ,numhigh=999

Step 5:
QueryAlertVsigns--Insert alertid,Tier,status 0,subtype as VSIGN(for procedure handling vitals) subtype as MVSIGN(for manually handling vitals),fieldid(take from html_clinicalfields),fieldname,numlow(min. value),numhigh(max. value),cdiname(take from html_clinicalfields),units


If value > 50 then numlow =0 ,numhigh=50(not in between numlow and numhigh)
If value >= 50 then numlow =0 ,numhigh=49
If value < 50 then numlow =50 ,numhigh=999
If value <= 50 then numlow =51 ,numhigh=999
Step 6:
QueryMedications--Insert alertid,Tier,status 0,medicationtype(medication name),route,condition(if required),keyword(if required)
If condition mentioned like icd+medication or medication+icd then we need to insert same value in condition column and in ICDConditions table condition column.
If condition mentioned like keyword+medication or medication+keyword then we need to insert same value in keyword column and in keywordconditions table keywordcondition column.

Step 7:
Follow the same process for all suggestive tables.