/// <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;
}
protected Dictionary<int, DataRow> GetIntvsAggregatedToReportingLevel(DateTime start, DateTime end, List<AdminLevel> units)
{
IntvRepository iRepo = new IntvRepository();
ReportOptions options = new ReportOptions
{
MonthYearStarts = start.Month,
StartDate = start,
EndDate = end,
IsCountryAggregation = false,
IsByLevelAggregation = true,
IsAllLocations = false,
IsNoAggregation = false,
IsGroupByRange = true,
};
options.SelectedAdminLevels = units;
IntvReportGenerator gen = new IntvReportGenerator();
var intvIds = new List<int> { 2, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
foreach (int id in intvIds)
AddIntvIndicators(options, id, iRepo);
ReportResult ddResult = gen.Run(new SavedReport { ReportOptions = options });
Dictionary<int, DataRow> intvData = new Dictionary<int, DataRow>();
foreach (DataRow dr in ddResult.DataTableResults.Rows)
{
int id = 0;
if (int.TryParse(dr["ID"].ToString(), out id))
{
if (intvData.ContainsKey(id))
intvData[id] = dr;
else
intvData.Add(id, dr);
}
}
return intvData;
}
//Worksheet 6: IVM Intervention
//aggregated to the reporting level
//all IVM intervention indicators
private void Add6(excel.Worksheet xlsWorksheet, excel.Range rng, List<AdminLevel> districts, DateTime start, DateTime end, int month)
{
ReportOptions options = new ReportOptions { MonthYearStarts = month, StartDate = start, EndDate = end, IsCountryAggregation = false, IsByLevelAggregation = true, IsAllLocations = false, IsNoAggregation = false };
options.SelectedAdminLevels = districts;
IntvRepository repo = new IntvRepository();
IntvType intv = repo.GetIntvType((int)StaticIntvType.Ivm);
IntvReportGenerator gen = new IntvReportGenerator();
AddReportToSheet(xlsWorksheet, intv.Indicators, options, gen, (int)StaticIntvType.Ivm, intv.IntvTypeName, rng);
}
protected ReportResult RunIntvReport(SavedReport report, PersonsTreatedCoverageReportOptions standardOpts, List<int> filteredIntvIds)
{
// Add all the relevant intervention indicators
foreach (IntvType intvType in IntvTypes)
{
AddIndicators(intvType.Id, "PcIntvNumEligibleIndividualsTargeted", intvType, intvType.IntvTypeName, intvType.DisplayNameKey, report.ReportOptions);
AddIndicators(intvType.Id, "PcIntvNumIndividualsTreated", intvType, intvType.IntvTypeName, intvType.DisplayNameKey, report.ReportOptions);
AddIndicators(intvType.Id, "PcIntvPsacTreated", intvType, intvType.IntvTypeName, intvType.DisplayNameKey, report.ReportOptions);
AddIndicators(intvType.Id, "PcIntvNumSacTreated", intvType, intvType.IntvTypeName, intvType.DisplayNameKey, report.ReportOptions);
}
// Report gen
IntvReportGenerator gen = new IntvReportGenerator();
// Set the IDs of the filtererd interventions
if (filteredIntvIds != null && filteredIntvIds.Count > 0)
gen.CmdTextOverride = DetermineInterventionSql(filteredIntvIds, report.ReportOptions);
// Recent distro static classs
RecentDistro recentDistro = RecentDistro.GetInstance(true /* instantiate */);
recentDistro.Run(report.ReportOptions);
// Run the report
ReportResult result = gen.Run(report);
// Clear the RecentDistro from memory
RecentDistro.ClearInstance();
return result;
}
protected Dictionary<int, DataRow> GetEligibleInSubdistricts(DateTime start, DateTime end)
{
IntvRepository iRepo = new IntvRepository();
ReportOptions options = new ReportOptions
{
MonthYearStarts = start.Month,
StartDate = start,
EndDate = end,
IsCountryAggregation = false,
IsByLevelAggregation = true,
IsAllLocations = false,
IsNoAggregation = false,
IsGroupByRange = true,
};
var childType = settings.GetAllAdminLevels().Where(a => a.LevelNumber > AdminLevelType.LevelNumber).OrderBy(l => l.LevelNumber).FirstOrDefault();
if (childType == null)
return new Dictionary<int, DataRow>();
options.SelectedAdminLevels = demo.GetAdminLevelByLevel(childType.LevelNumber);
IntvReportGenerator gen = new IntvReportGenerator();
IntvType iType = iRepo.GetIntvType(23);
var eligible = iType.Indicators.FirstOrDefault(i => i.Value.DisplayName == "PcIntvNumEligibleIndividualsTargeted");
options.SelectedIndicators.Add(ReportRepository.CreateReportIndicator(iType.Id, eligible.Value));
ReportResult ddResult = gen.Run(new SavedReport { ReportOptions = options });
Dictionary<int, DataRow> intvData = new Dictionary<int, DataRow>();
foreach (DataRow dr in ddResult.DataTableResults.Rows)
{
int id = 0;
if (int.TryParse(dr["ID"].ToString(), out id))
{
if (intvData.ContainsKey(id))
intvData[id] = dr;
else
intvData.Add(id, dr);
}
}
return intvData;
}
