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