/// <summary>
/// Adds data for the SCH sheet
/// </summary>
/// <param name="xlsWorksheet"></param>
/// <param name="start"></param>
/// <param name="end"></param>
private void AddSch(excel.Worksheet xlsWorksheet, DateTime start, DateTime end)
{
SurveyRepository repo = new SurveyRepository();
int rowCount = 0;
var surveys = repo.GetByTypeForDistrictsInDateRange(new List<int> { (int)StaticSurveyType.SchistoSentinel, (int)StaticSurveyType.SchMapping }, start, end);
int rowNumber = 8;
foreach (SurveyBase survey in surveys.OrderBy(s => s.SortOrder))
{
if (survey.TypeOfSurvey.Id == (int)StaticSurveyType.SchistoSentinel)
{
if (survey.SentinelSiteId.HasValue)
{
var site = repo.GetSiteById(survey.SentinelSiteId.Value);
if (site.Lat.HasValue)
xlsWorksheet.Cells[rowNumber,4] = Math.Round(site.Lat.Value, 2);
if (site.Lng.HasValue)
xlsWorksheet.Cells[rowNumber, 5] = Math.Round(site.Lng.Value, 2);
xlsWorksheet.Cells[rowNumber, 2] = site.SiteName;
}
else
{
if (survey.Lat.HasValue)
xlsWorksheet.Cells[rowNumber,4] = Math.Round(survey.Lat.Value, 2);
if (survey.Lng.HasValue)
xlsWorksheet.Cells[rowNumber, 5] = Math.Round(survey.Lng.Value, 2);
xlsWorksheet.Cells[rowNumber, 2] = survey.SpotCheckName;
}
}
foreach (AdminLevel unit in survey.AdminLevels)
{
xlsWorksheet.Cells[rowNumber, 1] = unit.Name;
foreach (IndicatorValue val in survey.IndicatorValues)
{
if (val.Indicator.DisplayName == "SCHMapSurSiteNames")
xlsWorksheet.Cells[rowNumber, 2] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurStartDateOfSurvey" || val.Indicator.DisplayName == "SCHMapSurStartDateOfSurvey")
{
DateTime date;
if (DateTime.TryParse(val.DynamicValue, out date))
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.C] = date.ToString("MMMM");
}
else if (val.Indicator.DisplayName == "SCHMapSurLatitude" && !string.IsNullOrEmpty(val.DynamicValue))
xlsWorksheet.Cells[rowNumber, 4] = Math.Round(Convert.ToDouble(val.DynamicValue), 2);
else if (val.Indicator.DisplayName == "SCHMapSurLongitude" && !string.IsNullOrEmpty(val.DynamicValue))
xlsWorksheet.Cells[rowNumber, 5] = Math.Round(Convert.ToDouble(val.DynamicValue), 2);
else if (val.Indicator.DisplayName == "SCHMapSurTestType" || val.Indicator.DisplayName == "SCHSurTestType")
xlsWorksheet.Cells[rowNumber, 6] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHMapSurAgeGroupSurveyed" || val.Indicator.DisplayName == "SCHSurAgeGroupSurveyed")
xlsWorksheet.Cells[rowNumber, 7] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurNumberOfIndividualsExaminedForUrin" || val.Indicator.DisplayName == "SCHMapSurNumberOfIndividualsExaminedForU")
xlsWorksheet.Cells[rowNumber, 8] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurNumberOfIndividualsPositiveForHaem" || val.Indicator.DisplayName == "SCHMapSurNumberOfIndividualsPositiveForH")
xlsWorksheet.Cells[rowNumber, 9] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurProportionOfHeavyIntensityUrinaryS" || val.Indicator.DisplayName == "SCHMapSurProportionOfHeavyIntensityUrina")
xlsWorksheet.Cells[rowNumber, 11] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurProportionOfModerateIntensityUrina" || val.Indicator.DisplayName == "SCHMapSurProportionOfModerateIntensityUr")
xlsWorksheet.Cells[rowNumber, 12] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurNumberOfIndividualsExaminedForInte" || val.Indicator.DisplayName == "SCHMapSurNumberOfIndividualsExaminedForI")
xlsWorksheet.Cells[rowNumber, 13] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurNumberOfIndividualsPositiveForInte" || val.Indicator.DisplayName == "SCHMapSurNumberOfIndividualsPositiveForI")
xlsWorksheet.Cells[rowNumber, 14] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurProportionOfHeavyIntensityIntestin" || val.Indicator.DisplayName == "SCHMapSurProportionOfHeavyIntensityIntes")
xlsWorksheet.Cells[rowNumber, 16] = val.DynamicValue;
else if (val.Indicator.DisplayName == "SCHSurProportionOfModerateIntensityIntes" || val.Indicator.DisplayName == "SCHMapSurProportionOfModerateIntensityIn")
xlsWorksheet.Cells[rowNumber, 17] = val.DynamicValue;
}
rowNumber++;
rowCount++;
}
}
xlsWorksheet.Cells[3, 5] = rowCount;
}
/// <summary>
/// Adds the LF sheet
/// </summary>
/// <param name="xlsWorksheet"></param>
/// <param name="start"></param>
/// <param name="end"></param>
private void AddLfMm(excel.Worksheet xlsWorksheet, DateTime start, DateTime end)
{
ReportOptions options = new ReportOptions { MonthYearStarts = 1, StartDate = start, EndDate = end, IsCountryAggregation = true, IsByLevelAggregation = false, IsAllLocations = false, IsNoAggregation = false };
IntvReportGenerator gen = new IntvReportGenerator();
IntvRepository repo = new IntvRepository();
SurveyRepository surveys = new SurveyRepository();
// Indicator parser
IndicatorParser indicatorParser = new IndicatorParser();
indicatorParser.LoadRelatedLists();
int rowCount = 0;
IntvType type = repo.GetIntvType((int)StaticIntvType.LfMorbidityManagement);
foreach (var indicator in type.Indicators)
if (!indicator.Value.IsCalculated)
options.SelectedIndicators.Add(ReportRepository.CreateReportIndicator(type.Id, indicator));
ReportResult result = gen.Run(new SavedReport { ReportOptions = options });
// Top horizontal columns
foreach (DataRow dr in result.DataTableResults.Rows)
{
foreach (DataColumn col in result.DataTableResults.Columns)
{
if (col.ColumnName.Contains(TranslationLookup.GetValue("LFMMDPNumLymphoedemaPatients", "LFMMDPNumLymphoedemaPatients") + " -"))
xlsWorksheet.Cells[5, 10] = dr[col];
if (col.ColumnName.Contains(TranslationLookup.GetValue("LFMMDPNumLymphoedemaPatientsTreated", "LFMMDPNumLymphoedemaPatientsTreated") + " -"))
xlsWorksheet.Cells[7, 10] = dr[col];
if (col.ColumnName.Contains(TranslationLookup.GetValue("LFMMDPNumHydroceleCases", "LFMMDPNumHydroceleCases") + " -"))
xlsWorksheet.Cells[8, 10] = dr[col];
if (col.ColumnName.Contains(TranslationLookup.GetValue("LFMMDPNumHydroceleCasesTreated", "LFMMDPNumHydroceleCasesTreated") + " -"))
xlsWorksheet.Cells[9, 10] = dr[col];
}
}
// Last half of year (J6)
options.StartDate = start.AddMonths(6);
result = gen.Run(new SavedReport { ReportOptions = options });
foreach (DataRow dr in result.DataTableResults.Rows)
{
foreach (DataColumn col in result.DataTableResults.Columns)
{
if (col.ColumnName.Contains(TranslationLookup.GetValue("LFMMDPNumLymphoedemaPatients", "LFMMDPNumLymphoedemaPatients") + " -"))
xlsWorksheet.Cells[6, 10] = dr[col];
}
}
// Get surveys for rows
var lfSurveys = surveys.GetByTypeForDistrictsInDateRange(new List<int> { (int)StaticSurveyType.LfMapping, (int)StaticSurveyType.LfSentinel, (int)StaticSurveyType.LfTas }, start, end);
int rowNumber = 15;
foreach (SurveyBase survey in lfSurveys.OrderBy(s => s.SortOrder))
{
foreach (var adminLevel in survey.AdminLevels)
{
bool isMfTestType = false;
// Static indicators
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.B] = adminLevel.Name;
if (survey.TypeOfSurvey.Id == (int)StaticSurveyType.LfMapping)
{
var testTypeVal = survey.IndicatorValues.FirstOrDefault(v => v.Indicator.DisplayName == "LFMapSurTestType");
if (testTypeVal != null && testTypeVal.DynamicValue == "Mf")
isMfTestType = true;
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.D] = TranslationLookup.GetValue("Mapping", "Mapping");
}
else if (survey.TypeOfSurvey.Id == (int)StaticSurveyType.LfSentinel)
{
var testTypeVal = survey.IndicatorValues.FirstOrDefault(v => v.Indicator.DisplayName == "LFSurTestType");
if (testTypeVal != null && testTypeVal.DynamicValue == "Mf")
isMfTestType = true;
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.D] = survey.SiteType;
if (survey.SentinelSiteId.HasValue)
{
var site = surveys.GetSiteById(survey.SentinelSiteId.Value);
if (site.Lat.HasValue)
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.F] = Math.Round(site.Lat.Value, 2);
if (site.Lng.HasValue)
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.G] = Math.Round(site.Lng.Value, 2);
// Name of survey site
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.C] = site.SiteName;
}
else
{
if (survey.Lat.HasValue)
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.F] = Math.Round(survey.Lat.Value, 2);
if (survey.Lng.HasValue)
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.G] = Math.Round(survey.Lng.Value, 2);
// Name of survey site
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.C] = survey.SpotCheckName;
}
}
else if (survey.TypeOfSurvey.Id == (int)StaticSurveyType.LfTas)
{
// Year of Start date of MDA of earliest Intervention
List<IntvBase> interventions = repo.GetAll(new List<int>
{
(int)StaticIntvType.Alb, (int)StaticIntvType.Alb2, (int)StaticIntvType.DecAlb, (int)StaticIntvType.Ivm, (int)StaticIntvType.IvmAlb,
(int)StaticIntvType.IvmPzq, (int)StaticIntvType.IvmPzqAlb, (int)StaticIntvType.Mbd, (int)StaticIntvType.Pzq, (int)StaticIntvType.PzqAlb,
(int)StaticIntvType.PzqMbd, (int)StaticIntvType.ZithroTeo
}, new List<int> {adminLevel.Id});
if (interventions.Count > 0) {
// Get all the MDA Start date indicators
List<string> mdaStarts = interventions.SelectMany(x => x.IndicatorValues).Where(v => v.Indicator.DisplayName == "PcIntvStartDateOfMda")
.Select(x => x.DynamicValue).ToList();
if (mdaStarts.Count > 0) {
// Get the earliest one
DateTime earliestMda = Convert.ToDateTime(mdaStarts.OrderBy(x => Convert.ToDateTime(x)).FirstOrDefault());
// Add it to the worksheet
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.H] = earliestMda.Year;
}
}
}
// Dynamic indicators
foreach (IndicatorValue val in survey.IndicatorValues)
{
// Eval name
if (val.Indicator.DisplayName == "EuName")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.A] = indicatorParser.Parse(val.Indicator.DataTypeId, val.IndicatorId, val.DynamicValue);
// Name of survey site
else if (val.Indicator.DisplayName == "LFMapSurSiteNames")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.C] = val.DynamicValue;
// TAS objective
else if (val.Indicator.DisplayName == "TASTasObjective")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.D] = TranslationLookup.GetValue(val.DynamicValue, val.DynamicValue);
else if (val.Indicator.DisplayName == "LFMapSurStartDateOfSurvey" || val.Indicator.DisplayName == "LFSurStartDateOfSurvey" || val.Indicator.DisplayName == "TASStartDateOfSurvey")
{
DateTime date;
if (DateTime.TryParse(val.DynamicValue, out date))
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.E] = date.ToString("MMMM");
}
else if (val.Indicator.DisplayName == "LFMapSurLatitude" && !string.IsNullOrEmpty(val.DynamicValue))
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.F] = Math.Round(Convert.ToDouble(val.DynamicValue), 2);
else if (val.Indicator.DisplayName == "LFMapSurLongitude" && !string.IsNullOrEmpty(val.DynamicValue))
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.G] = Math.Round(Convert.ToDouble(val.DynamicValue), 2);
else if (val.Indicator.DisplayName == "LFSurDateOfTheFirstRoundOfPc")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.H] = val.DynamicValue;
else if (val.Indicator.DisplayName == "LFSurNumberOfRoundsOfPcCompletedPriorToS" || val.Indicator.DisplayName == "4190984d-f272-4359-8414-6e7ef06fc4bc")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.I] = val.DynamicValue;
//else if (val.Indicator.DisplayName == "LFMapSurTestType" || val.Indicator.DisplayName == "LFSurTestType")
// xlsWorksheet.Cells[rowNumber, 9] = TranslationLookup.GetValue(val.DynamicValue, val.DynamicValue);
// MF: Number of people examined
else if (val.Indicator.DisplayName == "LFMapSurNumberOfIndividualsExamined" || val.Indicator.DisplayName == "LFSurNumberOfIndividualsExamined")
{
if (isMfTestType) // MF: Number of people examined
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.J] = val.DynamicValue;
else // Ag/Ab: Number of people examined
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.S] = val.DynamicValue;
}
// MF: Number of people positive
else if (val.Indicator.DisplayName == "LFSurNumberOfIndividualsPositive" || val.Indicator.DisplayName == "LFMapSurNumberOfIndividualsPositive")
{
if (isMfTestType) // MF: Number of people positive
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.K] = val.DynamicValue;
else // Ag/Ab: Number of people positive
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.T] = val.DynamicValue;
}
// mean dens
//else if (val.Indicator.DisplayName == "LFMapSurMeanDensity" || val.Indicator.DisplayName == "LFSurMeanDensity")
// xlsWorksheet.Cells[rowNumber, 13] = val.DynamicValue;
// count
//else if (val.Indicator.DisplayName == "LFSurCount" || val.Indicator.DisplayName == "LFMapSurCount")
// xlsWorksheet.Cells[rowNumber, 14] = val.DynamicValue;
// community load
//else if (val.Indicator.DisplayName == "LFMapSurCommunityMfLoad" || val.Indicator.DisplayName == "LFSurCommunityMfLoad")
// xlsWorksheet.Cells[rowNumber, 15] = val.DynamicValue;
// MF: % Positive
else if (val.Indicator.DisplayName == "LFMapSurPositive" || val.Indicator.DisplayName == "LFSurPositive")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.L] = val.DynamicValue;
// Test Type
else if (val.Indicator.DisplayName == "LFMapSurTestType" || val.Indicator.DisplayName == "LFSurTestType" || val.Indicator.DisplayName == "TASDiagnosticTest")
{
if (val.Indicator.DisplayName == "TASDiagnosticTest")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.M] = val.DynamicValue;
else
{
if (!isMfTestType)
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.M] = val.DynamicValue;
}
}
// Age range
else if (val.Indicator.DisplayName == "LFSurAgeRange" || val.Indicator.DisplayName == "LFMapSurAgeRange" || val.Indicator.DisplayName == "TASAgeRange")
{
if (val.Indicator.DisplayName == "TASAgeRange")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.N] = val.DynamicValue;
else
{
if (!isMfTestType)
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.N] = val.DynamicValue;
}
}
// Survey site
else if (val.Indicator.DisplayName == "LFMapSurMappingSiteLocation" || val.Indicator.DisplayName == "eab663f6-1eb8-4efc-85da-2844ee720020" || val.Indicator.DisplayName == "TASLocationType")
{
if (val.Indicator.DisplayName == "TASLocationType")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.O] = val.DynamicValue;
else
{
if (!isMfTestType)
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.O] = val.DynamicValue;
}
}
// Ag/Ab: Survey type
else if (val.Indicator.DisplayName == "TASSurveyType")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.P] = val.DynamicValue;
// Ag/Ab: # schools or EA targeted
else if (val.Indicator.DisplayName == "TASTargetNumberOfSchoolsOrEas")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.Q] = val.DynamicValue;
// Ag/Ab: Target sample size
else if (val.Indicator.DisplayName == "LFMapSurTargetSampleSize" || val.Indicator.DisplayName == "LFSurTargetSampleSize" || val.Indicator.DisplayName == "TASTargetSampleSize")
{
if (val.Indicator.DisplayName == "TASTargetSampleSize")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.R] = val.DynamicValue;
else
{
if (!isMfTestType)
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.R] = val.DynamicValue;
}
}
// Ag/Ab: Number of people examined
else if (val.Indicator.DisplayName == "d807913f-b3a1-4948-a2b3-54eb0800a3bc")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.S] = val.DynamicValue;
// Ag/Ab: Number of people positive
else if (val.Indicator.DisplayName == "TASActualSampleSizePositive")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.T] = val.DynamicValue;
// Ag/Ab: Critical cut-off
else if (val.Indicator.DisplayName == "TASCriticalCutoffValue")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.V] = val.DynamicValue;
// Ag/Ab: Decision
else if (val.Indicator.DisplayName == "TASCriticalCutoff")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.W] = TranslationLookup.GetValue(val.DynamicValue, val.DynamicValue);
// Lymphoedema: Number of people examined
else if (val.Indicator.DisplayName == "LFSurExaminedLympho" || val.Indicator.DisplayName == "LFMapSurExaminedLympho")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.X] = val.DynamicValue;
// Lymphoedema: Number of people positive
else if (val.Indicator.DisplayName == "LFMapSurNumberOfCasesOfLymphoedema" || val.Indicator.DisplayName == "LFSurPosLympho")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.Y] = val.DynamicValue;
// Hydrocele: Number of people examined
else if (val.Indicator.DisplayName == "LFSurExaminedHydro" || val.Indicator.DisplayName == "LFMapSurExaminedHydro1")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.AA] = val.DynamicValue;
// Hydrocele: Number of people positive
else if (val.Indicator.DisplayName == "LFMapSurNumberOfCasesOfHydrocele" || val.Indicator.DisplayName == "LFSurPosHydro")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.AB] = val.DynamicValue;
// Notes
else if (val.Indicator.DisplayName == "Notes")
xlsWorksheet.Cells[rowNumber, (int)ExcelCol.AD] = val.DynamicValue;
}
rowNumber++;
rowCount++;
}
}
// run formula
xlsWorksheet.Cells[3, 5] = rowCount;
}
