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

namespace SUNWEB
{
    public partial class ProductSale_Light : 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_ItemSales_light = 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 = "76";    // This is for   ProductSale


            if (!IsPostBack)
            {
                txtStartDt.Text = Curr_DateOnly;
                txtEndDt.Text = Curr_DateOnly;
            }

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

                    UpdateActiveBrach_Light();
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();
                    }
                }
            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin_light();
                }
                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                //LoadInventoryListGrid(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin_light();
                    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_light"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    // lblMsgTotalSelection.Text = "Item Sale  Between" + "&nbsp;&nbsp;&nbsp;&nbsp;" + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  and  &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                    if (!IsPostBack)
                    {
                        if (Session["Isdowld_Acess"].ToString() == "N")
                        {
                            // btnExcel.Enabled = false;
                            Response.Write(@"<script language='javascript'>alert('No Access To download.')</script>");
                        }
                        else
                        {
                            btnGetReports.Visible = true;

                            if (cmbBranches.Visible == false)
                            {
                                //  BindGridview(Session["Branch_HQ"].ToString(), txtStartDt.Text, txtEndDt.Text);
                            }

                        }


                    }

                }

            }
            else
            {

            }




        }


        public void ActiveCall()
        {
            UpdateActiveBrach_Light();

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


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

            if (ISHQ != "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch_Light(Session["Branch_HQ"].ToString());
                UPDATEBRANCH(ActiveCallvalue);
            }

            cd_BranchAlias = ActiveCallvalue;
            Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cd_BranchAlias);
            Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
            cmbBranches.Items.FindByText(Curr_BranchFullName).Selected = true;
            Branchidval = ObjUserBL.GetBranchIDbyName(ActiveCallvalue, ActiveCallvalue);
            DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            if (DT.Rows.Count > 0)
            {
                Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();

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

                System.Threading.Thread.Sleep(2000);

                // call the methods 
               // LoadTaxanalysisData(Cmb_BranchName, txtFromDt.Text, txtToDt.Text);  


            }

        }

        public void ActiveCall_on_BranchSelection()
        {
            BranchUpdate_Light(cmbBranches.SelectedItem.Text);

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


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

            if (ISHQ != "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch_Light(Session["Branch_HQ"].ToString());
                UPDATEBRANCH(ActiveCallvalue);
            }

            cd_BranchAlias = ActiveCallvalue;
            Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cd_BranchAlias);
            Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
            cmbBranches.Items.FindByText(Curr_BranchFullName).Selected = true;
            Branchidval = ObjUserBL.GetBranchIDbyName(ActiveCallvalue, ActiveCallvalue);
            DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            if (DT.Rows.Count > 0)
            {
                Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();

                Session["Page_Acess"] = Page_Acess;
                Session["Read_Acess"] = Read_Acess;
                Session["Write_Acess"] = Write_Acess;
                Session["Isdowld_Acess"] = Isdowld_Acess;
            }
            Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + cmbBranches.SelectedItem.Value + " and cd_branchalias='" + ActiveCallvalue + "';");
            RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
            DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);
            if (DtLicense.Rows.Count > 0)
            {
                L_ItemSales_light = DtLicense.Rows[0]["PRODUCTSALE_LIGHT"].ToString();
                Session["L_TaxAnalysis_light"] = L_ItemSales_light;
            }
            if (Session["L_TaxAnalysis_light"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {
                    
                System.Threading.Thread.Sleep(2000);
                // call the methods 
                LoadProductSale_Header(Cmb_BranchName, txtStartDt.Text, txtEndDt.Text);


            }

        }


        public void UPDATEBRANCH(string ActiveCallvalue)
        {
            Useridvalue = Session["UserId"].ToString();
            QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
            S = ObjMain.ExecuteNonQuery(QueryCurr_Select);
        }
        public void UpdateActiveBrach_Light()
        {
            string R_current_Activecellvalue = ObjMain.ExecuteScalar("select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ");
            int _upt = ObjMain.ExecuteNonQuery("UPDATE  portaldb.usermaster SET ActiveValue='' WHERE Userid=" + Session["UserId"].ToString() + "");
            ActiveCallvalue = ObjSel.GetFirstBranch_Light(Session["Branch_HQ"].ToString());
            int _upt_new = ObjMain.ExecuteNonQuery("UPDATE  portaldb.usermaster SET ActiveValue='" + ActiveCallvalue + "' WHERE Userid=" + Session["UserId"].ToString() + "");
        }

        /// <summary>
        ///  This function for get branchname on branch selection 
        /// </summary>
        public void BranchUpdate_Light(string branchname)
        {
            string R_current_Activecellvalue = ObjMain.ExecuteScalar("select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ");
            int _upt = ObjMain.ExecuteNonQuery("UPDATE  portaldb.usermaster SET ActiveValue='' WHERE Userid=" + Session["UserId"].ToString() + "");
            ActiveCallvalue = ObjSel.GetBranch_Branchname_Light(Session["Branch_HQ"].ToString(), branchname);
            int _upt_new = ObjMain.ExecuteNonQuery("UPDATE  portaldb.usermaster SET ActiveValue='" + ActiveCallvalue + "' WHERE Userid=" + Session["UserId"].ToString() + "");
        }

        public void LoadBranchName4SuperAdmin_light()
        {
            DtSuperAdmin = ObjUserBL.GetBranchOnSuperAdmin8_Light(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");
            }
        }




        protected void cmbSelectby_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void btnGetReports_Click(object sender, EventArgs e)
        {

            if (txtStartDt.Text != "" && txtEndDt.Text != "" && cmbBranches.SelectedIndex != 0)
            {

                string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text);
                string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text);
                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 )


                


                if (cmbSelectby.Text == "ALLInventory")
                {
                    LoadProductSale_Header(Cmb_BranchName, StartDT, EndDt);
                }
                else if (cmbSelectby.Text == "Category")
                {
                    // Search By Category
                    if (txtSearchText.Text != "")
                    {
                        LoadProductSale_Header_ByCategoryName(Cmb_BranchName, StartDT, EndDt, txtSearchText.Text.ToString().Trim());
                    }
                                        
                }
                else if (cmbSelectby.Text == "SInventory")
                {
                    // Search By Single Inventory
                    // Get CategoryNumber using  InventoryName .
                    string _rCategorynumber = ObjMain.ExecuteScalar("select CategoryNumber from " + Cmb_BranchName + " .ProductSaleSummary_detail_light  where barcode='" + txtSearchText.Text.Trim().ToString() + "' ");


                     LoadProductSale_Header_ByCategoryName5(Cmb_BranchName, StartDT, EndDt, _rCategorynumber);



                }

            }




        }

        protected void gvProductsale_RowDataBound(object sender, GridViewRowEventArgs e)
        {

            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 )

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

            }


            //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)["CategoryNumber"].ToString().Trim();
                gv.DataSource = LoadProductSale_Child(cd_BranchAlias, StartDT, EndDt, Convert.ToString(sCategoryNumber));
                gv.DataBind();
            }
            if (e.Row.RowType == DataControlRowType.Footer)
            {

            }


        }

        protected void gvProductsale_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            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 )

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

            }

            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 )              
                LoadProductSale_Header(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 )                
                LoadProductSale_Header(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            }

        }
        public void LoadProductSale_Header(string db ,string D1,string D2)
        {
            System.Threading.Thread.Sleep(1000);
            string Qry1 = " SELECT  ID,BusinessDate, CategoryNumber, '' as PriceType,'' as space1,'' as space2 ,CategoryName,sum(Qty) as Qty,sum(GrossAmt) as GrossAmt," 
            + "sum(FOCAMT) as FOCAMT,sum(ManualDisc) as ManualDisc, sum(TotalDisc) as TotalDisc ,  "
            + "sum(ActualPSDiscAmount) as ActualPSDiscAmount ,sum(TotalMemberDisc) as TotalMemberDisc ,sum(DiscAmt) as DiscAmt,sum(DiscQty) as DiscQty, sum(SalesAmt) as SalesAmt, " 
            + "sum(TaxAmount1) as TaxAmount1, sum(GST) as GST, sum(TaxAmount3) as TaxAmount3, sum(TaxAmount4) as TaxAmount4 , "
            + "sum(TaxAmount5) as TaxAmount5,sum(TaxAmount6) as TaxAmount6, sum(TaxableAmount) as TaxableAmount,TaxFlag,sum(PL) as PL, LastUpdate "
            + " FROM  {0}.ProductSaleSummary_light where date(businessdate) between @D1 and @D2  group by categorynumber order by CategoryName ,businessdate;";

            // Prevent the SQL Injections
            // Parameterized Approach 

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

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


        }

        public void LoadProductSale_Header_ByCategoryName(string db, string D1, string D2,string categoryName)
        {
            System.Threading.Thread.Sleep(1000);
            string Qry1 = " SELECT  ID,BusinessDate, CategoryNumber, '' as PriceType,'' as space1,'' as space2 ,CategoryName,SUM(Qty) AS Qty, SUM(GrossAmt) AS GrossAmt ," 
            + " SUM(FOCAMT) AS FOCAMT ,SUM(ManualDisc) AS ManualDisc ,SUM(TotalDisc) AS TotalDisc,  "
            + " SUM(ActualPSDiscAmount) AS ActualPSDiscAmount, SUM(TotalMemberDisc) AS TotalMemberDisc,SUM(DiscAmt) AS  DiscAmt,SUM(DiscQty) AS DiscQty,SUM( SalesAmt) AS SalesAmt, " 
            + " SUM(TaxAmount1) AS TaxAmount1,SUM(GST) AS GST,SUM(TaxAmount3) AS TaxAmount3,SUM(TaxAmount4) AS TaxAmount4 , "
            + " SUM(TaxAmount5) AS TaxAmount5,SUM(TaxAmount6) AS TaxAmount6,SUM(TaxableAmount) AS TaxableAmount, TaxFlag,SUM(PL) AS PL, LastUpdate "
            + " FROM  {0}.ProductSaleSummary_light where date(businessdate) between @D1 and @D2  and Categoryname=@Categoryname "
            + " group by categorynumber  order by CategoryName ;";

            // Prevent the SQL Injections
            // Parameterized Approach 

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

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


        }

        public void LoadProductSale_Header_ByCategoryName5(string db, string D1, string D2, string CategoryNumber)
        {
            System.Threading.Thread.Sleep(1000);
            string Qry1 = " SELECT  ID,BusinessDate, CategoryNumber, '' as PriceType,'' as space1,'' as space2 ,CategoryName,sum(Qty) as Qty,sum(GrossAmt) as GrossAmt," 
            + " sum(FOCAMT) as FOCAMT,sum(ManualDisc) as ManualDisc,sum(TotalDisc) as TotalDisc,  "
            + " sum(ActualPSDiscAmount) as ActualPSDiscAmount, sum(TotalMemberDisc) as TotalMemberDisc, sum(DiscAmt) as DiscAmt,sum(DiscQty) as DiscQty," 
            + " sum(SalesAmt) as SalesAmt,sum(TaxAmount1) as TaxAmount1,sum(GST) as GST,sum(TaxAmount3) as TaxAmount3,sum(TaxAmount4) as TaxAmount4, "
            + " sum(TaxAmount5) as TaxAmount5,sum(TaxAmount6) as TaxAmount6,sum(TaxableAmount) as TaxableAmount,TaxFlag,sum(PL) as PL, LastUpdate "
            + " FROM  {0}.ProductSaleSummary_light where date(businessdate) between @D1 and @D2  and CategoryNumber=@CategoryNumber "
            + " group by categorynumber  order by CategoryName  ;";

            // Prevent the SQL Injections
            // Parameterized Approach 

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

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


        }

        public  DataTable  LoadProductSale_Child(string db, string D1,string D2,string Categorynumber)
        {

            // string Qry1 = " select * from {0}.ProductSaleSummary_detail_light  where date(businessdate) between @D1 and  @D2  and Categorynumber=@Categorynumber; ";
            string Qry1 = " select* from {0}.ProductSaleSummary_detail_light" 
               + " where date(businessdate) between @D1 and @D2  " 
               + " and Categorynumber =@Categorynumber group by barcode order by InventoryName; ";
            

            // Prevent the SQL Injections
            // Parameterized Approach 

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

            DT = ObjMain.NewGetData_DT(command, parameters);


            return DT;

        }

        public void LoadProductSale_Header_Excel(string D1, string D2)
        {
            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 )

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

            }

            System.Threading.Thread.Sleep(1000);
            string Qry1 = " SELECT  ID,DATE_FORMAT(BusinessDate, '%Y-%m-%d') AS BusinessDate, CategoryNumber, '' as PriceType,'' as space1,'' as space2 ,CategoryName, Qty, GrossAmt, FOCAMT, ManualDisc, TotalDisc,  "
            + " ActualPSDiscAmount, TotalMemberDisc, DiscAmt, DiscQty, SalesAmt, TaxAmount1, GST, TaxAmount3, TaxAmount4, "
            + " TaxAmount5, TaxAmount6, TaxableAmount, TaxFlag, PL, LastUpdate "
            + " FROM  {0}.ProductSaleSummary_light where date(businessdate) between @D1 and @D2  group by businessdate,categorynumber  order by CategoryName ;";

            // Prevent the SQL Injections
            // Parameterized Approach 

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

            DT = ObjMain.NewGetData_DT(command, parameters);


            // --- Export to Excel ---
            GridView gv = new GridView();
            gv.DataSource = DT;
            gv.DataBind();

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=ProductSalesExport.xls");
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

            using (StringWriter sw = new StringWriter())
            using (HtmlTextWriter hw = new HtmlTextWriter(sw))
            {
                gv.RenderControl(hw);
                string style = "<style> td { mso-number-format:\\@; } </style>";
                HttpContext.Current.Response.Write(style);
                HttpContext.Current.Response.Output.Write(sw.ToString());
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();
            }

        }

        public void LoadProductSale_Detail_Excel(string D1,string D2)
        {

            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 )

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

            }


            string Qry1 = " select* from {0}.ProductSaleSummary_detail_light"
                        + "  where date(businessdate) between @D1 and @D2  "
                        + "  group by barcode order by InventoryName,businessdate ; ";


            // Prevent the SQL Injections
            // Parameterized Approach 

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

            DT = ObjMain.NewGetData_DT(command, parameters);


            // --- Export to Excel ---
            GridView gv = new GridView();
            gv.DataSource = DT;
            gv.DataBind();

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=ProductSalesDetailExport.xls");
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

            using (StringWriter sw = new StringWriter())
            using (HtmlTextWriter hw = new HtmlTextWriter(sw))
            {
                gv.RenderControl(hw);
                string style = "<style> td { mso-number-format:\\@; } </style>";
                HttpContext.Current.Response.Write(style);
                HttpContext.Current.Response.Output.Write(sw.ToString());
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();
            }



        }

        protected void btnExcel_Click(object sender, EventArgs e)
        {
            LoadProductSale_Header_Excel(txtStartDt.Text, txtEndDt.Text);
        }

        protected void btnExcel2_Click(object sender, EventArgs e)
        {
            LoadProductSale_Detail_Excel(txtStartDt.Text, txtEndDt.Text);
        }

        protected void btnExcel3_Click(object sender, EventArgs e)
        {

        }

       

        protected void btnApplySearch_Click(object sender, EventArgs e)
        {

        }
    }
}