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

namespace HH.WMS.DAL.Basic
{
    public class TN_WM_B_AREA_QTYDAL : DapperBaseDAL
    {
        /// <summary>
        /// 降库区量表并降分配量
        /// </summary>
        /// <param name="AreaQtyList">库区量实体集合</param>
        /// <param name="zanCunLocation">暂存区</param>
        /// <param name="trans"></param>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult ReduceAreaAllocQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in AreaQtyList)
            {
                TN_WM_B_AREA_QTYEntity.CheckNullValue(model);
                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='" + areaCode
                   + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE
                   + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO
                   + "'  and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
                   + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' ";


                strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY  " + sqlWhere + "  and CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + ">=0)");
                strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
                strSql.Append(" else ");
                strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY =0  " + sqlWhere + " ");

                Log.Detail(logPara, "降到货区库区量表sqlWhere：" + sqlWhere);
                Log.Detail(logPara, "降到货区库区量表strSql：" + strSql.ToString());

                result = ExecuteTranSql(strSql.ToString(), null, trans);

                Log.Detail(logPara, "降到货区库区量表影响行数: " + result.AffectedRows.ToString());
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            Log.Detail(logPara, "降低库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
            return result;
        }
        /// <summary>
        /// 获得库区下每个物料的数量
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="itemCodes"></param>
        /// <returns></returns>
        public List<TN_WM_B_AREA_QTYEntity> GetAreaQty(string strWhere)
        {
            var strSql = string.Format(@" select CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_PRODUCTION_BATCH,CN_S_LOT_NO,CN_S_STOCK_CODE,
                                    CN_S_STOCK_AREA, CAST(CN_F_QUANTITY AS INT ) AS CN_F_QUANTITY,CAST(CN_F_QUANTITY AS INT) AS CN_F_ALLOC_QTY,CN_S_MODEL,
                                    CN_S_FIGURE_NO,CN_S_MEASURE_UNIT  
                                from TN_WM_B_AREA_QTY  where {0}", strWhere).Replace("\r\n", "");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql);
            List<TN_WM_B_AREA_QTYEntity> list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_AREA_QTYEntity>(this.DataAccess, cmd, SetEntity);
            return list;
        }

        private void SetEntity(TN_WM_B_AREA_QTYEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
            SetEntityUti(entity, "CN_S_OWNER", "CN_S_OWNER", reader);
            SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
            SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
            SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader);
            SetEntityUti(entity, "CN_S_LOT_NO", "CN_S_LOT_NO", reader);
            SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
            SetEntityUti(entity, "CN_F_QUANTITY", "CN_F_QUANTITY", reader);
            SetEntityUti(entity, "CN_F_ALLOC_QTY", "CN_F_ALLOC_QTY", reader);
            SetEntityUti(entity, "CN_S_MODEL", "CN_S_MODEL", reader);
            SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader);
            SetEntityUti(entity, "CN_S_MEASURE_UNIT", "CN_S_MEASURE_UNIT", reader);
            SetEntityUti(entity, "CN_S_PRODUCTION_BATCH", "CN_S_PRODUCTION_BATCH", reader);
        }

        /// <summary>
        /// 根据物料汇总当前库存
        /// </summary>
        /// <param name="itemCode">物料编码</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public TN_WM_B_AREA_QTYEntity GetAreaSummaryQty(string itemCode, string strWhere)
        {
            TN_WM_B_AREA_QTYEntity areaQtyEntity = new TN_WM_B_AREA_QTYEntity();

            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT CN_S_ITEM_CODE,SUM(CN_F_QUANTITY) CN_F_QUANTITY FROM TN_WM_B_AREA_QTY WHERE CN_S_ITEM_CODE='" + itemCode + "' ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                sbStr.Append(strWhere);
            }
            sbStr.Append("GROUP BY CN_S_ITEM_CODE");

            return ExecuteSingleEntity<TN_WM_B_AREA_QTYEntity>(sbStr.ToString());
        }
        /// <summary>
        /// 根据物料汇总当前库存
        /// </summary>
        /// <param name="itemCode">物料编码</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public List<TN_WM_B_AREA_QTY_YSEntity> GetStockQtyInfo(string strWhere)
        {
            List<TN_WM_B_AREA_QTY_YSEntity> areaQtyEntity = new List<TN_WM_B_AREA_QTY_YSEntity>();

            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT CN_S_ITEM_CODE as itemCode ,CN_S_ITEM_NAME as itemName,CN_S_STOCK_CODE as stockCode,CN_S_STOCK_AREA as areaCode,(CN_F_QUANTITY - CN_F_ALLOC_QTY) as availableWeight FROM TN_WM_B_AREA_QTY WHERE 1=1  ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                sbStr.Append(strWhere);
            }
           // sbStr.Append("GROUP BY CN_S_ITEM_CODE");

            return ExecuteQuery<TN_WM_B_AREA_QTY_YSEntity>(sbStr.ToString());
        }

        public List<TN_WM_B_STOCK_DETAIL_YSEntity> GetStockQtyDetailList(string strWhere)
        {
            List<TN_WM_B_STOCK_DETAIL_YSEntity> lstStockDetail = new List<TN_WM_B_STOCK_DETAIL_YSEntity>();

            StringBuilder sbStr = new StringBuilder();

            sbStr.Append(" select c.CN_S_STOCK_CODE as stockCode,c.CN_S_STOCK_AREA as areaCode,a.CN_S_TRAY_CODE as trayCode,a.CN_S_ITEM_CODE as itemCode, ");
            sbStr.Append(" a.CN_S_ITEM_NAME as itemName,a.CN_S_MODEL as itemModel,SUM(cast(b.CN_S_SERIAL_NO as decimal(10,4))) AS itemWeight from dbo.TN_WM_B_TRAY_ITEM_MST a  ");
            sbStr.Append(" inner join dbo.TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID = b.CN_PARENT_GUID  ");
            sbStr.Append(" inner join dbo.tn_wm_b_tray_location c on a.CN_S_TRAY_CODE = c.CN_S_TRAY_CODE ");
            sbStr.Append(" group by c.CN_S_STOCK_CODE ,c.CN_S_STOCK_AREA ,a.CN_S_TRAY_CODE ,a.CN_S_ITEM_CODE ,a.CN_S_ITEM_NAME ,a.CN_S_MODEL ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                sbStr.Append(strWhere);
            }
            return ExecuteQuery<TN_WM_B_STOCK_DETAIL_YSEntity>(sbStr.ToString());
        }

        /// <summary>
        /// 获得库区下每个物料的数量
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="itemCodes"></param>
        /// <returns></returns>
        public List<OutAreaItemQty> GetAreaQty(string strWhere, string stockCode)
        {
            string strSql = @"select 
                              SUM(CN_F_QUANTITY) - SUM(CN_F_ALLOC_QTY) as Qty,CN_S_ITEM_CODE as itemCode,CN_S_STOCK_AREA as areaCode 
                              from TN_WM_B_AREA_QTY 
                              WHERE 1=1  " + strWhere + " AND CN_S_STOCK_CODE='" + stockCode + "'   GROUP by CN_S_STOCK_AREA,CN_S_ITEM_CODE ";
            List<OutAreaItemQty> list = ExecuteQuery<OutAreaItemQty>(strSql.ToString());
            return list;
        }

        /// <summary>
        /// 根据货主获取库存
        /// </summary>
        /// <param name="ownerCode"></param>
        /// <returns></returns>
        public List<TN_WM_B_AREA_QTYEntity> GetAreaQtyByOwner(string ownerCode)
        {
            string strSql = @"select * from (select distinct  aq.CN_S_OWNER,aq.CN_S_ITEM_CODE,aq.CN_S_ITEM_NAME,aq.CN_S_ITEM_STATE,aq.CN_S_LOT_NO,aq.CN_S_STOCK_CODE,aq.CN_S_STOCK_AREA,
aq.CN_F_QUANTITY-(SELECT ISNULL(SUM(CN_F_QUANTITY),0) FROM TN_WM_ITEMBACK_DTL DTL LEFT JOIN TN_WM_ITEMBACK_MST MST 
ON MST.CN_S_OP_NO=DTL.CN_S_OP_NO WHERE MST.CN_S_OWNER=aq.CN_S_OWNER 
 and DTL.CN_S_ITEM_CODE=aq.CN_S_ITEM_CODE and dtl.CN_S_LOT_NO=aq.CN_S_LOT_NO
 and DTL.CN_S_PRODUCTION_BATCH=aq.CN_S_PRODUCTION_BATCH and CN_S_STATE='新建') CN_F_QUANTITY,aq.CN_F_PLANNED_QTY,aq.CN_F_ALLOC_QTY,aq.CN_S_NOTE,aq.CN_S_TIMESTAMP,aq.CN_S_PRODUCTION_BATCH
  from dbo.tn_wm_b_area_qty aq   where aq.CN_S_OWNER='" + ownerCode + "') A  where A.CN_F_QUANTITY>0";
            List<TN_WM_B_AREA_QTYEntity> list = ExecuteQuery<TN_WM_B_AREA_QTYEntity>(strSql.ToString());
            return list;
        }

        /// <summary>
        /// 入库单取消时，判断物料是否可取消
        /// </summary>
        /// <param name="opNoList"></param>
        /// <returns></returns>
        public List<TN_WM_B_AREA_QTYEntity> GetCancelData(string opNoList)
        {
            string strSql = @" select Q.* from TN_WM_B_AREA_QTY Q,TN_WM_INCREASE_INVENTORY_DTL IND,TN_WM_INCREASE_INVENTORY_MST MST
                                WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE 
                                AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE
                                AND Q.CN_S_LOT_NO=MST.CN_S_LOT_NO AND ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') AND Q.CN_S_OWNER=MST.CN_S_OWNER AND IND.CN_S_OP_NO=MST.CN_S_OP_NO 
                                AND IND.CN_S_OP_NO in (" + opNoList + ")";
            List<TN_WM_B_AREA_QTYEntity> list = ExecuteQuery<TN_WM_B_AREA_QTYEntity>(strSql.ToString());
            return list;
        }

        /// <summary>
        /// 升库区库存
        /// </summary>
        /// <param name="AreaQtyList"></param>
        /// <param name="isDefaultOwner">是否默认货主</param>
        /// <param name="trans"></param>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult AddAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, IDbTransaction trans, bool isDefaultOwner = false)
        {
            OperateResult result = new OperateResult();
            foreach (var model in AreaQtyList)
            {
                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
                    + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
                    + "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "' ";

                StringBuilder strSql = new StringBuilder();
                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(" 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,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)");
                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,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
                Log.Info("===>影响库区量详细", strSql.ToString());
                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_GUID = model.CN_GUID,
                    CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()),
                    CN_S_ITEM_CODE = model.CN_S_ITEM_CODE,
                    CN_S_ITEM_NAME = model.CN_S_ITEM_NAME,
                    CN_S_ITEM_STATE = model.CN_S_ITEM_STATE,
                    CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO,
                    CN_S_STOCK_CODE = model.CN_S_STOCK_CODE,
                    CN_F_QUANTITY = model.CN_F_QUANTITY,
                    CN_F_ALLOC_QTY = model.CN_F_ALLOC_QTY,
                    CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY,
                    CN_S_STOCK_AREA = model.CN_S_STOCK_AREA,
                    CN_S_NOTE = model.CN_S_NOTE,
                    CN_S_TIMESTAMP = model.CN_S_TIMESTAMP,
                    CN_S_MODEL = model.CN_S_MODEL,
                    CN_S_FIGURE_NO = model.CN_S_FIGURE_NO,
                    CN_S_MEASURE_UNIT = model.CN_S_MEASURE_UNIT,
                    CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        /// <summary>
        /// 升库区库存
        /// </summary>
        /// <param name="AreaQtyList"></param>
        /// <param name="areaCode"></param>
        /// <param name="logPara"></param>
        /// <param name="trans"></param>
        /// <param name="isDefaultOwner"></param>
        /// <returns></returns>
        public OperateResult AddAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans, bool isDefaultOwner = false)
        {
            if (logPara == null)
                logPara = LogType.LogPara("升库区库存量表");
            OperateResult result = new OperateResult();
            foreach (var model in AreaQtyList)
            {
                TN_WM_B_AREA_QTYEntity.CheckNullValue(model);
                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='" + areaCode
                    + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE
                    + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO
                    + "'  and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
                    + "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "' ";

                StringBuilder strSql = new StringBuilder();
                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 + 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,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)");
                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,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
                Log.Info("===>影响库区量详细", strSql.ToString());
                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_GUID = System.Guid.NewGuid().ToString().ToUpper(),
                    CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()),
                    CN_S_ITEM_CODE = model.CN_S_ITEM_CODE,
                    CN_S_ITEM_NAME = model.CN_S_ITEM_NAME,
                    CN_S_ITEM_STATE = model.CN_S_ITEM_STATE,
                    CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO,
                    CN_S_STOCK_CODE = model.CN_S_STOCK_CODE,
                    CN_F_QUANTITY = model.CN_F_QUANTITY,
                    CN_F_ALLOC_QTY = 0,
                    CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY,
                    CN_S_STOCK_AREA = areaCode,
                    CN_S_NOTE = "",
                    CN_S_TIMESTAMP = "",
                    CN_S_MODEL = model.CN_S_MODEL,
                    CN_S_FIGURE_NO = "",
                    CN_S_MEASURE_UNIT = "",
                    CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            Log.Detail(logPara, "增加库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
            return result;
        }

        
        /// <summary>
        /// 升库区库存
        /// </summary>
        /// <param name="AreaQtyList"></param>
        /// <param name="isDefaultOwner">是否默认货主</param>
        /// <param name="trans"></param>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult AddAreaQtyAndAlloc(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, IDbTransaction trans, bool isDefaultOwner = false)
        {
            OperateResult result = new OperateResult();
            foreach (var model in AreaQtyList)
            {
                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
                    + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
                    + "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "' ";

                StringBuilder strSql = new StringBuilder();
                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(" 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,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)");
                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,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
                Log.Info("===>影响库区量详细", strSql.ToString());
                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_GUID = model.CN_GUID,
                    CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()),
                    CN_S_ITEM_CODE = model.CN_S_ITEM_CODE,
                    CN_S_ITEM_NAME = model.CN_S_ITEM_NAME,
                    CN_S_ITEM_STATE = model.CN_S_ITEM_STATE,
                    CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO,
                    CN_S_STOCK_CODE = model.CN_S_STOCK_CODE,
                    CN_F_QUANTITY = model.CN_F_QUANTITY,
                    CN_F_ALLOC_QTY = model.CN_F_ALLOC_QTY,
                    CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY,
                    CN_S_STOCK_AREA = model.CN_S_STOCK_AREA,
                    CN_S_NOTE = model.CN_S_NOTE,
                    CN_S_TIMESTAMP = model.CN_S_TIMESTAMP,
                    CN_S_MODEL = model.CN_S_MODEL,
                    CN_S_FIGURE_NO = model.CN_S_FIGURE_NO,
                    CN_S_MEASURE_UNIT = model.CN_S_MEASURE_UNIT,
                    CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        /// <summary>
        /// 升库区库存
        /// </summary>
        /// <param name="AreaQtyList"></param>
        /// <param name="areaCode"></param>
        /// <param name="logPara"></param>
        /// <param name="trans"></param>
        /// <param name="isDefaultOwner"></param>
        /// <returns></returns>
        public OperateResult AddAreaQtyAndAlloc(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans, bool isDefaultOwner = false)
        {
            if (logPara == null)
                logPara = LogType.LogPara("升库区库存量表");
            OperateResult result = new OperateResult();
            foreach (var model in AreaQtyList)
            {
                TN_WM_B_AREA_QTYEntity.CheckNullValue(model);
                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='" + areaCode
                    + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE
                    + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO
                    + "'  and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
                    + "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "' ";

                StringBuilder strSql = new StringBuilder();
                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_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,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)");
                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,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
                Log.Info("===>影响库区量详细", strSql.ToString());
                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_GUID = System.Guid.NewGuid().ToString().ToUpper(),
                    CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()),
                    CN_S_ITEM_CODE = model.CN_S_ITEM_CODE,
                    CN_S_ITEM_NAME = model.CN_S_ITEM_NAME,
                    CN_S_ITEM_STATE = model.CN_S_ITEM_STATE,
                    CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO,
                    CN_S_STOCK_CODE = model.CN_S_STOCK_CODE,
                    CN_F_QUANTITY = model.CN_F_QUANTITY,
                    CN_F_ALLOC_QTY = 0,
                    CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY,
                    CN_S_STOCK_AREA = areaCode,
                    CN_S_NOTE = "",
                    CN_S_TIMESTAMP = "",
                    CN_S_MODEL = "",
                    CN_S_FIGURE_NO = "",
                    CN_S_MEASURE_UNIT = "",
                    CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            Log.Detail(logPara, "增加库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
            return result;
        }


        /// <summary>
        /// 升分配量
        /// </summary>
        /// <param name="areaQty"></param>
        /// <param name="trans"></param>
        /// <param name="logPara"></param>
        /// <returns></returns>
        public OperateResult AddAllocQty(TN_WM_B_AREA_QTYEntity areaQty, IDbTransaction trans, LogPara logPara)
        {
            TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty);
            string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + areaQty.CN_F_QUANTITY
                    + " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE 
                    + "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE 
                    + "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA
                    + "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO 
                    + "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH
                    + "' ";
            Log.Detail(logPara, "升库区分配量执行SQL语句：" + sql);
            var result = ExecuteTranSql(sql, null, trans);
            return result;
        }

        /// <summary>
        /// 降分配量
        /// </summary>
        /// <param name="areaQty"></param>
        /// <param name="trans"></param>
        /// <param name="logPara"></param>
        /// <returns></returns>
        public OperateResult ReduceAllocQty(TN_WM_B_AREA_QTYEntity areaQty, IDbTransaction trans, LogPara logPara)
        {
            TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty);
            string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY
                    + " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE
                    + "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE
                    + "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA
                    + "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO
                    + "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH
                    + "' ";
            Log.Detail(logPara, "降库区分配量执行SQL语句：" + sql);
            var result = ExecuteTranSql(sql, null, trans);
            return result;
        }

        /// <summary>
        /// 降分配量
        /// </summary>
        /// <param name="areaQty"></param>
        /// <param name="trans"></param>
        /// <param name="logPara"></param>
        /// <returns></returns>
        public OperateResult ReduceAllocQty(string areaCode, string trayCode, IDbTransaction trans, LogPara logPara)
        {
            var result = new OperateResult();
            var trayItemMsts = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_MSTEntity>>().GetList(new { CN_S_TRAY_CODE = trayCode });
            foreach (var trayItemMst in trayItemMsts)
            {
                trayItemMst.CN_F_QUANTITY = 0;
                trayItemMst.TrayItemDtlList = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_DTLEntity>>().GetList(new { CN_PARENT_GUID = trayItemMst.CN_GUID });
                foreach (var trayItemDtl in trayItemMst.TrayItemDtlList)
                {
                    trayItemMst.CN_F_QUANTITY += Convert.ToDecimal(trayItemDtl.CN_S_SERIAL_NO);
                }

                var areaQty = new TN_WM_B_AREA_QTYEntity
                        {
                            CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY,
                            CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE,
                            CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE,
                            CN_S_STOCK_AREA = areaCode,
                            CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO,
                            CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH
                        };
                TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty);
                string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY
                        + " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE
                        + "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE
                        + "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA
                        + "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO
                        + "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH
                        + "' ";
                Log.Detail(logPara, "降库区分配量执行SQL语句：" + sql);
                result = ExecuteTranSql(sql, null, trans);

                if (!result.Success) return result;
            }
            return result;
        }

        public OperateResult ReduceAreaAndStockAllocQty(string areaCode, string trayCode, IDbTransaction trans, LogPara logPara)
        {
            var result = new OperateResult();
            var trayItemMsts = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_MSTEntity>>().GetList(new { CN_S_TRAY_CODE = trayCode });
            foreach (var trayItemMst in trayItemMsts)
            {
                trayItemMst.CN_F_QUANTITY = 0;
                trayItemMst.TrayItemDtlList = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_DTLEntity>>().GetList(new { CN_PARENT_GUID = trayItemMst.CN_GUID });
                foreach (var trayItemDtl in trayItemMst.TrayItemDtlList)
                {
                    trayItemMst.CN_F_QUANTITY += Convert.ToDecimal(trayItemDtl.CN_S_SERIAL_NO);
                }

                var areaQty = new TN_WM_B_AREA_QTYEntity
                {
                    CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY,
                    CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE,
                    CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE,
                    CN_S_STOCK_AREA = areaCode,
                    CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO,
                    CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH
                };
                var stockQty = new TN_WM_B_STOCK_QTYEntity
                {
                    CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY,
                    CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE,
                    CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE,
                    CN_S_STOCK_AREA = areaCode,
                    CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO,
                    CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH
                };
                TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty);
                TN_WM_B_STOCK_QTYEntity.CheckNullValue(stockQty);
                string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY
                        + " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE
                        + "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE
                        + "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA
                        + "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO
                        + "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH
                        + "' ";
                Log.Detail(logPara, "降库区分配量执行SQL语句：" + sql);
                result = ExecuteTranSql(sql, null, trans);

                sql = " Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + stockQty.CN_F_QUANTITY
                        + " Where CN_S_ITEM_CODE='" + stockQty.CN_S_ITEM_CODE
                        + "' and CN_S_ITEM_STATE='" + stockQty.CN_S_ITEM_STATE
                        + "' and CN_S_LOT_NO='" + stockQty.CN_S_LOT_NO
                        + "' and CN_S_PRODUCTION_BATCH='" + stockQty.CN_S_PRODUCTION_BATCH
                        + "' ";
                Log.Detail(logPara, "降仓库分配量执行SQL语句：" + sql);
                result = ExecuteTranSql(sql, null, trans);

                if (!result.Success) return result;
            }
            return result;
        }

        /// <summary>
        /// 降暂存区库区量表
        /// </summary>
        /// <param name="AreaQtyList">库区量实体集合</param>
        /// <param name="zanCunLocation">暂存区</param>
        /// <param name="trans"></param>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult ReduceAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            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 + @"'  
                            and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + @"' 
                            and CN_S_OWNER='" + model.CN_S_OWNER + "' and CN_S_STOCK_AREA ='" + model.CN_S_STOCK_AREA.Trim() + "' ";


                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 + " ");

                Log.Info("降到货区库区量表sqlWhere", sqlWhere);
                Log.Info("降到货区库区量表strSql", strSql.ToString());

                result = ExecuteTranSql(strSql.ToString(), null, trans);

                Log.Info("降到货区库区量表影响行数", result.AffectedRows.ToString());
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        /// <summary>
        /// 降暂存区库区量表
        /// </summary>
        /// <param name="AreaQtyList">库区量实体集合</param>
        /// <param name="zanCunLocation">暂存区</param>
        /// <param name="trans"></param>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult ReduceAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in AreaQtyList)
            {
                TN_WM_B_AREA_QTYEntity.CheckNullValue(model);
                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='" + areaCode
                   + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE
                   + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO
                   + "'  and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
                   + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' ";

                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 + " ");

                Log.Detail(logPara, "降到货区库区量表sqlWhere：" + sqlWhere);
                Log.Detail(logPara, "降到货区库区量表strSql" + strSql.ToString());

                result = ExecuteTranSql(strSql.ToString(), null, trans);

                Log.Detail(logPara, "降到货区库区量表影响行数：" + result.AffectedRows.ToString());
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            Log.Detail(logPara, "降低库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
            return result;
        }

        /// <summary>
        /// 降货主所在库区量
        /// </summary>
        /// <param name="AreaQtyList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult ReduceOwnerAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            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 + "'  and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' ";


                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 + " ");

                Log.Info("降货主所在库区量sqlWhere", sqlWhere);
                Log.Info("降货主所在库区量strSql", strSql.ToString());

                result = ExecuteTranSql(strSql.ToString(), null, trans);

                Log.Info("降货主所在库区量表影响行数", result.AffectedRows.ToString());
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        /// <summary>
        /// 降库区量表并降分配量
        /// </summary>
        /// <param name="AreaQtyList">库区量实体集合</param>
        /// <param name="zanCunLocation">暂存区</param>
        /// <param name="trans"></param>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult ReduceAreaQtyAndAllocQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in AreaQtyList)
            {
                TN_WM_B_AREA_QTYEntity.CheckNullValue(model);
                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='" + areaCode
                   + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE
                   + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO
                   + "'  and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
                   + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' ";


                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 + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
                strSql.Append(" else ");
                strSql.Append("DELETE FROM  TN_WM_B_AREA_QTY  " + sqlWhere + " ");

                Log.Detail(logPara, "降到货区库区量表sqlWhere：" + sqlWhere);
                Log.Detail(logPara, "降到货区库区量表strSql：" + strSql.ToString());

                result = ExecuteTranSql(strSql.ToString(), null, trans);

                Log.Detail(logPara, "降到货区库区量表影响行数: " + result.AffectedRows.ToString());
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            Log.Detail(logPara, "降低库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
            return result;
        }
      
        /// <summary>
        /// 修改库区分配量
        /// </summary>
        /// <param name="areaQtyList"></param>
        /// <returns></returns>
        public OperateResult UpdateAreaAlloc(List<TN_WM_B_AREA_QTYEntity> areaQtyList, IDbTransaction trans)
        {
            string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)";
            sql = string.Format(sql, DateTime.Now.ToString("yyyyMMddHHmmssffff"));
            return ExecuteTranSql(sql, areaQtyList, trans);
        }

        /// <summary>
        /// 修改库区数量
        /// </summary>
        /// <param name="areaQtyList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateAreaQty(List<TN_WM_B_AREA_QTYEntity> areaQtyList, IDbTransaction trans)
        {
            string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY=@CN_F_QUANTITY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)";
            sql = string.Format(sql, DateTime.Now.ToString("yyyyMMddHHmmssffff"));
            return ExecuteTranSql(sql, areaQtyList, trans);
        }

        /// <summary>
        /// 修改库存量和分配量
        /// </summary>
        /// <param name="areaQtyList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateAreaQtyAndAlloc(List<TN_WM_B_AREA_QTYEntity> areaQtyList, IDbTransaction trans)
        {
            string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY=@CN_F_QUANTITY,CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)";
            sql = string.Format(sql, DateTime.Now.ToString("yyyyMMddHHmmssffff"));
            return ExecuteTranSql(sql, areaQtyList, trans);
        }

        /// <summary>
        /// 修改多条数据库存量
        /// </summary>
        /// <param name="lstModel"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateAreaListAllocBysqlwhere(List<TN_WM_B_AREA_QTYEntity> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (TN_WM_B_AREA_QTYEntity entity in list)
            {
                string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY + " + entity.CN_F_ALLOC_QTY + " " + entity.SqlWhere + "";

                result = ExecuteTranSql(sql, null, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        /// <summary>
        /// 根据移库单  修改库区数量 移出库区减去数量 移入库区加上数量
        /// </summary>
        /// <param name="lstModel"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <history>[HANHE(LT)] CREATED BY 2018-12-20</history>
        public SqlExecuteResult MoveInfoUpdateAreaQty(List<TN_WM_B_AREA_QTYEntity> lstModel, DbTransaction trans)
        {
            SqlExecuteResult result = new SqlExecuteResult();
            List<DbCommand> cmdlist = new List<DbCommand>();
            foreach (TN_WM_B_AREA_QTYEntity model in lstModel)
            {

                StringBuilder strSql = new StringBuilder();

                //移出库区数量
                //strSql.Append("UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-:CN_F_QUANTITY,CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-:CN_F_QUANTITY WHERE CN_S_STOCK_AREA='" + model.startArea + "' AND " + model.SqlWhere + "");

                strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY  WHERE CN_S_STOCK_AREA='" + model.endArea + "' AND " + model.SqlWhere1 + " )");
                strSql.Append("UPDATE TN_WM_B_AREA_QTY SET CN_S_OWNER='" + model.endBit + "',CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY WHERE CN_S_STOCK_AREA='" + model.endArea + "' AND " + model.SqlWhere1 + "");
                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,CN_S_TIMESTAMP,CN_S_PRODUCTION_BATCH)");
                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,:CN_S_TIMESTAMP,:CN_S_PRODUCTION_BATCH)");
                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, 0);
                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);
                DataAccess.AddInParameter(cmd, "CN_S_TIMESTAMP", ComDbType.STRING, model.CN_S_TIMESTAMP);
                DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, model.CN_S_PRODUCTION_BATCH);
                cmdlist.Add(cmd);
            }
            return ExecuteCommands(cmdlist, trans);
        }

        /// <summary>
        /// 降库区库存量表
        /// </summary>
        /// <param name="opNoList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-15</History>
        public OperateResult DeleteAreaQtys(List<TN_WM_B_AREA_QTYEntity> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();

            foreach (TN_WM_B_AREA_QTYEntity entity in list)
            {
                string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =CN_F_QUANTITY-{1} {0}";
                sql = string.Format(sql, entity.SqlWhere, entity.CN_F_QUANTITY);
                result = ExecuteTranSql(sql.ToString(), null, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        /// <summary>
        /// 降某个特定库区所在的库区量表
        /// </summary>
        /// <param name="arrivalCode">到货单号</param>
        /// <param name="itemCode">物料编码</param>
        /// <param name="areaCode">库区编码</param>
        /// <param name="offSetQuantity">补缺数量</param>
        /// <param name="trans"></param>
        public OperateResult DeleteAreaQtyByAreaCode(string arrivalCode, string itemCode, string areaCode, string offSetQuantity, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-" + Convert.ToDecimal(offSetQuantity) + @" FROM 
                                    TN_WM_B_AREA_QTY Q,TN_WM_INCREASE_INVENTORY_DTL IND,TN_WM_INCREASE_INVENTORY_MST INMST
                                    WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE  
                                                            AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE
                                                            AND Q.CN_S_LOT_NO=IND.CN_S_LOT_NO  AND INMST.CN_S_OP_NO=IND.CN_S_OP_NO
                                                            AND Q.CN_S_OWNER=IND.CN_S_OWNER  AND  INMST.CN_S_ARRIVAL_NO='" + arrivalCode + @"' 
                                                            AND Q.CN_S_ITEM_CODE='" + itemCode + "' AND Q.CN_S_STOCK_AREA='" + areaCode + "' ";
            Log.Info("===>降某个特定库区所在的库区量表", sql.ToString());
            result = ExecuteTranSql(sql.ToString(), null, trans);
            if (result.Success)
            {
                string sqlstr = @" DELETE FROM TN_WM_B_AREA_QTY where CN_F_QUANTITY<=0 ";
                result = ExecuteTranSql(sqlstr.ToString(), null, trans);
            }
            return result;
        }

        /// <summary>
        /// 入库单反审时降库区量表
        /// </summary>
        /// <param name="opNoList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-15</History>
        public OperateResult DeleteAreaQty(string opNoList, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM 
                                    TN_WM_B_AREA_QTY Q,TN_WM_INCREASE_INVENTORY_DTL IND,TN_WM_INCREASE_INVENTORY_MST MST
                                    WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE 
                                                            AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE
                                                            AND Q.CN_S_LOT_NO=MST.CN_S_LOT_NO AND ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') AND Q.CN_S_OWNER=MST.CN_S_OWNER AND IND.CN_S_OP_NO=MST.CN_S_OP_NO 
                                                            AND IND.CN_S_OP_NO in (" + opNoList + ")";
            result = ExecuteTranSql(sql.ToString(), null, trans);
            if (result.Success)
            {
                string sqlstr = @" DELETE FROM TN_WM_B_AREA_QTY where CN_F_QUANTITY<=0 ";
                result = ExecuteTranSql(sqlstr.ToString(), null, trans);
            }
            return result;
        }

        /// <summary>
        /// 到货单反审时降库区量表
        /// </summary>
        /// <param name="opNoList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2019-03-04</History>
        public OperateResult DeleteAreaQtyArr(string opNoList, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM 
                                    TN_WM_B_AREA_QTY Q,TN_WM_ARRIVAL_DTL IND,TN_WM_ARRIVAL_MST MST
                                    WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE 
                                                            AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE
                                                            AND Q.CN_S_LOT_NO=MST.CN_S_LOT_NO AND ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') AND Q.CN_S_OWNER=MST.CN_S_OWNER AND IND.CN_S_OP_NO=MST.CN_S_OP_NO 
                                                            AND IND.CN_S_OP_NO in (" + opNoList + ")";
            result = ExecuteTranSql(sql.ToString(), null, trans);
            if (result.Success)
            {
                string sqlstr = @" DELETE FROM TN_WM_B_AREA_QTY where CN_F_QUANTITY<=0 ";
                result = ExecuteTranSql(sqlstr.ToString(), null, trans);
            }
            return result;
        }

        /// <summary>
        /// 删除为0库区
        /// </summary>
        /// <returns></returns>
        public OperateResult DeleteAreaQty(IDbTransaction trans)
        {
            string sql = "DELETE dbo.tn_wm_b_area_qty WHERE CN_F_QUANTITY<=0";
            return ExecuteTranSql(sql, null, trans);
        }
       
    }
}