public ExportResult DoExport(string fileName, int userId, ExportType exportType)
{
try
{
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();
xlsApp.DisplayAlerts = false;
excel.Workbook xlsWorkbook;
excel.Worksheet xlsWorksheet;
excel.Range rng = null;
object missing = System.Reflection.Missing.Value;
xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\WHO_EPIRF_PC_NATDAT.xls"),
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
Country country = demo.GetCountry();
int reportYear = DateTime.Now.Year;
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[1];
AddInfo(xlsWorksheet, rng, country, exportType, ref reportYear);
//xlsApp.Visible = true; //set to 'true' when debbugging, Exec is visible
//xlsApp.DisplayAlerts = true; //enable all the prompt alerts for debug.
DateTime start = new DateTime(reportYear, 1, 1);
DateTime end = new DateTime(reportYear, 12, 31);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[2];
AddLfMm(xlsWorksheet, start, end);
xlsApp.Run("Sheet13.UNIT_LF");
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[3];
AddOncho(xlsWorksheet, start, end);
xlsApp.Run("Sheet17.UNIT_ONCHO");
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[4];
AddSth(xlsWorksheet, start, end);
xlsApp.Run("Sheet15.UNIT_STH");
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[5];
AddSch(xlsWorksheet, start, end);
xlsApp.Run("Sheet16.UNIT_SCH");
xlsWorkbook.SaveAs(fileName, 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 override string GetYear(ExportType exportType)
{
var ind = exportType.IndicatorValues.FirstOrDefault(i => i.Indicator.DisplayName == "Year");
if (ind != null)
return ind.DynamicValue;
return "";
}
public GenericExportStep(ExportType t, string steptitle)
: base()
{
title = steptitle;
InitializeComponent();
exportType = t;
}
/// <summary>
/// Adds the info to the main sheet
/// </summary>
/// <param name="xlsWorksheet"></param>
/// <param name="rng"></param>
/// <param name="country"></param>
/// <param name="exportType"></param>
/// <param name="year"></param>
private void AddInfo(excel.Worksheet xlsWorksheet, excel.Range rng, Country country, ExportType exportType, ref int year)
{
AddValueToRange(xlsWorksheet, rng, "E32", country.Name);
foreach (var val in exportType.IndicatorValues)
{
if (val.Indicator.DisplayName == "Year")
{
year = Convert.ToInt32(val.DynamicValue);
AddValueToRange(xlsWorksheet, rng, "E34", val.DynamicValue);
}
if (val.Indicator.DisplayName == "JrfEndemicLf")
AddValueToRange(xlsWorksheet, rng, "E36", TranslationLookup.GetValue(val.DynamicValue, val.DynamicValue));
if (val.Indicator.DisplayName == "JrfEndemicOncho")
AddValueToRange(xlsWorksheet, rng, "E38", TranslationLookup.GetValue(val.DynamicValue, val.DynamicValue));
if (val.Indicator.DisplayName == "JrfEndemicSth")
AddValueToRange(xlsWorksheet, rng, "E40", TranslationLookup.GetValue(val.DynamicValue, val.DynamicValue));
if (val.Indicator.DisplayName == "JrfEndemicSch")
AddValueToRange(xlsWorksheet, rng, "E42", TranslationLookup.GetValue(val.DynamicValue, val.DynamicValue));
}
}
public virtual string GetYear(ExportType exportType)
{
return "";
}
public ExportResult DoExport(string fileName, int userId, ExportType exportType)
{
throw new NotImplementedException();
}
public GenericExportStep(ExportType t)
: base()
{
InitializeComponent();
exportType = t;
}
public ExportResult DoExport(string fileName, int userId, ExportType exportType)
{
try
{
transLookup = new TranslationLookupInstance(ExportCulture);
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();
xlsApp.DisplayAlerts = false;
excel.Workbook xlsWorkbook;
excel.Worksheet xlsWorksheet;
excel.Range rng = null;
object missing = System.Reflection.Missing.Value;
xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\" + transLookup.GetValue("RtiWorkbookLocation", "RtiWorkbookLocation")),
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
xlsWorkbook.Unprotect("NTDM&E101");
Country country = demo.GetCountry();
var cAdminDemo = demo.GetRecentDemography(1, StartDate, EndDate);
var countryDemo = demo.GetCountryDemoById(cAdminDemo.Id);
List<AdminLevel> reportingLevelUnits = demo.GetAdminUnitsWithParentsAndChildren(AdminLevelType.LevelNumber, StartDate, EndDate);
reportingLevelUnits = reportingLevelUnits.Where(d => d.LevelNumber == AdminLevelType.LevelNumber).OrderBy(a => a.Name).ToList();
var intvs = intvRepo.GetAll(new List<int> { 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 }, StartDate, EndDate);
Dictionary<int, DataRow> aggIntvs = GetIntvsAggregatedToReportingLevel(StartDate, EndDate, reportingLevelUnits);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabCountry", "RtiTabCountry")];
xlsWorksheet.Unprotect("NTDM&E101");
AddInfo(xlsWorksheet, rng, country, exportType, reportingLevelUnits.Count, intvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabDemo", "RtiTabDemo")];
xlsWorksheet.Unprotect("NTDM&E101");
AddDemo(xlsWorksheet, rng, reportingLevelUnits, countryDemo);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabLf", "RtiTabLf")];
xlsWorksheet.Unprotect("NTDM&E101");
AddLf(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabOncho", "RtiTabOncho")];
xlsWorksheet.Unprotect("NTDM&E101");
AddOncho(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabSch", "RtiTabSch")];
xlsWorksheet.Unprotect("NTDM&E101");
AddSchisto(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabSth", "RtiTabSth")];
xlsWorksheet.Unprotect("NTDM&E101");
AddSth(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabTra", "RtiTabTra")];
xlsWorksheet.Unprotect("NTDM&E101");
AddTrachoma(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorkbook.SaveAs(fileName, 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 override string GetYear(ExportType exportType)
{
return StartDate.Year.ToString() + "-" + EndDate.Year.ToString();
}
/// <summary>
/// Adds general information
/// </summary>
/// <param name="xlsWorksheet"></param>
/// <param name="rng"></param>
/// <param name="country"></param>
/// <param name="exportType"></param>
/// <param name="districtCount"></param>
/// <param name="intvs"></param>
private void AddInfo(excel.Worksheet xlsWorksheet, excel.Range rng, Country country, ExportType exportType, int districtCount,
List<IntvDetails> intvs)
{
AddValueToRange(xlsWorksheet, rng, "C7", country.Name);
AddValueToRange(xlsWorksheet, rng, "C10", DateTime.Now.ToString("dd-MMM-yyyy"));
var i = intvs.FirstOrDefault();
if (i != null)
{
AddValueToRange(xlsWorksheet, rng, "C12", i.StartDate.ToString("dd-MMM-yyyy"));
var endInd = intvRepo.GetById(i.Id).IndicatorValues.FirstOrDefault(v => v.Indicator.DisplayName == "PcIntvEndDateOfMda");
if (endInd != null && !string.IsNullOrEmpty(endInd.DynamicValue))
{
DateTime endDate = DateTime.ParseExact(endInd.DynamicValue, "MM/dd/yyyy", CultureInfo.InvariantCulture);
AddValueToRange(xlsWorksheet, rng, "C13", endDate.ToString("dd-MMM-yyyy"));
}
}
AddValueToRange(xlsWorksheet, rng, "C15", districtCount);
AddValueToRange(xlsWorksheet, rng, "C19", StartDate.ToString("dd-MMM-yyyy - ") + EndDate.ToString("dd-MMM-yyyy"));
foreach (var val in exportType.IndicatorValues)
{
if (val.Indicator.DisplayName == "RtiYearOfWorkbook")
AddValueToRange(xlsWorksheet, rng, "C11", transLookup.GetValue(val.DynamicValue, val.DynamicValue));
if (val.Indicator.DisplayName == "RtiName")
AddValueToRange(xlsWorksheet, rng, "C5", val.DynamicValue);
if (val.Indicator.DisplayName == "RtiTitle")
AddValueToRange(xlsWorksheet, rng, "C6", val.DynamicValue);
if (val.Indicator.DisplayName == "RtiProjectName")
AddValueToRange(xlsWorksheet, rng, "C8", transLookup.GetValue(val.DynamicValue, val.DynamicValue));
if (val.Indicator.DisplayName == "RtiSubPartnerName")
AddValueToRange(xlsWorksheet, rng, "C9", val.DynamicValue);
if (val.Indicator.DisplayName == "RtiReportingPeriod")
AddValueToRange(xlsWorksheet, rng, "C14", transLookup.GetValue(val.DynamicValue, val.DynamicValue));
if (val.Indicator.DisplayName == "RtiTotalDistrictsTreatedWithUsaid")
AddValueToRange(xlsWorksheet, rng, "C16", val.DynamicValue);
if (val.Indicator.DisplayName == "RtiTotalDistrictsComplete")
AddValueToRange(xlsWorksheet, rng, "C17", val.DynamicValue);
if (val.Indicator.DisplayName == "RtiDataCompleteness")
AddValueToRange(xlsWorksheet, rng, "C18", val.DynamicValue);
}
}
public void Save(ExportType export, int userId)
{
bool transWasStarted = false;
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
// START TRANS
OleDbCommand command = new OleDbCommand("BEGIN TRANSACTION", connection);
command.ExecuteNonQuery();
transWasStarted = true;
command = new OleDbCommand(@"DELETE FROM ExportIndicatorValues WHERE ExportTypeId=@ExportTypeId", connection);
command.Parameters.Add(new OleDbParameter("@ExportTypeId", export.Id));
command.ExecuteNonQuery();
foreach (IndicatorValue val in export.IndicatorValues)
{
command = new OleDbCommand(@"Insert Into ExportIndicatorValues (IndicatorId, ExportTypeId, DynamicValue, UpdatedById, UpdatedAt) VALUES
(@IndicatorId, @ProcessId, @DynamicValue, @UpdatedById, @UpdatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@IndicatorId", val.IndicatorId));
command.Parameters.Add(new OleDbParameter("@ExportTypeId", export.Id));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@DynamicValue", val.DynamicValue));
command.Parameters.Add(new OleDbParameter("@UpdatedById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
command.ExecuteNonQuery();
}
// COMMIT TRANS
command = new OleDbCommand("COMMIT TRANSACTION", connection);
command.ExecuteNonQuery();
transWasStarted = false;
}
catch (Exception)
{
if (transWasStarted)
{
try
{
OleDbCommand cmd = new OleDbCommand("ROLLBACK TRANSACTION", connection);
cmd.ExecuteNonQuery();
}
catch { }
}
throw;
}
}
}
public ExportType GetExportType(ExportTypeId id)
{
ExportType export = new ExportType { Id = (int)id };
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
List<string> indicatorIds = new List<string>();
OleDbCommand command = new OleDbCommand(@"Select
ExportIndicators.ID,
ExportIndicators.DataTypeId,
ExportIndicators.DisplayName,
ExportIndicators.IsRequired,
ExportIndicators.UpdatedAt,
aspnet_users.UserName,
IndicatorDataTypes.DataType
FROM ((ExportIndicators INNER JOIN aspnet_users ON ExportIndicators.UpdatedById = aspnet_users.UserId)
INNER JOIN IndicatorDataTypes ON ExportIndicators.DataTypeId = IndicatorDataTypes.ID)
WHERE ExportTypeId=@ExportTypeId
ORDER BY SortOrder, ExportIndicators.ID", connection);
command.Parameters.Add(new OleDbParameter("@ExportTypeId", id));
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
export.Indicators.Add(reader.GetValueOrDefault<string>("DisplayName"),
new Indicator
{
Id = reader.GetValueOrDefault<int>("ID"),
DataTypeId = reader.GetValueOrDefault<int>("DataTypeId"),
UpdatedBy = reader.GetValueOrDefault<DateTime>("UpdatedAt").ToShortDateString() + " by " +
reader.GetValueOrDefault<string>("UserName"),
DisplayName = reader.GetValueOrDefault<string>("DisplayName"),
IsRequired = reader.GetValueOrDefault<bool>("IsRequired"),
DataType = reader.GetValueOrDefault<string>("DataType")
});
indicatorIds.Add(reader.GetValueOrDefault<int>("ID").ToString());
}
reader.Close();
}
command = new OleDbCommand(@"Select
ExportIndicatorValues.ID,
ExportIndicatorValues.IndicatorId,
ExportIndicatorValues.DynamicValue,
ExportIndicators.DisplayName
FROM ExportIndicatorValues INNER JOIN ExportIndicators on ExportIndicatorValues.IndicatorId = ExportIndicators.ID
WHERE ExportIndicatorValues.ExportTypeId = @ExportTypeId", connection);
command.Parameters.Add(new OleDbParameter("@ExportTypeId", export.Id));
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (!export.Indicators.ContainsKey(reader.GetValueOrDefault<string>("DisplayName")))
continue;
export.IndicatorValues.Add(new IndicatorValue
{
Id = reader.GetValueOrDefault<int>("ID"),
IndicatorId = reader.GetValueOrDefault<int>("IndicatorId"),
DynamicValue = reader.GetValueOrDefault<string>("DynamicValue"),
Indicator = export.Indicators[reader.GetValueOrDefault<string>("DisplayName")]
});
}
reader.Close();
}
export.IndicatorDropdownValues = GetIndicatorDropdownValues(connection, command, IndicatorEntityType.Export, indicatorIds);
}
catch (Exception)
{
throw;
}
}
return export;
}
