public IView NewDiseaseDistro(Disease type)
{
if (type.Id < 1)
return null;
if (type.DiseaseType == Translations.CM)
return new DataEntryEdit(new DiseaseDistroCmVm(adminLevel, type.Id, new CalcDistro()));
return new DataEntryEdit(new DiseaseDistroPcVm(adminLevel, type.Id, new CalcDistro()));
}
public void Save(Disease disease, int byUserId)
{
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;
if (disease.Id > 0)
command = new OleDbCommand(@"UPDATE Diseases SET DisplayName=@DisplayName, UserDefinedName=@UserDefinedName, DiseaseType=@DiseaseType, UpdatedById=@UpdatedById,
UpdatedAt=@UpdatedAt WHERE ID = @id", connection);
else
command = new OleDbCommand(@"INSERT INTO diseases (DisplayName, UserDefinedName, DiseaseType, UpdatedById, UpdatedAt, CreatedById, CreatedAt) VALUES
(@DisplayName, @UserDefinedName, @DiseaseType, @UpdatedById, @UpdatedAt, @CreatedById, @CreatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@DisplayName", disease.DisplayName));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@UserDefinedName", disease.UserDefinedName));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@DiseaseType", disease.DiseaseType));
command.Parameters.Add(new OleDbParameter("@UpdatedById", byUserId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
if (disease.Id > 0)
command.Parameters.Add(new OleDbParameter("@id", disease.Id));
else
{
command.Parameters.Add(new OleDbParameter("@CreatedById", byUserId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@CreatedAt", DateTime.Now));
}
command.ExecuteNonQuery();
if (disease.Id <= 0)
{
command = new OleDbCommand(@"SELECT Max(ID) FROM Diseases", connection);
disease.Id = (int)command.ExecuteScalar();
// Insert Disease Distro Year Reported
command = new OleDbCommand(@"INSERT INTO DiseaseDistributionIndicators (DiseaseId, DisplayName, DataTypeId, AggTypeId, IsDisabled, IsEditable, IsRequired, IsDisplayed, IsCalculated, IsMetaData, CanAddValues, SortOrder, UpdatedById, UpdatedAt) VALUES
(@DiseaseId, 'DateReported', 4, 5, No, No, Yes, No, False, False, False, 1, @UpdatedById, @UpdatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@DiseaseId", disease.Id));
command.Parameters.Add(new OleDbParameter("@UpdatedById", byUserId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
command.ExecuteNonQuery();
// Insert Disease Distro Notes
command = new OleDbCommand(@"INSERT INTO DiseaseDistributionIndicators (DiseaseId, DisplayName, DataTypeId, AggTypeId, IsDisabled, IsEditable, IsRequired, IsDisplayed, IsCalculated, IsMetaData, CanAddValues, SortOrder, UpdatedById, UpdatedAt) VALUES
(@DiseaseId, 'Notes', 15, 4, No, No, No, Yes, False, False, False, 100000, @UpdatedById, @UpdatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@DiseaseId", disease.Id));
command.Parameters.Add(new OleDbParameter("@UpdatedById", byUserId));
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;
}
}
}
private void Upload(Disease disease)
{
List<IHaveDynamicIndicatorValues> forms = new List<IHaveDynamicIndicatorValues>();
if (disease.DiseaseType == "CM")
forms = options.DistrosCm.Where(d => d.Disease.Id == disease.Id).Cast<IHaveDynamicIndicatorValues>().ToList();
else
forms = options.DistrosPc.Where(d => d.Disease.Id == disease.Id).Cast<IHaveDynamicIndicatorValues>().ToList();
DistroImporter importer = new DistroImporter();
importer.SetType(disease.Id);
var payload = new WorkerPayload
{
Importer = importer,
Forms = forms,
DiseaseType = disease.DiseaseType
};
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = Translations.ExcelFiles + " (*.xlsx)|*.xlsx";
ofd.DefaultExt = ".xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
OnSwitchStep(new WorkingStep(Translations.ImportingFile));
payload.FileName = ofd.FileName;
BackgroundWorker importerWorker = new BackgroundWorker();
importerWorker.DoWork += importerWorker_DoWork;
importerWorker.RunWorkerCompleted += importerWorker_RunWorkerCompleted;
importerWorker.RunWorkerAsync(payload);
}
}
public void Delete(Disease disease, int byUserId)
{
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
// START TRANS
OleDbCommand command = new OleDbCommand(@"UPDATE Diseases SET IsDeleted=1 WHERE ID = @id", connection);
command.Parameters.Add(new OleDbParameter("@id", disease.Id));
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
}
}
private Disease GetDiseaseFromReader(OleDbDataReader reader)
{
var d = new Disease
{
DisplayNameKey = reader.GetValueOrDefault<string>("DisplayName"),
DisplayName = TranslationLookup.GetValue(reader.GetValueOrDefault<string>("DisplayName"),
reader.GetValueOrDefault<string>("DisplayName")),
UserDefinedName = reader.GetValueOrDefault<string>("UserDefinedName"),
DiseaseType = TranslationLookup.GetValue(reader.GetValueOrDefault<string>("DiseaseType"),
reader.GetValueOrDefault<string>("DiseaseType")),
Id = reader.GetValueOrDefault<int>("ID")
};
if (!string.IsNullOrEmpty(d.UserDefinedName))
d.DisplayName = d.UserDefinedName;
return d;
}
public Disease GetDiseaseById(int id)
{
Disease disease = new Disease();
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
string sql = @"Select Diseases.ID, DisplayName, DiseaseType, UserDefinedName, IsSelected, UpdatedAt,
aspnet_Users.UserName, CreatedAt, created.UserName as CreatedBy
from ((Diseases INNER JOIN aspnet_Users on Diseases.UpdatedById = aspnet_Users.UserId)
INNER JOIN aspnet_users created on Diseases.CreatedById = created.UserId)
where Diseases.ID = @did";
OleDbCommand command = new OleDbCommand(sql, connection);
command.Parameters.Add(new OleDbParameter("@did", id));
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
disease = GetDiseaseFromReader(reader); ;
}
reader.Close();
}
}
return disease;
}
void form_OnSave(Disease obj)
{
selected.Add(obj);
ReloadLists();
}
public ExportResult ExportData(string filePath, int userId, int year, AdminLevelType districtLevel)
{
try
{
int yearReporting = year;
country = demo.GetCountry();
DateTime start = new DateTime(year, 1, 1);
DateTime end = start.AddYears(1).AddDays(-1);
DiseaseRepository repo = new DiseaseRepository();
DiseaseDistroPc dd = repo.Create(DiseaseType.Oncho);
disease = dd.Disease;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlsWorkbook;
Microsoft.Office.Interop.Excel.Workbooks xlsWorkbooks;
Microsoft.Office.Interop.Excel.Worksheet xlsCountry;
Microsoft.Office.Interop.Excel.Sheets xlsWorksheets;
Microsoft.Office.Interop.Excel.Worksheet xlsDemo;
Microsoft.Office.Interop.Excel.Worksheet xls3;
Microsoft.Office.Interop.Excel.Worksheet xls4;
Microsoft.Office.Interop.Excel.Worksheet xls5;
Microsoft.Office.Interop.Excel.Worksheet xls6;
Microsoft.Office.Interop.Excel.Worksheet xls7;
Microsoft.Office.Interop.Excel.Worksheet xls8;
Microsoft.Office.Interop.Excel.Worksheet xls9;
Microsoft.Office.Interop.Excel.Worksheet xls10;
Microsoft.Office.Interop.Excel.Worksheet xls11;
excel.Range rng = null;
object missing = System.Reflection.Missing.Value;
// Open workbook
xlsWorkbooks = xlsApp.Workbooks;
xlsWorkbook = xlsWorkbooks.Add(true);
xlsWorksheets = xlsWorkbook.Worksheets;
CountryDemography countryDemo = demo.GetCountryDemoByYear(yearReporting);
List<AdminLevel> demography = new List<AdminLevel>();
DateTime startDate = new DateTime(yearReporting, 1, 1);
DateTime endDate = startDate.AddYears(1).AddDays(-1);
demo.GetAdminLevelTreeForDemography(districtLevel.LevelNumber, startDate, endDate, ref demography);
demography = demography.Where(d => d.LevelNumber == districtLevel.LevelNumber).ToList();
xlsCountry = (excel.Worksheet)xlsWorkbook.Worksheets[1];
xlsCountry.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("Country"));
AddCountryPage(xlsCountry, rng, country);
xlsDemo = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xlsCountry, missing, missing);
xlsDemo.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("Demography"));
AddDemoPage(xlsDemo, rng, demography, districtLevel);
xls3 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xlsDemo, missing, missing);
xls3.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("DiseaseDistribution"));
AddDdPage(xls3, rng, demography, start, end, 1, dd);
xls4 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xls3, missing, missing);
xls4.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("SurOnchoMapping").Replace(TranslationLookup.GetValue("Oncho") + " ", ""));
Add4(xls4, rng, demography, start, end, 1);
xls5 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xls4, missing, missing);
xls5.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("SurOnchoAssesments").Replace(TranslationLookup.GetValue("Oncho") + " ", ""));
Add5(xls5, rng, demography, start, end, 1);
xls6 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xls5, missing, missing);
xls6.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("IntvIvm"));
Add6(xls6, rng, demography, start, end, 1);
xls7 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xls6, missing, missing);
xls7.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("IntvIvmAlb"));
Add7(xls7, rng, demography, start, end, 1);
xls8 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xls7, missing, missing);
xls8.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("IntvIvmPzq"));
Add8(xls8, rng, demography, start, end, 1);
xls9 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xls8, missing, missing);
xls9.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("IntvIvmPzqAlb"));
Add9(xls9, rng, demography, start, end, 1);
xls10 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xls9, missing, missing);
xls10.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("SAEs"));
Add10(xls10, rng, demography, start, end, 1);
xls11 = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(missing, xls10, missing, missing);
xls11.Name = Util.TruncateStringForExcelSheetName(TranslationLookup.GetValue("PcTraining"));
Add11(xls11, rng, demography, start, end,1);
xlsApp.DisplayAlerts = false;
xlsWorkbook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, missing,
missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
missing, missing, missing);
xlsApp.Visible = true;
Marshal.ReleaseComObject(xlsWorksheets);
Marshal.ReleaseComObject(xlsCountry);
Marshal.ReleaseComObject(xlsDemo);
Marshal.ReleaseComObject(xls3);
Marshal.ReleaseComObject(xls4);
Marshal.ReleaseComObject(xls5);
Marshal.ReleaseComObject(xls6);
Marshal.ReleaseComObject(xls7);
Marshal.ReleaseComObject(xls8);
Marshal.ReleaseComObject(xls9);
Marshal.ReleaseComObject(xls10);
Marshal.ReleaseComObject(xls11);
Marshal.ReleaseComObject(xlsWorkbooks);
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);
}
}