private static void CalculateEarningComparisonMetric(short collectionPeriod, DateTime processingStartTime, FilterMode filterMode, bool useLegacyMode, short academicYear) { Spinner.Start("Getting required data. ", spinner => { var dasConnectionString = ConfigurationManager.ConnectionStrings["DasConnectionString"].ConnectionString; var dcConnectionString = ConfigurationManager.ConnectionStrings[$"DcEarnings{academicYear}ConnectionString"].ConnectionString; var outputPath = ConfigurationManager.AppSettings["OutputPath"]; var dasQuery = ResourceHelpers.ReadResource(DasQuery); var legacyDasQuery = ResourceHelpers.ReadResource(LegacyDasQuery); var dcQuery = ResourceHelpers.ReadResource(DcQuery); IEnumerable <EarningsRow> dcData; IEnumerable <EarningsRow> dasData; spinner.Text = "Getting DC data"; using (var dcConnection = new SqlConnection(dcConnectionString)) { dcData = dcConnection.Query <EarningsRow>(dcQuery, new { collectionperiod = collectionPeriod }, commandTimeout: 5000); } spinner.Text = "Getting DAS data"; using (var dasConnection = new SqlConnection(dasConnectionString)) { if (useLegacyMode) { dasData = dasConnection.Query <EarningsRow>(legacyDasQuery, new { collectionperiod = collectionPeriod, monthendStartTime = processingStartTime }, commandTimeout: 0); } else { dasData = dasConnection.Query <EarningsRow>(dasQuery, new { collectionperiod = collectionPeriod, academicyear = academicYear }, commandTimeout: 0); } } spinner.Text = "Calculating values"; var joinedValues = dasData.FullJoin( dcData, earningsRow => new { earningsRow.Ukprn, earningsRow.ApprenticeshipContractType }, dasRow => new CombinedRow(dasRow.Ukprn, dasRow.ApprenticeshipContractType) { DasRow = dasRow, DcRow = null }, dcRow => new CombinedRow(dcRow.Ukprn, dcRow.ApprenticeshipContractType) { DasRow = null, DcRow = dcRow }, (dasRow, dcRow ) => new CombinedRow(dasRow.Ukprn, dasRow.ApprenticeshipContractType) { DasRow = dasRow, DcRow = dcRow } ) .OrderBy(row => row.Ukprn) .ThenBy(row => row.ApprenticeshipContractType) .ToList(); var filteredResults = FilterValues(filterMode, joinedValues, spinner); using (var templateStream = ResourceHelpers.OpenResource(ExcelTemplate)) { using (var spreadsheet = new XLWorkbook(templateStream)) { var sheet = spreadsheet.Worksheet("Earnings Comparison"); AddFilterSheet(spreadsheet, filterMode, filteredResults.Item2); AddSummaryInfo(sheet, collectionPeriod, processingStartTime); WriteDataToSheet(sheet, filteredResults.Item1); sheet.AdjustToContent(); sheet.SetAsTable(9, 1); spinner.Text = $"Saving data to spreadsheet to: {outputPath}"; ExcelHelpers.SaveWorksheet(spreadsheet, outputPath); spinner.Text = "Spreadsheet saved."; } } }); }
private static List <long> GetFilterItems(FilterMode filterMode) { return(filterMode == FilterMode.Blacklist ? ResourceHelpers.LoadFilterValues(BlackListFile) : ResourceHelpers.LoadFilterValues(WhiteListFile)); }