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