private void selectCondensedColumns(SQLStringBuilder queryString)
{
string[] columns = { "ld.Division", "COUNT(ld.learnerKey)", "ld.learnerStatus" };
queryString.Select(columns);
queryString.From("learner_Details ld");
}
private void includeRegDate(SQLStringBuilder queryString, bool filterByDate)
{
var subQuery = new SQLStringBuilder();
subQuery.Scope("Registration Dates", () =>
{
string[] subColumns = { "learnerKey", "MIN(regDate) as theRegDate" };
subQuery.Select(subColumns);
subQuery.From("enrollments");
subQuery.GroupBy("learnerKey");
if (filterByDate)
{
filterHavingByDate(subQuery);
}
});
if (filterByDate)
{
queryString.RightJoin(String.Format("{0} as e", subQuery.ToString()), "e.LearnerKey = ld.LearnerKey");
}
else
{
queryString.LeftJoin(String.Format("{0} as e", subQuery.ToString()), "e.LearnerKey = ld.LearnerKey");
}
}
private void selectComprehensiveColumns(SQLStringBuilder queryString)
{
string[] columns = {"ld.learnerNumber",
"ld.Name",
"ld.Surname",
"ld.id",
"DATE(e.theRegDate)",
"ld.learnerStatus",
"d.divisionName as Division",
"ld.division as divisionAbrev"};
queryString.Select(columns);
queryString.From("learner_Details ld");
}
private void filterByStatus(SQLStringBuilder queryString)
{
queryString.And(String.Format("learnerStatus = '{0}'", Status.GetStringValue()));
}
private void filterHavingByDate(SQLStringBuilder subQuery)
{
subQuery.Having(String.Format("( (DATE(MIN(regDate)) BETWEEN {0} AND {1} ) OR ( MIN(regDate) IS NULL) )", StartDate.ToMySql(), EndDate.ToMySql()));
}
private string buildCondensedQuery()
{
SQLStringBuilder queryString = new SQLStringBuilder();
var report = new rptLearnerStatusComprehensive();
selectCondensedColumns(queryString);
includeRegDate(queryString, UseDate);
includeDivision(queryString);
limitToRegisteredEnrollments(queryString);
if (ShouldFilterByStatus)
{
filterByStatus(queryString);
}
if (!IncludeECD)
{
excludeECD(queryString);
}
if (ShouldFilterByCollege)
{
filterByCollege(queryString);
}
groupByDivision(queryString);
orderByLearnerNumber(queryString);
return queryString.ToString();
}
private void filterByCollege(SQLStringBuilder queryString)
{
queryString.And(String.Format("ld.division = '{0}'", College));
}
private static void orderByLearnerNumber(SQLStringBuilder queryString)
{
queryString.OrderBy("ld.learnerNumber");
}
private static void limitToRegisteredEnrollments(SQLStringBuilder queryString)
{
queryString.Where("NOT e.theRegDate IS NULL");
}
private static void includeDivision(SQLStringBuilder queryString)
{
queryString.Join("division d", "d.divisionAbrev = ld.division");
}
private static void groupByDivision(SQLStringBuilder queryString)
{
queryString.GroupBy("division");
}
private static void excludeECD(SQLStringBuilder queryString)
{
queryString.And("ld.division NOT LIKE '%ECD%'");
}