public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller) { StringBuilder sql = new StringBuilder(); Marshaller.InitFullkeyList(); sql.Append("select 'LowGradePY' = isnull(min(LowGrade),'12'), 'HighGradePY' = isnull(max(HighGrade),'64') FROM [tblAgencyFull] where "); sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.NONE, "FullKey")); 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()); }