private void ExportWorkingStep_Load(object sender, EventArgs e)
{
if (!DesignMode)
{
questions = repo.GetExportQuestions();
if (!questions.JrfYearReporting.HasValue || questions.JrfYearReporting.Value == 0)
questions.JrfYearReporting = DateTime.Now.Year;
Localizer.TranslateControl(this);
h3bLabel1.SetMaxWidth(500);
h3bLabel2.SetMaxWidth(500);
h3bLabel3.SetMaxWidth(500);
h3bLabel4.SetMaxWidth(500);
h3bLabel5.SetMaxWidth(500);
CreateEndemicityDropdown(comboBox1);
CreateEndemicityDropdown(comboBox2);
CreateEndemicityDropdown(comboBox3);
CreateEndemicityDropdown(comboBox4);
bindingSource1.DataSource = questions;
//ClearErrors();
this.saveFileDialog1.DefaultExt = "xlsx";
this.saveFileDialog1.Filter = "Excel (.xlsx)|*.xlsx";
var allLevelTypes = settings.GetAllAdminLevels();
var reportingType = allLevelTypes.First();
questions.AdminLevelType = reportingType;
bindingSource2.DataSource = allLevelTypes;
}
}
public ExportResult ExportData(string filePath, int userId, ExportJrfQuestions questions)
{
try
{
int yearReported = questions.JrfYearReporting.Value;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excel.Application xlsApp = new excel.ApplicationClass();
excel.Workbook xlsWorkbook;
excel.Worksheet xlsWorksheet;
excel.Worksheet xlsSummary;
excel.Range rng = null;
object missing = System.Reflection.Missing.Value;
// Open workbook
xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\WHO_JRF_PC.xls"),
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
var districtLevel = questions.AdminLevelType;
CountryDemography countryStats = demo.GetCountryLevelStatsRecent();
Country country = demo.GetCountry();
List<AdminLevel> demography = new List<AdminLevel>();
DateTime startDate = new DateTime(yearReported, 1, 1);
DateTime endDate = startDate.AddYears(1).AddDays(-1);
List<AdminLevel> tree = demo.GetAdminLevelTreeForDemography(districtLevel.LevelNumber, startDate, endDate, ref demography);
var reportingLevelUnits = demography.Where(d => d.LevelNumber == districtLevel.LevelNumber).ToList();
Dictionary<int, DataRow> aggIntvs = GetIntvsAggregatedToReportingLevel(startDate, endDate, reportingLevelUnits);
// Info page
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[1];
xlsWorksheet.Unprotect();
AddQuestions(xlsWorksheet, rng, questions, countryStats, demography, districtLevel.LevelNumber, country);
// run macro to create district rows.
xlsApp.DisplayAlerts = false;
xlsApp.Run("Sheet1.DISTRICT");
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[2];
xlsWorksheet.Unprotect();
AddDemo(xlsWorksheet, rng, demography, districtLevel.LevelNumber, startDate, endDate);
// Summary
xlsSummary = (excel.Worksheet)xlsWorkbook.Sheets["SUMMARY"];
// ALL THE INTVS
AddT3(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs);
AddT2(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs);
AddT1(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs);
AddMDA4(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs);
AddMDA3(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs);
AddMDA2(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs);
AddMDA1(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs);
AddDistricts(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs);
xlsWorkbook.SaveAs(filePath, excel.XlFileFormat.xlOpenXMLWorkbook, missing,
missing, false, false, excel.XlSaveAsAccessMode.xlNoChange,
excel.XlSaveConflictResolution.xlUserResolution, true,
missing, missing, missing);
xlsApp.ScreenUpdating = true;
xlsApp.Visible = true;
rng = null;
Marshal.ReleaseComObject(xlsWorksheet);
Marshal.ReleaseComObject(xlsWorkbook);
Marshal.ReleaseComObject(xlsApp);
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
return new ExportResult { WasSuccess = true };
}
catch (Exception ex)
{
return new ExportResult(ex.Message);
}
}
public void UpdateExportQuestions(ExportJrfQuestions questions)
{
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
OleDbCommand command = new OleDbCommand(@"Update
ExportJrfQuestions
SET
JrfYearReporting=@JrfYearReporting,
JrfEndemicLf=@JrfEndemicLf,
JrfEndemicOncho=@JrfEndemicOncho,
JrfEndemicSth=@JrfEndemicSth,
JrfEndemicSch=@JrfEndemicSch
WHERE ID = @id", connection);
command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfYearReporting", questions.JrfYearReporting));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfEndemicLf", questions.JrfEndemicLf));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfEndemicOncho", questions.JrfEndemicOncho));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfEndemicSth", questions.JrfEndemicSth));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfEndemicSch", questions.JrfEndemicSch));
command.Parameters.Add(new OleDbParameter("@id", questions.Id));
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
}
}
private void AddQuestions(excel.Worksheet xlsWorksheet, excel.Range rng, ExportJrfQuestions questions, CountryDemography countryDemo,
List<AdminLevel> demography, int districtLevel, Country country)
{
AddValueToRange(xlsWorksheet, rng, "E34", country.Name);
AddValueToRange(xlsWorksheet, rng, "E36", questions.JrfYearReporting.Value);
AddValueToRange(xlsWorksheet, rng, "E38", TranslationLookup.GetValue(questions.JrfEndemicLf, questions.JrfEndemicLf));
AddValueToRange(xlsWorksheet, rng, "E40", TranslationLookup.GetValue(questions.JrfEndemicOncho, questions.JrfEndemicOncho));
AddValueToRange(xlsWorksheet, rng, "E42", TranslationLookup.GetValue(questions.JrfEndemicSth, questions.JrfEndemicSth));
AddValueToRange(xlsWorksheet, rng, "E44", TranslationLookup.GetValue(questions.JrfEndemicSch, questions.JrfEndemicSch));
AddValueToRange(xlsWorksheet, rng, "E46", demography.Where(d => d.LevelNumber == districtLevel).Count());
if (countryDemo.PercentPsac.HasValue)
AddValueToRange(xlsWorksheet, rng, "E48", countryDemo.PercentPsac.Value / 100);
else
AddValueToRange(xlsWorksheet, rng, "E48", 0);
if (countryDemo.PercentSac.HasValue)
AddValueToRange(xlsWorksheet, rng, "E49", countryDemo.PercentSac.Value / 100);
else
AddValueToRange(xlsWorksheet, rng, "E49", 0);
if (countryDemo.PercentAdult.HasValue)
AddValueToRange(xlsWorksheet, rng, "E50", countryDemo.PercentAdult.Value / 100);
else
AddValueToRange(xlsWorksheet, rng, "E50", 0);
}
public ExportJrfQuestions GetExportQuestions()
{
ExportJrfQuestions questions = new ExportJrfQuestions();
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command = new OleDbCommand(@"Select ID,
JrfYearReporting,
JrfEndemicLf,
JrfEndemicOncho,
JrfEndemicSth,
JrfEndemicSch
FROM ExportJrfQuestions
WHERE ID = 1
", connection);
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
questions.Id = reader.GetValueOrDefault<int>("ID");
questions.JrfYearReporting = reader.GetValueOrDefault<int>("JrfYearReporting");
questions.JrfEndemicLf = reader.GetValueOrDefault<string>("JrfEndemicLf");
questions.JrfEndemicOncho = reader.GetValueOrDefault<string>("JrfEndemicOncho");
questions.JrfEndemicSth = reader.GetValueOrDefault<string>("JrfEndemicSth");
questions.JrfEndemicSch = reader.GetValueOrDefault<string>("JrfEndemicSch");
}
reader.Close();
}
}
return questions;
}
