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


namespace SUNWEB
{
    public partial class InventoryListReport : 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 Branchidval = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string L_Inventory = string.Empty;

        string ExcelQry = string.Empty;
        DataTable DTExcel = new DataTable();
        DataTable DTResult = new DataTable();
        DataTable DTA = new DataTable();
        DataTable DTLoad = new DataTable();

        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;
        //added by MANJU U
        DataTable DtInventoryList = new DataTable();
        DataTable DtCategoryList = new DataTable();
        DataSet DS = new DataSet();
        
        string Vw_Chk = string.Empty;
        string BranchAlias = string.Empty;
        string RBranchid = string.Empty;
        string Branchid = string.Empty;
        // Added On 02-12-2021
        string ReadHQ = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {
            PID = "13";    // This is for Inventory Related Page 
            if (!IsPostBack)
            {
                txtStartDt.Text = Curr_DateOnly;
                txtEndDt.Text = Curr_DateOnly;
            }
            MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                    cmbCategory.Enabled = false;
                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();
                    }
                }
            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                }
                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                LoadInventoryListGrid(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                    ActiveCall();
                }
            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == false))
            {
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;

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

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

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

                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }
                if ((Session["L_Inventory"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {

                    // XSS Attack Prevention 

                    string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
                    string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());

                    // lblMsgTotalSelection.Text = "Search Result - InventoryList  Between" + "&nbsp;&nbsp;&nbsp;&nbsp;" + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  and  &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                    lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; and &nbsp;" + EndDt;
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                    if (!IsPostBack)
                    {
                        if (Session["Isdowld_Acess"].ToString() == "N")
                        {
                            // btnExcel.Enabled = false;
                            Response.Write(@"<script language='javascript'>alert('No Access To download.')</script>");
                        }
                        else
                        {
                            btnGetReports.Visible = true;
                            //  btnExcel.Enabled = true;
                            if (cmbBranches.Visible == false)
                            {
                                BindGridview(Session["Branch_HQ"].ToString(), StartDT, EndDt);
                            }

                        }


                    }

                }

            }
            else
            {

            }

        }

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

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

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

                }


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

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

        }



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



            #region  HQ Validation in ActiveCall Function : Starts 
            cd_BranchAlias = ActiveCallvalue;
            BranchAlias = cd_BranchAlias;
            // HQ Validation For Loading Branch Name : Dropdown  Added Karthik  On 02-12-21 

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

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

            }

            #endregion



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

            }

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

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

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


                    string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
                    string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());
                    BranchAlias = cd_BranchAlias;
                    BindGridview(BranchAlias, StartDT, EndDt);

                }
                else
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                                
            }
            else
            {
                //btnExcel.Enabled = true;

                // XSS Attack Prevention 

                string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
                string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());

                BindGridview(BranchAlias, StartDT, EndDt);
            }

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

            // XSS Attack Prevention 

            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());

            if (cmbBranches.Visible == false)
            {
                btnGetReports.Visible = true;
                //lblMsgTotalSelection.Text = " Search Result - InventoryList Selection Between " + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; and &nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                BindGridview(Session["Branch_HQ"].ToString(), StartDT, EndDt);


            }
            else 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

                //lblMsgTotalSelection.Text = "Search Result - InventoryList Selection Between " + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; and &nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                BindGridview(BranchAlias, StartDT, EndDt);

            }

        }

        private void BindGridview(string BranchName, string D1, string D2)
        {
            System.Threading.Thread.Sleep(1000);

            if (cmbCategory.SelectedIndex == 0 && cmbInventory.SelectedIndex == 0)
            {

                string BindQry = "select * , sum(IC.PQuantity - (IC.CutQuantity + IC.RetailQty)) as OnHandQty , IC.Retailqty from {0}.inventory AS I inner join {0}.inventorycategories  as c ON I.CategoryID=c.CategoryID INNER JOIN {0}.inventorycostaverage AS IC ON I.InventoryID=IC.InventoryID group by I.Categoryid order by I.inventoryid;";
                string command = string.Format(BindQry, BranchName);
                DTA = ObjMain.GetData_DT(command);
            }
            else
            {
                string BindQry = "Select * , sum(IC.PQuantity - (IC.CutQuantity + IC.RetailQty)) as OnHandQty , IC.Retailqty from {0}.inventory AS I inner join {0}.inventorycategories as c ON I.CategoryID=c.CategoryID INNER JOIN {0}.inventorycostaverage AS IC ON I.InventoryID=IC.InventoryID where c.Categoryid=" + cmbCategory.SelectedValue + ";";
                string command = string.Format(BindQry, BranchName);
                DTA = ObjMain.GetData_DT(command);
            }

            gvInventoryList.DataSource = DTA;
            gvInventoryList.DataBind();
        }




        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 LoadBranchNamewithPortalTypeid()
        {
            DtMultiBranch = ObjUserBL.GetBranchOnLoginPortalid(Session["Branch_HQ"].ToString());
            if (DtMultiBranch.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtMultiBranch;
                cmbBranches.DataTextField = "BranchName";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        public void LoadInventoryListGrid(string Branch_Nam, string D1, string D2)
        {

            string Qry1 = "Select   distinct  b.categorynumber  as CategoryNo, (categoryname) as CategoryName,b.categorynumber as Categoryno    from {0}.inventorycategories a join {0}.transdetail b on a.categorynumber = b.categorynumber join {0}.trans c on c.transid=b.transid where date(businessdate)    between  @D1   and  @D2   and transstatus In(2,3,4) and itemstatus=0 order by CategoryName;";
            string command = string.Format(Qry1, Branch_Nam);

            MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

            DTProductSale = ObjMain.NewGetData_DT(command, parameters);
            //DTProductSale
            gvInventoryList.DataSource = DTProductSale;
            gvInventoryList.DataBind();
        }
        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {

            LoadCategory();
            cmbInventory.SelectedIndex = 0;
            cmbCategory.Enabled = false;


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

                if (cmbBranches.SelectedIndex == 1)
                {

                    BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                    Cmb_BranchName = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), BranchFullName);
                    UPDATEBRANCH(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);

                }

                if (DtLicense.Rows.Count > 0)
                {
                    L_Inventory = DtLicense.Rows[0]["INVENTORY"].ToString();
                    Session["L_Inventory"] = L_Inventory;

                }

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

                        BranchAlias = Session["Branch_HQ"].ToString();
                        BindGridview(BranchAlias, txtStartDt.Text, txtEndDt.Text); // Calling  Function
                    }
                    else
                    {
                        Response.Redirect("DeniedPage.aspx");
                    }
                                        
                }
                else
                {
                    //btnExcel.Enabled = true;
                    //lblMsgTotalSelection.Text = "Search Resut - InventoryList Between " + "&nbsp;&nbsp;&nbsp;" + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                    lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text) + "&nbsp;&nbsp; and &nbsp;" + Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text);
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                    BranchAlias = Cmb_BranchName;
                    BindGridview(BranchAlias, txtStartDt.Text, txtEndDt.Text); //FUNCTION 
                }
            }
            UPDATEBRANCH(BranchAlias);
        }

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

            }

        }

        // My Methods

        private void LoadCategory()
        {

            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
            {
                Cmb_BranchName = Session["Branch_HQ"].ToString();
            }
            try
            {
                DtCategoryList = ObjUserBL.GetCategoryList(BranchAlias);
                if (DtCategoryList.Rows.Count > 0)
                {
                    cmbCategory.DataSource = DtCategoryList;
                    cmbCategory.DataTextField = "CategoryName";
                    cmbCategory.DataValueField = "CategoryId";
                    cmbCategory.DataBind();
                    cmbCategory.Items.Insert(0, "Select");
                    cmbCategory.Enabled = false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected void cmbInventory_SelectedIndexChanged(object sender, EventArgs e)
        {

            if (cmbInventory.SelectedIndex == 0)
            {
                cmbCategory.Items.Clear();
                cmbCategory.DataSourceID = "";
                // cmbCategory.DataSource = cmbCategory.Select();
                cmbCategory.DataBind();
                //Insert new ListItem ... 
                cmbCategory.Items.Insert(0, "Select");

                cmbCategory.Enabled = false;

            }
            else
            {
                LoadCategory();
                cmbCategory.Enabled = true;
            }

        }
        public DataTable LoadChild(string CategoryID, string Branhc_Nam)
        {

            //MySqlCommand cmd1 = new MySqlCommand("Select I.Inventoryid,I.BARCODE,I.STOCKCODE,I.DESCRIPTION,I.CLASS,I.UNIT,I.PRICE,(IC.PQuantity - (IC.CutQuantity + IC.RetailQty)) as OnHandQty from " + Branhc_Nam + ".inventory AS I inner join " + Branhc_Nam + ".inventorycostaverage  AS IC ON I.InventoryID=IC.InventoryID  where I.Categoryid=" + CategoryID + ";", Cn);
            string QryLoadChild = "Select I.Inventoryid,I.BARCODE,I.STOCKCODE,I.DESCRIPTION,I.CLASS,I.UNIT,I.PRICE,I.PRICE2,I.PRICE3,I.PRICE4,(IC.PQuantity - (IC.CutQuantity + IC.RetailQty)) as OnHandQty ,ic.Unitcost as Unitcost , (IC.PQuantity - (IC.CutQuantity + IC.RetailQty))* Unitcost as  TotalCost from {0}.inventory AS I inner join {0}.inventorycostaverage  AS IC ON I.InventoryID=IC.InventoryID  where I.Categoryid=@CategoryID;";
            string command = string.Format(QryLoadChild, Branhc_Nam);
            MySqlParameter[] parameters =
              {
                     new MySqlParameter("@CategoryID", CategoryID),

                    };

            DTLoad = ObjMain.NewGetData_DT(command, parameters);

            return DTLoad;

        }

        decimal totqty = 0;
        decimal GTOTAMT;
        decimal OnhandTotal = 0;
        decimal GDISCAMT;


        protected void gvInventoryList_RowDataBound(object sender, GridViewRowEventArgs e)
        {


            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                string CategoryId = gvInventoryList.DataKeys[e.Row.RowIndex].Value.ToString();
                GridView gvCategory = e.Row.FindControl("gvchild") as GridView;
                gvCategory.DataSource = LoadChild(Convert.ToString(CategoryId), BranchAlias);
                gvCategory.DataBind();
            }


            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                GridView gvInventoryList = e.Row.FindControl("gvInventoryList") as GridView;
                Label TotalOnHandQty = e.Row.FindControl("lblOnhndqty") as Label;
                OnhandTotal += Convert.ToDecimal(TotalOnHandQty.Text);

            }
            if (e.Row.RowType == DataControlRowType.Footer)
            {
                (e.Row.FindControl("lblOnHandTotal") as Label).Text = OnhandTotal.ToString();
            }
        }

        protected void cmbCategory_SelectedIndexChanged(object sender, EventArgs e)
        {
            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();
            }
            BindGridview(BranchAlias, Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text), Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text));
        }

        protected void btnExcel_Click(object sender, EventArgs e)
        {

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


            }
            else
            {
                if (Session["Isdowld_Acess"].ToString() == "N")
                {
                    Page page = HttpContext.Current.Handler as Page;
                    string message = "No Access To download";
                    System.Web.UI.ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + message + "');", 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

                    ExcelQry = " Select I.Inventoryid,I.Categoryid,T.CategoryName,I.BARCODE,I.STOCKCODE,I.DESCRIPTION,I.CLASS,I.UNIT,ROUND(I.PRICE,2) AS PRICE,ROUND(I.PRICE2,2) AS PRICE2,ROUND(I.PRICE3,2) AS PRICE3,ROUND(I.PRICE4,2) AS PRICE4,(IC.PQuantity - (IC.CutQuantity + IC.RetailQty)) as OnHandQty , ic.Unitcost as Unitcost , (IC.PQuantity - (IC.CutQuantity + IC.RetailQty))* Unitcost as  TotalCost "
                              + " from {0}.inventory AS I inner join {0}.inventorycostaverage  AS IC ON I.InventoryID=IC.InventoryID inner join {0}.inventorycategories AS T  on T.Categoryid =I.CategoryID  order by T.CategoryName ;";


                    string command = string.Format(ExcelQry, BranchAlias);
                    DTExcel = ObjMain.GetData_DT(command);


                    //decimal AmTT = 0;

                    //for (int i = 0; i <= DTExcel.Rows.Count - 1; i++)
                    //{
                    //    if (i > 0)
                    //    {
                    //        if (DTExcel.Rows[i]["Categoryid"].ToString().ToLower() != DTExcel.Rows[i - 1]["Categoryid"].ToString().ToLower())
                    //        {
                    //            DTExcel.Rows.InsertAt(DTExcel.NewRow(), i);
                    //            DTExcel.Rows[i]["OnHandQty"] = AmTT;
                    //            AmTT = 0;
                    //            i++;
                    //        }
                    //    }
                    //    AmTT += Convert.ToDecimal(DTExcel.Rows[i]["OnHandQty"]);

                    //}
                    //DTExcel.Rows.Add(DTExcel.NewRow());
                    //DTExcel.Rows[DTExcel.Rows.Count - 1]["OnHandQty"] = AmTT;



                    //Create a dummy GridView
                    GridView GridView1 = new GridView();
                    GridView1.ShowFooter = true;
                    GridView1.AllowPaging = false;
                    GridView1.DataSource = DTExcel;
                    GridView1.DataBind();
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition",
                     "attachment;filename=InventoryList.xls");
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.ms-excel";
                    StringWriter sw = new StringWriter();
                    HtmlTextWriter hw = new HtmlTextWriter(sw);
                    for (int i = 0; i < GridView1.Rows.Count; i++)
                    {
                        //Apply text style to each Row
                        GridView1.Rows[i].Attributes.Add("class", "textmode");
                    }


                    GridView1.RenderControl(hw);

                    //style to format numbers to string
                    string style = @"<style> TD { mso-number-format:\@; } </style>";
                    Response.Write(style);
                    Response.Output.Write(sw.ToString());
                    Response.Flush();
                    Response.End();




                }





            
        }




        //protected void btnGenerateRpt_Click(object sender, EventArgs e)
        //{
        //    try
        //    {

        //        BranchFullName = cmbBranches.SelectedItem.Text;
        //        Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);


        //        string Vw_Chk_Qry = "SHOW FULL TABLES IN " + Cmb_BranchName + " WHERE TABLE_TYPE LIKE 'VIEW' and Tables_in_" + Cmb_BranchName + "='InventoryListDetails';";
        //        Vw_Chk = ObjMain.ExecuteScalar(Vw_Chk_Qry);

        //        if (Vw_Chk == "")
        //        {
        //            string Ins_Vw = "Create view " + Cmb_BranchName + ".InventoryListDetails as SELECT I.InventoryID,I.InventoryName,I.description, C.CategoryID,C.CategoryName,I.Barcode,I.stockcode,I.class,I.Unit, I.price,sum(IC.PQuantity - (IC.CutQuantity + IC.RetailQty)) as Onhand From "
        //                                     + Cmb_BranchName + ".inventory  AS I"
        //                                     + " inner join " + Cmb_BranchName + ".inventorycategories as c on  I.CategoryID=c.CategoryID"
        //                                     + " inner join " + Cmb_BranchName + ".inventorycostaverage as IC on I.InventoryID=IC.InventoryID  group by I.InventoryID order by I.categoryid;";
        //            L = ObjMain.ExecuteNonQuery(Ins_Vw);
        //        }


        //        CrystalReportViewer1.Visible = true;
        //        TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
        //        TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
        //        ConnectionInfo crConnectionInfo = new ConnectionInfo();
        //        Tables CrTables;
        //        string q2 = string.Empty;

        //        if (cmbInventory.Items.FindByText("All Inventory").Selected == true)
        //        {

        //            q2 = "select * FROM  " + Cmb_BranchName + ".InventoryListDetails;";

        //        }
        //        else
        //        {
        //            //q2 = "select * FROM  " + Cmb_BranchName + ".InventoryListDetails where datecreated between  '" + txtStartDt.Text + "'   and  '" + txtEndDt.Text + "' and categoryid=" + cmbCategory.SelectedItem.Value + ";";
        //            q2 = "select * FROM  " + Cmb_BranchName + ".InventoryListDetails where categoryid=" + cmbCategory.SelectedItem.Value + ";";
        //        }

        //        DS = ObjMain.GetData_DS(q2);

        //        if (DS.Tables[0].Rows.Count > 0)
        //        {
        //            rep = new ReportDocument();
        //            rep.Load(Server.MapPath("~/InventoryLstReport.rpt"));

        //            rep.SetDataSource(DS.Tables[0]);
        //            CrTables = rep.Database.Tables;

        //            foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
        //            {
        //                crtableLogoninfo = CrTable.LogOnInfo;

        //                crtableLogoninfo.ConnectionInfo = crConnectionInfo;

        //                CrTable.ApplyLogOnInfo(crtableLogoninfo);

        //            }

        //            CrystalReportViewer1.ReportSource = rep;
        //            //Session["Report"] = rep;
        //            CrystalReportViewer1.DataBind();
        //            CrystalReportViewer1.RefreshReport();
        //        }
        //        else
        //        {
        //            CrystalReportViewer1.Visible = false;
        //            Response.Write(@"<script language='javascript'>alert('No Data Found ! ')</script>");
        //        }


        //    }

        //    catch (Exception ex)
        //    {
        //        throw ex;

        //    }
        //    finally
        //    {

        //    }

        //}

    }
}