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 iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using System.Web.UI.DataVisualization.Charting;

namespace SUNWEB
{
    public partial class CondimentReport : 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();
        MYSQLCLOUD ObjMainCloud = new MYSQLCLOUD();
        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 DtLicense = new DataTable();
        DataTable DtSuperAdmin = new DataTable();
        DataTable DtMultiBranch = new DataTable();
        DataTable DtNormalUser = new DataTable();
        DataTable DTProductSale = new DataTable();
        DataTable DTActive = new DataTable();
        MySqlCommand Cmdv = new MySqlCommand();
        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;
        string PID = string.Empty;   // License Variables ..
        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_CondimentReport = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string Branchidval = string.Empty;
        string BraNam = "";
        string FDate = "";
        string TDate = "";
        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {
            PID = "14";                      // This is For Condiment Report need to add
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            if (!IsPostBack)
            {
                txtFromDt.Text = Curr_DateOnly;
                txtToDt.Text = Curr_DateOnly;
            }
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);

            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            //if (!IsPostBack)
            //{
            //    //LoadGrid();
            //    BraNam = "rasa1";
            //    FDate = txtFromDt.Text;
            //    TDate = txtToDt.Text;
            //    LoadGroupSalesMainQuery("rasa1", txtFromDt.Text, txtToDt.Text);
            //}
            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();
                    // LoadCondimentReport(cmbBranches.Text);
                }
                cd_BranchAlias = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
                BraNam = cd_BranchAlias;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                //LoadGroupSalesMainQuery(cd_BranchAlias, txtFromDt.Text, txtToDt.Text);
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName();
                    //LoadCondimentReport(cmbBranches.Text);
                    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 = "&nbsp;" + "-" + "&nbsp;" + Curr_BranchFullName + "&nbsp; ";
                cd_BranchAlias = Session["Branch_HQ"].ToString();
                Datapath = Session["DataPath"].ToString();
                PortalTypeId = Session["PortalTypeId"].ToString();
                PID = "14"; //  This is for ItemSale 
                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_Condiment"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx", false);
                }
                else
                {
                    //lblMsgTotalSelection.Text = "Group Sales Report  Between" + "&nbsp;&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.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;
                            //btnExcel.Enabled = true;
                            if (cmbBranches.Visible == false)
                            {
                                LoadCondimentReport(Session["Branch_HQ"].ToString());

                            }

                        }
                    }
                }
            }
            else
            {

            }



        }
        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 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 ActiveCall()
        {
            string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
            ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
            if (ActiveCallvalue == "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch7(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            }

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

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


            cd_BranchAlias = ActiveCallvalue;
            Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cd_BranchAlias);
            Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
            cmbBranches.ClearSelection();
            cmbBranches.Items.FindByText(Curr_BranchFullName).Selected = true;
            //cmbBranches.
            Branchidval = ObjUserBL.GetBranchIDbyName(ActiveCallvalue, ActiveCallvalue);
          


            if (Branchidval != "")
            {
                DTActive = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            }
            else
            {
                DTActive = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Session["Fistbranch"].ToString());

            }


            if (DTActive.Rows.Count > 0)
            {
                Page_Acess = DTActive.Rows[0]["Acess_status"].ToString();
                Read_Acess = DTActive.Rows[0]["Read_Access"].ToString();
                Write_Acess = DTActive.Rows[0]["Write_Access"].ToString();
                Isdowld_Acess = DTActive.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_CondimentReport = DtLicense.Rows[0]["CONDIMENTREPORT"].ToString();
                Session["L_Condiment"] = L_CondimentReport;
            }
            if (Session["L_Condiment"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx", false);
            }
            else
            {
                // XSS Attack Prevention 

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


                //btnExcel.Enabled = true;
                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;
                LoadCondimentReport(cd_BranchAlias);

                BraNam = cd_BranchAlias;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;

            }
        }

        public void LoadCondimentReport(string BranchAlias)
        {
            // XSS Attack Prevention 

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


            System.Threading.Thread.Sleep(1000);
            BraNam = BranchAlias;
            if (cmbBranches.Visible == true)
            {
                string sCmd = "Select T1.CondimentName,sum(T1.Qty) as QTY From (SELECT A.CondimentName,sum(B.Qty) as Qty FROM {0}.Transdetailcondiment A Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID " +
                        " Left Join {0}.Trans C on C.TransID=A.TransID Where C.TransStatus = 2 And B.ItemStatus <>1 And C.BusinessDate   Between  @StartDT  and  @EndDt  " +
                        " Group by A.CondimentName Order by Qty desc) T Left Join ( SELECT A.CondimentName,B.BarCode, B.Name,B.InventoryDesc,sum(B.Qty) as Qty  " +
                        " FROM {0}.Transdetailcondiment A Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID Left Join {0}.Trans C on C.TransID=A.TransID " +
                        " Where C.TransStatus = 2 And B.ItemStatus <>1 And C.BusinessDate " +
                        " Between  @StartDT and @EndDt  Group by A.CondimentName,B.BarCode Order by A.CondimentName) T1 on T.CondimentName=T1.CondimentName group by T1.Condimentname;";

                // Prevents SQL Injection : 01-09-21
                // Parameterized Approach
                string command = string.Format(sCmd, BranchAlias);
                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@StartDT",StartDT),
                     new MySqlParameter("@EndDt",EndDt)
                    };

                Dt1 = ObjMain.NewGetData_DT(command, parameters);
                grdCondiment.DataSource = Dt1;
                grdCondiment.DataBind();

            }

            else
            {


                string sCmd = "Select T1.CondimentName,sum(T1.Qty) as QTY From (SELECT A.CondimentName,sum(B.Qty) as Qty FROM {0}.Transdetailcondiment A Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID " +
                          " Left Join {0}.Trans C on C.TransID=A.TransID Where C.TransStatus = 2 And B.ItemStatus <>1 And C.BusinessDate   Between  @StartDT  and  @EndDt  " +
                          " Group by A.CondimentName Order by Qty desc) T Left Join ( SELECT A.CondimentName,B.BarCode, B.Name,B.InventoryDesc,sum(B.Qty) as Qty  " +
                          " FROM {0}.Transdetailcondiment A Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID Left Join {0}.Trans C on C.TransID=A.TransID " +
                          " Where C.TransStatus = 2 And B.ItemStatus <>1 And C.BusinessDate " +
                          " Between @StartDT  and  @EndDt  Group by A.CondimentName,B.BarCode Order by A.CondimentName) T1 on T.CondimentName=T1.CondimentName group by T1.Condimentname;";


                // Prevents SQL Injection 
                // Parameterized Approach
                string command = string.Format(sCmd, Microsoft.Security.Application.Encoder.HtmlEncode(Session["Branch_HQ"].ToString()));
                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@StartDT",StartDT),
                     new MySqlParameter("@EndDt",EndDt),
                  };

                Dt1 = ObjMain.NewGetData_DT(command, parameters);
                grdCondiment.DataSource = Dt1;
                grdCondiment.DataBind();

            }


        }

        protected void gv_RowDataBound(object sender, GridViewRowEventArgs 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 (e.Row.RowType == DataControlRowType.DataRow)
            {

                GridView gv = (GridView)e.Row.FindControl("GrdDetails");
                Dt2.Clear();
                string sCmd;



                if (cmbBranches.Visible == true)
                {

                    Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cd_BranchAlias);
                    string BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )


                    //string BranchAlias = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), cmbBranches.SelectedItem.Text);
                    sCmd = "Select CONCAT(T1.barcode,T1.InventoryDesc) as ItemName,T1.QTY From (SELECT A.CondimentName,sum(B.Qty) as Qty FROM {0}.Transdetailcondiment " +
                    " A Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID " +
                    " Left Join {0}.Trans C on C.TransID=A.TransID Where C.TransStatus = 2 And B.ItemStatus <>1 And C.BusinessDate   Between @StartDT and @EndDt  " +
                    " Group by A.CondimentName Order by Qty desc) T Left Join ( SELECT A.CondimentName,B.BarCode, B.Name,B.InventoryDesc,sum(B.Qty) as Qty   " +
                    "  FROM {0}.Transdetailcondiment A Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID Left Join {0}.Trans C on C.TransID=A.TransID  " +
                    " Where C.TransStatus = 2 And B.ItemStatus <>1 And C.BusinessDate  " +
                    " Between  @StartDT  and  @EndDt   Group by A.CondimentName,B.BarCode Order by A.CondimentName) T1 on T.CondimentName=T1.CondimentName " +
                    " where T1.Condimentname = '" + ((DataRowView)e.Row.DataItem)["Condimentname"].ToString() + "';";

                    // Prevent SQL Injection  : 2021-09-21
                    // Parameterized Approach   

                    string command = string.Format(sCmd, BranchAlias);
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@StartDT",StartDT),
                     new MySqlParameter("@EndDt",EndDt),
                    };
                    Dt2 = ObjMain.NewGetData_DT(command, parameters);
                }
                else
                {

                    sCmd = "Select CONCAT(T1.barcode,T1.InventoryDesc) as ItemName,T1.QTY From (SELECT A.CondimentName,sum(B.Qty) as Qty FROM {0}.Transdetailcondiment " +
                          " A Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID " +
                          " Left Join {0}.Trans C on C.TransID=A.TransID Where C.TransStatus = 2 And B.ItemStatus <>1 And C.BusinessDate   Between  @StartDT  and  @EndDt  " +
                          " Group by A.CondimentName Order by Qty desc) T Left Join ( SELECT A.CondimentName,B.BarCode, B.Name,B.InventoryDesc,sum(B.Qty) as Qty   " +
                          "  FROM {0}.Transdetailcondiment A Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID Left Join {0}.Trans C on C.TransID=A.TransID  " +
                          " Where C.TransStatus = 2 And B.ItemStatus <>1 And C.BusinessDate  " +
                          " Between  @StartDT  and  @EndDt   Group by A.CondimentName,B.BarCode Order by A.CondimentName) T1 on T.CondimentName=T1.CondimentName " +
                          " where T1.Condimentname = '" + ((DataRowView)e.Row.DataItem)["Condimentname"].ToString() + "';";

                    // Prevent SQL Injection  : 2021-09-21
                    // Parameterized Approach   

                    string command = string.Format(sCmd, Microsoft.Security.Application.Encoder.HtmlEncode(Session["Branch_HQ"].ToString()));

                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@StartDT",StartDT),
                     new MySqlParameter("@EndDt",EndDt),
                    };
                    Dt2 = ObjMain.NewGetData_DT(command, parameters);
                }
                gv.DataSource = Dt2;
                gv.DataBind();
            }
        }

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


            if (cmbBranches.Visible == true)
            {
                //lblMsgTotalSelection.Text = "Search Result - Condiment 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;");
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                LoadCondimentReport(cd_BranchAlias);
            }
            else
            {
                //lblMsgTotalSelection.Text = "Search Result - Condiment 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;");
                LoadCondimentReport(Session["Branch_HQ"].ToString());

            }


        }

        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("CondimentReport.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_CondimentReport = DtLicense.Rows[0]["CONDIMENTREPORT"].ToString();
                    Session["L_Condiment"] = L_CondimentReport;
                }
                if (Session["L_Condiment"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    Response.Redirect("DeniedPage.aspx", false);
                }
                else
                {
                    //btnExcel.Enabled = true;

                    //lblMsgTotalSelection.Text = "Search Result - Condiment 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;
                    LoadCondimentReport(cd_BranchAlias);

                }
            }
            UPDATEBRANCH(Cmb_BranchName);
        }
        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", false);
            }
        }


        protected void btnExcel_Click(object sender, EventArgs e)
        {

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


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

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

            }
            //nid added excel export on 03-Mar-2020

            Dt2.Clear();
            string sCmd;
            //sCmd = "Select T.TransID as TransID,T.TransNo as TransNo,Date(T.TransDate) as TransDate,TDC.ItemID as ItemID,TD.Barcode as Barcode,TD.Name as ItemName, TD.InventoryDesc as Description," +
            //    " TDC.ContinmentID as CondimentID,TDC.CondimentName as CondimentName, TDC.CondimentDesc as CondimentDesc, Sum(TD.Qty) as Qty" +
            //    " From " + BraNam + ".Trans T Left Outer Join " + BraNam + ".Transdetail TD on T.TransID=TD.TransID " +
            //    " Left Outer Join " + BraNam + ".TransDetailCondiment TDC on T.TransID=TDC.TransID" +
            //    " Where T.TransStatus in (2,3,4) And TD.ItemStatus=0 And TD.TransID=TDC.TransID And TD.ItemID=TDC.ItemID  " +
            //    " And T.TransDate Between '" + txtFromDt.Text + "' and '" + txtToDt.Text + "' " +
            //    " Group by T.TransID,T.TransNo,TD.Barcode,TD.Name,TD.InventoryDesc,TDC.ItemID,TDC.ContinmentID,TDC.CondimentName,TDC.CondimentDesc " +
            //    " Order by TDC.CondimentName";
            //sCmd = "Select A.TransID,TransNo,Date(TransDate)as TransDate,B.ItemID,B.Barcode,B.Name,B.InventoryDesc,C.ContinmentID,C.CondimentName,C.CondimentDesc,B.QTY " +
            //        " from " + BraNam + ".Trans A  " +
            //        " join " + BraNam + ".Transdetail B on A.TransID = B.TransID  " +
            //        " join " + BraNam + ".TransDetailCondiment C on " +
            //        " B.TransID = C.TransID  " +
            //        " where date(A.TransDate) Between '" + txtFromDt.Text + "' and '" + txtToDt.Text + "' " +
            //        " group by A.TransID,C.ItemID,C.ContinmentID " +
            //        " order by Condimentname;";

            sCmd = "Select t1.transID,t1.TransNo,t1.Transdate,t1.itemID,T1.Barcode,T1.Name,T1.InventoryDesc,T1.Condimentname,T1.QTY From ( " +
                    " SELECT C.TransID,C.TransNo,C.TransDate,B.itemID,B.BarCode, B.Name,B.InventoryDesc,A.CondimentName,sum(B.Qty) as Qty " +
                    " FROM {0}.Transdetailcondiment  A " +
                    " Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID " +
                    " Left Join {0}.Trans C on C.TransID=A.TransID  " +
                    " Where C.TransStatus = 2 And B.ItemStatus <>1 And date(C.BusinessDate)   " +
                    " Between  @StartDT  and  @EndDt   Group by A.CondimentName Order by A.CondimentName ) " +
                    " T Left Join(  " +
                    " SELECT C.TransID,C.TransNo,C.TransDate,B.itemID,B.BarCode, B.Name,B.InventoryDesc,A.CondimentName,sum(B.Qty) as Qty     FROM {0}.Transdetailcondiment A  " +
                    " Left Join {0}.TransDetail B on A.TransID=B.TransID and A.ItemID=B.ItemID Left Join  " +
                    " {0}.Trans C on C.TransID=A.TransID   Where C.TransStatus = 2 And B.ItemStatus <>1 And date(C.BusinessDate)   " +
                    "  Between  @StartDT and @EndDt   Group by A.CondimentName,B.BarCode Order by A.CondimentName)  " +
                    " T1 on T.CondimentName=T1.CondimentName;";

            string command = string.Format(sCmd, BraNam);
            MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@StartDT",StartDT),
                     new MySqlParameter("@EndDt",EndDt),
                    };

            Dt2 = ObjMain.NewGetData_DT(command, parameters);


            //Create a dummy GridView
            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = Dt1;
            GridView1.DataBind();
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=Condiment.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.DataSource = Dt2;
            GridView1.DataBind();

            GridView1.RenderControl(hw);
            //style to format numbers to string
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            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. */
        }


    }
}