private void UpdateDatabase()
        {
            DB.Open();
            DB.BeginTransaction();
            try
            {
                int companyId = Int32.Parse(hdfCompanyId.Value);

                // Update Comments and History
                FlatSectionJlCommentDetails flatSectionJlCommentDetails = new FlatSectionJlCommentDetails(flatSectionJlTDS);
                flatSectionJlCommentDetails.Save(companyId);

                FlatSectionJlHistoryDetails flatSectionJlHistoryDetails = new FlatSectionJlHistoryDetails(flatSectionJlTDS);
                flatSectionJlHistoryDetails.Save(companyId);

                // Update FlatSectionJl for new Comments
                int workId = Int32.Parse(hdfWorkId.Value.Trim());
                string workType = "Junction Lining Lateral";

                FlatSectionJl flatSectionJl = new FlatSectionJl(flatSectionJlTDS);
                flatSectionJl.UpdateCommentsHistoryForSummaryEdit(workId, workType, companyId);

                JlNavigator jlNavigator = new JlNavigator(jlNavigatorTDS);
                jlNavigator.UpdateComments(workId, workType, companyId);

                Session["flatSectionJlTDS"] = flatSectionJlTDS;
                Session["jlNavigatorTDS"] = jlNavigatorTDS;

                DB.CommitTransaction();
            }
            catch (Exception ex)
            {
                DB.RollbackTransaction();

                string url = string.Format("./../../error_page.aspx?error={0}", ex.Message.Replace('\n', ' '));
                Response.Redirect(url);
            }
        }
        /// <summary>
        /// Transfer to Comments
        /// </summary>
        /// <param name="loginId">loginId</param>
        /// <param name="adminPermission">admin Permission</param>
        /// <param name="jlinerComments">jlinerComments</param>
        public void TransferToComments(int loginId, bool adminPermission, FlatSectionJlCommentDetails flatSectionJlCommentDetails)
        {
            int? libraryFilesId = null;

            foreach (FlatSectionJlTDS.FlatSectionJlHistoryDetailsRow historyRow in (FlatSectionJlTDS.FlatSectionJlHistoryDetailsDataTable)Table)
            {
                if (adminPermission)
                {
                    if (!historyRow.IsToCommentsNull())
                    {
                        if ((historyRow.ToComments) && (!historyRow.Deleted))
                        {
                            if (!historyRow.IsLIBRARY_FILES_IDNull())
                            {
                                libraryFilesId = historyRow.LIBRARY_FILES_ID;
                            }
                            else
                            {
                                libraryFilesId = null;
                            }
                            string workType = ""; if (!historyRow.IsWorkTypeNull()) workType = historyRow.WorkType;
                            // Insert to comments
                            flatSectionJlCommentDetails.Insert(historyRow.WorkID, historyRow.Type, historyRow.Subject, historyRow.UserID, historyRow.DateTime_, historyRow.History, libraryFilesId, historyRow.Deleted, historyRow.COMPANY_ID, false, historyRow.UserFullName, false, workType);

                            // Delete from History
                            historyRow.Deleted = true;
                        }
                    }
                }
                else
                {
                    if (historyRow.UserID == loginId)
                    {
                        if (!historyRow.IsToCommentsNull())
                        {
                            if ((historyRow.ToComments) && (!historyRow.Deleted))
                            {
                                if (!historyRow.IsLIBRARY_FILES_IDNull())
                                {
                                    libraryFilesId = historyRow.LIBRARY_FILES_ID;
                                }
                                else
                                {
                                    libraryFilesId = null;
                                }
                                string workType = ""; if (!historyRow.IsWorkTypeNull()) workType = historyRow.WorkType;
                                // Insert to comments
                                flatSectionJlCommentDetails.Insert(historyRow.WorkID, historyRow.Type, historyRow.Subject, historyRow.UserID, historyRow.DateTime_, historyRow.History, libraryFilesId, historyRow.Deleted, historyRow.COMPANY_ID, false, historyRow.UserFullName, false, workType);

                                // Delete from History
                                historyRow.Deleted = true;
                            }
                        }
                    }
                }
            }
        }
        private void GrdCommentsAdd()
        {
            if (ValidateCommentsFooter())
            {
                Page.Validate("commentsDataAdd");
                if (Page.IsValid)
                {
                    int workId = Int32.Parse(hdfWorkId.Value);
                    int companyId = Int32.Parse(hdfCompanyId.Value);
                    int loginId = Convert.ToInt32(Session["loginID"]);
                    DateTime dateTime_ = DateTime.Now;
                    bool inDatabase = false;
                    bool deleted = false;
                    string workType = hdfWorkType.Value;

                    string newSubject = ((TextBox)grdComments.FooterRow.FindControl("tbxSubjectNew")).Text.Trim();
                    string newType = "Junction Lining Lateral";
                    string newComment = ((TextBox)grdComments.FooterRow.FindControl("tbxCommentsNew")).Text.Trim();
                    int? libraryFilesId = null; if (((Label)grdComments.FooterRow.FindControl("lblLIBRARY_FILES_IDNew")).Text != "") libraryFilesId = Int32.Parse(((Label)grdComments.FooterRow.FindControl("lblLIBRARY_FILES_IDNew")).Text.Trim());
                    bool toHistory = ((CheckBox)grdComments.FooterRow.FindControl("ckbxToHistoryNew")).Checked;

                    LoginGateway loginGateway = new LoginGateway();
                    loginGateway.LoadByLoginId(loginId, companyId);
                    string userFullName = loginGateway.GetLastName(loginId, companyId) + " " + loginGateway.GetFirstName(loginId, companyId);

                    FlatSectionJlCommentDetails model = new FlatSectionJlCommentDetails(flatSectionJlTDS);
                    model.Insert(workId, newType, newSubject, loginId, dateTime_, newComment, libraryFilesId, deleted, companyId, inDatabase, userFullName, toHistory, workType);

                    Session.Remove("flatSectionJlCommentDetailsDummy");
                    Session["flatSectionJlTDS"] = flatSectionJlTDS;
                    Session["flatSectionJlCommentDetails"] = flatSectionJlTDS.FlatSectionJlCommentDetails;

                    grdComments.DataBind();
                    grdComments.PageIndex = grdComments.PageCount - 1;
                }
            }
        }
        private void TransferToHistory()
        {
            int loginId = Convert.ToInt32(hdfLoginId.Value);
            bool adminPermission = Convert.ToBoolean(Session["sgLFS_CWP_JUNCTIONLINING_ADMIN"]);

            FlatSectionJlCommentDetails flatSectionJlCommentDetails = new FlatSectionJlCommentDetails(flatSectionJlTDS);
            FlatSectionJlHistoryDetails flatSectionJlHistoryDetails = new FlatSectionJlHistoryDetails(flatSectionJlTDS);

            flatSectionJlCommentDetails.TransferToHistory(loginId, adminPermission, flatSectionJlHistoryDetails);
        }
        // ////////////////////////////////////////////////////////////////////////
        // EVENTS
        //
        protected void Page_Load(object sender, EventArgs e)
        {
            // Register client scripts
            this.RegisterClientScripts();

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

                // Validate query string
                if (((string)Request.QueryString["source_page"] == null) || ((string)Request.QueryString["client_id"] == null) || ((string)Request.QueryString["project_id"] == null) || ((string)Request.QueryString["row_asset_id"] == null) || ((string)Request.QueryString["row_focus"] == null) || ((string)Request.QueryString["work_type"] == null))
                {
                    Response.Redirect("./../../error_page.aspx?error=" + "Invalid query string in jl_comments.aspx");
                }

                // Tag Page
                hdfCompanyId.Value = Session["companyID"].ToString();
                hdfCurrentProjectId.Value = Request.QueryString["project_id"].ToString();
                hdfCurrentClientId.Value = Request.QueryString["client_id"].ToString();
                hdfLoginId.Value = Convert.ToInt32(Session["loginID"]).ToString();
                hdfAssetId.Value = Convert.ToString(Request.QueryString["row_asset_id"]);
                hdfAdminPermission.Value = Convert.ToBoolean(Session["sgLFS_CWP_JUNCTIONLINING_ADMIN"]).ToString();
                hdfWorkType.Value = Request.QueryString["work_type"].ToString();
                hdfSection_.Value = Request.QueryString["section_"].ToString();
                hdfUpdate.Value = "yes";

                // ... Names for UserList
                string workType = hdfWorkType.Value.Trim();
                int companyId = Int32.Parse(hdfCompanyId.Value);

                LoginGateway loginGateway = new LoginGateway();
                loginGateway.LoadByLoginId(Convert.ToInt32(hdfLoginId.Value), companyId);
                hdfCreatedBy.Value = loginGateway.GetLastName(Convert.ToInt32(hdfLoginId.Value), companyId) + " " + loginGateway.GetFirstName(Convert.ToInt32(hdfLoginId.Value), companyId);
                hdfCreationDateTime.Value = DateTime.Now.ToString();

                // Prepare initial data
                Session.Remove("flatSectionJlCommentDetailsDummy");

                // If coming from
                // ... flat_section_jl_summary.aspx or flat_section_jl_edit.aspx
                if (Request.QueryString["source_page"] == "flat_section_jl_summary.aspx" || Request.QueryString["source_page"] == "flat_section_jl_edit.aspx")
                {
                    StoreNavigatorState();
                    ViewState["update"] = Request.QueryString["update"];
                    Session["rowFocus"] = Convert.ToInt32(Request.QueryString["row_focus"].ToString());

                    // ... Load comments to edit
                    flatSectionJlTDS = (FlatSectionJlTDS)Session["flatSectionJlTDS"];
                    jlNavigatorTDS = (JlNavigatorTDS)Session["jlNavigatorTDS"];

                    // Get workId
                    int assetId = Int32.Parse(hdfAssetId.Value.Trim());
                    int currentProjectId = Int32.Parse(hdfCurrentProjectId.Value.Trim());
                    int jlWorkId = 0;
                    int raWorkId = 0;
                    int flWorkId = 0;

                    WorkGateway searchWorkGateway = new WorkGateway();
                    searchWorkGateway.LoadByProjectIdAssetIdWorkType(currentProjectId, assetId, "Junction Lining Lateral", companyId);

                    // ... If the project has jl works
                    if (searchWorkGateway.Table.Rows.Count > 0)
                    {
                        jlWorkId = searchWorkGateway.GetWorkId(assetId, "Junction Lining Lateral", currentProjectId);

                        // ... ...Tag Page
                        hdfWorkId.Value = jlWorkId.ToString().Trim();

                        // ... ...Get ra work id for comments
                        int section_ = Int32.Parse(hdfSection_.Value) ;
                        WorkGateway workGatewayRAFL = new WorkGateway();

                        // ... ...Get fl work id for comments
                        workGatewayRAFL.LoadByProjectIdAssetIdWorkType(currentProjectId, section_, "Full Length Lining", companyId);
                        if (workGatewayRAFL.Table.Rows.Count > 0)
                        {
                            flWorkId = workGatewayRAFL.GetWorkId(section_, "Full Length Lining", currentProjectId);
                        }

                        workGatewayRAFL.LoadByProjectIdAssetIdWorkType(currentProjectId, section_, "Rehab Assessment", companyId);
                        if (workGatewayRAFL.Table.Rows.Count > 0)
                        {
                            raWorkId = workGatewayRAFL.GetWorkId(section_, "Rehab Assessment", currentProjectId);
                        }

                        // ... ...Get comments
                        FlatSectionJlCommentDetailsGateway flatSectionJlCommentDetailsGateway = new FlatSectionJlCommentDetailsGateway(flatSectionJlTDS);
                        flatSectionJlCommentDetailsGateway.LoadAllByJlWorkIdRaWorkIdFlWorkId(jlWorkId, raWorkId, flWorkId, companyId);

                        FlatSectionJlCommentDetails flatSectionJlCommentDetails = new FlatSectionJlCommentDetails(flatSectionJlCommentDetailsGateway.Data);
                        flatSectionJlCommentDetails.UpdateForProcess();

                        // ... ...Get history
                        FlatSectionJlHistoryDetailsGateway flatSectionJlHistoryDetailsGateway = new FlatSectionJlHistoryDetailsGateway(flatSectionJlTDS);
                        flatSectionJlHistoryDetailsGateway.LoadAllByJlWorkIdRaWorkIdFlWorkId(jlWorkId, raWorkId, flWorkId, companyId);
                    }

                    // ... Store datasets
                    Session["flatSectionJlTDS"] = flatSectionJlTDS;
                    Session["jlNavigatorTDS"] = jlNavigatorTDS;
                    Session["flatSectionJlCommentDetails"] = flatSectionJlTDS.FlatSectionJlCommentDetails;
                }
            }
            else
            {
                // Restore datasets
                flatSectionJlTDS = (FlatSectionJlTDS)Session["flatSectionJlTDS"];
                jlNavigatorTDS = (JlNavigatorTDS)Session["jlNavigatorTDS"];
                flatSectionJlCommentDetails = flatSectionJlTDS.FlatSectionJlCommentDetails;

                // Store
                Session["flatSectionJlCommentDetails"] = flatSectionJlTDS.FlatSectionJlCommentDetails;
            }
        }
        protected void grdComments_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            Page.Validate("commentsDataEdit");
            if (Page.IsValid)
            {
                int workId = (int)e.Keys["WorkID"];
                int refId = (int)e.Keys["RefID"];
                int companyId = Int32.Parse(hdfCompanyId.Value);
                int loginId = Convert.ToInt32(Session["loginID"]);
                bool adminPermission = Convert.ToBoolean(Session["sgLFS_CWP_JUNCTIONLINING_ADMIN"]);
                string type = ((Label)grdComments.Rows[e.RowIndex].Cells[4].FindControl("lblType")).Text.Trim();
                string newSubject = ((TextBox)grdComments.Rows[e.RowIndex].Cells[5].FindControl("tbxSubjectEdit")).Text.Trim();
                string newComment = ((TextBox)grdComments.Rows[e.RowIndex].Cells[5].FindControl("tbxCommentsEdit")).Text.Trim();
                bool toHistory = ((CheckBox)grdComments.Rows[e.RowIndex].Cells[5].FindControl("ckbxToHistoryEdit")).Checked;

                // Update data
                FlatSectionJlCommentDetails model = new FlatSectionJlCommentDetails(flatSectionJlTDS);
                model.Update(workId, refId, type, newSubject, companyId, newComment, loginId, adminPermission, toHistory);

                // Store dataset
                Session["flatSectionJlTDS"] = flatSectionJlTDS;
                Session["flatSectionJlCommentDetails"] = flatSectionJlTDS.FlatSectionJlCommentDetails;
            }
            else
            {
                e.Cancel = true;
            }
        }
        protected void grdComments_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            // Comments Gridview, if the gridview is edition mode
            if (grdComments.EditIndex >= 0)
            {
                grdComments.UpdateRow(grdComments.EditIndex, true);
            }

            // Delete comments
            int workId = (int)e.Keys["WorkID"];
            int refId = (int)e.Keys["RefID"];
            int companyId = Int32.Parse(hdfCompanyId.Value);
            int loginId = Convert.ToInt32(hdfLoginId.Value);
            bool adminPermission = Convert.ToBoolean(Session["sgLFS_CWP_JUNCTIONLINING_ADMIN"]);

            // Delete comment
            FlatSectionJlCommentDetails model = new FlatSectionJlCommentDetails(flatSectionJlTDS);
            model.Delete(workId, refId, companyId, loginId, adminPermission);

            // Store dataset
            Session["flatSectionJlTDS"] = flatSectionJlTDS;
        }
        private void UpdateDatabase()
        {
            // Get ids & location
            int projectId = Int32.Parse(hdfCurrentProject.Value.Trim());
            int companyId = Int32.Parse(Session["companyID"].ToString());

            // Load project
            ProjectGateway projectGateway = new ProjectGateway();
            projectGateway.LoadByProjectId(projectId);

            // Get location
            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);

            // Save
            DB.Open();
            DB.BeginTransaction();
            try
            {
                FlatSectionJl flatSectionJl = new FlatSectionJl(flatSectionJlTDS);

                if (Convert.ToBoolean(ViewState["UpdateComments"]))
                {
                    // Update Comments
                    FlatSectionJlCommentDetails flatSectionJlCommentDetails = new FlatSectionJlCommentDetails(flatSectionJlTDS);
                    flatSectionJlCommentDetails.Save(companyId);

                    // Update works
                    WorkUpdate();
                }
                else
                {
                    flatSectionJl.UpdateDirect(countryId, provinceId, countyId, cityId, projectId, companyId);
                }

                DB.CommitTransaction();

                flatSectionJlTDS.AcceptChanges();

                // Store datasets
                Session["flatSectionJlTDS"] = flatSectionJlTDS;
            }
            catch (Exception ex)
            {
                DB.RollbackTransaction();

                string url = string.Format("./../../error_page.aspx?error={0}", ex.Message.Replace('\n', ' '));
                Response.Redirect(url);
            }
        }
        private void AddComments(int workId, string comment)
        {
            int companyId = Convert.ToInt32(Session["companyID"]);
            int loginId = Convert.ToInt32(Session["loginID"]);
            DateTime dateTime_ = DateTime.Now;
            bool inDatabase = false;
            bool deleted = false;

            string newSubject = "Bulk Field Update Comments";
            string newType = "Junction Lining Lateral";
            int? libraryFilesId = null;
            bool toHistory = false;

            LoginGateway loginGateway = new LoginGateway();
            loginGateway.LoadByLoginId(loginId, companyId);
            string userFullName = loginGateway.GetLastName(loginId, companyId) + " " + loginGateway.GetFirstName(loginId, companyId);

            FlatSectionJlCommentDetails model = new FlatSectionJlCommentDetails(flatSectionJlTDS);
            model.InsertForBulkFieldUpdate(workId, newType, newSubject, loginId, dateTime_, comment, libraryFilesId, deleted, companyId, inDatabase, userFullName, toHistory);
        }