public String GradeCodesClause(SQLHelper.WhereClauseJoiner join, String field, String dbObject)
{
if (gradeCodes.ForceDisAgg)
{
return(BuildAutoGradeCodeClause(join, field, dbObject));
}
else
{
return(SQLHelper.WhereClauseSingleValueOrInclusiveRange(join, field, gradeCodes));
}
}
public String FullkeyClause(SQLHelper.WhereClauseJoiner join, String field)
{
if (globals.CompareTo.Key == CompareToKeys.SelSchools ||
globals.CompareTo.Key == CompareToKeys.SelDistricts)
{
return(" " + SQLHelper.GetJoinerString(join) + " " + BuildClauseForCompareToSelected());
}
else
{
return(SQLHelper.WhereClauseValuesInList(join, field, fullkeylist));
}
}
public String STYPClause(SQLHelper.WhereClauseJoiner join, String field, String dbObject)
{
String clause;
if (stypList.ForceDisAgg && GlobalValues.Group.Key == GroupKeys.Grade)
{
clause = " " + SQLHelper.GetJoinerString(join) + " ( SchoolType in ('9') AND right(fullkey, 1) = 'X' OR right(fullkey, 1) <> 'X' "
+ SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, field, stypList) + " ) ";
}
else
{
clause = SQLHelper.WhereClauseValuesInList(join, field, stypList);
}
return(clause + " " + SQLHelper.GetJoinerString(SQLHelper.WhereClauseJoiner.AND) + " AgencyType IN ('03', '04', '4C', '49', 'XX')");
}
public String BuildAutoGradeCodeClause(SQLHelper.WhereClauseJoiner join, String field, String dbObject)
{
String strOrAggGradeCode = String.Empty;
Int16 gradeCode = Int16.Parse(GlobalValues.Grade.Value);
Int16 floor = (GradeCodeFloorMap.ContainsKey(gradeCode))
? GradeCodeFloorMap[gradeCode]
: GradeCodeFloorMap[99];
//Include Combined Grades only for pages that have individual grade selections
if (GlobalValues.Group.Key != GroupKeys.Grade) // i.e. this clause was triggered some other way
{
strOrAggGradeCode = "OR {0} = '" +
//WSAS Exception for "All Tested" Flag
((GradeCodeFloorMap.ContainsKey(gradeCode) && GlobalValues.Grade.Value != "0")
? GlobalValues.Grade.Value : "99")
+ "' ";
}
return(" " + SQLHelper.GetJoinerString(join) + " " +
String.Format(@"(
(--lowgrade:
{0} >= (select top 1 CASE WHEN lowgrade < {2} THEN {2} ELSE lowgrade END from tblAgencyFull where {1}.year = tblAgencyFull.year and {1}.fullkey = tblAgencyFull.fullkey)
OR fullkey = 'XXXXXXXXXXXX' AND {0} >= {2}
)--/lowgrade
AND
(--highgrade
{0} <= (select top 1 highgrade from tblAgencyFull where {1}.year = tblAgencyFull.year and {1}.fullkey = tblAgencyFull.fullkey)
OR fullkey = 'XXXXXXXXXXXX' AND {0}< 70
)--/highgrade
" + strOrAggGradeCode + @"
) "
, field
, dbObject
, floor
));
}
