-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathquery.properties
34 lines (27 loc) · 9.06 KB
/
query.properties
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# patient.js.....
sql.currentPatient=SELECT pa.PatientPin, pa.ParentPinFK, SUM(si.State = ?) AS pendingCount, SUM(si.State = ?) AS deactivatedCount, st.Name AS stage FROM patients AS pa JOIN activity_instance AS si ON si.PatientPinFK = pa.PatientPin JOIN stage AS st ON st.StageId = pa.StageIdFK WHERE pa.PatientPin =? GROUP BY pa.PatientPin, pa.ParentPinFK, st.Name
sql.surveyInstances=SELECT pa.DateCompleted, si.ActivityInstanceId, si.StartTime, si.EndTime, si.UserSubmissionTime, si.ActualSubmissionTime, si.activityTitle,si.State as state, st.Name AS stageName FROM patients AS pa JOIN activity_instance AS si ON si.PatientPinFK = pa.PatientPin JOIN stage AS st ON st.StageId = pa.StageIdFK WHERE pa.PatientPin = ? and si.activityTitle NOT IN (?,?) ORDER BY si.StartTime
sql.currentTrial=SELECT tr.Name, tr.TrialId FROM patients AS pa JOIN stage AS st ON st.StageId = pa.StageIdFK JOIN trial AS tr ON tr.TrialId = st.TrialId WHERE pa.PatientPin = ?
sql.surveyResults=SELECT ai.PatientPinFK as pin, ai.activityTitle as name, ai.UserSubmissionTime as date, act.ActivityInstanceIdFk as id, act.questionIdFk as questionId, act.questionOptionIdFk as optionId, ans.OptionText as optionText, que.SurveyBlockIdFk as questionType, ai.StartTime as StartTime, ans.likertScale as likertScale, pi.type as patientType FROM question_result act JOIN questions que ON act.questionIdFk = que.QuestionId JOIN question_options ans ON act.questionOptionIdFk = ans.QuestionOptionId JOIN activity_instance ai ON act.ActivityInstanceIdFk = ai.ActivityInstanceId JOIN patients pi ON ai.PatientPinFK = pi.PatientPin WHERE act.ActivityInstanceIdFk IN (SELECT ActivityInstanceId FROM activity_instance WHERE PatientPinFK = ? and State=? and activityTitle=?)
sql.opioidResults= SELECT ai.PatientPinFK as pin, ai.activityTitle as name, ai.UserSubmissionTime as date, act.ActivityInstanceIdFk as id, act.questionIdFk as questionId, act.questionOptionIdFk as optionId, ans.OptionText as optionText, act.dosage, que.SurveyBlockIdFk as questionType, ai.StartTime as StartTime, ans.likertScale as likertScale, pi.type as patientType, mi.prescribedDosage, mi.noOfTablets as prescribedNoOfTablets FROM question_result act JOIN questions que ON act.questionIdFk = que.QuestionId JOIN question_options ans ON act.questionOptionIdFk = ans.QuestionOptionId JOIN activity_instance ai ON act.ActivityInstanceIdFk = ai.ActivityInstanceId JOIN patients pi ON ai.PatientPinFK = pi.PatientPin JOIN medication_information mi ON mi.PatientPINFK = ai.PatientPinFK and mi.MedicationName = ans.optionText WHERE act.ActivityInstanceIdFk IN (SELECT ActivityInstanceId FROM activity_instance WHERE PatientPinFK = ? and State=? and ai.activityTitle=?)
sql.bodyPainResults=SELECT ai.PatientPinFK as pin, ai.activityTitle as name, ai.UserSubmissionTime as date, act.ActivityInstanceIdFk as id, act.questionIdFk as questionId, act.questionOptionIdFk as optionId, ans.OptionText as optionText, que.SurveyBlockIdFk as questionType, ai.StartTime as StartTime, ans.likertScale as likertScale, pi.type as patientType FROM question_result act JOIN questions que ON act.questionIdFk = que.QuestionId JOIN question_options ans ON act.questionOptionIdFk = ans.QuestionOptionId JOIN activity_instance ai ON act.ActivityInstanceIdFk = ai.ActivityInstanceId JOIN patients pi ON ai.PatientPinFK = pi.PatientPin WHERE act.ActivityInstanceIdFk IN (SELECT ActivityInstanceId FROM activity_instance WHERE PatientPinFK = ? and State=? and que.questionId IN (74))
sql.dailySurvey= SELECT ai.PatientPinFK as pin, ai.activityTitle as name,ai.UserSubmissionTime as date, act.ActivityInstanceIdFk as id, act.questionIdFk as questionId, act.questionOptionIdFk as optionId, ans.OptionText as optionText, que.SurveyBlockIdFk as questionType, ai.StartTime as StartTime, ans.likertScale as likertScale, pi.type as patientType FROM question_result act JOIN questions que ON act.questionIdFk = que.QuestionId JOIN question_options ans ON act.questionOptionIdFk = ans.QuestionOptionId JOIN activity_instance ai ON act.ActivityInstanceIdFk = ai.ActivityInstanceId JOIN patients pi ON ai.PatientPinFK = pi.PatientPin WHERE act.ActivityInstanceIdFk IN (SELECT ActivityInstanceId FROM activity_instance WHERE PatientPinFK = ? and State=? and activityTitle=?)
# Query to get questions for score catagories...
sql.getScoreData=SELECT ai.PatientPinFK as pin, ai.activityTitle as name, ai.UserSubmissionTime as date, act.ActivityInstanceIdFk as id, act.questionIdFk as questionId, act.questionOptionIdFk as optionId, ans.OptionText as optionText, que.SurveyBlockIdFk as questionType, ai.StartTime as StartTime, ans.likertScale as likertScale, pi.type as patientType FROM question_result act JOIN questions que ON act.questionIdFk = que.QuestionId JOIN question_options ans ON act.questionOptionIdFk = ans.QuestionOptionId JOIN activity_instance ai ON act.ActivityInstanceIdFk = ai.ActivityInstanceId JOIN patients pi ON ai.PatientPinFK = pi.PatientPin WHERE act.ActivityInstanceIdFk IN (SELECT ActivityInstanceId FROM activity_instance WHERE PatientPinFK =? and State=? and que.questionId IN (?)) order by date
# dashboard.js ...
sql.trials=SELECT t.*, s.StageId, count(1) as recruitedCount from trial t, stage s INNER JOIN patients pa ON s.StageId = pa.StageIdFK WHERE t.TrialId = s.trialId GROUP BY t.TrialId, t.Name, t.Description , t.IRBID, t.IRBStart, t.IRBEnd, t.TargetCount, t.PatientPinCounter, t.CreatedAt, t.UpdatedAt, t.DeletedAt, t.Duration, s.StageId;
#deactivate-patient.js
sql.deactivatePatient=UPDATE activity_instance SET State = ? WHERE State = ? AND PatientPinFk = ?
sql.setCompleteDate=UPDATE patients SET DateCompleted = ? WHERE PatientPin = ?
sql.deleteDeactivated=DELETE FROM activity_instance WHERE State = ? AND EndTime >= DATE_ADD(?, INTERVAL 1 DAY) AND PatientPinFk = ? AND (activityTitle = ? or activityTitle=?)
# patient-csv.js ..
sql.csvPatient=SELECT ai.PatientPinFK AS pin, ai.activityTitle AS name, ai.UserSubmissionTime AS date, ai.ActivityInstanceId AS id, act.questionIdFk AS questionId, que.QuestionText AS questionText, act.questionOptionIdFk AS optionId, ans.OptionText AS optionText, act.dosage AS dosage, act.value FROM activity_instance ai LEFT JOIN question_result act ON act.ActivityInstanceIdFk = ai.ActivityInstanceId LEFT JOIN questions que ON act.questionIdFk = que.QuestionId LEFT JOIN question_options ans ON act.questionOptionIdFk = ans.QuestionOptionId WHERE ai.ActivityInstanceId IN (SELECT ActivityInstanceId FROM activity_instance WHERE PatientPinFK = ? AND (State =? OR State =?) AND activityTitle NOT IN (?,?)) ORDER BY id
# Survey.js...
sql.csvSurvey=SELECT ai.PatientPinFK as pin, ai.activityTitle as name, ai.UserSubmissionTime as date,act.ActivityInstanceIdFk as id, act.questionIdFk as questionId, que.QuestionText as questionText, act.questionOptionIdFk as optionId, ans.OptionText as optionText, act.dosage, act.Value FROM question_result act JOIN questions que ON act.questionIdFk = que.QuestionId JOIN question_options ans ON act.questionOptionIdFk = ans.QuestionOptionId JOIN activity_instance ai ON act.ActivityInstanceIdFk = ai.ActivityInstanceId WHERE act.ActivityInstanceIdFk IN (SELECT ActivityInstanceId FROM activity_instance WHERE PatientPinFK = ? and ActivityInstanceId = ? and State=?)
# Trial.js
sql.trialData=SELECT StageId, Name, CreatedAt, UpdatedAt, DeletedAt, TrialId FROM stage AS stage WHERE stage.DeletedAt IS NULL AND stage.TrialId = ?
sql.trialCompliance=SELECT tr.*, pa.PatientPin, pa.DateStarted, pa.DateCompleted, st.Name AS stage FROM trial AS tr JOIN stage AS st ON st.TrialId = tr.TrialId JOIN patients AS pa ON pa.StageIdFK = st.StageId WHERE tr.TrialId = ? ORDER BY pa.DateCompleted DESC
sql.surveyBiweekly=SELECT State, EndTime, PatientPinFK FROM activity_instance WHERE activityTitle = ? AND EndTime > DATE_SUB(now(),INTERVAL 8 DAY) AND EndTime <= now() AND State != ? ORDER BY EndTime DESC
sql.complianceData=SELECT pa.PatientPin, SUM(si.State = ? and si.activityTitle = ?) AS expiredWeeklyCount, SUM(si.State = ? and si.activityTitle = ?) AS completedWeeklyCount, SUM(si.State = ? and si.activityTitle = ?) AS expiredDailyCount, SUM(si.State = ? and si.activityTitle = ?) AS completedDailyCount, SUM(si.State = ?) AS pendingCount, SUM(si.State = ?) AS deactivatedCount, SUM(si.State = ? and si.activityTitle = ? and si.EndTime > DATE_SUB(now(), INTERVAL 8 DAY) and si.EndTime < now()) AS expiredTrendingWeeklyCount, SUM(si.State = ? and si.activityTitle = ? and si.EndTime > DATE_SUB(now(), INTERVAL 8 DAY) and si.EndTime < now()) AS completedTrendingWeeklyCount, SUM(si.State = ? and si.activityTitle = ? and si.EndTime > DATE_SUB(now(), INTERVAL 8 DAY) and si.EndTime < now()) AS expiredTrendingDailyCount, SUM(si.State = ? and si.activityTitle = ? and si.EndTime > DATE_SUB(now(), INTERVAL 8 DAY) and si.EndTime < now()) AS completedTrendingDailyCount FROM activity_instance AS si JOIN patients AS pa ON pa.PatientPin = si.PatientPinFK JOIN stage AS st ON st.StageId = pa.StageIdFK WHERE st.TrialId = ? GROUP BY pa.PatientPin
# Trial-csv.js
sql.csvTrial=SELECT a.State, a.StartTime, p.DateStarted, p.PatientPin FROM activity_instance a JOIN patients p ON a.PatientPinFk = p.PatientPin WHERE a.activityTitle = ? ORDER BY a.PatientPinFk, a.ActivityInstanceId;