using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using CLS_BL;
using CLS_DL;
using CLS_PL;
using System.Data;
using System.Web.UI.DataVisualization.Charting;
using InfoSoftGlobal;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using System.IO;
using Newtonsoft.Json;
using System.Web.UI.HtmlControls;
using System.Web.Services;

namespace SUNWEB
{
    public partial class SummaryReport : 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 connectionstring = ConfigurationManager.ConnectionStrings["ConMysql"].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;
        MYSQLDB ObjMain = new MYSQLDB();
        MYSQLCLOUD ObjCloud = new MYSQLCLOUD();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable Dtt = new DataTable();
        DataTable DtLinechart = new DataTable();
        DataTable DtLinechartNew = new DataTable();
        JSONTODATATABLE objConvertJson2dataTable = new JSONTODATATABLE();
        // User Variables 
        string cd_BranchAlias = string.Empty;
      
        string Useridvalue = string.Empty;
        string ActiveCallvalue = string.Empty;
        // Licensing Variables
        string QueryCurr_Select = string.Empty;
        string Rcd_id = string.Empty;
        string RPortalid = string.Empty;
        StringBuilder str = new StringBuilder();
      
        string L_SummarySales = string.Empty;
      
        DataTable DTTemp = new DataTable();
        string Branchidval = string.Empty;
        string ISHQ = 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;
        // Retail Variables 
        decimal Tot_Sales;
        
        string PayoutCnt = string.Empty;
        decimal PayoutAmt;
        string ReceivingCnt = string.Empty;
        decimal ReceivingAmt;
        decimal Cashin_Cash;
        decimal Cashin_TakeRA;
        decimal Cashin_Po;
        decimal CshinDraw;
        decimal Rounding;
        decimal Refund;
        decimal ItemDisc;
        decimal TotDisc;
        decimal PriceShifDisc;
        decimal DistTOT;
        decimal GrossSales;
        decimal Amt;
        decimal Gstamt;
        decimal SvcAmt;
        decimal Tax3Amt;
        decimal MemDiscAmt;
        decimal NetAmt;
        decimal VoucherAmt;
        string Holdcnt;
        decimal HoldAmt;
        string CancelCnt;
        decimal CancelAmtVal;
        string VoidCnt;
        decimal VoidAmt;
        decimal ExAmt;
        string NoSalesopen;
        string NoofReceipts;
        decimal AvgReceipts;
        string Cmb_BranchName;
        // Lastweek Sale Detail 
        decimal LMon;
        string LDayName1;
        DataTable DtLMon = new DataTable();
        decimal LTue;
        string LDayName2;
        DataTable DtLTue = new DataTable();
        decimal LWed;
        string LDayName3;
        DataTable DtLWed = new DataTable();
        decimal LThu;
        string LDayName4;
        DataTable DtLThu = new DataTable();
        decimal LFri;
        string LDayName5;
        DataTable DtLFri = new DataTable();
        decimal LSat;
        string LDayName6;
        DataTable DtLSat = new DataTable();
        decimal LSun;
        string LDayName7;
        DataTable DtLSun = new DataTable();
        // Currentweek Sale Details 
        decimal CMon;
        string CDayName1;
        DataTable DtCMon = new DataTable();
        decimal CTue;
        string CDayName2;
        DataTable DtCTue = new DataTable();
        decimal CWed;
        string CDayName3;
        DataTable DtCWed = new DataTable();
        decimal CThu;
        string CDayName4;
        DataTable DtCThu = new DataTable();
        decimal CFri;
        string CDayName5;
        DataTable DtCFri = new DataTable();
        decimal CSat;
        string CDayName6;
        DataTable DtCSat = new DataTable();
        decimal CSun;
        string CDayName7;
        DataTable DtCSun = new DataTable();
        DataTable DtFinaldata = new DataTable();
        string GraphWidth = "650";
        string GraphHeight = "400";
        string[] color = new string[12];
        string QueryCal = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string LastUpdatedTM;
        string HQ = string.Empty;
        string Cm_id = string.Empty;

        string TaxNameRef = string.Empty;
        string RBranchid = string.Empty;
        string RBranchNam = string.Empty;
        string RData_Constring = string.Empty;
        string ChkUPT = string.Empty;
        string FinalQuery = string.Empty;
        string UTValue = string.Empty;
        string CurMonth = string.Empty;
        string ChkDashBoard = string.Empty;
        string PortalTypeId = string.Empty;
        string Datapath = string.Empty;
        string final = string.Empty;
        string jsonstring;
        protected void Page_Load(object sender, EventArgs e)
        {
           lblmonthlyerrormsg.Text = string.Empty;
           PID = "2";
           lblGST.Text=string.Empty;
           lblSVC.Text = string.Empty;
           lblTax3.Text = string.Empty;

            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;

            // Removed this Condition 
            //ChkDashBoard = ObjMain.ExecuteScalar("SELECT * FROM information_schema.tables WHERE table_schema = '" + Session["Branch_HQ"].ToString() + "' AND table_name = 'DashboardCount' LIMIT 1;");

            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {


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

                    if (Request.QueryString["QBranchNam"] != null)
                    {
                        System.Threading.Thread.Sleep(2000);
                        cd_BranchAlias = Request.QueryString["QBranchNam"].ToString();
                        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 )
                        LoadBranchName4SuperAdmin();
                        cmbBranches.Items.FindByText(Curr_BranchFullName).Selected = true;
                        BranchWiseInfo(cd_BranchAlias);
                        // Call LastUpdated Value: 
                        string QryUpdateTM = "SELECT DATE_FORMAT(TransTime, '%d-%m-%Y %H:%i:%s') as TransTime FROM " + cd_BranchAlias + ".trans ORDER BY TransID DESC LIMIT 1";
                        LastUpdatedTM = ObjMain.ExecuteScalar(QryUpdateTM);
                        lblLastUpdatedTM.Text = " Last Updated : " + LastUpdatedTM;

                        Useridvalue = Session["UserId"].ToString();
                        QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + cd_BranchAlias + "' where Userid=" + Useridvalue + "";
                        S = ObjMain.ExecuteNonQuery(QueryCurr_Select);

                    }
                    else if (string.IsNullOrEmpty(ActiveCallvalue) != true || ActiveCallvalue != "Select Branch")
                    {
                        LoadBranchName4SuperAdmin();
                        ActiveCall();

                    }
                    else
                    {
                        PortalTypeId = ObjMain.ExecuteScalar("select  portaltypeid from portaldb.invoicedetails as I inner join  portaldb.companydetails as C on I.cd_id=C.cd_id  where cd_branchalias='" + cmbBranches.SelectedItem.Text + "';");
                        cd_BranchAlias = cmbBranches.SelectedItem.Text;



                    }

                }

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


                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();

                    if (Request.QueryString["QBranchNam"] != null)
                    {
                        //System.Threading.Thread.Sleep(2000);
                        cd_BranchAlias = Request.QueryString["QBranchNam"].ToString();

                        LoadBranchName4SuperAdmin();

                        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;
                        BranchWiseInfo(cd_BranchAlias);

                        string QryUpdateTM = "SELECT DATE_FORMAT(TransTime, '%d-%M-%Y %H:%i:%s') as TransTime FROM " + cd_BranchAlias + ".trans ORDER BY TransID DESC LIMIT 1";
                        LastUpdatedTM = ObjMain.ExecuteScalar(QryUpdateTM);
                        lblLastUpdatedTM.Text = " Last Updated : " + LastUpdatedTM;


                        Useridvalue = Session["UserId"].ToString();
                        QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + cd_BranchAlias + "' where Userid=" + Useridvalue + "";
                        S = ObjMain.ExecuteNonQuery(QueryCurr_Select);

                    }

                    else if (string.IsNullOrEmpty(ActiveCallvalue) != true || ActiveCallvalue != "Select Branch")
                    {
                        LoadBranchName4SuperAdmin();
                        ActiveCall();

                    }
                    else
                    {
                        PortalTypeId = ObjMain.ExecuteScalar("select  portaltypeid from portaldb.invoicedetails as I inner join  portaldb.companydetails as C on I.cd_id=C.cd_id  where cd_branchalias='" + cmbBranches.SelectedItem.Text + "';");
                        cd_BranchAlias = cmbBranches.SelectedItem.Text;
                    }

                }


            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"].ToString())) == true) && (Session["PortalTypeId"].ToString() == ""))
            {



                if (!IsPostBack)
                {
                    LoadBranchName();
                    if (Request.QueryString["QBranchNam"] != null)
                    {

                        cd_BranchAlias = Request.QueryString["QBranchNam"].ToString();

                        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;
                        BranchWiseInfo(cd_BranchAlias);
                        string QryUpdateTM = "SELECT DATE_FORMAT(TransTime, '%d-%M-%Y %H:%i:%s') as TransTime FROM " + cd_BranchAlias + ".trans ORDER BY TransID DESC LIMIT 1";
                        LastUpdatedTM = ObjMain.ExecuteScalar(QryUpdateTM);
                        lblLastUpdatedTM.Text = " Last Updated : " + LastUpdatedTM;


                        Useridvalue = Session["UserId"].ToString();
                        QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + cd_BranchAlias + "' where Userid=" + Useridvalue + "";
                        S = ObjMain.ExecuteNonQuery(QueryCurr_Select);

                    }

                    else if (string.IsNullOrEmpty(ActiveCallvalue) != true || ActiveCallvalue != "Select Branch")
                    {
                        LoadBranchName();
                        ActiveCall();

                    }
                    else
                    {
                        Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), ActiveCallvalue);
                        Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                        PortalTypeId = ObjMain.ExecuteScalar("select  portaltypeid from portaldb.invoicedetails as I inner join  portaldb.companydetails as C on I.cd_id=C.cd_id  where cd_branchalias='" + Cmb_BranchName + "';");
                        cd_BranchAlias = Cmb_BranchName;

                    }

                }

            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"].ToString())) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"].ToString())) == false))
            {
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;
                cd_BranchAlias = Session["Branch_HQ"].ToString();

                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 = "Welcome" + "&nbsp;&nbsp;" + Curr_BranchFullName + "&nbsp; !";
                Datapath = Session["DataPath"].ToString();
                PortalTypeId = Session["PortalTypeId"].ToString();


                DT = ObjUserBL.GetPageRightsDetailsPid(Session["UserId"].ToString(), PID);
                if (DT.Rows.Count > 0)
                {

                    // Prevent XSS Attack

                    string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text);
                    string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text);

                    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;
                    // Binding Report Functions 
                    LoadTestRpt(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadTotSale(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);

                   // LoadSalesAmt1(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadPayouts(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadReceving(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadCashindrawer(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadRounding(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadRefund(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    // LoadMonthlySale(cd_BranchAlias, Convert.ToString(MonthstartDt), Curr_DateOnly);
                    LoadItemDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadTotDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadTotPriceShiftDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadDiscountTOT();
                    LoadGrossAmount();
                    LoadGSTAmount(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadSVCAmount(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    Netmount();
                    LoadVoucher(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadHoldCnt(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadCancel(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadVoid(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadExchange(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadNoSalesPersonOpen(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadNoofReceipts(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                    LoadAvgReceipts();

                    // Disable Selection Charts : Chart2 and Chart3 and Char 4 

                    LastWCurrWSale(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias));
                    LoadLineChartData(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), Convert.ToString(lblLinechartStdt.Text), Curr_DateOnly);


                    string QryUpdateTM = "SELECT DATE_FORMAT(TransTime, '%d-%M-%Y %H:%i:%s') as TransTime FROM " + cd_BranchAlias + ".trans ORDER BY TransID DESC LIMIT 1";
                    LastUpdatedTM = ObjMain.ExecuteScalar(QryUpdateTM);
                    lblLastUpdatedTM.Text = " Last Updated : " + LastUpdatedTM;

                }

                if ((Session["L_SummarySales"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {

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

                    }


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



                }

            }
            else
            {

            }
            {

            }

             // save the querystring values


            if (cd_BranchAlias != "")
            {

                Useridvalue = Session["UserId"].ToString();
                QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + cd_BranchAlias + "' where Userid=" + Useridvalue + "";
                S = ObjMain.ExecuteNonQuery(QueryCurr_Select);

            }

            else
            {
                Useridvalue = Session["UserId"].ToString();
                QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
                S = ObjMain.ExecuteNonQuery(QueryCurr_Select);

            }
        }


        public void UPTMonthlySatus()
        {
            string StatusValue = "U";
            string UPTQuery451 = "UPDATE Portaldb.Usermaster  set MonthlyUPT='" + StatusValue + "' ,  MonthlyUPTDT='" + Curr_DateOnly + "' WHERE Userid=" + Session["UserId"].ToString() + "  ";
            K = ObjMain.ExecuteNonQuery(UPTQuery451);
        }
        public void ActiveCall()
        {
            string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
            ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
            if (ActiveCallvalue == "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch7(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            }


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

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

            Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), ActiveCallvalue);
            Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
            Session["Curr_BranchName"] = Curr_BranchFullName;
            ActiveCallvalue = Cmb_BranchName;
            cmbBranches.Items.FindByText(Curr_BranchFullName).Selected = true;


            Branchidval = ObjUserBL.GetBranchIDbyName(ActiveCallvalue, ActiveCallvalue);
            if (Branchidval != "")
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            }
            else
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Session["Fistbranch"].ToString());

            }


            if (DT.Rows.Count > 0)
            {
                Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();

                Session["Page_Acess"] = Page_Acess;
                Session["Read_Acess"] = Read_Acess;
                Session["Write_Acess"] = Write_Acess;
                Session["Isdowld_Acess"] = Isdowld_Acess;
            }

            Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + cmbBranches.SelectedItem.Value + " and cd_branchalias='" + ActiveCallvalue + "';");
            RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
            DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);

            if (DtLicense.Rows.Count > 0)
            {
                L_SummarySales = DtLicense.Rows[0]["SUMMARYSALES"].ToString();
                Session["L_SummarySales"] = L_SummarySales;
            }

            if ((Session["L_SummarySales"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
            {
                Response.Redirect("DeniedPage.aspx");
            }

            else
            {
                // Prevent XSS Attack

                string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text);
                string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text);

                // lblBranchAliasName.Text = "Welcome" + "&nbsp;&nbsp;" + ActiveCallvalue + "&nbsp; !";
                lblMsgTotalSelection.Text = "Summary Reports Selection Between " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                LoadTestRpt(ActiveCallvalue, StartDT, EndDt);
                LoadTotSale(ActiveCallvalue, StartDT, EndDt);
                // LoadMonthlySale(cd_BranchAlias, Convert.ToString(MonthstartDt), Curr_DateOnly);
               // LoadSalesAmt1(ActiveCallvalue, StartDT, EndDt);
                LoadPayouts(ActiveCallvalue, StartDT, EndDt);
                LoadReceving(ActiveCallvalue, StartDT, EndDt);
                LoadCashindrawer(ActiveCallvalue, StartDT, EndDt);
                LoadRounding(ActiveCallvalue, StartDT, EndDt);
                LoadRefund(ActiveCallvalue, StartDT, EndDt);

                LoadItemDisc(ActiveCallvalue, StartDT, EndDt);
                LoadTotDisc(ActiveCallvalue, StartDT, EndDt);
                LoadTotPriceShiftDisc(ActiveCallvalue, StartDT, EndDt);
                LoadDiscountTOT();
                LoadGrossAmount();
                LoadGSTAmount(ActiveCallvalue, StartDT, EndDt);
                LoadSVCAmount(ActiveCallvalue, StartDT, EndDt);
                LoadTax3(ActiveCallvalue, StartDT, EndDt);
                Netmount();
                LoadVoucher(ActiveCallvalue, StartDT, EndDt);
                LoadHoldCnt(ActiveCallvalue, StartDT, EndDt);
                LoadCancel(ActiveCallvalue, StartDT, EndDt);
                LoadVoid(ActiveCallvalue, StartDT, EndDt);
                LoadExchange(ActiveCallvalue, StartDT, EndDt);
                LoadNoSalesPersonOpen(ActiveCallvalue, StartDT, EndDt);
                LoadNoofReceipts(ActiveCallvalue, StartDT, EndDt);
                LoadAvgReceipts();
                LastWCurrWSale(ActiveCallvalue);
                LoadLineChartData(ActiveCallvalue, Convert.ToString(Microsoft.Security.Application.Encoder.HtmlEncode(lblLinechartStdt.Text)), Microsoft.Security.Application.Encoder.HtmlEncode(Curr_DateOnly));
                //System.Threading.Thread.Sleep(2000);

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

                }

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

            }

        }

        /// <summary>
        /// <para>This Fuction Update the Current Month TotalSale Count </para>
        /// <para>Its Return Every time this Page Load  </para>
        /// </summary>
        /// 
        /*
        public void LoadTotalSale_CurrentMths()
        {

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            DT = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            if (DT.Rows.Count > 0)
            {
                StringBuilder StrBObj = new StringBuilder();

                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    DataRow dr = DT.Rows[i];
                    RBranchid = dr["Branchid"].ToString();
                    RBranchNam = dr["BranchName"].ToString();
                    RData_Constring = dr["Datapath"].ToString();
                   

                    if (StrBObj.Length != 0)
                    {

                        StrBObj.AppendLine(" Union All ");
                        StrBObj.AppendLine(" (select  Monthname(tp.BusinessDate) as Month,sum(tp.PaymentAmount)  as Totalsales from " + RBranchNam + ".transpayment tp left join " + RBranchNam + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + Microsoft.Security.Application.Encoder.HtmlEncode( YearAlone) + "  and  monthname(tp.BusinessDate)=MonthName('" + Microsoft.Security.Application.Encoder.HtmlEncode(Curr_DateOnly) + "'))  ");

                    }
                    else
                    {
                        StrBObj.AppendLine(" select   sum(H.Totalsales)  as Totalsales , H.Month   from ( "
                        + " ( select  Monthname(tp.BusinessDate) as Month,sum(tp.PaymentAmount)  as Totalsales from " + RBranchNam + ".transpayment tp left join " + RBranchNam + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + YearAlone + "  and  monthname(tp.BusinessDate)=MonthName('" + Microsoft.Security.Application.Encoder.HtmlEncode(Curr_DateOnly) + "'))  ");

                    }

                }
                StrBObj.AppendLine(" ) as H Group by H.Month ;");

                FinalQuery = StrBObj.ToString();

                DTTemp = ObjMain.GetData_DT(FinalQuery);
                if (DTTemp.Rows.Count > 0)
                {
                    string Monthss = DTTemp.Rows[0]["Month"].ToString();
                    string TotalSaless = DTTemp.Rows[0]["Totalsales"].ToString();

                    if (Monthss != "" && TotalSaless != "")
                    {
                        string NewMonth = Monthss.Substring(0, 3);
                        K = ObjMain.ExecuteNonQuery("Update  portaldb.dashcount  set TotalsalesAmt=" + TotalSaless + " where Month='" + NewMonth + "' AND Userid=" + Session["UserId"].ToString() + " ");
                    }



                }



            }

        }
      */

        public void BranchWiseInfo(string cd_BranchAlia)
        {


            Datapath = Session["DataPath"].ToString();

            PID = "2"; // This is For RetailsDashboard 

            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;
                // Binding Report Functions 

                // Prevent XSS Attack
                string New_Curr_DateOnly = Microsoft.Security.Application.Encoder.HtmlEncode(Curr_DateOnly);


                LoadTestRpt(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadTotSale(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);

               // LoadSalesAmt1(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadPayouts(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadReceving(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadCashindrawer(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadRounding(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadRefund(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);

                LoadItemDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadTotDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadTotPriceShiftDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadDiscountTOT();
                LoadGrossAmount();
                LoadGSTAmount(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadSVCAmount(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                Netmount();
                LoadVoucher(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadHoldCnt(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadCancel(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadVoid(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadExchange(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), Curr_DateOnly, Curr_DateOnly);
                LoadNoSalesPersonOpen(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadNoofReceipts(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), New_Curr_DateOnly, New_Curr_DateOnly);
                LoadAvgReceipts();

                // Disable Selection Charts : Chart2 and Chart3 and Char 4 
                LastWCurrWSale(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia));
                LoadLineChartData(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlia), Convert.ToString(lblLinechartStdt.Text), Curr_DateOnly);
            }

        }

        /// <summary>
        ///  This Function For Count  Compare B/W Previous Week & Current Week
        /// </summary>
        /// <param name="BranchAlias"></param>
        public void LastWCurrWSale(string BranchAlias)
        {
            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach 

            // Lastweek Value : On Sale 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();
                    string LMonQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekmon,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate()- interval 7 day)  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Monday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";

                    string newquery = string.Format(LMonQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery, con);
                    MySqlDataAdapter AdaL1 = new MySqlDataAdapter();
                    AdaL1.SelectCommand = cmd;
                    AdaL1.Fill(DtLMon);
                    if (DtLMon.Rows.Count > 0)
                    {
                        LMon = Convert.ToDecimal(DtLMon.Rows[0]["lastweekmon"].ToString());
                        LDayName1 = DtLMon.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }



            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();
                    string LTueQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweektue,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate()- interval 7 day)  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Tuesday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery2 = string.Format(LTueQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery2, con);

                    MySqlDataAdapter AdaL2 = new MySqlDataAdapter();
                    AdaL2.SelectCommand = cmd;
                    AdaL2.Fill(DtLTue);
                    if (DtLTue.Rows.Count > 0)
                    {
                        LTue = Convert.ToDecimal(DtLTue.Rows[0]["lastweektue"].ToString());
                        LDayName2 = DtLTue.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();

                    string LWedQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekWed,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate()- interval 7 day)  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Wednesday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery3 = string.Format(LWedQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery3, con);
                    MySqlDataAdapter AdaL3 = new MySqlDataAdapter();
                    AdaL3.SelectCommand = cmd;
                    AdaL3.Fill(DtLWed);
                    if (DtLWed.Rows.Count > 0)
                    {
                        LWed = Convert.ToDecimal(DtLWed.Rows[0]["lastweekWed"].ToString());
                        LDayName3 = DtLWed.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }




            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();

                    string LThuQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekThurs,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate()- interval 7 day)  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Thursday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery4 = string.Format(LThuQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery4, con);
                    MySqlDataAdapter AdaL4 = new MySqlDataAdapter();
                    AdaL4.SelectCommand = cmd;
                    AdaL4.Fill(DtLThu);
                    if (DtLThu.Rows.Count > 0)
                    {
                        LThu = Convert.ToDecimal(DtLThu.Rows[0]["lastweekThurs"].ToString());
                        LDayName4 = DtLThu.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();

                    string LFriQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekFri,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate()- interval 7 day)  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Friday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery5 = string.Format(LFriQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery5, con);
                    MySqlDataAdapter AdaL5 = new MySqlDataAdapter();
                    AdaL5.SelectCommand = cmd;
                    AdaL5.Fill(DtLFri);
                    if (DtLFri.Rows.Count > 0)
                    {
                        LFri = Convert.ToDecimal(DtLFri.Rows[0]["lastweekFri"].ToString());
                        LDayName5 = DtLFri.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }



            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();

                    string LsatQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekSat,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate()- interval 7 day)  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Saturday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery6 = string.Format(LsatQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery6, con);
                    MySqlDataAdapter AdaL6 = new MySqlDataAdapter();
                    AdaL6.SelectCommand = cmd;

                    AdaL6.Fill(DtLSat);
                    if (DtLSat.Rows.Count > 0)
                    {
                        LSat = Convert.ToDecimal(DtLSat.Rows[0]["lastweekSat"].ToString());
                        LDayName6 = DtLSat.Rows[0]["Day"].ToString();

                    }

                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();

                    string LsunQry = "select  ifnull(sum(tp.PaymentAmount ),0) as lastweekSun,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate()- interval 7 day)  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Sunday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery7 = string.Format(LsunQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery7, con);

                    MySqlDataAdapter AdaL7 = new MySqlDataAdapter();
                    AdaL7.SelectCommand = cmd;
                    AdaL7.Fill(DtLSun);
                    if (DtLSun.Rows.Count > 0)
                    {
                        LSun = Convert.ToDecimal(DtLSun.Rows[0]["lastweekSun"].ToString());
                        LDayName7 = DtLSun.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }









            // Currentweek Value : On Sale 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();

                    string CMonQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekmon,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate())  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Monday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery8 = string.Format(CMonQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery8, con);

                    MySqlDataAdapter AdaC1 = new MySqlDataAdapter();
                    AdaC1.SelectCommand = cmd;
                    AdaC1.Fill(DtCMon);
                    if (DtCMon.Rows.Count > 0)
                    {
                        CMon = Convert.ToDecimal(DtCMon.Rows[0]["cweekmon"].ToString());
                        CDayName1 = DtCMon.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }



            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();
                    string CTueQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekTues,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate())  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Tuesday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery9 = string.Format(CTueQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery9, con);

                    MySqlDataAdapter AdaC2 = new MySqlDataAdapter();
                    AdaC2.SelectCommand = cmd;
                    AdaC2.Fill(DtCTue);
                    if (DtCTue.Rows.Count > 0)
                    {
                        CTue = Convert.ToDecimal(DtCTue.Rows[0]["cweekTues"].ToString());
                        CDayName2 = DtCTue.Rows[0]["Day"].ToString();

                    }



                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();
                    string CWedQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekWed,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate())  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Wednesday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery10 = string.Format(CWedQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery10, con);
                    MySqlDataAdapter AdaC3 = new MySqlDataAdapter();
                    AdaC3.SelectCommand = cmd;
                    AdaC3.Fill(DtCWed);
                    if (DtCWed.Rows.Count > 0)
                    {
                        CWed = Convert.ToDecimal(DtCWed.Rows[0]["cweekWed"].ToString());
                        CDayName3 = DtCWed.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }




            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();
                    string CThuQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekThurs,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate())  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Thursday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery11 = string.Format(CThuQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery11, con);

                    MySqlDataAdapter AdaC4 = new MySqlDataAdapter();
                    AdaC4.SelectCommand = cmd;
                    AdaC4.Fill(DtCThu);
                    if (DtCThu.Rows.Count > 0)
                    {
                        CThu = Convert.ToDecimal(DtCThu.Rows[0]["cweekThurs"].ToString());
                        CDayName4 = DtCThu.Rows[0]["Day"].ToString();

                    }

                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();
                    string CFriQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekFri,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate())  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Friday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery12 = string.Format(CFriQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery12, con);

                    MySqlDataAdapter AdaC5 = new MySqlDataAdapter();
                    AdaC5.SelectCommand = cmd;
                    AdaC5.Fill(DtCFri);
                    if (DtCFri.Rows.Count > 0)
                    {
                        CFri = Convert.ToDecimal(DtCFri.Rows[0]["cweekFri"].ToString());
                        CDayName5 = DtCFri.Rows[0]["Day"].ToString();

                    }



                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();
                    string CSatQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekSat,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate())  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Saturday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery13 = string.Format(CSatQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery13, con);
                    MySqlDataAdapter AdaC6 = new MySqlDataAdapter();
                    AdaC6.SelectCommand = cmd;

                    AdaC6.Fill(DtCSat);
                    if (DtCSat.Rows.Count > 0)
                    {
                        CSat = Convert.ToDecimal(DtCSat.Rows[0]["cweekSat"].ToString());
                        CDayName6 = DtCSat.Rows[0]["Day"].ToString();

                    }


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }




            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();
                    string CSunQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekSun,dayname(tp.BusinessDate) as Day from {0}.transpayment tp  left join {0}.trans t on tp.TransID=t.TransID where YEARWEEK(tp.BusinessDate) =yearweek(curdate())  and t.TransStatus in (2,3,4) and dayname(tp.BusinessDate) ='Sunday' group by dayname(tp.BusinessDate) order by dayname(tp.BusinessDate)";
                    string newquery14 = string.Format(CSunQry, BranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery14, con);

                    MySqlDataAdapter AdaC7 = new MySqlDataAdapter();
                    AdaC7.SelectCommand = cmd;
                    AdaC7.Fill(DtCSun);
                    if (DtCSun.Rows.Count > 0)
                    {
                        CSun = Convert.ToDecimal(DtCSun.Rows[0]["cweekSun"].ToString());
                        CDayName7 = DtCSun.Rows[0]["Day"].ToString();

                    }



                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }







            if (DtFinaldata.Rows.Count > 0)
            {

                DataRow Dr = DtFinaldata.NewRow();

                Dr["Day"] = LDayName1;
                Dr["Lastweeksale"] = LMon;
                Dr["CurrentWeekSal"] = CMon;

                DtFinaldata.Rows.Add(Dr);

                DataRow Dr1 = DtFinaldata.NewRow();

                Dr1["Day"] = LDayName2;
                Dr1["Lastweeksale"] = LTue;
                Dr1["CurrentWeekSal"] = CTue;
                DtFinaldata.Rows.Add(Dr1);

                DataRow Dr2 = DtFinaldata.NewRow();

                Dr2["Day"] = LDayName3;
                Dr2["Lastweeksale"] = LWed;
                Dr2["CurrentWeekSal"] = CWed;
                DtFinaldata.Rows.Add(Dr2);

                DataRow Dr3 = DtFinaldata.NewRow();

                Dr3["Day"] = LDayName4;
                Dr3["Lastweeksale"] = LThu;
                Dr3["CurrentWeekSal"] = CThu;
                DtFinaldata.Rows.Add(Dr3);

                DataRow Dr4 = DtFinaldata.NewRow();

                Dr4["Day"] = LDayName5;
                Dr4["Lastweeksale"] = LFri;
                Dr4["CurrentWeekSal"] = CFri;
                DtFinaldata.Rows.Add(Dr4);

                DataRow Dr5 = DtFinaldata.NewRow();

                Dr5["Day"] = LDayName6;
                Dr5["Lastweeksale"] = LSat;
                Dr5["CurrentWeekSal"] = CSat;
                DtFinaldata.Rows.Add(Dr5);

                DataRow Dr6 = DtFinaldata.NewRow();

                Dr6["Day"] = LDayName7;
                Dr6["Lastweeksale"] = LSun;
                Dr6["CurrentWeekSal"] = CSun;
                DtFinaldata.Rows.Add(Dr6);
                Session["DtFinaldata"] = DtFinaldata;

                // Load the Chart 
                BindChart();
                //CreateBarGraph();  oldone 

            }

            else
            {
                DtFinaldata.Columns.Add("Day", typeof(string));
                DtFinaldata.Columns.Add("Lastweeksale", typeof(decimal));
                DtFinaldata.Columns.Add("CurrentWeekSal", typeof(string));

                DataRow Dr = DtFinaldata.NewRow();


                Dr["Day"] = LDayName1;
                Dr["Lastweeksale"] = LMon;
                Dr["CurrentWeekSal"] = CMon;
                DtFinaldata.Rows.Add(Dr);

                DataRow Dr1 = DtFinaldata.NewRow();

                Dr1["Day"] = LDayName2;
                Dr1["Lastweeksale"] = LTue;
                Dr1["CurrentWeekSal"] = CTue;
                DtFinaldata.Rows.Add(Dr1);

                DataRow Dr2 = DtFinaldata.NewRow();

                Dr2["Day"] = LDayName3;
                Dr2["Lastweeksale"] = LWed;
                Dr2["CurrentWeekSal"] = CWed;
                DtFinaldata.Rows.Add(Dr2);

                DataRow Dr3 = DtFinaldata.NewRow();

                Dr3["Day"] = LDayName4;
                Dr3["Lastweeksale"] = LThu;
                Dr3["CurrentWeekSal"] = CThu;
                DtFinaldata.Rows.Add(Dr3);

                DataRow Dr4 = DtFinaldata.NewRow();

                Dr4["Day"] = LDayName5;
                Dr4["Lastweeksale"] = LFri;
                Dr4["CurrentWeekSal"] = CFri;
                DtFinaldata.Rows.Add(Dr4);

                DataRow Dr5 = DtFinaldata.NewRow();

                Dr5["Day"] = LDayName6;
                Dr5["Lastweeksale"] = LSat;
                Dr5["CurrentWeekSal"] = CSat;
                DtFinaldata.Rows.Add(Dr5);
                DataRow Dr6 = DtFinaldata.NewRow();
                Dr6["Day"] = LDayName7;
                Dr6["Lastweeksale"] = LSun;
                Dr6["CurrentWeekSal"] = CSun;
                DtFinaldata.Rows.Add(Dr6);

                // Load the Chart 
                //CreateBarGraph();  old one 
                BindChart();

            }
        }

        public void LoadTestRpt(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {

                  
                    string query = "Select PaymentType,count(Paymenttype) as Count,ifnull(sum(paymentamount),0) as Amount From {0}.trans t    join {0}.transpayment tp on t.transid = tp.transid where  t.transstatus in (2,3,4) AND    tp.businessdate between  @D1 and  @D2  group by PaymentType";

                    string newquery1 = string.Format(query, BranchAliasNam);

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

                    Dtt = ObjMain.NewGetData_DT(newquery1, parameters);

                    string htmlTable = "<table >";
                    /*
                     foreach (DataColumn column in Dtt.Columns)
                     {
                      htmlTable += "<thead>" + column.ColumnName + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp" + "</thead>";
                     }
                     htmlTable += "</tr>";
                    */
                    for (int j = 0; j < Dtt.Rows.Count; j++)
                    {
                        DataRow dr1 = Dtt.Rows[j];
                        string PaymentType = dr1["PaymentType"].ToString();
                        string Count = dr1["Count"].ToString();
                        string Amount = dr1["Amount"].ToString();
                        string.Format("{0:n2}", Amount);

                        decimal Amt = Convert.ToDecimal(Amount);
                        htmlTable += "<tr><td>" + PaymentType + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp " + "</td>  <td>" + Count + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " + "</td><td>" + Amt.ToString("c") + "</td></tr>";
                    }
                    htmlTable += "</table>";
                    lblTable.Text = htmlTable;
                  
                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }

            }



        }

        public void LoadTotSale(string CdbranchAlias, string D1, string D2)
        {

            // Preventing SQL Injection 
            // Parameterized Approach 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    /*
                    string query = "Select ifnull(sum(TP.PaymentAmount),0) as salesamount From {0}.trans t join  {0}.transpayment tp on t.transid = tp.transid where t.transstatus in (2,3,4) AND  t.businessdate between  @D1 and   @D2 ;";
                              
                    string newquery2 = string.Format(query, CdbranchAlias);
                    MySqlCommand cmd = new MySqlCommand(newquery2, con);
                    cmd.Parameters.AddWithValue("@D1", D1);
                    cmd.Parameters.AddWithValue("@D2", D2);
                    Tot_Sales = Convert.ToDecimal(cmd.ExecuteScalar());
                    lblRTotalSale.Text = Tot_Sales.ToString("c");
                    */


                    string query = "Select ifnull(sum(TP.PaymentAmount),0) as salesamount From {0}.trans t join  {0}.transpayment tp on t.transid = tp.transid where t.transstatus in (2,3,4) AND  tp.businessdate between  @D1 and   @D2 ;";
                    string newquery2 = string.Format(query, CdbranchAlias);
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                    Tot_Sales = Convert.ToDecimal(ObjCloud.NewExecuteScalar(newquery2, parameters));
                    lblRTotalSale.Text = Tot_Sales.ToString("c");





                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                  
                }




            }



        }


        public void LoadSalesAmt1(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   

                    //string query = "Select  ifnull(sum(TP.PaymentAmount),0) as salesamount From " + BranchAliasNam + ".trans t  join " + BranchAliasNam + ".transpayment tp on t.transid = tp.transid where  t.transstatus in (2,3,4) AND   tp.businessdate between '" + D1 + "' and '" + D2 + "';"; //john Hide on 07-Oct-2020 follow Trans Businessdate
                    string query = "Select  ifnull(sum(TP.PaymentAmount),0) as salesamount From {0}.trans t  join {0}.transpayment tp on t.transid = tp.transid where  t.transstatus in (2,3,4) AND   tp.businessdate between  @D1 and  @D2 ;";

                    string newquery3 = string.Format(query, BranchAliasNam);


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

                    Tot_Sales = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery3,parameters));
                    lblRTotalSale.Text = Tot_Sales.ToString("c");

                    string htmlTable = "<table ><tr>";

                    htmlTable += "<tr><td>" + "Sales Amt" + "" + "   </td><td>" + "N/A" + " " + "</td> <td>" + " " + Tot_Sales.ToString("c") + "</td></tr>";

                    htmlTable += "</table>";
                    lblTable1.Text = htmlTable;




                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }



        }



        public void LoadSalesAmt(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach 
            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    string query = "Select PaymentType,count(Paymenttype) as count,ifnull(sum(paymentamount),0) as amount From {0}.trans t    join {0}.transpayment tp on t.transid = tp.transid where tp.businessdate between  @D1  and  @D2  group by PaymentType";
                    string newquery4 = string.Format(query, BranchAliasNam);

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

                    Dtt = ObjMain.NewGetData_DT(newquery4, parameters);
                    
                    // DT = ObjSel.Retail_PaymentDetails(cd_BranchAlias, "2019-02-012", "2019-02-012");

                    string htmlTable = "<table> <tr>";
                    foreach (DataColumn column in Dtt.Columns)
                    {
                        htmlTable += "<thead>" + column.ColumnName + " </thead>";
                    }
                    htmlTable += "</tr>";
                    for (int j = 0; j < Dtt.Rows.Count; j++)
                    {
                        DataRow dr1 = Dtt.Rows[j];
                        string PaymentType = dr1["PaymentType"].ToString();
                        string Count = dr1["count"].ToString();
                        string Amount = dr1["amount"].ToString();
                        string.Format("{0:n2}", Amount);

                        Amt = Convert.ToDecimal(Amount);
                        htmlTable += "<tr><td>" + PaymentType + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " + "   </td> <td>" + Count + "</td><td>" + Amt.ToString("c") + "</td></tr>";
                    }
                    htmlTable += "</table>";
                    lblTable.Text = htmlTable;


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    
                }

            }
        }

        public void LoadPayouts(string BranchAliasNam, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach 

            // Payout Count : Fun

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                  
                    string query = "select ifnull(count(Amount),0)as payoutcount from {0}.rapo where TransType=1  and BusinessDate between  @D1 and  @D2 ;";
                    string newquery5 = string.Format(query, BranchAliasNam);

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

                    PayoutCnt = Convert.ToString(ObjMain.NewExecuteScalar(newquery5, parameters));
                    

                    string query1 = "select ifnull(sum(Amount),0) as payout  from {0}.rapo where TransType=1  and BusinessDate between  @D1  and  @D2 ; ";

                    string newquery6 = string.Format(query1, BranchAliasNam);
                    

                    MySqlParameter[] parametersb =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                
                    PayoutAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery6,parametersb));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Payouts" + " " + "</td> <td>" + PayoutCnt + "" + "</td><td>" + "" + PayoutAmt.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblPayouts.Text = htmlTable;


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }




        }

        public void LoadReceving(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   
                    // Payout Count : Fun
                    string Rquery = "select ifnull(count(Amount) ,0) as receivecount  from {0}.rapo where TransType=0  and BusinessDate    between  @D1  and  @D2 ;";

                    string newquery7 = string.Format(Rquery, BranchAliasNam);

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

                    ReceivingCnt = Convert.ToString(ObjMain.NewExecuteScalar(newquery7, parameters));
                                      
                                   



                    string Rquery1 = "select ifnull(sum(Amount),0) as receive from {0}.rapo where TransType=0  and BusinessDate      between  @D1 and  @D2 ;";
                    string newquery8 = string.Format(Rquery1, BranchAliasNam);

                    MySqlParameter[] parametersb =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                    ReceivingAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery8, parametersb));

                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Receiving" + " " + "</td> <td>" + " " + ReceivingCnt + "</td><td>" + " " + ReceivingAmt.ToString("c") + "</td></tr>";

                    htmlTable += "</table>";
                    lblReceiving.Text = htmlTable;


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }





        }

        public void LoadCashindrawer(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-9-21
            //  Parameterized Approach 
            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   // Payout Count : Fun


                    string Cquery = "select * from (select  ifnull(SUM(Paymentamount),0) AS cash from {0}.transpayment as P inner join {0}.trans as t on P.transid=t.transid  where P.Paymenttype='CASH' and   P.businessdate between  @D1    and  @D2   and t.transstatus=2 )a,(select ifnull(sum(Amount),0) as takeRA from {0}.rapo where TransType in (4,0) and BusinessDate between @D1  and  @D2 )d,(select ifnull(sum(Amount),0) as po from {0}.rapo where TransType='1' and BusinessDate between @D1  and  @D2 )q ;";
                    string newquery8 = string.Format(Cquery, BranchAliasNam);

                    MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    DataTable DtCashin = new DataTable();
                    DtCashin = ObjMain.NewGetData_DT(newquery8, parameters);

                    if (DtCashin.Rows.Count > 0)
                    {
                        Cashin_Cash = Convert.ToDecimal(DtCashin.Rows[0]["cash"].ToString());
                        Cashin_TakeRA = Convert.ToDecimal(DtCashin.Rows[0]["takeRA"].ToString());
                        Cashin_Po = Convert.ToDecimal(DtCashin.Rows[0]["po"].ToString());
                        // Calculation Logic :  cash +TakeRA-po    CashinDrawer = Cashin_Cash + Cashin_TakeRA - Cashin_Po
                        CshinDraw = Cashin_Cash + Cashin_TakeRA - Cashin_Po;
                        // lblDCashindrawer.Text = CshinDraw.ToString("c");  - This one disable Timebeing  


                    }


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



                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                  
                }




            }



        }

        public void LoadRounding(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   

                    // Payout Count : Fun


                    string RndRquery = "Select ifnull(Sum(A.Round),0) As rounding From {0}.Trans A Inner Join {0}.TransPayment B On A.TransID=B.TransID Where A.TransStatus = 2 And  B.BusinessDate between @D1  and  @D2 ;";
                    string newquery9 = string.Format(RndRquery, BranchAliasNam);

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

                    Rounding = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery9, parameters));
                    // Rounding Bind 
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Rounding" + " " + "</td> <td>" + Rounding.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblRounding.Text = htmlTable;





                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }




        }


        public void LoadRefund(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                  
                    string Refndquery1 = " select ifnull(sum(Amount),0) as refund from {0}.transdetail  left join {0}.trans on transdetail.TransID=trans.TransID where ItemStatus=2 and BusinessDate between  @D1  and  @D2 ;";
                    string newquery10 = string.Format(Refndquery1, BranchAliasNam);
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };


                    Refund = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery10, parameters));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Refund " + " " + "</td> <td>" + Refund.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblRefund.Text = htmlTable;


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }




        }

        public void LoadGrossAmount()
        {
            decimal cGrossSales;
            cGrossSales = Tot_Sales - Refund - VoucherAmt;
            GrossSales = cGrossSales + Rounding + DistTOT;

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

        }

        public void LoadItemDisc(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   
                    string ItemDisquery1 = " select ifnull(sum(ManualDisc),0) as itemdisc from {0}.transdetail td left join {0}.trans t on t.TransID=td.TransID where t.TransStatus='2' and td.ItemStatus='0'and t.BusinessDate between @D1 and @D2 ;";
                    string newquery11 = string.Format(ItemDisquery1, BranchAliasNam);

                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    
                    ItemDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery11, parameters));


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


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }





        }

        public void LoadTotDisc(string BranchAliasNam, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    con.Open();

                    decimal dTotalDisc;
                    string TotDiscquery1 = " SELECT coalesce((SELECT sum(TotalDisc)  from {0}.transdetail B join {0}.Trans A on A.TransID = B.TransID where businessdate  between  @D1  and  @D2 and transstatus in (2,3,4)),0)as totaldisc;";
                    string newquery12 = string.Format(TotDiscquery1, BranchAliasNam);


                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                 
                    TotDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery12, parameters));

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

                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }




            }




        }


        public void LoadTotPriceShiftDisc(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach  using 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                  

                    //string PriceShiftDiscquery1 = " select ifnull(sum(Amount),0) as refund from " + BranchAliasNam + ".transdetail  left join " + BranchAliasNam + ".trans on transdetail.TransID=trans.TransID where ItemStatus=2 and BusinessDate between '" + D1 + "' and '" + D2 + "';";
                    string PriceShiftDiscquery1 = "Select ifnull(Sum(A.ActualPSDiscAmount),0) As SPriceShiftDiscount From {0}.TransDetail A Inner Join {0}.Trans B On A.TransID=B.TransID Where B.TransStatus = 2 And A.ItemStatus <>1 And B.BusinessDate  Between @D1 and  @D2 ; ";

                    string newquery13 = string.Format(PriceShiftDiscquery1, BranchAliasNam);

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

                   PriceShifDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery13, parameters));


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

                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                 
                }




            }





        }
        public void LoadDiscountTOT()
        {

            DistTOT = ItemDisc + TotDisc + PriceShifDisc + MemDiscAmt;
            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Discount Total" + " " + "</td> <td>" + DistTOT.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblDiscountTotal.Text = htmlTable;

        }

        public void LoadMemberDISC(string sDB, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach  using 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   

                    string query = "Select ifnull(sum(MemberdiscAmount),0) as MemDiscAmt from {0}.Trans where businessdate between  @D1 and  @D2 ;";
                    string newquery14 = string.Format(query, sDB);

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

                  


                    MemDiscAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery14, parameters));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Member Total Disc Amount" + " " + "</td> <td>" + MemDiscAmt.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblMemberDiscount.Text = htmlTable;

                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }




        }

        public void LoadTax3(string sDB, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach  using 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   

                    string Query1 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 3;";

                    string newquery15 = string.Format(Query1, sDB);
                    string sTaxCol = Convert.ToString(ObjMain.ExecuteScalar(newquery15));

                    // Existing Code 
                    // string sTaxCol = ObjMain.ExecuteScalar("seLECT TAXCOLNAME FROM " + sDB + ".Taxnameidentifier WHERE TAXID = 3; ");


                    string GstAmountquery1 = " SELECT Taxname,(select  ifnull(sum(" + sTaxCol + "),0)   from {0}.trans AS t where t.TRANSSTATUS IN (2,3,4) AND  t.businessdate between  @D1 and @D2 ) as GSTTOTAL from {0}.tax where taxid=3;";
                    string newquery16 = string.Format(GstAmountquery1, sDB);
                    DataTable DtTax3 = new DataTable();

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

                    DtTax3 = ObjMain.NewGetData_DT(newquery16, parameters);
                           
                    if (DtTax3.Rows.Count > 0)
                    {
                        Tax3Amt = Convert.ToDecimal(DtTax3.Rows[0]["GSTTOTAL"].ToString());

                        if (Tax3Amt != 0)
                        {
                            if (DtTax3.Rows[0]["Taxname"].ToString() == "Tax3")
                            {
                                TaxNameRef = "TAX 3";
                            }
                            else
                            {
                                TaxNameRef = DtTax3.Rows[0]["Taxname"].ToString();
                            }
                        }
                    }
                    else
                    {
                        Tax3Amt = 0;
                    }

                    if (Tax3Amt != 0)
                    {

                        string htmlTable = "<table ><tr>";
                        htmlTable += "<tr><td>" + TaxNameRef + " " + "</td> <td>" + Tax3Amt.ToString("c") + "</td></tr>";
                        htmlTable += "</table>";
                        lblTax3.Text = htmlTable;

                    }
                    else
                    {

                    }



                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    
                }




            }





        }

        public void LoadGSTAmount(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach  using 

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   

                    //string GstAmountquery1 = " Select * From(Select ifnull(sum(round(Taxamount2,2)),0) as gst From " + BranchAliasNam + ".Trans where businessdate between '" + D1 + "' and '" + D2 + "' and transstatus in (2,3,4))A,(Select if(taxontax=1,'.07','') as taxgst from " + BranchAliasNam + ".tax where taxname like '%GST')b;";


                    string Query1 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 2;";

                    string newquery17 = string.Format(Query1, BranchAliasNam);
                    string sTaxCol = Convert.ToString(ObjMain.ExecuteScalar(newquery17));

                    string GstAmountquery1 = " SELECT Taxname,(select  ifnull(sum(" + sTaxCol + "),0)   from " + BranchAliasNam + ".trans AS t where t.TRANSSTATUS IN (2,3,4) AND  t.businessdate between '" + D1 + "' and '" + D2 + "') as GSTTOTAL from " + BranchAliasNam + ".tax where taxid=2;";
                    string newquery18 = string.Format(GstAmountquery1, BranchAliasNam);

                    MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    DataTable DtGst = new DataTable();
                    DtGst = ObjMain.NewGetData_DT(newquery18, parameters);
                                            
                        
                    if (DtGst.Rows.Count > 0)
                    {
                        Gstamt = Convert.ToDecimal(DtGst.Rows[0]["GSTTOTAL"].ToString());

                        if (Gstamt != 0)
                        {
                            if (DtGst.Rows[0]["Taxname"].ToString() == "Tax2")
                            {
                                TaxNameRef = "GST";
                            }
                            else
                            {
                                TaxNameRef = DtGst.Rows[0]["Taxname"].ToString();
                            }
                        }
                    }
                    else
                    {
                        Gstamt = 0;
                    }
                    
                    if (Gstamt != 0)
                    {   //string htmlTable = "<table ><tr>";
                        //htmlTable += "<tr><td>" + TaxNameRef + " " + "</td>" +
                        //             "<td>" + Gstamt.ToString("c") + "</td></tr>";
                        //htmlTable += "</table>"; 
                        //lblGST.Text = htmlTable;

                        System.Text.StringBuilder sb = new System.Text.StringBuilder();
                        sb.Append("<table><tr>");
                        sb.Append("<tr><td>" + TaxNameRef + " " + "</td>");
                        sb.Append(" <td>" + Gstamt.ToString("c") + "</td></tr>");
                        sb.Append("</table>");
                        lblGST.Text = sb.ToString();
                    }
                    else
                    {
                        
                    }



                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }







        }



        public void LoadSVCAmount(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach  

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   


                    //string SvcAmountquery1 = "select * from (select ifnull(sum(TaxAmount1),0) as svc from " + BranchAliasNam + ".trans where BusinessDate between '" + D1 + "' and '" + D2 + "')a, (select Tax as taxsvc from " + BranchAliasNam + ".Tax where TaxName like '%svc.c%')b;";
                    // string SvcAmountquery1 = "select ifnull(sum(TaxAmount1),0) as svc from " + BranchAliasNam + ".trans where BusinessDate between '" + D1 + "' and '" + D2 + "' ";
                    //string sTaxCol = ObjMain.ExecuteScalar("seLECT TAXCOLNAME FROM " + BranchAliasNam + ".Taxnameidentifier WHERE TAXID = 1; ");

                    string Query1 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 1;";

                    string newquery18 = string.Format(Query1, BranchAliasNam);
                    //MySqlCommand cmd = new MySqlCommand(newquery18, con);
                    string sTaxCol =Convert.ToString(ObjMain.ExecuteScalar(newquery18));


                    string SvcAmountquery1 = "SELECT Taxname,(select ifnull(sum(" + sTaxCol + "),0)  from {0}.trans where businessdate between  @D1 and  @D2  and transstatus in (2,3,4)) as GSTTOTAL from {0}.tax where taxid=1;";
                    string newquery19 = string.Format(SvcAmountquery1, BranchAliasNam);

                    DataTable DtSVC = new DataTable();
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                    DtSVC = ObjMain.NewGetData_DT(newquery19, parameters);

                    if (DtSVC.Rows.Count > 0)
                    {
                        //  SvcAmt = Convert.ToDecimal(DtSVC.Rows[0]["svc"].ToString());

                        SvcAmt = Convert.ToDecimal(DtSVC.Rows[0]["GSTTOTAL"].ToString());


                        if (DtSVC.Rows[0]["Taxname"].ToString() == "Tax1")
                        {
                            TaxNameRef = "SVC(10%)";
                        }
                        else
                        {
                            TaxNameRef = DtSVC.Rows[0]["Taxname"].ToString(); 
                        }

                    }
                    else
                    {
                        SvcAmt = 0;
                    }


                    if (SvcAmt != 0)
                    {
                        
                        string htmlTable = "<table ><tr>";
                        htmlTable += "<tr><td>" + TaxNameRef + " " + "</td> <td>" + SvcAmt.ToString("c") + "</td></tr>";
                        htmlTable += "</table>";
                        lblSVC.Text = htmlTable;
                     }
                    else
                    {
                        
                    }




                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }







        }

        public void Netmount()
        {

            if (Tot_Sales != 0)
            {
                NetAmt = Tot_Sales - (Gstamt + SvcAmt + Tax3Amt);
            }
            else
            {
                NetAmt = 0;
            }



            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Net Amount" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " + "</td> <td>" + NetAmt.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            //lblNetAmt.Text = htmlTable;
            lblDNetAmount.Text = NetAmt.ToString("c");

        }


        public void LoadVoucher(string BranchAliasNam, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach  
            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   


                    string Vouchcquery1 = " Select ifnull(Sum(B.TaxableAmount+B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6),0) AS Voucher From   {0}.Trans A Left Join {0}.TransDetail B On A.TransID=B.TransID Where  A.TransStatus = 2 And A.VoucherTotal > 0 And B.Amount < 0  And B.ItemStatus=0  And A.BusinessDate between  @D1  and @D2 ;";

                    string newquery20 = string.Format(Vouchcquery1, BranchAliasNam);
                    //string Vouchcquery1 = "Select Sum(B.Amount) as Voucher ,Name As Nam From " + BranchAliasNam + ".Trans A left join " + BranchAliasNam + ".TransDetail B   on A.TransID=B.TransID Where  A.TransStatus = 2  And A.BusinessDate  between '" + D1 + "' and '" + D2 + "' And A.VoucherTotal>0 And B.Amount<0 And B.ItemStatus<>1 Group By Name";
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                                    
                    VoucherAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery20, parameters));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Voucher Amount" + " " + "</td> <td>" + VoucherAmt.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblVoucher.Text = htmlTable;


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }


        }
        public void LoadHoldCnt(string BranchAliasNam, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach  

            DataTable Dtadd = new DataTable();
            Dtadd.Columns.Add("Type", typeof(string));
            Dtadd.Columns.Add("Count", typeof(Int32));
            Dtadd.Columns.Add("Amount", typeof(decimal));


            // Dtadd.AcceptChanges();
            // Adding Data Columns In Order to Heading Purpose 

            string htmlTable = "<table >";
            //foreach (DataColumn column in Dtadd.Columns)
            //{
            //    htmlTable += "<thead>" + column.ColumnName + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "</thead>";
            //}


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                //  Preventing SQL Injection : 28-09-21
                //  Parameterized Approach  

                try
                {
                    string Holdcntcquery1 = "select coalesce((select count(TransStatus)  from {0}.trans where TransStatus in (3,4) and BusinessDate between @D1 and  @D2 ),0) as holdcount";
                    string newquery21 = string.Format(Holdcntcquery1, BranchAliasNam);
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    Holdcnt = Convert.ToString( ObjMain.NewExecuteScalar(newquery21, parameters));
                    string HoldAmtcquery1 = "select coalesce((select ifnull(sum(Total),0) as hold  from {0}.trans where TransStatus in (3,4) and BusinessDate between @D1 and  @D2),0) as hold;";
                    string newquery22 = string.Format(HoldAmtcquery1, BranchAliasNam);
                    MySqlParameter[] parametersb =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                    HoldAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery22, parametersb));
                    htmlTable += "<tr><td>" + "Hold " + "" + "</td>     <td>" + "" + Holdcnt + "</td> <td>" + "" + HoldAmt.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblHold.Text = htmlTable;


                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }



        }

        public void LoadCancel(string BranchAliasNam, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach  

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   


                    string Cancecntcquery1 = "select coalesce((select ifnull(count(TransStatus),0)  from {0}.trans where TransStatus=5 and BusinessDate between  @D1  and  @D2 ),0)  as cancelcount";

                    string newquery23 = string.Format(Cancecntcquery1, BranchAliasNam);


                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    
                    CancelCnt = Convert.ToString(ObjMain.NewExecuteScalar(newquery23,parameters));
                    string CancelAmt = "select coalesce((select  ifnull(sum(Total),0)   from {0}.trans where TransStatus=5 and BusinessDate between  @D1  and @D2 ),0) as cancel;";
                    string newquery24 = string.Format(CancelAmt, BranchAliasNam);
                    MySqlParameter[] parametersb =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    CancelAmtVal = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery24, parametersb));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Cancel " + " " + "</td>     <td>" + " " + CancelCnt + "</td> <td>" + " " + CancelAmtVal.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblCancel.Text = htmlTable;

                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    
                }




            }



        }

        public void LoadVoid(string BranchAliasNam, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach  


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                   
                    string Voidcntcquery1 = "select coalesce((select count(Amount) from {0}.transdetail where  itemstatus='1' and date(VoidDate) between  @D1  and   @D2 ),0) as voidcount;";
                    string newquery24 = string.Format(Voidcntcquery1, BranchAliasNam);

                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    VoidCnt = Convert.ToString(ObjMain.NewExecuteScalar(newquery24, parameters));

                    string VoidlAmtquery = "select coalesce((select sum(Amount) from {0}.transdetail where  itemstatus='1' and date(VoidDate) between  @D1  and   @D2),0) as void";
                    string newquery25 = string.Format(VoidlAmtquery, BranchAliasNam);

                    MySqlParameter[] parametersb =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                  
                    VoidAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery25, parametersb));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Void " + " " + "</td>     <td>" + " " + VoidCnt + "</td> <td>" + "" + VoidAmt.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblVoid.Text = htmlTable;

                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }






        }

        public void LoadExchange(string BranchAliasNam, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach  

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                  
                    string ExchAmtcquery1 = "select coalesce((select sum(Amount) as exchange from {0}.transdetail  where ItemStatus=4 and date(VoidDate) between  @D1 and  @D2),0) as exchange";
                    string newquery26 = string.Format(ExchAmtcquery1, BranchAliasNam);

                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    ExAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery26, parameters));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "Exchange " + "" + "</td>     <td>" + "" + "N/A" + "</td> <td>" + " " + ExAmt.ToString("c") + "</td></tr>";
                    htmlTable += "</table>";
                    lblExchange.Text = htmlTable;



                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }





        }

        public void LoadNoSalesPersonOpen(string BranchAliasNam, string D1, string D2)
        {

            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach  

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {
                    
                    string NoSalesPersoquery1 = "select coalesce((select count(Notes) from {0}.rapo as NoSalesOpen where TransType=2  and BusinessDate between  @D1  and  @D2 ),0) as nosalesopen";
                    string newquery27 = string.Format(NoSalesPersoquery1, BranchAliasNam);


                    MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                                      
                    NoSalesopen = Convert.ToString(ObjMain.NewExecuteScalar(newquery27,parameters));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "No Sales Open " + "" + "</td>     <td>" + "" + NoSalesopen + "</td> <td>" + " " + "N/A" + "</td></tr>";
                    htmlTable += "</table>";
                    lblNosalespersonOpen.Text = htmlTable;



                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                    
                }




            }



        }

        public void LoadNoofReceipts(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-08-21
            //  Parameterized Approach  

            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try 
                {
                    
                    string NoofReceiptsqry = "select * from (select count(TransID) as noofreceipts from {0}.trans where BusinessDate between @D1  and  @D2 )f,(select sum(PaymentAmount) as salesamount from {0}.transpayment where BusinessDate between @D1  and  @D2 )s";
                    string newquery28 = string.Format(NoofReceiptsqry, BranchAliasNam);

                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                    NoofReceipts = Convert.ToString(ObjMain.NewExecuteScalar(newquery28, parameters));
                    string htmlTable = "<table ><tr>";
                    htmlTable += "<tr><td>" + "No. Of Receipts	 " + " " + "</td>     <td>" + " " + NoofReceipts + "</td> <td>" + "" + "N/A" + "</td></tr>";
                    htmlTable += "</table>";
                    lblNoofReceipts.Text = htmlTable;

                }
                catch (Exception ex)
                {
                    BL_ErrorLog bL_Error = new BL_ErrorLog();
                    bL_Error.Write(ex);
                }
                finally
                {
                   
                }




            }



        }

        public void LoadAvgReceipts()
        {

            if (Tot_Sales != 0)
            {
                AvgReceipts = Tot_Sales / Convert.ToDecimal(NoofReceipts);

            }
            else
            {

                AvgReceipts = 0;
            }



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

        }
        //public void LoadBranchName()
        //{
        //    DT = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());

        //    cmbBranches.DataSource = DT;
        //    cmbBranches.DataTextField = "BranchName";
        //    cmbBranches.DataValueField = "Branchid";
        //    cmbBranches.DataBind();
        //    cmbBranches.Items.Insert(0, "Select Branch");

        //}

        public void LoadBranchName4SuperAdmin()
        {
            DT = ObjUserBL.GetBranchOnSuperAdmin8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());

            cmbBranches.DataSource = DT;
            cmbBranches.DataTextField = "branchfullname";
            cmbBranches.DataValueField = "Branchid";

            cmbBranches.DataBind();
            cmbBranches.Items.Insert(0, "Select Branch");


        }

        public void LoadBranchNamewithPortalTypeid()
        {
            DT = ObjUserBL.GetBranchOnLoginPortalid(Session["Branch_HQ"].ToString());

            cmbBranches.DataSource = DT;
            cmbBranches.DataTextField = "BranchName";
            cmbBranches.DataValueField = "Branchid";
            cmbBranches.DataBind();
            cmbBranches.Items.Insert(0, "Select Branch");

        }
        public void LoadBranchName()
        {
            DT = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());


            if (DT.Rows.Count > 0)
            {
                cmbBranches.DataSource = DT;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");

            }

        }
        protected void btnGetReports_Click(object sender, EventArgs e)
        {
            ClearCache();
            if (txtStartDt.Text != "" && txtEndDt.Text != "" && cmbBranches.SelectedIndex != 0)
            {
                //Prevent XSS Attack 
                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 )
                cd_BranchAlias = Cmb_BranchName;
                lblMsgTotalSelection.Text = "Search Result - Date .. " + "&nbsp;&nbsp;" + StartDT.Trim() + "&nbsp;&nbsp;&nbsp;  To &nbsp;&nbsp;&nbsp;" + EndDt.Trim();
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                LoadTestRpt(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadTotSale(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);         // TotalAmt         
                // LoadMonthlySale(cd_BranchAlias, Convert.ToString(MonthstartDt), Curr_DateOnly);
               // LoadSalesAmt1(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadPayouts(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadReceving(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadCashindrawer(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadRounding(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadRefund(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadMemberDISC(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadItemDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadTotDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadTotPriceShiftDisc(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadDiscountTOT();


                LoadGSTAmount(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadSVCAmount(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadTax3(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                Netmount();            // Net Amt
                LoadVoucher(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadHoldCnt(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadCancel(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadVoid(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadExchange(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadNoSalesPersonOpen(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadGrossAmount();
                LoadNoofReceipts(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                LoadAvgReceipts();
                
                LoadLineChartData(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), Convert.ToString(Microsoft.Security.Application.Encoder.HtmlEncode(lblLinechartStdt.Text)), Microsoft.Security.Application.Encoder.HtmlEncode(Curr_DateOnly));
                System.Threading.Thread.Sleep(2000);
            }
            else
            {
                Response.Redirect("SummaryReport.aspx");
            }


        }
        public void LoadLineChartData(string cd_BranchAliasNam, string D1, string D2)
        {

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string query = "Select  sum(PaymentAmount) as TotalSales,DATE_FORMAT(date(PayDate),'%d-%b-%y') as PayDate, date(PayDate) as Date From " + cd_BranchAliasNam + ".transpayment left join " + cd_BranchAliasNam + ".trans on transpayment.TransID=trans.TransID where transpayment.BusinessDate  between '" + D1 + "' and '" + D2 + "' and trans.TransStatus in (2,3,4)  group by  trans.BusinessDate";
            MySqlDataAdapter Cmd = new MySqlDataAdapter(query, Cn);
            Cmd.Fill(DtLinechartNew);
            // Load the Date Value : 
            Session["DtLinechart"] = DtLinechartNew;
            chart_bind();
            //CreateLineGraph();  Old one

        }
        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)
        {
            ClearCache();

            lblmonthlyerrormsg.Text = "";
            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Redirect("SummaryReport.aspx");
            }
            else
            {
                if (txtStartDt.Text != "" && txtEndDt.Text != "")
                {


                    //Prevent XSS Attack 

                    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 )

                    lblBranchAliasName.Text = "Welcome" + "&nbsp;&nbsp;" + cmbBranches.SelectedItem.Text + "&nbsp; !";
                    lblMsgTotalSelection.Text = "Search Result - Date .. " + "&nbsp;&nbsp;" + StartDT.Trim() + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + EndDt.Trim();
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                    LoadTestRpt(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadTotSale(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);

                    //LoadSalesAmt1(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadPayouts(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadReceving(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadCashindrawer(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadRounding(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadRefund(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);

                    LoadItemDisc(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadTotDisc(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadTotPriceShiftDisc(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadDiscountTOT();
                    LoadGrossAmount();
                    LoadGSTAmount(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadSVCAmount(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadTax3(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    Netmount();
                    LoadVoucher(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadHoldCnt(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadCancel(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadVoid(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadExchange(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadNoSalesPersonOpen(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadNoofReceipts(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), StartDT, EndDt);
                    LoadAvgReceipts();
                    LastWCurrWSale(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName));
                    LoadLineChartData(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName), Convert.ToString(Microsoft.Security.Application.Encoder.HtmlEncode(lblLinechartStdt.Text)), Microsoft.Security.Application.Encoder.HtmlEncode(Curr_DateOnly));


                    
                    // System.Threading.Thread.Sleep(2000);


                    // Update the BranchName at Every Dropdown Selection..
                    UPDATEBRANCH(Microsoft.Security.Application.Encoder.HtmlEncode(Cmb_BranchName));


                }
                else
                {
                    Response.Redirect("SummaryReport.aspx");
                }


            }

        }
        private void CreateBarGraph()
        {
            try
            {
                string xmlData, categories, currentWeek, LastWeek;
                //Initialize <chart> element
                xmlData = @"<graph caption='Total Sale Comparision Between Current Week and Last Week' numberPrefix='$' formatNumberScale='1' rotateValues='1' placeValuesInside='1' decimals='0' >";

                //Initialize <categories> element - necessary to generate a multi-series chart
                categories = "<categories>";

                //Initiate <dataset> elements
                LastWeek = "<dataset seriesName='Lastweek Sale' color='#5E2612'>";
                currentWeek = "<dataset seriesName='Curr.Week Sale' color='#00868B'>";


                //Iterate through the data 
                int i = 0;
                foreach (DataRow DR in DtFinaldata.Rows)
                {
                    //Append <category name='...' /> to strCategories
                    categories += "<category name='" + DR["Day"].ToString() + "' />";

                    //Add <set value='...' /> to both the datasets
                    LastWeek += "<set value='" + DR["Lastweeksale"].ToString() + "' />";
                    currentWeek += "<set value='" + DR["CurrentWeekSal"].ToString() + "' />";

                    i++;
                }

                //Close <categories> element
                categories += "</categories>";

                //Close <dataset> elements
                LastWeek += "</dataset>";
                currentWeek += "</dataset>";


                //Assemble the entire XML now
                xmlData += categories + LastWeek + currentWeek + "</graph>";

                //Create the chart - MS Column 3D Chart with data contained in xmlData
                //  return FusionCharts.RenderChart("../../FusionCharts/MSColumn3D.swf", "", xmlData, "productSales", "600", "300", false, false);

                FCLiteral1.Text = InfoSoftGlobal.FusionCharts.RenderChartHTML(
                    "FusionCharts/FCF_MSColumn3D.swf", // Path to chart's SWF
                    "",                              // Leave blank when using Data String method
                    xmlData,                          // xmlStr contains the chart data
                    "productSales",                      // Unique chart ID
                    GraphWidth, GraphHeight,                   // Width & Height of chart
                    false
                    );
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        private void CreateLineGraph()
        {
            string strCaption = "TOTAL SALE TILL DATE";
            string strSubCaption = "";
            string xAxis = "PayDate";
            string yAxis = "TotalSales";

            //strXML will be used to store the entire XML document generated
            string strXML = null;

            //Generate the graph element
            strXML = @"                
            <graph caption='" + strCaption + @"' 
            subcaption='" + strSubCaption + @"'
            hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' 
            showvalues='0' numdivlines='3' numVdivlines='0' yaxisminvalue='80.00' yaxismaxvalue='80.00'  
            rotateNames='1'
            showAlternateHGridColor='1' AlternateHGridColor='ff5904' divLineColor='ff5904' 
            divLineAlpha='20' alternateHGridAlpha='5' 
            xAxisName='" + xAxis + @"' yAxisName='" + yAxis + @"' 
            >        
            ";

            string tmp = null;
            DtLinechartNew = (DataTable)Session["DtLinechart"];
            tmp = @"<categories font='Arial' fontSize='11' fontColor='000000'>";
            foreach (DataRow dr2 in DtLinechartNew.Rows)
            {
                tmp += @"<category name='" + dr2["PayDate"].ToString().Trim() + @"' />";
            }
            tmp += @"</categories>";

            strXML += tmp;

            tmp = @"<dataset seriesName='TOTAL SALE' color='1D8BD1' anchorBorderColor='1D8BD1' anchorBgColor='1D8BD1'>";
            foreach (DataRow dr2 in DtLinechartNew.Rows)
            {
                tmp += @"<set value='" + dr2["TotalSales"].ToString().Trim() + @"'  link=&quot;JavaScript:myJS('" + dr2["PayDate"].ToString() + "| " + dr2["TotalSales"].ToString() + "'); &quot; />";
            }

            tmp += @"</dataset>";
            strXML += tmp;
            strXML += "</graph>";

            FCLiteral2.Text = InfoSoftGlobal.FusionCharts.RenderChartHTML(
                "FusionCharts/FCF_MSLine.swf",   // Path to chart's SWF
                "",                              // Leave blank when using Data String method
                strXML,                          // xmlStr contains the chart data
                "mygraph4",                      // Unique chart ID
                GraphWidth, GraphHeight,         // Width & Height of chart
                false
                );
        }
        public void UPDATEBRANCH(string ActiveCallvalue)
        {
            if (!string.IsNullOrEmpty(Session["UserId"] as string))
            {
                Useridvalue = Session["UserId"].ToString();
                QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
                S = ObjMain.ExecuteNonQuery(QueryCurr_Select);
            }
            else
            {
                Response.Redirect("Login.aspx");

            }

        }
        private void BindChart()
        {
            DataTable dt = new DataTable();
            try
            {
                dt = DtFinaldata;
                str.Append(@"<script type=text/javascript> google.load( *visualization*, *1*, {packages:[*corechart*]});

                       google.setOnLoadCallback(drawChart);
                       function drawChart() {
                       var data = new google.visualization.DataTable();
                       data.addColumn('string', 'Day');
                       data.addColumn('number', 'Lastweeksale');
                       data.addColumn('number', 'CurrentWeekSal');      
                       data.addRows(" + dt.Rows.Count + ");");

                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["Day"].ToString() + "');");
                    str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["Lastweeksale"].ToString() + ") ;");
                    str.Append("data.setValue(" + i + "," + 2 + "," + dt.Rows[i]["CurrentWeekSal"].ToString() + ") ;");

                }
                str.Append(" var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));");
                str.Append(" chart.draw(data, { title: 'Weekwise',");
                //str.Append("hAxis: {title: 'Day', titleTextStyle: {color: 'green'}}");
                str.Append("}); }");
                str.Append("$(window).resize(function(){ drawChart();});");
                str.Append("</script>");
                lt.Text = str.ToString().Replace('*', '"');

            }

            catch

            { }

        }



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

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


        #region  Excel Code 

        public void LoadConsolidatedExcelReport(string BranchAliasNam, string D1, string D2)
        {
            //  Preventing SQL Injection : 28-09-21
            //  Parameterized Approach 


            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {

                    #region  PaymentType Result set 
                    string query = "Select PaymentType,count(Paymenttype) as Count,ifnull(sum(paymentamount),0) as Amount From {0}.trans t    join {0}.transpayment tp on t.transid = tp.transid where  t.transstatus in (2,3,4) AND    tp.businessdate between  @D1 and  @D2  group by PaymentType";

                    string newquery1 = string.Format(query, BranchAliasNam);

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

                    Dtt = ObjMain.NewGetData_DT(newquery1, parameters);
                  

                    DataTable tmpDTpaymentheader = new DataTable("paymentheader");

                    DataTable tempDT = new DataTable("tempDT");

                    DataColumn dtColumn;
                    DataRow myDataRow;

                    if (Dtt.Rows.Count > 0)
                    {



                        for (int j = 0; j < Dtt.Rows.Count; j++)
                        {
                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = Dtt.Rows[j]["PaymentType"].ToString();
                            dtColumn.ColumnName = Dtt.Rows[j]["PaymentType"].ToString();
                            dtColumn.ReadOnly = false;
                            tmpDTpaymentheader.Columns.Add(dtColumn);

                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = Dtt.Rows[j]["PaymentType"] + "Count";
                            tmpDTpaymentheader.Columns.Add(dtColumn);

                            if (j == 0)
                            {
                                myDataRow = tmpDTpaymentheader.NewRow();
                                myDataRow[Dtt.Rows[j]["PaymentType"].ToString()] = Math.Round(Convert.ToDecimal(Dtt.Rows[j]["Amount"].ToString()),2);
                                myDataRow[Dtt.Rows[j]["PaymentType"].ToString() + "Count"] = Dtt.Rows[j]["Count"].ToString();
                                // myDataRow["Count"] = Dtt.Rows[j]["Count"].ToString();
                                tmpDTpaymentheader.Rows.Add(myDataRow);

                                // count 



                            }
                            else
                            {
                                DataRow dr = tmpDTpaymentheader.Rows[0];
                                dr[Dtt.Rows[j]["PaymentType"].ToString()] =Math.Round(Convert.ToDecimal(Dtt.Rows[j]["Amount"].ToString()),2);
                                dr[Dtt.Rows[j]["PaymentType"].ToString() + "Count"] = Dtt.Rows[j]["Count"].ToString();


                            }
                        }




                        #endregion



                    #region    PayOutCount & PayOut Amount
                        //#3.  1] PayOutCount

                        string query2 = "select ifnull(count(Amount),0)as payoutcount from {0}.rapo where TransType=1  and BusinessDate between  @D1 and  @D2 ;";
                        string Subquery2 = string.Format(query2, BranchAliasNam);

                        MySqlParameter[] parameters2 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        string PayoutCnt = Convert.ToString(ObjMain.NewExecuteScalar(Subquery2, parameters2));


                        // 2] 
                        string query3 = "select ifnull(sum(Amount),0) as payout  from {0}.rapo where TransType=1  and BusinessDate between  @D1  and  @D2 ; ";
                        string subquery3 = string.Format(query3, BranchAliasNam);
                        MySqlParameter[] parameters3 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };


                        decimal PayoutAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery3, parameters3));


                        // 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_count1 = tmpDTpaymentheader.Columns.Count - 1;

                        dr2[row_count1] = PayoutCnt;


                        // 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_count2 = tmpDTpaymentheader.Columns.Count - 1;
                        PayoutAmt = Math.Round(PayoutAmt, 2);
                        dr3[row_count2] = PayoutAmt.ToString();

                        #endregion


                        #region Receiving Result 
                        //#4. Receiving Result 

                        //1.Receving Count
                        string query4 = "select ifnull(count(Amount) ,0) as receivecount  from {0}.rapo where TransType=0  and BusinessDate    between  @D1  and  @D2 ;";

                        string subquery4 = string.Format(query4, BranchAliasNam);

                        MySqlParameter[] parameters4 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        string ReceivingCnt = Convert.ToString(ObjMain.NewExecuteScalar(subquery4, parameters4));

                        //#2. Receving Amount 

                        string query5 = "select ifnull(sum(Amount),0) as receive from {0}.rapo where TransType=0  and BusinessDate      between  @D1 and  @D2 ;";
                        string subquery5 = string.Format(query5, BranchAliasNam);

                        MySqlParameter[] parameters5 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal ReceivingAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery5, parameters5));


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

                        dr2[row_count4] = ReceivingCnt;


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

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

                        #endregion


                        #region  Cashindrawer data Result 

                        // 5. Cashindrawer 


                        string query6 = "select * from (select  ifnull(SUM(Paymentamount),0) AS cash from {0}.transpayment as P inner join {0}.trans as t on P.transid=t.transid  where P.Paymenttype='CASH' and   P.businessdate between  @D1    and  @D2   and t.transstatus=2 )a,(select ifnull(sum(Amount),0) as takeRA from {0}.rapo where TransType in (4,0) and BusinessDate between @D1  and  @D2 )d,(select ifnull(sum(Amount),0) as po from {0}.rapo where TransType='1' and BusinessDate between @D1  and  @D2 )q ;";
                        string subquery6 = string.Format(query6, BranchAliasNam);

                        MySqlParameter[] parameters6 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        DataTable DtCashin = new DataTable();
                        DtCashin = ObjMain.NewGetData_DT(subquery6, parameters6);

                        decimal Cashin_Cash = 0;
                        decimal Cashin_TakeRA = 0;
                        decimal Cashin_Po = 0;
                        decimal CshinDraw = 0;



                        if (DtCashin.Rows.Count > 0)
                        {
                            Cashin_Cash = Convert.ToDecimal(DtCashin.Rows[0]["cash"].ToString());
                            Cashin_TakeRA = Convert.ToDecimal(DtCashin.Rows[0]["takeRA"].ToString());
                            Cashin_Po = Convert.ToDecimal(DtCashin.Rows[0]["po"].ToString());
                            // Calculation Logic :  cash +TakeRA-po    CashinDrawer = Cashin_Cash + Cashin_TakeRA - Cashin_Po
                            CshinDraw = Cashin_Cash + Cashin_TakeRA - Cashin_Po;

                        }


                        // 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;
                        CshinDraw = Math.Round(CshinDraw, 2);
                        dr6[row_count6] = CshinDraw.ToString();

                        #endregion

                        #region Rounding 
                        // #6. Rounding 

                        string query7 = "Select ifnull(Sum(A.Round),0) As rounding From {0}.Trans A Inner Join {0}.TransPayment B On A.TransID=B.TransID Where A.TransStatus = 2 And  B.BusinessDate between @D1  and  @D2 ;";
                        string subquery7 = string.Format(query7, BranchAliasNam);

                        MySqlParameter[] parameters7 =
                        {
                          new MySqlParameter("@D1",D1),
                          new MySqlParameter("@D2",D2),
                         };
                        decimal Rounding = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery7, parameters7));

                        // 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 Result set
                        // 7  Refund

                        string query8 = " select ifnull(sum(Amount),0) as refund from {0}.transdetail  left join {0}.trans on transdetail.TransID=trans.TransID where ItemStatus=2 and BusinessDate between  @D1  and  @D2 ;";
                        string subquery8 = string.Format(query8, BranchAliasNam);
                        MySqlParameter[] parameters8 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };


                        decimal Refund = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery8, parameters8));

                        // 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 Item Discount Result set 
                        //8. Item Discount 
                        string query9 = " select ifnull(sum(ManualDisc),0) as itemdisc from {0}.transdetail td left join {0}.trans t on t.TransID=td.TransID where t.TransStatus='2' and td.ItemStatus='0'and t.BusinessDate between @D1 and @D2 ;";
                        string subquer9 = string.Format(query9, BranchAliasNam);

                        MySqlParameter[] parameters9 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal ItemDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquer9, parameters9));


                        // 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;
                        ItemDisc = Math.Round(ItemDisc, 2);
                        dr9[row_count9] = ItemDisc.ToString();
                        #endregion


                        #region Total Discount 
                        // 9. Total Discount 

                        decimal dTotalDisc;
                        string query10 = " SELECT coalesce((SELECT sum(TotalDisc)  from {0}.transdetail B join {0}.Trans A on A.TransID = B.TransID where businessdate  between  @D1  and  @D2 and transstatus in (2,3,4)),0)as totaldisc;";
                        string subquery10 = string.Format(query10, BranchAliasNam);


                        MySqlParameter[] parameters10 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        dTotalDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery10, parameters10));


                        // 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;
                        dTotalDisc = Math.Round(dTotalDisc, 2);
                        dr10[row_count10] = dTotalDisc.ToString();

                        #endregion


                        #region Member Total Discount 
                        // 10. Member Total Discount 
                        string query11 = "Select ifnull(sum(MemberdiscAmount),0) as MemDiscAmt from {0}.Trans where businessdate between  @D1 and  @D2 ;";
                        string subquery11 = string.Format(query11, BranchAliasNam);

                        MySqlParameter[] parameters11 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        decimal MemDiscAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery11, parameters11));

                        // 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;
                        MemDiscAmt = Math.Round(MemDiscAmt, 2);
                        dr11[row_count11] = MemDiscAmt.ToString("c");


                        #endregion

                        #region  Total Price Shif Discount 
                        //12 Total Price Shif Discount 

                        string query12 = "Select ifnull(Sum(A.ActualPSDiscAmount),0) As SPriceShiftDiscount From {0}.TransDetail A Inner Join {0}.Trans B On A.TransID=B.TransID Where B.TransStatus = 2 And A.ItemStatus <>1 And B.BusinessDate  Between @D1 and  @D2 ; ";

                        string subquery12 = string.Format(query12, BranchAliasNam);

                        MySqlParameter[] parameters12 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal PriceShifDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery12, parameters12));

                        // 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;
                        MemDiscAmt = Math.Round(MemDiscAmt, 2);
                        dr12[row_count12] = MemDiscAmt.ToString();
                        #endregion

                        #region Total Discount 
                        // # 13. Total Discount 
                        // Formula : 
                        // DistTOT = ItemDisc + TotDisc + PriceShifDisc + MemDiscAmt;
                        decimal DistTOT = ItemDisc + dTotalDisc + MemDiscAmt + PriceShifDisc;

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

                        #endregion


                        #region  GST Amount Result set 
                        // #14.  Gst amount 
                        decimal Gstamt = 0;
                        string TaxNameRef = string.Empty;
                        string query13 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 2;";
                        string subquery13 = string.Format(query13, BranchAliasNam);
                        string sTaxCol = Convert.ToString(ObjMain.ExecuteScalar(subquery13));

                        string GstAmountquery1 = " SELECT Taxname,(select  ifnull(sum(" + sTaxCol + "),0)   from " + BranchAliasNam + ".trans AS t where t.TRANSSTATUS IN (2,3,4) AND  t.businessdate between '" + D1 + "' and '" + D2 + "') as GSTTOTAL from " + BranchAliasNam + ".tax where taxid=2;";
                        string subquery14 = string.Format(GstAmountquery1, BranchAliasNam);

                        MySqlParameter[] parameters14 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        DataTable DtGst = new DataTable();
                        DtGst = ObjMain.NewGetData_DT(subquery14, parameters14);


                        if (DtGst.Rows.Count > 0)
                        {
                            Gstamt = Convert.ToDecimal(DtGst.Rows[0]["GSTTOTAL"].ToString());

                            if (Gstamt != 0)
                            {
                                if (DtGst.Rows[0]["Taxname"].ToString() == "Tax2")
                                {
                                    TaxNameRef = "GST";
                                }
                                else
                                {
                                    TaxNameRef = DtGst.Rows[0]["Taxname"].ToString();
                                }
                            }
                        }
                        else
                        {
                            Gstamt = 0;
                        }

                        if (Gstamt != 0)
                        {
                            // If the gst amount value > 0
                            // TaxNameRef
                            // Gstamt.ToString("c")


                            // Add the new column
                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = TaxNameRef;
                            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;
                            Gstamt = Math.Round(Gstamt, 2);
                            dr14[row_count14] = Gstamt.ToString();

                        }
                        else
                        {

                        }
                        #endregion


                        #region svc Result set
                        // 15.  svc Result set

                        decimal SvcAmt = 0;
                        string TaxNameRef1 = string.Empty;
                        string query15 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 1;";

                        string subquery15 = string.Format(query15, BranchAliasNam);
                        //MySqlCommand cmd = new MySqlCommand(newquery18, con);
                        string sTaxCol1 = Convert.ToString(ObjMain.ExecuteScalar(subquery15));


                        string SvcAmountquery1 = "SELECT Taxname,(select ifnull(sum(" + sTaxCol1 + "),0)  from {0}.trans where businessdate between  @D1 and  @D2  and transstatus in (2,3,4)) as SVC from {0}.tax where taxid=1;";
                        string newquery3 = string.Format(SvcAmountquery1, BranchAliasNam);

                        DataTable DtSVC = new DataTable();
                        MySqlParameter[] parameters15 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        DtSVC = ObjMain.NewGetData_DT(newquery3, parameters15);

                        if (DtSVC.Rows.Count > 0)
                        {
                            //  SvcAmt = Convert.ToDecimal(DtSVC.Rows[0]["svc"].ToString());

                            SvcAmt = Convert.ToDecimal(DtSVC.Rows[0]["SVC"].ToString());


                            if (DtSVC.Rows[0]["Taxname"].ToString() == "Tax1")
                            {
                                TaxNameRef1 = "SVC(10%)";
                            }
                            else
                            {
                                TaxNameRef1 = DtSVC.Rows[0]["Taxname"].ToString();
                            }

                        }
                        else
                        {
                            SvcAmt = 0;
                        }


                        if (SvcAmt != 0)
                        {
                            // TaxNameRef
                            // SvcAmt.ToString("c")
                            // Pass the value into excel column 


                            // Add the new column
                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = TaxNameRef1;
                            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;
                            SvcAmt = Math.Round(SvcAmt, 2);
                            dr15[row_count15] = SvcAmt.ToString();
                        }
                        else
                        {
                        }

                        #endregion


                        #region  Tax3 Result set   & Vocher Result set value 
                        //# 15. Tax3 - Result set

                        string TaxNameRef3 = string.Empty;
                        decimal Tax3Amt = 0;
                        string query16 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 3;";

                        string subquery16 = string.Format(query16, BranchAliasNam);
                        string sTaxCol2 = Convert.ToString(ObjMain.ExecuteScalar(subquery16));

                        // Existing Code 
                        // string sTaxCol = ObjMain.ExecuteScalar("seLECT TAXCOLNAME FROM " + sDB + ".Taxnameidentifier WHERE TAXID = 3; ");


                        string GstAmountquery2 = " SELECT Taxname,(select  ifnull(sum(" + sTaxCol2 + "),0)   from {0}.trans AS t where t.TRANSSTATUS IN (2,3,4) AND  t.businessdate between  @D1 and @D2 ) as GSTTOTAL from {0}.tax where taxid=3;";
                        string newquery4 = string.Format(GstAmountquery2, BranchAliasNam);
                        DataTable DtTax3 = new DataTable();

                        MySqlParameter[] parameters16 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        DtTax3 = ObjMain.NewGetData_DT(newquery4, parameters16);

                        // Voucher Result Set value 
                        string Vouchcquery1 = " Select ifnull(Sum(B.TaxableAmount+B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6),0) AS Voucher From   {0}.Trans A Left Join {0}.TransDetail B On A.TransID=B.TransID Where  A.TransStatus = 2 And A.VoucherTotal > 0 And B.Amount < 0  And B.ItemStatus=0  And A.BusinessDate between  @D1  and @D2 ;";

                        string subquer17 = string.Format(Vouchcquery1, BranchAliasNam);
                        //string Vouchcquery1 = "Select Sum(B.Amount) as Voucher ,Name As Nam From " + BranchAliasNam + ".Trans A left join " + BranchAliasNam + ".TransDetail B   on A.TransID=B.TransID Where  A.TransStatus = 2  And A.BusinessDate  between '" + D1 + "' and '" + D2 + "' And A.VoucherTotal>0 And B.Amount<0 And B.ItemStatus<>1 Group By Name";
                        MySqlParameter[] parameters17 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal VoucherAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquer17, parameters17));

                        if (DtTax3.Rows.Count > 0)
                        {
                            Tax3Amt = Convert.ToDecimal(DtTax3.Rows[0]["GSTTOTAL"].ToString());

                            if (Tax3Amt != 0)
                            {
                                if (DtTax3.Rows[0]["Taxname"].ToString() == "Tax3")
                                {
                                    TaxNameRef3 = "TAX 3";
                                }
                                else
                                {
                                    TaxNameRef3 = DtTax3.Rows[0]["Taxname"].ToString();
                                }
                            }
                        }
                        else
                        {
                            Tax3Amt = 0;
                        }

                        if (Tax3Amt != 0)
                        {
                            // TaxNameRef3
                            // Tax3Amt.ToString("c")

                            // Add the new column
                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = TaxNameRef3;
                            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;
                            Tax3Amt = Math.Round(Tax3Amt,2);
                            dr16[row_count16] = Tax3Amt.ToString();


                        }
                        else
                        {

                        }

                        #endregion

                        #region Voucher Data Result  
                        // 18. Voucher Data Result  
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Voucher Amount";
                        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;
                        VoucherAmt = Math.Round(VoucherAmt, 2);
                        dr18[row_count18] = VoucherAmt.ToString();

                        #endregion

                        #region   Total Sale 
                        //#2 Total Sale 

                        string query1 = "Select ifnull(sum(TP.PaymentAmount),0) as salesamount From {0}.trans t join  {0}.transpayment tp on t.transid = tp.transid where t.transstatus in (2,3,4) AND  tp.businessdate between  @D1 and   @D2 ;";
                        string newquery2 = string.Format(query1, BranchAliasNam);
                        MySqlParameter[] parameters1 =
                        {
                        new MySqlParameter("@D1",D1),
                        new MySqlParameter("@D2",D2), 
                        };

                        decimal  Tot_Sales = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery2, parameters1));
                        //lblRTotalSale.Text = Tot_Sales.ToString("c");
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(decimal);
                        dtColumn.ColumnName = "Total Sale";
                        dtColumn.ReadOnly = false;
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        // Add the row value 
                        DataRow dr1 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count = tmpDTpaymentheader.Columns.Count - 1;

                        Tot_Sales = Math.Round(Tot_Sales, 2);

                         dr1[row_count] =Tot_Sales.ToString();
                       
                        #endregion

                        #region Net Amount 
                        // 17. Net Amount 


                        decimal NetAmt = Tot_Sales - (Gstamt + SvcAmt + Tax3Amt);
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Net Amount";
                        dtColumn.ReadOnly = false;
                        // Add the row value 
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        DataRow dr17 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count17 = tmpDTpaymentheader.Columns.Count - 1;
                        NetAmt = Math.Round(NetAmt, 2);
                        dr17[row_count17] = NetAmt.ToString();

                        #endregion

                        #region  Gross Amount 
                        //23. Gross Amount 

                        decimal cGrossSales;
                        cGrossSales = (Tot_Sales) - ( Refund) - (VoucherAmt);
                        decimal GrossSales = cGrossSales + Rounding + DistTOT;


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

                        #endregion


                        #region  Hold Count  & Hold Amount 
                        //# 18. Hold Count 
                        string Holdcntcquery1 = "select coalesce((select count(TransStatus)  from {0}.trans where TransStatus in (3,4) and BusinessDate between @D1 and  @D2 ),0) as holdcount";
                        string subquery19 = string.Format(Holdcntcquery1, BranchAliasNam);
                        MySqlParameter[] parameters19 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        string Holdcnt = Convert.ToString(ObjMain.NewExecuteScalar(subquery19, parameters19));

                        // Assign the value to the excel results 
                        // 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 dr19 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count19 = tmpDTpaymentheader.Columns.Count - 1;
                        dr19[row_count19] = Holdcnt.ToString();



                        string HoldAmtcquery1 = "select coalesce((select ifnull(sum(Total),0) as hold  from {0}.trans where TransStatus in (3,4) and BusinessDate between @D1 and  @D2),0) as hold;";
                        string subquery20 = string.Format(HoldAmtcquery1, BranchAliasNam);
                        MySqlParameter[] parameter20 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal HoldAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery20, parameter20));
                        // Assign the value to the excel results 

                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Hold Amount";
                        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;
                        HoldAmt = Math.Round(HoldAmt, 2);
                        dr20[row_count20] = HoldAmt.ToString();

                        #endregion


                        #region Cancel Count & Cancel Amount 
                        //19  Cancel Count & Cancel Amount 
                        //1.Cancel Count
                        string Cancecntcquery1 = "select coalesce((select ifnull(count(TransStatus),0)  from {0}.trans where TransStatus=5 and BusinessDate between  @D1  and  @D2 ),0)  as cancelcount";
                        string subquery21 = string.Format(Cancecntcquery1, BranchAliasNam);


                        MySqlParameter[] parameters21 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        string CancelCnt = Convert.ToString(ObjMain.NewExecuteScalar(subquery21, parameters21));

                        // Assing the Cancel Count value 
                        // 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 dr21 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count21 = tmpDTpaymentheader.Columns.Count - 1;
                        dr21[row_count21] = CancelCnt.ToString();


                        //1.Cancel Amount 

                        string CancelAmt = "select coalesce((select  ifnull(sum(Total),0)   from {0}.trans where TransStatus=5 and BusinessDate between  @D1  and @D2 ),0) as cancel;";
                        string subquery22 = string.Format(CancelAmt, BranchAliasNam);
                        MySqlParameter[] parameters22 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        decimal CancelAmtVal = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery22, parameters22));


                        // Assing the Cancel Amount value 
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Cancel Amount";
                        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;
                        CancelAmtVal = Math.Round(CancelAmtVal, 2);
                        dr22[row_count22] = CancelAmtVal.ToString();

                        #endregion

                        #region Void Count & Void Amount 
                        // #20  Void Count & Void Amount 
                        // 1.Void Count

                        string Voidcntcquery1 = "select coalesce((select count(Amount) from {0}.transdetail where  itemstatus='1' and date(VoidDate) between  @D1  and   @D2 ),0) as voidcount;";
                        string subquery23 = string.Format(Voidcntcquery1, BranchAliasNam);

                        MySqlParameter[] parameters23 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        string VoidCnt = Convert.ToString(ObjMain.NewExecuteScalar(subquery23, parameters23));

                        // Assign the void count 

                        // 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 dr23 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count23 = tmpDTpaymentheader.Columns.Count - 1;
                        dr23[row_count23] = VoidCnt.ToString();


                        //#2. Void Amount 
                        string VoidlAmtquery = "select coalesce((select sum(Amount) from {0}.transdetail where  itemstatus='1' and date(VoidDate) between  @D1  and   @D2),0) as void";
                        string subquery24 = string.Format(VoidlAmtquery, BranchAliasNam);

                        MySqlParameter[] parameters24 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal VoidAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery24, parameters24));

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

                        #region   Exchange Amount 
                        //21. Exchange Amount 
                        string ExchAmtcquery1 = "select coalesce((select sum(Amount) as exchange from {0}.transdetail  where ItemStatus=4 and date(VoidDate) between  @D1 and  @D2),0) as exchange";
                        string subquery25 = string.Format(ExchAmtcquery1, BranchAliasNam);

                        MySqlParameter[] parameters25 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        decimal ExAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery25, parameters25));

                        // Assign the Exchange Amount 

                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Exchange Amount";
                        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;
                        ExAmt = Math.Round(ExAmt, 2);
                        dr25[row_count25] = ExAmt.ToString();
                        #endregion


                        #region No.of Sales open 
                        // #22.  No.of Sales open 

                        string NoSalesPersoquery1 = "select coalesce((select count(Notes) from {0}.rapo as NoSalesOpen where TransType=2  and BusinessDate between  @D1  and  @D2 ),0) as nosalesopen";
                        string subquery26 = string.Format(NoSalesPersoquery1, BranchAliasNam);


                        MySqlParameter[] parameters26 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        string NoSalesopen = Convert.ToString(ObjMain.NewExecuteScalar(subquery26, parameters26));


                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "No Sales Open";
                        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] = NoSalesopen.ToString();

                        #endregion

                        #region  NO OF Receipts 
                        // 24.  NO OF Receipts 

                        string NoofReceiptsqry = "select * from (select count(TransID) as noofreceipts from {0}.trans where BusinessDate between @D1  and  @D2 )f,(select sum(PaymentAmount) as salesamount from {0}.transpayment where BusinessDate between @D1  and  @D2 )s";
                        string subquery28 = string.Format(NoofReceiptsqry, BranchAliasNam);

                        MySqlParameter[] parameters28 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        string NoofReceipts = Convert.ToString(ObjMain.NewExecuteScalar(subquery28, parameters28));


                        // Assign the Receipts values to excel
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "No. Of Receipts";
                        dtColumn.ReadOnly = false;
                        // Add the row value 
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        DataRow dr28 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count28 = tmpDTpaymentheader.Columns.Count - 1;
                        dr28[row_count28] = NoofReceipts.ToString();

                        #endregion

                        #region Avg Receipts 
                        // 25. Avg Receipts 
                        decimal AvgReceipts = 0;
                        if (Tot_Sales != 0)
                        {
                            AvgReceipts = Tot_Sales / Convert.ToDecimal(NoofReceipts);

                        }
                        else
                        {

                            AvgReceipts = 0;
                        }

                        // Assign the Avg Receipts values to excel
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Receipts Avg";
                        dtColumn.ReadOnly = false;
                        // Add the row value 
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        DataRow dr29 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count29 = tmpDTpaymentheader.Columns.Count - 1;
                        AvgReceipts = Math.Round(AvgReceipts, 2);
                        dr29[row_count29] = AvgReceipts.ToString();

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


                    }
                    else
                    {
                        ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' No Data Found between these date Range ..! '); ", true);
                    }
     

                     
                    #endregion


                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {

                }

            }



        }

        #endregion


        #region  Date wise Export 
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            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 )
            cd_BranchAlias = Cmb_BranchName;

            LoadConsolidatedExcelReport(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
        }

        protected void btnDatawiseExport_Click(object sender, EventArgs e)
        {

            // Put the Date Condition Here 

             //string  _startDT = txtStartDt.Text;
            DateTime _start_date = Convert.ToDateTime(txtStartDt.Text).AddDays(31);
            DateTime _End_date = Convert.ToDateTime(txtEndDt.Text);

            if (_End_date <= _start_date)
            {


                string result = GenFunction();
                // Json Export 
                // Convert json string to DataTable 
                DataTable dtResult = new DataTable();
                // dtResult = objConvertJson2dataTable.JsonStringToDataTable(result);
                DataTable _new_result = new DataTable();
                dynamic dynJson = JsonConvert.DeserializeObject(result);

                if (dynJson != null)
                {
                    foreach (var item in dynJson)
                    {
                        // string a=  item["CASH"];

                        string _xi = "[" + Convert.ToString(item) + "]";
                        dtResult = objConvertJson2dataTable.JsonStringToDataTable(_xi);

                        if (_new_result.Rows.Count > 0)
                        {
                            _new_result.Merge(dtResult);
                        }
                        else
                        {
                            _new_result = dtResult;
                        }
                    }
                    _new_result.AcceptChanges();

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

                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('No Data Found between these date Range ..! '); ", true);
                }

            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('Date Range shouldn't exceeds 31 Days ..!'); ", true);
                lblmonthlyerrormsg.Text = "Date Range shouldn't exceeds 31 Days for Monthly Export ";
                


            }
       
        }

        public string GenFunction()
        {
            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 )
            cd_BranchAlias = Cmb_BranchName;


            string ax = "";
            Session["jsonstring"] = null;
            for (DateTime i = Convert.ToDateTime(txtStartDt.Text); i <= Convert.ToDateTime(txtEndDt.Text); i = i.AddDays(1))
            {
                Convert.ToDateTime(i).ToShortDateString();

                string formatted_date = i.ToString("yyyy-MM-dd");

                ax = CallFunction(cd_BranchAlias, formatted_date, formatted_date);

            }

            return ax;

        }

        public string CallFunction(string BranchAliasNam, string D1, string D2)
        {
            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {

                try
                {

                    #region  PaymentType Result set 





                    string query = "Select PaymentType,count(Paymenttype) as Count,ifnull(sum(paymentamount),0) as Amount From {0}.trans t    join {0}.transpayment tp on t.transid = tp.transid where  t.transstatus in (2,3,4) AND    tp.businessdate between  @D1 and  @D2  group by PaymentType";

                    string newquery1 = string.Format(query, BranchAliasNam);

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



                    Dtt = ObjMain.NewGetData_DT(newquery1, parameters);

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

                    DataColumn dtColumn;
                    DataRow myDataRow;

                    // Adding date column 
                    dtColumn = new DataColumn();
                    dtColumn.DataType = typeof(string);
                    dtColumn.ColumnName = "Date";
                    dtColumn.ReadOnly = false;
                    dtColumn.DefaultValue = D1;

                    // Add the row value 
                    tmpDTpaymentheader.Columns.Add(dtColumn);


                    if (Dtt.Rows.Count > 0)
                    {


                        for (int j = 0; j < Dtt.Rows.Count; j++)
                        {
                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = Dtt.Rows[j]["PaymentType"].ToString();
                            dtColumn.ColumnName = Dtt.Rows[j]["PaymentType"].ToString();
                            dtColumn.ReadOnly = false;
                            tmpDTpaymentheader.Columns.Add(dtColumn);

                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = Dtt.Rows[j]["PaymentType"] + "Count";
                            tmpDTpaymentheader.Columns.Add(dtColumn);

                            if (j == 0)
                            {
                                myDataRow = tmpDTpaymentheader.NewRow();
                                myDataRow[Dtt.Rows[j]["PaymentType"].ToString()] = Math.Round(Convert.ToDecimal(Dtt.Rows[j]["Amount"].ToString()), 2);
                                myDataRow[Dtt.Rows[j]["PaymentType"].ToString() + "Count"] = Dtt.Rows[j]["Count"].ToString();
                                tmpDTpaymentheader.Rows.Add(myDataRow);

                            }
                            else
                            {
                                DataRow dr = tmpDTpaymentheader.Rows[0];
                                dr[Dtt.Rows[j]["PaymentType"].ToString()] = Math.Round(Convert.ToDecimal(Dtt.Rows[j]["Amount"].ToString()), 2);
                                dr[Dtt.Rows[j]["PaymentType"].ToString() + "Count"] = Dtt.Rows[j]["Count"].ToString();


                            }
                        }

                        #endregion



                        #region    PayOutCount & PayOut Amount
                        //#3.  1] PayOutCount

                        string query2 = "select ifnull(count(Amount),0)as payoutcount from {0}.rapo where TransType=1  and BusinessDate between  @D1 and  @D2 ;";
                        string Subquery2 = string.Format(query2, BranchAliasNam);

                        MySqlParameter[] parameters2 =
                        {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                        string PayoutCnt = Convert.ToString(ObjMain.NewExecuteScalar(Subquery2, parameters2));


                        // 2] 
                        string query3 = "select ifnull(sum(Amount),0) as payout  from {0}.rapo where TransType=1  and BusinessDate between  @D1  and  @D2 ; ";
                        string subquery3 = string.Format(query3, BranchAliasNam);
                        MySqlParameter[] parameters3 =
                        {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };


                        decimal PayoutAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery3, parameters3));


                        // 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_count1 = tmpDTpaymentheader.Columns.Count - 1;

                        dr2[row_count1] = PayoutCnt;


                        // 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_count2 = tmpDTpaymentheader.Columns.Count - 1;
                        // Rounds from the big value
                        PayoutAmt = Math.Round(PayoutAmt);
                        dr3[row_count2] = PayoutAmt.ToString();

                        #endregion


                        #region Receiving Result 
                        //#4. Receiving Result 

                        //1.Receving Count
                        string query4 = "select ifnull(count(Amount) ,0) as receivecount  from {0}.rapo where TransType=0  and BusinessDate    between  @D1  and  @D2 ;";

                        string subquery4 = string.Format(query4, BranchAliasNam);

                        MySqlParameter[] parameters4 =
                       {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                        string ReceivingCnt = Convert.ToString(ObjMain.NewExecuteScalar(subquery4, parameters4));

                        //#2. Receving Amount 

                        string query5 = "select ifnull(sum(Amount),0) as receive from {0}.rapo where TransType=0  and BusinessDate      between  @D1 and  @D2 ;";
                        string subquery5 = string.Format(query5, BranchAliasNam);

                        MySqlParameter[] parameters5 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                       };

                        decimal ReceivingAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery5, parameters5));


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

                        dr2[row_count4] = ReceivingCnt;


                        // 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;
                        ReceivingAmt = Math.Round(ReceivingAmt, 2);
                        dr5[row_count5] = ReceivingAmt.ToString();

                        #endregion


                        #region  Cashindrawer data Result 

                        // 5. Cashindrawer 


                        string query6 = "select * from (select  ifnull(SUM(Paymentamount),0) AS cash from {0}.transpayment as P inner join {0}.trans as t on P.transid=t.transid  where P.Paymenttype='CASH' and   P.businessdate between  @D1    and  @D2   and t.transstatus=2 )a,(select ifnull(sum(Amount),0) as takeRA from {0}.rapo where TransType in (4,0) and BusinessDate between @D1  and  @D2 )d,(select ifnull(sum(Amount),0) as po from {0}.rapo where TransType='1' and BusinessDate between @D1  and  @D2 )q ;";
                        string subquery6 = string.Format(query6, BranchAliasNam);

                        MySqlParameter[] parameters6 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        DataTable DtCashin = new DataTable();
                        DtCashin = ObjMain.NewGetData_DT(subquery6, parameters6);

                        decimal Cashin_Cash = 0;
                        decimal Cashin_TakeRA = 0;
                        decimal Cashin_Po = 0;
                        decimal CshinDraw = 0;



                        if (DtCashin.Rows.Count > 0)
                        {
                            Cashin_Cash = Convert.ToDecimal(DtCashin.Rows[0]["cash"].ToString());
                            Cashin_TakeRA = Convert.ToDecimal(DtCashin.Rows[0]["takeRA"].ToString());
                            Cashin_Po = Convert.ToDecimal(DtCashin.Rows[0]["po"].ToString());
                            // Calculation Logic :  cash +TakeRA-po    CashinDrawer = Cashin_Cash + Cashin_TakeRA - Cashin_Po
                            CshinDraw = Cashin_Cash + Cashin_TakeRA - Cashin_Po;

                        }


                        // 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;
                        CshinDraw = Math.Round(CshinDraw, 2);
                        dr6[row_count6] = CshinDraw.ToString();

                        #endregion

                        #region Rounding 
                        // #6. Rounding 

                        string query7 = "Select ifnull(Sum(A.Round),0) As rounding From {0}.Trans A Inner Join {0}.TransPayment B On A.TransID=B.TransID Where A.TransStatus = 2 And  B.BusinessDate between @D1  and  @D2 ;";
                        string subquery7 = string.Format(query7, BranchAliasNam);

                        MySqlParameter[] parameters7 =
                        {
                          new MySqlParameter("@D1",D1),
                          new MySqlParameter("@D2",D2),
                         };
                        decimal Rounding = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery7, parameters7));

                        // 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 Result set
                        // 7  Refund

                        string query8 = " select ifnull(sum(Amount),0) as refund from {0}.transdetail  left join {0}.trans on transdetail.TransID=trans.TransID where ItemStatus=2 and BusinessDate between  @D1  and  @D2 ;";
                        string subquery8 = string.Format(query8, BranchAliasNam);
                        MySqlParameter[] parameters8 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };


                        decimal Refund = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery8, parameters8));

                        // 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 Item Discount Result set 
                        //8. Item Discount 
                        string query9 = " select ifnull(sum(ManualDisc),0) as itemdisc from {0}.transdetail td left join {0}.trans t on t.TransID=td.TransID where t.TransStatus='2' and td.ItemStatus='0'and t.BusinessDate between @D1 and @D2 ;";
                        string subquer9 = string.Format(query9, BranchAliasNam);

                        MySqlParameter[] parameters9 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal ItemDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquer9, parameters9));


                        // 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;
                        // math fun
                        ItemDisc = Math.Round(ItemDisc, 2);
                        dr9[row_count9] = ItemDisc.ToString();
                        #endregion


                        #region Total Discount 
                        // 9. Total Discount 

                        decimal dTotalDisc;
                        string query10 = " SELECT coalesce((SELECT sum(TotalDisc)  from {0}.transdetail B join {0}.Trans A on A.TransID = B.TransID where businessdate  between  @D1  and  @D2 and transstatus in (2,3,4)),0)as totaldisc;";
                        string subquery10 = string.Format(query10, BranchAliasNam);


                        MySqlParameter[] parameters10 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        dTotalDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery10, parameters10));


                        // 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;
                        // math fun
                        dTotalDisc = Math.Round(dTotalDisc, 2);
                        dr10[row_count10] = dTotalDisc.ToString();

                        #endregion


                        #region Member Total Discount 
                        // 10. Member Total Discount 
                        string query11 = "Select ifnull(sum(MemberdiscAmount),0) as MemDiscAmt from {0}.Trans where businessdate between  @D1 and  @D2 ;";
                        string subquery11 = string.Format(query11, BranchAliasNam);

                        MySqlParameter[] parameters11 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        decimal MemDiscAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery11, parameters11));

                        // 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;
                        // math fun 

                        MemDiscAmt = Math.Round(MemDiscAmt, 2);
                        dr11[row_count11] = MemDiscAmt.ToString();


                        #endregion

                        #region  Total Price Shif Discount 
                        //12 Total Price Shif Discount 

                        string query12 = "Select ifnull(Sum(A.ActualPSDiscAmount),0) As SPriceShiftDiscount From {0}.TransDetail A Inner Join {0}.Trans B On A.TransID=B.TransID Where B.TransStatus = 2 And A.ItemStatus <>1 And B.BusinessDate  Between @D1 and  @D2 ; ";

                        string subquery12 = string.Format(query12, BranchAliasNam);

                        MySqlParameter[] parameters12 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal PriceShifDisc = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery12, parameters12));

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

                        // math fun 
                        MemDiscAmt = Math.Round(MemDiscAmt, 2);

                        dr12[row_count12] = MemDiscAmt.ToString();
                        #endregion



                        #region Total Discount 
                        // # 13. Total Discount 
                        // Formula : 
                        // DistTOT = ItemDisc + TotDisc + PriceShifDisc + MemDiscAmt;
                        decimal DistTOT = ItemDisc + dTotalDisc + MemDiscAmt + PriceShifDisc;

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

                        #endregion


                        #region  GST Amount Result set 
                        // #14.  Gst amount 
                        decimal Gstamt = 0;
                        string TaxNameRef = string.Empty;
                        string query13 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 2;";
                        string subquery13 = string.Format(query13, BranchAliasNam);
                        string sTaxCol = Convert.ToString(ObjMain.ExecuteScalar(subquery13));

                        string GstAmountquery1 = " SELECT Taxname,(select  ifnull(sum(" + sTaxCol + "),0)   from " + BranchAliasNam + ".trans AS t where t.TRANSSTATUS IN (2,3,4) AND  t.businessdate between '" + D1 + "' and '" + D2 + "') as GSTTOTAL from " + BranchAliasNam + ".tax where taxid=2;";
                        string subquery14 = string.Format(GstAmountquery1, BranchAliasNam);

                        MySqlParameter[] parameters14 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        DataTable DtGst = new DataTable();
                        DtGst = ObjMain.NewGetData_DT(subquery14, parameters14);


                        if (DtGst.Rows.Count > 0)
                        {
                            Gstamt = Convert.ToDecimal(DtGst.Rows[0]["GSTTOTAL"].ToString());

                            if (Gstamt != 0)
                            {
                                if (DtGst.Rows[0]["Taxname"].ToString() == "Tax2")
                                {
                                    TaxNameRef = "GST";
                                }
                                else
                                {
                                    TaxNameRef = DtGst.Rows[0]["Taxname"].ToString();
                                }
                            }
                        }
                        else
                        {
                            Gstamt = 0;
                        }

                        if (Gstamt != 0)
                        {
                            // If the gst amount value > 0
                            // TaxNameRef
                            // Gstamt.ToString("c")


                            // Add the new column
                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = TaxNameRef;
                            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;
                            // math fun 
                            Gstamt = Math.Round(Gstamt, 2);
                            dr14[row_count14] = Gstamt.ToString();

                        }
                        else
                        {

                        }
                        #endregion


                        #region svc Result set
                        // 15.  svc Result set

                        decimal SvcAmt = 0;
                        string TaxNameRef1 = string.Empty;
                        string query15 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 1;";

                        string subquery15 = string.Format(query15, BranchAliasNam);
                        //MySqlCommand cmd = new MySqlCommand(newquery18, con);
                        string sTaxCol1 = Convert.ToString(ObjMain.ExecuteScalar(subquery15));


                        string SvcAmountquery1 = "SELECT Taxname,(select ifnull(sum(" + sTaxCol1 + "),0)  from {0}.trans where businessdate between  @D1 and  @D2  and transstatus in (2,3,4)) as SVC from {0}.tax where taxid=1;";
                        string newquery3 = string.Format(SvcAmountquery1, BranchAliasNam);

                        DataTable DtSVC = new DataTable();
                        MySqlParameter[] parameters15 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        DtSVC = ObjMain.NewGetData_DT(newquery3, parameters15);

                        if (DtSVC.Rows.Count > 0)
                        {
                            //  SvcAmt = Convert.ToDecimal(DtSVC.Rows[0]["svc"].ToString());

                            SvcAmt = Convert.ToDecimal(DtSVC.Rows[0]["SVC"].ToString());


                            if (DtSVC.Rows[0]["Taxname"].ToString() == "Tax1")
                            {
                                TaxNameRef1 = "SVC(10%)";
                            }
                            else
                            {
                                TaxNameRef1 = DtSVC.Rows[0]["Taxname"].ToString();
                            }

                        }
                        else
                        {
                            SvcAmt = 0;
                        }


                        if (SvcAmt != 0)
                        {
                            // TaxNameRef
                            // SvcAmt.ToString("c")
                            // Pass the value into excel column 


                            // Add the new column
                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = TaxNameRef1;
                            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;
                            // math fun 
                            SvcAmt = Math.Round(SvcAmt, 2);
                            dr15[row_count15] = SvcAmt.ToString();



                        }
                        else
                        {
                        }

                        #endregion


                        #region  Tax3 Result set   & Vocher Result set value 
                        //# 15. Tax3 - Result set

                        string TaxNameRef3 = string.Empty;
                        decimal Tax3Amt = 0;
                        string query16 = "seLECT TAXCOLNAME FROM {0}.Taxnameidentifier WHERE TAXID = 3;";

                        string subquery16 = string.Format(query16, BranchAliasNam);
                        string sTaxCol2 = Convert.ToString(ObjMain.ExecuteScalar(subquery16));

                        // Existing Code 
                        // string sTaxCol = ObjMain.ExecuteScalar("seLECT TAXCOLNAME FROM " + sDB + ".Taxnameidentifier WHERE TAXID = 3; ");


                        string GstAmountquery2 = " SELECT Taxname,(select  ifnull(sum(" + sTaxCol2 + "),0)   from {0}.trans AS t where t.TRANSSTATUS IN (2,3,4) AND  t.businessdate between  @D1 and @D2 ) as GSTTOTAL from {0}.tax where taxid=3;";
                        string newquery4 = string.Format(GstAmountquery2, BranchAliasNam);
                        DataTable DtTax3 = new DataTable();

                        MySqlParameter[] parameters16 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        DtTax3 = ObjMain.NewGetData_DT(newquery4, parameters16);

                        // Voucher Result Set value 
                        string Vouchcquery1 = " Select ifnull(Sum(B.TaxableAmount+B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6),0) AS Voucher From   {0}.Trans A Left Join {0}.TransDetail B On A.TransID=B.TransID Where  A.TransStatus = 2 And A.VoucherTotal > 0 And B.Amount < 0  And B.ItemStatus=0  And A.BusinessDate between  @D1  and @D2 ;";

                        string subquer17 = string.Format(Vouchcquery1, BranchAliasNam);
                        //string Vouchcquery1 = "Select Sum(B.Amount) as Voucher ,Name As Nam From " + BranchAliasNam + ".Trans A left join " + BranchAliasNam + ".TransDetail B   on A.TransID=B.TransID Where  A.TransStatus = 2  And A.BusinessDate  between '" + D1 + "' and '" + D2 + "' And A.VoucherTotal>0 And B.Amount<0 And B.ItemStatus<>1 Group By Name";
                        MySqlParameter[] parameters17 =
                        {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

                        decimal VoucherAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquer17, parameters17));





                        if (DtTax3.Rows.Count > 0)
                        {
                            Tax3Amt = Convert.ToDecimal(DtTax3.Rows[0]["GSTTOTAL"].ToString());

                            if (Tax3Amt != 0)
                            {
                                if (DtTax3.Rows[0]["Taxname"].ToString() == "Tax3")
                                {
                                    TaxNameRef3 = "TAX 3";
                                }
                                else
                                {
                                    TaxNameRef3 = DtTax3.Rows[0]["Taxname"].ToString();
                                }
                            }
                        }
                        else
                        {
                            Tax3Amt = 0;
                        }

                        if (Tax3Amt != 0)
                        {
                            // TaxNameRef3
                            // Tax3Amt.ToString("c")

                            // Add the new column
                            dtColumn = new DataColumn();
                            dtColumn.DataType = typeof(string);
                            dtColumn.ColumnName = TaxNameRef3;
                            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;

                            // math fun 
                            Tax3Amt = Math.Round(Tax3Amt, 2);
                            dr16[row_count16] = Tax3Amt.ToString();


                        }
                        else
                        {

                        }

                        #endregion

                        #region Voucher Data Result  
                        // 18. Voucher Data Result  
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Voucher Amount";
                        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;
                        // math fun 
                        VoucherAmt = Math.Round(VoucherAmt, 2);
                        dr18[row_count18] = VoucherAmt.ToString();

                        #endregion

                        #region   Total Sale 
                        //#2 Total Sale 

                        string query1 = "Select ifnull(sum(TP.PaymentAmount),0) as salesamount From {0}.trans t join  {0}.transpayment tp on t.transid = tp.transid where t.transstatus in (2,3,4) AND  tp.businessdate between  @D1 and   @D2 ;";
                        string newquery2 = string.Format(query1, BranchAliasNam);
                        MySqlParameter[] parameters1 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal Tot_Sales = Convert.ToDecimal(ObjMain.NewExecuteScalar(newquery2, parameters1));
                        //lblRTotalSale.Text = Tot_Sales.ToString("c");
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(decimal);
                        dtColumn.ColumnName = "Total Sale";
                        dtColumn.ReadOnly = false;
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        // Add the row value 
                        DataRow dr1 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count = tmpDTpaymentheader.Columns.Count - 1;
                        // math fun 
                        Tot_Sales = Math.Round(Tot_Sales, 2);
                        dr1[row_count] = Tot_Sales.ToString();

                        #endregion

                        #region Net Amount 
                        // 17. Net Amount 
                        decimal NetAmt = Tot_Sales - (Gstamt + SvcAmt + Tax3Amt);
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Net Amount";
                        dtColumn.ReadOnly = false;
                        // Add the row value 
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        DataRow dr17 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count17 = tmpDTpaymentheader.Columns.Count - 1;
                        // math fun 
                        NetAmt = Math.Round(NetAmt, 2);
                        dr17[row_count17] = NetAmt.ToString();


                        #endregion

                        #region  Gross Amount 
                        //23. Gross Amount 

                        decimal cGrossSales;
                        cGrossSales = Tot_Sales - Refund - VoucherAmt;
                        decimal GrossSales = cGrossSales + Rounding + DistTOT;


                        // Add the new column 
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Gross Amount";
                        dtColumn.ReadOnly = false;
                        // Add the row value 
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        DataRow dr27 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count27 = tmpDTpaymentheader.Columns.Count - 1;
                        // math func
                        GrossSales = Math.Round(GrossSales, 2);
                        dr27[row_count27] = GrossSales.ToString();

                        #endregion


                        #region  Hold Count  & Hold Amount 
                        //# 18. Hold Count 
                        string Holdcntcquery1 = "select coalesce((select count(TransStatus)  from {0}.trans where TransStatus in (3,4) and BusinessDate between @D1 and  @D2 ),0) as holdcount";
                        string subquery19 = string.Format(Holdcntcquery1, BranchAliasNam);
                        MySqlParameter[] parameters19 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        string Holdcnt = Convert.ToString(ObjMain.NewExecuteScalar(subquery19, parameters19));

                        // Assign the value to the excel results 
                        // 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 dr19 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count19 = tmpDTpaymentheader.Columns.Count - 1;
                        dr19[row_count19] = Holdcnt.ToString();



                        string HoldAmtcquery1 = "select coalesce((select ifnull(sum(Total),0) as hold  from {0}.trans where TransStatus in (3,4) and BusinessDate between @D1 and  @D2),0) as hold;";
                        string subquery20 = string.Format(HoldAmtcquery1, BranchAliasNam);
                        MySqlParameter[] parameter20 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal HoldAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery20, parameter20));
                        // Assign the value to the excel results 

                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Hold Amount";
                        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;
                        // math fun
                        HoldAmt = Math.Round(HoldAmt, 2);
                        dr20[row_count20] = HoldAmt.ToString();

                        #endregion


                        #region Cancel Count & Cancel Amount 
                        //19  Cancel Count & Cancel Amount 
                        //1.Cancel Count
                        string Cancecntcquery1 = "select coalesce((select ifnull(count(TransStatus),0)  from {0}.trans where TransStatus=5 and BusinessDate between  @D1  and  @D2 ),0)  as cancelcount";
                        string subquery21 = string.Format(Cancecntcquery1, BranchAliasNam);


                        MySqlParameter[] parameters21 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        string CancelCnt = Convert.ToString(ObjMain.NewExecuteScalar(subquery21, parameters21));

                        // Assing the Cancel Count value 
                        // 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 dr21 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count21 = tmpDTpaymentheader.Columns.Count - 1;
                        dr21[row_count21] = CancelCnt.ToString();


                        //1.Cancel Amount 

                        string CancelAmt = "select coalesce((select  ifnull(sum(Total),0)   from {0}.trans where TransStatus=5 and BusinessDate between  @D1  and @D2 ),0) as cancel;";
                        string subquery22 = string.Format(CancelAmt, BranchAliasNam);
                        MySqlParameter[] parameters22 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        decimal CancelAmtVal = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery22, parameters22));


                        // Assing the Cancel Amount value 
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Cancel Amount";
                        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;
                        // math fun 
                        CancelAmtVal = Math.Round(CancelAmtVal, 2);
                        dr22[row_count22] = CancelAmtVal.ToString();

                        #endregion

                        #region Void Count & Void Amount 
                        // #20  Void Count & Void Amount 
                        // 1.Void Count

                        string Voidcntcquery1 = "select coalesce((select count(Amount) from {0}.transdetail where  itemstatus='1' and date(VoidDate) between  @D1  and   @D2 ),0) as voidcount;";
                        string subquery23 = string.Format(Voidcntcquery1, BranchAliasNam);

                        MySqlParameter[] parameters23 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        string VoidCnt = Convert.ToString(ObjMain.NewExecuteScalar(subquery23, parameters23));

                        // Assign the void count 

                        // 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 dr23 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count23 = tmpDTpaymentheader.Columns.Count - 1;
                        dr23[row_count23] = VoidCnt.ToString();


                        //#2. Void Amount 
                        string VoidlAmtquery = "select coalesce((select sum(Amount) from {0}.transdetail where  itemstatus='1' and date(VoidDate) between  @D1  and   @D2),0) as void";
                        string subquery24 = string.Format(VoidlAmtquery, BranchAliasNam);

                        MySqlParameter[] parameters24 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        decimal VoidAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery24, parameters24));

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



                        #endregion

                        #region   Exchange Amount 
                        //21. Exchange Amount 
                        string ExchAmtcquery1 = "select coalesce((select sum(Amount) as exchange from {0}.transdetail  where ItemStatus=4 and date(VoidDate) between  @D1 and  @D2),0) as exchange";
                        string subquery25 = string.Format(ExchAmtcquery1, BranchAliasNam);

                        MySqlParameter[] parameters25 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        decimal ExAmt = Convert.ToDecimal(ObjMain.NewExecuteScalar(subquery25, parameters25));

                        // Assign the Exchange Amount 

                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Exchange Amount";
                        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;
                        // math fun 
                        ExAmt = Math.Round(ExAmt, 2);
                        dr25[row_count25] = ExAmt.ToString();

                        #endregion


                        #region No.of Sales open 
                        // #22.  No.of Sales open 

                        string NoSalesPersoquery1 = "select coalesce((select count(Notes) from {0}.rapo as NoSalesOpen where TransType=2  and BusinessDate between  @D1  and  @D2 ),0) as nosalesopen";
                        string subquery26 = string.Format(NoSalesPersoquery1, BranchAliasNam);


                        MySqlParameter[] parameters26 =
                       {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };

                        string NoSalesopen = Convert.ToString(ObjMain.NewExecuteScalar(subquery26, parameters26));


                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "No Sales Open";
                        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] = NoSalesopen.ToString();

                        #endregion

                        #region  NO OF Receipts 
                        // 24.  NO OF Receipts 

                        string NoofReceiptsqry = "select * from (select count(TransID) as noofreceipts from {0}.trans where BusinessDate between @D1  and  @D2 )f,(select sum(PaymentAmount) as salesamount from {0}.transpayment where BusinessDate between @D1  and  @D2 )s";
                        string subquery28 = string.Format(NoofReceiptsqry, BranchAliasNam);

                        MySqlParameter[] parameters28 =
                        {
                         new MySqlParameter("@D1",D1),
                         new MySqlParameter("@D2",D2),
                        };
                        string NoofReceipts = Convert.ToString(ObjMain.NewExecuteScalar(subquery28, parameters28));
                        // Assign the Receipts values to excel
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "No. Of Receipts";
                        dtColumn.ReadOnly = false;
                        // Add the row value 
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        DataRow dr28 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count28 = tmpDTpaymentheader.Columns.Count - 1;
                        dr28[row_count28] = NoofReceipts.ToString();

                        #endregion

                        #region Avg Receipts 
                        // 25. Avg Receipts 
                        decimal AvgReceipts = 0;
                        if (Tot_Sales != 0)
                        {
                            AvgReceipts = Tot_Sales / Convert.ToDecimal(NoofReceipts);

                        }
                        else
                        {

                            AvgReceipts = 0;
                        }
                        // Assign the Avg Receipts values to excel
                        // Add the new column
                        dtColumn = new DataColumn();
                        dtColumn.DataType = typeof(string);
                        dtColumn.ColumnName = "Receipts Avg";
                        dtColumn.ReadOnly = false;
                        // Add the row value 
                        tmpDTpaymentheader.Columns.Add(dtColumn);
                        DataRow dr29 = tmpDTpaymentheader.Rows[0];
                        // Get the row count 
                        int row_count29 = tmpDTpaymentheader.Columns.Count - 1;
                        // math func 
                        AvgReceipts = Math.Round(AvgReceipts, 2);
                        dr29[row_count29] = AvgReceipts.ToString();







                        #endregion


                        // Test convert example 
                        jsonstring = DataTableToJSONWithJSONNet(tmpDTpaymentheader);
                        if (Session["jsonstring"] == null)
                        {

                            final = jsonstring;
                        }
                        else
                        {
                            string a = Session["jsonstring"].ToString();
                            a = a.Substring(1);

                            string b = a;
                            b = b.Remove(b.Length - 1);

                            string c = jsonstring;
                            c = c.Substring(1);
                            string d = c;
                            d = d.Remove(d.Length - 1);


                            final = "[" + b + "," + d + "]";
                        }
                        Session["jsonstring"] = final;
                        // Convert json to DataTable Back 



                    }
                    return final;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {

                }

            }

        }


        public string DataTableToJSONWithJSONNet(DataTable table)
        {
            string JSONString = string.Empty;
            JSONString = JsonConvert.SerializeObject(table);
            return JSONString;
        }

        #endregion



    }




}