using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Web;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using CLS_BL;
using CLS_DL;
using CLS_PL;
using System.Web.UI.DataVisualization.Charting;
using System.Web.Services;
using Newtonsoft.Json;
using static SUNWEB.InteractiveDashboard;
using DocumentFormat.OpenXml.Bibliography;
using iTextSharp.text.pdf;
using System.ComponentModel;

namespace SUNWEB
{
    public partial class ConsumerBehaviourAnalysis : System.Web.UI.Page
    {
        BL_SELECT ObjSel = new BL_SELECT();
        BLCompany ObjBL = new BLCompany();
        PLCOMMON ObjPL = new PLCOMMON();
        BL_USER ObjUserBL = new BL_USER(); //This Object for All User and Page Related fuctions included 
        PLCUSTOMER ObjCustPL = new PLCUSTOMER();
        Int32 K = 0;
        Int32 L = 0;
        Int32 S = 0;
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        DataSet ds = new DataSet();
        MYSQLDB ObjMain = new MYSQLDB();
        MYSQLCLOUD ObjMainCloud = new MYSQLCLOUD();
        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();
        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_ConsumerBehaviourAnalysis = string.Empty;
        string Branchidval = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;

        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;

        string BraNam = "";
        string FDate = "";
        string TDate = "";
        string BranchAlias = string.Empty;

        string Vw_Chk = string.Empty;
        StringBuilder str = new StringBuilder();

        string sWhereCondition = string.Empty;



       
        protected void Page_Load(object sender, EventArgs e)
        {
            
            PID = "41";    // This is for  Consumer Behaviour Analysis Page
            hidDropdownValue.Value = CmbSelect.SelectedItem.Text;

            if (!IsPostBack)
            {
                txtFromDt.Text = Curr_DateOnly;//"2021-01-01";
                txtToDt.Text = Curr_DateOnly;
            }
            MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();

                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();
                    }
                }
            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();
                }
                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                //LoadInventoryListGrid(cd_BranchAlias, txtStartDt.Text, txtEndDt.Text);
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName();
                    ActiveCall();
                }
            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == false))
            {
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;

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

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

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

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

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

                        }


                    }

                }

            }
            else
            {

            }


        }


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


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

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

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

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

            }

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

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


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

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

               
                // BindChart();
            }

        }


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

            }

        }


        protected void cmbSelect_SelectedIndexChanged(object sender, EventArgs e)
        {
            hidDropdownValue.Value = CmbSelect.SelectedItem.Text;
        }

        protected void btnGenerate_Click(object sender, EventArgs e)
        {

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

          

          

            if (cmbBranches.Visible == false)
            {
                btnGenerate.Visible = true;
                //lblMsgTotalSelection.Text = "Search Result - Journal Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp to &nbsp;&nbsp;" + txtToDt.Text;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px");
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;

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


                if (CmbSelect.SelectedItem.Text == "Itemwise")
                {
                    Session["ACTIVEDB"] = BranchAlias;
                    hidDropdownValue.Value = CmbSelect.SelectedItem.Text;

                    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "MainCall();", true);

                    LoadItemwiseRpt(Session["Branch_HQ"].ToString(), FDate, TDate);
                }
                else if (CmbSelect.SelectedItem.Text == "Categorywise")
                {
                    Session["ACTIVEDB"] = BranchAlias;
                    hidDropdownValue.Value = CmbSelect.SelectedItem.Text;

                    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "MainCall();", true);

                    LoadCategorywiseRpt(Session["Branch_HQ"].ToString(), FDate, TDate);
                }
                else if (CmbSelect.SelectedItem.Text == "Memberwise")
                {
                    Session["ACTIVEDB"] = BranchAlias;
                    hidDropdownValue.Value = CmbSelect.SelectedItem.Text;

                    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "MainCall();", true);

                    LoadMemberwiseRpt(Session["Branch_HQ"].ToString(), FDate, TDate);
                }


                //  System.Threading.Thread.Sleep(1000);
                //BindChart();
                //btnGenerateReport_Click(sender, e);
               
            }
            else if (cmbBranches.Visible == true)
            {
                //lblMsgTotalSelection.Text = "Search Result - Journal Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp to &nbsp;&nbsp;" + txtToDt.Text;
                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;

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

                //LoadItemwiseRpt(cd_BranchAlias, FDate, TDate);
                if (CmbSelect.SelectedItem.Text == "Itemwise")
                {
                    Session["ACTIVEDB"] = BranchAlias;
                    hidDropdownValue.Value = CmbSelect.SelectedItem.Text;

                    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "MainCall();", true);
                    LoadItemwiseRpt(cd_BranchAlias, FDate, TDate);
                }
                else if (CmbSelect.SelectedItem.Text == "Categorywise")
                {
                    Session["ACTIVEDB"] = BranchAlias;
                    hidDropdownValue.Value = CmbSelect.SelectedItem.Text;

                    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "MainCall();", true);
                    LoadCategorywiseRpt(cd_BranchAlias, FDate, TDate);
                }
                else if (CmbSelect.SelectedItem.Text == "Memberwise")
                {
                    Session["ACTIVEDB"] = BranchAlias;
                    hidDropdownValue.Value = CmbSelect.SelectedItem.Text;

                    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "MainCall();", true);
                    LoadMemberwiseRpt(cd_BranchAlias, FDate, TDate);
                }
               


                //BindChart();
                //btnGenerateReport_Click(sender, e);

            }
        }

        public void LoadItemwiseRpt(string BranchAlias, string D1, string D2)
        {

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

            if (cmbSelectby.Text == "AInventory")
            {

            }
            else if (cmbSelectby.Text == "Category")
            {
                sWhereCondition = " and CategoryNumber = @SearchVal ";
            }
            else if (cmbSelectby.Text == "SInventory")
            {
                sWhereCondition = " and Barcode = @SearchVal ";
            }
            else
            {
                if (txtName.Text.Trim() != "")
                {
                    sWhereCondition = "And I." + cmbSelectby.Text.Trim() + " = @SearchVa2";
                }
            }

            // System.Threading.Thread.Sleep(1000);
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string sCmd;

            //for CRM View
            sCmd = "DROP VIEW IF EXISTS " + BranchAlias + ".`CRM`;";
            S = ObjMain.ExecuteNonQuery(sCmd);
            sCmd = "CREATE VIEW " + BranchAlias + ".CRM AS " +
            " Select T.TransID as TransID, T.TransNo as TransNo, T.TransDate as TransDate, T.CustomerNumber as CustomerNumber, T.Total as Total," +
            " TD.Barcode as Barcode, TD.Name as Name, TD.InventoryDesc as InventoryDesc, TD.CategoryNumber as CategoryNumber, IC.CategoryName as CategoryName," +
            " TD.Group1 as Group1,TD.Group2 as Group2,TD.Group3 as Group3,TD.Group4 as Group4,TD.Group5 as Group5, TD.Qty as Qty, TD.PriceType as PriceType,TD.Price as Price,TD.Amount as Amount," +
            " TD.TaxAmount1 as TaxAmount1,TD.TaxAmount2 as TaxAmount2,TD.TaxAmount3 as TaxAmount3,TD.TaxAmount4 as TaxAmount4,TD.TaxAmount5 as TaxAmount5,TD.TaxAmount6 as TaxAmount6,TD.TaxableAmount as TaxableAmount," +
            " TD.ManualDisc as ManualDisc, TD.MemberDisc as MemberDisc,TD.TotalDisc as TotalDisc,TD.TotalMemberDisc as TotalMemberDisc, TD.TotalPSDisc as TotalPSDisc,TD.UnitCost as UnitCost" +
            " From " + BranchAlias + ".Trans T Left Outer Join " + BranchAlias + ".TransDetail TD On T.TransID = TD.TransID" +
            " Left Outer Join " + BranchAlias + ".InventoryCategories IC on TD.CategoryNumber = IC.CategoryNumber" +
            " Where Date(T.TransDate) Between '" + D1 + "' and '" + D2 + "' " +
            " And T.TransStatus in (2) And TD.ItemStatus = 0 And T.CustomerNumber <> ''" +
            " Order By 1;";
            S = ObjMain.ExecuteNonQuery(sCmd);

            //for Itemwise Query
            if (cmbSelectby.Text == "AInventory" || cmbSelectby.Text == "Category" || cmbSelectby.Text == "SInventory") //Inventory,Category
            {
                string cmd = "Select Barcode as Barcode, Name as Name, InventoryDesc as InventoryDesc, Sum(Qty) as Qty, Concat(Round(Sum(Qty) * 100 / T.TQty, 2),'%') as QtyPercentage," +
                " Round(Sum(Amount),2) Amount, Concat(Round(Sum(Amount) * 100 / T.TAmount, 2),'%') as AmountPercentage" +
                " From {0}.CRM Cross Join(Select Sum(Qty) as TQty, Sum(Amount) as TAmount From {0}.CRM ) T" +
                " Where TransDate Between @D1 and @D2 " + sWhereCondition + " " +
                " Group by Barcode;";

                DataTable Dt = new DataTable();
                string command = string.Format(cmd, BranchAlias);
                MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                     new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                     //new MySqlParameter("@SearchVa2",txtName.Text.Trim()),

                    };

                Dt = ObjMain.NewGetData_DT(command, parameters);
                GridView1.DataSource = Dt;
                GridView1.DataBind();
            }
            else
            {
                GridView1.DataSource = null;
                GridView1.DataBind();
            }

            //BindChartItemwiseQty(BranchAlias, D1, D2);
            //BindChart(BranchAlias, D1, D2);

        }

        public void LoadCategorywiseRpt(string BranchAlias, string D1, string D2)
        {

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

            if (cmbSelectby.Text == "AInventory")
            {

            }
            else if (cmbSelectby.Text == "Category")
            {
                sWhereCondition = " and CategoryNumber = @SearchVal ";
            }
            else if (cmbSelectby.Text == "SInventory")
            {
                sWhereCondition = " and Barcode = @SearchVal ";
            }
            else
            {
                if (txtName.Text.Trim() != "")
                {
                    sWhereCondition = "And I." + cmbSelectby.Text.Trim() + " = @SearchVa2";
                }
            }

            // System.Threading.Thread.Sleep(1000);
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string sCmd;

            //for CRM View
            sCmd = "DROP VIEW IF EXISTS " + BranchAlias + ".`CRM`;";
            S = ObjMain.ExecuteNonQuery(sCmd);
            sCmd = "CREATE VIEW " + BranchAlias + ".CRM AS " +
            " Select T.TransID as TransID, T.TransNo as TransNo, T.TransDate as TransDate, T.CustomerNumber as CustomerNumber, T.Total as Total," +
            " TD.Barcode as Barcode, TD.Name as Name, TD.InventoryDesc as InventoryDesc, TD.CategoryNumber as CategoryNumber, IC.CategoryName as CategoryName," +
            " TD.Group1 as Group1,TD.Group2 as Group2,TD.Group3 as Group3,TD.Group4 as Group4,TD.Group5 as Group5, TD.Qty as Qty, TD.PriceType as PriceType,TD.Price as Price,TD.Amount as Amount," +
            " TD.TaxAmount1 as TaxAmount1,TD.TaxAmount2 as TaxAmount2,TD.TaxAmount3 as TaxAmount3,TD.TaxAmount4 as TaxAmount4,TD.TaxAmount5 as TaxAmount5,TD.TaxAmount6 as TaxAmount6,TD.TaxableAmount as TaxableAmount," +
            " TD.ManualDisc as ManualDisc, TD.MemberDisc as MemberDisc,TD.TotalDisc as TotalDisc,TD.TotalMemberDisc as TotalMemberDisc, TD.TotalPSDisc as TotalPSDisc,TD.UnitCost as UnitCost" +
            " From " + BranchAlias + ".Trans T Left Outer Join " + BranchAlias + ".TransDetail TD On T.TransID = TD.TransID" +
            " Left Outer Join " + BranchAlias + ".InventoryCategories IC on TD.CategoryNumber = IC.CategoryNumber" +
            " Where Date(T.TransDate) Between '" + D1 + "' and '" + D2 + "' " +
            " And T.TransStatus in (2) And TD.ItemStatus = 0 And T.CustomerNumber <> ''" +
            " Order By 1;";
            S = ObjMain.ExecuteNonQuery(sCmd);

            if (cmbSelectby.Text == "AInventory" || cmbSelectby.Text == "Category" || cmbSelectby.Text == "SInventory") //Inventory,Category
            {
                //for Categorywise Query
                string cmd = "Select CategoryName as CategoryName, Sum(Qty) as Qty, Concat(Round(Sum(Qty) * 100 / T.TQty, 2), '%') as QtyPercentage," +
                   " Round(Sum(Amount), 2) Amount, Concat(Round(Sum(Amount) * 100 / T.TAmount, 2), '%') as AmountPercentage" +
                   " From {0}.CRM Cross Join(Select Sum(Qty) as TQty, Sum(Amount) as TAmount From {0}.CRM ) T" +
                   " Where TransDate Between @D1 and @D2 " + sWhereCondition + " " +
                   " Group by CategoryName;";

                DataTable Dt = new DataTable();
                string command = string.Format(cmd, BranchAlias);
                MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                     new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                     //new MySqlParameter("@SearchVa2",txtName.Text.Trim()),

                    };

                Dt = ObjMain.NewGetData_DT(command, parameters);
                GridView1.DataSource = Dt;
                GridView1.DataBind();
            }
            else
            {
                GridView1.DataSource = null;
                GridView1.DataBind();
            }


            //BindChartItemwiseQty(BranchAlias, D1, D2);
            //BindChart(BranchAlias, D1, D2);

        }

        public void LoadMemberwiseRpt(string BranchAlias, string D1, string D2)
        {

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

            if (cmbSelectby.Text == "AInventory")
            {

            }
            else if (cmbSelectby.Text == "Category")
            {
                sWhereCondition = " and CategoryNumber = @SearchVal ";
            }
            else if (cmbSelectby.Text == "SInventory")
            {
                sWhereCondition = " and Barcode = @SearchVal ";
            }
            else if (cmbSelectby.Text == "Customer")
            {
                sWhereCondition = " and CustomerNumber = @SearchVal ";
            }
            else
            {
                if (txtName.Text.Trim() != "")
                {
                    sWhereCondition = "And I." + cmbSelectby.Text.Trim() + " = @SearchVa2";
                }
            }

            // System.Threading.Thread.Sleep(1000);
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string sCmd;

            //for CRM View
            sCmd = "DROP VIEW IF EXISTS " + BranchAlias + ".`CRM`;";
            S = ObjMain.ExecuteNonQuery(sCmd);
            sCmd = "CREATE VIEW " + BranchAlias + ".CRM AS " +
            " Select T.TransID as TransID, T.TransNo as TransNo, T.TransDate as TransDate, T.CustomerNumber as CustomerNumber, T.Total as Total," +
            " TD.Barcode as Barcode, TD.Name as Name, TD.InventoryDesc as InventoryDesc, TD.CategoryNumber as CategoryNumber, IC.CategoryName as CategoryName," +
            " TD.Group1 as Group1,TD.Group2 as Group2,TD.Group3 as Group3,TD.Group4 as Group4,TD.Group5 as Group5, TD.Qty as Qty, TD.PriceType as PriceType,TD.Price as Price,TD.Amount as Amount," +
            " TD.TaxAmount1 as TaxAmount1,TD.TaxAmount2 as TaxAmount2,TD.TaxAmount3 as TaxAmount3,TD.TaxAmount4 as TaxAmount4,TD.TaxAmount5 as TaxAmount5,TD.TaxAmount6 as TaxAmount6,TD.TaxableAmount as TaxableAmount," +
            " TD.ManualDisc as ManualDisc, TD.MemberDisc as MemberDisc,TD.TotalDisc as TotalDisc,TD.TotalMemberDisc as TotalMemberDisc, TD.TotalPSDisc as TotalPSDisc,TD.UnitCost as UnitCost" +
            " From " + BranchAlias + ".Trans T Left Outer Join " + BranchAlias + ".TransDetail TD On T.TransID = TD.TransID" +
            " Left Outer Join " + BranchAlias + ".InventoryCategories IC on TD.CategoryNumber = IC.CategoryNumber" +
            " Where Date(T.TransDate) Between '" + D1 + "' and '" + D2 + "' " +
            " And T.TransStatus in (2) And TD.ItemStatus = 0 And T.CustomerNumber <> ''" +
            " Order By 1;";
            S = ObjMain.ExecuteNonQuery(sCmd);
            

            if (cmbSelectby.Text == "AInventory" || cmbSelectby.Text == "Category" || cmbSelectby.Text == "SInventory" || cmbSelectby.Text == "Customer") //Inventory,Category
            {
                //Memberwise Query
                 string cmd = "Select CustomerNumber, Sum(Qty) as Qty,Round(Sum(TaxAmount1), 2) as TaxAmount1,Round(Sum(TaxAmount2), 2) as TaxAmount2,Round(Sum(TaxAmount3), 2) as TaxAmount3," +
                   " Round(Sum(ManualDisc), 2) as ItemDisc,Round(Sum(MemberDisc), 2) as MemberDisc,Round(Sum(TotalDisc), 2) as TotalDisc,Round(Sum(Amount), 2) as Amount" +
                   " From {0}.CRM " +
                   " Where TransDate Between @D1 and @D2 " + sWhereCondition + " " +
                   " Group by CustomerNumber Order by CustomerNumber;";

                DataTable Dt = new DataTable();
                string command = string.Format(cmd, BranchAlias);
                MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@D1",D1),
                     new MySqlParameter("@D2",D2),
                     new MySqlParameter("@SearchVal",txtID.Text.Trim()),
                     //new MySqlParameter("@SearchVa2",txtName.Text.Trim()),
                   };

                Dt = ObjMain.NewGetData_DT(command, parameters);
                GridView2.DataSource = Dt;

                //for Grand Totals
                double totQty = 0,totTaxAmount1=0, totTaxAmount2 = 0, totTaxAmount3 = 0, totItemDisc = 0, totMemberDisc = 0, totTotalDisc = 0, totAmount = 0;
                foreach (DataRow dr in Dt.Rows)
                {
                    totQty += Convert.ToDouble(dr["Qty"]);
                    totTaxAmount1 += Convert.ToDouble(dr["TaxAmount1"]);
                    totTaxAmount2 += Convert.ToDouble(dr["TaxAmount2"]);
                    totTaxAmount3 += Convert.ToDouble(dr["TaxAmount3"]);
                    totItemDisc += Convert.ToDouble(dr["ItemDisc"]);
                    totMemberDisc += Convert.ToDouble(dr["MemberDisc"]);
                    totTotalDisc += Convert.ToDouble(dr["TotalDisc"]);
                    totAmount += Convert.ToDouble(dr["Amount"]);
                }
                GridView2.Columns[1].FooterText = "GrandTotal:";
                GridView2.Columns[2].FooterText = totQty.ToString();
                GridView2.Columns[3].FooterText = string.Format("{0:n}", totTaxAmount1);
                GridView2.Columns[4].FooterText = string.Format("{0:n}", totTaxAmount2);
                GridView2.Columns[5].FooterText = string.Format("{0:n}", totTaxAmount3);
                GridView2.Columns[6].FooterText = string.Format("{0:n}", totItemDisc);
                GridView2.Columns[7].FooterText = string.Format("{0:n}", totMemberDisc);
                GridView2.Columns[8].FooterText = string.Format("{0:n}", totTotalDisc);
                GridView2.Columns[9].FooterText = string.Format("{0:n}", totAmount);

                GridView2.DataBind();
            }
            else
            {
                GridView2.DataSource = null;
                GridView2.DataBind();
            }



        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {


        }

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            if (cmbBranches.Visible == false)
            {
                GridView1.PageIndex = e.NewPageIndex;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadItemwiseRpt(Session["Branch_HQ"].ToString(), FDate, TDate);
            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )

                GridView1.PageIndex = e.NewPageIndex;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadItemwiseRpt(Session["Branch_HQ"].ToString(), FDate, TDate);
            }
        }


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


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

            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
                Cn.Open();

                //for Details
                GridView gv1 = (GridView)e.Row.FindControl("GridView3");
                Dt2.Clear();
                sCmd = "Select TransID, Trim(TransNo) as TransNo, Date(TransDate) as TransDate, Barcode,Name,InventoryDesc,CategoryName,Round(Price, 2) as Price,Qty," +
                " Round(TaxAmount1, 2) as TaxAmount1,Round(TaxAmount2, 2) as TaxAmount2, Round(TaxAmount3, 2) as TaxAmount3,Round(ManualDisc, 2) as ItemDisc,Round(MemberDisc, 2) as MemberDisc,Round(TotalDisc, 2) as TotalDisc,Round(UnitCost, 2) as UnitCost,Round(Amount, 2) as Amount" +
                " From " + BranchAlias + ".CRM " +
                " Where TransDate Between  '" + StartDT + "' and  '" + EndDt + "' " +
                " And Customernumber = '" + ((DataRowView)e.Row.DataItem)["CustomerNumber"].ToString() + "' " +
                " Order by TransID,CustomerNumber;";

                MySqlDataAdapter Ada = new MySqlDataAdapter(sCmd, Cn);
                ds = new DataSet();
                Dt2.Clear();
                Ada.Fill(Dt2);

                gv1.DataSource = Dt2;
                gv1.DataBind();
            }

        }
        protected void GridView2_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            if (cmbBranches.Visible == false)
            {
                GridView2.PageIndex = e.NewPageIndex;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadMemberwiseRpt(Session["Branch_HQ"].ToString(), FDate, TDate);
            }
            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 )

                GridView2.PageIndex = e.NewPageIndex;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadMemberwiseRpt(Session["Branch_HQ"].ToString(), FDate, TDate);
            }
        }



        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {
        }
        private void BindChartItemwiseQty(string BranchAlias, string D1, string D2)
        {
            DataTable dt = new DataTable();

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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string sCmd;

            //DataTable dsChartData = new DataTable();
            //StringBuilder strScript = new StringBuilder();

            try
            {
                //dsChartData = GetChartData();
                //for ItemwiseQty Query
                sCmd = "Select InventoryDesc as InventoryDesc, Sum(Qty) as Qty" +
                " From " + BranchAlias + ".CRM " +
                " Where TransDate Between '" + D1 + "' and '" + D2 + "' " +
                " Group by InventoryDesc;";

                MySqlDataAdapter Ada = new MySqlDataAdapter(sCmd, Cn);
                ds = new DataSet();
                Ada.Fill(dt);

                Session["DtChart"] = dt;



                //str.Append(@"<script type=text/javascript> google.load( *visualization*, *1*, {packages:[*corechart*]});
                //       google.setOnLoadCallback(drawChart);
                //       function drawChart() {
                //var data = new google.visualization.DataTable();
                //data.addColumn('string', 'InventoryDesc');
                //data.addColumn('number','Qty');      


                //data.addRows(" + dt.Rows.Count + "); ");
                //for (int i = 0; i <= dt.Rows.Count - 1; i++)
                //{
                //    str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["InventoryDesc"].ToString() + "');");
                //    str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["Qty"].ToString() + ") ;");
                //}

                //str.Append(" var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));");
                //str.Append(" chart.draw(data, {height: 400, title: 'ItemwiseQty',");
                //str.Append("hAxis: {title: 'InventoryDesc', titleTextStyle: {color: 'green'}}");
                //str.Append("}); }");
                ////str.Append("$(window).resize(function(){ drawChart();});");
                //str.Append("</script>");
                //lt.Text = str.ToString().TrimEnd(',').Replace('*', '"');



                // Test code 

                str.Append(@"<script type=text/javascript> google.load( *visualization*, *1*, {packages:[*corechart*]});
                       google.setOnLoadCallback(drawChart);
                       function drawChart() 
                     {
                    var data = new google.visualization.DataTable();
                    data.addColumn('string', 'Month');
                    data.addColumn('number','TotSale');      

        data.addRows(" + dt.Rows.Count + ");");

                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["InventoryDesc"].ToString() + "');");
                    str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["Qty"].ToString() + ") ;");
                }

                str.Append(" var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));");
                str.Append(" chart.draw(data, {height: 400, title: 'HOURLY SALES REPORT',");
                str.Append("hAxis: {title: 'HOURS', titleTextStyle: {color: 'green'}}");
                str.Append("}); }");
                str.Append("$(window).resize(function(){ drawChart();});");
                str.Append("</script>");
                //lt.Text = str.ToString().TrimEnd(',').Replace('*', '"');

            }
            catch
            {
            }

        }

        //public void BindChart(string BranchAlias, string D1, string D2)
        //{
        //    DataTable dsChartData = new DataTable();
        //    StringBuilder strScript = new StringBuilder();

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

        //    MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
        //    Cn.Open();
        //    string sCmd;

        //    try
        //    {
        //        //dsChartData = GetChartData();
        //        sCmd = "Select InventoryDesc as InventoryDesc, Sum(Qty) as Qty" +
        //         " From " + BranchAlias + ".CRM " +
        //         " Where TransDate Between '" + D1 + "' and '" + D2 + "' " +
        //         " Group by InventoryDesc;";

        //        MySqlDataAdapter Ada = new MySqlDataAdapter(sCmd, Cn);
        //        ds = new DataSet();
        //        Ada.Fill(dsChartData);


        //        strScript.Append(@"<script type='text/javascript'>  
        //            google.load('visualization', '1', {packages: ['corechart']});</script>  

        //            <script type='text/javascript'>  
        //            function drawVisualization() {         
        //            var data = google.visualization.arrayToDataTable([  
        //            ['Inventory', 'Qty'],");


        //        foreach (DataRow row in dsChartData.Rows)
        //        {
        //            strScript.Append("['" + row["InventoryDesc"] + "'," + row["Qty"] + " ],");
        //        }


        //        //strScript.Append("['" + "Jan" + "'," + 50 + "],");

        //        //strScript.Append("['" + "Feb" + "'," + 60 + "," +
        //        //        50 + "," + 20 + "," + 30 + "],");

        //        strScript.Remove(strScript.Length - 1, 1);
        //        strScript.Append("]);");

        //        strScript.Append(" var options = { title : 'Itemwise', vAxis: {title: 'Qty'},  hAxis: {title: 'InventoryDesc'}, seriesType: 'bars', series: {3: {type: 'area'}} };");
        //        strScript.Append(" var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));  chart.draw(data, options); } google.setOnLoadCallback(drawVisualization);");
        //        strScript.Append(" </script>");

        //        ltScripts.Text = strScript.ToString();
        //    }
        //    catch
        //    {
        //    }
        //    finally
        //    {
        //        //dsChartData.Dispose();
        //        strScript.Clear();
        //    }
        //}


        public void BindChart(string BranchAlias, string D1, string D2)
        {
            DataTable dsChartData = new DataTable();
            StringBuilder strScript = new StringBuilder();

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

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string sCmd;

            try
            {
                //dsChartData = GetChartData();
                sCmd = "Select InventoryDesc as InventoryDesc, Sum(Qty) as Qty" +
                 " From " + BranchAlias + ".CRM " +
                 " Where TransDate Between '" + D1 + "' and '" + D2 + "' " +
                 " Group by InventoryDesc;";

                MySqlDataAdapter Ada = new MySqlDataAdapter(sCmd, Cn);
                ds = new DataSet();
                Ada.Fill(dsChartData);



                strScript.Append(@"<script type='text/javascript'>  
                    google.load('visualization', '1', {packages: ['corechart']});</script>  
  
                    <script type='text/javascript'>  
                    function drawVisualization() {         
                    var data = google.visualization.arrayToDataTable([  
                    ['Inventory', 'Qty'],");


                foreach (DataRow row in dsChartData.Rows)
                {
                    strScript.Append("['" + row["InventoryDesc"] + "'," + row["Qty"] + " ],");
                }


                //strScript.Append("['" + "Jan" + "'," + 50 + "],");

                //strScript.Append("['" + "Feb" + "'," + 60 + "," +
                //        50 + "," + 20 + "," + 30 + "],");

                strScript.Remove(strScript.Length - 1, 1);
                strScript.Append("]);");

                strScript.Append(" var options = { title : 'Itemwise', vAxis: {title: 'Qty'},  hAxis: {title: 'InventoryDesc'}, seriesType: 'bars', series: {3: {type: 'area'}} };");
                strScript.Append(" var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));  chart.draw(data, options); } google.setOnLoadCallback(drawVisualization);");
                strScript.Append(" </script>");

                //ltScripts.Text = str.ToString().TrimEnd(',').Replace('*', '"');
                // ltScripts.Text = strScript.ToString();
            }
            catch
            {
            }
            finally
            {
                //dsChartData.Dispose();
                strScript.Clear();
            }
        }

        //private void BindChart()
        //{
        //    DataTable dsChartData = new DataTable();
        //    StringBuilder strScript = new StringBuilder();

        //    try
        //    {
        //        //dsChartData = GetChartData();

        //        strScript.Append(@"<script type='text/javascript'>  
        //            google.load('visualization', '1', {packages: ['corechart']});</script>  

        //            <script type='text/javascript'>  
        //            function drawVisualization() {         
        //            var data = google.visualization.arrayToDataTable([  
        //            ['Month', 'Bolivia', 'Ecuador', 'Madagascar', 'Average'],");

        //        //foreach (DataRow row in dsChartData.Rows)
        //        //{
        //        //    strScript.Append("['" + row["Month"] + "'," + row["Bolivia"] + "," +
        //        //        row["Ecuador"] + "," + row["Madagascar"] + "," + row["Avarage"] + "],");
        //        //}


        //        strScript.Append("['" + "Jan" + "'," + 50 + "," +
        //                60 + "," + 80 + "," + 30 + "],");

        //        //strScript.Append("['" + "Feb" + "'," + 60 + "," +
        //        //        50 + "," + 20 + "," + 30 + "],");

        //        strScript.Remove(strScript.Length - 1, 1);
        //        strScript.Append("]);");

        //        strScript.Append("var options = { title : 'Monthly Coffee Production by Country', vAxis: {title: 'Cups'},  hAxis: {title: 'Month'}, seriesType: 'bars', series: {3: {type: 'area'}} };");
        //        strScript.Append(" var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));  chart.draw(data, options); } google.setOnLoadCallback(drawVisualization);");
        //        strScript.Append(" </script>");

        //        ltScripts.Text = strScript.ToString();
        //    }
        //    catch
        //    {
        //    }
        //    finally
        //    {
        //        //dsChartData.Dispose();
        //        //strScript.Clear();
        //    }
        //}

        protected void btnExcel_Click(object sender, EventArgs e)
        {




        }

        protected void btnGenerateReport_Click(object sender, EventArgs e)
        {

            //if (cmbBranches.Visible == false)
            //{
            //    BranchAlias = Session["Branch_HQ"].ToString();
            //}
            //else
            //{
            //    BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
            //    BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
            //}
            ////BindChart(BranchAlias, txtFromDt.Text, txtToDt.Text);
            //BindChart("da1", "2021-01-01", "2021-10-08");
            ////BindChart();

        }




        //[WebMethod]
        //public static List<object> GetChartData()
        //{

        //    DataTable dsChartData = new DataTable();
        //    StringBuilder strScript = new StringBuilder();

        //    string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        //    DataSet ds = new DataSet();

        //    MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
        //    Cn.Open();
        //    string sCmd ;


        //    sCmd = "Select InventoryDesc as InventoryDesc, Sum(Qty) as Qty" +
        //    " From da1.CRM " +
        //    " Where TransDate Between '2021-01-01' and '2021-10-11' " +
        //    " Group by InventoryDesc;";

        //    MySqlDataAdapter Ada = new MySqlDataAdapter(sCmd, Cn);
        //    ds = new DataSet();
        //    Ada.Fill(dsChartData);

        //    List<object> chartData = new List<object>();
        //    chartData.Add(new object[]
        //    {
        //        "InventoryDesc", "Qty"
        //    });



        //    foreach (DataRow row in dsChartData.Rows)
        //    {
        //        chartData.Add(new object[]
        //                    {
        //                row["InventoryDesc"], row["Qty"]
        //                    });
        //    }
        //    return chartData;

        //}




        public void GetChartData()
        {

            DataTable dsChartData = new DataTable();
            StringBuilder strScript = new StringBuilder();

            string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            DataSet ds = new DataSet();

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            string sCmd;


            sCmd = "Select InventoryDesc as InventoryDesc, Sum(Qty) as Qty" +
            " From da1.CRM " +
            " Where TransDate Between '2021-01-01' and '2021-10-11' " +
            " Group by InventoryDesc;";

            MySqlDataAdapter Ada = new MySqlDataAdapter(sCmd, Cn);
            ds = new DataSet();
            Ada.Fill(dsChartData);
            Session["dtpiechart"] = dsChartData;


        }



        [WebMethod]
        public static List<Data> GetData()
        {
            DataTable dt = new DataTable();
            dt = HttpContext.Current.Session["dtpiechart"] as DataTable;

            List<Data> dataList = new List<Data>();
            if (HttpContext.Current.Session["dtpiechart"] != null)
            {

                //dt = people;

                string cat = "";
                int val = 0;

                foreach (DataRow dr in dt.Rows)
                {
                    cat = dr[0].ToString();
                    val = Convert.ToInt32(dr[1]);
                    dataList.Add(new Data(cat, val));
                }


            }

            return dataList;

        }


        public class Data
        {
            public string ColumnName = "";
            public int Value = 0;

            public Data(string columnName, int value)
            {
                ColumnName = columnName;
                Value = value;
            }

        }


        protected void txtID_OnTextChange(object sender, EventArgs e)
        {
            txtName.Text = "";
        }
        protected void txtName_OnTextChange(object sender, EventArgs e)
        {
            txtID.Text = "";
        }

        //protected void cmbSelectby_SelectedIndexChanged(object sender, EventArgs e)
        //{
        //    hidDropdownValue.Value = cmbSelectby.SelectedValue;
        //    txtID.Text = "";
        //    txtName.Text = "";
            

        //}
        protected void btnApplySearch_Click(object sender, EventArgs e)
        {
            // Prevents XSS 
            // Prevents SQL Injection 

            Curr_BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);
            cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);



            if (cmbSelectby.Text == "AInventory")
            {
                grv.DataSource = null;
                grv.DataBind();
            }
            if (cmbSelectby.Text == "Category")
            {
                if (txtID.Text == "" && txtName.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select CategoryNumber as Row1,CategoryName as Row2,Description as Row3 from {0}.inventorycategories where categorynumber <> 1; ";
                    string command = string.Format(query, cd_BranchAlias);
                    Dt = ObjMain.GetData_DT(command);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtName.Text == "")
                {

                    DataTable Dt = new DataTable();
                    string query = "Select CategoryNumber as Row1,CategoryName as Row2,Description as Row3 from {0}.inventorycategories " +
                                    " where CategoryNumber like  CONCAT('%',@SearchVal,'%') ";
                    string command = string.Format(query, cd_BranchAlias);
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@SearchVal",txtID.Text.Trim()),

                    };
                    Dt = ObjMain.NewGetData_DT(command, parameters);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtID.Text == "")
                {
                    DataTable Dt = new DataTable();

                    string query = "Select CategoryNumber as Row1,CategoryName as Row2,Description as Row3 from {0}.inventorycategories " +
                                  " where CategoryName like  CONCAT('%',@SearchVal,'%')";

                    string command = string.Format(query, cd_BranchAlias);

                    MySqlParameter[] parameters =
                    {
                        new MySqlParameter("@SearchVal", txtName.Text.Trim()),

                    };
                    Dt = ObjMain.NewGetData_DT(command, parameters);

                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else
                {
                    DataTable Dt = new DataTable();

                    string query = "Select CategoryNumber as Row1,CategoryName as Row2,Description as Row3 from {0}.inventorycategories " +
                                 " where CategoryName like  CONCAT('%',@CategoryName,'%') and CategoryNumber like CONCAT('%',@CategoryNumber,'%') ";

                    string command = string.Format(query, cd_BranchAlias);
                    MySqlParameter[] parameters =
                   {
                        new MySqlParameter("@CategoryName", txtName.Text.Trim()),
                        new MySqlParameter("@CategoryNumber", txtID.Text.Trim()),

                    };
                    Dt = ObjMain.NewGetData_DT(command, parameters);

                    grv.DataSource = Dt;
                    grv.DataBind();
                }


            }

            if (cmbSelectby.Text == "SInventory")
            {
                if (txtID.Text == "" && txtName.Text == "")
                {


                    DataTable Dt = new DataTable();
                    string query = "Select Barcode as Row1,InventoryName as Row2,Description as Row3 from {0}.inventory where InventoryID<> 1";
                    string command = string.Format(query, cd_BranchAlias);
                    Dt = ObjMain.GetData_DT(command);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtName.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select Barcode as Row1,InventoryName as Row2,Description as Row3 from {0}.inventory" +
                                        " where Barcode like CONCAT('%',@SearchVal,'%') ";
                    string command = string.Format(query, cd_BranchAlias);

                    MySqlParameter[] parameters =
                    {
                        new MySqlParameter("@SearchVal", txtID.Text),
                    };
                    Dt = ObjMain.NewGetData_DT(command, parameters);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else if (txtID.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select Barcode as Row1,InventoryName as Row2,Description as Row3 from {0}.inventory" +
                                       " where InventoryName like CONCAT('%',@SearchVal,'%') ";
                    string command = string.Format(query, cd_BranchAlias);

                    MySqlParameter[] parameters =
                    {
                        new MySqlParameter("@SearchVal", txtName.Text),
                    };

                    Dt = ObjMain.NewGetData_DT(command, parameters);
                    grv.DataSource = Dt;
                    grv.DataBind();
                }
                else
                {
                    DataTable Dt = new DataTable();
                    string query = "Select Barcode as Row1,InventoryName as Row2,Description as Row3 from {0}.inventory" +
                                      " where InventoryName like CONCAT('%',@InventoryName,'%')  and barcode like CONCAT('%',@barcode,'%') ";
                    string command = string.Format(query, cd_BranchAlias);
                    MySqlParameter[] parameters =
                    {
                        new MySqlParameter("@InventoryName", txtName.Text.Trim()),
                        new MySqlParameter("@barcode", txtID.Text.Trim()),
                    };

                    Dt = ObjMain.NewGetData_DT(command, parameters);

                    grv.DataSource = Dt;
                    grv.DataBind();
                }
            }

            if (cmbSelectby.Text == "Customer")
            {
                if (txtID.Text == "" && txtName.Text == "")
                {
                    DataTable Dt = new DataTable();
                    string query = "Select Distinct CustomerNumber as Row1,'' as Row2,'' as Row3 from {0}.Trans;";
                    string command = string.Format(query, cd_BranchAlias);

                    MySqlParameter[] parameters =
                   {
                        new MySqlParameter("@Customernumber", txtID.Text.Trim()),

                   };
                    Dt = ObjMain.NewGetData_DT(command, parameters);

                    grv.DataSource = Dt;
                    grv.DataBind();
                }

                //Dt = ObjMain.NewGetData_DT(command, parameters);
                //grv.DataSource = Dt;
                //grv.DataBind();
            }

        

    }

    
        protected void CheckBox_Changed(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[2] { new DataColumn("Name"), new DataColumn("Country") });
            foreach (GridViewRow row in grv.Rows)
            {
                if (row.RowType == DataControlRowType.DataRow)
                {
                    CheckBox chkRow = (row.Cells[0].FindControl("chkSelect") as CheckBox);
                    if (chkRow.Checked)
                    {
                        string sRow1 = (row.Cells[1].FindControl("lblrow1") as Label).Text;
                        string sRow2 = (row.Cells[2].FindControl("lblrow2") as Label).Text;
                        string sRow3 = (row.Cells[2].FindControl("lblrow3") as Label).Text;
                        txtID.Text = sRow1;
                        txtName.Text = sRow2;
                        return;
                    }
                    else
                    {
                        txtID.Text = "";
                        txtName.Text = "";
                    }

                }
            }
        }



        #region     Webmethods for Charts   Added Karthik on 19-Aug-2024


       


        [WebMethod]
        public static string    Web_ItemWise_Qty_Count()
        {

           
          
            List<InventoryItem> _Itemwise_values = new List<InventoryItem>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select Name as Name,Sum(Qty) as Qty "
                                             + " From "+ HttpContext.Current.Session["ACTIVEDB"].ToString() + ".CRM Cross Join(Select Sum(Qty) as TQty, Sum(Amount) as TAmount "
                                             + " From "+ HttpContext.Current.Session["ACTIVEDB"].ToString() + ".CRM) T "
                                             + " Group by Barcode; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Itemwise_values.Add(new InventoryItem
                            {
                                Name = sdr["Name"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

                            });
                        }
                    }
                    con.Close();
                }

            }

            string value_result = JsonConvert.SerializeObject(_Itemwise_values);

            return value_result;

        }


        [WebMethod]
        public static string Web_ItemWise_Amount()
        {
            List<InventoryAmount> _Itemwise_values = new List<InventoryAmount>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select Name as Name, Round(Sum(Amount),2) Amount "
                                             + " From "+ HttpContext.Current.Session["ACTIVEDB"] + ".CRM Cross Join(Select  Sum(Amount) as TAmount "
                                             + " From "+ HttpContext.Current.Session["ACTIVEDB"] + ".CRM) T "
                                             + " Group by Barcode; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Itemwise_values.Add(new InventoryAmount
                            {
                                Name = sdr["Name"].ToString(),
                               Amount = Convert.ToInt32(sdr["Amount"])

                            });
                        }
                    }
                    con.Close();
                }

            }

            string value_result = JsonConvert.SerializeObject(_Itemwise_values);

            return value_result;

        }



        [WebMethod]
        public static string Web_Categorywise_Qty_Count()
        {
            List<CategoryItem> _Category_values = new List<CategoryItem>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select CategoryName as CategoryName, Sum(Qty) as Qty  "
                                            + " From " + HttpContext.Current.Session["ACTIVEDB"] + ".CRM Cross Join(Select Sum(Qty) as TQty, Sum(Amount) as TAmount "
                                            + " From " + HttpContext.Current.Session["ACTIVEDB"] + ".CRM) T "
                                            + " Group by CategoryName; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Category_values.Add(new CategoryItem
                            {
                               CategoryName  = sdr["CategoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

                            });
                        }
                    }
                    con.Close();
                }

            }

            string value_result = JsonConvert.SerializeObject(_Category_values);

            return value_result;

        }



        [WebMethod]
        public static string Web_Categorywise_Amount()
        {
            List<CategoryWiseAmount> _Category_values = new List<CategoryWiseAmount>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select CategoryName as CategoryName, "
                      + " Round(Sum(Amount), 2) Amount "
                      + " From " + HttpContext.Current.Session["ACTIVEDB"] + ".CRM Cross Join(Select Sum(Qty) as TQty, Sum(Amount) as TAmount" 
                      + " From " + HttpContext.Current.Session["ACTIVEDB"] + ".CRM) T "
                      + " Group by CategoryName; ", con))
                 {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Category_values.Add(new CategoryWiseAmount
                            {
                                CategoryName = sdr["CategoryName"].ToString(),
                               Amount = Convert.ToInt32(sdr["Amount"])

                            });
                        }
                    }
                    con.Close();
                }

            }

            string value_result = JsonConvert.SerializeObject(_Category_values);

            return value_result;

        }




        [WebMethod]
        public static string Web_Customerwise_Qty_Count()
        {
            List<ICustomers> _Category_values = new List<ICustomers>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select CustomerNumber, Sum(Qty) as Qty " 
                     + " From " + HttpContext.Current.Session["ACTIVEDB"] + ".CRM "
                     + " Group by CustomerNumber Order by CustomerNumber;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Category_values.Add(new ICustomers
                            {
                               CustomerNumber = sdr["CustomerNumber"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

                            });
                        }
                    }
                    con.Close();
                }

            }

            string value_result = JsonConvert.SerializeObject(_Category_values);

            return value_result;

        }

        [WebMethod]
        public static string Web_Customerwise_Amount()
        {
            List<ICustomersAmount> _Category_values = new List<ICustomersAmount>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select CustomerNumber , Round(Sum(Amount), 2) as Amount "
                                          + " From " + HttpContext.Current.Session["ACTIVEDB"] + ".CRM "
                                          + " Group by CustomerNumber Order by CustomerNumber; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Category_values.Add(new ICustomersAmount
                            {
                                CustomerNumber = sdr["CustomerNumber"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

                            });
                        }
                    }
                    con.Close();
                }

            }

            string value_result = JsonConvert.SerializeObject(_Category_values);

            return value_result;

        }


        #endregion

        #region Chart Property Models  Added Karthik on 19-Aug-2024
        public class InventoryItem
        {
            public string Name { get; set; }
            public int Qty { get; set; }

        }

        public class InventoryAmount
        {
            public string Name { get; set; }
            public int Amount { get; set; }

        }




        public class CategoryItem
        {
            public string CategoryName { get; set; }
            public int Qty { get; set; }
        }

        public class CategoryWiseAmount
        {
            public string CategoryName { get; set; }
            public int Amount { get; set; }
        }



        public class ICustomers
        {
            public string CustomerNumber { get; set; }
            public int Qty { get; set; }

        }

        public class ICustomersAmount
        {
            public string CustomerNumber { get; set; }
            public int Amount { get; set; }

        }



        #endregion

        protected void cmbSelectby_SelectedIndexChanged(object sender, EventArgs e)
        {
            
            txtID.Text = "";
            txtName.Text = "";
        }
    }
}