/// <summary>
        /// DeleteDirect
        /// </summary>
        /// <param name="assetId">assetId</param>
        /// <param name="companyId">companyId</param>
        public bool DeleteDirect(int assetId, int companyId)
        {
            // Delete laterals
            // ... Define initial models
            AssetSewerLateral assetSewerLateral = new AssetSewerLateral(null);

            // ... Load laterals
            AssetSewerLateralGateway assetSewerLateralGateway = new AssetSewerLateralGateway();
            assetSewerLateralGateway.LoadBySectionId(assetId, companyId);

            // ... Delete laterals
            foreach (AssetsTDS.AM_ASSET_SEWER_LATERALRow rowLateral in (AssetsTDS.AM_ASSET_SEWER_LATERALDataTable)assetSewerLateralGateway.Table)
            {
                assetSewerLateral.DeleteDirect(rowLateral.AssetID, companyId);
            }

            // Delete section
            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway(null);
            assetSewerSectionGateway.Delete(assetId, companyId);

            AssetSewer assetSewer = new AssetSewer(null);
            assetSewer.DeleteDirect(assetId, companyId);

            Asset asset = new Asset(null);
            asset.DeleteDirect(assetId, companyId);

            return true;
        }
        /// <summary>
        /// DeleteDirect
        /// </summary>
        /// <param name="assetId">assetId</param>
        /// <param name="companyId">companyId</param>
        /// <returns></returns>
        public bool DeleteDirect(int assetId, int companyId)
        {
            // Verify in use
            if (!InUse(assetId, companyId))
            {
                // Delete laterals
                // ... Define initial models
                LfsAssetSewerLateral lfsAssetSewerLateral = new LfsAssetSewerLateral(null);

                // ... Load laterals
                AssetSewerLateralGateway assetSewerLateralGateway = new AssetSewerLateralGateway();
                assetSewerLateralGateway.LoadBySectionId(assetId, companyId);

                // ... Delete laterals
                foreach (AssetsTDS.AM_ASSET_SEWER_LATERALRow rowLateral in (AssetsTDS.AM_ASSET_SEWER_LATERALDataTable)assetSewerLateralGateway.Table)
                {
                    lfsAssetSewerLateral.DeleteDirect(rowLateral.AssetID, companyId);
                }

                // Get MHs for deleted
                AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                assetSewerSectionGateway.LoadByAssetId(assetId, companyId);
                int? usmh_assetId = assetSewerSectionGateway.GetUSMH(assetId);
                int? dsmh_assetId = assetSewerSectionGateway.GetDSMH(assetId);

                // Delete section
                LfsAssetSewerSectionGateway lfsAssetSewerSectionGateway = new LfsAssetSewerSectionGateway(null);
                lfsAssetSewerSectionGateway.Delete(assetId, companyId);

                LfsAssetSewer lfsAssetSewer = new LfsAssetSewer(null);
                lfsAssetSewer.DeleteDirect(assetId, companyId);

                LfsAsset lfsAsset = new LfsAsset(null);
                lfsAsset.DeleteDirect(assetId, companyId);

                // delete section in AM tables
                AssetSewerSection assetSewerSection = new AssetSewerSection(null);
                assetSewerSection.DeleteDirect(assetId, companyId);

                // Delete USMH (if not in use)
                if (usmh_assetId.HasValue)
                {
                    LfsAssetSewerMH lfsAssetSewerUsmh = new LfsAssetSewerMH(null);
                    lfsAssetSewerUsmh.DeleteDirect((int)usmh_assetId, companyId);
                }

                // Delete DSMH (if not in use)
                if (dsmh_assetId.HasValue)
                {
                    LfsAssetSewerMH lfsAssetSewerDsmh = new LfsAssetSewerMH(null);
                    lfsAssetSewerDsmh.DeleteDirect((int)dsmh_assetId, companyId);
                }

                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// DeleteDirect
        /// </summary>
        /// <param name="assetId">assetId</param>
        /// <param name="companyId">companyId</param>
        public bool DeleteDirect(int assetId, int companyId)
        {
            // Get initial data
            AssetSewerLateralGateway assetSewerLateralGateway = new AssetSewerLateralGateway();
            assetSewerLateralGateway.LoadByAssetId(assetId, companyId);
            int section_ = assetSewerLateralGateway.GetSection(assetId);
            string state = assetSewerLateralGateway.GetState(assetId);

            // Delete lateral
            assetSewerLateralGateway.Delete(assetId, companyId);
            AssetSewer assetSewer = new AssetSewer(null);
            assetSewer.DeleteDirect(assetId, companyId);
            Asset asset = new Asset(null);
            asset.DeleteDirect(assetId, companyId);

            // Update Section
            // ... Load section
            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
            assetSewerSectionGateway.LoadByAssetId(section_, companyId);

            // ... Get old values of section
            string sectionIdS = assetSewerSectionGateway.GetSectionId(section_);
            string streetS = assetSewerSectionGateway.GetStreet(section_);
            int? usmhIdS = assetSewerSectionGateway.GetUSMH(section_);
            int? dsmhIdS = assetSewerSectionGateway.GetDSMH(section_);
            string mapSizeS = assetSewerSectionGateway.GetMapSize(section_);
            string size_S = assetSewerSectionGateway.GetSize_(section_);
            string mapLengthS = assetSewerSectionGateway.GetMapSize(section_);
            string lengthS = assetSewerSectionGateway.GetLength(section_);
            int? lateralsS = assetSewerSectionGateway.GetLaterals(section_);
            int? liveLateralsS = assetSewerSectionGateway.GetLiveLaterals(section_);
            string flowDirectionS = assetSewerSectionGateway.GetFlowDirection(section_);
            string usmhDepthS = assetSewerSectionGateway.GetUSMHDepth(section_);
            string dsmhDepthS = assetSewerSectionGateway.GetDSMHDepth(section_);
            bool deletedS = assetSewerSectionGateway.GetDeleted(section_);
            string flowOrderIdS = assetSewerSectionGateway.GetFlowOrderID(section_);

            // ... Calculate new values of section
            int? newLaterals = lateralsS - 1;

            int? newLiveLaterals = liveLateralsS;
            if ((newLiveLaterals.HasValue) && (state == "Live"))
            {
                newLiveLaterals = newLiveLaterals - 1;
            }

            // ... Update section
            AssetSewerSection assetSewerSection = new AssetSewerSection(assetSewerSectionGateway.Data);
            assetSewerSection.UpdateDirect(section_, sectionIdS, streetS, usmhIdS, dsmhIdS, mapSizeS, size_S, mapLengthS, lengthS, lateralsS, liveLateralsS, flowDirectionS, usmhDepthS, dsmhDepthS, deletedS, companyId, flowOrderIdS, section_, sectionIdS, streetS, usmhIdS, dsmhIdS, mapSizeS, size_S, mapLengthS, lengthS, newLaterals, newLiveLaterals, flowDirectionS, usmhDepthS, dsmhDepthS, deletedS, companyId, flowOrderIdS);

            return true;
        }
        /// <summary>
        /// UpdateFieldsForSections
        /// </summary>
        /// <param name="companyId">companyId</param>
        /// <param name="currentProjectId">currentProjectId</param>
        private void UpdateFieldsForSections(int companyId, int currentProjectId)
        {
            AssetSewerSectionGateway assetSewerFindSectionGateway = new AssetSewerSectionGateway();

            AssetSewerMHGateway assetSewerFindMHGateway = new AssetSewerMHGateway();
            WorkGateway workGateway = new WorkGateway();
            foreach (ProjectSectionsNavigatorTDS.LFS_PROJECT_SECTIONS_NAVIGATORRow row in (ProjectSectionsNavigatorTDS.LFS_PROJECT_SECTIONS_NAVIGATORDataTable)Table)
            {
                //General data for asset
                assetSewerFindSectionGateway.LoadByAssetId(row.AssetID, companyId); //COMPANY_ID

                // ... For usmh
                row.USMHDescription = "";
                if (!row.IsUSMHNull())
                {
                    assetSewerFindMHGateway.LoadByAssetId(row.USMH, companyId);
                    row.USMHDescription = assetSewerFindMHGateway.GetMHID(row.USMH);
                }

                // ... For dsmh
                row.DSMHDescription = "";
                if (!row.IsDSMHNull())
                {
                    assetSewerFindMHGateway.LoadByAssetId(row.DSMH, companyId);
                    row.DSMHDescription = assetSewerFindMHGateway.GetMHID(row.DSMH);
                }

                // ... For Works
                row.RehabAssessment = workGateway.ExistsProjectIdAssetIdWorkTypeCompanyId(row.AssetID, currentProjectId, "Rehab Assessment", companyId);
                row.FullLengthLining = workGateway.ExistsProjectIdAssetIdWorkTypeCompanyId(row.AssetID, currentProjectId, "Full Length Lining", companyId);
                row.JunctionLining = workGateway.ExistsProjectIdAssetIdWorkTypeCompanyId(row.AssetID, currentProjectId, "Junction Lining Section", companyId);

                row.WorksDescription = "";
                if (row.RehabAssessment) row.WorksDescription = row.WorksDescription + "Rehab Assessment";

                if (row.FullLengthLining)
                {
                    if (row.WorksDescription.Trim().Length > 0)
                    {
                        row.WorksDescription = row.WorksDescription + ", Full Length Lining";
                    }
                    else
                    {
                        row.WorksDescription = row.WorksDescription + "Full Length Lining";
                    }
                }
                if (row.JunctionLining)
                {
                    if (row.WorksDescription.Trim().Length > 0)
                    {
                        row.WorksDescription = row.WorksDescription + ", Junction Lining";
                    }
                    else
                    {
                        row.WorksDescription = row.WorksDescription + "Junction Lining";
                    }
                }

                // ... For Laterals
                AssetSewerLateralGateway assetSewerLateralGateway = new AssetSewerLateralGateway();

                assetSewerLateralGateway.LoadBySectionId(row.AssetID, companyId);

                AssetSewerLateral assetSewerLateral = new AssetSewerLateral(assetSewerLateralGateway.Data);
                row.LateralsDescription = assetSewerLateral.GetAllLaterals(row.AssetID, companyId);
            }
        }
        /// <summary>
        /// Save
        /// </summary>
        /// <param name="projectId">projectId</param>
        /// <param name="countryId">countryId</param>
        /// <param name="provinceId">provinceId</param>
        /// <param name="countyId">countyId</param>
        /// <param name="cityId">cityId</param>
        /// <param name="companyId">companyId</param>
        /// <param name="loginId">loginId</param>
        public void Save(int companyId, int loginId)
        {
            foreach (DataMigrationTDS.JlDataMigrationRow row in (DataMigrationTDS.JlDataMigrationDataTable)Table)
            {
                // Get ids & location
                ProjectGateway projectGateway = new ProjectGateway();
                DataMigrationProjectGateway dataMigrationProjectGateway = new DataMigrationProjectGateway(null);
                string name = string.Format("{0} Data Migration Project", row.Client);

                int projectId = dataMigrationProjectGateway.GetProjectIdByName(name);

                if (projectId != 0)
                {
                    projectGateway.LoadByProjectId(projectId);

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

                    //Save section
                    int section_assetId = SaveSection(row, projectId, countryId, provinceId, countyId, cityId, companyId);
                    Guid originalId = row.OriginalID;

                    if (row.JLiner)
                    {
                        SaveJLWork(originalId, section_assetId, row.NumLats, row.NotLinedYet, row.AllMeasured, row.IssueWithLaterals, row.NotMeasuredYet, row.NotDeliveredYet, projectId, countryId, provinceId, countyId, cityId, companyId);
                    }

                    // Insert in DataMigration Table
                    string originalSectionId = row.OriginalSectionID;
                    AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                    assetSewerSectionGateway.LoadByAssetId(section_assetId, companyId);
                    string newSectionId = assetSewerSectionGateway.GetSectionId(section_assetId);

                    DataMigrationGateway dataMigrationGateway = new DataMigrationGateway(null);
                    dataMigrationGateway.InsertDataMigration(originalId, originalSectionId, section_assetId, newSectionId);
                }
            }
        }
        // ////////////////////////////////////////////////////////////////////////
        // EVENTS
        //
        protected void Page_Load(object sender, EventArgs e)
        {
            // Register client scripts
            this.RegisterClientScripts();

            if (!IsPostBack)
            {
                // Security check
                if (!Convert.ToBoolean(Session["sgLFS_PROJECTS_VIEW"]))
                {
                    Response.Redirect("./../../error_page.aspx?error=" + "You are not authorized to view this page. Contact your system administrator.");
                }

                // Validate query string
                if (Request.QueryString["source_page"] == null)
                {
                    Response.Redirect("./../../error_page.aspx?error=" + "Invalid query string in projects2.aspx");
                }

                // Tag page
                hdfCompanyId.Value = Session["companyID"].ToString();
                hdfProjectId.Value = Request.QueryString["project_id"];
                hdfAssetID.Value = Request.QueryString["asset_id"];
                Session.Remove("projectSectionsNavigatorLateralsNewDummy");
                Session.Remove("projectSectionsNavigatorLaterals");

                // Initialize data
                projectTDS = (ProjectTDS)Session["lfsProjectTDS"];
                assetsTDS = new AssetsTDS();
                lfsAssetsTDS = new LfsAssetsTDS();
                workTDS = new WorkTDS();
                projectSectionsNavigatorTDS = new ProjectSectionsNavigatorTDS();

                // Load data
                int companyId = Int32.Parse(hdfCompanyId.Value);
                int assetId = Int32.Parse(hdfAssetID.Value);
                int projectId = Int32.Parse(hdfProjectId.Value);

                AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                assetSewerSectionGateway.LoadByAssetId(assetId, companyId);

                if (assetSewerSectionGateway.Table.Rows.Count > 0)
                {
                    tbxSectionId.Text = assetSewerSectionGateway.GetFlowOrderID(assetId);
                    tbxStreet.Text = assetSewerSectionGateway.GetStreet(assetId);

                    // ... For usmh
                    if (assetSewerSectionGateway.GetUSMH(assetId).HasValue)
                    {
                        AssetSewerMHGateway assetSewerFindMHGateway = new AssetSewerMHGateway();
                        int USMH = (int)assetSewerSectionGateway.GetUSMH(assetId);

                        assetSewerFindMHGateway.LoadByAssetId(USMH, companyId);
                        tbxUSMH.Text = assetSewerFindMHGateway.GetMHID(USMH);
                    }

                    // ... For usmh
                    if (assetSewerSectionGateway.GetDSMH(assetId).HasValue)
                    {
                        AssetSewerMHGateway assetSewerFindMHGateway = new AssetSewerMHGateway();
                        int DSMH = (int)assetSewerSectionGateway.GetDSMH(assetId);

                        assetSewerFindMHGateway.LoadByAssetId(DSMH, companyId);
                        tbxDSMH.Text = assetSewerFindMHGateway.GetMHID(DSMH);
                    }

                    // ... For Works
                    WorkGateway workGateway = new WorkGateway();
                    if (workGateway.ExistsProjectIdAssetIdWorkTypeCompanyId(assetId, projectId, "Rehab Assessment", companyId))
                        ckbxRehabAssessment.Checked = true;
                    if (workGateway.ExistsProjectIdAssetIdWorkTypeCompanyId(assetId, projectId, "Full Length Lining", companyId))
                        ckbxFullLengthLining.Checked = true;
                    if (workGateway.ExistsProjectIdAssetIdWorkTypeCompanyId(assetId, projectId, "Junction Lining Section", companyId))
                        ckbxJunctionLining.Checked = true;

                    // ... For Laterals
                    if (assetSewerSectionGateway.GetLaterals(int.Parse(hdfAssetID.Value)).HasValue)
                    {
                        tbxTotalLaterals.Text = ((int)assetSewerSectionGateway.GetLaterals(assetId)).ToString();
                    }
                    else
                    {
                        tbxTotalLaterals.Text = "0";
                    }

                    ProjectSectionsNavigatorLateralsGateway projectSectionsNavigatorLateralsGateway = new ProjectSectionsNavigatorLateralsGateway(projectSectionsNavigatorTDS);
                    projectSectionsNavigatorLateralsGateway.LoadBySection_ProjectId(assetId, projectId, companyId);

                    AssetSewerLateralGateway assetSewerLateralGateway = new AssetSewerLateralGateway(assetsTDS);
                    assetSewerLateralGateway.LoadBySectionProjectId(assetId, projectId, companyId);

                    // ... store datasets
                    Session["projectSectionsNavigatorTDS"] = projectSectionsNavigatorTDS;
                    projectSectionsNavigatorLaterals = projectSectionsNavigatorTDS.ProjectSectionNavigatorLaterals;
                    Session["projectSectionsNavigatorLaterals"] = projectSectionsNavigatorLaterals;
                }
            }
            else
            {
                // Restore dataset
                projectTDS = (ProjectTDS)Session["lfsProjectTDS"];
                projectSectionsNavigatorTDS = (ProjectSectionsNavigatorTDS)Session["lfsProjectSectionsNavigatorTDS"];
                projectSectionsNavigatorLaterals = (ProjectSectionsNavigatorTDS.ProjectSectionNavigatorLateralsDataTable)Session["projectSectionsNavigatorLaterals"];
                assetsTDS = (AssetsTDS)Session["assetsTDS"];
                lfsAssetsTDS = (LfsAssetsTDS)Session["lfsAssetsTDS"];
                workTDS = (WorkTDS)Session["workTDS"];
            }
        }
        private bool ProcessBulkUpload(string fName, out string bulkUploadResultMessage)
        {
            bool bulkUploadProccessed = true;
            bulkUploadResultMessage = "";

            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();

            AppSettingsReader appSettingReader = new AppSettingsReader();
            string excelConnectionString = appSettingReader.GetValue("ExcelConnectionString", typeof(System.String)).ToString() + fName + ";";
            OleDbConnection connection = new OleDbConnection(excelConnectionString);
            OleDbCommand command = null;
            OleDbDataReader dataReader = null;

            try
            {
                ArrayList validatedIds = new ArrayList();

                try
                {
                    connection.Open();
                    command = new OleDbCommand("select * from [sections]", connection);
                    dataReader = command.ExecuteReader();

                    while (dataReader.Read())
                    {
                        if (!IsEmptyRow(dataReader))
                        {
                            string raWork = "";
                            try
                            {
                                raWork = dataReader.GetValue(dataReader.GetOrdinal("RAWork")).ToString().ToUpper().Trim(); }
                            catch
                            {
                            }

                            string fllWork = "";
                            try
                            {
                                fllWork = dataReader.GetValue(dataReader.GetOrdinal("FLLWork")).ToString().ToUpper().Trim();
                            }
                            catch
                            {
                            }

                            string jlWork = "";
                            try
                            {
                                jlWork = dataReader.GetValue(dataReader.GetOrdinal("JLWork")).ToString().ToUpper().Trim();
                            }
                            catch
                            {
                            }

                            if (bulkUploadProccessed)
                            {
                                if (((raWork != null) && (raWork != "")) || ((fllWork != null) && (fllWork != "")) || ((jlWork != null) && (jlWork != "")))
                                {
                                    if ((raWork.ToUpper() == "YES") || (fllWork.ToUpper() == "YES") || (jlWork.ToUpper() == "YES"))
                                    {
                                        bulkUploadProccessed = true;
                                    }
                                    else
                                    {
                                        bulkUploadResultMessage = "At least one work must exist in 'Works' columns (sections data range). Bulk upload ABORTED.";
                                        bulkUploadProccessed = false;
                                    }
                                }
                                else
                                {
                                    bulkUploadResultMessage = "At least one work must exist in 'Works' columns (sections data range). Bulk upload ABORTED.";
                                    bulkUploadProccessed = false;
                                }

                                if (!bulkUploadProccessed)
                                {
                                    break;
                                }
                            }
                        }
                    }

                    dataReader.Close();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    bulkUploadResultMessage = "You did not define the 'sections' data range. Bulk upload ABORTED. Original message: " + ex.Message;
                    bulkUploadProccessed = false;

                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }

                //--- Process bulk upload
                if (bulkUploadProccessed)
                {
                    connection.Open();
                    command = new OleDbCommand("select * from [sections]", connection);
                    dataReader = command.ExecuteReader();

                    int id = 0;

                    while (dataReader.Read())
                    {
                        if (!IsEmptyRow(dataReader))
                        {
                            string street = "";
                            string clientId = "";
                            string subArea = "";
                            string usmh = "";
                            string dsmh = "";
                            string mapSize = "";
                            string confirmedSize = "";
                            string mapLength = "";
                            string actualLength = "";
                            bool raWork = false;
                            string raComments = "";
                            bool fllWork = false;
                            string fllComments = "";
                            bool jlWork = false;
                            string dataCell = null;
                            string dataCellToUpper = null;

                            //--- ... fill section row
                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                dataCell = dataReader.GetValue(i).ToString().Trim();
                                dataCellToUpper = dataReader.GetValue(i).ToString().Trim().ToUpper();

                                switch (dataReader.GetName(i).Trim())
                                {
                                    case "Street":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            street = dataCell;
                                        }
                                        else
                                        {
                                            street = "";
                                        }
                                        break;

                                    case "Client ID":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            clientId = dataCell;
                                        }
                                        else
                                        {
                                            clientId = "";
                                        }
                                        break;

                                    case "SubArea":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            subArea = dataCell;
                                        }
                                        else
                                        {
                                            subArea = "";
                                        }
                                        break;

                                    case "USMH":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            usmh = dataCell;
                                        }
                                        else
                                        {
                                            usmh = "";
                                        }
                                        break;

                                    case "DSMH":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            dsmh = dataCell;
                                        }
                                        else
                                        {
                                            dsmh = "";
                                        }
                                        break;

                                    case "MapSize":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                mapSize = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'MapSize' column (" + id + "). Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        else
                                        {
                                            mapSize = "";
                                        }
                                        break;

                                    case "ConfirmedSize":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                confirmedSize = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'ConfirmedSize' column (" + id + "). Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        else
                                        {
                                            confirmedSize = "";
                                        }
                                        break;

                                    case "MapLength":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                mapLength = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'MapLength' column (" + id + "). Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        else
                                        {
                                            mapLength = "";
                                        }
                                        break;

                                    case "ActualLength":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                actualLength = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'ActualLength' column (" + id + "). Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        else
                                        {
                                            actualLength = "";
                                        }
                                        break;

                                    case "RAWork":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            raWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        else
                                        {
                                            bulkUploadResultMessage = "Invalid value in 'RAWork' column (" + id + "). Bulk upload ABORTED.";
                                            bulkUploadProccessed = false;
                                        }
                                        break;

                                    case "RAComments":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            raComments = dataCell;
                                        }
                                        else
                                        {
                                            raComments = "";
                                        }
                                        break;

                                    case "FLLWork":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            fllWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        else
                                        {
                                            bulkUploadResultMessage = "Invalid value in 'FLLWork' column (" + id + "). Bulk upload ABORTED.";
                                            bulkUploadProccessed = false;
                                        }
                                        break;

                                    case "FLLComments":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            fllComments = dataCell;
                                        }
                                        else
                                        {
                                            fllComments = "";
                                        }
                                        break;

                                    case "JLWork":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            jlWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        else
                                        {
                                            bulkUploadResultMessage = "Invalid value in 'JLWork' column (" + id + "). Bulk upload ABORTED.";
                                            bulkUploadProccessed = false;
                                        }
                                        break;

                                    default:
                                        bulkUploadResultMessage = "Invalid column name '" + dataReader.GetName(i) + "' in section data range.";
                                        bulkUploadProccessed = false;
                                        break;
                                }

                                if (!bulkUploadProccessed)
                                {
                                    break;
                                }
                            }

                            if (bulkUploadProccessed)
                            {
                                //--- Initialize
                                BulkUploadTDS dataSet = new BulkUploadTDS();
                                dataSet.BulkUpload.Merge(bulkUpload, true);
                                BulkUpload model = new BulkUpload(dataSet);
                                id = id + 1;
                                model.Insert(id.ToString(), street, clientId, subArea, usmh, dsmh, mapSize, confirmedSize, mapLength, actualLength, raWork, raComments, fllWork, fllComments, jlWork);
                                bulkUpload = dataSet.BulkUpload;
                                Session["bulkUpload"] = dataSet.BulkUpload;
                            }
                        }
                    }

                    dataReader.Close();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                }

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }

                throw ex;
            }

            return (bulkUploadProccessed) ? true : false;
        }
        /// <summary>
        /// Save
        /// </summary>
        /// <param name="projectId">projectId</param>
        /// <param name="countryId">countryId</param>
        /// <param name="provinceId">provinceId</param>
        /// <param name="countyId">countyId</param>
        /// <param name="cityId">cityId</param>
        /// <param name="companyId">companyId</param>
        /// <param name="loginId">loginId</param>
        public void Save(int companyId, int loginId)
        {
            foreach (DataMigrationTDS.DataMigrationRow row in (DataMigrationTDS.DataMigrationDataTable)Table)
            {
                // Get ids & location
                ProjectGateway projectGateway = new ProjectGateway();
                DataMigrationProjectGateway dataMigrationProjectGateway = new DataMigrationProjectGateway(null);
                string name = string.Format("{0} Data Migration Project", row.Client);

                int projectId = dataMigrationProjectGateway.GetProjectIdByName(name);

                if (projectId != 0)
                {
                    projectGateway.LoadByProjectId(projectId);

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

                    //Save section
                    int section_assetId = SaveSection(row, projectId, countryId, provinceId, countyId, cityId, companyId);

                    if (row.RehabAssessment)
                    {
                        string raComments = "";
                        SaveRAWork(row, projectId, section_assetId, companyId, raComments, loginId);
                    }

                    if (row.FullLengthLining)
                    {
                        string fllComments = "";
                        SaveFLLWork(row, projectId, section_assetId, companyId, fllComments, loginId);
                    }

                    if (row.JLiner)
                    {
                        SaveJLWork(projectId, section_assetId, companyId);
                    }

                    // Insert in DataMigration Table
                    Guid originalId = row.OriginalID;
                    string originalSectionId = row.OriginalSectionID;
                    AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                    assetSewerSectionGateway.LoadByAssetId(section_assetId, companyId);
                    string newSectionId = assetSewerSectionGateway.GetSectionId(section_assetId);

                    DataMigrationGateway dataMigrationGateway = new DataMigrationGateway(null);
                    dataMigrationGateway.InsertDataMigration(originalId, originalSectionId, section_assetId, newSectionId);
                }
            }
        }
        /// <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="sectionAssetId">sectionAssetId</param>
        /// <param name="companyId">companyId</param>
        /// <param name="includeWetOutInformation">includeWetOutInformation</param>
        /// <param name="includeInversionInformation">includeInversionInformation</param>
        public void Save(Int64? countryId, Int64? provinceId, Int64? countyId, Int64? cityId, int projectId, int sectionAssetId, int companyId, bool includeWetOutInformation, bool includeInversionInformation)
        {
            FullLengthLiningTDS fullLengthLiningChanges = (FullLengthLiningTDS)Data.GetChanges();

            if (fullLengthLiningChanges.WorkDetails.Rows.Count > 0)
            {
                FullLengthLiningWorkDetailsGateway fullLengthLiningWorkDetailsGateway = new FullLengthLiningWorkDetailsGateway(fullLengthLiningChanges);

                // Update sections
                foreach (FullLengthLiningTDS.WorkDetailsRow row in (FullLengthLiningTDS.WorkDetailsDataTable)fullLengthLiningChanges.WorkDetails)
                {
                    // Unchanged values
                    int workId = row.WorkID;

                    // Original values
                    string originalClientId = fullLengthLiningWorkDetailsGateway.GetClientIdOriginal(workId);
                    DateTime? originalProposedLiningDate = fullLengthLiningWorkDetailsGateway.GetProposedLiningDateOriginal(workId);
                    DateTime? originalDeadlineLiningDate = fullLengthLiningWorkDetailsGateway.GetDeadlineLiningDateOriginal(workId);
                    DateTime? originalP1Date = fullLengthLiningWorkDetailsGateway.GetP1DateOriginal(workId);
                    DateTime? originalM1Date = fullLengthLiningWorkDetailsGateway.GetM1DateOriginal(workId);
                    DateTime? originalM2Date = fullLengthLiningWorkDetailsGateway.GetM2DateOriginal(workId);
                    DateTime? originalInstallDate = fullLengthLiningWorkDetailsGateway.GetInstallDateOriginal(workId);
                    DateTime? originalFinalVideoDate = fullLengthLiningWorkDetailsGateway.GetFinalVideoDateOriginal(workId);
                    bool originalIssueIdentified = fullLengthLiningWorkDetailsGateway.GetIssueIdentifiedOriginal(workId);
                    bool originalIssueLFS = fullLengthLiningWorkDetailsGateway.GetIssueLFSOriginal(workId);
                    bool originalIssueClient = fullLengthLiningWorkDetailsGateway.GetIssueClientOriginal(workId);
                    bool originalIssueSales = fullLengthLiningWorkDetailsGateway.GetIssueSalesOriginal(workId);
                    bool originalIssueGivenToClient = fullLengthLiningWorkDetailsGateway.GetIssueGivenToClientOriginal(workId);
                    bool originalIssueResolved = fullLengthLiningWorkDetailsGateway.GetIssueResolvedOriginal(workId);
                    bool originalIssueInvestigation = fullLengthLiningWorkDetailsGateway.GetIssueInvestigationOriginal(workId);
                    int? originalCxisRemoved = fullLengthLiningWorkDetailsGateway.GetCxisRemovedOriginal(workId);
                    bool originalRoboticPrepCompleted = fullLengthLiningWorkDetailsGateway.GetRoboticPrepCompletedOriginal(workId);
                    DateTime? originalRoboticPrepCompletedDate = fullLengthLiningWorkDetailsGateway.GetRoboticPrepCompletedDateOriginal(workId);
                    DateTime? originalPreFlushDate = fullLengthLiningWorkDetailsGateway.GetPreFlushDateOriginal(workId);
                    DateTime? originalPreVideoDate = fullLengthLiningWorkDetailsGateway.GetPreVideoDateOriginal(workId);
                    int originalRaWorkId = fullLengthLiningWorkDetailsGateway.GetRaWorkIdOriginal(workId);
                    bool originalP1Completed = fullLengthLiningWorkDetailsGateway.GetP1CompletedOriginal(workId);

                    // M1 data
                    string originalMeasurementTakenBy = fullLengthLiningWorkDetailsGateway.GetMeasurementTakenByOriginal(workId);
                    string originalMaterial = fullLengthLiningWorkDetailsGateway.GetMaterialOriginal(workId);
                    string originalTrafficControl = fullLengthLiningWorkDetailsGateway.GetTrafficControlOriginal(workId);
                    string originalSiteDetails = fullLengthLiningWorkDetailsGateway.GetSiteDetailsOriginal(workId);
                    bool originalPipeSizeChange = fullLengthLiningWorkDetailsGateway.GetPipeSizeChangeOriginal(workId);
                    bool originalStandardBypass = fullLengthLiningWorkDetailsGateway.GetStandardBypassOriginal(workId);
                    string originalStandardBypassComments = fullLengthLiningWorkDetailsGateway.GetStandardBypassCommentsOriginal(workId);
                    string originalTrafficControlDetails = fullLengthLiningWorkDetailsGateway.GetTrafficControlDetailsOriginal(workId);
                    string originalMeasurementType = fullLengthLiningWorkDetailsGateway.GetMeasurementTypeOriginal(workId);
                    string originalMeasurementFromMh = fullLengthLiningWorkDetailsGateway.GetMeasurementFromMhOriginal(workId);
                    string originalVideoDoneFromMh = fullLengthLiningWorkDetailsGateway.GetVideoDoneFromMhOriginal(workId);
                    string originalVideoDoneToMh = fullLengthLiningWorkDetailsGateway.GetVideoDoneToMhOriginal(workId);
                    string originalAccessType = fullLengthLiningWorkDetailsGateway.GetAccessTypeOriginal(workId);

                    // M2 data
                    string originalMeasurementTakenByM2 = fullLengthLiningWorkDetailsGateway.GetMeasurementTakenByM2Original(workId);
                    bool originalDropPipe = fullLengthLiningWorkDetailsGateway.GetDropPipeOriginal(workId);
                    string originalDropPipeInvertDepth = fullLengthLiningWorkDetailsGateway.GetDropPipeInvertDepthOriginal(workId);
                    int? originalCappedLaterals = fullLengthLiningWorkDetailsGateway.GetCappedLateralsOriginal(workId);
                    string originalLineWithId = fullLengthLiningWorkDetailsGateway.GetLineWithIdOriginal(workId);
                    string originalHydrantAddress = fullLengthLiningWorkDetailsGateway.GetHydrantAddressOriginal(workId);
                    string originalHydroWireWithin10FtOfInversionMH = fullLengthLiningWorkDetailsGateway.GetHydroWiredWithin10FtOfInversionMHOriginal(workId);
                    string originalDistanceToInversionMh = fullLengthLiningWorkDetailsGateway.GetDistanceToInversionMhOriginal(workId);
                    string originalSurfaceGrade = fullLengthLiningWorkDetailsGateway.GetSurfaceGradeOriginal(workId);
                    bool originalHydroPulley = fullLengthLiningWorkDetailsGateway.GetHydroPulleyOriginal(workId);
                    bool originalFridgeCart = fullLengthLiningWorkDetailsGateway.GetFridgeCartOriginal(workId);
                    bool originalTwoPump = fullLengthLiningWorkDetailsGateway.GetTwoPumpOriginal(workId);
                    bool originalSixBypass = fullLengthLiningWorkDetailsGateway.GetSixBypassOriginal(workId);
                    bool originalScaffolding = fullLengthLiningWorkDetailsGateway.GetScaffoldingOriginal(workId);
                    bool originalWinchExtension = fullLengthLiningWorkDetailsGateway.GetWinchExtensionOriginal(workId);
                    bool originalExtraGenerator = fullLengthLiningWorkDetailsGateway.GetExtraGeneratorOriginal(workId);
                    bool originalGreyCableExtension = fullLengthLiningWorkDetailsGateway.GetGreyCableExtensionOriginal(workId);
                    bool originalEasementMats = fullLengthLiningWorkDetailsGateway.GetEasementMatsOriginal(workId);
                    bool originalRampRequired = fullLengthLiningWorkDetailsGateway.GetRampRequiredOriginal(workId);
                    string originalVideoLength = fullLengthLiningWorkDetailsGateway.GetVideoLengthOriginal(workId);
                    bool originalCameraSkid = fullLengthLiningWorkDetailsGateway.GetCameraSkidOriginal(workId);

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

                    // New variables
                    string newClientId = fullLengthLiningWorkDetailsGateway.GetClientId(workId);
                    DateTime? newProposedLiningDate = fullLengthLiningWorkDetailsGateway.GetProposedLiningDate(workId);
                    DateTime? newDeadlineLiningDate = fullLengthLiningWorkDetailsGateway.GetDeadlineLiningDate(workId);
                    DateTime? newP1Date = fullLengthLiningWorkDetailsGateway.GetP1Date(workId);
                    DateTime? newM1Date = fullLengthLiningWorkDetailsGateway.GetM1Date(workId);
                    DateTime? newM2Date = fullLengthLiningWorkDetailsGateway.GetM2Date(workId);
                    DateTime? newInstallDate = fullLengthLiningWorkDetailsGateway.GetInstallDate(workId);
                    DateTime? newFinalVideoDate = fullLengthLiningWorkDetailsGateway.GetFinalVideoDate(workId);
                    bool newIssueIdentified = fullLengthLiningWorkDetailsGateway.GetIssueIdentified(workId);
                    bool newIssueLFS = fullLengthLiningWorkDetailsGateway.GetIssueLFS(workId);
                    bool newIssueClient = fullLengthLiningWorkDetailsGateway.GetIssueClient(workId);
                    bool newIssueSales = fullLengthLiningWorkDetailsGateway.GetIssueSales(workId);
                    bool newIssueGivenToClient = fullLengthLiningWorkDetailsGateway.GetIssueGivenToClient(workId);
                    bool newIssueResolved = fullLengthLiningWorkDetailsGateway.GetIssueResolved(workId);
                    bool newIssueInvestigation = fullLengthLiningWorkDetailsGateway.GetIssueInvestigation(workId);
                    int? newCxisRemoved = fullLengthLiningWorkDetailsGateway.GetCxisRemoved(workId);
                    bool newRoboticPrepCompleted = fullLengthLiningWorkDetailsGateway.GetRoboticPrepCompleted(workId);
                    DateTime? newRoboticPrepCompletedDate = fullLengthLiningWorkDetailsGateway.GetRoboticPrepCompletedDate(workId);
                    DateTime? newPreFlushDate = fullLengthLiningWorkDetailsGateway.GetPreFlushDate(workId);
                    DateTime? newPreVideoDate = fullLengthLiningWorkDetailsGateway.GetPreVideoDate(workId);
                    int newRaWorkId = fullLengthLiningWorkDetailsGateway.GetRaWorkId(workId);
                    bool newP1Completed = fullLengthLiningWorkDetailsGateway.GetP1Completed(workId);

                    // M1
                    string newMeasurementTakenBy = fullLengthLiningWorkDetailsGateway.GetMeasurementTakenBy(workId);
                    string newMaterial = fullLengthLiningWorkDetailsGateway.GetMaterial(workId);
                    string newTrafficControl = fullLengthLiningWorkDetailsGateway.GetTrafficControl(workId);
                    string newSiteDetails = fullLengthLiningWorkDetailsGateway.GetSiteDetails(workId);
                    bool newPipeSizeChange = fullLengthLiningWorkDetailsGateway.GetPipeSizeChange(workId);
                    bool newStandardBypass = fullLengthLiningWorkDetailsGateway.GetStandardBypass(workId);
                    string newStandardBypassComments = fullLengthLiningWorkDetailsGateway.GetStandardBypassComments(workId);
                    string newTrafficControlDetails = fullLengthLiningWorkDetailsGateway.GetTrafficControlDetails(workId);
                    string newMeasurementType = fullLengthLiningWorkDetailsGateway.GetMeasurementType(workId);
                    string newMeasurementFromMh = fullLengthLiningWorkDetailsGateway.GetMeasurementFromMh(workId);
                    string newVideoDoneFromMh = fullLengthLiningWorkDetailsGateway.GetVideoDoneFromMh(workId);
                    string newVideoDoneToMh = fullLengthLiningWorkDetailsGateway.GetVideoDoneToMh(workId);
                    string newAccessType = fullLengthLiningWorkDetailsGateway.GetAccessType(workId);

                    // M2
                    string newMeasurementTakenByM2 = fullLengthLiningWorkDetailsGateway.GetMeasurementTakenByM2(workId);
                    bool newDropPipe = fullLengthLiningWorkDetailsGateway.GetDropPipe(workId);
                    string newDropPipeInvertDepth = fullLengthLiningWorkDetailsGateway.GetDropPipeInvertDepth(workId);
                    int? newCappedLaterals = fullLengthLiningWorkDetailsGateway.GetCappedLaterals(workId);
                    string newLineWithId = fullLengthLiningWorkDetailsGateway.GetLineWithId(workId);
                    string newHydrantAddress = fullLengthLiningWorkDetailsGateway.GetHydrantAddress(workId);
                    string newHydroWireWithin10FtOfInversionMH = fullLengthLiningWorkDetailsGateway.GetHydroWiredWithin10FtOfInversionMH(workId);
                    string newDistanceToInversionMh = fullLengthLiningWorkDetailsGateway.GetDistanceToInversionMh(workId);
                    string newSurfaceGrade = fullLengthLiningWorkDetailsGateway.GetSurfaceGrade(workId);
                    bool newHydroPulley = fullLengthLiningWorkDetailsGateway.GetHydroPulley(workId);
                    bool newFridgeCart = fullLengthLiningWorkDetailsGateway.GetFridgeCart(workId);
                    bool newTwoPump = fullLengthLiningWorkDetailsGateway.GetTwoPump(workId);
                    bool newSixBypass = fullLengthLiningWorkDetailsGateway.GetSixBypass(workId);
                    bool newScaffolding = fullLengthLiningWorkDetailsGateway.GetScaffolding(workId);
                    bool newWinchExtension = fullLengthLiningWorkDetailsGateway.GetWinchExtension(workId);
                    bool newExtraGenerator = fullLengthLiningWorkDetailsGateway.GetExtraGenerator(workId);
                    bool newGreyCableExtension = fullLengthLiningWorkDetailsGateway.GetGreyCableExtension(workId);
                    bool newEasementMats = fullLengthLiningWorkDetailsGateway.GetEasementMats(workId);
                    bool newRampRequired = fullLengthLiningWorkDetailsGateway.GetRampRequired(workId);
                    string newVideoLength = fullLengthLiningWorkDetailsGateway.GetVideoLength(workId);
                    bool newCameraSkid = fullLengthLiningWorkDetailsGateway.GetCameraSkid(workId);

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

                    // Update work
                    UpdateWork(countryId, provinceId, countyId, cityId, workId, originalClientId, originalProposedLiningDate, originalDeadlineLiningDate, originalP1Date, originalM1Date, originalM2Date, originalInstallDate, originalFinalVideoDate, originalIssueIdentified, originalIssueLFS, originalIssueClient, originalIssueSales, originalIssueGivenToClient, originalIssueResolved, originalIssueInvestigation, originalCxisRemoved, originalRoboticPrepCompleted, originalRoboticPrepCompletedDate, originalMeasurementTakenBy, originalTrafficControl, originalSiteDetails, originalPipeSizeChange, originalStandardBypass, originalStandardBypassComments, originalTrafficControlDetails, originalMeasurementType, originalMeasurementFromMh, originalVideoDoneFromMh, originalVideoDoneToMh, originalMeasurementTakenByM2, originalDropPipe, originalDropPipeInvertDepth, originalCappedLaterals, originalLineWithId, originalHydrantAddress, originalHydroWireWithin10FtOfInversionMH, originalDistanceToInversionMh, originalSurfaceGrade, originalHydroPulley, originalFridgeCart, originalTwoPump, originalSixBypass, originalScaffolding, originalWinchExtension, originalExtraGenerator, originalGreyCableExtension, originalEasementMats, originalRampRequired, originalVideoLength, originalComments, originalPreFlushDate, originalPreVideoDate, originalRaWorkId, false, companyId, originalMaterial, originalCameraSkid, originalAccessType, originalP1Completed, newClientId, newProposedLiningDate, newDeadlineLiningDate, newP1Date, newM1Date, newM2Date, newInstallDate, newFinalVideoDate, newIssueIdentified, newIssueLFS, newIssueClient, newIssueSales, newIssueGivenToClient, newIssueResolved, newIssueInvestigation, newCxisRemoved, newRoboticPrepCompleted, newRoboticPrepCompletedDate, newMeasurementTakenBy, newMaterial, newTrafficControl, newSiteDetails, newPipeSizeChange, newStandardBypass, newStandardBypassComments, newTrafficControlDetails, newMeasurementType, newMeasurementFromMh, newVideoDoneFromMh, newVideoDoneToMh, newMeasurementTakenByM2, newDropPipe, newDropPipeInvertDepth, newCappedLaterals, newLineWithId, newHydrantAddress, newHydroWireWithin10FtOfInversionMH, newDistanceToInversionMh, newSurfaceGrade, newHydroPulley, newFridgeCart, newTwoPump, newSixBypass, newScaffolding, newWinchExtension, newExtraGenerator, newGreyCableExtension, newEasementMats, newRampRequired, newVideoLength, newComments, newPreFlushDate, newPreVideoDate, newRaWorkId, sectionAssetId, false, companyId, newCameraSkid, newAccessType, newP1Completed);

                    // For wet out information
                    if (includeWetOutInformation)
                    {
                        // Update data
                        WorkFullLengthLiningWetOutGateway workFullLengthLiningWetOutGateway = new WorkFullLengthLiningWetOutGateway();
                        workFullLengthLiningWetOutGateway.LoadByWorkId(workId, companyId);

                        // ... Verify if work has wet out data information
                        if (workFullLengthLiningWetOutGateway.Table.Rows.Count > 0)
                        {
                            // Wet Out data original values
                            string originalLinerTube = fullLengthLiningWorkDetailsGateway.GetLinerTubeOriginal(workId);
                            int originalResinID = fullLengthLiningWorkDetailsGateway.GetResinIdOriginal(workId);
                            decimal originalExcessResin = fullLengthLiningWorkDetailsGateway.GetExcessResinOriginal(workId);
                            string originalPoundsDrums = fullLengthLiningWorkDetailsGateway.GetPoundsDrumsOriginal(workId);
                            decimal originalDrumDiameter = fullLengthLiningWorkDetailsGateway.GetDrumDiameterOriginal(workId);
                            decimal originalHoistMaximumHeight = fullLengthLiningWorkDetailsGateway.GetHoistMaximumHeightOriginal(workId);
                            decimal originalHoistMinimumHeight = fullLengthLiningWorkDetailsGateway.GetHoistMinimumHeightOriginal(workId);
                            decimal originalDownDropTubeLenght = fullLengthLiningWorkDetailsGateway.GetDownDropTubeLenghtOriginal(workId);
                            decimal originalPumpHeightAboveGround = fullLengthLiningWorkDetailsGateway.GetPumpHeightAboveGroundOriginal(workId);
                            int originalTubeResinToFeltFactor = fullLengthLiningWorkDetailsGateway.GetTubeResinToFeltFactorOriginal(workId);
                            DateTime originalDateOfSheet = fullLengthLiningWorkDetailsGateway.GetDateOfSheetOriginal(workId);
                            int originalEmployeeID = fullLengthLiningWorkDetailsGateway.GetEmployeeIdOriginal(workId);
                            string originalRunDetails = fullLengthLiningWorkDetailsGateway.GetRunDetailsOriginal(workId);
                            string originalRunDetails2 = fullLengthLiningWorkDetailsGateway.GetRunDetails2Original(workId);
                            DateTime originalWetOutDate = fullLengthLiningWorkDetailsGateway.GetWetOutDateOriginal(workId);
                            DateTime? originalWetOutInstallDate = fullLengthLiningWorkDetailsGateway.GetWetOutInstallDateOriginal(workId);
                            string originalThickness = fullLengthLiningWorkDetailsGateway.GetInversionThicknessOriginal(workId);
                            decimal originalLengthToLine = fullLengthLiningWorkDetailsGateway.GetLengthToLineOriginal(workId);
                            decimal originalPlusExtra = fullLengthLiningWorkDetailsGateway.GetPlusExtraOriginal(workId);
                            decimal originalForTurnOffset = fullLengthLiningWorkDetailsGateway.GetForTurnOffsetOriginal(workId);
                            decimal originalLengthToWetOut = fullLengthLiningWorkDetailsGateway.GetLengthToWetOutOriginal(workId);
                            decimal originalTubeMaxColdHead = fullLengthLiningWorkDetailsGateway.GetTubeMaxColdHeadOriginal(workId);
                            decimal originalTubeMaxColdHeadPsi = fullLengthLiningWorkDetailsGateway.GetTubeMaxColdHeadPsiOriginal(workId);
                            decimal originalTubeMaxHotHead = fullLengthLiningWorkDetailsGateway.GetTubeMaxHotHeadOriginal(workId);
                            decimal originalTubeMaxHotHeadPsi = fullLengthLiningWorkDetailsGateway.GetTubeMaxHotHeadPsiOriginal(workId);
                            decimal originalTubeIdealHead = fullLengthLiningWorkDetailsGateway.GetTubeIdealHeadOriginal(workId);
                            decimal originalTubeIdealHeadPsi = fullLengthLiningWorkDetailsGateway.GetTubeIdealHeadPsiOriginal(workId);
                            decimal originalNetResinForTube = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeOriginal(workId);
                            decimal originalNetResinForTubeUsgals = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeUsgalsOriginal(workId);
                            string originalNetResinForTubeDrumsIns = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeDrumsInsOriginal(workId);
                            decimal originalNetResinForTubeLbsFt = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeLbsFtOriginal(workId);
                            decimal originalNetResinForTubeUsgFt = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeUsgFtOriginal(workId);
                            int originalExtraResinForMix = fullLengthLiningWorkDetailsGateway.GetExtraResinForMixOriginal(workId);
                            decimal originalExtraLbsForMix = fullLengthLiningWorkDetailsGateway.GetExtraLbsForMixOriginal(workId);
                            decimal originalTotalMixQuantity = fullLengthLiningWorkDetailsGateway.GetTotalMixQuantityOriginal(workId);
                            decimal originalTotalMixQuantityUsgals = fullLengthLiningWorkDetailsGateway.GetTotalMixQuantityUsgalsOriginal(workId);
                            string originalTotalMixQuantityDrumsIns = fullLengthLiningWorkDetailsGateway.GetTotalMixQuantityDrumsInsOriginal(workId);
                            string originalInversionType = fullLengthLiningWorkDetailsGateway.GetInversionTypeOriginal(workId);
                            decimal originalDepthOfInversionMH = fullLengthLiningWorkDetailsGateway.GetDepthOfInversionMHOriginal(workId);
                            decimal originalTubeForColumn = fullLengthLiningWorkDetailsGateway.GetTubeForColumnOriginal(workId);
                            decimal originalTubeForStartDry = fullLengthLiningWorkDetailsGateway.GetTubeForStartDryOriginal(workId);
                            decimal originalTotalTube = fullLengthLiningWorkDetailsGateway.GetTotalTubeOriginal(workId);
                            string originalDropTubeConnects = fullLengthLiningWorkDetailsGateway.GetDropTubeConnectsOriginal(workId);
                            decimal originalAllowsHeadTo = fullLengthLiningWorkDetailsGateway.GetAllowsHeadToOriginal(workId);
                            decimal originalRollerGap = fullLengthLiningWorkDetailsGateway.GetRollerGapOriginal(workId);
                            decimal originalHeightNeeded = fullLengthLiningWorkDetailsGateway.GetHeightNeededOriginal(workId);
                            string originalAvailable = fullLengthLiningWorkDetailsGateway.GetAvailableOriginal(workId);
                            string originalHoistHeight = fullLengthLiningWorkDetailsGateway.GetHoistHeightOriginal(workId);
                            string originalCommentsCipp = fullLengthLiningWorkDetailsGateway.GetCommentsCippOriginal(workId);
                            string originalResinLabel = fullLengthLiningWorkDetailsGateway.GetResinsLabelOriginal(workId);
                            string originalDrumContainsLabel = fullLengthLiningWorkDetailsGateway.GetDrumContainsLabelOriginal(workId);
                            string originalLinerTubeLabel = fullLengthLiningWorkDetailsGateway.GetLinerTubeLabelOriginal(workId);
                            string originalForLbDrumsLabel = fullLengthLiningWorkDetailsGateway.GetForLbDrumsLabelOriginal(workId);
                            string originalNetResinLabel = fullLengthLiningWorkDetailsGateway.GetNetResinLabelOriginal(workId);
                            string originalCatalystLabel = fullLengthLiningWorkDetailsGateway.GetCatalystLabelOriginal(workId);

                            // Wet Out new data
                            string newLinerTube = fullLengthLiningWorkDetailsGateway.GetLinerTube(workId);
                            int newResinID = fullLengthLiningWorkDetailsGateway.GetResinId(workId);
                            decimal newExcessResin = fullLengthLiningWorkDetailsGateway.GetExcessResin(workId);
                            string newPoundsDrums = fullLengthLiningWorkDetailsGateway.GetPoundsDrums(workId);
                            decimal newDrumDiameter = fullLengthLiningWorkDetailsGateway.GetDrumDiameter(workId);
                            decimal newHoistMaximumHeight = fullLengthLiningWorkDetailsGateway.GetHoistMaximumHeight(workId);
                            decimal newHoistMinimumHeight = fullLengthLiningWorkDetailsGateway.GetHoistMinimumHeight(workId);
                            decimal newDownDropTubeLenght = fullLengthLiningWorkDetailsGateway.GetDownDropTubeLenght(workId);
                            decimal newPumpHeightAboveGround = fullLengthLiningWorkDetailsGateway.GetPumpHeightAboveGround(workId);
                            int newTubeResinToFeltFactor = fullLengthLiningWorkDetailsGateway.GetTubeResinToFeltFactor(workId);
                            DateTime newDateOfSheet = fullLengthLiningWorkDetailsGateway.GetDateOfSheet(workId);
                            int newEmployeeID = fullLengthLiningWorkDetailsGateway.GetEmployeeId(workId);
                            string newRunDetails = fullLengthLiningWorkDetailsGateway.GetRunDetails(workId);
                            string newRunDetails2 = fullLengthLiningWorkDetailsGateway.GetRunDetails2(workId);
                            DateTime newWetOutDate = fullLengthLiningWorkDetailsGateway.GetWetOutDate(workId);
                            DateTime? newWetOutInstallDate = fullLengthLiningWorkDetailsGateway.GetWetOutInstallDate(workId);
                            string newThickness = fullLengthLiningWorkDetailsGateway.GetInversionThickness(workId);
                            decimal newLengthToLine = fullLengthLiningWorkDetailsGateway.GetLengthToLine(workId);
                            decimal newPlusExtra = fullLengthLiningWorkDetailsGateway.GetPlusExtra(workId);
                            decimal newForTurnOffset = fullLengthLiningWorkDetailsGateway.GetForTurnOffset(workId);
                            decimal newLengthToWetOut = fullLengthLiningWorkDetailsGateway.GetLengthToWetOut(workId);
                            decimal newTubeMaxColdHead = fullLengthLiningWorkDetailsGateway.GetTubeMaxColdHead(workId);
                            decimal newTubeMaxColdHeadPsi = fullLengthLiningWorkDetailsGateway.GetTubeMaxColdHeadPsi(workId);
                            decimal newTubeMaxHotHead = fullLengthLiningWorkDetailsGateway.GetTubeMaxHotHead(workId);
                            decimal newTubeMaxHotHeadPsi = fullLengthLiningWorkDetailsGateway.GetTubeMaxHotHeadPsi(workId);
                            decimal newTubeIdealHead = fullLengthLiningWorkDetailsGateway.GetTubeIdealHead(workId);
                            decimal newTubeIdealHeadPsi = fullLengthLiningWorkDetailsGateway.GetTubeIdealHeadPsi(workId);
                            decimal newNetResinForTube = fullLengthLiningWorkDetailsGateway.GetNetResinForTube(workId);
                            decimal newNetResinForTubeUsgals = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeUsgals(workId);
                            string newNetResinForTubeDrumsIns = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeDrumsIns(workId);
                            decimal newNetResinForTubeLbsFt = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeLbsFt(workId);
                            decimal newNetResinForTubeUsgFt = fullLengthLiningWorkDetailsGateway.GetNetResinForTubeUsgFt(workId);
                            int newExtraResinForMix = fullLengthLiningWorkDetailsGateway.GetExtraResinForMix(workId);
                            decimal newExtraLbsForMix = fullLengthLiningWorkDetailsGateway.GetExtraLbsForMix(workId);
                            decimal newTotalMixQuantity = fullLengthLiningWorkDetailsGateway.GetTotalMixQuantity(workId);
                            decimal newTotalMixQuantityUsgals = fullLengthLiningWorkDetailsGateway.GetTotalMixQuantityUsgals(workId);
                            string newTotalMixQuantityDrumsIns = fullLengthLiningWorkDetailsGateway.GetTotalMixQuantityDrumsIns(workId);
                            string newInversionType = fullLengthLiningWorkDetailsGateway.GetInversionType(workId);
                            decimal newDepthOfInversionMH = fullLengthLiningWorkDetailsGateway.GetDepthOfInversionMH(workId);
                            decimal newTubeForColumn = fullLengthLiningWorkDetailsGateway.GetTubeForColumn(workId);
                            decimal newTubeForStartDry = fullLengthLiningWorkDetailsGateway.GetTubeForStartDry(workId);
                            decimal newTotalTube = fullLengthLiningWorkDetailsGateway.GetTotalTube(workId);
                            string newDropTubeConnects = fullLengthLiningWorkDetailsGateway.GetDropTubeConnects(workId);
                            decimal newAllowsHeadTo = fullLengthLiningWorkDetailsGateway.GetAllowsHeadTo(workId);
                            decimal newRollerGap = fullLengthLiningWorkDetailsGateway.GetRollerGap(workId);
                            decimal newHeightNeeded = fullLengthLiningWorkDetailsGateway.GetHeightNeeded(workId);
                            string newAvailable = fullLengthLiningWorkDetailsGateway.GetAvailable(workId);
                            string newHoistHeight = fullLengthLiningWorkDetailsGateway.GetHoistHeight(workId);
                            string newCommentsCipp = fullLengthLiningWorkDetailsGateway.GetCommentsCipp(workId);
                            string newResinLabel = fullLengthLiningWorkDetailsGateway.GetResinsLabel(workId);
                            string newDrumContainsLabel = fullLengthLiningWorkDetailsGateway.GetDrumContainsLabel(workId);
                            string newLinerTubeLabel = fullLengthLiningWorkDetailsGateway.GetLinerTubeLabel(workId);
                            string newForLbDrumsLabel = fullLengthLiningWorkDetailsGateway.GetForLbDrumsLabel(workId);
                            string newNetResinLabel = fullLengthLiningWorkDetailsGateway.GetNetResinLabel(workId);
                            string newCatalystLabel = fullLengthLiningWorkDetailsGateway.GetCatalystLabel(workId);

                            string originalInversionComment = "";
                            string originalPipeType = "";
                            string originalPipeCondition = "";
                            string originalGroundMoisture = "";
                            decimal originalBoilerSize = 0m;
                            decimal originalPumpTotalCapacity = 0m;
                            decimal originalLayFlatSize = 0m;
                            decimal originalLayFlatQuantityTotal = 0m;
                            decimal originalWaterStartTemp = 0m;
                            decimal originalTemp1 =  0m;
                            decimal originalHoldAtT1 = 0m;
                            decimal originalTempT2 = 0m;
                            decimal originalCookAtT2 = 0m;
                            decimal originalCoolDownFor = 0m;
                            decimal originalCoolToTemp = 0m;
                            decimal originalDropInPipeRun = 0m;
                            decimal originalPipeSlopOf = 0m;
                            decimal originalF45F120 = 0m;
                            decimal originalHold = 0m;
                            decimal originalF120F185 = 0m;
                            decimal originalCookTime = 0m;
                            decimal originalCoolTime = 0m;
                            decimal originalAproxTotal = 0m;
                            decimal originalWaterChangesPerHour = 0m;
                            decimal originalReturnWaterVelocity = 0m;
                            decimal originalLayflatBackPressure = 0m;
                            decimal originalPumpLiftAtIdealHead = 0m;
                            decimal originalWaterToFillLinerColumn = 0m;
                            decimal originalWaterPerFit = 0m;
                            string originalInstallationResults = "";
                            string originalInversionLinerTubeLabel = "";
                            string originalHeadsIdealLabel = "";
                            string originalPumpingAndCirculationLabel = "";

                            // Inversion new Data
                            string newInversionComment = "";
                            string newPipeType = "";
                            string newPipeCondition = "";
                            string newGroundMoisture = "";
                            decimal newBoilerSize = 0m;
                            decimal newPumpTotalCapacity = 0m;
                            decimal newLayFlatSize = 0m;
                            decimal newLayFlatQuantityTotal = 0m;
                            decimal newWaterStartTemp = 0m;
                            decimal newTemp1 = 0m;
                            decimal newHoldAtT1 = 0m;
                            decimal newTempT2 = 0m;
                            decimal newCookAtT2 = 0m;
                            decimal newCoolDownFor = 0m;
                            decimal newCoolToTemp = 0m;
                            decimal newDropInPipeRun = 0m;
                            decimal newPipeSlopOf = 0m;
                            decimal newF45F120 = 0m;
                            decimal newHold = 0m;
                            decimal newF120F185 = 0m;
                            decimal newCookTime = 0m;
                            decimal newCoolTime = 0m;
                            decimal newAproxTotal = 0m;
                            decimal newWaterChangesPerHour = 0m;
                            decimal newReturnWaterVelocity = 0m;
                            decimal newLayflatBackPressure = 0m;
                            decimal newPumpLiftAtIdealHead = 0m;
                            decimal newWaterToFillLinerColumn = 0m;
                            decimal newWaterPerFit = 0m;
                            string newInstallationResults = "";
                            string newInversionLinerTubeLabel = "";
                            string newHeadsIdealLabel = "";
                            string newPumpingAndCirculationLabel = "";

                            if (includeInversionInformation)
                            {
                                // InversionData original values
                                originalInversionComment = fullLengthLiningWorkDetailsGateway.GetInversionCommentOriginal(workId);
                                originalPipeType = fullLengthLiningWorkDetailsGateway.GetPipeTypeOriginal(workId);
                                originalPipeCondition = fullLengthLiningWorkDetailsGateway.GetPipeConditionOriginal(workId);
                                originalGroundMoisture = fullLengthLiningWorkDetailsGateway.GetGroundMoistureOriginal(workId);
                                originalBoilerSize = fullLengthLiningWorkDetailsGateway.GetBoilerSizeOriginal(workId);
                                originalPumpTotalCapacity = fullLengthLiningWorkDetailsGateway.GetPumpTotalCapacityOriginal(workId);
                                originalLayFlatSize = fullLengthLiningWorkDetailsGateway.GetLayFlatSizeOriginal(workId);
                                originalLayFlatQuantityTotal = fullLengthLiningWorkDetailsGateway.GetLayFlatQuantityTotalOriginal(workId);
                                originalWaterStartTemp = fullLengthLiningWorkDetailsGateway.GetWaterStartTempOriginal(workId);
                                originalTemp1 = fullLengthLiningWorkDetailsGateway.GetTemp1Original(workId);
                                originalHoldAtT1 = fullLengthLiningWorkDetailsGateway.GetHoldAtT1Original(workId);
                                originalTempT2 = fullLengthLiningWorkDetailsGateway.GetTempT2Original(workId);
                                originalCookAtT2 = fullLengthLiningWorkDetailsGateway.GetCookAtT2Original(workId);
                                originalCoolDownFor = fullLengthLiningWorkDetailsGateway.GetCoolDownForOriginal(workId);
                                originalCoolToTemp = fullLengthLiningWorkDetailsGateway.GetCoolToTempOriginal(workId);
                                originalDropInPipeRun = fullLengthLiningWorkDetailsGateway.GetDropInPipeRunOriginal(workId);
                                originalPipeSlopOf = fullLengthLiningWorkDetailsGateway.GetPipeSlopOfOriginal(workId);
                                originalF45F120 = fullLengthLiningWorkDetailsGateway.GetF45F120Original(workId);
                                originalHold = fullLengthLiningWorkDetailsGateway.GetHoldOriginal(workId);
                                originalF120F185 = fullLengthLiningWorkDetailsGateway.GetF120F185Original(workId);
                                originalCookTime = fullLengthLiningWorkDetailsGateway.GetCookTimeOriginal(workId);
                                originalCoolTime = fullLengthLiningWorkDetailsGateway.GetCoolTimeOriginal(workId);
                                originalAproxTotal = fullLengthLiningWorkDetailsGateway.GetAproxTotalOriginal(workId);
                                originalWaterChangesPerHour = fullLengthLiningWorkDetailsGateway.GetWaterChangesPerHourOriginal(workId);
                                originalReturnWaterVelocity = fullLengthLiningWorkDetailsGateway.GetReturnWaterVelocityOriginal(workId);
                                originalLayflatBackPressure = fullLengthLiningWorkDetailsGateway.GetLayflatBackPressureOriginal(workId);
                                originalPumpLiftAtIdealHead = fullLengthLiningWorkDetailsGateway.GetPumpLiftAtIdealHeadOriginal(workId);
                                originalWaterToFillLinerColumn = fullLengthLiningWorkDetailsGateway.GetWaterToFillLinerColumnOriginal(workId);
                                originalWaterPerFit = fullLengthLiningWorkDetailsGateway.GetWaterPerFitOriginal(workId);
                                originalInstallationResults = fullLengthLiningWorkDetailsGateway.GetInstallationResultsOriginal(workId);
                                originalInversionLinerTubeLabel = fullLengthLiningWorkDetailsGateway.GetInversionLinerTubeLabelOriginal(workId);
                                originalHeadsIdealLabel = fullLengthLiningWorkDetailsGateway.GetHeadsIdealLabelOriginal(workId);
                                originalPumpingAndCirculationLabel = fullLengthLiningWorkDetailsGateway.GetPumpingAndCirculationLabelOriginal(workId);

                                // Inversion new Data
                                newInversionComment = fullLengthLiningWorkDetailsGateway.GetInversionComment(workId);
                                newPipeType = fullLengthLiningWorkDetailsGateway.GetPipeType(workId);
                                newPipeCondition = fullLengthLiningWorkDetailsGateway.GetPipeCondition(workId);
                                newGroundMoisture = fullLengthLiningWorkDetailsGateway.GetGroundMoisture(workId);
                                newBoilerSize = fullLengthLiningWorkDetailsGateway.GetBoilerSize(workId);
                                newPumpTotalCapacity = fullLengthLiningWorkDetailsGateway.GetPumpTotalCapacity(workId);
                                newLayFlatSize = fullLengthLiningWorkDetailsGateway.GetLayFlatSize(workId);
                                newLayFlatQuantityTotal = fullLengthLiningWorkDetailsGateway.GetLayFlatQuantityTotal(workId);
                                newWaterStartTemp = fullLengthLiningWorkDetailsGateway.GetWaterStartTemp(workId);
                                newTemp1 = fullLengthLiningWorkDetailsGateway.GetTemp1(workId);
                                newHoldAtT1 = fullLengthLiningWorkDetailsGateway.GetHoldAtT1(workId);
                                newTempT2 = fullLengthLiningWorkDetailsGateway.GetTempT2(workId);
                                newCookAtT2 = fullLengthLiningWorkDetailsGateway.GetCookAtT2(workId);
                                newCoolDownFor = fullLengthLiningWorkDetailsGateway.GetCoolDownFor(workId);
                                newCoolToTemp = fullLengthLiningWorkDetailsGateway.GetCoolToTemp(workId);
                                newDropInPipeRun = fullLengthLiningWorkDetailsGateway.GetDropInPipeRun(workId);
                                newPipeSlopOf = fullLengthLiningWorkDetailsGateway.GetPipeSlopOf(workId);
                                newF45F120 = fullLengthLiningWorkDetailsGateway.GetF45F120(workId);
                                newHold = fullLengthLiningWorkDetailsGateway.GetHold(workId);
                                newF120F185 = fullLengthLiningWorkDetailsGateway.GetF120F185(workId);
                                newCookTime = fullLengthLiningWorkDetailsGateway.GetCookTime(workId);
                                newCoolTime = fullLengthLiningWorkDetailsGateway.GetCoolTime(workId);
                                newAproxTotal = fullLengthLiningWorkDetailsGateway.GetAproxTotal(workId);
                                newWaterChangesPerHour = fullLengthLiningWorkDetailsGateway.GetWaterChangesPerHour(workId);
                                newReturnWaterVelocity = fullLengthLiningWorkDetailsGateway.GetReturnWaterVelocity(workId);
                                newLayflatBackPressure = fullLengthLiningWorkDetailsGateway.GetLayflatBackPressure(workId);
                                newPumpLiftAtIdealHead = fullLengthLiningWorkDetailsGateway.GetPumpLiftAtIdealHead(workId);
                                newWaterToFillLinerColumn = fullLengthLiningWorkDetailsGateway.GetWaterToFillLinerColumn(workId);
                                newWaterPerFit = fullLengthLiningWorkDetailsGateway.GetWaterPerFit(workId);
                                newInstallationResults = fullLengthLiningWorkDetailsGateway.GetInstallationResults(workId);
                                newInversionLinerTubeLabel = fullLengthLiningWorkDetailsGateway.GetInversionLinerTubeLabel(workId);
                                newHeadsIdealLabel = fullLengthLiningWorkDetailsGateway.GetHeadsIdealLabel(workId);
                                newPumpingAndCirculationLabel = fullLengthLiningWorkDetailsGateway.GetPumpingAndCirculationLabel(workId);
                            }

                            // Get All sectionIds for insert
                            string runDetails = row.RunDetails;
                            string[] runDetailsList = runDetails.Split('>');

                            for (int i = 0; i < runDetailsList.Length; i++)
                            {
                                AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                                string sectionId = runDetailsList[i].ToString();
                                assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                                int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                                WorkGateway workGateway = new WorkGateway();
                                int newWorkId = 0;
                                workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                                if (workGateway.Table.Rows.Count > 0)
                                {
                                    newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                                }

                                WorkFullLengthLiningWetOutGateway workFullLengthLiningWetOutGatewayForReview = new WorkFullLengthLiningWetOutGateway();
                                workFullLengthLiningWetOutGatewayForReview.LoadByWorkId(newWorkId, companyId);
                                if (workFullLengthLiningWetOutGatewayForReview.Table.Rows.Count > 0)
                                {
                                    // Update work with cipp information
                                    UpdateWorkWetOutInformation(newWorkId, originalLinerTube, originalResinID, originalExcessResin, originalPoundsDrums, originalDrumDiameter, originalHoistMaximumHeight, originalHoistMinimumHeight, originalDownDropTubeLenght, originalPumpHeightAboveGround, originalTubeResinToFeltFactor, originalDateOfSheet, originalEmployeeID, originalRunDetails, originalRunDetails2, originalWetOutDate, originalWetOutInstallDate, originalThickness, originalLengthToLine, originalPlusExtra, originalForTurnOffset, originalLengthToWetOut, originalTubeMaxColdHead, originalTubeMaxColdHeadPsi, originalTubeMaxHotHead, originalTubeMaxHotHeadPsi, originalTubeIdealHead, originalTubeIdealHeadPsi, originalNetResinForTube, originalNetResinForTubeUsgals, originalNetResinForTubeDrumsIns, originalNetResinForTubeLbsFt, originalNetResinForTubeUsgFt, originalExtraResinForMix, originalExtraLbsForMix, originalTotalMixQuantity, originalTotalMixQuantityUsgals, originalTotalMixQuantityDrumsIns, originalInversionType, originalDepthOfInversionMH, originalTubeForColumn, originalTubeForStartDry, originalTotalTube, originalDropTubeConnects, originalAllowsHeadTo, originalRollerGap, originalHeightNeeded, originalAvailable, originalHoistHeight, originalCommentsCipp, originalResinLabel, originalDrumContainsLabel, originalLinerTubeLabel, originalForLbDrumsLabel, originalNetResinLabel, originalCatalystLabel, originalInversionComment, originalPipeType, originalPipeCondition, originalGroundMoisture, originalBoilerSize, originalPumpTotalCapacity, originalLayFlatSize, originalLayFlatQuantityTotal, originalWaterStartTemp, originalTemp1, originalHoldAtT1, originalTempT2, originalCookAtT2, originalCoolDownFor, originalCoolToTemp, originalDropInPipeRun, originalPipeSlopOf, originalF45F120, originalHold, originalF120F185, originalCookTime, originalCoolTime, originalAproxTotal, originalWaterChangesPerHour, originalReturnWaterVelocity, originalLayflatBackPressure, originalPumpLiftAtIdealHead, originalWaterToFillLinerColumn, originalWaterPerFit, originalInstallationResults, originalInversionLinerTubeLabel, originalHeadsIdealLabel, originalPumpingAndCirculationLabel, false, companyId, newLinerTube, newResinID, newExcessResin, newPoundsDrums, newDrumDiameter, newHoistMaximumHeight, newHoistMinimumHeight, newDownDropTubeLenght, newPumpHeightAboveGround, newTubeResinToFeltFactor, newDateOfSheet, newEmployeeID, newRunDetails, newRunDetails2, newWetOutDate, newWetOutInstallDate, newThickness, newLengthToLine, newPlusExtra, newForTurnOffset, newLengthToWetOut, newTubeMaxColdHead, newTubeMaxColdHeadPsi, newTubeMaxHotHead, newTubeMaxHotHeadPsi, newTubeIdealHead, newTubeIdealHeadPsi, newNetResinForTube, newNetResinForTubeUsgals, newNetResinForTubeDrumsIns, newNetResinForTubeLbsFt, newNetResinForTubeUsgFt, newExtraResinForMix, newExtraLbsForMix, newTotalMixQuantity, newTotalMixQuantityUsgals, newTotalMixQuantityDrumsIns, newInversionType, newDepthOfInversionMH, newTubeForColumn, newTubeForStartDry, newTotalTube, newDropTubeConnects, newAllowsHeadTo, newRollerGap, newHeightNeeded, newAvailable, newHoistHeight, newCommentsCipp, newResinLabel, newDrumContainsLabel, newLinerTubeLabel, newForLbDrumsLabel, newNetResinLabel, newCatalystLabel, newInversionComment, newPipeType, newPipeCondition, newGroundMoisture, newBoilerSize, newPumpTotalCapacity, newLayFlatSize, newLayFlatQuantityTotal, newWaterStartTemp, newTemp1, newHoldAtT1, newTempT2, newCookAtT2, newCoolDownFor, newCoolToTemp, newDropInPipeRun, newPipeSlopOf, newF45F120, newHold, newF120F185, newCookTime, newCoolTime, newAproxTotal, newWaterChangesPerHour, newReturnWaterVelocity, newLayflatBackPressure, newPumpLiftAtIdealHead, newWaterToFillLinerColumn, newWaterPerFit, newInstallationResults, newInversionLinerTubeLabel, newHeadsIdealLabel, newPumpingAndCirculationLabel, false, companyId, includeWetOutInformation, includeInversionInformation);
                                }
                                else
                                {
                                    // ... Insert wet out data
                                    string inversionComment = fullLengthLiningWorkDetailsGateway.GetInversionComment(workId);
                                    string installationResults = fullLengthLiningWorkDetailsGateway.GetInstallationResults(workId);
                                    DateTime? wetOutInstallDate = fullLengthLiningWorkDetailsGateway.GetWetOutInstallDate(workId);

                                    InsertWorkWetOutInformation(newWorkId, row.LinerTube, row.ResinID, row.ExcessResin, row.PoundsDrums, row.DrumDiameter, row.HoistMaximumHeight, row.HoistMinimumHeight, row.DownDropTubeLenght, row.PumpHeightAboveGround, row.TubeResinToFeltFactor, row.DateOfSheet, row.EmployeeID, row.RunDetails, row.RunDetails2, row.WetOutDate, wetOutInstallDate, row.Thickness, row.LengthToLine, row.PlusExtra, row.ForTurnOffset, row.LengthToWetOut, row.TubeMaxColdHead, row.TubeMaxColdHeadPsi, row.TubeMaxHotHead, row.TubeMaxHotHeadPsi, row.TubeIdealHead, row.TubeIdealHeadPsi, row.NetResinForTube, row.NetResinForTubeUsgals, row.NetResinForTubeDrumsIns, row.NetResinForTubeLbsFt, row.NetResinForTubeUsgFt, row.ExtraResinForMix, row.ExtraLbsForMix, row.TotalMixQuantity, row.TotalMixQuantityUsgals, row.TotalMixQuantityDrumsIns, row.InversionType, row.DepthOfInversionMH, row.TubeForColumn, row.TubeForStartDry, row.TotalTube, row.DropTubeConnects, row.AllowsHeadTo, row.RollerGap, row.HeightNeeded, row.Available, row.HoistHeight, row.CommentsCipp, row.ResinsLabel, row.DrumContainsLabel, row.LinerTubeLabel, row.ForLbDrumsLabel, row.NetResinLabel, row.CatalystLabel, inversionComment, row.PipeType, row.PipeCondition, row.GroundMoisture, row.BoilerSize, row.PumpTotalCapacity, row.LayFlatSize, row.LayFlatQuantityTotal, row.WaterStartTemp, row.Temp1, row.HoldAtT1, row.TempT2, row.CookAtT2, row.CoolDownFor, row.CoolToTemp, row.DropInPipeRun, row.PipeSlopOf, row.F45F120, row.Hold, row.F120F185, row.CookTime, row.CoolTime, row.AproxTotal, row.WaterChangesPerHour, row.ReturnWaterVelocity, row.LayflatBackPressure, row.PumpLiftAtIdealHead, row.WaterToFillLinerColumn, row.WaterPerFit, installationResults, row.InversionLinerTubeLabel, row.HeadsIdealLabel, row.PumpingAndCirculationLabel, row.Deleted, row.COMPANY_ID, includeWetOutInformation, includeInversionInformation);
                                }
                            }
                        }
                        else
                        {
                            // Get All sectionIds for insert
                            string runDetails = row.RunDetails;
                            string[] runDetailsList = runDetails.Split('>');

                            for (int i = 0; i < runDetailsList.Length; i++)
                            {
                                AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                                string sectionId = runDetailsList[i].ToString();
                                assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                                int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                                WorkGateway workGateway = new WorkGateway();
                                workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                                if (workGateway.Table.Rows.Count > 0)
                                {
                                    int newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);

                                    // ... Insert wet out data
                                    string inversionComment = fullLengthLiningWorkDetailsGateway.GetInversionComment(workId);
                                    string installationResults = fullLengthLiningWorkDetailsGateway.GetInstallationResults(workId);
                                    DateTime? wetOutInstallDate = fullLengthLiningWorkDetailsGateway.GetWetOutInstallDate(workId);

                                    InsertWorkWetOutInformation(newWorkId, row.LinerTube, row.ResinID, row.ExcessResin, row.PoundsDrums, row.DrumDiameter, row.HoistMaximumHeight, row.HoistMinimumHeight, row.DownDropTubeLenght, row.PumpHeightAboveGround, row.TubeResinToFeltFactor, row.DateOfSheet, row.EmployeeID, row.RunDetails, row.RunDetails2, row.WetOutDate, wetOutInstallDate, row.Thickness, row.LengthToLine, row.PlusExtra, row.ForTurnOffset, row.LengthToWetOut, row.TubeMaxColdHead, row.TubeMaxColdHeadPsi, row.TubeMaxHotHead, row.TubeMaxHotHeadPsi, row.TubeIdealHead, row.TubeIdealHeadPsi, row.NetResinForTube, row.NetResinForTubeUsgals, row.NetResinForTubeDrumsIns, row.NetResinForTubeLbsFt, row.NetResinForTubeUsgFt, row.ExtraResinForMix, row.ExtraLbsForMix, row.TotalMixQuantity, row.TotalMixQuantityUsgals, row.TotalMixQuantityDrumsIns, row.InversionType, row.DepthOfInversionMH, row.TubeForColumn, row.TubeForStartDry, row.TotalTube, row.DropTubeConnects, row.AllowsHeadTo, row.RollerGap, row.HeightNeeded, row.Available, row.HoistHeight, row.CommentsCipp, row.ResinsLabel, row.DrumContainsLabel, row.LinerTubeLabel, row.ForLbDrumsLabel, row.NetResinLabel, row.CatalystLabel, inversionComment, row.PipeType, row.PipeCondition, row.GroundMoisture, row.BoilerSize, row.PumpTotalCapacity, row.LayFlatSize, row.LayFlatQuantityTotal, row.WaterStartTemp, row.Temp1, row.HoldAtT1, row.TempT2, row.CookAtT2, row.CoolDownFor, row.CoolToTemp, row.DropInPipeRun, row.PipeSlopOf, row.F45F120, row.Hold, row.F120F185, row.CookTime, row.CoolTime, row.AproxTotal, row.WaterChangesPerHour, row.ReturnWaterVelocity, row.LayflatBackPressure, row.PumpLiftAtIdealHead, row.WaterToFillLinerColumn, row.WaterPerFit, installationResults, row.InversionLinerTubeLabel, row.HeadsIdealLabel, row.PumpingAndCirculationLabel, row.Deleted, row.COMPANY_ID, includeWetOutInformation, includeInversionInformation);
                                }
                            }
                        }
                    }

                    // Update JL Section WorkID
                    int sectionWorkId = 0;
                    WorkGateway workGatewayForJL = new WorkGateway();
                    workGatewayForJL.LoadByProjectIdAssetIdWorkType(projectId, sectionAssetId, "Junction Lining Section", companyId);

                    if (workGatewayForJL.Table.Rows.Count > 0)
                    {
                        sectionWorkId = workGatewayForJL.GetWorkId(sectionAssetId, "Junction Lining Section", projectId);
                        WorkJunctionLiningSectionGateway workJunctionLiningSectionGateway = new WorkJunctionLiningSectionGateway();
                        workJunctionLiningSectionGateway.LoadByWorkId(sectionWorkId, companyId);

                        // get old values of section
                        int numLats = workJunctionLiningSectionGateway.GetNumLats(sectionWorkId);
                        int notLinedYet = workJunctionLiningSectionGateway.GetNotLinedYet(sectionWorkId);
                        bool allMeasured = workJunctionLiningSectionGateway.GetAllMeasured(sectionWorkId);
                        bool deleted = workJunctionLiningSectionGateway.GetDeleted(sectionWorkId);
                        string issueWithLaterals = workJunctionLiningSectionGateway.GetIssueWithLaterals(sectionWorkId);
                        int notMeasuredYet = workJunctionLiningSectionGateway.GetNotMeasuredYet(sectionWorkId);
                        int notDeliveredYet = workJunctionLiningSectionGateway.GetNotDeliveredYet(sectionWorkId);
                        string trafficControl = workJunctionLiningSectionGateway.GetTrafficControl(sectionWorkId);
                        string trafficControlDetails = workJunctionLiningSectionGateway.GetTrafficControlDetails(sectionWorkId);
                        bool standardBypass = workJunctionLiningSectionGateway.GetStandardBypass(sectionWorkId);
                        string standardBypassComments = workJunctionLiningSectionGateway.GetStandardBypassComments(sectionWorkId);
                        int availableToLine = workJunctionLiningSectionGateway.GetAvailableToLine(sectionWorkId);

                        WorkJunctionLiningSection workJunctionLiningSection = new WorkJunctionLiningSection(null);
                        workJunctionLiningSection.UpdateDirect(sectionWorkId, numLats, notLinedYet, allMeasured, deleted, issueWithLaterals, notMeasuredYet, notDeliveredYet, companyId, trafficControl, trafficControlDetails, standardBypass, standardBypassComments, availableToLine, numLats, notLinedYet, allMeasured, issueWithLaterals, notMeasuredYet, notDeliveredYet, newTrafficControl, newTrafficControlDetails, newStandardBypass, newStandardBypassComments, availableToLine);
                    }
                }
            }
        }
        // ////////////////////////////////////////////////////////////////////////
        // PRIVATE METHODS
        //
        /// <summary>
        /// GetSectionNextId
        /// </summary>
        /// <param name="countryId">countryId</param>
        /// <param name="provinceId">provinceId</param>
        /// <param name="countyId">countyId</param>
        /// <param name="cityId">cityId</param>
        /// <param name="companyId">companyId</param>
        /// <returns>nextSectionId</returns>
        private string GetSectionNextId(Int64? countryId, Int64? provinceId, Int64? countyId, Int64? cityId, int companyId)
        {
            string nextSectionId = "";

            // Locations string
            string location = "";
            if (!countryId.HasValue) location = location + "0."; else location = location + countryId.ToString() + ".";
            if (!provinceId.HasValue) location = location + "0."; else location = location + provinceId.ToString() + ".";
            if (!countyId.HasValue) location = location + "0."; else location = location + countyId.ToString() + ".";
            if (!cityId.HasValue) location = location + "0"; else location = location + cityId.ToString();
            location = location + "-";

            // AutoIncrement part
            // ... load lastSectionId
            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
            string lastSectionId = "";

            if (assetSewerSectionGateway.GetLastSectionId(countryId, provinceId, countyId, cityId, companyId) != "")
            {
                lastSectionId = assetSewerSectionGateway.GetLastSectionId(countryId, provinceId, countyId, cityId, companyId);
            }

            // ... Get next secuential number
            string newSecuentialNumber = "";
            int lastSectionId_ = 0;

            // ... If there is a last section id
            if (lastSectionId != "")
            {
                string[] lastSectionIdSplit = lastSectionId.Split('-');

                if (lastSectionIdSplit.Length >= 2)
                {
                    lastSectionId_ = Int32.Parse(lastSectionIdSplit[1]);
                }
            }
            newSecuentialNumber = GetSectionIncrement(lastSectionId_);

            // next sectionId
            nextSectionId = location + newSecuentialNumber;

            return nextSectionId;
        }
Example #11
0
        protected void btnWetOutCalculateOnClick(object sender, EventArgs e)
        {
            int companyId = Int32.Parse(hdfCompanyId.Value);
            double pi = double.Parse(Decimal.Round(decimal.Parse(Math.PI.ToString()),4).ToString());
            double breakSize = 10; // in
            double factor1 = 0.99;
            double factor2 = 0.01;

            // ...Section values
            // ... ... tube size = confirmed Size
            Distance confirmedSizeDistance = new Distance(tbxConfirmedSize.Text);
            double confirmedSize = 0;
            string[] confirmedSizeString = confirmedSizeDistance.ToStringInEng1().Split('\"');

            if (!confirmedSizeDistance.ToStringInEng1().Contains("'"))
            {
                if (Validator.IsValidDouble(tbxConfirmedSize.Text))
                {
                    confirmedSize = double.Parse(tbxConfirmedSize.Text);
                }
                else
                {
                    confirmedSize = double.Parse(confirmedSizeString[0]);
                }
            }
            else
            {
                confirmedSize = Math.Ceiling(confirmedSizeDistance.ToDoubleInEng3() * 12);
            }

            // ... For Calculations
            // ... ... Validate setup data
            if ((ddlWetOutDataLinerTube.SelectedValue != "") && (ddlWetOutDataResins.SelectedValue != "-1") && (tbxWetOutDataExcessResin.Text != "") && (ddlWetOutDataPoundsDrums.SelectedValue != "(Select)") && (tbxWetOutDataDrumDiameter.Text != "") && (tbxWetOutDataHoistMaximumHeight.Text != "") && (tbxWetOutDataHoistMinimumHeight.Text != "") && (tbxWetOutDataDownDropTubeLength.Text != "") && (tbxWetOutDataPumpHeightAboveGround.Text != "") && (tbxWetOutDataTubeResinToFeltFactor.Text != ""))
            {
                // ... ... validate wet out data
                if ((ddlThickness.SelectedValue != "") && (tbxWetOutDataPlusExtra.Text != "") && (tbxWetOutDataForTurnOffset.Text != "") && (tbxWetOutDataExtraResinForMix.Text != "") && (ddlWetOutDataInversionType.SelectedValue != "(Select)") && (tbxWetOutDataDepthOfInversionMH.Text != "") && (tbxWetOutDataTubeForColumn.Text != "") && (tbxWetOutDataTubeForStartDry.Text != "") && (tbxWetOutDataRollerGap.Text != ""))
                {
                    // Lenght to line of all sections (sume of lenght of asset table)
                    string newRunDetails = "";
                    foreach (ListItem lst in cbxlSectionId.Items)
                    {
                        if (lst.Selected)
                        {
                            newRunDetails = newRunDetails + lst.Value + ">";
                        }
                    }
                    newRunDetails = newRunDetails.Substring(0, newRunDetails.Length - 1);
                    string[] runDetailsList = newRunDetails.Split('>');

                    double lengthToLine = 0d;
                    for (int i = 0; i < runDetailsList.Length; i++)
                    {
                        AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                        string sectionId = runDetailsList[i].ToString();
                        if (sectionId != "-1")
                        {
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            Distance lengthDistance = new Distance(assetSewerSectionGateway.GetLength(assetId));
                            lengthToLine = lengthToLine + lengthDistance.ToDoubleInEng3();
                        }
                    }
                    tbxWetOutDataLengthToLine.Text = decimal.Round(decimal.Parse(lengthToLine.ToString()), 1).ToString();
                    tbxInversionDataRunLength.Text = decimal.Round(decimal.Parse(lengthToLine.ToString()), 1).ToString();

                    // ... Inversion run details
                    for (int i = 0; i < runDetailsList.Length; i++)
                    {
                        cbxlInversionDataSectionId.Items.FindByValue(runDetailsList[i]).Selected = true;
                    }

                    // .. Resin Information
                    int resinId = Int32.Parse(ddlWetOutDataResins.SelectedValue);
                    WorkFullLengthLiningResinsGateway workFullLengthLiningResinsGateway = new WorkFullLengthLiningResinsGateway();
                    workFullLengthLiningResinsGateway.LoadByResinId(resinId, companyId);

                    string resinMake = workFullLengthLiningResinsGateway.GetResinMake(resinId);
                    string resinType = workFullLengthLiningResinsGateway.GetResinType(resinId);
                    string resinNumber = workFullLengthLiningResinsGateway.GetResinNumber(resinId);
                    double lbUsg = double.Parse(workFullLengthLiningResinsGateway.GetLbUsg(resinId).ToString());
                    int lbDrums = Int32.Parse(workFullLengthLiningResinsGateway.GetLbDrums(resinId).ToString());
                    double activeResin = double.Parse(workFullLengthLiningResinsGateway.GetActiveResin(resinId).ToString());
                    string applyCatalystTo = workFullLengthLiningResinsGateway.GetApplyCatalystTo(resinId);
                    double filter = double.Parse(workFullLengthLiningResinsGateway.GetFilter(resinId).ToString());

                    // ... Calculations
                    // ... ... LengthtToWetOut
                    Double plusExtra = double.Parse(tbxWetOutDataPlusExtra.Text);
                    Double forTurnOffset = double.Parse(tbxWetOutDataForTurnOffset.Text);

                    Double lengthtToWetOut = lengthToLine + plusExtra + forTurnOffset;
                    tbxWetOutDataLengthtToWetOut.Text = decimal.Round(decimal.Parse(lengthtToWetOut.ToString()), 1).ToString();
                    tbxInversionDataWetOutLenght.Text = decimal.Round(decimal.Parse(lengthtToWetOut.ToString()), 1).ToString();

                    // ... ... Resin label
                    lblWetOutDataResinGray.Text = "RESIN: " + resinMake + resinType + resinNumber + ", " + lbUsg + "lbs/usg, " + activeResin.ToString() + " % Active Resin ";

                    // ... ... Lb drum label
                    lblWetOutDataLbDrumsGrey.Text = "For " + lbDrums.ToString() + " lb drums";

                    // ... ... Drum contains label
                    // ... ... Drum Fill Height Information
                    double usgDrum = lbDrums / lbUsg;
                    double lbsPerUsg = lbUsg;
                    double lbsPerDrum = lbDrums;
                    double usgalsPerDrum = lbsPerDrum / lbsPerUsg;
                    double drumInsideDiameter = Double.Parse(tbxWetOutDataDrumDiameter.Text);
                    double drumInsideDiameterPow = Math.Pow(drumInsideDiameter, 2);
                    double usgalsPerDrumInch = (1 * (pi * drumInsideDiameterPow) / 4) * 0.004329;
                    double lbsPerDrumInch = usgalsPerDrumInch * lbsPerUsg;
                    double drumFillHeightShouldBeApprox = lbsPerDrum / lbsPerDrumInch;

                    if (ddlWetOutDataPoundsDrums.SelectedValue == "Pounds & Drums")  // OP 1
                    {
                        lblWetOutDataDrumContainsGray.Text = "Drum contains: " + lbDrums.ToString() + "lbs / " + Decimal.Round(decimal.Parse(usgDrum.ToString()), 1).ToString() + "usg.  Full drum level: Approx " + Decimal.Round(Decimal.Parse(drumFillHeightShouldBeApprox.ToString()), 1).ToString() + "ins.";
                    }
                    else
                    {
                        lblWetOutDataDrumContainsGray.Text = "-";
                    }

                    // ... ... Liner Tube
                    string linerTube = ddlWetOutDataLinerTube.SelectedValue;
                    double tubeThickness = Double.Parse(ddlThickness.SelectedValue);
                    lblWetOutDataLinerTubeGray.Text = "LINER TUBE: " + linerTube.ToString() + ", Inversion. Tube Size: " + confirmedSize.ToString() + " ins x " + tubeThickness.ToString() + " mm.";
                    tbxInversionDataLinerSize.Text = confirmedSize + " ins x " + tubeThickness + " mm";
                    lblInversionDataSubtitle.Text = ddlWetOutDataLinerTube.SelectedValue;

                    double maxCold = -1;
                    double maxColdExact = -1;
                    double maxColdRounded = -1;

                    double maxHot = -1;
                    double maxHotExact = -1;
                    double maxHotRounded = -1;

                    double idealHead = -1;
                    double idealHeadExact = -1;
                    double idealHeadRounded = -1;

                    switch (linerTube)
                    {
                        case "Applied Felts":
                            double sizeAppliedFelts = confirmedSize * 25.4;

                            // ...  ... For max cold
                            maxColdExact = (tubeThickness / sizeAppliedFelts) * 308 * 3.2808;
                            maxColdRounded = Double.Parse(Decimal.Round(Decimal.Parse(maxColdExact.ToString()), 1).ToString());
                            maxCold = maxColdRounded;

                            // ... ... For max hot
                            maxHotExact = (tubeThickness / sizeAppliedFelts) * 269 * 3.2808;
                            maxHotRounded = Double.Parse(Decimal.Round(Decimal.Parse(maxHotExact.ToString()), 1).ToString());
                            maxHot = maxHotRounded;

                            // ... ... For Idead head
                            double sizeToUseAppliedFelts = 0d;
                            if (confirmedSize > breakSize)
                            {
                                sizeToUseAppliedFelts = sizeAppliedFelts;
                            }
                            else
                            {
                                sizeToUseAppliedFelts = sizeAppliedFelts - 25;
                            }
                            idealHeadExact = (tubeThickness / sizeToUseAppliedFelts) * 201 * 3.2808;
                            idealHeadRounded = Double.Parse(Decimal.Round(Decimal.Parse(idealHeadExact.ToString()), 1).ToString());
                            idealHead = idealHeadRounded;
                            break;

                        case "Novapipe":
                            // ...  ... For max cold
                            maxColdExact = (39 * tubeThickness) / confirmedSize;
                            maxColdRounded = double.Parse(Decimal.Round(Decimal.Parse(maxColdExact.ToString()), 1).ToString());
                            maxCold = maxColdRounded;

                            // ... ... For max hot
                            maxHotExact = (33.1 * tubeThickness) / confirmedSize;
                            maxHotRounded = Double.Parse(Decimal.Round(Decimal.Parse(maxHotExact.ToString()), 1).ToString());
                            maxHot = maxHotRounded;

                            // ... ... For Idead head
                            idealHeadExact = (25.76 * tubeThickness) / confirmedSize;
                            idealHeadRounded = Double.Parse(Decimal.Round(Decimal.Parse(idealHeadExact.ToString()), 1).ToString());
                            idealHead = idealHeadRounded;
                            break;

                        case "Liner Products":
                            // ...  ... For max cold
                            maxColdExact = (tubeThickness / confirmedSize) * 34.057;
                            maxColdRounded = double.Parse(Decimal.Round(Decimal.Parse(maxColdExact.ToString()), 1).ToString());
                            maxCold = maxColdRounded;

                            // ... ... For max hot
                            maxHotExact = (tubeThickness / confirmedSize) * 28.381;
                            maxHotRounded = Double.Parse(Decimal.Round(Decimal.Parse(maxHotExact.ToString()), 1).ToString());
                            maxHot = maxHotRounded;

                            // ... ... For Idead head
                            idealHeadExact = (tubeThickness / confirmedSize) * 20.888;
                            idealHeadRounded = Double.Parse(Decimal.Round(Decimal.Parse(idealHeadExact.ToString()), 1).ToString());
                            idealHead = idealHeadRounded;
                            break;

                        case "Generic":
                            double sizeGeneric = confirmedSize * 25.4;

                            // ...  ... For max cold
                            maxColdExact = (tubeThickness / sizeGeneric) * 308 * 3.2808;
                            maxColdRounded = Double.Parse(Decimal.Round(Decimal.Parse(maxColdExact.ToString()), 1).ToString());
                            maxCold = maxColdRounded;

                            // ... ... For max hot
                            maxHotExact = (tubeThickness / sizeGeneric) * 269 * 3.2808;
                            maxHotRounded = Double.Parse(Decimal.Round(Decimal.Parse(maxHotExact.ToString()), 1).ToString());
                            maxHot = maxHotRounded;

                            // ... ... For Idead head
                            double sizeToUseGeneric = 0d;

                            if (confirmedSize > breakSize)
                            {
                                sizeToUseGeneric = sizeGeneric;
                            }
                            else
                            {
                                sizeToUseGeneric = sizeGeneric - 25;
                            }
                            idealHeadExact = (tubeThickness / sizeToUseGeneric) * 201 * 3.2808;
                            idealHeadRounded = Double.Parse(Decimal.Round(Decimal.Parse(idealHeadExact.ToString()), 1).ToString());
                            idealHead = idealHeadRounded;
                            break;

                        case "Tube 5 No Data":
                            // ...  ... For max cold
                            maxCold = -1;

                            // ... ... For max hot
                            maxHot = -1;

                            // ... ... For Idead head
                            idealHead = -1;
                            break;

                        case "Tube 6 No Data":
                            // ...  ... For max cold
                            maxCold = -1;

                            // ... ... For max hot
                            maxHot = -1;

                            // ... ... For Idead head
                            idealHead = -1;
                            break;
                    }

                    // ... ... .... Tube Max Cold Head, Tube Max Cold head PSI
                    if (maxCold != -1)
                    {
                        tbxWetOutDataTubeMaxColdHead.Text = maxCold.ToString();
                        double maxColdPsi = 0.434 * maxCold;
                        tbxWetOutDataTubeMaxColdHeadPSI.Text = decimal.Round(decimal.Parse(maxColdPsi.ToString()), 1).ToString();
                    }
                    else
                    {
                        tbxWetOutDataTubeMaxColdHead.Text = "NA";
                        tbxWetOutDataTubeMaxColdHeadPSI.Text = "NA";
                    }

                    // ... ... .... Tube Max Hot Head, Tube Max Hot Head PSI
                    if (maxHot != -1)
                    {
                        tbxWetOutDataTubeMaxHotHead.Text = maxHot.ToString();
                        double maxHotPsi = 0.434 * maxHot;
                        tbxWetOutDataTubeMaxHotHeadPSI.Text = decimal.Round(decimal.Parse(maxHotPsi.ToString()), 1).ToString();
                    }
                    else
                    {
                        tbxWetOutDataTubeMaxHotHead.Text = "NA";
                        tbxWetOutDataTubeMaxHotHeadPSI.Text = "NA";
                    }

                    // ... ... .... Tube Ideal Head, Tube Ideal Head PSI
                    if (idealHead != -1)
                    {
                        tbxWetOutDataTubeIdealHead.Text = idealHead.ToString();
                        double idealHeadPsi = 0.434 * idealHead;
                        tbxWetOutDataTubeIdealHeadPSI.Text = Decimal.Round(Decimal.Parse(idealHeadPsi.ToString()), 1).ToString();
                    }
                    else
                    {
                        tbxWetOutDataTubeIdealHead.Text = "NA";
                        tbxWetOutDataTubeIdealHeadPSI.Text = "NA";
                    }

                    // ... ... Net Resins
                    // ... ... ... For net resin for tube lbs ft(lbs/ft)
                    double lbsFt = 0d;
                    double usgFt = 0d;
                    double excessResin = Double.Parse(tbxWetOutDataExcessResin.Text)/100;
                    double tubeResinToFeltFactor = Double.Parse(tbxWetOutDataTubeResinToFeltFactor.Text);

                    switch (linerTube)
                    {
                        case "Applied Felts":
                            // ... ... .... For lbsFt
                            double fromSetUp = tubeResinToFeltFactor / 100;
                            double t = tubeThickness / 25.4;
                            double id = confirmedSize - 2 * t;
                            double area = pi * (Math.Pow(confirmedSize, 2) - Math.Pow(id, 2)) / 4;
                            double v1 = 12 * area * 0.004329;
                            double v2 = v1 * fromSetUp;
                            double v3 = v2 * excessResin;
                            double v4 = v3 + v2;
                            lbsFt = v4 * lbUsg;

                            // ... ... .... For usgFt
                            usgFt = v4;
                            break;

                        case "Novapipe":
                            // ... ... .... For lbsFt
                            double fromSetUpNovapipe = tubeResinToFeltFactor / 100;
                            double tNovapipe = tubeThickness / 25.4;
                            double idNovapipe = confirmedSize - 2 * tNovapipe;
                            double areaNovapipe = pi * (Math.Pow(confirmedSize, 2) - Math.Pow(idNovapipe, 2)) / 4;
                            double v1Novapipe = 12 * areaNovapipe * 0.004329;
                            double v2Novapipe = v1Novapipe * fromSetUpNovapipe;
                            double v3Novapipe = v2Novapipe * excessResin;
                            double v4Novapipe = v3Novapipe + v2Novapipe;
                            lbsFt = v4Novapipe * lbUsg;

                            // ... ... .... For usgFt
                            usgFt = v4Novapipe;
                            break;

                        case "Liner Products":
                            // ... ... .... For lbsFt
                            double lbft3 = lbUsg / 0.1337;
                            double specificGravity = double.Parse(decimal.Round(decimal.Parse((lbft3 / 62.4).ToString()), 3).ToString());

                            double midCalc = 0d;
                            if (confirmedSize > 8)
                            {
                                midCalc = 261.5184;
                            }
                            else
                            {
                                midCalc = 217.932;
                            }

                            Decimal confirmedSizeDecimal = Decimal.Parse(confirmedSize.ToString());
                            Decimal firstDivision = confirmedSizeDecimal / 24;
                            Double firstDivisionDouble = Double.Parse(firstDivision.ToString());
                            double midCalc2 = RoundUp(firstDivisionDouble, 0);

                            Decimal secondDivision = confirmedSizeDecimal / 30;
                            double secondDivisionDouble = double.Parse(secondDivision.ToString());
                            double midCalc3 = RoundUp(secondDivisionDouble, 0);
                            double estimatedResinMultiplier = (((545.2 * (tubeThickness / 25.4) * ((((confirmedSize - 2 * (tubeThickness / 25.4)) * pi * 0.92) / pi) + (tubeThickness / 25.4)) + (midCalc * midCalc3 * ((tubeThickness - 3) / 25.4)) + (55.98 * midCalc2)) * specificGravity) / 453.59) * 0.92;

                            double excessResinAdded = excessResin * estimatedResinMultiplier;
                            lbsFt = excessResinAdded + estimatedResinMultiplier;

                            // ... ... .... For usgFt
                            double totalResin = excessResinAdded + estimatedResinMultiplier;
                            usgFt = totalResin / lbUsg;
                            break;

                        case "Generic":
                            // ... ... .... For lbsFt
                            double fromSetUpGeneric = tubeResinToFeltFactor / 100;
                            double tGeneric = tubeThickness / 25.4;
                            double idGeneric = confirmedSize - 2 * tGeneric;
                            double areaGeneric = pi * (Math.Pow(confirmedSize, 2) - Math.Pow(idGeneric, 2)) / 4;
                            double v1Generic = 12 * areaGeneric * 0.004329;
                            double v2Generic = v1Generic * fromSetUpGeneric;
                            double v3Generic = v2Generic * excessResin;
                            double v4Generic = v3Generic + v2Generic;
                            lbsFt = v4Generic * lbUsg;

                            // ... ... .... For usgFt
                            usgFt = v4Generic;
                            break;

                        case "Tube 5 No Data":
                            // ... ... .... For lbsFt
                            lbsFt = -1;

                            // ... ... .... For usgFt
                            usgFt = -1;
                            break;

                        case "Tube 6 No Data":
                            // ... ... .... For lbsFt
                            lbsFt = -1;

                            // ... ... .... For usgFt
                            usgFt = -1;
                            break;
                    }

                    double netResinForTubeLbsFt = Double.Parse(Decimal.Round(Decimal.Parse(lbsFt.ToString()), 2).ToString());
                    tbxWetOutDataNetResinForTubeLbsFt.Text = netResinForTubeLbsFt.ToString();

                    // ... ... ... For net resin for tube (lbs)
                    double netResinForTube = lengthtToWetOut * netResinForTubeLbsFt;
                    tbxWetOutDataNetResinForTube.Text = Decimal.Round(decimal.Parse(netResinForTube.ToString()), 0).ToString();

                    // ... ... ... For net resin for tube usg/ft (usg/ft)
                    double netResinForTubeUsgFt = Double.Parse(Decimal.Round(Decimal.Parse(usgFt.ToString()), 3).ToString());
                    tbxWetOutDataNetResinForTubeUsgFt.Text = netResinForTubeUsgFt.ToString();

                    // ... ... ... For net resin for tube (usgals)
                    double netResinForTubeUsgals = lengthtToWetOut * netResinForTubeUsgFt;
                    tbxWetOutDataNetResinForTubeUsgals.Text = Decimal.Round(decimal.Parse(netResinForTubeUsgals.ToString()), 1).ToString();

                    // ... ... ... For net resin for tube drums Ins
                    // ... ... ... .... Drum Fill Height Information
                    double exactDrumsRequired = netResinForTube / lbDrums;
                    double rounddownToWholeDrums = RoundDown(exactDrumsRequired, 0);
                    double remainingPartDrum = exactDrumsRequired - rounddownToWholeDrums;
                    double fillHeight = drumFillHeightShouldBeApprox;
                    double remainingPartDrumInInches = fillHeight * remainingPartDrum;
                    double drumFillHeight = fillHeight;
                    double difference = drumFillHeight - remainingPartDrumInInches;

                    double drumsToUseInString = 0d;
                    if (remainingPartDrum > factor1)
                    {
                        drumsToUseInString = rounddownToWholeDrums + 1;
                    }
                    else
                    {
                        if (remainingPartDrum < factor2)
                        {
                            drumsToUseInString = rounddownToWholeDrums;
                        }
                        else
                        {
                            drumsToUseInString = rounddownToWholeDrums;
                        }
                    }

                    double inchesToUseInString = 0;
                    if (remainingPartDrum > factor1)
                    {
                        inchesToUseInString = 0;
                    }
                    else
                    {
                        if (remainingPartDrum < factor2)
                        {
                            inchesToUseInString = 0;
                        }
                        else
                        {
                            inchesToUseInString = remainingPartDrumInInches;
                        }
                    }

                    string stringForDrumInches = "= " + drumsToUseInString + " Drum + " + Decimal.Round(Decimal.Parse(inchesToUseInString.ToString()), 1).ToString() + " ins";
                    string stringForDrumsInches = "= " + drumsToUseInString + " Drums + " + Decimal.Round(Decimal.Parse(inchesToUseInString.ToString()), 1).ToString() + " ins";

                    string stringToCarry = "";
                    if (drumsToUseInString == 1)
                    {
                        stringToCarry = stringForDrumInches;
                    }
                    else
                    {
                        stringToCarry = stringForDrumsInches;
                    }
                    tbxWetOutDataNetResinForTubeDrumsIns.Text = stringToCarry.ToString();

                    // ... ... Newt resin label
                    lblWetOutDataNetResinGrey.Text = "Net resin is amount required in the tube after wet out complete and tube ready for installation. Includes excess at " + excessResin * 100 + "%";

                    // ... ... Extra lbs for Mix
                    double extraResinForMix = Double.Parse(tbxWetOutDataExtraResinForMix.Text);
                    double extraLbsForMix = netResinForTube * extraResinForMix/100;
                    tbxWetOutDataExtraLbsForMix.Text = Decimal.Round(decimal.Parse(extraLbsForMix.ToString()), 0).ToString();

                    // ... ... Total Mix Quantity
                    double totalMixQuantity = (1 + extraResinForMix / 100) * netResinForTube;
                    tbxWetOutDataTotalMixQuantity.Text = Decimal.Round(decimal.Parse(totalMixQuantity.ToString()), 0).ToString();

                    // .... ... Total Mix Quantity usgals
                    double totalMixQuantityUsgals = (1 + extraResinForMix / 100) * netResinForTubeUsgals;
                    tbxWetOutDataTotalMixQuantityUsgals.Text = Decimal.Round(decimal.Parse(totalMixQuantityUsgals.ToString()), 1).ToString(); ;

                    // ... ... Total Mix Quantity Drums Ins
                    if (ddlWetOutDataPoundsDrums.SelectedValue == "Pounds & Drums")  // OP 1
                    {
                        tbxWetOutDataTotalMixQuantityDrumsIns.Text = stringToCarry;
                    }
                    else
                    {
                        tbxWetOutDataTotalMixQuantityDrumsIns.Text = "-";
                    }

                    // ... ... Catalyst label
                    string catalystLabel = "";

                    if (applyCatalystTo == "Active Resin")
                    {
                        double activeResinVal = totalMixQuantity * activeResin/100;
                        double activeResinInDrum = lbsPerDrum * activeResin/100;
                        if (ddlWetOutDataPoundsDrums.SelectedValue == "Pounds & Drums")  // OP 1
                        {
                            catalystLabel = "Catalyst % applied to weight of Active Resin = " + decimal.Round(decimal.Parse(activeResinVal.ToString()), 0) + " lbs  (" + decimal.Round(decimal.Parse(activeResinInDrum.ToString()), 1).ToString() + " lbs per drum )";
                        }
                        else
                        {
                            catalystLabel = "Catalyst % applied to weight of Active Resin = " + decimal.Round(decimal.Parse(activeResinVal.ToString()), 0) + " lbs";
                        }
                    }
                    else
                    {
                        if (applyCatalystTo == "Active Resin & Filter")
                        {
                            if (ddlWetOutDataPoundsDrums.SelectedValue == "Pounds & Drums")  // OP 1
                            {
                                catalystLabel = "Catalyst % applied to weight of Active Resin and Filler = " + decimal.Round(decimal.Parse(totalMixQuantity.ToString()), 0).ToString() + " lbs (" + decimal.Round(decimal.Parse(lbsPerDrum.ToString()), 1).ToString() + " lbs per drum )";
                            }
                            else
                            {
                                catalystLabel = "Catalyst % applied to weight of Active Resin and Filler = " + decimal.Round(decimal.Parse(totalMixQuantity.ToString()), 0).ToString() + " lbs";
                            }
                        }
                        else
                        {
                            catalystLabel = "ERROR IN RESIN LIST ENTRY(S). CATALYST QTYS ARE INCORRECT!";
                        }
                    }

                    lblWetOutDataCatalystGrey.Text = catalystLabel;

                    // ... ... Total Tube
                    double tubeForColumn = Double.Parse(tbxWetOutDataTubeForColumn.Text);
                    double tubeForStartDry = Double.Parse(tbxWetOutDataTubeForStartDry.Text);
                    double totalTube = tubeForColumn + tubeForStartDry + lengthtToWetOut; ;
                    tbxWetOutDataTotalTube.Text = Decimal.Round(decimal.Parse(totalTube.ToString()), 1).ToString();

                    // ... ... Drop Tube Connects
                    string inversionType = ddlWetOutDataInversionType.SelectedValue;
                    double depthOfInversionMH = Double.Parse(tbxWetOutDataDepthOfInversionMH.Text);

                    if (inversionType == "Bottom")
                    {
                        if (depthOfInversionMH == tubeForColumn)
                        {
                            tbxWetOutDataDropTubeConnects.Text = "At MH lid";
                        }
                        else
                        {
                            if (depthOfInversionMH > tubeForColumn)
                            {
                                tbxWetOutDataDropTubeConnects.Text = "Below MH lid";
                            }
                            else
                            {
                                tbxWetOutDataDropTubeConnects.Text = "Above MH lid";
                            }
                        }
                    }
                    else
                    {
                        tbxWetOutDataDropTubeConnects.Text = "-";
                    }

                    // ... ... Allows Head To
                    double allowsToHeadTo = 0d;
                    double downDropTubeLength = Double.Parse(tbxWetOutDataDownDropTubeLength.Text);
                    if (inversionType == "Top")
                    {
                        allowsToHeadTo = tubeForColumn;
                        tbxWetOutDataAllowsHeadTo.Text = allowsToHeadTo.ToString();
                    }
                    else
                    {
                        if (inversionType == "Bottom")
                        {
                            allowsToHeadTo = tubeForColumn + downDropTubeLength;
                            tbxWetOutDataAllowsHeadTo.Text = allowsToHeadTo.ToString();
                        }
                        else
                        {
                            tbxWetOutDataAllowsHeadTo.Text = "NA";
                        }
                    }

                    // ... ... Height Needed
                    double heightNeeded = 0;
                    if (tbxWetOutDataAllowsHeadTo.Text != "NA")
                    {
                        heightNeeded = allowsToHeadTo - depthOfInversionMH;
                        tbxWetOutDataHeightNeeded.Text = heightNeeded.ToString();
                    }

                    // ... ... Available
                    double hoistMinimumHeight = Double.Parse(tbxWetOutDataHoistMinimumHeight.Text);
                    double hoistMaximunHeight = Double.Parse(tbxWetOutDataHoistMaximumHeight.Text);
                    tbxWetOutDataAvailable.Text = hoistMinimumHeight.ToString() + " ft to " + hoistMaximunHeight.ToString() + " ft";

                    // ... ... Hoist Height?
                    if (heightNeeded > hoistMaximunHeight)
                    {
                        tbxWetOutDataHoistHeight.Text = "Too High";
                    }
                    else
                    {
                        if (heightNeeded < hoistMinimumHeight)
                        {
                            tbxWetOutDataHoistHeight.Text = "Too Low";
                        }
                        else
                        {
                            tbxWetOutDataHoistHeight.Text = "OK";
                        }
                    }

                    // ... ... Warning
                    if ((tbxWetOutDataHoistHeight.Text == "OK") || (tbxWetOutDataHoistHeight.Text == ""))
                    {
                        lblWetOutDataWarning.Visible = false;
                    }
                    else
                    {
                        lblWetOutDataWarning.Visible = true;
                    }

                    // ... ... graphic labels
                    lblWetOutDataDimensionLabel.Text = confirmedSize + " ins x " + tubeThickness + " mm Tube";
                    lblWetOutDataTotalTubeLengthlabel.Text = "Total Tube Length " + tbxWetOutDataTotalTube.Text + " ft";
                    lblWetOutDataForColumnLabel.Text = tbxWetOutDataTubeForColumn.Text + " ft  for Column";
                    lblWetOutDataDryFtLabel.Text = "Dry " + tbxWetOutDataTubeForStartDry.Text + " ft";
                    lblWetOutDataWetOutLengthlabel.Text = "Wet-Out Length " + tbxWetOutDataLengthtToWetOut.Text + " ft";
                    lblWetOutDataDryFtEndLabel.Text = "Dry " + tbxWetOutDataTubeForColumn.Text + " ft";
                    lblWetOutDataTailEndlabel.Text = "Tail End";
                    lblWetOutDataColumnEndlabel.Text = "Column End";
                    lblWetOutDataRollerGapLabel.Text = "Roller Gap " + tbxWetOutDataRollerGap.Text + " mm";

                    // ... ... Inversion Liner Size
                    tbxInversionDataLinerSize.Text = confirmedSize + " ins x " + tubeThickness + " mm";

                    // ... ... Inversion Gray texts
                    lblInversionDataSubtitle.Text = "For: " + ddlWetOutDataLinerTube.SelectedValue;
                    lblInversionDataLinerInfoGrey.Text = linerTube + " tube with " + resinMake + " " + resinType + " " + resinNumber + " resin";
                    lblInversionDataHeadsGrey.Text = "Heads Ideal: " + tbxWetOutDataTubeIdealHead.Text + " ft (" + tbxWetOutDataTubeIdealHeadPSI.Text + ");  Max Hot: " + tbxWetOutDataTubeMaxHotHead.Text + " ft (" + tbxWetOutDataTubeMaxHotHeadPSI.Text + "psi);  Max Cold: " + tbxWetOutDataTubeMaxColdHead.Text + " ft (" + tbxWetOutDataTubeMaxColdHeadPSI.Text + ")";

                    // ... ... .... Validate data for calcs
                    if ((ddlInversionDataInversionPipeType.SelectedValue != "(Select)") && (ddlInversionDataPipeCondition.SelectedValue != "(Select)") && (ddlInversionDataGroundMoisture.SelectedValue != "(Select)") && (tbxInversionDataBoilerSize.Text != "") && (tbxInversionDataPumpsTotalCapacity.Text != "") && (tbxInversionDataLayflatSize.Text != "") && (tbxInversionDataLayflatQuantityTotal.Text != ""))
                    {
                        if ((tbxInversionDataWaterStartTempTs.Text != "") && (tbxInversionDataTempT1.Text != "") && (tbxInversionDataHoldAtT1For.Text != "") && (tbxInversionDataTempT2.Text != "") && (tbxInversionDataCookAtT2For.Text != "") && (tbxInversionDataCoolDownFor.Text != "") && (tbxInversionDataCoolToTemp.Text != "") && (tbxInversionDataDropInPipeRun.Text != ""))
                        {
                            // ... ... Inversion Pipe Slope Of
                            Decimal dropInPipeRun = Decimal.Parse(tbxInversionDataDropInPipeRun.Text);
                            Decimal pipeSlopeOf = Decimal.Round(dropInPipeRun, 1) / Decimal.Parse(lengthToLine.ToString())*100;
                            tbxInversionDataPipeSlopeOf.Text = Decimal.Round(decimal.Parse(pipeSlopeOf.ToString()), 2).ToString();

                            // ... ... Inversion 45F120F
                            double odsIns = confirmedSize;
                            double tIns = (tubeThickness / 25.4);
                            double inversionId = (odsIns - 2 * tIns);
                            lblInversionData45F120F.Text = tbxInversionDataWaterStartTempTs.Text + "°F-" + tbxInversionDataTempT1.Text + "°F (hr)";

                            // ... ... Inversion Hold
                            tbxInversionDataHold.Text = Decimal.Round(decimal.Parse(tbxInversionDataHoldAtT1For.Text), 1).ToString();

                            // ... ... Inversion 120F185F
                            lblInversionData120F185F.Text = tbxInversionDataTempT1.Text + "°F-" + tbxInversionDataTempT2.Text + "°F (hr)";
                            double pipeTypeFactor = 0d;
                            string pipeType = ddlInversionDataInversionPipeType.SelectedValue;
                            if (pipeType == "Clay") pipeTypeFactor = 0.90;
                            if (pipeType == "Concrete") pipeTypeFactor = 0.80;
                            if (pipeType == "Brick") pipeTypeFactor = 1.00;

                            double pipeConditionFactor = 0d;
                            string pipeCondition = ddlInversionDataPipeCondition.SelectedValue;
                            if (pipeCondition == "Good") pipeConditionFactor = 1.00;
                            if (pipeCondition == "Fair") pipeConditionFactor = 0.90;
                            if (pipeCondition == "Poor") pipeConditionFactor = 0.80;
                            if (pipeCondition == "Badly Broken") pipeConditionFactor = 0.60;

                            double groundMoistureFactor = 0d;
                            string groundMoisture = ddlInversionDataGroundMoisture.SelectedValue;
                            if (groundMoisture == "Dry") groundMoistureFactor = 1.00;
                            if (groundMoisture == "Typical") groundMoistureFactor = 0.80;
                            if (groundMoisture == "Wet") groundMoistureFactor = 0.60;

                            double boilerSize = double.Parse(tbxInversionDataBoilerSize.Text);
                            double matFactorF1 = pipeTypeFactor;
                            double conditionFactorF2 = pipeConditionFactor;
                            double groundMoistureFactorF3 = groundMoistureFactor;
                            double overallFactorF3 = matFactorF1 * conditionFactorF2 * groundMoistureFactorF3;
                            double ianCorrectionFactor = 0.85;
                            double tubeMaxColdHead = double.Parse(tbxWetOutDataTubeMaxColdHead.Text);
                            double totalTubee = tubeMaxColdHead + lengthToLine;
                            double idForLinerFt = inversionId / 12;
                            double particalCalc = Math.Pow(idForLinerFt, 2);
                            double areaFt2 = (pi * particalCalc) / 4;
                            double ft3PerFt = areaFt2 * 1;
                            double usgPerFt = 7.481 * ft3PerFt;
                            double totalUsg = totalTubee * usgPerFt;
                            double cubFt = totalUsg / 7.481;
                            double punds = cubFt * 62.4;
                            double temp1 = double.Parse(tbxInversionDataTempT1.Text);
                            double temp2 = double.Parse(tbxInversionDataTempT2.Text);
                            double ts = double.Parse(tbxInversionDataWaterStartTempTs.Text);
                            double t1t2Btus = ((temp2 - temp1) * punds);
                            double netBtu = (ianCorrectionFactor * overallFactorF3 * boilerSize);
                            double timeHrs = (t1t2Btus / netBtu);
                            tbxInversionData120F185F.Text = Decimal.Round(decimal.Parse(timeHrs.ToString()), 1).ToString();

                            double tsT1Btus = (temp1 - ts) * punds;
                            double timeHrs1 = tsT1Btus / netBtu;
                            tbxInversionData45F120F.Text = Decimal.Round(decimal.Parse(timeHrs1.ToString()), 1).ToString();

                            // ... ... Inversion Cook Time
                            tbxInversionDataCookTime.Text = Decimal.Round(decimal.Parse(tbxInversionDataCookAtT2For.Text), 1).ToString(); ;

                            // ... ... Inversion Cool Time
                            tbxInversionDataCoolTime.Text = Decimal.Round(decimal.Parse(tbxInversionDataCoolDownFor.Text), 1).ToString();

                            // ... ... Inversion Aprox Total
                            double f45f120 = timeHrs1;
                            double hold = Double.Parse(tbxInversionDataHold.Text);
                            double f120f185 = timeHrs;
                            double cookTime = Double.Parse(tbxInversionDataCookTime.Text);
                            double coolTime = Double.Parse(tbxInversionDataCoolTime.Text);
                            tbxInversionDataAproxTotal.Text = Decimal.Round(decimal.Parse((f45f120 + hold + f120f185 + cookTime + coolTime).ToString()), 1).ToString();

                            // ... ... Inversion pumping circulation subtitle
                            double pumpsTotalCapacity = double.Parse(tbxInversionDataPumpsTotalCapacity.Text);
                            lblInversionDataPumpingCirculationSubtitle.Text = "Pumping and Circulation Parameters at " + tbxInversionDataPumpsTotalCapacity.Text + "usgpm (= " + (pumpsTotalCapacity * 60).ToString() + " usgph)";

                            // ... ... Inversion water changes per hour
                            double totalLinerInWaterCol = totalUsg;
                            double pumpsTotalCapacity60 = pumpsTotalCapacity * 60;
                            double changesPerHour = pumpsTotalCapacity60 / totalLinerInWaterCol;
                            decimal waterChangesPerHour = Decimal.Round(decimal.Parse(changesPerHour.ToString()), 2);
                            tbxInversionDataWaterChangesPerHour.Text = Decimal.Round(decimal.Parse(waterChangesPerHour.ToString()), 2).ToString();

                            // ... ... Inversion return water velocity
                            double layFlatQuantity = double.Parse(tbxInversionDataLayflatQuantityTotal.Text);
                            double layFlatSize = double.Parse(tbxInversionDataLayflatSize.Text);
                            double middleCalcEach = Math.Pow(layFlatSize, 2);
                            double areaLayFlatft2Each = ((pi * (middleCalcEach)) / 4) / 144;
                            double areaLayflatft2 = areaLayFlatft2Each * layFlatQuantity;
                            double qInCfs = pumpsTotalCapacity / 448.8;
                            double netQAreaFt2 = areaFt2 - areaLayflatft2;
                            double returnFlowArea = netQAreaFt2;
                            double returnsWaterVelocity = qInCfs / returnFlowArea;
                            tbxInversionDataReturnWaterVelocity.Text = Decimal.Round(decimal.Parse(returnsWaterVelocity.ToString()), 2).ToString();

                            // ... ... Inversion layflat back pressure
                            double c = 130;
                            double dFt = layFlatSize / 12;
                            double flowPerLayFlat = pumpsTotalCapacity / layFlatQuantity;
                            double qCfm = flowPerLayFlat * 0.1336;
                            double qCfs = qCfm / 60;
                            double middleCalcAreaIns2 = Math.Pow(layFlatSize, 2);
                            double areaIns2 = (pi * middleCalcAreaIns2) / 4;
                            double areaft2 = areaIns2 / 144;
                            double vFtS = qCfs / areaft2;
                            double wetOutLenght = double.Parse(tbxInversionDataWetOutLenght.Text);
                            double totalLfLength = tubeMaxColdHead + wetOutLenght;
                            double hfFtL = (Math.Pow((1.816 / c), 1.852) * ((totalLfLength / (Math.Pow(dFt, 1.167))) * Math.Pow(vFtS, 1.852)));
                            double hfPsiL = hfFtL * 0.4335;
                            double layFlatBackPressure = hfPsiL;
                            tbxInversionDataLayflatBackPressure.Text = Decimal.Round(decimal.Parse(layFlatBackPressure.ToString()), 1).ToString();

                            // ... ... Inversion pump lift at ideal head
                            double pumpHeightAboveGround = double.Parse(tbxWetOutDataPumpHeightAboveGround.Text);
                            double pumpFromInvert = pumpHeightAboveGround + depthOfInversionMH;
                            double lift = pumpFromInvert - idealHead;
                            double pumpLiftAtIdealHead = lift;
                            tbxInversionDataPumpLiftAtIdealHead.Text = Decimal.Round(decimal.Parse(pumpLiftAtIdealHead.ToString()), 1).ToString();

                            // ... ... Inversion water to fil liner column
                            double waterToFillLinerColumn = totalUsg;
                            tbxInversionDataWaterToFillLinerColumn.Text = Decimal.Round(decimal.Parse(waterToFillLinerColumn.ToString()), 0).ToString();

                            // ... ... Inversion water per ft
                            double waterPerFt = usgPerFt;
                            tbxInversionDataWaterPerFit.Text = Decimal.Round(decimal.Parse(waterPerFt.ToString()), 2).ToString();

                            // ... ... graphic labels
                            lblInversionDataMaxColdForTubeLabel.Text = tbxWetOutDataTubeMaxColdHead.Text + " ft = Max Cold for tube";
                            lblInversionDataMaxHotForTubeLabel.Text = tbxWetOutDataTubeMaxHotHead.Text + " ft = Max Hot for tube";
                            lblInversionDataIdelForTubeLabel.Text = tbxWetOutDataTubeIdealHead.Text + " ft = Ideal for tube";

                            double maxColdForTubeEnd = double.Parse(tbxWetOutDataTubeMaxColdHead.Text) + double.Parse(tbxInversionDataDropInPipeRun.Text);
                            lblInversionDataMaxColdForTubeEndLabel.Text = decimal.Round(decimal.Parse(maxColdForTubeEnd.ToString()), 1).ToString() + " ft";

                            double maxHotForTubeEnd = double.Parse(tbxWetOutDataTubeMaxHotHead.Text) + double.Parse(tbxInversionDataDropInPipeRun.Text);
                            lblInversionDataMaxHotForTubeEndLabel.Text = decimal.Round(decimal.Parse(maxHotForTubeEnd.ToString()), 1).ToString() + " ft";

                            double idealForTubeEnd = double.Parse(tbxWetOutDataTubeIdealHead.Text) + double.Parse(tbxInversionDataDropInPipeRun.Text);
                            lblInversionDataIdelForTubeEndLabel.Text = decimal.Round(decimal.Parse(idealForTubeEnd.ToString()), 1).ToString() + " ft";

                            lblInversionDataPumpHeightLabel.Text = "   " + tbxWetOutDataPumpHeightAboveGround.Text + " ft";
                            lblInversionDataLinerSizeLabel.Text = confirmedSize.ToString() + " ins x" + tubeThickness.ToString() + " Liner";

                            lblInversionDataRunLengthLabel.Text = "Run Length: " + tbxInversionDataRunLength.Text + " ft; Fall: " + tbxInversionDataDropInPipeRun.Text + " ft";
                            lblInversionDataDepthOfInversionMHLabel.Text = "   " + tbxWetOutDataDepthOfInversionMH.Text + " ft";
                            lblInversionDataEndLabel.Text = "End";
                        }
                    }
                }
            }
        }
        private bool ProcessBulkUpload(string fName, out string wincapBulkUploadResultMessage)
        {
            bool wincapBulkUploadProccessed = true;
            wincapBulkUploadResultMessage = "";

            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();

            AppSettingsReader appSettingReader = new AppSettingsReader();
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';Data Source=" + fName + ";";
            OleDbConnection connection = new OleDbConnection(excelConnectionString);
            OleDbCommand command = null;
            OleDbDataReader dataReader = null;

            try
            {
                ArrayList validatedIds = new ArrayList();

                connection.Open();
                command = new OleDbCommand("select * from [sections]", connection);
                dataReader = command.ExecuteReader();

                int id = 0;

                while (dataReader.Read())
                {
                    if (!IsEmptyRow(dataReader))
                    {
                        string sectionId = "";
                        string state = "";
                        string distance = "";
                        string direction = "";
                        string videoDistance = "";
                        string clockPosition = "";
                        string distanceToCentre = "";
                        string reverseSetup = "";
                        string comments = "";

                        string dataCell = null;
                        string dataCellToUpper = null;

                        //--- ... fill section row
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            dataCell = dataReader.GetValue(i).ToString().Trim();
                            dataCellToUpper = dataReader.GetValue(i).ToString().Trim().ToUpper();

                            switch (dataReader.GetName(i).Trim())
                            {
                                case "SectionID":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        sectionId = dataCell;
                                    }
                                    else
                                    {
                                        sectionId = "";
                                    }
                                    break;

                                case "State":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        state = dataCell;
                                    }
                                    else
                                    {
                                        state = "";
                                    }
                                    break;

                                case "Direction":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        direction = dataCell;
                                    }
                                    else
                                    {
                                        direction = "";
                                    }
                                    break;

                                case "Distance":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            distance = dataCell;
                                        }
                                        else
                                        {
                                            wincapBulkUploadResultMessage = "Invalid value in 'Distance' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincapBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        distance = "";
                                    }
                                    break;

                                case "VideoDistance":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            videoDistance = dataCell;
                                        }
                                        else
                                        {
                                            wincapBulkUploadResultMessage = "Invalid value in 'VideoDistance' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincapBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        videoDistance = "";
                                    }
                                    break;

                                case "ClockPosition":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        clockPosition = dataCell;
                                    }
                                    else
                                    {
                                        clockPosition = "";
                                    }
                                    break;

                                case "DistanceToCentre":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            distanceToCentre = dataCell;
                                        }
                                        else
                                        {
                                            wincapBulkUploadResultMessage = "Invalid value in 'DistanceToCentre' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincapBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        distanceToCentre = "";
                                    }
                                    break;

                                case "ReverseSetup":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            reverseSetup = dataCell;
                                        }
                                        else
                                        {
                                            wincapBulkUploadResultMessage = "Invalid value in 'ReverseSetup' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincapBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        reverseSetup = "";
                                    }
                                    break;

                                case "Comments":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        comments = dataCell;
                                    }
                                    else
                                    {
                                        comments = "";
                                    }
                                    break;

                                default:
                                    wincapBulkUploadResultMessage = "Invalid column name '" + dataReader.GetName(i) + "' in section data range.";
                                    wincapBulkUploadProccessed = false;
                                    break;
                            }

                            if (!wincapBulkUploadProccessed)
                            {
                                break;
                            }
                        }

                        if (wincapBulkUploadProccessed)
                        {
                            //--- Initialize
                            WincapBulkUploadTDS dataSet = new WincapBulkUploadTDS();
                            dataSet.WincapBulkUpload.Merge(wincapBulkUpload, true);
                            WincapBulkUpload model = new WincapBulkUpload(dataSet);
                            id = id + 1;
                            model.Insert(id.ToString(), sectionId, state, direction, distance, videoDistance, clockPosition, distanceToCentre, reverseSetup, comments);
                            wincapBulkUpload = dataSet.WincapBulkUpload;
                            Session["wincapBulkUpload"] = dataSet.WincapBulkUpload;
                        }
                    }
                }

                dataReader.Close();
                connection.Close();
            }
            catch (Exception ex)
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                }

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }

                throw ex;
            }

            return (wincapBulkUploadProccessed) ? true : false;
        }
 /// <summary>
 /// UpdateDirect
 /// </summary>
 /// <param name="countryId">countryId</param>
 /// <param name="provinceId">provinceId</param>
 /// <param name="countyId">countyId</param>
 /// <param name="cityId">cityId</param>
 /// <param name="originalAssetId">originalAssetId</param>
 /// <param name="originalSectionId">originalSectionId</param>
 /// <param name="originalStreet">originalStreet</param>
 /// <param name="originalUsmh">originalUsmh</param>
 /// <param name="originalDsmh">originalDsmh</param>
 /// <param name="originalMapSize">originalMapSize</param>
 /// <param name="originalSize">originalSize</param>
 /// <param name="originalMapLength">originalMapLength</param>
 /// <param name="originalLength">originalLength</param>
 /// <param name="originalLaterals">originalLaterals</param>
 /// <param name="originalLiveLaterals">originalLiveLaterals</param>
 /// <param name="originalFlowDirection">originalFlowDirection</param>
 /// <param name="originalUsmhDepth">originalUsmhDepth</param>
 /// <param name="originalDsmhDepth">originalDsmhDepth</param>
 /// <param name="originalDeleted">originalDeleted</param>
 /// <param name="originalCompanyId">originalCompanyId</param>
 /// <param name="originalFlowOrderId">originalFlowOrderId</param>
 /// <param name="newAssetId">newAssetId</param>
 /// <param name="newSectionId">newSectionId</param>
 /// <param name="newStreet">newStreet</param>
 /// <param name="newUsmh">newUsmh</param>
 /// <param name="newDsmh">newDsmh</param>
 /// <param name="newMapSize">newMapSize</param>
 /// <param name="newSize">newSize</param>
 /// <param name="newMapLength">newMapLength</param>
 /// <param name="newLength">newLength</param>
 /// <param name="newLaterals">newLaterals</param>
 /// <param name="newLiveLaterals">newLiveLaterals</param>
 /// <param name="newFlowDirection">newFlowDirection</param>
 /// <param name="newUsmhDepth">newUsmhDepth</param>
 /// <param name="newDsmhDepth">newDsmhDepth</param>
 /// <param name="newDeleted">newDeleted</param>
 /// <param name="newCompanyId">newCompanyId</param>
 /// <param name="newFlowOrderId">newFlowOrderId</param>
 public void UpdateDirect(int originalAssetId, string originalSectionId, string originalStreet, int? originalUsmh, int? originalDsmh, string originalMapSize, string originalSize, string originalMapLength, string originalLength, int? originalLaterals, int? originalLiveLaterals, string originalFlowDirection, string originalUsmhDepth, string originalDsmhDepth, bool originalDeleted, int originalCompanyId, string originalFlowOrderId, int newAssetId, string newSectionId, string newStreet, int? newUsmh, int? newDsmh, string newMapSize, string newSize, string newMapLength, string newLength, int? newLaterals, int? newLiveLaterals, string newFlowDirection, string newUsmhDepth, string newDsmhDepth, bool newDeleted, int newCompanyId, string newFlowOrderId)
 {
     // update section
     AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway(null);
     assetSewerSectionGateway.Update(originalAssetId, originalSectionId, originalStreet, originalUsmh, originalDsmh, originalMapSize, originalSize, originalMapLength, originalLength, originalLaterals, originalLiveLaterals, originalFlowDirection, originalUsmhDepth, originalDsmhDepth, originalDeleted, originalCompanyId, originalFlowOrderId, newAssetId, newSectionId, newStreet, newUsmh, newDsmh, newMapSize, newSize, newMapLength, newLength, newLaterals, newLiveLaterals, newFlowDirection, newUsmhDepth, newDsmhDepth, newDeleted, newCompanyId, newFlowOrderId);
 }
        // ////////////////////////////////////////////////////////////////////////
        // PUBLIC METHODS
        //
        /// <summary>
        /// InsertDirect
        /// </summary>
        /// <param name="countryId">countryId</param>
        /// <param name="provinceId">provinceId</param>
        /// <param name="countyId">countyId</param>
        /// <param name="cityId">cityId</param>
        /// <param name="sectionId">sectionId</param>
        /// <param name="street">street</param>
        /// <param name="usmh">usmh</param>
        /// <param name="dsmh">dsmh</param>
        /// <param name="mapSize">mapSize</param>
        /// <param name="size">size</param>
        /// <param name="mapLength">mapLength</param>
        /// <param name="length">length</param>
        /// <param name="laterals">laterals</param>
        /// <param name="liveLaterals">liveLaterals</param>
        /// <param name="flowDirections">flowDirections</param>
        /// <param name="usmhDepth">usmhDepth</param>
        /// <param name="dsmhDepth">dsmhDepth</param>
        /// <param name="usmhAddress">usmhAddress</param>
        /// <param name="dsmhAddress">dsmhAddress</param>
        /// <param name="deleted">deleted</param>
        /// <param name="companyId">companyId</param>
        /// <param name="flowOrderId">flowOrderId</param>
        /// <returns>section_assetId</returns>
        public int InsertDirect(Int64? countryId, Int64? provinceId, Int64? countyId, Int64? cityId, string sectionId, string street, int? usmh, int? dsmh, string mapSize, string size, string mapLength, string length, int? laterals, int? liveLaterals, string flowDirections, string usmhDepth, string dsmhDepth, string usmhAddress, string dsmhAddress, bool deleted, int companyId, string flowOrderId)
        {
            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
            assetSewerSectionGateway.LoadByCountryIdProvinceIdCountyIdCityIdSectionId(countryId, provinceId, countyId, cityId, sectionId, companyId);

            int section_assetId = 0;
            if (assetSewerSectionGateway.Table.Rows.Count == 0)
            {
                section_assetId = new Asset(new DataSet()).InsertDirect("Sewer", countryId, provinceId, countyId, cityId, deleted, companyId);
                new AssetSewer(new DataSet()).InsertDirect(section_assetId, "Section", deleted, companyId);
                assetSewerSectionGateway.Insert(section_assetId, sectionId, street, usmh, dsmh, mapSize, size, mapLength, length, laterals, liveLaterals, flowDirections, usmhDepth, dsmhDepth, deleted, companyId, flowOrderId);
            }
            else
            {
                section_assetId = assetSewerSectionGateway.GetAssetID(sectionId);
            }

            return section_assetId;
        }
        private bool ProcessBulkUpload(string fName, out string bulkUploadResultMessage)
        {
            bool bulkUploadProccessed = true;
            bulkUploadResultMessage = "";

            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();

            AppSettingsReader appSettingReader = new AppSettingsReader();

            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';Data Source=" + fName + ";";

            OleDbConnection connection = new OleDbConnection(excelConnectionString);
            OleDbCommand command = null;
            OleDbDataReader dataReader = null;

            try
            {
                try
                {
                    connection.Open();
                    command = new OleDbCommand("select * from [sections]", connection);
                    dataReader = command.ExecuteReader();
                    dataReader.Close();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    bulkUploadResultMessage = "You did not define the 'sections' data range.  Bulk upload ABORTED.  Original message: " + ex.Message;
                    bulkUploadProccessed = false;

                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }

                //--- Process bulk upload
                if (bulkUploadProccessed)
                {
                    connection.Open();
                    command = new OleDbCommand("select * from [sections]", connection);
                    dataReader = command.ExecuteReader();

                    int id = 0;

                    while (dataReader.Read())
                    {
                        if (!IsEmptyRow(dataReader))
                        {
                            string clientId = "";
                            int companiesId = 0;
                            string client = "";
                            string subArea = "";
                            string street = "";
                            string usmh = "";
                            string dsmh = "";
                            string mapSize = ""; //Size_
                            string mapLength = ""; //ScaledLength
                            DateTime? p1Date = null;
                            string actualLength = ""; //
                            string cxisRemoved = "";
                            DateTime? m1Date = null;
                            DateTime? m2Date = null;
                            DateTime? installDate = null;
                            DateTime? finalVideo = null;
                            bool issueIdentified = false;
                            bool issueResolved = false;
                            bool fllWork = false; // FLL Work
                            bool issueGivenToBayCity = false;
                            string confirmedSize = "";
                            DateTime? deadLineDate = null;
                            DateTime? proposedLiningDate = null;
                            bool salesIssue = false;
                            bool lfsIssue = false;
                            bool clientIssue = false;
                            bool investigationIssue = false;
                            bool jlWork = false; // JL work
                            bool raWork = false; // RA Work
                            DateTime? preFlushDate = null;
                            DateTime? preVideoDate = null;
                            string usmhMn = "";
                            string dsmhMn = "";
                            string usmhDepth = "";
                            string dsmhDepth = "";
                            string measurementsTakenBy = "";
                            string steelTapeThruPipe = "";
                            string usmhAtMouth1200 = "";
                            string usmhAtMouth100 = "";
                            string usmhAtMouth200 = "";
                            string usmhAtMouth300 = "";
                            string usmhAtMouth400 = "";
                            string usmhAtMouth500 = "";
                            string dsmhAtMouth1200 = "";
                            string dsmhAtMouth100 = "";
                            string dsmhAtMouth200 = "";
                            string dsmhAtMouth300 = "";
                            string dsmhAtMouth400 = "";
                            string dsmhAtMouth500 = "";
                            string hydrantAdrress = "";
                            string distanceToInversionMh = "";
                            bool rampsRequired = false;
                            string degreeOfTrafficControl = "";
                            bool standarBypass = false;
                            string hydroWireDetails = "";
                            string pipeMaterialType = "";
                            int? capedLaterals = null;
                            bool roboticPrepRequired = false;
                            bool pipeSizeChange = false;
                            string videoDoneFrom = "";
                            string ToManhole = "";
                            string cutterDescriptionDuringMeasuring = "";
                            string lineWidthId = "";
                            bool schoolZone = false;
                            bool restaurantArea = false;
                            bool carswashLaundromat = false;
                            bool hydroPulley = false;
                            bool fridgeCart = false;
                            bool twoInchPump = false;
                            bool sixInchBypass = false;
                            bool scaffolding = false;
                            bool winchExtension = false;
                            bool extraGenerator = false;
                            bool greyCableExtension = false;
                            bool easementMats = false;
                            string measurementType = "";
                            bool dropPipe = false;
                            string dropPipeInvertDepth = "";
                            string measurementFromManhole = "";

                            string dataCell = null;
                            string dataCellToUpper = null;

                            //--- ... fill section row
                            string originalSectionId = dataReader.GetValue(dataReader.GetOrdinal("ID")).ToString().Trim();

                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                dataCell = dataReader.GetValue(i).ToString().Trim();
                                dataCellToUpper = dataReader.GetValue(i).ToString().Trim().ToUpper();

                                switch (dataReader.GetName(i).Trim())
                                {
                                    case "ID":
                                        originalSectionId = dataCell;
                                        break;

                                    case "ClientID":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            clientId = dataCell;
                                        }
                                        break;

                                    case "COMPANIES_ID":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            companiesId = int.Parse(dataCell);
                                        }
                                        else
                                        {
                                            string aux = dataReader.GetFieldType(2).ToString();
                                            string aux2 = dataReader.GetDouble(dataReader.GetOrdinal("COMPANIES_ID")).ToString();
                                            string aux3 = dataReader.GetString(dataReader.GetOrdinal("COMPANIES_ID")).ToString();
                                        }
                                        break;

                                    case "Client":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            client = dataCell;
                                        }
                                        break;

                                    case "SubArea":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            subArea = dataCell;
                                        }
                                        break;

                                    case "Street":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            street = dataCell;
                                        }
                                        break;

                                    case "USMH":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmh = dataCell;
                                        }
                                        break;

                                    case "DSMH":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmh = dataCell;
                                        }
                                        break;

                                    case "Size_":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                mapSize = dataCell;
                                            }
                                        }
                                        break;

                                    case "ScaledLength":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                mapLength = dataCell;
                                            }
                                        }
                                        break;

                                    case "P1Date":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            p1Date = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "ActualLength":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                actualLength = dataCell;
                                            }
                                        }
                                        break;

                                    case "CXIsRemoved":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            cxisRemoved = dataCell;
                                        }
                                        break;

                                    case "M1Date":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            m1Date = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "M2Date":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            m2Date = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "InstallDate":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            installDate = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "FinalVideo":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            finalVideo = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "IssueIdentified":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            issueIdentified = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "IssueResolved":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            issueResolved = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "FullLengthLining":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            fllWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "IssueGivenToBayCity":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            issueGivenToBayCity = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "ConfirmedSize":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                confirmedSize = dataCell;
                                            }
                                        }
                                        break;

                                    case "DeadlineDate":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            deadLineDate = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "ProposedLiningDate":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            proposedLiningDate = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "SalesIssue":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            salesIssue = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "LFSIssue":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            lfsIssue = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "ClientIssue":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            clientIssue = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "InvestigationIssue":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            investigationIssue = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "JLiner":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            jlWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "RehabAssessment":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            raWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "PreFlushDate":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            preFlushDate = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "PreVideoDate":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            preVideoDate = Convert.ToDateTime(dataCell);
                                        }
                                        break;

                                    case "USMHMN":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhMn = dataCell;
                                        }
                                        break;

                                    case "DSMHMN":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhMn = dataCell;
                                        }
                                        break;

                                    case "USMHDepth":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhDepth = dataCell;
                                        }
                                        break;

                                    case "DSMHDepth":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhDepth = dataCell;
                                        }
                                        break;

                                    case "MeasurementsTakenBy":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            measurementsTakenBy = dataCell;
                                        }
                                        break;

                                    case "SteelTapeThruPipe":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            steelTapeThruPipe = dataCell;
                                        }
                                        break;

                                    case "USMHAtMouth1200":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth1200 = dataCell;
                                        }
                                        break;

                                    case "USMHAtMouth100":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth100 = dataCell;
                                        }
                                        break;

                                    case "USMHAtMouth200":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth200 = dataCell;
                                        }
                                        break;
                                    case "USMHAtMouth300":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth300 = dataCell;
                                        }
                                        break;
                                    case "USMHAtMouth400":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth400 = dataCell;
                                        }
                                        break;
                                    case "USMHAtMouth500":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth500 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth1200":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth1200 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth100":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth100 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth200":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth200 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth300":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth300 = dataCell;
                                        }
                                        break;
                                    case "DSMHAtMouth400":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth400 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth500":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth500 = dataCell;
                                        }
                                        break;

                                    case "HydrantAddress":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            hydrantAdrress = dataCell;
                                        }
                                        break;

                                    case "DistanceToInversionMH":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            distanceToInversionMh = dataCell;
                                        }
                                        break;

                                    case "RampsRequired":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            rampsRequired = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "DegreeOfTrafficControl":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            degreeOfTrafficControl = dataCell;
                                        }
                                        break;

                                    case "StandarBypass":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            standarBypass = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "HydroWireDetails":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            hydroWireDetails = dataCell;
                                        }
                                        break;

                                    case "PipeMaterialType":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            pipeMaterialType = dataCell;
                                        }
                                        break;

                                    case "CappedLaterals":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            capedLaterals = int.Parse(dataCell);
                                        }
                                        break;

                                    case "RoboticPrepRequired":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            roboticPrepRequired = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "PipeSizeChange":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            pipeSizeChange = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "VideoDoneFrom":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            videoDoneFrom = dataCell;
                                        }
                                        break;

                                    case "ToManhole":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            ToManhole = dataCell;
                                        }
                                        break;

                                    case "CutterDescriptionDuringMeasuring":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            cutterDescriptionDuringMeasuring = dataCell;
                                        }
                                        break;

                                    case "LineWithID":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            lineWidthId = dataCell;
                                        }
                                        break;

                                    case "SchoolZone":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            schoolZone = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "RestaurantArea":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            restaurantArea = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "CarwashLaundromat":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            carswashLaundromat = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "HydroPulley":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            hydroPulley = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "FridgeCart":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            fridgeCart = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "TwoInchPump":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            twoInchPump = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "SixInchBypass":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            sixInchBypass = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "Scaffolding":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            scaffolding = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "WinchExtension":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            winchExtension = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "ExtraGenerator":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            extraGenerator = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "GreyCableExtension":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            greyCableExtension = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "EasementMats":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            easementMats = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "MeasurementType":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            measurementType = dataCell;
                                        }
                                        break;

                                    case "DropPipe":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            dropPipe = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "DropPipeInvertDepth":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dropPipeInvertDepth = dataCell;
                                        }
                                        break;

                                    case "MeasuredFromManhole":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            measurementFromManhole = dataCell;
                                        }
                                        break;

                                    default:
                                        bulkUploadResultMessage = "Invalid column name '" + dataReader.GetName(i) + "' in section data range.";
                                        bulkUploadProccessed = false;
                                        break;
                                }

                                if (!bulkUploadProccessed)
                                {
                                    break;
                                }
                            }

                            if (bulkUploadProccessed)
                            {
                                DataMigrationGateway dataMigrationGateway = new DataMigrationGateway(null);
                                Guid originalId = dataMigrationGateway.GetOriginalIdByCompanyIdCompaniesIdRecordIdStreet(int.Parse(hdfCompanyId.Value), companiesId, originalSectionId, street);

                                id = id + 1;

                                if (!dataMigrationGateway.IsMigratedSection(originalId, originalSectionId))
                                {
                                    //--- Initialize
                                    DataMigrationTDS dataSet = new DataMigrationTDS();
                                    dataSet.DataMigration.Merge(dataMigration, true);
                                    DataMigration model = new DataMigration(dataSet);

                                    model.Insert(originalId, originalSectionId, clientId, companiesId, client, subArea, street, usmh, dsmh, mapSize, mapLength, p1Date, actualLength, cxisRemoved, m1Date, m2Date, installDate, finalVideo, issueIdentified, issueResolved, fllWork, issueGivenToBayCity, confirmedSize, deadLineDate, proposedLiningDate, salesIssue, lfsIssue, clientIssue, investigationIssue, jlWork, raWork, preFlushDate, preVideoDate, usmhMn, dsmhMn, usmhDepth, dsmhDepth, measurementsTakenBy, steelTapeThruPipe, usmhAtMouth1200, usmhAtMouth100, usmhAtMouth200, usmhAtMouth300, usmhAtMouth400, usmhAtMouth500, dsmhAtMouth1200, dsmhAtMouth100, dsmhAtMouth200, dsmhAtMouth300, dsmhAtMouth400, dsmhAtMouth500, hydrantAdrress, distanceToInversionMh, rampsRequired, degreeOfTrafficControl, standarBypass, hydroWireDetails, pipeMaterialType, capedLaterals, roboticPrepRequired, pipeSizeChange, videoDoneFrom, ToManhole, cutterDescriptionDuringMeasuring, lineWidthId, schoolZone, restaurantArea, carswashLaundromat, hydroPulley, fridgeCart, twoInchPump, sixInchBypass, scaffolding, winchExtension, extraGenerator, greyCableExtension, easementMats, measurementType, dropPipe, dropPipeInvertDepth, measurementFromManhole);

                                    dataMigration = dataSet.DataMigration;
                                    Session["dataMigration"] = dataSet.DataMigration;
                                }
                            }
                        }
                    }

                    dataReader.Close();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                }

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }

                throw ex;
            }

            return (bulkUploadProccessed) ? true : false;
        }
        /// <summary>
        /// UpdateDirect
        /// </summary>
        /// <param name="originalAssetId">originalAssetId</param>
        /// <param name="originalSection_">originalSection_</param>
        /// <param name="originalAddress">originalAddress</param>
        /// <param name="originalLateralId">originalLateralId</param>
        /// <param name="originalLatitudeAtSection">originalLatitudeAtSection</param>
        /// <param name="originalLongitudeAtSection">originalLongitudeAtSection</param>
        /// <param name="originalLatitudeAtPropertyLine">originalLatitudeAtPropertyLine</param>
        /// <param name="originalLongitudeAtPropertyLine">originalLongitudeAtPropertyLine</param>
        /// <param name="originalState">originalState</param>
        /// <param name="orignalSize">orignalSize</param>
        /// <param name="originalDistanceFromUsmh">originalDistanceFromUsmh</param>
        /// <param name="originalDistanceFromDsmh">originalDistanceFromDsmh</param>
        /// <param name="originalMapSize">originalMapSize</param>
        /// <param name="originalDeleted">originalDeleted</param>
        /// <param name="originalCompanyId">originalCompanyId</param>
        /// <param name="originalConnectionType">originalConnectionType</param>
        /// 
        /// <param name="newAssetId">newAssetId</param>
        /// <param name="newSection_">newSection_</param>
        /// <param name="newAddress">newAddress</param>
        /// <param name="newLateralId">newLateralId</param>
        /// <param name="newLatitudeAtSection">newLatitudeAtSection</param>
        /// <param name="newLongitudeAtSection">newLongitudeAtSection</param>
        /// <param name="newLatitudeAtPropertyLine">newLatitudeAtPropertyLine</param>
        /// <param name="newLongitudeAtPropertyLine">newLongitudeAtPropertyLine</param>
        /// <param name="newState">newState</param>
        /// <param name="newSize">newSize</param>
        /// <param name="newDistanceFromUsmh">newDistanceFromUsmh</param>
        /// <param name="newDistanceFromDsmh">newDistanceFromDsmh</param>
        /// <param name="newMapSize">newMapSize</param>
        /// <param name="newDeleted">newDeleted</param>
        /// <param name="newCompanyId">newCompanyId</param>
        /// <param name="newConnectionType">newConnectionType</param>
        public void UpdateDirect(int originalAssetId, int originalSection_, string originalAddress, string originalLateralId, string originalLatitudeAtSection, string originalLongitudeAtSection, string originalLatitudeAtPropertyLine, string originalLongitudeAtPropertyLine, string originalState, string orignalSize, string originalDistanceFromUsmh, string originalDistanceFromDsmh, string originalMapSize, bool originalDeleted, int originalCompanyId, string originalConnectionType, int newAssetId, int newSection_, string newAddress, string newLateralId, string newLatitudeAtSection, string newLongitudeAtSection, string newLatitudeAtPropertyLine, string newLongitudeAtPropertyLine, string newState, string newSize, string newDistanceFromUsmh, string newDistanceFromDsmh, string newMapSize, bool newDeleted, int newCompanyId, string newConnectionType)
        {
            AssetSewerLateralGateway assetSewerLateralGateway = new AssetSewerLateralGateway();
            assetSewerLateralGateway.Update(originalAssetId, originalSection_, originalAddress, originalLateralId, originalLatitudeAtSection, originalLongitudeAtSection, originalLatitudeAtPropertyLine, originalLongitudeAtPropertyLine, originalState, orignalSize, originalDistanceFromUsmh, originalDistanceFromDsmh, originalMapSize, originalDeleted, originalCompanyId, originalConnectionType, newAssetId, newSection_, newAddress, newLateralId, newLatitudeAtSection, newLongitudeAtSection, newLatitudeAtPropertyLine, newLongitudeAtPropertyLine, newState, newSize, newDistanceFromUsmh, newDistanceFromDsmh, newMapSize, newDeleted, newCompanyId, newConnectionType);

            // Update section
            if ((originalState != newState) && ((newState == "Live") || (originalState == "Live")))
            {
                // ... Load section
                AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                assetSewerSectionGateway.LoadByAssetId(originalSection_, originalCompanyId);

                // ... Get old values of section
                string sectionIdS = assetSewerSectionGateway.GetSectionId(originalSection_);
                string streetS = assetSewerSectionGateway.GetStreet(originalSection_);
                int? usmhIdS = assetSewerSectionGateway.GetUSMH(originalSection_);
                int? dsmhIdS = assetSewerSectionGateway.GetDSMH(originalSection_);
                string mapSizeS = assetSewerSectionGateway.GetMapSize(originalSection_);
                string size_S = assetSewerSectionGateway.GetSize_(originalSection_);
                string mapLengthS = assetSewerSectionGateway.GetMapSize(originalSection_);
                string lengthS = assetSewerSectionGateway.GetLength(originalSection_);
                int? lateralsS = assetSewerSectionGateway.GetLaterals(originalSection_);
                int? liveLateralsS = assetSewerSectionGateway.GetLiveLaterals(originalSection_);
                string flowDirectionS = assetSewerSectionGateway.GetFlowDirection(originalSection_);
                string usmhDepthS = assetSewerSectionGateway.GetUSMHDepth(originalSection_);
                string dsmhDepthS = assetSewerSectionGateway.GetDSMHDepth(originalSection_);
                bool deletedS = assetSewerSectionGateway.GetDeleted(originalSection_);
                string flowOrderIdS = assetSewerSectionGateway.GetFlowOrderID(originalSection_);

                // ... Calculate new values of section
                int? newLiveLaterals = liveLateralsS;
                if (originalState == "Live")
                {
                    newLiveLaterals = newLiveLaterals - 1;
                }
                if (newState == "Live")
                {
                    newLiveLaterals = newLiveLaterals + 1;
                }

                // ... Update section
                AssetSewerSection assetSewerSection = new AssetSewerSection(assetSewerSectionGateway.Data);
                assetSewerSection.UpdateDirect(originalSection_, sectionIdS, streetS, usmhIdS, dsmhIdS, mapSizeS, size_S, mapLengthS, lengthS, lateralsS, liveLateralsS, flowDirectionS, usmhDepthS, dsmhDepthS, deletedS, originalCompanyId, flowOrderIdS, originalSection_, sectionIdS, streetS, usmhIdS, dsmhIdS, mapSizeS, size_S, mapLengthS, lengthS, lateralsS, newLiveLaterals, flowDirectionS, usmhDepthS, dsmhDepthS, deletedS, originalCompanyId, flowOrderIdS);
            }
        }
        // ////////////////////////////////////////////////////////////////////////
        // PUBLIC METHODS
        //
        /// <summary>
        /// InsertDirect
        /// </summary>
        /// <param name="countryId">countryId</param>
        /// <param name="provinceId">provinceId</param>
        /// <param name="countyId">countyId</param>
        /// <param name="cityId">cityId</param>
        /// <param name="section_">section_</param>
        /// <param name="address">address</param>
        /// <param name="lateralID">lateralID</param>
        /// <param name="latitudeAtSection">latitudeAtSection</param>
        /// <param name="longitudeAtSection">longitudeAtSection</param>
        /// <param name="latitudeAtPropertyLine">latitudeAtPropertyLine</param>
        /// <param name="longitudeAtPropertyLine">longitudeAtPropertyLine</param>
        /// <param name="state">state</param>
        /// <param name="size_">size_</param>
        /// <param name="distanceFromUSMH">distanceFromUSMH</param>
        /// <param name="distanceFromDSMH">distanceFromDSMH</param>
        /// <param name="mapSize">mapSize</param>
        /// <param name="deleted">deleted</param>
        /// <param name="companyId">companyId</param>
        /// <param name="connectionType">connectionType</param>
        /// <returns></returns>
        public int InsertDirect(Int64? countryId, Int64? provinceId, Int64? countyId, Int64? cityId, int section_, string address, string lateralID, string latitudeAtSection, string longitudeAtSection, string latitudeAtPropertyLine, string longitudeAtPropertyLine, string state, string size_, string distanceFromUSMH, string distanceFromDSMH, string mapSize, bool deleted, int companyId, string connectionType)
        {
            AssetSewerLateralGateway assetSewerLateralGateway = new AssetSewerLateralGateway();
            assetSewerLateralGateway.LoadBySectionLateralId(section_, lateralID, companyId);

            int lateral_assetId = 0;
            if (assetSewerLateralGateway.Table.Rows.Count == 0)
            {
                // Insert Asset
                lateral_assetId = new Asset(new DataSet()).InsertDirect("Sewer", countryId, provinceId, countyId, cityId, deleted, companyId);
                new AssetSewer(new DataSet()).InsertDirect(lateral_assetId, "Lateral", deleted, companyId);
                assetSewerLateralGateway.Insert(lateral_assetId, section_, address, lateralID, latitudeAtSection, longitudeAtSection, latitudeAtPropertyLine, longitudeAtPropertyLine, state, size_, distanceFromUSMH, distanceFromDSMH, mapSize, deleted, companyId, connectionType);

                // Update Section
                // ... load section
                AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                assetSewerSectionGateway.LoadByAssetId(section_, companyId);

                // ... get old values of section
                string sectionIdS = assetSewerSectionGateway.GetSectionId(section_);
                string streetS = assetSewerSectionGateway.GetStreet(section_);
                int? usmhIdS = assetSewerSectionGateway.GetUSMH(section_);
                int? dsmhIdS = assetSewerSectionGateway.GetDSMH(section_);
                string mapSizeS = assetSewerSectionGateway.GetMapSize(section_);
                string size_S = assetSewerSectionGateway.GetSize_(section_);
                string mapLengthS = assetSewerSectionGateway.GetMapSize(section_);
                string lengthS = assetSewerSectionGateway.GetLength(section_);
                int? lateralsS = assetSewerSectionGateway.GetLaterals(section_);
                int? liveLateralsS = assetSewerSectionGateway.GetLiveLaterals(section_);
                string flowDirectionS = assetSewerSectionGateway.GetFlowDirection(section_);
                string usmhDepthS = assetSewerSectionGateway.GetUSMHDepth(section_);
                string dsmhDepthS = assetSewerSectionGateway.GetDSMHDepth(section_);
                string flowOrderIdS = assetSewerSectionGateway.GetFlowOrderID(section_);

                // ... calculate new values of section
                int? newLaterals  = lateralsS;
                if (newLaterals.HasValue)
                {
                    newLaterals = newLaterals + 1;
                }
                else
                {
                    newLaterals = 1;
                }

                int? newLiveLaterals = liveLateralsS;
                if (state == "Live")
                {
                    if (newLiveLaterals.HasValue)
                    {
                        newLiveLaterals = newLiveLaterals + 1;
                    }
                    else
                    {
                        newLiveLaterals = 1;
                    }
                }
                else
                {
                    newLiveLaterals = 0;
                }

                // ... update section
                AssetSewerSection assetSewerSection = new AssetSewerSection(assetSewerSectionGateway.Data);
                assetSewerSection.UpdateDirect(section_, sectionIdS, streetS, usmhIdS, dsmhIdS, mapSizeS, size_S, mapLengthS, lengthS, lateralsS, liveLateralsS, flowDirectionS, usmhDepthS, dsmhDepthS, deleted, companyId, flowOrderIdS, section_, sectionIdS, streetS, usmhIdS, dsmhIdS, mapSizeS, size_S, mapLengthS, lengthS, newLaterals, newLiveLaterals, flowDirectionS, usmhDepthS, dsmhDepthS, deleted, companyId, flowOrderIdS);
            }
            else
            {
                lateral_assetId = assetSewerLateralGateway.GetAssetID(section_, lateralID);
            }

            return lateral_assetId;
        }
        /// <summary>
        /// UpdateSection
        /// </summary>
        /// <param name="countryId">countryId</param>
        /// <param name="provinceId">provinceId</param>
        /// <param name="countyId">countyId</param>
        /// <param name="cityId">cityId</param>
        /// <param name="assetId">assetId</param>
        /// <param name="sectionId">sectionId</param>
        /// <param name="originalStreet">originalStreet</param>
        /// <param name="originalUsmh">originalUsmh</param>
        /// <param name="originalDsmh">originalDsmh</param>
        /// <param name="originalSize_">originalSize_</param>
        /// <param name="originalLength">originalLength</param>
        /// <param name="originalSubArea">originalSubArea</param>
        /// <param name="newStreet">newStreet</param>
        /// <param name="newUsmh">newUsmh</param>
        /// <param name="newDsmh">newDsmh</param>
        /// <param name="newSize_">newSize_</param>
        /// <param name="newLength">newLength</param>
        /// <param name="newSubArea">newSubArea</param>
        /// <param name="companyId">companyId</param>
        /// <param name="projectId">projectId</param>
        /// <param name="workId">workId</param>
        private void UpdateSection(Int64? countryId, Int64? provinceId, Int64? countyId, Int64? cityId, int assetId, string sectionId, string originalStreet, int? originalUsmh, int? originalDsmh, string originalSize_, string originalLength, string originalSubArea, string newStreet, int? newUsmh, int? newDsmh, string newSize_, string newLength, string newSubArea, int companyId, int projectId, int workId)
        {
            // Load data
            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
            assetSewerSectionGateway.LoadByAssetId(assetId, companyId);

            LfsAssetSewerSectionGateway lfsAssetSewerSectionGateway = new LfsAssetSewerSectionGateway(assetSewerSectionGateway.Data);
            lfsAssetSewerSectionGateway.LoadByAssetId(assetId, companyId);

            // Get unchanged data
            // ... section
            string mapSize_ = assetSewerSectionGateway.GetMapSize(assetId);
            string mapLength = assetSewerSectionGateway.GetMapLength(assetId);
            int? laterals = assetSewerSectionGateway.GetLaterals(assetId);
            int? liveLaterals = assetSewerSectionGateway.GetLiveLaterals(assetId);
            string flowDirection = assetSewerSectionGateway.GetFlowDirection(assetId);
            string usmhDepth = assetSewerSectionGateway.GetUSMHDepth(assetId);
            string dsmhDepth = assetSewerSectionGateway.GetDSMHDepth(assetId);

            // ... lfs section
            string steelTapeTroughSewer = lfsAssetSewerSectionGateway.GetSteelTapeThroughSewer(assetId);
            string usmhMouth12 = lfsAssetSewerSectionGateway.GetUSMHMouth12(assetId);
            string usmhMouth1 = lfsAssetSewerSectionGateway.GetUSMHMouth1(assetId);
            string usmhMouth2 = lfsAssetSewerSectionGateway.GetUSMHMouth2(assetId);
            string usmhMouth3 = lfsAssetSewerSectionGateway.GetUSMHMouth3(assetId);
            string usmhMouth4 = lfsAssetSewerSectionGateway.GetUSMHMouth4(assetId);
            string usmhMouth5 = lfsAssetSewerSectionGateway.GetUSMHMouth5(assetId);
            string dsmhMouth12 = lfsAssetSewerSectionGateway.GetDSMHMouth12(assetId);
            string dsmhMouth1 = lfsAssetSewerSectionGateway.GetDSMHMouth1(assetId);
            string dsmhMouth2 = lfsAssetSewerSectionGateway.GetDSMHMouth2(assetId);
            string dsmhMouth3 = lfsAssetSewerSectionGateway.GetDSMHMouth3(assetId);
            string dsmhMouth4 = lfsAssetSewerSectionGateway.GetDSMHMouth4(assetId);
            string dsmhMouth5 = lfsAssetSewerSectionGateway.GetDSMHMouth5(assetId);
            string thickness = lfsAssetSewerSectionGateway.GetThickness(assetId);

            //Update Full Length Lining work
            if (originalLength != newLength)
            {
                steelTapeTroughSewer = newLength;
            }
            // Update
            LfsAssetSewerSection lfsAssetSewerSection = new LfsAssetSewerSection(null);
            lfsAssetSewerSection.UpdateDirect(assetId, sectionId, originalStreet, originalUsmh, originalDsmh, mapSize_, originalSize_, mapLength, originalLength, laterals, liveLaterals, flowDirection, usmhDepth, dsmhDepth, steelTapeTroughSewer, usmhMouth12, usmhMouth1, usmhMouth2, usmhMouth3, usmhMouth4, usmhMouth5, dsmhMouth12, dsmhMouth1, dsmhMouth2, dsmhMouth3, dsmhMouth4, dsmhMouth5, false, companyId, originalSubArea, thickness, assetId, sectionId, newStreet, newUsmh, newDsmh, mapSize_, newSize_, mapLength, newLength, laterals, liveLaterals, flowDirection, usmhDepth, dsmhDepth, steelTapeTroughSewer, usmhMouth12, usmhMouth1, usmhMouth2, usmhMouth3, usmhMouth4, usmhMouth5, dsmhMouth12, dsmhMouth1, dsmhMouth2, dsmhMouth3, dsmhMouth4, dsmhMouth5, false, companyId, newSubArea, thickness);
        }
        /// <summary>
        /// Save all comments to database (direct)
        /// </summary>
        /// <param name="companyId">companyId</param>   
        /// <param name="runDetails">runDetails</param>
        /// <param name="projectId">projectId</param>
        public void Save(int companyId, string runDetails, int projectId)
        {
            string[] runDetailsList = runDetails.Split('>');

            FullLengthLiningTDS fullLengthLiningWetOutCommentsDetailsChanges = (FullLengthLiningTDS)Data.GetChanges();

            if (fullLengthLiningWetOutCommentsDetailsChanges.WetOutCommentsDetails.Rows.Count > 0)
            {
                FullLengthLiningWetOutCommentsDetailsGateway fullLengthLiningWetOutCommentsDetailsGateway = new FullLengthLiningWetOutCommentsDetailsGateway(fullLengthLiningWetOutCommentsDetailsChanges);

                foreach (FullLengthLiningTDS.WetOutCommentsDetailsRow row in (FullLengthLiningTDS.WetOutCommentsDetailsDataTable)fullLengthLiningWetOutCommentsDetailsChanges.WetOutCommentsDetails)
                {
                    // Insert new comments
                    if ((!row.Deleted) && (!row.InDatabase))
                    {
                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;
                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }

                            WorkFullLengthLiningWetOutComments workFullLengthLiningWetOutComments = new WorkFullLengthLiningWetOutComments(null);
                            int? libraryFilesId = null; if (!row.IsLIBRARY_FILES_IDNull()) libraryFilesId = row.LIBRARY_FILES_ID;

                            workFullLengthLiningWetOutComments.InsertDirect(newWorkId, row.RefID, row.Type, row.Subject, row.UserID, row.DateTime_, row.Comment, libraryFilesId, row.Deleted, row.COMPANY_ID, row.WorkType);
                        }
                    }

                    // Update comments
                    if ((!row.Deleted) && (row.InDatabase))
                    {
                        int workId = row.WorkID;
                        int refId = row.RefID;
                        bool originalDeleted = false;
                        int originalCompanyId = companyId;

                        // original values
                        string originalType = fullLengthLiningWetOutCommentsDetailsGateway.GetTypeOriginal(workId, refId);
                        string originalSubject = fullLengthLiningWetOutCommentsDetailsGateway.GetSubjectOriginal(workId, refId);
                        int originalUserId = fullLengthLiningWetOutCommentsDetailsGateway.GetUserIdOriginal(workId, refId);
                        DateTime? originalDateTime = null; if (fullLengthLiningWetOutCommentsDetailsGateway.GetDateTime_Original(workId, refId) != null) originalDateTime = fullLengthLiningWetOutCommentsDetailsGateway.GetDateTime_Original(workId, refId);
                        string originalComment = fullLengthLiningWetOutCommentsDetailsGateway.GetCommentOriginal(workId, refId);
                        int? originalLibraryFilesId = null; if (fullLengthLiningWetOutCommentsDetailsGateway.GetLIBRARY_FILES_IDOriginal(workId, refId) != null) originalLibraryFilesId = fullLengthLiningWetOutCommentsDetailsGateway.GetLIBRARY_FILES_IDOriginal(workId, refId);
                        string originalWorkType = fullLengthLiningWetOutCommentsDetailsGateway.GetWorkTypeOriginal(workId, refId);

                        // new values
                        string newType = fullLengthLiningWetOutCommentsDetailsGateway.GetType(workId, refId);
                        string newSubject = fullLengthLiningWetOutCommentsDetailsGateway.GetSubject(workId, refId);
                        string newComment = fullLengthLiningWetOutCommentsDetailsGateway.GetComment(workId, refId);
                        int? newLibraryFilesId = null; if (fullLengthLiningWetOutCommentsDetailsGateway.GetLIBRARY_FILES_IDOriginal(workId, refId) != null) originalLibraryFilesId = fullLengthLiningWetOutCommentsDetailsGateway.GetLIBRARY_FILES_ID(workId, refId);

                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;
                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }

                            string workType = "Full Length Lining Wet Out";
                            FullLengthLiningWetOutCommentsDetailsGateway fullLengthLiningWetOutCommentsDetailsGatewayForReview = new FullLengthLiningWetOutCommentsDetailsGateway();
                            fullLengthLiningWetOutCommentsDetailsGatewayForReview.LoadAllByWorkIdWorkType(newWorkId, companyId, workType);
                            if (fullLengthLiningWetOutCommentsDetailsGatewayForReview.Table.Rows.Count > 0)
                            {
                                WorkFullLengthLiningWetOutComments workFullLengthLiningWetOutComments = new WorkFullLengthLiningWetOutComments(null);
                                workFullLengthLiningWetOutComments.UpdateDirect(newWorkId, refId, originalType, originalSubject, originalUserId, originalDateTime, originalComment, originalLibraryFilesId, originalDeleted, originalCompanyId, originalWorkType, workId, refId, newType, newSubject, originalUserId, originalDateTime, newComment, newLibraryFilesId, originalDeleted, originalCompanyId, originalWorkType);
                            }
                            else
                            {
                                WorkFullLengthLiningWetOutComments workFullLengthLiningWetOutComments = new WorkFullLengthLiningWetOutComments(null);
                                int? libraryFilesId = null; if (!row.IsLIBRARY_FILES_IDNull()) libraryFilesId = row.LIBRARY_FILES_ID;

                                workFullLengthLiningWetOutComments.InsertDirect(newWorkId, row.RefID, row.Type, row.Subject, row.UserID, row.DateTime_, row.Comment, libraryFilesId, row.Deleted, row.COMPANY_ID, row.WorkType);
                            }
                        }
                    }

                    // Deleted comments
                    if ((row.Deleted) && (row.InDatabase))
                    {
                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;
                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }
                            WorkFullLengthLiningWetOutComments workFullLengthLiningWetOutComments = new WorkFullLengthLiningWetOutComments(null);
                            workFullLengthLiningWetOutComments.DeleteDirect(newWorkId, row.RefID, row.COMPANY_ID);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Save all catalysts to database (direct)
        /// </summary>
        /// <param name="companyId">companyId</param>   
        /// <param name="runDetails">runDetails</param>
        /// <param name="projectId">projectId</param>
        public void Save(int companyId, string runDetails, int projectId)
        {
            string[] runDetailsList = runDetails.Split('>');

            FullLengthLiningTDS fullLengthLiningWetOutCatalystsDetailsChanges = (FullLengthLiningTDS)Data.GetChanges();

            if (fullLengthLiningWetOutCatalystsDetailsChanges.WetOutCatalystsDetails.Rows.Count > 0)
            {
                FullLengthLiningWetOutCatalystsDetailsGateway fullLengthLiningWetOutCatalystsDetailsGateway = new FullLengthLiningWetOutCatalystsDetailsGateway(fullLengthLiningWetOutCatalystsDetailsChanges);

                foreach (FullLengthLiningTDS.WetOutCatalystsDetailsRow row in (FullLengthLiningTDS.WetOutCatalystsDetailsDataTable)fullLengthLiningWetOutCatalystsDetailsChanges.WetOutCatalystsDetails)
                {
                    // Insert new catalysts
                    if ((!row.Deleted) && (!row.InDatabase))
                    {
                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;

                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }

                            if (newWorkId > 0)
                            {
                                WorkFullLengthLiningWetOutCatalysts workFullLengthLiningWetOutCatalysts = new WorkFullLengthLiningWetOutCatalysts(null);
                                workFullLengthLiningWetOutCatalysts.InsertDirect(newWorkId, row.RefID, row.CatalystID, row.PercentageByWeight, row.LbsForMixQuantity, row.LbsForDrum, row.Deleted, row.COMPANY_ID);
                            }
                        }
                    }

                    // Update catalysts
                    if ((!row.Deleted) && (row.InDatabase))
                    {
                        int workId = row.WorkID;
                        int refId = row.RefID;
                        bool originalDeleted = false;
                        int originalCompanyId = companyId;
                        int originalCatalystId = row.CatalystID;

                        // original values
                        decimal originalPercentageByWeight = fullLengthLiningWetOutCatalystsDetailsGateway.GetPercentageByWeightOriginal(workId, refId);
                        decimal originalLbsForMixQuantity = fullLengthLiningWetOutCatalystsDetailsGateway.GetLbsForMixQuantityOriginal(workId, refId);
                        string originalLbsForDrum = fullLengthLiningWetOutCatalystsDetailsGateway.GetLbsForDrumOriginal(workId, refId);

                        // new values
                        decimal newPercentageByWeight = fullLengthLiningWetOutCatalystsDetailsGateway.GetPercentageByWeight(workId, refId);
                        decimal newLbsForMixQuantity = fullLengthLiningWetOutCatalystsDetailsGateway.GetLbsForMixQuantity(workId, refId);
                        string newLbsForDrum = fullLengthLiningWetOutCatalystsDetailsGateway.GetLbsForDrum(workId, refId);

                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;

                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }

                            if (newWorkId > 0)
                            {
                                FullLengthLiningWetOutCatalystsDetailsGateway fullLengthLiningWetOutCatalystsDetailsGatewayForReview = new FullLengthLiningWetOutCatalystsDetailsGateway();
                                fullLengthLiningWetOutCatalystsDetailsGatewayForReview.LoadByWorkId(newWorkId, companyId);

                                if (fullLengthLiningWetOutCatalystsDetailsGatewayForReview.Table.Rows.Count > 0)
                                {
                                    WorkFullLengthLiningWetOutCatalysts workFullLengthLiningWetOutCatalysts = new WorkFullLengthLiningWetOutCatalysts(null);
                                    workFullLengthLiningWetOutCatalysts.UpdateDirect(newWorkId, refId, originalCatalystId, originalPercentageByWeight, originalLbsForMixQuantity, originalLbsForDrum, originalDeleted, originalCompanyId, workId, refId, originalCatalystId, newPercentageByWeight, newLbsForMixQuantity, newLbsForDrum, originalDeleted, originalCompanyId);
                                }
                                else
                                {
                                    WorkFullLengthLiningWetOutCatalysts workFullLengthLiningWetOutCatalysts = new WorkFullLengthLiningWetOutCatalysts(null);
                                    workFullLengthLiningWetOutCatalysts.InsertDirect(newWorkId, row.RefID, row.CatalystID, row.PercentageByWeight, row.LbsForMixQuantity, row.LbsForDrum, row.Deleted, row.COMPANY_ID);
                                }
                            }
                        }
                    }

                    // Deleted catalysts
                    if ((row.Deleted) && (row.InDatabase))
                    {
                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;

                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }

                            if (newWorkId > 0)
                            {
                                WorkFullLengthLiningWetOutCatalysts workFullLengthLiningWetOutCatalysts = new WorkFullLengthLiningWetOutCatalysts(null);
                                workFullLengthLiningWetOutCatalysts.DeleteDirect(newWorkId, row.RefID, row.COMPANY_ID);
                            }
                        }
                    }
                }
            }
        }
        /// <summary>
        /// UpdateSection
        /// </summary>
        /// <param name="originalWorkId">originalWorkId</param>
        /// <param name="assetId">assetId</param>
        /// <param name="originalSectionId">originalSectionId</param>
        /// <param name="originalStreet">originalStreet</param>
        /// <param name="originalUsmh">originalUsmh</param>
        /// <param name="originalDsmh">originalDsmh</param>
        /// <param name="originalMapSize">originalMapSize</param>
        /// <param name="originalSize">originalSize</param>
        /// <param name="originalMapLength">originalMapLength</param>
        /// <param name="originalLength">originalLength</param>
        /// <param name="originalLaterals">laterals</param>
        /// <param name="originalLiveLaterals">liveLaterals</param>
        /// <param name="originalUsmhDepth">originalUsmhDepth</param>
        /// <param name="originalDsmhDepth">originalDsmhDepth</param>
        /// <param name="originalSteelTapeThroughSewer">originalSteelTapeThroughSewer</param>
        /// <param name="originalSubArea">originalSubArea</param>
        /// <param name="originalUsmhMouth12">originalUsmhMouth12</param>
        /// <param name="originalUsmhMouth1">originalUsmhMouth1</param>
        /// <param name="originalUsmhMouth2">originalUsmhMouth2</param>
        /// <param name="originalUsmhMouth3">originalUsmhMouth3</param>
        /// <param name="originalUsmhMouth4">originalUsmhMouth4</param>
        /// <param name="originalUsmhMouth5">originalUsmhMouth5</param>
        /// <param name="originalDsmhMouth12">originalDsmhMouth12</param>
        /// <param name="originalDsmhMouth1">originalDsmhMouth1</param>
        /// <param name="originalDsmhMouth2">originalDsmhMouth2</param>
        /// <param name="originalDsmhMouth3">originalDsmhMouth3</param>
        /// <param name="originalDsmhMouth4">originalDsmhMouth4</param>
        /// <param name="originalDsmhMouth5">originalDsmhMouth5</param>
        /// <param name="originalDelete">originalDelete</param>
        /// <param name="originalCompanyId">originalCompanyId</param>
        /// <param name="originalUsmhAddress">originalUsmhAddress</param>
        /// <param name="originalDsmhAddress">originalDsmhAddress</param>
        /// <param name="originalThickness">originalThickness</param>
        /// 
        /// <param name="newWorkId">newWorkId</param>
        /// <param name="newAssetId">newAssetId</param>
        /// <param name="newSectionId">newSectionId</param>
        /// <param name="newStreet">newStreet</param>
        /// <param name="newUsmh">newUsmh</param>
        /// <param name="newDsmh">newDsmh</param>
        /// <param name="newMapSize">newMapSize</param>
        /// <param name="newSize">newSize</param>
        /// <param name="newMapLength">newMapLength</param>
        /// <param name="newLength">newLength</param>
        /// <param name="newMapLength">newMapLength</param>
        /// <param name="newLaterals">newLaterals</param>
        /// <param name="newLiveLaterals">newLiveLaterals</param>
        /// <param name="newUsmhDepth">newUsmhDepth</param>
        /// <param name="newDsmhDepth">newDsmhDepth</param>
        /// <param name="newSteelTapeThroughSewer">newSteelTapeThroughSewer</param>
        /// <param name="newSubArea">newSubArea</param>
        /// <param name="newUsmhMouth12">newUsmhMouth12</param>
        /// <param name="newUsmhMouth1">newUsmhMouth1</param>
        /// <param name="newUsmhMouth2">newUsmhMouth2</param>
        /// <param name="newUsmhMouth3">newUsmhMouth3</param>
        /// <param name="newUsmhMouth4">newUsmhMouth4</param>
        /// <param name="newUsmhMouth5">newUsmhMouth5</param>
        /// <param name="newDsmhMouth12">newDsmhMouth12</param>
        /// <param name="newDsmhMouth1">newDsmhMouth1</param>
        /// <param name="newDsmhMouth2">newDsmhMouth2</param>
        /// <param name="newDsmhMouth3">newDsmhMouth3</param>
        /// <param name="newDsmhMouth4">newDsmhMouth4</param>
        /// <param name="newDsmhMouth5">newDsmhMouth5</param>
        /// <param name="newDelete">newDelete</param>
        /// <param name="newCompanyId">newCompanyId</param>
        /// <param name="newUsmhAddress">newUsmhAddress</param>
        /// <param name="newDsmhAddress">newDsmhAddress</param>
        /// <param name="newThickness">newThickness</param>
        /// <param name="projectId">projectId</param>        
        private void UpdateSection(int originalWorkId, int assetId, string originalSectionId, string originalStreet, int? originalUsmh, int? originalDsmh, string originalMapSize, string originalSize, string originalMapLength, string originalLength, int? originalLaterals, int? originalLiveLaterals, string originalUsmhDepth, string originalDsmhDepth, string originalSteelTapeThroughSewer, string originalSubArea, string originalUsmhMouth12, string originalUsmhMouth1, string originalUsmhMouth2, string originalUsmhMouth3, string originalUsmhMouth4, string originalUsmhMouth5, string originalDsmhMouth12, string originalDsmhMouth1, string originalDsmhMouth2, string originalDsmhMouth3, string originalDsmhMouth4, string originalDsmhMouth5, bool originalDelete, int originalCompanyId, string originalUsmhAddress, string originalDsmhAddress, string originalThickness, int newWorkId, int newAssetId, string newSectionId, string newStreet, int? newUsmh, int? newDsmh, string newMapSize, string newSize, string newMapLength, string newLength, int? newLaterals, int? newLiveLaterals, string newUsmhDepth, string newDsmhDepth, string newSteelTapeThroughSewer, string newSubArea, string newUsmhMouth12, string newUsmhMouth1, string newUsmhMouth2, string newUsmhMouth3, string newUsmhMouth4, string newUsmhMouth5, string newDsmhMouth12, string newDsmhMouth1, string newDsmhMouth2, string newDsmhMouth3, string newDsmhMouth4, string newDsmhMouth5, bool newDelete, int newCompanyId, string newUsmhAddress, string newDsmhAddress, string newThickness, int projectId)
        {
            // Get unchanged data
            // ... section
            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
            assetSewerSectionGateway.LoadByAssetId(assetId, originalCompanyId);

            int? laterals = assetSewerSectionGateway.GetLaterals(assetId);
            string flowDirection = assetSewerSectionGateway.GetFlowDirection(assetId);

            // Update section
            LfsAssetSewerSection lfsAssetSewerSection = new LfsAssetSewerSection(null);
            lfsAssetSewerSection.UpdateDirect(assetId, originalSectionId, originalStreet, originalUsmh, originalDsmh, originalMapSize, originalSize, originalMapLength, originalLength, originalLaterals, originalLiveLaterals, flowDirection, originalUsmhDepth, originalDsmhDepth, originalSteelTapeThroughSewer, originalUsmhMouth12, originalUsmhMouth1, originalUsmhMouth2, originalUsmhMouth3, originalUsmhMouth4, originalUsmhMouth5, originalDsmhMouth12, originalDsmhMouth1, originalDsmhMouth2, originalDsmhMouth3, originalDsmhMouth4, originalDsmhMouth5, originalDelete, originalCompanyId, originalSubArea, originalThickness, assetId, newSectionId, newStreet, newUsmh, newDsmh, newMapSize, newSize, newMapLength, newLength, newLaterals, newLiveLaterals, flowDirection, newUsmhDepth, newDsmhDepth, newSteelTapeThroughSewer, newUsmhMouth12, newUsmhMouth1, newUsmhMouth2, newUsmhMouth3, newUsmhMouth4, newUsmhMouth5, newDsmhMouth12, newDsmhMouth1, newDsmhMouth2, newDsmhMouth3, newDsmhMouth4, newDsmhMouth5, newDelete, newCompanyId, newSubArea, newThickness);
        }
        /// <summary>
        /// Save all field cure record to database (direct)
        /// </summary>
        /// <param name="companyId">companyId</param>        
        /// <param name="runDetails">runDetails</param>
        /// <param name="projectId">projectId</param>
        public void Save(int companyId, string runDetails, int projectId)
        {
            string[] runDetailsList = runDetails.Split('>');

            FlInversionFieldCureRecordTDS flInversionFieldCureRecordChanges = (FlInversionFieldCureRecordTDS)Data.GetChanges();

            if (flInversionFieldCureRecordChanges.InversionFieldCureRecord.Rows.Count > 0)
            {
                FlInversionFieldCureRecordGateway flInversionFieldCureRecordGateway = new FlInversionFieldCureRecordGateway(flInversionFieldCureRecordChanges);

                foreach (FlInversionFieldCureRecordTDS.InversionFieldCureRecordRow row in (FlInversionFieldCureRecordTDS.InversionFieldCureRecordDataTable)flInversionFieldCureRecordChanges.InversionFieldCureRecord)
                {
                    // Insert new field cure records
                    if ((!row.Deleted) && (!row.InDatabase))
                    {
                        DateTime readingTime = row.ReadingTime;
                        decimal? headFt = null; if (!row.IsHeadFtNull()) headFt = row.HeadFt;
                        decimal? boilerInF = null; if (!row.IsBoilerInFNull()) boilerInF = row.BoilerInF;
                        decimal? boilerOutF = null; if (!row.IsBoilerOutFNull()) boilerOutF = row.BoilerOutF;
                        decimal? pumpFlow = null; if (!row.IsPumpFlowNull()) pumpFlow = row.PumpFlow;
                        decimal? pumpPsi = null; if (!row.IsPumpPsiNull()) pumpPsi = row.PumpPsi;
                        decimal? mh1Top = null; if (!row.IsMH1TopNull()) mh1Top = row.MH1Top;
                        decimal? mh1Bot = null; if (!row.IsMH1BotNull()) mh1Bot = row.MH1Bot;
                        decimal? mh2Top = null; if (!row.IsMH2TopNull()) mh2Top = row.MH2Top;
                        decimal? mh2Bot = null; if (!row.IsMH2BotNull()) mh2Bot = row.MH2Bot;
                        decimal? mh3Top = null; if (!row.IsMH3TopNull()) mh3Top = row.MH3Top;
                        decimal? mh3Bot = null; if (!row.IsMH3BotNull()) mh3Bot = row.MH3Bot;
                        decimal? mh4Top = null; if (!row.IsMH4TopNull()) mh4Top = row.MH4Top;
                        decimal? mh4Bot = null; if (!row.IsMH4BotNull()) mh4Bot = row.MH4Bot;
                        string comments = ""; if (!row.IsCommentsNull()) comments = row.Comments;

                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;
                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }

                            WorkFullLengthLiningInversionFieldCureRecord workFullLengthLiningInversionFieldCureRecord = new WorkFullLengthLiningInversionFieldCureRecord(null);
                            workFullLengthLiningInversionFieldCureRecord.InsertDirect(newWorkId, row.RefID, readingTime, headFt, boilerInF, boilerOutF, pumpFlow, pumpPsi, mh1Top, mh1Bot, mh2Top, mh2Bot, mh3Top, mh3Bot, mh4Top, mh4Bot, comments, row.Deleted, row.COMPANY_ID);
                        }
                    }

                    // Update field cure records
                    if ((!row.Deleted) && (row.InDatabase))
                    {
                        int workId = row.WorkID;
                        int refId = row.RefID;
                        bool originalDeleted = false;
                        int originalCompanyId = companyId;

                        // original values
                        DateTime originalReadingTime= flInversionFieldCureRecordGateway.GetReadingTimeOriginal(workId, refId);
                        decimal? originalHeadFt = flInversionFieldCureRecordGateway.GetHeadFtOriginal(workId, refId);
                        decimal? originalBoilerInF = flInversionFieldCureRecordGateway.GetBoilerInFOriginal(workId, refId);
                        decimal? originalBoilerOutF = flInversionFieldCureRecordGateway.GetBoilerOutFOriginal(workId, refId);
                        decimal? originalPumpFlow = flInversionFieldCureRecordGateway.GetPumpFlowOriginal(workId, refId);
                        decimal? originalPumpPsi = flInversionFieldCureRecordGateway.GetPumpPsiOriginal(workId, refId);
                        decimal? originalMH1Top = flInversionFieldCureRecordGateway.GetMH1TopOriginal(workId, refId);
                        decimal? originalMH1Bot = flInversionFieldCureRecordGateway.GetMH1BotOriginal(workId, refId);
                        decimal? originalMH2Top = flInversionFieldCureRecordGateway.GetMH2TopOriginal(workId, refId);
                        decimal? originalMH2Bot = flInversionFieldCureRecordGateway.GetMH2BotOriginal(workId, refId);
                        decimal? originalMH3Top = flInversionFieldCureRecordGateway.GetMH3TopOriginal(workId, refId);
                        decimal? originalMH3Bot = flInversionFieldCureRecordGateway.GetMH3BotOriginal(workId, refId);
                        decimal? originalMH4Top = flInversionFieldCureRecordGateway.GetMH4TopOriginal(workId, refId);
                        decimal? originalMH4Bot = flInversionFieldCureRecordGateway.GetMH4BotOriginal(workId, refId);
                        string originalComments = flInversionFieldCureRecordGateway .GetCommentsOriginal(workId, refId);

                        // new values
                        DateTime newReadingTime = flInversionFieldCureRecordGateway.GetReadingTime(workId, refId);
                        decimal? newHeadFt = flInversionFieldCureRecordGateway.GetHeadFt(workId, refId);
                        decimal? newBoilerInF = flInversionFieldCureRecordGateway.GetBoilerInF(workId, refId);
                        decimal? newBoilerOutF = flInversionFieldCureRecordGateway.GetBoilerOutF(workId, refId);
                        decimal? newPumpFlow = flInversionFieldCureRecordGateway.GetPumpFlow(workId, refId);
                        decimal? newPumpPsi = flInversionFieldCureRecordGateway.GetPumpPsi(workId, refId);
                        decimal? newMH1Top = flInversionFieldCureRecordGateway.GetMH1Top(workId, refId);
                        decimal? newMH1Bot = flInversionFieldCureRecordGateway.GetMH1Bot(workId, refId);
                        decimal? newMH2Top = flInversionFieldCureRecordGateway.GetMH2Top(workId, refId);
                        decimal? newMH2Bot = flInversionFieldCureRecordGateway.GetMH2Bot(workId, refId);
                        decimal? newMH3Top = flInversionFieldCureRecordGateway.GetMH3Top(workId, refId);
                        decimal? newMH3Bot = flInversionFieldCureRecordGateway.GetMH3Bot(workId, refId);
                        decimal? newMH4Top = flInversionFieldCureRecordGateway.GetMH4Top(workId, refId);
                        decimal? newMH4Bot = flInversionFieldCureRecordGateway.GetMH4Bot(workId, refId);
                        string newComments = flInversionFieldCureRecordGateway .GetComments(workId, refId);

                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;
                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }

                            FlInversionFieldCureRecordGateway flInversionFieldCureRecordGatewayForReview = new FlInversionFieldCureRecordGateway();
                            flInversionFieldCureRecordGatewayForReview.LoadByWorkId(newWorkId, companyId);
                            if (flInversionFieldCureRecordGatewayForReview.Table.Rows.Count > 0)
                            {
                                WorkFullLengthLiningInversionFieldCureRecord workFullLengthLiningInversionFieldCureRecord = new WorkFullLengthLiningInversionFieldCureRecord(null);
                                workFullLengthLiningInversionFieldCureRecord.UpdateDirect(newWorkId, refId, originalReadingTime, originalHeadFt, originalBoilerInF, originalBoilerOutF, originalPumpFlow, originalPumpPsi, originalMH1Top, originalMH1Bot, originalMH2Top, originalMH2Bot, originalMH3Top, originalMH3Bot, originalMH4Top, originalMH4Bot, originalComments, originalDeleted, originalCompanyId, workId, refId, newReadingTime, newHeadFt, newBoilerInF, newBoilerOutF, newPumpFlow, newPumpPsi, newMH1Top, newMH1Bot, newMH2Top, newMH2Bot, newMH3Top, newMH3Bot, newMH4Top, newMH4Bot, originalComments, originalDeleted, originalCompanyId);
                            }
                            else
                            {
                                DateTime readingTime = row.ReadingTime;
                                decimal? headFt = null; if (!row.IsHeadFtNull()) headFt = row.HeadFt;
                                decimal? boilerInF = null; if (!row.IsBoilerInFNull()) boilerInF = row.BoilerInF;
                                decimal? boilerOutF = null; if (!row.IsBoilerOutFNull()) boilerOutF = row.BoilerOutF;
                                decimal? pumpFlow = null; if (!row.IsPumpFlowNull()) pumpFlow = row.PumpFlow;
                                decimal? pumpPsi = null; if (!row.IsPumpPsiNull()) pumpPsi = row.PumpPsi;
                                decimal? mh1Top = null; if (!row.IsMH1TopNull()) mh1Top = row.MH1Top;
                                decimal? mh1Bot = null; if (!row.IsMH1BotNull()) mh1Bot = row.MH1Bot;
                                decimal? mh2Top = null; if (!row.IsMH2TopNull()) mh2Top = row.MH2Top;
                                decimal? mh2Bot = null; if (!row.IsMH2BotNull()) mh2Bot = row.MH2Bot;
                                decimal? mh3Top = null; if (!row.IsMH3TopNull()) mh3Top = row.MH3Top;
                                decimal? mh3Bot = null; if (!row.IsMH3BotNull()) mh3Bot = row.MH3Bot;
                                decimal? mh4Top = null; if (!row.IsMH4TopNull()) mh4Top = row.MH4Top;
                                decimal? mh4Bot = null; if (!row.IsMH4BotNull()) mh4Bot = row.MH4Bot;
                                string comments = ""; if (!row.IsCommentsNull()) comments = row.Comments;

                                WorkFullLengthLiningInversionFieldCureRecord workFullLengthLiningInversionFieldCureRecord = new WorkFullLengthLiningInversionFieldCureRecord(null);
                                workFullLengthLiningInversionFieldCureRecord.InsertDirect(newWorkId, row.RefID, readingTime, headFt, boilerInF, boilerOutF, pumpFlow, pumpPsi, mh1Top, mh1Bot, mh2Top, mh2Bot, mh3Top, mh3Bot, mh4Top, mh4Bot, comments, row.Deleted, row.COMPANY_ID);
                            }
                        }
                    }

                    // Deleted field cure records
                    if ((row.Deleted) && (row.InDatabase))
                    {
                        for (int i = 0; i < runDetailsList.Length; i++)
                        {
                            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                            string sectionId = runDetailsList[i].ToString();
                            assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                            int assetId = assetSewerSectionGateway.GetAssetID(sectionId);

                            WorkGateway workGateway = new WorkGateway();
                            int newWorkId = 0;
                            workGateway.LoadByProjectIdAssetIdWorkType(projectId, assetId, "Full Length Lining", companyId);
                            if (workGateway.Table.Rows.Count > 0)
                            {
                                newWorkId = workGateway.GetWorkId(assetId, "Full Length Lining", projectId);
                            }

                            WorkFullLengthLiningInversionFieldCureRecord workFullLengthLiningInversionFieldCureRecord = new WorkFullLengthLiningInversionFieldCureRecord(null);
                            workFullLengthLiningInversionFieldCureRecord.DeleteDirect(newWorkId, row.RefID, row.COMPANY_ID);
                        }
                    }
                }
            }
        }
        // ////////////////////////////////////////////////////////////////////////
        // PRIVATE METHODS
        //
        /// <summary>
        /// UpdateForReport
        /// </summary>
        private void UpdateForReport()
        {
            // Load field cure records
            FlInversionFieldCureRecordReportGateway flInversionFieldCureGateway = new FlInversionFieldCureRecordReportGateway();
            flInversionFieldCureGateway.ClearBeforeFill = false;

            foreach (FlInversionReportTDS.LFS_WORK_FULLLENGTHLINING_INVERSIONRow row in (FlInversionReportTDS.LFS_WORK_FULLLENGTHLINING_INVERSIONDataTable)Table)
            {
                // Load catalysts
                flInversionFieldCureGateway.LoadByWorkId(row.COMPANY_ID, row.WorkID);

                // Update comments
                if (!row.IsCommentNull())
                {
                    row.Comment = row.Comment.Replace("<br>", "\n");
                }

                if (!row.IsInstallationResultsNull())
                {
                    row.InstallationResults = row.InstallationResults.Replace("<br>", "\n");
                }

                // Update tube size for report
                string[] confirmedSizeString = row.TubeSize.ToString().Split('\"');
                row.TubeSize = confirmedSizeString[0];

                // Run Details
                string runDetails = row.RunDetails;
                int companyId = row.COMPANY_ID;
                string[] runDetailsList = runDetails.Split('>');
                string flowOrderIds = "";
                for (int i = 0; i < runDetailsList.Length; i++)
                {
                    AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();
                    string sectionId = runDetailsList[i].ToString();
                    assetSewerSectionGateway.LoadBySectionId(sectionId, companyId);
                    string flowOrderId = assetSewerSectionGateway.GetFlowOrderID(sectionId);
                    flowOrderIds = flowOrderIds + flowOrderId + '>';
                }
                flowOrderIds = flowOrderIds.Substring(0, flowOrderIds.Length - 1);
                row.RunDetails = flowOrderIds;
            }

            flInversionFieldCureGateway.ClearBeforeFill = true;
        }
        private bool ProcessBulkUpload(string fName, out string bulkUploadResultMessage)
        {
            bool bulkUploadProccessed = true;
            bulkUploadResultMessage = "";

            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();

            AppSettingsReader appSettingReader = new AppSettingsReader();
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';Data Source=" + fName + ";";

            OleDbConnection connection = new OleDbConnection(excelConnectionString);
            OleDbCommand command = null;
            OleDbDataReader dataReader = null;

            try
            {
                try
                {
                    connection.Open();
                    command = new OleDbCommand("select * from [sections]", connection);
                    dataReader = command.ExecuteReader();
                    dataReader.Close();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    bulkUploadResultMessage = "You did not define the 'sections' data range.  Bulk upload ABORTED.  Original message: " + ex.Message;
                    bulkUploadProccessed = false;

                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }

                //--- Process bulk upload
                if (bulkUploadProccessed)
                {
                    connection.Open();
                    command = new OleDbCommand("select * from [sections]", connection);
                    dataReader = command.ExecuteReader();

                    int id = 0;

                    while (dataReader.Read())
                    {
                        if (!IsEmptyRow(dataReader))
                        {
                            bool deleted = false;
                            int companiesId = 0;
                            string client = "";
                            string subArea = "";
                            string street = "";
                            string usmh = "";
                            string dsmh = "";
                            string mapSize = ""; //Size_
                            string mapLength = ""; //ScaledLength
                            string actualLength = ""; //
                            string confirmedSize = "";
                            bool jlWork = false; // JL work
                            string usmhMn = "";
                            string dsmhMn = "";
                            string usmhDepth = "";
                            string dsmhDepth = "";
                            string steelTapeThruPipe = "";
                            string usmhAtMouth1200 = "";
                            string usmhAtMouth100 = "";
                            string usmhAtMouth200 = "";
                            string usmhAtMouth300 = "";
                            string usmhAtMouth400 = "";
                            string usmhAtMouth500 = "";
                            string dsmhAtMouth1200 = "";
                            string dsmhAtMouth100 = "";
                            string dsmhAtMouth200 = "";
                            string dsmhAtMouth300 = "";
                            string dsmhAtMouth400 = "";
                            string dsmhAtMouth500 = "";

                            string dataCell = null;
                            string dataCellToUpper = null;

                            //--- ... fill section row
                            string originalSectionId = dataReader.GetValue(dataReader.GetOrdinal("ID")).ToString().Trim();

                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                dataCell = dataReader.GetValue(i).ToString().Trim();
                                dataCellToUpper = dataReader.GetValue(i).ToString().Trim().ToUpper();

                                switch (dataReader.GetName(i).Trim())
                                {
                                    case "ID":
                                        originalSectionId = dataCell;
                                        break;

                                    case "COMPANIES_ID":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            companiesId = int.Parse(dataCell);
                                        }
                                        else
                                        {
                                            bulkUploadResultMessage = "Invalid value in 'COMPANIES_ID' column (" + originalSectionId + ").  Bulk upload ABORTED.";
                                        }

                                        break;

                                    case "SubArea":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            subArea = dataCell;
                                        }
                                        break;

                                    case "Street":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            street = dataCell;
                                        }
                                        break;

                                    case "USMH":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmh = dataCell;
                                        }
                                        break;

                                    case "DSMH":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmh = dataCell;
                                        }
                                        break;

                                    case "Size_":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                mapSize = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'Size_' column (" + originalSectionId + ").  Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        break;

                                    case "ScaledLength":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                mapLength = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'ScaledLength' column (" + originalSectionId + ").  Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        break;

                                    case "ActualLength":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                actualLength = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'ActualLength' column (" + originalSectionId + " " + dataCell + ").  Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        break;

                                    case "ConfirmedSize":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                confirmedSize = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'ConfirmedSize' column (" + originalSectionId + ").  Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        break;

                                    case "JLiner":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            jlWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    case "USMHMN":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhMn = dataCell;
                                        }
                                        break;

                                    case "DSMHMN":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhMn = dataCell;
                                        }
                                        break;

                                    case "USMHDepth":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhDepth = dataCell;
                                        }
                                        break;

                                    case "DSMHDepth":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhDepth = dataCell;
                                        }
                                        break;

                                    case "SteelTapeThruPipe":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            steelTapeThruPipe = dataCell;
                                        }
                                        break;

                                    case "USMHAtMouth1200":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth1200 = dataCell;
                                        }
                                        break;

                                    case "USMHAtMouth100":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth100 = dataCell;
                                        }
                                        break;

                                    case "USMHAtMouth200":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth200 = dataCell;
                                        }
                                        break;
                                    case "USMHAtMouth300":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth300 = dataCell;
                                        }
                                        break;
                                    case "USMHAtMouth400":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth400 = dataCell;
                                        }
                                        break;
                                    case "USMHAtMouth500":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            usmhAtMouth500 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth1200":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth1200 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth100":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth100 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth200":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth200 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth300":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth300 = dataCell;
                                        }
                                        break;
                                    case "DSMHAtMouth400":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth400 = dataCell;
                                        }
                                        break;

                                    case "DSMHAtMouth500":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            dsmhAtMouth500 = dataCell;
                                        }
                                        break;

                                    case "Deleted":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            deleted = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        break;

                                    default:
                                        bulkUploadResultMessage = "Invalid column name '" + dataReader.GetName(i) + "' in section data range.";
                                        bulkUploadProccessed = false;
                                        break;
                                }

                                if (!bulkUploadProccessed)
                                {
                                    break;
                                }
                            }

                            if (bulkUploadProccessed && jlWork && !deleted)
                            {
                                DataMigrationGateway dataMigrationGateway = new DataMigrationGateway(null);
                                Guid originalId = dataMigrationGateway.GetOriginalIdByCompanyIdCompaniesIdRecordIdStreet(int.Parse(hdfCompanyId.Value), companiesId, originalSectionId, street);

                                id = id + 1;

                                if (!dataMigrationGateway.IsMigratedSection(originalId, originalSectionId))
                                {
                                    //--- Initialize
                                    int companyId = Int32.Parse(hdfCompanyId.Value);
                                    DataMigrationTDS dataSet = new DataMigrationTDS();
                                    dataSet.JlDataMigration.Merge(dataMigration, true);
                                    JlDataMigration model = new JlDataMigration(dataSet);
                                    CompaniesGateway companiesGateway = new CompaniesGateway();
                                    companiesGateway.LoadByCompaniesId(companiesId, companyId);
                                    client = companiesGateway.GetName(companiesId);

                                    model.Insert(originalId, originalSectionId, companiesId, client, subArea, street, usmh, dsmh, mapSize, mapLength, actualLength, confirmedSize, jlWork, usmhMn, dsmhMn, usmhDepth, dsmhDepth, steelTapeThruPipe, usmhAtMouth1200, usmhAtMouth100, usmhAtMouth200, usmhAtMouth300, usmhAtMouth400, usmhAtMouth500, dsmhAtMouth1200, dsmhAtMouth100, dsmhAtMouth200, dsmhAtMouth300, dsmhAtMouth400, dsmhAtMouth500, 0, 0, false, "No", 0, 0);

                                    dataMigration = dataSet.JlDataMigration;
                                    Session["dataMigration"] = dataSet.JlDataMigration;
                                }
                            }
                        }
                    }

                    dataReader.Close();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                }

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }

                throw ex;
            }

            return (bulkUploadProccessed) ? true : false;
        }
        // ////////////////////////////////////////////////////////////////////////
        // STEP5 - NEW INTERMEDIATE SECTIONS - AUXILIARY EVENTS
        //
        protected void cvSectionId_ServerValidate(object source, ServerValidateEventArgs args)
        {
            ProjectAddSectionsTDS dataSet = new ProjectAddSectionsTDS();
            dataSet.ProjectAddSectionsNew.Merge(projectAddSectionsNew, true);
            dataSet.ProjectAddSectionsTemp.Merge(projectAddSectionsTemp, true);

            ProjectAddSectionsNew projectAddSectionsNewModel = new ProjectAddSectionsNew(dataSet);
            ProjectAddSectionsTemp projectAddSectionsTempModel = new ProjectAddSectionsTemp(dataSet);

            // Initialize
            CustomValidator cvSectionId = (CustomValidator)source;
            args.IsValid = true;

            // verify in this step
            if (projectAddSectionsNewModel.ExistsBySectionId(args.Value))
            {
                cvSectionId.Text = "Duplicated section. (you already have this section here)";
                args.IsValid = false;
            }

            // verify before added or selected
            if (args.IsValid)
            {
                if (projectAddSectionsTempModel.ExistsBySectionId(args.Value))
                {
                    cvSectionId.Text = "Duplicated section. (this section was already created or added previously)";
                    args.IsValid = false;
                }
            }

            // verify in project with specific work
            if (args.IsValid)
            {
                string workType;
                switch (hdfWorkType.Value)
                {
                    case "All":
                        workType = "%";
                        break;

                    case "Rehab Assessment":
                        workType = "Rehab Assessment";
                        break;

                    case "Full Length Lining":
                        workType = "Full Length Lining";
                        break;

                    case "Point Repairs":
                        workType = "Point Repairs";
                        break;

                    case "Junction Lining":
                        workType = "Junction Lining Section";
                        break;

                    case "Manhole Rehabilitation":
                        workType = "%";
                        break;

                    default:
                        throw new Exception("Erroneus work type in Add Section query string.");
                }

                WorkGateway workGateway = new WorkGateway(null);
                if (workGateway.ExistsSectionByProjectIdSectionIdWorkType(int.Parse(hdfProjectId.Value), args.Value, workType, int.Parse(hdfCompanyId.Value)))
                {
                    cvSectionId.Text = "This section is already associated with this project. (if you would like to see it go to the Sections data of this project)";
                    args.IsValid = false;
                }
            }

            // verify in project general
            if (args.IsValid)
            {
                Int64? countryId = int.Parse(hdfCountryId.Value);
                Int64? provinceId = null; if (hdfProvinceId.Value != "") provinceId = Int64.Parse(hdfProvinceId.Value);
                Int64? countyId = null; if (hdfCountyId.Value != "") countyId = Int64.Parse(hdfCountyId.Value);
                Int64? cityId = null; if (hdfCityId.Value != "") cityId = Int64.Parse(hdfCityId.Value);
                int companyId = int.Parse(hdfCompanyId.Value);

                AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway(null);
                if (assetSewerSectionGateway.ExistsByCountryIdProvinceIdCountyIdCityIdSectionId(countryId, provinceId, countyId, cityId, args.Value, companyId))
                {
                    cvSectionId.Text = "This section is already in the database. (you can try adding it using the Add Existing Sections option)";
                    args.IsValid = false;
                }
            }
        }
        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);
            }
        }