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;

namespace SUNWEB
{
    public partial class RetailDashboard : 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;
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        MYSQLDB ObjMain = new MYSQLDB();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable Dtt = new DataTable();
        DataTable DtLinechart = new DataTable();
        // User Variables 
        string UserId = string.Empty;
        string UserName = string.Empty;
        string PortalTypeId = string.Empty;
        string cd_Id = string.Empty;
        string Datapath = string.Empty;
        string cd_BranchAlias = string.Empty;
        string IsMultiBranch = string.Empty;
        string IsSuperAdmin = string.Empty;

        // 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;
        decimal Tot_MonthlySale;
        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 NetAmt;
        decimal VoucherAmt;
        string Holdcnt;
        decimal HoldAmt;
        string CancelCnt;
        decimal CancelAmtVal;
        string VoidCnt;
        decimal VoidAmt;
        decimal ExAmt;
        string NoSalesopen;
        string NoofReceipts;
        decimal AvgReceipts;
        // 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();







        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Chart2.Visible = false;
                Chart3.Visible = false;


                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;
               

            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }


            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {
                LoadBranchName4SuperAdmin();


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

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

            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == false))
            {
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;
                lblBranchAliasName.Text = "Welcome" + "&nbsp;&nbsp;" + Session["Branch_HQ"].ToString() + "&nbsp; !";
                cd_BranchAlias = Session["Branch_HQ"].ToString();
                Datapath = Session["DataPath"].ToString();
                PortalTypeId = Session["PortalTypeId"].ToString();
                PID = "1"; // This is For Dashboard  Page

                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 
                    LoadTestRpt(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadTotSale(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                   
                    LoadSalesAmt1(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadPayouts(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadReceving(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadCashindrawer(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadRounding(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadRefund(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                   // LoadMonthlySale(cd_BranchAlias, Convert.ToString(MonthstartDt), Curr_DateOnly);
                    LoadItemDisc(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadTotDisc(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadTotPriceShiftDisc(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadDiscountTOT();
                    LoadGrossAmount();
                    LoadGSTAmount(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadSVCAmount(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    Netmount();
                    LoadVoucher(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadHoldCnt(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadCancel(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadVoid(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadExchange(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadNoSalesPersonOpen(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadNoofReceipts(cd_BranchAlias, Curr_DateOnly, Curr_DateOnly);
                    LoadAvgReceipts();

                    // Disable Selection Charts : Chart2 and Chart3 and Char 4 
                    
                    LastWCurrWSale();
                    LoadLineChartData(cd_BranchAlias,Convert.ToString(MonthstartDt), Curr_DateOnly);
                }

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

                }

            }
            else
            {

            }
            {

            }

        }

        public  void     LastWCurrWSale()
        {

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();

            // Lastweek Value : On Sale 

            string LMonQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekmon,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaL1 = new MySqlDataAdapter(LMonQry, Cn);
            AdaL1.Fill(DtLMon);
            if (DtLMon.Rows.Count > 0)
            {
                LMon = Convert.ToDecimal(DtLMon.Rows[0]["lastweekmon"].ToString());
                LDayName1 = DtLMon.Rows[0]["Day"].ToString();

            }

            string LTueQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweektue,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaL2 = new MySqlDataAdapter(LTueQry, Cn);
            AdaL2.Fill(DtLTue);
            if (DtLTue.Rows.Count > 0)
            {
                LTue = Convert.ToDecimal(DtLTue.Rows[0]["lastweektue"].ToString());
                LDayName2 = DtLTue.Rows[0]["Day"].ToString();

            }


            string LWedQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekWed,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";

            MySqlDataAdapter AdaL3 = new MySqlDataAdapter(LWedQry, Cn);
            AdaL3.Fill(DtLWed);
            if (DtLTue.Rows.Count > 0)
            {
                LWed = Convert.ToDecimal(DtLWed.Rows[0]["lastweekWed"].ToString());
                LDayName3 = DtLWed.Rows[0]["Day"].ToString();

            }

            string LThuQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekThurs,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaL4 = new MySqlDataAdapter(LThuQry, Cn);
            AdaL4.Fill(DtLThu);
            if (DtLThu.Rows.Count > 0)
            {
                LThu = Convert.ToDecimal(DtLThu.Rows[0]["lastweekThurs"].ToString());
                LDayName4 = DtLThu.Rows[0]["Day"].ToString();

            }
            string LFriQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekFri,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaL5 = new MySqlDataAdapter(LFriQry, Cn);
            AdaL5.Fill(DtLFri);
            if (DtLFri.Rows.Count > 0)
            {
                LFri = Convert.ToDecimal(DtLFri.Rows[0]["lastweekFri"].ToString());
                LDayName5 = DtLFri.Rows[0]["Day"].ToString();

            }
            string LsatQry = "select ifnull(sum(tp.PaymentAmount ),0) as lastweekSat,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaL6 = new MySqlDataAdapter(LsatQry, Cn);
            AdaL6.Fill(DtLSat);
            if (DtLSat.Rows.Count > 0)
            {
                LSat = Convert.ToDecimal(DtLSat.Rows[0]["lastweekSat"].ToString());
                LDayName6 = DtLSat.Rows[0]["Day"].ToString();

            }
            string LsunQry = "select  ifnull(sum(tp.PaymentAmount ),0) as lastweekSun,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaL7 = new MySqlDataAdapter(LsunQry, Cn);
            AdaL7.Fill(DtLSun);
            if (DtLSun.Rows.Count > 0)
            {
                LSun = Convert.ToDecimal(DtLSun.Rows[0]["lastweekSun"].ToString());
                LDayName7 = DtLSun.Rows[0]["Day"].ToString();

            }




            // Currentweek Value : On Sale 

            string CMonQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekmon,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaC1 = new MySqlDataAdapter(CMonQry, Cn);
            AdaC1.Fill(DtCMon);
            if (DtCMon.Rows.Count > 0)
            {
                CMon = Convert.ToDecimal(DtCMon.Rows[0]["cweekmon"].ToString());
                CDayName1 = DtCMon.Rows[0]["Day"].ToString();

            }
            string CTueQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekTues,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaC2 = new MySqlDataAdapter(CTueQry, Cn);
            AdaC2.Fill(DtCTue);
            if (DtCTue.Rows.Count > 0)
            {
                CTue = Convert.ToDecimal(DtCTue.Rows[0]["cweekTues"].ToString());
                CDayName2 = DtCTue.Rows[0]["Day"].ToString();

            }
            string CWedQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekWed,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaC3 = new MySqlDataAdapter(CWedQry, Cn);
            AdaC3.Fill(DtCWed);
            if (DtCWed.Rows.Count > 0)
            {
                CWed = Convert.ToDecimal(DtCWed.Rows[0]["cweekWed"].ToString());
                CDayName3 = DtCWed.Rows[0]["Day"].ToString();

            }
            string CThuQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekThurs,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaC4 = new MySqlDataAdapter(CThuQry, Cn);
            AdaC4.Fill(DtCThu);
            if (DtCThu.Rows.Count > 0)
            {
                CThu = Convert.ToDecimal(DtCThu.Rows[0]["cweekThurs"].ToString());
                CDayName4 = DtCThu.Rows[0]["Day"].ToString();

            }
            string CFriQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekFri,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaC5 = new MySqlDataAdapter(CFriQry, Cn);
            AdaC5.Fill(DtCFri);
            if (DtCFri.Rows.Count > 0)
            {
                CFri = Convert.ToDecimal(DtCFri.Rows[0]["cweekFri"].ToString());
                CDayName5 = DtCFri.Rows[0]["Day"].ToString();

            }
            string CSatQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekSat,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaC6 = new MySqlDataAdapter(CSatQry, Cn);
            AdaC6.Fill(DtCSat);
            if (DtCSat.Rows.Count > 0)
            {
                CSat = Convert.ToDecimal(DtCSat.Rows[0]["cweekSat"].ToString());
                CDayName6 = DtCSat.Rows[0]["Day"].ToString();

            }
            string CSunQry = "select ifnull(sum(tp.PaymentAmount ),0) as cweekSun,dayname(tp.BusinessDate) as Day from rasa1.transpayment tp  left join rasa1.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)";
            MySqlDataAdapter AdaC7 = new MySqlDataAdapter(CSunQry, Cn);
            AdaC7.Fill(DtCSun);
            if (DtCSun.Rows.Count > 0)
            {
                CSun = Convert.ToDecimal(DtCSun.Rows[0]["cweekSun"].ToString());
                CDayName7 = DtCSun.Rows[0]["Day"].ToString();

            }

            DataTable DtFinaldata = new DataTable();
            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);
                     

            // Loading Chart Data ..
            Chart1.DataSource = DtFinaldata;
            Chart1.DataBind();

            

            Chart1.Series["Series1"].LabelToolTip = "$" + "#VALY";
            Chart1.Series["Series1"].ToolTip = "$" + "#VALY";

            Chart1.Series["Series2"].LabelToolTip = "$" +"#VALY";
            Chart1.Series["Series2"].ToolTip =   "$"+"#VALY";
             }
        
        public void LoadTestRpt(string BranchAliasNam, string D1, string D2)
        {

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string query = "Select PaymentType,count(Paymenttype) as count,ifnull(sum(paymentamount),0) as amount From " + BranchAliasNam + ".trans t    join " + BranchAliasNam + ".transpayment tp on t.transid = tp.transid where tp.businessdate between '" + D1 + "' and '" + D2 + "' group by PaymentType";
            MySqlDataAdapter Cmd = new MySqlDataAdapter(query, Cn);

            Cmd.Fill(Dtt);

            // 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 + "&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>";
            }
            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 " + "</td><td>" + Amt.ToString("c") + "</td></tr>";
            }
            htmlTable += "</table>";
            lblTable.Text = htmlTable;
            Cn.Close();
        }

        public void LoadTotSale(string CdbranchAlias, string D1, string D2)
        {
            //Tot_Sales = ObjSel.Retail_TotalSales(cd_BranchAlias, "2019-02-012", "2019-02-012");

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string query = "Select ifnull(sum(TP.PaymentAmount),0) as salesamount From " + CdbranchAlias + ".trans t join  " + CdbranchAlias + ".transpayment tp on t.transid = tp.transid where tp.businessdate between '" + D1 + "' and '" + D2 + "';";
            MySqlCommand Cmd = new MySqlCommand(query, Cn);
            Tot_Sales = Convert.ToDecimal(Cmd.ExecuteScalar());
            lblRTotalSale.Text = Tot_Sales.ToString("c");
          
           

        }








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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string query = "Select  ifnull(sum(TP.PaymentAmount),0) as salesamount From " + BranchAliasNam + ".trans t  join " + BranchAliasNam + ".transpayment tp on t.transid = tp.transid where tp.businessdate between '" + D1 + "' and '" + D2 + "';";
            MySqlCommand Cmd = new MySqlCommand(query, Cn);
            Tot_Sales = Convert.ToDecimal(Cmd.ExecuteScalar());
            lblRTotalSale.Text = Tot_Sales.ToString("c");
  
            // DT = ObjSel.Retail_PaymentDetails(cd_BranchAlias, "2019-02-012", "2019-02-012");

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

            htmlTable += "<tr><td>" + "Sales 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;" + "   </td><td>" + "N/A" + "&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; " + "</td><td>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + Tot_Sales.ToString("c") + "</td></tr>";

            htmlTable += "</table>";
            lblTable1.Text = htmlTable;
            Cn.Close();
        }



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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string query = "Select PaymentType,count(Paymenttype) as count,ifnull(sum(paymentamount),0) as amount From " + BranchAliasNam + ".trans t    join " + BranchAliasNam + ".transpayment tp on t.transid = tp.transid where tp.businessdate between '" + D1 + "' and '" + D2 + "' group by PaymentType";
            MySqlDataAdapter Cmd = new MySqlDataAdapter(query, Cn);

            Cmd.Fill(Dtt);

            // 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;
            Cn.Close();
        }

        public void LoadPayouts(string BranchAliasNam, string D1, string D2)
        {
            // Payout Count : Fun
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string query = "select ifnull(count(Amount),0)as payoutcount from " + BranchAliasNam + ".rapo where TransType=1  and BusinessDate between '" + D1 + "' and '" + D1 + "'";
            MySqlCommand Cmd = new MySqlCommand(query, Cn);
            PayoutCnt = Convert.ToString(Cmd.ExecuteScalar());


            string query1 = "select ifnull(sum(Amount),0) as payout  from " + BranchAliasNam + ".rapo where TransType=1  and BusinessDate between '" + D1 + "' and '" + D1 + "'";
            MySqlCommand Cmd1 = new MySqlCommand(query1, Cn);
            PayoutAmt = Convert.ToDecimal(Cmd1.ExecuteScalar());



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

            htmlTable += "<tr><td>" + "Payouts" + "&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>" + PayoutCnt + "&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>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + PayoutAmt.ToString("c") + "</td></tr>";

            htmlTable += "</table>";
            lblPayouts.Text = htmlTable;
            Cn.Close();
        }

        public void LoadReceving(string BranchAliasNam, string D1, string D2)
        {
            // Payout Count : Fun
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string Rquery = "select ifnull(count(Amount) ,0) as receivecount  from " + BranchAliasNam + ".rapo where TransType=0  and BusinessDate    between '" + D1 + "' and '" + D2 + "';";
            MySqlCommand Cmd = new MySqlCommand(Rquery, Cn);
            ReceivingCnt = Convert.ToString(Cmd.ExecuteScalar());


            string Rquery1 = "select ifnull(sum(Amount),0) as receive from " + BranchAliasNam + ".rapo where TransType=0  and BusinessDate      between '" + D1 + "' and '" + D2 + "';";
            MySqlCommand Cmd1 = new MySqlCommand(Rquery1, Cn);
            ReceivingAmt = Convert.ToDecimal(Cmd1.ExecuteScalar());



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

            htmlTable += "<tr><td>" + "Receiving" + "&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>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + ReceivingCnt + "</td><td>" + "&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;" + ReceivingAmt.ToString("c") + "</td></tr>";

            htmlTable += "</table>";
            lblReceiving.Text = htmlTable;
            Cn.Close();
        }

        public void LoadCashindrawer(string BranchAliasNam, string D1, string D2)
        {
            // Payout Count : Fun
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string Cquery = "select * from(select   ifnull(sum(PaymentAmount),0) as cash from " + BranchAliasNam + ".transpayment where PaymentType='Cash' and BusinessDate between '" + D1 + "'  and '" + D2 + "')a,(select ifnull(sum(Amount),0) as takeRA from " + BranchAliasNam + ".rapo where TransType in (4,0) and BusinessDate between '" + D1 + "' and '" + D2 + "'  )d,(select ifnull(sum(Amount),0) as po from " + BranchAliasNam + ".rapo where TransType='1' and BusinessDate between '" + D1 + "' and '" + D2 + "')q ;";
            DataTable DtCashin = new DataTable();
            MySqlDataAdapter Ada = new MySqlDataAdapter(Cquery, Cn);
            Ada.Fill(DtCashin);
            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" + "&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;&nbsp;  " + "</td> <td>" + CshinDraw.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblCashinDrawer.Text = htmlTable;
            Cn.Close();
        }

        public void LoadRounding(string BranchAliasNam, string D1, string D2)
        {
            // Payout Count : Fun
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string RndRquery = "Select ifnull(Sum(A.Round),0) As rounding From " + BranchAliasNam + ".Trans A Inner Join " + BranchAliasNam + ".TransPayment B On A.TransID=B.TransID Where A.TransStatus = 2 And  B.BusinessDate between '" + D1 + "' and '" + D2 + "';";
            MySqlCommand Cmd = new MySqlCommand(RndRquery, Cn);
            Rounding = Convert.ToDecimal(Cmd.ExecuteScalar());
            // Rounding Bind 
            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Rounding" + "&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;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " + "</td> <td>" + Rounding.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblRounding.Text = htmlTable;


            Cn.Close();
        }


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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string Refndquery1 = " 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 + "';";
            MySqlCommand Cmd1 = new MySqlCommand(Refndquery1, Cn);
            Refund = Convert.ToDecimal(Cmd1.ExecuteScalar());


            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Refund " + "&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;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " + "</td> <td>" + Rounding.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblRefund.Text = htmlTable;
            Cn.Close();
        }

        public void LoadGrossAmount()
        {

            GrossSales = Tot_Sales + Rounding + DistTOT;

            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Gross 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;&nbsp;&nbsp;&nbsp;  " + "</td> <td>" + GrossSales.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblGrossAmount.Text = htmlTable;

        }

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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string ItemDisquery1 = " select ifnull(sum(ManualDisc),0) as itemdisc from " + BranchAliasNam + ".transdetail td left join " + BranchAliasNam + ".trans t on t.TransID=td.TransID where t.TransStatus='2' and td.ItemStatus='0'and t.BusinessDate between '" + D1 + "' and '" + D2 + "';";
            MySqlCommand Cmd1 = new MySqlCommand(ItemDisquery1, Cn);
            ItemDisc = Convert.ToDecimal(Cmd1.ExecuteScalar());


            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Item Discount" + "&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;&nbsp;&nbsp;&nbsp;&nbsp; " + "</td> <td>" + ItemDisc.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblItemDiscount.Text = htmlTable;
            Cn.Close();
        }

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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string TotDiscquery1 = " SELECT coalesce((SELECT sum(TotalDisc)  from " + BranchAliasNam + ".transdetail B join " + BranchAliasNam + ".Trans A on A.TransID = B.TransID where businessdate  between '" + D1 + "' and '" + D2 + "'),0)as totaldisc;";
            MySqlCommand Cmd1 = new MySqlCommand(TotDiscquery1, Cn);
            TotDisc = Convert.ToDecimal(Cmd1.ExecuteScalar());


            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Total Discount" + "&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;&nbsp;&nbsp;&nbsp;&nbsp; " + "</td> <td>" + TotDisc.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblTotDiscount.Text = htmlTable;
            Cn.Close();
        }


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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            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 + "';";
            MySqlCommand Cmd1 = new MySqlCommand(PriceShiftDiscquery1, Cn);
            PriceShifDisc = Convert.ToDecimal(Cmd1.ExecuteScalar());


            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Price Shift  Discount" + "&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>" + PriceShifDisc.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblPriceShiftDiscount.Text = htmlTable;
            Cn.Close();
        }
        public void LoadDiscountTOT()
        {

            DistTOT = ItemDisc + TotDisc + PriceShifDisc;

            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Discount Total" + "&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;&nbsp;&nbsp;&nbsp;&nbsp;  " + "</td> <td>" + DistTOT.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblDiscountTotal.Text = htmlTable;

        }

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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string GstAmountquery1 = " Select * From(Select ifnull(sum(round(Taxamount2,2)),0) as gst From rasa1.Trans where businessdate between '" + D1 + "' and '" + D2 + "' and transstatus in (2,3,4))A,(Select if(taxontax=1,'.07','') as taxgst from rasa1.tax where taxname like '%GST')b;";
            MySqlDataAdapter Ada1 = new MySqlDataAdapter(GstAmountquery1, Cn);
            DataTable DtGst = new DataTable();
            Ada1.Fill(DtGst);
            if (DtGst.Rows.Count > 0)
            {
                Gstamt = Convert.ToDecimal(DtGst.Rows[0]["gst"].ToString());

            }
            else
            {
                Gstamt = 0;
            }

            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "(7%)GST INCL	" + "&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;&nbsp;&nbsp;&nbsp; " + "</td> <td>" + Gstamt.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblGST.Text = htmlTable;
            Cn.Close();
        }

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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            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;";
            MySqlDataAdapter Ada2 = new MySqlDataAdapter(SvcAmountquery1, Cn);
            DataTable DtSVC = new DataTable();
            Ada2.Fill(DtSVC);
            if (DtSVC.Rows.Count > 0)
            {
                SvcAmt = Convert.ToDecimal(DtSVC.Rows[0]["svc"].ToString());

            }
            else
            {
                SvcAmt = 0;
            }


            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "SVC	" + "&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;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " + "</td> <td>" + SvcAmt.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblSVC.Text = htmlTable;
            Cn.Close();
        }

        public void Netmount()
        {

            if (Tot_Sales != 0)
            {
                NetAmt = Tot_Sales - (Gstamt + SvcAmt);
            }
            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)
        {

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string Vouchcquery1 = " Select ifnull(Sum(B.TaxableAmount+B.TaxAmount1+B.TaxAmount2+B.TaxAmount3+B.TaxAmount4+B.TaxAmount5+B.TaxAmount6),0) AS Voucher From   " + BranchAliasNam + ".Trans A Left Join " + BranchAliasNam + ".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 + "' ;";
            MySqlCommand Cmd1 = new MySqlCommand(Vouchcquery1, Cn);
            VoucherAmt = Convert.ToDecimal(Cmd1.ExecuteScalar());


            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Voucher 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>" + VoucherAmt.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblVoucher.Text = htmlTable;
            Cn.Close();
        }
        public void LoadHoldCnt(string BranchAliasNam, string D1, string D2)
        {

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

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

            string htmlTable = "<table ><tr>";
            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;&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;" + "</thead>";
            }

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string Holdcntcquery1 = "select coalesce((select count(TransStatus)  from " + BranchAliasNam + ".trans where TransStatus in (3,4) and BusinessDate between '" + D1 + "' and '" + D2 + "'),0) as holdcount";
            MySqlCommand Cmd1 = new MySqlCommand(Holdcntcquery1, Cn);
            Holdcnt = Convert.ToString(Cmd1.ExecuteScalar());

            string HoldAmtcquery1 = "select coalesce((select ifnull(sum(Total),0) as hold  from " + BranchAliasNam + ".trans where TransStatus=3 and BusinessDate between '" + D1 + "' and '" + D2 + "'),0) as hold;";
            MySqlCommand Cmd2 = new MySqlCommand(HoldAmtcquery1, Cn);
            HoldAmt = Convert.ToDecimal(Cmd2.ExecuteScalar());

            htmlTable += "<tr><td>" + "Hold " + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "</td>     <td>" + "&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;" + Holdcnt + "</td> <td>" + "&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;" + HoldAmt.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblHold.Text = htmlTable;
            Cn.Close();
        }

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


            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string Cancecntcquery1 = "select coalesce((select ifnull(count(TransStatus),0)  from " + BranchAliasNam + ".trans where TransStatus=5 and BusinessDate between '" + D1 + "' and '" + D2 + "'),0)  as cancelcount";
            MySqlCommand Cmd1 = new MySqlCommand(Cancecntcquery1, Cn);
            CancelCnt = Convert.ToString(Cmd1.ExecuteScalar());

            string CancelAmt = "select coalesce((select  ifnull(sum(Total),0)   from " + BranchAliasNam + ".trans where TransStatus=5 and BusinessDate between '" + D1 + "' and '" + D2 + "'),0) as cancel;";
            MySqlCommand Cmd2 = new MySqlCommand(CancelAmt, Cn);
            CancelAmtVal = Convert.ToDecimal(Cmd2.ExecuteScalar());
            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Cancel " + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "</td>     <td>" + "&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;" + CancelCnt + "</td> <td>" + "&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;" + CancelAmtVal.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblCancel.Text = htmlTable;
            Cn.Close();
        }

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


            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string Voidcntcquery1 = "select coalesce((select count(Amount) from " + BranchAliasNam + ".transdetail where  itemstatus='1' and date(VoidDate) between '" + D1 + "' and  '" + D1 + "'),0) as voidcount;";
            MySqlCommand Cmd1 = new MySqlCommand(Voidcntcquery1, Cn);
            VoidCnt = Convert.ToString(Cmd1.ExecuteScalar());

            string VoidlAmtquery = "select coalesce((select sum(Amount) from " + BranchAliasNam + ".transdetail where  itemstatus='1' and date(VoidDate) between '" + D1 + "' and  '" + D2 + "'),0) as void";
            MySqlCommand Cmd2 = new MySqlCommand(VoidlAmtquery, Cn);
            VoidAmt = Convert.ToDecimal(Cmd2.ExecuteScalar());
            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Void " + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "</td>     <td>" + "&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;" + VoidCnt + "</td> <td>" + "&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;" + VoidAmt.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblVoid.Text = htmlTable;
            Cn.Close();
        }

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


            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string ExchAmtcquery1 = "select coalesce((select sum(Amount) as exchange from " + BranchAliasNam + ".transdetail  where ItemStatus=4 and date(VoidDate) between '" + D1 + "'and '" + D2 + "'),0) as exchange";
            MySqlCommand Cmd1 = new MySqlCommand(ExchAmtcquery1, Cn);
            ExAmt = Convert.ToDecimal(Cmd1.ExecuteScalar());
            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "Exchange " + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "</td>     <td>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "N/A" + "</td> <td>" + "&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;" + ExAmt.ToString("c") + "</td></tr>";
            htmlTable += "</table>";
            lblExchange.Text = htmlTable;
            Cn.Close();
        }

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


            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string NoSalesPersoquery1 = "select coalesce((select count(Notes) from " + BranchAliasNam + ".rapo as NoSalesOpen where TransType=2  and BusinessDate between '" + D1 + "' and  '" + D2 + "'),0) as nosalesopen";
            MySqlCommand Cmd1 = new MySqlCommand(NoSalesPersoquery1, Cn);
            NoSalesopen = Convert.ToString(Cmd1.ExecuteScalar());
            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "No Sales Open " + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "</td>     <td>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + NoSalesopen + "</td> <td>" + "&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;" + "N/A" + "</td></tr>";
            htmlTable += "</table>";
            lblNosalespersonOpen.Text = htmlTable;
            Cn.Close();
        }

        public void LoadNoofReceipts(string BranchAliasNam, string D1, string D2)
        {
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string NoofReceiptsqry = "select * from (select count(TransID) as noofreceipts from " + BranchAliasNam + ".trans where BusinessDate between '" + D1 + "' and '" + D2 + "')f,(select sum(PaymentAmount) as salesamount from " + BranchAliasNam + ".transpayment where BusinessDate between '" + D1 + "' and '" + D2 + "')s";
            MySqlCommand Cmd1 = new MySqlCommand(NoofReceiptsqry, Cn);
            NoofReceipts = Convert.ToString(Cmd1.ExecuteScalar());

            string htmlTable = "<table ><tr>";
            htmlTable += "<tr><td>" + "No. Of Receipts	 " + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "</td>     <td>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + NoofReceipts + "</td> <td>" + "&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;" + "N/A" + "</td></tr>";
            htmlTable += "</table>";
            lblNoofReceipts.Text = htmlTable;
            Cn.Close();
        }

        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" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "</td><td>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + "N/A" + "</td> <td>" + "&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;" + 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.GetBranchOnSuperAdmin(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 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");

        }

        protected void btnGetReports_Click(object sender, EventArgs e)
        {


            Chart2.Visible = false;
            Chart3.Visible = false;
            LoadTestRpt(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadTotSale(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
          // LoadMonthlySale(cd_BranchAlias, Convert.ToString(MonthstartDt), Curr_DateOnly);
            LoadSalesAmt1(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadPayouts(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadReceving(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadCashindrawer(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadRounding(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadRefund(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);

            LoadItemDisc(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadTotDisc(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadTotPriceShiftDisc(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadDiscountTOT();
            LoadGrossAmount();
            LoadGSTAmount(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadSVCAmount(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            Netmount();
            LoadVoucher(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadHoldCnt(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadCancel(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadVoid(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadExchange(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadNoSalesPersonOpen(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadNoofReceipts(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            LoadAvgReceipts();
            LoadLineChartData(cd_BranchAlias, Convert.ToString(MonthstartDt), Curr_DateOnly);

        }

        protected void cmbsel_SelectedIndexChanged(object sender, EventArgs e)
        {

            if (cmbsel.SelectedItem.Text == "LastWeek Sale")
            {
                Chart1.Visible = false;
                Chart3.Visible = false;
                DataTable DtLastWeek = new DataTable();
                DtLastWeek.Columns.Add("Day", typeof(string));
                DtLastWeek.Columns.Add("Lastweeksale", typeof(decimal));


                DataRow Dr = DtLastWeek.NewRow();

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


                DtLastWeek.Rows.Add(Dr);

                DataRow Dr1 = DtLastWeek.NewRow();

                Dr1["Day"] = LDayName2;
                Dr1["Lastweeksale"] = LTue;
               
                DtLastWeek.Rows.Add(Dr1);

                DataRow Dr2 = DtLastWeek.NewRow();

                Dr2["Day"] = LDayName3;
                Dr2["Lastweeksale"] = LWed;
             
                DtLastWeek.Rows.Add(Dr2);

                DataRow Dr3 = DtLastWeek.NewRow();

                Dr3["Day"] = LDayName4;
                Dr3["Lastweeksale"] = LThu;
               
                DtLastWeek.Rows.Add(Dr3);

                DataRow Dr4 = DtLastWeek.NewRow();

                Dr4["Day"] = LDayName5;
                Dr4["Lastweeksale"] = LFri;
               
                DtLastWeek.Rows.Add(Dr4);

                DataRow Dr5 = DtLastWeek.NewRow();

                Dr5["Day"] = LDayName6;
                Dr5["Lastweeksale"] = LSat;
               
                DtLastWeek.Rows.Add(Dr5);

                DataRow Dr6 = DtLastWeek.NewRow();

                Dr6["Day"] = LDayName7;
                Dr6["Lastweeksale"] = LSun;
               
                DtLastWeek.Rows.Add(Dr6);


                // Loading Chart Data ..
                Chart2.DataSource = DtLastWeek;
                Chart2.DataBind();



                Chart2.Series["Series1"].LabelToolTip = "$" + "#VALY";
                Chart2.Series["Series1"].ToolTip = "$" + "#VALY";

                //Chart1.Series["Series2"].LabelToolTip = "$" + "#VALY";
                //Chart1.Series["Series2"].ToolTip = "$" + "#VALY";

                LoadLineChartData(cd_BranchAlias, Convert.ToString(MonthstartDt), Curr_DateOnly);
            
            }
            else if (cmbsel.SelectedItem.Text == "CurrWeek Sale")
            {

                Chart1.Visible = false;
                Chart2.Visible = false;
                DataTable DtCurrWeek = new DataTable();
                DtCurrWeek.Columns.Add("Day", typeof(string));
             
                DtCurrWeek.Columns.Add("CurrentWeekSal", typeof(string));

                DataRow Dr = DtCurrWeek.NewRow();

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

                DtCurrWeek.Rows.Add(Dr);

                DataRow Dr1 = DtCurrWeek.NewRow();

                Dr1["Day"] = LDayName2;
            
                Dr1["CurrentWeekSal"] = CTue;
                DtCurrWeek.Rows.Add(Dr1);

                DataRow Dr2 = DtCurrWeek.NewRow();

                Dr2["Day"] = LDayName3;
                
                Dr2["CurrentWeekSal"] = CWed;
                DtCurrWeek.Rows.Add(Dr2);

                DataRow Dr3 = DtCurrWeek.NewRow();

                Dr3["Day"] = LDayName4;
             
                Dr3["CurrentWeekSal"] = CThu;
                DtCurrWeek.Rows.Add(Dr3);

                DataRow Dr4 = DtCurrWeek.NewRow();

                Dr4["Day"] = LDayName5;
            
                Dr4["CurrentWeekSal"] = CFri;
                DtCurrWeek.Rows.Add(Dr4);

                DataRow Dr5 = DtCurrWeek.NewRow();

                Dr5["Day"] = LDayName6;
               
                Dr5["CurrentWeekSal"] = CSat;
                DtCurrWeek.Rows.Add(Dr5);

                DataRow Dr6 = DtCurrWeek.NewRow();

                Dr6["Day"] = LDayName7;
                
                Dr6["CurrentWeekSal"] = CSun;
                DtCurrWeek.Rows.Add(Dr6);


                // Loading Chart Data ..
                Chart3.DataSource = DtCurrWeek;
                Chart3.DataBind();
                Chart3.Series["Series1"].LabelToolTip = "$" + "#VALY";
                Chart3.Series["Series1"].ToolTip = "$" + "#VALY";
                LoadLineChartData(cd_BranchAlias, Convert.ToString(MonthstartDt), Curr_DateOnly);

            }


            

        }

        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(DtLinechart);
            
            // Load the Date Value : 
             
            Chart4.DataSource = DtLinechart;
            Chart4.DataBind();
            Chart4.Series["Series1"].LabelToolTip =  " #VALX   " +"Total Sale  -" + "$" +" #VALY";
            Chart4.Series["Series1"].ToolTip =  " #VALX  " + "Total Sale -"+ "$" +"#VALY";
        }




    }
}