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 ItemSale : 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;

        Int32 iDropTbl = 0;
        Int32 iCreateTbl = 0;
        Int32 iInsertTbl = 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 DTExcel = 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();

        MYSQLCLOUD ObjCloudMain = new MYSQLCLOUD();
        // 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_RetailDashboard = string.Empty;
        string L_SummarySales = string.Empty;
        string L_ItemSales = string.Empty;
        string L_TaxAnalysis = string.Empty;
        string L_TopSale = string.Empty;
        string L_MothlySales = string.Empty;
        string L_HourlySales = string.Empty;
        string L_GroupSale = string.Empty;
        string Branchidval = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string BranchName = string.Empty;
        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;

        string sWhereCondition = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {
            PID = "3";            // This is for ItemSale
            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() == ""))
            {
                string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text);
                string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text);

                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();
                }
                cd_BranchAlias = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                LoadProductSaleGrid(cd_BranchAlias, StartDT, EndDt);
            }
            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_ItemSales"].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;
                            btnExcel.Enabled = 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.GetFirstBranch7(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            }


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

            if (ISHQ != "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch7(Session["Branch_HQ"].ToString(), Session["UserId"].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_ItemSales = DtLicense.Rows[0]["ITEMSALE"].ToString();
                Session["L_ItemSales"] = L_ItemSales;

            }
            if (Session["L_ItemSales"].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);


                if (Session["Write_Acess"].ToString() == "N")
                {
                    btnGetReports.Enabled = false;
                    btnApplySearch.Enabled = false;


                }
                if (Session["Isdowld_Acess"].ToString() == "N")
                {
                    btnExcel.Enabled = false;
                    btnExcel2.Enabled = false;
                    btnExcel3.Enabled = false;
                    btnMonthlycondiment.Enabled = false;
                }



            }

        }
        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 txtID_OnTextChange(object sender, EventArgs e)
        {
            txtName.Text = "";
        }

        protected void txtName_OnTextChange(object sender, EventArgs e)
        {
            txtID.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();
                }
            }
            if (cmbSelectby.Text == "Customer")
            {
                if (txtID.Text == "" && txtName.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select CustomerNumber as Row1,CustomerName as Row2,Address as Row3 from {0}.customers;";
                    string command = string.Format(query, Session["Branch_HQ"].ToString().Trim());
                    Dt = ObjMain.GetData_DT(command);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtName.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select CustomerNumber as Row1,CustomerName as Row2,Address as Row3 from {0}.customers " +
                                   " where Customernumber like CONCAT('%',@Customernumber,'%')  ";
                    string command = string.Format(query, Session["Branch_HQ"].ToString().Trim());

                    MySqlParameter[] parameters =
                   {
                        new MySqlParameter("@Customernumber", 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 CustomerNumber as Row1,CustomerName as Row2,Address as Row3 from {0}.customers" +
                                          " where CustomerName like CONCAT('%',@CustomerName,'%') ";
                    string command = string.Format(query, Session["Branch_HQ"].ToString().Trim());
                    MySqlParameter[] parameters =
                    {
                        new MySqlParameter("@CustomerName", txtName.Text.Trim()),

                    };

                    Dt = ObjMain.NewGetData_DT(command, parameters);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else
                {
                    DataTable Dt = new DataTable();
                    string query = " Select CustomerNumber as Row1,CustomerName as Row2,Address as Row3 from {0}.customers" +
                                   " where Customernumber like CONCAT('%',@Customernumber,'%') and customername like CONCAT('%',@CustomerName,'%') ";

                    string command = string.Format(query, Session["Branch_HQ"].ToString().Trim());

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

                    };

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

            if (cmbSelectby.Text == "Group1" || cmbSelectby.Text == "Group2" || cmbSelectby.Text == "Group3" || cmbSelectby.Text == "Group4" || cmbSelectby.Text == "Group5")
            {
                DataTable Dt = new DataTable();
                string sWhereGroupLike = string.Empty;
                if(txtID.Text!=""){
                    sWhereGroupLike = " where  Groupname like CONCAT('%',@Groupname,'%') ";

                }
                string query = "Select GroupID as Row1,GroupName as Row2,Description as Row3 from {0}." + cmbSelectby.Text + " " + sWhereGroupLike + " ";
                string command = string.Format(query, cd_BranchAlias);

                MySqlParameter[] parameters =
                   {
                        new MySqlParameter("@Groupname", txtID.Text.Trim()),
                   };
                Dt = ObjMain.NewGetData_DT(command, parameters);

                grv.DataSource = Dt;
                grv.DataBind();
            }
            if (gvProductsale.Rows.Count > 1)
            {
                gvProductsale.DataSource = null;
                gvProductsale.DataBind();
            }
            lblSearchResult.Text = "Search Result";
            idSearch.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
            

        }

        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 LoadProductSaleGrid(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 businessdate between @D1  and  @D2    and transstatus In(2,3,4) and itemstatus<>1 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);


            gvProductsale.DataSource = DTProductSale;
            gvProductsale.DataBind();
        }
        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Write("ItemSale.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;
                }

                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 = 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_ItemSales = DtLicense.Rows[0]["ITEMSALE"].ToString();
                    Session["L_ItemSales"] = L_ItemSales;

                }

                if (Session["L_ItemSales"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    
                    if (Session["Write_Acess"].ToString() == "N")
                    {
                        btnGetReports.Enabled = false;
                        btnApplySearch.Enabled = false;


                    }
                    else
                    {
                        btnExcel.Enabled = true;
                        cd_BranchAlias = Cmb_BranchName;

                    }

                    if (Session["Isdowld_Acess"].ToString() == "N")
                    {
                        btnExcel.Enabled = false;
                        btnExcel2.Enabled = false;
                        btnExcel3.Enabled = false;
                        btnMonthlycondiment.Enabled = false;
                    }




                }
            }
            UPDATEBRANCH(cd_BranchAlias);
            Curr_BranchFullName = cmbBranches.SelectedItem.Text;
            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
            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";
            BindGridview(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
        }

        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 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 B.categorynumber = @SearchVal ";
            }
            else if (cmbSelectby.Text == "SInventory")
            {
                sWhereCondition = " and B.Barcode = @SearchVal ";
            }
            else if (cmbSelectby.Text == "Customer")
            {
                sWhereCondition = "and A.CustomerNumber = @SearchVal ";
            }
            else
            {
                if (txtName.Text.Trim() != "")
                {
                    sWhereCondition = "And B." + cmbSelectby.Text.Trim() + " = @SearchVa2";
                }
            }

            sCmd = "DROP Table IF EXISTS {0}.`P_AssemblyInventoryUnitCost`";
            string command1 = string.Format(sCmd, BranchName);
            iDropTbl = ObjCloudMain.ExecuteNonQuery(command1);


            sCmd = "Create Table  {0}.`P_AssemblyInventoryUnitCost`(`InventoryID` INTEGER NOT NULL,`UnitCost` DOUBLE DEFAULT 0.00,`Barcode` varchar(18) NOT NULL,`CategoryID` INTEGER NOT NULL, PRIMARY KEY (`InventoryID`)) ENGINE=InnoDB;";
            string command2 = string.Format(sCmd, BranchName);
            iCreateTbl = ObjCloudMain.ExecuteNonQuery(command2);

            sCmd = "Insert Into {0}.P_AssemblyInventoryUnitCost (InventoryID,UnitCost,Barcode,CategoryID) " +
                                                  " SELECT Inventory.InventoryID,  if(SUM(InventoryCostAverage.UnitCost * InventoryAssembly.Quantity)is null,0,SUM(InventoryCostAverage.UnitCost * InventoryAssembly.Quantity)) As UnitCost,Barcode,CategoryID" +
                                                  " FROM {0}.Inventory LEFT JOIN ({0}.InventoryAssembly" +
                                                  " LEFT JOIN {0}.InventoryCostAverage ON InventoryAssembly.AssemblyInventoryID = InventoryCostAverage.InventoryID) ON " +
                                                  " Inventory.inventoryID = InventoryAssembly.inventoryID" +
                                                  " Where Inventory.Class = 3 " +
                                                  " Group by Inventory.InventoryID";

            string command3 = string.Format(sCmd, BranchName);
            iInsertTbl = ObjCloudMain.ExecuteNonQuery(command3);


            if (cmbSelectby.Text == "AInventory" || cmbSelectby.Text == "Category" || cmbSelectby.Text == "SInventory" || cmbSelectby.Text == "Customer") //Inventory,Category,Customer
            {
                string cmd = "Select   b.categorynumber  as CategoryNo,D.CategoryNumber,'' as PriceType,'' as space1,'' as space2,D.CategoryName, sum(B.Qty) as Qty,  " +
                                    " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as GrossAmt,  " +
                                    " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT,  " +
                                    " sum(B.ManualDisc) as ManualDisc,  sum(B.TotalDisc) as TotalDisc, " +
                                    " sum(B.ActualPSDiscAmount) as ActualPSDiscAmount,  " +

                                    " sum(B.MemberDisc) as TotalMemberDisc,   " +
                                    " sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as DiscAmt,   " +
                                    " sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty,   " +
                                    " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as SalesAmt,   " +
                                    " sum(B.TaxAmount1) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST,sum(B.TaxAmount3) as TaxAmount3, " +
                                    " sum(B.TaxAmount4) as TaxAmount4,  sum(B.TaxAmount5) as TaxAmount5,sum(B.TaxAmount6) as TaxAmount6, Round(sum(B.TaxableAmount),2) as TaxableAmount,B.TaxFlag, " +
                                    " '' as UCost , " +
                                    " '' as UnitCost,  " +
                                    " '' as UCostAvg ,   " +
                                    " '' as TUCost ,  " +
                                    " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-Sum(B.UnitCost*B.Qty) as PL    " +
                                    " From {0}.Trans A " +
                                    " Join {0}.TransDetail B on A.TransID=B.TransID " +
                                    " Left Join {0}.Inventorycategories D on B.CategoryNumber=D.Categorynumber  " +
                                    " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  " +

                                    " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1 and  @D2  And A.TransStatus in(2,3,4) " + sWhereCondition + " " +

                                    " group by D.CategoryNumber  order by D.CategoryName ";
                                    

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




                gvProductsale.DataSource = Dt;

                double totalQty = 0;
                double dGrossSale = 0.00;
                double dFOCAmt = 0.00;
                double dItemDisc = 0.00;
                double dTotalDisc = 0.00;
                double dPromo = 0.00;
                double dMemberDisc = 0.00;
                double dDiscAmt = 0.00;
                double iDiscQTY = 0;
                double dSalesAmt = 0.00;
                double dGst = 0.00;
                double dTaxableAmt = 0.00;
                double dUnitCost = 0.00;
                double dUnitCostAvg = 0.00;
                double dTotalCost = 0.00;
                double dPL = 0.00;
                double totalAmount = 0;
                foreach (DataRow dr in Dt.Rows)
                {
                    totalQty += Convert.ToDouble(dr["Qty"]);
                    dFOCAmt += Convert.ToDouble(dr["FOCAMT"]);
                    dGrossSale += Convert.ToDouble(dr["GrossAmt"]);
                    dItemDisc += Convert.ToDouble(dr["ManualDisc"]);
                    dTotalDisc += Convert.ToDouble(dr["TotalDisc"]);
                    dPromo += Convert.ToDouble(dr["ActualPSDiscAmount"]);
                    dMemberDisc += Convert.ToDouble(dr["TotalMemberDisc"]);
                    dDiscAmt += Convert.ToDouble(dr["DiscAmt"]);
                    iDiscQTY += Convert.ToDouble(dr["DiscQty"]);
                    dSalesAmt += Convert.ToDouble(dr["SalesAmt"]);
                    dGst += Convert.ToDouble(dr["GST"]);
                    dTaxableAmt += Convert.ToDouble(dr["TaxableAmount"]);
                    if (dr["UCost"].ToString() == "")
                    {
                        dUnitCost += 0.00;
                    }
                    else
                    {
                        dUnitCost += Convert.ToDouble(dr["UCost"]);
                    }
                    if (dr["TUCost"].ToString() == "")
                    {
                        dTotalCost += 0.00;
                    }
                    else
                    {
                        dTotalCost += Convert.ToDouble(dr["TUCost"]);
                    }

                    if (dr["PL"].ToString() == "")
                    {
                        dPL += 0.00;
                    }
                    else
                    {
                        dPL += Convert.ToDouble(dr["PL"]);
                    }
                }
                gvProductsale.Columns[5].FooterText = totalQty.ToString();
                gvProductsale.Columns[5].FooterStyle.HorizontalAlign = HorizontalAlign.Center;
                gvProductsale.Columns[6].FooterText = dGrossSale.ToString("#,##0.00");
                gvProductsale.Columns[7].FooterText = dFOCAmt.ToString("#,##0.00");
                gvProductsale.Columns[8].FooterText = dItemDisc.ToString("#,##0.00");
                gvProductsale.Columns[9].FooterText = dTotalDisc.ToString("#,##0.00");
                gvProductsale.Columns[10].FooterText = dPromo.ToString("#,##0.00");
                gvProductsale.Columns[11].FooterText = dMemberDisc.ToString("#,##0.00");
                gvProductsale.Columns[12].FooterText = dDiscAmt.ToString("#,##0.00");
                gvProductsale.Columns[13].FooterText = iDiscQTY.ToString("#,##0.00");
                gvProductsale.Columns[14].FooterText = dSalesAmt.ToString("#,##0.00");
                gvProductsale.Columns[15].FooterText = dGst.ToString("#,##0.00");
                gvProductsale.Columns[16].FooterText = dTaxableAmt.ToString("#,##0.00");
                gvProductsale.Columns[17].FooterText = dPL.ToString("#,##0.00");
                gvProductsale.DataBind();
            }
            else //Group Query
            {
                string  sGroupCmd = "Select B.TransID from {0}.Trans A " +
                                                        " join {0}.TransDetail B on A.TransID = B.TransID " +
                                                        " where businessdate Between  @D1  and  @D2  " +
                                                        " and Transstatus = 2 and itemstatus = 0 and  " + cmbSelectby.Text + "<>'' " ;
                DataTable DTGroup = new DataTable();
                string command = string.Format(sGroupCmd, BranchName);
                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                DTGroup = ObjMain.NewGetData_DT(command, parameters);


                if (DTGroup.Rows.Count > 0)
                {
                    string  cmd = "Select   if(B." + cmbSelectby.Text + "<>'',B." + cmbSelectby.Text + ",'No Group')  as CategoryNo,if(B." + cmbSelectby.Text + "<>'',B." + cmbSelectby.Text + ",'No Group') as CategoryNumber,'' as PriceType,'' as space1,'' as space2, " +
                                                        " if(B." + cmbSelectby.Text + "<>'',B." + cmbSelectby.Text + ",'No Group') as CategoryName, sum(B.Qty) as Qty,   " +
                                                        " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as GrossAmt,   " +
                                                        " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT,   " +
                                                        " sum(B.ManualDisc) as ManualDisc,  " +
                                                        " sum(B.TotalDisc) as TotalDisc,  " +
                                                        " sum(B.ActualPSDiscAmount) as ActualPSDiscAmount,  " +
                                                        
                                                        " sum(B.MemberDisc) as TotalMemberDisc,    " +
                                                        " sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as DiscAmt,    " +
                                                        " sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty,    " +
                                                        " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as SalesAmt,    " +
                                                        " sum(B.TaxAmount1) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST,sum(B.TaxAmount3) as TaxAmount3,  " +
                                                        " sum(B.TaxAmount4) as TaxAmount4,  sum(B.TaxAmount5) as TaxAmount5,sum(B.TaxAmount6) as TaxAmount6, " +
                                                        " Round(sum(B.TaxableAmount),2) as TaxableAmount,B.TaxFlag, " +
                                                        " '' as UCost , " +
                                                        " '' as UnitCost, " +
                                                        " '' as UCostAvg , " +
                                                        " '' as TUCost ,   " +
                                                        " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-Sum(B.UnitCost*B.Qty) as PL " +
                                                        " From {0}.Trans A  " +
                                                        " Join {0}.TransDetail B on A.TransID=B.TransID  " +
                                                        " Left Join {0}.Inventorycategories D on B.CategoryNumber=D.Categorynumber " +
                                                        " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber " +
                                                        " Left Join {0}." + cmbSelectby.Text + " E on B." + cmbSelectby.Text + " = E.GroupName " +
                                                        " Where B.ItemStatus<>1 And date(A.BusinessDate) Between  @D1 and  @D2 And A.TransStatus in(2,3,4) " + sWhereCondition + " " +
                                                        " group by B." + cmbSelectby.Text + "  order by B." + cmbSelectby.Text + " ;" ;
                 
                 
                 
                    DataTable DtTemp = new DataTable();
                    DataTable Dt = new DataTable();

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

                    DtTemp = ObjMain.NewGetData_DT(commandA, parametersA);




                    for (int i = DtTemp.Rows.Count - 1; i >= 0; i--)
                    {
                        if (DtTemp.Rows[i][1].ToString() == "No Group")
                        {
                            DtTemp.Rows[i].Delete();
                        }
                         
                    }
                    DtTemp.AcceptChanges();
                    Dt = DtTemp;
                    gvProductsale.DataSource = Dt;

                    double totalQty = 0;
                    double dGrossSale = 0.00;
                    double dFOCAmt = 0.00;
                    double dItemDisc = 0.00;
                    double dTotalDisc = 0.00;
                    double dPromo = 0.00;
                    double dMemberDisc = 0.00;
                    double dDiscAmt = 0.00;
                    double iDiscQTY = 0;
                    double dSalesAmt = 0.00;
                    double dGst = 0.00;
                    double dTaxableAmt = 0.00;
                    double dUnitCost = 0.00;
                    double dUnitCostAvg = 0.00;
                    double dTotalCost = 0.00;
                    double dPL = 0.00;
                    double totalAmount = 0;
                    foreach (DataRow dr in Dt.Rows)
                    {
                        totalQty += Convert.ToDouble(dr["Qty"]);
                        dFOCAmt += Convert.ToDouble(dr["FOCAMT"]);
                        dGrossSale += Convert.ToDouble(dr["GrossAmt"]);
                        dItemDisc += Convert.ToDouble(dr["ManualDisc"]);
                        dTotalDisc += Convert.ToDouble(dr["TotalDisc"]);
                        dPromo += Convert.ToDouble(dr["ActualPSDiscAmount"]);
                        dMemberDisc += Convert.ToDouble(dr["TotalMemberDisc"]);
                        dDiscAmt += Convert.ToDouble(dr["DiscAmt"]);
                        iDiscQTY += Convert.ToDouble(dr["DiscQty"]);
                        dSalesAmt += Convert.ToDouble(dr["SalesAmt"]);
                        dGst += Convert.ToDouble(dr["GST"]);
                        dTaxableAmt += Convert.ToDouble(dr["TaxableAmount"]);
                        if (dr["UCost"].ToString() == "")
                        {
                            dUnitCost += 0.00;
                        }
                        else
                        {
                            dUnitCost += Convert.ToDouble(dr["UCost"]);
                        }
                        if (dr["TUCost"].ToString() == "")
                        {
                            dTotalCost += 0.00;
                        }
                        else
                        {
                            dTotalCost += Convert.ToDouble(dr["TUCost"]);
                        }

                        if (dr["PL"].ToString() == "")
                        {
                            dPL += 0.00;
                        }
                        else
                        {
                            dPL += Convert.ToDouble(dr["PL"]);
                        }
                    }
                    //gvProductsale.Columns[6].FooterText = totalQty.ToString();
                    //gvProductsale.Columns[6].FooterStyle.HorizontalAlign = HorizontalAlign.Center;
                    //gvProductsale.Columns[7].FooterText = dGrossSale.ToString("#,##0.00");
                    //gvProductsale.Columns[8].FooterText = dFOCAmt.ToString("#,##0.00");
                    //gvProductsale.Columns[9].FooterText = dItemDisc.ToString("#,##0.00");
                    //gvProductsale.Columns[10].FooterText = dTotalDisc.ToString("#,##0.00");
                    //gvProductsale.Columns[11].FooterText = dPromo.ToString("#,##0.00");
                    //gvProductsale.Columns[12].FooterText = dMemberDisc.ToString("#,##0.00");
                    //gvProductsale.Columns[13].FooterText = dDiscAmt.ToString("#,##0.00");
                    //gvProductsale.Columns[14].FooterText = iDiscQTY.ToString("#,##0.00");
                    //gvProductsale.Columns[15].FooterText = dSalesAmt.ToString("#,##0.00");
                    //gvProductsale.Columns[16].FooterText = dGst.ToString("#,##0.00");
                    //gvProductsale.Columns[17].FooterText = dTaxableAmt.ToString("#,##0.00");
                    //gvProductsale.Columns[18].FooterText = dPL.ToString("#,##0.00");
                    gvProductsale.Columns[1].FooterText = "Grand Total:";
                    gvProductsale.Columns[5].FooterText = totalQty.ToString();
                    gvProductsale.Columns[5].FooterStyle.HorizontalAlign = HorizontalAlign.Center;
                    gvProductsale.Columns[6].FooterText = dGrossSale.ToString("#,##0.00");
                    gvProductsale.Columns[7].FooterText = dFOCAmt.ToString("#,##0.00");
                    gvProductsale.Columns[8].FooterText = dItemDisc.ToString("#,##0.00");
                    gvProductsale.Columns[9].FooterText = dTotalDisc.ToString("#,##0.00");
                    gvProductsale.Columns[10].FooterText = dPromo.ToString("#,##0.00");
                    gvProductsale.Columns[11].FooterText = dMemberDisc.ToString("#,##0.00");
                    gvProductsale.Columns[12].FooterText = dDiscAmt.ToString("#,##0.00");
                    gvProductsale.Columns[13].FooterText = iDiscQTY.ToString("#,##0.00");
                    gvProductsale.Columns[14].FooterText = dSalesAmt.ToString("#,##0.00");
                    gvProductsale.Columns[15].FooterText = dGst.ToString("#,##0.00");
                    gvProductsale.Columns[16].FooterText = dTaxableAmt.ToString("#,##0.00");
                    gvProductsale.Columns[17].FooterText = dPL.ToString("#,##0.00");
                    gvProductsale.DataBind();

                }
                else
                {
                    gvProductsale.DataSource = null;
                    gvProductsale.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;
        }
        public DataTable LoadChild(string CategoryNo, string BranchName, string D1, string D2)
        {
            if (cmbBranches.Visible == false)
            {

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

            }

            if (cmbSelectby.Text == "SInventory")
            {
                sWhereCondition = " and B.Barcode = '" + txtID.Text.Trim() + "' ";
            }
            if (cmbSelectby.Text.Trim() == "AInventory" || cmbSelectby.Text.Trim() == "Category" || cmbSelectby.Text.Trim() == "SInventory" || cmbSelectby.Text.Trim() == "Customer")
            {
              string  cmd1 = "Select  D.CategoryNumber,D.CategoryName, " +
                                                " Case When B.Pricetype = '0' Then 'Normal Price' " +
                                                " When B.Pricetype = '1' Then 'Manual Price'   " +
                                                " When B.Pricetype = '2' Then 'FOC'   " +
                                                " When B.Pricetype = '3' Then 'Price 2'  " +
                                                " When B.Pricetype = '4' Then 'Price 3'   " +
                                                " When B.Pricetype = '5' Then 'Price 4'  " +
                                                " When B.Pricetype = '6' Then 'Package Price' " +
                                                " End as Pricetype,  " +
                                                //" Trim(B.BarCode) as BarCode,B.Name as InventoryName,B.InventoryDesc,B.Price,   " + //John Hide on 05-Feb-2021
                                                " Trim(B.BarCode) as BarCode,B.Name as InventoryName,B.InventoryDesc,I.Price,   " + //John added on 05-Feb-2020 Get Price In Inventory
                                                " sum(B.qty*B.Price)/sum(B.Qty) as AvgPrice, sum(B.Qty) as Qty,  " +
                                                " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as GrossAmt,  " +
                                                " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT, " +
                                                " sum(B.ManualDisc) as ManualDisc,  sum(B.TotalDisc) as TotalDisc,  " +
                                                " sum(B.ActualPSDiscAmount) as ActualPSDiscAmount, " +
                                                //" sum(B.TotalMemberDisc) as TotalMemberDisc, " +
                                                " sum(B.MemberDisc) as TotalMemberDisc, " +
                                               // " sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as DiscAmt, " +
                                                " sum(B.TotalDisc+B.MemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as DiscAmt, " +
                                                " sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty, " +
                                                " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as SalesAmt, " +
                                                " sum(B.TaxAmount1) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST,sum(B.TaxAmount3) as TaxAmount3, sum(B.TaxAmount4) as TaxAmount4, " +
                                                " sum(B.TaxAmount5) as TaxAmount5,sum(B.TaxAmount6) as TaxAmount6, Round(sum(B.TaxableAmount),2) as TaxableAmount, " +
                                                //" if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost) as UnitCost , " +
                                                " if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost) as UnitCost , " +
                                                //" if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost) as UCost , " +
                                                " if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost) as UCost , " +
                                                //" if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost) as UCostAvg , " +
                                                " if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost) as UCostAvg , " +
                                                //" if(B.Class<>3,sum(B.UnitCost)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)) as TUCost , " +
                                                " if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)) as TUCost , " +
                                                //" sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,(sum(B.UnitCost))/sum((B.Qty)*B.Qty),C.unitCost) as PL " +
                                                " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.unitCost) as PL " +
                                                " From {0}.Trans A " +
                                                " Join {0}.TransDetail B on A.TransID=B.TransID  " +
                                                " Left join {0}.Inventory I on I.Barcode = B.Barcode " +  //'John Added on 05-Feb-2021
                                                " Left join {0}.P_AssemblyInventoryUnitCost C on B.Barcode = C.Barcode " +
                                                " Left Join {0}.Inventorycategories D on B.CategoryNumber=D.CategoryNumber   " +
                                                " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  " +
                                                " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1  and  @D2   " +
                                                //" And A.TransStatus =2 and D.Categorynumber = '" + CategoryNo + "' " + sWhereCondition + " " +
                                                " And A.TransStatus in(2,3,4) and D.Categorynumber =@CategoryNo  " + sWhereCondition + " " +
                                                " Group by Trim(B.BarCode),pricetype" ;


                string command = string.Format(cmd1, cd_BranchAlias, Session["Branch_HQ"].ToString().Trim());
                DataTable Dt1 = new DataTable();
                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                     new MySqlParameter("@CategoryNo",CategoryNo),
                     new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                     

                  };

                Dt1 = ObjMain.NewGetData_DT(command, parameters);
                return Dt1;
            }
            else
            {

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




                if (CategoryNo=="No Category")
                {
                    CategoryNo="";
                }
                DataTable Dt1 = new DataTable();
                string cmd1 = "Select  D.CategoryNumber,D.CategoryName, " +
                                                " Case When B.Pricetype = '0' Then 'Normal Price' " +
                                                " When B.Pricetype = '1' Then 'Manual Price'   " +
                                                " When B.Pricetype = '2' Then 'FOC'   " +
                                                " When B.Pricetype = '3' Then 'Price 2'  " +
                                                " When B.Pricetype = '4' Then 'Price 3'   " +
                                                " When B.Pricetype = '5' Then 'Price 4'  " +
                                                " When B.Pricetype = '6' Then 'Package Price' " +
                                                " End as Pricetype,  " +
                                                //" Trim(B.BarCode) as BarCode,B.Name as InventoryName,B.InventoryDesc,B.Price,   " + //John Hide on 05-Feb-2021
                                                  " Trim(B.BarCode) as BarCode,B.Name as InventoryName,B.InventoryDesc,I.Price,   " + //John added on 05-Feb-2020 Get Price In Inventory
                                                " sum(B.qty*B.Price)/sum(B.Qty) as AvgPrice, sum(B.Qty) as Qty,  " +
                                                " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as GrossAmt,  " +
                                                " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT, " +
                                                " sum(B.ManualDisc) as ManualDisc,  sum(B.TotalDisc) as TotalDisc,  " +
                                                " sum(B.ActualPSDiscAmount) as ActualPSDiscAmount, " +
                    //" sum(B.TotalMemberDisc) as TotalMemberDisc, " +
                                                " sum(B.MemberDisc) as TotalMemberDisc, " +
                    // " sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as DiscAmt, " +
                                                " sum(B.TotalDisc+B.MemberDisc+B.ActualPSDiscAmount+B.ManualDisc) as DiscAmt, " +
                                                " sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty, " +
                                                " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as SalesAmt, " +
                                                " sum(B.TaxAmount1) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST,sum(B.TaxAmount3) as TaxAmount3, sum(B.TaxAmount4) as TaxAmount4, " +
                                                " sum(B.TaxAmount5) as TaxAmount5,sum(B.TaxAmount6) as TaxAmount6, Round(sum(B.TaxableAmount),2) as TaxableAmount, " +
                                                //" if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost) as UnitCost , " +
                                                " if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost) as UnitCost , " +
                                                //" if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost) as UCost , " +
                                                " if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost) as UCost , " +
                                                //" if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost) as UCostAvg , " +
                                                " if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost) as UCostAvg , " +
                                                //" if(B.Class<>3,sum(B.UnitCost)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)) as TUCost , " +
                                                " if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)) as TUCost , " +
                                                //" sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,(sum(B.UnitCost))/sum((B.Qty)*B.Qty),C.unitCost) as PL " +
                                                " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.unitCost) as PL " +
                                                " From {0}.Trans A " +
                                                " Join {0}.TransDetail B on A.TransID=B.TransID  " +
                                                " Left join {0}.P_AssemblyInventoryUnitCost C on B.Barcode = C.Barcode " +
                                                " Left join {0}.Inventory I on I.Barcode = B.Barcode " +  //'John Added on 05-Feb-2021
                                                " Left Join {0}.Inventorycategories D on B.CategoryNumber=D.CategoryNumber   " +
                                                " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  " +
                                                " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between  @D1 and  @D2 " +
                                                //" And A.TransStatus =2 and " + cmbSelectby.Text.Trim() + " = '" + CategoryNo + "' " + sWhereCondition + " " +
                                                " And A.TransStatus in(2,3,4) and B." + cmbSelectby.Text.Trim() + " = @CategoryNo " +
                                                " Group by Trim(B.BarCode),pricetype " ;


                string command = string.Format(cmd1, cd_BranchAlias, Session["Branch_HQ"].ToString().Trim());
                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                     new MySqlParameter("@CategoryNo",CategoryNo),
                     
                   };

                Dt1 = ObjMain.NewGetData_DT(command, parameters);

                return Dt1;
            }
        }
        decimal totqty = 0;
        decimal GTOTAMT;
        decimal DisscntQty = 0;
        decimal GDISCAMT;

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

            int id = int.Parse(e.CommandName.ToString());
            int rowindex = int.Parse(e.CommandArgument.ToString());
            GridView GVMember = (GridView)gvProductsale.Rows[rowindex].FindControl("gvchild");
            Button btn = ((Button)gvProductsale.Rows[rowindex].FindControl("Button1"));
            if (btn.Text == "D")
            {
                btn.Text = "C";

                if (cmbBranches.Visible == false)
                {
                    GVMember.DataSource = LoadChild(Convert.ToString(id), Session["Branch_HQ"].ToString(), StartDT, EndDt);
                }
                else
                {
                    Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                    cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                    GVMember.DataSource = LoadChild(Convert.ToString(id), cd_BranchAlias, StartDT, EndDt);
                }
                GVMember.DataBind();
                GVMember.Visible = true;
                decimal total = 0;
                decimal Totqty = 0;
                decimal TotDisQty = 0;
                decimal DiscTot = 0;
                string BarCode = string.Empty;
                string ProductNam = string.Empty;
                foreach (GridViewRow row in GVMember.Rows)
                {
                    BarCode = row.Cells[1].Text;
                    ProductNam = row.Cells[2].Text;
                    Totqty += decimal.Parse(row.Cells[5].Text);
                    total += decimal.Parse(row.Cells[6].Text);
                    TotDisQty += decimal.Parse(row.Cells[7].Text);
                    DiscTot += decimal.Parse(row.Cells[8].Text);
                }
            }
            else
            {
                btn.Text = "D";
                GVMember.Visible = false;
            }

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


            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                GridView gv = (GridView)e.Row.FindControl("gvchild");
                string sCategoryNumber = ((DataRowView)e.Row.DataItem)["CategoryNo"].ToString().Trim();
                gv.DataSource = LoadChild(Convert.ToString(sCategoryNumber), cd_BranchAlias, StartDT, EndDt);
                gv.DataBind();
            }
            if (e.Row.RowType == DataControlRowType.Footer)
            {

            }

        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */
        }
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            string sWhereCondition = string.Empty;
            if (cmbBranches.Visible == true)
            {
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchName = cd_BranchAlias;
            }
            else
            {
                BranchName = Session["Branch_HQ"].ToString();
            }

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

            }
            else if (cmbSelectby.Text == "Category")
            {
                sWhereCondition = " and D.categorynumber =@SearchVal ";
            }
            else if (cmbSelectby.Text == "SInventory")
            {
                sWhereCondition = " and B.Barcode =@SearchVal ";
            }
            else if (cmbSelectby.Text == "Customer")
            {
                sWhereCondition = "and A.CustomerNumber = @SearchVal ";
                
                DTExcel.Clear();
                string sCmd = string.Empty;
                sCmd = "Select A.CustomerNumber,F.CustomerName,D.CategoryName, " +
                    " Case " +
                    " When B.Pricetype = '0' Then 'Normal Price' " +
                    " When B.Pricetype = '1' Then 'Manual Price' " +
                    " When B.Pricetype = '2' Then 'FOC' " +
                    " When B.Pricetype = '3' Then 'Price 2' " +
                    " When B.Pricetype = '4' Then 'Price 3' " +
                    " When B.Pricetype = '5' Then 'Price 4' " +
                    " When B.Pricetype = '6' Then 'Package Price' " +
                    " End as Pricetype, " +
                    " Trim(B.BarCode) as BarCode,C.InventoryName,C.Description,round(C.Price,2) as Price, " +
                    " round(sum(B.qty*B.Price)/sum(B.Qty),2) as AvgPrice, sum(B.Qty) as Qty,   " +
                    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as GrossAmt,   " +
                    " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT, " +
                    " round(sum(B.ManualDisc),2) as ManualDisc,round(sum(B.TotalDisc),2) as TotalDisc,round(sum(B.ActualPSDiscAmount),2) as ActualPSDiscAmount,  round(sum(B.TotalMemberDisc),2) as TotalMemberDisc,   " +
                    " round(sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as DiscAmt,sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty,    " +
                    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as SalesAmt,  round(sum(B.TaxAmount1),2) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST, " +
                    " round(sum(B.TaxAmount3),2) as TaxAmount3, round(sum(B.TaxAmount4),2) as TaxAmount4,  round(sum(B.TaxAmount5),2) as TaxAmount5,round(sum(B.TaxAmount6),2) as TaxAmount6, " +
                    " Round(sum(B.TaxableAmount),2) as TaxableAmount, " +
                    " round(IF(C.Class<>3,E.UnitCost,G.Unitcost),2) as UnitCost, " +
                    " round(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as UCost ,   " +
                    " round(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as UCostAvg , " +
                    " format(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as TUCost ,  " +
                    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as PL,    " +
                    " B.Group1,B.Group2,B.Group3,B.Group4,B.Group5" +
                    " From {0}.Trans A Join {0}.TransDetail B on A.TransID=B.TransID Left Join {0}.Inventory C on C.BarCode=B.BarCode  " +
                    " Left Join {0}.Inventorycategories D on C.CategoryID=D.CategoryID  Left Join {0}.InventoryCostAverage E on E.InventoryID=C.InventoryID   " +
                    " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  Left Join {0}.P_AssemblyInventoryUnitCost G ON G.InventoryID=C.InventoryID " +
                    //" Where B.ItemStatus<>1 And date(A.BusinessDate)  Between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' And A.TransStatus =2 " + sWhereCondition + " " +
                    " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1 and @D2 And A.TransStatus in(2,3,4) " + sWhereCondition + " " +
                    " Group by Trim(B.BarCode),pricetype order by D.CategoryName;";

                string command = string.Format(sCmd, BranchName, Session["Branch_HQ"].ToString().Trim());

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@D1",txtStartDt.Text.ToString().Trim()),
                     new MySqlParameter("@D2",txtEndDt.Text.ToString().Trim()),
                     new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                    };

                DTExcel = ObjMain.NewGetData_DT(command, parameters);

                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = DTExcel;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition",
                    "attachment;filename=ProductSales.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++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);
                string style = @"<style> td { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();


            }
            if (cmbSelectby.Text != "Customer" && cmbSelectby.Text != "Group1" && cmbSelectby.Text != "Group2" && cmbSelectby.Text != "Group3" && cmbSelectby.Text != "Group4" && cmbSelectby.Text != "Group5")
            {
                DTExcel.Clear();
                string sCmd = string.Empty;
                sCmd = "Select  D.CategoryNumber,D.CategoryName,  " +
                       " Case When B.Pricetype = '0' Then 'Normal Price'  " +
                       " When B.Pricetype = '1' Then 'Manual Price'   " +
                       " When B.Pricetype = '2' Then 'FOC'   " +
                       " When B.Pricetype = '3' Then 'Price 2'  " +
                       " When B.Pricetype = '4' Then 'Price 3'   " +
                       " When B.Pricetype = '5' Then 'Price 4'  " +
                       " When B.Pricetype = '6' Then 'Package Price' " +
                       " End as Pricetype,  " +
                       " Trim(B.BarCode) as BarCode,B.Name,B.InventoryDesc,format(B.Price,2) as Price,   " +
                       " format(sum(B.qty*B.Price)/sum(B.Qty),2) as AvgPrice, sum(B.Qty) as Qty,  " +
                       " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as GrossAmt,  " +
                       " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT, " +
                       " format(sum(B.ManualDisc),2) as ManualDisc,  format(sum(B.TotalDisc),2) as TotalDisc,  " +
                       " format(sum(B.ActualPSDiscAmount),2) as ActualPSDiscAmount, " +
                       " format(sum(B.TotalMemberDisc),2) as TotalMemberDisc, " +
                       " format(sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as DiscAmt, " +
                       " sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty, " +
                       " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as SalesAmt, " +
                       " format(sum(B.TaxAmount1),2) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST,format(sum(B.TaxAmount3),2) as TaxAmount3, format(sum(B.TaxAmount4),2) as TaxAmount4, " +
                       " format(sum(B.TaxAmount5),2) as TaxAmount5,format(sum(B.TaxAmount6),2) as TaxAmount6, Round(sum(B.TaxableAmount),2) as TaxableAmount, " +
                       //" format(if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost),2) as UCostAvg , " +
                       " format(if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost),2) as UCostAvg , " +
                       //" format(if(B.Class<>3,sum(B.UnitCost)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)),2) as TUCost , " +
                       " format(if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)),2) as TUCost , " +
                       //" format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,(sum(B.UnitCost))/sum((B.Qty)*B.Qty),C.unitCost),2) as PL " +
                       " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.unitCost),2) as PL, " +
                       " B.Group1,B.Group2,B.Group3,B.Group4,B.Group5" +
                       " From {0}.Trans A " +
                       " Join {0}.TransDetail B on A.TransID=B.TransID  " +
                       " Left join {0}.P_AssemblyInventoryUnitCost C on B.Barcode = C.Barcode " +
                       " Left Join {0}.Inventorycategories D on B.CategoryNumber=D.CategoryNumber " +
                       " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  " +
                       //" Where B.ItemStatus<>1 And date(A.BusinessDate)  Between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' And A.TransStatus =2 " + sWhereCondition + " " +
                       " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1 and @D2 And A.TransStatus in(2,3,4) " + sWhereCondition + " " +
                       //" Group by Trim(B.BarCode),pricetype order by D.CategoryName,B.Barcode;";
                       " Group by Trim(D.CategoryNumber),pricetype order by D.CategoryName,B.Barcode;";

                      string command = string.Format(sCmd, BranchName, Session["Branch_HQ"].ToString().Trim());

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

                     DTExcel = ObjMain.NewGetData_DT(command, parameters);


                //GridView GridView1 = new GridView();
                //GridView1.AllowPaging = false;
                //GridView1.DataSource = DTExcel;
                //GridView1.DataBind();
                //Response.Clear();
                //Response.Buffer = true;
                ////Response.ContentEncoding = System.Text.Encoding.UTF8;
                //Response.AddHeader("content-disposition",
                //    "attachment;filename=ProductSales.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++)
                //{
                //    GridView1.Rows[i].Attributes.Add("class", "textmode");
                //}
                //GridView1.RenderControl(hw);
                //string style = @"<style> td { mso-number-format:\@; } </style>";
                //Response.Write(style);
                //Response.Output.Write(sw.ToString());
                //Response.Flush();
                //Response.End();
                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = DTExcel;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.ContentEncoding = System.Text.Encoding.Unicode;
                Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
                Response.Charset = "";
                Response.AddHeader("content-disposition",
                    "attachment;filename=ProductSales.xls");
  
                Response.ContentType = "application/vnd.ms-excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter hw = new HtmlTextWriter(sw);
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);
                string style = @"<style> td { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
            else if (cmbSelectby.Text == "Group1" || cmbSelectby.Text == "Group2" || cmbSelectby.Text == "Group3" || cmbSelectby.Text == "Group4" || cmbSelectby.Text == "Group5")
            {
                string sWhereGroupCondition = string.Empty;
                if(txtID.Text=="" || txtName.Text == ""){
                    
                }
                if(txtName.Text!=""){
                    sWhereGroupCondition = "and " + cmbSelectby.Text.Trim() + "='" + txtName.Text.Trim() +"'";
                }
               
                DTExcel.Clear();
                string sCmd = string.Empty;
                sCmd = "Select  E.GroupName,D.CategoryNumber,D.CategoryName, " +
                                                " Case When B.Pricetype = '0' Then 'Normal Price' " +
                                                " When B.Pricetype = '1' Then 'Manual Price'   " +
                                                " When B.Pricetype = '2' Then 'FOC'   " +
                                                " When B.Pricetype = '3' Then 'Price 2'  " +
                                                " When B.Pricetype = '4' Then 'Price 3'   " +
                                                " When B.Pricetype = '5' Then 'Price 4'  " +
                                                " When B.Pricetype = '6' Then 'Package Price' " +
                                                " End as Pricetype,  " +
                                                " Trim(B.BarCode) as BarCode,B.Name as InventoryName,B.InventoryDesc,format(B.Price,2) as Price,   " +
                                                " format(sum(B.qty*B.Price)/sum(B.Qty),2) as AvgPrice,  sum(B.Qty) as Qty,  " +
                                                " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as GrossAmt,  " +
                                                " format(if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00'),2)as FOCAMT, " +
                                                " format(sum(B.ManualDisc),2) as ManualDisc,  format(sum(B.TotalDisc),2) as TotalDisc,  " +
                                                " format(sum(B.ActualPSDiscAmount),2) as ActualPSDiscAmount, " +
                                                " format(sum(B.TotalMemberDisc),2) as TotalMemberDisc, " +
                                                " format(sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as DiscAmt, " +
                                                " format(sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))),2) as DiscQty, " +
                                                " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as SalesAmt, " +
                                                " format(sum(B.TaxAmount1),2) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST,format(sum(B.TaxAmount3),2) as TaxAmount3, format(sum(B.TaxAmount4),2) as TaxAmount4, " +
                                                " format(sum(B.TaxAmount5),2) as TaxAmount5,format(sum(B.TaxAmount6),2) as TaxAmount6, Round(sum(B.TaxableAmount),2) as TaxableAmount, " +
                                                //" format(if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost),2) as UnitCost , " +
                                                " format(if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost),2) as UnitCost , " +
                                                //" format(if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost),2) as UCost , " +
                                                " format(if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost),2) as UCost , " +
                                                //" format(if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost),2) as UCostAvg , " +
                                                " format(if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost),2) as UCostAvg , " +
                                                //" format(if(B.Class<>3,sum(B.UnitCost)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)),2) as TUCost , " +
                                                " format(if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)),2) as TUCost , " +
                                                " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.unitCost),2) as PL " +
                                                " From {0}.Trans A " +
                                                " Join {0}.TransDetail B on A.TransID=B.TransID  " +
                                                " Left join {0}.P_AssemblyInventoryUnitCost C on B.Barcode = C.Barcode " +
                                                " Left Join {0}.Inventorycategories D on B.CategoryNumber=D.CategoryNumber   " +
                                                " Left Join {0}." + cmbSelectby.Text + " E on B." + cmbSelectby.Text + " = E.GroupName " +
                                                " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  " +
                                                " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1 and @D2 " +
                                                " And A.TransStatus =2 and " + cmbSelectby.Text.Trim() + " <> '' " + sWhereGroupCondition + " " +
                                                " Group by Trim(B.BarCode),pricetype order by " + cmbSelectby.Text.Trim() + " ";

                string command = string.Format(sCmd, BranchName, Session["Branch_HQ"].ToString().Trim());
                MySqlParameter[] parameters =
                    {
                      new MySqlParameter("@D1",txtStartDt.Text),
                      new MySqlParameter("@D2",txtEndDt.Text),
                     };

                DTExcel = ObjMain.NewGetData_DT(command, parameters);


                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = DTExcel;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition",
                    "attachment;filename=ProductSales.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++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);
                string style = @"<style> td { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
        }

        protected void btnExcel2_Click(object sender, EventArgs e)
        {

            string sWhereCondition = string.Empty;
            if (cmbBranches.Visible == true)
            {
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchName = cd_BranchAlias;
            }
            else
            {
                BranchName = Session["Branch_HQ"].ToString();
            }

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

            }
            else if (cmbSelectby.Text == "Category")
            {
                sWhereCondition = " and D.categorynumber = @SearchVal ";
            }
            else if (cmbSelectby.Text == "SInventory")
            {
                sWhereCondition = " and B.Barcode = @SearchVal ";
            }
            else if (cmbSelectby.Text == "Customer")
            {
                sWhereCondition = "and A.CustomerNumber = @SearchVal ";

               
                DTExcel.Clear();
                string sCmd = string.Empty;
                sCmd = "Select A.CustomerNumber,F.CustomerName,D.CategoryName, " +
                    " Case " +
                    " When B.Pricetype = '0' Then 'Normal Price' " +
                    " When B.Pricetype = '1' Then 'Manual Price' " +
                    " When B.Pricetype = '2' Then 'FOC' " +
                    " When B.Pricetype = '3' Then 'Price 2' " +
                    " When B.Pricetype = '4' Then 'Price 3' " +
                    " When B.Pricetype = '5' Then 'Price 4' " +
                    " When B.Pricetype = '6' Then 'Package Price' " +
                    " End as Pricetype, " +
                    " Trim(B.BarCode) as BarCode,C.InventoryName,C.Description,round(C.Price,2) as Price, " +
                    " round(sum(B.qty*B.Price)/sum(B.Qty),2) as AvgPrice, sum(B.Qty) as Qty,   " +
                    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as GrossAmt,   " +
                    " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT, " +
                    " round(sum(B.ManualDisc),2) as ManualDisc,round(sum(B.TotalDisc),2) as TotalDisc,round(sum(B.ActualPSDiscAmount),2) as ActualPSDiscAmount,  round(sum(B.TotalMemberDisc),2) as TotalMemberDisc,   " +
                    " round(sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as DiscAmt,sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty,    " +
                    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as SalesAmt,  round(sum(B.TaxAmount1),2) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST, " +
                    " round(sum(B.TaxAmount3),2) as TaxAmount3, round(sum(B.TaxAmount4),2) as TaxAmount4,  round(sum(B.TaxAmount5),2) as TaxAmount5,round(sum(B.TaxAmount6),2) as TaxAmount6, " +
                    " Round(sum(B.TaxableAmount),2) as TaxableAmount,  round(IF(C.Class<>3,E.UnitCost,G.Unitcost),2) as UnitCost,round(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as UCost ,   " +
                    " round(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as UCostAvg ,  format(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as TUCost ,  " +
                    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as PL,    " +
                    " B.Group1,B.Group2,B.Group3,B.Group4,B.Group5" +
                    " From {0}.Trans A Join {0}.TransDetail B on A.TransID=B.TransID Left Join {0}.Inventory C on C.BarCode=B.BarCode  " +
                    " Left Join {0}.Inventorycategories D on C.CategoryID=D.CategoryID  Left Join {0}.InventoryCostAverage E on E.InventoryID=C.InventoryID   " +
                    " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  Left Join {0}.P_AssemblyInventoryUnitCost G ON G.InventoryID=C.InventoryID " +
                    //" Where B.ItemStatus<>1 And date(A.BusinessDate)  Between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' And A.TransStatus =2 " + sWhereCondition + " " +
                    " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1 and @D2 And A.TransStatus in(2,3,4) " + sWhereCondition + " " +
                    " Group by Trim(B.BarCode),pricetype,b.unitcost order by D.CategoryName;";


                string command = string.Format(sCmd, BranchName, Session["Branch_HQ"].ToString().Trim());
                MySqlParameter[] parameters =
                    {
                      new MySqlParameter("@D1",txtStartDt.Text),
                      new MySqlParameter("@D2",txtEndDt.Text),
                      new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                     };

                DTExcel = ObjMain.NewGetData_DT(command, parameters);



                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = DTExcel;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.ContentEncoding = System.Text.Encoding.Unicode;
                Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
                Response.AddHeader("content-disposition",
                    "attachment;filename=ProductSalesDetailed.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++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);
                string style = @"<style> td { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();


            }

            if (cmbSelectby.Text != "Customer" && cmbSelectby.Text != "Group1" && cmbSelectby.Text != "Group2" && cmbSelectby.Text != "Group3" && cmbSelectby.Text != "Group4" && cmbSelectby.Text != "Group5")
            {

               
                DTExcel.Clear();
                string sCmd = string.Empty;

                sCmd = "Select  D.CategoryNumber,D.CategoryName,  " +
                       " Case When B.Pricetype = '0' Then 'Normal Price'  " +
                       " When B.Pricetype = '1' Then 'Manual Price'   " +
                       " When B.Pricetype = '2' Then 'FOC'   " +
                       " When B.Pricetype = '3' Then 'Price 2'  " +
                       " When B.Pricetype = '4' Then 'Price 3'   " +
                       " When B.Pricetype = '5' Then 'Price 4'  " +
                       " When B.Pricetype = '6' Then 'Package Price' " +
                       " End as Pricetype,  " +
                       " Trim(B.BarCode) as BarCode,B.Name as InventoryName,B.InventoryDesc,format(B.Price,2) as Price,   " +
                       " format(sum(B.qty*B.Price)/sum(B.Qty),2) as AvgPrice, sum(B.Qty) as Qty,  " +
                       " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as GrossAmt,  " +
                       " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT, " +
                       " format(sum(B.ManualDisc),2) as ManualDisc,  format(sum(B.TotalDisc),2) as TotalDisc,  " +
                       " format(sum(B.ActualPSDiscAmount),2) as ActualPSDiscAmount, " +
                       " format(sum(B.TotalMemberDisc),2) as TotalMemberDisc, " +
                       " format(sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as DiscAmt, " +
                       " sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty, " +
                       " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as SalesAmt, " +
                       " format(sum(B.TaxAmount1),2) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST,format(sum(B.TaxAmount3),2) as TaxAmount3, format(sum(B.TaxAmount4),2) as TaxAmount4, " +
                       " format(sum(B.TaxAmount5),2) as TaxAmount5,format(sum(B.TaxAmount6),2) as TaxAmount6, Round(sum(B.TaxableAmount),2) as TaxableAmount, " +
                       //" format(if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost),2) as UCostAvg , " +
                       " format(if(B.Class<>3,Sum(B.UnitCost*B.QTY)/sum(B.Qty),C.UnitCost),2) as UCostAvg , " +
                       //" format(if(B.Class<>3,sum(B.UnitCost)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)),2) as TUCost , " +
                       " format(if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)),2) as TUCost , " +
                       //" format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,(sum(B.UnitCost))/sum((B.Qty)*B.Qty),C.unitCost),2) as PL " +
                       " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,sum(B.UnitCost*B.QTY)/sum(B.Qty)*sum(B.Qty),C.unitCost),2) as PL, " +
                       " B.Group1,B.Group2,B.Group3,B.Group4,B.Group5" +
                       " From {0}.Trans A " +
                       " Join {0}.TransDetail B on A.TransID=B.TransID  " +
                       " Left join {0}.P_AssemblyInventoryUnitCost C on B.Barcode = C.Barcode " +
                       " Left Join {0}.Inventorycategories D on B.CategoryNumber=D.CategoryNumber " +
                       " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  " +
                       //" Where B.ItemStatus<>1 And date(A.BusinessDate)  Between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' And A.TransStatus =2 " + sWhereCondition + " " +
                       " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1 and @D2 And A.TransStatus in(2,3,4) " + sWhereCondition + " " +
                       " Group by Trim(B.BarCode),pricetype,B.UnitCost order by D.CategoryName,B.Barcode;";


                string command = string.Format(sCmd, BranchName, Session["Branch_HQ"].ToString().Trim());
                MySqlParameter[] parameters =
                    {
                      new MySqlParameter("@D1",txtStartDt.Text),
                      new MySqlParameter("@D2",txtEndDt.Text),
                      new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                     };

                DTExcel = ObjMain.NewGetData_DT(command, parameters);



                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = DTExcel;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.ContentEncoding = System.Text.Encoding.Unicode;
                Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
                Response.AddHeader("content-disposition",
                    "attachment;filename=ProductSalesDetailed.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++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);
                string style = @"<style> td { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
            else if (cmbSelectby.Text == "Group1" || cmbSelectby.Text == "Group2" || cmbSelectby.Text == "Group3" || cmbSelectby.Text == "Group4" || cmbSelectby.Text == "Group5")
            {
                string sWhereGroupCondition = string.Empty;
                if (txtID.Text == "" || txtName.Text == "")
                {

                }
                if (txtName.Text != "")
                {
                    sWhereGroupCondition = "and " + cmbSelectby.Text.Trim() + "='" + txtName.Text.Trim() + "'";
                }
                
                DTExcel.Clear();
                string sCmd = string.Empty;
                sCmd = "Select  E.GroupName,D.CategoryNumber,D.CategoryName, " +
                                                " Case When B.Pricetype = '0' Then 'Normal Price' " +
                                                " When B.Pricetype = '1' Then 'Manual Price'   " +
                                                " When B.Pricetype = '2' Then 'FOC'   " +
                                                " When B.Pricetype = '3' Then 'Price 2'  " +
                                                " When B.Pricetype = '4' Then 'Price 3'   " +
                                                " When B.Pricetype = '5' Then 'Price 4'  " +
                                                " When B.Pricetype = '6' Then 'Package Price' " +
                                                " End as Pricetype,  " +
                                                " Trim(B.BarCode) as BarCode,B.Name as InventoryName,B.InventoryDesc,format(B.Price,2) as Price,   " +
                                                " format(sum(B.qty*B.Price)/sum(B.Qty),2) as AvgPrice, format(sum(B.Qty),2) as Qty,  " +
                                                " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as GrossAmt,  " +
                                                " format(if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00'),2) as FOCAMT, " +
                                                " format(sum(B.ManualDisc),2) as ManualDisc,format(sum(B.TotalDisc),2) as TotalDisc,  " +
                                                " format(sum(B.ActualPSDiscAmount),2) as ActualPSDiscAmount, " +
                                                " format(sum(B.TotalMemberDisc),2) as TotalMemberDisc, " +
                                                " format(sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as DiscAmt, " +
                                                " sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty, " +
                                                " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as SalesAmt, " +
                                                " format(sum(B.TaxAmount1),2) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST,format(sum(B.TaxAmount3),2) as TaxAmount3, format(sum(B.TaxAmount4),2) as TaxAmount4, " +
                                                " sum(B.TaxAmount5) as TaxAmount5,sum(B.TaxAmount6) as TaxAmount6, Round(sum(B.TaxableAmount),2) as TaxableAmount, " +
                                                // " format(if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost),2) as UnitCost , " +
                                                " format(if(B.Class<>3,Sum(B.UnitCost*(B.QTY))/sum(B.Qty),C.UnitCost),2) as UnitCost , " +
                                                //" format(if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost),2) as UCost , " +
                                                " format(if(B.Class<>3,Sum(B.UnitCost*(B.QTY))/sum(B.Qty),C.UnitCost),2) as UCost , " +
                                                //" format(if(B.Class<>3,Sum(B.UnitCost)/sum(B.Qty),C.UnitCost),2) as UCostAvg , " +
                                                " format(if(B.Class<>3,Sum(B.UnitCost*(B.QTY))/sum(B.Qty),C.UnitCost),2) as UCostAvg , " +
                                                //" format(if(B.Class<>3,sum(B.UnitCost)/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)),2) as TUCost , " +
                                                " format(if(B.Class<>3,sum(B.UnitCost*(B.QTY))/sum(B.Qty)*sum(B.Qty),C.UnitCost*sum(B.QTY)),2) as TUCost , " +
                                                //" format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,(sum(B.UnitCost))/sum((B.Qty)*B.Qty),C.unitCost),2) as PL " +
                                                " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-if(B.Class<>3,sum(B.UnitCost*(B.QTY))/sum(B.Qty)*sum(B.Qty),C.unitCost),2) as PL, " +
                                                 " B.Group1,B.Group2,B.Group3,B.Group4,B.Group5" +
                                                " From {0}.Trans A " +
                                                " Join {0}.TransDetail B on A.TransID=B.TransID  " +
                                                " Left join {0}.P_AssemblyInventoryUnitCost C on B.Barcode = C.Barcode " +
                                                " Left Join {0}.Inventorycategories D on B.CategoryNumber=D.CategoryNumber   " +
                                                " Left Join {0}." + Microsoft.Security.Application.Encoder.HtmlEncode(cmbSelectby.Text) + " E on B." + Microsoft.Security.Application.Encoder.HtmlEncode(cmbSelectby.Text) + " = E.GroupName " +
                                                " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  " +
                                                " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1 and @D2 " +
                                                //" And A.TransStatus =2 and " + cmbSelectby.Text.Trim() + " <> '' " + sWhereGroupCondition + " " +
                                                " And A.TransStatus in(2,3,4) and " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbSelectby.Text.Trim()) + " <> '' " + sWhereGroupCondition + " " +
                                                " Group by Trim(B.BarCode),pricetype,B.UnitCost order by " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbSelectby.Text.Trim()) + " ";


                string command = string.Format(sCmd, BranchName, Session["Branch_HQ"].ToString().Trim());
                MySqlParameter[] parameters =
                    {
                      new MySqlParameter("@D1",txtStartDt.Text),
                      new MySqlParameter("@D2",txtEndDt.Text),
                      new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                     };

                DTExcel = ObjMain.NewGetData_DT(command, parameters);

                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = DTExcel;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.ContentEncoding = System.Text.Encoding.Unicode;
                Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
                Response.AddHeader("content-disposition",
                    "attachment;filename=ProductSalesDetailed.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++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);
                string style = @"<style> td { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
        }

        //nid added for Product Sales with Condiments on 03-Jan-2022
        protected void btnExcel3_Click(object sender, EventArgs e)
        {

            string sWhereCondition = string.Empty;
            if (cmbBranches.Visible == true)
            {
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchName = cd_BranchAlias;
            }
            else
            {
                BranchName = Session["Branch_HQ"].ToString();
            }

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

                DTExcel.Clear();
                string sCmd = string.Empty;
                //sCmd = "Select A.CustomerNumber,F.CustomerName,D.CategoryName, " +
                //    " Case " +
                //    " When B.Pricetype = '0' Then 'Normal Price' " +
                //    " When B.Pricetype = '1' Then 'Manual Price' " +
                //    " When B.Pricetype = '2' Then 'FOC' " +
                //    " When B.Pricetype = '3' Then 'Price 2' " +
                //    " When B.Pricetype = '4' Then 'Price 3' " +
                //    " When B.Pricetype = '5' Then 'Price 4' " +
                //    " When B.Pricetype = '6' Then 'Package Price' " +
                //    " End as Pricetype, " +
                //    " Trim(B.BarCode) as BarCode,C.InventoryName,C.Description,round(C.Price,2) as Price, " +
                //    " round(sum(B.qty*B.Price)/sum(B.Qty),2) as AvgPrice, sum(B.Qty) as Qty,   " +
                //    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount +B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as GrossAmt,   " +
                //    " if(B.Pricetype=2,(round(B.Actualprice,2)),'0.00')as FOCAMT, " +
                //    " round(sum(B.ManualDisc),2) as ManualDisc,round(sum(B.TotalDisc),2) as TotalDisc,round(sum(B.ActualPSDiscAmount),2) as ActualPSDiscAmount,  round(sum(B.TotalMemberDisc),2) as TotalMemberDisc,   " +
                //    " round(sum(B.TotalDisc+B.TotalMemberDisc+B.ActualPSDiscAmount+B.ManualDisc),2) as DiscAmt,sum(if((B.TotalDisc+B.TotalMemberDisc+B.ManualDisc)<>0,B.Qty,if(B.ActualPSDiscAmount<>0,B.DiscQty,0))) as DiscQty,    " +
                //    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as SalesAmt,  round(sum(B.TaxAmount1),2) as TaxAmount1,Round(sum(B.TaxAmount2),2) as GST, " +
                //    " round(sum(B.TaxAmount3),2) as TaxAmount3, round(sum(B.TaxAmount4),2) as TaxAmount4,  round(sum(B.TaxAmount5),2) as TaxAmount5,round(sum(B.TaxAmount6),2) as TaxAmount6, " +
                //    " Round(sum(B.TaxableAmount),2) as TaxableAmount,  round(IF(C.Class<>3,E.UnitCost,G.Unitcost),2) as UnitCost,round(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as UCost ,   " +
                //    " round(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as UCostAvg ,  format(Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as TUCost ,  " +
                //    " round(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount)-Sum(IF(C.Class<>3, B.UnitCost*B.Qty,G.Unitcost*B.qty)),2) as PL    " +
                //    " From {0}.Trans A Join {0}.TransDetail B on A.TransID=B.TransID Left Join {0}.Inventory C on C.BarCode=B.BarCode  " +
                //    " Left Join {0}.Inventorycategories D on C.CategoryID=D.CategoryID  Left Join {0}.InventoryCostAverage E on E.InventoryID=C.InventoryID   " +
                //    " Left Join {1}.Customers F on F.CustomerNumber=A.CustomerNumber  Left Join {0}.P_AssemblyInventoryUnitCost G ON G.InventoryID=C.InventoryID " +
                //    //" Where B.ItemStatus<>1 And date(A.BusinessDate)  Between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' And A.TransStatus =2 " + sWhereCondition + " " +
                //    " Where B.ItemStatus<>1 And date(A.BusinessDate)  Between @D1 and @D2 And A.TransStatus in(2,3,4) " + sWhereCondition + " " +
                //    " Group by Trim(B.BarCode),pricetype,b.unitcost order by D.CategoryName;";


                sCmd = "Select Barcode, Name, InventoryDesc, CategoryName, Sum(Qty) as Qty,PriceType,Format(Price, 2) as Price,Format(Sum(ItemDisc), 2) as ItemDisc,Format(Sum(TotalDisc), 2) as TotalDisc,Format(Sum(ActualPSDiscAmount), 2) as PSDisc,Format(Sum(TotalMemberDisc), 2) as TotalMDisc, " +
                    " Sum(DiscQty) as DiscQty,Format(Sum(DiscAmt), 2) as DiscAmt,Format(Sum(SalesAmt), 2) as SalesAmt,Group1,Group2,Group3,Group4,Group5  From(" +
                    "   Select T.TransID as TransID, TD.ItemId as ItemID, TD.Barcode as Barcode, TD.Name as Name, TD.InventoryDesc as InventoryDesc, IC.CategoryName as CategoryName, TD.Qty as Qty, " +
                    "   Case When TD.Pricetype = '0' Then 'Normal Price' " +
                    "   When TD.Pricetype = '1' Then 'Manual Price' " +
                    "   When TD.Pricetype = '2' Then 'FOC' " +
                    "   When TD.Pricetype = '3' Then 'Price 2' " +
                    "   When TD.Pricetype = '4' Then 'Price 3' " +
                    "   When TD.Pricetype = '5' Then 'Price 4' " +
                    "   When TD.Pricetype = '6' Then 'Package Price' " +
                    "   End as Pricetype, " +
                    " TD.Price as Price, " +
                    " TD.ManualDisc as ItemDisc, TD.TotalDisc as TotalDisc, TD.ActualPSDiscAmount as ActualPSDiscAmount, TD.TotalMemberDisc as TotalMemberDisc, " +
                    " if ((TD.TotalDisc + TD.TotalMemberDisc + TD.ManualDisc) <> 0,TD.Qty,if (TD.ActualPSDiscAmount <> 0,TD.DiscQty,0)) as DiscQty, " +
                    " Format(TD.TotalDisc + TD.TotalMemberDisc + TD.ActualPSDiscAmount + TD.ManualDisc, 2) as DiscAmt, " +
                    " Format((TD.TotalDisc + TD.TotalMemberDisc + TD.ActualPSDiscAmount + TD.ManualDisc) / TD.Amount, 2) as TotDiscPerc, " +
                    " Format(TD.TaxAmount1 + TD.TaxAmount2 + TD.TaxAmount3 + TD.TaxAmount4 + TD.TaxAmount5 + TD.TaxAmount6 + TD.TaxableAmount, 2) - " +
                    " IFNUll((Select Sum(RIGHT(TDC1.CondimentName, LENGTH(TDC1.CondimentName) - 1 - INSTR(TDC1.CondimentName, ' +')) - (RIGHT(CondimentName, LENGTH(CondimentName) - 1 - INSTR(CondimentName, ' +')) * (TD.TotalDisc + TD.TotalMemberDisc + TD.ActualPSDiscAmount + TD.ManualDisc) / TD.Amount)) as Price " +
                    " From {0}.Trans T1 Left Outer Join {0}.TransDetail TD1 On T1.TransID = TD1.TransID " +
                    " Left Outer Join {0}.TransDetailCondiment TDC1 on TD1.TransID = TDC1.TransID " +
                    " Where T.TransID = T1.TransID " +
                    " And TD.TransID = TD1.TransID " +
                    " And TD1.ItemID = TDC1.ItemID " +
                    " And TD.ItemID = TDC1.ItemID " +
                    " And INSTR(TDC1.CondimentName, ' +') > 0 " +
                    " And T1.TransStatus in (2) And TD1.ItemStatus<>1 " +
                    " And T.BusinessDate Between @D1 and @D2 " +
                    " ),0) as SalesAmt,Group1,Group2,Group3,Group4,Group5  " +
                    " From {0}.Trans T Left Outer Join {0}.TransDetail TD On T.TransID = TD.TransID " +
                    " Left Outer Join {0}.InventoryCategories IC On TD.CategoryNumber = IC.CategoryNumber " +
                    " Where T.TransStatus in (2) And TD.ItemStatus<>1 " +
                    " And T.BusinessDate Between @D1 and @D2 " +
                    " Union ALL " +
                    " Select T.TransID as TransID,TD.ItemId as ItemID,'Condiment' as Barcode, TDC.CondimentName as Name, TDC.CondimentDesc as InventoryDescription, 'Z-Condiment' as CategoryName, 1 as Qty, " +
                    " 'Manual Price' as PriceType, " +
                    " Format(RIGHT(CondimentName, LENGTH(CondimentName) - 1 - INSTR(CondimentName, ' +')), 2) as Price, " +
                    " Format(0, 2) as ItemDisc,Format(0, 2) as TotalDisc,Format(0, 2) as ActualPSDiscount,Format(0, 2) as TotalMemberDisc, " +
                    " 0 as DiscQty, " +
                    " Format(0, 2) as DiscAmt, " +
                    " Format((TD.TotalDisc + TD.TotalMemberDisc + TD.ActualPSDiscAmount + TD.ManualDisc) / TD.Amount, 2) as TotDiscPerc, " +
                    " Format(RIGHT(CondimentName, LENGTH(CondimentName) - 1 - INSTR(CondimentName, ' +')) - (RIGHT(CondimentName, LENGTH(CondimentName) - 1 - INSTR(CondimentName, ' +')) * (TD.TotalDisc + TD.TotalMemberDisc + TD.ActualPSDiscAmount + TD.ManualDisc) / TD.Amount), 2) as SalesAmt,Group1,Group2,Group3,Group4,Group5  " +
                    " From {0}.Trans T Left Outer Join {0}.TransDetail TD On T.TransID = TD.TransID " +
                    " Left Outer Join {0}.TransDetailCondiment TDC on TD.TransID = TDC.TransID " +
                    " Where T.TransStatus in (2) And TD.ItemStatus<>1 " +
                    " And TD.ItemID = TDC.ItemID " +
                    " And INSTR(TDC.CondimentName, ' +')> 0 " +
                    " And T.BusinessDate Between @D1 and @D2 " +
                    " ) as K " +
                    " Group By Name,InventoryDesc,CategoryName " +
                    " Order by CategoryName, Barcode; ";

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

                DTExcel = ObjMain.NewGetData_DT(command, parameters);

                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = DTExcel;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.ContentEncoding = System.Text.Encoding.Unicode;
                Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
                Response.AddHeader("content-disposition",
                    "attachment;filename=ProductSaleswithCondiments.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++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);
                string style = @"<style> td { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();


            }

 
        }
        protected void gvProductsale_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            if (cmbBranches.Visible == false)
            {
                gvProductsale.PageIndex = e.NewPageIndex;
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )              
                BindGridview(Session["Branch_HQ"].ToString(), txtStartDt.Text, txtEndDt.Text);
            }
            else
            {
                gvProductsale.PageIndex = e.NewPageIndex;
                Curr_BranchFullName = 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 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 cmbSelectby_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtID.Text = "";
            txtName.Text = "";
            //grv.DataSource = null;
            //grv.DataBind();

            //gvProductsale.DataSource = null;
            //gvProductsale.DataBind();
        }

        protected void btnMonthlycondiment_Click(object sender, EventArgs e)
        {

            string sWhereCondition = string.Empty;
            if (cmbBranches.Visible == true)
            {
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName); 
                BranchName = cd_BranchAlias;
            }
            else
            {
                BranchName = Session["Branch_HQ"].ToString();
            }

            

                DTExcel.Clear();
                string sCmd = string.Empty;
                // Get the Starting Day of the Year
                string _FD = ObjMain.ExecuteScalar("  SELECT  DATE_FORMAT(MAKEDATE(year(now()),1),'%y-%m-%d') FD;");
                // Get the ending day of the year 
                string _LD = ObjMain.ExecuteScalar("SELECT  DATE_FORMAT(LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH)),'%y-%m-%d') AS LD ;");
               

                  sCmd = " Select K.CondimentName as CondimentName, Sum(K.Jan_Qty) as Jan_Qty,Sum(K.Feb_Qty) as Feb_Qty,Sum(K.Mar_Qty) as Mar_Qty,Sum(K.Apr_Qty) as Apr_Qty,Sum(K.May_Qty) as May_Qty, "
                  + "Sum(K.Jun_Qty) as Jun_Qty, Sum(K.Jul_Qty) as Jul_Qty,Sum(K.Aug_Qty) as Aug_Qty,Sum(K.Sep_Qty) as Sep_Qty,Sum(K.Oct_Qty) as Oct_Qty,Sum(K.Nov_Qty) as Nov_Qty,Sum(K.Dec_Qty) as Dec_Qty "
                  + "From"
                  + "("
                  + "Select TDC.CondimentName As CondimentName, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty,"
                  + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+ _FD + "' And '"+_LD +"'"
                  + " And Month(T.BusinessDate) = '01' "
                  + " Group by TDC.CondimentName "
                  + " union all"

                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty,"
                  + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+_FD+"' And '"+_LD+"' "
                  + " And Month(T.BusinessDate) = '02' "
                  + " Group by TDC.CondimentName "
                  + " union all "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty,"
                  + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From juss1.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+ _FD +"' And '"+_LD +"' "
                  + " And Month(T.BusinessDate) = '03' "
                  + " Group by TDC.CondimentName "
                  + " union all "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Apr_Qty, 0 as May_Qty, "
                  + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+ _FD+"' And '"+_LD +"' "
                  + " And Month(T.BusinessDate) = '04' "
                  + " Group by TDC.CondimentName "
                  + " union All "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as May_Qty, "
                  + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+ _FD+"' And '"+_LD+"' "
                  + "  And Month(T.BusinessDate) = '05' "
                  + " Group by TDC.CondimentName "
                  + " union all "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Jun_Qty, "
                  + " 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+_FD+"' And '"+_LD+"' "
                  + " And Month(T.BusinessDate) = '06' "
                  + " Group by TDC.CondimentName "
                  + " Union All "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, "
                  + " IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+_FD+"' And '"+_LD +"' "
                  + " And Month(T.BusinessDate) = '07' "
                  + " Group by TDC.CondimentName "
                  + " Union All "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, "
                  + " IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+_FD+"' And '"+ _LD+"' "
                  + " And Month(T.BusinessDate) = '08' "
                  + " Group by TDC.CondimentName "
                  + " Union All "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, "
                  + " IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null"
                  + " And T.BusinessDate Between '"+ _FD+"' And '"+ _LD+"' "
                  + " And Month(T.BusinessDate) = '09' "
                  + " Group by TDC.CondimentName "
                  + " Union All "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, "
                  + " IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+_FD +"' And '"+ _LD+"' "
                  + " And Month(T.BusinessDate) = '10' "
                  + " Group by TDC.CondimentName "
                  + " Union All "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, "
                  + " 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Nov_Qty, 0 as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+ _FD+"' And '"+ _LD+"' "
                  + " And Month(T.BusinessDate) = '11' "
                  + " Group by TDC.CondimentName "
                  + " Union All "
                  + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, "
                  + " 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Dec_Qty "
                  + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
                  + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
                  + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
                  + " And T.BusinessDate Between '"+_FD+"' And '"+ _LD+ "' "
                  + " And Month(T.BusinessDate) = '12' "
                  + " Group by TDC.CondimentName "
                  + " ) as K "
                  + " Group By K.CondimentName; ";

                string command = string.Format(sCmd, BranchName);
                DTExcel = ObjMain.GetData_DT(command);
                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = DTExcel;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.ContentEncoding = System.Text.Encoding.Unicode;
                Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
                Response.AddHeader("content-disposition",
                    "attachment;filename=MonthlyCondiment.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++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);
                string style = @"<style> td { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            

        }
    }
}