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