public CustomResult <Organisation> LoadInfoFromEmployerIdentifier(string employerIdentifier)
{
int organisationId = _obfuscator.DeObfuscate(employerIdentifier);
if (organisationId == 0)
{
return(new CustomResult <Organisation>(InternalMessages.HttpBadRequestCausedByInvalidEmployerIdentifier(employerIdentifier)));
}
Organisation organisation = GetOrganisationById(organisationId);
if (organisation == null)
{
return(new CustomResult <Organisation>(InternalMessages.HttpNotFoundCausedByOrganisationIdNotInDatabase(employerIdentifier)));
}
return(new CustomResult <Organisation>(organisation));
}
public virtual async Task <IEnumerable <CompareReportModel> > GetCompareDataAsync(
IEnumerable <string> encBasketOrgIds,
int year,
string sortColumn,
bool sortAscending)
{
// decrypt all ids for query
var basketOrgIds = encBasketOrgIds.Select(x => _obfuscator.DeObfuscate(x).ToInt64()).ToList();
// query against scopes and filter by basket ids
var dbScopesQuery = _DataRepository.GetAll <OrganisationScope>()
.Where(os => os.Status == ScopeRowStatuses.Active && os.SnapshotDate.Year == year)
.Where(r => basketOrgIds.Contains(r.OrganisationId)).ToList();
// query submitted returns for current year
var dbReturnsQuery = _DataRepository.GetAll <Return>()
.Where(r => r.Status == ReturnStatuses.Submitted && r.AccountingDate.Year == year).ToList();
// finally, generate the left join sql statement between scopes and returns
var dbResults = dbScopesQuery.AsQueryable().GroupJoin(
// join
dbReturnsQuery.AsQueryable(),
// on
// inner
Scope => Scope.OrganisationId,
// outer
Return => Return.OrganisationId,
// into
(Scope, Return) => new { Scope, Return = Return.LastOrDefault() })
// execute on sql server and return results into memory
.ToList();
// build the compare reports list
var compareReports = dbResults.Select(
r =>
{
return(new CompareReportModel
{
EncOrganisationId = _obfuscator.Obfuscate(r.Scope.OrganisationId.ToString()),
OrganisationName = r.Scope.Organisation.OrganisationName,
ScopeStatus = r.Scope.ScopeStatus,
HasReported = r.Return != null,
OrganisationSize = r.Return?.OrganisationSize,
DiffMeanBonusPercent = r.Return?.DiffMeanBonusPercent,
DiffMeanHourlyPayPercent = r.Return?.DiffMeanHourlyPayPercent,
DiffMedianBonusPercent = r.Return?.DiffMedianBonusPercent,
DiffMedianHourlyPercent = r.Return?.DiffMedianHourlyPercent,
FemaleLowerPayBand = r.Return?.FemaleLowerPayBand,
FemaleMedianBonusPayPercent = r.Return?.FemaleMedianBonusPayPercent,
FemaleMiddlePayBand = r.Return?.FemaleMiddlePayBand,
FemaleUpperPayBand = r.Return?.FemaleUpperPayBand,
FemaleUpperQuartilePayBand = r.Return?.FemaleUpperQuartilePayBand,
MaleMedianBonusPayPercent = r.Return?.MaleMedianBonusPayPercent,
HasBonusesPaid = r.Return?.HasBonusesPaid()
});
})
.ToList();
// Sort the results if required
if (!string.IsNullOrWhiteSpace(sortColumn))
{
return(SortCompareReports(compareReports, sortColumn, sortAscending));
}
// Return the results
return(compareReports);
}