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%'"); }