using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Web;
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 System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Services;
using CLS_BL;
using CLS_DL;
using CLS_PL;
using System.Web.UI.DataVisualization.Charting;


namespace SUNWEB
{
    public partial class Dashboard : 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 P = 0;
        Int32 Q = 0;
        //MySqlConnection conn = new MySqlConnection("Data Source=192.168.15.252;port=3306;Initial Catalog=che;User Id=root;password=suntoyo;");
        MySqlConnection conn = new MySqlConnection("Data Source=suntoyords.clx5rxtnm7i1.ap-southeast-1.rds.amazonaws.com;port=3306;Initial Catalog=masterdata;User Id=suntoyoroot;password=Suntoyo_123;");
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        string YearAlone = DateTime.Now.Year.ToString();
        string Monthid = DateTime.Now.Month.ToString();
        DateTime MonthstartDt;
        MYSQLDB ObjMain = new MYSQLDB();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable Dtt = new DataTable();
        DataTable DtLinechart = new DataTable();
        DataTable ReadResult = new DataTable();
        DataTable TempResult = new DataTable();
        DataTable DtRead = new DataTable();

        DataTable DtPriceFlag = new DataTable();
        DataTable DtinventoryCnt = new DataTable();


        StringBuilder str = new StringBuilder();

        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string ChkValue = string.Empty;
        string BranchAlias = string.Empty;

        // 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 Cd_BranchName = string.Empty;
        string Rcd_id = string.Empty;
        string RPortalid = 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;
        // GTotal Sale : Variables 
        decimal GotSaleAmt;
        decimal JanAmt;
        decimal JanGTot;
        decimal FebAmt;
        decimal FebGTot;
        decimal MarAmt;
        decimal MarGTot;
        decimal AprAmt;
        decimal AprGTot;
        decimal MayAmt;
        decimal MayGTot;
        decimal JuneAmt;
        decimal JunGTot;
        decimal JulAmt;
        decimal JulGTot;
        decimal AugAmt;
        decimal AugGTot;
        decimal SepAmt;
        decimal SepGTot;
        decimal OctAmt;
        decimal OctGTot;
        decimal NovAmt;
        decimal NovGTot;
        decimal DecAmt;
        decimal DecGTot;
        string MonthName;
        string DashCnt;
        string UTValue = string.Empty;
        string CurMonth = string.Empty;
       
        DataTable DtCSun = new DataTable(); 
        string RBranchid = string.Empty;
        string RBranchNam = string.Empty;
        string RData_Constring = string.Empty;
        string RUserid = string.Empty;
        string RMonth = string.Empty;
        string RTotalsalesAmt = string.Empty;

        // For DashBoard Variables 

        string DBranchId = string.Empty;
        string DBranchName = string.Empty;
        string DMonthly = string.Empty;
        string DDaily = string.Empty;
        string DUDATEDTIME = string.Empty;
        string ChkDashBoard = string.Empty;
        string L_RetailDashboard = string.Empty;

        // DashboardVariable ;
        decimal TotMonthlyAmt;
        decimal TotaCurrAmt;
        DataTable DTFinalSalesData = new DataTable();
        DataTable DTFinalSalesAmt = new DataTable();
        DataTable DTTemp = new DataTable();

        DataTable DtResult = new DataTable();
        DataTable Dtadd = new DataTable();
        // Chart Variables ..
        DataTable dt = new DataTable("Chart");
        string X, Y;
        string GraphWidth = "900";
        string GraphHeight = "440";
        string[] color = new string[12];
        string LastUpdatedTM = string.Empty;
        string Ck = string.Empty;
        string ActiveCallvalue = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            PID = "1";
            DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, "2");
            if (DT.Rows.Count == 0)
            {
                #region  Validation Between HQ Branch and Normal Branch 
                string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                #endregion
                string _branchid = ObjSel.Get_BranchId_By_BranchName(ActiveCallvalue, Session["Branch_HQ"].ToString());
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, _branchid);
            }
            else
            {
            }
            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=2 and cm_id=" + Session["CM_ID"] .ToString()+ ";");
            RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
            DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);

            if (DtLicense.Rows.Count > 0)
            {
                L_RetailDashboard = DtLicense.Rows[0]["RETAILDASHBOARD"].ToString();
                Session["L_RetailDashboard"] = L_RetailDashboard;
            }

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



             // This is For RetailsDashboard 
             cmbBranches.Visible = false;  // Disable BranchDetails:
           
            if (!IsPostBack)
            {
                txtStartDt.Text = Curr_DateOnly;
                txtEndDt.Text = Curr_DateOnly;
               
            }
            MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx",false);
            }
            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {

                if (!IsPostBack)
                { 
              
                LoadBranchName4SuperAdmin();
                //ChkDashBoard = ObjMain.ExecuteScalar("select * from " + Session["Branch_HQ"].ToString() + ".dashboardcount;");
                ChkDashBoard = ObjMain.ExecuteScalar("SELECT * FROM information_schema.tables WHERE table_schema = '" + Session["Branch_HQ"].ToString() + "' AND table_name = 'DashboardCount' LIMIT 1;");
                    

                if (ChkDashBoard != "")
                {
                    
                    LoadCurr_MonSaleTotalNew(Session["Branch_HQ"].ToString());
                    IsDashBoard();


                    LoadBranchTotalAG();
                    BindChart();
                  //  InsertNewTables();
                }
                else
                {
                    
                    LoadBranchTotalValues();
                    LoadBranchTotalForChart();
                    BindChart();
                    //InsertNewTables();
                }

               
              
                }
            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                  
                    LoadBranchName4SuperAdmin();
                    ChkDashBoard = ObjMain.ExecuteScalar("select * from " + Session["Branch_HQ"].ToString() + ".dashboardcount;");
                    if (ChkDashBoard != "")
                    {
                        
                        LoadCurr_MonSaleTotalNew(Session["Branch_HQ"].ToString());
                        IsDashBoard();
                        LoadBranchTotalAG();
                        BindChart();
                        
                    }
                    else
                    {
                        

                        LoadBranchTotalValues();
                        LoadBranchTotalForChart();
                        BindChart();
                    }
                  
                }
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                System.Threading.Thread.Sleep(2000);
                LoadBranchName4SuperAdmin();
                ChkDashBoard = ObjMain.ExecuteScalar("select * from " + Session["Branch_HQ"].ToString() + ".dashboardcount;");
                if (ChkDashBoard != "")
                {
                    
                    LoadCurr_MonSaleTotalNew(Session["Branch_HQ"].ToString());
                    IsDashBoard();
                    LoadBranchTotalAG();
                    BindChart();
                }
                else
                {

                    LoadBranchTotalValues();
                    LoadBranchTotalForChart();
                    BindChart();
                }
               
            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == false))
            {
                Response.Redirect("SummaryReport.aspx", false);
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;
                lblBranchAliasName.Text = "Welcome" + "&nbsp;&nbsp;" + Session["Branch_HQ"].ToString() + "&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;
                    // Binding Report Functions 
                    System.Threading.Thread.Sleep(2000);

                }

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

                }

            }
            else
            {

            }
            {

            }

        }

        private void ConfigureColors()
        {
            color[0] = "#104E8B	";
            color[1] = "#104E8B	";
            color[2] = "#104E8B	";
            color[3] = "#104E8B	";
            color[4] = "#104E8B";
            color[5] = "#104E8B	";
            color[6] = "#104E8B	";
            color[7] = "#104E8B	";
            color[8] = "#104E8B	";
            color[9] = "#104E8B	";
            color[10] = "#104E8B	";
            color[11] = "#104E8B	";
        }

        
        //private void CreateBarGraph()
        //{
        //    string strCaption = "Month wise Total Sales Report";
        //    //string strSubCaption = "Monthwise Total Sale";
        //    string xAxis = "Month";
        //    string yAxis = "TotalsalesAmt";

        //    //strXML will be used to store the entire XML document generated
        //    string strXML = null;

        //    //Generate the graph element 
        //    strXML = @"<graph caption='" + strCaption + @"' subCaption='" + "" + @"' decimalPrecision='0' 
        //                  pieSliceDepth='50' formatNumberScale='0'
        //                  xAxisName='" + xAxis + @"' yAxisName='" + yAxis + @"' rotateNames='1' >";


        //    int i = 0;

        //    foreach (DataRow dr2 in DtResult.Rows)
        //    {
        //        strXML += "<set name='" + dr2[0].ToString() + "' value='" + dr2[1].ToString() + "' color='" + color[i] + @"'  link=&quot;JavaScript:myJS('" + dr2["Month"].ToString() + ", " + dr2["TotalsalesAmt"].ToString() + "'); &quot;/>";
        //        i++;
        //    }
        //    //Finally, close <graph> element
        //    strXML += "</graph>";

        //    FCLiteral2.Text = InfoSoftGlobal.FusionCharts.RenderChartHTML(
        //        "FusionCharts/FCF_Column2D.swf",                                               // Path to chart's SWF
        //        "",                                                                            // Leave blank when using Data String method
        //        strXML,                                                                        // xmlStr contains the chart data
        //        "mygraph1",                                                                    // Unique chart ID
        //        GraphWidth, GraphHeight,                                                       // Width & Height of chart
        //        false
        //        );
        //}
        public void LoadBranchName()
        {
            DT = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            cmbBranches.DataSource = DT;
            cmbBranches.DataTextField = "BranchName";
            cmbBranches.DataValueField = "Branchid";
            cmbBranches.DataBind();
            cmbBranches.Items.Insert(0, "Select Branch");
        }
        public void LoadBranchName4SuperAdmin()
        {
            DT = ObjUserBL.GetBranchOnSuperAdmin8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            cmbBranches.DataSource = DT;
            cmbBranches.DataTextField = "Branchfullname";
            cmbBranches.DataValueField = "Branchid";
            cmbBranches.DataBind();
            cmbBranches.Items.Insert(0, "Select Branch");
        }
        public void LoadBranchNamewithPortalTypeid()
        {
            DT = ObjUserBL.GetBranchOnLoginPortalid(Session["Branch_HQ"].ToString());
            cmbBranches.DataSource = DT;
            cmbBranches.DataTextField = "BranchName";
            cmbBranches.DataValueField = "Branchid";
            cmbBranches.DataBind();
            cmbBranches.Items.Insert(0, "Select Branch");
        }
        protected void btnGetReports_Click(object sender, EventArgs e)
        {
           
            //DTFinalSalesData.Columns.Remove("Branch");
            //DTFinalSalesData.Columns.Remove("TotalCurrentSale");
            //DTFinalSalesData.Columns.Remove("TotalMonthlySale");
            //DTFinalSalesData.Columns.Remove("cd_BranchName");
            DTFinalSalesData.Clear();
            lblMsgTotalSelection.Text = "Search Result - Total Sale Selection Between" + "&nbsp;&nbsp;&nbsp;" + txtStartDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtEndDt.Text;
            LoadBranchTotalValues();
            LoadBranchTotalForChart();
            BindChart();
        }
        protected void cmbsel_SelectedIndexChanged(object sender, EventArgs e)
        {
        }



        /// <summary>
        /// This Funcation 
        /// Not in use At the Moment. 
        /// i did the calculation In Sp. 
        /// </summary>
        public void UPT_CurrMonthTOT()
        {
            string QrryUpt = "select  sum(b.MonthlySale)  from " + Session["Branch_HQ"].ToString() + ".dashboardcount  as b inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid  where S.userid=" + Session["UserId"].ToString() + "   and Multi_Access<>'N' ";
            UTValue = ObjMain.ExecuteScalar(QrryUpt);

            CurMonth = ObjMain.ExecuteScalar("SELECT substring(MONTHNAME(CURDATE()),1,3) as CurMonth;");
            K = ObjMain.ExecuteNonQuery("Update  portaldb.dashcount  set TotalsalesAmt=" + UTValue + " where Month='" + CurMonth + "' AND Userid=" + Session["UserId"].ToString() + " ");


        }
        public void IsDashBoard()
        {

            string qw = "Select IsDashBoard from Portaldb.Usermaster WHERE Userid=" + Session["UserId"].ToString() + " ";
            Ck = ObjMain.ExecuteScalar(qw);
            if (Ck == "")
            {
                string QryIsDashBoard = "Update Portaldb.Usermaster set  IsDashBoard='Y' WHERE Userid=" + Session["UserId"].ToString() + " ";
                L = ObjMain.ExecuteNonQuery(QryIsDashBoard);
            }

            

        }

        public void LoadBranchTotalValues()
        {

            System.Threading.Thread.Sleep(1000);

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            //DT = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());       (Previous Live)
            DT = ObjUserBL.GetBranchOnLoginValues8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());       
            if (DT.Rows.Count > 0)
            {
               for (int i = 0; i < DT.Rows.Count; i++)
                {
                    DataRow dr = DT.Rows[i];
                    RBranchid = dr["Branchid"].ToString();
                    RBranchNam = dr["BranchName"].ToString();
                    LoadCurr_MonSaleTotal(RBranchNam, txtStartDt.Text, txtEndDt.Text);
                    RData_Constring = dr["Datapath"].ToString();
                   }
           }
            dtDashboardList.DataSource = DTFinalSalesData;
            dtDashboardList.DataBind();
        }

        // Created On 20-03-20 - Karthi 
        public void LoadCurr_MonSaleTotalNew(string Branch_Name)
        {
            
            //dt = ObjSel.GetDashboard(Session["Branch_HQ"].ToString(), Session["UserId"].ToString()); // Live 
            dt = ObjSel.GetDashboard8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());   // Test on 12-09-20

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    DBranchId = dr["Branchid"].ToString();
                    DBranchName = dr["BranchNames"].ToString();
                    DDaily = dr["DailySales"].ToString();
                    DMonthly = dr["MonthlySale"].ToString();
                    DUDATEDTIME = dr["UPDATEDTIME"].ToString();
                    if (DTFinalSalesData.Rows.Count > 0)
                    {
                        DataRow Dr = DTFinalSalesData.NewRow();
                        Dr["Branch"] = DBranchName;
                        Dr["TotalCurrentSale"] = DDaily;
                        Dr["TotalMonthlySale"] = DMonthly;
                        Cd_BranchName = ObjMain.ExecuteScalar("SELECT cd_branchname from portaldb.companydetails where cd_branchalias='" + DBranchName + " '");
                        Dr["cd_BranchName"] = Cd_BranchName;
                        Dr["LastUpdatedTM"] = DUDATEDTIME;
                        DTFinalSalesData.Rows.Add(Dr);
                    }
                    else
                    {
                        DTFinalSalesData.Columns.Add("Branch", typeof(string));
                        DTFinalSalesData.Columns.Add("TotalCurrentSale", typeof(decimal));
                        DTFinalSalesData.Columns.Add("TotalMonthlySale", typeof(decimal));
                        DTFinalSalesData.Columns.Add("cd_BranchName", typeof(string));
                        DTFinalSalesData.Columns.Add("LastUpdatedTM", typeof(string));
                        DataRow Dr = DTFinalSalesData.NewRow();
                        Dr["Branch"] = DBranchName;
                        Dr["TotalCurrentSale"] = DDaily;
                        Dr["TotalMonthlySale"] = DMonthly;
                        Cd_BranchName = ObjMain.ExecuteScalar("SELECT cd_branchname from portaldb.companydetails where cd_branchalias='" + DBranchName + " '");
                        Dr["cd_BranchName"] = Cd_BranchName;
                        Dr["LastUpdatedTM"] = DUDATEDTIME;
                        DTFinalSalesData.Rows.Add(Dr);
                    }

                }

                dtDashboardList.DataSource = DTFinalSalesData;
                dtDashboardList.DataBind();
            }
            
        }
        public void LoadCurr_MonSaleTotal(string Branch_Name, string Stdt, string EndDt)
        {
            // Get the Total Monthly Sale 
            MySqlConnection Cnn = new MySqlConnection(ConStr_Rds);
            Cnn.Open();
            string QryMonthTots = "select format(ifnull(sum(tp.PaymentAmount),0),2,'ta_in')  as Totalsales,Monthname(tp.BusinessDate) as Month from " + Branch_Name + ".transpayment tp left join " + Branch_Name + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + YearAlone + " and  MONTH(tp.BusinessDate)=" + Monthid + " group by monthname(tp.BusinessDate) order by month(tp.BusinessDate);";
            MySqlCommand Cmd = new MySqlCommand(QryMonthTots, Cnn);
            TotMonthlyAmt = Convert.ToDecimal(Cmd.ExecuteScalar());
            // Get the Curr Total Sale
            string QryCurrTotSal = "select format(ifnull(sum(tp.PaymentAmount),0),2,'ta_in') as total from " + Branch_Name + ".transpayment tp  left join " + Branch_Name + ".trans t on tp.TransID=t.TransID where tp.BusinessDate between '" + Stdt + "' and '" + EndDt + "'  and t.TransStatus in (2,3,4);";
            MySqlCommand Cmd2 = new MySqlCommand(QryCurrTotSal, Cnn);
            TotaCurrAmt = Convert.ToDecimal(Cmd2.ExecuteScalar());

            string QryUpdateTM = "SELECT DATE_FORMAT(TransTime, '%d-%M-%Y %H:%i:%s') as TransTime FROM " + Branch_Name + ".trans ORDER BY TransID DESC LIMIT 1";
            MySqlCommand Cmd3 = new MySqlCommand(QryUpdateTM, Cnn);
            LastUpdatedTM =Convert.ToString(Cmd3.ExecuteScalar());
            if (DTFinalSalesData.Rows.Count > 0)
            {
                DataRow Dr = DTFinalSalesData.NewRow();
                Dr["Branch"] = RBranchNam;
                Dr["TotalCurrentSale"] = TotaCurrAmt;
                Dr["TotalMonthlySale"] = TotMonthlyAmt;
                Cd_BranchName = ObjMain.ExecuteScalar("SELECT cd_branchname from portaldb.companydetails where cd_branchalias='" + RBranchNam + " '");
                Dr["cd_BranchName"] = Cd_BranchName;
                Dr["LastUpdatedTM"] = LastUpdatedTM;
                DTFinalSalesData.Rows.Add(Dr);
            }
            else
            {
                DTFinalSalesData.Columns.Add("Branch", typeof(string));
                DTFinalSalesData.Columns.Add("TotalCurrentSale", typeof(decimal));
                DTFinalSalesData.Columns.Add("TotalMonthlySale", typeof(decimal));
                DTFinalSalesData.Columns.Add("cd_BranchName", typeof(string));
                DTFinalSalesData.Columns.Add("LastUpdatedTM", typeof(string));
                DataRow Dr = DTFinalSalesData.NewRow();
                Dr["Branch"] = RBranchNam;
                Dr["TotalCurrentSale"] = TotaCurrAmt;
                Dr["TotalMonthlySale"] = TotMonthlyAmt;
                Cd_BranchName = ObjMain.ExecuteScalar("SELECT cd_branchname from portaldb.companydetails where cd_branchalias='" + RBranchNam + " '");
                Dr["cd_BranchName"] = Cd_BranchName;
                Dr["LastUpdatedTM"] = LastUpdatedTM;
                DTFinalSalesData.Rows.Add(Dr);
            }

        }

        public void InsertNewTables()
        {
            DataTable DtX = new DataTable();
            DtX = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            if (DT.Rows.Count > 0)
            {
                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    DataRow dr = DT.Rows[i];
                    RBranchid = dr["Branchid"].ToString();
                    RBranchNam = dr["BranchName"].ToString();

                    // Call The PriceFlag Table 
                    // Call the TimeScheduleFlag

                    string chk_Condition =ObjMain.ExecuteScalar("select IsWebOrderApp from portaldb.companydetails where  cd_branchAlias='"+ RBranchNam + "';");
                    if (chk_Condition == "Y")
                    {
                        INS_PriceFlage(RBranchNam);
                        INS_ScheduleFlag(RBranchNam);
                        INS_WebStock(RBranchNam);
                    }
                    else
                    { 

                    }
                    


                }
            }



        }


        public void INS_PriceFlage(string sdb)
        {

            // Local Variable 
            DtinventoryCnt = null;
            DtPriceFlag = null;

            string Inventoryid = string.Empty;
            string Barcode = string.Empty;

            //#1 Check the PriceFlage table Record  count 

            string PriceFlagQuery = "select * from {0}.PriceFlag;";
            string Price_command = string.Format(PriceFlagQuery, sdb);
            DtPriceFlag = ObjMain.GetData_DT(Price_command);

            //#2 Get Inventory Table Record Count 

            string InventoryRowCountQuery = "select * from {0}.Inventory; ";
            string Inventory_command = string.Format(InventoryRowCountQuery, sdb);
            DtinventoryCnt = ObjMain.GetData_DT(Inventory_command);

            //#3. If PriceFlag  is NULL 
            if (DtPriceFlag.Rows.Count == 0)
            {
                // Call Insert Function

                string ReadInventoryids = "select Inventoryid,Barcode from {0}.inventory order by Inventoryid; ";
                string commandReads = string.Format(ReadInventoryids, sdb);
                DtRead = ObjMain.GetData_DT(commandReads);

                for (int i = 0; i < DtRead.Rows.Count; i++)
                {
                    DataRow dr = DtRead.Rows[i];

                    Inventoryid = dr["Inventoryid"].ToString();
                    Barcode = dr["BarCode"].ToString();

                    // #1.2 IF Not Exist   
                    // Call Insert  Function : PriceFlag
                    #region   Insert Portion
                    string Ins_PriceFlag = " insert into {0}.PriceFlag (Inventoryid,Barcode,Price_Flag,Price2_Flag,Price3_Flag,Price4_Flag)" +
                                           " values " +
                                           " (@Inventoryid,@Barcode,@Price_Flag,@Price2_Flag,@Price3_Flag,@Price4_Flag);";
                    string Ins_command = string.Format(Ins_PriceFlag, sdb);
                    MySqlParameter[] parameters =
                        {
                             new MySqlParameter("@Inventoryid",Inventoryid),
                             new MySqlParameter("@Barcode",Barcode),
                             new MySqlParameter("@Price_Flag","0"),
                             new MySqlParameter("@Price2_Flag","0"),
                             new MySqlParameter("@Price3_Flag","0"),
                             new MySqlParameter("@Price4_Flag","0"),
                        };
                    Q = ObjMain.ExecuteQuery(Ins_command, parameters);




                    #endregion
                }


            }
            //#4. If PriceFlag  < Inventory 
            else if (DtPriceFlag.Rows.Count < DtinventoryCnt.Rows.Count)
            {

                string CheckA = string.Empty;

                //#1.1 IF Inventoryid is exist  in  PriceFlag Table Ignore to Insert 

                string ReadInventoryids = "select Inventoryid,Barcode from {0}.inventory order by Inventoryid; ";
                string commandReads = string.Format(ReadInventoryids, sdb);
                DtRead = ObjMain.GetData_DT(commandReads);

                for (int i = 0; i < DtRead.Rows.Count; i++)
                {
                    DataRow dr = DtRead.Rows[i];

                    Inventoryid = dr["Inventoryid"].ToString();
                    Barcode = dr["BarCode"].ToString();

                    // #1.2 IF Inventoryid is  Exist  in PriceFlag  Ignore to Insert  
                    // Check the Inventoryid in 'PriceFlag'

                    string ReadInventoryidOnPriceFlag = "select Inventoryid from {0}.PriceFlag where  Inventoryid=@Inventoryid ; ";
                    string command_ReadPriceflag = string.Format(ReadInventoryidOnPriceFlag, sdb);
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@Inventoryid",Inventoryid),
                    };
                    CheckA = ObjMain.NewExecuteScalar(command_ReadPriceflag, parameters);

                    if (CheckA != "")
                    {
                        // Record Already Exist In PriceFlag : Ignore for insert 
                    }
                    else
                    {
                        // Call Insert  Function : PriceFlag  
                        #region   Insert Portion
                        string Ins_PriceFlag = " insert into {0}.PriceFlag (Inventoryid,Barcode,Price_Flag,Price2_Flag,Price3_Flag,Price4_Flag)" +
                                               " values " +
                                               " (@Inventoryid,@Barcode,@Price_Flag,@Price2_Flag,@Price3_Flag,@Price4_Flag);";
                        string Ins_command = string.Format(Ins_PriceFlag, sdb);
                        MySqlParameter[] parametersM =
                            {
                             new MySqlParameter("@Inventoryid",Inventoryid),
                             new MySqlParameter("@Barcode",Barcode),
                             new MySqlParameter("@Price_Flag","0"),
                             new MySqlParameter("@Price2_Flag","0"),
                             new MySqlParameter("@Price3_Flag","0"),
                             new MySqlParameter("@Price4_Flag","0"),
                        };
                        Q = ObjMain.ExecuteQuery(Ins_command, parametersM);

                        #endregion

                    }


                }



            }
            else
            {
                // Nothing To :  Run 
            }




        }
        public void INS_ScheduleFlag(string sdb)
        {

            // Local Variable 

            string Inventoryid = string.Empty;
            string Barcode = string.Empty;
            DataTable DtScheduleFlag = new DataTable();
            DtinventoryCnt = null;
            DtScheduleFlag = null;


            //#1 Check the PriceFlage table Record  count 

            string TimescheduleFlagQuery = "select * from {0}.timescheduleFlag;";
            string schedule_command = string.Format(TimescheduleFlagQuery, sdb);
            DtScheduleFlag = ObjMain.GetData_DT(schedule_command);

            //#2 Get Inventory Table Record Count 

            string InventoryRowCountQuery = "select * from {0}.Inventory; ";
            string Inventory_command = string.Format(InventoryRowCountQuery, sdb);
            DtinventoryCnt = ObjMain.GetData_DT(Inventory_command);

            //#3. If PriceFlag  is NULL 
            if (DtScheduleFlag.Rows.Count == 0)
            {
                // Call Insert Function

                string ReadInventoryids = "select Inventoryid,Barcode from {0}.inventory order by Inventoryid; ";
                string commandReads = string.Format(ReadInventoryids, sdb);
                DtRead = ObjMain.GetData_DT(commandReads);

                for (int i = 0; i < DtRead.Rows.Count; i++)
                {
                    DataRow dr = DtRead.Rows[i];

                    Inventoryid = dr["Inventoryid"].ToString();
                    Barcode = dr["BarCode"].ToString();

                    // #1.2 IF Not Exist   
                    // Call Insert  Function : PriceFlag
                    #region   Insert Portion
                    string Ins_ScheduleFlag = " insert into {0}.timescheduleFlag (Inventoryid,Barcode,IsCommonUpdate)" +
                                              " values " +
                                              " (@Inventoryid,@Barcode,@IsCommonUpdate);";
                    string Ins_command = string.Format(Ins_ScheduleFlag, sdb);
                    MySqlParameter[] parameters =
                        {
                             new MySqlParameter("@Inventoryid",Inventoryid),
                             new MySqlParameter("@Barcode",Barcode),
                             new MySqlParameter("@IsCommonUpdate","0"),
                        };
                    Q = ObjMain.ExecuteQuery(Ins_command, parameters);




                    #endregion
                }


            }
            //#4. If ScheduleFlag  < Inventory 
            else if (DtScheduleFlag.Rows.Count < DtinventoryCnt.Rows.Count)
            {

                string CheckA = string.Empty;

                //#1.1 IF Inventoryid is exist  in  PriceFlag Table Ignore to Insert 

                string ReadInventoryids = "select Inventoryid,Barcode from {0}.inventory order by Inventoryid; ";
                string commandReads = string.Format(ReadInventoryids, sdb);
                DtRead = ObjMain.GetData_DT(commandReads);

                for (int i = 0; i < DtRead.Rows.Count; i++)
                {
                    DataRow dr = DtRead.Rows[i];

                    Inventoryid = dr["Inventoryid"].ToString();
                    Barcode = dr["BarCode"].ToString();

                    // #1.2 IF Inventoryid is  Exist  in PriceFlag  Ignore to Insert  
                    // Check the Inventoryid in 'PriceFlag'

                    string ReadInventoryidOnScheduleFlag = "select Inventoryid from {0}.timescheduleFlag where  Inventoryid=@Inventoryid ; ";
                    string command_ReadScheduleflag = string.Format(ReadInventoryidOnScheduleFlag, sdb);
                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@Inventoryid",Inventoryid),
                    };
                    CheckA = ObjMain.NewExecuteScalar(command_ReadScheduleflag, parameters);

                    if (CheckA != "")
                    {
                        // Record Already Exist In ScheduleFlag : Ignore for insert 
                    }
                    else
                    {
                        // Call Insert  Function : ScheduleFlag  
                        #region   Insert Portion
                        string Ins_ScheduleFlag = " insert into {0}.timescheduleFlag (Inventoryid,Barcode,IsCommonUpdate)" +
                                               " values " +
                                               " (@Inventoryid,@Barcode,@IsCommonUpdate);";
                        string Ins_command = string.Format(Ins_ScheduleFlag, sdb);
                        MySqlParameter[] parametersAA =
                            {
                             new MySqlParameter("@Inventoryid",Inventoryid),
                             new MySqlParameter("@Barcode",Barcode),
                             new MySqlParameter("@IsCommonUpdate","0"),
                        };
                        Q = ObjMain.ExecuteQuery(Ins_command, parametersAA);




                        #endregion

                    }


                }



            }
            else
            {
                // Nothing To :  Process
            }




        }

        public void INS_WebStock(string sdb)
        {

            // Local Variable 

            string Inventoryid = string.Empty;
            string Barcode = string.Empty;
            DataTable Dtwebstock = new DataTable();
            //#1 Check the Webstock table Record  count 

            string webstock_query = " select * from {0}.`WebstockPreset`";
            string webstock_command = string.Format(webstock_query, sdb);
            Dtwebstock = ObjMain.GetData_DT(webstock_command);

            //#2 Get Inventory Table Record Count 

            string InventoryRowCountQuery = "select * from {0}.Inventory; ";
            string Inventory_command = string.Format(InventoryRowCountQuery, sdb);
            DtinventoryCnt = ObjMain.GetData_DT(Inventory_command);

            //#3. If PriceFlag  is NULL 
            if (Dtwebstock.Rows.Count == 0)
            {
                // Call Insert Function

                string ReadInventoryids = "select Inventoryid,Barcode from {0}.inventory order by Inventoryid; ";
                string commandReads = string.Format(ReadInventoryids, sdb);
                DtRead = ObjMain.GetData_DT(commandReads);

                for (int i = 0; i < DtRead.Rows.Count; i++)
                {
                    DataRow dr = DtRead.Rows[i];

                    Inventoryid = dr["Inventoryid"].ToString();
                    Barcode = dr["BarCode"].ToString();

                    // #1.2 IF Not Exist   
                    // Call Insert  Function : PriceFlag
                    #region   Insert Portion
                    string Ins_webstock = " insert into {0}.WebstockPreset (Inventoryid,Barcode,PreSetValue,PresetTime,TriggerDate)" +
                                           " values " +
                                           " (@Inventoryid,@Barcode,@PreSetValue,@PresetTime,@TriggerDate);";
                    string Ins_command = string.Format(Ins_webstock, sdb);
                    MySqlParameter[] parameters =
                        {
                             new MySqlParameter("@Inventoryid",Inventoryid),
                             new MySqlParameter("@Barcode",Barcode),
                             new MySqlParameter("@PreSetValue","0"),
                             new MySqlParameter("@PresetTime",DateTime.Today.AddDays(1)),
                             new MySqlParameter("@TriggerDate",DateTime.Today),

                        };
                    Q = ObjMain.ExecuteQuery(Ins_command, parameters);

                    #endregion
                }


            }
            //#4. If webstock  < Inventory 
            else if (Dtwebstock.Rows.Count < DtinventoryCnt.Rows.Count)
            {

                string CheckA = string.Empty;

                //#1.1 IF Inventoryid is exist  in  PriceFlag Table Ignore to Insert 

                string ReadInventoryids = "select Inventoryid,Barcode from {0}.inventory order by Inventoryid; ";
                string commandReads = string.Format(ReadInventoryids, sdb);
                DtRead = ObjMain.GetData_DT(commandReads);

                for (int i = 0; i < DtRead.Rows.Count; i++)
                {
                    DataRow dr = DtRead.Rows[i];

                    Inventoryid = dr["Inventoryid"].ToString();
                    Barcode = dr["BarCode"].ToString();

                    // #1.2 IF Inventoryid is  Exist  in WebstockPreset  Ignore to Insert  
                    // Check the Inventoryid in 'WebstockPreset'

                    string ReadInventoryidOnwebstock = "select Inventoryid from {0}.WebstockPreset where  Inventoryid=@Inventoryid ; ";
                    string command_Rwebstock = string.Format(ReadInventoryidOnwebstock, sdb);
                    MySqlParameter[] parameters_webstock =
                    {
                     new MySqlParameter("@Inventoryid",Inventoryid),
                    };
                    CheckA = ObjMain.NewExecuteScalar(command_Rwebstock, parameters_webstock);

                    if (CheckA != "")
                    {
                        // Record Already Exist In WebstockPreset : Ignore for insert 
                    }
                    else
                    {
                        // Call Insert  Function : WebstockPreset  

                        #region   Insert Portion
                        string Ins_webstock = " insert into {0}.WebstockPreset (Inventoryid,Barcode,PreSetValue,PresetTime,TriggerDate)" +
                                               " values " +
                                               " (@Inventoryid,@Barcode,@PreSetValue,@PresetTime,@TriggerDate);";
                        string Ins_command = string.Format(Ins_webstock, sdb);
                        MySqlParameter[] parameters =
                            {
                             new MySqlParameter("@Inventoryid",Inventoryid),
                             new MySqlParameter("@Barcode",Barcode),
                             new MySqlParameter("@PreSetValue",100),
                             new MySqlParameter("@PresetTime",DateTime.Today.AddDays(1)),
                             new MySqlParameter("@TriggerDate",DateTime.Today),

                        };
                        Q = ObjMain.ExecuteQuery(Ins_command, parameters);

                        #endregion


                    }


                }



            }
            else
            {
                // Nothing To :  Run 
            }




        }


        public void LoadBranchTotalForChart()
        {
            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            DT = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            if (DT.Rows.Count > 0)
            {
                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    DataRow dr = DT.Rows[i];
                    RBranchid = dr["Branchid"].ToString();
                    RBranchNam = dr["BranchName"].ToString();
                    RData_Constring = dr["Datapath"].ToString();
                    LoadTotalSaleForChart(RBranchNam, txtStartDt.Text, txtEndDt.Text);
                   
                }
           }
            {
                DtResult.Columns.Add("Month", typeof(string));
                DtResult.Columns.Add("TotalsalesAmt", typeof(decimal));
                DataRow Dr = DtResult.NewRow();
                DataRow Dr1 = DtResult.NewRow();
                DataRow Dr2 = DtResult.NewRow();
                DataRow Dr3 = DtResult.NewRow();
                DataRow Dr4 = DtResult.NewRow();
                DataRow Dr5 = DtResult.NewRow();
                DataRow Dr6 = DtResult.NewRow();
                DataRow Dr7 = DtResult.NewRow();
                DataRow Dr8 = DtResult.NewRow();
                DataRow Dr9 = DtResult.NewRow();
                DataRow Dr10 = DtResult.NewRow();
                DataRow Dr11 = DtResult.NewRow();
                string M1 = "Jan";
                string M2 = "Feb ";
                string M3 = "Mar";
                string M4 = "Apr";
                string M5 = "May";
                string M6 = "Jun";
                string M7 = "Jul";
                string M8 = "Aug";
                string M9 = "Sep";
                string M10 = "Oct ";
                string M11 = "Nov";
                string M12 = "Dec";
                Dr["Month"] = M1;

                if (JanGTot != 0)
                {
                    Dr["TotalsalesAmt"] = JanGTot;
                    DtResult.Rows.Add(Dr);
                }
                else
                {
                    Dr["TotalsalesAmt"] = JanGTot;
                    DtResult.Rows.Add(Dr);
                }
               
                Dr1["Month"] = M2;
                Dr1["TotalsalesAmt"] = FebGTot;
                DtResult.Rows.Add(Dr1);
                Dr2["Month"] = M3;
                Dr2["TotalsalesAmt"] = MarGTot;
                DtResult.Rows.Add(Dr2);
                Dr3["Month"] = M4;
                Dr3["TotalsalesAmt"] =AprGTot ;
                DtResult.Rows.Add(Dr3);
                Dr4["Month"] = M5;
                Dr4["TotalsalesAmt"] = MayGTot;
                DtResult.Rows.Add(Dr4);
                Dr5["Month"] = M6;
                Dr5["TotalsalesAmt"] = JunGTot;
                DtResult.Rows.Add(Dr5);
                Dr6["Month"] = M7;
                Dr6["TotalsalesAmt"] = JulGTot;
                DtResult.Rows.Add(Dr6);
                Dr7["Month"] = M8;
                Dr7["TotalsalesAmt"] = AugGTot;
                DtResult.Rows.Add(Dr7);
                Dr8["Month"] = M9;
                Dr8["TotalsalesAmt"] = SepGTot;
                DtResult.Rows.Add(Dr8);
                Dr9["Month"] = M10;
                Dr9["TotalsalesAmt"] = OctGTot;
                DtResult.Rows.Add(Dr9);
                Dr10["Month"] = M11;
                Dr10["TotalsalesAmt"] = NovGTot;
                DtResult.Rows.Add(Dr10);
                Dr11["Month"] = M12;
                Dr11["TotalsalesAmt"] = DecGTot;
                DtResult.Rows.Add(Dr11);
                DtResult.AcceptChanges();
                Session["DtResult"]=DtResult;
                
                // CreateBarGraph();
                // For Testing 
                
                }
        }


        // Karthik  : (Modified 12-03-20)

        /// <summary>
        ///  <para>Newly Added Function</para> 
        ///  <para>Data Comes From DashCount Table In Portaldb.</para> 
        ///  <para>Preloaded MonthlyTotal Count Except Current Month</para> 
        /// </summary>
        public void LoadBranchTotalAG()
        {
            

            DashCnt = ObjMain.ExecuteScalar("select TotalsalesAmt  from Portaldb.dashcount where userid='" + Session["UserId"].ToString() + "'");

            if (DashCnt != "")
            {
                //LoadTotalSale_CurrentMths();

                dt = ObjMain.GetData_DT("select * from Portaldb.dashcount where userid='" + Session["UserId"].ToString() + "' ");

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        TotMonthlyAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        MonthName = dr["Month"].ToString();
                        if (MonthName == "Jan")
                        {
                            JanAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Feb")
                        {
                            FebAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Mar")
                        {
                            MarAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Apr")
                        {
                            AprAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "May")
                        {
                            MayAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Jun")
                        {
                            JuneAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Jul")
                        {
                            JulAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Aug")
                        {
                            AugAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Sep")
                        {
                            SepAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Oct")
                        {
                            OctAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Nov")
                        {
                            NovAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                        else if (MonthName.ToString().Trim() == "Dec")
                        {
                            DecAmt = Convert.ToDecimal(dr["TotalsalesAmt"]);
                        }
                    }



                    {
                        DtResult.Columns.Add("Month", typeof(string));
                        DtResult.Columns.Add("TotalsalesAmt", typeof(decimal));
                        DataRow Dr = DtResult.NewRow();
                        DataRow Dr1 = DtResult.NewRow();
                        DataRow Dr2 = DtResult.NewRow();
                        DataRow Dr3 = DtResult.NewRow();
                        DataRow Dr4 = DtResult.NewRow();
                        DataRow Dr5 = DtResult.NewRow();
                        DataRow Dr6 = DtResult.NewRow();
                        DataRow Dr7 = DtResult.NewRow();
                        DataRow Dr8 = DtResult.NewRow();
                        DataRow Dr9 = DtResult.NewRow();
                        DataRow Dr10 = DtResult.NewRow();
                        DataRow Dr11 = DtResult.NewRow();
                        string M1 = "Jan";
                        string M2 = "Feb";
                        string M3 = "Mar";
                        string M4 = "Apr";
                        string M5 = "May";
                        string M6 = "Jun";
                        string M7 = "Jul";
                        string M8 = "Aug";
                        string M9 = "Sep";
                        string M10 = "Oct ";
                        string M11 = "Nov";
                        string M12 = "Dec";
                        Dr["Month"] = M1;

                        if (JanGTot != 0)
                        {
                            Dr["TotalsalesAmt"] = JanAmt;
                            DtResult.Rows.Add(Dr);
                        }
                        else
                        {
                            Dr["TotalsalesAmt"] = JanAmt;
                            DtResult.Rows.Add(Dr);
                        }

                        Dr1["Month"] = M2;
                        Dr1["TotalsalesAmt"] = FebAmt;
                        DtResult.Rows.Add(Dr1);

                        Dr2["Month"] = M3;
                        Dr2["TotalsalesAmt"] = MarAmt;
                        DtResult.Rows.Add(Dr2);
                        Dr3["Month"] = M4;
                        Dr3["TotalsalesAmt"] = AprAmt;
                        DtResult.Rows.Add(Dr3);
                        Dr4["Month"] = M5;
                        Dr4["TotalsalesAmt"] = MayAmt;
                        DtResult.Rows.Add(Dr4);
                        Dr5["Month"] = M6;
                        Dr5["TotalsalesAmt"] = JuneAmt;
                        DtResult.Rows.Add(Dr5);
                        Dr6["Month"] = M7;
                        Dr6["TotalsalesAmt"] = JulAmt;
                        DtResult.Rows.Add(Dr6);
                        Dr7["Month"] = M8;
                        Dr7["TotalsalesAmt"] = AugAmt;
                        DtResult.Rows.Add(Dr7);
                        Dr8["Month"] = M9;
                        Dr8["TotalsalesAmt"] = SepAmt;
                        DtResult.Rows.Add(Dr8);
                        Dr9["Month"] = M10;
                        Dr9["TotalsalesAmt"] = OctAmt;
                        DtResult.Rows.Add(Dr9);
                        Dr10["Month"] = M11;
                        Dr10["TotalsalesAmt"] = NovAmt;
                        DtResult.Rows.Add(Dr10);
                        Dr11["Month"] = M12;
                        Dr11["TotalsalesAmt"] = DecAmt;
                        DtResult.Rows.Add(Dr11);
                        DtResult.AcceptChanges();

                        Session["DtResult"] = DtResult;


                        // For Testing 

                    }

                }

            }
            else
            {

                MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
                Cn.Open();
                DT = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
                if (DT.Rows.Count > 0)
                {
                    StringBuilder StrBObj = new StringBuilder();

                    for (int i = 0; i < DT.Rows.Count; i++)
                    {
                        DataRow dr = DT.Rows[i];
                        RBranchid = dr["Branchid"].ToString();
                        RBranchNam = dr["BranchName"].ToString();
                        RData_Constring = dr["Datapath"].ToString();
                        // LoadTotalSaleAG(RBranchNam, txtStartDt.Text, txtEndDt.Text);
                        // LoadTotalSale_CurrentMths(RBranchNam, txtStartDt.Text, txtEndDt.Text);

                        if (StrBObj.Length != 0)
                        {

                            StrBObj.Append(" Union All ");
                            StrBObj.Append(" (select  Monthname(tp.BusinessDate) as Month,sum(tp.PaymentAmount)  as Totalsales from " + RBranchNam + ".transpayment tp left join " + RBranchNam + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + YearAlone + "   group by monthname(tp.BusinessDate) )  ");

                        }
                        else
                        {
                            StrBObj.Append(" select   sum(H.Totalsales)  as Totalsales , H.Month   from ( "
                                         + " (select  Monthname(tp.BusinessDate) as Month,sum(tp.PaymentAmount)  as Totalsales from " + RBranchNam + ".transpayment tp left join " + RBranchNam + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + YearAlone + "   group by monthname(tp.BusinessDate) )  ");

                        }

                    }
                    StrBObj.Append(") as H Group by H.Month ;");

                    DTFinalSalesAmt = ObjMain.GetData_DT(StrBObj.ToString());


                    if (DTFinalSalesAmt.Rows.Count > 0)
                    {
                        for (int i = 0; i < DTFinalSalesAmt.Rows.Count; i++)
                        {
                            DataRow dr = DTFinalSalesAmt.Rows[i];
                            TotMonthlyAmt = Convert.ToDecimal(dr["totalsales"]);
                            MonthName = dr["Month"].ToString();
                            if (MonthName == "January")
                            {
                                JanAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "February")
                            {
                                FebAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "March")
                            {
                                MarAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "April")
                            {
                                AprAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "May")
                            {
                                MayAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "June")
                            {
                                JuneAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "July")
                            {
                                JulAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "August")
                            {
                                AugAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "September")
                            {
                                SepAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "October")
                            {
                                OctAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "November")
                            {
                                NovAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                            else if (MonthName == "December")
                            {
                                DecAmt = Convert.ToDecimal(dr["totalsales"]);
                            }
                        }

                    }

                }


                {
                    DtResult.Columns.Add("Month", typeof(string));
                    DtResult.Columns.Add("TotalsalesAmt", typeof(decimal));
                    DataRow Dr = DtResult.NewRow();
                    DataRow Dr1 = DtResult.NewRow();
                    DataRow Dr2 = DtResult.NewRow();
                    DataRow Dr3 = DtResult.NewRow();
                    DataRow Dr4 = DtResult.NewRow();
                    DataRow Dr5 = DtResult.NewRow();
                    DataRow Dr6 = DtResult.NewRow();
                    DataRow Dr7 = DtResult.NewRow();
                    DataRow Dr8 = DtResult.NewRow();
                    DataRow Dr9 = DtResult.NewRow();
                    DataRow Dr10 = DtResult.NewRow();
                    DataRow Dr11 = DtResult.NewRow();
                    string M1 = "Jan";
                    string M2 = "Feb";
                    string M3 = "Mar";
                    string M4 = "Apr";
                    string M5 = "May";
                    string M6 = "Jun";
                    string M7 = "Jul";
                    string M8 = "Aug";
                    string M9 = "Sep";
                    string M10 = "Oct ";
                    string M11 = "Nov";
                    string M12 = "Dec";
                    Dr["Month"] = M1;

                    if (JanGTot != 0)
                    {
                        Dr["TotalsalesAmt"] = JanAmt;
                        DtResult.Rows.Add(Dr);
                    }
                    else
                    {
                        Dr["TotalsalesAmt"] = JanAmt;
                        DtResult.Rows.Add(Dr);
                    }

                    Dr1["Month"] = M2;
                    Dr1["TotalsalesAmt"] = FebAmt;
                    DtResult.Rows.Add(Dr1);

                    Dr2["Month"] = M3;
                    Dr2["TotalsalesAmt"] = MarAmt;
                    DtResult.Rows.Add(Dr2);
                    Dr3["Month"] = M4;
                    Dr3["TotalsalesAmt"] = AprAmt;
                    DtResult.Rows.Add(Dr3);
                    Dr4["Month"] = M5;
                    Dr4["TotalsalesAmt"] = MayAmt;
                    DtResult.Rows.Add(Dr4);
                    Dr5["Month"] = M6;
                    Dr5["TotalsalesAmt"] = JuneAmt;
                    DtResult.Rows.Add(Dr5);
                    Dr6["Month"] = M7;
                    Dr6["TotalsalesAmt"] = JulAmt;
                    DtResult.Rows.Add(Dr6);
                    Dr7["Month"] = M8;
                    Dr7["TotalsalesAmt"] = AugAmt;
                    DtResult.Rows.Add(Dr7);
                    Dr8["Month"] = M9;
                    Dr8["TotalsalesAmt"] = SepAmt;
                    DtResult.Rows.Add(Dr8);
                    Dr9["Month"] = M10;
                    Dr9["TotalsalesAmt"] = OctAmt;
                    DtResult.Rows.Add(Dr9);
                    Dr10["Month"] = M11;
                    Dr10["TotalsalesAmt"] = NovAmt;
                    DtResult.Rows.Add(Dr10);
                    Dr11["Month"] = M12;
                    Dr11["TotalsalesAmt"] = DecAmt;
                    DtResult.Rows.Add(Dr11);
                    DtResult.AcceptChanges();

                    //.....
                    for (int i = 0; i < DtResult.Rows.Count; i++)
                    {
                        DataRow dr = DtResult.Rows[i];
                        RMonth = dr["Month"].ToString();
                        RTotalsalesAmt = dr["TotalsalesAmt"].ToString();

                        K = ObjMain.ExecuteNonQuery(" Insert into portaldb.dashcount(Userid,Month,TotalsalesAmt) Values(" + Session["UserId"].ToString() + ",'" + RMonth + "'," + RTotalsalesAmt + "  ) ");


                    }
                    Session["DtResult"] = DtResult;

                    // For Testing 

                }

            }
            
           
     
        }

       public void LoadTotalSaleForChart(string Branch_Name, string YearId, string MonthId)
        {
            // Get the Total Monthly Sale 
            MySqlConnection Cnn = new MySqlConnection(ConStr_Rds);
            Cnn.Open();
            string QryTotbyMon = "select sum(tp.PaymentAmount)  as Totalsales,Monthname(tp.BusinessDate) as Month from " + RBranchNam + ".transpayment tp left join " + RBranchNam + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + YearAlone + "  group by monthname(tp.BusinessDate) order by month(tp.BusinessDate);";
            MySqlDataAdapter Ada_List = new MySqlDataAdapter(QryTotbyMon, Cnn);
            DTFinalSalesAmt.Clear();
            Ada_List.Fill(DTFinalSalesAmt);

           if (DTFinalSalesAmt.Rows.Count > 0)
            {
                for (int i = 0; i < DTFinalSalesAmt.Rows.Count; i++)
                {
                    DataRow dr = DTFinalSalesAmt.Rows[i];
                    TotMonthlyAmt = Convert.ToDecimal(dr["totalsales"]);
                    MonthName = dr["Month"].ToString();
                    if (MonthName.ToString().Trim() == "January")
                    {
                        JanAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "February")
                    {
                        FebAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "March")
                    {
                        MarAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "April")
                    {
                        AprAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "May")
                    {
                        MayAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "June")
                    {
                        JuneAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "July")
                    {
                        JulAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "August")
                    {
                        AugAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "September")
                    {
                        SepAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "October")
                    {
                        OctAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "November")
                    {
                        NovAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "December")
                    {
                        DecAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
               }

                JanGTot += JanAmt;
                FebGTot += FebAmt;
                MarGTot += MarAmt;
                AprGTot += AprAmt;
                MayGTot += MayAmt;
                JunGTot += JuneAmt;
                JulGTot += JulAmt;
                AugGTot += AugAmt;
                SepGTot += SepAmt;
                OctGTot += OctAmt;
                NovGTot += NovAmt;
                DecGTot += DecAmt;

            }
         }


       // Testing Code  : (Modified 12-03-20)
        public void LoadTotalSaleAG(string Branch_Name, string YearId, string MonthId)
        {
            // Get the Total Monthly Sale 
            MySqlConnection Cnn = new MySqlConnection(ConStr_Rds);
            Cnn.Open();
            string QryTotbyMon = "select sum(tp.PaymentAmount)  as Totalsales,Monthname(tp.BusinessDate) as Month from " + RBranchNam + ".transpayment tp left join " + RBranchNam + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + YearAlone + " and  monthname(tp.BusinessDate)!=MonthName('"+Curr_DateOnly+"')  group by monthname(tp.BusinessDate) order by month(tp.BusinessDate);";
            MySqlDataAdapter Ada_List = new MySqlDataAdapter(QryTotbyMon, Cnn);
            Ada_List.Fill(DTFinalSalesAmt);
            if (DTFinalSalesAmt.Rows.Count > 0)
            {
                for (int i = 0; i < DTFinalSalesAmt.Rows.Count; i++)
                {
                    DataRow dr = DTFinalSalesAmt.Rows[i];
                    TotMonthlyAmt = Convert.ToDecimal(dr["totalsales"]);
                    MonthName = dr["Month"].ToString();
                    if (MonthName.ToString().Trim() == "January")
                    {
                        JanAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "February")
                    {
                        FebAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "March")
                    {
                        MarAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "April")
                    {
                        AprAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "May")
                    {
                        MayAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "June")
                    {
                        JuneAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "July")
                    {
                        JulAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "August")
                    {
                        AugAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "September")
                    {
                        SepAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "October")
                    {
                        OctAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "November")
                    {
                        NovAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                    else if (MonthName.ToString().Trim() == "December")
                    {
                        DecAmt = Convert.ToDecimal(dr["totalsales"]);
                    }
                }

                JanGTot += JanAmt;
                FebGTot += FebAmt;
                MarGTot += MarAmt;
                AprGTot += AprAmt;
                MayGTot += MayAmt;
                JunGTot += JuneAmt;
                JulGTot += JulAmt;
                AugGTot += AugAmt;
                SepGTot += SepAmt;
                OctGTot += OctAmt;
                NovGTot += NovAmt;
                DecGTot += DecAmt;

            }

        }

        // Calculate Current Month Values (Modified 12-03-20)
        // Not In Use ( SP Created Instead of This Function )
       public void LoadTotalSale_CurrentMths()
        {

            MySqlConnection Cn = new MySqlConnection(ConStr_Rds);
            Cn.Open();
            DT = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            if (DT.Rows.Count > 0)
            {
                StringBuilder StrBObj = new StringBuilder();

                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    DataRow dr = DT.Rows[i];
                    RBranchid = dr["Branchid"].ToString();
                    RBranchNam = dr["BranchName"].ToString();
                    RData_Constring = dr["Datapath"].ToString();
                    // LoadTotalSaleAG(RBranchNam, txtStartDt.Text, txtEndDt.Text);
                    // LoadTotalSale_CurrentMths(RBranchNam, txtStartDt.Text, txtEndDt.Text);

                    if (StrBObj.Length != 0)
                    {

                        StrBObj.Append(" Union All ");
                        StrBObj.Append(" (select  Monthname(tp.BusinessDate) as Month,sum(tp.PaymentAmount)  as Totalsales from " + RBranchNam + ".transpayment tp left join " + RBranchNam + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + YearAlone + "  and  monthname(tp.BusinessDate)=MonthName('" + Curr_DateOnly + "'))  ");

                    }
                    else
                    {
                        StrBObj.Append(" select   sum(H.Totalsales)  as Totalsales , H.Month   from ( "
                        + "(select  Monthname(tp.BusinessDate) as Month,sum(tp.PaymentAmount)  as Totalsales from " + RBranchNam + ".transpayment tp left join " + RBranchNam + ".trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=" + YearAlone + "  and  monthname(tp.BusinessDate)=MonthName('" + Curr_DateOnly + "'))  ");

                    }

                }
                StrBObj.Append(") as H Group by H.Month ;");
                DTTemp = ObjMain.GetData_DT(StrBObj.ToString());
                if (DTTemp.Rows.Count > 0)
                { 
                    string Monthss = DTTemp.Rows[0]["Month"].ToString();
                    string TotalSaless = DTTemp.Rows[0]["Totalsales"].ToString(); 

                    string NewMonth=Monthss.Substring(0,3);
                    K = ObjMain.ExecuteNonQuery("Update  portaldb.dashcount  set TotalsalesAmt=" + TotalSaless + " where Month='" + NewMonth + "' AND Userid=" + Session["UserId"].ToString() + " ");

                }
                             

            }

        }
   private void BindChart()
    {
        DataTable dt = new DataTable();
        try
        {
            dt =(DataTable) Session["DtResult"];

            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', 'Total Sale');      
                      data.addRows(" + dt.Rows.Count + ");");

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

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

      }



     }