using HanHe.Utility.Data;
using HH.WMS.Entitys.Basic;
using HH.WMS.Entitys.Common;
using HH.WMS.Entitys.External;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HH.WMS.DAL.External
{
    /// <summary>
    /// 库存数量数据处理类
    /// </summary>
    /// <history>[HanHe(HHC)] CREATED 2018/3/5</history>
    public class TN_WM_B_STOCK_QTYDAL : BaseDAL
    {
        #region 根据Guid获取
        /// <summary>
        /// 根据Guid获取
        /// </summary>
        /// <param name="guidList"></param>
        /// <returns></returns>
        public List<TN_WM_B_STOCK_QTYEntity> GetStockByGuid(List<string> guidList)
        {
            string sql = @"SELECT * FROM TN_WM_B_STOCK_QTY WHERE CN_GUID IN ('" + string.Join("','", guidList) + "')";
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteListEntity<TN_WM_B_STOCK_QTYEntity>(this.DataAccess, cmd,
               (e, r) =>
               {
                   SetEntityUti(e, "CN_GUID", "CN_GUID", r);                    //GUID
                   SetEntityUti(e, "CN_F_QUANTITY", "CN_F_QUANTITY", r);        //库存数量
                   SetEntityUti(e, "CN_F_PLANNED_QTY", "CN_F_PLANNED_QTY", r);  //在单量
                   SetEntityUti(e, "CN_F_ALLOC_QTY", "CN_F_ALLOC_QTY", r);      //分配量
               });
        }
        #endregion

        #region 影响仓库量表
        public SqlExecuteResult AddStockQty(List<TN_WM_B_AREA_QTYEntity> stockQtyList, DbTransaction trans)
        {
            SqlExecuteResult result = new SqlExecuteResult();
            foreach (TN_WM_B_AREA_QTYEntity model in stockQtyList)
            {
                StringBuilder strSql = new StringBuilder();
                string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"'  
                            and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"' 
                            and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"' 
                            and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' ";

                strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY  " + sqlWhere + " )");
                strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
                strSql.Append(" else ");
                strSql.Append("insert into TN_WM_B_STOCK_QTY(");
                strSql.Append("CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_STOCK_CODE,CN_F_QUANTITY,CN_F_ALLOC_QTY,CN_F_PLANNED_QTY,CN_S_NOTE)");
                strSql.Append(" values (");
                strSql.Append(":CN_GUID,:CN_S_OWNER,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_ITEM_STATE,:CN_S_LOT_NO,:CN_S_STOCK_CODE,:CN_F_QUANTITY,:CN_F_ALLOC_QTY,:CN_F_PLANNED_QTY,:CN_S_NOTE)");
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, model.CN_GUID);
                DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, model.CN_S_OWNER);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, model.CN_S_ITEM_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, model.CN_S_ITEM_NAME);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, model.CN_S_ITEM_STATE);
                DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, model.CN_S_LOT_NO);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, model.CN_S_STOCK_CODE);
                DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, model.CN_F_QUANTITY);
                DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, model.CN_F_ALLOC_QTY);
                DataAccess.AddInParameter(cmd, "CN_F_PLANNED_QTY", ComDbType.DECIMAL, model.CN_F_PLANNED_QTY);
                DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, model.CN_S_NOTE);
                result = ExecuteCommand(cmd, trans);
                if (!result.Success)
                {
                    break;
                }
            }
            return result;
        }
        //降暂仓库量表
        public SqlExecuteResult reduceStockQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, DbTransaction trans)
        {
            SqlExecuteResult result = new SqlExecuteResult();


            foreach (TN_WM_B_AREA_QTYEntity model in AreaQtyList)
            {

                StringBuilder strSql = new StringBuilder();
                string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"'  
                            and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"'
                            and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"' 
                            and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "'";

                strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY  " + sqlWhere + "  and CN_F_QUANTITY-" + model.CN_F_QUANTITY + ">0)");
                strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
                strSql.Append(" else ");
                strSql.Append("DELETE FROM  TN_WM_B_STOCK_QTY  " + sqlWhere + " ");
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                result = ExecuteCommand(cmd, trans);
                if (!result.Success)
                {
                    break;
                }
            }
            return result;
        }
        public SqlExecuteResult AddStockAllocQty(List<string> trayCodes, string stockCode, string areaCode, DbTransaction trans)
        {
            var trayItemRelList = DALCreator.Create<TN_WM_B_TRAY_ITEM_RELDAL>().GetListByTrays(trayCodes);

            //增加库存分配数量
            List<TN_WM_B_AREA_QTYEntity> stockQtyList = new List<TN_WM_B_AREA_QTYEntity>();
            foreach (TN_WM_B_TRAY_ITEM_RELEntity itemRel in trayItemRelList)
            {
                TN_WM_B_AREA_QTYEntity entityQty = new TN_WM_B_AREA_QTYEntity();
                entityQty.CN_S_ITEM_STATE = itemRel.CN_S_ITEM_STATE;
                entityQty.CN_F_QUANTITY = itemRel.CN_F_QUANTITY;
                entityQty.CN_GUID = Guid.NewGuid().ToString();
                entityQty.CN_S_ITEM_CODE = itemRel.CN_S_ITEM_CODE;
                entityQty.CN_S_ITEM_NAME = itemRel.CN_S_ITEM_NAME;
                entityQty.CN_S_LOT_NO = itemRel.CN_S_PRODUCTION_BATCH;
                entityQty.CN_S_OWNER = itemRel.CN_S_OWNER;
                entityQty.CN_S_STOCK_AREA = areaCode;
                entityQty.CN_S_STOCK_CODE = stockCode;
                stockQtyList.Add(entityQty);
            }
            SqlExecuteResult result = new SqlExecuteResult();
            foreach (TN_WM_B_AREA_QTYEntity model in stockQtyList)
            {
                StringBuilder strSql = new StringBuilder();
                string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"'  
                            and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"' 
                            and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"' 
                            and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' ";

                strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY  " + sqlWhere + " )");
                strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + model.CN_F_ALLOC_QTY + " " + sqlWhere + " ");
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                result = ExecuteCommand(cmd, trans);
                if (!result.Success)
                {
                    break;
                }
            }
            return result;
        }
        //降暂仓库量表
        public SqlExecuteResult reduceStockAllocQty(List<string> trayCodes, string stockCode, string areaCode, DbTransaction trans)
        {
            var trayItemRelList = DALCreator.Create<TN_WM_B_TRAY_ITEM_RELDAL>().GetListByTrays(trayCodes);

            //增加库存分配数量
            List<TN_WM_B_AREA_QTYEntity> stockQtyList = new List<TN_WM_B_AREA_QTYEntity>();
            foreach (TN_WM_B_TRAY_ITEM_RELEntity itemRel in trayItemRelList)
            {
                TN_WM_B_AREA_QTYEntity entityQty = new TN_WM_B_AREA_QTYEntity();
                entityQty.CN_S_ITEM_STATE = itemRel.CN_S_ITEM_STATE;
                entityQty.CN_F_QUANTITY = itemRel.CN_F_QUANTITY;
                entityQty.CN_GUID = Guid.NewGuid().ToString();
                entityQty.CN_S_ITEM_CODE = itemRel.CN_S_ITEM_CODE;
                entityQty.CN_S_ITEM_NAME = itemRel.CN_S_ITEM_NAME;
                entityQty.CN_S_LOT_NO = itemRel.CN_S_PRODUCTION_BATCH;
                entityQty.CN_S_OWNER = itemRel.CN_S_OWNER;
                entityQty.CN_S_STOCK_AREA = areaCode;
                entityQty.CN_S_STOCK_CODE = stockCode;
                stockQtyList.Add(entityQty);

            }
            SqlExecuteResult result = new SqlExecuteResult();
            foreach (TN_WM_B_AREA_QTYEntity model in stockQtyList)
            {

                StringBuilder strSql = new StringBuilder();
                string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"'  
                            and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"'
                            and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"' 
                            and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "'";

                strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY  " + sqlWhere + "  and CN_F_ALLOC_QTY-" + model.CN_F_ALLOC_QTY + ">0)");
                strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_ALLOC_QTY + " " + sqlWhere + " ");
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                result = ExecuteCommand(cmd, trans);
                if (!result.Success)
                {
                    break;
                }
            }
            return result;
        }
        #endregion

    }
}