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());
}
