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

namespace SUNWEB
{
    public partial class SupplierwisecostandcollectionReport : 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();
        DataTable DT = new DataTable();
        DataTable Dt1 = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable Dt3 = new DataTable();
        DataTable DtT = new DataTable();
        DataTable DtF = 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();
        MYSQLCLOUD ObjCloudMain = new MYSQLCLOUD();
        // 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_SupplierWisecostandCollection = 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 BraNam = "";
        string FDate = "";
        string TDate = "";
        string sSelQry = "";
        string sTotQry = "";
        string sCondQry = "";
        string sPLQry = "";
        string sCmd = "";

        protected void Page_Load(object sender, EventArgs e)
        {

            PID = "55";    // This is for Supplierwise Cost and Collection Report Page 


            if (!IsPostBack)
            {
                txtFromDt.Text = Curr_DateOnly;
                txtToDt.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_SupplierWisecostandCollection"].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
                        {
                            btnGenerate.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_SupplierWisecostandCollection = DtLicense.Rows[0]["SUPPLIERWISECOSTANDCOLLECTIONREPORT"].ToString();
                Session["L_SupplierWisecostandCollection"] = L_SupplierWisecostandCollection;
            }
            if (Session["L_SupplierWisecostandCollection"].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(ActiveCallvalue, txtStartDt.Text, txtEndDt.Text);
                System.Threading.Thread.Sleep(2000);
            }
        }

        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 cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {
            // XSS Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text.Trim());

            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Redirect("SupplierwisecostandcollectionReport.aspx", false);
            }
            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;
                }

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

                if (ISHQ != "")
                {
                    ActiveCallvalue = ObjSel.GetFirstBranch(Session["Branch_HQ"].ToString());
                    UPDATEBRANCH(ActiveCallvalue);
                }
                else
                {
                    BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(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);
                }

                if (DtLicense.Rows.Count > 0)
                {
                    L_MaxUserCnt = DtLicense.Rows[0]["UserCnt"].ToString();
                    L_SupplierWisecostandCollection = DtLicense.Rows[0]["SUPPLIERWISECOSTANDCOLLECTIONREPORT"].ToString();
                    // Create Session for License Variables 
                    Session["L_MaxUserCnt"] = L_MaxUserCnt;
                    Session["L_SupplierWisecostandCollection"] = L_SupplierWisecostandCollection;
                }
                if (Session["L_SupplierWisecostandCollection"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    Response.Redirect("DeniedPage.aspx", false);
                }
                else
                {
                    //btnExcel.Enabled = true;
                    //lblMsgTotalSelection.Text = "Group Sales Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                    //lblMsgTotalSelection.Text = "Search Result - Retail Payment Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                    lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt;
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                    cd_BranchAlias = Cmb_BranchName;
                    BraNam = cd_BranchAlias;
                    FDate = txtFromDt.Text;
                    TDate = txtToDt.Text;
                    //LoadSupplierWisecostandCollectionQry(cd_BranchAlias, StartDT, EndDt);
                    //BindGridview(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
                }
            }
            UPDATEBRANCH(Cmb_BranchName);

        }


        
        protected void btnGenerate_Click(object sender, EventArgs e)
        {
            // XSS Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text.Trim());

            GridView1.DataSource = null;
            GridView1.DataBind();

            if (cmbBranches.Visible == false)
            {
                btnGenerate.Visible = true;
                //lblMsgTotalSelection.Text = "Search Result - Retail Payment Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                LoadSupplierWisecostandCollectionQry(Session["Branch_HQ"].ToString(), StartDT, EndDt);
                System.Threading.Thread.Sleep(2000);
            }
            else if (cmbBranches.Visible == true)
            {
                //lblMsgTotalSelection.Text = " Search Result - Retail Payment Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                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 )
                BraNam = cd_BranchAlias;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadSupplierWisecostandCollectionQry(cd_BranchAlias, StartDT, EndDt);
            }

        }

        //nid added for Item group PL on 24-May-2024
        protected void btnGenerate1_Click(object sender, EventArgs e)
        {
            // XSS Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text.Trim());

            GridView1.DataSource = null;
            GridView1.DataBind();

            if (cmbBranches.Visible == false)
            {
                btnGenerate.Visible = true;
                //lblMsgTotalSelection.Text = "Search Result - Retail Payment Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                LoadItemGroupwisePL(Session["Branch_HQ"].ToString(), StartDT, EndDt);
                System.Threading.Thread.Sleep(2000);
            }
            else if (cmbBranches.Visible == true)
            {
                //lblMsgTotalSelection.Text = " Search Result - Retail Payment Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                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 )
                BraNam = cd_BranchAlias;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadItemGroupwisePL(cd_BranchAlias, StartDT, EndDt);
            }

        }

        protected void LoadSupplierWisecostandCollectionQry(string BranchAlias, string D1, string D2)
        {
            BraNam = BranchAlias;

            // Prevent  SQL Injections 
            // Parameterized Approach

            //for generating Date Range for the report generation (0 filling purpose)
            sCmd = "DROP TABLE IF EXISTS {0}.`olrpdates` ";
            string command = string.Format(sCmd, BraNam);
            K = ObjCloudMain.ExecuteNonQuery(command);

            string sCmd1 = "CREATE TABLE  {0}.`olrpdates` (" +
             " WeekName varchar(50),`TransDate` datetime DEFAULT NULL " +
             " ) ENGINE=InnoDB ";

            string command1 = string.Format(sCmd1, BraNam);
            K = ObjCloudMain.ExecuteNonQuery(command1);

            DateTime dateStart = Convert.ToDateTime(Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text));
            do
            {
                string sCmd3 = "INSERT INTO {0}.olrpdates (WeekName,TransDate) VALUES (DayName('" + dateStart.ToString("yyyy-MM-dd") + "'),'" + dateStart.ToString("yyyy-MM-dd") + "' ) ";
                //string sCmd3 = "INSERT INTO {0}.olrpdates (WeekName,TransDate) VALUES (DATE_FORMAT('" + dateStart.ToString("dd-MM-YYYY") + "', '%a'),'" + dateStart.ToString("dd-MM-YYYY") + "' ) ";
                string command2 = string.Format(sCmd3, BraNam);

                K = ObjCloudMain.ExecuteNonQuery(command2);
                dateStart = dateStart.AddDays(1);
            } while (dateStart <= Convert.ToDateTime(Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text)));

            //for setting WeekName and Date (Dt1)
            //sCmd = "Select DayName(TransDate) as WeekName, DATE_FORMAT(TransDate, '%Y-%m-%d') as TransDate " +
            sCmd = "Select DATE_FORMAT(TransDate, '%a') as Week, DATE_FORMAT(TransDate, '%d-%m-%Y') as Date " +
                " From {0}.olrpdates " +
                " Where TransDate Between  @D1 and  @D2  Order by TransDate ";

            string command3 = string.Format(sCmd, BraNam);
            MySqlParameter[] params1 =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

            Dt1 = ObjMain.NewGetData_DT(command3, params1);

            //Part 2 For getting Supplier total from SupplierPurchase Table (Datatable Dt2)
            sSelQry = "";
            sTotQry = "";
            sCondQry = "";
            //sCmd = "Select Distinct PTE.PaymentType" +
            sCmd = "Select Distinct Replace(SP.SupplierName,' ','') as SupplierName" +
                " From {0}.SupplierPurchase SP " +
                " Where SP.TransDate Between  @D1 and  @D2  And Status <> 1 Order by 1 ";
            DtT.Clear();

            string command4 = string.Format(sCmd, BraNam);
            MySqlParameter[] params2 =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

            DtT = ObjMain.NewGetData_DT(command4, params2);

            //,Sum(Round(ifnull(K.GRAB,0),2)) as GRAB
            ///,case when PTE.PaymentType = 'GRAB' then EC.Amount end as GRAB
            if (DtT.Rows.Count != 0)
            {
                foreach (DataRow row in DtT.Rows)
                {
                    if (sSelQry == "")
                    {
                        sSelQry = ",format(Sum(Round(ifnull(K." + row["SupplierName"] + ",0),2)),2) as " + row["SupplierName"];
                        sTotQry = " Round(ifnull(K." + row["SupplierName"] + ",0),2)+";
                        sCondQry = ",case when Replace(SP.SupplierName,' ','')='" + row["SupplierName"] + "' then SP.Amount end As " + row["SupplierName"];
                    }
                    else
                    {
                        sSelQry = sSelQry + ",format(Sum(Round(ifnull(K." + row["SupplierName"] + ",0),2)),2) as " + row["SupplierName"];
                        sTotQry = sTotQry+ " Round(ifnull(K." + row["SupplierName"] + ",0),2)+";
                        sCondQry = sCondQry + ",case when Replace(SP.SupplierName,' ','')='" + row["SupplierName"] + "' then SP.Amount end As " + row["SupplierName"];
                    }
                }
                
                //for adding Format and Sum
                sTotQry = ",format(Sum(" + sTotQry + "),2) as TotalPurchase ";
                //for removing last + from TotalPurchase string
                sTotQry = sTotQry.Replace("+),", "),");

                sCmd = "Select  DATE_FORMAT(K.TransDate, '%a') as Week, DATE_FORMAT(K.TransDate, '%d-%m-%Y') as Date" + sSelQry + sTotQry + "  From (" +
                " Select SP.TransDate as TransDate" + sCondQry +
                " From {0}.SupplierPurchase SP " +
                " Where SP.TransDate Between @D1 and @D2 And Status <> 1 " +
                " ) K Group by 2";

                string command5 = string.Format(sCmd, BraNam);
                MySqlParameter[] param3 =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                Dt2 = ObjMain.NewGetData_DT(command5, param3);
            }

            //Part 3 Sales Total (Datatable Dt3)
            //sCmd = "Select DayName(T.BusinessDate) as WeekName, DATE_FORMAT(T.BusinessDate, '%Y-%m-%d') as TransDate,Format(Sum(TD.Amount),2) as TotalSales, '0.00' as PL " +
            //" From {0}.Trans T Left Outer Join {0}.Transdetail TD On T.TransID = TD.TransID " +
            //" Where T.BusinessDate Between @D1 and @D2 " +
            //" And T.TransStatus in (2) And TD.ItemStatus <> 1 And Group3<>'' " +
            //" Group by 2 Order By 2";

            //string command6 = string.Format(sCmd, BraNam);
            //MySqlParameter[] param4 =
            //        {
            //         new MySqlParameter("@D1",D1),
            //         new MySqlParameter("@D2",D2),
            //        };
            //Dt3 = ObjMain.NewGetData_DT(command6, param4);

            //for Setting Primary key for Merge
            Dt1.PrimaryKey = new DataColumn[] { Dt1.Columns["Date"], Dt1.Columns["Week"] };
            Dt2.PrimaryKey = new DataColumn[] { Dt2.Columns["Date"], Dt2.Columns["Week"] };
            //Dt3.PrimaryKey = new DataColumn[] { Dt3.Columns["TransDate"], Dt3.Columns["WeekName"] };
            
            //for Merging
            Dt1.Merge(Dt2);
            //Dt1.Merge(Dt3);

            //for filling 0 values
            for (int i = 0; i < Dt1.Rows.Count; i++)
            {
                for (int j = 0; j < Dt1.Columns.Count; j++)
                {
                    
                    if (string.IsNullOrEmpty(Dt1.Rows[i][j].ToString()))
                    {
                        Dt1.Rows[i][j] = "0.00";
                    }
                }
            }

            // for calculating PL value
            //foreach (DataRow row in Dt1.Rows)
            //{
            //    // Retrieve current values
            //    double v1 = (double)Convert.ToDecimal(row["TotalPurchase"]);
            //    double v2 = (double)Convert.ToDecimal(row["TotalSales"]);

            //    // Perform dynamic update logic
            //    double result = (double)Convert.ToDecimal(v2 - v1);

            //    // Assign the new value to the "Result" column
            //    row["pl"] = string.Format("{0:n2}", result); ;
            //}

            //Part 4 For getting Total fields
            DataRow totalsRow = Dt1.NewRow();
            DataColumn dc = new DataColumn("Sales", typeof(string));
            foreach (DataColumn col in Dt1.Columns)
            {
                Double colTotal = 0;
                foreach (DataRow row in col.Table.Rows)
                {
                    if ((col.ColumnName != "Week") && (col.ColumnName != "Date"))
                    {
                        object value = row[col];
                        //if (row[col] != "")
                        if (value != DBNull.Value)
                        {
                            if (Double.Parse(row[col].ToString()) != 0)
                            {
                                colTotal += Double.Parse(row[col].ToString());
                            }
                        }
                    }
                }
                if (col.ColumnName == "Week")
                {
                    totalsRow[col.ColumnName] = "Total";
                }
                else if (col.ColumnName == "Date")
                {
                    totalsRow[col.ColumnName] = "";

                }
                else
                {
                    totalsRow[col.ColumnName] = string.Format("{0:n2}", colTotal);
                }
            }
            Dt1.Rows.Add(totalsRow);


            // Clear existing data and columns
            GridView1.DataSource = null;
            GridView1.DataBind();
            GridView1.Columns.Clear();

            //for generating report
            GridView1.DataSource = Dt1;

            // Dynamically create columns
            foreach (DataColumn column in Dt1.Columns)
            {
                BoundField boundField = new BoundField
                {
                    DataField = column.ColumnName,
                    HeaderText = column.ColumnName
                };
                GridView1.Columns.Add(boundField);
            }

            GridView1.DataBind();

            //GridView1.Rows[GridView1.Rows.Count - 1].BackColor = Color.Gray;
            //GridView1.Rows[GridView1.Rows.Count - 1].ForeColor = Color.Yellow;
            GridView1.Rows[GridView1.Rows.Count - 1].BackColor = Color.Cornsilk;
            GridView1.Rows[GridView1.Rows.Count - 1].ForeColor = Color.DarkSlateBlue ;
            GridView1.Rows[GridView1.Rows.Count - 1].Font.Bold = true;

        }


        //nid addded for Item Group wise PL on 24-May-2024
        protected void LoadItemGroupwisePL(string BranchAlias, string D1, string D2)
        {
            BraNam = BranchAlias;

            // Prevent  SQL Injections 
            // Parameterized Approach

            //for generating Date Range for the report generation (0 filling purpose)
            sCmd = "DROP TABLE IF EXISTS {0}.`olrpdates` ";
            string command = string.Format(sCmd, BraNam);
            K = ObjCloudMain.ExecuteNonQuery(command);

            string sCmd1 = "CREATE TABLE  {0}.`olrpdates` (" +
             " WeekName varchar(50),`TransDate` datetime DEFAULT NULL " +
             " ) ENGINE=InnoDB ";

            string command1 = string.Format(sCmd1, BraNam);
            K = ObjCloudMain.ExecuteNonQuery(command1);

            DateTime dateStart = Convert.ToDateTime(Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text));
            do
            {
                string sCmd3 = "INSERT INTO {0}.olrpdates (WeekName,TransDate) VALUES (DayName('" + dateStart.ToString("yyyy-MM-dd") + "'),'" + dateStart.ToString("yyyy-MM-dd") + "' ) ";
                string command2 = string.Format(sCmd3, BraNam);

                K = ObjCloudMain.ExecuteNonQuery(command2);
                dateStart = dateStart.AddDays(1);
            } while (dateStart <= Convert.ToDateTime(Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text)));

            //for setting WeekName and Date (Dt1)
            sCmd = "Select DATE_FORMAT(TransDate, '%a') as Week, DATE_FORMAT(TransDate, '%d-%m-%Y') as Date " +
                " From {0}.olrpdates " +
                " Where TransDate Between  @D1 and  @D2  Order by TransDate ";

            string command3 = string.Format(sCmd, BraNam);
            MySqlParameter[] params1 =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

            Dt1 = ObjMain.NewGetData_DT(command3, params1);

            //Part 2 For getting Item Group Total from SupplierPurchase Table (Datatable Dt2)
            sSelQry = "";
            sTotQry = "";
            sCondQry = "";
            //sCmd = "Select Distinct PTE.PaymentType" +
            sCmd = "Select Distinct(ItemGroup) as ItemGroup From {0}.SupplierPurchaseLink Order by 1 ";
            DtT.Clear();

            string command4 = string.Format(sCmd, BraNam);
            MySqlParameter[] params2 =
                    {
   
                    };

            DtT = ObjMain.NewGetData_DT(command4, params2);

            //,Sum(Round(ifnull(K.GRAB,0),2)) as GRAB
            ///,case when PTE.PaymentType = 'GRAB' then EC.Amount end as GRAB
            if (DtT.Rows.Count != 0)
            {
                foreach (DataRow row in DtT.Rows)
                {
                    if (sSelQry == "")
                    {
                        sSelQry = ",format(Sum(Round(ifnull(K." + row["ItemGroup"] + ",0),2)),2) as " + row["ItemGroup"];
                        sTotQry = " Round(ifnull(K." + row["ItemGroup"] + ",0),2)+";
                        sCondQry = ",case when Replace(SPL.ItemGroup,' ','')='" + row["ItemGroup"] + "' then SP.Amount end As " + row["ItemGroup"];
                    }
                    else
                    {
                        sSelQry = sSelQry + ",format(Sum(Round(ifnull(K." + row["ItemGroup"] + ",0),2)),2) as " + row["ItemGroup"];
                        sTotQry = sTotQry + " Round(ifnull(K." + row["ItemGroup"] + ",0),2)+";
                        sCondQry = sCondQry + ",case when Replace(SPL.ItemGroup,' ','')='" + row["ItemGroup"] + "' then SP.Amount end As " + row["ItemGroup"];
                    }
                }

                //for adding Format and Sum
                sTotQry = ",format(Sum(" + sTotQry + "),2) as TotalPurchase ";
                //for removing last + from TotalPurchase string
                sTotQry = sTotQry.Replace("+),", "),");

                sCmd = "Select  DATE_FORMAT(K.TransDate, '%a') as Week, DATE_FORMAT(K.TransDate, '%d-%m-%Y') as Date" + sSelQry + sTotQry + "  From (" +
                " Select SP.TransDate as TransDate" + sCondQry +
                " From {0}.SupplierPurchase SP Left Outer Join {0}.SupplierPurchaseLink SPL On SP.SupplierName=SPL.SupplierName " +
                " Where SP.TransDate Between @D1 and @D2 And Status <> 1 " +
                " ) K Group by 2";

                string command5 = string.Format(sCmd, BraNam);
                MySqlParameter[] param3 =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                Dt2 = ObjMain.NewGetData_DT(command5, param3);
            }

            //for renaming columns names for avoiding merging issue
            foreach (DataColumn column in Dt2.Columns)
            {
                string colname = column.ColumnName;
                if (colname != "Week" && colname != "Date" && colname != "TotalPurchase") 
                {
                    Dt2.Columns[colname].ColumnName = "P_" + colname;
                }
            }

            //Part 3 Sales Total (Datatable Dt3)
            sSelQry = "";
            sTotQry = "";
            sCondQry = "";
            //sCmd = "Select Distinct PTE.PaymentType" +
            sCmd = "Select Distinct(ItemGroup) as ItemGroup From {0}.SupplierPurchaseLink Order by 1 ";
            DtT.Clear();

            string command6 = string.Format(sCmd, BraNam);
            MySqlParameter[] params4 =
                    {

                    };

            DtT = ObjMain.NewGetData_DT(command6, params4);

            //,Sum(Round(ifnull(K.GRAB,0),2)) as GRAB
            ///,case when PTE.PaymentType = 'GRAB' then EC.Amount end as GRAB
            if (DtT.Rows.Count != 0)
            {
                foreach (DataRow row in DtT.Rows)
                {
                    if (sSelQry == "")
                    {
                        sSelQry = ",format(Sum(Round(ifnull(K." + row["ItemGroup"] + ",0),2)),2) as " + row["ItemGroup"];
                        sTotQry = " Round(ifnull(K." + row["ItemGroup"] + ",0),2)+";
                        //sCondQry = ",case when Replace(TD.Group2,' ','')='" + row["ItemGroup"] + "' then TD.Amount end As " + row["ItemGroup"];
                        sCondQry = ",case when Replace(TD.Group2,' ','')='" + row["ItemGroup"] + "' then TD.Amount-TD.PSDiscAmount-TD.ManualDisc-TotalDisc end As " + row["ItemGroup"];       //nid added on 22-Jan-2025
                        sPLQry = " ,'0.00' as " +"PL_"+ row["ItemGroup"] + " ";
                    }
                    else
                    {
                        sSelQry = sSelQry + ",format(Sum(Round(ifnull(K." + row["ItemGroup"] + ",0),2)),2) as " + row["ItemGroup"];
                        sTotQry = sTotQry + " Round(ifnull(K." + row["ItemGroup"] + ",0),2)+";
                        //sCondQry = sCondQry + ",case when Replace(TD.Group2,' ','')='" + row["ItemGroup"] + "' then TD.Amount end As " + row["ItemGroup"];
                        sCondQry = sCondQry + ",case when Replace(TD.Group2,' ','')='" + row["ItemGroup"] + "' then TD.Amount-TD.PSDiscAmount-TD.ManualDisc-TotalDisc end As " + row["ItemGroup"];    //nid added on 22-Jan-2025
                        sPLQry = sPLQry + " ,'0.00' as " + "PL_" + row["ItemGroup"] + " ";
                    }
                }

                //for adding Format and Sum
                sTotQry = ",format(Sum(" + sTotQry + "),2) as TotalSales ";
                //for removing last + from TotalPurchase string
                sTotQry = sTotQry.Replace("+),", "),");

                sCmd = "Select  DATE_FORMAT(K.BusinessDate, '%a') as Week, DATE_FORMAT(K.BusinessDate, '%d-%m-%Y') as Date" + sSelQry + sTotQry + sPLQry + " ,'0.00' as PL From (" +
                " Select T.BusinessDate as BusinessDate" + sCondQry +
                " From {0}.Trans T Left Outer Join {0}.TransDetail TD On T.TransID=TD.TransID " +
                " Where T.BusinessDate Between @D1 and @D2 And T.TransStatus in (2) And TD.ItemStatus<>1  " +
                " ) K Group by 2";

                string command5 = string.Format(sCmd, BraNam);
                MySqlParameter[] param3 =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };
                Dt3 = ObjMain.NewGetData_DT(command5, param3);
            }

            //for renaming columns names for avoiding merging issue
            foreach (DataColumn column in Dt3.Columns)
            {
                string colname = column.ColumnName;
                 
                if (colname != "Week" && colname != "Date" && colname != "TotalSales" && colname != "PL" && !colname.Contains("PL_")  )
                {
                    Dt3.Columns[colname].ColumnName = "S_" + colname;
                }
            }


            //for Setting Primary key for Merge
            Dt1.PrimaryKey = new DataColumn[] { Dt1.Columns["Date"], Dt1.Columns["Week"] };
            Dt2.PrimaryKey = new DataColumn[] { Dt2.Columns["Date"], Dt2.Columns["Week"] };
            Dt3.PrimaryKey = new DataColumn[] { Dt3.Columns["Date"], Dt3.Columns["Week"] };

            //for Merging
            Dt1.Merge(Dt2);
            Dt1.Merge(Dt3);

            //for filling 0 values
            for (int i = 0; i < Dt1.Rows.Count; i++)
            {
                for (int j = 0; j < Dt1.Columns.Count; j++)
                {

                    if (string.IsNullOrEmpty(Dt1.Rows[i][j].ToString()))
                    {
                        Dt1.Rows[i][j] = "0.00";
                    }
                 }
            }


            //for calculating PL for individual Item group
            // List to store query results
            List<string> pList = new List<string>();
            List<string> sList = new List<string>();
            List<string> plList = new List<string>();

            sCmd = "Select Distinct(ItemGroup) as ItemGroup From {0}.SupplierPurchaseLink Order by 1 ";
            DtT.Clear();
            string command7 = string.Format(sCmd, BraNam);
            MySqlParameter[] params5 =
                    {

                    };

            DtT = ObjMain.NewGetData_DT(command7, params5);

            //Assiging values to List
            foreach (DataRow row in DtT.Rows)
            {
                pList.Add("P_"+ row["ItemGroup"]);
                sList.Add("S_" + row["ItemGroup"]);
                plList.Add("PL_" + row["ItemGroup"]);
            }

            // Convert the list to an array
            string[] pListArray = pList.ToArray();
            string[] sListArray = sList.ToArray();
            string[] plListArray = plList.ToArray();

            foreach (DataRow row in Dt1.Rows)
            {
                for (int i = 0; i <= pList.Count - 1; i++) 
                {
                    // Retrieve current values
                    double v1 = (double)Convert.ToDecimal(row[pListArray[i]]);
                    double v2 = (double)Convert.ToDecimal(row[sListArray[i]]);
                    
                    // Perform dynamic update logic
                    double result = (double)Convert.ToDecimal(v2 - v1);

                    // Assign the new value to the "Result" column
                    row[plListArray[i]] = string.Format("{0:n2}", result);
                }


            }

            // for calculating Total PL 
            foreach (DataRow row in Dt1.Rows)
            {
                // Retrieve current values
                double v1 = (double)Convert.ToDecimal(row["TotalPurchase"]);
                double v2 = (double)Convert.ToDecimal(row["TotalSales"]);

                // Perform dynamic update logic
                double result = (double)Convert.ToDecimal(v2 - v1);

                // Assign the new value to the "Result" column
                row["pl"] = string.Format("{0:n2}", result);
            }


            //Part 4 For getting Total fields
            DataRow totalsRow = Dt1.NewRow();
            DataColumn dc = new DataColumn("Sales", typeof(string));
            foreach (DataColumn col in Dt1.Columns)
            {
                Double colTotal = 0;
                foreach (DataRow row in col.Table.Rows)
                {
                    if ((col.ColumnName != "Week") && (col.ColumnName != "Date"))
                    {
                        object value = row[col];
                        //if (row[col] != "")
                        if (value != DBNull.Value)
                        {
                            if (Double.Parse(row[col].ToString()) != 0)
                            {
                                colTotal += Double.Parse(row[col].ToString());
                            }
                        }
                    }
                }
                if (col.ColumnName == "Week")
                {
                    totalsRow[col.ColumnName] = "Total";
                }
                else if (col.ColumnName == "Date")
                {
                    totalsRow[col.ColumnName] = "";

                }
                else
                {
                    totalsRow[col.ColumnName] = string.Format("{0:n2}", colTotal);
                }
            }
            Dt1.Rows.Add(totalsRow);


            // Clear existing data and columns
            GridView1.DataSource = null;
            GridView1.DataBind();
            GridView1.Columns.Clear();

            //for generating report
            GridView1.DataSource = Dt1;

            // Dynamically create columns
            foreach (DataColumn column in Dt1.Columns)
            {
                BoundField boundField = new BoundField
                {
                    DataField = column.ColumnName,
                    HeaderText = column.ColumnName
                };
                GridView1.Columns.Add(boundField);
            }

            GridView1.DataBind();
            GridView1.Rows[GridView1.Rows.Count - 1].BackColor = Color.Cornsilk   ;
            GridView1.Rows[GridView1.Rows.Count - 1].ForeColor = Color.DarkSlateBlue;
            GridView1.Rows[GridView1.Rows.Count - 1].Font.Bold = true;
        }



        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            if (cmbBranches.Visible == false)
            {
                GridView1.PageIndex = e.NewPageIndex;
                LoadItemGroupwisePL(Session["Branch_HQ"].ToString(), Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text), Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text));
            }
            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 )

                GridView1.PageIndex = e.NewPageIndex;
                LoadItemGroupwisePL(Cmb_BranchName, Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text), Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text));
            }
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            int lastIndex = GridView1.Rows.Count - 1;

            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                // Get the column index for the "Age" column
                int plColumnIndex = GetColumnIndexByName("PL");
                int tpColumnIndex= GetColumnIndexByName("TotalPurchase");
                int tsColumnIndex = GetColumnIndexByName("TotalSales");

                //int pl_ColumnIndex = GetColumnIndexByNameContains("PL_");

                if (plColumnIndex != -1)
                {
                    e.Row.Cells[plColumnIndex].ForeColor  = System.Drawing.Color.CornflowerBlue;
                    e.Row.Cells[plColumnIndex].Font.Bold = true;
                }
                if (tpColumnIndex!=-1)
                {
                    e.Row.Cells[tpColumnIndex].ForeColor = System.Drawing.Color.CornflowerBlue;
                    e.Row.Cells[tpColumnIndex].Font.Bold = true;
                }
                if (tsColumnIndex !=-1)
                {
                    e.Row.Cells[tsColumnIndex].ForeColor = System.Drawing.Color.CornflowerBlue;
                    e.Row.Cells[tsColumnIndex].Font.Bold = true;
                }

                //if (pl_ColumnIndex != -1)
                //{
                //    e.Row.Cells[tsColumnIndex].ForeColor = System.Drawing.Color.CornflowerBlue;
                //    e.Row.Cells[tsColumnIndex].Font.Bold = true;
                //}
                

            }
            else if (e.Row.RowIndex == lastIndex)
            {
            
                
            }

        }

        private int GetColumnIndexByName(string columnName)
        {
            // Loop through the columns collection to find the index of the specified column
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText.Equals(columnName, StringComparison.OrdinalIgnoreCase))
                {
                    return i;
                }

  
            }
            return -1; // Return -1 if the column was not found
        }

        //private int GetColumnIndexByNameContains(string columnName)
        //{
        //    // Loop through the columns collection to find the index of the specified column
        //    for (int i = 0; i < GridView1.Columns.Count; i++)
        //    {
        //        if (!GridView1.Columns[i].HeaderText.Contains(columnName))
        //        {
        //            return i;
        //        }


        //    }
        //    return -1; // Return -1 if the column was not found
        //}

        protected void btnExcel_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=GridViewExport.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);
            //string style = @"<style> td { mso-number-format:\@; } </style>";         // Old Line :  Text   
            string style = @"&lt;style> .text { mso-number-format:\@; } </style> ";   // Added For Number Formate Issue 
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();

        }


        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */
        }


    }
}