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

namespace HH.WMS.DAL.InStock
{
    /// <summary>
    /// 增加库存业务表-入库单数据处理层
    /// </summary>
    public class TN_WM_INCREASE_INVENTORYDAL : DapperBaseDAL
    {
        #region 更新入库单状态
        /// <summary>
        /// 提交入库单-主表
        /// </summary>
        /// <param name="entity">审批实体</param>
        /// <param name="examineType">审批类型（已提交、已审核、已反审、已驳回）</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult ExamineMst(List<TN_WM_B_EXAMINEEntity> entity, string examineType, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in entity)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" UPDATE TN_WM_INCREASE_INVENTORY_MST SET CN_S_STATE=@CN_S_STATE,CN_S_AUDITOR=@CN_S_AUDITOR,CN_T_AUDIT_TIME=@CN_T_AUDIT_TIME,CN_S_AUDIT_REMARK=@CN_S_AUDIT_REMARK WHERE CN_S_OP_NO=@CN_S_OP_NO");

                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_S_STATE = examineType,
                    CN_S_OP_NO = model.CN_S_FROM_NO,
                    CN_S_AUDITOR = model.CN_S_CREATOR,
                    CN_T_AUDIT_TIME = DateTime.Now,
                    CN_S_AUDIT_REMARK = model.CN_S_OPINION
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        /// <summary>
        /// 提交入库单-子表
        /// </summary>
        /// <param name="entity">审批实体</param>
        /// <param name="examineType">审批类型（已提交、已审核、已反审、已驳回）</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult ExamineDtl(List<TN_WM_B_EXAMINEEntity> entity, string examineType, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in entity)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_S_STATE=@CN_S_STATE WHERE CN_S_OP_NO=@CN_S_OP_NO");

                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_S_STATE = examineType,
                    CN_S_OP_NO = model.CN_S_FROM_NO
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
        #endregion

        #region 查询数据，返回数据集合
        //根据入库单号获取数据集合
        /// <summary>
        /// 根据入库单号获取数据
        /// </summary>
        /// <param name="opNo">入库单号</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-15</History>
        public DataTable GetIncreaseData(string opNo)
        {
            StringBuilder sbStr = new StringBuilder();
            DataTable dt = new DataTable();

            sbStr.Append("SELECT MST.CN_S_VENDOR_NO,MST.CN_S_VENDOR_NAME,MST.CN_S_OP_TYPE,MST.CN_S_FROM_NO,(SELECT TOP 1 CN_S_OPERATION_CODE FROM  dbo.tn_wm_operation_type ty WHERE ty.CN_S_OPERATION_NAME=MST.CN_S_OP_TYPE ) CN_S_OPERATION_CODE,MST.CN_S_OWNER,MST.CN_S_STOCK_CODE,MST.CN_S_LOT_NO,DTL.* FROM TN_WM_INCREASE_INVENTORY_DTL DTL LEFT JOIN TN_WM_INCREASE_INVENTORY_MST MST ");
            sbStr.Append("ON DTL.CN_S_OP_NO =MST.CN_S_OP_NO ");

            if (!string.IsNullOrEmpty(opNo))
            {
                sbStr.Append("WHERE MST.CN_S_OP_NO='" + opNo + "' ");
            }
            else
            {
                return dt;
            }
            return ExecuteDataTable(sbStr.ToString());
        }


        public DataTable GetIncreaseList(SearchModel searchModel, out long total, bool isExport)
        {
            var condition = searchModel.SearchCondition;
            string sql = " select * from TN_WM_INCREASE_INVENTORY_MST where 1=1 ";
            string ConStr = "";
            if (condition != null)
            {
                if (!string.IsNullOrEmpty(condition.CN_S_OP_NO.ToString()))
                {
                    ConStr = ConStr + " and CN_S_OP_NO like '%" + Util.ToStringInput(condition.CN_S_OP_NO) + "%'";
                }
                string state = condition.CN_S_STATE.ToString();
                if (!string.IsNullOrEmpty(state))
                {
                    if (state.IndexOf(",") > 0)
                    {
                        state = "'" + state.Replace(",", "','") + "'";
                        ConStr = ConStr + " and CN_S_STATE in (" + state + ")";
                    }
                    else
                    {
                        ConStr = ConStr + " and CN_S_STATE='" + state + "'";
                    }
                }

                if (!string.IsNullOrEmpty(condition.CN_T_START.ToString()))
                {
                    ConStr = ConStr + " and CN_T_CREATE >= '" + Util.ToStringInput(condition.CN_T_START) + " 00:00:00'";
                }
                if (!string.IsNullOrEmpty(condition.CN_T_END.ToString()))
                {
                    ConStr = ConStr + " and CN_T_CREATE <= '" + Util.ToStringInput(condition.CN_T_END) + " 23:59:59'";
                }


                ConStr = ConStr + " and CN_S_OP_TYPE != '盈亏入库' and  CN_S_OP_TYPE != '盘盈入库'";

                if (!string.IsNullOrEmpty(condition.CN_S_OP_TYPE.ToString()))
                {
                    ConStr = ConStr + " and CN_S_OP_TYPE = '" + Util.ToStringInput(condition.CN_S_OP_TYPE) + "'";
                }
                if (!string.IsNullOrEmpty(condition.CN_S_VENDOR_NAME.ToString()))
                {
                    ConStr = ConStr + " and CN_S_VENDOR_NAME like '%" + Util.ToStringInput(condition.CN_S_VENDOR_NAME) + "%'";
                }
                if (!string.IsNullOrEmpty(condition.IsHandWork.ToString()))
                {
                    if (condition.IsHandWork.ToString() == "true" || condition.IsHandWork.ToString() == "True")
                        ConStr = ConStr + " and (CN_S_FROM_NO='' or CN_S_FROM_NO IS NULL) ";
                    else
                        ConStr = ConStr + " and (CN_S_FROM_NO!='' and CN_S_FROM_NO IS not NULL) ";
                }
            }
            if (!string.IsNullOrEmpty(ConStr))
            {
                sql = sql + ConStr;
            }
            OperateResult result = new OperateResult();
            DataTable dt = new DataTable();
            if (!isExport)
            {
                return ExecutePagingData(" (" + sql + ") t ", searchModel.PageIndex, searchModel.PageSize, out total, "", "");
            }
            else
            {
                total = 0;//无效
                return ExecuteDataTable(sql, "");
            }
            // return ExecutePagingResult(sql, searchModel.PageIndex, searchModel.PageSize, "", "");
        }




        #endregion

        #region 获取可上架入库单列表(PDA)
        /// <summary>
        /// 获取可上架入库单列表
        /// </summary>
        /// <param name="CN_S_OP_NO">模糊查询条件：入库单号</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-13</History>
        public List<TN_WM_INCREASE_INVENTORY_MSTEntity> GetIncreaseInventoryList(string CN_S_OP_NO)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT CN_S_OP_NO,CN_S_OP_TYPE,CN_S_STOCK_CODE,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE from TN_WM_INCREASE_INVENTORY_MST ");
            sbStr.Append("WHERE CN_S_OP_NO IN (SELECT CN_S_OP_NO FROM TN_WM_INCREASE_INVENTORY_DTL WHERE CN_S_STATE='" + Constants.State_Audit + "'  AND CN_S_OP_TYPE!='盘盈入库'  ");

            switch (NowDbType)
            {
                case DBTYPE.MSSQL:
                    sbStr.Append("AND ISNULL(CN_F_UPSHELF_QTY,0)<ISNULL(CN_F_QUANTITY,0) ");
                    break;
                case DBTYPE.MYSQL:
                    sbStr.Append("AND IFNULL(CN_F_UPSHELF_QTY,0)<IFNULL(CN_F_QUANTITY,0) ");
                    break;
                case DBTYPE.ORACLE:
                    sbStr.Append("AND NVL(CN_F_UPSHELF_QTY,0)<NVL(CN_F_QUANTITY,0) ");
                    break;
                default:
                    break;
            }
            sbStr.Append(")");

            if (!string.IsNullOrEmpty(CN_S_OP_NO))
            {
                sbStr.Append("AND CN_S_OP_NO LIKE '%" + CN_S_OP_NO + "%' ");
            }
            return ExecuteQuery<TN_WM_INCREASE_INVENTORY_MSTEntity>(sbStr.ToString()).ToList();
        }
        #endregion

        #region 获取可码盘入库单列表(PDA)
        /// <summary>
        /// 获取可码盘入库单列表
        /// </summary>
        /// <param name="CN_S_OP_NO">模糊查询条件：入库单号</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-12-5</History>
        public List<TN_WM_INCREASE_INVENTORY_MSTEntity> GetInListWithCodedisc(string CN_S_OP_NO)
        {
            string sql = "SELECT CN_S_OP_NO,CN_S_OP_TYPE,CN_S_STOCK_CODE,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE from TN_WM_INCREASE_INVENTORY_MST WHERE CN_S_OP_NO IN (SELECT CN_S_OP_NO FROM TN_WM_INCREASE_INVENTORY_DTL WHERE ISNULL(CN_F_ADD_ONTRAY_QTY,0)<CN_F_QUANTITY) AND CN_S_STATE ='" + Constants.State_Audit + "' AND CN_S_OP_TYPE!='盘盈入库' ";
            if (!string.IsNullOrEmpty(CN_S_OP_NO))
            {
                sql = sql + " AND CN_S_OP_NO like '%" + CN_S_OP_NO + "%'";
            }
            return ExecuteQuery<TN_WM_INCREASE_INVENTORY_MSTEntity>(sql);
        }
        #endregion

        #region 根据入库单号获取入库单明细(PDA)
        /// <summary>
        /// 根据入库单号获取入库单明细
        /// </summary>
        /// <param name="CN_S_OP_NO">入库单号</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-13</History>
        public List<TN_WM_INCREASE_INVENTORY_DTLEntity> GetIncreaseInventoryDetail(string CN_S_OP_NO)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT MST.CN_S_OP_TYPE,MST.CN_S_STOCK_CODE,MST.CN_S_VENDOR_NO,MST.CN_S_VENDOR_NAME,DTL.CN_S_OP_NO, ");
            sbStr.Append("DTL.CN_S_ITEM_CODE,DTL.CN_S_ITEM_NAME,DTL.CN_S_ITEM_STATE,DTL.CN_F_QUANTITY,MST.CN_S_LOT_NO,MST.CN_S_OWNER,DTL.CN_F_ACTUAL_PRICE,DTL.CN_T_PRODUCTION,DTL.CN_T_EXPIRATION,DTL.CN_S_PRODUCTION_BATCH ");
            sbStr.Append("FROM TN_WM_INCREASE_INVENTORY_DTL DTL LEFT JOIN TN_WM_INCREASE_INVENTORY_MST MST ");
            sbStr.Append("ON DTL.CN_S_OP_NO=MST.CN_S_OP_NO ");

            if (!string.IsNullOrEmpty(CN_S_OP_NO))
            {
                sbStr.Append("WHERE MST.CN_S_OP_NO='" + CN_S_OP_NO.Trim() + "' ");
            }
            return ExecuteQuery<TN_WM_INCREASE_INVENTORY_DTLEntity>(sbStr.ToString()).ToList();
        }
        #endregion

        #region 更新累积入库数
        /// <summary>
        /// 更新累积入库数
        /// </summary>
        /// <param name="list">更新实体集合</param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult UpdateAllocQty(List<ItemRow> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var item in list)
            {
                string sq1 = "UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_ALLOT_QTY=CN_F_ALLOT_QTY+@CN_F_ALLOT_QTY WHERE CN_S_OP_NO=@CN_S_OP_NO AND CN_S_ITEM_CODE=@CN_S_ITEM_CODE";
                result = ExecuteTranSql(sq1, new
                {
                    CN_F_ALLOT_QTY = item.CN_F_QUANTITY,
                    CN_S_OP_NO = item.CN_S_FROM_NO,
                    CN_S_ITEM_CODE = item.CN_S_ITEM_CODE
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
        #endregion

        #region 获取入库
        /// <summary>
        /// 获取入库
        /// </summary>
        /// <param name="sqlwhere">条件</param>
        /// <returns></returns>
        /// <History>[HANHE(lt)] CREATED BY 2018-11-15</History>
        public DataTable GetDataTableInventory(string sqlwhere)
        {
            string sql = "SELECT * FROM TN_WM_INCREASE_INVENTORY_MST AS a INNER JOIN dbo.TN_WM_INCREASE_INVENTORY_DTL AS b ON b.CN_S_OP_NO = a.CN_S_OP_NO where {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql);
        }
        #endregion

        #region 获取入库列表
        /// <summary>
        /// 获取入库列表
        /// </summary>
        /// <param name="sqlwhere">条件</param>
        /// <returns></returns>
        /// <History>[HANHE(lt)] CREATED BY 2018-11-22</History>
        public DataTable GetList(string sqlwhere)
        {
            string sql = "select * from TN_WM_INCREASE_INVENTORY_MST where {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql);
        }
        #endregion

        #region 获取入库订单列表
        /// <summary>
        /// 获取入库订单列表
        /// </summary>
        /// <param name="sqlwhere">条件</param>
        /// <returns></returns>
        /// <History>[HANHE(lt)] CREATED BY 2018-11-22</History>
        public DataTable GetInMstList(string sqlwhere)
        {
            string sql = "select * from tn_wm_in_mst {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql);
        }


        /// <summary>
        /// 获取入库订单列表
        /// </summary>
        /// <param name="sqlwhere">条件</param>
        /// <returns></returns>
        /// <History>[HANHE(lt)] CREATED BY 2018-11-22</History>
        public DataTable GetInDtlList(string sqlwhere)
        {
            string sql = "select * from tn_wm_in_dtl {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql);
        }
        #endregion

        #region 更改入库单状态
        /// <summary>
        /// 更改入库单状态(已码盘)
        /// </summary>
        /// <param name="list">更新列表</param>
        /// <param name="state">状态</param>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-17</History>
        public OperateResult UpdateInventoryState(List<TrayItemRow> list, string state)
        {
            OperateResult result = new OperateResult();
            foreach (var item in list)
            {
                StringBuilder sbStr = new StringBuilder();
                sbStr.Append(" IF  EXISTS( SELECT CN_S_OP_NO FROM TN_WM_INCREASE_INVENTORY_DTL P  LEFT JOIN ");
                sbStr.Append(" (SELECT SUM(CN_F_QUANTITY) CN_F_QUANTITY,CN_S_ITEM_CODE,CN_S_LOT_NO FROM TN_WM_B_TRAY_ITEM_REL  ");
                sbStr.Append(" WHERE  CN_S_LOT_NO='" + item.CN_S_LOT_NO + "'   GROUP BY CN_S_LOT_NO,CN_S_ITEM_CODE) DT   ON P.CN_S_ITEM_CODE=DT.CN_S_ITEM_CODE    ");
                sbStr.Append(" WHERE DT.CN_F_QUANTITY>=P.CN_F_QUANTITY AND P.CN_S_OP_NO='" + item.CN_S_OP_NO + "') ");
                sbStr.Append(" UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_S_STATE='" + state + "' WHERE  CN_S_OP_NO='" + item.CN_S_OP_NO + "' ");
                result = ExecuteTranSql(sbStr.ToString(), null, null);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }


        /// <summary>
        /// 获取采购单
        /// </summary>
        /// <param name="sqlwhere">条件</param>
        /// <returns></returns>
        /// <History>[HANHE(lt)] CREATED BY 2018-11-15</History>
        public DataTable GetMstJoinDtl(string sqlwhere)
        {
            string sql = "SELECT * FROM TN_WM_IN_MST AS a INNER JOIN TN_WM_IN_DTL AS b ON b.CN_S_OP_NO = a.CN_S_OP_NO {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql);
        }
        #endregion


        /// <summary>
        ///物料上架-根据单号和物料编码更新累积上架数
        /// </summary>
        /// <param name="list"></param>
        /// <param name="trans"></param>
        public OperateResult updateUpselefQty(List<ItemRow> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var item in list)
            {
                string sql = @"UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_UPSHELF_QTY=ISNULL(CN_F_UPSHELF_QTY,0)+" + Convert.ToDecimal(item.CN_F_QUANTITY) + "  WHERE CN_S_OP_NO='" + item.CN_S_FROM_NO + "' AND CN_S_ITEM_CODE='" + item.CN_S_ITEM_CODE + "'  ";
                Log.Info("更新累积上架数", sql);
                result = ExecuteTranSql(sql, null, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
        /// <summary>
        /// 物料码盘-根据单号和物料编码更新累积码盘数
        /// </summary>
        /// <param name="list"></param>
        /// <param name="trans"></param>
        public OperateResult updateOntrayQty(List<TrayItemRow> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var item in list)
            {
                string sql = @"UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_ADD_ONTRAY_QTY=ISNULL(CN_F_ADD_ONTRAY_QTY,0)+" + Convert.ToDecimal(item.CN_F_QUANTITY) + "  WHERE CN_S_OP_NO='" + item.CN_S_OP_NO + "' AND CN_S_ITEM_CODE='" + item.CN_S_ITEM_CODE + "'  ";
                Log.Info("更新累积码盘数", sql);
                result = ExecuteTranSql(sql, null, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
        /// <summary>
        /// 物料上架-根据单号和物料编码更新累积码盘数
        /// </summary>
        /// <param name="list"></param>
        /// <param name="trans"></param>
        public OperateResult updateOntrayQty(List<ItemRow> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var item in list)
            {
                string sql = @"UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_ADD_ONTRAY_QTY=ISNULL(CN_F_ADD_ONTRAY_QTY,0)+" + Convert.ToDecimal(item.CN_F_QUANTITY) + "  WHERE CN_S_OP_NO='" + item.CN_S_FROM_NO + "' AND CN_S_ITEM_CODE='" + item.CN_S_ITEM_CODE + "'  ";
                Log.Info("更新累积码盘数", sql);
                result = ExecuteTranSql(sql, null, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
        /// <summary>
        ///整托上架-根据批次和物料编码更新累积上架数
        /// </summary>
        /// <param name="list"></param>
        /// <param name="trans"></param>
        public OperateResult UpdateUpselefQtyByLot(List<TrayItemMstAndDtlEntity> Group_List, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var item in Group_List)
            {
                if (string.IsNullOrEmpty(item.CN_S_LOT_NO)) continue;//没有批次的话，不用更新入库单累积上架数
                string sql = @"UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_UPSHELF_QTY=ISNULL(CN_F_UPSHELF_QTY,0)+" + Convert.ToDecimal(item.CN_F_QUANTITY) + "  WHERE CN_S_OP_NO in (select CN_S_OP_NO from TN_WM_INCREASE_INVENTORY_MST where CN_S_LOT_NO ='" + item.CN_S_LOT_NO + "') AND CN_S_ITEM_CODE='" + item.CN_S_ITEM_CODE + "'  ";

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

        /// <summary>
        /// 根据入库单号获取可码盘明细
        /// </summary>
        /// <param name="CN_S_OP_NO">入库单号</param>
        /// <returns></returns>
        public List<TN_WM_INCREASE_INVENTORY_DTLEntity> GetDtlList_OnTray(string CN_S_OP_NO)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT * FROM TN_WM_INCREASE_INVENTORY_DTL WHERE 1=1 ");
            if (!string.IsNullOrEmpty(CN_S_OP_NO))
            {
                sbStr.Append("AND CN_S_OP_NO='" + CN_S_OP_NO.Trim() + "' ");
            }
            sbStr.Append("AND ISNULL(CN_F_ADD_ONTRAY_QTY,0)!=CN_F_QUANTITY ");

            return ExecuteQuery<TN_WM_INCREASE_INVENTORY_DTLEntity>(sbStr.ToString()).ToList();
        }

        /// <summary>
        /// 根据入库单号获取可上架明细
        /// </summary>
        /// <param name="CN_S_OP_NO">入库单号</param>
        /// <returns></returns>
        public List<TN_WM_INCREASE_INVENTORY_DTLEntity> GetInMstAndDtl_OnShelf(string CN_S_OP_NO)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT * FROM TN_WM_INCREASE_INVENTORY_DTL WHERE 1=1 ");
            if (!string.IsNullOrEmpty(CN_S_OP_NO))
            {
                sbStr.Append("AND CN_S_OP_NO='" + CN_S_OP_NO.Trim() + "' ");
            }
            sbStr.Append("AND ISNULL(CN_F_UPSHELF_QTY,0)!=CN_F_QUANTITY ");

            return ExecuteQuery<TN_WM_INCREASE_INVENTORY_DTLEntity>(sbStr.ToString()).ToList();
        }

        /// <summary>
        /// 更新托盘物料关联
        /// </summary>
        /// <param name="transVouchList">物料批次集合</param>
        /// <returns></returns>
        public OperateResult UpdateTrayItemState(List<U8_TransVouchEntity> transVouchList, IDbTransaction trans)
        {
            string sql = "UPDATE  TN_WM_B_TRAY_ITEM_MST SET CN_S_ITEM_STATE=@CN_S_ITEM_STATE WHERE CN_S_ITEM_CODE=@CN_S_ITEM_CODE AND CN_GUID IN (SELECT CN_PARENT_GUID FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH) ";
            var result = ExecuteTranSql(sql, transVouchList, trans);
            return result;
        }

        /// <summary>
        /// 更新仓库量表物料状态
        /// </summary>
        /// <param name="transVouchList">物料批次集合</param>
        /// <returns></returns>
        public OperateResult UpdateStockQtyItemState(List<U8_TransVouchEntity> transVouchList, IDbTransaction trans)
        {
            string sql = "UPDATE TN_WM_B_STOCK_QTY SET CN_S_ITEM_STATE=@CN_S_ITEM_STATE WHERE CN_S_ITEM_CODE =@CN_S_ITEM_CODE and CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH ";
            var result = ExecuteTranSql(sql, transVouchList, trans);
            return result;
        }

        /// <summary>
        /// 更新库区量表物料状态
        /// </summary>
        /// <param name="transVouchList">物料批次集合</param>
        /// <returns></returns>
        public OperateResult UpdateAreaQtyItemState(List<U8_TransVouchEntity> transVouchList, IDbTransaction trans)
        {
            string sql = "UPDATE TN_WM_B_AREA_QTY SET CN_S_ITEM_STATE=@CN_S_ITEM_STATE WHERE CN_S_ITEM_CODE =@CN_S_ITEM_CODE and CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH ";
            var result = ExecuteTranSql(sql, transVouchList, trans);
            return result;
        }

        /// <summary>
        /// 更新XML表物料状态
        /// </summary>
        /// <param name="transVouchList">物料批次集合</param>
        /// <returns></returns>
        public OperateResult UpdateXmlItemState(List<U8_TransVouchEntity> transVouchList, IDbTransaction trans)
        {
            string sql = "UPDATE TN_WM_B_UNIQUE_BARCODE SET CN_S_ITEM_STATE=@CN_S_ITEM_STATE WHERE CN_S_ITEM_CODE =@CN_S_ITEM_CODE and CN_S_BATCH_NO=@CN_S_BATCH_NO ";
            var result = ExecuteTranSql(sql, transVouchList, trans);
            return result;
        }

        #region 获取未完全打印的入库单记录
        /// <summary>
        /// 获取未完全打印的入库单记录
        /// </summary>
        /// <param name="stockCode">仓库号</param>
        /// <returns></returns>
        /// <History>[HANHE(DBS)] CREATED BY 2019-02-28</History>
        public OperateResult GetWaitPrintOrder(string stockCode)
        {
            string sql = @"SELECT a.CN_S_OP_NO
                              ,[CN_S_OP_TYPE]
                              ,[CN_S_STOCK_CODE]
                              ,[CN_S_OP_FROM]
                              ,[CN_S_FROM_NO]
                              ,a.[CN_T_CREATE]
                              ,[CN_S_DELIVERY_NOTE]
                              ,[CN_S_ARRIVAL_NO]
                              ,[CN_S_VENDOR_NO]
                              ,[CN_S_VENDOR_NAME]
                              ,sum(b.CN_F_QUANTITY) CN_F_QUANTITY
                              ,sum(b.CN_F_PRINTING_QTY) CN_F_PRINTING_QTY
                          FROM TN_WM_INCREASE_INVENTORY_MST a
                          join TN_WM_INCREASE_INVENTORY_DTL b on a.CN_S_OP_NO=b.CN_S_OP_NO
                          where CN_S_STOCK_CODE=@CN_S_STOCK_CODE and b.CN_F_PRINTING_QTY <b.CN_F_QUANTITY
                          group by a.CN_S_OP_NO
                              ,[CN_S_OP_TYPE]
                              ,[CN_S_STOCK_CODE]
                              ,[CN_S_OP_FROM]
                              ,[CN_S_FROM_NO]
                              ,a.[CN_T_CREATE]
                              ,[CN_S_DELIVERY_NOTE]
                              ,[CN_S_ARRIVAL_NO]
                              ,[CN_S_VENDOR_NO]
                              ,[CN_S_VENDOR_NAME]";
            DataTable dt = ExecuteDataTable(sql, new { CN_S_STOCK_CODE = stockCode });
            return OperateResult.Succeed("", dt);
        }
        #endregion

        #region 获取所有待上架（暂存区）的物料(到货入+移库入)
        /// <summary>
        /// 获取所有待上架（暂存区）的物料(到货入+移库入)
        /// </summary>
        /// <param name="itemCode">物料编号</param>
        /// <param name="zcAreaList">暂存区库区列表</param>
        /// <returns></returns>
        /// <History>[HANHE(DBS)] CREATED BY 2019-03-7</History>
        public List<TN_WM_B_TRAY_ITEM_MSTEntity> GetWaitUpShelvesItems(string itemCode, List<AutoBomStockAreaEntity> zcAreaList)
        {
            //暂存区的量-分配量-待上架的量
            string sql = @"select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT 
                            ,(a.CN_F_QUANTITY-ISNULL(a.CN_F_ALLOC_QTY,0)-ISNULL(b.CN_F_QUANTITY,0)) CN_F_QUANTITY
                            from tn_wm_b_area_qty a left join 
                            tn_wm_tray_alloc b on a.CN_S_ITEM_CODE=b.CN_S_ITEM_CODE
                            where a.CN_S_STOCK_AREA=@CN_S_STOCK_AREA and a.CN_S_ITEM_CODE=@CN_S_ITEM_CODE";
            return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, new { CN_S_STOCK_AREA = zcAreaList[0].CN_S_AREA_CODE, CN_S_ITEM_CODE = itemCode });
           // return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, new { CN_S_STOCK_AREA = "SHQ01", CN_S_ITEM_CODE = itemCode });
        }
        #endregion

        #region 获取OMS拼接数据
        /// <summary>
        /// 获取OMS确认数据
        /// </summary>
        /// <param name="ArrivalNo"></param>
        /// <returns></returns>
        public DataTable GetDeleteInOmsModel(string opNo)
        {
            string sql = @"select distinct arrm.CN_S_OP_TYPE,insd.CN_S_INSPECT_NO,inm.CN_S_FROM_NO,insd.CN_S_ITEM_CODE,insd.CN_F_QUANTITY 
                             ,insd.CN_F_QUALIFIED,insd.CN_F_UNQUALIFIED,IND.CN_F_ACTUAL_PRICE  ,IND.CN_F_ACTUAL_MONEY
                             from tn_wm_inspect_dtl  insd
                             left join tn_wm_arrival_mst arrm on arrm.CN_S_ARRIVAL_NO=insd.CN_S_FROM_NO
                             left join tn_wm_in_mst  inm on  arrm.CN_S_FROM_NO=inm.CN_S_OP_NO
                             LEFT JOIN TN_WM_IN_DTL IND  ON  IND.CN_S_OP_NO=INM.CN_S_OP_NO and insd.CN_N_ROW_NO=ind.CN_N_ROW_NO
                             left join TN_WM_INCREASE_INVENTORY_MST inMst on inMst.CN_S_ARRIVAL_NO=arrm.CN_S_ARRIVAL_NO
                             where inMst.CN_S_OP_NO='" + opNo + "' and inm.CN_S_FROM_NO  is not null";
            return ExecuteDataTable(sql);
        }

        /// <summary>
        /// 入库单审核时调用
        /// </summary>
        /// <param name="inNo"></param>
        /// <returns></returns>
        public DataTable GetOmsInModel(string inNo)
        {
            string sql = @"SELECT INSD.CN_S_OP_NO CN_S_FROM_NO,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_F_QUANTITY,CN_S_OP_TYPE,CN_S_STOCK_CODE,CN_S_VENDOR_NO,CN_S_VENDOR_NAME
                            ,CN_F_ACTUAL_PRICE CN_F_PRICE,CN_F_ACTUAL_MONEY CN_F_MONEY_SUM,CN_S_FIGURE_NO,CN_S_MODEL,INMST.CN_S_DELIVERY_NOTE,CN_S_MEASURE_UNIT,INMST.CN_S_NOTE
                              FROM  TN_WM_INCREASE_INVENTORY_DTL  INSD
                             LEFT JOIN  TN_WM_INCREASE_INVENTORY_MST INMST ON INMST.CN_S_OP_NO=INSD.CN_S_OP_NO    
                             WHERE INMST.CN_S_OP_NO='" + inNo + "'";
            return ExecuteDataTable(sql);
        }

        /// <summary>
        /// 入库单审核时调用
        /// </summary>
        /// <param name="inNo"></param>
        /// <returns></returns>
        public DataTable GetOmsInOrderModel(string inNo)
        {
            string sql = @"SELECT distinct INSD.CN_S_ITEM_CODE,ad.CN_N_FROM_ROW_NO, INSD.CN_S_ITEM_NAME,INMST.CN_S_OP_TYPE,INMST.CN_S_STOCK_CODE,
                             INSD.CN_F_QUANTITY ,INMST.CN_S_VENDOR_NO,INMST.CN_S_VENDOR_NAME,ARRM.CN_S_FROM_NO,
                             INSD.CN_N_ROW_NO,INM.CN_S_FROM_NO CN_S_OP_NO,INSD.CN_F_ACTUAL_PRICE CN_F_PRICE,INSD.CN_F_ACTUAL_MONEY CN_F_MONEY_SUM,INSD.CN_S_FIGURE_NO,INSD.CN_S_MODEL,ARRM.CN_S_DELIVERY_NOTE
                             FROM  TN_WM_INCREASE_INVENTORY_DTL  INSD
                             LEFT JOIN  TN_WM_INCREASE_INVENTORY_MST INMST ON INMST.CN_S_OP_NO=INSD.CN_S_OP_NO
                             LEFT JOIN TN_WM_ARRIVAL_MST ARRM ON ARRM.CN_S_ARRIVAL_NO=INMST.CN_S_ARRIVAL_NO
                             LEFT JOIN TN_WM_IN_MST INM ON INM.CN_S_OP_NO=ARRM.CN_S_FROM_NO
                             LEFT JOIN tn_wm_arrival_dtl AD ON ARRM.CN_S_ARRIVAL_NO=ad.CN_S_ARRIVAL_NO and INSD.CN_N_ROW_NO=ad.CN_N_ROW_NO
                             WHERE INMST.CN_S_OP_NO='" + inNo + "'";
            return ExecuteDataTable(sql);
        }
        #endregion

        #region  PDA接口
        /// <summary>
        /// PDA接口-获取待贴码信息
        /// </summary>
        /// <param name="printBarCode">打码实体</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2019-03-15</History>
        public DataTable GetFixedData(MONGO_PRINT_BARCODE printBarCode)
        {
            string sql = @"SELECT D.CN_S_ITEM_CODE,D.CN_S_ITEM_NAME,D.CN_S_MODEL,D.CN_S_ARRIVAL_NO,D.CN_F_QUANTITY,M.CN_S_VENDOR_NAME,D.CN_C_IS_FIXED_STATE,D.CN_S_PUT_POSITION  
                           FROM TN_WM_ARRIVAL_DTL D LEFT JOIN   
                           TN_WM_ARRIVAL_MST M ON D.CN_S_MSTGUID=M.CN_GUID 
                           LEFT JOIN  TN_WM_INCREASE_INVENTORY_MST INMST ON INMST.CN_S_ARRIVAL_NO=M.CN_S_ARRIVAL_NO 
                           WHERE INMST.CN_S_OP_NO='"+ printBarCode.CN_S_IN_ORDER + @"'  AND D.CN_S_ITEM_CODE='" + printBarCode.CN_S_ITEM_CODE + @"'  
                           AND M.CN_S_LOT_CODE='" + printBarCode.CN_S_LOT_NO + @"' AND D.CN_S_PRODUCTION_BATCH='" + printBarCode.CN_S_PRODUCTION_BATCH + @"'
                           AND  D.CN_C_IS_FIXED_STATE!='Y' ";
            return ExecuteDataTable(sql);
        }

        /// <summary>
        /// 贴码确认
        /// </summary>
        /// <param name="printBarCode">打码实体</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2019-03-15</History>
        public OperateResult FixedConfirm(MONGO_PRINT_BARCODE printBarCode)
        {
            string sq1 = @" UPDATE TN_WM_ARRIVAL_DTL  SET  CN_C_IS_FIXED_STATE='Y'  
                            FROM TN_WM_ARRIVAL_DTL WHERE CN_S_ARRIVAL_NO = (select CN_S_ARRIVAL_NO from TN_WM_INCREASE_INVENTORY_MST WHERE CN_S_OP_NO = @CN_S_OP_NO and CN_S_LOT_NO = @CN_S_LOT_NO) AND
                            CN_S_ITEM_CODE = @CN_S_ITEM_CODE and CN_S_ITEM_STATE = @CN_S_ITEM_STATE and CN_S_PRODUCTION_BATCH = @CN_S_PRODUCTION_BATCH";
            OperateResult result = ExecuteTranSql(sq1, new
            {
                CN_S_OP_NO = printBarCode.CN_S_IN_ORDER,
                CN_S_LOT_NO = printBarCode.CN_S_LOT_NO,
                CN_S_ITEM_CODE = printBarCode.CN_S_ITEM_CODE,
                CN_S_ITEM_STATE = printBarCode.CN_S_ITEM_STATE,
                CN_S_PRODUCTION_BATCH = printBarCode.CN_S_PRODUCTION_BATCH
            }, null);

            return result;
        }

        /// <summary>
        /// PDA接口-获取搬运信息
        /// </summary>
        /// <param name="printBarCode">打码实体</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2019-03-15</History>
        public DataTable GetTransData(MONGO_PRINT_BARCODE printBarCode)
        {
            string sql = @"SELECT D.CN_S_ITEM_CODE,D.CN_S_ITEM_NAME,D.CN_S_MODEL,D.CN_S_ARRIVAL_NO,D.CN_F_QUANTITY,M.CN_S_VENDOR_NAME,D.CN_C_IS_FIXED_STATE,D.CN_S_PUT_POSITION  
                           FROM TN_WM_ARRIVAL_DTL D LEFT JOIN   
                           TN_WM_ARRIVAL_MST M ON D.CN_S_MSTGUID=M.CN_GUID 
                           LEFT JOIN  TN_WM_INCREASE_INVENTORY_MST INMST ON INMST.CN_S_ARRIVAL_NO=M.CN_S_ARRIVAL_NO 
                           WHERE INMST.CN_S_OP_NO='" + printBarCode.CN_S_IN_ORDER + @"'  AND D.CN_S_ITEM_CODE='" + printBarCode.CN_S_ITEM_CODE + @"'  
                           AND M.CN_S_LOT_CODE='" + printBarCode.CN_S_LOT_NO + @"' AND D.CN_S_PRODUCTION_BATCH='" + printBarCode.CN_S_PRODUCTION_BATCH + @"'
                           AND  D.CN_C_IS_FIXED_STATE!='Y' ";
            return ExecuteDataTable(sql);
        }
        #endregion

        #region 获取入库单中的物料信息
        /// <summary>
        /// 获取托盘中物料的信息，按批次汇总
        /// </summary>
        /// <param name="trayCode">托盘号</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] CREATED BY 2018/12/8</History>
        public DataTable GetIncreaseDetail(string CN_S_OP_NO)
        {
            string sql = @" select b.CN_S_ITEM_CODE,b.CN_N_ROW_NO,b.CN_S_ITEM_NAME,b.CN_S_MODEL,b.CN_S_ITEM_STATE,a.CN_S_OWNER,b.CN_S_MEASURE_UNIT,a.CN_S_LOT_NO,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,a.CN_S_VENDOR_NO
                           ,a.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,(b.CN_F_QUANTITY - b.CN_F_ADD_ONTRAY_QTY) as CAN_ENCODE_QTY,b.CN_S_PRODUCTION_BATCH ,b.CN_F_ADD_ONTRAY_QTY from TN_WM_INCREASE_INVENTORY_MST a join 
                            TN_WM_INCREASE_INVENTORY_DTL b on a.CN_S_OP_NO=b.CN_S_OP_NO
                            where a.CN_S_OP_NO=@CN_S_OP_NO and b.CN_F_QUANTITY - b.CN_F_ADD_ONTRAY_QTY>0 ";

            return ExecuteDataTable(sql, new { CN_S_OP_NO = CN_S_OP_NO });
        }
        #endregion
    }
}