using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Web;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using CLS_BL;
using CLS_DL;
using CLS_PL;
using System.Web.UI.DataVisualization.Charting;
using Newtonsoft.Json;
using System.Web.Services;

namespace SUNWEB
{
    public partial class ReturnStockTransfer : 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 iInsertHeader = 0;
        Int32 iInsertDetail = 0;
        Int32 iUpdateReturn = 0;
        Int32 UpdateFromInventoryCostAverageTable = 0;
        Int32 M = 0;
        Int32 N = 0;
        Int32 O = 0;
        Int32 P = 0;


        Int32 UpdateTransferHeader = 0;
        Int32 iGetFromBranch = 0;
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        DataSet ds = new DataSet();
        MYSQLDB ObjMain = new MYSQLDB();
        MYSQLCLOUD ObjCloud = new MYSQLCLOUD();
        DataTable DT = new DataTable();
        DataTable Dt2 = new DataTable();
        DataTable DTTransferStock = new DataTable();
        DataTable DTInventory = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable DtSuperAdmin = new DataTable();
        DataTable DtMultiBranch = new DataTable();
        DataTable DtNormalUser = new DataTable();
        DataTable DtSearchInv = new DataTable();
        DataTable DTGetFromBranch = new DataTable();

        //MYSQLDB ObjMain = new MYSQLDB();
        MYSQLCLOUD ObjMainCloud = new MYSQLCLOUD();

        decimal totaltaxableamount = 0;
        decimal Gtotaltaxableamount = 0;

        decimal totaltaxamount = 0;
        decimal Gtotaltaxamount = 0;
        // 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;

        // 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 Branch;

        string Search;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

                //txtFromDt.Text = Curr_DateOnly;
                //txtToDt.Text = Curr_DateOnly;

            }
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            //MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);

            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }


            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();
                }
            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchName4SuperAdmin();
                }
                cd_BranchAlias = cmbBranches.Text;
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {

                if (!IsPostBack)
                {
                    LoadBranchName();
                }
                cd_BranchAlias = cmbBranches.Text;

            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == 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();
                PID = "3"; //  This is for ItemSale 
            }

            if (!IsPostBack)
            {
                txtReturnNo.CssClass = "form-control";
                txtFBranch.CssClass = "form-control";
                txtRefNo.CssClass = "form-control";
                txtRetrunDate.CssClass = "form-control";
                txtConfirm.CssClass = "form-control";
                txtReturnStatus.CssClass = "form-control";
                txtNotes.CssClass = "form-control";

                if (Request.QueryString["ReturnNo"] != null)
                {
                    string ToDBFullName = Request.QueryString["ToBranch"].ToString();
                    string FromDBFullName = Request.QueryString["FromBranch"].ToString();
                    string ReturnNo = Request.QueryString[0].ToString();
                    //Branch = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), ToDBFullName);
                    string ToDB = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), ToDBFullName);
                    string FromDB = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), FromDBFullName);

                    //string FromDB = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), Branch2);
                    cmbBranches.Text = ObjSel.GetBranchFullNameByDB(ToDB);
                    LoadReturnHeader(FromDB, ReturnNo, FromDBFullName);
                    LoadReturnDetail(FromDB, GetReturnIDByReturnNo(FromDB, ReturnNo.ToString()));
                    Session["FromDB"] = FromDB;
                    Session["ReturnID"] = ObjSel.GetReturnIDbyReturnNo(FromDB, txtReturnNo.Text);
                    //LoadReturnDetails(Branch, sReceiveID);
                }
            }
            if (txtConfirm.Text == "1")
            {

            }


        }
        public void LoadBranchName4SuperAdmin()
        {
            //DtSuperAdmin = ObjUserBL.GetBranchOnSuperAdmin(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            //if (DtSuperAdmin.Rows.Count > 0)
            //{
            //    cmbBranches.DataSource = DtSuperAdmin;
            //    cmbBranches.DataTextField = "BranchName";
            //    cmbBranches.DataValueField = "Branchid";
            //    cmbBranches.DataBind();
            //    cmbBranches.Items.Insert(0, "Select Branch");
            //}
        }

        public void LoadBranchName()
        {
            //DtNormalUser = ObjUserBL.GetBranchOnLoginValues(Session["Branch_HQ"].ToString(), Session["UserId"].ToString());
            //if (DtNormalUser.Rows.Count > 0)
            //{
            //    cmbBranches.DataSource = DtNormalUser;
            //    cmbBranches.DataTextField = "BranchName";
            //    cmbBranches.DataValueField = "Branchid";
            //    cmbBranches.DataBind();
            //    cmbBranches.Items.Insert(0, "Select Branch");
            //}
        }

        //John Created on 14-Oct-2021
        public void LoadReturnHeader(string sDB, string sReturnNo, string sFromBranch)
        {
            DataTable DTReturnHeader = new DataTable();
            //string sCmd = "Select *From " + sDB + ".returnstock where ReturnNo = '" + sReturnNo + "'";
            string sCmd = "Select *From {0}.returnstock where ReturnNo = @ReturnNo";
            string command = string.Format(sCmd, sDB);
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@ReturnNo",sReturnNo)
            };

            //DTReturnHeader = ObjMainCloud.GetData_DT(sCmd);
            DTReturnHeader = ObjMain.NewGetData_DT(command, parameters);
            if (DTReturnHeader.Rows.Count > 0)
            {
                txtReturnNo.Text = DTReturnHeader.Rows[0]["ReturnNo"].ToString();
                txtRefNo.Text = DTReturnHeader.Rows[0]["TransferNo"].ToString();
                txtConfirm.Text = DTReturnHeader.Rows[0]["ConfirmNo"].ToString();
                txtFBranch.Text = sFromBranch;
                //ObjSel.GetBranchFullName(Session["Branch_HQ"].ToString(), DTReturnHeader.Rows[0]["FromBranchID"].ToString());
                txtRetrunDate.Text = DTReturnHeader.Rows[0]["ReturnDate"].ToString();
                if (DTReturnHeader.Rows[0]["ReturnFlag"].ToString() == "0")
                {
                    txtReturnStatus.Text = "PENDING";
                }
                else
                {
                    txtReturnStatus.Text = "CONFIRMED";
                }
                txtNotes.Text = DTReturnHeader.Rows[0]["TNotes"].ToString();
            }
        }

        //John Created on 14-Oct-2021
        public void LoadReturnDetail(string sDB, string sReturnID)
        {
            DataTable DTReturnDetail = new DataTable();
            // string sCmd = "Select B.Barcode,InventoryName,Description,Unit,PackUnit,ReturnQuantity from " + sDB + ".returnstockdetail A " +
            //             " join " + sDB + ".Inventory B on A.Barcode = B.Barcode where ReturnID = '" + sReturnID + "'; ";
            string sCmd = "Select B.Barcode,InventoryName,Description,Unit,PackUnit,ReturnQuantity from {0}.returnstockdetail A " +
                       " join " + sDB + ".Inventory B on A.Barcode = B.Barcode where ReturnID = @ReturnID; ";
            string command = string.Format(sCmd, sDB);
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@ReturnID",sReturnID)
            };
            //DTReturnDetail = ObjMainCloud.GetData_DT(sCmd);
            DTReturnDetail = ObjMain.NewGetData_DT(command, parameters);
            GrdReturnItems.DataSource = DTReturnDetail;
            GrdReturnItems.DataBind();
        }


        //public void UpdateReturnStatus(string sDB, int ReturnID, string sEmployeeName, string sNotes)
        //{
        //    DataTable DTFromICA = new DataTable();
        //    string sCmd = "Update " + sDB + ".ReturnStock Set ReturnFlag = 1,EmployeeName = '" + sEmployeeName + "', TNotes = '" + sNotes + "' where ReturID = " + ReturnID + "";
        //    iUpdateReturn = ObjCloud.ExecuteNonQuery(sCmd);
        //    for (int i = 0; i < GrdReturnItems.Rows.Count - 1; i++)
        //    {
        //        Label lblBarcode = (Label)GrdReturnItems.Rows[i].Cells[0].FindControl("lblBarcode");
        //        TextBox txtReturnQty = (TextBox)GrdReturnItems.Rows[i].Cells[0].FindControl("txtReturnQTY");
        //        //int InventoryID = ObjSel.id

        //        string sCmdSFromICA = "Select PQuantity from " + sDB + ".inventorycostaverage A join " + sDB + ".inventory B on A.InventoryID = B.InventoryID where barcode = '" + lblBarcode.Text + "'; ";
        //        DTFromICA = ObjMainCloud.GetData_DT(sCmdSFromICA);
        //        if (DTFromICA.Rows.Count > 0)
        //        {
        //            double iToQOH = double.Parse(DTFromICA.Rows[0]["PQuantity"].ToString());
        //            double iLatestFromQOH = iToQOH + double.Parse(txtReturnQty.Text);
        //            string sCmdFromICA = "Update " + sDB + ".inventorycostaverage A inner join " + sDB + ".inventory B on A.InventoryID = B.InventoryID set PQuantity = " + iLatestFromQOH + "  where barcode = '" + lblBarcode.Text + "';";
        //            UpdateFromInventoryCostAverageTable = ObjCloud.ExecuteNonQuery(sCmdFromICA);
        //        }
        //    }
        //}
        public void GetTransferStockHeader(string sBranchAlias, string sTransferID)
        {
            //string sCmd = "Select transferno,date_format(transferdate,'%Y-%m-%d') as transferdate,date_format(senddatetime,'%Y-%m-%d')  as senddatetime, " +
            //               "confirmno,date_format(confirmdatetime,'%Y-%m-%d') as confirmdatetime, " +
            //               "confirmflag from " + sBranchAlias + ".receivestock where receiveid = '" + sTransferID + "';";
            string sCmd = "Select transferno,date_format(transferdate,'%Y-%m-%d') as transferdate,date_format(senddatetime,'%Y-%m-%d')  as senddatetime, " +
                           "confirmno,date_format(confirmdatetime,'%Y-%m-%d') as confirmdatetime, " +
                           "confirmflag from {0}.receivestock where receiveid = @receiveid;";
            string command = string.Format(sCmd, sBranchAlias);
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@receiveid",sTransferID)
            };
            //DTTransferStock = ObjMainCloud.GetData_DT(sCmd);
            DTTransferStock = ObjMain.NewGetData_DT(command, parameters);
            if (DTTransferStock.Rows.Count > 0)
            {
                txtRetrunDate.Text = DTTransferStock.Rows[0]["TransferDate"].ToString();
                //txtSendDate.Text = DTTransferStock.Rows[0]["senddatetime"].ToString();
                txtConfirm.Text = DTTransferStock.Rows[0]["confirmno"].ToString();
                //txtConfirmStatus.Text = DTTransferStock.Rows[0]["confirmflag"].ToString();
            }

        }

        protected void GetFromBranch(string BranchAlias, string sReceiveID)
        {
            //string sCmd = "Select BranchFullName from " + BranchAlias + ".receivestock a join " + BranchAlias + ".branches b on a.frombranchid = b.branchid where receiveid='" + sReceiveID + "';";
            string sCmd = "Select BranchFullName from {0}.receivestock a join {0}.branches b on a.frombranchid = b.branchid where receiveid=@receiveid;";
            string command = string.Format(sCmd, BranchAlias);
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@receiveid",sReceiveID)
            };
            //DTGetFromBranch = ObjMainCloud.GetData_DT(sCmd);
            DTGetFromBranch = ObjMain.NewGetData_DT(command, parameters);
            if (DTGetFromBranch.Rows.Count > 0)
            {
                txtFBranch.Text = DTGetFromBranch.Rows[0]["BranchFullName"].ToString();

            }
        }

        public void LoadReturnDetails(string Branch_AliasName, string sReceiveID)
        {
            //string sCmd = "Select B.Barcode,B.InventoryName,B.Description,'PCS' as unit,PackUnit,RejectQuantity as Rejected " +
            //                "from " + Branch_AliasName + ".receivestockdetail A Join  " + Branch_AliasName + ".Inventory B on " +
            //                "A.barcode=B.barcode where ReceiveID = '" + sReceiveID + "' and RejectQuantity <> 0;";
            string sCmd = "Select B.Barcode,B.InventoryName,B.Description,'PCS' as unit,PackUnit,RejectQuantity as Rejected " +
                            "from {0}.receivestockdetail A Join  {0}.Inventory B on " +
                            "A.barcode=B.barcode where ReceiveID = @receiveid and RejectQuantity <> 0;";
            string command = string.Format(sCmd, Branch_AliasName);
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@receiveid",sReceiveID)
            };
            //DT = ObjMainCloud.GetData_DT(sCmd);
            DT = ObjMain.NewGetData_DT(command, parameters);
            GrdReturnItems.DataSource = DT;
            GrdReturnItems.DataBind();
        }

        public void InsertReturnHeader(string BranchAlias)
        {
            string sFromBranch = ObjSel.GetBranchIDbyName(BranchAlias, txtFBranch.Text.Trim());
            string sToBranch = ObjSel.GetBranchIDbyName(BranchAlias, Session["Branch_HQ"].ToString());
            // string sBranchPrefix = ObjSel.GetBranchPrefix(sFromBranch);
            string iReturnID = ObjSel.GenerateReturnID(BranchAlias).ToString();
            string sReturnNo = txtReturnNo.Text.Trim();
            //string sReturnDate = DateTime.Now.ToString();
            string sReturnDate = Curr_Date;
            string sTransferID = "";
            string sConfrimID = "";
            double dSubtotal = 0.00;
            double dDiscount = 0.00;
            double dGST = 0.00;
            double dTTotal = 0.00;
            Int32 iAcceptTotal = 0;
            string sEmployeeName = "";
            Int32 iReturnFlag = 0;
            string sSInvoice = "";
            string sTNotes = "";


            string sCmd = "Insert Into " + BranchAlias + ".ReturnStock(ReturnID,FromBranchID,ToBranchID,ReturnNo,ReturnDate,TransferID,ConfirmID,Subtotal,Discount,GST,TTotal,AcceptTotal,EmployeeName,ReturnFlag,SInvoiceNo,TNotes)Values" +
                         "('" + iReturnID + "','" + sFromBranch + "','" + sToBranch + "','" + sReturnNo + "','" + sReturnDate + "','" + sTransferID + "','" + sConfrimID + "','" + dSubtotal + "','" + dDiscount + "','" + dGST + "', " +
                         " '" + dTTotal + "','" + iAcceptTotal + "','" + sEmployeeName + "','" + iReturnFlag + "','" + sSInvoice + "','" + sTNotes + "')";
            iInsertHeader = ObjCloud.ExecuteNonQuery(sCmd);


        }

        public void InsertDetails(string BranchAlias, string sReturnID)
        {
            Int32 iDReturnID = 0;
            Int32 iRetrunID = 0;
            Int32 iTSDetailD = 0;
            for (int i = 0; i <= GrdReturnItems.Rows.Count - 1; i++)
            {

                Label lBarcode = (Label)GrdReturnItems.Rows[i].Cells[0].FindControl("Barcode");
                Label lInventoryName = (Label)GrdReturnItems.Rows[i].Cells[1].FindControl("inventoryname");
                Label lDescription = (Label)GrdReturnItems.Rows[i].Cells[2].FindControl("description");
                double dPackUnit = 0.00;
                double dPackPrice = 0.00;

                string sCmd = "Insert Into " + BranchAlias + ".ReturnStockDetail(ReturnDetailID,ReturnID,TSDetailID,Barcode,PackUnit,TransferPrice,ReturnQuantity,Reason)values" +
                            "(" + iDReturnID + "," + iRetrunID + "," + iTSDetailD + ",'" + lBarcode.ToString() + "','" + lInventoryName.ToString() + "','" + lDescription + "'," + dPackUnit + "," + dPackPrice + ")";
                iInsertDetail = ObjCloud.ExecuteNonQuery(sCmd);

            }

        }

        public void GenerateRetrunNo(string FromBranch_AliasName, string ToBranch)//Load Reference No.
        {
            string sReturnNo;
            string sPreFix = string.Empty;
            string sHQ = Session["Branch_HQ"].ToString();
            sPreFix = "RT" + ObjSel.GetBranchPrefix(sHQ, ObjSel.GetBranchFullName(sHQ, ToBranch));
            int NewReturn;
            MySqlConnection Cnn = new MySqlConnection(ConStr_Rds);
            Cnn.Open();
            //string sCmd = "Select ReturnNo from " + FromBranch_AliasName + ".ReturnStock order by returnid desc limit 1;";
            string sCmd = "Select ReturnNo from {0}.ReturnStock order by returnid desc limit 1;";
            MySqlCommand Cmd = new MySqlCommand(sCmd, Cnn);
            sReturnNo = Convert.ToString(Cmd.ExecuteScalar());


            if (sReturnNo.Trim() != "")
            {
                NewReturn = int.Parse(sReturnNo) + 1;
                txtReturnNo.Text = sPreFix + String.Format("{0:D10}", NewReturn);
            }
            else
            {
                txtReturnNo.Text = sPreFix + "0000000001";

            }

        }

        public string GetReturnIDByReturnNo(string sDB, string sReturnNo)
        {
            string sGetReturnIDByReturnNo = string.Empty;
            DataTable DT = new DataTable();
            //string sCmd = "Select ReturnID from " + sDB + ".returnstock where ReturnNo = '" + sReturnNo + "'";
            string sCmd = "Select ReturnID from {0}.returnstock where ReturnNo = @returnno";
            string command = string.Format(sCmd, sDB);
            MySqlParameter[] parameters ={
                new MySqlParameter("@returnno", sReturnNo)
            };
            //DT = ObjMainCloud.GetData_DT(sCmd);
            DT = ObjMain.NewGetData_DT(command, parameters);
            if (DT.Rows.Count > 0)
            {
                sGetReturnIDByReturnNo = DT.Rows[0]["ReturnID"].ToString();
            }

            return sGetReturnIDByReturnNo;
        }
        protected void btnReceive_Click(object sender, EventArgs e)
        {
            // InsertReturnHeader(Session["Branch_HQ"].ToString());
        }

        protected void btnConfirmReturn_Click(object sender, EventArgs e)
        {
            string FromDB = Session["FromDB"].ToString();
            int iReturnID = int.Parse(GetReturnIDByReturnNo(FromDB, txtReturnNo.Text.Trim()));
            string sEmpoyeeName = "";
            string sNotes = txtNotes.Text;
            // UpdateReturnStatus(FromDB, iReturnID,sEmpoyeeName, sNotes);
        }

        [WebMethod]
        public static string GetReturnHeader(string DB, string ReturnID)
        {
            DataTable DTHeader = new DataTable();
            MYSQLDB MySqlObj = new MYSQLDB();

            string JSONResult = string.Empty;
            string Query = "Select * from " + DB + ".returnstock where returnid = @returnid";
            string command = string.Format(Query, DB);
            MySqlParameter[] parameters = {
                new MySqlParameter("@returnid",ReturnID)
            };

            DTHeader = MySqlObj.NewGetData_DT(command, parameters);
            JSONResult = JsonConvert.SerializeObject(DTHeader);
            return JSONResult;
        }

        [WebMethod]
        public static string GetReturnDetail(string DB, string ReturnID)
        {
            DataTable DTDetail = new DataTable();
            MYSQLDB MySqlObj = new MYSQLDB();

            string JSONResult = string.Empty;
            string Query = "Select A.Barcode,Inventoryname,Description,Unit,ReturnQuantity from " + DB + ".returnstockdetail A " +
                        " join " + DB + ".Inventory B on A.Barcode = B.Barcode where returnid = @returnid; ";
            string command = string.Format(Query, DB);
            MySqlParameter[] parameters = {
                new MySqlParameter("@returnid",ReturnID)
            };
            DTDetail = MySqlObj.NewGetData_DT(command, parameters);
            JSONResult = JsonConvert.SerializeObject(DTDetail);
            return JSONResult;
        }

        [WebMethod]
        public static string GetBranchFullName(string DB, string BranchID)
        {
            DataTable DTBranch = new DataTable();
            MYSQLDB MysqlObj = new MYSQLDB();

            string JSONResult = string.Empty;
            string sQuery = "Select * from " + DB + ".branches where branchid = @BranchID";
            string command = string.Format(sQuery, BranchID);
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@BranchID",BranchID)
            };

            DTBranch = MysqlObj.NewGetData_DT(command, parameters);
            JSONResult = JsonConvert.SerializeObject(DTBranch);
            return JSONResult;
        }

        [WebMethod]
        public static string GET_ReturnStockDetailList(string db)
        {
            BL_API objAPI = new BL_API();
            string bearertoken = string.Empty;
            bearertoken = objAPI.GetBarerToken();
            string json = objAPI.GET_ReturnStockDetailList(bearertoken, db);
            var obj = JsonConvert.DeserializeObject(json);
            return obj.ToString();
        }

    }
}