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.Data.OleDb;

using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using System.Web.UI.DataVisualization.Charting;
using System.Drawing.Printing;
using System.Drawing;
using System.Drawing.Text;

namespace SUNWEB
{
    public partial class PointsHistory : System.Web.UI.Page
    {

        BL_SELECT ObjSel = new BL_SELECT();
        BLCompany ObjBL = new BLCompany();
        PLCOMMON ObjPL = new PLCOMMON();
        BL_USER ObjUserBL = new BL_USER(); //This Object for All User and Page Related fuctions included 
        PLCUSTOMER ObjCustPL = new PLCUSTOMER();
        Int32 K = 0;
        Int32 L = 0;
        Int32 S = 0;
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        DataSet ds = new DataSet();
        MYSQLDB ObjMain = new MYSQLDB();
        MYSQLCLOUD ObjCloudMain = new MYSQLCLOUD();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable DtSuperAdmin = new DataTable();
        DataTable DtMultiBranch = new DataTable();
        DataTable DtNormalUser = new DataTable();
        DataTable DTProductSale = new DataTable();
        MySqlCommand Cmdv = new MySqlCommand();
        // User Variables 
        string UserId = string.Empty;
        string UserName = string.Empty;
        string PortalTypeId = string.Empty;
        string cd_Id = string.Empty;
        string Datapath = string.Empty;
        string cd_BranchAlias = string.Empty;
        string IsMultiBranch = string.Empty;
        string IsSuperAdmin = string.Empty;
        string ActiveCallvalue = string.Empty;
        // License Variables ..
        string PID = string.Empty;
        string Page_Acess = string.Empty;
        string Read_Acess = string.Empty;
        string Write_Acess = string.Empty;
        string Isdowld_Acess = string.Empty;
        string Rcd_id = string.Empty;
        string RPortalid = string.Empty;
        string Useridvalue = string.Empty;
        string QueryCurr_Select = string.Empty;
        string L_MaxUserCnt = string.Empty;

        string L_PointsHistory = string.Empty;
        string Branchidval = string.Empty;
        string BranchFullName = 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;
        DataSet DS = new DataSet();
      
        string Vw_Chk = string.Empty;
        string MBranch = string.Empty;

        string MBranchName = string.Empty;
        string MCustNo = string.Empty;
        string SplBranChk = string.Empty;
        string RBranchhq = string.Empty;
        string RBranchName = string.Empty;
        string RBranchFullName = string.Empty;

        decimal R_EP = 0;
        decimal R_RP = 0;
        decimal R_ADJ = 0;


        protected void Page_Load(object sender, EventArgs e)
        {

            PID = "26";    // This is for Retail Payment Page 


            if (!IsPostBack)
            {
              txtStartDt.Text = Curr_DateOnly;
              txtEndDt.Text = Curr_DateOnly;
            }
            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)
                {
                    LoadBranchName4SuperAdmin();
                }
                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                //LoadInventoryListGrid(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName();
                    ActiveCall();
                }
            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == false))
            {
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;

                Cm_id = ObjSel.GETcmid(Session["Cd_Id"].ToString());  // Get cm_id
                HQ = ObjSel.Get_HQ(Cm_id);     // Get the HQ Value
                Curr_BranchFullName = ObjSel.GetBranchFullName(HQ, Session["Branch_HQ"].ToString());
                lblBranchAliasName.ForeColor = System.Drawing.Color.DodgerBlue;
                lblBranchAliasName.Text = "Branch Name" + "&nbsp;" + "-" + "&nbsp;" + Curr_BranchFullName + "&nbsp; ";

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

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

                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }
                if ((Session["L_PointsHistory"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    // lblMsgTotalSelection.Text = "Item Sale  Between" + "&nbsp;&nbsp;&nbsp;&nbsp;" + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  and  &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                    if (!IsPostBack)
                    {
                        if (Session["Isdowld_Acess"].ToString() == "N")
                        {
                            // btnExcel.Enabled = false;
                            Response.Write(@"<script language='javascript'>alert('No Access To download.')</script>");
                        }
                        else
                        {
                          //  btnGetReports.Visible = true;

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

                        }


                    }

                }

            }
            else
            {

            }


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


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

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

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

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

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


                L_PointsHistory = DtLicense.Rows[0]["RETAILPAYMENT"].ToString();
                Session["L_PointsHistory"] = L_PointsHistory;
            }
            if (Session["L_PointsHistory"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {

                //btnExcel.Enabled = true;
                //lblMsgTotalSelection.Text = "Total Sale Selection Between" + "&nbsp;&nbsp;&nbsp;" + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
                cd_BranchAlias = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                // BindGridview(ActiveCallvalue, txtStartDt.Text, txtEndDt.Text);
                System.Threading.Thread.Sleep(2000);

            }

        }
        protected void BtnGenerate_Click(object sender, EventArgs e)
        {

            if (cmbBranches.Visible == true)
            {

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

            }
            else if (cmbBranches.Visible == false)
            {
                cd_BranchAlias = Session["Branch_HQ"].ToString();
            }


            System.Threading.Thread.Sleep(2000);
            lblSearchResult2.Text = " Search Result -  Points History  Between" + "&nbsp;&nbsp;&nbsp;&nbsp;" + Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text) + "&nbsp;&nbsp;&nbsp;  and  &nbsp;&nbsp;&nbsp;" + Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text);
            BindGridview(cd_BranchAlias);
        }

        public void LoadBranchName()
        {
            DtNormalUser = ObjUserBL.GetBranchOnLoginValues8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtNormalUser.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtNormalUser;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        public void LoadBranchName4SuperAdmin()
        {
            DtSuperAdmin = ObjUserBL.GetBranchOnSuperAdmin8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtSuperAdmin.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtSuperAdmin;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        public void UPDATEBRANCH(string ActiveCallvalue)
        {
            if (!string.IsNullOrEmpty(Session["UserId"] as string))
            {
                Useridvalue = Session["UserId"].ToString();
                QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
                S = ObjMain.ExecuteNonQuery(QueryCurr_Select);
            }
            else
            {
                Response.Redirect("Login.aspx");

            }

        }

        protected void CheckBox_Changed(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[2] { new DataColumn("Name"), new DataColumn("Country") });
            foreach (GridViewRow row in grdCustomerMaster.Rows)
            {
                if (row.RowType == DataControlRowType.DataRow)
                {
                    CheckBox chkRow = (row.Cells[0].FindControl("chkSelect") as CheckBox);
                    if (chkRow.Checked)
                    {
                        string CustomerNumber = (row.Cells[2].FindControl("lblCustomerNumber") as Label).Text;
                        string CustomerName = (row.Cells[4].FindControl("lblCustomerName") as Label).Text;
                        txtCustomerName.Text = CustomerName;
                        txtCutomerNumber.Text = CustomerNumber;

                    }
                }
            }

        }
        protected void grdPointsHead_RowCommand(object sender, GridViewCommandEventArgs e)
        {

            if (cmbBranches.Visible == true)
            {

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

            }
            else if (cmbBranches.Visible == false)
            {
                cd_BranchAlias = Session["Branch_HQ"].ToString();
            }
            int id = int.Parse(e.CommandName.ToString());
            int rowindex = int.Parse(e.CommandArgument.ToString());
            GridView GVMember = (GridView)grdPointsHead.Rows[rowindex].FindControl("gvchild");
            Button btn = ((Button)grdPointsHead.Rows[rowindex].FindControl("Button1"));
            Label LCustNo = ((Label)grdPointsHead.Rows[rowindex].FindControl("lblCustomerNumber"));

            if (btn.Text == "D")
            {
                btn.Text = "C";

                if (cmbBranches.Visible == false)
                {
                    GVMember.DataSource = LoadChild(Convert.ToString(id), cd_BranchAlias); 
                }
                else
                {
                    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 )


                    GVMember.DataSource = LoadChild(Convert.ToString(id), cd_BranchAlias); 
                }
                GVMember.DataBind();
                GVMember.Visible = true;
            }
            else
            {

                btn.Text = "D";
                GVMember.Visible = false;
            }


        }


        public DataTable LoadChild(string CustNumber, string MBranch)
        {

          
               string Qry45 = "Select H.Transdate,H.TransNo ,H.CustomerNumber, C.CustomerName , round(H.EP,2) as EP   ,Round(H.RDM,2) as RDM  ,Round(H.Adj,2) as Adj ,C.CurrentPoints  From "
               + "("
               + " select T.Transdate,T.TransNo,T.CustomerNumber, ROUND(T.Points,2) as EP ,ROUND(T.rdpoints,2)  AS RDM  , 0 as Adj   from  {0}.customerpointsdetails as T where   T.TransStatus=0  AND   date(T.Transdate) between @D1 and @D2 "
               + " UNION ALL"
               + " select x.Transdate,x.TransNo ,x.CustomerNumber, 0 as EP ,ROUND(x.rdpoints,2)   AS RDM, ROUND(x.Points,2) as Adj   from  {0}.customerpointsdetails AS x  where   x.TransStatus=2  AND   date(x.Transdate) between  @D1 and @D2 "
               + " UNION ALL"
               + " select T.Transdate,T.TransNo,T.CustomerNumber, ROUND(T.Points,2) as EP ,ROUND(T.rdpoints,2)  AS RDM  , 0 as Adj    from {0}.customerpointsdetails as T where   T.TransStatus=1  AND   date(T.Transdate) between  @D1 and @D2 "
               + " )H LEFT  join {0}.Customers C  on H.CustomerNumber=C.CustomerNumber  WHERE   date(H.Transdate)  between @D1  and @D1  AND H.CustomerNumber=@CustomerNumber  order by date(H.Transdate)  ;  ";

              string command = string.Format(Qry45, MBranch);

            MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",txtStartDt.Text),
                     new MySqlParameter("@D2",txtEndDt.Text),
                     new MySqlParameter("@CustomerNumber",CustNumber),
                    };
            DT = ObjMain.NewGetData_DT(command, parameters);
            return DT;
                    
        }
        protected void grdPointsHead_RowDataBound(object sender, GridViewRowEventArgs e)
        {

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

                GridView gv = (GridView)e.Row.FindControl("gvchild");
                string CustNOS = ((DataRowView)e.Row.DataItem)["CustomerNumber"].ToString().Trim();
                gv.DataSource = LoadChild(CustNOS, cd_BranchAlias);
                gv.DataBind();




                //string customerId = grdPointsHead.DataKeys[e.Row.RowIndex].Value.ToString();
                //GridView gvChildValue = e.Row.FindControl("grdMemberHead") as GridView;
                //Label total = e.Row.FindControl("lblEarnPoints") as Label;
                //Label total1 = e.Row.FindControl("lblRedeem") as Label;
                //Label total2 = e.Row.FindControl("lblAdjPoint") as Label;

                //R_EP += Convert.ToDecimal(total.Text);
                //R_RP += Convert.ToDecimal(total1.Text);
                //R_ADJ += Convert.ToDecimal(total2.Text);

            }

            if (e.Row.RowIndex == grdPointsHead.Rows.Count)
            {
                Label label = grdPointsHead.HeaderRow.FindControl("HEP") as Label;
                label.Text = R_EP.ToString();
                label.Visible = true;


                Label label1 = grdPointsHead.HeaderRow.FindControl("HRDM") as Label;
                label1.Text = R_RP.ToString();
                label1.Visible = true;

                Label label2 = grdPointsHead.HeaderRow.FindControl("HADJ") as Label;
                label2.Text = R_ADJ.ToString();
                label2.Visible = true;



            }


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

                

            }

        }
        protected void btnSearch_Click(object sender, EventArgs e)
        {

            SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");

            if (SplBranChk != "")
            {
                RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                Session["Branch_HQ"] = RBranchhq;

            }
            else
            {


            }

            if (txtCustomerName.Text.ToString().Trim() != "")
            {
                string QryLoad = "select *  from  {0}.customers   where CustomerName   like CONCAT('%',@SeachText,'%');";
                string command = string.Format(QryLoad, Session["Branch_HQ"].ToString());
                MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@SeachText",txtCustomerName.Text),
                   };
                DT = ObjMain.NewGetData_DT(command, parameters);
                grdCustomerMaster.DataSource = DT;
                grdCustomerMaster.DataBind();

                if (DT.Rows.Count > 0)
                {
                    lblSearchResult.Text = "Search Result ";
                    txtCutomerNumber.Text = DT.Rows[0]["CustomerNumber"].ToString();
                }


            }
            else if (txtCutomerNumber.Text.ToString().Trim() != "")
            {
                string QryLoad = "select *  from  {0}.customers where CustomerNumber like CONCAT('%',@SeachText,'%') ;";
                string command = string.Format(QryLoad, Session["Branch_HQ"].ToString());
                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@SeachText",txtCutomerNumber.Text ),
                   };

                DT = ObjMain.NewGetData_DT(command, parameters);
                grdCustomerMaster.DataSource = DT;
                grdCustomerMaster.DataBind();


                if (DT.Rows.Count > 0)
                {
                    lblSearchResult.Text = "Search Result";
                    txtCustomerName.Text = DT.Rows[0]["CustomerName"].ToString();
                }
               
            }

        }
        private void BindGridview( string BranchNam)
        {
            if (txtCutomerNumber.Text.ToString().Trim() != "")
            {
                SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                if (SplBranChk != "")
                {
                    DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                    RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                    Session["Branch_HQ"] = RBranchhq;
                }
                else
                {
                    DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".branches where Branchid<>1 order by BranchId");
                }
                string PointHDQrySpecific = "Select H.Transdate ,H.TransNo,H.CustomerNumber, C.CustomerName ,  round(Sum(H.EP),2)  as EP ,round(SUm(H.RDM),2) AS RDM ,round(sum(H.Adj),2) AS Adj , round(sum(H.OPBAL),2)  AS OPBAL , round(sum(H.OPBAL)+Sum(H.EP)+ sum(H.Adj)- SUm(H.RDM),2)  AS CLOBAL , round(Sum(H.EP)+sum(H.Adj) -SUm(H.RDM),2) CPoints   From "
                 + " ("
                 + " select T.Transdate,T.TransNo,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EP , SUM(ROUND(T.rdpoints,2))  AS RDM  , 0 as Adj ,0 as OPBAL   from " + BranchNam + ".customerpointsdetails as T where   T.TransStatus=0  AND   date(T.Transdate) between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' group by T.CustomerNumber "
                 + "  UNION ALL"
                 + " select x.Transdate ,x.TransNo,x.CustomerNumber, 0 as EP ,sum(ROUND(x.rdpoints,2))   AS RDM, sum(ROUND(x.Points,2)) as Adj ,0 as OPBAL from  " + BranchNam + ".customerpointsdetails AS x  where   x.TransStatus=2  AND   date(x.Transdate) between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' group by x.CustomerNumber "
                 + "  UNION ALL"
                 + " select T.Transdate,T.TransNo,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EP ,ROUND(T.rdpoints,2)  AS RDM  , 0 as Adj , 0 as OPBAL   from " + BranchNam + ".customerpointsdetails as T where   T.TransStatus=1  AND   date(T.Transdate) between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' group by T.CustomerNumber "
                 + "  Union All"
                 + " Select P.Transdate,P.TransNo,P.CustomerNumber, 0 as EP ,0  AS RDM  , 0 as Adj, sum(P.points)-sum(P.rdpoints) as OPBAL from " + BranchNam + ".customerpointsdetails AS P    inner join " + BranchNam + ".customers  AS C on P.CustomerNumber=C.CustomerNumber  where  P.transdate <  '" + txtEndDt.Text + "' group by P.customernumber"
                 + " ) H LEFT  join " + BranchNam + ".Customers C  on H.CustomerNumber=C.CustomerNumber  WHERE   date(H.Transdate)  between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "'  and  H.CustomerNumber='"+txtCutomerNumber.Text +"'  order by H.Transdate ;";

                Dt2 = ObjCloudMain.GetData_DT(PointHDQrySpecific);

                R_EP = Convert.ToDecimal(Dt2.Compute("sum(EP)", string.Empty));
                R_RP = Convert.ToDecimal(Dt2.Compute("sum(RDM)", string.Empty));
                R_ADJ = Convert.ToDecimal(Dt2.Compute("sum(Adj)", string.Empty));




                grdPointsHead.DataSource = Dt2;
                grdPointsHead.DataBind();
                // Calculation Purpose 
                // Calculation Purpose 
              
                  



            }
            else if (txtCutomerNumber.Text == string.Empty && txtCustomerName.Text == string.Empty && txtStartDt.Text != Curr_DateOnly && txtStartDt.Text != Curr_DateOnly)
            {


                SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");


                if (SplBranChk != "")
                {
                    DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                    RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                    Session["Branch_HQ"] = RBranchhq;

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


                StringBuilder StrBObj = new StringBuilder();

                // String Builder Concepts : On Query 

                string PointHDQry = "Select H.TransNo,H.CustomerNumber, C.CustomerName ,  round(Sum(H.EP),2)  as EP ,round(SUm(H.RDM),2) AS RDM ,round(sum(H.Adj),2) AS Adj , round(sum(H.OPBAL),2)  AS OPBAL , round(sum(H.OPBAL)+Sum(H.EP)+ sum(H.Adj)- SUm(H.RDM),2)  AS CLOBAL , round(Sum(H.EP)+sum(H.Adj) -SUm(H.RDM),2) CPoints   From "
                  + " ("
                  + " select T.Transdate,T.TransNo,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EP , SUM(ROUND(T.rdpoints,2))  AS RDM  , 0 as Adj ,0 as OPBAL   from " + BranchNam + ".customerpointsdetails as T where   T.TransStatus=0  AND   date(T.Transdate) between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "'  group by T.CustomerNumber  "
                  + "  UNION ALL"
                  + " select x.Transdate ,x.TransNo,x.CustomerNumber, 0 as EP ,sum(ROUND(x.rdpoints,2))   AS RDM, sum(ROUND(x.Points,2)) as Adj ,0 as OPBAL from  " + BranchNam + ".customerpointsdetails AS x  where   x.TransStatus=2  AND   date(x.Transdate) between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "'  group by x.CustomerNumber  "
                  + "  UNION ALL"
                  + " select T.Transdate,T.TransNo,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EP ,ROUND(T.rdpoints,2)  AS RDM  , 0 as Adj , 0 as OPBAL   from " + BranchNam + ".customerpointsdetails as T where   T.TransStatus=1  AND   date(T.Transdate) between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "'  group by T.CustomerNumber  "
                  + "  Union All"
                  + " Select P.Transdate,P.TransNo,P.CustomerNumber, 0 as EP ,0  AS RDM  , 0 as Adj, sum(P.points)-sum(P.rdpoints) as OPBAL from " + BranchNam + ".customerpointsdetails AS P    inner join " + BranchNam + ".customers  AS C on P.CustomerNumber=C.CustomerNumber  where  P.transdate <  '" + txtEndDt.Text + "' group by P.customernumber  "
                  + " ) H LEFT  join " + BranchNam + ".Customers C  on H.CustomerNumber=C.CustomerNumber     group by H.CustomerNumber   ;";

                Dt2 = ObjCloudMain.GetData_DT(PointHDQry);


                // Calculation Purpose 
                R_EP = Convert.ToDecimal(Dt2.Compute("sum(EP)", string.Empty));
                R_RP = Convert.ToDecimal(Dt2.Compute("sum(RDM)", string.Empty));
                R_ADJ = Convert.ToDecimal(Dt2.Compute("sum(Adj)", string.Empty));

                grdPointsHead.DataSource = Dt2;
                grdPointsHead.DataBind();
               

            
            }
            else if (txtCutomerNumber.Text == string.Empty && txtCustomerName.Text == string.Empty)
            {

                SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");


                if (SplBranChk != "")
                {
                    DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                    RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                    Session["Branch_HQ"] = RBranchhq;

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


                StringBuilder StrBObj = new StringBuilder();

                // String Builder Concepts : On Query 

                string PointHDQry = "Select H.TransNo,H.CustomerNumber, C.CustomerName ,  round(Sum(H.EP),2)  as EP ,round(SUm(H.RDM),2) AS RDM ,round(sum(H.Adj),2) AS Adj , round(sum(H.OPBAL),2)  AS OPBAL , round(sum(H.OPBAL)+Sum(H.EP)+ sum(H.Adj)- SUm(H.RDM),2)  AS CLOBAL , round(Sum(H.EP)+sum(H.Adj) -SUm(H.RDM),2) CPoints   From "
                  + " ("
                  + " select T.Transdate,T.TransNo,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EP , SUM(ROUND(T.rdpoints,2))  AS RDM  , 0 as Adj ,0 as OPBAL   from " + BranchNam + ".customerpointsdetails as T where   T.TransStatus=0   group by T.CustomerNumber "
                  + "  UNION ALL"
                  + " select x.Transdate ,x.TransNo,x.CustomerNumber, 0 as EP ,sum(ROUND(x.rdpoints,2))   AS RDM, sum(ROUND(x.Points,2)) as Adj ,0 as OPBAL from  " + BranchNam + ".customerpointsdetails AS x  where   x.TransStatus=2   group by x.CustomerNumber "
                  + "  UNION ALL"
                  + " select T.Transdate,T.TransNo,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EP ,ROUND(T.rdpoints,2)  AS RDM  , 0 as Adj , 0 as OPBAL   from " + BranchNam + ".customerpointsdetails as T where   T.TransStatus=1   group by T.CustomerNumber "
                  + "  Union All"
                  + " Select P.Transdate,P.TransNo,P.CustomerNumber, 0 as EP ,0  AS RDM  , 0 as Adj, sum(P.points)-sum(P.rdpoints) as OPBAL from " + BranchNam + ".customerpointsdetails AS P    inner join " + BranchNam + ".customers  AS C on P.CustomerNumber=C.CustomerNumber  where  P.transdate <  '" + txtEndDt.Text + "' group by P.customernumber "
                  + " ) H LEFT  join " + BranchNam + ".Customers C  on H.CustomerNumber=C.CustomerNumber   group by H.CustomerNumber   ;";

                Dt2 = ObjCloudMain.GetData_DT(PointHDQry);



                // Calculation Purpose 
                R_EP = Convert.ToDecimal(Dt2.Compute("sum(EP)", string.Empty));
                R_RP = Convert.ToDecimal(Dt2.Compute("sum(RDM)", string.Empty));
                R_ADJ = Convert.ToDecimal(Dt2.Compute("sum(Adj)", string.Empty));



                grdPointsHead.DataSource = Dt2;
                grdPointsHead.DataBind();

            }


        }


        protected void btnExcelHeader_Click(object sender, EventArgs e)
        {

            if (cmbBranches.SelectedIndex != 0)
            {
                if (Session["Isdowld_Acess"].ToString() == "N")
                {
                    Page page = HttpContext.Current.Handler as Page;
                    string message = "No Access To download";
                    System.Web.UI.ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + message + "');", true);
                }
                else
                {
                    MySqlConnection Cnn = new MySqlConnection(ConStr_Rds);
                    Cnn.Open();

                    if (txtCutomerNumber.Text != "")
                    {

                        SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                        if (SplBranChk != "")
                        {
                            DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                            RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                            Session["Branch_HQ"] = RBranchhq;

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



                        string PointHDQrySpecific = "Select H.Transdate ,C.CustomerName ,H.CustomerNumber,round(Sum(H.EarnPoints),2)  as EarnPoints ,round(SUm(H.RedeemPoints),2) AS RedeemPoints ,round(sum(H.AdjustPoints),2) AS AdjustPoints , round(sum(H.OPBAL),2)  AS OPBAL , round(sum(H.OPBAL)+Sum(H.EarnPoints)+ sum(H.AdjustPoints)- SUm(H.RedeemPoints),2)  AS CLOBAL , round(Sum(H.EarnPoints)+sum(H.AdjustPoints) -SUm(H.RedeemPoints),2) CPoints   From "
                      + " ("
                      + " select T.Transdate,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EarnPoints , SUM(ROUND(T.rdpoints,2))  AS RedeemPoints  , 0 as AdjustPoints ,0 as OPBAL   from {0}.customerpointsdetails as T where   T.TransStatus=0  AND   date(T.Transdate) between @D1 and @D2  group by T.CustomerNumber "
                      + "  UNION ALL"
                      + " select x.Transdate ,x.CustomerNumber, 0 as EarnPoints ,sum(ROUND(x.rdpoints,2))   AS RedeemPoints, sum(ROUND(x.Points,2)) as AdjustPoints ,0 as OPBAL from  {0}.customerpointsdetails AS x  where   x.TransStatus=2  AND   date(x.Transdate) between @D1 and @D2  group by x.CustomerNumber "
                      + "  UNION ALL"
                      + " select T.Transdate ,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints  , 0 as AdjustPoints , 0 as OPBAL   from {0}.customerpointsdetails as T where   T.TransStatus=1  AND   date(T.Transdate) between @D1 and @D2  group by T.CustomerNumber "
                      + "  Union All"
                      + " Select P.Transdate,P.CustomerNumber, 0 as EarnPoints ,0  AS RedeemPoints  , 0 as AdjustPoints, sum(P.points)-sum(P.rdpoints) as OPBAL from {0}.customerpointsdetails AS P    inner join {0}.customers  AS C on P.CustomerNumber=C.CustomerNumber  where  P.transdate <  @D2 group by P.customernumber"
                      + " ) H LEFT  join {0}.Customers C  on H.CustomerNumber=C.CustomerNumber  WHERE   date(H.Transdate)  between @D1 and @D2  and  H.CustomerNumber=@CustomerNumber  order by H.Transdate ;";

                        string command = string.Format(PointHDQrySpecific, MBranch);
                       MySqlParameter[] parameters =
                       {
                         new MySqlParameter("@D1",txtStartDt.Text ),
                         new MySqlParameter("@D2",txtEndDt.Text),
                         new MySqlParameter("@CustomerNumber",txtCutomerNumber.Text),
                        };

                        Dt2 = ObjMain.NewGetData_DT(command,parameters);
                        //Create a dummy GridView
                        GridView GridView1 = new GridView();
                        GridView1.ShowFooter = true;


                        GridView1.AllowPaging = false;
                        GridView1.DataSource = Dt2;
                        GridView1.DataBind();
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition",
                         "attachment;filename=PointsHistory_Header.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++)
                        {
                            //Apply text style to each Row
                            GridView1.Rows[i].Attributes.Add("class", "textmode");
                        }
                        GridView1.RenderControl(hw);
                        //style to format numbers to string
                        string style = @"<style> { mso-number-format:\@; } </style>";
                        Response.Write(style);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.End();
                    }

                    else if (txtCutomerNumber.Text == string.Empty && txtCustomerName.Text == string.Empty && txtStartDt.Text != Curr_DateOnly && txtStartDt.Text != Curr_DateOnly)
                    {


                        SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");


                        if (SplBranChk != "")
                        {
                            DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                            RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                            Session["Branch_HQ"] = RBranchhq;

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


                        StringBuilder StrBObj = new StringBuilder();

                        // String Builder Concepts : On Query 

                        string PointHDQry = "Select H.TransNo,H.CustomerNumber, C.CustomerName ,  round(Sum(H.EarnPoints),2)  as EarnPoints ,round(SUm(H.RedeemPoints),2) AS RedeemPoints ,round(sum(H.AdjustPoints),2) AS AdjustPoints , round(sum(H.OPBAL),2)  AS OPBAL , round(sum(H.OPBAL)+Sum(H.EarnPoints)+ sum(H.AdjustPoints)- SUm(H.RedeemPoints),2)  AS CLOBAL , round(Sum(H.EarnPoints)+sum(H.AdjustPoints) -SUm(H.RedeemPoints),2) CPoints   From "
                          + " ("
                          + " select T.Transdate,T.TransNo,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EarnPoints , SUM(ROUND(T.rdpoints,2))  AS RedeemPoints  , 0 as AdjustPoints ,0 as OPBAL   from {0}.customerpointsdetails as T where   T.TransStatus=0  AND   date(T.Transdate) between @D1 and @D2  group by T.CustomerNumber  "
                          + "  UNION ALL"
                          + " select x.Transdate ,x.TransNo,x.CustomerNumber, 0 as EarnPoints ,sum(ROUND(x.rdpoints,2))   AS RedeemPoints, sum(ROUND(x.Points,2)) as AdjustPoints ,0 as OPBAL from  {0}.customerpointsdetails AS x  where   x.TransStatus=2  AND   date(x.Transdate) between @D1 and @D2  group by x.CustomerNumber  "
                          + "  UNION ALL"
                          + " select T.Transdate,T.TransNo,T.CustomerNumber, SUM(ROUND(T.Points,2)) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints  , 0 as AdjustPoints , 0 as OPBAL   from {0}.customerpointsdetails as T where   T.TransStatus=1  AND   date(T.Transdate) between @D1 and @D2  group by T.CustomerNumber  "
                          + "  Union All"
                          + " Select P.Transdate,P.TransNo,P.CustomerNumber, 0 as EarnPoints ,0  AS RedeemPoints  , 0 as AdjustPoints, sum(P.points)-sum(P.rdpoints) as OPBAL from {0}.customerpointsdetails AS P    inner join {0}.customers  AS C on P.CustomerNumber=C.CustomerNumber  where  P.transdate <  @D2 group by P.customernumber  "
                          + " ) H LEFT  join {0}.Customers C  on H.CustomerNumber=C.CustomerNumber     group by H.CustomerNumber   ;";

                        string command = string.Format(PointHDQry, MBranch);
                                               
                        MySqlParameter[] parameters =
                        {
                         new MySqlParameter("@D1",txtStartDt.Text ),
                         new MySqlParameter("@D2",txtEndDt.Text),
                         
                        };

                        Dt2 = ObjMain.NewGetData_DT(command,parameters);
                        //Create a dummy GridView
                        GridView GridView1 = new GridView();
                        GridView1.ShowFooter = true;

                        GridView1.AllowPaging = false;
                        GridView1.DataSource = Dt2;
                        GridView1.DataBind();
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition",
                         "attachment;filename=PointsHistory_Header.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++)
                        {
                            //Apply text style to each Row
                            GridView1.Rows[i].Attributes.Add("class", "textmode");
                        }
                        GridView1.RenderControl(hw);
                        //style to format numbers to string
                        //  string style = @"<style> textmode  { mso-number-format:\@; } </style>";

                        string style = @"<style> { mso-number-format:\@;  } </style>";
                        Response.Write(style);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.End();


                    }

                    else if (txtCutomerNumber.Text == string.Empty && txtCustomerName.Text == string.Empty)
                    {

                        SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                        if (SplBranChk != "")
                        {
                            DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                            RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                            Session["Branch_HQ"] = RBranchhq;

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


                        string PointHDQry = "Select C.CustomerName ,H.CustomerNumber, round(Sum(H.EarnPoints),2)  as EarnPoints ,round(SUm(H.RedeemPoints),2) AS RedeemPoints ,round(sum(H.AdjustPoints),2) AS AdjustPoints , round(sum(H.OPBAL),2)  AS OPBAL , round(sum(H.OPBAL)+Sum(H.EarnPoints)+ sum(H.AdjustPoints)- SUm(H.RedeemPoints),2)  AS CLOBAL , round(Sum(H.EarnPoints)+sum(H.AdjustPoints) -SUm(H.RedeemPoints),2) CPoints   From "
                         + " ("
                         + " select T.CustomerNumber, SUM(ROUND(T.Points,2)) as EarnPoints , SUM(ROUND(T.rdpoints,2))  AS RedeemPoints  , 0 as AdjustPoints ,0 as OPBAL   from {0}.customerpointsdetails as T where   T.TransStatus=0   group by T.CustomerNumber "
                         + "  UNION ALL"
                         + " select x.CustomerNumber, 0 as EarnPoints ,sum(ROUND(x.rdpoints,2))   AS RedeemPoints, sum(ROUND(x.Points,2)) as AdjustPoints ,0 as OPBAL from  {0}.customerpointsdetails AS x  where   x.TransStatus=2  group by x.CustomerNumber "
                         + "  UNION ALL"
                         + " select T.CustomerNumber, SUM(ROUND(T.Points,2)) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints  , 0 as AdjustPoints , 0 as OPBAL   from {0}.customerpointsdetails as T where   T.TransStatus=1   group by T.CustomerNumber "
                         + "  Union All "
                         + " Select P.CustomerNumber, 0 as EarnPoints ,0  AS RedeemPoints  , 0 as AdjustPoints, sum(P.points)-sum(P.rdpoints) as OPBAL from {0}.customerpointsdetails AS P    inner join {0}.customers  AS C on P.CustomerNumber=C.CustomerNumber  where  P.transdate < @D1 group by P.customernumber "
                         + " ) H LEFT  join {0}.Customers C  on H.CustomerNumber=C.CustomerNumber  group by H.CustomerNumber   ;";

                        string command = string.Format(PointHDQry, MBranch);
                        MySqlParameter[] parameters =
                        {
                          new MySqlParameter("@D2",txtEndDt.Text),
                        };
                        Dt2 = ObjCloudMain.NewGetData_DT(command, parameters);
                        //Create a dummy GridView
                        GridView GridView1 = new GridView();
                        GridView1.ShowFooter = true;

                        GridView1.AllowPaging = false;
                        GridView1.DataSource = Dt2;
                        GridView1.DataBind();
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition",
                         "attachment;filename=PointsHistory_Header.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++)
                        {
                            //Apply text style to each Row
                            GridView1.Rows[i].Attributes.Add("class", "textmode");
                        }
                        GridView1.RenderControl(hw);
                        //style to format numbers to string
                        //  string style = @"<style> textmode  { mso-number-format:\@; } </style>";

                        string style = @"<style> { mso-number-format:\@;  } </style>";
                        Response.Write(style);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.End();

                        // Testing Code :  Start 


                        // Test : End 

                    }

                }
            }


        }

        protected void btnExcel_Click(object sender, EventArgs e)
        {
            if (cmbBranches.SelectedIndex != 0)
            {
                if (Session["Isdowld_Acess"].ToString() == "N")
                {
                    Page page = HttpContext.Current.Handler as Page;
                    string message = "No Access To download";
                    System.Web.UI.ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + message + "');", true);
                }
                else
                {
                   

                    if (txtCutomerNumber.Text != "")
                    {

                        SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                        if (SplBranChk != "")
                        {
                            DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                            RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                            Session["Branch_HQ"] = RBranchhq;

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



                        string Qry45 = "Select H.Transdate,H.TransNo, C.CustomerName,H.CustomerNumber,round(H.EarnPoints,2) as EarnPoints,Round(H.RedeemPoints,2) as RedeemPoints  ,Round(H.AdjustPoints,2) as AdjustPoints  From "
                     + "("
                     + " select T.Transdate,T.TransNo,T.CustomerNumber, ROUND(T.Points,2) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints  , 0 as AdjustPoints   from {0}.customerpointsdetails as T where   T.TransStatus=0  AND   date(T.Transdate) between @D1  and @D2  "
                     + " UNION ALL"
                     + " select x.Transdate,x.TransNo ,x.CustomerNumber, 0 as EarnPoints ,ROUND(x.rdpoints,2)   AS RedeemPoints, ROUND(x.Points,2) as AdjustPoints   from  {0}.customerpointsdetails AS x  where   x.TransStatus=2  AND   date(x.Transdate) between @D1  and @D2  "
                     + " UNION ALL"
                     + " select T.Transdate,T.TransNo,T.CustomerNumber, ROUND(T.Points,2) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints  , 0 as AdjustPoints    from {0}.customerpointsdetails as T where   T.TransStatus=1  AND   date(T.Transdate) between @D1  and @D2  "
                     + " )H LEFT  join {0}.Customers C  on H.CustomerNumber=C.CustomerNumber  WHERE   date(H.Transdate)  between @D1  and @D2  AND H.CustomerNumber=@CustomerNumber  order by date(H.Transdate)  ;  ";

                        string command = string.Format(Qry45, MBranch);

                        MySqlParameter[] parameters =
                           {
                             new MySqlParameter("@D1",txtStartDt.Text),
                             new MySqlParameter("@D2",txtEndDt.Text),
                             new MySqlParameter("@CustomerNumber",txtCutomerNumber.Text.ToString().Trim()),
                             
                            };
                        Dt2 = ObjMain.NewGetData_DT(command, parameters);

                      // Calculation Purpose 
                                         

                        //Create a dummy GridView
                        GridView GridView1 = new GridView();
                        GridView1.ShowFooter = true;


                        GridView1.AllowPaging = false;
                        GridView1.DataSource = Dt2;
                        GridView1.DataBind();
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition",
                         "attachment;filename=PointsHistory_Details.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++)
                        {
                            //Apply text style to each Row
                            GridView1.Rows[i].Attributes.Add("class", "textmode");
                        }
                        GridView1.RenderControl(hw);
                        //style to format numbers to string
                        string style = @"<style> { mso-number-format:\@; } </style>";
                        Response.Write(style);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.End();
                    }

                    else if (txtCutomerNumber.Text == string.Empty && txtCustomerName.Text == string.Empty && txtStartDt.Text != Curr_DateOnly && txtStartDt.Text != Curr_DateOnly)
                    {

                        SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                        if (SplBranChk != "")
                        {
                            DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                            RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                            Session["Branch_HQ"] = RBranchhq;

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



                        string Qry45 = "Select  H.Transdate,H.TransNo, C.CustomerName,H.CustomerNumber, round(H.EarnPoints,2) as EarnPoints,Round(H.RedeemPoints,2) as RedeemPoints  ,Round(H.AdjustPoints,2) as AdjustPoints  From "
                     + "("
                     + " select T.Transdate,T.TransNo,T.CustomerNumber, ROUND(T.Points,2) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints , 0 as AdjustPoints   from  {0}.customerpointsdetails as T where   T.TransStatus=0  AND   date(T.Transdate) between @D1  and @D2 "
                     + " UNION ALL"
                     + " select x.Transdate,x.TransNo ,x.CustomerNumber, 0 as EarnPoints ,ROUND(x.rdpoints,2)   AS RedeemPoints, ROUND(x.Points,2) as AdjustPoints   from  {0}.customerpointsdetails AS x  where   x.TransStatus=2  AND   date(x.Transdate) between @D1  and @D2 "
                     + " UNION ALL"
                     + " select T.Transdate,T.TransNo,T.CustomerNumber, ROUND(T.Points,2) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints , 0 as AdjustPoints    from {0}.customerpointsdetails as T where   T.TransStatus=1  AND   date(T.Transdate) between @D1  and @D2 "
                     + " )H LEFT  join {0}.Customers C  on H.CustomerNumber=C.CustomerNumber  WHERE   date(H.Transdate)  between @D1  and @D2    order by date(H.Transdate)  ;  ";
                        string command = string.Format(Qry45, MBranch);
                        MySqlParameter[] parameters =
                           {
                             new MySqlParameter("@D1",txtStartDt.Text),
                             new MySqlParameter("@D2",txtEndDt.Text),
                           };
                        Dt2 = ObjMain.NewGetData_DT(command, parameters);
                        //Create a dummy GridView
                        GridView GridView1 = new GridView();
                        GridView1.ShowFooter = true;
                        GridView1.AllowPaging = false;
                        GridView1.DataSource = Dt2;
                        GridView1.DataBind();
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition",
                         "attachment;filename=PointsHistory_Details.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++)
                        {
                            //Apply text style to each Row
                            GridView1.Rows[i].Attributes.Add("class", "textmode");
                        }
                        GridView1.RenderControl(hw);
                        //style to format numbers to string
                        string style = @"<style> { mso-number-format:\@; } </style>";
                        Response.Write(style);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.End();

                    }

                    else if (txtCutomerNumber.Text == string.Empty && txtCustomerName.Text == string.Empty)
                    {

                        SplBranChk = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                        if (SplBranChk != "")
                        {
                            DT = ObjCloudMain.GetData_DT("select BranchName,BranchFullName from " + Session["Branch_HQ"] + ".specialbranches where Branchid<>1 order by BranchId");
                            RBranchhq = ObjCloudMain.ExecuteScalar("select BranchName from " + Session["Branch_HQ"] + ".specialbranches where Branchid=1");
                            Session["Branch_HQ"] = RBranchhq;

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


                  string Qry47 = "Select H.Transdate,H.TransNo, C.CustomerName,H.CustomerNumber, round(H.EarnPoints,2) as EarnPoints,Round(H.RedeemPoints,2) as RedeemPoints  ,Round(H.AdjustPoints,2) as AdjustPoints  From "
                  + "("
                  + " select T.Transdate,T.TransNo,T.CustomerNumber, ROUND(T.Points,2) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints  , 0 as AdjustPoints   from  {0}.customerpointsdetails as T where   T.TransStatus=0  "
                  + " UNION ALL"
                  + " select x.Transdate, x.TransNo ,x.CustomerNumber, 0 as EarnPoints ,ROUND(x.rdpoints,2)   AS RedeemPoints, ROUND(x.Points,2) as AdjustPoints   from  {0}.customerpointsdetails AS x  where   x.TransStatus=2  "
                  + " UNION ALL"
                  + " select T.Transdate,T.TransNo,T.CustomerNumber, ROUND(T.Points,2) as EarnPoints ,ROUND(T.rdpoints,2)  AS RedeemPoints  , 0 as AdjustPoints    from {0}.customerpointsdetails as T where   T.TransStatus=1  "
                  + " )H LEFT  join {0}.Customers C  on H.CustomerNumber=C.CustomerNumber  order BY H.Transdate,H.CustomerNumber ;  ";

                        string command = string.Format(Qry47, MBranch);

                        Dt2 = ObjCloudMain.GetData_DT(command);

                        //Create a dummy GridView
                        GridView GridView1 = new GridView();
                        GridView1.ShowFooter = true;

                        GridView1.AllowPaging = false;
                        GridView1.DataSource = Dt2;
                        GridView1.DataBind();
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition",
                         "attachment;filename=PointsHistory_Details.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++)
                        {
                            //Apply text style to each Row
                            GridView1.Rows[i].Attributes.Add("class", "textmode");
                        }
                        GridView1.RenderControl(hw);
                        //style to format numbers to string
                        //  string style = @"<style> textmode  { mso-number-format:\@; } </style>";

                        string style = @"<style>  { mso-number-format:\@;  } </style>";
                        Response.Write(style);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.End();

                        // Testing Code :  Start 


                        // Test : End 

                    }

                }
            }
        }
        protected void btnGetReports_Click(object sender, EventArgs e)
        {

        }
        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        protected void chkAll_CheckedChanged(object sender, EventArgs e)
        {
            txtCustomerName.Text = "";
            txtCutomerNumber.Text = "";
        }


    }
}