private string GetQuery(ChartAcrossAssessmentsParameters parameters)
{
var dateRangeWhere = GetDateRange(parameters);
var ageWhere = GetAgeRange(parameters);
var genderWhere = GetGender(parameters);
var responseValues = GetResponseValues(parameters);
var responseValuesInt = GetResponseValuesInt(parameters);
if (responseValues.Length == 0)
{
responseValues = responseValuesInt;
responseValuesInt = string.Empty;
}
var union = string.Empty;
if (responseValuesInt.Length > 0)
{
union = " UNION " + string.Format(Query, parameters.OrganizationKey, ageWhere, responseValuesInt, genderWhere, dateRangeWhere);
}
var finalQuery = string.Format(Query, parameters.OrganizationKey, ageWhere, genderWhere, responseValues, dateRangeWhere);
if (responseValuesInt.Length > 0)
{
finalQuery += union;
}
var query = finalQuery + QueryOrder;
Logger.Info("ChartAcrossAssessments Query: {0}", query);
return(query);
}
private ChartAcrossAssessmentsData GetData(ChartAcrossAssessmentsParameters parameters)
{
var returnData = new ChartAcrossAssessmentsData();
using (var connection = _connectionFactory.CreateConnection())
{
var reportDtos = connection.Query <ChartAcrossAssessmentsDto>(GetQuery(parameters)).ToList();
if (!reportDtos.Any())
{
return(null);
}
var data = reportDtos.Select(
reportDto => new ChartAcrossAssessmentsDataObject
{
ResponseType = reportDto.ResponseType,
IsCode = reportDto.IsCode,
Age = reportDto.PatientAge,
Gender = reportDto.GenderCode.Substring(0, 1),
PatientName = reportDto.PatientFirstName + " " + reportDto.PatientLastName,
AssessmentDate = reportDto.LastModifiedTime.ToShortDateString(),
PatientKey = reportDto.PatientKey,
AssessmentInstanceKey = reportDto.AssessmentInstanceKey,
AssessmentName = _resourcesManager.GetResourceManagerByName(reportDto.AssessmentName).GetString("_" + reportDto.AssessmentCode),
ItemDefinitionCode = reportDto.ItemDefinitionCode,
Question = _resourcesManager.GetResourceManagerByName(reportDto.AssessmentName).GetString("_" + reportDto.ItemDefinitionCode),
Response = GetResponseValue(reportDto)
}).ToList();
returnData.Data = data;
}
return(returnData);
}
private string GetResponseValues(ChartAcrossAssessmentsParameters parameters)
{
var responseValues = string.Empty;
var or = string.Empty;
foreach (QuestionResponse qr in parameters.QuestionResponses)
{
switch (qr.InputType)
{
case "IntRange":
break;
case "MultipleSelect":
responseValues += GetMultiselectQuery(qr, or);
break;
default:
responseValues += GetStringQuery(qr, or);
break;
}
or = " OR ";
}
if (responseValues.Length > 0)
{
responseValues = " AND (" + responseValues + ")";
}
return(responseValues);
}
private List <QuestionResponse> GetQuestionResponsesFromResource(ChartAcrossAssessmentsParameters reportParams)
{
var returnResponses = reportParams.QuestionResponses;
foreach (var response in returnResponses)
{
response.LocalizedResponses = new List <string> ();
QuestionResponse response1 = response;
foreach (var r in response.Responses.Where(r => response1.InputType == "MultipleSelect").ToList())
{
var assessmentName = string.Empty;
var assessment = GetAssessmentDefinitionForResponse(response.AssessmentDefinitionKey.ToString());
if (assessment != null)
{
assessmentName = assessment.AssessmentName;
}
response.LocalizedResponses.Add(_resourcesManager.GetResourceManagerByName(assessmentName).GetString("_" + r));
}
foreach (var r in response.Responses.Where(r => response1.InputType != "MultipleSelect").ToList())
{
response.LocalizedResponses.Add(response1.InputType == "Height" ? GetHeightString(r) : r);
}
}
return(returnResponses);
}
private void SetStrings(ChartAcrossAssessmentsData data, ChartAcrossAssessmentsParameters reportParams, string reportName)
{
if (reportParams == null || data == null)
{
return;
}
var start = reportParams.StartDate;
var end = reportParams.EndDate;
var dateRange = start.GetValueOrDefault().ToShortDateString() + " - " + end.GetValueOrDefault().ToShortDateString();
if (reportParams.TimePeriod != null)
{
reportParams.TimePeriod.GetRange(out start, out end);
dateRange = reportParams.TimePeriod.DisplayName;
}
var ageRange = _resourcesManager.GetResourceManagerByName(reportName).GetString("NA");
if (reportParams.AgeRangeLow != null && reportParams.AgeRangeHigh != null)
{
ageRange = reportParams.AgeRangeLow + " - " + reportParams.AgeRangeHigh;
}
var gender = _resourcesManager.GetResourceManagerByName(reportName).GetString("NA");
if (!string.IsNullOrWhiteSpace(reportParams.Gender))
{
gender = reportParams.Gender;
}
var assessment = _resourcesManager.GetResourceManagerByName(reportName).GetString("All");
if (!string.IsNullOrWhiteSpace(reportParams.AssessmentName))
{
assessment = reportParams.AssessmentName;
}
data.AssessmentParameter = _resourcesManager.GetResourceManagerByName(reportName).GetString("Assessment") + ": " + assessment;
data.DateRangeParameter = _resourcesManager.GetResourceManagerByName(reportName).GetString("DateRange") + ": " + dateRange;
data.AgeGroupParameter = _resourcesManager.GetResourceManagerByName(reportName).GetString("AgeGroup") + ": " + ageRange;
data.GenderParameter = _resourcesManager.GetResourceManagerByName(reportName).GetString("Gender") + ": " + gender;
data.TotalQuestionsForQuery = _resourcesManager.GetResourceManagerByName(reportName).GetString("TotalQuestionsForQuery") +
": " + data.Data.GroupBy(a => a.ItemDefinitionCode).Select(group => group.First()).Count();
data.TotalNumberOfSpecifiedResults = _resourcesManager.GetResourceManagerByName(reportName).GetString("TotalNumberOfSpecificResults") +
": " + data.Data.Count();
data.TotalNumberOfAssessmentsDuringTimeFrame = _resourcesManager.GetResourceManagerByName(reportName).GetString("TotalNumberOfAssessmentsDuringTimeFrame") +
": " + data.Data.GroupBy(a => a.PatientKey).Select(group => group.First()).Count();
data.LocalizedQuestionResponses = GetQuestionResponsesFromResource(reportParams);
data.ReportName = _resourcesManager.GetResourceManagerByName(reportName).GetString("ReportName");
data.HeaderQuestion = _resourcesManager.GetResourceManagerByName(reportName).GetString("HeaderQuestion");
data.HeaderSpecificResponseValue = _resourcesManager.GetResourceManagerByName(reportName).GetString("HeaderSpecificResponse");
data.HeaderName = _resourcesManager.GetResourceManagerByName(reportName).GetString("HeaderName");
data.HeaderAge = _resourcesManager.GetResourceManagerByName(reportName).GetString("HeaderAge");
data.HeaderGender = _resourcesManager.GetResourceManagerByName(reportName).GetString("HeaderGender");
data.HeaderAssessmentName = _resourcesManager.GetResourceManagerByName(reportName).GetString("HeaderAssessmentName");
data.HeaderAssessmentDate = _resourcesManager.GetResourceManagerByName(reportName).GetString("AssessmentDate");
data.HeaderGivenResponse = _resourcesManager.GetResourceManagerByName(reportName).GetString("GivenResponse");
data.HeaderViewAssessment = _resourcesManager.GetResourceManagerByName(reportName).GetString("HeaderViewAssessment");
}
private string GetGender(ChartAcrossAssessmentsParameters parameters)
{
const string genderWhereString = " PatientModule.Patient.GenderCode = '{0}'";
var genderWhere = string.Empty;
if (!string.IsNullOrWhiteSpace(parameters.Gender))
{
genderWhere = " AND " + string.Format(genderWhereString, parameters.Gender);
}
return(genderWhere);
}
private string GetAgeRange(ChartAcrossAssessmentsParameters parameters)
{
const string ageWhereString = @" FLOOR((CAST (GetDate() AS INTEGER) - CAST(DateOfBirth AS INTEGER)) / 365.25) >= {0}
AND FLOOR((CAST (GetDate() AS INTEGER) - CAST(DateOfBirth AS INTEGER)) / 365.25) <= {1}" ;
var ageWhere = string.Empty;
if (parameters.AgeRangeLow != null && parameters.AgeRangeHigh != null)
{
ageWhere = " AND " + string.Format(ageWhereString, parameters.AgeRangeLow, parameters.AgeRangeHigh);
}
return(ageWhere);
}
private string GetDateRange(ChartAcrossAssessmentsParameters parameters)
{
const string dateRangeWhereString = " AND (LastModifiedTime >= '{0}' AND LastModifiedTime <= DATEADD(day,1, '{1}'))";
var startDate = parameters.StartDate;
var endDate = parameters.EndDate;
if (parameters.TimePeriod != null)
{
parameters.TimePeriod.GetRange(out startDate, out endDate);
}
return(string.Format(dateRangeWhereString, startDate.GetValueOrDefault().ToShortDateString(), endDate.GetValueOrDefault().ToShortDateString()));
}
private string GetResponseValuesInt(ChartAcrossAssessmentsParameters parameters)
{
var responseValues = string.Empty;
foreach (QuestionResponse qr in parameters.QuestionResponses)
{
switch (qr.InputType)
{
case "IntRange":
responseValues += GetIntRangeQuery(qr, string.Empty);
break;
}
}
if (responseValues.Length > 0)
{
responseValues = " AND (" + responseValues + ")";
}
return(responseValues);
}