public async Task <SchoolTrustFinancialDataObject> GetSchoolFinanceDataObjectAsync(long urn, string term, EstablishmentType estabType, CentralFinancingType cFinance)
{
var dataGroup = estabType.ToDataGroup(cFinance);
string collectionName = await _dataCollectionManager.GetCollectionIdByTermByDataGroupAsync(term, dataGroup);
var container = _client.GetContainer(_databaseId, collectionName);
var queryString = $"SELECT * FROM c WHERE c.{SchoolTrustFinanceDataFieldNames.URN}=@URN";
var queryDefinition = new QueryDefinition(queryString)
.WithParameter($"@URN", urn);
try
{
var feedIterator = container.GetItemQueryIterator <SchoolTrustFinancialDataObject>(queryDefinition, null);
return((await feedIterator.ReadNextAsync()).FirstOrDefault());
}
catch (Exception ex)
{
if (term.Contains(_dataCollectionManager.GetLatestFinancialDataYearPerEstabTypeAsync(estabType).ToString()))
{
var errorMessage = $"{collectionName} could not be loaded! : {ex.Message} : {queryDefinition.QueryText}";
base.LogException(ex, errorMessage);
}
return(null);
}
}
public static string ToDataGroup(this EstablishmentType estabType, CentralFinancingType cFinance)
{
switch (estabType)
{
case EstablishmentType.Academies:
return((cFinance == CentralFinancingType.Include) ? DataGroups.MATAllocs : DataGroups.Academies);
default:
return(estabType.ToDataGroup());
}
}
public async Task <SchoolTrustFinancialDataObject> GetSchoolFinancialDataObjectAsync(long urn, string term, EstablishmentType estabType, CentralFinancingType cFinance)
{
var dataGroup = estabType.ToDataGroup(cFinance);
var collectionName = await _dataCollectionManager.GetCollectionIdByTermByDataGroupAsync(term, dataGroup);
var container = _client.GetContainer(_databaseId, collectionName);
if (collectionName == null)
{
return(null);
}
var queryString = $"SELECT * FROM c WHERE c.{SchoolTrustFinanceDataFieldNames.URN}=@URN";
var queryDefinition = new QueryDefinition(queryString)
.WithParameter($"@URN", urn);
try
{
var feedIterator = container.GetItemQueryIterator <SchoolTrustFinancialDataObject>(queryDefinition, null);
var result = (await feedIterator.ReadNextAsync()).FirstOrDefault();
if (dataGroup == DataGroups.MATAllocs && result == null)//if nothing found in MAT-Allocs collection try to source it from Academies data
{
return(await GetSchoolFinancialDataObjectAsync(urn, term, estabType, CentralFinancingType.Exclude));
}
if (result != null && result.DidNotSubmit)//School did not submit finance, return & display none in the charts
{
return(null);
}
return(result);
}
catch (Exception ex)
{
if (term.Contains(_dataCollectionManager.GetLatestFinancialDataYearPerEstabTypeAsync(estabType).ToString()))
{
var errorMessage = $"{collectionName} could not be loaded! : {ex.Message} : {queryDefinition.QueryText}";
base.LogException(ex, errorMessage);
}
return(null);
}
}
//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 static string ToDataGroup(this EstablishmentType estabType, MatFinancingType mFinance)
{
switch (estabType)
{
case EstablishmentType.MAT:
switch (mFinance)
{
case MatFinancingType.TrustOnly:
return(DataGroups.MATCentral);
case MatFinancingType.TrustAndAcademies:
return(DataGroups.MATOverview);
case MatFinancingType.AcademiesOnly:
return(DataGroups.MATTotals);
default:
return(DataGroups.MATCentral);
}
default:
return(estabType.ToDataGroup());
}
}
public async Task <int> GetLatestFinancialDataYearPerEstabTypeAsync(EstablishmentType type)
{
var latestCollectionId = await GetLatestActiveCollectionIdByDataGroupAsync(type.ToDataGroup());
return(int.Parse(latestCollectionId.Split('-').Last()));
}