public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); String dbObject = "v_AP_TESTS"; sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); //Adds " ... AND (SexCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.NONE, "Sex", Marshaller.sexCodes)); //Adds " ... AND (RaceCode in (1, 2, 3, 4, 5)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "Race", Marshaller.raceCodes)); sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "grade", dbObject)); //Adds " ... AND ((year >= 1997) AND (year <= 2007)) ..." sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.AND, "year", Marshaller.years)); //Static required: sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "ExamCode", 99.ToString())); sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.AND, "SchoolType", dbObject)); sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "fullkey")); return(sql.ToString()); }
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); String dbObject = "v_SuspensionsSchoolDistState"; sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.NONE, "SchoolType", dbObject)); ////Adds " ... AND (SexCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "SexCode", Marshaller.sexCodes)); //Adds " ... AND (RaceCode in (1, 2, 3, 4, 5)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "RaceCode", Marshaller.raceCodes)); //Adds " ... AND ((GradeCode >= 16) AND (GradeCode <= 64)) ..." sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "GradeCode", dbObject)); //Adds " ... AND (DisabilityCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "DisabilityCode", Marshaller.disabilityCodes)); //Adds " ... AND ((year >= 1997) AND (year <= 2007)) ..." sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.AND, "year", Marshaller.years)); sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey")); sql.AppendFormat(SQLHelper.GetOrderByClause(Marshaller.orderByList)); return(sql.ToString()); }
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); GlobalValues globals = Marshaller.GlobalValues; String dbObject = ((globals.SubjectID.Key == SubjectIDKeys.Reading || globals.SubjectID.Key == SubjectIDKeys.Math) ? "v_WSASDemographics" : "v_WSASDemographics4810" ); sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years)); //fullkey if (Marshaller.GlobalValues.OrgLevel.Key == OrgLevelKeys.District) { sql.Append(" AND right(fullkey,6) = '03XXXX' ");// all districts in LOCATION } else //School Level -- State not Supported { sql.Append(" AND right(fullkey,4) <> 'XXXX' ");//- all school in LOCATION } //Location if (Marshaller.GlobalValues.LF.Key == LFKeys.CESA) { sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "CESA", Marshaller.GlobalValues.Agency.CESA)); } else if (Marshaller.GlobalValues.LF.Key == LFKeys.County) { sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "county", Marshaller.GlobalValues.Agency.County)); } //else // State sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "Racecode", Marshaller.raceCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "sexcode", Marshaller.sexCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "DisabilityCode", Marshaller.disabilityCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "migrantcode", Marshaller.migrantCodes)); sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "GradeCode", dbObject)); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "FAYCode", "2")); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "EconDisadvCode", Marshaller.econDisadvCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "ELPCode", Marshaller.ELPCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "SubjectID", Marshaller.WsasSubjectCodes)); sql.AppendFormat(SQLHelper.GetOrderByClause(Marshaller.orderByList)); return(sql.ToString()); }
public string BuildWsasSimilarCurrentAgencyQuery(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); Marshaller.InitFullkeyList(); String dbObject = ((Marshaller.GlobalValues.SubjectID.Key == SubjectIDKeys.Reading || Marshaller.GlobalValues.SubjectID.Key == SubjectIDKeys.Math) ? "v_WSASDemographics" : "v_WSASDemographics4810" ); sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years)); String fullkey = SligoCS.BL.WI.FullKeyUtils.GetMaskedFullkey(Marshaller.GlobalValues.FULLKEY, Marshaller.GlobalValues.OrgLevel); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "FullKey", fullkey)); //and grade = '4' and subjectid = '1RE' and if (Marshaller.GlobalValues.Grade.Value == GradeKeys.AllDisAgg && Marshaller.GlobalValues.SuperDownload.Key != SupDwnldKeys.True) { sql.Append(" AND GradeCode <> 99 "); } else { sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "GradeCode", dbObject)); } sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "SubjectID", Marshaller.WsasSubjectCodes)); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "RaceCode", "9")); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "SexCode", "9")); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "DisabilityCode", "9")); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "EconDisadvCode", "9")); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "ELPCode", "9")); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "Migrantcode", "9")); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "FAYCode", "2")); return(sql.ToString()); }
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); String dbObject = "v_ActivitiesSchoolDistState"; sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); //Adds " ... AND ((year >= 1997) AND (year <= 2007)) ..." sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years)); sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "fullkey")); sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.AND, "SchoolType", dbObject)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "ActivityCode", Marshaller.ActivityCodes)); sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "grade", dbObject)); sql.AppendFormat(SQLHelper.GetOrderByClause(Marshaller.orderByList)); return(sql.ToString()); }
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); String dbObject = "v_RetentionWWoDisEconELPSchoolDistState"; sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.NONE, "SchoolType", dbObject)); //Adds " ... AND (SexCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "SexCode", Marshaller.sexCodes)); //Adds " ... AND (RaceCode in (1, 2, 3, 4, 5)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "RaceCode", Marshaller.raceCodes)); sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "GradeCode", dbObject)); //Adds " ... AND (DisabilityCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "DisabilityCode", Marshaller.disabilityCodes)); //BR //Adds " ... AND (EconDisadvCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "EconDisadvCode", Marshaller.econDisadvCodes)); //Adds " ... AND (ELPCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "ELPCode", Marshaller.ELPCodes)); //Adds " ... AND ((year >= 1997) AND (year <= 2007)) ..." sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.AND, "year", Marshaller.years)); sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey")); //order by clause //sql.AppendFormat(" ORDER BY {0}", SQLHelper.ConvertToCSV(orderBy, false)); sql.Append(SQLHelper.GetOrderByClause(Marshaller.orderByList)); return(sql.ToString()); }
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); String dbObject = "v_Coursework"; sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years)); sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey")); //Adds " ... AND (SexCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "Sex", Marshaller.sexCodes)); sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "Grade", dbObject)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "CourseTypeID", Marshaller.CourseTypeCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "WMASID1", Marshaller.WMASCodes)); sql.Append(SQLHelper.GetOrderByClause(Marshaller.orderByList)); return(sql.ToString()); }
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); String dbObject = "v_wsas"; sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "SexCode", Marshaller.sexCodes)); // working around odd SQL Server performance issue by not using (RaceCode in ('1', '2', '3', '4', '5', '6', '8')) on Select/AllSchoolsIn if (Marshaller.GlobalValues.S4orALL.Key == S4orALLKeys.AllSchoolsOrDistrictsIn && Marshaller.GlobalValues.CompareTo.Key == CompareToKeys.SelSchools && Marshaller.GlobalValues.Group.Key == GroupKeys.Race) { sql.Append(" AND (RaceCode <> '9') "); } else { sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "RaceCode", Marshaller.raceCodes)); } sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "DisabilityCode", Marshaller.disabilityCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "EconDisadvCode", Marshaller.econDisadvCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "ELPCode", Marshaller.ELPCodes)); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "Migrantcode", Marshaller.migrantCodes)); sql.Append(" AND ( "); if (Marshaller.GlobalValues.Group.Key != GroupKeys.All) { sql.Append("(year < 2003 AND FAYCode = 9 ) OR (year > 2002 AND faycode = 2)"); } else { sql.Append("FAYCode = 2 AND left(fullkey,1) <> 'X'"); } sql.Append(" OR (FullKey in ('XXXXXXXXXXXX') AND faycode = 9)"); sql.Append(")"); if (Marshaller.GlobalValues.Grade.Value == GradeKeys.AllDisAgg && Marshaller.GlobalValues.SuperDownload.Key != SupDwnldKeys.True) { sql.Append(" AND GradeCode <> 99 "); } else { sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "GradeCode", dbObject)); } sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "SubjectID", Marshaller.WsasSubjectCodes)); if (Marshaller.GlobalValues.S4orALL.Key == S4orALLKeys.AllSchoolsOrDistrictsIn && Marshaller.GlobalValues.CompareTo.Key == CompareToKeys.SelSchools ) {// only include schooltype at school level sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.AND, "SchoolType", dbObject)); } sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey")); sql.Append(SQLHelper.GetOrderByClause(Marshaller.orderByList)); //throw new Exception(sql.ToString()); return(sql.ToString()); }
public override String BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); String dbObject = "v_HSCWWoDisSchoolDistStateEconELPXYearRate"; sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject)); //Adds " ... AND (SexCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.NONE, "SexCode", Marshaller.sexCodes)); ////Adds " ... AND (RaceCode in (1, 2, 3, 4, 5)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "RaceCode", Marshaller.raceCodes)); sql.Append(Marshaller.GradeCodesClause(SQLHelper.WhereClauseJoiner.AND, "GradeCode", dbObject)); //Adds " ... AND (DisabilityCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "DisabilityCode", Marshaller.disabilityCodes)); //Adds " ... AND (EconDisadvCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "EconDisadv", Marshaller.econDisadvCodes)); //Adds " ... AND (ELPCode in (1, 2)) ..." sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "ELPCode", Marshaller.ELPCodes)); //Adds " ... AND ((year >= 1997) AND (year <= 2007)) ..." sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.AND, "year", Marshaller.years)); sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey")); //Special Case for Compare To OrgLevel //Distirct and State Levels should not reflect SchoolType if (Marshaller.GlobalValues.CompareTo.Key == CompareToKeys.OrgLevel) { sql.Append(SQLHelper.WhereClauseJoiner.AND); sql.Append("(("); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.NONE, "right(fullkey, 4)", "XXXX")); sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "SchoolType", Marshaller.GlobalValues.STYP.Range[STYPKeys.StateSummary])); sql.Append(") OR ("); sql.Append(SQLHelper.WhereClauseNotEquals(SQLHelper.WhereClauseJoiner.NONE, "right(fullkey, 4)", "XXXX")); sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "SchoolType", Marshaller.stypList)); sql.Append("))"); } if (Marshaller.GlobalValues.CompareTo.Key != CompareToKeys.OrgLevel) { sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.AND, "SchoolType", dbObject)); } //Timeframe TmFrm frame = Marshaller.GlobalValues.TmFrm; List <int> sqlTmFrm = null; if (frame.Key == TmFrmKeys.All) { sqlTmFrm = new List <int>(new int[] { 0, 1, 2, 3 }); } else if (frame.Key == TmFrmKeys.FourYear) { sqlTmFrm = new List <int>(new int[] { 1 }); } else if (frame.Key == TmFrmKeys.FiveYear) { sqlTmFrm = new List <int>(new int[] { 2 }); } else if (frame.Key == TmFrmKeys.SixYear) { sqlTmFrm = new List <int>(new int[] { 3 }); } else //if (frame.Key == TmFrmKeys.Legacy) { sqlTmFrm = new List <int>(new int[] { 0 }); } sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "timeframe", sqlTmFrm)); //order by clause //sb.AppendFormat(" ORDER BY {0}", SQLHelper.ConvertToCSV(orderBy, false)); sql.Append(SQLHelper.GetOrderByClause(Marshaller.orderByList)); return(sql.ToString()); }