using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Web;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using CLS_BL;
using CLS_DL;
using CLS_PL;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using System.Web.UI.DataVisualization.Charting;
using System.Drawing.Printing;
using System.Drawing;
using System.Drawing.Text;


namespace SUNWEB
{
    public partial class CRMCustomerMaster : System.Web.UI.Page
    {
        BL_API ObjApi = new BL_API();
        BL_SELECT ObjSel = new BL_SELECT();
        BLCompany ObjBL = new BLCompany();
        PLCOMMON ObjPL = new PLCOMMON();
        BL_USER ObjUserBL = new BL_USER(); //This Object for All User and Page Related fuctions included 
        PLCUSTOMER ObjCustPL = new PLCUSTOMER();
        Int32 K = 0;
        Int32 L = 0;
        Int32 S = 0;
        Int32 U = 0;
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        DataSet ds = new DataSet();
        MYSQLDB ObjMain = new MYSQLDB();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable DtSuperAdmin = new DataTable();
        DataTable DtMultiBranch = new DataTable();
        DataTable DtNormalUser = new DataTable();
        DataTable DTProductSale = new DataTable();
        DataTable DtMemberList = new DataTable();
        DataTable DTMemberDL = new DataTable();
        DataTable DTCustomerPINDL = new DataTable();
        MySqlCommand Cmdv = new MySqlCommand();

        // User Variables 
        string UserId = string.Empty;
        string UserName = string.Empty;
        string PortalTypeId = string.Empty;
        string cd_Id = string.Empty;
        string Datapath = string.Empty;
        string cd_BranchAlias = string.Empty;
        string IsMultiBranch = string.Empty;
        string IsSuperAdmin = string.Empty;
        string ActiveCallvalue = string.Empty;
        // License Variables ..
        string PID = string.Empty;
        string Page_Acess = string.Empty;
        string Read_Acess = string.Empty;
        string Write_Acess = string.Empty;
        string Isdowld_Acess = string.Empty;
        string Rcd_id = string.Empty;
        string RPortalid = string.Empty;
        string Useridvalue = string.Empty;
        string QueryCurr_Select = string.Empty;
        string L_MaxUserCnt = string.Empty;

        string L_CRMCustomerMaster = string.Empty;
        string Branchidval = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string ARBranchid = string.Empty;
        string Branchid = string.Empty;

        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;
        DataSet DS = new DataSet();

        string Vw_Chk = string.Empty;
        // Added On 20-06-2022
        string ReadHQ = string.Empty;

        string BranchAlias = string.Empty;
        string MobValue;
        string Ins_CustQuery = string.Empty;
        string RCustomerID = string.Empty;
        string RBranchid = string.Empty;
        string AES_Key = string.Empty;
        string en_userName = string.Empty;

        string en_CustomerName = string.Empty;
        string en_Mobile = string.Empty;
        string en_password = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {
            customeridmsg.Visible = false;

            PID = "47";    // This is for CRM CustomerMaster  Page 
            if (!IsPostBack)
            {


                txtDateRegistered.Text = Curr_DateOnly;
                txtDOB.Text = Curr_DateOnly;
                chkPDPAStatus.Checked = true;
                chkActive.Checked = true;
                chkAllowAds.Checked = true;
                btnSave.Visible = true;
                btnSave.Enabled = true;
                btnUpdate.Visible = false;
                btnUpdate.Enabled = false;
                lblBranchAliasName.Visible = false;
                btnDelete.Visible = false;

            }
            MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                    LoadMembershipList();
                    LoadCDRValue(Session["Branch_HQ"].ToString());
                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();
                    }
                }
            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                }
                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                //BranchAlias = Cmb_BranchName;
                BranchAlias = Session["Branch_HQ"].ToString();
                //LoadInventoryListGrid(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                    ActiveCall();
                }
            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == false))
            {
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;

                Cm_id = ObjSel.GETcmid(Session["Cd_Id"].ToString());  // Get cm_id
                HQ = ObjSel.Get_HQ(Cm_id);     // Get the HQ Value
                Curr_BranchFullName = ObjSel.GetBranchFullName(HQ, Session["Branch_HQ"].ToString());
                lblBranchAliasName.ForeColor = System.Drawing.Color.DodgerBlue;
                lblBranchAliasName.Text = "Branch Name" + "&nbsp;" + "-" + "&nbsp;" + Curr_BranchFullName + "&nbsp; ";

                cd_BranchAlias = Session["Branch_HQ"].ToString();
                Datapath = Session["DataPath"].ToString();
                PortalTypeId = Session["PortalTypeId"].ToString();

                DT = ObjUserBL.GetPageRightsDetailsPid(Session["UserId"].ToString(), PID);
                if (DT.Rows.Count > 0)
                {
                    Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                    Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                    Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                    Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();

                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }
                if ((Session["L_CRMCustomerMaster"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    // lblMsgTotalSelection.Text = "Item Sale  Between" + "&nbsp;&nbsp;&nbsp;&nbsp;" + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  and  &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                    if (!IsPostBack)
                    {
                        if (Session["Isdowld_Acess"].ToString() == "N")
                        {
                            // btnExcel.Enabled = false;
                            Response.Write(@"<script language='javascript'>alert('No Access To download.')</script>");
                        }
                        else
                        {


                            if (cmbBranches.Visible == false)
                            {
                                //  BindGridview(Session["Branch_HQ"].ToString(), txtStartDt.Text, txtEndDt.Text);
                            }

                        }


                    }

                }

            }
            else
            {

            }


        }


        public void ActiveCall()
        {
            string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
            ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
            if (ActiveCallvalue == "")
            {

                #region  Default Branch Validation : Either HQ or First Branch Based On IsHQ Ini in PMS 
                string _hq_query = "select IsHQ from  portaldb.companymaster where cm_id=" + Session["CM_ID"] + " ;";
                string _Is_HQ_Enabled = Convert.ToString(ObjMain.ExecuteScalar(_hq_query));
                if (_Is_HQ_Enabled == "1")
                {
                    ActiveCallvalue = ObjSel.GetHQ(Session["Branch_HQ"].ToString());

                }
                else if (_Is_HQ_Enabled == "0")
                {
                    ActiveCallvalue = ObjSel.GetFirstBranch(Session["Branch_HQ"].ToString());
                }

                #endregion
            }

            #region  HQ Validation in ActiveCall Function : Starts 
            cd_BranchAlias = ActiveCallvalue;

            // HQ Validation For Loading Branch Name : Dropdown  Added Karthik  On 02-12-21 

            ReadHQ = ObjUserBL.GetBranchFullNameByBranchName(Session["Branch_HQ"].ToString(), cd_BranchAlias);

            if (ReadHQ == "HQ")
            {
                cmbBranches.Items.FindByText(ReadHQ).Selected = true;
            }
            else
            {
                Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cd_BranchAlias);
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                cmbBranches.Items.FindByText(Curr_BranchFullName).Selected = true;
                Branchidval = ObjUserBL.GetBranchIDbyName(ActiveCallvalue, ActiveCallvalue);
            }

            #endregion

            // Getting The Current Page Name 
            //_pagename will return CRMCustomerMaster.aspx
            string _pagename = System.IO.Path.GetFileName(Request.Url.ToString());


            if (Branchidval != "")
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            }
            else
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Session["Fistbranch"].ToString());

            }


            if (DT.Rows.Count > 0)
            {
                Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();

                Session["Page_Acess"] = Page_Acess;
                Session["Read_Acess"] = Read_Acess;
                Session["Write_Acess"] = Write_Acess;
                Session["Isdowld_Acess"] = Isdowld_Acess;
            }
            Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + cmbBranches.SelectedItem.Value + " and cd_branchalias='" + ActiveCallvalue + "';");
            RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
            DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);
            if (DtLicense.Rows.Count > 0)
            {
                L_CRMCustomerMaster = DtLicense.Rows[0]["CRMCUSTOMERMASTER"].ToString();
                Session["L_CRMCustomerMaster"] = L_CRMCustomerMaster;
            }

            if ((Session["L_CRMCustomerMaster"] == null) || Session["L_CRMCustomerMaster"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                if (cmbBranches.SelectedItem.Text == "HQ")
                {

                    BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                    Cmb_BranchName = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), BranchFullName);
                    UPDATEBRANCH(Cmb_BranchName);


                }
                else
                {
                    Response.Redirect("DeniedPage.aspx");
                }



            }
            else
            {

                // This Is Crm Page . Default Branch is HQ 

                if (_pagename.Equals("CRMCustomerMaster.aspx"))
                {
                    Cmb_BranchName = Session["Branch_HQ"].ToString();
                    LoadMemberDataList(Cmb_BranchName);

                }

                LoadDefaultiMembership();

            }
            //txtUserName.Text = "";
            //txtPassword.Text = "";

        }

        public void LoadDefaultiMembership()
        {
            #region  Validation Between HQ Branch and Normal Branch 
            BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
            if (BranchFullName == "HQ")
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Cmb_BranchName;

            }
            #endregion

            UPDATEBRANCH(BranchAlias);

            //Get the Defualt value of membership 
            string Get_Membership = "select inivalue from " + BranchAlias + ".weborderinginisetting where id = 49;";
            string Read_Value = ObjMain.ExecuteScalar(Get_Membership);
            if (Read_Value != "")
            {
                cmbMembershipType.SelectedValue = Read_Value;
                cmbMembershipType.DataBind();
            }
            else
            {
                cmbMembershipType.SelectedIndex = 0;
                cmbMembershipType.DataBind();
            }



        }

        public void LoadBranchesOnID()
        {
            DT = ObjUserBL.GetBranchIDList(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            StringBuilder StrBObj = new StringBuilder();

            for (int i = 0; i < DT.Rows.Count; i++)
            {
                DataRow dr = DT.Rows[i];
                RBranchid = dr["Branchid"].ToString();
                if (StrBObj.Length != 0)
                {

                    RBranchid = "," + RBranchid;
                    StrBObj.Append("  " + RBranchid + "    ");
                }
                else
                {
                    Branchid = RBranchid;
                    StrBObj.Append("SELECT  Branchid ,BranchName,branchfullname,datapath FROM " + Session["Branch_HQ"].ToString() + ".branches where branchid in (1 , " + Branchid + " ");

                }


            }
            StrBObj.Append(")");
            Dt2 = ObjMain.GetData_DT(StrBObj.ToString());

            if (Dt2.Rows.Count > 0)
            {
                cmbBranches.DataSource = Dt2;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }

        }

        public void LoadBranchName()
        {
            DtNormalUser = ObjUserBL.GetBranchOnLoginValues8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtNormalUser.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtNormalUser;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }

        public void LoadBranchName4SuperAdmin()
        {
            DtSuperAdmin = ObjUserBL.GetBranchOnSuperAdmin8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtSuperAdmin.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtSuperAdmin;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }

        public void UPDATEBRANCH(string ActiveCallvalue)
        {
            if (!string.IsNullOrEmpty(Session["UserId"] as string))
            {
                Useridvalue = Session["UserId"].ToString();
                QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
                S = ObjMain.ExecuteNonQuery(QueryCurr_Select);
            }
            else
            {
                Response.Redirect("Login.aspx");

            }

        }

        public void LoadMembershipList()
        {
            cmbMembershipType.DataSource = ObjSel.iCRM_MembershipTypes(Session["Branch_HQ"].ToString());
            cmbMembershipType.DataTextField = "MembershipName";
            cmbMembershipType.DataValueField = "MembershipID";
            cmbMembershipType.DataBind();
            cmbMembershipType.Items.Insert(0, "Select Membership");
        }

        protected void btnGetReports_Click(object sender, EventArgs e)
        {

        }

        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {
            ClearForm();

            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Write("CRMCustomerMaster.aspx");
            }
            else
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Value));
                if (DT.Rows.Count > 0)
                {
                    Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                    Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                    Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                    Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();
                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }


                if (cmbBranches.SelectedIndex == 1)
                {

                    BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);    // This will Return Branch FullName 
                    Cmb_BranchName = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), BranchFullName);
                    UPDATEBRANCH(Cmb_BranchName);

                }
                else
                {
                    BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);    // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + cmbBranches.SelectedItem.Value + " and cd_branchalias='" + Cmb_BranchName + "';");
                    RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
                    DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);

                }


                if (DtLicense.Rows.Count > 0)
                {

                    L_CRMCustomerMaster = DtLicense.Rows[0]["CRMCUSTOMERMASTER"].ToString();
                    Session["L_CRMCustomerMaster"] = L_CRMCustomerMaster;

                }

                if (Session["L_CRMCustomerMaster"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    if (cmbBranches.SelectedItem.Text == "HQ")
                    {
                        string _pagename = System.IO.Path.GetFileName(Request.Url.ToString());
                        if (_pagename.Equals("CRMCustomerMaster.aspx"))
                        {
                            Cmb_BranchName = Session["Branch_HQ"].ToString();
                            LoadMemberDataList(Cmb_BranchName);
                            UPDATEBRANCH(Cmb_BranchName);
                        }

                        LoadDefaultiMembership();


                    }
                    else
                    {
                        Response.Redirect("DeniedPage.aspx");
                    }




                }

                else
                {

                    if (cmbBranches.SelectedItem.Text == "HQ")
                    {

                        string _pagename = System.IO.Path.GetFileName(Request.Url.ToString());
                        if (_pagename.Equals("CRMCustomerMaster.aspx"))
                        {
                            Cmb_BranchName = Session["Branch_HQ"].ToString();
                            LoadMemberDataList(Cmb_BranchName);
                            UPDATEBRANCH(Cmb_BranchName);
                        }

                        LoadDefaultiMembership();



                    }
                    else
                    {
                        string _pagename = System.IO.Path.GetFileName(Request.Url.ToString());
                        if (_pagename.Equals("CRMCustomerMaster.aspx"))
                        {
                            Cmb_BranchName = Session["Branch_HQ"].ToString();
                            LoadMemberDataList(Cmb_BranchName);
                            UPDATEBRANCH(Cmb_BranchName);
                        }

                        LoadDefaultiMembership();


                    }



                }
            }
            UPDATEBRANCH(Cmb_BranchName);



        }

        protected void cmbMembershipType_SelectedIndexChanged(object sender, EventArgs e)
        {
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            // XSS Attack Prevention
            if (cmbBranches.Visible == false)
            {

                BranchAlias = Session["Branch_HQ"].ToString();

            }
            else
            {

                BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                if (BranchFullName == "HQ")
                {
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
            }

            // Prevents SQL Injections 
            // Parameterized Approach
            //string MobQuery = "select Mobile From {0}.icustomers where  Mobile = @Mobile;";
            //string command = string.Format(MobQuery, BranchAlias);
            //MySqlParameter[] parameters =
            //{
            //   new MySqlParameter("@Mobile",txtMobileNo.Text),
            //};

            //if (txtMobileNo.Text  == "")
            //{
            //    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid Mobile No ');location.href='CRMCustomerMaster.aspx'", true);
            //    return;
            //}

            if (BranchAlias != "")
            {
                AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);
                en_userName = ObjSel.EncryptString(txtUserName.Text, AES_Key);
            }


            //nid added on 29-Feb-2024
            string MobQuery = "select UserName From {0}.icustomers where  UserName = @UserName;";
            string command = string.Format(MobQuery, Session["Branch_HQ"].ToString());
            MySqlParameter[] parameters =
            {
               new MySqlParameter("@UserName",en_userName),
            };

            if (txtUserName.Text == "")
            {
                //ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid UserName ');location.href='CRMCustomerMaster.aspx'", true); //john hide this to prevent reload/refresh if alert message pop up
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid UserName ');", true);
                txtUserName.Text = string.Empty;
                return;
            }

            if (txtPassword.Text == "")
            {
                //ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid Password ');location.href='CRMCustomerMaster.aspx'", true); //john hide this to prevent reload/refresh if alert message pop up
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid Password ');", true);
                txtPassword.Text = string.Empty;
                return;
            }


            if (cmbMembershipType.Text == "Select Membership")
            {
                //ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid Membership Type ');location.href='CRMCustomerMaster.aspx'", true); //john hide this to prevent reload/refresh if alert message pop up
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid Membership Type ');", true);
                return;
            }


            MobValue = Convert.ToString(ObjMain.NewExecuteScalar(command, parameters));



            if (MobValue != "")
            {
                //ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Mobile No Already Exist ');location.href='CRMCustomerMaster.aspx'", true);
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' UserName Already Exist ');location.href='CRMCustomerMaster.aspx'", true);
                return;
            }
            else
            {
                string Active = "1";
                string PDPA = "1";
                Guid g = Guid.NewGuid();
                string sCID = g.ToString();
                // Insert : iCustomers 


                if (BranchAlias != "")
                {
                    AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);

                }

                #region Encrption Prams

                en_CustomerName = ObjSel.Generic_AES_Encrypt(txtCustomerName.Text.ToString().Trim(), AES_Key);
                en_Mobile = ObjSel.EncryptString(txtMobileNo.Text.ToString().Trim(), AES_Key);
                en_userName = ObjSel.EncryptString(txtUserName.Text.ToString().Trim(), AES_Key);
                // en_password = ObjSel.EncryptString(txtPassword.Text).ToString().Trim();
                en_password = ObjSel.ConvertToAES(HttpUtility.UrlDecode(txtPassword.Text), AES_Key).ToString().Trim();
                #endregion

                Ins_CustQuery = " INSERT INTO {0}.icustomers(CID, CustomerName,Email, Address, PostalCode, DOB, RegistrationDate, PDPA, Active, MembershipType, Notes, UserName, Password)" +
                                " VALUES " +
                                " (@CID,@CustomerName,@Email,@Address,@PostalCode,@DOB,@RegistrationDate,@PDPA,@Active,@MembershipType,@Notes, @UserName, @Password) ";
                string commandA = string.Format(Ins_CustQuery, BranchAlias);
                MySqlParameter[] paramss =
                    {

                    new MySqlParameter("@CID",sCID),
                    new MySqlParameter("@CustomerName", en_CustomerName),
                   // new MySqlParameter("@Mobile", en_Mobile),
                    new MySqlParameter("@Email",txtEmail.Text ),
                    new MySqlParameter("@Address", txtAddress.Text ),
                    new MySqlParameter("@PostalCode", txtPostalCode.Text ),
                    new MySqlParameter("@DOB", Convert.ToDateTime(txtDOB.Text)),
                    new MySqlParameter("@RegistrationDate",Curr_Date),
                    new MySqlParameter("@PDPA", PDPA),
                    new MySqlParameter("@Active", Active ),
                    new MySqlParameter("@MembershipType", cmbMembershipType.SelectedValue ),
                    new MySqlParameter("@Notes", txtNotes.Text ),
                    new MySqlParameter("@UserName", en_userName),
                    new MySqlParameter("@Password", en_password ),

                };

                K = ObjMain.ExecuteQuery(commandA, paramss);

                //john added on 30-July-2024 Insert iCustomerPIN
                string iCustomerPINQuery = "INSERT INTO {0}.icustomerspin(cid,pin,generatedate)values(@cid,@pin,@generatedate)";
                string cmd = string.Format(iCustomerPINQuery, BranchAlias);
                MySqlParameter[] paramsPIN =
                {
                    new MySqlParameter("@cid",sCID),
                    new MySqlParameter("@pin",txtPIN.Text.Trim()),
                    new MySqlParameter("@generatedate",Curr_DateOnly)
                };
                L = ObjMain.ExecuteQuery(cmd, paramsPIN);


                #region  Insert CurrentPoint Balance Row In iCurrentPoints Table 28-09-2024
                string icurrentPoint_Query = "Insert into {0}.icurrentPoints(CID,CurrentPoint,UpdatedDate)values(@CID,@CurrentPoint,@UpdatedDate);";
                string icurrentPoint_cmd = string.Format(icurrentPoint_Query, BranchAlias);
                decimal _cp = 0;
                MySqlParameter[] paramsCurrentPoint =
                    {
                       new MySqlParameter("@CID",sCID),
                       new MySqlParameter("@CurrentPoint",_cp),
                       new MySqlParameter("@UpdatedDate",Curr_Date)
                    };

                U = ObjMain.ExecuteQuery(icurrentPoint_cmd, paramsCurrentPoint);


                #endregion



                if (K > 0)
                {
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Customer Registration completed ');location.href='CRMCustomerMaster.aspx'", true);
                    btnClear_Click(sender, e);
                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('Somthing went wrong : Customer Registration Not completed ');location.href='CRMCustomerMaster.aspx'", true);
                }



            }


        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            if (txtPIN.Text.Length < 6)
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('Please Enter Minimum of 6 Digits')", true);
                txtPIN.Text = "";
                txtPIN.Focus();
                return;
            }

            // XSS Attack Prevention
            if (cmbBranches.Visible == false)
            {

                BranchAlias = Session["Branch_HQ"].ToString();

            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                if (BranchFullName == "HQ")
                {
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
            }

            //if (txtMobileNo.Text == "")
            //{
            //    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid Mobile No ');location.href='CRMCustomerMaster.aspx'", true);
            //    return;
            //}
            if (txtUserName.Text == "")
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid UserName ');location.href='CRMCustomerMaster.aspx'", true);
                return;
            }
            if (txtPassword.Text == "")
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid Password ');location.href='CRMCustomerMaster.aspx'", true);
                return;
            }

            if (cmbMembershipType.SelectedValue == "Select Membership")
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Please enter valid Mobile No ');location.href='CRMCustomerMaster.aspx'", true);
                return;
            }

            string Active = "1";
            if (chkActive.Checked == true)
            {
                Active = "1";
            }
            else
            {
                Active = "0";
            }

            string AllowAds = "1";

            if(chkAllowAds.Checked == true)
            {
                AllowAds = "1";
            }
            else
            {
                AllowAds = "0";
            }
            string UQuery = "UPDATE  {0}.icustomers SET " +
                " CustomerName=@CustomerName,Email=@Email,Address=@Address,PostalCode=@PostalCode,DOB=@DOB,Active=@Active,MembershipType=@MembershipType,Notes=@Notes,UserName=@UserName,Password=@Password,AllowAdvertisement=@AllowAdvertisement " +
                " WHERE CID=@CID  ";
            string command = string.Format(UQuery, BranchAlias);



            if (BranchAlias != "")
            {
                AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);

            }

            #region Encrption Prams

            en_CustomerName = ObjSel.Generic_AES_Encrypt(txtCustomerName.Text.ToString().Trim(), AES_Key);
            //en_Mobile = ObjSel.EncryptString(txtMobileNo.Text.ToString().Trim(), AES_Key);
            en_userName = ObjSel.EncryptString(txtUserName.Text.ToString().Trim(), AES_Key);
            en_password = ObjSel.EncryptString(txtPassword.Text.ToString().Trim(), AES_Key);

            #endregion

            MySqlParameter[] paramss =
                {
                    new MySqlParameter("@CID", txtCID.Text),
                    new MySqlParameter("@CustomerName", en_CustomerName),
                   // new MySqlParameter("@Mobile", en_Mobile),
                    new MySqlParameter("@Email",txtEmail.Text ),
                    new MySqlParameter("@Address", txtAddress.Text ),
                    new MySqlParameter("@PostalCode", txtPostalCode.Text ),
                    new MySqlParameter("@DOB", Convert.ToDateTime(txtDOB.Text)),
                    new MySqlParameter("@Active", Active ),
                    new MySqlParameter("@AllowAdvertisement", AllowAds),
                    new MySqlParameter("@MembershipType", cmbMembershipType.SelectedValue ),
                    new MySqlParameter("@Notes", txtNotes.Text ),
                    new MySqlParameter("@UserName", en_userName ),
                    new MySqlParameter("@Password", en_password ),
                };
            U = ObjMain.ExecuteQuery(command, paramss);

            //Update iCustomersPIN john added on 29-July-2024
            string iCRMPIN = "UPDATE {0}.icustomerspin SET PIN = @pin where CID = @cid";
            string commandPIN = string.Format(iCRMPIN, BranchAlias);

            MySqlParameter[] paramPIN = {
                new MySqlParameter("@pin",txtPIN.Text.Trim()),
                new MySqlParameter("@cid",txtCID.Text.Trim()),
            };
            ObjMain.ExecuteQuery(commandPIN, paramPIN);


            if (U > 0)
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Member details Updated Successfully ') ;location.href='CRMCustomerMaster.aspx'", true);
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Member details Not Updated Successfully ') ", true);
            }

        }

        protected void btnClear_Click(object sender, EventArgs e)
        {
            ClearForm();
        }

        public void ClearForm()
        {
            chkPDPAStatus.Checked = true;
            chkActive.Checked = true;
            chkAllowAds.Checked = true;
            txtMobileNo.Text = "";
            txtCID.Text = "";
            txtCustomerName.Text = "";
            txtDateRegistered.Text = Curr_DateOnly;
            txtDOB.Text = Curr_DateOnly;
            txtEmail.Text = "";
            txtPostalCode.Text = "";
            txtAddress.Text = "";
            txtNotes.Text = "";
            txtUserName.Text = "";

            this.txtPassword.Attributes.Add("value", "");

            btnSave.Visible = true;
            btnSave.Enabled = true;
            btnUpdate.Visible = false;
            btnUpdate.Enabled = false;


            this.txtPIN.Attributes.Add("value", "");

            LoadDefaultiMembership();


        }

        protected void btnMemSearch_Click(object sender, EventArgs e)
        {
            btnDelete.Visible = true;
            BranchAlias = Session["Branch_HQ"].ToString();
            Load_SearchData(BranchAlias, txtUserNameSearch.Text.ToString().Trim());
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowModalMemberInformationList();", true);
        }

        public void Load_SearchData(string BranchNam, string SearchTxt)
        {
            btnDelete.Visible = true;

            if (cmbBranches.Visible == false)
            {

                BranchAlias = Session["Branch_HQ"].ToString();

            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Session["Branch_HQ"].ToString();
            }


            #region UserName Decrption 
            if (BranchAlias != "" || Cmb_BranchName != "")
            {
                if (cmbBranches.SelectedItem.Text == "HQ")
                {
                    AES_Key = ObjApi.Get_AESKey(BranchAlias);
                    en_userName = ObjSel.EncryptString(SearchTxt, AES_Key);
                    UPDATEBRANCH(BranchAlias);
                }
                else
                {
                    AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);
                    en_userName = ObjSel.EncryptString(SearchTxt, AES_Key);
                    UPDATEBRANCH(Cmb_BranchName);

                }




            }


            #endregion


            //string QuerySrc = "Select CustomerID,Mobile,CustomerName  From   {0}.icustomers  Where  Mobile like CONCAT('%', @Mobile, '%')  ;";
            string QuerySrc = "Select CustomerID,UserName,CustomerName  From   {0}.icustomers  Where  UserName=@UserName;";
            string command = string.Format(QuerySrc, BranchAlias);

            MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@UserName",en_userName),
                   };

            DtMemberList = ObjMain.NewGetData_DT(command, parameters);


            DataTable temp_dt = new DataTable();

            if (DtMemberList.Rows.Count > 0)
            {
                foreach (DataRow row in DtMemberList.Rows)
                {
                    string value = row.Field<string>("UserName");
                    row.SetField("UserName", ObjSel.DecryptString(value, AES_Key));
                    string value1 = row.Field<string>("CustomerName");
                    row.SetField("CustomerName", ObjSel.DecryptString(value1, AES_Key));
                }

                grdMember.DataSource = DtMemberList;
                grdMember.DataBind();
            }
            else
            {
                customeridmsg.Visible = true;
                grdMember.DataSource = null;
                grdMember.DataBind();
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowModalMemberInformationList();", true);

            }
        }

        public void LoadMemberDataList(string sDb)
        {

            if (cmbBranches.Visible == false)
            {

                BranchAlias = Session["Branch_HQ"].ToString();

            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Session["Branch_HQ"].ToString();
            }

            DtMemberList = ObjSel.GetMemberList(sDb);
            DataTable temp_dt = new DataTable();

            #region UserName Decrption 
            if (Cmb_BranchName != "" || BranchAlias != "")
            {

                if (cmbBranches.SelectedItem.Text == "HQ")
                {
                    AES_Key = ObjApi.Get_AESKey(BranchAlias);
                }
                else
                {
                    AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);
                }


            }


            #endregion

            if (DtMemberList.Rows.Count > 0)
            {
                foreach (DataRow row in DtMemberList.Rows)
                {
                    string value = row.Field<string>("UserName");
                    row.SetField("UserName", ObjSel.DecryptString(value, AES_Key));
                    string value1 = row.Field<string>("CustomerName");
                    row.SetField("CustomerName", ObjSel.DecryptString(value1, AES_Key));
                }

                grdMember.DataSource = DtMemberList;
                grdMember.DataBind();
            }



        }
        protected void grdMember_SelectedIndexChanged(object sender, EventArgs e)
        {


            // XSS Attack Prevention 
            if (cmbBranches.Visible == false)
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                if (cmbBranches.SelectedItem.Text == "HQ")
                {
                    // btnExcel.Enabled = true;
                    // HQ Validation    02-12-21
                    BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                    Cmb_BranchName = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), BranchFullName);
                    UPDATEBRANCH(Cmb_BranchName);
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Cmb_BranchName;
                }
            }

            btnSave.Visible = false;
            btnSave.Enabled = false;
            btnUpdate.Visible = true;
            btnUpdate.Enabled = true;
            btnDelete.Visible = true;
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ToshowDelButton();", true);
            #region UserName Decrption 
            if (Cmb_BranchName != "")
            {
                AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);

            }


            #endregion

            RCustomerID = (grdMember.SelectedRow.FindControl("lblCustomerID") as Label).Text;
            DTMemberDL = ObjSel.ReadMemberInformations(Session["Branch_HQ"].ToString(), RCustomerID);
            DTCustomerPINDL = ObjSel.ReadiCrmCustomerPIN(Session["Branch_HQ"].ToString(), DTMemberDL.Rows[0]["CID"].ToString().Trim());
            //txtMobileNo.Text = DTMemberDL.Rows[0]["Mobile"].ToString();
            txtCID.Text = DTMemberDL.Rows[0]["CID"].ToString();

            string R_customerName = DTMemberDL.Rows[0]["CustomerName"].ToString();
            txtCustomerName.Text = ObjSel.DecryptString(R_customerName, AES_Key);
            txtDateRegistered.Text = ((DateTime)DTMemberDL.Rows[0]["RegistrationDate"]).ToString("yyyy-MM-dd");
            txtDOB.Text = ((DateTime)DTMemberDL.Rows[0]["DOB"]).ToString("yyyy-MM-dd");
            txtAddress.Text = DTMemberDL.Rows[0]["Address"].ToString();
            txtPostalCode.Text = DTMemberDL.Rows[0]["PostalCode"].ToString();

            txtEmail.Text = DTMemberDL.Rows[0]["Email"].ToString();
            //cmbMembershipType.SelectedIndex = cmbMembershipType.Items.IndexOf(cmbMembershipType.Items.FindByValue("MembershipType"));
            cmbMembershipType.SelectedValue = DTMemberDL.Rows[0]["MembershipType"].ToString();
            txtNotes.Text = DTMemberDL.Rows[0]["Notes"].ToString();
            txtUserName.Text = ObjSel.DecryptString(DTMemberDL.Rows[0]["UserName"].ToString(), AES_Key);
            //txtPassword.Text = DTMemberDL.Rows[0]["Password"].ToString();
            //PIN
            //txtPIN.Text = DTCustomerPINDL.Rows[0]["PIN"].ToString();
            string R_password = DTMemberDL.Rows[0]["Password"].ToString();
            this.txtPIN.Attributes.Add("value", DTCustomerPINDL.Rows[0]["PIN"].ToString());
            this.txtPassword.Attributes.Add("value", ObjSel.DecryptString(R_password, AES_Key));


            // Get and Load PointExpiry Date
            string r_pointexpirydt = string.Empty;
            string command_get_pointexpirydate = "  select  date(PointExpiryOn) from {0}.iCurrentPoints where cid='"+ txtCID.Text.ToString().Trim() + "';";

            string  select_command = string.Format(command_get_pointexpirydate, Session["Branch_HQ"].ToString());

            r_pointexpirydt = ObjMain.ExecuteScalar(select_command);

            DateTime dtall = DateTime.Parse(r_pointexpirydt);
            string dateOnly = dtall.ToString("yyyy-MM-dd");
            txtPointExpiryDate.Text = dateOnly;
            txtPointExpiryDate.Enabled = false;

            if (DTMemberDL.Rows[0]["Active"].ToString() == "1")
            {
                chkActive.Checked = true;
            }
            else
            {
                chkActive.Checked = false;
            }

            if (DTMemberDL.Rows[0]["PDPA"].ToString() == "1")
            {
                chkPDPAStatus.Checked = true;
            }
            else
            {
                chkPDPAStatus.Checked = false;
            }

            if(DTMemberDL.Rows[0]["AllowAdvertisement"].ToString() == "1")
            {
                chkAllowAds.Checked = true;
            }
            else
            {
                chkAllowAds.Checked = false;
            }



        }

        protected void btnAddNew_Click(object sender, EventArgs e)
        {
            ClearForm();
            Response.Redirect(HttpContext.Current.Request.Url.ToString(), true);
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {

            #region  Validation Between HQ Branch and Normal Branch 
            BranchFullName = cmbBranches.SelectedItem.Text;             // This will Return Branch FullName 
            if (BranchFullName == "HQ")
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;         // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Session["Branch_HQ"].ToString();

            }
            #endregion

            #region Delete icustomers on HQ Table    
            string Del_Inventorycategories = " Delete from {0}.icustomers where CID=@CID; ";
            string Del_command = string.Format(Del_Inventorycategories, BranchAlias);
            MySqlParameter[] Del_Inventorycategories_parameters =
                       {
                             new MySqlParameter("@CID",txtCID.Text),

                        };
            L = ObjMain.ExecuteQuery(Del_command, Del_Inventorycategories_parameters);
            // Delete icustomerspin in HQ Db
            string Del_Menu = " Delete from {0}.icustomerspin where CID=@CID; ";
            string Del_Menu_Command = string.Format(Del_Menu, BranchAlias);
            MySqlParameter[] Del_Menu_parameters =
                       {
                             new MySqlParameter("@CID",txtCID.Text),

                        };
            S = ObjMain.ExecuteQuery(Del_Menu_Command, Del_Menu_parameters);

            #region  Delete IcurrentPoint   Added On 28-08-2024

            string Del_CurrentPoint = "Delete from  {0}.icurrentpoints where CID=@CID; ";
            string Del_CurrentPoint_command = string.Format(Del_CurrentPoint, BranchAlias);
            MySqlParameter[] Del_CurrentPoint_Parameter =
                      {
                        new MySqlParameter("@CID",txtCID.Text),
                      };

            U = ObjMain.ExecuteQuery(Del_CurrentPoint_command, Del_CurrentPoint_Parameter);

            #endregion



            if (L > 0 && S > 0)
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('User ID Deleted Successfully');location.href='CRMCustomerMaster.aspx'", true);
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('User ID Not Deleted');", true);
            }
            #endregion



        }

        private void LoadCDRValue(string BranchName)
        {
            //DataTable dtiCrmSetting;
            //string query = "select customer_data_retention from {0}.icrmsetting";
            //string command = string.Format(query, BranchAlias);
            //dtiCrmSetting = ObjMain.NewGetData_DT(command);
            //MysqlObj.GetData_D\
            //Int32 i;
            string query = "SELECT customer_data_retention_period from " + BranchName + ".icrmsetting";
            string Read_Value = ObjMain.ExecuteScalar(query);
            if (Read_Value != "")
            {
                cmbYearSelected.SelectedValue = Read_Value;
                // cmbMembershipType.DataBind();
            }
            else
            {
                cmbYearSelected.SelectedIndex = 0;
                string queryInsert = "INSERT INTO " + BranchName + ".`icrmsetting`(customer_data_retention_period)values(@customer_data_retention_period);";
                string command = string.Format(queryInsert, BranchAlias);
                MySqlParameter[] param =
                {
                    new MySqlParameter("@customer_data_retention_period", 3),
                };
                ObjMain.ExecuteQuery(command, param);
                //cmbMembershipType.DataBind();
            }


        }

        protected void btnUpdateCDR_Click(object sender, EventArgs e)
        {
            Int32 i;

            BranchAlias = Session["Branch_HQ"].ToString();

            string query = "UPDATE {0}.icrmsetting set customer_data_retention_period = @cutomer_data_retention_period";
            string command = string.Format(query, BranchAlias);
            MySqlParameter[] param =
            {
                new MySqlParameter("@cutomer_data_retention_period", cmbYearSelected.Text.Trim()),
            };
            i = ObjMain.ExecuteQuery(command, param);

            if (i > 0)
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('Customer Data Retention Period Updated.') ;location.href='CRMCustomerMaster.aspx'", true);
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('Customer Data Retention Period failed to update.')", true);
            }
        }

        protected void btnExcel_Click(object sender, EventArgs e)
        {
            
            Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cmbBranches.SelectedItem.Text);
            AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);
            DtMemberList = ObjSel.GetMemberInformation(Session["Branch_HQ"].ToString().Trim());
            foreach (DataRow row in DtMemberList.Rows)
            {                            
                row["Mobile No."] = ObjSel.DecryptString(row["Mobile No."].ToString().Trim(), AES_Key);
                //row["CustomerName"] = ObjSel.DecryptString(row["CustomerName"].ToString().Trim(), AES_Key);
            }    

            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = DtMemberList;
            GridView1.DataBind();
            Response.Clear();
            Response.Buffer = true;
            Response.ContentEncoding = System.Text.Encoding.Unicode;
            Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
            Response.Charset = "";
            Response.AddHeader("content-disposition",
                "attachment;filename=customerlist.xls");

            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridView1.Rows[i].Attributes.Add("class", "textmode");
            }
            GridView1.RenderControl(hw);
            string style = @"<style> td { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();


        }
    }
}