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

namespace HH.WMS.DAL.Basic
{
    /// <summary>
    /// 仓库量表
    /// </summary>
    public class TN_WM_B_STOCK_QTYDAL : DapperBaseDAL
    {
        /// <summary>
        /// 获取仓库量表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="stockCode"></param>
        /// <param name="stockArea"></param>
        /// <param name="itemCode"></param>
        /// <param name="owner"></param>
        /// <returns></returns>
        public OperateResult GetEnableCheckQty(int pageIndex, int pageSize, string stockCode, string stockArea, string itemCode, string owner)
        {
            string where = "";
            if (!string.IsNullOrEmpty(itemCode))
                where += " and CN_S_ITEM_CODE like '%" + itemCode + "%' ";
            if (!string.IsNullOrEmpty(owner))
                where += " and CN_S_OWNER ='" + owner + "'";

            string sql = @"(SELECT 
                          CN_S_STOCK_CODE,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,
                          CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,sum(CN_F_QUANTITY) CN_F_QUANTITY
                        FROM tn_wm_b_stock_qty 
                        where  CN_S_STOCK_CODE=@CN_S_STOCK_CODE " + where + @"
                      group by CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,
                          CN_S_STOCK_CODE,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT ) t";

            return ExecutePagingResult(sql, pageIndex, pageSize, " order by CN_S_ITEM_CODE", new { CN_S_STOCK_CODE = stockCode });
        }

        #region 升/降仓库库存

        /// <summary>
        /// 升仓库库存
        /// </summary>
        /// <param name="stockQtyList"></param>
        /// <param name="trans"></param>
        /// <param name="isDefaultOwner">是否默认货主</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-15</History>
        public OperateResult AddStockQty(List<TN_WM_B_STOCK_QTYEntity> stockQtyList, IDbTransaction trans, bool isDefaultOwner = false)
        {
            OperateResult result = new OperateResult();
            foreach (var model in stockQtyList)
            {
                try
                {
                    TN_WM_B_STOCK_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_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_STOCK_QTY  " + sqlWhere + " )");
                    strSql.Append("Update TN_WM_B_STOCK_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_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_F_PRELOCK_QTY,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_F_PRELOCK_QTY,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");

                    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_F_PRELOCK_QTY = 0,
                        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;
                }
                catch (Exception)
                {

                }
            }
            return result;
        }

        /// <summary>
        /// 降仓库库存
        /// </summary>
        /// <param name="reduceStockQty"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult ReduceStockQty(List<TN_WM_B_STOCK_QTYEntity> reduceStockQty, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (TN_WM_B_STOCK_QTYEntity model in reduceStockQty)
            {
                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 + "' ";

                strSql.Append(" if exists(select CN_GUID 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 + " ");

                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="reduceStockQty"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult ReduceStockQtyAndAllocQty(List<TN_WM_B_STOCK_QTYEntity> reduceStockQty, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in reduceStockQty)
            {
                TN_WM_B_STOCK_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_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 CN_GUID 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 + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
                strSql.Append(" else ");
                strSql.Append("DELETE FROM  TN_WM_B_STOCK_QTY  " + sqlWhere + " ");

                result = ExecuteTranSql(strSql.ToString(), null, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
        /// <summary>
        /// 降仓库库存和分配量
        /// </summary>
        /// <param name="reduceStockQty"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult ReduceStockAllocQty(List<TN_WM_B_STOCK_QTYEntity> reduceStockQty, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in reduceStockQty)
            {
                TN_WM_B_STOCK_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_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 CN_GUID from TN_WM_B_STOCK_QTY  " + sqlWhere + "  and CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + ">=0)");
                strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");

                strSql.Append(" else ");
                strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY =0  " + sqlWhere + " ");

                result = ExecuteTranSql(strSql.ToString(), null, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
      
        /// <summary>
        /// 降货主所在仓库量
        /// </summary>
        /// <param name="StockQtyList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult ReduceOwnerStockQty(List<TN_WM_B_STOCK_QTYEntity> StockQtyList, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (TN_WM_B_STOCK_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 + "'  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_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 + " ");

                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;
        }

        #endregion

        #region 升/降仓库分配量

        /// <summary>
        /// 升仓库分配量
        /// </summary>
        /// <param name="stockQty"></param>
        /// <param name="trans"></param>
        /// <param name="logPara"></param>
        /// <returns></returns>
        public OperateResult AddAllocQty(TN_WM_B_STOCK_QTYEntity stockQty , IDbTransaction trans, LogPara logPara)
        {
            string 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_STOCK_CODE='" + stockQty.CN_S_STOCK_CODE
                + "' 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);
            var result = ExecuteTranSql(sql, null, trans);
            return result;
        }

        /// <summary>
        /// 降仓库分配量
        /// </summary>
        /// <param name="stockQty"></param>
        /// <param name="trans"></param>
        /// <param name="logPara"></param>
        /// <returns></returns>
        public OperateResult ReduceAllocQty(TN_WM_B_STOCK_QTYEntity stockQty, IDbTransaction trans, LogPara logPara)
        {
            string 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_STOCK_CODE='" + stockQty.CN_S_STOCK_CODE
                + "' 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);
            var result = ExecuteTranSql(sql, null, trans);
            return result;
        }

        #endregion

        /// <summary>
        /// 修改库存编辑量
        /// </summary>
        /// <param name="stockList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateStockEditingQty(List<TN_WM_B_STOCK_QTYEntity> stockList, IDbTransaction trans, string timeStamp = "")
        {
            if (!stockList.Any()) return OperateResult.Succeed();
            string sql = "UPDATE TN_WM_B_STOCK_QTY SET CN_F_EDITING_QTY=@CN_F_EDITING_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, string.IsNullOrEmpty(timeStamp) ? DateTime.Now.ToString("yyyyMMddHHmmssffff") : timeStamp);
            return ExecuteTranSql(sql, stockList, trans);
        }

        /// <summary>
        /// 修改库存分配量
        /// </summary>
        /// <param name="stockList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateStockAllocQty(List<TN_WM_B_STOCK_QTYEntity> stockList, IDbTransaction trans, string timeStamp = "")
        {
            if (!stockList.Any()) return OperateResult.Succeed();
            string sql = "UPDATE TN_WM_B_STOCK_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, string.IsNullOrEmpty(timeStamp) ? DateTime.Now.ToString("yyyyMMddHHmmssffff") : timeStamp);
            return ExecuteTranSql(sql, stockList, trans);
        }

        /// <summary>
        /// 修改库存数量
        /// </summary>
        /// <param name="stockList"></param>
        /// <param name="trans"></param>
        /// <param name="timeStamp"></param>
        /// <returns></returns>
        public OperateResult UpdateStockQty(List<TN_WM_B_STOCK_QTYEntity> stockList, IDbTransaction trans, string timeStamp = "")
        {
            string sql = "UPDATE TN_WM_B_STOCK_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, string.IsNullOrEmpty(timeStamp) ? DateTime.Now.ToString("yyyyMMddHHmmssffff") : timeStamp);
            return ExecuteTranSql(sql, stockList, trans);
        }

        /// <summary>
        /// 降库存
        /// </summary>
        /// <param name="lockType"></param>
        /// <param name="outLockList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateAllocQtyByLock(string lockType, List<TN_WM_B_OUTLOCKING_DTLEntity> outLockList, IDbTransaction trans)
        {
            string sql = string.Empty;
            if (lockType.Equals(Constants.Rule_OutOrder)) //根据出库单降库存
            {
                sql = "UPDATE TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY = CN_F_ALLOC_QTY-@CN_F_QUANTITY,CN_F_QUANTITY=CN_F_QUANTITY-@CN_F_QUANTITY WHERE CN_GUID =@CN_S_TABLE_ID";
            }
            else if (lockType.Equals(Constants.Rule_SortingNo)) //根据分拣单降库存
            {
                sql = "UPDATE TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY = CN_F_ALLOC_QTY-@CN_F_QUANTITY,CN_F_QUANTITY=CN_F_QUANTITY-@CN_F_QUANTITY WHERE CN_GUID =@CN_S_TABLE_ID";
            }
            if (!string.IsNullOrEmpty(sql))
                return ExecuteTranSql(sql, outLockList, trans);
            return OperateResult.Succeed();
        }

        /// <summary>
        /// 修改多条数据库存量
        /// </summary>
        /// <param name="lstModel"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public SqlExecuteResult UpdateListBysqlwhere(List<TN_WM_B_STOCK_QTYEntity> list, DbTransaction trans)
        {
            SqlExecuteResult result = new SqlExecuteResult();
            List<DbCommand> cmdlist = new List<DbCommand>();
            foreach (TN_WM_B_STOCK_QTYEntity entity in list)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY  " + entity.SqlWhere + " )");
                if (entity.CN_F_QUANTITY > entity.CN_F_ALLOC_QTY)
                {
                    strSql.Append("UPDATE tn_wm_b_stock_qty SET CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY WHERE CN_S_OWNER=:CN_S_OWNER AND CN_S_ITEM_CODE=:CN_S_ITEM_CODE AND CN_S_ITEM_STATE=:CN_S_ITEM_STATE AND CN_S_STOCK_CODE=:CN_S_STOCK_CODE AND CN_S_LOT_NO=:CN_S_LOT_NO AND CN_S_PRODUCTION_BATCH=:CN_S_PRODUCTION_BATCH");
                    entity.CN_F_QUANTITY = entity.CN_F_QUANTITY - entity.CN_F_ALLOC_QTY;
                }
                else
                {
                    strSql.Append("UPDATE tn_wm_b_stock_qty SET CN_F_QUANTITY=CN_F_QUANTITY-:CN_F_QUANTITY WHERE CN_S_OWNER=:CN_S_OWNER AND CN_S_ITEM_CODE=:CN_S_ITEM_CODE AND CN_S_ITEM_STATE=:CN_S_ITEM_STATE AND CN_S_STOCK_CODE=:CN_S_STOCK_CODE AND CN_S_LOT_NO=:CN_S_LOT_NO AND CN_S_PRODUCTION_BATCH=:CN_S_PRODUCTION_BATCH");
                    entity.CN_F_QUANTITY = entity.CN_F_ALLOC_QTY - entity.CN_F_QUANTITY;
                }
                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_F_PRELOCK_QTY,CN_S_PRODUCTION_BATCH,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT)");

                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_F_PRELOCK_QTY,:CN_S_PRODUCTION_BATCH,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_S_MEASURE_UNIT)");

                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
                DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
                DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
                DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.INT32, entity.CN_F_QUANTITY);
                DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, 0);
                DataAccess.AddInParameter(cmd, "CN_F_PLANNED_QTY", ComDbType.DECIMAL, entity.CN_F_PLANNED_QTY);
                DataAccess.AddInParameter(cmd, "CN_F_PRELOCK_QTY", ComDbType.DECIMAL, entity.CN_F_PRELOCK_QTY);
                DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH);
                DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
                DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
                DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
                cmdlist.Add(cmd);

            }
            return ExecuteCommands(cmdlist, trans);
        }

        /// <summary>
        /// 删除锁定明细
        /// </summary>
        /// <param name="outLockList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult DeleteOutLock(List<TN_WM_B_OUTLOCKING_DTLEntity> outLockList, IDbTransaction trans)
        {
            string sql = @"DELETE FROM TN_WM_B_OUTLOCKING_DTL WHERE CN_GUID=@CN_GUID";
            return ExecuteTranSql(sql, outLockList, trans);
        }

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

                StringBuilder strSql = new StringBuilder();

                //移出库区数量
                strSql.Append("UPDATE TN_WM_B_STOCK_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-:CN_F_QUANTITY WHERE CN_S_STOCK_CODE='" + entity.CN_S_STOCK_CODE + "' AND " + entity.SqlWhere + "");

                strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY  WHERE  " + entity.SqlWhere1 + " )");
                strSql.Append("UPDATE TN_WM_B_STOCK_QTY SET CN_S_OWNER='" + entity.endBit + "',CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY WHERE CN_S_STOCK_CODE='" + entity.CN_S_STOCK_CODE + "' AND " + entity.SqlWhere1 + "");
                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_F_PRELOCK_QTY,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_F_PRELOCK_QTY,:CN_S_TIMESTAMP,:CN_S_PRODUCTION_BATCH)");
                DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
                DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
                DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
                DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
                DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
                DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
                DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
                DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, 0);
                DataAccess.AddInParameter(cmd, "CN_F_PLANNED_QTY", ComDbType.DECIMAL, entity.CN_F_PLANNED_QTY);
                DataAccess.AddInParameter(cmd, "CN_F_PRELOCK_QTY", ComDbType.DECIMAL, entity.CN_F_PRELOCK_QTY);

                DataAccess.AddInParameter(cmd, "CN_S_TIMESTAMP", ComDbType.STRING, entity.CN_S_TIMESTAMP);
                DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.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(lt)] CREATED BY 2019-1-21</History>
        public OperateResult DeleteStockQtys(List<TN_WM_B_STOCK_QTYEntity> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();

            foreach (TN_WM_B_STOCK_QTYEntity entity in list)
            {
                string sql = @" UPDATE TN_WM_B_STOCK_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>
        /// 删除为0的数据
        /// </summary>
        /// <returns></returns>
        public OperateResult DeleteStockQty(IDbTransaction trans)
        {
            string sql = "DELETE dbo.tn_wm_b_stock_qty WHERE CN_F_QUANTITY<=0 ";
            return ExecuteTranSql(sql, null, trans);
        }

        /// <summary>
        /// 入库单反审时降仓库量表
        /// </summary>
        /// <param name="opNoList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-15</History>
        public OperateResult DeleteStockQty(string opNoList, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            string sql = @" UPDATE TN_WM_B_STOCK_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM 
                                    TN_WM_B_STOCK_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 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_STOCK_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 DeleteStockQtyArr(string opNoList, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            string sql = @" UPDATE TN_WM_B_STOCK_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM 
                                    TN_WM_B_STOCK_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 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_STOCK_QTY where CN_F_QUANTITY<=0 ";
                result = ExecuteTranSql(sqlstr.ToString(), null, trans);
            }
            return result;
        }
    }
}