public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder sql = new StringBuilder();
String dbObject = "v_course_offerings";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years));
sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "CourseTypeID", Marshaller.CourseTypeCodes));
sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "WMASID1", Marshaller.WMASCodes));
sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey"));
// jdj: must exclude null district records - workaround - databse object design is messy and needs refactoring
sql.Append(" and linkeddistrictname IS NOT NULL ");
// jdj: must exclude topic code 999 when selecting Subject: Other - workaround - databse object design is messy and needs refactoring
// mzd: Except when viewing CAPP
if (Marshaller.GlobalValues.WMAS.Key == WMASKeys.Other && Marshaller.GlobalValues.CourseTypeID.Key != CourseTypeIDKeys.CAPP)
{
sql.Append(" and v_course_offerings.topic <> '999' ");
}
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_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 v_TeacherQualifications GetTeacherQualifications(List<int> raceCode,
* List<int> sexCode,
* List<int> year,
* List<string> fullKey,
* List<int> schoolType,
* string subjectCode,
* string clauseForCompareToSchoolsDistrict,
* bool useFullkeys,
* List<string> orderBy)*/
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
//v_TeacherQualifications ds = new v_TeacherQualifications();
StringBuilder sql = new StringBuilder();
String dbObject = "v_TeacherQualifications";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.NONE, "SchoolTypeCode", 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 ((year >= 1997) AND (year <= 2007)) ..."
sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.AND, "year", Marshaller.years));
sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey"));
//LinkSubjectCode
sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "LinkSubjectCode", Marshaller.TQSubjectCodes));
//order by clause
//sb.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_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();
String dbObject = "v_ACT";
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));
////Adds " ... AND ((year >= 1997) AND (year <= 2007)) ..."
sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.AND, "year", Marshaller.years));
if (Marshaller.GlobalValues.CompareTo.Key != CompareToKeys.OrgLevel)
{
sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.AND, "SchoolType", dbObject));
}
sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey"));
//order by clause
sql.Append(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 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());
}
private SimilarRange InitDemographicsRange(SligoCS.BL.WI.QueryMarshaller Marshaller, String Name, String Column)
{
String strMeasure = Marshaller.Database.GetStringColumn(Column);
double meas = Double.Parse(strMeasure);
double min, max;
if (meas < 10.01)
{
min = 0;
max = 10;
}
else if (meas < 25.01)
{
min = 10.01;
max = 25;
}
else if (meas < 50.01)
{
min = 25.01;
max = 50;
}
else //if (meas > 50)
{
if (Name == Marshaller.GlobalValues.ECON.Name &&
meas > 75)
{
min = 75.01;
}
else
{
min = 50.01;
}
if (Name == Marshaller.GlobalValues.ECON.Name &&
meas < 75.01)
{
max = 75;
}
else
{
max = 100; // "infinity"
}
}
SimilarRange range = new SimilarRange();
range.MeasureName = Name;
range.Min = min.ToString();
range.Max = max.ToString();
return(range);
}
/// <summary>
/// NOT USED AS OF Jan '10 --mzd
/// </summary>
/// <param name="Marshaller"></param>
/// <returns></returns>
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
throw new Exception("An error has occured. Please copy the URL from your browser and email the site administrator.");
StringBuilder sql = new StringBuilder();
String dbObject = "v_AgencyFull";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.NONE, "FullKey"));
sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "AND", Marshaller.years));
return(sql.ToString());
}
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * FROM v_AgencyFullDistinct");
sql.Append(" WHERE ");
//must use the real fullkey, not "org level masked"
sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.NONE, "FULLKEY", Marshaller.GlobalValues.FULLKEY));
//sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "year", new List<String>(new String[] { Marshaller.GlobalValues.Year.ToString() })));
//sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "year", new List<int>(new int[] {Marshaller.AgencyYear })));
return(sql.ToString());
}
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder sql = new StringBuilder();
String dbObject = "vExpulsionServicesAndReturns";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years));
sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey"));
sql.Append(SQLHelper.GetOrderByClause(Marshaller.orderByList));
return(sql.ToString());
}
public String OrderByClause(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder order = new StringBuilder();
String fullkey = SligoCS.BL.WI.FullKeyUtils.GetMaskedFullkey(Marshaller.GlobalValues.FULLKEY, Marshaller.GlobalValues.OrgLevel);
String floatToTop = (Marshaller.GlobalValues.Sim.Key == SimKeys.AllSimilar)
? String.Empty
: " (case fullkey when '" + fullkey + "' then fullkey end) desc, "
;
order.Append(" ORDER BY ");
if (Marshaller.GlobalValues.WOW.Key == WOWKeys.WSASCombined &&
Marshaller.GlobalValues.SORT.Key == SORTKeys.AdvPlusProf)
{
order.Append(floatToTop + " (cast(dbo.[ConvertNonNumericCodesToZeroFloat](AdvancedPlusProficientTotalWSAS) as numeric(9,1)) ) desc ");
}
if (Marshaller.GlobalValues.WOW.Key == WOWKeys.WSASCombined &&
Marshaller.GlobalValues.SORT.Key == SORTKeys.Advanced)
{
order.Append(floatToTop + " cast(dbo.[ConvertNonNumericCodesToZeroFloat](AdvancedWSAS) as numeric(9,1)) desc");
}
//WKCE, Advanced + Proficient:
if (Marshaller.GlobalValues.WOW.Key == WOWKeys.WKCE &&
Marshaller.GlobalValues.SORT.Key == SORTKeys.AdvPlusProf)
{
order.Append(floatToTop + " (cast(dbo.[ConvertNonNumericCodesToZeroFloat]([PCTAdvPlusPCTPrf]) as numeric(9,1))) desc ");
}
//WKCE, Advanced:
if (Marshaller.GlobalValues.WOW.Key == WOWKeys.WKCE &&
Marshaller.GlobalValues.SORT.Key == SORTKeys.Advanced)
{
order.Append(floatToTop + " cast(dbo.[ConvertNonNumericCodesToZeroFloat]([Percent Advanced]) as numeric(9,1)) desc ");
}
if (order.Length > 1)
{
order.Append(", ");
}
order.Append("[" + v_WSASDemographics.District_Name + "]");
return(order.ToString());
}
public SimilarRanges InitSimilarRanges(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
SimilarRanges ranges = new SimilarRanges();
DataSet ds = Marshaller.Database.DataSet;
Marshaller.InitLists();
Marshaller.Database.SQL = BuildWsasSimilarCurrentAgencyQuery(Marshaller);
Marshaller.ManualQuery();
// throw new Exception(Marshaller.Database.SQL);
GlobalValues globals = Marshaller.GlobalValues;
if (globals.SIZE.Key == SIZEKeys.On)
{
ranges.Add(InitSizeRange(Marshaller));
}
if (globals.SPEND.Key == SPENDKeys.On)
{
ranges.Add(InitSpendingRange(Marshaller));
}
if (globals.ECON.Key == ECONKeys.On)
{
ranges.Add(InitDemographicsRange(Marshaller, globals.ECON.Name, v_WSASDemographics.PctEcon));
}
if (globals.LEP.Key == LEPKeys.On)
{
ranges.Add(InitDemographicsRange(Marshaller, globals.LEP.Name, v_WSASDemographics.PctLEP));
}
if (globals.DISABILITY.Key == DISABILITYKeys.On)
{
ranges.Add(InitDemographicsRange(Marshaller, globals.DISABILITY.Name, v_WSASDemographics.PctDisabled));
}
//StringBuilder sb = new StringBuilder();
//ranges.ForEach(delegate(SimilarRange rng) { sb.Append(String.Format("name: {0}; min: {1}; max {2};", rng.MeasureName, rng.Min, rng.Max)); });
//throw new Exception(sb.ToString());
return(ranges);
}
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder sql = new StringBuilder();
String dbObject = "v_Grad_Reqs";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years));
sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "FullKey"));
sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.AND, "subjectID", Marshaller.GradReqSubjCodes));
//order by clause
//sb.AppendFormat(" ORDER BY {0}", SQLHelper.ConvertToCSV(orderBy, false));
sql.Append(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 v_TeacherQualificationsScatterPlot
* GetTeacherQualificationsScatterPlot(
* string year,
* List<string> fullKey,
* List<int> schoolType,
* string subjectCode,
* string teacherVariableCode,
* string relatedToKey,
* string locationCode,
* int county,
* string cesa,
* string orgLevel,
* List<string> orderBy)*/
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder sql = new StringBuilder();
String dbObject = "v_TeacherQualificationsScatterPlot";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.NONE, "SchoolTypeCode", dbObject));
sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "year", Marshaller.years));
if (Marshaller.GlobalValues.TQLocation.Key == TQLocationKeys.CESA)
{
sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "CESA", Marshaller.GlobalValues.Agency.CESA));
}
else if (Marshaller.GlobalValues.TQLocation.Key == TQLocationKeys.County)
{
sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "County", Marshaller.GlobalValues.Agency.County));
}
//LinkSubjectCode
sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "LinkSubjectCode", Marshaller.GlobalValues.TQSubjectsSP.Value));
//Related key
sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "RelateToKey", Marshaller.GlobalValues.TQRelateTo.Value));
if (Marshaller.GlobalValues.OrgLevel.Key == OrgLevelKeys.School)
{
sql.Append(" and right(fullkey,1) <> 'X' and left(fullkey,1) <> 'X' ");
}
else
{
sql.Append(" and right(fullkey,1) = 'X' and left(fullkey,1) <> 'X' ");
}
//order by clause
//sb.AppendFormat(" ORDER BY {0}", sql.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_Expend_2";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.NONE, "FullKey"));
////CT for cost category
sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "CT", Marshaller.CostTypeCodes));
////Adds " ... AND ((year >= 1997) AND (year <= 2007)) ..."
sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.AND, "year", Marshaller.years));
////order by clause
sql.Append(SQLHelper.GetOrderByClause(Marshaller.orderByList));
return(sql.ToString());
}
public static String WSASGrades(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder sql = new StringBuilder();
sql.Append("select distinct gradecode from v_WSAS where ");
if (SligoCS.BL.WI.FullKeyUtils.GetOrgLevelFromFullKeyX(Marshaller.GlobalValues.FULLKEY).Key == OrgLevelKeys.School)
{ //Regardless of OrgLevel, use the fullkey if a school has been selected
sql.Append("year = (select max(year) from v_WSAS where fullkey = '");
sql.Append(Marshaller.GlobalValues.FULLKEY);
sql.Append("' and year <= '" + Marshaller.GlobalValues.Year.ToString() + "')");
}
else
{
sql.Append("year <= '" + Marshaller.GlobalValues.Year.ToString() + "'");
}
sql.Append(SQLHelper.WhereClauseEquals(SQLHelper.WhereClauseJoiner.AND, "fullkey", Marshaller.GlobalValues.FULLKEY));
sql.Append(" order by gradecode asc ");
return(sql.ToString());
}
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder sql = new StringBuilder();
String dbObject = "v_Disabilities2";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
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, "SchoolTypeCode", dbObject));
sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "RaceCode", Marshaller.raceCodes));
sql.Append(SQLHelper.WhereClauseValuesInList(SQLHelper.WhereClauseJoiner.AND, "SexCode", Marshaller.sexCodes));
sql.Append(SQLHelper.GetOrderByClause(Marshaller.orderByList));
return(sql.ToString());
}
public System.Collections.Hashtable GetLowGradeHiGradePY(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
System.Collections.Hashtable LowGrHiGr = new System.Collections.Hashtable();
Marshaller.GlobalValues.SQL = this.BuildSQL(Marshaller);
Marshaller.AssignQuery(this, Marshaller.GlobalValues.SQL);
if (Table.Rows.Count < 1)
{
if (Marshaller.GlobalValues.TraceLevels > 0)
{
throw new Exception("query returned empty;");
}
return(null);
}
LowGrHiGr["low"] = GetStringColumn("LowGradePY");
LowGrHiGr["hi"] = GetStringColumn("HighGradePY");
return(LowGrHiGr);
}
private SimilarRange InitSizeRange(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
String strSize = Marshaller.Database.GetStringColumn(v_WSASDemographics.District_Size);
Int32 size = Int32.Parse(strSize);
Int32 min, max;
if (size < 501)
{
min = 1;
max = 500;
}
else if (size < 1001)
{
min = 501;
max = 1000;
}
else if (size < 2001)
{
min = 1001;
max = 2000;
}
else if (size < 10001)
{
min = 2001;
max = 10000;
}
else
{
min = 10001;
max = Int32.MaxValue; //"infinity"
}
SimilarRange range = new SimilarRange();
range.MeasureName = Marshaller.GlobalValues.SIZE.Name;
range.Min = min.ToString();
range.Max = max.ToString();
return(range);
}
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_WRCT";
sql.Append(Marshaller.SelectListFromVisibleColumns(dbObject));
sql.Append(SQLHelper.WhereClauseSingleValueOrInclusiveRange(SQLHelper.WhereClauseJoiner.NONE, "year", Marshaller.years));
sql.Append(Marshaller.FullkeyClause(SQLHelper.WhereClauseJoiner.AND, "fullkey"));
//view does not support schooltype, except for schools
if ((Marshaller.GlobalValues.CompareTo.Key == CompareToKeys.SelSchools || Marshaller.GlobalValues.CompareTo.Key == CompareToKeys.SelDistricts) &&
Marshaller.GlobalValues.S4orALL.Key == S4orALLKeys.AllSchoolsOrDistrictsIn)
{
sql.Append(Marshaller.STYPClause(SQLHelper.WhereClauseJoiner.AND, "SchoolType", 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());
}
//http://dpi.wi.gov/oea_similar
private SimilarRange InitSpendingRange(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
String strSpend = Marshaller.Database.GetStringColumn(v_WSASDemographics.Cost_Per_Member);
Double spend;
int min, max;
if (!Double.TryParse(strSpend, out spend))
{
spend = 0;
}
min = 0;
if (spend < 9500.01)
{
max = 9500;
}
else if (spend < 10500.01)
{
max = 10500;
}
else if (spend < 11500.01)
{
max = 11500;
}
else
{
max = Int32.MaxValue;
}
SimilarRange range = new SimilarRange();
range.MeasureName = Marshaller.GlobalValues.SPEND.Name;
range.Min = min.ToString() + ".0";
range.Max = max.ToString() + ".0";
return(range);
}
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)
{
return(GetAthleticConferenceListSQL());
}
public override string BuildSQL(SligoCS.BL.WI.QueryMarshaller Marshaller)
{
StringBuilder sql = new StringBuilder();
string demographics = new DALWSASDemographics().BuildSQL(Marshaller);
if (Marshaller.GlobalValues.Sim.Key == SimKeys.Default)
{
demographics = demographics.Replace("SELECT", "SELECT top 6 ");
}
if (Marshaller.GlobalValues.NoChce.Key != NoChceKeys.On)
{
String[] initial = demographics.Split(
(new string[1] {
"WHERE"
}),
StringSplitOptions.None
);
GlobalValues globals = Marshaller.GlobalValues;
List <String> whereclause = new List <String>();
String frmt = String.Empty;
Ranges = InitSimilarRanges(Marshaller);
frmt = (globals.Sim.Key == SimKeys.AllSimilar)
? FRMT_BETWEEN
: FRMT_GREATERTHAN
;
if (Ranges.Exists(globals.SIZE.Name))
{
whereclause.Add(FindAndFormatRange(FRMT_BETWEEN, globals.SIZE.Name, Ranges, v_WSASDemographics.District_Size));
}
if (Ranges.Exists(globals.SPEND.Name))
{
String frmtSpend;
if (globals.Sim.Key == SimKeys.AllSimilar)
{
frmtSpend = FRMT_BETWEEN_CHAR;
}
else
{
frmtSpend = FRMT_LESSTHAN_CHAR;
}
whereclause.Add(FindAndFormatRange(frmtSpend, globals.SPEND.Name, Ranges, v_WSASDemographics.Cost_Per_Member));
}
if (Ranges.Exists(globals.ECON.Name))
{
whereclause.Add(FindAndFormatRange(frmt, globals.ECON.Name, Ranges, v_WSASDemographics.PctEcon));
}
if (Ranges.Exists(globals.LEP.Name))
{
whereclause.Add(FindAndFormatRange(frmt, globals.LEP.Name, Ranges, v_WSASDemographics.PctLEP));
}
if (Ranges.Exists(globals.DISABILITY.Name))
{
whereclause.Add(FindAndFormatRange(frmt, globals.DISABILITY.Name, Ranges, v_WSASDemographics.PctDisabled));
}
if (globals.Sim.Key == SimKeys.Outperform)
{
String col;
if (globals.SORT.Key == SORTKeys.AdvPlusProf)
{
col = (globals.WOW.Key == WOWKeys.WKCE)? v_WSASDemographics.PCTAdvPlusPCTPrf : v_WSASDemographics.AdvancedPlusProficientTotalWSAS;
}
else
{
col = (globals.WOW.Key == WOWKeys.WKCE) ? v_WSASDemographics.Percent_Advanced : v_WSASDemographics.AdvancedWSAS;
}
//Marshaller initialized above in InitRanges...
string val = Marshaller.Database.GetStringColumn(col);
Decimal toss; //only required by TryParse
//If not suppressed, then add constraint:
if (Decimal.TryParse(val, out toss))
{
whereclause.Add("cast(dbo.[ConvertNonNumericCodesToZeroFloat]([" + col + "]) as float) >= " + val);
}
}
sql.Append(String.Join(" AND ", whereclause.ToArray()));
//Put it all back together
sql.Insert(0, initial[0] + " WHERE ");
sql.Append(" AND ");
sql.Append(initial[1]);
}
else
{
sql = new StringBuilder(demographics);
}
//before we add our custom order by, check that the framework hasn't added one:
if (sql.ToString().ToLower().Contains("order by"))
{ //remove the framework order-by-clause:
int start = sql.ToString().IndexOf("order by", StringComparison.CurrentCultureIgnoreCase);
sql.Remove(start, sql.Length - start);
}
sql.Append(OrderByClause(Marshaller));
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());
}
