public ExportResult DoExport(string fileName, int userId, LeishReportQuestions questions)
{
try
{
// Setup the Excel worksheet
SetupWorksheet();
// Determine the start and end of the year
DetermineStartDate(questions);
// Get the 2nd admin levelt ype
GetAdmin2ndLvlData();
// Get country data
GetCountryData();
// Get intervention type data
GetIntvTypes();
// Get all the Leish Annual interventions
GetAllLeishAnnualIntvs();
// Get disease dist data
GetDiseases();
// Add country data
AddCountryInfo(questions);
// Run Leish monthly intervention with country agg report data
RunLeishMonthlyIntvCountryAggReport();
// Run Leish annual intervention with country agg report data
RunLeishAnnualIntvCountryAggReport();
// Run Leish monthly intervention report with no agg
RunLeishMonthlyIntvNoAggReport();
// Run the Leish disease distribution report for the country
RunLeishCountryAggDdReport();
// Add epi
AddEpi(questions);
// Add Pop at risk (if there is a country total population)
if (CountryDemo != null && CountryDemo.TotalPopulation.HasValue && CountryDemo.TotalPopulation.Value > 0)
AddPopAtRisk();
// Add monthly dist
AddMonthlyDist();
// Control
AddControl(questions);
// Diagnosis
AddDiagnosis();
// Treatment
AddTreatment(questions);
// Teardown worksheet
TeardownWorksheet(fileName);
return new ExportResult { WasSuccess = true };
}
catch (Exception ex)
{
return new ExportResult(ex.Message);
}
}
private void SetupOptions()
{
Questions = new LeishReportQuestions();
}
private void AddTreatment(LeishReportQuestions questions)
{
// Treatment free in public sector
AddValueToRange(XlsWorksheet, XlsRange, "I78", questions.LeishRepIsTreatFree ? "Yes" : "No");
// Antileish medicines
AddValueToRange(XlsWorksheet, XlsRange, "I79", !string.IsNullOrEmpty(questions.LeishRepAntiMedInNml) ? questions.LeishRepAntiMedInNml : "");
// Number of relapses
AddReportValueToExport(XlsWorksheet, XlsRange, "G82", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvNumberOfVLRelapseCases, LeishAnnualIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I82", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvNumberOfCLRelapseCases, LeishAnnualIntvCountryAggReport);
// Number of cases treated
AddReportValueToExport(XlsWorksheet, XlsRange, "G83", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvNumberOfNewVLCasesTreated, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I83", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvNumberOfNewCLCasesTreated, LeishMonthlyIntvCountryAggReport);
// Initial cure rate
AddReportValueToExport(XlsWorksheet, XlsRange, "G84", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntOfInitialCuredCasesOutOfTotalNewCasesTreatedForVL, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I84", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntOfInitialCuredCasesOutOfTotalNewCasesTreatedForCL, LeishMonthlyIntvCountryAggReport);
// Failure rate
AddReportValueToExport(XlsWorksheet, XlsRange, "G85", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntOfFailureCasesOutOfTotalNewCasesTreatedForVL, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I85", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntOfFailureCasesOutOfTotalNewCasesTreatedForCL, LeishMonthlyIntvCountryAggReport);
// Case fatality rate
AddReportValueToExport(XlsWorksheet, XlsRange, "G86", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntCaseFatalityRateForVL, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I86", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntCaseFatalityRateForCL, LeishMonthlyIntvCountryAggReport);
// Number of cases followed up at least 6 months
AddReportValueToExport(XlsWorksheet, XlsRange, "G87", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvNumberOfNewVLCasesFollowedUpAtLeast6Months, LeishAnnualIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I87", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvNumberOfNewCLCasesFollowedUpAtLeast6Months, LeishAnnualIntvCountryAggReport);
// Cure rate 6 months
AddReportValueToExport(XlsWorksheet, XlsRange, "G88", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvOfNewVLCasesCuredOutOfNewCasesFollowedUp, LeishAnnualIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I88", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvOfNewCLCasesCuredOutOfNewCasesFollowedUp, LeishAnnualIntvCountryAggReport);
// Relapse definition
AddValueToRange(XlsWorksheet, XlsRange, "F89", !string.IsNullOrEmpty(questions.LeishRepRelapseDefVl) ? questions.LeishRepRelapseDefVl : "");
AddValueToRange(XlsWorksheet, XlsRange, "F90", !string.IsNullOrEmpty(questions.LeishRepRelapseDefCl) ? questions.LeishRepRelapseDefCl : "");
// Failure definition
AddValueToRange(XlsWorksheet, XlsRange, "F91", !string.IsNullOrEmpty(questions.LeishRepFailureDefVl) ? questions.LeishRepFailureDefVl : "");
AddValueToRange(XlsWorksheet, XlsRange, "F92", !string.IsNullOrEmpty(questions.LeishRepFailureDefCl) ? questions.LeishRepFailureDefCl : "");
}
private void AddControl(LeishReportQuestions questions)
{
// Year established
AddValueToRange(XlsWorksheet, XlsRange, "F60", questions.LeishRepYearLncpEstablished.HasValue ? questions.LeishRepYearLncpEstablished.Value.ToString() : "");
// URL LNCP
AddValueToRange(XlsWorksheet, XlsRange, "J60", !string.IsNullOrEmpty(questions.LeishRepUrlLncp) ? questions.LeishRepUrlLncp : "");
// Year latest guidelines
AddValueToRange(XlsWorksheet, XlsRange, "F61", questions.LeishRepYearLatestGuide.HasValue ? questions.LeishRepYearLatestGuide.Value.ToString() : "");
// Is notifiable
AddValueToRange(XlsWorksheet, XlsRange, "M61", questions.LeishRepIsNotifiable ? "Yes" : "No");
// Vector
AddValueToRange(XlsWorksheet, XlsRange, "F62", questions.LeishRepIsVectProg ? "Yes" : "No");
// Host
AddValueToRange(XlsWorksheet, XlsRange, "M62", questions.LeishRepIsHostProg ? "Yes" : "No");
// Type of insesticide IRS
AddReportValueToExport(XlsWorksheet, XlsRange, "F63", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvTypeOfInsecticideUsedForIndoorResidualSpryaing, LeishAnnualIntvCountryAggReport);
// Number of facilities
AddValueToRange(XlsWorksheet, XlsRange, "M63", questions.LeishRepNumHealthFac.HasValue ? questions.LeishRepNumHealthFac.Value.ToString() : "");
}
private void AddEpi(LeishReportQuestions questions)
{
// Endemicity
AddReportValueToExport(XlsWorksheet, XlsRange, "G13", LeishDd.Disease.DisplayName,
Translations.LeishDiseaseDistEndemicityStatusVL, LeishCountryAggDdReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I13", LeishDd.Disease.DisplayName,
Translations.LeishDiseaseDistEndemicityStatusCL, LeishCountryAggDdReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "K13", LeishDd.Disease.DisplayName,
Translations.LeishDiseaseDistEndemicityStatusPKDL, LeishCountryAggDdReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "M13", LeishDd.Disease.DisplayName,
Translations.LeishDiseaseDistEndemicityStatusMCL, LeishCountryAggDdReport);
// Number of new cases (incidence)
AddReportValueToExport(XlsWorksheet, XlsRange, "G14", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvTotalNumberOfNewVLCasesDiagnosedLabAndClinical, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I14", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvTotalNumberOfNewCLCasesDiagnosedLabAndClinical, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "K14", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvTotalNumberOfNewPKDLCasesDiagnosed, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "M14", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvTotalNumberOfNewMCLCasesDiagnosed, LeishMonthlyIntvCountryAggReport);
// Number of imported new cases
AddReportValueToExport(XlsWorksheet, XlsRange, "G15", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvNumberOfImportedVLCases, LeishAnnualIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I15", LeishAnnualIntvType.IntvTypeName,
Translations.LeishAnnIntvNumberOfImportedCLCases, LeishAnnualIntvCountryAggReport);
// Incidence rate (calc)
AddReportValueToExport(XlsWorksheet, XlsRange, "G16", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvVLIncidenceRate10000PeopleYear, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I16", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvCLIncidenceRate10000PeopleYear, LeishMonthlyIntvCountryAggReport);
// Gender distribution (%)
AddReportValueToExport(XlsWorksheet, XlsRange, "G17", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntFemaleVL, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I17", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntFemaleCL, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "K17", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntFemalePKDL, LeishMonthlyIntvCountryAggReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "M17", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntFemaleMCL, LeishMonthlyIntvCountryAggReport);
// Age group Distribution
AddAgeGroupDistToExport(XlsWorksheet, XlsRange, "G18", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntOfNewVLCasesInChildrenLssThn5Years, Translations.LeishMontIntvPrcntOfNewVLCasesInChildren5To14Years,
Translations.LeishMontIntvPrcntOfNewVLCasesInAdultsGrtrThn14Years, LeishMonthlyIntvCountryAggReport);
AddAgeGroupDistToExport(XlsWorksheet, XlsRange, "I18", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntOfNewCLCasesInChildrenLssThn5Years, Translations.LeishMontIntvPrcntOfNewCLCasesInChildren5To14Years,
Translations.LeishMontIntvPrcntOfNewCLCasesInAdultsGrtrThn14Years, LeishMonthlyIntvCountryAggReport);
AddAgeGroupDistToExport(XlsWorksheet, XlsRange, "K18", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntOfNewPKDLCasesInChildrenLssThn5Years, Translations.LeishMontIntvPrcntOfNewPKDLCasesInChildren5To14Years,
Translations.LeishMontIntvPrcntOfNewPKDLCasesInAdultsGrtrThn14Years, LeishMonthlyIntvCountryAggReport);
AddAgeGroupDistToExport(XlsWorksheet, XlsRange, "M18", LeishMonthlyIntvType.IntvTypeName,
Translations.LeishMontIntvPrcntOfNewMCLCasesInChildrenLssThn5Years, Translations.LeishMontIntvPrcntOfNewMCLCasesInChildren5To14Years,
Translations.LeishMontIntvPrcntOfNewMCLCasesInAdultsGrtrThn14Years, LeishMonthlyIntvCountryAggReport);
// 2nd level admin endemic
AddValueToRange(XlsWorksheet, XlsRange, "G19", questions.LeishRepEndemicAdmin2Vl.HasValue ? questions.LeishRepEndemicAdmin2Vl.Value.ToString() : "");
AddValueToRange(XlsWorksheet, XlsRange, "I19", questions.LeishRepEndemicAdmin2Cl.HasValue ? questions.LeishRepEndemicAdmin2Cl.Value.ToString() : "");
// Outbreak
AddReportValueToExport(XlsWorksheet, XlsRange, "G21", LeishDd.Disease.DisplayName,
Translations.LeishDiseaseDistWasThereAnyVLOutbreakThisYear, LeishCountryAggDdReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I21", LeishDd.Disease.DisplayName,
Translations.LeishDiseaseDistWasThereAnyCLOutbreakThisYear, LeishCountryAggDdReport);
// Foci
AddReportValueToExport(XlsWorksheet, XlsRange, "G22", LeishDd.Disease.DisplayName,
Translations.LeishDiseaseDistNumberOfNewVLFociThisYearAreasReportingCasesForTheFirstTime, LeishCountryAggDdReport);
AddReportValueToExport(XlsWorksheet, XlsRange, "I22", LeishDd.Disease.DisplayName,
Translations.LeishDiseaseDistNumberOfNewCLFociThisYearAreasReportingCasesForTheFirstTime, LeishCountryAggDdReport);
}
private void AddCountryInfo(LeishReportQuestions questions)
{
AddValueToRange(XlsWorksheet, XlsRange, "B3", CountryData.Name);
AddValueToRange(XlsWorksheet, XlsRange, "M3", questions.LeishRepYearReporting);
AddValueToRange(XlsWorksheet, XlsRange, "D6", CountryDemo.TotalPopulation);
// Income status
AddValueToRange(XlsWorksheet, XlsRange, "D9", CountryDemo.CountryIncomeStatus);
// GDP
AddValueToRange(XlsWorksheet, XlsRange, "D8", CountryDemo.GrossDomesticProduct);
// Gender ratio
string femalePercent = CalculatePercent(CountryDemo.PopFemale, CountryDemo.TotalPopulation);
string malePercent = CalculatePercent(CountryDemo.PopMale, CountryDemo.TotalPopulation);
string genderRatio = string.Format("{0}% female - {1}% male", femalePercent, malePercent);
AddValueToRange(XlsWorksheet, XlsRange, "D7", genderRatio);
// Population age
string ageRatio = string.Format("{0}%/{1}%/{2}%", CountryDemo.PercentPsac, CountryDemo.PercentSac, CountryDemo.PercentAdult);
AddValueToRange(XlsWorksheet, XlsRange, "L6", ageRatio);
// Life expectancy
string lifeExpectancy = string.Format("female: {0}, male: {1}", CountryDemo.LifeExpectBirthFemale, CountryDemo.LifeExpectBirthMale);
AddValueToRange(XlsWorksheet, XlsRange, "J7", lifeExpectancy);
// Name of admin level 2 type
if (AdminLevel2Type != null)
AddValueToRange(XlsWorksheet, XlsRange, "M8", AdminLevel2Type.DisplayName);
// Number of admin level 2 types
if (Admin2ndLevels != null)
AddValueToRange(XlsWorksheet, XlsRange, "K8", Admin2ndLevels.Count);
}
private void DetermineStartDate(LeishReportQuestions questions)
{
int year;
if (questions != null && questions.LeishRepYearReporting.HasValue)
{
// Determine the year that was entered
year = questions.LeishRepYearReporting.Value;
}
else
{
// Use the current year
year = DateTime.Now.Year;
}
StartDate = new DateTime(year, 1, 1);
EndDate = new DateTime(year, 1, 1).AddYears(1).AddDays(-1);
}
