private async Task <IEnumerable <Document> > QueryDBCollectionAsync(BenchmarkCriteria criteria, string type)
{
var collectionName = _dataCollectionManager.GetLatestActiveTermByDataGroup(type);
var query = BuildQueryFromBenchmarkCriteria(criteria);
query = Exclude6Forms(query);
if (string.IsNullOrEmpty(query))
{
return(new List <Document>());
}
IQueryable <Document> result;
if (type == "Academies")
{
result = _client.CreateDocumentQuery <Document>(
UriFactory.CreateDocumentCollectionUri(DatabaseId, collectionName),
$"SELECT c['URN'], c['School Name'], c['Type'], 'A' AS FinanceType, c['No Pupils'] FROM c WHERE {query}");
}
else
{
result = _client.CreateDocumentQuery <Document>(
UriFactory.CreateDocumentCollectionUri(DatabaseId, collectionName),
$"SELECT c['URN'], c['School Name'], c['Type'], 'M' AS FinanceType, c['No Pupils'] FROM c WHERE {query}");
}
return(await result.QueryAsync());
}
private async Task <int> QueryDBTrustCollectionForCountAsync(BenchmarkCriteria criteria)
{
var collectionName = await _dataCollectionManager.GetLatestActiveCollectionIdByDataGroupAsync(DataGroups.MATOverview);
var container = _client.GetContainer(_databaseId, collectionName);
string queryString;
if (criteria == null)
{
queryString = $"SELECT VALUE COUNT(c) FROM c WHERE c.{SchoolTrustFinanceDataFieldNames.MEMBER_COUNT} > 1";
}
else
{
var query = BuildQueryFromBenchmarkCriteria(criteria);
query = ExcludeSAMATs(query);
queryString = $"SELECT VALUE COUNT(c) FROM c WHERE {query}";
}
var queryDefinition = new QueryDefinition(queryString);
var feedIterator = container.GetItemQueryIterator <int>(queryDefinition, null);
return((await feedIterator.ReadNextAsync()).FirstOrDefault());
}
private async Task <int> QueryDBSchoolCollectionForCountAsync(
BenchmarkCriteria criteria,
string type,
bool excludePartial = false,
bool excludeFeds = true)
{
var collectionName = await _dataCollectionManager.GetLatestActiveCollectionIdByDataGroupAsync(type);
var container = _client.GetContainer(_databaseId, collectionName);
var query = BuildQueryFromBenchmarkCriteria(criteria);
if (excludePartial)
{
query = ExcludePartials(query);
}
if (excludeFeds)
{
query = ExcludeFeds(query);
}
var queryString = $"SELECT VALUE COUNT(c) FROM c WHERE {query}";
if (string.IsNullOrEmpty(query))
{
queryString = $"SELECT VALUE COUNT(c) FROM c";
}
var queryDefinition = new QueryDefinition(queryString);
var feedIterator = container.GetItemQueryIterator <int>(queryDefinition, null);
return((await feedIterator.ReadNextAsync()).FirstOrDefault());
}
public BenchmarkChartListViewModel(List <ChartViewModel> modelList, SchoolComparisonListModel comparisonList, List <ChartViewModel> chartGroups,
ComparisonType comparisonType, BenchmarkCriteria advancedCriteria, SimpleCriteria simpleCriteria, BestInClassCriteria bicCriteria,
FinancialDataModel benchmarkSchoolData, EstablishmentType estabType, EstablishmentType searchedEstabType, string schoolArea, string selectedArea,
string latestTermAcademies, string latestTermMaintained, ComparisonArea areaType, string laCode, long?urn, int basketSize,
TrustComparisonListModel trustComparisonList = null, List <EstablishmentViewModelBase> comparisonSchools = null, bool excludePartial = false)
: base(modelList, comparisonList)
{
this.ChartGroups = chartGroups;
this.AdvancedCriteria = advancedCriteria;
this.SimpleCriteria = simpleCriteria;
this.BicCriteria = bicCriteria;
this.ComparisonType = comparisonType;
this.BenchmarkSchoolData = benchmarkSchoolData;
this.EstablishmentType = estabType;
this.SearchedEstablishmentType = searchedEstabType;
this.SchoolArea = schoolArea;
this.SelectedArea = selectedArea;
this.TrustComparisonList = trustComparisonList;
this.LatestTermAcademies = latestTermAcademies;
this.LatestTermMaintained = latestTermMaintained;
this.AreaType = areaType;
this.LaCode = laCode;
this.URN = urn;
this.BasketSize = basketSize;
this.ComparisonSchools = comparisonSchools;
this.ExcludePartial = excludePartial;
}
public async Task <ActionResult> Advanced(int companyNo, BenchmarkCriteria advancedCriteria)
{
var benchmarkTrust = await _trustBenchmarkListService.SetTrustAsDefaultAsync(companyNo);
var vm = new TrustCharacteristicsViewModel(benchmarkTrust, advancedCriteria);
return(View(vm));
}
public async Task <ActionResult> GenerateFromAdvancedCriteria(BenchmarkCriteria criteria, EstablishmentType estType, int?lacode, string urn, ComparisonArea areaType, BenchmarkListOverwriteStrategy overwriteStrategy = BenchmarkListOverwriteStrategy.Overwrite)
{
criteria.LocalAuthorityCode = lacode;
var benchmarkSchool = InstantiateBenchmarkSchool(urn);
switch (overwriteStrategy)
{
case BenchmarkListOverwriteStrategy.Overwrite:
var result = await _comparisonService.GenerateBenchmarkListWithAdvancedComparisonAsync(criteria, estType);
var cookie = base.UpdateSchoolComparisonListCookie(CompareActions.CLEAR_BENCHMARK_LIST, null);
Response.Cookies.Add(cookie);
foreach (var schoolDoc in result.BenchmarkSchools)
{
var benchmarkSchoolToAdd = new BenchmarkSchoolViewModel()
{
Name = schoolDoc.GetPropertyValue <string>("School Name"),
Type = schoolDoc.GetPropertyValue <string>("Type"),
FinancialType = schoolDoc.GetPropertyValue <string>("FinanceType") == "A" ? SchoolFinancialType.Academies.ToString() : SchoolFinancialType.Maintained.ToString(),
Urn = schoolDoc.GetPropertyValue <string>("URN")
};
cookie = base.UpdateSchoolComparisonListCookie(CompareActions.ADD_TO_COMPARISON_LIST, benchmarkSchoolToAdd);
Response.Cookies.Add(cookie);
}
break;
case BenchmarkListOverwriteStrategy.Add:
var comparisonList = base.ExtractSchoolComparisonListFromCookie();
var comparisonResult = await _comparisonService.GenerateBenchmarkListWithAdvancedComparisonAsync(criteria, estType, ComparisonListLimit.LIMIT - comparisonList.BenchmarkSchools.Count);
foreach (var schoolDoc in comparisonResult.BenchmarkSchools)
{
var benchmarkSchoolToAdd = new BenchmarkSchoolViewModel()
{
Name = schoolDoc.GetPropertyValue <string>("School Name"),
Type = schoolDoc.GetPropertyValue <string>("Type"),
FinancialType = schoolDoc.GetPropertyValue <string>("FinanceType") == "A" ? SchoolFinancialType.Academies.ToString() : SchoolFinancialType.Maintained.ToString(),
Urn = schoolDoc.GetPropertyValue <string>("URN")
};
cookie = base.UpdateSchoolComparisonListCookie(CompareActions.ADD_TO_COMPARISON_LIST, benchmarkSchoolToAdd);
Response.Cookies.Add(cookie);
}
break;
}
AddDefaultBenchmarkSchoolToList();
return(await Index(urn, null,
criteria, ComparisonListLimit.DEFAULT, benchmarkSchool.HistoricalSchoolFinancialDataModels.Last(), estType, ComparisonType.Advanced, areaType, lacode.ToString()));
}
private string BuildQueryFromBenchmarkCriteria(BenchmarkCriteria criteria)
{
var queryBuilder = new StringBuilder();
foreach (var property in (typeof(BenchmarkCriteria)).GetProperties())
{
var attribute = property.GetCustomAttributes(typeof(DBFieldAttribute)).FirstOrDefault();
if (attribute != null && attribute is DBFieldAttribute)
{
var fieldValue = criteria.GetType().GetProperty(property.Name).GetValue(criteria, null);
if (fieldValue != null)
{
var fieldName = (attribute as DBFieldAttribute).Name;
var fieldType = (attribute as DBFieldAttribute).Type;
switch (fieldType)
{
case CriteriaFieldComparisonTypes.MIN:
queryBuilder.Append($"c['{fieldName}'] >= {fieldValue}");
break;
case CriteriaFieldComparisonTypes.MAX:
queryBuilder.Append($"c['{fieldName}'] <= {fieldValue}");
break;
case CriteriaFieldComparisonTypes.EQUALTO:
if (fieldValue is int)
{
queryBuilder.Append($"c['{fieldName}'] = {fieldValue}");
}
else if (fieldValue is string)
{
queryBuilder.Append($"c['{fieldName}'] = '{fieldValue}'");
}
else if (fieldValue is Array)
{
queryBuilder.Append(
$"c['{fieldName}'] IN ('{string.Join("','", fieldValue as string[])}')");
}
break;
}
queryBuilder.Append(" AND ");
}
}
}
var query = queryBuilder.ToString();
return(string.IsNullOrEmpty(query) ? query : query.Substring(0, query.Length - 5));
}
public async Task <int> SearchSchoolsCountByCriteriaAsync(BenchmarkCriteria criteria, EstablishmentType estType)
{
if (estType == EstablishmentType.All)
{
var maintainedSchoolsCountTask = QueryDBCollectionForCountAsync(criteria, "Maintained");
var academiesCountTask = QueryDBCollectionForCountAsync(criteria, "Academies");
return((await maintainedSchoolsCountTask).First() + (await academiesCountTask).First());
}
else
{
var type = estType == EstablishmentType.Academy ? "Academies" : "Maintained";
var result = (await QueryDBCollectionForCountAsync(criteria, type)).First();
return(result);
}
}
public async Task <int> SearchSchoolsCountByCriteriaAsync(BenchmarkCriteria criteria, EstablishmentType estType, bool excludePartial = false)
{
if (estType == EstablishmentType.All)
{
var maintainedSchoolsCountTask = QueryDBSchoolCollectionForCountAsync(criteria, DataGroups.Maintained, excludePartial);
var academiesCountTask = QueryDBSchoolCollectionForCountAsync(criteria, DataGroups.Academies, excludePartial);
return((await maintainedSchoolsCountTask)+(await academiesCountTask));
}
else
{
var type = estType == EstablishmentType.Academies ? DataGroups.Academies : DataGroups.Maintained;
var result = (await QueryDBSchoolCollectionForCountAsync(criteria, type, excludePartial));
return(result);
}
}
public BenchmarkCriteria BuildFromSimpleComparisonCriteria(FinancialDataModel benchmarkSchoolData, bool includeFsm, bool includeSen, bool includeEal, bool includeLa, int percentageMargin = 0)
{
var criteria = new BenchmarkCriteria();
criteria.SchoolOverallPhase = new [] { benchmarkSchoolData.SchoolOverallPhase };
if (benchmarkSchoolData.UrbanRural != null)
{
criteria.UrbanRural = new[] { benchmarkSchoolData.UrbanRural };
}
var minMarginFactor = 1 - ((percentageMargin + CriteriaSearchConfig.PC_DEFAULT_MARGIN) / 100m);
var maxMarginFactor = 1 + ((percentageMargin + CriteriaSearchConfig.PC_DEFAULT_MARGIN) / 100m);
criteria.MinNoPupil = WithinPositiveLimits(benchmarkSchoolData.PupilCount - CriteriaSearchConfig.QC_DEFAULT_CONSTANT_PUPIL_COUNT_TOPUP) * minMarginFactor;
criteria.MaxNoPupil = (benchmarkSchoolData.PupilCount + CriteriaSearchConfig.QC_DEFAULT_CONSTANT_PUPIL_COUNT_TOPUP) * maxMarginFactor;
if (includeFsm)
{
var fsm = benchmarkSchoolData.PercentageOfEligibleFreeSchoolMeals;
criteria.MinPerFSM = WithinPercentLimits(fsm - percentageMargin);
criteria.MaxPerFSM = WithinPercentLimits(fsm + percentageMargin);
}
if (includeSen)
{
var sen = benchmarkSchoolData.PercentageOfPupilsWithSen;
criteria.MinPerSEN = WithinPercentLimits(sen - percentageMargin);
criteria.MaxPerSEN = WithinPercentLimits(sen + percentageMargin);
}
if (includeEal)
{
var eal = benchmarkSchoolData.PercentageOfPupilsWithEal;
criteria.MinPerEAL = WithinPercentLimits(eal - percentageMargin);
criteria.MaxPerEAL = WithinPercentLimits(eal + percentageMargin);
}
if (includeLa)
{
criteria.LocalAuthorityCode = benchmarkSchoolData.LaNumber;
}
criteria.PeriodCoveredByReturn = 12;
return(criteria);
}
public async Task <List <Document> > SearchSchoolsByCriteriaAsync(BenchmarkCriteria criteria, EstablishmentType estType)
{
if (estType == EstablishmentType.All)
{
var maintainedSchoolsTask = QueryDBCollectionAsync(criteria, "Maintained");
var academiesTask = QueryDBCollectionAsync(criteria, "Academies");
var maintainedSchools = (await maintainedSchoolsTask).ToList();
var academies = (await academiesTask).ToList();
maintainedSchools.AddRange(academies);
return(maintainedSchools);
}
else
{
var type = estType == EstablishmentType.Academy ? "Academies" : "Maintained";
return((await QueryDBCollectionAsync(criteria, type)).ToList());
}
}
//public async Task<IEnumerable<SchoolTrustFinancialDataObject>> GetTrustFinancialDataObjectAsync(int companyNo, string term, MatFinancingType matFinance)
//{
// var dataGroup = EstablishmentType.MAT.ToDataGroup(matFinance);
// var collectionName = await _dataCollectionManager.GetCollectionIdByTermByDataGroupAsync(term, dataGroup);
// var query = $"SELECT * FROM c WHERE c['{SchoolTrustFinanceDataFieldNames.COMPANY_NUMBER}']=@companyNo";
// SqlQuerySpec querySpec = new SqlQuerySpec(query);
// querySpec.Parameters = new SqlParameterCollection();
// querySpec.Parameters.Add(new SqlParameter($"@companyNo", companyNo));
// try
// {
// var documentQuery =
// _client.CreateDocumentQuery<SchoolTrustFinancialDataObject>(
// UriFactory.CreateDocumentCollectionUri(DatabaseId, collectionName),
// querySpec);
// return await documentQuery.QueryAsync();
// }
// catch (Exception ex)
// {
// if (term.Contains(_dataCollectionManager.GetLatestFinancialDataYearPerEstabTypeAsync(EstablishmentType.MAT).ToString()))
// {
// var errorMessage = $"{collectionName} could not be loaded! : {ex.Message} : {querySpec.Parameters[0].Name} = {querySpec.Parameters[0].Value}";
// base.LogException(ex, errorMessage);
// }
// return null;
// }
//}
public async Task <List <SchoolTrustFinancialDataObject> > SearchSchoolsByCriteriaAsync(
BenchmarkCriteria criteria,
EstablishmentType estType,
bool excludePartial = false,
bool excludeFeds = true)
{
if (estType == EstablishmentType.All)
{
var maintainedSchoolsTask = QueryDBSchoolCollectionAsync(criteria, DataGroups.Maintained, excludePartial, excludeFeds);
var academiesTask = QueryDBSchoolCollectionAsync(criteria, DataGroups.Academies, excludePartial, excludeFeds);
var maintainedSchools = (await maintainedSchoolsTask).ToList();
var academies = (await academiesTask).ToList();
maintainedSchools.AddRange(academies);
return(maintainedSchools);
}
else
{
return((await QueryDBSchoolCollectionAsync(criteria, estType.ToDataGroup(), excludePartial, excludeFeds)).ToList());
}
}
public BenchmarkCriteria BuildFromOneClickComparisonCriteria(FinancialDataModel benchmarkSchoolData, int percentageMargin = 0)
{
var criteria = new BenchmarkCriteria();
criteria.SchoolOverallPhase = new[] { benchmarkSchoolData.SchoolOverallPhase };
criteria.UrbanRural = new[] { benchmarkSchoolData.UrbanRural };
var minPcMarginFactor = 1 - ((percentageMargin + CriteriaSearchConfig.PC_DEFAULT_MARGIN) / 100m);
var maxPcMarginFactor = 1 + ((percentageMargin + CriteriaSearchConfig.PC_DEFAULT_MARGIN) / 100m);
criteria.MinNoPupil = WithinPositiveLimits(benchmarkSchoolData.PupilCount - CriteriaSearchConfig.QC_DEFAULT_CONSTANT_PUPIL_COUNT_TOPUP) * minPcMarginFactor;
criteria.MaxNoPupil = (benchmarkSchoolData.PupilCount + CriteriaSearchConfig.QC_DEFAULT_CONSTANT_PUPIL_COUNT_TOPUP) * maxPcMarginFactor;
var fsm = benchmarkSchoolData.PercentageOfEligibleFreeSchoolMeals;
criteria.MinPerFSM = WithinPercentLimits(fsm - percentageMargin);
criteria.MaxPerFSM = WithinPercentLimits(fsm + percentageMargin);
var sen = benchmarkSchoolData.PercentageOfPupilsWithSen;
criteria.MinPerSEN = WithinPercentLimits(sen - percentageMargin);
criteria.MaxPerSEN = WithinPercentLimits(sen + percentageMargin);
var eal = benchmarkSchoolData.PercentageOfPupilsWithEal;
criteria.MinPerEAL = WithinPercentLimits(eal - percentageMargin);
criteria.MaxPerEAL = WithinPercentLimits(eal + percentageMargin);
var ppGrantFunding = benchmarkSchoolData.PerPupilGrantFunding;
var minGfMarginFactor = 1 - ((percentageMargin + CriteriaSearchConfig.GF_DEFAULT_MARGIN) / 100m);
var maxGfMarginFactor = 1 + ((percentageMargin + CriteriaSearchConfig.GF_DEFAULT_MARGIN) / 100m);
criteria.MinPerPupilGrantFunding = ppGrantFunding * minGfMarginFactor;
criteria.MaxPerPupilGrantFunding = ppGrantFunding * maxGfMarginFactor;
criteria.LondonWeighting = benchmarkSchoolData.LondonWeighting == "Neither" ? new[] { "Neither" } : new[] { "Inner", "Outer" };
criteria.PeriodCoveredByReturn = 12;
return(criteria);
}
public BenchmarkCriteria BuildFromSimpleComparisonCriteria(SchoolFinancialDataModel benchmarkSchoolData, bool includeFsm, bool includeSen, bool includeEal, bool includeLa, int percentageMargin = 0)
{
var criteria = new BenchmarkCriteria();
criteria.SchoolOverallPhase = new [] { benchmarkSchoolData.SchoolOverallPhase };
criteria.UrbanRural = new [] { benchmarkSchoolData.UrbanRural };
var minMarginFactor = 1 - ((percentageMargin + CriteriaSearchConfig.DEFAULT_MARGIN) / 100d);
var maxMarginFactor = 1 + ((percentageMargin + CriteriaSearchConfig.DEFAULT_MARGIN) / 100d);
criteria.MinNoPupil = benchmarkSchoolData.PupilCount * minMarginFactor;
criteria.MaxNoPupil = benchmarkSchoolData.PupilCount * maxMarginFactor;
if (includeFsm)
{
var fsm = double.Parse(benchmarkSchoolData.PercentageOfEligibleFreeSchoolMeals);
criteria.MinPerFSM = (fsm - percentageMargin) < 0 ? 0 : (fsm - percentageMargin);
criteria.MaxPerFSM = fsm + percentageMargin;
}
if (includeSen)
{
var sen = double.Parse(benchmarkSchoolData.PercentageOfPupilsWithSen);
criteria.MinPerSEN = (sen - percentageMargin < 0) ? 0 : (sen - percentageMargin);
criteria.MaxPerSEN = sen + percentageMargin;
}
if (includeEal)
{
var eal = double.Parse(benchmarkSchoolData.PercentageOfPupilsWithEal);
criteria.MinPerEAL = (eal - percentageMargin) < 0 ? 0 : (eal - percentageMargin);
criteria.MaxPerEAL = eal + percentageMargin;
}
if (includeLa)
{
criteria.LocalAuthorityCode = benchmarkSchoolData.LaNumber;
}
return(criteria);
}
private async Task <IEnumerable <int> > QueryDBCollectionForCountAsync(BenchmarkCriteria criteria, string type)
{
var collectionName = _dataCollectionManager.GetLatestActiveTermByDataGroup(type);
var query = BuildQueryFromBenchmarkCriteria(criteria);
query = Exclude6Forms(query);
if (string.IsNullOrEmpty(query))
{
return(new List <int> {
0
});
}
var result =
_client.CreateDocumentQuery <int>(UriFactory.CreateDocumentCollectionUri(DatabaseId, collectionName),
$"SELECT VALUE COUNT(c) FROM c WHERE {query}");
return(await result.QueryAsync());
}
public BenchmarkCriteria BuildFromSpecialComparisonCriteria(FinancialDataModel benchmarkSchoolData, SpecialCriteria specialCriteria, int tryCount = 0)
{
var criteria = new BenchmarkCriteria();
criteria.SchoolOverallPhase = new[] { "Special" };
if (specialCriteria.SimilarPupils.GetValueOrDefault())
{
criteria.MinLowestAgePupils = benchmarkSchoolData.LowestAgePupils - CriteriaSearchConfig.SPECIALS_AGE_EXP_RANGE;
criteria.MaxLowestAgePupils = benchmarkSchoolData.LowestAgePupils + CriteriaSearchConfig.SPECIALS_AGE_EXP_RANGE;
criteria.MinHighestAgePupils = benchmarkSchoolData.HighestAgePupils - CriteriaSearchConfig.SPECIALS_AGE_EXP_RANGE;
criteria.MaxHighestAgePupils = benchmarkSchoolData.HighestAgePupils + CriteriaSearchConfig.SPECIALS_AGE_EXP_RANGE;
}
foreach (var sen in specialCriteria.TopSenCriteria)
{
criteria.FindAndSetMaxMinSenInCriteria(sen, tryCount);
}
return(criteria);
}
public BenchmarkCriteria BuildFromBicComparisonCriteria(FinancialDataModel benchmarkSchoolData, BestInClassCriteria bicCriteria, int percentageMargin = 0)
{
var bmCriteria = new BenchmarkCriteria()
{
SchoolOverallPhase = new[] { bicCriteria.OverallPhase },
SchoolPhase = CriteriaSearchConfig.BIC_ALLOWED_PHASES,
MinKs2Progress = bicCriteria.Ks2ProgressScoreMin,
MaxKs2Progress = bicCriteria.Ks2ProgressScoreMax,
MinP8Mea = bicCriteria.Ks4ProgressScoreMin,
MaxP8Mea = bicCriteria.Ks4ProgressScoreMax,
MinRRToIncome = bicCriteria.RRPerIncomeMin,
MinNoPupil = WithinPositiveLimits(bicCriteria.NoPupilsMin - (bicCriteria.NoPupilsMin * percentageMargin / 100)),
MaxNoPupil = bicCriteria.NoPupilsMax + (bicCriteria.NoPupilsMax * percentageMargin / 100),
MinPerPupilExp = bicCriteria.PerPupilExpMin,
MaxPerPupilExp = bicCriteria.PerPupilExpMax + (bicCriteria.PerPupilExpMax * percentageMargin / 100),
MinPerFSM = WithinPercentLimits(bicCriteria.PercentageFSMMin - (bicCriteria.PercentageFSMMin * percentageMargin / 100)),
MaxPerFSM = WithinPercentLimits(bicCriteria.PercentageFSMMax + (bicCriteria.PercentageFSMMax * percentageMargin / 100)),
LondonWeighting = bicCriteria.LondonWeighting
};
if (bicCriteria.OverallPhase == "All-through")
{
bmCriteria.SchoolPhase = new[] { "All-through" };
}
if (bicCriteria.SENEnabled)
{
bmCriteria.MinPerSEN = WithinPercentLimits(bicCriteria.PercentageSENMin - (bicCriteria.PercentageSENMin * percentageMargin / 100));
bmCriteria.MaxPerSEN = WithinPercentLimits(bicCriteria.PercentageSENMax + (bicCriteria.PercentageSENMax * percentageMargin / 100));
}
if (bicCriteria.UREnabled)
{
bmCriteria.UrbanRural = new[] { bicCriteria.UrbanRural };
}
return(bmCriteria);
}
/// <summary>
/// Step 3 - Advanced
/// </summary>
/// <param name="urn"></param>
/// <param name="estType"></param>
/// <param name="comparisonType"></param>
/// <param name="areaType"></param>
/// <param name="lacode"></param>
/// <returns></returns>
public ActionResult AdvancedCharacteristics(string urn, ComparisonType comparisonType, EstablishmentType estType, ComparisonArea?areaType, int?lacode,
string laNameText, BenchmarkCriteria AdvancedCriteria)
{
if (areaType == ComparisonArea.LaName && !string.IsNullOrEmpty(laNameText) && lacode == null)
{
var exactLaMatch = _laSearchService.SearchExactMatch(laNameText);
if (exactLaMatch != null)
{
lacode = Int32.Parse(exactLaMatch.id);
}
}
ViewBag.URN = urn;
ViewBag.ComparisonType = comparisonType;
ViewBag.EstType = estType;
ViewBag.AreaType = areaType;
ViewBag.LaCode = lacode;
var benchmarkSchool = new SchoolViewModel(_contextDataService.GetSchoolByUrn(urn), base.ExtractSchoolComparisonListFromCookie());
var latestYear = _financialDataService.GetLatestDataYearPerSchoolType(benchmarkSchool.FinancialType);
var term = FormatHelpers.FinancialTermFormatAcademies(latestYear);
var document = _financialDataService.GetSchoolDataDocument(urn, term, benchmarkSchool.FinancialType);
benchmarkSchool.HistoricalSchoolFinancialDataModels = new List <SchoolFinancialDataModel> {
new SchoolFinancialDataModel(urn, term, document, benchmarkSchool.FinancialType)
};
if (!IsAreaFieldsValid(areaType, lacode, benchmarkSchool))
{
ViewBag.Authorities = _laService.GetLocalAuthorities();
return(View("ChooseRegion", benchmarkSchool));
}
var schoolCharsVM = new SchoolCharacteristicsViewModel(benchmarkSchool, base.ExtractSchoolComparisonListFromCookie(), AdvancedCriteria);
return(View(schoolCharsVM));
}
public BenchmarkCriteriaVM(BenchmarkCriteria benchmarkCriteria)
{
AdvancedCriteria = benchmarkCriteria;
}
public async Task <ActionResult> Index(
string urn,
SimpleCriteria simpleCriteria,
BenchmarkCriteria benchmarkCriteria,
int basketSize = ComparisonListLimit.DEFAULT,
SchoolFinancialDataModel benchmarkSchoolData = null,
EstablishmentType searchedEstabType = EstablishmentType.All,
ComparisonType comparisonType = ComparisonType.Manual,
ComparisonArea areaType = ComparisonArea.All,
string laCode = null,
RevenueGroupType tab = RevenueGroupType.Expenditure,
CentralFinancingType financing = CentralFinancingType.Include)
{
ChartGroupType chartGroup;
switch (tab)
{
case RevenueGroupType.Expenditure:
chartGroup = ChartGroupType.TotalExpenditure;
break;
case RevenueGroupType.Income:
chartGroup = ChartGroupType.TotalIncome;
break;
case RevenueGroupType.Balance:
chartGroup = ChartGroupType.InYearBalance;
break;
case RevenueGroupType.Workforce:
chartGroup = ChartGroupType.Workforce;
break;
default:
chartGroup = ChartGroupType.All;
break;
}
var defaultUnitType = tab == RevenueGroupType.Workforce ? UnitType.AbsoluteCount : UnitType.AbsoluteMoney;
var benchmarkCharts = await BuildSchoolBenchmarkChartsAsync(tab, chartGroup, defaultUnitType, financing);
var establishmentType = DetectEstablishmentType(base.ExtractSchoolComparisonListFromCookie());
var chartGroups = _benchmarkChartBuilder.Build(tab, establishmentType).DistinctBy(c => c.ChartGroup).ToList();
string selectedArea = "";
switch (areaType)
{
case ComparisonArea.All:
selectedArea = "All England";
break;
case ComparisonArea.LaCode:
case ComparisonArea.LaName:
selectedArea = _laService.GetLaName(laCode);
break;
}
string schoolArea = "";
if (benchmarkSchoolData != null)
{
schoolArea = _laService.GetLaName(benchmarkSchoolData.LaNumber.ToString());
}
var academiesTerm = FormatHelpers.FinancialTermFormatAcademies(_financialDataService.GetLatestDataYearPerSchoolType(SchoolFinancialType.Academies));
var maintainedTerm = FormatHelpers.FinancialTermFormatMaintained(_financialDataService.GetLatestDataYearPerSchoolType(SchoolFinancialType.Maintained));
var vm = new BenchmarkChartListViewModel(benchmarkCharts, base.ExtractSchoolComparisonListFromCookie(), chartGroups, comparisonType, benchmarkCriteria, simpleCriteria, benchmarkSchoolData, establishmentType, searchedEstabType, schoolArea, selectedArea, academiesTerm, maintainedTerm, areaType, laCode, urn, basketSize);
ViewBag.Tab = tab;
ViewBag.ChartGroup = chartGroup;
ViewBag.UnitType = defaultUnitType;
ViewBag.HomeSchoolId = vm.SchoolComparisonList.HomeSchoolUrn;
ViewBag.EstablishmentType = vm.EstablishmentType;
ViewBag.Financing = financing;
ViewBag.ChartFormat = ChartFormat.Charts;
return(View("Index", vm));
}
public async Task <ComparisonResult> GenerateBenchmarkListWithOneClickComparisonAsync(BenchmarkCriteria benchmarkCriteria, EstablishmentType estType,
int basketSize, FinancialDataModel defaultSchoolFinancialDataModel)
{
//STEP 1: Straight search with predefined criteria
var benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType);
if (benchmarkSchools.Count > basketSize) //Original query returns more than required. Clip from top by people count proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.NoPupils.GetValueOrDefault() - defaultSchoolFinancialDataModel.PupilCount.GetValueOrDefault())).Take(basketSize).ToList();
benchmarkCriteria.MinNoPupil = benchmarkSchools.Min(s => s.NoPupils);
benchmarkCriteria.MaxNoPupil = benchmarkSchools.Max(s => s.NoPupils); //Update the used criteria to reflect the max and min pupil count of the found schools
}
//STEP 2: Original query returns less than required. Expand criteria values gradually and try this max 10 times
var tryCount = 0;
while (benchmarkSchools.Count < basketSize)
{
if (++tryCount > CriteriaSearchConfig.MAX_TRY_LIMIT) //Max query try reached. Return whatever is found.
{
break;
}
benchmarkCriteria = _benchmarkCriteriaBuilderService.BuildFromOneClickComparisonCriteria(defaultSchoolFinancialDataModel, tryCount);
benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType);
if (benchmarkSchools.Count > basketSize) //Number jumping to more than ideal. Cut from top by pupil count proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.NoPupils.GetValueOrDefault() - defaultSchoolFinancialDataModel.PupilCount.GetValueOrDefault())).Take(basketSize).ToList();
benchmarkCriteria.MinNoPupil = benchmarkSchools.Min(s => s.NoPupils);
benchmarkCriteria.MaxNoPupil = benchmarkSchools.Max(s => s.NoPupils); //Update the criteria to reflect the max and min pupil count of the found schools
break;
}
}
//STEP 3: Query return is still less than required. Flex the Urban/Rural criteria gradually.
tryCount = 1;
while (benchmarkSchools.Count < basketSize)
{
var urbanRuralDefault = defaultSchoolFinancialDataModel.UrbanRural;
var urbanRuralKey = Dictionaries.UrbanRuralDictionary.First(d => d.Value == urbanRuralDefault).Key;
var urbanRuralQuery = Dictionaries.UrbanRuralDictionary.Where(d =>
d.Key >= urbanRuralKey - tryCount && d.Key <= urbanRuralKey + tryCount).Select(d => d.Value).ToArray();
benchmarkCriteria.UrbanRural = urbanRuralQuery;
benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType);
if (benchmarkSchools.Count > basketSize) //Number jumping to more than ideal. Cut from top by pupil count proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.NoPupils.GetValueOrDefault() - defaultSchoolFinancialDataModel.PupilCount.GetValueOrDefault())).Take(basketSize).ToList();
benchmarkCriteria.MinNoPupil = benchmarkSchools.Min(s => s.NoPupils);
benchmarkCriteria.MaxNoPupil = benchmarkSchools.Max(s => s.NoPupils); //Update the criteria to reflect the max and min pupil count of the found schools
break;
}
if (urbanRuralQuery.Length == Dictionaries.UrbanRuralDictionary.Count)
{
break;
}
tryCount++;
}
return(new ComparisonResult()
{
BenchmarkSchools = benchmarkSchools,
BenchmarkCriteria = benchmarkCriteria
});
}
public BenchmarkChartListViewModel(List <ChartViewModel> modelList, ComparisonListModel comparisonList, List <ChartViewModel> chartGroups, ComparisonType comparisonType, BenchmarkCriteria advancedCriteria, SimpleCriteria simpleCriteria, SchoolFinancialDataModel benchmarkSchoolData, EstablishmentType estabType, EstablishmentType searchedEstabType, string schoolArea, string selectedArea, string latestTermAcademies, string latestTermMaintained, ComparisonArea areaType, string laCode, string urn, int basketSize, TrustComparisonViewModel trustComparisonList = null)
{
base.SchoolComparisonList = comparisonList;
base.ModelList = modelList;
this.ChartGroups = chartGroups;
this.AdvancedCriteria = advancedCriteria;
this.SimpleCriteria = simpleCriteria;
this.ComparisonType = comparisonType;
this.BenchmarkSchoolData = benchmarkSchoolData;
this.EstablishmentType = estabType;
this.SearchedEstablishmentType = searchedEstabType;
this.SchoolArea = schoolArea;
this.SelectedArea = selectedArea;
this.TrustComparisonList = trustComparisonList;
this.LatestTermAcademies = latestTermAcademies;
this.LatestTermMaintained = latestTermMaintained;
this.AreaType = areaType;
this.LaCode = laCode;
this.URN = urn;
this.BasketSize = basketSize;
}
public SchoolCharacteristicsViewModel(SchoolViewModel school, ComparisonListModel comparisonList, BenchmarkCriteria benchmarkCriteria)
{
base.ComparisonList = comparisonList;
this.BenchmarkSchool = school;
this.SchoolCharacteristics = BuildSchoolCharacteristics(school);
this.BenchmarkCriteria = benchmarkCriteria;
}
public async Task <int> SearchSchoolsCountByCriteriaAsync(BenchmarkCriteria criteria, EstablishmentType estType)
{
return(await _financialDataRepository.SearchSchoolsCountByCriteriaAsync(criteria, estType));
}
public async Task <ComparisonResult> GenerateBenchmarkListWithSpecialComparisonAsync(BenchmarkCriteria benchmarkCriteria, SpecialCriteria specialCriteria, FinancialDataModel defaultSchoolFinancialDataModel)
{
//STEP 1: Straight search with prefilled criteria
var benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, EstablishmentType.All, true);
if (benchmarkSchools.Count > ComparisonListLimit.SPECIALS) //Original query returns more than required. Clip to closest schools to the top SEN criteria.
{
var highestSENName = specialCriteria.TopSenCriteria[0].DataName;
var highestSENValue = specialCriteria.TopSenCriteria[0].Original.GetValueOrDefault();
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.getValueByCriteriaName(highestSENName).GetValueOrDefault() - highestSENValue)).Take(ComparisonListLimit.SPECIALS).ToList();
//Updating the criteria to reflect the final min and max used values used on top SEN
var minValue = benchmarkSchools.Min(s => s.getValueByCriteriaName(highestSENName));
var maxValue = benchmarkSchools.Max(s => s.getValueByCriteriaName(highestSENName));
benchmarkCriteria.FindAndSetMaxMinSenInCriteria(highestSENName, minValue, maxValue);
}
//STEP 2: Original query returns less than required. Expand criteria values gradually and try this max 10 times
var tryCount = 0;
while (benchmarkSchools.Count < ComparisonListLimit.SPECIALS)
{
if (++tryCount > CriteriaSearchConfig.MAX_TRY_LIMIT) //Max query try reached. Return whatever is found.
{
break;
}
benchmarkCriteria = _benchmarkCriteriaBuilderService.BuildFromSpecialComparisonCriteria(defaultSchoolFinancialDataModel, specialCriteria, tryCount);
benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, EstablishmentType.All, true);
if (benchmarkSchools.Count > ComparisonListLimit.SPECIALS) //Number jumping to more than ideal. Clip to closest schools to the top SEN criteria.
{
var highestSENName = specialCriteria.TopSenCriteria[0].DataName;
var highestSENValue = specialCriteria.TopSenCriteria[0].Original.GetValueOrDefault();
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.getValueByCriteriaName(highestSENName).GetValueOrDefault() - highestSENValue)).Take(ComparisonListLimit.SPECIALS).ToList();
//Updating the criteria to reflect the final min and max used values used on top SEN
var minValue = benchmarkSchools.Min(s => s.getValueByCriteriaName(highestSENName));
var maxValue = benchmarkSchools.Max(s => s.getValueByCriteriaName(highestSENName));
benchmarkCriteria.FindAndSetMaxMinSenInCriteria(highestSENName, minValue, maxValue); break;
}
}
return(new ComparisonResult()
{
BenchmarkSchools = benchmarkSchools,
BenchmarkCriteria = benchmarkCriteria
});
}
public TrustCharacteristicsViewModel(TrustViewModel trust, BenchmarkCriteria criteria)
{
this.BenchmarkTrust = trust;
this.TrustCharacteristics = BuildTrustCharacteristics(trust);
this.BenchmarkCriteria = criteria;
}
public async Task <ComparisonResult> GenerateBenchmarkListWithAdvancedComparisonAsync(BenchmarkCriteria criteria, EstablishmentType estType,
bool excludePartial, int basketSize = ComparisonListLimit.LIMIT)
{
var benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(criteria, estType, excludePartial);
return(new ComparisonResult()
{
BenchmarkSchools = benchmarkSchools,
BenchmarkCriteria = criteria
});
}
/// <summary>
/// Step 3 - Advanced
/// </summary>
/// <param name="urn"></param>
/// <param name="estType"></param>
/// <param name="comparisonType"></param>
/// <param name="areaType"></param>
/// <param name="laCodeName"></param>
/// <returns></returns>
public async Task <ActionResult> AdvancedCharacteristics(
long?urn,
ComparisonType comparisonType,
EstablishmentType estType,
ComparisonArea?areaType,
string laCodeName,
BenchmarkCriteria AdvancedCriteria,
bool excludePartial = false,
int?laCode = null)
{
if (!areaType.HasValue)
{
SchoolViewModel vm = null;
if (urn.HasValue)
{
vm = new SchoolViewModel(await _contextDataService.GetSchoolDataObjectByUrnAsync(urn.Value), _benchmarkBasketService.GetSchoolBenchmarkList());
}
else
{
vm = new SchoolViewModelWithNoDefaultSchool();
}
vm.ErrorMessage = ErrorMessages.SelectAreaType;
ViewBag.Authorities = _laService.GetLocalAuthorities();
ViewBag.URN = urn;
ViewBag.ComparisonType = comparisonType;
ViewBag.EstType = estType;
ViewBag.AreaType = areaType;
ViewBag.ExcludePartial = excludePartial.ToString();
return(View("ChooseRegion", vm));
}
if (areaType == ComparisonArea.LaCodeName && laCode == null)
{
string errorMessage = _valService.ValidateLaCodeNameParameter(laCodeName);
if (string.IsNullOrEmpty(errorMessage))
{
if (IsNumeric(laCodeName))
{
laCode = int.Parse(laCodeName);
if (!_laSearchService.LaCodesContain(laCode.Value))
{
errorMessage = SearchErrorMessages.NO_LA_RESULTS;
}
}
else
{
var exactLaMatch = _laSearchService.SearchExactMatch(laCodeName);
if (exactLaMatch == null)
{
errorMessage = SearchErrorMessages.NO_LA_RESULTS;
}
else
{
laCode = int.Parse(exactLaMatch.Id);
}
}
}
if (errorMessage != null)
{
SchoolViewModel vm = null;
if (urn.HasValue)
{
vm = new SchoolViewModel(await _contextDataService.GetSchoolDataObjectByUrnAsync(urn.Value), _benchmarkBasketService.GetSchoolBenchmarkList());
}
else
{
vm = new SchoolViewModelWithNoDefaultSchool();
}
vm.ErrorMessage = errorMessage;
ViewBag.Authorities = _laService.GetLocalAuthorities();
ViewBag.URN = urn;
ViewBag.ComparisonType = comparisonType;
ViewBag.EstType = estType;
ViewBag.AreaType = areaType;
ViewBag.ExcludePartial = excludePartial.ToString();
return(View("ChooseRegion", vm));
}
}
SchoolViewModel benchmarkSchoolVM;
if (urn.HasValue)
{
benchmarkSchoolVM = new SchoolViewModel(await _contextDataService.GetSchoolDataObjectByUrnAsync(urn.Value), _benchmarkBasketService.GetSchoolBenchmarkList());
var schoolsLatestFinancialDataModel = await _financialDataService.GetSchoolsLatestFinancialDataModelAsync(benchmarkSchoolVM.Id, benchmarkSchoolVM.EstablishmentType);
benchmarkSchoolVM.HistoricalFinancialDataModels = new List <FinancialDataModel> {
schoolsLatestFinancialDataModel
};
}
else
{
benchmarkSchoolVM = new SchoolViewModelWithNoDefaultSchool();
}
var schoolCharsVM = new SchoolCharacteristicsViewModel(benchmarkSchoolVM, _benchmarkBasketService.GetSchoolBenchmarkList(), AdvancedCriteria);
ViewBag.URN = urn;
ViewBag.ComparisonType = comparisonType;
ViewBag.EstType = estType;
ViewBag.EstTypeDescription = estType.GetDescription();
ViewBag.ExcludePartial = excludePartial.ToString();
ViewBag.AreaType = areaType;
ViewBag.AreaTypeDescription = areaType == ComparisonArea.All ? "All of England" : _laService.GetLaName(laCode.ToString());
ViewBag.LaCode = laCode;
return(View(schoolCharsVM));
}
public async Task <ComparisonResult> GenerateBenchmarkListWithBestInClassComparisonAsync(EstablishmentType estType,
BenchmarkCriteria benchmarkCriteria, BestInClassCriteria bicCriteria,
FinancialDataModel defaultSchoolFinancialDataModel)
{
//STEP 1: Straight search with prefilled criteria
var benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType, true);
if (benchmarkSchools.Count > CriteriaSearchConfig.BIC_TARGET_POOL_COUNT) //Original query returns more than required. Clip from top by per people expenditure proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.PerPupilTotalExpenditure.GetValueOrDefault() - defaultSchoolFinancialDataModel.PerPupilTotalExpenditure.GetValueOrDefault()))
.Take(CriteriaSearchConfig.BIC_TARGET_POOL_COUNT).ToList();
}
//STEP 2: Original query returns less than required. Expand criteria values gradually and try this max 10 times
var tryCount = 0;
while (benchmarkSchools.Count < CriteriaSearchConfig.BIC_TARGET_POOL_COUNT)
{
if (++tryCount > CriteriaSearchConfig.MAX_BIC_TRY_LIMIT) //Max query try reached. Return whatever is found.
{
break;
}
benchmarkCriteria = _benchmarkCriteriaBuilderService.BuildFromBicComparisonCriteria(defaultSchoolFinancialDataModel, bicCriteria, tryCount);
benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType, true);
if (benchmarkSchools.Count > CriteriaSearchConfig.BIC_TARGET_POOL_COUNT) //Number jumping to more than ideal. Clip from top by per people expenditure proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.PerPupilTotalExpenditure.GetValueOrDefault() - defaultSchoolFinancialDataModel.PerPupilTotalExpenditure.GetValueOrDefault()))
.Take(CriteriaSearchConfig.BIC_TARGET_POOL_COUNT).ToList();
break;
}
}
//STEP 3: Further reduce pool of 50 (or less) to target 15 by highest progress measure
if (benchmarkSchools.Count > ComparisonListLimit.BIC)
{
benchmarkSchools = benchmarkSchools
.OrderByDescending(b => b.OverallPhase == "Secondary" || b.OverallPhase == "All-through" ? b.Progress8Measure : b.Ks2Progress)
.Take(ComparisonListLimit.BIC)
.ToList();
}
return(new ComparisonResult()
{
BenchmarkSchools = benchmarkSchools,
BenchmarkCriteria = benchmarkCriteria
});
}
public async Task <ComparisonResult> GenerateBenchmarkListWithAdvancedComparisonAsync(BenchmarkCriteria criteria,
EstablishmentType estType, int basketSize = ComparisonListLimit.LIMIT)
{
return(await GenerateBenchmarkListWithAdvancedComparisonAsync(criteria, estType, false, basketSize));
}
