public async Task <ObservableRangeCollection <Response> > GetDistressInterventions(string DistressLevelType)
{
/*
* This function pulls the Second Half of the Support Plan from the Responses Table (Mild Moderate and Acute)
*/
ObservableRangeCollection <Response> r = new ObservableRangeCollection <Response>();
if (!DistressLevelType.Equals("Calm"))
{
//Pull every response with DistressLevelType override OR if the Question associated with the checked response has a QuestionCarePlanArea containing "Intervention"
//Since Override takes priority over the actual Value of the stepper, the tuples that will be added that have the correct value will only be added if the Override is empty
r.AddRange(await db.QueryAsync <Response>(
"SELECT *, RANDOM() As Random FROM [CheckBoxLabels] LEFT JOIN [Questions] WHERE (Questions.CPQID = CheckBoxLabels.CPQID AND CheckBoxLabels.Value = 1) AND (Questions.QuestionCarePlanArea LIKE '%Intervention%' OR CheckBoxLabels.Override = ?)"
+ "UNION SELECT *, RANDOM() As Random FROM [StepperLabels] LEFT JOIN [Questions] WHERE (Questions.CPQID = StepperLabels.CPQID AND (StepperLabels.Value = ? AND StepperLabels.Override IS NULL)) AND (Questions.QuestionCarePlanArea LIKE '%Intervention%' OR StepperLabels.Override = ?)"
+ "ORDER BY Random LIMIT 5"
, DistressLevelType, DistressType.DistressTypeValue(DistressLevelType), DistressLevelType));
}
return(r);
}
public async Task <ObservableRangeCollection <Response> > GetDistressExpressions(string DistressLevelType)
{
/*
* This function pulls the Top Half of the Support Plan.
*/
ObservableRangeCollection <Response> r = new ObservableRangeCollection <Response>();
if (DistressLevelType.Equals("Calm"))
{
//Calm Request, pull every response that is checked with a "Calm" override OR if the Question associated with the checked response has a QuestionCarePlanArea containing "Positive"
//This will also pull Stepper Responses if the value is >0 and the QuestionCarePlanArea contains "Positive"
//When using UNION in a query, the ORDER BY clause is restricted to a specific column
r.AddRange(await db.QueryAsync <Response>(
"SELECT *, RANDOM() As Random FROM [CheckBoxLabels] LEFT JOIN [Questions] WHERE (Questions.CPQID = CheckBoxLabels.CPQID AND CheckBoxLabels.Value = 1) AND (Questions.QuestionCarePlanArea LIKE '%Positive%' OR CheckBoxLabels.Override = ?)"
+ "UNION SELECT *, RANDOM() As Random FROM [StepperLabels] LEFT JOIN [Questions] WHERE (Questions.CPQID = StepperLabels.CPQID AND StepperLabels.Value > 1) AND (Questions.QuestionCarePlanArea LIKE '%Positive%' OR StepperLabels.Override = ?)"
+ "ORDER BY Random LIMIT 5", DistressLevelType, DistressLevelType));
}
else
{
//Non Calm Request, pull every response with DistressLevelType override OR if the Question associated with the checked response has a QuestionCarePlanArea containing "Distress Actions"
//Since Override takes priority over the actual Value of the stepper, the tuples that will be added that have the correct value will only be added if the Override is empty
r.AddRange(await db.QueryAsync <Response>(
"SELECT *, RANDOM() As Random FROM [CheckBoxLabels] LEFT JOIN [Questions] WHERE (Questions.CPQID = CheckBoxLabels.CPQID AND CheckBoxLabels.Value = 1) AND (Questions.QuestionCarePlanArea LIKE '%Distress Actions%' OR CheckBoxLabels.Override = ?)"
+ "UNION SELECT *, RANDOM() As Random FROM [StepperLabels] LEFT JOIN [Questions] WHERE (Questions.CPQID = StepperLabels.CPQID AND (StepperLabels.Value = ? AND StepperLabels.Override IS NULL)) AND (Questions.QuestionCarePlanArea LIKE '%Distress Actions%' OR StepperLabels.Override = ?)"
+ "ORDER BY Random LIMIT 5", DistressLevelType, DistressType.DistressTypeValue(DistressLevelType), DistressLevelType));
if (DistressLevelType.Equals("Acute"))
{
//Acute Request, in addition to pulling every response associated with it, it will also append all responses that have an Override value of "LT-Acute", regardless of value above 0
//This will also go above the previous 5 limit, which will also ensure that this partitiion of the query always is appended
r.AddRange(await db.QueryAsync <Response>("SELECT * FROM [StepperLabels] WHERE (StepperLabels.Override = 'LT-Acute' AND Value > 0 AND Value IS NOT NULL)"));
}
}
return(r);
}
