public FileContentResult DownloadUnverifiedUserOrganisationRegistrations()
{
List <UserOrganisation> userOrganisations = dataRepository.GetEntities <UserOrganisation>()
.Where(uo => uo.PINConfirmedDate == null)
.Include(uo => uo.User)
.ToList();
var records = userOrganisations.Select(
uo => new
{
uo.OrganisationId,
uo.UserId,
uo.Organisation.OrganisationName,
uo.Organisation.CompanyNumber,
uo.Organisation.SectorType,
uo.Method,
uo.User.Firstname,
uo.User.Lastname,
uo.User.JobTitle,
uo.User.EmailAddress,
uo.PINSentDate,
uo.PINConfirmedDate,
uo.Created
})
.ToList();
string fileDownloadName = $"Gpg-UnverifiedUserOrganisationRegistrations-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadAllOrganisations()
{
List <Organisation> allOrganisations = dataRepository.GetAll <Organisation>()
.Include(org => org.OrganisationAddresses)
.Include(org => org.OrganisationSicCodes)
.ToList();
var records = allOrganisations.Select(
org => new
{
org.OrganisationId,
org.OrganisationName,
org.CompanyNumber,
Sector = org.SectorType,
Status = org.Status,
Address = org.GetLatestAddress()?.GetAddressString(),
SicCodes = org.GetSicCodeIdsString(),
Created = org.Created,
})
.ToList();
string fileDownloadName = $"Gpg-AllOrganisations-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadAllSubmissionsForYear(int year)
{
List <Organisation> organisationsWithReturnsForYear = dataRepository.GetAll <Organisation>()
.Where(org => org.Status == OrganisationStatuses.Active)
.Where(org =>
org.Returns.Any(ret => ret.Status == ReturnStatuses.Submitted &&
ret.AccountingDate.Year == year))
.Include(org => org.OrganisationScopes)
.Include(org => org.Returns)
.ToList();
List <object> records = organisationsWithReturnsForYear.Select(
org =>
{
Return returnForYear = org.GetReturn(year);
return(ConvertReturnToDownloadFormat(returnForYear));
})
.ToList();
string fileDownloadName = $"Gpg-AllSubmissionsForYear-{year}--{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadUserOrganisationRegistrations()
{
List <UserOrganisation> userOrganisations = dataRepository.GetAll <UserOrganisation>()
.Include(uo => uo.User)
.ToList();
var records = userOrganisations.Select(
uo => new
{
uo.OrganisationId,
uo.UserId,
uo.Organisation.OrganisationName,
uo.Organisation.CompanyNumber,
uo.Organisation.SectorType,
uo.Method,
uo.User.Firstname,
uo.User.Lastname,
uo.User.JobTitle,
uo.User.EmailAddress,
uo.PINSentDate,
uo.PINConfirmedDate,
uo.Created
})
.ToList();
string fileDownloadName = $"Gpg-UserOrganisationRegistrations-{VirtualDateTime.Now:yyyy-MM-dd HH:mm}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadAllUsers()
{
List <User> users = dataRepository.GetAll <User>().ToList();
var records = users.Select(
u => new
{
u.UserId,
u.Firstname,
u.Lastname,
u.JobTitle,
u.EmailAddress,
u.EmailVerifySendDate,
u.EmailVerifiedDate,
u.Status,
u.StatusDate,
u.StatusDetails,
u.Created
})
.ToList();
string fileDownloadName = $"Gpg-AllUsers-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public static FileContentResult GenerateEhrcAllOrganisationsForYearFile(IDataRepository dataRepository, int year)
{
// IMPORTANT: This variable isn't used, but running this query makes the next query much faster
var allOrgsWithAddresses = dataRepository.GetAll <Organisation>()
.Include(o => o.OrganisationAddresses)
.ToList();
// IMPORTANT: This variable isn't used, but running this query makes the next query much faster
var allOrgsWithScopes = dataRepository.GetAll <Organisation>()
.Include(o => o.OrganisationScopes)
.ToList();
// IMPORTANT: This variable isn't used, but running this query makes the next query much faster
var allOrgsWithReturns = dataRepository.GetAll <Organisation>()
.Include(o => o.Returns)
.ToList();
// IMPORTANT: This variable isn't used, but running this query makes the next query much faster
var allOrgsWithUserOrgs = dataRepository.GetAll <Organisation>()
.Include(o => o.UserOrganisations)
.ToList();
List <Organisation> organisations = dataRepository
.GetAll <Organisation>()
//.Include(org => org.OrganisationAddresses) // Moved into separate pre-load query
.Include(org => org.OrganisationSicCodes)
//.Include(org => org.OrganisationScopes) // Moved into separate pre-load query
//.Include(org => org.Returns) // Moved into separate pre-load query
//.Include(org => org.UserOrganisations) // Moved into separate pre-load query
.ToList();
var records = organisations.Select(
o => new
{
OrganisationId = o.OrganisationId,
EmployerReference = o.EmployerReference,
OrganisationName = o.OrganisationName,
CompanyNo = o.CompanyNumber,
Sector = o.SectorType,
Status = o.Status,
StatusDate = o.StatusDate,
StatusDetails = o.StatusDetails,
Address = o.GetLatestAddress()?.GetAddressString(),
SicCodes = o.GetSicCodeIdsString(),
LatestRegistrationDate = o.UserOrganisations.OrderByDescending(uo => uo.Created).FirstOrDefault()?.Created,
LatestRegistrationMethod = o.UserOrganisations.OrderByDescending(uo => uo.Created).FirstOrDefault()?.Method.ToString(),
LatestReturn = o.GetLatestReturn()?.Modified,
ScopeStatus = o.GetLatestScopeForSnapshotYear(year)?.ScopeStatus,
ScopeDate = o.GetLatestScopeForSnapshotYear(year)?.ScopeStatusDate,
Created = o.Created,
})
.ToList();
string fileDownloadName = $"GPG-Organisations_{ReportingYearsHelper.FormatYearAsReportingPeriod(year)}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadFeedback(bool nonSpamOnly = false)
{
var feedback = dataRepository.GetAll <Feedback>();
if (nonSpamOnly)
{
feedback = feedback.Where(f => f.FeedbackStatus == FeedbackStatus.NotSpam);
}
return(DownloadHelper.CreateCsvDownload(feedback, "Feedback.csv"));
}
public static FileContentResult GenerateOrganisationsWithNoSubmittedReturnsForYear(IDataRepository dataRepository, int year)
{
IEnumerable <Organisation> organisationsWithNoSubmittedReturns = GetOrganisationsWithNoSubmittedReturns(dataRepository, year);
IEnumerable <dynamic> records = BuildOrganisationsWithNoSubmittedReturnsRecords(organisationsWithNoSubmittedReturns);
string fileDownloadName = $"Gpg-NoSubmissionsFor{year}-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadAllSicCodes()
{
List <SicCode> allSicCodes = dataRepository.GetAll <SicCode>().ToList();
var records = allSicCodes.Select(GetSicCodeDetails)
.ToList();
string fileDownloadName = $"Gpg-SicCodes-{VirtualDateTime.Now:yyyy-MM-dd HH:mm}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public IActionResult DownloadReturnDetailsCsv(long id)
{
Organisation organisation = dataRepository.Get <Organisation>(id);
var returns = organisation.Returns.OrderByDescending(r => r.AccountingDate).ThenByDescending(r => r.StatusDate);
var records = returns.Select(
ret =>
new
{
OrganisationId = ret.Organisation.OrganisationId,
OrganisationName = ret.Organisation.OrganisationName,
ReturnId = ret.ReturnId,
SnapshotDate = ret.AccountingDate,
DeadlineDate = ReportingYearsHelper.GetDeadlineForAccountingDate(ret.AccountingDate),
ModifiedDate = ret.Modified,
Status = ret.Status,
Modifications = ret.Modifications,
Late = ret.IsLateSubmission,
LateReason = ret.LateReason,
Employees = ret.OrganisationSize.GetAttribute <DisplayAttribute>().Name,
HourlyPayGapMean = ret.DiffMeanHourlyPayPercent,
HourlyPayGapMedian = ret.DiffMedianHourlyPercent,
BonusPayPercentMale = ret.MaleMedianBonusPayPercent,
BonusPayPercentFemale = ret.FemaleMedianBonusPayPercent,
BonusPayGapMean = ret.DiffMeanBonusPercent,
BonusPayGapMedian = ret.DiffMedianBonusPercent,
UpperQuarterPercentMale = ret.MaleUpperQuartilePayBand,
UpperQuarterPercentFemale = ret.FemaleUpperQuartilePayBand,
UpperMiddleQuarterPercentMale = ret.MaleUpperPayBand,
UpperMiddleQuarterPercentFemale = ret.FemaleUpperPayBand,
LowerMiddleQuarterPercentMale = ret.MaleMiddlePayBand,
LowerMiddleQuarterPercentFemale = ret.FemaleMiddlePayBand,
LowerQuarterPercentMale = ret.MaleLowerPayBand,
LowerQuarterPercentFemale = ret.FemaleLowerPayBand,
LinkToCompanyWebsite = ret.CompanyLinkToGPGInfo,
SeniorResponsibleOfficer = ret.ResponsiblePerson
});
string sanitisedOrganisationName = SanitiseOrganisationNameForFilename(organisation.OrganisationName);
string fileDownloadName = $"ReturnsForOrganisation-{sanitisedOrganisationName}--{VirtualDateTime.Now:yyyy-MM-dd HH:mm}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public IActionResult DownloadData(int year = 0)
{
if (year == 0)
{
year = SectorTypes.Private.GetAccountingStartDate().Year;
}
string filePath = Path.Combine(Global.DownloadsLocation, $"GPGData_{year}-{year + 1}.csv");
string fileContents = fileRepository.Read(filePath);
string userFacingDownloadFileName = $"UK Gender Pay Gap Data - {year} to {year + 1}.csv";
//Track the download
WebTracker.TrackPageView(this, userFacingDownloadFileName);
return(DownloadHelper.CreateCsvDownload(fileContents, userFacingDownloadFileName));
}
public FileContentResult DownloadLateSubmissions(int year)
{
List <Organisation> organisationsWithLateReturns = dataRepository.GetAll <Organisation>()
.Where(org => org.Status == OrganisationStatuses.Active)
.Where(
org => !org.OrganisationScopes.Any(s => s.SnapshotDate.Year == year) ||
org.OrganisationScopes.Any(
s => s.SnapshotDate.Year == year &&
(s.ScopeStatus == ScopeStatuses.InScope ||
s.ScopeStatus == ScopeStatuses.PresumedInScope)))
// There might not be a Return for any given year
// So we search for organisations that do NOT have a non-late submission
.Where(
org => !org.Returns
.Any(
r => r.AccountingDate.Year == year &&
r.Status == ReturnStatuses.Submitted &&
!r.IsLateSubmission))
.Include(org => org.Returns)
.ToList();
var records = organisationsWithLateReturns.Select(
org => new
{
org.OrganisationId,
org.OrganisationName,
org.SectorType,
Submitted = org.GetReturn(year) != null,
ReportingDeadline = ReportingYearsHelper.GetDeadlineForAccountingDate(org.SectorType.GetAccountingStartDate(year)).ToString("d MMMM yyyy"),
SubmittedDate = org.GetReturn(year)?.Created,
ModifiedDate = org.GetReturn(year)?.Modified,
org.GetReturn(year)?.LateReason
})
.ToList();
string fileDownloadName = $"Gpg-LateSubmissions-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadFullSubmissionHistoryForYear(int year)
{
List <Organisation> organisationsWithReturnsForYear = dataRepository.GetAll <Organisation>()
.Where(org => org.Status == OrganisationStatuses.Active)
.Include(org => org.OrganisationScopes)
.Include(org => org.Returns)
.ToList();
List <Return> returnsForYear = organisationsWithReturnsForYear
.SelectMany(organisation => organisation.Returns)
.Where(ret => ret.AccountingDate.Year == year)
.ToList();
List <object> records = returnsForYear
.Select(ConvertReturnToDownloadFormat)
.ToList();
string fileDownloadName = $"Gpg-FullSubmissionHistoryForYear-{year}--{VirtualDateTime.Now:yyyy-MM-dd HH:mm}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadOrganisationAddresses()
{
List <Organisation> organisationAddresses = dataRepository.GetAll <Organisation>()
.Where(org => org.Status == OrganisationStatuses.Active)
.Include(org => org.OrganisationAddresses)
// This .ToList() materialises the collection (i.e. runs the SQL query)
.ToList()
// We only want organisations with valid addresses
// The following filter only works in code (cannot be converted to SQL) so must be done after the first .ToList()
.Where(org => org.GetLatestAddress() != null)
.ToList();
var records = organisationAddresses.Select(
org =>
{
OrganisationAddress address = org.GetLatestAddress();
string postCode = address.GetPostCodeInAllCaps();
return(new
{
org.OrganisationId,
org.OrganisationName,
address.PoBox,
address.Address1,
address.Address2,
address.Address3,
address.TownCity,
address.County,
address.Country,
postCode,
});
})
.ToList();
string fileDownloadName = $"Gpg-OrganisationAddresses-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadUserConsentSendUpdates()
{
List <User> users = dataRepository.GetAll <User>()
.Where(user => user.Status == UserStatuses.Active)
.Where(user => user.SendUpdates)
.ToList();
var records = users.Select(
u => new
{
u.UserId,
u.Firstname,
u.Lastname,
u.JobTitle,
u.EmailAddress,
})
.ToList();
string fileDownloadName = $"Gpg-UserConsent-SendUpdates-{VirtualDateTime.Now:yyyy-MM-dd HH:mm}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadUserConsentAllowContactForFeedback()
{
List <User> users = dataRepository.GetAll <User>()
.Where(user => user.Status == UserStatuses.Active)
.Where(user => user.AllowContact)
.ToList();
var records = users.Select(
u => new
{
u.UserId,
u.Firstname,
u.Lastname,
u.JobTitle,
u.EmailAddress,
})
.ToList();
string fileDownloadName = $"Gpg-UserConsent-AllowContactForFeedback-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadOrganisationScopesForYear(int year)
{
List <Organisation> organisationsWithScopesForYear = dataRepository.GetAll <Organisation>()
.Where(org => org.Status == OrganisationStatuses.Active)
.Where(org => org.OrganisationScopes.Any(scope => scope.SnapshotDate.Year == year && scope.Status == ScopeRowStatuses.Active))
.Include(org => org.OrganisationScopes)
.ToList();
var records = organisationsWithScopesForYear.Select(
org => new
{
org.OrganisationId,
org.OrganisationName,
org.GetScopeForYear(year).ScopeStatus,
DateScopeLastChanged = org.GetScopeForYear(year).ScopeStatusDate,
})
.ToList();
string fileDownloadName = $"Gpg-OrganisationScopesForYear-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
public FileContentResult DownloadOrphanOrganisations()
{
DateTime pinExpiresDate = VirtualDateTime.Now.AddDays(0 - Global.PinInPostExpiryDays);
List <Organisation> orphanOrganisations = dataRepository.GetAll <Organisation>()
.Where(org => org.Status == OrganisationStatuses.Active)
.Where(org =>
org.OrganisationScopes.OrderByDescending(s => s.SnapshotDate).FirstOrDefault(s => s.Status == ScopeRowStatuses.Active) == null ||
org.OrganisationScopes.OrderByDescending(s => s.SnapshotDate).FirstOrDefault(s => s.Status == ScopeRowStatuses.Active).ScopeStatus == ScopeStatuses.InScope ||
org.OrganisationScopes.OrderByDescending(s => s.SnapshotDate).FirstOrDefault(s => s.Status == ScopeRowStatuses.Active).ScopeStatus == ScopeStatuses.PresumedInScope)
// We need the AsEnumerable here because EF gets upset about method calls - so we get the list at this point and then can filter it using a method call
.AsEnumerable()
.Where(org => org.UserOrganisations == null ||
!org.UserOrganisations.Any(uo => uo.HasBeenActivated() || // Registration complete
uo.Method == RegistrationMethods.Manual || // Manual registration
(uo.Method == RegistrationMethods.PinInPost && // PITP registration in progress
uo.PINSentDate.HasValue &&
uo.PINSentDate.Value > pinExpiresDate)))
.ToList();
var records = orphanOrganisations.Select(
org => new
{
org.OrganisationId,
org.OrganisationName,
Address = org.GetLatestAddress()?.GetAddressString(),
Sector = org.SectorType,
ReportingDeadline = ReportingYearsHelper.GetDeadlineForAccountingDate(org.SectorType.GetAccountingStartDate()),
})
.ToList();
string fileDownloadName = $"Gpg-OrphanOrganisations-{new GDSDateFormatter(VirtualDateTime.Now).FullStartDateTime}.csv";
FileContentResult fileContentResult = DownloadHelper.CreateCsvDownload(records, fileDownloadName);
return(fileContentResult);
}
