using HH.WMS.Common;
using HH.WMS.Common.External;
using HH.WMS.Entitys.Common;
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_AREA_QTYDAL : BaseDAL
    {
        #region 影响库区量表
        public SqlExecuteResult AddAreaQty(List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity> stockAreaQtyList, DbTransaction trans)
        {
            SqlExecuteResult result = new SqlExecuteResult();
            foreach (var model in stockAreaQtyList)
            {
                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_STOCK_AREA='" + model.CN_S_STOCK_AREA + @"'
                            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_AREA_QTY  " + sqlWhere + " )");
                //  strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + model.CN_F_ALLOC_QTY + " " + sqlWhere + " ");
                strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
                strSql.Append(" else ");
                strSql.Append("insert into TN_WM_B_AREA_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_STOCK_AREA,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_STOCK_AREA,: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_STOCK_AREA", ComDbType.STRING, model.CN_S_STOCK_AREA);
                DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, model.CN_S_NOTE);
                //Log.Info("AddAreaQty", "sql语句：" + strSql.ToString());
                Log.Info(LogType.CheckTempService, "AddAreaQty！sql语句：" + strSql.ToString());
                result = ExecuteCommand(cmd, trans);
                if (!result.Success)
                {
                    break;
                }
            }
            return result;
        }

        //降暂存区库区量表
        public SqlExecuteResult reduceAreaQty(List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity> AreaQtyList, DbTransaction trans)
        {
            SqlExecuteResult result = new SqlExecuteResult();


            foreach (var 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 + "' and CN_S_STOCK_AREA  = '" + model.CN_S_STOCK_AREA + "' ";

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

        public SqlExecuteResult addAreaAllocQty(List<string> trayCodes, string stockCode, string areaCode, DbTransaction trans)
        {
            var trayItemRelList = DALCreator.Create<TN_WM_B_TRAY_ITEM_RELDAL>().GetListByTrays(trayCodes);

            //增加库存分配数量
            List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity> stockAreaQtyList = new List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity>();
            foreach (var itemRel in trayItemRelList)
            {
                HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity entityQty = new HH.WMS.Entitys.Basic.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;
                stockAreaQtyList.Add(entityQty);
            }
            SqlExecuteResult result = new SqlExecuteResult();
            foreach (HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity model in stockAreaQtyList)
            {
                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 + "' and CN_S_STOCK_AREA  = '" + model.CN_S_STOCK_AREA + "' ";

                strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY  " + sqlWhere + "  and CN_F_ALLOC_QTY-" + model.CN_F_ALLOC_QTY + ">0)");
                strSql.Append("Update TN_WM_B_AREA_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 reduceAreaAllocQty(List<string> trayCodes, string stockCode, string areaCode, DbTransaction trans)
        {
            var trayItemRelList = DALCreator.Create<TN_WM_B_TRAY_ITEM_RELDAL>().GetListByTrays(trayCodes);

            //增加库存分配数量
            var stockAreaQtyList = new List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity>();
            foreach (var itemRel in trayItemRelList)
            {
                var entityQty = new HH.WMS.Entitys.Basic.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;
                stockAreaQtyList.Add(entityQty);

            }
            SqlExecuteResult result = new SqlExecuteResult();
            foreach (var model in stockAreaQtyList)
            {


                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 + "' and CN_S_STOCK_AREA  = '" + model.CN_S_STOCK_AREA + "' ";

                strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY  " + sqlWhere + "  and CN_F_ALLOC_QTY-" + model.CN_F_ALLOC_QTY + ">0)");
                strSql.Append("Update TN_WM_B_AREA_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
    }
}