using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Web;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using CLS_BL;
using CLS_DL;
using CLS_PL;

using System.Web.UI.DataVisualization.Charting;
namespace SUNWEB
{
    public partial class TaxAnalysis : System.Web.UI.Page
    {
        BL_SELECT ObjSel = new BL_SELECT();
        BLCompany ObjBL = new BLCompany();
        PLCOMMON ObjPL = new PLCOMMON();
        BL_ErrorLog ObjLog = new BL_ErrorLog();
        BL_USER ObjUserBL = new BL_USER(); //This Object for All User and Page Related fuctions included 
        PLCUSTOMER ObjCustPL = new PLCUSTOMER();
        Int32 K = 0;
        Int32 L = 0;
        Int32 S = 0;
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        DataSet ds = new DataSet();
        MYSQLDB ObjMain = new MYSQLDB();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable DtK = new DataTable();
        DataTable DtM = new DataTable();
        DataTable DtSuperAdmin = new DataTable();
        DataTable DtMultiBranch = new DataTable();
        DataTable DtNormalUser = new DataTable();
        DataTable DtExcel = new DataTable();
        DataTable DTExcelTemp = new DataTable();
        string ActiveCallvalue = string.Empty;
        decimal totaltaxableamount = 0;
        decimal Gtotaltaxableamount = 0;
        decimal totaltaxamount = 0;
        decimal Gtotaltaxamount = 0;
        // User Variables 
        string UserId = string.Empty;
        string UserName = string.Empty;
        string PortalTypeId = string.Empty;
        string cd_Id = string.Empty;
        string Datapath = string.Empty;
        string cd_BranchAlias = string.Empty;
        string IsMultiBranch = string.Empty;
        string IsSuperAdmin = string.Empty;
        string Cd_BranchName = string.Empty;
        string Rcd_id = string.Empty;
        string RPortalid = string.Empty;
        // License Variables ..
        string PID = string.Empty;
        string Page_Acess = string.Empty;
        string Read_Acess = string.Empty;
        string Write_Acess = string.Empty;
        string Isdowld_Acess = string.Empty;




        string L_TaxAnalysis = string.Empty;

        // Others
        string Branchidval = string.Empty;
        string Useridvalue = string.Empty;
        string QueryCurr_Select = string.Empty;

        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;
        string BranchDB = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {
            PID = "4";                             //  This is for Tax Analysis 
            if (!IsPostBack)
            {
                txtFromDt.Text = Curr_DateOnly;
                txtToDt.Text = Curr_DateOnly;
            }
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();
                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();
                    }
                }
            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    System.Threading.Thread.Sleep(2000);
                    LoadBranchName4SuperAdmin();
                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();
                    }
                }
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    System.Threading.Thread.Sleep(2000);
                    LoadBranchName();
                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();
                    }
                }
            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == false))
            {
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;
                lblBranchAliasName.Visible = true;
                Cm_id = ObjSel.GETcmid(Session["Cd_Id"].ToString());  // Get cm_id
                HQ = ObjSel.Get_HQ(Cm_id);     // Get the HQ Value
                Curr_BranchFullName = ObjSel.GetBranchFullName(HQ, Session["Branch_HQ"].ToString());
                lblBranchAliasName.ForeColor = System.Drawing.Color.DodgerBlue;
                lblBranchAliasName.Text = "Branch Name " + "&nbsp;&nbsp;" + Curr_BranchFullName + "&nbsp; ";
                cd_BranchAlias = Session["Branch_HQ"].ToString();

                Datapath = Session["DataPath"].ToString();
                PortalTypeId = Session["PortalTypeId"].ToString();
                PID = "4";               //  This is for Tax Analysis 
                DT = ObjUserBL.GetPageRightsDetailsPid(Session["UserId"].ToString(), PID);
                if (DT.Rows.Count > 0)
                {
                    Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                    Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                    Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                    Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();
                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }
                if ((Session["L_TaxAnalysis"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    // XSS Prevention  : 02-08-21 

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

                    LoadDate(cd_BranchAlias, StartDT, EndDt);
                    lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;  " + Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text) + "&nbsp;&nbsp; to &nbsp;&nbsp;" + Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text);
                    idTax.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");

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

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

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

            cd_BranchAlias = ActiveCallvalue;
            Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cd_BranchAlias);
            Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
            cmbBranches.Items.FindByText(Curr_BranchFullName).Selected = true;
            Branchidval = ObjUserBL.GetBranchIDbyName(ActiveCallvalue, ActiveCallvalue);
            if (Branchidval != "")
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            }
            else
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Session["Fistbranch"].ToString());

            }


            if (DT.Rows.Count > 0)
            {
                Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();
                Session["Page_Acess"] = Page_Acess;
                Session["Read_Acess"] = Read_Acess;
                Session["Write_Acess"] = Write_Acess;
                Session["Isdowld_Acess_7"] = 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_TaxAnalysis = DtLicense.Rows[0]["TAXANALYSIS"].ToString();
                Session["L_TaxAnalysis"] = L_TaxAnalysis;

            }
            if (Session["L_TaxAnalysis"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {
                // XSS Prevention  : 30-09-21 

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

                }


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

                LoadDate(ActiveCallvalue, StartDT, EndDt);
                System.Threading.Thread.Sleep(2000);






            }
        }
        public void UPDATEBRANCH(string ActiveCallvalue)
        {
            Useridvalue = Session["UserId"].ToString();
            QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
            S = ObjMain.ExecuteNonQuery(QueryCurr_Select);
        }
        public void LoadDate(string Branch_Nam, string D1, string D2)
        {


            System.Threading.Thread.Sleep(1000);


            //string Qry1 = "Select distinct(DATE_FORMAT(a.businessdate,'%Y-%m-%d'))as businessdate,'' as Paymenttype,round(Sum(PaymentAmount),2) as Total,round(sum(total-taxamount1-taxamount2),2) as taxableamount,'7%' taxrate, " +
            //              " round(sum(TaxAmount1+TaxAmount2+TaxAmount3+TaxAmount4+TaxAmount5),2) as TaxAmount  " +
            //              " from " + Branch_Nam + ".Transpayment a join " + Branch_Nam + ".trans b on a.transid=b.transid " +
            //              " where a.businessdate between '" + D1 + "' and '" + D2 + "'  group by businessdate;";

            // string Qry1 = "Select distinct(DATE_FORMAT(a.businessdate,'%Y-%m-%d'))as businessdate,'' as Paymenttype,round(Sum(PaymentAmount),2) as Total, " +
            //string Qry1 = "Select distinct(date(a.businessdate))as businessdate,'' as Paymenttype,round(Sum(PaymentAmount),2) as Total, " +
            //            " round(sum(total-taxamount1-taxamount2),2) as taxableamount, " +
            //            " (Select Tax from " + Branch_Nam + ".tax where taxid = 1) as Tax1,  " +
            //            " (Select Tax from " + Branch_Nam + ".tax where taxid = 2) as Tax2,  " +
            //            " (Select sum(Tax*100) from " + Branch_Nam + ".tax where taxid = 3) as Tax3, " +
            //            " round(sum(TaxAmount1+TaxAmount2+TaxAmount3+TaxAmount4+TaxAmount5),2) as TaxAmount   " +
            //            " from " + Branch_Nam + ".Transpayment a join " + Branch_Nam + ".trans b on a.transid=b.transid  where date(a.businessdate) between '" + D1 + "' and '" + D2 + "'  " +
            //            " and transstatus in (2,3,4) and b.itemstatus = 0 " +
            //            " group by businessdate;";

            string Qry1 = " Select distinct(date(b.businessdate))as businessdate,'' as Paymenttype,round(Sum(Total),2) as Total,  " +
                         " round(sum(b.total-b.taxamount1-b.taxamount2),2) as taxableamount,  " +
                //" (Select Tax from {0}.tax where taxid = 1) as Tax1,   " +
                //" (Select Tax from  {0}.tax where taxid = 2) as Tax2,   " +
                //" (Select Tax from  {0}.tax where taxid = 2) as Tax2,   " +
                " round(sum(+b.TaxAmount1),2) as Tax1, " +
                " round(sum(+b.TaxAmount2), 2) as Tax2, " +
                         "(Select sum(Tax*100) from  {0}.tax where taxid = 3) as Tax3,  " +
                         " round(sum(b.TaxAmount1+b.TaxAmount2+b.TaxAmount3+b.TaxAmount4+b.TaxAmount5),2) as TaxAmount    " +
                         " from  {0}.Trans b " +
                         //" from  {0}.Transpayment a " +
                         //" join  {0}.trans b on a.transid=b.transid  " +
                         " where date(b.businessdate) " +
                         " between @D1  and  @D2 " +
                         " and transstatus in (2,3,4) group by businessdate;";

            // Prevent the SQL Injections
            // Parameterized Approach 

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

            DtM = ObjMain.NewGetData_DT(command, parameters);

            grdTaxAnalysis.DataSource = DtM;
            grdTaxAnalysis.DataBind();
        }
        //Common Function
        public void LoadBranchName()
        {
            DtNormalUser = ObjUserBL.GetBranchOnLoginValues8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtNormalUser.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtNormalUser;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        public void LoadBranchName4SuperAdmin()
        {
            DtSuperAdmin = ObjUserBL.GetBranchOnSuperAdmin8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtSuperAdmin.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtSuperAdmin;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        protected void btnGetReports_Click(object sender, EventArgs e)
        {
            if (cmbBranches.SelectedItem.Text != "Select Branch")
            {

                // XSS Prevention  : 30-09-21 

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

                Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                LoadDate(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
                lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;  " + StartDT.Trim() + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt.Trim();
                idTax.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
            }
        }
        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {
        }
        protected void grdTaxAnalysis_SelectedIndexChanged(object sender, EventArgs e)
        {
        }
        protected void cmbBranches_SelectedIndexChanged1(object sender, EventArgs e)
        {
            // XSS Prevention  : 30-09-21 

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

            }

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


            Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
            LoadDate(Microsoft.Security.Application.Encoder.HtmlEncode(cd_BranchAlias), StartDT, EndDt);
            lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;  " + StartDT.Trim() + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt.Trim();
            idTax.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
            UPDATEBRANCH(cd_BranchAlias);




        }
        protected void grdTaxAnalysis_RowDataBound(object sender, GridViewRowEventArgs e)
        {

            try
            {

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

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

                }

                if (e.Row.RowType == DataControlRowType.DataRow)
                {

                    GridView gv = (GridView)e.Row.FindControl("grdDetails");
                    string businessdate1 = ((DataRowView)e.Row.DataItem)["businessdate"].ToString().Trim();
                    string businessdateT = businessdate1.Substring(0, businessdate1.IndexOf(' '));


                    DateTime businessdate;
                    businessdate = Convert.ToDateTime(businessdateT);
                    string BusinesDT = businessdate.ToString("yyyy-MM-dd");
                    /*string Qry2 = "Select a.TransID,paymenttype,round(sum(b.taxableamount+b.taxamount1+b.taxamount2+b.taxamount3+b.taxamount4+b.taxamount5),2) as Sales,  " +
                                    " round(sum(b.taxableamount),2) as Taxablemaount, " +
                                    " round(sum(b.taxamount1),2) as SVC ,round(sum(b.Taxamount2),2) as GST, " +
                                    "  round(sum(b.Taxamount3),2) as TAX3, " +
                                    " round(sum(b.taxamount4+b.taxamount5),2) as Other_tax, " +
                                    " round(sum(b.taxableamount+b.taxamount1+b.taxamount2+b.taxamount3+b.taxamount4+b.taxamount5),2)  as Total" +
                                    " From {0}.Trans a " +
                                    " join {0}.transdetail b on " +
                                    " a.transid = b.transid  " +
                                    " join {0}.transpayment c on " +
                                    " a.transid=c.transid " +
                                    " where  DATE_FORMAT(a.businessdate,'%Y-%m-%d')=@BusinesDT " +
                                    " and transstatus in (2,3,4) and b.itemstatus = 0 " +
                                    " group by transid;";
                    /*string Qry2 = "Select a.TransID,paymenttype,round(a.subtotal+a.taxamount1+a.taxamount2+a.taxamount3+a.taxamount4+a.taxamount5,2) as Sales,  " +
                       " round(a.subtotal,2) as Taxablemaount, " +
                       " round(a.taxamount1,2) as SVC ,round(a.Taxamount2,2) as GST, " +
                       "  round(a.Taxamount3,2) as TAX3, " +
                       " round(a.taxamount4+a.taxamount5,2) as Other_tax, " +
                       " round(a.subtotal+a.taxamount1+a.taxamount2+a.taxamount3+a.taxamount4+a.taxamount5,2)  as Total" +
                       " From {0}.Trans a " +
                       " join {0}.transpayment c on " +
                       " a.transid=c.transid " +
                       " where  DATE_FORMAT(a.businessdate,'%Y-%m-%d')=@BusinesDT " +
                       " and transstatus in (2,3,4) " +
                       " group by transid;";*/

                    string Qry2 = "SELECT " +
                                    " a.TransID, " +
                                    " MAX(c.PaymentType) AS PaymentType,  " +
                                    " ROUND(SUM(b.TaxableAmount + b.TaxAmount1 + b.TaxAmount2 + b.TaxAmount3 + b.TaxAmount4 + b.TaxAmount5), 2) AS Sales, " +
                                    " ROUND(SUM(b.TaxableAmount), 2) AS Taxablemaount, " +
                                    " ROUND(SUM(b.TaxAmount1), 2) AS SVC, " +
                                    " ROUND(SUM(b.TaxAmount2), 2) AS GST, " +
                                    " ROUND(SUM(b.TaxAmount3), 2) AS TAX3, " +
                                    " ROUND(SUM(b.TaxAmount4 + b.TaxAmount5), 2) AS Other_Tax, " +
                                    " ROUND(SUM(b.TaxableAmount + b.TaxAmount1 + b.TaxAmount2 + b.TaxAmount3 + b.TaxAmount4 + b.TaxAmount5), 2) AS Total " +
                                    " FROM {0}.Trans a " +
                                    " JOIN {0}.TransDetail b ON a.TransID = b.TransID " +
                                    " RIGHT JOIN( " +
                                    "     SELECT TransID, MAX(PaymentType) AS PaymentType " +
                                    "     FROM {0}.TransPayment " +
                                    "     GROUP BY TransID " +
                                    " ) c ON a.TransID = c.TransID " +
                                    " WHERE " +
                                    "     DATE_FORMAT(a.BusinessDate, '%Y-%m-%d') = @BusinesDT " +
                                    "     AND a.TransStatus IN(2, 3, 4) " +
                                    "     AND b.ItemStatus = 0 " +
                                    " GROUP BY a.TransID; ";
                    // Prevent the SQL Injections
                    // Parameterized Approach 

                    string command = string.Format(Qry2, cd_BranchAlias);
                    MySqlParameter[] parameters =
                         {
                          new MySqlParameter("@BusinesDT",BusinesDT)
                         };

                    DtK = ObjMain.NewGetData_DT(command, parameters);

                    gv.DataSource = DtK;
                    gv.DataBind();

                }

            }
            catch (Exception Ex)
            {
                ObjLog.Write(Ex);
            }



        }


        protected void grdTaxAnalysis_RowCommand(object sender, GridViewCommandEventArgs e)
        {



        }


        protected void OnRowDataBoundChild(object sender, GridViewRowEventArgs e)
        {
            //if (e.Row.RowType == DataControlRowType.DataRow)
            //{
            //    decimal Gtaxableamount = Convert.ToDecimal((e.Row.FindControl("lblSales") as Label).Text);
            //    totaltaxableamount += Gtaxableamount;
            //    Gtotaltaxableamount += Gtaxableamount;

            //    decimal Gtaxamount = Convert.ToDecimal((e.Row.FindControl("lblTaxablemaount") as Label).Text);
            //    totaltaxamount += Gtaxamount;
            //    Gtotaltaxamount += Gtaxamount;

            //}
            //if (e.Row.RowType == DataControlRowType.Footer)
            //{

            //}
        }
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            // XSS Prevention  : 30-09-21 

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



            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cmbBranches.SelectedItem.Text.Trim());

            string sCmd = "SELECT " +
                                 " DATE_FORMAT(a.Businessdate, '%d/%m/%Y') as Businessdate, a.TransID, " +
                                 " MAX(c.PaymentType) AS PaymentType, " +
                                 " ROUND(SUM(b.TaxableAmount + b.TaxAmount1 + b.TaxAmount2 + b.TaxAmount3 + b.TaxAmount4 + b.TaxAmount5), 2) AS Sales, " +
                                 " ROUND(SUM(b.TaxableAmount), 2) AS TaxableAmount, " +
                                 " ROUND(SUM(b.TaxAmount1), 2) AS SVC, " +
                                 " ROUND(SUM(b.TaxAmount2), 2) AS GST, " +
                                 " ROUND(SUM(b.TaxAmount3), 2) AS TAX3, " +
                                 " ROUND(SUM(b.TaxAmount4 + b.TaxAmount5), 2) AS Other_Tax, " +
                                 " ROUND(SUM(b.TaxableAmount + b.TaxAmount1 + b.TaxAmount2 + b.TaxAmount3 + b.TaxAmount4 + b.TaxAmount5), 2) AS Total " +
                                 " FROM {0}.Trans a " +
                                 " JOIN {0}.TransDetail b ON a.TransID = b.TransID " +
                                 " RIGHT JOIN( " +
                                 "     SELECT TransID, MAX(PaymentType) AS PaymentType " +
                                 "     FROM {0}.TransPayment " +
                                 "     GROUP BY TransID " +
                                 " ) c ON a.TransID = c.TransID " +
                                 " WHERE " +
                                 "     DATE_FORMAT(a.BusinessDate, '%Y-%m-%d') between @StartDT and @EndDt " +
                                 "     AND a.TransStatus IN(2, 3, 4) " +
                                 "     AND b.ItemStatus = 0 " +
                                 " GROUP BY a.TransID order by a.Businessdate; ";

            /*string sCmd = "Select  DATE_FORMAT(a.Businessdate, '%d/%m/%Y') as Businessdate  ,a.TransID,paymenttype,round(sum(b.taxableamount+b.taxamount1+b.taxamount2+b.taxamount3+b.taxamount4+b.taxamount5),2) as Sales,  " +
            " round(sum(b.taxableamount),2) as TaxableAmount, " +
            " round(sum(b.taxamount1),2) as SVC ,round(sum(b.Taxamount2),2) as GST, " +
            "  round(sum(b.Taxamount3),2) as TAX3, " +
            " round(sum(b.taxamount4+b.taxamount5),2) as Other_tax, " +
            " round(sum(b.taxableamount+b.taxamount1+b.taxamount2+b.taxamount3+b.taxamount4+b.taxamount5),2)  as Total" +
            " From {0}.Trans a " +
            " join {0}.transdetail b on " +
            " a.transid = b.transid  " +
            " join {0}.transpayment c on " +
            " a.transid=c.transid " +
            " where  date(a.businessdate) between @StartDT and @EndDt " +
            " and transstatus in (2,3,4) and b.itemstatus = 0 " +
            " group by transid order by a.Businessdate;;";*/
            /*string sCmd = "Select DATE_FORMAT(a.Businessdate, '%d/%m/%Y') as Businessdate, a.TransID,paymenttype,round(a.subtotal+a.taxamount1+a.taxamount2+a.taxamount3+a.taxamount4+a.taxamount5,2) as Sales,  " +
                    " round(a.subtotal,2) as TaxableAmount, " +
                    " round(a.taxamount1,2) as SVC ,round(a.Taxamount2,2) as GST, " +
                    "  round(a.Taxamount3,2) as TAX3, " +
                    " round(a.taxamount4+a.taxamount5,2) as Other_tax, " +
                    " round(a.subtotal+a.taxamount1+a.taxamount2+a.taxamount3+a.taxamount4+a.taxamount5,2)  as Total" +
                    " From {0}.Trans a " +
                    " join {0}.transpayment c on " +
                    " a.transid=c.transid " +
                   " where  date(a.businessdate) between @StartDT and @EndDt " +
                    " and transstatus in (2,3,4) " +
                    " group by transid order by transid,a.Businessdate;";*/


            // Prevent the SQL Injections
            // Parameterized Approach 


            string command = string.Format(sCmd, cd_BranchAlias);
            MySqlParameter[] parameters =
                 {
                          new MySqlParameter("@StartDT",StartDT.Trim()),
                          new MySqlParameter("@EndDt",EndDt.Trim())
                         };
            DtExcel = ObjMain.NewGetData_DT(command, parameters);





            // Jquery Codde For Calculation (Sales)
            string Qry1 = " Select distinct(date(b.businessdate))as businessdate,'' as Paymenttype,round(Sum(Total),2) as Total,  " +
                " round(sum(b.total-b.taxamount1-b.taxamount2),2) as taxableamount,  " +
                //" (Select Tax from {0}.tax where taxid = 1) as Tax1,   " +
                //" (Select Tax from  {0}.tax where taxid = 2) as Tax2,   " +
                " round(sum(+b.TaxAmount1),2) as Tax1, " +
                " round(sum(+b.TaxAmount2), 2) as Tax2, " +
                " (Select sum(Tax*100) from  {0}.tax where taxid = 3) as Tax3,  " +
                " round(sum(b.TaxAmount1+b.TaxAmount2+b.TaxAmount3+b.TaxAmount4+b.TaxAmount5),2) as TaxAmount    " +
                " from  {0}.Trans b " +
                //" from  {0}.Transpayment a " +
                //" join  {0}.trans b on a.transid=b.transid  " +
                " where date(b.businessdate) " +
                " between @D1  and  @D2 " +
                " and transstatus in (2,3,4) group by businessdate;";

            // Prevent the SQL Injections
            // Parameterized Approach 

            string commandheader = string.Format(Qry1, cd_BranchAlias);
            MySqlParameter[] parametersheader =
                  {
                     new MySqlParameter("@D1",StartDT.Trim()),
                     new MySqlParameter("@D2",EndDt.Trim()),
                    };

            DtM = ObjMain.NewGetData_DT(commandheader, parametersheader);





            decimal AmTT = 0;
            decimal Tax_AmTT = 0;
            decimal Tax_SVC = 0;
            decimal Tax_GST = 0;
            decimal Tax_TAX3 = 0;
            decimal Tax_Other_tax = 0;
            decimal Tax_Total = 0;

            //for (int i = 0; i <= DtExcel.Rows.Count - 1; i++)
            //{
            //    /*if (i == 0)
            //    {

            //        DtExcel.Rows.InsertAt(DtExcel.NewRow(), i);
            //        DtExcel.Rows[i]["Sales"] = AmTT;
            //        DtExcel.Rows[i]["TaxableAmount"] = Tax_AmTT;
            //        DtExcel.Rows[i]["SVC"] = Tax_SVC;
            //        DtExcel.Rows[i]["GST"] = Tax_GST;
            //        DtExcel.Rows[i]["TAX3"] = Tax_TAX3;
            //        DtExcel.Rows[i]["Other_tax"] = Tax_Other_tax;
            //        DtExcel.Rows[i]["Total"] = Tax_Total;

            //        i++;

            //    }*/
            //    AmTT += Convert.ToDecimal(DtExcel.Rows[i]["Sales"]);
            //    Tax_AmTT += Convert.ToDecimal(DtExcel.Rows[i]["TaxableAmount"]);
            //    Tax_SVC += Convert.ToDecimal(DtExcel.Rows[i]["SVC"]);
            //    Tax_GST += Convert.ToDecimal(DtExcel.Rows[i]["GST"]);
            //    Tax_TAX3 += Convert.ToDecimal(DtExcel.Rows[i]["TAX3"]);
            //    Tax_Other_tax += Convert.ToDecimal(DtExcel.Rows[i]["Other_tax"]);
            //    Tax_Total += Convert.ToDecimal(DtExcel.Rows[i]["Total"]);
            //}

            for (int i = 0; i <= DtM.Rows.Count - 1; i++)
            {
                AmTT += Convert.ToDecimal(DtM.Rows[i]["Total"]);
                Tax_AmTT += Convert.ToDecimal(DtM.Rows[i]["taxableamount"]);
                Tax_SVC += Convert.ToDecimal(DtM.Rows[i]["Tax1"]);
                Tax_GST += Convert.ToDecimal(DtM.Rows[i]["Tax2"]);
                Tax_TAX3 += Convert.ToDecimal(DtM.Rows[i]["Tax3"]);
                //Tax_Other_tax += Convert.ToDecimal(DtM.Rows[0]["Other_tax"]);
                //Tax_Total += Convert.ToDecimal(DtM.Rows[0]["Total"]);
            }


            DtExcel.Rows.Add(DtExcel.NewRow());
            DtExcel.Rows[DtExcel.Rows.Count - 1]["Sales"] = AmTT;
            DtExcel.Rows[DtExcel.Rows.Count - 1]["taxableamount"] = Tax_AmTT;
            DtExcel.Rows[DtExcel.Rows.Count - 1]["SVC"] = Tax_SVC;
            DtExcel.Rows[DtExcel.Rows.Count - 1]["GST"] = Tax_GST;
            DtExcel.Rows[DtExcel.Rows.Count - 1]["Tax3"] = Tax_TAX3;
            DtExcel.Rows[DtExcel.Rows.Count - 1]["Other_tax"] = Tax_Other_tax;
            DtExcel.Rows[DtExcel.Rows.Count - 1]["Total"] = AmTT;

            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = DtExcel;
            GridView1.DataBind();
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
                "attachment;filename=TaxAnalysis.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();

        }

    }
}