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 System.Web.UI.DataVisualization.Charting;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.Services;


namespace SUNWEB
{
    public partial class TopsaleNew : System.Web.UI.Page
    {
        BL_SELECT ObjSel = new BL_SELECT();
        BLCompany ObjBL = new BLCompany();
        PLCOMMON ObjPL = new PLCOMMON();
        BL_USER ObjUserBL = new BL_USER(); //This Object for All User and Page Related fuctions included 
        PLCUSTOMER ObjCustPL = new PLCUSTOMER();
        Int32 K = 0;
        Int32 L = 0;
        Int32 S = 0;
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        DataSet ds = new DataSet();
        string Rcd_id = string.Empty;
        string RPortalid = string.Empty;
        string Useridvalue = string.Empty;
        string QueryCurr_Select = string.Empty;
        string ActiveCallvalue = 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;
        DataTable dtgraphItem = new DataTable("Chart");
        DataTable dtgraphCat = new DataTable("Chart");
        string X, Y;
        string GraphWidth = "360";
        string GraphHeight = "260";
        string[] color = new string[12];
        MYSQLCLOUD ObjMainCloud = new MYSQLCLOUD();
        MYSQLDB ObjMain = new MYSQLDB();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable DtSuperAdmin = new DataTable();
        DataTable DtMultiBranch = new DataTable();
        DataTable DtNormalUser = new DataTable();
        DataTable DTExcel = new DataTable();
        DataTable DTExcelTemp = new DataTable();

        decimal totaltaxableamount = 0;
        decimal Gtotaltaxableamount = 0;
        decimal totaltaxamount = 0;
        decimal Gtotaltaxamount = 0;
        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 PID = string.Empty;
        string Page_Acess = string.Empty;
        string Read_Acess = string.Empty;
        string Write_Acess = string.Empty;
        string Isdowld_Acess = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;

        string sWhereCondition = string.Empty;
        string sOrderByCondition = string.Empty;


        StringBuilder str = new StringBuilder();
        MySqlConnection con = new MySqlConnection("Data Source=suntoyords.clx5rxtnm7i1.ap-southeast-1.rds.amazonaws.com;port=3306;Initial Catalog=masterdata;User Id=suntoyoroot;password=Suntoyo_123");
        protected void Page_Load(object sender, EventArgs e)
        {

            PID = "5";            // This is for TopSale
            Chart sales = new Chart();
            if (!IsPostBack)
            {
                txtFromDt.Text = Curr_DateOnly;
                txtToDt.Text = Curr_DateOnly;
            }
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();
                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();
                    }
                }


            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();

                    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["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName();
                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        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;
                    // LoadDate(Session["Branch_HQ"].ToString(), txtFromDt.Text, txtToDt.Text);
                    //LoadDataGraphByCategory(Session["Branch_HQ"].ToString(), txtFromDt.Text, txtToDt.Text);
                    //LoadDataGraphByItem(Session["Branch_HQ"].ToString(), txtFromDt.Text, txtToDt.Text);
                    System.Threading.Thread.Sleep(2000);
                }
                if ((Session["L_TopSale"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    if (Session["Write_Acess"].ToString() == "N")
                    {
                        btnGetReports.Enabled = false;
                    }
                    if (Session["Isdowld_Acess"].ToString() == "N")
                    {
                        btnExcel.Enabled = false;
                        btnExcel2.Enabled = false;
                    }



                }

            }
            else
            {

            }
        }

        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 LoadDate(string Branch_Nam, string D1, string D2)
        {
           
            string Qry1 = "Select b.barcode,b.Name,b.InventoryDesc,sum(qty) as QTY,sum(b.taxamount1+b.taxamount2+b.taxamount3+b.TaxableAmount) as TPrice " +
                        " 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 " +
                        " where businessdate between @D1  and @D2  and TransStatus in (2,3,4) and itemstatus=0 " +
                        " group by barcode order by qty desc ; ";

            string command = string.Format(Qry1, Branch_Nam);
            MySqlParameter[] parameters =
                 {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

            DT = ObjMain.NewGetData_DT(command, parameters);
            grdTopSales.DataSource = DT;
            grdTopSales.DataBind();
        }
        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_TopSale = DtLicense.Rows[0]["TOPSALE"].ToString();
                Session["L_TopSale"] = L_TopSale;

            }
            if (Session["L_TopSale"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {
                //lblMsgTotalSelection.Text = "Top Sale   Between" + "&nbsp; &nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                ////LoadDate(ActiveCallvalue, txtFromDt.Text, txtToDt.Text);
                //LoadDataGraphByCategory(ActiveCallvalue, txtFromDt.Text, txtToDt.Text);
                //LoadDataGraphByItem(ActiveCallvalue, txtFromDt.Text, txtToDt.Text);
                //System.Threading.Thread.Sleep(2000);

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


            }
        }
        public void UPDATEBRANCH(string ActiveCallvalue)
        {
            if (!string.IsNullOrEmpty(Session["UserId"] as string))
            {
                Useridvalue = Session["UserId"].ToString();
                QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
                S = ObjMain.ExecuteNonQuery(QueryCurr_Select);
            }
            else
            {
                Response.Redirect("Login.aspx");
            }
        }
        public void LoadBranchName()
        {
            DtNormalUser = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            if (DtNormalUser.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtNormalUser;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {

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



            if (cmbBranches.Visible == true)
            {
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                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 )

            }

            if (cmbBranches.Visible == false)
            {
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = Session["Branch_HQ"].ToString();

            }

            //Label1.Text = "Search Result -" + "&nbsp; &nbsp;Top Sale Between  Between" + "&nbsp;&nbsp;&nbsp; " + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;

            if (cmbBranches.SelectedIndex != 0)
            {
                //lblMsgTotalSelection.Text = "Search Result" + "&nbsp; &nbsp;Top Sale   Between " + "&nbsp; &nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                ////LoadDate(cd_BranchAlias, txtFromDt.Text, txtToDt.Text);
                //LoadDataGraphByCategory(cd_BranchAlias, txtFromDt.Text, txtToDt.Text);

                //LoadDataGraphByItem(cd_BranchAlias, txtFromDt.Text, txtToDt.Text);


                UPDATEBRANCH(cd_BranchAlias);
            }
            else
            {
            }

            // XSS Prevention 

            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text);
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text);


            //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
            Curr_BranchFullName = cmbBranches.SelectedItem.Text.Trim();
            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);
            GetTopSales(cd_BranchAlias, StartDT, EndDt);
            Page.Response.Redirect(Page.Request.Url.ToString(), true);
        }
        protected void btnGetReports_Click(object sender, EventArgs e)
        {
            // XSS Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text);
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text);

            //Label1.Text = "Search Result -" + "&nbsp; &nbsp;Top Sale Between  Between" + "&nbsp;&nbsp;&nbsp; " + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
            System.Threading.Thread.Sleep(2000);
            if (cmbBranches.Visible == true)
            {
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                Curr_BranchFullName = cmbBranches.SelectedItem.Text.Trim();
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )

            }

            if (cmbBranches.Visible == false)
            {
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                Curr_BranchFullName =cmbBranches.SelectedItem.Text.Trim();
                cd_BranchAlias = Session["Branch_HQ"].ToString();

            }

            if (cmbBranches.SelectedIndex != 0)
            {

                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                Curr_BranchFullName =cmbBranches.SelectedItem.Text.Trim();
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);
                GetTopSales(cd_BranchAlias, StartDT, EndDt);
            }
            // Page.Response.Redirect(Page.Request.Url.ToString(), true);  

            lblMsgTotalSelection.Text = " Search Result - from " + StartDT.Trim() + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt.Trim();
            idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
            //lblReportSearchResult.Text = "PRODUCT SALES SEACH RESULT";

        }



        private void LoadDataGraphByItem(string Branch_Nam, string D1, string D2)
        {

            if (cmbBranches.Visible == true)
            {
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                Curr_BranchFullName = cmbBranches.SelectedItem.Text.Trim();
                Branch_Nam = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )

            }

            if (cmbBranches.Visible == false)
            {
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                Curr_BranchFullName = cmbBranches.SelectedItem.Text.Trim();
                Branch_Nam = Session["Branch_HQ"].ToString();

            }

            string sCmd = "Select TRIM(SUBSTRING(`InventoryDesc`, 1, 16)) as InventoryDesc,sum(qty) as QTY " +
                         " from {0}.trans a join {0}.transdetail b " +
                         " on a.transid=b.transid " +
                         " where businessdate between  @D1  and @D2  and TransStatus in (2,3,4) and itemstatus=0 " +
                         " group by barcode order by qty desc limit 10 ;";

            string command = string.Format(sCmd, Branch_Nam);
            MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

            dtgraphItem = ObjMain.NewGetData_DT(command, parameters);
            Session["dtgraphItem"] = dtgraphItem;
            BindChart();

        }

        private void LoadDataGraphByCategory(string Branch_Nam, string D1, string D2)
        {

            if (cmbBranches.Visible == true)
            {
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                Branch_Nam = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )

            }

            if (cmbBranches.Visible == false)
            {
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                Branch_Nam = Session["Branch_HQ"].ToString();

            }


            string sCmd = "  Select C.CategoryName,sum(qty) as QTY " +
                          " from {0}.trans a join {0}.transdetail b " +
                          " on a.transid=b.transid join {0}.inventorycategories c " +
                          " on b.categorynumber = c.categorynumber " +
                          " where businessdate between  @D1  and  @D2  and TransStatus in (2,3,4) and itemstatus=0 " +
                          " group by b.CategoryNumber order by qty desc limit 10 ;";
            string command = string.Format(sCmd, Branch_Nam);
            MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

            dtgraphCat = ObjMain.NewGetData_DT(command, parameters);

            Session["dtgraphCat"] = dtgraphCat;



        }

        private void BindChart()
        {
            DataTable dt = new DataTable();
            try
            {
                dt = (DataTable)Session["dtgraphItem"];

                str.Append(@"<script type=text/javascript> google.load( *visualization*, *1*, {packages:[*corechart*]});
                       google.setOnLoadCallback(drawChart);
                       function drawChart() 
                       {
                       var data = new google.visualization.DataTable();
                      data.addColumn('string', 'InventoryDesc');
                      data.addColumn('number', 'QTY');      
                      data.addRows(" + dt.Rows.Count + ");");

                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["InventoryDesc"].ToString() + "');");
                    str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["QTY"].ToString() + ") ;");
                }

                str.Append(" var chart = new google.visualization.PieChart(document.getElementById('chart_div'));");
                str.Append(" chart.draw(data, {height:400,  title: 'Top Sale By Item',");
                //str.Append("hAxis: {title: 'MONTH', titleTextStyle: {color: 'green'}}");
                str.Append("}); }");
                str.Append("$(window).resize(function(){ drawChart();});");
                str.Append("</script>");
                //lt.Text = str.ToString().TrimEnd(',').Replace('*', '"');
            }
            catch
            {
            }
        }



        private void BindChart1()
        {
            DataTable dt = new DataTable();
            try
            {
                dt = (DataTable)Session["dtgraphCat"];

                str.Append(@"<script type=text/javascript> google.load( *visualization*, *1*, {packages:[*corechart*]});
                       google.setOnLoadCallback(drawChart1);
                       function drawChart1() 
                       {
                       var data = new google.visualization.DataTable();
                      data.addColumn('string', 'CategoryName');
                      data.addColumn('number', 'QTY');      
                      data.addRows(" + dt.Rows.Count + ");");

                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["CategoryName"].ToString() + "');");
                    str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["QTY"].ToString() + ") ;");
                }

                str.Append(" var chart = new google.visualization.PieChart(document.getElementById('chart_Div1'));");
                str.Append(" chart.draw(data, {height:400,  title: 'Top Sale By Category',");
                //str.Append("hAxis: {title: 'MONTH', titleTextStyle: {color: 'green'}}");
                str.Append("}); }");
                str.Append("$(window).resize(function(){ drawChart1();});");
                str.Append("</script>");
                //Literal1.Text = str.ToString().TrimEnd(',').Replace('*', '"');
            }
            catch
            {
            }
        }




        protected void grdTopSales_SelectedIndexChanged(object sender, EventArgs e)
        {

        }


        protected void btnApplySearch_Click(object sender, EventArgs e)
        {
            //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
            Curr_BranchFullName = cmbBranches.SelectedItem.Text;
            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);



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

            }
            else if (CmbSelect.Text == "Category")
            {
                string cmd = "Select CategoryNumber as Row1,CategoryName as Row2 ,Description as Row3 from {0}.inventorycategories where Active <> 0 and CategoryName <> 'round';";
                string command = string.Format(cmd, cd_BranchAlias);
                DT = ObjMain.GetData_DT(command);
                grv.DataSource = DT;
                grv.DataBind();
            }
            else if (CmbSelect.Text == "Group1" || CmbSelect.Text == "Group2" || CmbSelect.Text == "Group3" || CmbSelect.Text == "Group4" || CmbSelect.Text == "Group5")
            {
                string cmd = "Select GroupID as Row1,GroupName as Row2,Description as Row3 from {0}.{1} ";

                DataTable Dt = new DataTable();
                string command = string.Format(cmd, cd_BranchAlias, Microsoft.Security.Application.Encoder.HtmlEncode(CmbSelect.Text));

                Dt = ObjMain.GetData_DT(command);



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


            lblSearchResult.Text = "Search Result";
            idSearch.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
            //grdTopSales.DataSource = null;
            //grdTopSales.DataBind();
        }

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

            }
        }

        protected void cmbSelect_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtID.Text = "";
            txtName.Text = "";
            grv.DataSource = null;
            grv.DataBind();

            grdTopSales.DataSource = null;
            grdTopSales.DataBind();
            //cmdAction.ServerClick += new EventHandler(submit_click);
            //Curr_BranchFullName = cmbBranches.SelectedItem.Text;
            //cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);
            //GetTopSales(cd_BranchAlias, txtFromDt.Text, txtToDt.Text);
            btnApplySearch_Click(btnApplySearch, EventArgs.Empty);
        }

        private void GetTopSales(string sBranchName, string DFrom, string DTo)
        {
            sOrderByCondition = "";
            sWhereCondition = "";
            if (CmbSelect.Text == "AInventory")
            {
                sWhereCondition = "";
                if (cmbTopBy.Text == "Amount")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by totalTemp " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " ";
                    }
                    else
                    {
                        sOrderByCondition = "order by totalTemp " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                    }

                }
                else if (cmbTopBy.Text == "QTY")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by Qty  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by Qty  " + cmbOrderBy.Text + " limit " + txtTopN.Text.Trim() + "";
                    }
                }
                else if (cmbTopBy.Text == "AMTPER")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by AMTPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by AMTPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                    }
                }
                else if (cmbTopBy.Text == "QTYPER")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by QTYPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by QTYPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                    }
                }

            }
            else if (CmbSelect.Text == "Category")
            {
                sWhereCondition = " and C.CategoryNumber = @ID ";
                if (txtID.Text != "")
                {

                    if (txtToDt.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "  order by Qty  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                    }
                    else
                    {
                        sOrderByCondition = "  order by Qty  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                    }

                }
            }
            else//Group
            {
                sWhereCondition = "";
                if (cmbTopBy.Text == "Amount")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by TotalTemp " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " ";
                    }
                    else
                    {
                        sOrderByCondition = "order by TotalTemp " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                    }

                }
                else if (cmbTopBy.Text == "QTY")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by Qty  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by Qty  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                    }
                }
                else if (cmbTopBy.Text == "AMTPER")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by AMTPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by AMTPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                    }
                }
                else if (cmbTopBy.Text == "QTYPER")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by QTYPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by QTYPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                    }
                }

            }


            if (CmbSelect.Text == "AInventory" || CmbSelect.Text == "Category")
            {

                string cmd = "Select B.CategoryNumber,C.CategoryName,sum(QTY) as QTY, " +
                                     " concat(round((sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +

                                     " where businessdate between @DFrom and @DTo and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER, " +
                                     " (sum(qty)/ " +
                                     " (Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID  " +

                                     " where businessdate between @DFrom and @DTo and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ) AS QTYPERTemp, " +
                                     " format(sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount),2) as Total, " +
                                     " sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) as TotalTemp, " +
                                     " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/ " +
                                     " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from  " +
                                     " {0}.Trans A join {0}.Transdetail B on A.Transid=B.Transid " +

                                     " where businessdate between @DFrom and @DTo and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                     " (sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                     " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from  {0}.Trans A  " +
                                     " join {0}.Transdetail B on A.Transid=B.Transid  " +

                                     " where businessdate between @DFrom and @DTo and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ) AS AMTPERTemp " +
                                     " from {0}.Trans A " +
                                     " join {0}.TransDetail B on A.TransID = B.TransID " +
                                     " join {0}.inventorycategories C on B.CategoryNumber = C.CategoryNumber " +
                                     " where Businessdate between @DFrom and @DTo and  transstatus in (2,3,4) and itemstatus = 0 " + sWhereCondition + " " +
                                     " group by B.CategoryNumber " + sOrderByCondition + " ";



                DataTable Dt = new DataTable();
                string command = string.Format(cmd, sBranchName);

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@DFrom",DFrom),
                     new MySqlParameter("@DTo",DTo),
                     //new MySqlParameter("@TopN",Microsoft.Security.Application.Encoder.HtmlEncode(txtTopN.Text)),
                     //new MySqlParameter("@TopN",txtTopN.Text),
                     //new MySqlParameter("@ID",Microsoft.Security.Application.Encoder.HtmlEncode(txtID.Text))
                     new MySqlParameter("@ID",txtID.Text.Trim())

                    };
                Dt = ObjMain.NewGetData_DT(command, parameters);
                grdTopSales.DataSource = Dt;
                grdTopSales.DataBind();
            }
            else
            {
                MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
                Cn.Open();
                //MySqlCommand cmd = new MySqlCommand("Select D.GroupName as CategoryNumber,D.GroupName as CategoryName,sum(QTY) as QTY, " +
                //                    " concat(round((sum(qty)/(Select sum(qty) from " + sBranchName + ".Trans A join " + sBranchName + ".Transdetail B on A.TransID = B.TransID " +
                //                    " where businessdate between '" + DFrom + "' and '" + DTo + "' and transstatus = 2 and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER, " +
                //                    " (sum(qty)/ " +
                //                    " (Select sum(qty) from " + sBranchName + ".Trans A join " + sBranchName + ".Transdetail B on A.TransID = B.TransID  " +
                //                    " where businessdate between '" + DFrom + "' and '" + DTo + "' and transstatus = 2 and b.itemstatus = 0 ) * 100 ) AS QTYPERTemp, " +
                //                    " format(sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount),2) as Total, " +
                //                    " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/ " +
                //                    " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from  " +
                //                    " " + sBranchName + ".Trans A join " + sBranchName + ".Transdetail B on A.Transid=B.Transid " +
                //                    " where businessdate between '" + DFrom + "' and '" + DTo + "' and transstatus = 2 and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER " +
                //                    " from " + sBranchName + ".Trans A " +
                //                    " join " + sBranchName + ".TransDetail B on A.TransID = B.TransID " +
                //                    " join " + sBranchName + ".inventorycategories C on B.CategoryNumber = C.CategoryNumber " +
                //                    " join " + sBranchName + "." + CmbSelect.Text.Trim() + " D on B." + CmbSelect.Text.Trim() + " = D.GroupName  " +
                //                    " where Businessdate between '" + DFrom + "' and '" + DTo + "' " + sWhereCondition + " " +
                //                    " group by B." + CmbSelect.Text.Trim() + " " + sOrderByCondition + " ", Cn);

                string cmd = "Select D.GroupName as CategoryNumber,D.GroupName as CategoryName,sum(QTY) as QTY, " +
                                    " concat(round((sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +

                                    " where businessdate between @DFrom and @DTo and transstatus in(2,3,4) and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER, " +
                                    " (sum(qty)/ " +
                                    " (Select sum(qty) from {0}.trans A join {0}.Transdetail B on A.TransID = B.TransID  " +
                                    " where businessdate between @DFrom and @DTo and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ) AS QTYPERTemp, " +
                                    " format(sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount),2) as Total, " +
                                    " sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) as TotalTemp, " +
                                    " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/ " +
                                    " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from  " +
                                    " {0}.Trans A join {0}.Transdetail B on A.Transid=B.Transid " +

                                    " where businessdate between @DFrom and @DTo and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                    " (sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                    " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A  " +
                                    " join {0}.Transdetail B on A.Transid=B.Transid  " +

                                    " where businessdate between @DFrom and @DTo and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ) AS AMTPERTemp " +
                                    " from {0}.Trans A " +
                                    " join {0}.TransDetail B on A.TransID = B.TransID " +
                                    " join {0}.inventorycategories C on B.CategoryNumber = C.CategoryNumber " +
                                    " join {0}." + CmbSelect.Text.Trim() + " D on B." + CmbSelect.Text.Trim() + " = D.GroupName  " +
                                    " where Businessdate between  @DFrom  and @DTo  and transstatus in (2,3,4) and itemstatus = 0 " + sWhereCondition + " " +
                                    " group by B." + CmbSelect.Text.Trim() + " " + sOrderByCondition + " ";

                DataTable Dt = new DataTable();
                string command = string.Format(cmd, sBranchName);

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@DFrom", DFrom),
                     new MySqlParameter("@DTo", DTo),
                     //new MySqlParameter("@TopN", Microsoft.Security.Application.Encoder.HtmlEncode(txtTopN.Text)),
                     new MySqlParameter("@ID", Microsoft.Security.Application.Encoder.HtmlEncode(txtID.Text))

                    };
                Dt = ObjMain.NewGetData_DT(command, parameters);
                grdTopSales.DataSource = Dt;
                grdTopSales.DataBind();
            }
            //lblMsgTotalSelection.Text = "Search Result - Top Sales Between &nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp; And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
            lblMsgTotalSelection.Text = " Search Result - from " + Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text) + "&nbsp;&nbsp; to &nbsp;&nbsp;" + Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text);
            idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
        }

        protected void grdTopSales_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);
            if (e.Row.RowType == DataControlRowType.DataRow)
            {

                // XSS Prevention 

                string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text);
                string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text);

                GridView grdChildTS = (GridView)e.Row.FindControl("grdChildTS");
                string sCategoryNumber = ((DataRowView)e.Row.DataItem)["CategoryNumber"].ToString().Trim();
                grdChildTS.DataSource = LoadChild(Convert.ToString(sCategoryNumber), cd_BranchAlias, StartDT, EndDt);
                grdChildTS.DataBind();


            }
        }


        protected void grdTopSales_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {

            // XSS Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text);
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text);



            if (cmbBranches.Visible == false)
            {
                grdTopSales.PageIndex = e.NewPageIndex;
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                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 )              
                GetTopSales(Session["Branch_HQ"].ToString(), StartDT, EndDt);
            }
            else
            {
                grdTopSales.PageIndex = e.NewPageIndex;
                //Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                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 )                
                GetTopSales(cd_BranchAlias, StartDT, EndDt);
            }
        }

        public DataTable LoadChild(string CategoryNo, string Branch_Name, string D1, string D2)
        {
            sOrderByCondition = "";
            //if (CmbSelect.Text == "AInventory")
            //{
            //  sWhereCondition = " and B.Categorynumber = '" + txtID.Text.Trim() + "'";
            if (cmbTopBy.Text == "Amount")
            {
                if (txtTopN.Text == "0" || txtTopN.Text == "")
                {
                    sOrderByCondition = "order by AmountTemp " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " ";
                }
                else
                {
                    sOrderByCondition = "order by AmountTemp " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                }

            }
            else if (cmbTopBy.Text == "QTY")
            {
                if (txtToDt.Text == "0" || txtTopN.Text == "")
                {
                    sOrderByCondition = "order by Qty  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                }
                else
                {
                    sOrderByCondition = "order by Qty  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                }
            }
            else if (cmbTopBy.Text == "AMTPER")
            {
                if (txtToDt.Text == "0" || txtTopN.Text == "")
                {
                    sOrderByCondition = "order by AMTPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                }
                else
                {
                    sOrderByCondition = "order by AMTPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                }
            }
            else if (cmbTopBy.Text == "QTYPER")
            {
                if (txtToDt.Text == "0" || txtTopN.Text == "")
                {
                    sOrderByCondition = "order by QTYPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + "";
                }
                else
                {
                    sOrderByCondition = "order by QTYPERTemp  " + Microsoft.Security.Application.Encoder.HtmlEncode(cmbOrderBy.Text) + " limit " + txtTopN.Text.Trim() + "";
                }
            }

            if (CmbSelect.Text == "AInventory" || CmbSelect.Text == "Category")
            {
                string sCmd = "Select B.Categorynumber,B.Barcode,Name,InventoryDesc,sum(QTY) as QTY, " +
                                    " concat(round((sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +
                                    " where businessdate between @D1  and @D2  and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER,  " +
                                    " sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +
                                    " where businessdate between @D1  and @D2 and transstatus in (2,3,4) and b.itemstatus = 0 )  AS QTYPERTemp,  " +
                                    " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as Amount, " +
                                    " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as AmountTemp, " +
                                    " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                    " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A " +
                                    " join {0}.Transdetail B on A.Transid=B.Transid  where businessdate between  @D1  and @D2 and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                    " sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                    " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A " +
                                    " join {0}.Transdetail B on A.Transid=B.Transid  where businessdate between  @D1  and @D2 and transstatus in (2,3,4) and b.itemstatus = 0) AS AMTPERTemp, " +
                                    " sum(B.ManualDisc+B.MemberDisc+TotalDisc+TotalMemberDisc) as DISC " +
                                    " from {0}.trans A  " +
                                    " join {0}.transdetail B  " +
                                    " on A.TransID = B.TransID " +
                                    " where A.businessdate between @D1  and @D2 and CategoryNumber = @CategoryNo  and transstatus in (2,3,4) and itemstatus = 0 " +
                                    " group by Barcode " + sOrderByCondition + " ";


                // Preventing SQL Injections
                // Parameterized Approach 


                DataTable Dt1 = new DataTable();
                string command = string.Format(sCmd, Branch_Name);

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@D1", D1),
                     new MySqlParameter("@D2", D2),
                     //new MySqlParameter("@TopN", Microsoft.Security.Application.Encoder.HtmlEncode(txtTopN.Text)),
                     new MySqlParameter("@CategoryNo", CategoryNo)

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

                return Dt1;
            }
            else//Group
            {
                MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
                Cn.Open();
                string sCmd = "Select B.Categorynumber,B.Barcode,Name,InventoryDesc,sum(QTY) as QTY, " +
                                     " concat(round((sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +
                                     " where businessdate between  @D1 and @D2  and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER,  " +
                                     " sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +
                                     " where businessdate between @D1 and @D2 and transstatus in (2,3,4) and b.itemstatus = 0 )  AS QTYPERTemp,  " +
                                     " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as Amount, " +
                                     " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as AmountTemp, " +
                                     " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                     " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A " +
                                     " join {0}.Transdetail B on A.Transid=B.Transid  where businessdate between  @D1 and @D2 and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                     " sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                     " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A " +
                                     " join {0}.Transdetail B on A.Transid=B.Transid  where businessdate between  @D1 and @D2 and transstatus in (2,3,4) and b.itemstatus = 0) AS AMTPERTemp, " +
                                     " sum(B.ManualDisc+B.MemberDisc+TotalDisc+TotalMemberDisc) as DISC " +
                                     " from {0}.trans A  " +
                                     " join {0}.transdetail B  " +
                                     " on A.TransID = B.TransID " +
                                     " where A.businessdate between @D1 and @D2 and " + Microsoft.Security.Application.Encoder.HtmlEncode(CmbSelect.Text.Trim()) + " = @CategoryNo and transstatus in (2,3,4) and itemstatus = 0 " +
                                     " group by Barcode " + sOrderByCondition + " ";


                DataTable Dt1 = new DataTable();
                string command = string.Format(sCmd, Branch_Name);
                MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1", D1),
                     new MySqlParameter("@D2", D2),
                     //new MySqlParameter("@TopN", Microsoft.Security.Application.Encoder.HtmlEncode(txtTopN.Text)),
                     new MySqlParameter("@CategoryNo", Microsoft.Security.Application.Encoder.HtmlEncode(CategoryNo))

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


        }

        //protected void grdTopSales_RowDataBound(object sender, GridViewRowEventArgs e)
        //{
        //}
        protected void btnExcelHeader_Click(object sender, EventArgs e)
        {
            // XSS Attack Prevention 

            // Prevents SQL Injection 
            // Parameterized Approach 

            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text);
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text);


            if (grdTopSales.Rows.Count > 0)
            {


                sOrderByCondition = "";
                if (cmbTopBy.Text == "Amount")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by TotalTemp " + cmbOrderBy.Text + " ";
                    }
                    else
                    {
                        sOrderByCondition = "order by TotalTemp " + cmbOrderBy.Text + " limit " + txtTopN.Text.Trim() + "";
                    }

                }
                else if (cmbTopBy.Text == "QTY")
                {
                    if (txtToDt.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by QTY  " + cmbOrderBy.Text + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by QTY  " + cmbOrderBy.Text + " limit " + txtTopN.Text.Trim() + "";
                    }
                }
                else if (cmbTopBy.Text == "AMTPER")
                {
                    if (txtToDt.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by AMTPERTemp  " + cmbOrderBy.Text + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by AMTPERTemp  " + cmbOrderBy.Text + " limit " + txtTopN.Text.Trim() + "";
                    }
                }
                else if (cmbTopBy.Text == "QTYPER")
                {
                    if (txtToDt.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by QTYPERTemp  " + cmbOrderBy.Text + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by QTYPERTemp  " + cmbOrderBy.Text + " limit " + txtTopN.Text.Trim() + "";
                    }
                }

                //}
                //else
                //{
                //}

                //GridView grdChildTS = (GridView)e.Row.FindControl("grdChildTS");
                //string CategoryNo = "2";


                // for (int i = 0; i < grdTopSales.Rows.Count; i++)
                if (CmbSelect.Text == "AInventory" || CmbSelect.Text == "Category")
                {

                    foreach (GridViewRow gr in grdTopSales.Rows)
                    {
                        string sCategoryNumber = gr.Cells[1].Text;

                        if ((DataTable)Session["ResultDTExcel"] == null)
                        {
                            DTExcel = (DataTable)Session["ResultDTExcel"];
                        }

                        cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cmbBranches.SelectedItem.Text.Trim());
                     
                        string sCmd = "Select B.Categorynumber,C.CategoryName,B.Barcode,Name,InventoryDesc,sum(QTY) as QTY, " +
                                            " concat(round((sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +
                                            " where businessdate between @D1 and @D2 and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER,  " +
                                            " (sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +
                                            " where businessdate between @D1 and @D2 and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ) AS QTYPERTemp,  " +
                                            " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as Total, " +
                                            " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as TotalTemp, " +
                                            " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                            " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A " +
                                            " join {0}.Transdetail B on A.Transid=B.Transid  " +
                                            " where businessdate between  @D1 and @D2 " +
                                            " and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                            " (sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                            " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A " +
                                            " join {0}.Transdetail B on A.Transid=B.Transid  " +
                                            " where businessdate between  @D1 and @D2 " +
                                            " and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ) AS AMTPERTemp, " +
                                            " format(sum(B.ManualDisc+B.MemberDisc+TotalDisc+TotalMemberDisc),2) as DISC " +
                                            " from {0}.trans A  " +
                                            " join {0}.transdetail B  " +
                                            " on A.TransID = B.TransID " +
                                            " join {0}.inventoryCategories C " +
                                            " on B.CategoryNumber = C.CategoryNumber " +
                                            " where A.businessdate between @D1 and @D2 and B.CategoryNumber = @CategoryNo " +
                                            " and transstatus in (2,3,4) and itemstatus = 0 " +
                                            " group by Barcode " + sOrderByCondition + " " ;

                                        string command = string.Format(sCmd, cd_BranchAlias);
                                        MySqlParameter[] parameters =
                                           {
                                          new MySqlParameter("@D1",  Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text.Trim())),
                                          new MySqlParameter("@D2", Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text.Trim())),
                                          new MySqlParameter("@TopN", Microsoft.Security.Application.Encoder.HtmlEncode(txtTopN.Text)),
                                          new MySqlParameter("@CategoryNo", Microsoft.Security.Application.Encoder.HtmlEncode(sCategoryNumber))

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



                      
                        //if (DTExcel == null)
                        //{
                        //    DTExcel = DTExcelTemp;
                        //    Session["ResultDTExcel"] = DTExcel;

                        //}
                        //else
                        //{
                        //    DTExcel.Merge(DTExcelTemp);
                        //    Session["ResultDTExcel"] = DTExcel;
                        //}

                    }
                }
                else//Group
                {
                    foreach (GridViewRow gr in grdTopSales.Rows)
                    {
                        string sCategoryNumber = gr.Cells[1].Text;

                        if ((DataTable)Session["ResultDTExcel"] == null)
                        {
                            DTExcel = (DataTable)Session["ResultDTExcel"];
                        }
                        cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cmbBranches.SelectedItem.Text.Trim());
                       



                        string sCmd ="Select B.Group1,B.Barcode,Name,InventoryDesc,sum(QTY) as QTY, " +
                                            " concat(round((sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +
                                            //" where businessdate between '" + txtFromDt.Text.Trim() + "' and '" + txtToDt.Text.Trim() + "' and transstatus = 2 and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER,  " +
                                            " where businessdate between  @D1 and @D2 and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER,  " +
                                            " sum(qty)/(Select sum(qty) from {0}.Trans A join {0}.Transdetail B on A.TransID = B.TransID " +
                                            " where businessdate between  @D1 and @D2 and transstatus = 2 and b.itemstatus = 0 )  AS QTYPERTemp,  " +
                                            " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as Total, " +
                                            " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as TotalTemp, " +
                                            " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                            " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A " +
                                            //" join " + cd_BranchAlias + ".Transdetail B on A.Transid=B.Transid  where businessdate between  '" + txtFromDt.Text.Trim() + "' and '" + txtToDt.Text.Trim() + "' and transstatus = 2 and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                            " join {0}.Transdetail B on A.Transid=B.Transid  where businessdate between   @D1 and @D2 and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                              " sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                            " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   {0}.Trans A " +
                                            " join {0}.Transdetail B on A.Transid=B.Transid  where businessdate between   @D1 and @D2 and transstatus = 2 and b.itemstatus = 0) AS AMTPERTemp, " +
                                            " sum(B.ManualDisc+B.MemberDisc+TotalDisc+TotalMemberDisc) as DISC " +
                                            " from {0}.trans A  " +
                                            " join {0}.transdetail B  " +
                                            " on A.TransID = B.TransID " +
                                            " where A.businessdate between  @D1 and @D2 and " + CmbSelect.Text.Trim() + " = @CategoryNo and transstatus in (2,3,4) and itemstatus = 0 " +
                                            " group by Barcode " + sOrderByCondition + " " ;


                        string command = string.Format(sCmd, cd_BranchAlias);
                        MySqlParameter[] parameters =
                           {
                                          new MySqlParameter("@D1",  Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text.Trim())),
                                          new MySqlParameter("@D2", Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text.Trim())),
                                          new MySqlParameter("@TopN", Microsoft.Security.Application.Encoder.HtmlEncode(txtTopN.Text)),
                                          new MySqlParameter("@CategoryNo", Microsoft.Security.Application.Encoder.HtmlEncode(sCategoryNumber))

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





                        //DTExcelTemp
                        //if (DTExcel == null)
                        //{
                        //    DTExcel = DTExcelTemp;
                        //    Session["ResultDTExcel"] = DTExcel;

                        //}
                        //else
                        //{
                        //    DTExcel.Merge(DTExcelTemp);
                        //    Session["ResultDTExcel"] = DTExcel;
                        //}
                    }

                }


                DTExcelTemp.Columns.Remove("QTYPERTemp");
                DTExcelTemp.Columns.Remove("TotalTemp");
                DTExcelTemp.Columns.Remove("AMTPERTemp");
                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                //GridView1.DataSource = Session["ResultDTExcel"];
                GridView1.DataSource = DTExcelTemp;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition",
                    "attachment;filename=TopSalesDetails.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
            {

                Page.ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('Please generate report first.');", true);
            }


        }

        protected void btnExcelDetails_Click(object sender, EventArgs e)
        {
            // XSS Prevention 

            // Prevent SQL Injections 
            // Parameterized Approaches 

            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text);
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text);

            if (grdTopSales.Rows.Count > 0)
            {


                sOrderByCondition = "";
                if (cmbTopBy.Text == "Amount")
                {
                    if (txtTopN.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by TotalTemp " + cmbOrderBy.Text + " ";
                    }
                    else
                    {
                        sOrderByCondition = "order by TotalTemp " + cmbOrderBy.Text + " limit " + txtTopN.Text + "  ";
                    }

                }
                else if (cmbTopBy.Text == "QTY")
                {
                    if (txtToDt.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by QTY  " + cmbOrderBy.Text + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by QTY  " + cmbOrderBy.Text + " limit " + txtTopN.Text + "  ";
                    }
                }
                else if (cmbTopBy.Text == "AMTPER")
                {
                    if (txtToDt.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by AMTPERTemp  " + cmbOrderBy.Text + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by AMTPERTemp  " + cmbOrderBy.Text + " limit " + txtTopN.Text + "  ";
                    }
                }
                else if (cmbTopBy.Text == "QTYPER")
                {
                    if (txtToDt.Text == "0" || txtTopN.Text == "")
                    {
                        sOrderByCondition = "order by QTYPERTemp  " + cmbOrderBy.Text + "";
                    }
                    else
                    {
                        sOrderByCondition = "order by QTYPERTemp  " + cmbOrderBy.Text + " limit " + txtTopN.Text + "  ";
                    }
                }

                //}
                //else
                //{
                //}

                //GridView grdChildTS = (GridView)e.Row.FindControl("grdChildTS");
                //string CategoryNo = "2";


                // for (int i = 0; i < grdTopSales.Rows.Count; i++)
                if (CmbSelect.Text == "AInventory" || CmbSelect.Text == "Category")
                {

                    foreach (GridViewRow gr in grdTopSales.Rows)
                    {
                        string sCategoryNumber = gr.Cells[1].Text;

                        if ((DataTable)Session["ResultDTExcel"] == null)
                        {
                            DTExcel = (DataTable)Session["ResultDTExcel"];
                        }

                        cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cmbBranches.SelectedItem.Text.Trim());
                        MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
                        Cn.Open();
                        MySqlCommand sCmd = new MySqlCommand("Select B.Categorynumber,C.CategoryName,B.Barcode,Name,InventoryDesc,sum(QTY) as QTY, " +
                                            " concat(round((sum(qty)/(Select sum(qty) from " + cd_BranchAlias + ".Trans A join " + cd_BranchAlias + ".Transdetail B on A.TransID = B.TransID " +
                                            " where businessdate between '" + StartDT + "' and '" + EndDt + "' and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER,  " +
                                            " (sum(qty)/(Select sum(qty) from " + cd_BranchAlias + ".Trans A join " + cd_BranchAlias + ".Transdetail B on A.TransID = B.TransID " +
                                            " where businessdate between '" + StartDT + "' and '" + EndDt + "' and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ) AS QTYPERTemp,  " +
                                            " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as Total, " +
                                            " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as TotalTemp, " +
                                            " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                            " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   " + cd_BranchAlias + ".Trans A " +
                                            " join " + cd_BranchAlias + ".Transdetail B on A.Transid=B.Transid  " +
                                            " where businessdate between  '" + StartDT + "' and '" + EndDt + "' " +
                                            " and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                            " (sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                            " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   " + cd_BranchAlias + ".Trans A " +
                                            " join " + cd_BranchAlias + ".Transdetail B on A.Transid=B.Transid  " +
                                            " where businessdate between  '" + StartDT + "' and '" + EndDt + "' " +
                                            " and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ) AS AMTPERTemp, " +
                                            " format(sum(B.ManualDisc+B.MemberDisc+TotalDisc+TotalMemberDisc),2) as DISC " +
                                            " from " + cd_BranchAlias + ".trans A  " +
                                            " join " + cd_BranchAlias + ".transdetail B  " +
                                            " on A.TransID = B.TransID " +
                                            " join " + cd_BranchAlias + " .inventoryCategories C " +
                                            " on B.CategoryNumber = C.CategoryNumber " +
                                            " where A.businessdate between '" + StartDT + "' and '" + EndDt + "' and B.CategoryNumber = '" + sCategoryNumber + "' " +
                                            " and transstatus in (2,3,4) and itemstatus = 0 " +
                                            " group by Barcode " + sOrderByCondition + " ", Cn);
                        MySqlDataAdapter Ada = new MySqlDataAdapter(sCmd);
                        ds = new DataSet();
                        Ada.Fill(DTExcelTemp);
                        Cn.Close();


                        //if (DTExcel == null)
                        //{
                        //    DTExcel = DTExcelTemp;
                        //    Session["ResultDTExcel"] = DTExcel;

                        //}
                        //else
                        //{
                        //    DTExcel.Merge(DTExcelTemp);
                        //    Session["ResultDTExcel"] = DTExcel;
                        //}

                    }
                }
                else//Group
                {
                    foreach (GridViewRow gr in grdTopSales.Rows)
                    {
                        string sCategoryNumber = gr.Cells[1].Text;

                        if ((DataTable)Session["ResultDTExcel"] == null)
                        {
                            DTExcel = (DataTable)Session["ResultDTExcel"];
                        }
                        cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cmbBranches.SelectedItem.Text.Trim());
                        MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
                        Cn.Open();
                        MySqlCommand sCmd = new MySqlCommand("Select B.Group1,B.Barcode,Name,InventoryDesc,sum(QTY) as QTY, " +
                                            " concat(round((sum(qty)/(Select sum(qty) from " + cd_BranchAlias + ".Trans A join " + cd_BranchAlias + ".Transdetail B on A.TransID = B.TransID " +
                                            //" where businessdate between '" + txtFromDt.Text.Trim() + "' and '" + txtToDt.Text.Trim() + "' and transstatus = 2 and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER,  " +
                                            " where businessdate between '" + StartDT + "' and '" + EndDt + "' and transstatus in (2,3,4) and b.itemstatus = 0 ) * 100 ),2),'%') AS QTYPER,  " +
                                            " sum(qty)/(Select sum(qty) from " + cd_BranchAlias + ".Trans A join " + cd_BranchAlias + ".Transdetail B on A.TransID = B.TransID " +
                                            " where businessdate between '" + StartDT + "' and '" + EndDt + "' and transstatus = 2 and b.itemstatus = 0 )  AS QTYPERTemp,  " +
                                            " format(sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount),2) as Total, " +
                                            " sum(B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6+B.TaxableAmount) as TotalTemp, " +
                                            " concat(round((sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                            " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   " + cd_BranchAlias + ".Trans A " +
                                            //" join " + cd_BranchAlias + ".Transdetail B on A.Transid=B.Transid  where businessdate between  '" + txtFromDt.Text.Trim() + "' and '" + txtToDt.Text.Trim() + "' and transstatus = 2 and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                            " join " + cd_BranchAlias + ".Transdetail B on A.Transid=B.Transid  where businessdate between  '" + StartDT + "' and '" + EndDt + "' and transstatus in (2,3,4) and b.itemstatus = 0) * 100 ),2),'%') AS AMTPER, " +
                                              " sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount)/  " +
                                            " (Select sum(B.taxamount1+B.taxamount2+B.taxamount3+B.taxamount4+B.taxamount5+B.taxableamount) from   " + cd_BranchAlias + ".Trans A " +
                                            " join " + cd_BranchAlias + ".Transdetail B on A.Transid=B.Transid  where businessdate between  '" + StartDT + "' and '" + EndDt + "' and transstatus = 2 and b.itemstatus = 0) AS AMTPERTemp, " +
                                            " sum(B.ManualDisc+B.MemberDisc+TotalDisc+TotalMemberDisc) as DISC " +
                                            " from " + cd_BranchAlias + ".trans A  " +
                                            " join " + cd_BranchAlias + ".transdetail B  " +
                                            " on A.TransID = B.TransID " +
                                            " where A.businessdate between '" + StartDT + "' and '" + EndDt + "' and " + CmbSelect.Text.Trim() + " = '" + sCategoryNumber + "' and transstatus in (2,3,4) and itemstatus = 0 " +
                                            " group by Barcode " + sOrderByCondition + " ", Cn);
                        MySqlDataAdapter Ada = new MySqlDataAdapter(sCmd);
                        ds = new DataSet();
                        Ada.Fill(DTExcelTemp);
                        Cn.Close();


                        //if (DTExcel == null)
                        //{
                        //    DTExcel = DTExcelTemp;
                        //    Session["ResultDTExcel"] = DTExcel;

                        //}
                        //else
                        //{
                        //    DTExcel.Merge(DTExcelTemp);
                        //    Session["ResultDTExcel"] = DTExcel;
                        //}
                    }

                }


                DTExcelTemp.Columns.Remove("QTYPERTemp");
                DTExcelTemp.Columns.Remove("TotalTemp");
                DTExcelTemp.Columns.Remove("AMTPERTemp");
                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                //GridView1.DataSource = Session["ResultDTExcel"];
                GridView1.DataSource = DTExcelTemp;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition",
                    "attachment;filename=TopSalesDetails.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
            {

                Page.ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('Please generate report first.');", true);
            }



        }

    }



}