private void ExportWorkingStep_Load(object sender, EventArgs e)
{
if (!DesignMode)
{
Localizer.TranslateControl(this);
questions = repo.GetCmExportQuestions();
h3bLabel1.SetMaxWidth(500);
h3Required1.SetMaxWidth(400);
this.saveFileDialog1.DefaultExt = "xlsx";
this.saveFileDialog1.Filter = "Excel (.xlsx)|*.xlsx";
bindingSource1.DataSource = questions;
exportContactBindingSource.DataSource = questions.Contacts;
List<Language> langz = new List<Language>();
langz.Add(new Language { IsoCode = "en-US", Name = "English" });
langz.Add(new Language { IsoCode = "fr-FR", Name = "Français" });
bsLanguages.DataSource = langz;
if (langz.FirstOrDefault(x => x.IsoCode == Thread.CurrentThread.CurrentCulture.Name) != null)
cbLanguages.SelectedValue = Thread.CurrentThread.CurrentCulture.Name;
else
cbLanguages.SelectedValue = "en-US";
var allLevelTypes = settings.GetAllAdminLevels();
var reportingType = allLevelTypes.First();
questions.AdminLevelType = reportingType;
bindingSource2.DataSource = allLevelTypes;
}
}
public void UpdateCmExportQuestions(ExportCmJrfQuestions questions)
{
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
OleDbCommand command = new OleDbCommand(@"Update
ExportCmJrfQuestions
SET
YearReporting=@YearReporting
,CmHaveMasterPlan=@CmHaveMasterPlan
,CmYearsMasterPlan=@CmYearsMasterPlan
,CmBuget=@CmBuget
,CmPercentFunded=@CmPercentFunded
,CmHaveAnnualOpPlan=@CmHaveAnnualOpPlan
,CmDiseaseSpecOrNtdIntegrated=@CmDiseaseSpecOrNtdIntegrated
,CmBuHasPlan=@CmBuHasPlan
,CmGwHasPlan=@CmGwHasPlan
,CmHatHasPlan=@CmHatHasPlan
,CmLeishHasPlan=@CmLeishHasPlan
,CmLeprosyHasPlan=@CmLeprosyHasPlan
,CmYawsHasPlan=@CmYawsHasPlan
,CmAnySupplyFunds=@CmAnySupplyFunds
,CmHasStorage=@CmHasStorage
,CmStorageNtdOrCombined=@CmStorageNtdOrCombined
,CmStorageSponsor1=@CmStorageSponsor1
,CmStorageSponsor2=@CmStorageSponsor2
,CmStorageSponsor3=@CmStorageSponsor3
,CmStorageSponsor4=@CmStorageSponsor4
,CmHasTaskForce=@CmHasTaskForce
,CmHasMoh=@CmHasMoh
,CmHasMosw=@CmHasMosw
,CmHasMot=@CmHasMot
,CmHasMoe=@CmHasMoe
,CmHasMoc=@CmHasMoc
,CmHasUni=@CmHasUni
,CmHasNgo=@CmHasNgo
,CmHasAnnualForum=@CmHasAnnualForum
,CmForumHasRegions=@CmForumHasRegions
,CmForumHasTaskForce=@CmForumHasTaskForce
,CmHasNtdReviewMeetings=@CmHasNtdReviewMeetings
,CmHasDiseaseSpecMeetings=@CmHasDiseaseSpecMeetings
,CmHasGwMeeting=@CmHasGwMeeting
,CmHasLeprosyMeeting=@CmHasLeprosyMeeting
,CmHasHatMeeting=@CmHasHatMeeting
,CmHasLeishMeeting=@CmHasLeishMeeting
,CmHasBuMeeting=@CmHasBuMeeting
,CmHasYawsMeeting=@CmHasYawsMeeting
,CmHasWeeklyMech=@CmHasWeeklyMech
,CmHasMonthlyMech=@CmHasMonthlyMech
,CmHasQuarterlyMech=@CmHasQuarterlyMech
,CmHasSemesterMech=@CmHasSemesterMech
,CmOtherMechs=@CmOtherMechs
,CmBudgetProportion=@CmBudgetProportion
WHERE ID = @id", connection);
command.Parameters.Add(OleDbUtil.CreateNullableParam("@YearReporting", questions.YearReporting));
command.Parameters.Add(new OleDbParameter("@CmHaveMasterPlan", questions.CmHaveMasterPlan));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmYearsMasterPlan", questions.CmYearsMasterPlan));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmBuget", questions.CmBuget));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmPercentFunded", questions.CmPercentFunded));
command.Parameters.Add(new OleDbParameter("@CmHaveAnnualOpPlan", questions.CmHaveAnnualOpPlan));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmDiseaseSpecOrNtdIntegrated", questions.CmDiseaseSpecOrNtdIntegrated));
command.Parameters.Add(new OleDbParameter("@CmBuHasPlan", questions.CmBuHasPlan));
command.Parameters.Add(new OleDbParameter("@CmGwHasPlan", questions.CmGwHasPlan));
command.Parameters.Add(new OleDbParameter("@CmHatHasPlan", questions.CmHatHasPlan));
command.Parameters.Add(new OleDbParameter("@CmLeishHasPlan", questions.CmLeishHasPlan));
command.Parameters.Add(new OleDbParameter("@CmLeprosyHasPlan", questions.CmLeprosyHasPlan));
command.Parameters.Add(new OleDbParameter("@CmYawsHasPlan", questions.CmYawsHasPlan));
command.Parameters.Add(new OleDbParameter("@CmAnySupplyFunds", questions.CmAnySupplyFunds));
command.Parameters.Add(new OleDbParameter("@CmHasStorage", questions.CmHasStorage));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmStorageNtdOrCombined", questions.CmStorageNtdOrCombined));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmStorageSponsor1", questions.CmStorageSponsor1));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmStorageSponsor2", questions.CmStorageSponsor2));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmStorageSponsor3", questions.CmStorageSponsor3));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmStorageSponsor4", questions.CmStorageSponsor4));
command.Parameters.Add(new OleDbParameter("@CmHasTaskForce", questions.CmHasTaskForce));
command.Parameters.Add(new OleDbParameter("@CmHasMoh", questions.CmHasMoh));
command.Parameters.Add(new OleDbParameter("@CmHasMosw", questions.CmHasMosw));
command.Parameters.Add(new OleDbParameter("@CmHasMot", questions.CmHasMot));
command.Parameters.Add(new OleDbParameter("@CmHasMoe", questions.CmHasMoe));
command.Parameters.Add(new OleDbParameter("@CmHasMoc", questions.CmHasMoc));
command.Parameters.Add(new OleDbParameter("@CmHasUni", questions.CmHasUni));
command.Parameters.Add(new OleDbParameter("@CmHasNgo", questions.CmHasNgo));
command.Parameters.Add(new OleDbParameter("@CmHasAnnualForum", questions.CmHasAnnualForum));
command.Parameters.Add(new OleDbParameter("@CmForumHasRegions", questions.CmForumHasRegions));
command.Parameters.Add(new OleDbParameter("@CmForumHasTaskForce", questions.CmForumHasTaskForce));
command.Parameters.Add(new OleDbParameter("@CmHasNtdReviewMeetings", questions.CmHasNtdReviewMeetings));
command.Parameters.Add(new OleDbParameter("@CmHasDiseaseSpecMeetings", questions.CmHasDiseaseSpecMeetings));
command.Parameters.Add(new OleDbParameter("@CmHasGwMeeting", questions.CmHasGwMeeting));
command.Parameters.Add(new OleDbParameter("@CmHasLeprosyMeeting", questions.CmHasLeprosyMeeting));
command.Parameters.Add(new OleDbParameter("@CmHasHatMeeting", questions.CmHasHatMeeting));
command.Parameters.Add(new OleDbParameter("@CmHasLeishMeeting", questions.CmHasLeishMeeting));
command.Parameters.Add(new OleDbParameter("@CmHasBuMeeting", questions.CmHasBuMeeting));
command.Parameters.Add(new OleDbParameter("@CmHasYawsMeeting", questions.CmHasYawsMeeting));
command.Parameters.Add(new OleDbParameter("@CmHasWeeklyMech", questions.CmHasWeeklyMech));
command.Parameters.Add(new OleDbParameter("@CmHasMonthlyMech", questions.CmHasMonthlyMech));
command.Parameters.Add(new OleDbParameter("@CmHasQuarterlyMech", questions.CmHasQuarterlyMech));
command.Parameters.Add(new OleDbParameter("@CmHasSemesterMech", questions.CmHasSemesterMech));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmOtherMechs", questions.CmOtherMechs));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmBudgetProportion", questions.CmBudgetProportion));
command.Parameters.Add(new OleDbParameter("@id", questions.Id));
command.ExecuteNonQuery();
command = new OleDbCommand(@"DELETE From ExportContacts", connection);
command.ExecuteNonQuery();
foreach (var contact in questions.Contacts)
{
command = new OleDbCommand(@"INSERT INTO ExportContacts (CmContactName, CmContactPost, CmContactTele, CmContactEmail) values
(@CmContactName, @CmContactPost, @CmContactTele, @CmContactEmail)", connection);
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmContactName", contact.CmContactName));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmContactPost", contact.CmContactPost));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmContactTele", contact.CmContactTele));
command.Parameters.Add(OleDbUtil.CreateNullableParam("@CmContactEmail", contact.CmContactEmail));
command.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
}
}
public ExportCmJrfQuestions GetCmExportQuestions()
{
ExportCmJrfQuestions questions = new ExportCmJrfQuestions();
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command = new OleDbCommand(@"Select ID,
YearReporting
,CmHaveMasterPlan
,CmYearsMasterPlan
,CmBuget
,CmPercentFunded
,CmHaveAnnualOpPlan
,CmDiseaseSpecOrNtdIntegrated
,CmBuHasPlan
,CmGwHasPlan
,CmHatHasPlan
,CmLeishHasPlan
,CmLeprosyHasPlan
,CmYawsHasPlan
,CmAnySupplyFunds
,CmHasStorage
,CmStorageNtdOrCombined
,CmStorageSponsor1
,CmStorageSponsor2
,CmStorageSponsor3
,CmStorageSponsor4
,CmHasTaskForce
,CmHasMoh
,CmHasMosw
,CmHasMot
,CmHasMoe
,CmHasMoc
,CmHasUni
,CmHasNgo
,CmHasAnnualForum
,CmForumHasRegions
,CmForumHasTaskForce
,CmHasNtdReviewMeetings
,CmHasDiseaseSpecMeetings
,CmHasGwMeeting
,CmHasLeprosyMeeting
,CmHasHatMeeting
,CmHasLeishMeeting
,CmHasBuMeeting
,CmHasYawsMeeting
,CmHasWeeklyMech
,CmHasMonthlyMech
,CmHasQuarterlyMech
,CmHasSemesterMech
,CmOtherMechs
,CmBudgetProportion
FROM ExportCmJrfQuestions
WHERE ID = 1
", connection);
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
questions.Id = reader.GetValueOrDefault<int>("ID");
questions.YearReporting = reader.GetValueOrDefault<Nullable<int>>("YearReporting");
questions.CmHaveMasterPlan = reader.GetValueOrDefault<bool>("CmHaveMasterPlan");
questions.CmYearsMasterPlan = reader.GetValueOrDefault<string>("CmYearsMasterPlan");
questions.CmBuget = reader.GetValueOrDefault<Nullable<int>>("CmBuget");
questions.CmPercentFunded = reader.GetNullableDouble("CmPercentFunded");
questions.CmBudgetProportion = reader.GetNullableDouble("CmBudgetProportion");
questions.CmHaveAnnualOpPlan = reader.GetValueOrDefault<bool>("CmHaveAnnualOpPlan");
questions.CmDiseaseSpecOrNtdIntegrated = reader.GetValueOrDefault<string>("CmDiseaseSpecOrNtdIntegrated");
questions.CmBuHasPlan = reader.GetValueOrDefault<bool>("CmBuHasPlan");
questions.CmGwHasPlan = reader.GetValueOrDefault<bool>("CmGwHasPlan");
questions.CmHatHasPlan = reader.GetValueOrDefault<bool>("CmHatHasPlan");
questions.CmLeishHasPlan = reader.GetValueOrDefault<bool>("CmLeishHasPlan");
questions.CmLeprosyHasPlan = reader.GetValueOrDefault<bool>("CmLeprosyHasPlan");
questions.CmYawsHasPlan = reader.GetValueOrDefault<bool>("CmYawsHasPlan");
questions.CmAnySupplyFunds = reader.GetValueOrDefault<bool>("CmAnySupplyFunds");
questions.CmHasStorage = reader.GetValueOrDefault<bool>("CmHasStorage");
questions.CmStorageNtdOrCombined = reader.GetValueOrDefault<string>("CmStorageNtdOrCombined");
questions.CmStorageSponsor1 = reader.GetValueOrDefault<string>("CmStorageSponsor1");
questions.CmStorageSponsor2 = reader.GetValueOrDefault<string>("CmStorageSponsor2");
questions.CmStorageSponsor3 = reader.GetValueOrDefault<string>("CmStorageSponsor3");
questions.CmStorageSponsor4 = reader.GetValueOrDefault<string>("CmStorageSponsor4");
questions.CmHasTaskForce = reader.GetValueOrDefault<bool>("CmHasTaskForce");
questions.CmHasMoh = reader.GetValueOrDefault<bool>("CmHasMoh");
questions.CmHasMosw = reader.GetValueOrDefault<bool>("CmHasMosw");
questions.CmHasMot = reader.GetValueOrDefault<bool>("CmHasMot");
questions.CmHasMoe = reader.GetValueOrDefault<bool>("CmHasMoe");
questions.CmHasMoc = reader.GetValueOrDefault<bool>("CmHasMoc");
questions.CmHasUni = reader.GetValueOrDefault<bool>("CmHasUni");
questions.CmHasNgo = reader.GetValueOrDefault<bool>("CmHasNgo");
questions.CmHasAnnualForum = reader.GetValueOrDefault<bool>("CmHasAnnualForum");
questions.CmForumHasRegions = reader.GetValueOrDefault<bool>("CmForumHasRegions");
questions.CmForumHasTaskForce = reader.GetValueOrDefault<bool>("CmForumHasTaskForce");
questions.CmHasNtdReviewMeetings = reader.GetValueOrDefault<bool>("CmHasNtdReviewMeetings");
questions.CmHasDiseaseSpecMeetings = reader.GetValueOrDefault<bool>("CmHasDiseaseSpecMeetings");
questions.CmHasGwMeeting = reader.GetValueOrDefault<bool>("CmHasGwMeeting");
questions.CmHasLeprosyMeeting = reader.GetValueOrDefault<bool>("CmHasLeprosyMeeting");
questions.CmHasHatMeeting = reader.GetValueOrDefault<bool>("CmHasHatMeeting");
questions.CmHasLeishMeeting = reader.GetValueOrDefault<bool>("CmHasLeishMeeting");
questions.CmHasBuMeeting = reader.GetValueOrDefault<bool>("CmHasBuMeeting");
questions.CmHasYawsMeeting = reader.GetValueOrDefault<bool>("CmHasYawsMeeting");
questions.CmHasWeeklyMech = reader.GetValueOrDefault<bool>("CmHasWeeklyMech");
questions.CmHasMonthlyMech = reader.GetValueOrDefault<bool>("CmHasMonthlyMech");
questions.CmHasQuarterlyMech = reader.GetValueOrDefault<bool>("CmHasQuarterlyMech");
questions.CmHasSemesterMech = reader.GetValueOrDefault<bool>("CmHasSemesterMech");
questions.CmOtherMechs = reader.GetValueOrDefault<string>("CmOtherMechs");
}
reader.Close();
}
command = new OleDbCommand(@"Select CmContactName, CmContactPost, CmContactTele, CmContactEmail
From ExportContacts", connection);
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
questions.Contacts.Add(new ExportContact
{
CmContactName = reader.GetValueOrDefault<string>("CmContactName"),
CmContactEmail = reader.GetValueOrDefault<string>("CmContactEmail"),
CmContactPost = reader.GetValueOrDefault<string>("CmContactPost"),
CmContactTele = reader.GetValueOrDefault<string>("CmContactTele")
});
}
reader.Close();
}
}
return questions;
}