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;
using Newtonsoft.Json;
using System.Web.Services;
using CsvHelper.Configuration.Attributes;
using SunWeb.Model;


namespace SUNWEB
{
    public partial class MemberHistory : System.Web.UI.Page
    {
        BL_API ObjApi = new BL_API();
        BL_API objAPI = new BL_API();
        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_MeberHistory = 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 RBranchName = string.Empty;
        string RBranchFullName = string.Empty;
        decimal TotAmunt = 0;
        string MBranchName = string.Empty;
        string SplBranChk = string.Empty;
        string RBranchhq = string.Empty;
      
        string RBranchid = string.Empty;
        string Branchid = string.Empty;
        string ReadHQ = string.Empty;
        string BranchAlias = string.Empty;
        DataTable DtMemberList = new DataTable();
        DataTable DTMemberDL = new DataTable();
        string Ins_CustQuery = string.Empty;
        string RCustomerID = string.Empty;
        string AES_Key = string.Empty;
        string en_userName = string.Empty;

        string en_CustomerName = string.Empty;
        string en_Mobile = string.Empty;
        string en_password = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {

            customeridmsg.Visible = false;
            PID = "25";                         // This is for MemberHistory 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_MeberHistory"].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
                        {


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

            Session["BranchDB"] = ActiveCallvalue;

            if (DtLicense.Rows.Count > 0)
            {
                L_MeberHistory = DtLicense.Rows[0]["MEMBERHISTORY"].ToString();
                Session["L_MeberHistory"] = L_MeberHistory;
            }
            if (Session["L_MeberHistory"].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 = cmbBranches.SelectedItem.Text;
                //BindGridview();
                string Is_CashBack = "";
                string Is_PointRedemption = "";
                if (!IsPostBack)
                {
                    Cmb_BranchName = Session["Branch_HQ"].ToString();
                    Load_CampaingnType();

                    LoadMemberDataList(Cmb_BranchName);

                    Is_CashBack = Get_Generic_Webordingini(BranchAlias, "46");
                    Is_PointRedemption = Get_Generic_Webordingini(BranchAlias, "47");

                    if (Is_CashBack == "1")
                    {
                        divA.Visible = true;
                        DivB.Visible = false;
                        GetCashTrans_ALL(Session["Branch_HQ"].ToString(), BranchAlias);
                        ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText('" + BranchAlias + "');", true);
                        lblSelectionResult.Text = "Based on All Cashback ";

                    }
                    if (Is_PointRedemption == "1")
                    {
                        divA.Visible = false;
                        DivB.Visible = true;
                        GetPointRedeemption_ALL(Session["Branch_HQ"].ToString(), BranchAlias);
                        ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText2('" + BranchAlias + "');", true);
                        lblSelectionResult.Text = "Based on All Points Redemption ";
                    }




                }



            }

        }

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

        }

        public void LoadMemberDataList(string sDb)
        {
            /*   Previous code 
             DtMemberList = ObjSel.GetMemberList(sDb);
             grdMember1.DataSource = DtMemberList;
             grdMember1.DataBind();
            */


            if (cmbBranches.Visible == false)
            {

                BranchAlias = Session["Branch_HQ"].ToString();

            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Session["Branch_HQ"].ToString();
            }

            DtMemberList = ObjSel.GetMemberList(BranchAlias);
            

            #region UserName Decrption 
            if (Cmb_BranchName != "")
            {
                AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);

            }
            #endregion

            if (DtMemberList.Rows.Count > 0)
            {
                foreach (DataRow row in DtMemberList.Rows)
                {
                    string value = row.Field<string>("UserName");
                    row.SetField("UserName", ObjSel.DecryptString(value, AES_Key));
                    string value1 = row.Field<string>("CustomerName");
                    row.SetField("CustomerName", ObjSel.DecryptString(value1, AES_Key));
                }

                grdMember1.DataSource = DtMemberList;
                grdMember1.DataBind();
            }





        }

        public void Load_CampaingnType()
        {
            cmbCampaignType.Items.Add("Cash Back");
            cmbCampaignType.Items.Add("Points");
            cmbCampaignType.Items.Insert(0, "Select CampaignType");
            cmbCampaignType.DataBind();

        }

        public void ClearFormdata()
        {
            txtRMobileNo.Text = "";
            txtRCustomerName.Text = "";
            txtRcid.Text = "";
            cmbCampaignType.SelectedIndex = 0;
            txtCommonValue.Text = "";
            RtxtUserName.Text = "";


        }


        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {

            string Is_CashBack = "";
            string Is_PointRedemption = "";
            ClearFormdata();
            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Write("CampaignAdjustment.aspx");
            }
            else
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, cmbBranches.SelectedItem.Value);
                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 (cmbBranches.SelectedIndex == 1)
                {

                    BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                    Cmb_BranchName = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), BranchFullName);
                    UPDATEBRANCH(Cmb_BranchName);
                    Session["BranchDB"] = Cmb_BranchName;
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + cmbBranches.SelectedItem.Value + " and cd_branchalias='" + Cmb_BranchName + "';");
                    RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
                    DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);
                    Session["BranchDB"] = Cmb_BranchName;
                }
                if (DtLicense.Rows.Count > 0)
                {
                    L_MeberHistory = DtLicense.Rows[0]["CAMPAIGNADJUSTMENT"].ToString();
                    Session["L_MeberHistory"] = L_MeberHistory;
                }
                if ((Session["L_MeberHistory"] == null) || Session["L_MeberHistory"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    if (cmbBranches.SelectedIndex == 1)
                    {
                        // btnExcel.Enabled = true;
                        #region  Validation Between HQ Branch and Normal Branch 
                        BranchFullName = cmbBranches.SelectedItem.Text;          // This will Return Branch FullName 
                        if (BranchFullName == "HQ")
                        {
                            BranchAlias = Session["Branch_HQ"].ToString();
                        }
                        else
                        {
                            BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                            Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                            BranchAlias = Cmb_BranchName;
                        }
                         
                        Is_CashBack = Get_Generic_Webordingini(BranchAlias, "46");
                        Is_PointRedemption = Get_Generic_Webordingini(BranchAlias, "47");

                        if (Is_CashBack == "1")
                        {
                            divA.Visible = true;
                            DivB.Visible = false;
                            GetCashTrans_ALL(Session["Branch_HQ"].ToString(), BranchAlias);
                            ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText('" + BranchAlias + "');", true);
                            lblSelectionResult.Text = "Based on All Cashback ";
                        }
                        if (Is_PointRedemption == "1")
                        {

                            divA.Visible = false;
                            DivB.Visible = true;
                            GetPointRedeemption_ALL(Session["Branch_HQ"].ToString(), BranchAlias);
                            ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText2('" + BranchAlias + "');", true);
                            lblSelectionResult.Text = "Based on All Points Redemption ";
                        }


                        #endregion
                        string _pagename = System.IO.Path.GetFileName(Request.Url.ToString());
                        if (_pagename.Equals("MemberHistory.aspx"))
                        {
                            Cmb_BranchName = Session["Branch_HQ"].ToString();

                            if (!IsPostBack)
                            {
                                Load_CampaingnType();

                            }

                            LoadMemberDataList(Cmb_BranchName);
                            

                        }
                    }
                    else
                    {
                        Response.Redirect("DeniedPage.aspx");
                    }
                }
                else if (Session["Write_Acess"].ToString() == "N")
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    #region  Validation Between HQ Branch and Normal Branch 
                    BranchFullName = cmbBranches.SelectedItem.Text;             // This will Return Branch FullName 
                    if (BranchFullName == "HQ")
                    {
                        BranchAlias = Session["Branch_HQ"].ToString();
                    }
                    else
                    {
                        BranchFullName = cmbBranches.SelectedItem.Text;         // This will Return Branch FullName 
                        Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                        BranchAlias = Cmb_BranchName;
                    }
                    Session["BranchDB"] = BranchAlias;
                    #endregion
                    string _pagename = System.IO.Path.GetFileName(Request.Url.ToString());
                    if (_pagename.Equals("MemberHistory.aspx"))
                    {
                        if (!IsPostBack)
                        {
                            Load_CampaingnType();

                        }

                        Cmb_BranchName = Session["Branch_HQ"].ToString();
                      LoadMemberDataList(Cmb_BranchName);
 
                    }

                }
            }
            UPDATEBRANCH(BranchAlias);
            Is_CashBack = Get_Generic_Webordingini(BranchAlias, "46");
            Is_PointRedemption = Get_Generic_Webordingini(BranchAlias, "47");
            if (Is_CashBack == "1")
            {
                divA.Visible = true;
                DivB.Visible = false;

                GetCashTrans_ALL(Session["Branch_HQ"].ToString(), BranchAlias);
                ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText('" + BranchAlias + "');", true);
                lblSelectionResult.Text = "Based on All Cashback ";
            }
            if (Is_PointRedemption == "1")
            {

                divA.Visible = false;
                DivB.Visible = true;
                GetPointRedeemption_ALL(Session["Branch_HQ"].ToString(), BranchAlias);
                ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText2('" + BranchAlias + "');", true);
                lblSelectionResult.Text = "Based on All Points Redemption ";
            }

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

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


            if (cmbCampaignType.SelectedIndex == 1)
            {
             if (txtRcid.Text != "")
                {
                    divA.Visible = true;
                    DivB.Visible = false;
                    GetCashTrans(Session["Branch_HQ"].ToString(), txtRcid.Text, Cmb_BranchName);
                    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText2('" + BranchAlias + "');", true);
                    lblSelectionResult.Text = " Based on between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' And Specific Customer's  Cashback ";

                }
                else
                {
                    divA.Visible = true;
                    DivB.Visible = false;
                    lblSelectionResult.Text = "Based on All Cashback ";
                    GetCashTrans_ALL(Session["Branch_HQ"].ToString(), BranchAlias);
                }
            }
            else if (cmbCampaignType.SelectedIndex == 2)
            {

                if (txtRcid.Text != "")
                {

                    divA.Visible = false;
                    DivB.Visible = true;
                    GetPointRedeemption(Session["Branch_HQ"].ToString(), txtRcid.Text, Cmb_BranchName);
                    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText2('" + BranchAlias + "');", true);
                    lblSelectionResult.Text = " Based on between '" + txtStartDt.Text + "' and '" + txtEndDt.Text + "' And Specific Customer's  Point Redemption  ";
                }
                else
                {
                    divA.Visible = false;
                    DivB.Visible = true;
                    lblSelectionResult.Text = "Based on All Points Redemption ";
                    GetPointRedeemption_ALL(Session["Branch_HQ"].ToString(), BranchAlias);
                    
                }


            }
            else if (cmbCampaignType.SelectedIndex == 0)
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('Select Any Compaingn Type ');", true);
            }



        }

        protected void btnClear1_Click(object sender, EventArgs e)
        {
            ClearFormdata();

        }

        public void GetCashTrans(string dbhq , string CID,string DB)
        {
            AES_Key = ObjApi.Get_AESKey(DB);
            //DT = ObjMain.GetData_DT("SELECT *  FROM "+ dbhq+".icashbacktrans as C  INNER JOIN  "+ dbhq +".Branches as B on C.Branchid=B.Branchid where CID='"+CID + "' and date(Businessdate) between '"+txtStartDt.Text+"' and  '"+txtEndDt.Text+"' ;");
            DT = ObjMain.GetData_DT("SELECT  C.CashbackID,C.CBID, C.Transid, C.Amount,C.CashbackRate,C.Businessdate as TransDate,B.BranchFullName as BranchName,IC.UserName "
                + " FROM " + dbhq + ".icashbacktrans as C  INNER JOIN  " + dbhq + ".Branches as B " 
                + " on C.Branchid=B.Branchid  inner join " + dbhq + ".Icustomers AS  IC  ON C.Cid= IC.Cid   "  
                + " where C.CID='" + CID + "' and date(C.Businessdate) between '" + txtStartDt.Text + "' and  '" + txtEndDt.Text + "' ;");


            foreach (DataRow row in DT.Rows)
            {
                string value = row.Field<string>("UserName");
                row.SetField("UserName", ObjSel.DecryptString(value, AES_Key));
            }


            grdCompaigncashback.DataSource = DT;
            grdCompaigncashback.DataBind();
       
        }
        public void GetCashTrans_ALL(string dbhq,string DB)
        {
            AES_Key = ObjApi.Get_AESKey(DB);

            DT = ObjMain.GetData_DT("SELECT  C.CashbackID,C.CBID, C.Transid, C.Amount, C.CashbackRate, C.Businessdate as TransDate, B.BranchFullName as BranchName, IC.UserName as UserName  "
                + " FROM " + dbhq + ".icashbacktrans as C  INNER JOIN  " + dbhq + ".Branches as B on " 
                + " C.Branchid = B.Branchid  inner join " + dbhq + ".Icustomers AS  IC  ON C.Cid = IC.Cid ; ");

            foreach (DataRow row in DT.Rows)
            {
                string value = row.Field<string>("UserName");
                row.SetField("UserName", ObjSel.DecryptString(value, AES_Key));
            }



            grdCompaigncashback.DataSource = DT;
            grdCompaigncashback.DataBind();

        }

            
        public void GetPointRedeemption(string dbhq, string CID,string DB)
        {
            AES_Key = ObjApi.Get_AESKey(DB);

            DT = ObjMain.GetData_DT(" SELECT L.ID, L.Transid, L.TransNo, L.BranchName, L.CID, L.Amount, L.Points, L.RDPoints, L.LPRate,L.Transdate,C.UserName FROM  " 
                  +  " "+ dbhq + ".iloyaltypointsdetails as L "
                  + " inner join  "+ dbhq + ".Icustomers as C On L.CID = C.CID WHERE  date(Transdate) between '" + txtStartDt.Text+"' and  '"+txtEndDt.Text+"' AND C.CID = '"+CID+"' ; ");

            foreach (DataRow row in DT.Rows)
            {
                string value = row.Field<string>("UserName");
                row.SetField("UserName", ObjSel.DecryptString(value, AES_Key));
            }

            grdPointredemption.DataSource = DT;
            grdPointredemption.DataBind();
        }

        public void GetPointRedeemption_ALL(string dbhq,string DB)
        {
            AES_Key = ObjApi.Get_AESKey(DB);

            DT = ObjMain.GetData_DT(" SELECT L.ID, L.Transid, L.TransNo, L.BranchName, L.CID, L.Amount, L.Points, L.RDPoints, L.LPRate,L.Transdate,C.UserName AS UserName FROM  "
                  + " " + dbhq + ".iloyaltypointsdetails as L "
                  + " inner join  " + dbhq + ".Icustomers as C On L.CID = C.CID; ");


            foreach (DataRow row in DT.Rows)
            {
                string value = row.Field<string>("UserName");
                row.SetField("UserName",  ObjSel.DecryptString(value, AES_Key));
            }

            grdPointredemption.DataSource = DT;
            grdPointredemption.DataBind();
        }




        protected void btnSearch1_Click(object sender, EventArgs e)
        {
            BranchAlias = Session["Branch_HQ"].ToString();
            Load_SearchData1(BranchAlias, txtMobileNo1.Text.ToString().Trim());
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowModalMemberInformationList1();", true);

        }

        protected void grdMember1_SelectedIndexChanged(object sender, EventArgs e)
        {
            // XSS Attack Prevention 
            if (cmbBranches.Visible == false)
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                if (cmbBranches.SelectedItem.Text == "HQ")
                {
                    // btnExcel.Enabled = true;
                    // HQ Validation    02-12-21
                    BranchFullName = cmbBranches.SelectedItem.Text;        // This will Return Branch FullName 
                    Cmb_BranchName = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), BranchFullName);
                    UPDATEBRANCH(Cmb_BranchName);
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Cmb_BranchName;
                }
            }

            string Is_CashBack = Get_Generic_Webordingini(BranchAlias, "46"); 
            string Is_PointRedemption = Get_Generic_Webordingini(BranchAlias, "47");
            string _mobileno = string.Empty;
            string _customername = string.Empty;

            string _username = string.Empty;

            if (Is_CashBack == "1")
            {
                // show the cashback rate 
                ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText('" + BranchAlias + "');", true);

                #region UserName Decrption 
                if (Cmb_BranchName != "")
                {
                    AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);

                }
                #endregion


                RCustomerID = (grdMember1.SelectedRow.FindControl("lblCustomerID1") as Label).Text;
                DTMemberDL = ObjSel.ReadMemberInformations(Session["Branch_HQ"].ToString(), RCustomerID);
                //_mobileno = ObjSel.DecryptString(DTMemberDL.Rows[0]["Mobile"].ToString(),AES_Key);
                //txtRMobileNo.Text = DTMemberDL.Rows[0]["Mobile"].ToString();
                txtRcid.Text = DTMemberDL.Rows[0]["CID"].ToString();

                _customername=ObjSel.DecryptString(DTMemberDL.Rows[0]["CustomerName"].ToString(), AES_Key);
                txtRCustomerName.Text = _customername;
                _username = ObjSel.DecryptString(DTMemberDL.Rows[0]["UserName"].ToString(),AES_Key);
                RtxtUserName.Text = _username;
                #region ReadCashBackValue 
                string BarerTokenVal = objAPI.GetBarerToken();
                string _cashback_result = objAPI.iCRM_CashbackBalance_By_CID(Session["Branch_HQ"].ToString(), BarerTokenVal, txtRcid.Text.ToString().Trim());
                var cashback_json_result = JsonConvert.DeserializeObject(_cashback_result).ToString();
                var _cashback_result_values = JsonConvert.DeserializeObject<CashBackBalanceModel>(cashback_json_result);
                string _cid = _cashback_result_values.CID;
               
                // Cashback Balance Value 
                decimal _cashback_bal = _cashback_result_values.CashBackBalance;
                txtCommonValue.Text = Convert.ToString(_cashback_bal);
                #endregion


            }

            if (Is_PointRedemption == "1")
            {
                // show the loyalty point rate 
                ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "LoadcompaignTypeText2('" + BranchAlias + "');", true);

                if (Cmb_BranchName != "")
                {
                    AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);

                }


                RCustomerID = (grdMember1.SelectedRow.FindControl("lblCustomerID1") as Label).Text;
                DTMemberDL = ObjSel.ReadMemberInformations(Session["Branch_HQ"].ToString(), RCustomerID);
                //_mobileno = ObjSel.DecryptString(DTMemberDL.Rows[0]["Mobile"].ToString(),AES_Key);
                // txtRMobileNo.Text = _mobileno;
                txtRcid.Text = DTMemberDL.Rows[0]["CID"].ToString();

                _customername = ObjSel.DecryptString(DTMemberDL.Rows[0]["CustomerName"].ToString(), AES_Key);
                txtRCustomerName.Text = _customername;

                _username = ObjSel.DecryptString(DTMemberDL.Rows[0]["UserName"].ToString(), AES_Key);
                RtxtUserName.Text = _username;
                #region  Read the CurrentPoint 
                string BarerTokenVal = objAPI.GetBarerToken();
                // Previous Getting Current Points : 
                //string result_currentPoint = objAPI.iCRM_Get_CurrentPointByCID(Session["Branch_HQ"].ToString(), BarerTokenVal, txtRcid.Text.ToString().Trim());
                string result_currentPoint = objAPI.API_GET_ICurrentPoints(BarerTokenVal, Session["Branch_HQ"].ToString(), txtRcid.Text.ToString().Trim());
                var json_currentpoint = JsonConvert.DeserializeObject(result_currentPoint).ToString();
                var json_curentpoint_values = JsonConvert.DeserializeObject<CurrentPointModel>(json_currentpoint);
                double _currentPoints = json_curentpoint_values.CurrentPoint;

                string current_Bal_Points = Convert.ToString(_currentPoints);
                txtCommonValue.Text = current_Bal_Points;

                #endregion


            }
                      
        }
        public void Load_SearchData1(string BranchNam, string SearchTxt)
        {



            //#region  Existing Code 

            //if (cmbBranches.Visible == false)
            //{

            //    BranchAlias = Session["Branch_HQ"].ToString();

            //}
            //else
            //{
            //    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
            //    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
            //    BranchAlias = Session["Branch_HQ"].ToString();
            //}


            //string QuerySrc = "Select CustomerID,Mobile,CustomerName,UserName  From   {0}.icustomers  Where  UserName like CONCAT('%', @UserName, '%')  ;";
            //string command = string.Format(QuerySrc, BranchAlias);

            //MySqlParameter[] parameters =
            //       {
            //         new MySqlParameter("@UserName",SearchTxt),
            //       };

            //DtMemberList = ObjMain.NewGetData_DT(command, parameters);



            //if (DtMemberList.Rows.Count > 0)
            //{
            //    grdMember1.DataSource = DtMemberList;
            //    grdMember1.DataBind();
            //}
            //else
            //{
            //    LoadMemberDataList(BranchAlias);
            //}

            //#endregion


            #region Load Customer details with decrption 

            if (cmbBranches.Visible == false)
            {

                BranchAlias = Session["Branch_HQ"].ToString();

            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Session["Branch_HQ"].ToString();
            }


            #region UserName Decrption 
            if (Cmb_BranchName != "")
            {
                AES_Key = ObjApi.Get_AESKey(Cmb_BranchName);
                en_userName = ObjSel.EncryptString(SearchTxt, AES_Key);
            }


            #endregion


            //string QuerySrc = "Select CustomerID,Mobile,CustomerName  From   {0}.icustomers  Where  Mobile like CONCAT('%', @Mobile, '%')  ;";
            string QuerySrc = "Select CustomerID,UserName,CustomerName  From   {0}.icustomers  Where  UserName=@UserName;";
            string command = string.Format(QuerySrc, BranchAlias);

            MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@UserName",en_userName),
                   };

            DtMemberList = ObjMain.NewGetData_DT(command, parameters);

            foreach (DataRow row in DtMemberList.Rows)
            {
                         
                string value = row.Field<string>("UserName");
                row.SetField("UserName", ObjSel.DecryptString(value, AES_Key));
                string value1 = row.Field<string>("CustomerName");
                row.SetField("CustomerName", ObjSel.DecryptString(value1, AES_Key));

             }

            if (DtMemberList.Rows.Count > 0)
            {
               
                grdMember1.DataSource = DtMemberList;
                grdMember1.DataBind();
            }
            else
            {
                customeridmsg.Visible = true;
                grdMember1.DataSource = null;
                grdMember1.DataBind();
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "ShowModalIcustomers();", true);

            }
            #endregion


        }
        private string Get_Generic_Webordingini(string DB, string IniID)
        {
            if (cmbBranches.Visible == true)
            {
                #region  Validation Between HQ Branch and Normal Branch 
                BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                if (BranchFullName == "HQ")
                {
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Cmb_BranchName;

                }
                #endregion
            }
            else
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }




            BL_API objAPI = new BL_API();
            string returnvalue = string.Empty;
            string sBearerToken = objAPI.GetBarerToken();

            string JSONRetValue = objAPI.API_GET_INIWebOrderingIniValue(sBearerToken, BranchAlias, IniID);
            var sObject = "[" + JsonConvert.DeserializeObject(JSONRetValue).ToString().Trim() + "]";
            DataTable dtResult = (DataTable)JsonConvert.DeserializeObject(sObject, (typeof(DataTable)));
            if (dtResult.Rows.Count == 0)
            {
                returnvalue = "";
            }
            else
            {
                returnvalue = (dtResult.Rows[0]["ini_value"]).ToString();
            }
            return returnvalue;
        }


        [WebMethod]
        public static string IsCashbackEnable(string DB)
        {
            try
            {
                BL_SELECT objSel = new BL_SELECT();
                DataTable DT = new DataTable();
                BL_API ObjApi = new BL_API();

                string IniID = "46"; //  ICRM:Cashback 
                string BarereTokenVal = ObjApi.GetBarerToken();
                string JSONRetVal = ObjApi.API_GET_INIWebOrderingIniValue(BarereTokenVal, DB, IniID);
                var sObject = JsonConvert.DeserializeObject(JSONRetVal);
                return sObject.ToString();
                //string BarerTokenVal = 
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        [WebMethod]
        public static string IsPointredemptionEnable(string DB)
        {
            try
            {
                BL_SELECT objSel = new BL_SELECT();
                DataTable DT = new DataTable();
                BL_API ObjApi = new BL_API();

                string IniID = "47"; //  ICRM: PointRedemption
                string BarereTokenVal = ObjApi.GetBarerToken();
                string JSONRetVal = ObjApi.API_GET_INIWebOrderingIniValue(BarereTokenVal, DB, IniID);
                var sObject = JsonConvert.DeserializeObject(JSONRetVal);
                return sObject.ToString();
                //string BarerTokenVal = 
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected void btnExcel_Click(object sender, EventArgs e)
        {


            if (cmbCampaignType.SelectedIndex == 1)
            {

                if (txtRcid.Text != "" && txtStartDt.Text != "" && txtEndDt.Text != "")
                {
                    DT = ObjMain.GetData_DT("SELECT  C.CBID, C.Transid, C.Amount,C.CashbackRate,C.Businessdate as TransDate,B.BranchFullName as BranchName,IC.UserName  FROM " + Session["Branch_HQ"].ToString() + ".icashbacktrans as C  INNER JOIN  " + Session["Branch_HQ"].ToString() + ".Branches as B on C.Branchid=B.Branchid  inner join " + Session["Branch_HQ"].ToString() + ".Icustomers AS  IC  ON C.Cid= IC.Cid   where C.CID='" + txtRcid.Text + "' and date(C.Businessdate) between '" + txtStartDt.Text + "' and  '" + txtEndDt.Text + "' ;");

                }
                else if (txtStartDt.Text != "" && txtEndDt.Text != "")
                {


                    if (DT.Rows.Count > 0)
                    {
                        DT = ObjMain.GetData_DT("SELECT  C.CBID, C.Transid, C.Amount,C.CashbackRate,C.Businessdate as TransDate,B.BranchFullName as BranchName ,IC.UserName  FROM dahq.icashbacktrans as C  INNER JOIN  " + Session["Branch_HQ"].ToString() + ".Branches as B on C.Branchid=B.Branchid  inner join " + Session["Branch_HQ"].ToString() + ".Icustomers AS  IC  ON C.Cid= IC.Cid   where date(C.Businessdate) between '" + txtStartDt.Text + "' and  '" + txtEndDt.Text + "' ");
                    }
                    else

                    {
                        DT = ObjMain.GetData_DT("SELECT  C.CBID, C.Transid, C.Amount,C.CashbackRate,C.Businessdate as TransDate,B.BranchFullName as BranchName ,IC.UserName  FROM " + Session["Branch_HQ"].ToString() + ".icashbacktrans as C  INNER JOIN  " + Session["Branch_HQ"].ToString() + ".Branches as B on C.Branchid=B.Branchid  inner join " + Session["Branch_HQ"].ToString() + ".Icustomers AS  IC  ON C.Cid= IC.Cid ;");

                    }



                }
                else
                {
                    DT = ObjMain.GetData_DT("SELECT  C.CBID, C.Transid, C.Amount,C.CashbackRate,C.Businessdate as TransDate,B.BranchFullName as BranchName ,IC.UserName  FROM " + Session["Branch_HQ"].ToString() + ".icashbacktrans as C  INNER JOIN  " + Session["Branch_HQ"].ToString() + ".Branches as B on C.Branchid=B.Branchid  inner join " + Session["Branch_HQ"].ToString() + ".Icustomers AS  IC  ON C.Cid= IC.Cid ;");

                }



                //Create a dummy GridView
                GridView GridView1 = new GridView();
                GridView1.ShowFooter = true;
                GridView1.AllowPaging = false;
                GridView1.DataSource = DT;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition",
                 "attachment;filename=MemberHistory_Cashback.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> TD { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();

            }
            else if (cmbCampaignType.SelectedIndex == 2)
            {
                if (txtRcid.Text != "" && txtStartDt.Text != "" && txtEndDt.Text != "")
                {
                    //DT = ObjMain.GetData_DT("SELECT  C.CBID, C.Transid, C.Amount,C.CashbackRate,C.Businessdate as TransDate,B.BranchFullName as BranchName,IC.UserName  FROM " + Session["Branch_HQ"].ToString() + ".icashbacktrans as C  INNER JOIN  " + Session["Branch_HQ"].ToString() + ".Branches as B on C.Branchid=B.Branchid  inner join " + Session["Branch_HQ"].ToString() + ".Icustomers AS  IC  ON C.Cid= IC.Cid   where C.CID='" + txtRcid.Text + "' and date(C.Businessdate) between '" + txtStartDt.Text + "' and  '" + txtEndDt.Text + "' ;");


                    DT = ObjMain.GetData_DT(" SELECT L.ID, L.Transid, L.TransNo, L.BranchName, L.CID, L.Amount, L.Points, L.RDPoints, L.LPRate,L.Transdate,C.UserName FROM  "
                  + " " + Session["Branch_HQ"].ToString() + ".iloyaltypointsdetails as L "
                  + " inner join  " + Session["Branch_HQ"].ToString() + ".Icustomers as C On L.CID = C.CID WHERE  date(Transdate) between '" + txtStartDt.Text + "' and  '" + txtEndDt.Text + "' AND C.CID = '" + txtRcid.Text + "' ; ");

                }
                else if (txtStartDt.Text != "" && txtEndDt.Text != "")
                {


                    if (DT.Rows.Count > 0)
                    {
                         DT = ObjMain.GetData_DT(" SELECT L.ID, L.Transid, L.TransNo, L.BranchName, L.CID, L.Amount, L.Points, L.RDPoints, L.LPRate,L.Transdate,C.UserName FROM  "
                         + " " + Session["Branch_HQ"].ToString() + ".iloyaltypointsdetails as L "
                         + " inner join  " + Session["Branch_HQ"].ToString() + ".Icustomers as C On L.CID = C.CID WHERE  date(Transdate) between '" + txtStartDt.Text + "' and  '" + txtEndDt.Text + "' ; ");

                    }
                    else

                    {
                        DT = ObjMain.GetData_DT(" SELECT L.ID, L.Transid, L.TransNo, L.BranchName, L.CID, L.Amount, L.Points, L.RDPoints, L.LPRate,L.Transdate,C.UserName FROM  "
                       + " " + Session["Branch_HQ"].ToString() + ".iloyaltypointsdetails as L "
                       + " inner join  " + Session["Branch_HQ"].ToString() + ".Icustomers as C On L.CID = C.CID; ");

                    }



                }
                else
                {
                    DT = ObjMain.GetData_DT(" SELECT L.ID, L.Transid, L.TransNo, L.BranchName, L.CID, L.Amount, L.Points, L.RDPoints, L.LPRate,L.Transdate,C.UserName FROM  "
                  + " " + Session["Branch_HQ"].ToString() + ".iloyaltypointsdetails as L "
                  + " inner join  " + Session["Branch_HQ"].ToString() + ".Icustomers as C On L.CID = C.CID; ");

                }



                //Create a dummy GridView
                GridView GridView1 = new GridView();
                GridView1.ShowFooter = true;
                GridView1.AllowPaging = false;
                GridView1.DataSource = DT;
                GridView1.DataBind();
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition",
                 "attachment;filename=MemberHistory_Pointsredemption.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> TD { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
            else if(cmbCampaignType.SelectedIndex == 0)
            {
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert('Select Any Compaingn Type to Generate Excel Report');", true);
            }



        }
    }
}