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;
        }