using CLS_BL;
using CLS_DL;
using CLS_PL;
using DocumentFormat.OpenXml.Presentation;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using static SUNWEB.InteractiveDashboard;

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

        string HQ = string.Empty;
        string Cm_id = string.Empty;
        string ISHQ = string.Empty;
        DataSet DS = new DataSet();

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

            PID = "56";    // This is for Interactive Dashboard Page 
                                  
            if (!IsPostBack)
            {
                
                var today = DateTime.Today;
                var month = new DateTime(today.Year, today.Month, 1);
                var first = month.AddMonths(0);
                var last = month.AddDays(-1);
               
               

                System.Web.HttpCookie ck = HttpContext.Current.Request.Cookies["STDT"];
                System.Web.HttpCookie ck2 = HttpContext.Current.Request.Cookies["ENDDT"];


                if (ck != null && ck2 != null)
                {
                    string D1 = ck.Values["STDT"];
                    string D2 = ck2.Values["ENDDT"];

                    txtStartDt.Text = D1;
                    txtEndDt.Text = D2;

                    Session["STDT"] = txtStartDt.Text;
                    Session["ENDDT"] = txtEndDt.Text;

                }
                else
                {
                    txtStartDt.Text = first.ToString("yyyy-MM-dd");
                    txtEndDt.Text = Curr_DateOnly;

                    Session["STDT"] = txtStartDt.Text;
                    Session["ENDDT"] = txtEndDt.Text;

                }


            }
            else 
            {
                System.Web.HttpCookie ck = HttpContext.Current.Request.Cookies["STDT"];
                System.Web.HttpCookie ck2 = HttpContext.Current.Request.Cookies["ENDDT"];
                if (ck != null && ck2 != null)
                {
                    string D1 = ck.Values["STDT"];
                    string D2 = ck2.Values["ENDDT"];

                    txtStartDt.Text = D1;
                    txtEndDt.Text=D2;

                    Session["STDT"] = txtStartDt.Text;
                    Session["ENDDT"] = txtEndDt.Text;

                }




            }


            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_RetailPayment"].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
                        {
                            //btnGetReports.Visible = true;

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

                        }


                    }

                }

            }
            else
            {

            }

            
        }

        //protected void Page_PreRender(object sender, EventArgs e)
        //{
        //    ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "text", "Callitback();", true);


        //}

        



        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_InteractiveDashboard = DtLicense.Rows[0]["INTERACTIVEDASHBOARD"].ToString();
                Session["L_InteractiveDashboard"] = L_InteractiveDashboard;
            }
            if (Session["L_InteractiveDashboard"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {
                Response.Redirect("DeniedPage.aspx");
            }
            else
            {

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

            }

        }


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

            }

        }

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

        //[WebMethod]
        //public static DataTable  Web_GetTotalSaleslist (string DB)
        //{
        //    DataTable dataTable= new DataTable();
        //    MYSQLCLOUD ObjMain = new MYSQLCLOUD();
        //    string RetVal =string.Empty;
        //    dataTable = ObjMain.GetData_DT("select  * from Portaldb.dashcount where userid=190 ;");
        //    return dataTable;



        public class Dashcount
        {
            public string Branch { get; set; }
            public int TotalsalesAmt { get; set; }

        }

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

        }


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

        }

        public class Inventory
        {
            public string InventoryName { get; set; }
            public int Amount  { get; set; }

        }


        public class QtyInventory
        {
            public string InventoryName { get; set; }
            public int Qty { get; set; }

        }

        public class datevalues
        {
            public string  StartDt { get; set; }
            public string EndDt { get; set; }
        }

        public class Branch
        {
            public string BranchFullName { get; set; }
            public int Amount  { get; set; }
            public int Qty  { get; set; }

        }

        public class InventorylIST
        {
            public string Branch { get; set; }
            public string BusinessDate { get; set; }
            public string Barcode { get; set; }
            public string Name { get; set; }
            public string InventoryDesc { get; set; }
            public string CategoryName { get; set; }
            public decimal Amount { get; set; }
            public int Qty { get; set; }

        }

        #region  1st and 2nd Level Chart Functions

        // Get Toltal Sales Branch wise 

        [WebMethod]
        public static string Web_GetTotalSaleslist( )
        {          
            List<Dashcount> _dashcount_values = new List<Dashcount>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select S.BranchID as BranchID, B.BranchFullname as Branch , Sum(Total) as Total From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryTotal as S INNER JOIN " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B on S.Branchid=B.Branchid Where BusinessDate Between '"+ HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "'  Group By S.BranchID Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _dashcount_values.Add(new Dashcount
                            {
                                Branch = sdr["Branch"].ToString(),
                                TotalsalesAmt = Convert.ToInt32(sdr["Total"])

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

            }

            string value_result = JsonConvert.SerializeObject(_dashcount_values);

            return value_result;

        }

        [WebMethod]
        public static string Web_GetTotalSaleslist5(string Startdt)
        {


            List<Dashcount> _dashcount_values = new List<Dashcount>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select S.BranchID as BranchID, B.BranchFullname as Branch , Sum(Total) as Total From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryTotal as S INNER JOIN " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B on S.Branchid=B.Branchid Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "'  Group By S.BranchID Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _dashcount_values.Add(new Dashcount
                            {
                                Branch = sdr["Branch"].ToString(),
                                TotalsalesAmt = Convert.ToInt32(sdr["Total"])

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

            }

            string value_result = JsonConvert.SerializeObject(_dashcount_values);

            return value_result;

        }


        [WebMethod]
        public static string GetBranchId(string BranchFullName)
        {
            MYSQLCLOUD ObjMain = new MYSQLCLOUD();
            string RetVal = "";
            RetVal = ObjMain.ExecuteScalar("Select Branchid from " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches where BranchFullName='" + BranchFullName.ToString().Trim() + "';");
            return RetVal;
        }


       



        [WebMethod]
        public static string WebCategoryListByBranchid(string BranchID)
        {
            List<Category> _categorylist_values = new List<Category>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select CategoryName as CategoryName, Sum(Amount) as Amount "
                 + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem "
                 + " Where BusinessDate Between '"+ HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                 + " And BranchID =" + BranchID + " "
                 + " Group By CategoryName "
                 + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _categorylist_values.Add(new Category
                            {
                                CategoryName = sdr["CategoryName"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string Category_result = JsonConvert.SerializeObject(_categorylist_values);

            return Category_result;

        }


        // Get the CategoryList All.. 
        [WebMethod]
        public static string WebCategoryListAll()
        {
            List<Category> _categorylist_values = new List<Category>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select CategoryName as CategoryName, Sum(Amount) as Amount "
                 + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem "
                 + " Where BusinessDate Between '"+ HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                 + " Group By CategoryName "
                 + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _categorylist_values.Add(new Category
                            {
                                CategoryName = sdr["CategoryName"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string Category_result = JsonConvert.SerializeObject(_categorylist_values);

            return Category_result;

        }

        // Category wise Qty Total 
        [WebMethod]
        public static string WebCategoryListQuantitywiseByBranchid(string BranchID)
        {
            List<QuantityCategory> _Qtywisecategorylist_values = new List<QuantityCategory>();
            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["Branch_HQ"].ToString() + ".SummaryItem "
                 +"  Where BusinessDate Between '"+ HttpContext.Current.Session["STDT"] + "' And '"+ HttpContext.Current.Session["ENDDT"] +  "' "
                 +"  And BranchID ="+ BranchID + " "
                 +"  Group By CategoryName "
                 +"  Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Qtywisecategorylist_values.Add(new QuantityCategory
                            {
                                CategoryName = sdr["CategoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string QtywiseCategory_result = JsonConvert.SerializeObject(_Qtywisecategorylist_values);

            return QtywiseCategory_result;

        }

       
        [WebMethod]
        public static string WebCategoryListQuantitywiseALL()
        {
            List<QuantityCategory> _Qtywisecategorylist_values = new List<QuantityCategory>();
            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["Branch_HQ"].ToString() + ".SummaryItem "
                 + "  Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                 + "  Group By CategoryName "
                 + "  Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Qtywisecategorylist_values.Add(new QuantityCategory
                            {
                                CategoryName = sdr["CategoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string QtywiseCategory_result = JsonConvert.SerializeObject(_Qtywisecategorylist_values);

            return QtywiseCategory_result;

        }





        // Inventory wise Total By Branchid 
        [WebMethod]
        public static string WebInventoryByAmountBranchid(string BranchID)
        {
            List<Inventory> _InventoryTot_values = new List<Inventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select Name as InventoryName, Sum(Amount) as Amount "
                      +" From  "+ HttpContext.Current.Session["Branch_HQ"].ToString() + " .SummaryItem "
                      +" Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '"+ HttpContext.Current.Session["ENDDT"] + "' "
                      +" And BranchID = "+ BranchID + " "
                      +" Group By Name "
                      +" Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _InventoryTot_values.Add(new Inventory
                            {
                               InventoryName = sdr["InventoryName"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string InventoryTot_result = JsonConvert.SerializeObject(_InventoryTot_values);

            return InventoryTot_result;

        }


        /// <summary>
        ///  Branch Wise InventoryBy Amount By Category   : Ohm 
        /// </summary>
        /// <param name="BranchID"></param>
        /// <returns></returns>

        [WebMethod]
        public static string WebInventoryByAmountBranchidAndCategory(string BranchID,string CategoryName)
        {
            List<Inventory> _InventoryTot_values = new List<Inventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select Name as InventoryName, Sum(Amount) as Amount "
                      + " From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + " .SummaryItem "
                      + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                      + " And BranchID = " + BranchID + " "
                      + " And Categoryname = '"+ CategoryName + "' "
                      + " Group By Name "
                      + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _InventoryTot_values.Add(new Inventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string InventoryTot_result = JsonConvert.SerializeObject(_InventoryTot_values);

            return InventoryTot_result;

        }




       // Branch Wise Inventory  By Amount By Category   : Ohm

        [WebMethod]
        public static string WebInventoryByQtyBranchidAndCategory(string BranchID, string CategoryName)
        {
            List<QtyInventory> _InventoryTot_values = new List<QtyInventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select Name as InventoryName, Sum(Qty) as Qty "
                      + " From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + " .SummaryItem "
                      + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                      + " And BranchID = " + BranchID + " "
                      + " And Categoryname = '" + CategoryName + "' "
                      + " Group By Name "
                      + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _InventoryTot_values.Add(new QtyInventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string InventoryTot_result = JsonConvert.SerializeObject(_InventoryTot_values);

            return InventoryTot_result;

        }





        // Inventory wise Total ALL
        [WebMethod]
        public static string WebInventoryByAmountALL()
        {
            List<Inventory> _InventoryTot_values = new List<Inventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select Name as InventoryName, Sum(Amount) as Amount "
                      + " From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + " .SummaryItem "
                      + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                      + " Group By Name "
                      + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _InventoryTot_values.Add(new Inventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string InventoryTot_result = JsonConvert.SerializeObject(_InventoryTot_values);

            return InventoryTot_result;

        }



        // Inventory wise Qty Total By Branchid 
        [WebMethod]
        public static string WebQtyInventoryWiseBranchid(string BranchID)
        {
            List<QtyInventory> _InventoryQtyTot_values = new List<QtyInventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("  Select Name as InventoryName, Sum(Qty) as Qty "
                    +"  From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem "
                    +"  Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                    +"  And BranchID = "+ BranchID + " "
                    +"  Group By Name "
                    +"  Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _InventoryQtyTot_values.Add(new QtyInventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string InventoryQtyTot_result = JsonConvert.SerializeObject(_InventoryQtyTot_values);

            return InventoryQtyTot_result;

        }



        // Inventory wise Qty Total By ALL
        [WebMethod]
        public static string WebQtyInventoryALL()
        {
            List<QtyInventory> _InventoryQtyTot_values = new List<QtyInventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("  Select Name as InventoryName, Sum(Qty) as Qty "
                    + "  From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem "
                    + "  Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                    + "  Group By Name "
                    + "  Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _InventoryQtyTot_values.Add(new QtyInventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string InventoryQtyTot_result = JsonConvert.SerializeObject(_InventoryQtyTot_values);

            return InventoryQtyTot_result;

        }
        #endregion

        #region 3rd Level Chart 


        //#1. InventoryItems and Amount By CategoryName 
        [WebMethod]
        public static string WebInventoryItemsAndAmountByCategoryName(string CategoryName)
        {
            List<Inventory> _Inventoryitems_values = new List<Inventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("  Select Name as InventoryName ,Sum(Amount) as Amount "
                      + " From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem "
                      + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                      + " And CategoryName = '" + CategoryName + "' "
                      + " Group By Name "
                      + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Inventoryitems_values.Add(new Inventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string Inventoryitems_result = JsonConvert.SerializeObject(_Inventoryitems_values);

            return Inventoryitems_result;

        }


        //#2. InventoryItems and Amount : top50  This for pageLoad Time 
        [WebMethod]
        public static string WebInventoryItemsAndAmountALL()
        {
            List<Inventory> _Inventoryitems_values = new List<Inventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("  Select  Name as InventoryName ,Sum(Amount) as Amount "
                              + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ". SummaryItem "
                              + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "'  "
                              + " Group By Name "
                              + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Inventoryitems_values.Add(new Inventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string Inventoryitems_result = JsonConvert.SerializeObject(_Inventoryitems_values);

            return Inventoryitems_result;

        }



        //#3. InventoryItems and Amount By QTY WISE 
        [WebMethod]
        public static string WebInventoryQtyWiseItemsAndAmountByCategoryName(string CategoryName)
        {
            List<QtyInventory> _Inventoryitems_values = new List<QtyInventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select Name as InventoryName ,Sum(Qty) as Qty "
                                + "  From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ". SummaryItem "
                                + "  Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                                + "  And CategoryName = '" + CategoryName + "'"
                                + "  Group By Name Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Inventoryitems_values.Add(new QtyInventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string Inventoryitems_result = JsonConvert.SerializeObject(_Inventoryitems_values);

            return Inventoryitems_result;

        }



        [WebMethod]
        public static string  QTYWiseProductListByCategoryNameAndBranch(string BranchID,string CategoryName)
        {
            List<QtyInventory> _Inventoryitems_values = new List<QtyInventory>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select Name as InventoryName ,Sum(Qty) as Qty "
                                + "  From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ". SummaryItem "
                                + "  Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                                + "  And CategoryName = '" + CategoryName + "' "
                                + "  AND BranchID ="+ BranchID + "  "
                                + "  Group By Name Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Inventoryitems_values.Add(new QtyInventory
                            {
                                InventoryName = sdr["InventoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string Inventoryitems_result = JsonConvert.SerializeObject(_Inventoryitems_values);

            return Inventoryitems_result;

        }










        #endregion

        #region  4th Level Charts ..

        [WebMethod]
        public static string Fourth_WebBranchAmountData(string Name)
        {
            List<Branch> _Inventroy_values = new List<Branch>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select Sum(S.Amount) as Amount ,B.BranchFullName as  Branch "
                                  + " From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem as S "
                                  + " INNER JOIN  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B ON S.Branchid = B.Branchid "
                                  + " Where S.BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' " 
                                  + " and  S.Name = '" + Name + "' "
                                  + " Group By S.Name, S.Branchid "
                                  + " Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Inventroy_values.Add(new Branch
                            {
                                BranchFullName = sdr["Branch"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string Inventories_result = JsonConvert.SerializeObject(_Inventroy_values);
            return Inventories_result;

        }


        [WebMethod]
        public static string Fourth_WebBranchQtyData(string Name)
        {
            List<Branch> _Inventroy_values = new List<Branch>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select Sum(S.Qty) as Qty ,B.BranchFullName as  Branch "
                                  + " From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem as S "
                                  + " INNER JOIN  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B ON S.Branchid = B.Branchid "
                                  + " Where S.BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                                  + " and  S.Name = '" + Name + "' "
                                  + " Group By S.Name, S.Branchid "
                                  + " Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Inventroy_values.Add(new Branch
                            {
                                BranchFullName = sdr["Branch"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string Inventories_result = JsonConvert.SerializeObject(_Inventroy_values);
            return Inventories_result;

        }



        [WebMethod]
        public static string BranchDat_Pie(string Name)
        {
            List<Branch> _Inventroy_values = new List<Branch>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select B.Branchfullname as Branch, Sum(Qty) as Qty,B.Branchid "
                                  + " From  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem as S "
                                  + " INNER JOIN  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B ON S.Branchid = B.Branchid "
                                  + " Where S.BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                                  + " And Name ='"+ Name + "' "
                                  + " Group By B.Branchid "
                                  + " Order by 1;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Inventroy_values.Add(new Branch
                            {
                                BranchFullName = sdr["Branch"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string Inventories_result = JsonConvert.SerializeObject(_Inventroy_values);
            return Inventories_result;

        }








        #endregion

        #region Date Session and Reset 


        [WebMethod]
        public static string GetDatesvalues(string startdate, string Enddate)
        {
            datevalues datevalues = new datevalues();
            datevalues.StartDt = startdate;
            datevalues.EndDt = Enddate;
            


            var myCookie = new System.Web.HttpCookie("STDT");
            myCookie["STDT"] = datevalues.StartDt;
            HttpContext.Current.Response.Cookies.Add(myCookie);

            var myCookie2 = new System.Web.HttpCookie("ENDDT");
            myCookie2["ENDDT"] = datevalues.EndDt;
            HttpContext.Current.Response.Cookies.Add(myCookie2);
           

            return datevalues.ToString();




        }

        protected void btnReset_Click(object sender, EventArgs e)
        {


            if (Request.Cookies["STDT"] != null)
            {
                Response.Cookies["STDT"].Expires = DateTime.Now.AddDays(-1);
            }

            if (Request.Cookies["ENDDT"] != null)
            {
                Response.Cookies["ENDDT"].Expires = DateTime.Now.AddDays(-1);
            }



            var today = DateTime.Today;
            var month = new DateTime(today.Year, today.Month, 1);
            var first = month.AddMonths(0);
            var last = month.AddDays(-1);
            txtStartDt.Text = first.ToString("yyyy-MM-dd");
            txtEndDt.Text = Curr_DateOnly;
            Session["STDT"] = txtStartDt.Text;
            Session["ENDDT"] = txtEndDt.Text;
            Page.Response.Redirect(Page.Request.Url.ToString(), true);

        }
        #endregion


        #region  Gridloading Functions

        //Currently not in use ..
        /// <summary>
        ///  This Function Loads the InventoryList 
        /// </summary>
        /// <param name="categoryname"></param>
        /// <returns></returns>
        [WebMethod] 
        public static string WebGrid1 (string CategoryName)
        {
            List<InventorylIST> _Grid_values = new List<InventorylIST>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select B.BranchFullName as Branch ,S.Barcode as Barcode ,S.Name as Name ,S.InventoryDesc as InventoryDesc,S.CategoryName as CategoryName,S.Amount as Amount,S.Qty as Qty ,  DATE_FORMAT(S.Businessdate,'%Y-%m-%d') as  Businessdate "
                     + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem as S INNER JOIN  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B on S.Branchid = B.Branchid "
                     + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                     + " And CategoryName = '"+ CategoryName + "' "
                   //  + " Group By Name "
                     + " Order by Name,Businessdate;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Grid_values.Add(new InventorylIST
                            {
                                Branch = sdr["Branch"].ToString(),
                                BusinessDate = sdr["Businessdate"].ToString(),
                                Barcode = sdr["Barcode"].ToString(),
                                Name= sdr["Name"].ToString(),
                                InventoryDesc= sdr["InventoryDesc"].ToString(),
                                CategoryName = sdr["CategoryName"].ToString(),
                                Amount= Convert.ToDecimal(String.Format("{0:0.00}", sdr["Amount"])),
                                Qty = Convert.ToInt32(sdr["Qty"])
                                

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

            }

            string InventoryGrid_result = JsonConvert.SerializeObject(_Grid_values);
            

            return InventoryGrid_result;

        }

        // Grid Load :  ohm
        [WebMethod]
        public static string WebGridwithBranchid(string CategoryName, string BranchID)
        {
            List<InventorylIST> _Grid_values = new List<InventorylIST>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select B.BranchFullName as Branch ,S.Barcode as Barcode ,S.Name as Name ,S.InventoryDesc as InventoryDesc,S.CategoryName as CategoryName,S.Amount as Amount,S.Qty as Qty ,DATE_FORMAT(S.Businessdate,'%Y-%m-%d') as  Businessdate "
                     + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem as S INNER JOIN  " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B on S.Branchid = B.Branchid "
                     + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                     + " And CategoryName = '" + CategoryName + "' "
                     + " And S.BranchID = " + BranchID + " "
                    // + " Group By Name "
                     + " Order by Name,Businessdate;", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Grid_values.Add(new InventorylIST
                            {
                                Branch = sdr["Branch"].ToString(),
                                BusinessDate = sdr["Businessdate"].ToString(),
                                Barcode = sdr["Barcode"].ToString(),
                                Name = sdr["Name"].ToString(),
                                InventoryDesc = sdr["InventoryDesc"].ToString(),
                                CategoryName = sdr["CategoryName"].ToString(),
                                Amount = Convert.ToDecimal(String.Format("{0:0.00}", sdr["Amount"])),
                                Qty = Convert.ToInt32(sdr["Qty"])


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

            }

            string InventoryGrid_result = JsonConvert.SerializeObject(_Grid_values);



            return InventoryGrid_result;

        }








        [WebMethod]
        public static string WebGrid11(string CategoryName)
        {
            string constr = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            DataSet ds = new DataSet();

            string   CommandText = "Select B.BranchFullName as Branch ,S.Barcode as Barcode ,S.Name as Name ,S.InventoryDesc as InventoryDesc,S.CategoryName as "
                           + " CategoryName,S.Amount as Amount,S.Qty as Qty, DATE_FORMAT(S.Businessdate,'%Y-%m-%d') as  Businessdate  "
                           + " From " + HttpContext.Current.Session["Branch_HQ"].ToString()+".SummaryItem "
                           + " as S INNER JOIN  " 
                           + " "+ HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B on S.Branchid = B.Branchid "
                           + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                           + " And CategoryName = '" + CategoryName + "' " 
                           + " Group By Name " 
                           + " Order by 1;)";
                                
                        MYSQLDB ObjMain = new MYSQLDB();
                        
                        ds = ObjMain.GetData_DS(CommandText);
                        return ds.GetXml();
                                 
         }



        // WebGrid By Branch and ProductWise 
        [WebMethod]
        public static string WebGridByBranchAndProducts(string BranchID, string Name)
        {

            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            List<InventorylIST> _Grid_values = new List<InventorylIST>();
            string InventoryGrid_result = string.Empty;
            using (MySqlConnection con = new MySqlConnection(constr))  
                {
                    using (MySqlCommand cmd = new MySqlCommand("Select B.Branchfullname as Branch, S.Barcode as Barcode ,S.Name as Name ,S.InventoryDesc as InventoryDesc,S.CategoryName as CategoryName,S.Amount as Amount,S.Qty as Qty, DATE_FORMAT(S.Businessdate,'%Y-%m-%d') as  Businessdate "
                         + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem as S  inner join " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B on B.Branchid = S.Branchid  "
                         + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                         + " And S.Name = '" + Name + "' "
                         + " And S.BranchID = " + BranchID + " "
                         + " order by Businessdate ; ", con))
                    {
                        con.Open();
                        using (MySqlDataReader sdr = cmd.ExecuteReader())
                        {
                            while (sdr.Read())
                            {
                                _Grid_values.Add(new InventorylIST
                                {
                                    Branch = sdr["Branch"].ToString(),
                                    BusinessDate = sdr["Businessdate"].ToString(),
                                    Barcode = sdr["Barcode"].ToString(),
                                    Name = sdr["Name"].ToString(),
                                    InventoryDesc = sdr["InventoryDesc"].ToString(),
                                    CategoryName = sdr["CategoryName"].ToString(),
                                    Amount = Convert.ToDecimal(String.Format("{0:0.00}", sdr["Amount"])),
                                    Qty = Convert.ToInt32(sdr["Qty"])


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



                }



                InventoryGrid_result = JsonConvert.SerializeObject(_Grid_values);
           
                return InventoryGrid_result;



        }



        [WebMethod]
        public static string WebGridByProductsAlone(string Name)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            List<InventorylIST> _Grid_values = new List<InventorylIST>();
            string InventoryGrid_result = string.Empty;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("Select B.Branchfullname as Branch, S.Barcode as Barcode ,S.Name as Name ,S.InventoryDesc as InventoryDesc,S.CategoryName as CategoryName,S.Amount as Amount,S.Qty as Qty, DATE_FORMAT(S.Businessdate,'%Y-%m-%d') as  Businessdate "
                     + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem as S  inner join " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B on B.Branchid = S.Branchid  "
                     + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                     + " And S.Name = '" + Name + "' "
                     + " Order by 1,Businessdate; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Grid_values.Add(new InventorylIST
                            {
                                Branch = sdr["Branch"].ToString(),
                                BusinessDate = sdr["Businessdate"].ToString(),
                                Barcode = sdr["Barcode"].ToString(),
                                Name = sdr["Name"].ToString(),
                                InventoryDesc = sdr["InventoryDesc"].ToString(),
                                CategoryName = sdr["CategoryName"].ToString(),
                                Amount = Convert.ToDecimal(String.Format("{0:0.00}", sdr["Amount"])),
                                Qty = Convert.ToInt32(sdr["Qty"])


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

               

            }


            InventoryGrid_result = JsonConvert.SerializeObject(_Grid_values);

            return InventoryGrid_result;
        }




        /// <summary>
        /// / This Function For get the top - For Default category on the First load .
        /// </summary>
        /// <param name="CategoryName"></param>
        /// <returns></returns>
        [WebMethod]
        public static string WebGetTopCategory()
        {
            List<QuantityCategory> _Qtywisecategorylist_values = new List<QuantityCategory>();
            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["Branch_HQ"].ToString() + ".SummaryItem "
                      + " Where BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
                      + " Group By CategoryName "
                      + " Order by Qty desc limit 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _Qtywisecategorylist_values.Add(new QuantityCategory
                            {
                                CategoryName = sdr["CategoryName"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string QtywiseCategory_result = JsonConvert.SerializeObject(_Qtywisecategorylist_values);
            return QtywiseCategory_result;
        }





        /// <summary>
        ///  Branchwise Amount List : This is load on the Category Selection 
        /// </summary>
        /// <returns></returns>
        [WebMethod]
        public static string  BranchwiseAmount(string CategoryName)
        {
            List<Branch> _BranchwiseAmountlist_values = new List<Branch>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select   B.Branchfullname as Branch,Sum(S.Amount) as Amount,S.Branchid "
             + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem AS S  inner join " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B ON S.Branchid = B.Branchid "
             + " Where S.BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
             + " And S.CategoryName = '"+ CategoryName + "' "
             + " Group By categoryName, Branchid "
             + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _BranchwiseAmountlist_values.Add(new Branch
                            {
                                BranchFullName = sdr["Branch"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string Amountvalues_result = JsonConvert.SerializeObject(_BranchwiseAmountlist_values);
            return Amountvalues_result;
        }


        /// <summary>
        ///  Branchwise Qty List : This is load on the Category Selection 
        /// </summary>
        /// <returns></returns>

        [WebMethod]
        public static string BranchwiseQty(string CategoryName)
        {
            List<Branch> _BranchwiseQtylist_values = new List<Branch>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select   B.Branchfullname as Branch,Sum(S.Qty) as Qty,S.Branchid "
             + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryItem AS S  inner join " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B ON S.Branchid = B.Branchid "
             + " Where S.BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
             + " And S.CategoryName = '"+ CategoryName+"' "
             + " Group By categoryName, Branchid "
             + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _BranchwiseQtylist_values.Add(new Branch
                            {
                                BranchFullName = sdr["Branch"].ToString(),
                                Qty = Convert.ToInt32(sdr["Qty"])

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

            }

            string Qtyvalues_result = JsonConvert.SerializeObject(_BranchwiseQtylist_values);
            return Qtyvalues_result;
        }



        /// <summary>
        ///  This Function for Pie Chart 
        /// </summary>
        /// <returns></returns>
        [WebMethod]
        public static string AllBranchWiseGrandTotalAmount()
        {
            List<Branch> _BranchwiseAmountlist_values = new List<Branch>();
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand(" Select  B.Branchfullname as Branch,Sum(Total) as Amount,S.Branchid "
             + " From " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".SummaryTotal AS S  inner join " + HttpContext.Current.Session["Branch_HQ"].ToString() + ".Branches as B ON S.Branchid = B.Branchid  "
             + " Where S.BusinessDate Between '" + HttpContext.Current.Session["STDT"] + "' And '" + HttpContext.Current.Session["ENDDT"] + "' "
             + " Group By S.Branchid "
             + " Order by 1; ", con))
                {
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            _BranchwiseAmountlist_values.Add(new Branch
                            {
                                BranchFullName = sdr["Branch"].ToString(),
                                Amount = Convert.ToInt32(sdr["Amount"])

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

            }

            string Amountvalues_result = JsonConvert.SerializeObject(_BranchwiseAmountlist_values);
            return Amountvalues_result;
        }







        #endregion




    }
}

