/// <summary>
        /// Save
        /// </summary>
        /// <param name="countryId">countryId</param>
        /// <param name="provinceId">provinceId</param>
        /// <param name="countyId">countyId</param>
        /// <param name="cityId">cityId</param>
        /// <param name="projectId">projectId</param>
        /// <param name="assetId">assetId</param>
        /// <param name="companyId">companyId</param>
        /// <param name="inProject">inProject</param>
        /// <returns>WorkID</returns>
        public int Save(Int64? countryId, Int64? provinceId, Int64? countyId, Int64? cityId, int projectId, int assetId, int companyId, bool inProject)
        {
            ManholeRehabilitationTDS manholeRehabilitationChanges = (ManholeRehabilitationTDS)Data.GetChanges();
            int workId = 0;

            if (manholeRehabilitationChanges.WorkDetails.Rows.Count > 0)
            {
                ManholeRehabilitationWorkDetailsGateway manholeRehabilitationWorkDetailsGateway = new ManholeRehabilitationWorkDetailsGateway(manholeRehabilitationChanges);

                // Update manhole rehabilitation
                foreach (ManholeRehabilitationTDS.WorkDetailsRow row in (ManholeRehabilitationTDS.WorkDetailsDataTable)manholeRehabilitationChanges.WorkDetails)
                {
                    // Unchanged values
                    workId = row.WorkID;

                    WorkManholeRehabilitationGateway workManholeRehabilitationGateway = new WorkManholeRehabilitationGateway();
                    workManholeRehabilitationGateway.LoadByWorkId(workId, companyId);

                    if (workManholeRehabilitationGateway.Table.Rows.Count > 0)
                    {
                        // Update Information
                        // Original values
                        // ... work values
                        DateTime? originalPreppedDate = manholeRehabilitationWorkDetailsGateway.GetPreppedDateOriginal(workId);
                        DateTime? originalSprayedDate = manholeRehabilitationWorkDetailsGateway.GetSprayedDateOriginal(workId);

                        // ... Comments
                        string originalComments = manholeRehabilitationWorkDetailsGateway.GetCommentsOriginal(workId);

                        // New variables
                        DateTime? newPreppedDate = manholeRehabilitationWorkDetailsGateway.GetPreppedDate(workId);
                        DateTime? newSprayedDate = manholeRehabilitationWorkDetailsGateway.GetSprayedDate(workId);

                        // comments
                        string newComments = manholeRehabilitationWorkDetailsGateway.GetComments(workId);

                        // Update work
                        UpdateWork(countryId, provinceId, countyId, cityId, projectId, assetId, originalPreppedDate, originalSprayedDate, row.BatchID, originalComments, false, companyId, newPreppedDate, newSprayedDate, row.BatchID, newComments, false);
                    }
                    else
                    {
                        // Loading workId
                        WorkGateway workGateway = new WorkGateway();
                        workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Manhole Rehabilitation", companyId);

                        if (workGateway.Table.Rows.Count == 0)
                        {
                            // New variables
                            DateTime? newPreppedDate = manholeRehabilitationWorkDetailsGateway.GetPreppedDate(workId);
                            DateTime? newSprayedDate = manholeRehabilitationWorkDetailsGateway.GetSprayedDate(workId);
                            int? newBatchId = manholeRehabilitationWorkDetailsGateway.GetBatchID(workId);
                            string newComments = manholeRehabilitationWorkDetailsGateway.GetComments(workId);

                            // InsertWork
                            Work work = new Work();

                            int? libraryCategories = null;
                            string history = "";

                            int newWorkId = work.InsertDirect(projectId, assetId, "Manhole Rehabilitation", libraryCategories, false, companyId, newComments, history);

                            // ... Insert manhole rehabilitation work
                            WorkManholeRehabilitation workManholeRehabilitation = new WorkManholeRehabilitation();
                            workManholeRehabilitation.InsertDirect(newWorkId, newPreppedDate, newSprayedDate, newBatchId, false, companyId);

                            workId = newWorkId;
                        }
                    }
                }
            }

            return workId;
        }
        private void LoadWorkData(int workId, int assetId)
        {
            int companyId = Int32.Parse(hdfCompanyId.Value);

            if (workId != 0)
            {
                ManholeRehabilitationWorkDetailsGateway manholeRehabilitationWorkDetailsGateway = new ManholeRehabilitationWorkDetailsGateway(manholeRehabilitationTDS);
                if (manholeRehabilitationWorkDetailsGateway.Table.Rows.Count > 0)
                {
                    tbxRehabilitationPreppedDate.Text = "";
                    if (manholeRehabilitationWorkDetailsGateway.GetPreppedDate(workId).HasValue)
                    {
                        DateTime preppedDate = (DateTime)manholeRehabilitationWorkDetailsGateway.GetPreppedDate(workId);
                        tbxRehabilitationPreppedDate.Text = preppedDate.Month.ToString() + "/" + preppedDate.Day.ToString() + "/" + preppedDate.Year.ToString();
                    }

                    tbxRehabilitationSprayedDate.Text = "";
                    if (manholeRehabilitationWorkDetailsGateway.GetSprayedDate(workId).HasValue)
                    {
                        DateTime sprayedDate = (DateTime)manholeRehabilitationWorkDetailsGateway.GetSprayedDate(workId);
                        tbxRehabilitationSprayedDate.Text = sprayedDate.Month.ToString() + "/" + sprayedDate.Day.ToString() + "/" + sprayedDate.Year.ToString();
                    }

                    tbxRehabilitationBatchDate.Text = "";
                    if (manholeRehabilitationWorkDetailsGateway.GetDate(workId).HasValue)
                    {
                        try
                        {
                            DateTime batchDate = (DateTime)manholeRehabilitationWorkDetailsGateway.GetDate(workId);
                            tbxRehabilitationBatchDate.Text = batchDate.Month.ToString() + "/" + batchDate.Day.ToString() + "/" + batchDate.Year.ToString();
                            hdfBatchId.Value = manholeRehabilitationWorkDetailsGateway.GetBatchID(workId).ToString();
                        }
                        catch
                        {
                        }
                    }

                    // Show Comments
                    tbxCommentsDataComments.Text = manholeRehabilitationWorkDetailsGateway.GetComments(workId);

                    // ... ... Store datasets
                    Session["manholeRehabilitationTDS"] = manholeRehabilitationTDS;
                }
            }
            else
            {
                // Load last batch
                MrBatchVerificationGateway mrBatchVerificationGateway = new MrBatchVerificationGateway();
                mrBatchVerificationGateway.LoadAll(companyId);
                if (mrBatchVerificationGateway.Table.Rows.Count > 0)
                {
                    WorkManholeRehabilitationBatchGateway workManholeRehabilitationBatchGateway = new WorkManholeRehabilitationBatchGateway();
                    hdfBatchId.Value = workManholeRehabilitationBatchGateway.GetLastId(companyId).ToString();
                    int batchId = Int32.Parse(hdfBatchId.Value);

                    mrBatchVerificationGateway.LoadByBatchId(batchId, companyId);
                    DateTime batchDate = mrBatchVerificationGateway.GetDate(batchId);
                    tbxRehabilitationBatchDate.Text = batchDate.Month.ToString() + "/" + batchDate.Day.ToString() + "/" + batchDate.Year.ToString();

                    // ... ... Store datasets
                    Session["manholeRehabilitationTDS"] = manholeRehabilitationTDS;
                }
            }
        }
        private void LoadWorkData(int workId, int assetId)
        {
            int companyId = Int32.Parse(hdfCompanyId.Value);

            if (workId != 0)
            {
                ManholeRehabilitationWorkDetailsGateway manholeRehabilitationWorkDetailsGateway = new ManholeRehabilitationWorkDetailsGateway(manholeRehabilitationTDS);
                if (manholeRehabilitationWorkDetailsGateway.Table.Rows.Count > 0)
                {
                    if (manholeRehabilitationWorkDetailsGateway.GetPreppedDate(workId).HasValue)
                    {
                        DateTime preppedDate = (DateTime)manholeRehabilitationWorkDetailsGateway.GetPreppedDate(workId);
                        tkrdpRehabilitationPreppedDate.SelectedDate = DateTime.Parse(preppedDate.Month.ToString() + "/" + preppedDate.Day.ToString() + "/" + preppedDate.Year.ToString());
                    }

                    if (manholeRehabilitationWorkDetailsGateway.GetSprayedDate(workId).HasValue)
                    {
                        DateTime sprayedDate = (DateTime)manholeRehabilitationWorkDetailsGateway.GetSprayedDate(workId);
                        tkrdpRehabilitationSprayedDate.SelectedDate = DateTime.Parse(sprayedDate.Month.ToString() + "/" + sprayedDate.Day.ToString() + "/" + sprayedDate.Year.ToString());
                    }

                    if (manholeRehabilitationWorkDetailsGateway.GetDate(workId).HasValue)
                    {
                        //Load saved batch
                        int batchId = (manholeRehabilitationWorkDetailsGateway.GetBatchID(workId));
                        ddlRehabilitationBatchDate.SelectedValue = batchId.ToString();
                        hdfExistBatchId.Value = "True";
                        hdfBatchId.Value = batchId.ToString();
                    }
                    else
                    {
                        ddlRehabilitationBatchDate.SelectedValue = "0";
                        hdfExistBatchId.Value = "False";
                    }

                    // Show Comments
                    tbxCommentsDataComments.Text = manholeRehabilitationWorkDetailsGateway.GetComments(workId);

                    // ... ... Store datasets
                    Session["manholeRehabilitationTDS"] = manholeRehabilitationTDS;
                }
            }

            // Load last batch
            MrBatchVerificationGateway mrBatchVerificationGateway = new MrBatchVerificationGateway();
            mrBatchVerificationGateway.LoadAll(companyId);
            if (mrBatchVerificationGateway.Table.Rows.Count > 0)
            {
                WorkManholeRehabilitationBatchGateway workManholeRehabilitationBatchGateway = new WorkManholeRehabilitationBatchGateway();
                hdfBatchId.Value = workManholeRehabilitationBatchGateway.GetLastId(companyId).ToString();
                int batchId = Int32.Parse(hdfBatchId.Value);

                mrBatchVerificationGateway.LoadByBatchId(batchId, companyId);
                DateTime batchDate = mrBatchVerificationGateway.GetDate(batchId);
                tbxLastRehabilitationBatchDate.Text = batchDate.Month.ToString() + "/" + batchDate.Day.ToString() + "/" + batchDate.Year.ToString();
                lblBatchDateRequired.Visible = false;

                // ... ... Store datasets
                Session["manholeRehabilitationTDS"] = manholeRehabilitationTDS;
            }
            else
            {
                lblBatchDateRequired.Visible = true;
            }
        }
        private void UpdateDatabase()
        {
            try
            {
                TeamProjectTime2Gateway teamProjectTime2Gateway = new TeamProjectTime2Gateway(teamProjectTime2TDSToSave);
                teamProjectTime2Gateway.Update(projectTime2TDS);

                teamProjectTime2TDSToSave.AcceptChanges();
                teamProjectTime2TDS.AcceptChanges();
                projectTime2TDS.AcceptChanges();
                Session["teamProjectTime2TDS"] = teamProjectTime2TDS;
            }
            catch (Exception ex)
            {
                string url = string.Format("./../../error_page.aspx?error={0}", ex.Message.Replace('\n', ' '));
                Response.Redirect(url);
            }

            DB.Open();
            DB.BeginTransaction();
            try
            {
                if (ddlTypeOfWork.SelectedValue == "MH Rehab")
                {
                    int companyId = Int32.Parse(hdfCompanyId.Value);

                    // Get ids & location
                    int projectId = Int32.Parse(ddlProject.SelectedValue);
                    ProjectGateway projectGateway = new ProjectGateway();
                    projectGateway.LoadByProjectId(projectId);

                    Int64 countryId = projectGateway.GetCountryID(projectId);
                    Int64? provinceId = null; if (projectGateway.GetProvinceID(projectId).HasValue) provinceId = (Int64)projectGateway.GetProvinceID(projectId);
                    Int64? countyId = null; if (projectGateway.GetCountyID(projectId).HasValue) countyId = (Int64)projectGateway.GetCountyID(projectId);
                    Int64? cityId = null; if (projectGateway.GetCityID(projectId).HasValue) cityId = (Int64)projectGateway.GetCityID(projectId);

                    manholeRehabilitationTDS = new ManholeRehabilitationTDS();
                    ManholeRehabilitationWorkDetails manholeRehabilitationWorkDetails = new ManholeRehabilitationWorkDetails(manholeRehabilitationTDS);
                    ManholeRehabilitationWorkDetailsGateway manholeRehabilitationWorkDetailsGateway = new ManholeRehabilitationWorkDetailsGateway(manholeRehabilitationTDS);

                    switch (ddlFunction.SelectedValue)
                    {
                        case "Prep":
                            foreach (GridViewRow row in grdManholesRehabPrep.Rows)
                            {
                                bool selected = ((CheckBox)row.FindControl("cbxSelected")).Checked;
                                DateTime? prepDate = Convert.ToDateTime(((Label)row.FindControl("lblPrepDate")).Text);

                                if (selected)
                                {
                                    int assetId = Convert.ToInt32(grdManholesRehabPrep.DataKeys[row.RowIndex].Values["AssetID"].ToString());
                                    int workId = 0;

                                    workId = GetWorkId(Int32.Parse(ddlProject.SelectedValue), assetId, "Manhole Rehabilitation", companyId);

                                    manholeRehabilitationWorkDetails.LoadByWorkIdAssetId(workId, assetId, Int32.Parse(hdfCompanyId.Value));

                                    if (manholeRehabilitationWorkDetailsGateway.Table.Rows.Count > 0)
                                    {
                                        int? batchId = manholeRehabilitationWorkDetailsGateway.GetBatchID(workId);
                                        manholeRehabilitationWorkDetails.Update(workId, prepDate, manholeRehabilitationWorkDetailsGateway.GetSprayedDate(workId), batchId, manholeRehabilitationWorkDetailsGateway.GetDate(workId).Value, companyId);
                                    }
                                    else
                                    {
                                        manholeRehabilitationWorkDetails.Update(workId, prepDate, null, null, DateTime.Now, companyId);
                                    }

                                    manholeRehabilitationWorkDetails.Save2(countryId, provinceId, countyId, cityId, projectId, assetId, companyId, true);
                                }
                            }
                            break;

                        case "Spray":
                            foreach (GridViewRow row in grdManholesRehabSpray.Rows)
                            {
                                bool selected = ((CheckBox)row.FindControl("cbxSelected")).Checked;
                                DateTime? sprayDate = Convert.ToDateTime(((Label)row.FindControl("lblSprayDate")).Text);

                                if (selected)
                                {
                                    int assetId = Convert.ToInt32(grdManholesRehabSpray.DataKeys[row.RowIndex].Values["AssetID"].ToString());
                                    int workId = 0;

                                    workId = GetWorkId(Int32.Parse(ddlProject.SelectedValue), assetId, "Manhole Rehabilitation", companyId);

                                    manholeRehabilitationWorkDetails.LoadByWorkIdAssetId(workId, assetId, Int32.Parse(hdfCompanyId.Value));

                                    if (manholeRehabilitationWorkDetailsGateway.Table.Rows.Count > 0)
                                    {
                                        int? batchId = manholeRehabilitationWorkDetailsGateway.GetBatchID(workId);
                                        manholeRehabilitationWorkDetails.Update(workId, manholeRehabilitationWorkDetailsGateway.GetPreppedDate(workId), sprayDate, batchId, manholeRehabilitationWorkDetailsGateway.GetDate(workId).Value, companyId);
                                    }
                                    else
                                    {
                                        manholeRehabilitationWorkDetails.Update(workId, null, sprayDate, null, DateTime.Now, companyId);
                                    }

                                    manholeRehabilitationWorkDetails.Save2(countryId, provinceId, countyId, cityId, projectId, assetId, companyId, true);
                                }
                            }
                            break;
                    }

                    DB.CommitTransaction();

                    // Store datasets
                    manholeRehabilitationTDS.AcceptChanges();
                }
                else
                {
                    if (ddlTypeOfWork.SelectedValue == "Full Length")
                    {
                        fullLengthLiningTDS = new FullLengthLiningTDS();
                        AssetSewerSectionGateway aass = new AssetSewerSectionGateway();
                        FullLengthLiningWorkDetails fullLengthLiningWorkDetails = new FullLengthLiningWorkDetails(fullLengthLiningTDS);
                        FullLengthLiningWorkDetailsGateway fullLengthLiningWorkDetailsGateway = new FullLengthLiningWorkDetailsGateway(fullLengthLiningTDS);

                        int assetId = 0;
                        int workId = 0;

                        // Get ids & location
                        int projectId = Int32.Parse(ddlProject.SelectedValue);
                        ProjectGateway projectGateway = new ProjectGateway();
                        projectGateway.LoadByProjectId(projectId);

                        Int64 countryId = projectGateway.GetCountryID(projectId);
                        Int64? provinceId = null; if (projectGateway.GetProvinceID(projectId).HasValue) provinceId = (Int64)projectGateway.GetProvinceID(projectId);
                        Int64? countyId = null; if (projectGateway.GetCountyID(projectId).HasValue) countyId = (Int64)projectGateway.GetCountyID(projectId);
                        Int64? cityId = null; if (projectGateway.GetCityID(projectId).HasValue) cityId = (Int64)projectGateway.GetCityID(projectId);

                        int companyId = Int32.Parse(hdfCompanyId.Value);

                        switch (ddlFunction.SelectedValue)
                        {
                            case "Install":
                                foreach (GridViewRow row in grdSectionsInstall.Rows)
                                {
                                    bool selected = ((CheckBox)row.FindControl("cbxSelected")).Checked;
                                    DateTime? installDate = Convert.ToDateTime(((Label)row.FindControl("lblInstallDate")).Text);

                                    if (selected)
                                    {
                                        if (installDate != tkrdpDate_.SelectedDate.Value)
                                        {
                                            installDate = tkrdpDate_.SelectedDate.Value;
                                        }

                                        string sectionId = grdSectionsInstall.DataKeys[row.RowIndex].Values["SectionID"].ToString();
                                        aass.LoadBySectionId(sectionId, companyId);
                                        assetId = aass.GetAssetID(sectionId);
                                        workId = GetWorkId(Int32.Parse(ddlProject.SelectedValue), assetId, "Full Length Lining", companyId);

                                        fullLengthLiningWorkDetails.LoadByWorkIdAssetId(workId, assetId, Int32.Parse(hdfCompanyId.Value));
                                        fullLengthLiningWorkDetails.Update(workId, fullLengthLiningWorkDetailsGateway.GetP1Date(workId), fullLengthLiningWorkDetailsGateway.GetP1Completed(workId), installDate, fullLengthLiningWorkDetailsGateway.GetFinalVideoDate(workId));
                                        fullLengthLiningWorkDetails.Save(countryId, provinceId, countyId, cityId, projectId, assetId, companyId, false, false);
                                    }
                                }
                                break;

                            case "Prep & Measure":
                                foreach (GridViewRow row in grdSections.Rows)
                                {
                                    bool selected = ((CheckBox)row.FindControl("cbxSelected")).Checked;
                                    bool completed = ((CheckBox)row.FindControl("cbxCompleted")).Checked;
                                    DateTime? prepDate = Convert.ToDateTime(((Label)row.FindControl("lblPrepDate")).Text);

                                    if (selected)
                                    {
                                        if (completed)
                                        {
                                            if (prepDate != tkrdpDate_.SelectedDate.Value)
                                            {
                                                prepDate = tkrdpDate_.SelectedDate.Value;
                                            }
                                        }
                                        else
                                        {
                                            prepDate = null;
                                        }

                                        string sectionId = grdSections.DataKeys[row.RowIndex].Values["SectionID"].ToString();
                                        aass.LoadBySectionId(sectionId, companyId);
                                        assetId = aass.GetAssetID(sectionId);
                                        workId = GetWorkId(Int32.Parse(ddlProject.SelectedValue), assetId, "Full Length Lining", companyId);

                                        fullLengthLiningWorkDetails.LoadByWorkIdAssetId(workId, assetId, Int32.Parse(hdfCompanyId.Value));
                                        fullLengthLiningWorkDetails.Update(workId, prepDate, completed, fullLengthLiningWorkDetailsGateway.GetInstallDate(workId), fullLengthLiningWorkDetailsGateway.GetFinalVideoDate(workId));
                                        fullLengthLiningWorkDetails.Save(countryId, provinceId, countyId, cityId, projectId, assetId, companyId, false, false);
                                    }
                                }
                                break;

                            case "Reinstate & Post Video":
                                FullLengthLiningLateralDetails fullLengthLiningLateralDetails = new FullLengthLiningLateralDetails(fullLengthLiningTDS);
                                FullLengthLiningLateralDetailsGateway fullLengthLiningLateralDetailsGateway = new FullLengthLiningLateralDetailsGateway(fullLengthLiningTDS);

                                foreach (GridViewRow row in grdSectionsReinstatePostVideo.Rows)
                                {
                                    bool selected = ((CheckBox)row.FindControl("cbxSelected")).Checked;
                                    bool completed = ((CheckBox)row.FindControl("cbxCompleted")).Checked;
                                    DateTime? postVideo = Convert.ToDateTime(((Label)row.FindControl("lblPostVideo")).Text);
                                    string sectionId = grdSectionsReinstatePostVideo.DataKeys[row.RowIndex].Values["SectionID"].ToString();

                                    aass.LoadBySectionId(sectionId, companyId);
                                    assetId = aass.GetAssetID(sectionId);
                                    workId = GetWorkId(Int32.Parse(ddlProject.SelectedValue), assetId, "Full Length Lining", companyId);

                                    if (selected)
                                    {
                                        if (completed)
                                        {
                                            if (postVideo != tkrdpDate_.SelectedDate.Value)
                                            {
                                                postVideo = tkrdpDate_.SelectedDate.Value;
                                            }
                                        }
                                        else
                                        {
                                            postVideo = null;
                                        }

                                        fullLengthLiningWorkDetails.LoadByWorkIdAssetId(workId, assetId, Int32.Parse(hdfCompanyId.Value));
                                        fullLengthLiningWorkDetails.Update(workId, fullLengthLiningWorkDetailsGateway.GetP1Date(workId), fullLengthLiningWorkDetailsGateway.GetP1Completed(workId), fullLengthLiningWorkDetailsGateway.GetInstallDate(workId), postVideo);
                                        fullLengthLiningWorkDetails.Save(countryId, provinceId, countyId, cityId, projectId, assetId, companyId, false, false);
                                    }
                                }

                                foreach (GridViewRow row in grdLaterals.Rows)
                                {
                                    string sectionId = grdLaterals.DataKeys[row.RowIndex].Values["SectionID"].ToString();
                                    int assetIdLateral = Convert.ToInt32(((Label)row.FindControl("lblAssetIDLateral")).Text);
                                    bool selected = ((CheckBox)row.FindControl("cbxSelected")).Checked;
                                    DateTime? opened = null; if (((CheckBox)row.FindControl("cbxOpened")).Checked) opened = tkrdpDate_.SelectedDate;
                                    DateTime? brushed = null; if (((CheckBox)row.FindControl("cbxBrushed")).Checked) brushed = tkrdpDate_.SelectedDate;

                                    if (selected)
                                    {
                                        aass.LoadBySectionId(sectionId, companyId);
                                        assetId = aass.GetAssetID(sectionId);
                                        workId = GetWorkId(Int32.Parse(ddlProject.SelectedValue), assetId, "Full Length Lining", companyId);

                                        fullLengthLiningLateralDetails.SaveFll(workId, assetIdLateral, companyId, opened, brushed);
                                    }
                                }
                                break;
                        }

                        DB.CommitTransaction();

                        // Store datasets
                        fullLengthLiningTDS.AcceptChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                DB.RollbackTransaction();

                string url = string.Format("./../../error_page.aspx?error={0}", ex.Message.Replace('\n', ' '));
                Response.Redirect(url);
            }
        }