public CountryDemography GetCountryDemoByYear(int year)
{
CountryDemography demo = new CountryDemography();
demo.AdminLevelId = Convert.ToInt32(ConfigurationManager.AppSettings["CountryId"]);
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
int demoId = 0;
OleDbCommand command = new OleDbCommand(@"Select ID FROM AdminLevelDemography WHERE AdminLevelId=@id "
+ CreateDemoYearRange(year) + @" ORDER BY DateDemographyData DESC ", connection);
command.Parameters.Add(new OleDbParameter("@id", demo.AdminLevelId));
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
demoId = reader.GetValueOrDefault<int>("ID");
}
reader.Close();
}
if (demoId == 0)
return demo;
demo = GetCountryDemoById(command, connection, demoId);
}
catch (Exception)
{
throw;
}
}
return demo;
}
public void Save(CountryDemography demo, 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;
SaveCountryDemo(demo, byUserId, connection, command);
// 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 SaveCountryDemo(CountryDemography demo, int byUserId, OleDbConnection connection, OleDbCommand command)
{
SaveAdminDemography(command, connection, demo, byUserId);
command = new OleDbCommand(@"Delete from CountryDemography WHERE AdminLevelDemographyId=@id", connection);
command.Parameters.Add(new OleDbParameter("@id", demo.Id));
command.ExecuteNonQuery();
command = new OleDbCommand(@"INSERT INTO CountryDemography (AdminLevelDemographyId, AgeRangePsac,
AgeRangeSac, Percent6mos, PercentPsac, PercentSac, Percent5yo, PercentFemale,PercentMale, PercentAdult,
GrossDomesticProduct, CountryIncomeStatus, LifeExpectBirthFemale, LifeExpectBirthMale)
values (@AdminLevelDemographyId, @AgeRangePsac,
@AgeRangeSac, @Percent6mos, @PercentPsac, @PercentSac, @Percent5yo, @PercentFemale, @PercentMale,
@PercentAdult, @GrossDomesticProduct, @CountryIncomeStatus, @LifeExpectBirthFemale,
@LifeExpectBirthMale)", connection);
command.Parameters.Add(new OleDbParameter("@AdminLevelDemographyId", demo.Id));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@AgeRangePsac", demo.AgeRangePsac));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@AgeRangeSac", demo.AgeRangeSac));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@Percent6mos", demo.Percent6mos));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@PercentPsac", demo.PercentPsac));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@PercentSac", demo.PercentSac));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@Percent5yo", demo.Percent5yo));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@PercentFemale", demo.PercentFemale));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@PercentMale", demo.PercentMale));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@PercentAdult", demo.PercentAdult));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@GrossDomesticProduct", demo.GrossDomesticProduct));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CountryIncomeStatus", demo.CountryIncomeStatus));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@LifeExpectBirthFemale", demo.LifeExpectBirthFemale));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@LifeExpectBirthMale", demo.LifeExpectBirthMale));
command.ExecuteNonQuery();
}
private CountryDemography GetCountryDemoById(OleDbCommand command, OleDbConnection connection, int demoId)
{
CountryDemography demo = new CountryDemography();
// Get existing
GetDemoById(demo, demoId, connection, command);
command = new OleDbCommand(@"Select AgeRangePsac, AgeRangeSac, Percent6mos, PercentPsac, PercentSac, Percent5yo,
PercentFemale, PercentMale, PercentAdult, GrossDomesticProduct, CountryIncomeStatus,
LifeExpectBirthFemale, LifeExpectBirthMale
FROM CountryDemography WHERE AdminLevelDemographyId=@AdminLevelDemographyId", connection);
command.Parameters.Add(new OleDbParameter("@AdminLevelDemographyId", demo.Id));
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
demo.AgeRangePsac = reader.GetValueOrDefault<string>("AgeRangePsac");
demo.AgeRangeSac = reader.GetValueOrDefault<string>("AgeRangeSac");
demo.Percent6mos = reader.GetValueOrDefault<Nullable<double>>("Percent6mos");
demo.PercentPsac = reader.GetValueOrDefault<Nullable<double>>("PercentPsac");
demo.PercentSac = reader.GetValueOrDefault<Nullable<double>>("PercentSac");
demo.Percent5yo = reader.GetValueOrDefault<Nullable<double>>("Percent5yo");
demo.PercentFemale = reader.GetValueOrDefault<Nullable<double>>("PercentFemale");
demo.PercentMale = reader.GetValueOrDefault<Nullable<double>>("PercentMale");
demo.PercentAdult = reader.GetValueOrDefault<Nullable<double>>("PercentAdult");
demo.GrossDomesticProduct = reader.GetValueOrDefault<Nullable<double>>("GrossDomesticProduct");
demo.CountryIncomeStatus = reader.GetValueOrDefault<string>("CountryIncomeStatus");
demo.LifeExpectBirthFemale = reader.GetValueOrDefault<Nullable<double>>("LifeExpectBirthFemale");
demo.LifeExpectBirthMale = reader.GetValueOrDefault<Nullable<double>>("LifeExpectBirthMale");
}
reader.Close();
}
return demo;
}
public CountryDemography GetCountryLevelStatsRecent()
{
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
CountryDemography demo = new CountryDemography();
OleDbCommand command = new OleDbCommand(@"Select AdminLevelDemographyId
FROM CountryDemography
ORDER BY AdminLevelDemographyId DESC", connection);
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
int id = reader.GetValueOrDefault<int>("AdminLevelDemographyId");
demo = GetCountryDemoById(command, connection, id);
}
reader.Close();
}
// Make sure the Country level is set
if (demo.AdminLevelId <= 0)
demo.AdminLevelId = (int)CommonAdminLevelTypesKeys.Country;
return demo;
}
}
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);
}
private void GetCountryData()
{
CountryData = DemoRepo.GetCountry();
CountryDemo = DemoRepo.GetCountryLevelStatsRecent();
}
/// <summary>
/// Adds demography information
/// </summary>
/// <param name="xlsWorksheet"></param>
/// <param name="rng"></param>
/// <param name="demography"></param>
/// <param name="countryDemo"></param>
private void AddDemo(excel.Worksheet xlsWorksheet, excel.Range rng, List<AdminLevel> demography, CountryDemography countryDemo)
{
if (countryDemo.YearCensus.HasValue)
AddValueToRange(xlsWorksheet, rng, "D4", countryDemo.YearCensus.Value);
if (countryDemo.GrowthRate.HasValue)
AddValueToRange(xlsWorksheet, rng, "D6", countryDemo.GrowthRate.Value / 100);
if (countryDemo.PercentFemale.HasValue)
AddValueToRange(xlsWorksheet, rng, "D7", countryDemo.PercentFemale.Value / 100);
AddValueToRange(xlsWorksheet, rng, "D8", countryDemo.AgeRangePsac);
if (countryDemo.PercentPsac.HasValue)
AddValueToRange(xlsWorksheet, rng, "D9", countryDemo.PercentPsac.Value / 100);
AddValueToRange(xlsWorksheet, rng, "D10", countryDemo.AgeRangeSac);
if (countryDemo.PercentSac.HasValue)
AddValueToRange(xlsWorksheet, rng, "D11", countryDemo.PercentSac.Value / 100);
int rowNum = 17;
foreach (var demog in demography)
{
AddValueToRange(xlsWorksheet, rng, "C" + rowNum, demog.TaskForceName);
AddValueToRange(xlsWorksheet, rng, "B" + rowNum, demog.Parent.TaskForceName);
if (demog.CurrentDemography.TotalPopulation.HasValue)
AddValueToRange(xlsWorksheet, rng, "D" + rowNum, demog.CurrentDemography.TotalPopulation.Value);
if (demog.CurrentDemography.PopPsac.HasValue)
AddValueToRange(xlsWorksheet, rng, "E" + rowNum, demog.CurrentDemography.PopPsac.Value);
if (demog.CurrentDemography.PopSac.HasValue)
AddValueToRange(xlsWorksheet, rng, "F" + rowNum, demog.CurrentDemography.PopSac.Value);
if (demog.CurrentDemography.PopFemale.HasValue)
AddValueToRange(xlsWorksheet, rng, "H" + rowNum, demog.CurrentDemography.PopFemale.Value);
if (demog.CurrentDemography.PopMale.HasValue)
AddValueToRange(xlsWorksheet, rng, "G" + rowNum, demog.CurrentDemography.PopMale.Value);
AddValueToRange(xlsWorksheet, rng, "K" + rowNum, demog.CurrentDemography.Notes);
rowNum++;
}
}
