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 MonthlySale : 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");
        string First_DateYear = System.DateTime.Now.ToString("yyyy-01-01");    // new DateTime(Now.Year, 1, 1);
        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 DTSession = new DataTable();
        DataTable DTExcel = 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_RetailDashboard = string.Empty;
        string L_SummarySales = string.Empty;
        string L_ItemSales = string.Empty;
        string L_TaxAnalysis = string.Empty;
        string L_TopSale = string.Empty;
        string L_MothlySales = string.Empty;
        string L_HourlySales = string.Empty;
        string L_GroupSale = 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 = "";
        double totalJan = 0, totalFeb = 0, totalMar = 0, totalApr = 0, totalMay = 0, totalJun = 0, totalJul = 0, totalAug = 0, totalSep = 0, totalOct = 0, totalNov = 0, totalDec = 0;
        int totalWJan = 0, totalWFeb = 0, totalWMar = 0, totalWApr = 0, totalWMay = 0, totalWJun = 0, totalWJul = 0, totalWAug = 0, totalWSep = 0, totalWOct = 0, totalWNov = 0, totalWDec = 0;
        double avgJan = 0, avgFeb = 0, avgMar = 0, avgApr = 0, avgMay = 0, avgJun = 0, avgJul = 0, avgAug = 0, avgSep = 0, avgOct = 0, avgNov = 0, avgDec = 0;
        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string BranchName = string.Empty;
        string ISHQ = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {
            PID = "6";                     // This is For MonthlySale
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            if (!IsPostBack)
            {
                txtFromDt.Text = First_DateYear; // 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();
                }
                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 )
                cd_BranchAlias = Cmb_BranchName;
                BraNam = cd_BranchAlias;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadMonthlySalesMainQuery(cd_BranchAlias, Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text), Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.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
                lblBranchAliasName.ForeColor = System.Drawing.Color.DodgerBlue;
                Curr_BranchFullName = ObjSel.GetBranchFullName(HQ, Session["Branch_HQ"].ToString());
                lblBranchAliasName.Text = "&nbsp;" + "-" + "&nbsp;" + Curr_BranchFullName + "&nbsp; ";
                //lblGreeting.Text = "Welcome" + "&nbsp;&nbsp;" + Session["UserName"].ToString() + "!";
                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_MothlySales"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    //lblMsgTotalSelection.Text = "Search Result - Monthly Sales Report  Between" + "&nbsp;&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                    lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp; to &nbsp;&nbsp;" + txtToDt.Text;
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                    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)
                            {
                                LoadMonthlySalesMainQuery(Session["Branch_HQ"].ToString(), txtFromDt.Text, txtToDt.Text);
                            }

                        }
                    }
                }
            }
            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.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_MothlySales = DtLicense.Rows[0]["MONTHLYSALE"].ToString();
                Session["L_MothlySales"] = L_MothlySales;

            }

            if (Session["L_MothlySales"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {

                #region Write(Button click ) & download rights ...

                if (Session["Write_Acess"].ToString() == "N")
                {
                    btnGenerate.Enabled = false;
                }
                if (Session["Isdowld_Acess"].ToString() == "N")
                {
                    btnExcel.Enabled = false;
                    btnMonthlycondiment.Enabled = false;
                    btnProductExcel.Enabled = false;

                }
                #endregion



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

                //btnExcel.Enabled = true;
                //lblMsgTotalSelection.Text = "Search Result - Monthly Sales Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;&nbsp;" + StartDT.Trim() + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt.Trim();
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                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 )
                cd_BranchAlias = Cmb_BranchName;
                BraNam = cd_BranchAlias;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadMonthlySalesMainQuery(cd_BranchAlias, StartDT.Trim(), EndDt.Trim());
                System.Threading.Thread.Sleep(2000);
            }
        }
        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Write("GroupSale.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;
                }
                // Get the BranchAlias Name 
                BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                cd_BranchAlias = 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='" + cd_BranchAlias + "';");
                RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
                DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);
                if (DtLicense.Rows.Count > 0)
                {
                    L_MothlySales = DtLicense.Rows[0]["MONTHLYSALE"].ToString();
                    Session["L_MothlySales"] = L_MothlySales;

                }
                if (Session["L_MothlySales"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {
                    #region Write(Button click ) & download rights ...

                    if (Session["Write_Acess"].ToString() == "N")
                    {
                        btnGenerate.Enabled = false;
                    }
                    if (Session["Isdowld_Acess"].ToString() == "N")
                    {
                        btnExcel.Enabled = false;
                        btnMonthlycondiment.Enabled = false;
                        btnProductExcel.Enabled = false;

                    }
                    #endregion


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

                    //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 - from " + "&nbsp;&nbsp;&nbsp;" + StartDT.Trim() + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt.Trim();
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px;");
                    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 )
                    cd_BranchAlias = Cmb_BranchName;
                    BraNam = cd_BranchAlias;
                    FDate = txtFromDt.Text;
                    TDate = txtToDt.Text;
                    LoadMonthlySalesMainQuery(cd_BranchAlias, StartDT.Trim(), EndDt.Trim());
                }
            }
            UPDATEBRANCH(cd_BranchAlias);

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

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


            //lblMsgTotalSelection.Text = "Search Result - Monthly Sales Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
            lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;&nbsp;" + StartDT.Trim() + "&nbsp;&nbsp; to &nbsp;&nbsp;" + EndDt.Trim();
            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 )
            BraNam = cd_BranchAlias;
            FDate = txtFromDt.Text;
            TDate = txtToDt.Text;
            LoadMonthlySalesMainQuery(cd_BranchAlias, StartDT.Trim(), EndDt.Trim());
        }
        protected void LoadMonthlySalesMainQuery(string BranchAlias, string D1, string D2)
        {

            // Preventing SQL Injection : 30-09-21 
            // Parameterized Approach 

            System.Threading.Thread.Sleep(1000);
            string sCmd;
            sCmd = "Select TransDay,Format(Sum(Jan),2) as Jan ,Format(Sum(Feb),2) as Feb ,Format(Sum(Mar),2) as Mar,Format(Sum(Apr),2) as Apr,Format(Sum(May),2) as May,Format(Sum(Jun),2) as Jun,Format(Sum(Jul),2) as Jul,Format(Sum(Aug),2) as Aug,Format(Sum(Sep),2) as Sep,Format(Sum(Oct),2) as Oct,Format(Sum(Nov),2) as Nov,Format(Sum(Dece),2) as Dece From (" +
            " Select Day(Tp.BusinessDate) as TransDay, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jan, 0.00 as Feb,0.00 as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID " +
            " Where Tp.BusinessDate between @D1  and  @D2 " +
            " And T.TransStatus in (2,3,4) " +
            " And Month(Tp.BusinessDate)='01' " +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Feb,0.00 as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID " +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='02'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2   " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='03'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='04'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='05'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='06'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='07'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='08'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='09'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Oct,0.00 as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='10'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Nov,0.00 as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='11'" +
            " Group by Day(Tp.BusinessDate)" +
            " Union All" +
            " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Dece" +
            " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
            " Where Tp.BusinessDate between @D1 and @D2 " +
            " And T.TransStatus in (2,3,4)" +
            " And Month(Tp.BusinessDate)='12'" +
            " Group by Day(Tp.BusinessDate)" +
            " ) as K" +
            " Group by K.TransDay Order by 1";

            string newquery1 = string.Format(sCmd, BranchAlias);

            MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                    };

            Dt1 = ObjMain.NewGetData_DT(newquery1, parameters);


            GridView1.DataSource = Dt1;
            //gridview totals
            //int totalQty = 0;
            foreach (DataRow dr in Dt1.Rows)
            {
                totalJan += Convert.ToDouble(dr["Jan"]);
                if (Convert.ToDouble(dr["Jan"]) > 0)
                {
                    totalWJan += 1;
                };
                totalFeb += Convert.ToDouble(dr["Feb"]);
                if (Convert.ToDouble(dr["Feb"]) > 0)
                {
                    totalWFeb += 1;
                };
                totalMar += Convert.ToDouble(dr["Mar"]);
                if (Convert.ToDouble(dr["Mar"]) > 0)
                {
                    totalWMar += 1;
                };
                totalApr += Convert.ToDouble(dr["Apr"]);
                if (Convert.ToDouble(dr["Apr"]) > 0)
                {
                    totalWApr += 1;
                };
                totalMay += Convert.ToDouble(dr["May"]);
                if (Convert.ToDouble(dr["May"]) > 0)
                {
                    totalWMay += 1;
                };
                totalJun += Convert.ToDouble(dr["Jun"]);
                if (Convert.ToDouble(dr["Jun"]) > 0)
                {
                    totalWJun += 1;
                };
                totalJul += Convert.ToDouble(dr["Jul"]);
                if (Convert.ToDouble(dr["Jul"]) > 0)
                {
                    totalWJul += 1;
                };
                totalAug += Convert.ToDouble(dr["Aug"]);
                if (Convert.ToDouble(dr["Aug"]) > 0)
                {
                    totalWAug += 1;
                };
                totalSep += Convert.ToDouble(dr["Sep"]);
                if (Convert.ToDouble(dr["Sep"]) > 0)
                {
                    totalWSep += 1;
                };
                totalOct += Convert.ToDouble(dr["Oct"]);
                if (Convert.ToDouble(dr["Oct"]) > 0)
                {
                    totalWOct += 1;
                };
                totalNov += Convert.ToDouble(dr["Nov"]);
                if (Convert.ToDouble(dr["Nov"]) > 0)
                {
                    totalWNov += 1;
                };
                totalDec += Convert.ToDouble(dr["Dece"]);
                if (Convert.ToDouble(dr["Dece"]) > 0)
                {
                    totalWDec += 1;
                };

            }
            //GridView1.Columns[1].FooterText = totalJan.ToString("#,##0.00");
            //GridView1.Columns[2].FooterText = totalFeb.ToString("#,##0.00");
            //GridView1.Columns[3].FooterText = totalMar.ToString("#,##0.00");
            //GridView1.Columns[4].FooterText = totalApr.ToString("#,##0.00");
            //GridView1.Columns[5].FooterText = totalMay.ToString("#,##0.00");
            //GridView1.Columns[6].FooterText = totalJun.ToString("#,##0.00");
            //GridView1.Columns[7].FooterText = totalJul.ToString("#,##0.00");
            //GridView1.Columns[8].FooterText = totalAug.ToString("#,##0.00");
            //GridView1.Columns[9].FooterText = totalSep.ToString("#,##0.00");
            //GridView1.Columns[10].FooterText = totalOct.ToString("#,##0.00");
            //GridView1.Columns[11].FooterText = totalNov.ToString("#,##0.00");
            //GridView1.Columns[12].FooterText = totalDec.ToString("#,##0.00");
            GridView1.DataBind();
        }

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

        //for Monthly Sale Excel
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            // XSS Attack Prevention

            // Prevented SQL Injection : 30-09-21
            // Parameterized Approach 

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


            if (cmbBranches.SelectedIndex != 0)
            {
                if (Session["Isdowld_Acess"].ToString() == "N")
                {
                    Page page = HttpContext.Current.Handler as Page;
                    string message = "No Access To download";
                    System.Web.UI.ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + message + "');", true);
                }
                else
                {
                  
                    if (cmbBranches.Visible == false)
                    {
                        string strquery = "Select TransDay,Format(Sum(Jan),2) as Jan ,Format(Sum(Feb),2) as Feb ,Format(Sum(Mar),2) as Mar,Format(Sum(Apr),2) as Apr,Format(Sum(May),2) as May,Format(Sum(Jun),2) as Jun,Format(Sum(Jul),2) as Jul,Format(Sum(Aug),2) as Aug,Format(Sum(Sep),2) as Sep,Format(Sum(Oct),2) as Oct,Format(Sum(Nov),2) as Nov,Format(Sum(Dece),2) as Dece From (" +
                       " Select Day(Tp.BusinessDate) as TransDay, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jan, 0.00 as Feb,0.00 as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID " +
                       " Where Tp.BusinessDate between  @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4) " +
                       " And Month(Tp.BusinessDate)='01' " +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Feb,0.00 as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID " +
                       " Where Tp.BusinessDate between @StartDT and @EndDt  " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='02'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between  @StartDT and  @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='03'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='04'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='05'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='06'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='07'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='08'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='09'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='10'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='11'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='12'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " ) as K" +
                       " Group by K.TransDay Order by 1";


                        string newquery = string.Format(strquery, Session["Branch_HQ"].ToString());
                        DataTable Dt1 = new DataTable();
                        MySqlParameter[] parameters =
                             {
                                  new MySqlParameter("@StartDT", StartDT),
                                  new MySqlParameter("@EndDt", EndDt),
                             };

                        Dt1 = ObjMain.NewGetData_DT(newquery, parameters);
                        
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition", "attachment;filename=MonthlySale.xls");
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.ms-excel";
                        using (StringWriter sw = new StringWriter())
                        {
                            HtmlTextWriter hw = new HtmlTextWriter(sw);

                            //To Export all pages
                            GridView1.AllowPaging = false;

                            LoadMonthlySalesMainQuery(Session["Branch_HQ"].ToString(), Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text), Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text));
                            GridView1.HeaderRow.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffffff");
                            foreach (TableCell cell in GridView1.HeaderRow.Cells)
                            {
                                cell.BackColor = GridView1.HeaderStyle.BackColor;
                            }
                            foreach (GridViewRow row in GridView1.Rows)
                            {
                                row.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffffff");
                                foreach (TableCell cell in row.Cells)
                                {
                                    if (row.RowIndex % 2 == 0)
                                    {
                                        cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                                    }
                                    else
                                    {
                                        cell.BackColor = GridView1.RowStyle.BackColor;
                                    }
                                    cell.CssClass = "textmode";
                                }
                            }

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



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


                        string strquery = "Select TransDay,Format(Sum(Jan),2) as Jan ,Format(Sum(Feb),2) as Feb ,Format(Sum(Mar),2) as Mar,Format(Sum(Apr),2) as Apr,Format(Sum(May),2) as May,Format(Sum(Jun),2) as Jun,Format(Sum(Jul),2) as Jul,Format(Sum(Aug),2) as Aug,Format(Sum(Sep),2) as Sep,Format(Sum(Oct),2) as Oct,Format(Sum(Nov),2) as Nov,Format(Sum(Dece),2) as Dece From (" +
                       " Select Day(Tp.BusinessDate) as TransDay, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jan, 0.00 as Feb,0.00 as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID " +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4) " +
                       " And Month(Tp.BusinessDate)='01' " +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Feb,0.00 as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID " +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='02'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Mar,0.00 as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='03'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt  " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='04'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='05'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='06'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='07'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='08'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Sep, 0.00 as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4) " +
                       " And Month(Tp.BusinessDate)='09'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Oct,0.00 as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='10'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Nov,0.00 as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='11'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " Union All" +
                       " Select Day(Tp.BusinessDate) as TransDay, 0.00 as Jan, 0.00 as Feb, 0.00 Mar,0.00 Apr,0.00 as May,0.00 as Jun,0.00 as Jul,0.00 as Aug,0.00 as Sep, 0.00 as Oct,0.00 as Nov,IF(Tp.PaymentAmount Is Null,0,Sum(Tp.PaymentAmount)) as Dece" +
                       " From {0}.Transpayment Tp Left Join {0}.Trans T on Tp.TransID=T.TransID" +
                       " Where Tp.BusinessDate between  @StartDT and @EndDt " +
                       " And T.TransStatus in (2,3,4)" +
                       " And Month(Tp.BusinessDate)='12'" +
                       " Group by Day(Tp.BusinessDate)" +
                       " ) as K" +
                       " Group by K.TransDay";

                        string newquery1 = string.Format(strquery, Cmb_BranchName);
                        DataTable Dt1 = new DataTable();
                        MySqlParameter[] parameters =
                             {
                                  new MySqlParameter("@StartDT", StartDT),
                                  new MySqlParameter("@EndDt", EndDt),
                             };
                        Dt1 = ObjMain.NewGetData_DT(newquery1,parameters);
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition", "attachment;filename=MonthlySale.xls");
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.ms-excel";
                        using (StringWriter sw = new StringWriter())
                        {
                            HtmlTextWriter hw = new HtmlTextWriter(sw);

                            //To Export all pages
                            GridView1.AllowPaging = false;

                            LoadMonthlySalesMainQuery(Cmb_BranchName, txtFromDt.Text, txtToDt.Text);
                            GridView1.HeaderRow.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffffff");
                            foreach (TableCell cell in GridView1.HeaderRow.Cells)
                            {
                                cell.BackColor = GridView1.HeaderStyle.BackColor;
                            }
                            foreach (GridViewRow row in GridView1.Rows)
                            {
                                row.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffffff");
                                foreach (TableCell cell in row.Cells)
                                {
                                    if (row.RowIndex % 2 == 0)
                                    {
                                        cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                                    }
                                    else
                                    {
                                        cell.BackColor = GridView1.RowStyle.BackColor;
                                    }
                                    cell.CssClass = "textmode";
                                }
                            }

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

                    }
                }
            }

        }

        //nid added for Monthly Product Sale excel report add on 14-Jun-2021
        protected void btnProductExcel_Click(object sender, EventArgs e)
        {

            // XSS Attack Prevention

            // Prevented SQL Injection : 27-08-21
            // Parameterized Approach 


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

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

                        string strquery = "Select CategoryName as CategoryName,InventoryDesc as InventoryDesc," +
                         " Sum(Jan_Qty) as Jan_Qty,Format(Sum(Jan_Amt),2) as Jan_Amt, Sum(Feb_Qty) as Feb_Qty,Format(Sum(Feb_Amt),2) as Feb_Amt, Sum(Mar_Qty) as Mar_Qty,Format(Sum(Mar_Amt),2) as Mar_Amt, Sum(Apr_Qty) as Apr_Qty,Format(Sum(Apr_Amt),2) as Apr_Amt, Sum(May_Qty) as May_Qty,Format(Sum(May_Amt),2) as May_Amt, Sum(Jun_Qty) as Jun_Qty,Format(Sum(Jun_Amt),2) as Jun_Amt," +
                         " Sum(Jul_Qty) as Jul_Qty,Format(Sum(Jul_Amt),2) as Jul_Amt, Sum(Aug_Qty) as Aug_Qty,Format(Sum(Aug_Amt),2) as Aug_Amt, Sum(Sep_Qty) as Sep_Qty,Format(Sum(Sep_Amt),2) as Sep_Amt, Sum(Oct_Qty) as Oct_Qty,Format(Sum(Oct_Amt),2) as Oct_Amt, Sum(Nov_Qty) as Nov_Qty,Format(Sum(Nov_Amt),2) as Nov_Amt ,Sum(Dec_Qty) as Dec_Qty,Format(Sum(Dec_Amt),2) as Dec_Amt From (" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Jan_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between  @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='01'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL " +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Feb_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='02'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Mar_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='03'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Apr_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='04'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as May_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='05'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Jun_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber " +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0 " +
                         " And Month(T.BusinessDate)='06'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Jun_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Jun_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='07'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Aug_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='08'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Sep_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='09'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Oct_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt  " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='10'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Nov_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='11'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " UNION ALL" +
                         " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                         " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                         " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Dec_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Dec_Amt" +
                         " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                         " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                         " Where T.BusinessDate between @StartDT and  @EndDt " +
                         " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                         " And Month(T.BusinessDate)='12'" +
                         " Group by IC.CategoryName,TD.InventoryDesc" +
                         " ) As K" +
                         " Group by K.CategoryName, K.InventoryDesc" +
                         " Order by 1,2;";


                        string newquery2 = string.Format(strquery, Session["Branch_HQ"].ToString());


                        MySqlParameter[] parameters =
                           {
                                  new MySqlParameter("@StartDT", StartDT),
                                  new MySqlParameter("@EndDt", EndDt),
                            };
                        Dt1 = ObjMain.NewGetData_DT(newquery2, parameters);


                     


                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition", "attachment;filename=MonthlyProductExport.xls");
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.ms-excel";
                        using (StringWriter sw = new StringWriter())
                        {
                            HtmlTextWriter hw = new HtmlTextWriter(sw);

                            //To Export all pages
                            GridView1.AllowPaging = false;

                            LoadMonthlySalesMainQuery(Session["Branch_HQ"].ToString(), Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text), Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text));
                            GridView1.HeaderRow.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffffff");
                            foreach (TableCell cell in GridView1.HeaderRow.Cells)
                            {
                                cell.BackColor = GridView1.HeaderStyle.BackColor;
                            }
                            foreach (GridViewRow row in GridView1.Rows)
                            {
                                row.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffffff");
                                foreach (TableCell cell in row.Cells)
                                {
                                    if (row.RowIndex % 2 == 0)
                                    {
                                        cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                                    }
                                    else
                                    {
                                        cell.BackColor = GridView1.RowStyle.BackColor;
                                    }
                                    cell.CssClass = "textmode";
                                }
                            }

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



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


                        string strquery = "Select CategoryName as CategoryName,InventoryDesc as InventoryDesc," +
                        " Sum(Jan_Qty) as Jan_Qty,Format(Sum(Jan_Amt),2) as Jan_Amt, Sum(Feb_Qty) as Feb_Qty,Format(Sum(Feb_Amt),2) as Feb_Amt, Sum(Mar_Qty) as Mar_Qty,Format(Sum(Mar_Amt),2) as Mar_Amt, Sum(Apr_Qty) as Apr_Qty,Format(Sum(Apr_Amt),2) as Apr_Amt, Sum(May_Qty) as May_Qty,Format(Sum(May_Amt),2) as May_Amt, Sum(Jun_Qty) as Jun_Qty,Format(Sum(Jun_Amt),2) as Jun_Amt," +
                        " Sum(Jul_Qty) as Jul_Qty,Format(Sum(Jul_Amt),2) as Jul_Amt, Sum(Aug_Qty) as Aug_Qty,Format(Sum(Aug_Amt),2) as Aug_Amt, Sum(Sep_Qty) as Sep_Qty,Format(Sum(Sep_Amt),2) as Sep_Amt, Sum(Oct_Qty) as Oct_Qty,Format(Sum(Oct_Amt),2) as Oct_Amt, Sum(Nov_Qty) as Nov_Qty,Format(Sum(Nov_Amt),2) as Nov_Amt ,Sum(Dec_Qty) as Dec_Qty,Format(Sum(Dec_Amt),2) as Dec_Amt From (" +
                        // " -- Jan" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Jan_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between  @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='01'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Feb" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Feb_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='02'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Mar" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Mar_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between  @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='03'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Apr" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Apr_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt  " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='04'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- May" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as May_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='05'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Jun" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Jun_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='06'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Jul" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Jun_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Jun_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='07'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Aug" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Aug_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='08'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Sep" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Sep_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='09'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Oct" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Oct_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='10'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Nov" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Nov_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Nov_Amt,0 as Dec_Qty,0 as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='11'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        //" -- Dec" +
                        " UNION ALL" +
                        " Select IC.CategoryName as CategoryName, TD.InventoryDesc as InventoryDesc," +
                        " 0 as Jan_Qty,0 as Jan_Amt,0 as Feb_Qty,0 as Feb_Amt,0 as Mar_Qty,0 as Mar_Amt,0 as Apr_Qty,0 as Apr_Amt,0 as May_Qty,0 as May_Amt,0 as Jun_Qty,0 as Jun_Amt," +
                        " 0 as Jul_Qty,0 as Jul_Amt,0 as Aug_Qty,0 as Aug_Amt,0 as Sep_Qty,0 as Sep_Amt,0 as Oct_Qty,0 as Oct_Amt,0 as Nov_Qty,0 as Nov_Amt,IF(TD.Qty Is Null,0,Sum(TD.Qty)) as Dec_Qty, IF(TD.Amount IS Null,0,Sum(TD.Amount)) as Dec_Amt" +
                        " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID=T.TransID" +
                        " Left Outer Join {0}.InventoryCategories IC on TD.CategoryNumber=IC.CategoryNumber" +
                        " Where T.BusinessDate between @StartDT and   @EndDt " +
                        " And T.TransStatus in (2,3,4) And TD.ItemStatus=0" +
                        " And Month(T.BusinessDate)='12'" +
                        " Group by IC.CategoryName,TD.InventoryDesc" +
                        " ) As K" +
                        " Group by K.CategoryName, K.InventoryDesc" +
                        " Order by 1,2;";

                        string newquery3 = string.Format(strquery, Cmb_BranchName);
                        MySqlParameter[] parameters =
                           {
                                  new MySqlParameter("@StartDT", StartDT),
                                  new MySqlParameter("@EndDt", EndDt),
                             };
                        Dt1 = ObjMain.NewGetData_DT(newquery3, parameters);

                        Session["MonthlyProduct"] = Dt1;

                        //Response.Clear();
                        //Response.Buffer = true;
                        //Response.AddHeader("content-disposition", "attachment;filename=MonthlyProductExport.xls");
                        //Response.Charset = "";
                        //Response.ContentType = "application/vnd.ms-excel";
                        //using (StringWriter sw = new StringWriter())
                        //{
                        //    HtmlTextWriter hw = new HtmlTextWriter(sw);

                        //    //To Export all pages
                        //    GridView1.AllowPaging = false;

                        //    LoadMonthlySalesMainQuery(Cmb_BranchName, txtFromDt.Text, txtToDt.Text);
                        //    GridView1.HeaderRow.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffffff");
                        //    foreach (TableCell cell in GridView1.HeaderRow.Cells)
                        //    {
                        //        cell.BackColor = GridView1.HeaderStyle.BackColor;
                        //    }
                        //    foreach (GridViewRow row in GridView1.Rows)
                        //    {
                        //        row.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffffff");
                        //        foreach (TableCell cell in row.Cells)
                        //        {
                        //            if (row.RowIndex % 2 == 0)
                        //            {
                        //                cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                        //            }
                        //            else
                        //            {
                        //                cell.BackColor = GridView1.RowStyle.BackColor;
                        //            }
                        //            cell.CssClass = "textmode";
                        //        }
                        //    }

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

                        DTSession = (DataTable)Session["MonthlyProduct"];
                        //Create a dummy GridView
                        GridView GridView2 = new GridView();
                        GridView2.AllowPaging = false;
                        GridView2.DataSource = DTSession;
                        GridView2.DataBind();



                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition",
                         "attachment;filename=MonthlyProduct.xls");
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.ms-excel";
                        StringWriter sw = new StringWriter();
                        HtmlTextWriter hw = new HtmlTextWriter(sw);
                        for (int i = 0; i < GridView2.Rows.Count; i++)
                        {
                            //Apply text style to each Row
                            GridView2.Rows[i].Attributes.Add("class", "textmode");
                        }



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

                }
            }
        }


        protected void grd_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            {
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
                    Label lblCellJan = ((Label)e.Row.FindControl("lblJan"));
                    TableCell CellJan = e.Row.Cells[1];
                    if (lblCellJan.Text == "0.00")
                    {
                        CellJan.ForeColor = System.Drawing.Color.Red;
                        CellJan.Font.Bold = true;
                    }
                    Label lblCellFeb = ((Label)e.Row.FindControl("lblFeb"));
                    TableCell CellFeb = e.Row.Cells[2];
                    if (lblCellFeb.Text == "0.00")
                    {
                        CellFeb.ForeColor = System.Drawing.Color.Red;
                        CellFeb.Font.Bold = true;
                    }
                    Label lblCellMar = ((Label)e.Row.FindControl("lblMar"));
                    TableCell CellMar = e.Row.Cells[3];
                    if (lblCellMar.Text == "0.00")
                    {
                        CellMar.ForeColor = System.Drawing.Color.Red;
                        CellMar.Font.Bold = true;
                    }
                    Label lblCellApr = ((Label)e.Row.FindControl("lblApr"));
                    TableCell CellApr = e.Row.Cells[4];
                    if (lblCellApr.Text == "0.00")
                    {
                        CellApr.ForeColor = System.Drawing.Color.Red;
                        CellApr.Font.Bold = true;
                    }
                    Label lblCellMay = ((Label)e.Row.FindControl("lblMay"));
                    TableCell CellMay = e.Row.Cells[5];
                    if (lblCellMay.Text == "0.00")
                    {
                        CellMay.ForeColor = System.Drawing.Color.Red;
                        CellMay.Font.Bold = true;
                    }
                    Label lblCellJun = ((Label)e.Row.FindControl("lblJun"));
                    TableCell CellJun = e.Row.Cells[6];
                    if (lblCellJun.Text == "0.00")
                    {
                        CellJun.ForeColor = System.Drawing.Color.Red;
                        CellJun.Font.Bold = true;
                    }
                    Label lblCellJul = ((Label)e.Row.FindControl("lblJul"));
                    TableCell CellJul = e.Row.Cells[7];
                    if (lblCellJul.Text == "0.00")
                    {
                        CellJul.ForeColor = System.Drawing.Color.Red;
                        CellJul.Font.Bold = true;
                    }
                    Label lblCellAug = ((Label)e.Row.FindControl("lblAug"));
                    TableCell CellAug = e.Row.Cells[8];
                    if (lblCellAug.Text == "0.00")
                    {
                        CellAug.ForeColor = System.Drawing.Color.Red;
                        CellAug.Font.Bold = true;
                    }
                    Label lblCellSep = ((Label)e.Row.FindControl("lblSep"));
                    TableCell CellSep = e.Row.Cells[9];
                    if (lblCellSep.Text == "0.00")
                    {
                        CellSep.ForeColor = System.Drawing.Color.Red;
                        CellSep.Font.Bold = true;
                    }
                    Label lblCellOct = ((Label)e.Row.FindControl("lblOct"));
                    TableCell CellOct = e.Row.Cells[10];
                    if (lblCellOct.Text == "0.00")
                    {
                        CellOct.ForeColor = System.Drawing.Color.Red;
                        CellOct.Font.Bold = true;
                    }
                    Label lblCellNov = ((Label)e.Row.FindControl("lblNov"));
                    TableCell CellNov = e.Row.Cells[11];
                    if (lblCellNov.Text == "0.00")
                    {
                        CellNov.ForeColor = System.Drawing.Color.Red;
                        CellNov.Font.Bold = true;
                    }
                    Label lblCellDec = ((Label)e.Row.FindControl("lblDec"));
                    TableCell CellDec = e.Row.Cells[12];
                    if (lblCellDec.Text == "0.00")
                    {
                        CellDec.ForeColor = System.Drawing.Color.Red;
                        CellDec.Font.Bold = true;
                    }
                }

                //Footer code
                if (e.Row.RowType == DataControlRowType.Footer)
                {
                    //Jan
                    Label lbltotalJan = (Label)e.Row.FindControl("lbltotalJan");
                    lbltotalJan.Text = string.Format("{0:n}", totalJan);
                    Label lbltotalWJan = (Label)e.Row.FindControl("lbltotalWJan");
                    lbltotalWJan.Text = totalWJan.ToString();
                    Label lblavgJan = (Label)e.Row.FindControl("lblavgJan");
                    avgJan = Math.Round((totalJan / totalWJan), 1);
                    if (avgJan > 0)
                    {
                        lblavgJan.Text = string.Format("{0:n}", avgJan);
                    }
                    else
                    {
                        lblavgJan.Text = string.Format("{0:n}", 0.00);
                    };

                    //Feb
                    Label lbltotalFeb = (Label)e.Row.FindControl("lbltotalFeb");
                    lbltotalFeb.Text = string.Format("{0:n}", totalFeb);
                    Label lbltotalWFeb = (Label)e.Row.FindControl("lbltotalWFeb");
                    lbltotalWFeb.Text = totalWFeb.ToString();
                    Label lblavgFeb = (Label)e.Row.FindControl("lblavgFeb");
                    avgFeb = Math.Round((totalFeb / totalWFeb), 1);
                    if (avgFeb > 0)
                    {
                        lblavgFeb.Text = string.Format("{0:n}", avgFeb);
                    }
                    else
                    {
                        lblavgFeb.Text = string.Format("{0:n}", 0.00);
                    };

                    //Mar
                    Label lbltotalMar = (Label)e.Row.FindControl("lbltotalMar");
                    lbltotalMar.Text = string.Format("{0:n}", totalMar);
                    Label lbltotalWMar = (Label)e.Row.FindControl("lbltotalWMar");
                    lbltotalWMar.Text = totalWMar.ToString();
                    Label lblavgMar = (Label)e.Row.FindControl("lblavgMar");
                    avgMar = Math.Round((totalMar / totalWMar), 1);
                    if (avgMar > 0)
                    {
                        lblavgMar.Text = string.Format("{0:n}", avgMar);
                    }
                    else
                    {
                        lblavgMar.Text = string.Format("{0:n}", 0.00);
                    };

                    //Apr
                    Label lbltotalApr = (Label)e.Row.FindControl("lbltotalApr");
                    lbltotalApr.Text = string.Format("{0:n}", totalApr);
                    Label lbltotalWApr = (Label)e.Row.FindControl("lbltotalWApr");
                    lbltotalWApr.Text = totalWApr.ToString();
                    Label lblavgApr = (Label)e.Row.FindControl("lblavgApr");
                    avgApr = Math.Round((totalApr / totalWApr), 1);
                    if (avgApr > 0)
                    {
                        lblavgApr.Text = string.Format("{0:n}", avgApr);
                    }
                    else
                    {
                        lblavgApr.Text = string.Format("{0:n}", 0.00);
                    };

                    //May
                    Label lbltotalMay = (Label)e.Row.FindControl("lbltotalMay");
                    lbltotalMay.Text = string.Format("{0:n}", totalMay);
                    Label lbltotalWMay = (Label)e.Row.FindControl("lbltotalWMay");
                    lbltotalWMay.Text = totalWMay.ToString();
                    Label lblavgMay = (Label)e.Row.FindControl("lblavgMay");
                    avgMay = Math.Round((totalMay / totalWMay), 1);
                    if (avgMay > 0)
                    {
                        lblavgMay.Text = string.Format("{0:n}", avgMay);
                    }
                    else
                    {
                        lblavgMay.Text = string.Format("{0:n}", 0.00);
                    };

                    //Jun
                    Label lbltotalJun = (Label)e.Row.FindControl("lbltotalJun");
                    lbltotalJun.Text = string.Format("{0:n}", totalJun);
                    Label lbltotalWJun = (Label)e.Row.FindControl("lbltotalWJun");
                    lbltotalWJun.Text = totalWJun.ToString();
                    Label lblavgJun = (Label)e.Row.FindControl("lblavgJun");
                    avgJun = Math.Round((totalJun / totalWJun), 1);
                    if (avgJun > 0)
                    {
                        lblavgJun.Text = string.Format("{0:n}", avgJun);
                    }
                    else
                    {
                        lblavgJun.Text = string.Format("{0:n}", 0.00);
                    };

                    //Jul
                    Label lbltotalJul = (Label)e.Row.FindControl("lbltotalJul");
                    lbltotalJul.Text = string.Format("{0:n}", totalJul);
                    Label lbltotalWJul = (Label)e.Row.FindControl("lbltotalWJul");
                    lbltotalWJul.Text = totalWJul.ToString();
                    Label lblavgJul = (Label)e.Row.FindControl("lblavgJul");
                    avgJul = Math.Round((totalJul / totalWJul), 1);
                    if (avgJul > 0)
                    {
                        lblavgJul.Text = string.Format("{0:n}", avgJul);
                    }
                    else
                    {
                        lblavgJul.Text = string.Format("{0:n}", 0.00);
                    };

                    //Aug
                    Label lbltotalAug = (Label)e.Row.FindControl("lbltotalAug");
                    lbltotalAug.Text = string.Format("{0:n}", totalAug);
                    Label lbltotalWAug = (Label)e.Row.FindControl("lbltotalWAug");
                    lbltotalWAug.Text = totalWAug.ToString();
                    Label lblavgAug = (Label)e.Row.FindControl("lblavgAug");
                    avgAug = Math.Round((totalAug / totalWAug), 1);
                    if (avgAug > 0)
                    {
                        lblavgAug.Text = string.Format("{0:n}", avgAug);
                    }
                    else
                    {
                        lblavgAug.Text = string.Format("{0:n}", 0.00);
                    };

                    //Sep
                    Label lbltotalSep = (Label)e.Row.FindControl("lbltotalSep");
                    lbltotalSep.Text = string.Format("{0:n}", totalSep);
                    Label lbltotalWSep = (Label)e.Row.FindControl("lbltotalWSep");
                    lbltotalWSep.Text = totalWSep.ToString();
                    Label lblavgSep = (Label)e.Row.FindControl("lblavgSep");
                    avgSep = Math.Round((totalSep / totalWSep), 1);
                    if (avgSep > 0)
                    {
                        lblavgSep.Text = string.Format("{0:n}", avgSep);
                    }
                    else
                    {
                        lblavgSep.Text = string.Format("{0:n}", 0.00);
                    };

                    //Oct
                    Label lbltotalOct = (Label)e.Row.FindControl("lbltotalOct");
                    lbltotalOct.Text = string.Format("{0:n}", totalOct);
                    Label lbltotalWOct = (Label)e.Row.FindControl("lbltotalWOct");
                    lbltotalWOct.Text = totalWOct.ToString();
                    Label lblavgOct = (Label)e.Row.FindControl("lblavgOct");
                    avgOct = Math.Round((totalOct / totalWOct), 1);
                    if (avgOct > 0)
                    {
                        lblavgOct.Text = string.Format("{0:n}", avgOct);
                    }
                    else
                    {
                        lblavgOct.Text = string.Format("{0:n}", 0.00);
                    };

                    //Nov
                    Label lbltotalNov = (Label)e.Row.FindControl("lbltotalNov");
                    lbltotalNov.Text = string.Format("{0:n}", totalNov);
                    Label lbltotalWNov = (Label)e.Row.FindControl("lbltotalWNov");
                    lbltotalWNov.Text = totalWNov.ToString();
                    Label lblavgNov = (Label)e.Row.FindControl("lblavgNov");
                    avgNov = Math.Round((totalNov / totalWNov), 1);
                    if (avgNov > 0)
                    {
                        lblavgNov.Text = string.Format("{0:n}", avgNov);
                    }
                    else
                    {
                        lblavgNov.Text = string.Format("{0:n}", 0.00);
                    };

                    //Dec
                    Label lbltotalDec = (Label)e.Row.FindControl("lbltotalDec");
                    lbltotalDec.Text = string.Format("{0:n}", totalDec);
                    Label lbltotalWDec = (Label)e.Row.FindControl("lbltotalWDec");
                    lbltotalWDec.Text = totalWDec.ToString();
                    Label lblavgDec = (Label)e.Row.FindControl("lblavgDec");
                    avgDec = Math.Round((totalDec / totalWDec), 1);
                    if (avgDec > 0)
                    {
                        lblavgDec.Text = string.Format("{0:n}", avgDec);
                    }
                    else
                    {
                        lblavgDec.Text = string.Format("{0:n}", 0.00);
                    };
                }
            }
        }
        /// <summary>
        /// Monthly condiment  Report Page.. 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnMonthlycondiment_Click(object sender, EventArgs e)
        {

            string sWhereCondition = string.Empty;
            if (cmbBranches.Visible == true)
            {
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);
                BranchName = cd_BranchAlias;
            }
            else
            {
                BranchName = Session["Branch_HQ"].ToString();
            }



            DTExcel.Clear();
            string sCmd = string.Empty;
            // Get the Starting Day of the Year
           
            string _Current_year =string.Empty;
            string _Current_year_On_DateSelection = string.Empty;
            string _FD = string.Empty;
            string _LD = string.Empty;

            _Current_year = ObjMain.ExecuteScalar("select year(curdate());");
            _Current_year_On_DateSelection = ObjMain.ExecuteScalar("select   year('" + txtFromDt.Text + "');");

            if (int.Parse(_Current_year) == int.Parse(_Current_year_On_DateSelection))
            {
                _FD = ObjMain.ExecuteScalar(" SELECT  DATE_FORMAT(MAKEDATE(year(now()),1),'%y-%m-%d') FD;");
                // Get the ending day of the year 
                _LD = ObjMain.ExecuteScalar("SELECT  DATE_FORMAT(LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH)),'%y-%m-%d') AS LD ;");

            }
            else if ( int.Parse(_Current_year) > int.Parse(_Current_year_On_DateSelection))
            {
                _FD = ObjMain.ExecuteScalar(" SELECT  DATE_FORMAT(MAKEDATE(year('"+txtFromDt.Text+"'),1),'%y-%m-%d') FD;");
                // Get the ending day of the year 
                _LD = ObjMain.ExecuteScalar("SELECT  DATE_FORMAT(LAST_DAY(DATE_ADD('"+txtFromDt.Text+"', INTERVAL 12-MONTH('"+txtFromDt.Text +"') MONTH)),'%y-%m-%d') AS LD ;");

            }


            sCmd = " Select K.CondimentName as CondimentName, Sum(K.Jan_Qty) as Jan_Qty,Sum(K.Feb_Qty) as Feb_Qty,Sum(K.Mar_Qty) as Mar_Qty,Sum(K.Apr_Qty) as Apr_Qty,Sum(K.May_Qty) as May_Qty, "
            + "Sum(K.Jun_Qty) as Jun_Qty, Sum(K.Jul_Qty) as Jul_Qty,Sum(K.Aug_Qty) as Aug_Qty,Sum(K.Sep_Qty) as Sep_Qty,Sum(K.Oct_Qty) as Oct_Qty,Sum(K.Nov_Qty) as Nov_Qty,Sum(K.Dec_Qty) as Dec_Qty "
            + "From"
            + "("
            + "Select TDC.CondimentName As CondimentName, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty,"
            + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "'"
            + " And Month(T.BusinessDate) = '01' "
            + " Group by TDC.CondimentName "
            + " union all "

            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty,"
            + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '02' "
            + " Group by TDC.CondimentName "
            + " union all "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty,"
            + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '03' "
            + " Group by TDC.CondimentName "
            + " union all "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Apr_Qty, 0 as May_Qty, "
            + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '04' "
            + " Group by TDC.CondimentName "
            + " union All "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as May_Qty, "
            + " 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + "  And Month(T.BusinessDate) = '05' "
            + " Group by TDC.CondimentName "
            + " union all "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Jun_Qty, "
            + " 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '06' "
            + " Group by TDC.CondimentName "
            + " Union All "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, "
            + " IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '07' "
            + " Group by TDC.CondimentName "
            + " Union All "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, "
            + " IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '08' "
            + " Group by TDC.CondimentName "
            + " Union All "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, "
            + " IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null"
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '09' "
            + " Group by TDC.CondimentName "
            + " Union All "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, 0 as Sep_Qty, "
            + " IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Oct_Qty, 0 as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '10' "
            + " Group by TDC.CondimentName "
            + " Union All "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, "
            + " 0 as Aug_Qty, 0 as Sep_Qty, 0 as Oct_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Nov_Qty, 0 as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '11' "
            + " Group by TDC.CondimentName "
            + " Union All "
            + " Select TDC.CondimentName As CondimentName, 0 as Jan_Qty, 0 as Feb_Qty, 0 as Mar_Qty, 0 as Apr_Qty, 0 as May_Qty, 0 as Jun_Qty, 0 as Jul_Qty, 0 as Aug_Qty, "
            + " 0 as Sep_Qty, 0 as Oct_Qty, 0 as Nov_Qty, IF(Count(TDC.CondimentName) Is Null, 0, Count(TDC.CondimentName)) as Dec_Qty "
            + " From {0}.TransDetail TD Left Outer Join {0}.Trans T On TD.TransID = T.TransID "
            + " Left Outer Join {0}.TransDetailCondiment TDC On TD.TransID = TDC.TransID "
            + " Where T.TransStatus in (2, 3, 4) And TD.ItemStatus = 0 And TDC.CondimentName is not null "
            + " And T.BusinessDate Between '" + _FD + "' And '" + _LD + "' "
            + " And Month(T.BusinessDate) = '12' "
            + " Group by TDC.CondimentName "
            + " ) as K "
            + " Group By K.CondimentName; ";

            string command = string.Format(sCmd, BranchName);
            DTExcel = ObjMain.GetData_DT(command);
            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = DTExcel;
            GridView1.DataBind();
            Response.Clear();
            Response.Buffer = true;
            Response.ContentEncoding = System.Text.Encoding.Unicode;
            Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
            Response.AddHeader("content-disposition",
                "attachment;filename=MonthlyCondiment.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridView1.Rows[i].Attributes.Add("class", "textmode");
            }
            GridView1.RenderControl(hw);
            string style = @"<style> td { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();


        }

    }
}