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;


namespace SUNWEB
{
    public partial class ReorderReport : 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_ReorderReport = 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 sWhereCondition = string.Empty;
        string BraNam = "";
        string FDate = "";
        string TDate = "";
        DataTable DTSession = new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {

            PID = "42";    // This is for ReorderReport 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)
                {
                    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_ReorderReport"].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
                        {
                            btnGetReports.Visible = true;

                            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.GetFirstBranch(Session["Branch_HQ"].ToString());
            }


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

            if (ISHQ != "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch(Session["Branch_HQ"].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);
            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_ReorderReport = DtLicense.Rows[0]["REORDERREPORT"].ToString();
                Session["L_ReorderReport"] = L_ReorderReport;
            }
            if (Session["L_ReorderReport"].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;
                
                BindGridview(Cmb_BranchName, txtStartDt.Text, txtEndDt.Text);
                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 cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {

            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Redirect("ReorderReport.aspx", false);
            }
            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;
                }

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

                if (ISHQ != "")
                {
                    ActiveCallvalue = ObjSel.GetFirstBranch(Session["Branch_HQ"].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=" + 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_ReorderReport = DtLicense.Rows[0]["REORDERREPORT"].ToString();
                    Session["L_ReorderReport"] = L_ReorderReport;
                }
                if (Session["L_ReorderReport"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    Response.Redirect("DeniedPage.aspx", false);
                }
                else
                {
                    // Call The Report bind  function here 
                    //lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt;
                    //idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                    cd_BranchAlias = Cmb_BranchName;
                    BraNam = cd_BranchAlias;
                    FDate = txtStartDt.Text;
                    TDate = txtEndDt.Text; 
                    BindGridview(cd_BranchAlias, FDate , TDate);


                }
            }
            UPDATEBRANCH(Cmb_BranchName);

        }
        protected void txtID_OnTextChange(object sender, EventArgs e)
        {
            txtName.Text = "";
        }

        protected void txtName_OnTextChange(object sender, EventArgs e)
        {
            txtID.Text = "";
        }

        protected void cmbSelectby_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtID.Text = "";
            txtName.Text = "";

        }


        protected void btnApplySearch_Click(object sender, EventArgs e)
        {
            // Prevents XSS 
            // Prevents SQL Injection 

            Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);



            if (cmbSelectby.Text == "AInventory")
            {
                grv.DataSource = null;
                grv.DataBind();
            }
            if (cmbSelectby.Text == "Category")
            {
                if (txtID.Text == "" && txtName.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select CategoryNumber as Row1,CategoryName as Row2,Description as Row3 from {0}.inventorycategories where categorynumber <> 1; ";
                    string command = string.Format(query, cd_BranchAlias);
                    Dt = ObjMain.GetData_DT(command);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtName.Text == "")
                {

                    DataTable Dt = new DataTable();
                    string query = "Select CategoryNumber as Row1,CategoryName as Row2,Description as Row3 from {0}.inventorycategories " +
                                    " where CategoryNumber like  CONCAT('%',@SearchVal,'%') ";
                    string command = string.Format(query, cd_BranchAlias);
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@SearchVal",txtID.Text.Trim()),

                    };
                    Dt = ObjMain.NewGetData_DT(command, parameters);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtID.Text == "")
                {
                    DataTable Dt = new DataTable();

                    string query = "Select CategoryNumber as Row1,CategoryName as Row2,Description as Row3 from {0}.inventorycategories " +
                                  " where CategoryName like  CONCAT('%',@SearchVal,'%')";

                    string command = string.Format(query, cd_BranchAlias);

                    MySqlParameter[] parameters =
                    {
                        new MySqlParameter("@SearchVal", txtName.Text.Trim()),

                    };
                    Dt = ObjMain.NewGetData_DT(command, parameters);

                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else
                {
                    DataTable Dt = new DataTable();

                    string query = "Select CategoryNumber as Row1,CategoryName as Row2,Description as Row3 from {0}.inventorycategories " +
                                 " where CategoryName like  CONCAT('%',@CategoryName,'%') and CategoryNumber like CONCAT('%',@CategoryNumber,'%') ";

                    string command = string.Format(query, cd_BranchAlias);
                    MySqlParameter[] parameters =
                   {
                        new MySqlParameter("@CategoryName", txtName.Text.Trim()),
                        new MySqlParameter("@CategoryNumber", txtID.Text.Trim()),

                    };
                    Dt = ObjMain.NewGetData_DT(command, parameters);

                    grv.DataSource = Dt;
                    grv.DataBind();
                }


            }

            if (cmbSelectby.Text == "SInventory")
            {
                if (txtID.Text == "" && txtName.Text == "")
                {


                    DataTable Dt = new DataTable();
                    string query = "Select Barcode as Row1,InventoryName as Row2,Description as Row3 from {0}.inventory where InventoryID<> 1";
                    string command = string.Format(query, cd_BranchAlias);
                    Dt = ObjMain.GetData_DT(command);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtName.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select Barcode as Row1,InventoryName as Row2,Description as Row3 from {0}.inventory" +
                                        " where Barcode like CONCAT('%',@SearchVal,'%') ";
                    string command = string.Format(query, cd_BranchAlias);

                    MySqlParameter[] parameters =
                    {
                        new MySqlParameter("@SearchVal", txtID.Text),
                    };
                    Dt = ObjMain.NewGetData_DT(command, parameters);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtID.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select Barcode as Row1,InventoryName as Row2,Description as Row3 from {0}.inventory" +
                                       " where InventoryName like CONCAT('%',@SearchVal,'%') ";
                    string command = string.Format(query, cd_BranchAlias);

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

                    Dt = ObjMain.NewGetData_DT(command, parameters);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else
                {
                    DataTable Dt = new DataTable();
                    string query = "Select Barcode as Row1,InventoryName as Row2,Description as Row3 from {0}.inventory" +
                                      " where InventoryName like CONCAT('%',@InventoryName,'%')  and barcode like CONCAT('%',@barcode,'%') ";
                    string command = string.Format(query, cd_BranchAlias);
                    MySqlParameter[] parameters =
                    {
                        new MySqlParameter("@InventoryName", txtName.Text.Trim()),
                        new MySqlParameter("@barcode", txtID.Text.Trim()),
                    };

                    Dt = ObjMain.NewGetData_DT(command, parameters);

                    grv.DataSource = Dt;
                    grv.DataBind();
                }
            }
 

        }
        protected void CheckBox_Changed(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[2] { new DataColumn("Name"), new DataColumn("Country") });
            foreach (GridViewRow row in grv.Rows)
            {
                if (row.RowType == DataControlRowType.DataRow)
                {
                    CheckBox chkRow = (row.Cells[0].FindControl("chkSelect") as CheckBox);
                    if (chkRow.Checked)
                    {
                        string sRow1 = (row.Cells[1].FindControl("lblrow1") as Label).Text;
                        string sRow2 = (row.Cells[2].FindControl("lblrow2") as Label).Text;
                        string sRow3 = (row.Cells[2].FindControl("lblrow3") as Label).Text;
                        txtID.Text = sRow1;
                        txtName.Text = sRow2;
                        return;
                    }
                    else
                    {
                        txtID.Text = "";
                        txtName.Text = "";
                    }

                }
            }
        }

        protected void btnGetReports_Click(object sender, EventArgs e)
        {
            
            // Preventing xss Attack 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text);
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text);

            if (cmbBranches.Visible == false)
            {
                btnGetReports.Visible = true;
                if (cmbSelectby.Text == "AInventory")
                {
                    BindGridview(Session["Branch_HQ"].ToString(), StartDT, EndDt);
                }
            }
            else if (cmbBranches.Visible == true)
            {
                Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                BindGridview(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            }
        }

        protected void BindGridview(string BranchName, string D1, string D2)
        {
            System.Threading.Thread.Sleep(1000);
            string sCmd = string.Empty;

            if (cmbSelectby.Text == "AInventory")
            {

            }
            else if (cmbSelectby.Text == "Category")
            {
                sWhereCondition = " and I.CategoryID = @SearchVal ";
            }
            else if (cmbSelectby.Text == "SInventory")
            {
                sWhereCondition = " and I.Barcode = @SearchVal ";
            }
            else
            {
                if (txtName.Text.Trim() != "")
                {
                    sWhereCondition = "And I." + cmbSelectby.Text.Trim() + " = @SearchVa2";
                }
            }

            if (cmbSelectby.Text == "AInventory" || cmbSelectby.Text == "Category" || cmbSelectby.Text == "SInventory") //Inventory,Category
            {
                string cmd = "Select I.InventoryID as InventoryID,I.Barcode as Barcode, I.InventoryName as Name, I.Description as Description, I.CategoryID as CategoryID, IC.CategoryName as CategoryName," +
               " I.Reorderlevel as Reorderlevel, ICA.PQuantity - ICA.CutQuantity - ICA.RetailQty as Onhand " +
               " From {0}.Inventory I Left Outer Join {0}.InventoryCategories IC On I.CategoryID = IC.CategoryID" +
               " Left Outer Join {0}.InventoryCostAverage ICA On I.InventoryID = ICA.InventoryID" +
               " Where I.Reorderlevel > 0 And I.Active <> 0" +
               " And ICA.PQuantity - ICA.CutQuantity - ICA.RetailQty < I.Reorderlevel " + sWhereCondition + "  " +
               " Order by I.CategoryID, I.InventoryID; ";

                DataTable Dt = new DataTable();


                string command = string.Format(cmd, BranchName, Session["Branch_HQ"].ToString().Trim());
                MySqlParameter[] parameters =
                   {
                     //new MySqlParameter("@D1",D1),
                     //new MySqlParameter("@D2",D2),
                     new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                     //new MySqlParameter("@SearchVa2",txtName.Text.Trim()),

                    };

                Dt = ObjMain.NewGetData_DT(command, parameters);
                GridView1.DataSource = Dt;
                Session["ReorderReport"] = Dt;
                GridView1.DataBind();
            }
            else
            {
                GridView1.DataSource = null;
                GridView1.DataBind();
            }
            
            //lblMsgTotalSelection.Text = " Search Result - from " + txtStartDt.Text + "&nbsp;&nbsp; to &nbsp;&nbsp;" + txtEndDt.Text;
            //idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
            //lblReportSearchResult.Text = "PRODUCT SALES SEACH RESULT";

            //background-color: #4c4c4c;
            //color: White;
        }


        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {


        }
        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs 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)
            {
                GridView1.PageIndex = e.NewPageIndex;
                BindGridview(Session["Branch_HQ"].ToString(), StartDT, EndDt);
            }
            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 )

                GridView1.PageIndex = e.NewPageIndex;
                BindGridview(Cmb_BranchName, StartDT, EndDt);
            }
        }
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            DTSession = (DataTable)Session["ReorderReport"];
            //Create a dummy GridView
            GridView GridView2 = new GridView();
            GridView2.AllowPaging = false;
            GridView2.DataSource = DTSession;
            GridView2.DataBind();



            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=ReorderReport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            for (int i = 0; i < GridView2.Rows.Count; i++)
            {
                //Apply text style to each Row
                GridView2.Rows[i].Attributes.Add("class", "textmode");
            }



            GridView2.RenderControl(hw);
            string style = @"<style> td { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();

        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */
        }


    }
}