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