public void Save(AdminLevelType adminLevel, int byUserId)
{
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
OleDbCommand command = null;
if (adminLevel.IsAggregatingLevel)
{
command = new OleDbCommand(@"Update AdminLevelTypes set IsAggregatingLevel=0", connection);
command.ExecuteNonQuery();
}
if (adminLevel.Id > 0)
{
command = new OleDbCommand(@"Update AdminLevelTypes set DisplayName=@name, AdminLevel=@AdminLevel,
IsAggregatingLevel=@IsAggregatingLevel, UpdatedById=@updatedby, UpdatedAt=@updatedat WHERE ID = @id", connection);
command.Parameters.Add(new OleDbParameter("@name", adminLevel.DisplayName));
command.Parameters.Add(new OleDbParameter("@AdminLevel", adminLevel.LevelNumber));
command.Parameters.Add(new OleDbParameter("@IsAggregatingLevel", adminLevel.IsAggregatingLevel));
command.Parameters.Add(new OleDbParameter("@updatedby", byUserId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@updatedat", DateTime.Now));
command.Parameters.Add(new OleDbParameter("@id", adminLevel.Id));
command.ExecuteNonQuery();
}
else
{
// INSERT
command = new OleDbCommand(@"INSERT INTO AdminLevelTypes (DisplayName, AdminLevel, IsAggregatingLevel,
UpdatedById, UpdatedAt, CreatedById, CreatedAt) VALUES
(@DisplayName, @AdminLevel, @IsAggregatingLevel, @UpdatedBy, @UpdatedAt, @CreatedById, @CreatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@DisplayName", adminLevel.DisplayName));
command.Parameters.Add(new OleDbParameter("@AdminLevel", adminLevel.LevelNumber));
command.Parameters.Add(new OleDbParameter("@IsAggregatingLevel", adminLevel.IsAggregatingLevel));
command.Parameters.Add(new OleDbParameter("@UpdatedById", byUserId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
command.Parameters.Add(new OleDbParameter("@CreatedById", byUserId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@CreatedAt", DateTime.Now));
command.ExecuteNonQuery();
command = new OleDbCommand(@"SELECT Max(ID) FROM AdminLevelTypes", connection);
adminLevel.Id = (int)command.ExecuteScalar();
}
}
catch (Exception)
{
throw;
}
}
}
public AdminLevelType GetNextLevel(int levelNumber)
{
AdminLevelType al = null;
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
OleDbCommand command = new OleDbCommand(@"Select ID, DisplayName, AdminLevel, IsAggregatingLevel from AdminLevelTypes
WHERE AdminLevel > 0 AND IsDeleted=0 AND AdminLevel > @id
ORDER BY AdminLevel", connection);
command.Parameters.Add(new OleDbParameter("@id", levelNumber));
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
al = new AdminLevelType
{
Id = reader.GetValueOrDefault<int>("ID"),
DisplayName = reader.GetValueOrDefault<string>("DisplayName"),
LevelNumber = reader.GetValueOrDefault<int>("AdminLevel"),
IsAggregatingLevel = reader.GetValueOrDefault<bool>("IsAggregatingLevel"),
};
}
reader.Close();
}
if (al == null)
return null;
command = new OleDbCommand(@"Select AdminLevel from AdminLevelTypes
WHERE IsAggregatingLevel = @IsAggregatingLevel", connection);
command.Parameters.Add(new OleDbParameter("@IsAggregatingLevel", true));
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
int aggLevel = reader.GetValueOrDefault<int>("AdminLevel");
if (al.LevelNumber >= aggLevel)
al.IsDemographyAllowed = true;
}
reader.Close();
}
}
return al;
}
public void Delete(AdminLevelType adminLevelType, int userId)
{
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
OleDbCommand command = new OleDbCommand(@"Update AdminLevelTypes set IsDeleted=1, UpdatedById=@updatedby,
UpdatedAt=@updatedat WHERE ID = @id", connection);
command.Parameters.Add(new OleDbParameter("@updatedby", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@updatedat", DateTime.Now));
command.Parameters.Add(new OleDbParameter("@id", adminLevelType.Id));
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
}
}
public void AddChildren(AdminLevel parent, List<AdminLevel> children, AdminLevelType childType, 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;
foreach (var child in children)
{
command = new OleDbCommand(@"Insert Into AdminLevels (DisplayName, AdminLevelTypeId,
ParentId, UrbanOrRural, LatWho, LngWho, UpdatedById, UpdatedAt, CreatedById, CreatedAt) VALUES
(@DisplayName, @AdminLevelTypeId, @ParentId, @UrbanOrRural, @LatWho, @LngWho,
@updatedby, @updatedat, @CreatedById, @CreatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@DisplayName", child.Name));
command.Parameters.Add(new OleDbParameter("@AdminLevelTypeId", childType.Id));
command.Parameters.Add(new OleDbParameter("@ParentId", parent.Id));
command.Parameters.Add(new OleDbParameter("@UrbanOrRural", child.UrbanOrRural));
command.Parameters.Add(new OleDbParameter("@LatWho", child.LatWho));
command.Parameters.Add(new OleDbParameter("@LngWho", child.LngWho));
command.Parameters.Add(new OleDbParameter("@updatedby", byUserId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@updatedat", DateTime.Now));
command.Parameters.Add(new OleDbParameter("@CreatedById", byUserId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@CreatedAt", 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 Dictionary<string, int> GetParentIds(AdminLevel filterBy, AdminLevelType parentType)
{
Dictionary<string, int> parentIds = new Dictionary<string, int>();
if (filterBy == null && parentType == null)
return parentIds;
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
OleDbCommand command = new OleDbCommand();
if (filterBy != null)
{
command = new OleDbCommand(@"Select AdminLevels.ID, AdminLevels.DisplayName
FROM AdminLevels WHERE ParentId=@ParentId AND IsDeleted=0 ", connection);
command.Parameters.Add(new OleDbParameter("@ParentId", filterBy.Id));
}
else if (parentType != null)
{
command = new OleDbCommand(@"Select AdminLevels.ID, AdminLevels.DisplayName
FROM AdminLevels WHERE AdminLevelTypeId = @AdminLevelTypeId AND IsDeleted=0 ", connection);
command.Parameters.Add(new OleDbParameter("@AdminLevelTypeId", parentType.Id));
}
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (!parentIds.ContainsKey(reader.GetValueOrDefault<string>("DisplayName").ToLower()))
parentIds.Add(reader.GetValueOrDefault<string>("DisplayName").ToLower(), reader.GetValueOrDefault<int>("ID"));
}
reader.Close();
}
return parentIds;
}
catch (Exception)
{
throw;
}
}
}
public void ApplyGrowthRate(double growthRatePercent, int userId, AdminLevelType aggLevel, DateTime dateReported)
{
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;
var growthRateDemonminator = growthRatePercent / 100;
// Get Agg Level & Below and create new demo
List<AdminLevelDemography> mostRecent = GetRecentDemographyByLevel(aggLevel.LevelNumber, command, connection);
foreach (var d in mostRecent)
{
d.Id = 0;
d.DateDemographyData = dateReported;
d.GrowthRate = growthRatePercent;
if (d.Pop0Month.HasValue) d.Pop0Month = (int)Math.Round((double)d.Pop0Month * growthRateDemonminator) + d.Pop0Month;
if (d.Pop5yo.HasValue) d.Pop5yo = (int)Math.Round((double)d.Pop5yo * growthRateDemonminator) + d.Pop5yo;
if (d.PopAdult.HasValue) d.PopAdult = (int)Math.Round((double)d.PopAdult * growthRateDemonminator) + d.PopAdult;
if (d.PopFemale.HasValue) d.PopFemale = (int)Math.Round((double)d.PopFemale * growthRateDemonminator) + d.PopFemale;
if (d.PopMale.HasValue) d.PopMale = (int)Math.Round((double)d.PopMale * growthRateDemonminator) + d.PopMale;
if (d.PopPsac.HasValue) d.PopPsac = (int)Math.Round((double)d.PopPsac * growthRateDemonminator) + d.PopPsac;
if (d.PopPsac.HasValue) d.PopSac = (int)Math.Round((double)d.PopSac * growthRateDemonminator) + d.PopSac;
if (d.PopPsac.HasValue) d.TotalPopulation = (int)Math.Round((double)d.TotalPopulation * growthRateDemonminator) + d.TotalPopulation;
SaveAdminDemography(command, connection, d, userId);
}
// 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 void AggregateUp(AdminLevelType locationType, DateTime demoDate, int userId, double? growthRate, int? countryDemoId)
{
try
{
var c = GetCountry();
DateTime startDate = new DateTime(demoDate.Year, 1, 1);
DateTime endDate = startDate.AddYears(1).AddDays(-1);
List<AdminLevel> list = new List<AdminLevel>();
var tree = GetAdminLevelTreeForDemography(locationType.LevelNumber, startDate, endDate, ref list);
var country = tree.FirstOrDefault();
CountryDemography countryDemo = null;
if (countryDemoId.HasValue)
countryDemo = GetCountryDemoById(countryDemoId.Value);
else
countryDemo = GetCountryDemoRecent();
if (!growthRate.HasValue)
growthRate = countryDemo.GrowthRate;
country.CurrentDemography = IndicatorAggregator.AggregateTree(country, growthRate);
if (countryDemoId.HasValue)
country.CurrentDemography.Id = countryDemoId.Value;
BulkImportAggregatedDemo(tree, userId, locationType.LevelNumber);
}
catch (Exception)
{
throw;
}
}
private void GetAdmin2ndLvlData()
{
// Get the admin level 2 type
AdminLevel2Type = SettingsRepo.GetAdminLevelTypeByLevel(Admin2LevelNumber);
// Get the Admin Levels (as well as the current demography)
DemoRepo.GetAdminLevelTreeForDemography(Admin2LevelNumber, StartDate, EndDate, ref Admin2ndLevels);
// Filter the 2nd levels out
Admin2ndLevels = Admin2ndLevels.Where(a => a.LevelNumber == Admin2LevelNumber).ToList();
}
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);
}
}
private void AddDemoPage(excel.Worksheet xlsWorksheet, excel.Range rng, List<AdminLevel> demography, AdminLevelType districtType)
{
AddValueToRange(xlsWorksheet, rng, "A1", districtType.DisplayName);
AddValueToRange(xlsWorksheet, rng, "B1", TranslationLookup.GetValue("YearCensus"));
AddValueToRange(xlsWorksheet, rng, "C1", TranslationLookup.GetValue("DateReported"));
AddValueToRange(xlsWorksheet, rng, "D1", TranslationLookup.GetValue("GrowthRate"));
AddValueToRange(xlsWorksheet, rng, "E1", TranslationLookup.GetValue("TotalPopulation"));
AddValueToRange(xlsWorksheet, rng, "F1", TranslationLookup.GetValue("Pop5yo"));
AddValueToRange(xlsWorksheet, rng, "G1", TranslationLookup.GetValue("PopAdult"));
AddValueToRange(xlsWorksheet, rng, "H1", TranslationLookup.GetValue("PopFemale"));
AddValueToRange(xlsWorksheet, rng, "I1", TranslationLookup.GetValue("PopMale"));
AddValueToRange(xlsWorksheet, rng, "J1", TranslationLookup.GetValue("PercentRural"));
AddValueToRange(xlsWorksheet, rng, "K1", TranslationLookup.GetValue("Notes"));
int rowNum = 2;
foreach (var demog in demography)
{
AddValueToRange(xlsWorksheet, rng, "A" + rowNum, demog.Name);
if (demog.CurrentDemography.YearCensus.HasValue)
AddValueToRange(xlsWorksheet, rng, "B" + rowNum, demog.CurrentDemography.YearCensus.Value);
AddValueToRange(xlsWorksheet, rng, "C" + rowNum, demog.CurrentDemography.DateDemographyData.ToShortDateString());
if (demog.CurrentDemography.GrowthRate.HasValue)
AddValueToRange(xlsWorksheet, rng, "D" + rowNum, demog.CurrentDemography.GrowthRate.Value);
if (demog.CurrentDemography.TotalPopulation.HasValue)
AddValueToRange(xlsWorksheet, rng, "E" + rowNum, demog.CurrentDemography.TotalPopulation.Value);
if (demog.CurrentDemography.Pop5yo.HasValue)
AddValueToRange(xlsWorksheet, rng, "F" + rowNum, demog.CurrentDemography.Pop5yo.Value);
if (demog.CurrentDemography.PopAdult.HasValue)
AddValueToRange(xlsWorksheet, rng, "G" + rowNum, demog.CurrentDemography.PopAdult.Value);
if (demog.CurrentDemography.PopFemale.HasValue)
AddValueToRange(xlsWorksheet, rng, "H" + rowNum, demog.CurrentDemography.PopFemale.Value);
if (demog.CurrentDemography.PopMale.HasValue)
AddValueToRange(xlsWorksheet, rng, "I" + rowNum, demog.CurrentDemography.PopMale.Value);
if (demog.CurrentDemography.PercentRural.HasValue)
AddValueToRange(xlsWorksheet, rng, "J" + rowNum, demog.CurrentDemography.PercentRural.Value);
AddValueToRange(xlsWorksheet, rng, "K" + rowNum, demog.CurrentDemography.Notes);
rowNum++;
}
}