using CLS_BL;
using CLS_DL;
using CLS_PL;
using MySql.Data.MySqlClient;
using MySqlX.XDevAPI.Relational;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using SunWeb.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using static SunWeb.Model.CQuickbookItemSummary;
using static SunWeb.Model.CQuickbookPaymenttype;
namespace SUNWEB
{
    public partial class Quickbook : 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 
        BL_API objAPI = new BL_API();
        MYSQLCLOUD ObjCloudMain = new MYSQLCLOUD();
        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 Dt1 = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable Dt3 = new DataTable();
        DataTable Dt4 = new DataTable();
        DataTable Dt5= 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_QuickBook = 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;

        string BraNam = "";
        string FDate = "";
        string TDate = "";
        string ExecQry = "";
        public string sCmd;
        string saccessToken = string.Empty;
        string srefreshToken = string.Empty;
        string Stat1 = string.Empty;
        string Stat2 = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {

            PID = "73";    // This is for QuickBook  Page 


            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");
            }
            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_QuickBook"].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
            {

            }


        }


        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);
            DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            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_QuickBook = DtLicense.Rows[0]["QUICKBOOK"].ToString();
                Session["L_QuickBook"] = L_QuickBook;
            }
            if (Session["L_QuickBook"].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)
        {


            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Write("Quickbook.aspx");
            }
            else
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Value));
                if (DT.Rows.Count > 0)
                {
                    Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                    Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                    Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                    Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();
                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }

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

                if (ISHQ != "")
                {
                    ActiveCallvalue = ObjSel.GetFirstBranch7(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
                    UPDATEBRANCH(ActiveCallvalue);
                }
                else
                {
                    BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);    // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Value) + " and cd_branchalias='" + Cmb_BranchName + "';");
                    RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
                    DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);
                }



                if (DtLicense.Rows.Count > 0)
                {
                    L_MaxUserCnt = DtLicense.Rows[0]["UserCnt"].ToString();
                    L_QuickBook = DtLicense.Rows[0]["QUICKBOOK"].ToString();
                    Session["L_QuickBook"] = L_QuickBook;


                }

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

                }
                else if (Session["Write_Acess"].ToString() == "N")
                {
                    //btnGeneratebyinventoryid.Enabled = false;
                    //btnGenerateIorderlaTiming.Enabled = false;

                }
                else
                {

                    // Call the function if any . 

                }
            }
            UPDATEBRANCH(Cmb_BranchName);


        }

        //Quickbook integration 
        protected void btnGetReports_Click(object sender, EventArgs e)
        {
            // XSS - Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());

            if (cmbBranches.Visible == false)
            {
                btnGetReports.Visible = true;
                //lblMsgTotalSelection.Text = "Search Result - Journal Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp to &nbsp;&nbsp;" + EndDt;
                //idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px");
                LoadJournalMainQuery(Session["Branch_HQ"].ToString(), StartDT, EndDt);
                System.Threading.Thread.Sleep(1000);
            }
            else if (cmbBranches.Visible == true)
            {
                //lblMsgTotalSelection.Text = "Search Result - Journal Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from" + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp to &nbsp;&nbsp;" + EndDt;
                //idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White;font-size:14px");
                Curr_BranchFullName = cmbBranches.SelectedItem.Text;
                cd_BranchAlias = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                BraNam = cd_BranchAlias;
                FDate = txtStartDt.Text;
                TDate = txtEndDt.Text;
                LoadJournalMainQuery(cd_BranchAlias, StartDT, EndDt);
            }
        }

        protected void LoadJournalMainQuery(string BranchAlias, string D1, string D2)
        {
            // XSS - Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());



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

            //For AccessToken
            Get_QuickbookAccessToken(BranchAlias);
            Get_QuickbookSyncPaymentTypes(BranchAlias);
            Get_QuickbookSyncItems(BranchAlias);
            System.Threading.Thread.Sleep(1000);

            // Prevents SQL Injection  : 01-09-21
            // Parameterized Approach 


            sCmd = " Select Date(T.BusinessDate) as BusinessDate,TD.Barcode as Barcode,TD.InventoryDesc as InventoryDesc, Sum(TD.QTY) as Qty,Round(TD.Price,2) as Price,Round(TD.DiscValue*100,2) as Disc " +
                " From {0}.Trans T Left Outer Join {0}.TransDetail TD On T.TransID=TD.TransID" +
                " Where T.TransStatus = 2 And T.NavSync = 0 And TD.ItemStatus <> 1" +
                " And T.BusinessDate Between  @StartDT  and  @EndDt " +
                " Group By T.BusinessDate,TD.Barcode, TD.InventoryDesc,TD.Price,TD.DiscValue" +
                " Order by 1,2;  ";

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


            Dt1 = ObjMain.NewGetData_DT(Command, parameters);
            GridView1.DataSource = Dt1;
            //gridview totals
            GridView1.DataBind();


        }

   
        public void Get_QuickbookAccessToken(string BranchAlias)
        {
            //for getting Xero Tokens
            sCmd = "Select AppID, ClientID, ClientSecret, Code, RealmID, TokenBaseURL, TransBaseURL, AccessToken, RefreshToken, CustomerRef From {0}.QuickbookAuth";
            string Command3 = string.Format(sCmd, BranchAlias);
            MySqlParameter[] parameters3 =
                  {
                  };

            Dt4 = ObjMain.NewGetData_DT(Command3, parameters3);
            Session["sQBAppID"] = Dt4.Rows[0]["AppID"].ToString();
            Session["sQBClientID"] = Dt4.Rows[0]["ClientID"].ToString();
            Session["sQBClientSecret"] = Dt4.Rows[0]["ClientSecret"].ToString();
            Session["sQBCode"] = Dt4.Rows[0]["Code"].ToString();
            Session["sQBRealmID"] = Dt4.Rows[0]["RealmID"].ToString();
            Session["sQBTokenBaseURL"] = Dt4.Rows[0]["TokenBaseURL"].ToString();
            Session["sQBTransBaseURL"] = Dt4.Rows[0]["TransBaseURL"].ToString();
            Session["sQBAccessToken"] = Dt4.Rows[0]["AccessToken"].ToString();
            Session["sQBRefreshToken"] = Dt4.Rows[0]["RefreshToken"].ToString();
            Session["sQBCustomerRef"] = Dt4.Rows[0]["CustomerRef"].ToString();

            //Call AccessToken API
            string sResp = objAPI.Quickbook_GetAccessToken(Session["sQBRefreshToken"].ToString(), Session["sQBTokenBaseURL"].ToString(), Session["sQBClientID"].ToString(), Session["sQBClientSecret"].ToString());
            // Reading Json Data : 
            var ReadData = JObject.Parse(sResp.ToString());
            JArray ReadArray = new JArray();
            ReadArray.Add(ReadData);
            foreach (JToken ReadItem in ReadArray)
            {
                saccessToken = ReadItem["access_token"].ToString();
                srefreshToken = ReadItem["refresh_token"].ToString();
            }

            Session["sessionQuickbookAccessToken"] = saccessToken;

            // Update QuickbookAuth 
            string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string QueryUPT = "UPDATE {0}.Quickbookauth SET accesstoken='" + saccessToken + "', refreshtoken='" + srefreshToken + "',gendate='" + Curr_Date + "' ";
            string commandM = string.Format(QueryUPT, BranchAlias);
            int U = ObjCloudMain.ExecuteNonQuery(commandM);


        }
        public void Get_QuickbookSyncPaymentTypes(string BranchAlias)
        {
            //for Truncating QuickbookPayment Table
            string QueryUPT = "Truncate Table {0}.QuickbookPaymentType ";
            string commandTruncate = string.Format(QueryUPT, BranchAlias);
            int U = ObjCloudMain.ExecuteNonQuery(commandTruncate);


            //Call GetPaymentAPI
            string sResp = objAPI.Quickbook_GetPaymenttypes(Session["sessionQuickbookAccessToken"].ToString(), Session["sQBRealmID"].ToString(), Session["sQBTransBaseURL"].ToString());

            // Deserialize into your Root class
            var qbPaymentResponse = JsonConvert.DeserializeObject<CQuickbookPaymenttype.Root>(sResp);

            // Now you can access data
            foreach (var method in qbPaymentResponse.QueryResponse.PaymentMethod)
            {
                Console.WriteLine($"{method.Id} - {method.Name} - {method.Type}");
                //for inserting paymenttypes into QuickbookPayment Table
                string QueryIns = "INSERT INTO {0}.QuickbookPaymentType (PaymentID,PaymentType,Type) Values (" + method.Id + ",'" + method.Name + "', '" + method.Type + "') ";
                string commandIns = string.Format(QueryIns, BranchAlias);
                int I = ObjCloudMain.ExecuteNonQuery(commandIns);
            }

            //for updating POSPaymentType
            string QueryQPT = "Update {0}.quickbookpaymenttype QP Left Outer Join {0}.PaymentType PT On QP.PaymentType=PT.PaymentType SET QP.POSPaymentID=PT.PaymentTypeID; ";
            string commandM = string.Format(QueryQPT, BranchAlias);
            int p = ObjCloudMain.ExecuteNonQuery(commandM);

            //for missing all set to 1
            QueryQPT = "Update {0}.quickbookpaymenttype SET POSPaymentID=1 Where POSPaymentID IS NULL; ";
            commandM = string.Format(QueryQPT, BranchAlias);
            p = ObjCloudMain.ExecuteNonQuery(commandM);

        }

        public void Get_QuickbookSyncItems(string BranchAlias)
        {
            // 1. Truncate your QuickbookItem table
            string QueryUPT = "TRUNCATE TABLE {0}.QuickbookItem";
            string commandTruncate = string.Format(QueryUPT, BranchAlias);
            int U = ObjCloudMain.ExecuteNonQuery(commandTruncate);

            // 2. Call GetItems API
            string sResp = objAPI.Quickbook_GetItems(Session["sessionQuickbookAccessToken"].ToString(),Session["sQBRealmID"].ToString(),Session["sQBTransBaseURL"].ToString());

            // 3. Deserialize into your Root class
            var qbItemResponse = JsonConvert.DeserializeObject<CQuickbookItem.Root>(sResp);

            // 4. Loop through items and insert into DB
            foreach (var item in qbItemResponse.QueryResponse.Item)
            {
                Console.WriteLine($"{item.Id} - {item.Name} - {item.Sku}");

                // Insert into your QuickbookItem table
                string QueryIns = "INSERT INTO {0}.QuickbookItem (QID, Name,SKU) " +
                                  "VALUES (" + item.Id + ", '" + item.Name.Replace("'", "''") + "', '" + (item.Sku ?? "") + "')";
                string commandIns = string.Format(QueryIns, BranchAlias);
                int I = ObjCloudMain.ExecuteNonQuery(commandIns);
            }

         }


        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {

        }
        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            // XSS Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());


            if (cmbBranches.Visible == false)
            {
                GridView1.PageIndex = e.NewPageIndex;
                LoadJournalMainQuery(Session["Branch_HQ"].ToString(), StartDT, EndDt);
            }
            else
            {
                BranchFullName = Microsoft.Security.Application.Encoder.HtmlEncode(cmbBranches.SelectedItem.Text);    // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )

                GridView1.PageIndex = e.NewPageIndex;
                LoadJournalMainQuery(Cmb_BranchName, StartDT, EndDt);
            }
        }


        #region "Quickbook Upload Summary Module"
        //for upalod Sales summary to Quickbook system
        public void btnUploadSales_Click(object sender, EventArgs e)
        {
            try
            {
                string BranchAlias = "";
                string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
                string EndDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());

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

                //1. Get totals
                sCmd = "Select Sum(Total) as Total From {0}.Trans T " +
                       "Where T.BusinessDate Between  @StartDT  and  @EndDt " +
                       "And TransStatus in (2) And T.NavSync = 0;";

                string Command1 = string.Format(sCmd, BranchAlias);
                MySqlParameter[] parameters1 = {
                    new MySqlParameter("@StartDT", StartDT),
                    new MySqlParameter("@EndDt", EndDT),
                };
                DataTable Dt2 = ObjMain.NewGetData_DT(Command1, parameters1);

                //2. Get item details
                sCmd = "Select TD.Barcode, TD.InventoryDesc, Sum(TD.QTY) as Qty, TD.Price, Sum(TD.Amount) As Amount,TD.DiscValue*100 as Disc,Q.QID " +
                       "From {0}.Trans T " +
                       "Left Join {0}.TransDetail TD On T.TransID=TD.TransID " +
                       "Left Outer Join {0}.QuickbookItem Q on TD.Barcode=Q.SKU " +
                       "Where T.TransStatus = 2 And T.NavSync = 0 And TD.ItemStatus <> 1 " +
                       "And T.BusinessDate Between @StartDT and @EndDt " +
                       "Group By TD.Barcode, TD.InventoryDesc, TD.Price;";

                Command = string.Format(sCmd, BranchAlias);
                MySqlParameter[] parameters2 = {
                    new MySqlParameter("@StartDT", StartDT),
                    new MySqlParameter("@EndDt", EndDT),
                };
                DataTable Dt1 = ObjMain.NewGetData_DT(Command, parameters2);

                // 3. Build QuickBooks invoice
                var invoice = new CQuickbookCreateInvoice.Rootval
                {
                    TxnDate = EndDT,
                    PrivateNote = "Consolidated POS Sales for " + EndDT,
                    CustomerRef = new CQuickbookCreateInvoice.CustomerRef
                    {
                        value = Session["sQBCustomerRef"].ToString(),
                        name = "POS"
                    },
                    //GlobalTaxCalculation = "TaxInclusive",
                    GlobalTaxCalculation= "NotApplicable",
                    Line = new List<CQuickbookCreateInvoice.Line>()
                };

                foreach (DataRow row in Dt1.Rows)
                {
                    double qty = Convert.ToDouble(row["Qty"]);
                    double price = Convert.ToDouble(row["Price"]);

                    var line = new CQuickbookCreateInvoice.Line
                    {
                        DetailType = "SalesItemLineDetail",
                        Amount = Convert.ToDouble(row["Amount"]),
                        SalesItemLineDetail = new CQuickbookCreateInvoice.SalesItemLineDetail
                        {
                            ItemRef = new CQuickbookCreateInvoice.ItemRef
                            {
                                value = row["QID"].ToString(),
                                name = row["InventoryDesc"].ToString()
                            },
                            Qty = (int)qty,
                            UnitPrice = price,
                            TaxCodeRef = new CQuickbookCreateInvoice.TaxCodeRef
                            {
                                //value = "7" // Standard GST/VAT code in QBO
                                value = "Non" // Standard GST/VAT code in QBO
                            }
                        }
                    };
                    invoice.Line.Add(line);
                }

                // Add Discount Line Example (10%)
                var discountLine = new CQuickbookCreateInvoice.Line
                {
                    DetailType = "DiscountLineDetail",
                    Amount = 0,
                    DiscountLineDetail = new CQuickbookCreateInvoice.DiscountLineDetail
                    {
                        PercentBased = true,
                        DiscountPercent = 0,
                        DiscountAccountRef = new CQuickbookCreateInvoice.DiscountAccountRef
                        {
                            value = "86",
                            name = "Discounts Given"
                        }
                    }
                };
                invoice.Line.Add(discountLine);

                // Assign total after discount
                //invoice.TotalAmt = Convert.ToDouble(Dt2.Rows[0]["Total"]) - 35.00;
                invoice.TotalAmt = Convert.ToDouble(Dt2.Rows[0]["Total"]) ;

                // 4. Serialize JSON
                string jsonInvoice = JsonConvert.SerializeObject(invoice, Formatting.Indented);

                // 5. Call QuickBooks CreateInvoice API
                //var objAPI = new YourAPIClass();
                string response = objAPI.Quickbook_CreateInvoice(
                    Session["sQBAccessToken"].ToString(),
                    Session["sQBRealmID"].ToString(),
                    jsonInvoice,
                    Session["sQBTransBaseURL"].ToString()
                );

                dynamic result = JsonConvert.DeserializeObject<dynamic>(response);
                string invoiceId = result["Invoice"]["Id"].ToString();
                if(invoiceId =="" || invoiceId =="0")
                {
                    return;
                }
                Session["Invoice_ID"] = invoiceId;
                Stat1 = "0";

                //*****************************************************
                //for Payment Summary
                sCmd = "Select TP.PaymentType as PaymentType, Sum(TP.PaymentAmount) as PaymentAmount,QP.PaymentID as QbPaymentID " +
                " From {0}.TransPayment TP Left Outer Join {0}.Trans T on TP.TransID=T.TransID " +
                " Left Outer Join {0}.quickbookpaymenttype QP on TP.Paymenttype=QP.PaymentType" +
                " Where TP.BusinessDate Between @StartDT  and  @EndDt " +
                " And T.TransStatus in (2) And T.NavSync=0  " +
                " Group By TP.PaymentType" +
                " Order By 1,2;  ";

                string Command4 = string.Format(sCmd, BranchAlias);
                MySqlParameter[] parameters4 =
                      {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDT),
                      };
                DataTable Dt3 = ObjMain.NewGetData_DT(Command4, parameters4);
                foreach (DataRow row in Dt3.Rows)
                {
                    string customerId = Session["sQBCustomerRef"].ToString();            // QuickBooks customer ID
                    double totalAmount = (double)(decimal) row["PaymentAmount"];        // Payment amount
                    string paymentMethodId = row["QbPaymentID"].ToString();      // QuickBooks PaymentMethodRef ID
                    string paymentMethodName = row["PaymentType"].ToString();  // e.g., Cash, MasterCard

                    // Create Payment Root object
                    var payment = new CQuickbookCreatePayment.Root
                    {
                        CustomerRef = new CQuickbookCreatePayment.CustomerRef
                        {
                            value = customerId
                        },
                        TotalAmt = totalAmount,
                        PaymentMethodRef = new CQuickbookCreatePayment.PaymentMethodRef
                        {
                            value = paymentMethodId,
                            name = paymentMethodName
                        },
                        Line = new List<CQuickbookCreatePayment.Line>
                        {
                            new CQuickbookCreatePayment.Line
                            {
                                Amount = totalAmount,
                                LinkedTxn = new List<CQuickbookCreatePayment.LinkedTxn>
                                {
                                    new CQuickbookCreatePayment.LinkedTxn
                                    {
                                        TxnId = Session["Invoice_ID"].ToString(),
                                        TxnType = "Invoice"
                                    }
                                }
                            }
                        }
                    };

                    // Serialize to JSON
                    string jsonPayment = JsonConvert.SerializeObject(payment, Formatting.Indented);

                    // Debug/Log JSON
                    Console.WriteLine(jsonPayment);

                    // Call API 
                    var sResp = objAPI.Quickbook_CreatePayment(
                        Session["sQBAccessToken"].ToString(),
                        Session["sQBRealmID"].ToString(),
                        jsonPayment,
                        Session["sQBTransBaseURL"].ToString()
                    );

                    // 4️⃣ Deserialize Response
                    dynamic resultp = JsonConvert.DeserializeObject<dynamic>(sResp);
                    string invoiceIdp = resultp["Payment"]["Id"].ToString();
                    if (invoiceIdp == "" || invoiceIdp == "0")
                    {
                        return;
                    }
                    //Session["Invoice_ID"] = invoiceId;
                    Stat2 = "0";

                }

                //Final
                if (Stat1 == "0" && Stat2 == "0")
                {
                    sCmd = "Update {0}.Trans Set NavSync=1 Where BusinessDate Between @StartDT  and  @EndDt ";
                    string Command5 = string.Format(sCmd, BranchAlias);
                    MySqlParameter[] parameters5 =
                        {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDT),
                        };
                    int U = ObjCloudMain.ExecuteQuery(Command5, parameters5);

                    btnGetReports_Click(btnGetReports, EventArgs.Empty);
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Sales Summary uploaded to Quickbook successfully ') ", true);
                }
                else
                {
                    btnGetReports_Click(btnGetReports, EventArgs.Empty);
                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Sales Summary Upload has been Failed ') ", true);
                }
            }
            catch (Exception ex)
            {
                //lblResult.ForeColor = System.Drawing.Color.Red;
                //lblResult.Text = "Error: " + ex.Message;
                ScriptManager.RegisterStartupScript(Page, Page.GetType(), "err_msg", "alert(' Upload Process has been Failed ') ", true);
            }

        }
        #endregion 
    }
}