using AjaxControlToolkit.HtmlEditor.ToolbarButtons;
using CLS_BL;
using CLS_DL;
using CLS_PL;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using MySql.Data.MySqlClient;
using MySqlX.XDevAPI.Relational;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.EnterpriseServices;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices.ComTypes;
using System.Text;
using System.util.collections;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace SUNWEB
{
    public partial class SummaryReport_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;
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        DataSet ds = new DataSet();
        MYSQLDB ObjMain = new MYSQLDB();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable DtSuperAdmin = new DataTable();
        DataTable DtMultiBranch = new DataTable();
        DataTable DtNormalUser = new DataTable();
        DataTable DTProductSale = new DataTable();
        MySqlCommand Cmdv = new MySqlCommand();
        // User Variables 
        string UserId = string.Empty;
        string UserName = string.Empty;
        string PortalTypeId = string.Empty;
        string cd_Id = string.Empty;
        string Datapath = string.Empty;
        string cd_BranchAlias = string.Empty;
        string IsMultiBranch = string.Empty;
        string IsSuperAdmin = string.Empty;
        string ActiveCallvalue = string.Empty;
        // License Variables ..
        string PID = string.Empty;
        string Page_Acess = string.Empty;
        string Read_Acess = string.Empty;
        string Write_Acess = string.Empty;
        string Isdowld_Acess = string.Empty;
        string Rcd_id = string.Empty;
        string RPortalid = string.Empty;
        string Useridvalue = string.Empty;
        string QueryCurr_Select = string.Empty;
        string L_MaxUserCnt = string.Empty;
        StringBuilder str = new StringBuilder();

        string L_SummarySales_light = string.Empty;
        string Branchidval = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string BranchAlias = string.Empty;
        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;
        DataSet DS = new DataSet();

        string Vw_Chk = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {

            PID = "74";    // This is for SummaryReport_light


            if (!IsPostBack)
            {
                txtStartDt.Text = Curr_DateOnly;
                txtEndDt.Text = Curr_DateOnly;
            }
            MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);
            lblLinechartStdt.Text = MonthstartDt.ToString("yyyy-MM-dd");
            lblLineChartEndDt.Text = Curr_DateOnly;


            int TH = System.DateTime.Now.Hour;
            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_SummarySales_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 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() + "");
        }
        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_SummarySales_light = DtLicense.Rows[0]["SUMMARYREPORT_LIGHT"].ToString();
                Session["L_SummarySales_light"] = L_SummarySales_light;
            }
            if (Session["L_SummarySales_light"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {

                System.Threading.Thread.Sleep(2000);
                LoadSummaryReport(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
             
                CreateLinechardata(cd_BranchAlias,lblLinechartStdt.Text, Curr_DateOnly);

            }

        }

        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 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_SummarySales_light = DtLicense.Rows[0]["TAXANALYSIS_LIGHT"].ToString();
                Session["L_SummarySales_light"] = L_SummarySales_light;
            }
            if (Session["L_SummarySales_light"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {

                

                // CALL METHODS
                
                LoadSummaryReport(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);

                CreateLinechardata(cd_BranchAlias, lblLinechartStdt.Text, Curr_DateOnly);



            }

        }


        public void LoadBranchName()
        {
            DtNormalUser = ObjUserBL.GetBranchOnLoginValues8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtNormalUser.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtNormalUser;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        public void LoadBranchName4SuperAdmin()
        {
            DtSuperAdmin = ObjUserBL.GetBranchOnSuperAdmin8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtSuperAdmin.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtSuperAdmin;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }

        public void 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");
            }
        }

        public void UPDATEBRANCH(string ActiveCallvalue)
        {
            if (!string.IsNullOrEmpty(Session["UserId"] as string))
            {
                Useridvalue = Session["UserId"].ToString();
                QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
                S = ObjMain.ExecuteNonQuery(QueryCurr_Select);
            }
            else
            {
                Response.Redirect("Login.aspx");

            }

        }


        protected void btnGetReports_Click(object sender, EventArgs e)
        {

            ClearCache();
            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 )
                LoadSummaryReport(Cmb_BranchName, StartDT, EndDt);
               
                CreateLinechardata(Cmb_BranchName, lblLinechartStdt.Text, Curr_DateOnly);
                

            }

        }


        public void ClearCache()
        {

            HttpContext.Current.Response.Cache.SetExpires(DateTime.UtcNow.AddDays(-1));
            HttpContext.Current.Response.Cache.SetValidUntilExpires(false);
            HttpContext.Current.Response.Cache.SetRevalidation(HttpCacheRevalidation.AllCaches);
            HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            HttpContext.Current.Response.Cache.SetNoStore();

        }


        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {

           if (cmbBranches.SelectedIndex == 0)
            {
                Response.Redirect("SummaryReport_light.aspx");
            }
            else
            {
                if (txtStartDt.Text != "" && txtEndDt.Text != "")
                {
                  
                    string StartDT = txtStartDt.Text;
                    string EndDt =txtEndDt.Text;
                    
                    ActiveCall_on_BranchSelection();



                }

            }

        }
        public void LoadSummaryReport(string DB, string D1, string D2)
        {

        

            decimal _totalsalesamount=0;
            decimal _netsale;
            int _payoutCount=0;
            decimal _payoutAmount=0;
            int _receiveCount=0;
            decimal _receiveAmount = 0;
            decimal _cashindrawer = 0;
            decimal _cashindrawer_final = 0;
            decimal _rounding = 0;
            decimal _refund = 0;
            decimal _grossamount=0; // do the calculation from values 
            decimal _itemdiscount = 0;
            decimal _totaldiscount = 0;
            decimal _memberdiscount = 0;
            decimal _priceshifdiscount = 0;
            decimal _discountTotal = 0;
            decimal _gst = 0;
            decimal _vocheramount = 0;
            string _tax3 = string.Empty;
            decimal _tax3_amount = 0;
            string _tax2 = string.Empty;
            decimal _tax2_amount = 0;
            string _tax1 = string.Empty;
            decimal _tax1_amount = 0;
            int _holdCount = 0;
            decimal _holdAmount = 0;
            int _cancelcount = 0;
            decimal _cancelamount = 0;
            int _voidcount = 0;
            decimal _Voidamount = 0;
            decimal _ExchangeAmount = 0;
            int _noofsalesopen = 0;
            int _receiptcount = 0;
            decimal _receiptAvg = 0;


              DT = ObjMain.GetData_DT("  SELECT  "
             + "  BusinessDate, "
             + " SUM(TotalSalesAmount) AS TotalSalesAmount,"
             + " SUM(PayOutCount) AS PayOutCount, "
             + " SUM(PayOutAmount) AS PayOutAmount,"
             + " SUM(Receivecount) AS Receivecount, "
             + " SUM(ReceiveAmount) AS ReceiveAmount, "
             + " SUM(CashinDrawer+ReceiveAmount-PayOutAmount) AS CashinDrawer, "
             + " SUM(Rounding) AS Rounding, "
             + " SUM(Refund) AS Refund, "
             + " SUM(ItemDiscount) AS ItemDiscount, "
             + " SUM(TotalDiscount) AS TotalDiscount, "
             + " SUM(TotalPriceshiftDiscount) AS TotalPriceshiftDiscount, "
             + " SUM(MemDiscAmt) AS MemDiscAmt, "
             + " Tax3, "
             + " SUM(Tax3Total) AS Tax3Total, "
             + " Tax2, "
             + " SUM(Tax2Total) AS Tax2Total,"
             + " Tax1, "
             + " SUM(Tax1Total) AS Tax1Total, "
             + " SUM(NetSale) AS NetSale, "
             + " SUM(HoldCount) AS HoldCount,"
             + " SUM(HoldAmount) AS HoldAmount, "
             + " SUM(CancelCount) AS CancelCount, "
             + " SUM(CancelAmount) AS CancelAmount, "
             + " SUM(voidcount) AS VoidCount, "
             + " SUM(voidAmount) AS VoidAmount, "
             + " SUM(ExchangeAmount) AS ExchangeAmount,"
             + " SUM(nosalesopen) AS NoSalesOpen, "
             + " SUM(ReceiptsCount) AS ReceiptsCount,"
             + " AVG(ReceiptAverage) AS ReceiptAverage,"
             + " SUM(VoucherAmount) AS VoucherAmount,"
             + " SUM(discountTotal) AS DiscountTotal,"
             + " sum(TotalSalesAmount + Rounding + discountTotal - Refund - VoucherAmount) as GrossAmount, "
             + " LastUpdate "
             + " FROM "+DB+".summarydetails_light "
             + " WHERE BusinessDate BETWEEN '"+D1+"' AND '"+ D2+"' "
             + " GROUP BY Tax1, Tax2, Tax3; ");

            if (DT.Rows.Count > 0)
            {

                _totalsalesamount = Convert.ToDecimal(DT.Rows[0]["TotalSalesAmount"].ToString());
                _netsale = Convert.ToDecimal(DT.Rows[0]["NetSale"].ToString());
                _payoutCount = int.Parse(DT.Rows[0]["PayOutCount"].ToString());
                _payoutAmount =decimal.Parse(DT.Rows[0]["PayOutAmount"].ToString());
                _receiveCount = int.Parse(DT.Rows[0]["Receivecount"].ToString());
                _receiveAmount = decimal.Parse(DT.Rows[0]["ReceiveAmount"].ToString());
                _cashindrawer= decimal.Parse(DT.Rows[0]["CashinDrawer"].ToString());
                _rounding = decimal.Parse(DT.Rows[0]["Rounding"].ToString());
                _refund = decimal.Parse(DT.Rows[0]["Refund"].ToString());
                _itemdiscount = decimal.Parse(DT.Rows[0]["ItemDiscount"].ToString());
                _totaldiscount = decimal.Parse(DT.Rows[0]["TotalDiscount"].ToString());
                _priceshifdiscount = decimal.Parse(DT.Rows[0]["TotalPriceshiftDiscount"].ToString());
                _memberdiscount = decimal.Parse(DT.Rows[0]["MemDiscAmt"].ToString());
                _discountTotal = decimal.Parse(DT.Rows[0]["discountTotal"].ToString());
                _discountTotal = decimal.Parse(DT.Rows[0]["discountTotal"].ToString());
                _tax3 = DT.Rows[0]["Tax3"].ToString();
                _tax3_amount= decimal.Parse(DT.Rows[0]["Tax3Total"].ToString());
                _tax2 = DT.Rows[0]["Tax2"].ToString();
                _tax2_amount = decimal.Parse(DT.Rows[0]["Tax2Total"].ToString());
                _tax1 = DT.Rows[0]["Tax1"].ToString();
                _tax1_amount = decimal.Parse(DT.Rows[0]["Tax1Total"].ToString());
                _vocheramount= decimal.Parse(DT.Rows[0]["VoucherAmount"].ToString());
                _holdCount= int.Parse(DT.Rows[0]["HoldCount"].ToString());
                _holdAmount = decimal.Parse(DT.Rows[0]["holdAmount"].ToString());
                _cancelcount = int.Parse(DT.Rows[0]["CancelCount"].ToString());
                _cancelamount =decimal.Parse(DT.Rows[0]["CancelAmount"].ToString());
                _voidcount = int.Parse(DT.Rows[0]["voidcount"].ToString());
                _Voidamount = decimal.Parse(DT.Rows[0]["voidAmount"].ToString());

                _ExchangeAmount = decimal.Parse(DT.Rows[0]["ExchangeAmount"].ToString());
                _noofsalesopen = int.Parse(DT.Rows[0]["nosalesopen"].ToString());
                _receiptcount = int.Parse(DT.Rows[0]["ReceiptsCount"].ToString());
                _receiptAvg = decimal.Parse(DT.Rows[0]["ReceiptAverage"].ToString());
                _grossamount = decimal.Parse(DT.Rows[0]["GrossAmount"].ToString());
                




                lblRTotalSale.Text = _totalsalesamount.ToString("c");
                lblDNetAmount.Text = _netsale.ToString("c");

            }







            var combined = new Dictionary<string, PaymentSummaryItem>();
            DateTime? lastUpdated = null;

            using (var conn = new MySqlConnection(ConStr))
            {
                conn.Open();
                string query = @"SELECT Businessdate, PivotResult, LastUpdate "
                               + " FROM "+DB+".paymentpivotresultsjson_light  "
                               + " WHERE Businessdate BETWEEN @D1 AND @D2";

                using (var cmd = new MySqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@D1",D1);
                    cmd.Parameters.AddWithValue("@D2", D2);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string json = reader["PivotResult"].ToString();

                            var dailyData = JsonConvert.DeserializeObject<Dictionary<string, PaymentSummaryItem>>(json);

                            foreach (var kvp in dailyData)
                            {
                                if (!combined.ContainsKey(kvp.Key))
                                {
                                    combined[kvp.Key] = new PaymentSummaryItem
                                    {
                                        PaymentMethod = kvp.Key,
                                        Count = kvp.Value.Count,
                                        Amount = kvp.Value.Amount
                                    };
                                }
                                else
                                {
                                    combined[kvp.Key].Count += kvp.Value.Count;
                                    combined[kvp.Key].Amount += kvp.Value.Amount;
                                }
                            }

                            // Track latest LastUpdate
                            DateTime currentUpdate = Convert.ToDateTime(reader["LastUpdate"]);
                            if (!lastUpdated.HasValue || currentUpdate > lastUpdated)
                                lastUpdated = currentUpdate;
                        }
                    }
                }
            }

            // Build HTML table
            var list = combined.Values.ToList();
            StringBuilder sb = new StringBuilder();
            sb.Append("<table class='table  mt-3 bg-white text-dark table-sm' style='widht=30%'>");
            //sb.Append("<table class='table mt-2 bg-white text-dark' style='font-size:14px;'>");
            sb.Append("<thead class='table-dark'><tr><th>Payment Method</th><th>Total Count</th><th>Total Amount (SGD)</th></tr></thead>");
            sb.Append("<tbody>");

            int grandCount = 0;
            decimal grandAmount = 0;

            foreach (var item in list)
            {
                sb.Append("<tr>");
                sb.AppendFormat("<td style='padding-left:10px'>{0}</td>", item.PaymentMethod);
                sb.AppendFormat("<td>{0}</td>", item.Count);
                sb.AppendFormat("<td>{0:N2}</td>", item.Amount);
                sb.Append("</tr>");

                grandCount += item.Count;
                grandAmount += item.Amount;
            }

            // Total row
            //sb.Append("<tr class='fw-bold table-secondary text-dark'>");
            //sb.AppendFormat("<td>Total</td><td>{0}</td><td>{1:N2}</td>", grandCount, grandAmount);


            // Payouts 
            sb.Append("<tr>");
            sb.AppendFormat("<td style='padding-left:10px'>{0}</td>", "Payouts");
            sb.AppendFormat("<td>{0}</td>", _payoutCount);
            sb.AppendFormat("<td>{0:N2}</td>",_payoutAmount);

            sb.Append("</tr>");

            // Receiving
            sb.Append("<tr>");
            sb.AppendFormat("<td style='padding-left:10px'>{0}</td>", "Receiving");
            sb.AppendFormat("<td>{0}</td>",_receiveCount);
            sb.AppendFormat("<td>{0:N2}</td>", _receiveAmount);

            sb.Append("</tr>");


            

            sb.Append("</tbody></table>");

            // Render to Literal
            ltTable.Text = sb.ToString();


            // Retail Discount Details Section 

            // Cashindraw  calculation 
           // _cashindrawer_final = _cashindrawer + _receiveAmount - _payoutAmount;

            string htmlTable = "<table><tr>";
            htmlTable += "<tr><td>" + "Cash in Drawer" + " " + "</td> <td>" + _cashindrawer.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblCashinDrawer.Text = htmlTable;

            //Rounding 
            string htmlTable1 = "<table ><tr>";
            htmlTable1 += "<tr><td>" + "Rounding" + " " + "</td> <td>" +_rounding.ToString("c") + "</td></tr>";
            htmlTable1 += "</table>";
            lblRounding.Text = htmlTable1;


            // Refund 
            string htmlTable2 = "<table ><tr>";
            htmlTable2 += "<tr><td>" + "Refund " + " " + "</td> <td>" +_refund.ToString("c") + "</td></tr>";
            htmlTable2 += "</table>";
            lblRefund.Text = htmlTable2;

            // Grossamount Calculation

            // decimal  _cgrossamount = _totalsalesamount - _refund - _vocheramount;

           // _grossamount = _cgrossamount + _rounding + _discountTotal;

            string htmlTable3 = "<table ><tr>";
            htmlTable3 += "<tr><td>" + "Gross Amount" + " " + "</td> <td>" + _grossamount.ToString("c") + "</td></tr>";
            htmlTable3 += "</table>";
            lblGrossAmount.Text = htmlTable3;

            // ItemDiscount


            string htmlTable4 = "<table ><tr>";
            htmlTable4 += "<tr><td>" + "Item Discount" + " " + "</td> <td>" + _itemdiscount.ToString("c") + "</td></tr>";
            htmlTable4 += "</table>";
            lblItemDiscount.Text = htmlTable4;


            // Totaldiscount
            string htmlTable5 = "<table ><tr>";
            htmlTable5 += "<tr><td>" + "Total Discount" + " " + "</td> <td>" + _totaldiscount.ToString("c") + "</td></tr>";
            //htmlTable += "<tr><td>" + "Total Discount" + " " + "</td> <td>" + dTotalDisc.ToString("c") + "</td></tr>";
            htmlTable5 += "</table>";
            lblTotDiscount.Text = htmlTable5;

            // Membersdiscount
            string htmlTable6 = "<table ><tr>";
            htmlTable6 += "<tr><td>" + "Member Total Disc Amount" + " " + "</td> <td>" + _memberdiscount.ToString("c") + "</td></tr>";
            htmlTable6 += "</table>";
            lblMemberDiscount.Text = htmlTable6;

            //priceshiftdiscount
            string htmlTable7 = "<table ><tr>";
            htmlTable7 += "<tr><td>" + "Price Shift Discount" + " " + "</td> <td>" + _priceshifdiscount.ToString("c") + "</td></tr>";
            htmlTable7 += "</table>";
            lblPriceShiftDiscount.Text = htmlTable7;

            //DiscountTotal 
            string htmlTable8 = "<table ><tr>";
            htmlTable8 += "<tr><td>" + "Discount Total" + " " + "</td> <td>" + _discountTotal.ToString("c") + "</td></tr>";
            htmlTable8 += "</table>";
            lblDiscountTotal.Text = htmlTable8;

            //Tax2
            string htmlTable9 = "<table ><tr>";
            htmlTable9 += "<tr><td>" + _tax2 + " " + "</td> <td>" + _tax2_amount.ToString("c") + "</td></tr>";
            htmlTable9 += "</table>";
            lblGST.Text = htmlTable9;

            //Tax1
            string htmlTable10 = "<table ><tr>";
            htmlTable10 += "<tr><td>" + _tax1 + " " + "</td> <td>" + _tax1_amount.ToString("c") + "</td></tr>";
            htmlTable10 += "</table>";
            lblSVC.Text = htmlTable10;


            //Tax3
            string htmlTable11 = "<table ><tr>";
            htmlTable11 += "<tr><td>" + _tax3 + " " + "</td> <td>" + _tax3_amount.ToString("c") + "</td></tr>";
            htmlTable11 += "</table>";
            lblTax3.Text = htmlTable11;

            // vocheramount 
            string htmlTable12 = "<table ><tr>";
            htmlTable12 += "<tr><td>" + "Voucher Amount" + " " + "</td> <td>" + _vocheramount .ToString("c") + "</td></tr>";
            htmlTable12 += "</table>";
            lblVoucher.Text = htmlTable12;


            // HoldCount ,Holdamount

            string htmlTable13 = "<table ><tr>";
            htmlTable13 += "<tr><td>" + "Hold " + "" + "</td>     <td>" + "" + _holdCount + "</td> <td>" + "" + _holdAmount.ToString("c") + "</td></tr>";
            htmlTable13 += "</table>";
            lblHold.Text = htmlTable13;


            string htmlTable14 = "<table ><tr>";
            htmlTable14 += "<tr><td>" + "Cancel " + " " + "</td>     <td>" + " " + _cancelcount + "</td> <td>" + " " + _cancelamount.ToString("c") + "</td></tr>";
            htmlTable14 += "</table>";
            lblCancel.Text = htmlTable14;


            string htmlTable15 = "<table ><tr>";
            htmlTable15 += "<tr><td>" + "Void " + " " + "</td>     <td>" + " " + _voidcount + "</td> <td>" + "" + _Voidamount.ToString("c") + "</td></tr>";
            htmlTable15 += "</table>";
            lblVoid.Text = htmlTable15;


            string htmlTable16 = "<table ><tr>";
            htmlTable16 += "<tr><td>" + "Exchange " + "" + "</td>     <td>" + "" + "N/A" + "</td> <td>" + " " + _ExchangeAmount.ToString("c") + "</td></tr>";
            htmlTable16 += "</table>";
            lblExchange.Text = htmlTable16;


            string htmlTabl17 = "<table ><tr>";
            htmlTabl17 += "<tr><td>" + "No Sales Open " + "" + "</td>     <td>" + "" + _noofsalesopen + "</td> <td>" + " " + "N/A" + "</td></tr>";
            htmlTabl17 += "</table>";
            lblNosalespersonOpen.Text = htmlTabl17;

            string htmlTable18 = "<table ><tr>";
            htmlTable18 += "<tr><td>" + "No. Of Receipts	 " + " " + "</td>     <td>" + " " + _receiptcount + "</td> <td>" + "" + "N/A" + "</td></tr>";
            htmlTable18 += "</table>";
            lblNoofReceipts.Text = htmlTable18;

            string htmlTable19 = "<table ><tr>";
            htmlTable19 += "<tr><td>" + "Receipts Avg" + " " + "</td><td>" + "" + "N/A" + "</td> <td>" + " " + _receiptAvg.ToString("c") + "</td></tr>";
            htmlTable19 += "</table>";
            lblAvgReceipts.Text = htmlTable19;




            // Show last updated timestamp
            if (lastUpdated.HasValue)
                lblLastUpdatedTM.Text = $" (Last Updated: {lastUpdated.Value:yyyy-MM-dd HH:mm:ss})";
            else
                lblLastUpdatedTM.Text = "";
        }

        public void ConsolidatedSummaryExcelReport(string DB, string D1, string D2)
        {

            decimal _totalsalesamount = 0;
            decimal _netsale=0;
            int _payoutCount = 0;
            decimal _payoutAmount = 0;
            int _receiveCount = 0;
            decimal _receiveAmount = 0;
            decimal _cashindrawer = 0;
            
            decimal _rounding = 0;
            decimal _refund = 0;
            decimal _grossamount = 0; // do the calculation from values 
            decimal _itemdiscount = 0;
            decimal _totaldiscount = 0;
            decimal _memberdiscount = 0;
            decimal _priceshifdiscount = 0;
            decimal _discountTotal = 0;
            decimal _gst = 0;
            decimal _vocheramount = 0;
            string _tax3 = string.Empty;
            decimal _tax3_amount = 0;
            string _tax2 = string.Empty;
            decimal _tax2_amount = 0;
            string _tax1 = string.Empty;
            decimal _tax1_amount = 0;
            int _holdCount = 0;
            decimal _holdAmount = 0;
            int _cancelcount = 0;
            decimal _cancelamount = 0;
            int _voidcount = 0;
            decimal _Voidamount = 0;
            decimal _ExchangeAmount = 0;
            int _noofsalesopen = 0;
            int _receiptcount = 0;
            decimal _receiptAvg = 0;


            DT = ObjMain.GetData_DT("  SELECT  "
           + "  BusinessDate, "
           + " SUM(TotalSalesAmount) AS TotalSalesAmount,"
           + " SUM(PayOutCount) AS PayOutCount, "
           + " SUM(PayOutAmount) AS PayOutAmount,"
           + " SUM(Receivecount) AS Receivecount, "
           + " SUM(ReceiveAmount) AS ReceiveAmount, "
           + " SUM(CashinDrawer+ReceiveAmount-PayOutAmount) AS CashinDrawer, "
           + " SUM(Rounding) AS Rounding, "
           + " SUM(Refund) AS Refund, "
           + " SUM(ItemDiscount) AS ItemDiscount, "
           + " SUM(TotalDiscount) AS TotalDiscount, "
           + " SUM(TotalPriceshiftDiscount) AS TotalPriceshiftDiscount, "
           + " SUM(MemDiscAmt) AS MemDiscAmt, "
           + " Tax3, "
           + " SUM(Tax3Total) AS Tax3Total, "
           + " Tax2, "
           + " SUM(Tax2Total) AS Tax2Total,"
           + " Tax1, "
           + " SUM(Tax1Total) AS Tax1Total, "
           + " SUM(NetSale) AS NetSale, "
           + " SUM(HoldCount) AS HoldCount,"
           + " SUM(HoldAmount) AS HoldAmount, "
           + " SUM(CancelCount) AS CancelCount, "
           + " SUM(CancelAmount) AS CancelAmount, "
           + " SUM(voidcount) AS VoidCount, "
           + " SUM(voidAmount) AS VoidAmount, "
           + " SUM(ExchangeAmount) AS ExchangeAmount,"
           + " SUM(nosalesopen) AS NoSalesOpen, "
           + " SUM(ReceiptsCount) AS ReceiptsCount,"
           + " AVG(ReceiptAverage) AS ReceiptAverage,"
           + " SUM(VoucherAmount) AS VoucherAmount,"
           + " SUM(discountTotal) AS DiscountTotal,"
           + " sum(TotalSalesAmount + Rounding + discountTotal - Refund - VoucherAmount) as GrossAmount, "
           + " LastUpdate "
           + " FROM " + DB + ".summarydetails_light "
           + " WHERE BusinessDate BETWEEN '" + D1 + "' AND '" + D2 + "' "
           + " GROUP BY Tax1, Tax2, Tax3; ");

            if (DT.Rows.Count > 0)
            {

                _totalsalesamount = Convert.ToDecimal(DT.Rows[0]["TotalSalesAmount"].ToString());
                _netsale = Convert.ToDecimal(DT.Rows[0]["NetSale"].ToString());
                _payoutCount = int.Parse(DT.Rows[0]["PayOutCount"].ToString());
                _payoutAmount = decimal.Parse(DT.Rows[0]["PayOutAmount"].ToString());
                _receiveCount = int.Parse(DT.Rows[0]["Receivecount"].ToString());
                _receiveAmount = decimal.Parse(DT.Rows[0]["ReceiveAmount"].ToString());
                _cashindrawer = decimal.Parse(DT.Rows[0]["CashinDrawer"].ToString());
                _rounding = decimal.Parse(DT.Rows[0]["Rounding"].ToString());
                _refund = decimal.Parse(DT.Rows[0]["Refund"].ToString());
                _itemdiscount = decimal.Parse(DT.Rows[0]["ItemDiscount"].ToString());
                _totaldiscount = decimal.Parse(DT.Rows[0]["TotalDiscount"].ToString());
                _priceshifdiscount = decimal.Parse(DT.Rows[0]["TotalPriceshiftDiscount"].ToString());
                _memberdiscount = decimal.Parse(DT.Rows[0]["MemDiscAmt"].ToString());
                _discountTotal = decimal.Parse(DT.Rows[0]["discountTotal"].ToString());
                _discountTotal = decimal.Parse(DT.Rows[0]["discountTotal"].ToString());
                _tax3 = DT.Rows[0]["Tax3"].ToString();
                _tax3_amount = decimal.Parse(DT.Rows[0]["Tax3Total"].ToString());
                _tax2 = DT.Rows[0]["Tax2"].ToString();
                _tax2_amount = decimal.Parse(DT.Rows[0]["Tax2Total"].ToString());
                _tax1 = DT.Rows[0]["Tax1"].ToString();
                _tax1_amount = decimal.Parse(DT.Rows[0]["Tax1Total"].ToString());
                _vocheramount = decimal.Parse(DT.Rows[0]["VoucherAmount"].ToString());
                _holdCount = int.Parse(DT.Rows[0]["HoldCount"].ToString());
                _holdAmount = decimal.Parse(DT.Rows[0]["holdAmount"].ToString());
                _cancelcount = int.Parse(DT.Rows[0]["CancelCount"].ToString());
                _cancelamount = decimal.Parse(DT.Rows[0]["CancelAmount"].ToString());
                _voidcount = int.Parse(DT.Rows[0]["voidcount"].ToString());
                _Voidamount = decimal.Parse(DT.Rows[0]["voidAmount"].ToString());

                _ExchangeAmount = decimal.Parse(DT.Rows[0]["ExchangeAmount"].ToString());
                _noofsalesopen = int.Parse(DT.Rows[0]["nosalesopen"].ToString());
                _receiptcount = int.Parse(DT.Rows[0]["ReceiptsCount"].ToString());
                _receiptAvg = decimal.Parse(DT.Rows[0]["ReceiptAverage"].ToString());
                _grossamount = decimal.Parse(DT.Rows[0]["GrossAmount"].ToString());





                
                

            }







            var combined = new Dictionary<string, PaymentSummaryItem>();
            DateTime? lastUpdated = null;

            using (var conn = new MySqlConnection(ConStr))
            {
                conn.Open();
                string query = @"SELECT Businessdate, PivotResult, LastUpdate "
                               + " FROM " + DB + ".paymentpivotresultsjson_light  "
                               + " WHERE Businessdate BETWEEN @D1 AND @D2";

                using (var cmd = new MySqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@D1", D1);
                    cmd.Parameters.AddWithValue("@D2", D2);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string json = reader["PivotResult"].ToString();

                            var dailyData = JsonConvert.DeserializeObject<Dictionary<string, PaymentSummaryItem>>(json);

                            foreach (var kvp in dailyData)
                            {
                                if (!combined.ContainsKey(kvp.Key))
                                {
                                    combined[kvp.Key] = new PaymentSummaryItem
                                    {
                                        PaymentMethod = kvp.Key,
                                        Count = kvp.Value.Count,
                                        Amount = kvp.Value.Amount
                                    };
                                }
                                else
                                {
                                    combined[kvp.Key].Count += kvp.Value.Count;
                                    combined[kvp.Key].Amount += kvp.Value.Amount;
                                }
                            }

                            // Track latest LastUpdate
                            DateTime currentUpdate = Convert.ToDateTime(reader["LastUpdate"]);
                            if (!lastUpdated.HasValue || currentUpdate > lastUpdated)
                                lastUpdated = currentUpdate;
                        }
                    }
                }
            }

            // Build HTML table
            var list = combined.Values.ToList();


            DataTable tmpDTpaymentheader = new DataTable("paymentheader");

            DataTable tempDT = new DataTable("tempDT");

            DataColumn dtColumn;
           //ataRow myDataRow;



            
            

            // Step 1: Dynamically create columns for each payment type
            foreach (var item in list)
            {
                
                // Count column
                if (!tmpDTpaymentheader.Columns.Contains(item.PaymentMethod + "_Count"))
                    tmpDTpaymentheader.Columns.Add(item.PaymentMethod + "_Count", typeof(int));

                // Amount column
                if (!tmpDTpaymentheader.Columns.Contains(item.PaymentMethod + "_Amount"))
                    tmpDTpaymentheader.Columns.Add(item.PaymentMethod + "_Amount", typeof(decimal));
            }

            // Step 2: Create a single DataRow
            DataRow myDataRow = tmpDTpaymentheader.NewRow();

            // Step 3: Fill the DataRow with values from the list
            foreach (var item in list)
            {
                myDataRow[item.PaymentMethod + "_Count"] = item.Count;
                myDataRow[item.PaymentMethod + "_Amount"] = item.Amount;
            }

            // Step 4: Add the row to the DataTable
            tmpDTpaymentheader.Rows.Add(myDataRow);




            #region    PayOutCount & PayOut Amount
            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "PayOut Count";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr2 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count2 = tmpDTpaymentheader.Columns.Count - 1;

            dr2[row_count2] = _payoutCount;

            //Add the new column
           dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "PayOut Amount";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr3 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count3 = tmpDTpaymentheader.Columns.Count - 1;
            _payoutAmount = Math.Round(_payoutAmount, 2);
            dr3[row_count3] = _payoutAmount.ToString();
            #endregion


            #region Receiving Count & Receiving Amount


            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Receiving Count";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr4 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count4 = tmpDTpaymentheader.Columns.Count - 1;

            dr4[row_count4] =_receiveCount;



            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Receiving Amount";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr5 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count5 = tmpDTpaymentheader.Columns.Count - 1;

            _receiveAmount = Math.Round(_receiveAmount, 2);
            dr5[row_count5] = _receiveAmount.ToString();

            #endregion

            #region Cashindrawer 


            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Cash in Drawer";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr6 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count6 = tmpDTpaymentheader.Columns.Count - 1;
            _cashindrawer = Math.Round(_cashindrawer, 2);
            dr6[row_count6] = _cashindrawer.ToString();





            #endregion

            #region  Rounding
            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Rounding";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr7 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count7 = tmpDTpaymentheader.Columns.Count - 1;

            _rounding = Math.Round(_rounding, 2);
            dr7[row_count7] = _rounding.ToString();

            #endregion

            #region Refund

            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Refund";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr8 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count8 = tmpDTpaymentheader.Columns.Count - 1;
            _refund = Math.Round(_refund, 2);
            dr8[row_count8] = _refund.ToString();

            #endregion

            #region ItemsDiscount
            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Item Discount";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr9 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count9 = tmpDTpaymentheader.Columns.Count - 1;
            _itemdiscount = Math.Round(_itemdiscount, 2);
            dr9[row_count9] = _itemdiscount.ToString();
            #endregion

            #region  Totaldiscount
            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Total Discount";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr10 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count10 = tmpDTpaymentheader.Columns.Count - 1;
            _totaldiscount = Math.Round(_totaldiscount, 2);
            dr10[row_count10] = _totaldiscount.ToString();

            #endregion
            
            #region  Memberdiscount
            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Member Total Disc Amount";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr11 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count11 = tmpDTpaymentheader.Columns.Count - 1;
            _memberdiscount = Math.Round(_memberdiscount, 2);
            dr11[row_count11] = _memberdiscount.ToString("c");

            #endregion

            #region TotalPriceshiftdiscount 
            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Total Price Shift Disc Amount";
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr12 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count12 = tmpDTpaymentheader.Columns.Count - 1;
            _priceshifdiscount = Math.Round(_priceshifdiscount, 2);
            dr12[row_count12] = _priceshifdiscount.ToString();
            #endregion

            #region  DiscountTotal 

            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Discount Total";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr13 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count13 = tmpDTpaymentheader.Columns.Count - 1;
            _discountTotal = Math.Round(_discountTotal, 2);
            dr13[row_count13] = _discountTotal.ToString();


            #endregion
            
            #region Tax3   - svc 

            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName =_tax3;
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr14 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count14 = tmpDTpaymentheader.Columns.Count - 1;
            _tax3_amount = Math.Round(_tax3_amount, 2);
            dr14[row_count14] = _tax3_amount.ToString();

            #endregion

            #region Tax2  - gst 

            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = _tax2;
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr15= tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count15 = tmpDTpaymentheader.Columns.Count - 1;
            _tax2_amount = Math.Round(_tax2_amount, 2);
            dr15[row_count15] = _tax2_amount.ToString();

            #endregion

            #region Tax1  

            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = _tax1;
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr16 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count16 = tmpDTpaymentheader.Columns.Count - 1;
            _tax1_amount = Math.Round(_tax1_amount, 2);
            dr16[row_count16] = _tax1_amount.ToString();

            #endregion
            #region NetSale

            // Add the new column

            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "NetSale ";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr17 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count17 = tmpDTpaymentheader.Columns.Count - 1;
            _netsale = Math.Round(_netsale, 2);
            dr17[row_count17] = _netsale.ToString();

            #endregion


            #region HoldCount
            

            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Hold Count";
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr18 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count18 = tmpDTpaymentheader.Columns.Count - 1;
            dr18[row_count18] = _holdCount;

            #endregion


            #region  HoldAmount
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Hold Amount ";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr19 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count19 = tmpDTpaymentheader.Columns.Count - 1;
            _holdAmount = Math.Round(_holdAmount, 2);
            dr19[row_count19] = _holdAmount.ToString();

            #endregion


            #region HoldCount


            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Cancel Count";
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr20 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count20 = tmpDTpaymentheader.Columns.Count - 1;
            dr20[row_count20] = _cancelcount;

            #endregion


            #region  Cancel Amount
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Cancel Amount ";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr21 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count21 = tmpDTpaymentheader.Columns.Count - 1;
            _cancelamount = Math.Round(_cancelamount, 2);
            dr21[row_count21] = _cancelamount.ToString();

            #endregion


            #region VoidCount


            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Void Count";
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr22 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count22 = tmpDTpaymentheader.Columns.Count - 1;
            dr22[row_count22] = _voidcount;

            #endregion


            #region  Void Amount
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Void Amount ";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr23 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count23 = tmpDTpaymentheader.Columns.Count - 1;
            _Voidamount = Math.Round(_Voidamount, 2);
            dr23[row_count23] = _Voidamount.ToString();

            #endregion

            #region  Exchange Amount
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Exchange Amount ";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr24 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count24 = tmpDTpaymentheader.Columns.Count - 1;
            _ExchangeAmount = Math.Round(_ExchangeAmount, 2);
            dr24[row_count24] = _ExchangeAmount.ToString();

            #endregion



            #region Nosalesopen


            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "NoSales Open";
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr25 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count25 = tmpDTpaymentheader.Columns.Count - 1;
            dr25[row_count25] = _noofsalesopen;

            #endregion

            #region ReceiptCount


            // Add the new column
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Receipt Count";
            dtColumn.ReadOnly = false;
            // Add the row value 
            tmpDTpaymentheader.Columns.Add(dtColumn);
            DataRow dr26 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count26 = tmpDTpaymentheader.Columns.Count - 1;
            dr26[row_count26] = _receiptcount;

            #endregion

            #region  ReceiptAverage 
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Receipt Average ";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr27 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count27 = tmpDTpaymentheader.Columns.Count - 1;
            _receiptAvg = Math.Round(_receiptAvg, 2);
            dr27[row_count27] = _receiptAvg.ToString();

            #endregion

            #region  voucherAmount
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Voucher Amount ";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr28 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count28 = tmpDTpaymentheader.Columns.Count - 1;
            _vocheramount = Math.Round(_vocheramount, 2);
            dr28[row_count28] = _vocheramount.ToString();

            #endregion

            #region  GrossAmount
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(string);
            dtColumn.ColumnName = "Gross Amount ";
            dtColumn.ReadOnly = false;
            tmpDTpaymentheader.Columns.Add(dtColumn);
            // Add the row value 
            DataRow dr29 = tmpDTpaymentheader.Rows[0];
            // Get the row count 
            int row_count29 = tmpDTpaymentheader.Columns.Count - 1;
            _grossamount = Math.Round(_grossamount, 2);
            dr29[row_count29] = _grossamount.ToString();

            #endregion


            tmpDTpaymentheader.AcceptChanges();


            // Assing to Excel Report 
            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            //GridView1.DataSource = Session["ResultDTExcel"];
            GridView1.DataSource = tmpDTpaymentheader;
            GridView1.DataBind();
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
                "attachment;filename=SummaryExport.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();
            HttpContext.Current.Response.End();







        }
        //public void BusinessdatewiseSummaryExcelReport(string DB, string D1, string D2)
        //{
        //    string _businessdate = string.Empty;
        //    decimal _totalsalesamount = 0;
        //    decimal _netsale = 0;
        //    int _payoutCount = 0;
        //    decimal _payoutAmount = 0;
        //    int _receiveCount = 0;
        //    decimal _receiveAmount = 0;
        //    decimal _cashindrawer = 0;

        //    decimal _rounding = 0;
        //    decimal _refund = 0;
        //    decimal _grossamount = 0;       // do the calculation from values 
        //    decimal _itemdiscount = 0;
        //    decimal _totaldiscount = 0;
        //    decimal _memberdiscount = 0;
        //    decimal _priceshifdiscount = 0;
        //    decimal _discountTotal = 0;
        //    decimal _gst = 0;
        //    decimal _vocheramount = 0;
        //    string _tax3 = string.Empty;
        //    decimal _tax3_amount = 0;
        //    string _tax2 = string.Empty;
        //    decimal _tax2_amount = 0;
        //    string _tax1 = string.Empty;
        //    decimal _tax1_amount = 0;
        //    int _holdCount = 0;
        //    decimal _holdAmount = 0;
        //    int _cancelcount = 0;
        //    decimal _cancelamount = 0;
        //    int _voidcount = 0;
        //    decimal _Voidamount = 0;
        //    decimal _ExchangeAmount = 0;
        //    int _noofsalesopen = 0;
        //    int _receiptcount = 0;
        //    decimal _receiptAvg = 0;


        //    DT = ObjMain.GetData_DT("  SELECT  "
        //   + " BusinessDate, "
        //   + " SUM(TotalSalesAmount) AS TotalSalesAmount,"
        //   + " SUM(PayOutCount) AS PayOutCount, "
        //   + " SUM(PayOutAmount) AS PayOutAmount,"
        //   + " SUM(Receivecount) AS Receivecount, "
        //   + " SUM(ReceiveAmount) AS ReceiveAmount, "
        //   + " SUM(CashinDrawer+ReceiveAmount-PayOutAmount) AS CashinDrawer, "
        //   + " SUM(Rounding) AS Rounding, "
        //   + " SUM(Refund) AS Refund, "
        //   + " SUM(ItemDiscount) AS ItemDiscount, "
        //   + " SUM(TotalDiscount) AS TotalDiscount, "
        //   + " SUM(TotalPriceshiftDiscount) AS TotalPriceshiftDiscount, "
        //   + " SUM(MemDiscAmt) AS MemDiscAmt, "
        //   + " Tax3, "
        //   + " SUM(Tax3Total) AS Tax3Total, "
        //   + " Tax2, "
        //   + " SUM(Tax2Total) AS Tax2Total, "
        //   + " Tax1, "
        //   + " SUM(Tax1Total) AS Tax1Total, "
        //   + " SUM(NetSale) AS NetSale, "
        //   + " SUM(HoldCount) AS HoldCount,"
        //   + " SUM(HoldAmount) AS HoldAmount, "
        //   + " SUM(CancelCount) AS CancelCount, "
        //   + " SUM(CancelAmount) AS CancelAmount, "
        //   + " SUM(voidcount) AS VoidCount, "
        //   + " SUM(voidAmount) AS VoidAmount, "
        //   + " SUM(ExchangeAmount) AS ExchangeAmount,"
        //   + " SUM(nosalesopen) AS NoSalesOpen, "
        //   + " SUM(ReceiptsCount) AS ReceiptsCount, "
        //   + " AVG(ReceiptAverage) AS ReceiptAverage, "
        //   + " SUM(VoucherAmount) AS VoucherAmount, "
        //   + " SUM(discountTotal) AS DiscountTotal, "
        //   + " sum(TotalSalesAmount + Rounding + discountTotal - Refund - VoucherAmount) as GrossAmount, "
        //   + " LastUpdate "
        //   + " FROM " + DB + ".summarydetails_light "
        //   + " WHERE BusinessDate BETWEEN '" + D1 + "' AND '" + D2 + "' "
        //   + " GROUP BY Tax1, Tax2, Tax3,BusinessDate ; ");


        //    DataTable tmpDTpaymentheader = new DataTable("paymentheader");

        //    DataTable tempDT = new DataTable("tempDT");

        //    DataColumn dtColumn;

        //    if (DT.Rows.Count > 0)
        //    {

        //        foreach (DataRow row in DT.Rows)
        //        {




        //            _businessdate = row["BusinessDate"].ToString();
        //            _totalsalesamount = Convert.ToDecimal(row["TotalSalesAmount"].ToString());
        //            _netsale = Convert.ToDecimal(row["NetSale"].ToString());
        //            _payoutCount = int.Parse(row["PayOutCount"].ToString());
        //            _payoutAmount = decimal.Parse(row["PayOutAmount"].ToString());
        //            _receiveCount = int.Parse(row["Receivecount"].ToString());
        //            _receiveAmount = decimal.Parse(row["ReceiveAmount"].ToString());
        //            _cashindrawer = decimal.Parse(row["CashinDrawer"].ToString());
        //            _rounding = decimal.Parse(row["Rounding"].ToString());
        //            _refund = decimal.Parse(row["Refund"].ToString());
        //            _itemdiscount = decimal.Parse(row["ItemDiscount"].ToString());
        //            _totaldiscount = decimal.Parse(row["TotalDiscount"].ToString());
        //            _priceshifdiscount = decimal.Parse(row["TotalPriceshiftDiscount"].ToString());
        //            _memberdiscount = decimal.Parse(row["MemDiscAmt"].ToString());
        //            _discountTotal = decimal.Parse(row["discountTotal"].ToString());
        //            _discountTotal = decimal.Parse(row["discountTotal"].ToString());
        //            _tax3 = row["Tax3"].ToString();
        //            _tax3_amount = decimal.Parse(row["Tax3Total"].ToString());
        //            _tax2 = row["Tax2"].ToString();
        //            _tax2_amount = decimal.Parse(row["Tax2Total"].ToString());
        //            _tax1 = row["Tax1"].ToString();
        //            _tax1_amount = decimal.Parse(row["Tax1Total"].ToString());
        //            _vocheramount = decimal.Parse(row["VoucherAmount"].ToString());
        //            _holdCount = int.Parse(row["HoldCount"].ToString());
        //            _holdAmount = decimal.Parse(row["holdAmount"].ToString());
        //            _cancelcount = int.Parse(row["CancelCount"].ToString());
        //            _cancelamount = decimal.Parse(row["CancelAmount"].ToString());
        //            _voidcount = int.Parse(row["voidcount"].ToString());
        //            _Voidamount = decimal.Parse(row["voidAmount"].ToString());

        //            _ExchangeAmount = decimal.Parse(row["ExchangeAmount"].ToString());
        //            _noofsalesopen = int.Parse(row["nosalesopen"].ToString());
        //            _receiptcount = int.Parse(row["ReceiptsCount"].ToString());
        //            _receiptAvg = decimal.Parse(row["ReceiptAverage"].ToString());
        //            _grossamount = decimal.Parse(row["GrossAmount"].ToString());




        //            var combined = new Dictionary<string, PaymentSummaryItem>();
        //            DateTime? lastUpdated = null;

        //            using (var conn = new MySqlConnection(ConStr))
        //            {
        //                conn.Open();
        //                string query = @"SELECT Businessdate, PivotResult, LastUpdate "
        //                               + " FROM " + DB + ".paymentpivotresultsjson_light  "
        //                               + " WHERE Businessdate=@Businessdate;";

        //                using (var cmd = new MySqlCommand(query, conn))
        //                {
        //                    cmd.Parameters.AddWithValue("@Businessdate", _businessdate);
        //                    //cmd.Parameters.AddWithValue("@D2", D2);

        //                    using (var reader = cmd.ExecuteReader())
        //                    {
        //                        while (reader.Read())
        //                        {
        //                            string json = reader["PivotResult"].ToString();

        //                            var dailyData = JsonConvert.DeserializeObject<Dictionary<string, PaymentSummaryItem>>(json);

        //                            foreach (var kvp in dailyData)
        //                            {
        //                                if (!combined.ContainsKey(kvp.Key))
        //                                {
        //                                    combined[kvp.Key] = new PaymentSummaryItem
        //                                    {
        //                                        PaymentMethod = kvp.Key,
        //                                        Count = kvp.Value.Count,
        //                                        Amount = kvp.Value.Amount
        //                                    };
        //                                }
        //                                else
        //                                {
        //                                    combined[kvp.Key].Count += kvp.Value.Count;
        //                                    combined[kvp.Key].Amount += kvp.Value.Amount;
        //                                }
        //                            }

        //                            // Track latest LastUpdate
        //                            DateTime currentUpdate = Convert.ToDateTime(reader["LastUpdate"]);
        //                            if (!lastUpdated.HasValue || currentUpdate > lastUpdated)
        //                                lastUpdated = currentUpdate;
        //                        }
        //                    }
        //                }
        //            }

        //            // Build HTML table
        //            var list = combined.Values.ToList();




        //            // Step 1: Dynamically create columns for each payment type
        //            foreach (var item in list)
        //            {

        //                // Count column
        //                if (!tmpDTpaymentheader.Columns.Contains(item.PaymentMethod + "_Count"))
        //                    tmpDTpaymentheader.Columns.Add(item.PaymentMethod + "_Count", typeof(int));

        //                // Amount column
        //                if (!tmpDTpaymentheader.Columns.Contains(item.PaymentMethod + "_Amount"))
        //                    tmpDTpaymentheader.Columns.Add(item.PaymentMethod + "_Amount", typeof(decimal));
        //            }

        //            // Step 2: Create a single DataRow
        //            DataRow myDataRow = tmpDTpaymentheader.NewRow();

        //            // Step 3: Fill the DataRow with values from the list
        //            foreach (var item in list)
        //            {
        //                myDataRow[item.PaymentMethod + "_Count"] = item.Count;
        //                myDataRow[item.PaymentMethod + "_Amount"] = item.Amount;
        //            }

        //            // Step 4: Add the row to the DataTable
        //            tmpDTpaymentheader.Rows.Add(myDataRow);




        //            #region    PayOutCount & PayOut Amount

        //            //dtColumn = new DataColumn();
        //            //dtColumn.DataType = typeof(string);
        //            //dtColumn.ColumnName = "PayOut Count";
        //            //dtColumn.ReadOnly = false;
        //            //tmpDTpaymentheader.Columns.Add(dtColumn);
        //            //// Add the row value 
        //            //DataRow dr2 = tmpDTpaymentheader.Rows[0];
        //            //// Get the row count 
        //            //int row_count2 = tmpDTpaymentheader.Columns.Count - 1;
        //            //dr2[row_count2] = _payoutCount;



        //            string colName = "PayOut Count";

        //            if (!tmpDTpaymentheader.Columns.Contains(colName))
        //            {
        //                DataColumn dtColumn1 = new DataColumn
        //                {
        //                    DataType = typeof(string),
        //                    ColumnName = colName,
        //                    ReadOnly = false
        //                };
        //                tmpDTpaymentheader.Columns.Add(dtColumn1);
        //            }

        //            // Make sure there’s at least one row in the table
        //            if (tmpDTpaymentheader.Rows.Count == 0)
        //                tmpDTpaymentheader.Rows.Add(tmpDTpaymentheader.NewRow());

        //            // Assign value safely
        //            tmpDTpaymentheader.Rows[0][colName] = _payoutCount;







        //            //Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "PayOut Amount";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr3 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count3 = tmpDTpaymentheader.Columns.Count - 1;
        //            _payoutAmount = Math.Round(_payoutAmount, 2);
        //            dr3[row_count3] = _payoutAmount.ToString();
        //            #endregion


        //            #region Receiving Count & Receiving Amount


        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Receiving Count";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr4 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count4 = tmpDTpaymentheader.Columns.Count - 1;

        //            dr4[row_count4] = _receiveCount;



        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Receiving Amount";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr5 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count5 = tmpDTpaymentheader.Columns.Count - 1;

        //            _receiveAmount = Math.Round(_receiveAmount, 2);
        //            dr5[row_count5] = _receiveAmount.ToString();

        //            #endregion

        //            #region Cashindrawer 


        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Cash in Drawer";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr6 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count6 = tmpDTpaymentheader.Columns.Count - 1;
        //            _cashindrawer = Math.Round(_cashindrawer, 2);
        //            dr6[row_count6] = _cashindrawer.ToString();





        //            #endregion

        //            #region  Rounding
        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Rounding";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr7 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count7 = tmpDTpaymentheader.Columns.Count - 1;

        //            _rounding = Math.Round(_rounding, 2);
        //            dr7[row_count7] = _rounding.ToString();

        //            #endregion

        //            #region Refund

        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Refund";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr8 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count8 = tmpDTpaymentheader.Columns.Count - 1;
        //            _refund = Math.Round(_refund, 2);
        //            dr8[row_count8] = _refund.ToString();

        //            #endregion

        //            #region ItemsDiscount
        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Item Discount";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr9 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count9 = tmpDTpaymentheader.Columns.Count - 1;
        //            _itemdiscount = Math.Round(_itemdiscount, 2);
        //            dr9[row_count9] = _itemdiscount.ToString();
        //            #endregion

        //            #region  Totaldiscount
        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Total Discount";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr10 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count10 = tmpDTpaymentheader.Columns.Count - 1;
        //            _totaldiscount = Math.Round(_totaldiscount, 2);
        //            dr10[row_count10] = _totaldiscount.ToString();

        //            #endregion

        //            #region  Memberdiscount
        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Member Total Disc Amount";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr11 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count11 = tmpDTpaymentheader.Columns.Count - 1;
        //            _memberdiscount = Math.Round(_memberdiscount, 2);
        //            dr11[row_count11] = _memberdiscount.ToString("c");

        //            #endregion

        //            #region TotalPriceshiftdiscount 
        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Total Price Shift Disc Amount";
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr12 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count12 = tmpDTpaymentheader.Columns.Count - 1;
        //            _priceshifdiscount = Math.Round(_priceshifdiscount, 2);
        //            dr12[row_count12] = _priceshifdiscount.ToString();
        //            #endregion

        //            #region  DiscountTotal 

        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Discount Total";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr13 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count13 = tmpDTpaymentheader.Columns.Count - 1;
        //            _discountTotal = Math.Round(_discountTotal, 2);
        //            dr13[row_count13] = _discountTotal.ToString();


        //            #endregion

        //            #region Tax3   - svc 

        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = _tax3;
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr14 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count14 = tmpDTpaymentheader.Columns.Count - 1;
        //            _tax3_amount = Math.Round(_tax3_amount, 2);
        //            dr14[row_count14] = _tax3_amount.ToString();

        //            #endregion

        //            #region Tax2  - gst 

        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = _tax2;
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr15 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count15 = tmpDTpaymentheader.Columns.Count - 1;
        //            _tax2_amount = Math.Round(_tax2_amount, 2);
        //            dr15[row_count15] = _tax2_amount.ToString();

        //            #endregion

        //            #region Tax1  

        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = _tax1;
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr16 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count16 = tmpDTpaymentheader.Columns.Count - 1;
        //            _tax1_amount = Math.Round(_tax1_amount, 2);
        //            dr16[row_count16] = _tax1_amount.ToString();

        //            #endregion
        //            #region NetSale

        //            // Add the new column

        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "NetSale ";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr17 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count17 = tmpDTpaymentheader.Columns.Count - 1;
        //            _netsale = Math.Round(_netsale, 2);
        //            dr17[row_count17] = _netsale.ToString();

        //            #endregion


        //            #region HoldCount


        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Hold Count";
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr18 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count18 = tmpDTpaymentheader.Columns.Count - 1;
        //            dr18[row_count18] = _holdCount;

        //            #endregion


        //            #region  HoldAmount
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Hold Amount ";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr19 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count19 = tmpDTpaymentheader.Columns.Count - 1;
        //            _holdAmount = Math.Round(_holdAmount, 2);
        //            dr19[row_count19] = _holdAmount.ToString();

        //            #endregion


        //            #region HoldCount


        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Cancel Count";
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr20 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count20 = tmpDTpaymentheader.Columns.Count - 1;
        //            dr20[row_count20] = _cancelcount;

        //            #endregion


        //            #region  Cancel Amount
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Cancel Amount ";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr21 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count21 = tmpDTpaymentheader.Columns.Count - 1;
        //            _cancelamount = Math.Round(_cancelamount, 2);
        //            dr21[row_count21] = _cancelamount.ToString();

        //            #endregion


        //            #region VoidCount


        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Void Count";
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr22 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count22 = tmpDTpaymentheader.Columns.Count - 1;
        //            dr22[row_count22] = _voidcount;

        //            #endregion


        //            #region  Void Amount
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Void Amount ";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr23 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count23 = tmpDTpaymentheader.Columns.Count - 1;
        //            _Voidamount = Math.Round(_Voidamount, 2);
        //            dr23[row_count23] = _Voidamount.ToString();

        //            #endregion

        //            #region  Exchange Amount
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Exchange Amount ";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr24 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count24 = tmpDTpaymentheader.Columns.Count - 1;
        //            _ExchangeAmount = Math.Round(_ExchangeAmount, 2);
        //            dr24[row_count24] = _ExchangeAmount.ToString();

        //            #endregion



        //            #region Nosalesopen


        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "NoSales Open";
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr25 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count25 = tmpDTpaymentheader.Columns.Count - 1;
        //            dr25[row_count25] = _noofsalesopen;

        //            #endregion

        //            #region ReceiptCount


        //            // Add the new column
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Receipt Count";
        //            dtColumn.ReadOnly = false;
        //            // Add the row value 
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            DataRow dr26 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count26 = tmpDTpaymentheader.Columns.Count - 1;
        //            dr26[row_count26] = _receiptcount;

        //            #endregion

        //            #region  ReceiptAverage 
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Receipt Average ";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr27 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count27 = tmpDTpaymentheader.Columns.Count - 1;
        //            _receiptAvg = Math.Round(_receiptAvg, 2);
        //            dr27[row_count27] = _receiptAvg.ToString();

        //            #endregion

        //            #region  voucherAmount
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Voucher Amount ";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr28 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count28 = tmpDTpaymentheader.Columns.Count - 1;
        //            _vocheramount = Math.Round(_vocheramount, 2);
        //            dr28[row_count28] = _vocheramount.ToString();

        //            #endregion

        //            #region  GrossAmount
        //            dtColumn = new DataColumn();
        //            dtColumn.DataType = typeof(string);
        //            dtColumn.ColumnName = "Gross Amount ";
        //            dtColumn.ReadOnly = false;
        //            tmpDTpaymentheader.Columns.Add(dtColumn);
        //            // Add the row value 
        //            DataRow dr29 = tmpDTpaymentheader.Rows[0];
        //            // Get the row count 
        //            int row_count29 = tmpDTpaymentheader.Columns.Count - 1;
        //            _grossamount = Math.Round(_grossamount, 2);
        //            dr29[row_count29] = _grossamount.ToString();

        //            #endregion
        //        }

        //        tmpDTpaymentheader.AcceptChanges();

        //    }
        //    // Assing to Excel Report 
        //    GridView GridView1 = new GridView();
        //    GridView1.AllowPaging = false;
        //    //GridView1.DataSource = Session["ResultDTExcel"];
        //    GridView1.DataSource = tmpDTpaymentheader;
        //    GridView1.DataBind();
        //    Response.Clear();
        //    Response.Buffer = true;
        //    Response.AddHeader("content-disposition",
        //        "attachment;filename=SummaryExport005.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();
        //    HttpContext.Current.Response.End();







        //}



        public void BusinessdatewiseSummaryExcelReport(string DB, string D1, string D2)
        {
            // --- Create the main DataTable ---
            DataTable tmpDTpaymentheader = new DataTable("paymentheader");

            // --- Get summary rows first ---
            DataTable DT = ObjMain.GetData_DT($@"
        SELECT  
            BusinessDate,
            SUM(TotalSalesAmount) AS TotalSalesAmount,
            SUM(PayOutCount) AS PayOutCount,
            SUM(PayOutAmount) AS PayOutAmount,
            SUM(Receivecount) AS Receivecount,
            SUM(ReceiveAmount) AS ReceiveAmount,
            SUM(CashinDrawer+ReceiveAmount-PayOutAmount) AS CashinDrawer,
            SUM(Rounding) AS Rounding,
            SUM(Refund) AS Refund,
            SUM(ItemDiscount) AS ItemDiscount,
            SUM(TotalDiscount) AS TotalDiscount,
            SUM(TotalPriceshiftDiscount) AS TotalPriceshiftDiscount,
            SUM(MemDiscAmt) AS MemDiscAmt,
            Tax3, SUM(Tax3Total) AS Tax3Total,
            Tax2, SUM(Tax2Total) AS Tax2Total,
            Tax1, SUM(Tax1Total) AS Tax1Total,
            SUM(NetSale) AS NetSale,
            SUM(VoucherAmount) AS VoucherAmount,
            SUM(ReceiptsCount) AS ReceiptsCount,
            AVG(ReceiptAverage) AS ReceiptAverage,
            SUM(discountTotal) AS DiscountTotal,
            SUM(TotalSalesAmount + Rounding + discountTotal - Refund - VoucherAmount) as GrossAmount
        FROM {DB}.summarydetails_light
        WHERE BusinessDate BETWEEN '{D1}' AND '{D2}'
        GROUP BY BusinessDate, Tax1, Tax2, Tax3;
    ");

            if (DT.Rows.Count == 0)
                return;

            // --- Helper for adding value safely ---
            void AddValue(DataRow row, string colName, object value)
            {
                if (!tmpDTpaymentheader.Columns.Contains(colName))
                    tmpDTpaymentheader.Columns.Add(colName, typeof(string));

                row[colName] = value?.ToString() ?? "";
            }

            // --- Loop through Business Dates ---
            foreach (DataRow summaryRow in DT.Rows)
            {
                DataRow newRow = tmpDTpaymentheader.NewRow();

                string businessDate = summaryRow["BusinessDate"].ToString();

                // Add static summary values
                AddValue(newRow, "BusinessDate", businessDate);
                AddValue(newRow, "Total Sales", summaryRow["TotalSalesAmount"]);
                AddValue(newRow, "PayOut Count", summaryRow["PayOutCount"]);
                AddValue(newRow, "PayOut Amount", summaryRow["PayOutAmount"]);
                AddValue(newRow, "Receiving Count", summaryRow["Receivecount"]);
                AddValue(newRow, "Receiving Amount", summaryRow["ReceiveAmount"]);
                AddValue(newRow, "Cash in Drawer", summaryRow["CashinDrawer"]);
                AddValue(newRow, "Rounding", summaryRow["Rounding"]);
                AddValue(newRow, "Refund", summaryRow["Refund"]);
                AddValue(newRow, "Item Discount", summaryRow["ItemDiscount"]);
                AddValue(newRow, "Total Discount", summaryRow["TotalDiscount"]);
                AddValue(newRow, "Member Discount", summaryRow["MemDiscAmt"]);
                AddValue(newRow, "Gross Amount", summaryRow["GrossAmount"]);
                AddValue(newRow, "Voucher Amount", summaryRow["VoucherAmount"]);
                AddValue(newRow, "Net Sale", summaryRow["NetSale"]);

                // --- Load Pivot JSON (dynamic payments) ---
                var combined = new Dictionary<string, PaymentSummaryItem>();

                using (var conn = new MySqlConnection(ConStr))
                {
                    conn.Open();
                    string query = $@"SELECT PivotResult FROM {DB}.paymentpivotresultsjson_light 
                              WHERE BusinessDate = @BusinessDate";

                    using (var cmd = new MySqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@BusinessDate", businessDate);
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string json = reader["PivotResult"].ToString();

                                var jsonArray = JsonConvert.DeserializeObject<List<Dictionary<string, PaymentSummaryItem>>>(json);

                                foreach (var dayObj in jsonArray)
                                {
                                    foreach (var kvp in dayObj)
                                    {
                                        if (!combined.ContainsKey(kvp.Key))
                                            combined[kvp.Key] = kvp.Value;
                                        else
                                        {
                                            combined[kvp.Key].Count += kvp.Value.Count;
                                            combined[kvp.Key].Amount += kvp.Value.Amount;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                // --- Add dynamic payment columns (flattened) ---
                foreach (var kvp in combined)
                {
                    string name = kvp.Key;
                    AddValue(newRow, $"{name}_Count", kvp.Value.Count);
                    AddValue(newRow, $"{name}_Amount", kvp.Value.Amount.ToString("F2"));
                }

                tmpDTpaymentheader.Rows.Add(newRow);
            }

            tmpDTpaymentheader.AcceptChanges();

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

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=SummaryExport.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 CreateLinechardata(string db, string D1, string D2)
        {
            DataTable Dtchartdata = new DataTable();
            Dtchartdata = ObjMain.GetData_DT("Select Totalsalesamount as TotalSales , DATE_FORMAT(date(Businessdate),'%d-%b-%y') as PayDate  , date(Businessdate) as Date from  " + db + ".summarydetails_light  "
            + "  WHERE date(Businessdate) between '" + D1 + "' AND '" + D2 + "' ; ");

            Session["DtLinechart"] = Dtchartdata;

            chart_bind();

        }


        //private void chart_bind()
        //{
        //    DataTable dt = new DataTable();
        //    dt = (DataTable)Session["DtLinechart"];

        //    try
        //    {




        //        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', 'PayDate');
        //    data.addColumn('number', 'TotalSales');

        //    data.addRows(" + dt.Rows.Count + ");");

        //        Int32 i;

        //        for (i = 0; i <= dt.Rows.Count - 1; i++)
        //        {
        //            str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["PayDate"].ToString() + "');");
        //            str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["TotalSales"].ToString() + ") ;");

        //        }

        //        str.Append("var chart = new google.visualization.LineChart(document.getElementById('chart_div1'));");
        //        str.Append(" chart.draw(data, { title: 'Total Sales Till Date',");
        //        //str.Append("hAxis: {title: 'Year', titleTextStyle: {color: 'green'}}");
        //        str.Append("}); }");
        //        str.Append("$(window).resize(function(){ drawChart1();});");
        //        str.Append("</script>");
        //        lt1.Text = str.ToString().TrimEnd(',').Replace('*', '"');
        //    }
        //    catch
        //    { }
        //}

        private void chart_bind()
        {
            DataTable dt = (DataTable)Session["DtLinechart"];
            if (dt == null || dt.Rows.Count == 0) return;

            try
            {
                StringBuilder str = new StringBuilder();

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

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

                        str.Append(@"var chart = new google.visualization.LineChart(document.getElementById('chart_div1'));
                    chart.draw(data, { title: 'Total Sales Till Date', curveType: 'function', legend: { position: 'bottom' } });
                }
                </script>");



                System.Diagnostics.Debug.WriteLine("Generated Chart Script: " + str.ToString());

                // ✅ This ensures the script executes after postback
                ClientScript.RegisterStartupScript(this.GetType(), "drawChart1", str.ToString(), false);
            }
            catch (Exception ex)
            {
                // optional logging
            }
        }






        // Model for PaymentType
        public class PaymentSummaryItem
        {
            public string PaymentMethod { get; set; }
            public int Count { get; set; }
            public decimal Amount { get; set; }
        }

        protected void btnExcel_Click(object sender, EventArgs e)
        {
            #region  Validation Between HQ Branch and Normal Branch 
            BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
            if (BranchFullName == "HQ")
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Cmb_BranchName;

            }
            #endregion


            ConsolidatedSummaryExcelReport(BranchAlias, txtStartDt.Text, txtEndDt.Text);


        }

        protected void btnDatawiseExport_Click(object sender, EventArgs e)
        {
            #region  Validation Between HQ Branch and Normal Branch 
            BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
            if (BranchFullName == "HQ")
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Cmb_BranchName;

            }
            #endregion
            DataTable DtResult_datewise= GetCombinedReportByDate(ConStr, BranchAlias, txtStartDt.Text, txtEndDt.Text);




            DtResult_datewise.AcceptChanges();

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

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=SummaryDatewiseExport.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 DataTable GetCombinedReportByDate(string ConStr, string DB, string D1, string D2)
        {
            // ✅ Step 1: Prepare result table
            DataTable dt = new DataTable("CombinedReport");
            dt.Columns.Add("BusinessDate", typeof(string));

            // We’ll dynamically add payment + summary columns later

            // ✅ Step 2: Get all summary data (grouped by BusinessDate)
            var summaries = new Dictionary<string, Dictionary<string, object>>();

            using (var conn = new MySqlConnection(ConStr))
            {
                conn.Open();

                string summaryQuery = $@"
            SELECT 
                DATE_FORMAT(BusinessDate, '%Y-%m-%d') AS BusinessDate,
                SUM(TotalSalesAmount) AS TotalSalesAmount,
                SUM(PayOutCount) AS PayOutCount,
                SUM(PayOutAmount) AS PayOutAmount,
                SUM(Receivecount) AS Receivecount,
                SUM(ReceiveAmount) AS ReceiveAmount,
                SUM(CashinDrawer+ReceiveAmount-PayOutAmount) AS CashinDrawer,
                SUM(Rounding) AS Rounding,
                SUM(Refund) AS Refund,
                SUM(ItemDiscount) AS ItemDiscount,
                SUM(TotalDiscount) AS TotalDiscount,
                SUM(TotalPriceshiftDiscount) AS TotalPriceshiftDiscount,
                SUM(MemDiscAmt) AS MemDiscAmt,
                SUM(Tax1Total) AS Tax1Total,
                SUM(Tax2Total) AS Tax2Total,
                SUM(Tax3Total) AS Tax3Total,
                SUM(NetSale) AS NetSale,
                SUM(HoldCount) AS HoldCount,
                SUM(HoldAmount) AS HoldAmount,
                SUM(CancelCount) AS CancelCount,
                SUM(CancelAmount) AS CancelAmount,
                SUM(VoidCount) AS VoidCount,
                SUM(VoidAmount) AS VoidAmount,
                SUM(ExchangeAmount) AS ExchangeAmount,
                SUM(NoSalesOpen) AS NoSalesOpen,
                SUM(ReceiptsCount) AS ReceiptsCount,
                AVG(ReceiptAverage) AS ReceiptAverage,
                SUM(VoucherAmount) AS VoucherAmount,
                SUM(DiscountTotal) AS DiscountTotal,
                sum(TotalSalesAmount+Rounding+discountTotal-Refund-VoucherAmount ) as GrossAmount
            FROM {DB}.summarydetails_light
            WHERE BusinessDate BETWEEN @D1 AND @D2
            GROUP BY BusinessDate";

                using (var cmd = new MySqlCommand(summaryQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@D1", D1);
                    cmd.Parameters.AddWithValue("@D2", D2);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var dict = new Dictionary<string, object>();
                            for (int i = 1; i < reader.FieldCount; i++) // skip BusinessDate (index 0)
                            {
                                dict[reader.GetName(i)] = reader.IsDBNull(i) ? 0 : reader.GetValue(i);
                            }
                            summaries[reader["BusinessDate"].ToString()] = dict;
                        }
                    }
                }

                // ✅ Step 3: Get payment data per BusinessDate
                string paymentQuery = $@"
            SELECT DATE_FORMAT(BusinessDate, '%Y-%m-%d') AS BusinessDate, PivotResult, LastUpdate
            FROM {DB}.paymentpivotresultsjson_light
            WHERE Businessdate BETWEEN @D1 AND @D2";

                using (var cmd = new MySqlCommand(paymentQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@D1", D1);
                    cmd.Parameters.AddWithValue("@D2", D2);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                           string  businessDate = reader["BusinessDate"].ToString();

                            string json = reader["PivotResult"].ToString();
                            if (string.IsNullOrWhiteSpace(json))
                                continue;

                            var combined = new Dictionary<string, PaymentSummaryItem>();

                            try
                            {
                                var token = JToken.Parse(json);

                                if (token.Type == JTokenType.Array)
                                {
                                    var jsonArray = token.ToObject<List<Dictionary<string, PaymentSummaryItem>>>();
                                    foreach (var dayObj in jsonArray)
                                    {
                                        foreach (var kvp in dayObj)
                                            AddOrUpdate(combined, kvp.Key, kvp.Value);
                                    }
                                }
                                else if (token.Type == JTokenType.Object)
                                {
                                    var jsonObj = token.ToObject<Dictionary<string, PaymentSummaryItem>>();
                                    foreach (var kvp in jsonObj)
                                        AddOrUpdate(combined, kvp.Key, kvp.Value);
                                }
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine($"JSON parse error: {ex.Message}");
                            }

                            // ✅ Build row for this BusinessDate
                            DataRow dr = dt.NewRow();
                            dr["BusinessDate"] = businessDate;

                            // Add payment columns dynamically if needed
                            foreach (var kvp in combined)
                            {
                                string safeName = MakeSafeColumnName(kvp.Key);
                                string countCol = $"{safeName}_Count";
                                string amountCol = $"{safeName}_Amount";

                                if (!dt.Columns.Contains(countCol))
                                    dt.Columns.Add(countCol, typeof(int));
                                if (!dt.Columns.Contains(amountCol))
                                    dt.Columns.Add(amountCol, typeof(decimal));

                                dr[countCol] = kvp.Value.Count;
                                dr[amountCol] = kvp.Value.Amount;
                            }

                            // Merge summary totals (if available)
                            if (summaries.ContainsKey(businessDate))
                            {
                                foreach (var kv in summaries[businessDate])
                                {
                                    if (!dt.Columns.Contains(kv.Key))
                                        dt.Columns.Add(kv.Key, typeof(decimal));
                                    dr[kv.Key] = kv.Value;
                                }
                            }

                            dt.Rows.Add(dr);
                        }
                    }
                }
            }

            return dt;
        }

        private static void AddOrUpdate(Dictionary<string, PaymentSummaryItem> combined, string key, PaymentSummaryItem value)
        {
            if (!combined.ContainsKey(key))
            {
                combined[key] = new PaymentSummaryItem
                {
                    PaymentMethod = key,
                    Count = value.Count,
                    Amount = value.Amount
                };
            }
            else
            {
                combined[key].Count += value.Count;
                combined[key].Amount += value.Amount;
            }
        }

        private static string MakeSafeColumnName(string method)
        {
            return method.Replace(" ", "_")
                         .Replace("-", "_")
                         .Replace("/", "_")
                         .Replace(".", "_")
                         .Trim();
        }



    }


}


