using CLS_BL;
using CLS_DL;
using CLS_PL;
using DocumentFormat.OpenXml.Bibliography;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.EnterpriseServices;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using static SUNWEB.InteractiveDashboard;

namespace SUNWEB
{
    public partial class PriceSwitch : System.Web.UI.Page
    {

        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;
        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();
        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_PriceSwitch = string.Empty;
        string Branchidval = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;

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

        string Vw_Chk = string.Empty;
        string RBranchid = string.Empty;
        string Branchid = string.Empty;
        string BranchAlias = string.Empty;
        string sActive = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {

            PID = "63";    // This is for Priceswitch Page 


            if (!IsPostBack)
            {
               
            }
            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)
                {
                    LoadBranchName4SuperAdmin();

                    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)
                {
                    LoadBranchName4SuperAdmin();
                }
                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                //LoadInventoryListGrid(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName();
                    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_RetailPayment"].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 == "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            }


            ISHQ = ObjMain.ExecuteScalar("select BranchName from " + ActiveCallvalue + ".branches where Branchname='" + ActiveCallvalue + "' and branchID=1;");  //now 

            if (ISHQ != "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
                UPDATEBRANCH(ActiveCallvalue);
            }

            cd_BranchAlias = ActiveCallvalue;
            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);
            DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            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_PriceSwitch = DtLicense.Rows[0]["PRICESWITCH"].ToString();
                Session["L_PriceSwitch"] = L_PriceSwitch;
            }
            if (Session["L_PriceSwitch"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {

                //btnExcel.Enabled = true;
                //lblMsgTotalSelection.Text = "Total Sale Selection Between" + "&nbsp;&nbsp;&nbsp;" + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                // BindGridview(ActiveCallvalue, txtStartDt.Text, txtEndDt.Text);
                LoadPriceSwitch(ActiveCallvalue);
                LoadInventoryview();
                LoadPriceGroup();
                LoadPriceGroupName();
                btnUpdateGroup.Visible = false;
                btnSaveGroup.Visible = true;
                System.Threading.Thread.Sleep(2000);

            }

        }


        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");

            }

        }


        protected void btnGetReports_Click(object sender, EventArgs e)
        {

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

            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Write("Iorderlasetting.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;
                }

                ISHQ = ObjMain.ExecuteScalar("select BranchName from branches where Branchname='" + ActiveCallvalue + "' and branchID=1;");  //now 

                if (ISHQ != "")
                {
                    ActiveCallvalue = ObjSel.GetFirstBranch7(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
                    UPDATEBRANCH(ActiveCallvalue);
                }
                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=" + Microsoft.Security.Application.Encoder.HtmlEncode(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_MaxUserCnt = DtLicense.Rows[0]["UserCnt"].ToString();
                    L_PriceSwitch = DtLicense.Rows[0]["PRICESWITCH"].ToString();
                    Session["L_PriceSwitch"] = L_PriceSwitch;
                }

                if (Session["L_PriceSwitch"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    Response.Redirect("DeniedPage.aspx");

                }
                else if (Session["Write_Acess"].ToString() == "N")
                {
                  
                }

                else
                {
                    // btnExcel.Enabled = true;
                    LoadPriceSwitch(Cmb_BranchName);
                    LoadInventoryview();
                    LoadPriceGroup();
                    LoadPriceGroupName();
                }
            }
            UPDATEBRANCH(Cmb_BranchName);

        }

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

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

            }
            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 )
                BranchAlias = Cmb_BranchName;
            }


            foreach (GridViewRow gvrow in grdPriceSwitch.Rows)
            {
                var checkbox = gvrow.FindControl("chkSelect") as CheckBox;
                if (checkbox.Checked)
                {
                    Label lblInvenid = gvrow.FindControl("lblInventoryid") as Label;
                    Label lblbarcode = gvrow.FindControl("lblBarcode") as Label;
                    CheckBox checkBox_Val = gvrow.FindControl("ChoiceCheckBox") as CheckBox;

                    DropDownList cmbswitchprice= gvrow.FindControl("cmbPriceswitch") as DropDownList;

                    TextBox txtRT1_StTm = (TextBox)gvrow.FindControl("txtT1_StTm");
                    TextBox txtRT1_EndTm = (TextBox)gvrow.FindControl("txtT1_EndTm");
                    

                    string Added = txtRT1_StTm.Text;
                    string NewStartDt = string.Concat(Curr_DateOnly, " ", Added);
                    string Added1 = txtRT1_EndTm.Text;
                    string NewEndtDt = string.Concat(Curr_DateOnly, " ", Added1);
                   

                    string chk_Values = string.Empty;
                    
                    if (checkBox_Val.Checked == true)
                    {
                        chk_Values = "1";


                    }
                    else if (checkBox_Val.Checked == false)
                    {
                        chk_Values = "0";
                    }



                    string Query = "Update  {0}.SwitchPrice set Start_Tm=@Start_Tm, End_Tm=@End_Tm ,SwitchPrice=@SwitchPrice,IsEnable=@IsEnable  where Inventoryid=@Inventoryid AND Barcode=@Barcode; ";
                    string command = string.Format(Query, BranchAlias);

                    MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@Start_Tm",NewStartDt),
                     new MySqlParameter("@End_Tm",NewEndtDt),
                     new MySqlParameter("@SwitchPrice",cmbswitchprice.SelectedItem.Text),
                     new MySqlParameter("@IsEnable",chk_Values),
                     new MySqlParameter("@Inventoryid",lblInvenid.Text),
                     new MySqlParameter("@Barcode",lblbarcode.Text),
                   };

                    K = ObjMain.ExecuteQuery(command, parameters);
                    LoadPriceSwitch(BranchAlias);
                    if (K > 0)
                    {
                        ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' PriceSwitch Updated ');location.href='PriceSwitch.aspx'", true);

                    }
                    else
                    {
                        ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Something went Wrong ');location.href='PriceSwitch.aspx'", true);
                    }

                        

                    
                }

            }






        }


        public void LoadPriceSwitch(string Branchname)
        {
            string query = "SELECT   CASE  WHEN ROW_NUMBER() OVER (PARTITION BY G.PSGroupName ORDER BY S.Barcode) = 1 " 
              + " THEN  G.PSGroupName  " 
              + " ELSE ''    END AS GroupName,   S.Inventoryid,    S.Barcode,    S.SwitchPrice," 
              + " S.Start_Tm , S.End_Tm ,S.IsEnable ,S.PSGID,i.InventoryName ,I.Description "
              + " FROM    " 
              + " {0}.SwitchPrice AS S  "
              + " INNER JOIN {0}.Inventory as i  on i.inventoryid = s.inventoryid "
              + " INNER JOIN    " 
              + " {0}.SwitchPriceGroup AS G  ON    S.PSGID = G.PSGID ORDER BY    G.PSGroupName, S.Barcode;";
            string command = string.Format(query, Branchname);
            DT = ObjMain.GetData_DT(command);
            grdPriceSwitch.DataSource = DT;
            grdPriceSwitch.DataBind();
        }

        protected void grdPriceSwitch_RowDataBound(object sender, GridViewRowEventArgs e)
        {

            if (cmbBranches.Visible == false)
            {

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

            }
            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 )
                BranchAlias = Cmb_BranchName;
            }

            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                //Find the DropDownList in the Row
         
         
                Label lblswitchPriceval = (e.Row.FindControl("lblSwitchPrice") as Label);
                DropDownList _cmbPriceswitch = (e.Row.FindControl("cmbPriceswitch") as DropDownList);

                if (lblswitchPriceval.Text == "Price2")
                {

                    _cmbPriceswitch.Items.Add("Price2");
                    _cmbPriceswitch.Items.Add("Price3");
                    _cmbPriceswitch.Items.Add("Price4");
                    
                    _cmbPriceswitch.DataBind();
                    _cmbPriceswitch.SelectedItem.Text = lblswitchPriceval.Text;

                }
                else if (lblswitchPriceval.Text == "Price3")
                {
                    _cmbPriceswitch.Items.Add("Price3");
                    _cmbPriceswitch.Items.Add("Price2");
                    _cmbPriceswitch.Items.Add("Price4");
                    
                    _cmbPriceswitch.DataBind();
                    _cmbPriceswitch.SelectedItem.Text = lblswitchPriceval.Text;


                }

                else if (lblswitchPriceval.Text == "Price4")
                {
                    _cmbPriceswitch.Items.Add("Price4");
                    _cmbPriceswitch.Items.Add("Price2");
                    _cmbPriceswitch.Items.Add("Price3");
                    
                    _cmbPriceswitch.DataBind();
                    _cmbPriceswitch.SelectedItem.Text = lblswitchPriceval.Text;


                }



                lblswitchPriceval.Visible = false;

            }

        }

        protected void grdPriceSwitch_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (cmbBranches.Visible == false)
            {

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

            }
            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 )
                BranchAlias = Cmb_BranchName;
            }


            if (e.CommandName == "Select")
            {
                int index = Convert.ToInt32(e.CommandArgument);
                GridViewRow row = grdPriceSwitch.Rows[index];

                string Invenid = (row.FindControl("lblInventoryid") as Label).Text;
                string barcode = (row.FindControl("lblBarcode") as Label).Text;
                CheckBox checkBox_Val = (row.FindControl("ChoiceCheckBox") as CheckBox);

                DropDownList cmbswitchprice = (row.FindControl("cmbPriceswitch") as DropDownList);

                TextBox txtRT1_StTm = (TextBox)(row.FindControl("txtT1_StTm"));
                TextBox txtRT1_EndTm = (TextBox)(row.FindControl("txtT1_EndTm"));


                string Added = txtRT1_StTm.Text;
                string NewStartDt = string.Concat(Curr_DateOnly, " ", Added);
                string Added1 = txtRT1_EndTm.Text;
                string NewEndtDt = string.Concat(Curr_DateOnly, " ", Added1);


                string chk_Values = string.Empty;

                if (checkBox_Val.Checked == true)
                {
                    chk_Values = "1";


                }
                else if (checkBox_Val.Checked == false)
                {
                    chk_Values = "0";
                }


                string Query = "Update  {0}.SwitchPrice set Start_Tm=@Start_Tm, End_Tm=@End_Tm ,SwitchPrice=@SwitchPrice,IsEnable=@IsEnable  where Inventoryid=@Inventoryid AND Barcode=@Barcode; ";
                string command = string.Format(Query, BranchAlias);

                MySqlParameter[] parameters =
               {
                     new MySqlParameter("@Start_Tm",NewStartDt),
                     new MySqlParameter("@End_Tm",NewEndtDt),
                     new MySqlParameter("@SwitchPrice",cmbswitchprice.SelectedItem.Text),
                     new MySqlParameter("@IsEnable",chk_Values),
                     new MySqlParameter("@Inventoryid",Invenid),
                     new MySqlParameter("@Barcode",barcode),
                   };

                K = ObjMain.ExecuteQuery(command, parameters);
                //LoadPriceSwitch(BranchAlias);
                if (K > 0)
                {                    
                 ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Price Switch Edited Successfully  ');location.href='PriceSwitch.aspx'", true);
                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Something Went Wrong.');location.href='PriceSwitch.aspx'", true);
                }

            }
            else if (e.CommandName == "Delete")
            {
                int index = Convert.ToInt32(e.CommandArgument);
                GridViewRow row = grdPriceSwitch.Rows[index];

                string Invenid = (row.FindControl("lblInventoryid") as Label).Text;
                string barcode = (row.FindControl("lblBarcode") as Label).Text;


                string Query = " Delete from {0}.SwitchPrice where Inventoryid=@Inventoryid AND Barcode=@Barcode; ";
                string command = string.Format(Query, BranchAlias);

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@Inventoryid",Invenid),
                     new MySqlParameter("@Barcode",barcode),
                   };

                K = ObjMain.ExecuteQuery(command, parameters);
               // LoadPriceSwitch(BranchAlias);
                if (K > 0)
                {
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' PriceSwitch Deleted ');location.href='PriceSwitch.aspx'", true);

                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Something went Wrong ');location.href='PriceSwitch.aspx'", true);
                }



            }
            else if (e.CommandName == "Select")
            {
                int index = Convert.ToInt32(e.CommandArgument);
                GridViewRow row = grdPriceSwitch.Rows[index];

                string Invenid = (row.FindControl("lblInventoryid") as Label).Text;
                string barcode = (row.FindControl("lblBarcode") as Label).Text;
              

            }




        }


        protected void btnToggle_Click(object sender, EventArgs e)
        {
            bool selectAll = (btnToggle.Text == "Select All");

            foreach (GridViewRow row in grdPriceSwitch.Rows)
            {
                CheckBox chk = row.FindControl("ChoiceCheckBox") as CheckBox;
                if (chk != null)
                {
                    chk.Checked = selectAll;
                }
            }

            btnToggle.Text = selectAll ? "Unselect All" : "Select All";
        }


        protected void cmbSearch_SelectedIndexChanged(object sender, EventArgs e)
        {
            //  ModalPopupExtender7.Show();
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowModalInventoryList();", true);
            if (cmbBranches.Visible == true)
            {
                #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
            }
            else
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            if (cmbSearch.SelectedIndex == 1)
            {
                //txtSearchVal.Text = "";
                LoadInventoryview();
                txtSearchVal.Enabled = true;
            }
            else if (cmbSearch.SelectedIndex == 2)
            {
                //txtSearchVal.Text = "";
                LoadInventoryview();
            }
            else if (cmbSearch.SelectedIndex == 3)
            {
                //txtSearchVal.Text = "";
                LoadInventoryview();
                txtSearchVal.Enabled = true;
            }
            else if (cmbSearch.SelectedIndex == 4)
            {
                //txtSearchVal.Text = "";
                LoadInventoryview();
            }
            else if (cmbSearch.SelectedIndex == 5)
            {
                //txtSearchVal.Text = "";
                LoadInventoryview();
                txtSearchVal.Enabled = true;
            }
            if (cmbSearch.SelectedIndex == 6)
            {
                //txtSearchVal.Text = "";
                txtSearchVal.Enabled = true;
            }
            else if (cmbSearch.SelectedIndex == 1 || cmbSearch.SelectedIndex == 2 || cmbSearch.SelectedIndex == 3 || cmbSearch.SelectedIndex == 4 || cmbSearch.SelectedIndex == 5 || cmbSearch.SelectedIndex == 6)
            {

            }
            else
            {
                LoadInventoryview();
            }

            if (cmbSearch.SelectedIndex == 7)
            {

                txtSearchVal.Text = "";

            }




        }

        public void LoadInventoryview(string sortExpression = null)
        {
            if (cmbBranches.SelectedIndex == 1)
            {

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

            }
            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 )
                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);
                Session["Active_Branch"] = Cmb_BranchName;

            }

            /*  string InvenQuery = "select *,sum(pquantity-(CutQuantity+RetailQty))as OnHandICA from " + BranchAlias + ".inventory as I  " +
                                  " join " + BranchAlias + ".inventorycategories  as c ON I.Categoryid=c.categoryid  " +
                                  " join " + BranchAlias + ".inventorycostaverage ic on ic.inventoryid=i.inventoryid  " +
                                  " where i.active  <> 0  group by i.inventoryid  ORDER BY I.inventoryID asc LIMIT 50000;";
             */

            string InvenQuery = "select *,sum(pquantity-(CutQuantity+RetailQty))as OnHandICA from {0}.inventory as I  " +
                                " join {0}.inventorycategories  as c ON I.Categoryid=c.categoryid  " +
                                " join {0}.inventorycostaverage ic on ic.inventoryid=i.inventoryid " +
                                " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                                " where P.iNVENTORYID IS NULL AND  i.active  <> 0  group by i.inventoryid  ORDER BY I.inventoryID asc LIMIT 50000;";

            string Command = string.Format(InvenQuery, Cmb_BranchName);
            DT = ObjMain.GetData_DT(Command);


            if (sortExpression != null)
            {
                DataView dv = DT.AsDataView();
                this.SortDirection = this.SortDirection == "ASC" ? "DESC" : "ASC";

                dv.Sort = sortExpression + " " + this.SortDirection;
                grdInventoryView.DataSource = dv;
            }
            else
            {
                grdInventoryView.DataSource = DT;
            }
            grdInventoryView.DataBind();


        }

        public void LoadPriceGroup()
        {

            if (cmbBranches.SelectedIndex == 1)
            {

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

            }
            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 )
                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);
                Session["Active_Branch"] = Cmb_BranchName;

            }

               string PriceGroupQuery = "SELECT * FROM {0}.switchpriceGroup;";
               string Command = string.Format(PriceGroupQuery, Cmb_BranchName);
               DT = ObjMain.GetData_DT(Command);
               grdPriceGroup.DataSource = DT;
               grdPriceGroup.DataBind();
        
        }

        public void LoadPriceGroupName()
        {

            if (cmbBranches.SelectedIndex == 1)
            {

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

            }
            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 )
                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);
                Session["Active_Branch"] = Cmb_BranchName;

            }

            string QueryPriceGroup = "select * from {0}.switchpriceGroup ;";

            string Command = string.Format(QueryPriceGroup, Cmb_BranchName);
            DT = ObjMain.GetData_DT(Command);
            cmbPriceGroupName.DataSource = DT;
            
            cmbPriceGroupName.DataTextField = "PSGroupName";
            cmbPriceGroupName.DataValueField = "PSGID";
            cmbPriceGroupName.DataBind();
            cmbPriceGroupName.Items.Insert(0, "Select GroupPrice");






        }




        private string SortDirection
        {
            get { return ViewState["SortDirection"] != null ? ViewState["SortDirection"].ToString() : "ASC"; }
            set { ViewState["SortDirection"] = value; }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {

            string sActive = string.Empty;

            txtSearchVal.CssClass = "form-control";
            Page.ClientScript.RegisterStartupScript(this.GetType(), "hash", "location.hash = '#Inventoryx';", true);

            //Page.ClientScript.RegisterStartupScript(this.GetType(), "hash", "location.hash = '#GridContend';", true);
            //  ModalPopupExtender7.Show();
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowModalInventoryList();", true);

            if (cmbBranches.Visible == true)
            {
                #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

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

            if (cmbSearch.SelectedIndex == 0)
            {
                //John added This All Condition
                if (chkActiveonly.Checked == true)// John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " where I.active <> 0 AND  P.iNVENTORYID IS NULL";
                }
                else
                {
                    sActive = " where I.active = 0 AND  P.iNVENTORYID IS NULL";
                }
                string Inv_Qry1 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid  " 
                    + " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid  " + sActive;
                string NewQuery = string.Format(Inv_Qry1, BranchAlias);

                DT = ObjMain.GetData_DT(NewQuery);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();
            }
            else if (cmbSearch.SelectedIndex == 1)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string Inv_Qry1 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                      " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                      " where   P.iNVENTORYID IS NULL  AND I.Inventoryid=@SearchVal   " + sActive;
                string command = string.Format(Inv_Qry1, BranchAlias);

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                  };

                DT = ObjMain.NewGetData_DT(command, parameters);
                // DT = ObjMain.GetData_DT(Inv_Qry1);     Existing code 
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 2)
            {
                if (chkActiveonly.Checked == true)// John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }


                string Inv_Qry2 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                    " where  P.iNVENTORYID IS NULL AND I.barcode like CONCAT('%', @SearchVal, '%')  " + sActive;

                MySqlParameter[] parameters =
                {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };

                string command = string.Format(Inv_Qry2, BranchAlias);
                DT = ObjMain.NewGetData_DT(command, parameters);
                //  DT = ObjMain.GetData_DT(Inv_Qry2);  Exiting Code 
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 3)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }


                string Inv_Qry3 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " 
                    + " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " 
                    + " where P.iNVENTORYID IS NULL AND  I.InventoryName like CONCAT('%', @SearchVal, '%') " + sActive;

                MySqlParameter[] parameters =
                {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };

                string command = string.Format(Inv_Qry3, BranchAlias);
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 4)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }
                string Inv_Qry4 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " 
                    + " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " 
                    + " where  P.iNVENTORYID IS NULL AND  I.Description like CONCAT('%', @SearchVal, '%') " + sActive;

                MySqlParameter[] parameters =
               {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
               };

                string command = string.Format(Inv_Qry4, BranchAlias);
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 5)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string Inv_Qry4 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " 
                    + " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " 
                    + " where P.iNVENTORYID IS NULL AND  Price =@SearchVal  OR price2 =@SearchVal OR price3 =@SearchVal OR  price4 =@SearchVal  " + sActive;
                string command = string.Format(Inv_Qry4, BranchAlias);

                MySqlParameter[] parameters =
                {
                 new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();


            }
            else if (cmbSearch.SelectedIndex == 6)
            {
                string sActivex = string.Empty;

                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActivex = " and I.active <> 0";
                }
                else
                {
                    sActivex = " and I.active = 0";
                }


                //string Inv_Qry5 = "select * from " + BranchAlias + ".inventory as I join  " + BranchAlias + ".inventorycategories  as c ON I.Categoryid=c.categoryid ORDER BY I.inventoryID asc LIMIT " + txtTop.Text.ToString().Trim() + "";
                string Inv_Qry5 = "Select *,sum(pquantity-(CutQuantity+RetailQty))as OnHandICA from {0}.inventory as I " +
                                " join {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                                " join {0}.inventorycostaverage ic on ic.inventoryid=i.inventoryid " +
                                " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                                " where P.iNVENTORYID IS NULL AND  c.categoryname like CONCAT('%', @SearchVal, '%')   " + sActivex + "    group by I.inventoryid";

                string command = string.Format(Inv_Qry5, BranchAlias);
                MySqlParameter[] parameters =
                {
                 new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();
                //txtSearchVal.Text = "";

            }
            else if (cmbSearch.SelectedIndex == 7)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string sCmdMBarcode = "Select * from {0}.inventory as I join {0}.inventorycategories as B on I.CategoryID = B.CategoryID " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                    " Where  P.iNVENTORYID IS NULL AND Class = '10' and BarCode like CONCAT('%', @SearchVal, '%') " + sActive;


                string command = string.Format(sCmdMBarcode, BranchAlias);
                MySqlParameter[] parameters =
                {
                 new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }

            // Groups Condition
            if (cmbSearch.SelectedIndex == 8)
            {
                string group1Value = "group1";
                LoadGroupDetail(group1Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 9)
            {
                string group2Value = "group2";
                LoadGroupDetail(group2Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 10)
            {
                string group3Value = "group3";
                LoadGroupDetail(group3Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 11)
            {
                string group4Value = "group4";
                LoadGroupDetail(group4Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 12)
            {
                string group5Value = "group5";
                LoadGroupDetail(group5Value, txtSearchVal.Text.ToString().Trim());

            }
            // Search Option for Takeaway 
            else if (cmbSearch.SelectedIndex == 14)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                //string sCmdMBarcode = "Select * from {0}.inventory as A join {0}.inventorycategories as B on A.CategoryID = B.CategoryID Where Class = '10' and BarCode like CONCAT('%', @SearchVal, '%') " + sActive;

                string sCmdMBarcode = "select * from {0}.inventory as I  join  " +
                    " {0}.Webtakeawaychargeonly as TA ON I.Taid=TA.id join " +
                    " {0}.inventorycategories  as c ON I.Categoryid=c.categoryid  " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                    " where P.iNVENTORYID IS NULL  AND  TA.Takeawaycaption  like CONCAT('%', @SearchVal, '%') " + sActive;


                string command = string.Format(sCmdMBarcode, BranchAlias);
                MySqlParameter[] parameters =
                {
                 new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 15)
            {
                if (chkActiveonly.Checked == true)
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }
                string Inv_Qry4 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                    " where P.iNVENTORYID IS NULL AND  I.StockCode like CONCAT('%', @SearchVal, '%') " + sActive;

                MySqlParameter[] parameters =
               {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
               };

                string command = string.Format(Inv_Qry4, BranchAlias);
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }




        }

        public void LoadGroupDetail(string groupname, string SearchText)
        {



            if (cmbBranches.Visible == true)
            {
                #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

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




            if (chkActiveonly.Checked == true)
            {
                sActive = " and I.active <> 0";
            }
            else
            {
                sActive = " and I.active = 0";
            }


            string Inv_Qry5 = "Select *,sum(pquantity-(CutQuantity+RetailQty))as OnHandICA from {0}.inventory as I " +
                       " join {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                       " join {0}.inventorycostaverage ic on ic.inventoryid=i.inventoryid " +
                       " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                       " where  " +
                       " P.iNVENTORYID IS NULL AND  I.{1} like  CONCAT('%',@SearchText,'%')   " + sActive + "    group by I.inventoryid";

            string command = string.Format(Inv_Qry5, BranchAlias, groupname);

            MySqlParameter[] parameters =
                {
                     new MySqlParameter("@SearchText",SearchText.ToString().Trim()),

                    };


            DT = ObjMain.NewGetData_DT(command, parameters);
            grdInventoryView.DataSource = DT;
            grdInventoryView.DataBind();
            //txtSearchVal.Text = "";

        }


        protected void txtSearchVal_TextChanged(object sender, EventArgs e)
        {

            string sActive = string.Empty;

            txtSearchVal.CssClass = "form-control";

            //Page.ClientScript.RegisterStartupScript(this.GetType(), "hash", "location.hash = '#GridContend';", true);
            // ModalPopupExtender7.Show();
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowModalInventoryList();", true);

            if (cmbBranches.Visible == true)
            {
                #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
            }
            else
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }



            if (cmbSearch.SelectedIndex == 0)
            {
                //John added This All Condition
                if (chkActiveonly.Checked == true)// John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " where I.active <> 0 AND P.iNVENTORYID IS NULL";
                }
                else
                {
                    sActive = " where I.active = 0 AND P.iNVENTORYID IS NULL";
                }

                // Prevents SQL Injections 
                // Parameterized approach 

                string Inv_Qry1 = "select * from {0}.inventory as I join  " 
                  + " {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " 
                  + " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " + sActive;
                string command1 = string.Format(Inv_Qry1, BranchAlias);
                // Append the Database's Variable along with the query 
                DT = ObjMain.GetData_DT(command1);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();
            }
            else if (cmbSearch.SelectedIndex == 1)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string Inv_Qry1 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                    " where P.iNVENTORYID IS NULL AND  I.Inventoryid=@SearchVal " + sActive;
                string command2 = string.Format(Inv_Qry1, BranchAlias);

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@SearchVal", txtSearchVal.Text.ToString().Trim() ),
                  };

                DT = ObjMain.NewGetData_DT(command2, parameters);

                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 2)
            {
                if (chkActiveonly.Checked == true)// John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }


                string Inv_Qry2 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                    " where P.iNVENTORYID IS NULL AND   I.barcode like CONCAT('%', @SearchVal, '%') " + sActive;
                string command3 = string.Format(Inv_Qry2, BranchAlias);

                MySqlParameter[] parameters =
                 {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim() ),
                  };

                DT = ObjMain.NewGetData_DT(command3, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }

            else if (cmbSearch.SelectedIndex == 3)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }


                string Inv_Qry3 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid  " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                    " where P.iNVENTORYID IS NULL AND   I.InventoryName like CONCAT('%', @SearchVal, '%') " + sActive;
                string command4 = string.Format(Inv_Qry3, BranchAlias);

                MySqlParameter[] parameters =
                 {
                     new MySqlParameter("@SearchVal", txtSearchVal.Text.ToString().Trim() ),
                  };

                DT = ObjMain.NewGetData_DT(command4, parameters);

                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();


            }
            else if (cmbSearch.SelectedIndex == 4)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }
                string Inv_Qry4 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                    " where P.iNVENTORYID IS NULL AND   I.Description like CONCAT('%', @SearchVal, '%') " + sActive;

                string command5 = string.Format(Inv_Qry4, BranchAlias);

                MySqlParameter[] parameters =
                 {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()) ,
                  };

                DT = ObjMain.NewGetData_DT(command5, parameters);

                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }

            else if (cmbSearch.SelectedIndex == 5)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string Inv_Qry4 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid  " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                    " where P.iNVENTORYID IS NULL AND  Price =@Price  OR price2 =@Price OR price3 =@Price OR  price3 =@Price " + sActive;
                string command6 = string.Format(Inv_Qry4, BranchAlias);
                MySqlParameter[] parameters =
                 {
                     new MySqlParameter("@Price", txtSearchVal.Text.ToString().Trim()) ,
                  };

                DT = ObjMain.NewGetData_DT(command6, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();


            }


            else if (cmbSearch.SelectedIndex == 6)
            {
                string sActivex = string.Empty;

                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActivex = " and I.active <> 0";
                }
                else
                {
                    sActivex = " and I.active = 0";
                }


                //string Inv_Qry5 = "select * from " + BranchAlias + ".inventory as I join  " + BranchAlias + ".inventorycategories  as c ON I.Categoryid=c.categoryid ORDER BY I.inventoryID asc LIMIT " + txtTop.Text.ToString().Trim() + "";
                string Inv_Qry5 = "Select *,sum(pquantity-(CutQuantity+RetailQty))as OnHandICA from {0}.inventory as I " +
                                " join {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                                " join {0}.inventorycostaverage ic on ic.inventoryid=i.inventoryid " +
                                " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                                " where P.iNVENTORYID IS NULL AND  c.categoryname like CONCAT('%', @SearchVal, '%')    " + sActivex + "    group by I.inventoryid";
                string command7 = string.Format(Inv_Qry5, BranchAlias);
                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                  };

                DT = ObjMain.NewGetData_DT(command7, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();
                //txtSearchVal.Text = "";

            }

            else if (cmbSearch.SelectedIndex == 7)
            {
                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string sCmdMBarcode = "Select * from {0}.inventory as A join {0}.inventorycategories as B on A.CategoryID = B.CategoryID " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                    " Where P.iNVENTORYID IS NULL AND  Class = '10' and BarCode like CONCAT('%', @SearchVal, '%') " + sActive;


                string command8 = string.Format(sCmdMBarcode, BranchAlias);
                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@SearchVal", txtSearchVal.Text.ToString().Trim()) ,
                  };

                DT = ObjMain.NewGetData_DT(command8, parameters);

                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }



            // Groups Condition
            if (cmbSearch.SelectedIndex == 8)
            {
                string group1Value = "group1";
                LoadGroupDetail(group1Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 9)
            {
                string group2Value = "group2";
                LoadGroupDetail(group2Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 10)
            {
                string group3Value = "group3";
                LoadGroupDetail(group3Value, txtSearchVal.Text.ToString().Trim());

            }

            else if (cmbSearch.SelectedIndex == 11)
            {
                string group4Value = "group4";
                LoadGroupDetail(group4Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 12)
            {
                string group5Value = "group5";
                LoadGroupDetail(group5Value, txtSearchVal.Text.ToString().Trim());

            }

            //if (cmbActive.SelectedIndex == 0) //john Hide on 02-Nov-2020
            //{

            //    string InvenQuery = "select *,sum(pquantity-(CutQuantity+RetailQty))as OnHandICA from " + BranchAlias + ".inventory as I " +
            //                  " join " + BranchAlias + ".inventorycategories  as c ON I.Categoryid=c.categoryid " +
            //                  " join " + BranchAlias + ".inventorycostaverage ic on ic.inventoryid=i.inventoryid " +
            //                  " where i.active IN (1) " +
            //                  " group by i.inventoryid " +
            //                  " ORDER BY I.inventoryID asc LIMIT 2000;";
            //    DT = ObjMain.GetData_DT(InvenQuery);

            //    grdInventoryView.DataSource = DT;
            //    grdInventoryView.DataBind();

            //}
            //else if (cmbActive.SelectedIndex == 1)
            //{

            //    string InvenQuery = "select *,sum(pquantity-(CutQuantity+RetailQty))as OnHandICA from " + BranchAlias + ".inventory as I " +
            //                  " join " + BranchAlias + ".inventorycategories  as c ON I.Categoryid=c.categoryid " +
            //                  " join " + BranchAlias + ".inventorycostaverage ic on ic.inventoryid=i.inventoryid " +
            //                  " where i.active  IN (-1,0) " +
            //                  " group by i.inventoryid " +
            //                  " ORDER BY I.inventoryID asc LIMIT 2000;";
            //    DT = ObjMain.GetData_DT(InvenQuery);

            //    grdInventoryView.DataSource = DT;
            //    grdInventoryView.DataBind();


            //}

        }

        protected void OnSelectedIndexChanged(object sender, EventArgs e)
        {
            string Barcode = (grdInventoryView.SelectedRow.FindControl("lblBarCode") as Label).Text.ToString().Trim();
            string InventoryId = (grdInventoryView.SelectedRow.FindControl("lblInventoryID") as Label).Text;
            hidRInventoryid.Value = InventoryId;
            hidBarcode.Value = Barcode;

            txtPSInventoryid.Text= hidRInventoryid.Value;
            txtPSBarcode.Text= hidBarcode.Value;


            #region Add the Insert Logic Here 

            //#1.Check the condition using Barcode & Inventoryid.. 
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowCreatePriceswitchModal();", true);


            #endregion

        }

        protected void grdInventoryView_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {

            if (cmbBranches.Visible == false)
            {
                grdInventoryView.PageIndex = e.NewPageIndex;
                LoadInventoryview(Session["Branch_HQ"].ToString());
            }
            else
            {

                #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


                grdInventoryView.PageIndex = e.NewPageIndex;
                // LoadInventoryview();
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowModalInventoryList();", true);

            }


            if (cmbSearch.SelectedIndex == 0)
            {
                #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


                //John added This All Condition
                if (chkActiveonly.Checked == true)// John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " where P.iNVENTORYID IS NULL AND  I.active <> 0  ";
                }
                else
                {
                    sActive = " where P.iNVENTORYID IS NULL AND  I.active = 0 ";
                }
                string Inv_Qry1 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid  " 
                    + " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" + sActive;
                string NewQuery = string.Format(Inv_Qry1, BranchAlias);

                DT = ObjMain.GetData_DT(NewQuery);

                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();
            }
            else if (cmbSearch.SelectedIndex == 1)
            {
                #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


                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string Inv_Qry1 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid where P.iNVENTORYID IS NULL AND   I.Inventoryid=@SearchVal   " + sActive;
                string command = string.Format(Inv_Qry1, BranchAlias);

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                  };

                DT = ObjMain.NewGetData_DT(command, parameters);
                // DT = ObjMain.GetData_DT(Inv_Qry1);     Existing code 
                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;

                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 2)
            {

                #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

                if (chkActiveonly.Checked == true)// John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }


                string Inv_Qry2 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                    " where  P.iNVENTORYID IS NULL AND   I.barcode like CONCAT('%', @SearchVal, '%')  " + sActive;

                MySqlParameter[] parameters =
                {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };

                string command = string.Format(Inv_Qry2, BranchAlias);
                DT = ObjMain.NewGetData_DT(command, parameters);
                //  DT = ObjMain.GetData_DT(Inv_Qry2);  Exiting Code 

                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 3)
            {

                #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

                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }


                string Inv_Qry3 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " 
                    +"  left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " 
                    + " where  P.iNVENTORYID IS NULL AND  I.InventoryName like CONCAT('%', @SearchVal, '%') " + sActive;

                MySqlParameter[] parameters =
                {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };

                string command = string.Format(Inv_Qry3, BranchAlias);
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 4)
            {
                #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

                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }
                string Inv_Qry4 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " 
                    + " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " 
                    + " where   P.iNVENTORYID IS NULL AND  I.Description like CONCAT('%', @SearchVal, '%') " + sActive;

                MySqlParameter[] parameters =
               {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
               };

                string command = string.Format(Inv_Qry4, BranchAlias);
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 5)
            {
                #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

                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string Inv_Qry4 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid left  " +
                    " join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                    " where P.iNVENTORYID IS NULL AND Price =@SearchVal  OR price2 =@SearchVal OR price3 =@SearchVal OR  price4 =@SearchVal " + sActive;
                string command = string.Format(Inv_Qry4, BranchAlias);

                MySqlParameter[] parameters =
                {
                 new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();


            }
            else if (cmbSearch.SelectedIndex == 6)
            {
                #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

                string sActivex = string.Empty;

                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActivex = " and I.active <> 0";
                }
                else
                {
                    sActivex = " and I.active = 0";
                }


                //string Inv_Qry5 = "select * from " + BranchAlias + ".inventory as I join  " + BranchAlias + ".inventorycategories  as c ON I.Categoryid=c.categoryid ORDER BY I.inventoryID asc LIMIT " + txtTop.Text.ToString().Trim() + "";
                string Inv_Qry5 = "Select *,sum(pquantity-(CutQuantity+RetailQty))as OnHandICA from {0}.inventory as I " +
                                " join {0}.inventorycategories  as c ON I.Categoryid=c.categoryid " +
                                " join {0}.inventorycostaverage ic on ic.inventoryid=i.inventoryid  " +
                                " left join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                                " where P.iNVENTORYID IS NULL AND c.categoryname like CONCAT('%', @SearchVal, '%')   " + sActivex + "    group by I.inventoryid";

                string command = string.Format(Inv_Qry5, BranchAlias);
                MySqlParameter[] parameters =
                {
                 new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();
                //txtSearchVal.Text = "";

            }
            else if (cmbSearch.SelectedIndex == 7)
            {
                #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

                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                string sCmdMBarcode = "Select * from {0}.inventory as A join {0}.inventorycategories as B on A.CategoryID = B.CategoryID " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid " +
                    " Where P.iNVENTORYID IS NULL AND  Class = '10' and BarCode like CONCAT('%', @SearchVal, '%') " + sActive;


                string command = string.Format(sCmdMBarcode, BranchAlias);
                MySqlParameter[] parameters =
                {
                 new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }

            // Groups Condition
            if (cmbSearch.SelectedIndex == 8)
            {
                string group1Value = "group1";
                grdInventoryView.PageIndex = e.NewPageIndex;
                LoadGroupDetail(group1Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 9)
            {
                string group2Value = "group2";
                grdInventoryView.PageIndex = e.NewPageIndex;
                LoadGroupDetail(group2Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 10)
            {
                string group3Value = "group3";
                grdInventoryView.PageIndex = e.NewPageIndex;
                LoadGroupDetail(group3Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 11)
            {
                string group4Value = "group4";
                grdInventoryView.PageIndex = e.NewPageIndex;
                LoadGroupDetail(group4Value, txtSearchVal.Text.ToString().Trim());

            }
            else if (cmbSearch.SelectedIndex == 12)
            {
                string group5Value = "group5";
                grdInventoryView.PageIndex = e.NewPageIndex;
                LoadGroupDetail(group5Value, txtSearchVal.Text.ToString().Trim());

            }
            // Search Option for Takeaway 
            else if (cmbSearch.SelectedIndex == 14)
            {
                #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

                if (chkActiveonly.Checked == true) // John added 02-Nov-2020 Active/InActive Include inside Query
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }

                //string sCmdMBarcode = "Select * from {0}.inventory as A join {0}.inventorycategories as B on A.CategoryID = B.CategoryID Where Class = '10' and BarCode like CONCAT('%', @SearchVal, '%') " + sActive;

                string sCmdMBarcode = "select * from {0}.inventory as I  join  {0}.Webtakeawaychargeonly as TA ON I.Taid=TA.id join {0}.inventorycategories  as c ON I.Categoryid=c.categoryid  " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                    " where P.iNVENTORYID IS NULL AND  TA.Takeawaycaption  like CONCAT('%', @SearchVal, '%') " + sActive;


                string command = string.Format(sCmdMBarcode, BranchAlias);
                MySqlParameter[] parameters =
                {
                 new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
                };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.PageIndex = e.NewPageIndex;
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }
            else if (cmbSearch.SelectedIndex == 15)
            {
                #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

                if (chkActiveonly.Checked == true)
                {
                    sActive = " and I.active <> 0";
                }
                else
                {
                    sActive = " and I.active = 0";
                }
                string Inv_Qry4 = "select * from {0}.inventory as I join  {0}.inventorycategories  as c ON I.Categoryid=c.categoryid  " +
                    " left  join {0}.SwitchPrice P on I.inventoryid=P.inventoryid" +
                    " where P.iNVENTORYID IS NULL AND  I.StockCode like CONCAT('%', @SearchVal, '%') " + sActive;

                MySqlParameter[] parameters =
               {
                     new MySqlParameter("@SearchVal",txtSearchVal.Text.ToString().Trim()),
               };

                string command = string.Format(Inv_Qry4, BranchAlias);
                grdInventoryView.PageIndex = e.NewPageIndex;
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdInventoryView.DataSource = DT;
                grdInventoryView.DataBind();

            }

        }

        protected void grdInventoryView_Sorting(object sender, GridViewSortEventArgs e)
        {
            this.LoadInventoryview(e.SortExpression);

        }

        protected void OnSorting(object sender, GridViewSortEventArgs e)
        {
            this.LoadInventoryview(e.SortExpression);
        }

        protected void btnSaveGroup_Click(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 = Cmb_BranchName;
                }
                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;
                }
            }

            btnSaveGroup.Visible = true;
            btnSaveGroup.CssClass = "btn btn-primary";
            btnUpdateGroup.Visible =false;
            



            string Ins_PriceGroup = " Insert into {0}.switchpriceGroup (PSGroupName)values(@PSGroupName) ; ";

            MySqlParameter[] parameters =
              {
                     new MySqlParameter("@PSGroupName",txtGroupName.Text.Trim().ToString() ),
                     
              };

            string command = string.Format(Ins_PriceGroup, BranchAlias);
            K = ObjMain.ExecuteQuery(command, parameters);

            LoadPriceGroup();

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowGroupModal();", true);


        }

        protected void grdPriceGroup_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 = Cmb_BranchName;
                }
                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;
                }
            }

            btnSaveGroup.Visible = false;
            btnUpdateGroup.CssClass = "btn btn-primary";
            btnUpdateGroup.Visible = true;
            btnUpdateGroup.Enabled = true;
            
            
            string psgid = (grdPriceGroup.SelectedRow.FindControl("lblPSGID") as Label).Text;
            // Assign the Psgid value to Hidden variable in order to Update .
            hidpsid.Value= psgid;
            DataTable Dtpricegroup = ObjMain.GetData_DT("Select PSGroupName from "+ Cmb_BranchName + ".switchpriceGroup where PSGID="+ psgid + "  ");
            string _psgroupname  = Dtpricegroup.Rows[0]["PSGroupName"].ToString();

            txtGroupName.Text= _psgroupname;
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowGroupModal();", true);

        }

        protected void btnUpdateGroup_Click(object sender, EventArgs e)
        {

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

            string UpdateGroup = "Update {0}.switchpriceGroup set  PSGroupName=@PSGroupName where  PSGID=@PSGID ; ";

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@PSGroupName",txtGroupName.Text.Trim().ToString()),
                     new MySqlParameter("@PSGID",hidpsid.Value),
                  };

            string command = string.Format(UpdateGroup, BranchAlias);
            K= ObjMain.ExecuteQuery(command, parameters);
            LoadPriceGroup();
            LoadPriceSwitch(BranchAlias);

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowGroupModal();", true);

        }

        protected void btnCreateSwitchPrice_Click(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 = Cmb_BranchName;
                }
                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;
                }
            }

            
            txtPSBarcode.Enabled = false;
            txtPSInventoryid.Enabled = false;
            #region Insert Logic for SwitchPrice 

            if (cmbPriceGroupName.SelectedIndex != 0 && cmbPriceList.SelectedIndex != 0)
            {


                string Check_Query = "select  Inventoryid  from " + BranchAlias + ".SwitchPrice where Inventoryid=" + txtPSInventoryid.Text + "; ";
                string chk_InvenId = ObjMain.ExecuteScalar(Check_Query);


                if (chk_InvenId == "" || chk_InvenId == null)
                {
                    string Added = txtT1_StTm.Text;
                    string NewStartDt = string.Concat(Curr_DateOnly, " ", Added);
                    string Added1 = txtT1_EndTm.Text;
                    string NewEndtDt = string.Concat(Curr_DateOnly, " ", Added1);
                    


                    string Ins_PriceGroup = " Insert into {0}.SwitchPrice (Inventoryid,PSGID,Barcode,SwitchPrice,Start_Tm,End_Tm,DefaultPrice)"
                                          + " values(@Inventoryid,@PSGID,@Barcode,@SwitchPrice,@Start_Tm,@End_Tm,@DefaultPrice) ; ";
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@Inventoryid",txtPSInventoryid.Text ),
                     new MySqlParameter("@PSGID",cmbPriceGroupName.SelectedValue ),
                     new MySqlParameter("@Barcode",txtPSBarcode.Text.Trim().ToString() ),
                     new MySqlParameter("@SwitchPrice",cmbPriceList.SelectedItem.Text ),
                     new MySqlParameter("@Start_Tm",NewStartDt),
                     new MySqlParameter("@End_Tm",NewEndtDt),
                     new MySqlParameter("@DefaultPrice","Price"),

                    };
                    string command = string.Format(Ins_PriceGroup, BranchAlias);
                    K = ObjMain.ExecuteQuery(command, parameters);
                    LoadPriceGroup();
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Items Added to  Switch Price!.');location.href='PriceSwitch.aspx'", true);

                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('Barcode Added To PriceSwitch.');", true);
                }


                

                #endregion
            }
            else
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowCreatePriceswitchModal();", true);
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Either PriceGroupName or PriceList Invalid value ');", true);
                
            }





        }

  

        protected void grdPriceSwitch_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {

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

            grdPriceSwitch.PageIndex = e.NewPageIndex;
            LoadPriceSwitch(BranchAlias);




        }
    }
}