Wms_WebApi/NXWMS.WebApi/NXWMS.Service/NXWMS/OutStock/WmsOutInvoiceService.cs

using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using Microsoft.Extensions.Configuration;
using NXWMS.IService.NXWMS.OutStock;
using NXWMS.Model.AppModels.Condition.Balance;
using NXWMS.Model.AppModels.Condition.OutStock;
using NXWMS.Model.AppModels.Result.Balance;
using NXWMS.Model.AppModels.Result.Base;
using NXWMS.Model.AppModels.Result.OutStock;
using NXWMS.Model.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WestDistance.DapperORM.Repository.Repositorys;

namespace NXWMS.Service.NXWMS.OutStock
{
    /// <summary>
    /// 发货单服务
    /// </summary>
    [AutoInject(typeof(IWmsOutInvoiceService), InjectType.Scope)]
    public class WmsOutInvoiceService : ServiceBase, IWmsOutInvoiceService
    {
        #region 全局变量、构造注入
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

        /// <summary>
        /// SQL节点仓储
        /// </summary>
        private ISQLNodeRepository _iSQLNodeRepository;

        /// <summary>
        /// 配置
        /// </summary>
        private IConfiguration _configuration;
        /// <summary>
        /// 构造注入
        /// </summary>
        /// <param name="dataRepositoryContext"></param>
        /// <param name="configuration"></param>
        /// <param name="iSQLNodeRepository"></param>
        public WmsOutInvoiceService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository)
        {
            this._dataContext = dataRepositoryContext;
            this._configuration = configuration;
            this._iSQLNodeRepository = iSQLNodeRepository;
        }
        #endregion

        /// <summary>
        /// 分页查询发货单主表数据
        /// </summary>
        /// <param name="invoiceSearchMd">发货单查询条件实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsOutInvoiceResult>> GetWmsOutInvoiceListForPage(WmsOutInvoiceSearchMd invoiceSearchMd)
        {
            try
            {
                #region SQL语句生成
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(invoiceSearchMd.InvoiceNoMsg))
                {
                    sqlCondition.Append($" AND INVOICE_NO = '{invoiceSearchMd.InvoiceNoMsg}'");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.SourceNoMsg))
                {
                    sqlCondition.Append($" AND SOURCE_BILL_NO = '{invoiceSearchMd.SourceNoMsg}'");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND INVOICE_ID IN (SELECT INVOICE_ID FROM VW_WMS_OUT_INVOICE_DTL WHERE MATERIEL_CODE LIKE '%{invoiceSearchMd.MaterielMsg}%' OR MATERIEL_NAME LIKE '%{invoiceSearchMd.MaterielMsg}%' OR MATERIEL_BARCODE LIKE '%{invoiceSearchMd.MaterielMsg}%'");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.InvoiceType))
                {
                    sqlCondition.Append($" AND INVOICE_TYPE = '{invoiceSearchMd.InvoiceType}'");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.InvoiceStatus))
                {
                    sqlCondition.Append($" AND INVOICE_STATUS = '{invoiceSearchMd.InvoiceStatus}'");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.SupplierMsg))
                {
                    sqlCondition.Append($" AND INVOICE_ID IN (SELECT INVOICE_ID FROM VW_WMS_OUT_INVOICE_DTL WHERE SUPPLIER_CODE LIKE '%{invoiceSearchMd.SupplierMsg}%' OR SUPPLIER_NAME LIKE '%{invoiceSearchMd.SupplierMsg}%')");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.StartCreateTime))
                {
                    sqlCondition.Append($" AND CREATE_TIME >= '{invoiceSearchMd.StartCreateTime}'");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.EndCreateTime))
                {
                    sqlCondition.Append($" AND CREATE_TIME <= '{invoiceSearchMd.EndCreateTime}'");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.StartInvoiceEndTime))
                {
                    sqlCondition.Append($" AND INVOICE_END_TIME >= '{invoiceSearchMd.StartInvoiceEndTime}'");
                }
                if (!string.IsNullOrEmpty(invoiceSearchMd.EndInvoiceEndTime))
                {
                    sqlCondition.Append($" AND INVOICE_END_TIME <= '{invoiceSearchMd.EndInvoiceEndTime}'");
                }
                StringBuilder sqlCountInspectionData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_OUT_INVOICE WHERE 1=1");
                sqlCountInspectionData.Append(sqlCondition.ToString());

                int pageStartIndex = (invoiceSearchMd.PageNum - 1) * invoiceSearchMd.EveryPageQty;
                int pageEndIndex = invoiceSearchMd.PageNum * invoiceSearchMd.EveryPageQty;

                StringBuilder sqlQueryInspectionData = new StringBuilder($@"
                                                                        SELECT
	                                                                        INVOICE_ID,
	                                                                        INVOICE_NO,
	                                                                        INVOICE_TYPE,
	                                                                        INVOICE_TYPE_NAME,
	                                                                        BATCH_NO,
	                                                                        SOURCE_BILL_NO,
	                                                                        INVOICE_PRIORITY,
	                                                                        INVOICE_END_TIME,
	                                                                        INVOICE_STATUS,
	                                                                        INVOICE_STATUS_NAME,
                                                                            DESCRIBE,
	                                                                        CREATE_BY,
	                                                                        CREATE_NAME,
	                                                                        CREATE_TIME,
	                                                                        UPDATE_BY,
	                                                                        UPDATE_NAME,
	                                                                        UPDATE_TIME
                                                                        FROM
                                                                            VW_WMS_OUT_INVOICE 
                                                                        WHERE
                                                                            1=1
			                                                                {sqlCondition} 
                                                                            ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
                                                                            OFFSET {pageStartIndex} ROWS 
                                                                            FETCH NEXT {invoiceSearchMd.EveryPageQty} ROWS ONLY
	                                                                        
                                                                    ");
                #endregion

                int dataCount = Convert.ToInt32(new DataRepository<object>(_dataContext).ExecuteScalar(sqlCountInspectionData.ToString()));
                List<WmsOutInvoiceResult> resultList = new DataRepository<WmsOutInvoiceResult>(_dataContext).Query(sqlQueryInspectionData.ToString()).ToList();
                OperateResultInfo<List<WmsOutInvoiceResult>> retDataMsg = SuccessStatus(resultList);
                retDataMsg.DataCount = dataCount;

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsOutInvoiceResult>>($"查询发货单数据发生异常，【{ex.Message}】", null);
            }
        }
        /// <summary>
        /// 根据发货单主键Id查询发货单明细数据
        /// </summary>
        /// <param name="wmsOutInvoice">发货单实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<WmsOutInvoiceResult> GetWmsOutInvoiceDtlListForID(WmsOutInvoiceResult wmsOutInvoice)
        {
            try
            {
                #region SQL语句生成
                string sqlQueryWmsOutInvoice = $@"
                                                SELECT
	                                                INVOICE_ID,
	                                                INVOICE_NO,
	                                                INVOICE_TYPE,
	                                                INVOICE_TYPE_NAME,
	                                                BATCH_NO,
	                                                SOURCE_BILL_NO,
	                                                INVOICE_PRIORITY,
	                                                INVOICE_END_TIME,
	                                                INVOICE_STATUS,
	                                                INVOICE_STATUS_NAME,
                                                    DESCRIBE,
	                                                CREATE_BY,
	                                                CREATE_NAME,
	                                                CREATE_TIME,
	                                                UPDATE_BY,
	                                                UPDATE_NAME,
	                                                UPDATE_TIME
                                                FROM
                                                    VW_WMS_OUT_INVOICE 
                                                WHERE   
                                                    INVOICE_ID = '{wmsOutInvoice.INVOICE_ID}'
                                              ";
                string strWhere = string.Empty;
                if (wmsOutInvoice.REMARKS1 == "查询已删除明细数据")
                {
                    strWhere = "1=1";
                }
                else
                {
                    strWhere = "INVOICE_DTL_STATUS < 99";
                }
                string sqlQueryWmsInArivalDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_OUT_INVOICE_DTL
                                                    WHERE
	                                                    INVOICE_ID = '{wmsOutInvoice.INVOICE_ID}'
                                                    AND {strWhere}
                                                    ORDER BY INVOICE_DTL_STATUS,INVOICE_DTL_ID
                                                    ";
                #endregion
                List<WmsOutInvoiceResult> resultList = new DataRepository<WmsOutInvoiceResult>(_dataContext).Query(sqlQueryWmsOutInvoice).ToList();
                List<WmsOutInvoiceDtlResult> resultDtlList = new DataRepository<WmsOutInvoiceDtlResult>(_dataContext).Query(sqlQueryWmsInArivalDtl).ToList();
                wmsOutInvoice = resultList[0];
                wmsOutInvoice.WmsOutInvoiceDtlList = resultDtlList;
                OperateResultInfo<WmsOutInvoiceResult> retDataMsg = SuccessStatus(wmsOutInvoice);

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<WmsOutInvoiceResult>($"查询发货单明细数据发生异常，【{ex.Message}】", null);
            }
        }

        /// <summary>
        /// 新增发货单数据
        /// </summary>
        /// <param name="wmsOutInvoice">发货单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo AddWmsOutInvoiceData(WmsOutInvoiceResult wmsOutInvoice)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                wmsOutInvoice.INVOICE_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Invoice_Id"));
                wmsOutInvoice.INVOICE_NO = new DataRepository<object>(_dataContext).GetSequenceMsg("Invoice_No");
                wmsOutInvoice.INVOICE_STATUS = 0;
                StringBuilder sqlAddWmsOutInvoice = new StringBuilder();
                string invoiceEndTime = DateTime.Compare(wmsOutInvoice.INVOICE_END_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsOutInvoice.INVOICE_END_TIME}'";
                sqlAddWmsOutInvoice.Append($@"
                                            INSERT INTO [WMS_OUT_INVOICE] (
	                                            [INVOICE_ID],
	                                            [INVOICE_NO],
	                                            [INVOICE_TYPE],
	                                            [INVOICE_END_TIME],
	                                            [BATCH_NO],
	                                            [SOURCE_BILL_NO],
	                                            [INVOICE_PRIORITY],
	                                            [INVOICE_STATUS],
	                                            [DESCRIBE],
	                                            [CREATE_BY],
	                                            [CREATE_TIME],
	                                            [UPDATE_BY],
	                                            [UPDATE_TIME],
	                                            [DATA_VERSION],
	                                            [REMARKS1],
	                                            [REMARKS2],
	                                            [REMARKS3],
	                                            [REMARKS4],
	                                            [REMARKS5]
                                            )
                                            VALUES
	                                            (
		                                            '{wmsOutInvoice.INVOICE_ID}',
		                                            '{wmsOutInvoice.INVOICE_NO}',
		                                            '{wmsOutInvoice.INVOICE_TYPE}',
		                                            {invoiceEndTime},
		                                            '{wmsOutInvoice.BATCH_NO}',
		                                            '{wmsOutInvoice.SOURCE_BILL_NO}',
		                                            '{wmsOutInvoice.INVOICE_PRIORITY}',
		                                            '{wmsOutInvoice.INVOICE_STATUS}',
		                                            '{wmsOutInvoice.DESCRIBE}',
		                                            '{wmsOutInvoice.CREATE_BY}',
		                                            getdate(),
		                                            '{wmsOutInvoice.UPDATE_BY}',
		                                            getdate(),
		                                            '{0}',
		                                            NULL,
		                                            NULL,
		                                            NULL,
		                                            NULL,
		                                            NULL
	                                            );
                                         ");
                sqlList.Add(sqlAddWmsOutInvoice.ToString());
                foreach (WmsOutInvoiceDtlResult item in wmsOutInvoice.WmsOutInvoiceDtlList)
                {
                    StringBuilder sqlAddWmsOutInvoiceDtl = new StringBuilder();
                    item.INVOICE_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Invoice_Dtl_Id"));
                    item.INVOICE_ID = wmsOutInvoice.INVOICE_ID;
                    item.INVOICE_DTL_STATUS = 0;
                    string productTime = DateTime.Compare(item.PRODUCT_DATE, new DateTime()) == 0 ? "NULL" : $"'{item.PRODUCT_DATE}'";
                    string expTime = DateTime.Compare(item.EXP_DATE, new DateTime()) == 0 ? "NULL" : $"'{item.PRODUCT_DATE}'";
                    sqlAddWmsOutInvoiceDtl.Append($@"
                                                    INSERT INTO [WMS_OUT_INVOICE_DTL] (
	                                                    [INVOICE_DTL_ID],
	                                                    [INVOICE_ID],
	                                                    [MATERIEL_CODE],
	                                                    [MATERIEL_NAME],
	                                                    [MATERIEL_BARCODE],
	                                                    [MATERIEL_SPEC],
	                                                    [PACKAGE_CODE],
	                                                    [UNIT_CODE],
	                                                    [BATCH_NO],
	                                                    [SUPPLIER_CODE],
	                                                    [SUPPLIER_NAME],
	                                                    [INVOICE_DEMAND_QTY],
	                                                    [INVOICE_CONFIRM_QTY],
	                                                    [PUTDOWN_QTY],
	                                                    [PRODUCT_DATE],
	                                                    [EXP_DATE],
	                                                    [INVOICE_DTL_STATUS],
	                                                    [INSPECTION_RESULT],
	                                                    [ITEM_STATUS],
	                                                    [SOURCE_BILL_DTL_IDX],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{item.INVOICE_DTL_ID}',
		                                                    '{item.INVOICE_ID}',
		                                                    '{item.MATERIEL_CODE}',
		                                                    '{item.MATERIEL_NAME}',
		                                                    '{item.MATERIEL_BARCODE}',
		                                                    '{item.MATERIEL_SPEC}',
		                                                    '{item.PACKAGE_CODE}',
		                                                    '{item.UNIT_CODE}',
		                                                    '{item.BATCH_NO}',
		                                                    '{item.SUPPLIER_CODE}',
		                                                    '{item.SUPPLIER_NAME}',
		                                                    '{item.INVOICE_DEMAND_QTY}',
		                                                    '{item.INVOICE_CONFIRM_QTY}',
		                                                    '{item.PUTDOWN_QTY}',
		                                                    {productTime},
		                                                    {expTime},
		                                                    '{item.INVOICE_DTL_STATUS}',
		                                                    '{item.INSPECTION_RESULT}',
		                                                    '{item.ITEM_STATUS}',
		                                                    '{item.SOURCE_BILL_DTL_IDX}',
		                                                    '{item.DESCRIBE}',
		                                                    '{item.CREATE_BY}',
		                                                    getdate(),
		                                                    '{item.UPDATE_BY}',
		                                                    getdate(),
		                                                    '{0}',
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL
	                                                    );
                                             ");
                    sqlList.Add(sqlAddWmsOutInvoiceDtl.ToString());
                }
                #endregion

                int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                if (row > 0)
                {
                    return SuccessMessageStatus("新增发货单数据成功！", row);
                }
                else
                {
                    return FailMessageStatus("新增发货单数据失败！", row);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"新增发货单数据发生异常，【{ex.Message}】");
            }
        }

        /// <summary>
        /// 修改发货单数据
        /// </summary>
        /// <param name="wmsOutInvoice">发货单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo EditWmsOutInvoiceData(WmsOutInvoiceResult wmsOutInvoice)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                string invoiceEndTime = DateTime.Compare(wmsOutInvoice.INVOICE_END_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsOutInvoice.INVOICE_END_TIME}'";
                string sqlUpdateWmsOutInvoice = $@"
                                                    UPDATE [WMS_OUT_INVOICE]
                                                    SET
                                                     [INVOICE_NO] = '{wmsOutInvoice.INVOICE_NO}',
                                                     [INVOICE_TYPE] = '{wmsOutInvoice.INVOICE_TYPE}',
                                                     [INVOICE_END_TIME] = {invoiceEndTime},
                                                     [BATCH_NO] = '{wmsOutInvoice.BATCH_NO}',
                                                     [SOURCE_BILL_NO] = '{wmsOutInvoice.SOURCE_BILL_NO}',
                                                     [INVOICE_PRIORITY] = '{wmsOutInvoice.INVOICE_PRIORITY}',
                                                     [INVOICE_STATUS] = '{wmsOutInvoice.INVOICE_STATUS}',
                                                     [DESCRIBE] = '{wmsOutInvoice.DESCRIBE}',
                                                     [UPDATE_BY] = '{wmsOutInvoice.UPDATE_BY}',
                                                     [UPDATE_TIME] = getdate(),
                                                     [DATA_VERSION] = [DATA_VERSION] + 1
                                                    WHERE
	                                                    ([INVOICE_ID] = '{wmsOutInvoice.INVOICE_ID}');
                                                ";
                sqlList.Add(sqlUpdateWmsOutInvoice);
                foreach (WmsOutInvoiceDtlResult item in wmsOutInvoice.WmsOutInvoiceDtlList)
                {
                    if (item.REMARKS1 == "更新" || item.REMARKS1 == "删除")
                    {
                        string productTime = DateTime.Compare(item.PRODUCT_DATE, new DateTime()) == 0 ? "NULL" : $"'{item.PRODUCT_DATE}'";
                        string expTime = DateTime.Compare(item.EXP_DATE, new DateTime()) == 0 ? "NULL" : $"'{item.PRODUCT_DATE}'";
                        string sqlUpdateWmsOutInvoiceDtl = $@"
                                                                UPDATE [WMS_OUT_INVOICE_DTL]
                                                                SET 
                                                                 [MATERIEL_CODE] = '{item.MATERIEL_CODE}',
                                                                 [MATERIEL_NAME] = '{item.MATERIEL_NAME}',
                                                                 [MATERIEL_BARCODE] = '{item.MATERIEL_BARCODE}',
                                                                 [MATERIEL_SPEC] = '{item.MATERIEL_SPEC}',
                                                                 [PACKAGE_CODE] = '{item.PACKAGE_CODE}',
                                                                 [UNIT_CODE] = '{item.UNIT_CODE}',
                                                                 [BATCH_NO] = '{item.BATCH_NO}',
                                                                 [SUPPLIER_CODE] = '{item.SUPPLIER_CODE}',
                                                                 [SUPPLIER_NAME] = '{item.SUPPLIER_NAME}',
                                                                 [INVOICE_DEMAND_QTY] = '{item.INVOICE_DEMAND_QTY}',
                                                                 [INVOICE_CONFIRM_QTY] = '{item.INVOICE_CONFIRM_QTY}',
                                                                 [PUTDOWN_QTY] = '{item.PUTDOWN_QTY}',
                                                                 [PRODUCT_DATE] = {productTime},
                                                                 [EXP_DATE] = {expTime},
                                                                 [INVOICE_DTL_STATUS] = '{item.INVOICE_DTL_STATUS}',
                                                                 [INSPECTION_RESULT] = '{item.INSPECTION_RESULT}',
                                                                 [ITEM_STATUS] = '{item.ITEM_STATUS}',
                                                                 [SOURCE_BILL_DTL_IDX] = '{item.SOURCE_BILL_DTL_IDX}',
                                                                 [DESCRIBE] = '{item.DESCRIBE}',
                                                                 [UPDATE_BY] = '{item.UPDATE_BY}',
                                                                 [UPDATE_TIME] = GETDATE(),
                                                                 [DATA_VERSION] = [DATA_VERSION] + 1
                                                                WHERE
	                                                                ([INVOICE_DTL_ID] = '{item.INVOICE_DTL_ID}');
                                                        ";
                        sqlList.Add(sqlUpdateWmsOutInvoiceDtl);
                    }
                    if (item.REMARKS1 == "添加")
                    {
                        StringBuilder sqlAddWmsOutInvoiceDtl = new StringBuilder();
                        item.INVOICE_DTL_ID = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Invoice_Dtl_Id"));
                        item.INVOICE_ID = wmsOutInvoice.INVOICE_ID;
                        item.INVOICE_DTL_STATUS = 0;
                        string productTime = DateTime.Compare(item.PRODUCT_DATE, new DateTime()) == 0 ? "NULL" : $"'{item.PRODUCT_DATE}'";
                        string expTime = DateTime.Compare(item.EXP_DATE, new DateTime()) == 0 ? "NULL" : $"'{item.PRODUCT_DATE}'";
                        sqlAddWmsOutInvoiceDtl.Append($@"
                                                    INSERT INTO [WMS_OUT_INVOICE_DTL] (
	                                                    [INVOICE_DTL_ID],
	                                                    [INVOICE_ID],
	                                                    [MATERIEL_CODE],
	                                                    [MATERIEL_NAME],
	                                                    [MATERIEL_BARCODE],
	                                                    [MATERIEL_SPEC],
	                                                    [PACKAGE_CODE],
	                                                    [UNIT_CODE],
	                                                    [BATCH_NO],
	                                                    [SUPPLIER_CODE],
	                                                    [SUPPLIER_NAME],
	                                                    [INVOICE_DEMAND_QTY],
	                                                    [INVOICE_CONFIRM_QTY],
	                                                    [PUTDOWN_QTY],
	                                                    [PRODUCT_DATE],
	                                                    [EXP_DATE],
	                                                    [INVOICE_DTL_STATUS],
	                                                    [INSPECTION_RESULT],
	                                                    [ITEM_STATUS],
	                                                    [SOURCE_BILL_DTL_IDX],
	                                                    [DESCRIBE],
	                                                    [CREATE_BY],
	                                                    [CREATE_TIME],
	                                                    [UPDATE_BY],
	                                                    [UPDATE_TIME],
	                                                    [DATA_VERSION],
	                                                    [REMARKS1],
	                                                    [REMARKS2],
	                                                    [REMARKS3],
	                                                    [REMARKS4],
	                                                    [REMARKS5]
                                                    )
                                                    VALUES
	                                                    (
		                                                    '{item.INVOICE_DTL_ID}',
		                                                    '{item.INVOICE_ID}',
		                                                    '{item.MATERIEL_CODE}',
		                                                    '{item.MATERIEL_NAME}',
		                                                    '{item.MATERIEL_BARCODE}',
		                                                    '{item.MATERIEL_SPEC}',
		                                                    '{item.PACKAGE_CODE}',
		                                                    '{item.UNIT_CODE}',
		                                                    '{item.BATCH_NO}',
		                                                    '{item.SUPPLIER_CODE}',
		                                                    '{item.SUPPLIER_NAME}',
		                                                    '{item.INVOICE_DEMAND_QTY}',
		                                                    '{item.INVOICE_CONFIRM_QTY}',
		                                                    '{item.PUTDOWN_QTY}',
		                                                    {productTime},
		                                                    {expTime},
		                                                    '{item.INVOICE_DTL_STATUS}',
		                                                    '{item.INSPECTION_RESULT}',
		                                                    '{item.ITEM_STATUS}',
		                                                    '{item.SOURCE_BILL_DTL_IDX}',
		                                                    '{item.DESCRIBE}',
		                                                    '{item.CREATE_BY}',
		                                                    getdate(),
		                                                    '{item.UPDATE_BY}',
		                                                    getdate(),
		                                                    '{0}',
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL,
		                                                    NULL
	                                                    );
                                             ");
                        sqlList.Add(sqlAddWmsOutInvoiceDtl.ToString());
                    }
                }
                #endregion

                int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                if (row > 0)
                {
                    return SuccessMessageStatus("修改发货单数据成功！", row);
                }
                else
                {
                    return FailMessageStatus("修改发货单数据失败！", row);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"修改发货单数据发生异常，【{ex.Message}】");
            }
        }

        /// <summary>
        /// 删除发货单数据
        /// </summary>
        /// <param name="wmsOutInvoice">发货单主表对象</param>
        /// <returns></returns>
        public OperateResultInfo DeleteWmsOutInvoiceData(WmsOutInvoiceResult wmsOutInvoice)
        {
            try
            {
                #region SQL语句生成
                string[] invoiceIdList = wmsOutInvoice.INVOICE_NO.Split(',');
                List<string> sqlList = new List<string>();
                foreach (string item in invoiceIdList)
                {
                    string sqlDeleteWmsOutInvoice = $@"
                                                UPDATE WMS_OUT_INVOICE
                                                SET INVOICE_STATUS = '111',
                                                 UPDATE_BY = '{wmsOutInvoice.UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                INVOICE_ID = '{item}';
                                                ";
                    string sqlDeleteWmsOutInvoiceDtl = $@"
                                                    UPDATE WMS_OUT_INVOICE_DTL
                                                    SET INVOICE_DTL_STATUS = '111',
                                                     UPDATE_BY = '{wmsOutInvoice.UPDATE_BY}',
                                                     UPDATE_TIME = GETDATE(),
                                                     DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    INVOICE_ID = '{item}';
                                                    ";
                    sqlList.Add(sqlDeleteWmsOutInvoice);
                    sqlList.Add(sqlDeleteWmsOutInvoiceDtl);
                    /*
                     ToDo: 后续增加删除移至历史表中。
                     */
                }
                #endregion

                int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                if (row > 0)
                {
                    return SuccessMessageStatus("删除发货单数据成功！", row);
                }
                else
                {
                    return FailMessageStatus("删除发货单数据失败！", row);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"删除发货单数据发生异常，【{ex.Message}】");
            }
        }

        /// <summary>
        /// 分页查询发货记录数据
        /// </summary>
        /// <param name="receiptRecordSearchMd">发货记录查询条件实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsOutInvoiceRecordResult>> GetWmsOutInvoiceRecordListForPage(WmsOutInvoiceRecordSearchMd receiptRecordSearchMd)
        {
            try
            {
                #region SQL语句生成
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.InvoiceNoMsg))
                {
                    sqlCondition.Append($" AND INVOICE_NO = '{receiptRecordSearchMd.InvoiceNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.SourceNoMsg))
                {
                    sqlCondition.Append($" AND SOURCE_BILL_NO = '{receiptRecordSearchMd.SourceNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.RegionNoMsg))
                {
                    sqlCondition.Append($" AND REGION_CODE = '{receiptRecordSearchMd.RegionNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.BinNoMsg))
                {
                    sqlCondition.Append($" AND (BIN_CODE like '%{receiptRecordSearchMd.BinNoMsg}%' OR BIN_NAME like '%{receiptRecordSearchMd.BinNoMsg}%')");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.PalletNoMsg))
                {
                    sqlCondition.Append($" AND PALLET_CODE = '{receiptRecordSearchMd.PalletNoMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.PriorityMsg))
                {
                    sqlCondition.Append($" AND INVOICE_PRIORITY = '{receiptRecordSearchMd.PriorityMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND (MATERIEL_CODE like '%{receiptRecordSearchMd.MaterielMsg}%' OR MATERIEL_NAME = '%{receiptRecordSearchMd.MaterielMsg}%')");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.MaterielTypeMsg))
                {
                    sqlCondition.Append($" AND (MATERIEL_TYPE_CODE = '%{receiptRecordSearchMd.MaterielTypeMsg}%' OR MATERIEL_TYPE_NAME = '%{receiptRecordSearchMd.MaterielTypeMsg}%')");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.SupplierMsg))
                {
                    sqlCondition.Append($" AND SUPPLIER_CODE = '{receiptRecordSearchMd.SupplierMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.BatchNoMsg))
                {
                    sqlCondition.Append($" AND BATCH_NO = '{receiptRecordSearchMd.SupplierMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.ItemStatusMsg))
                {
                    sqlCondition.Append($" AND ITEM_STATUS = '{receiptRecordSearchMd.ItemStatusMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.InspectionResultMsg))
                {
                    sqlCondition.Append($" AND INSPECTION_RESULT = '{receiptRecordSearchMd.InspectionResultMsg}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.InvoiceRecordStatus))
                {
                    sqlCondition.Append($" AND INVOICE_RECORD_STATUS = '{receiptRecordSearchMd.InvoiceRecordStatus}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.StartInvoiceEndTime))
                {
                    sqlCondition.Append($" AND INVOICE_END_TIME >= '{receiptRecordSearchMd.StartInvoiceEndTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.EndInvoiceEndTime))
                {
                    sqlCondition.Append($" AND INVOICE_END_TIME <= '{receiptRecordSearchMd.EndInvoiceEndTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.StartProductTime))
                {
                    sqlCondition.Append($" AND PRODUCT_DATE >= '{receiptRecordSearchMd.StartProductTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.EndProductTime))
                {
                    sqlCondition.Append($" AND PRODUCT_DATE <= '{receiptRecordSearchMd.EndProductTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.StartExpTime))
                {
                    sqlCondition.Append($" AND EXP_DATE >= '{receiptRecordSearchMd.StartExpTime}'");
                }
                if (!string.IsNullOrEmpty(receiptRecordSearchMd.EndExpTime))
                {
                    sqlCondition.Append($" AND EXP_DATE <= '{receiptRecordSearchMd.EndExpTime}'");
                }
                StringBuilder sqlCountInvoiceData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_OUT_INVOICE_RECORD WHERE 1=1");
                sqlCountInvoiceData.Append(sqlCondition.ToString());

                int pageStartIndex = (receiptRecordSearchMd.PageNum - 1) * receiptRecordSearchMd.EveryPageQty;
                int pageEndIndex = receiptRecordSearchMd.PageNum * receiptRecordSearchMd.EveryPageQty;

                StringBuilder sqlQueryInvoiceData = new StringBuilder($@"
                                                                        SELECT
                                                                            INVOICE_RECORD_ID,
                                                                            NEWID,
                                                                            INVOICE_ID,
                                                                            INVOICE_NO,
                                                                            SOURCE_BILL_NO,
                                                                            INVOICE_PRIORITY,
                                                                            INVOICE_DTL_ID,
                                                                            REGION_CODE,
                                                                            REGION_NAME,
                                                                            BIN_CODE,
                                                                            BIN_NAME,
                                                                            TRAY_CODE,
                                                                            PALLET_CODE,
                                                                            INVOICE_END_TIME,
                                                                            MATERIEL_TYPE_CODE,
                                                                            MATERIEL_TYPE_NAME,
                                                                            MATERIEL_CODE,
                                                                            MATERIEL_NAME,
                                                                            MATERIEL_BARCODE,
                                                                            MATERIEL_SPEC,
                                                                            SUPPLIER_CODE,
                                                                            SUPPLIER_NAME,
                                                                            BATCH_NO,
                                                                            PACKAGE_CODE,
                                                                            UNIT_CODE,
                                                                            INVOICE_QTY,
                                                                            PRODUCT_DATE,
                                                                            EXP_DATE,
                                                                            INVOICE_RECORD_STATUS,
                                                                            INVOICE_RECORD_STATUS_NAME,
                                                                            INSPECTION_RESULT,
                                                                            INSPECTION_RESULT_NAME,
                                                                            ITEM_STATUS,
                                                                            ITEM_STATUS_NAME,
                                                                            [DESCRIBE],
                                                                            CREATE_BY,
                                                                            CREATE_NAME,
                                                                            CREATE_TIME,
                                                                            UPDATE_BY,
                                                                            UPDATE_NAME,
                                                                            UPDATE_TIME,
                                                                            DATA_VERSION,
                                                                            REMARKS1,
                                                                            REMARKS2,
                                                                            REMARKS3,
                                                                            REMARKS4,
                                                                            REMARKS5
                                                                        FROM
	                                                                        VW_WMS_OUT_INVOICE_RECORD
                                                                        WHERE
                                                                            1=1
			                                                                {sqlCondition} 
                                                                            ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
                                                                            OFFSET {pageStartIndex} ROWS 
                                                                            FETCH NEXT {receiptRecordSearchMd.EveryPageQty} ROWS ONLY
	                                                                        
                                                                    ");
                #endregion

                int dataCount = Convert.ToInt32(new DataRepository<object>(_dataContext).ExecuteScalar(sqlCountInvoiceData.ToString()));
                List<WmsOutInvoiceRecordResult> resultList = new DataRepository<WmsOutInvoiceRecordResult>(_dataContext).Query(sqlQueryInvoiceData.ToString()).ToList();
                OperateResultInfo<List<WmsOutInvoiceRecordResult>> retDataMsg = SuccessStatus(resultList);
                retDataMsg.DataCount = dataCount;

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsOutInvoiceRecordResult>>($"查询发货记录数据发生异常，【{ex.Message}】", null);
            }
        }
        /// <summary>
        /// 获取可以发货的库存数据
        /// </summary>
        /// <param name="SearchMd">库存查询实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsStkBalanceDtlResult>> GetBalanceForInvoice(WmsStkBalanceDtlSearchMd SearchMd)
        {
            try
            {
                #region SQL语句生成
                string sqlQueryBalanceForInvoice = $@"
                                                    SELECT
	                                                    MATERIEL_CODE,
	                                                    MATERIEL_NAME,
	                                                    MATERIEL_BARCODE,
	                                                    MATERIEL_SPEC,
	                                                    UNIT_CODE,
	                                                    PACKAGE_CODE,
	                                                    BATCH_NO,
	                                                    SUM(QTY) QTY
                                                    FROM
	                                                    VW_WMS_STK_BALANCE_DTL
                                                    WHERE
	                                                    TRAY_STATUS = 55
                                                    AND BALANCE_STATUS = 55
                                                    AND TRAY_DTL_STATUS = 55
                                                    AND BATCH_NO LIKE '%{SearchMd.BatchNoMsg}%'
                                                    AND (
	                                                    MATERIEL_CODE LIKE '%{SearchMd.MaterielMsg}%'
	                                                    OR MATERIEL_NAME LIKE '%{SearchMd.MaterielMsg}%'
	                                                    OR MATERIEL_BARCODE LIKE '%{SearchMd.MaterielMsg}%'
                                                    )
                                                    GROUP BY
	                                                    MATERIEL_CODE,
	                                                    MATERIEL_NAME,
	                                                    MATERIEL_BARCODE,
	                                                    MATERIEL_SPEC,
	                                                    UNIT_CODE,
	                                                    PACKAGE_CODE,
	                                                    BATCH_NO
                                                    ";
                #endregion

                List<WmsStkBalanceDtlResult> resultList = new DataRepository<WmsStkBalanceDtlResult>(_dataContext).Query(sqlQueryBalanceForInvoice).ToList();
                OperateResultInfo<List<WmsStkBalanceDtlResult>> retDataMsg = SuccessStatus(resultList);

                return retDataMsg;
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsStkBalanceDtlResult>>($"查询库存数据发生异常，【{ex.Message}】", null);
            }
        }
        /// <summary>
        /// 发货单审核  -- 自动分配
        /// 查询满足发货单需求的所有库存数据
        /// </summary>
        /// <param name="wmsOutInvoice">发货单实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<WmsBalanceAllocateStatus> InvoiceAllocationBalance(WmsOutInvoiceResult wmsOutInvoice)
        {
            try
            {
                string sqlQueryTrayDtlRowsCount = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_TRAY_DTL_ROWS_COUNT
                                                    ORDER BY DtlRows desc
                                                    ";
                List<WmsStkTrayDtlRowsCountResult> trayDtlRows = new DataRepository<WmsStkTrayDtlRowsCountResult>(_dataContext).Query(sqlQueryTrayDtlRowsCount).ToList();
                if (trayDtlRows.Count > 0)
                {
                    string sqlQueryBalanceDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_STK_BALANCE_DTL
                                                    WHERE
	                                                    TRAY_STATUS = 55
                                                    AND TRAY_DTL_STATUS = 55
                                                    AND BALANCE_STATUS = 55
                                                ";
                    List<WmsStkBalanceDtlResult> balanceDtlData = new DataRepository<WmsStkBalanceDtlResult>(_dataContext).Query(sqlQueryBalanceDtl).ToList();
                    List<WmsStkTrayDtlRowsCountResultExt1> trayAllocateMsg = new List<WmsStkTrayDtlRowsCountResultExt1>();
                    int TrayTaskIdx = 0;
                    if (balanceDtlData.Count > 0)
                    {
                        #region 统计托盘明细命中订单明细的行数
                        foreach (WmsStkTrayDtlRowsCountResult item in trayDtlRows)
                        {
                            //托盘库存明细
                            List<WmsStkBalanceDtlResult> trayDtlBalanceTmp = balanceDtlData.FindAll(x => x.TRAY_ID == item.TRAY_ID);
                            //命中订单物料明细行数
                            int containNum = 0;
                            foreach (WmsStkBalanceDtlResult mdTmp in trayDtlBalanceTmp)
                            {
                                WmsOutInvoiceDtlResult invoiceDtlMd = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO);
                                if (invoiceDtlMd != null)
                                {
                                    containNum++;
                                }
                            }
                            item.IncludedInInvoiceNum = containNum;
                        }
                        #endregion

                        //已分配的托盘库存明细
                        List<WmsStkBalanceDtlResult> AllocateBalanceDtlLst = new List<WmsStkBalanceDtlResult>();

                        //寻找托盘明细可以全部命中订单明细行数的托盘
                        List<WmsStkTrayDtlRowsCountResult> trayDtlRows_1 = trayDtlRows.Where(x => x.IncludedInInvoiceNum == wmsOutInvoice.WmsOutInvoiceDtlList.Count).ToList();//x.DtlRows >= wmsOutInvoice.WmsOutInvoiceDtlList.Count && 
                        if (trayDtlRows_1.Count > 0)
                        {
                            WmsTrayDtlMatchedRateMd trayDtlMatchedRateMd = GetInvoiceOptimalAllocatePallet(trayDtlRows_1, balanceDtlData, wmsOutInvoice.WmsOutInvoiceDtlList);
                            if (trayDtlMatchedRateMd.CompletelyMatchedBalanceDtl.Count > 0)
                            {
                                trayDtlMatchedRateMd.CompletelyMatchedBalanceDtl.OrderBy(x => x.DtlRows).OrderBy(x => x.WeightedValue);
                                WmsStkTrayDtlRowsCountResultExt item = trayDtlMatchedRateMd.CompletelyMatchedBalanceDtl[0];
                                List<WmsStkBalanceDtlResult> trayDtlBalanceTmp = balanceDtlData.FindAll(x => x.TRAY_ID == item.TRAY_ID);
                                foreach (WmsStkBalanceDtlResult mdTmp in trayDtlBalanceTmp)
                                {
                                    WmsOutInvoiceDtlResult invoiceDtlMd = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO);
                                    if (invoiceDtlMd != null)
                                    {
                                        decimal demandQty = invoiceDtlMd.INVOICE_DEMAND_QTY;
                                        decimal allocateQty = invoiceDtlMd.AllocatedBalanceQty;
                                        if (allocateQty < demandQty)//发货单已分配数量
                                        {
                                            wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO).AllocatedBalanceQty = allocateQty + mdTmp.QTY;
                                        }
                                    }
                                }
                                AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                                TrayTaskIdx++;
                                trayAllocateMsg.Add(new WmsStkTrayDtlRowsCountResultExt1 {
                                    BALANCE_ID = item.BALANCE_ID,
                                    TRAY_ID = item.TRAY_ID,
                                    DtlRows = item.DtlRows,
                                    IncludedInInvoiceNum = item.IncludedInInvoiceNum,
                                    WeightedValue = item.WeightedValue,
                                    PalletTaskPriorityIdx = TrayTaskIdx,
                                });
                                return SuccessStatus(new WmsBalanceAllocateStatus { 
                                    BalanceDtlResultLst = AllocateBalanceDtlLst,
                                    PalletTaskResultTask = trayAllocateMsg,
                                });                                
                            }
                            if (trayDtlMatchedRateMd.SomeMatchedBalanceDtl.Count > 0)
                            {
                                trayDtlMatchedRateMd.SomeMatchedBalanceDtl.OrderByDescending(x => x.IncludedInInvoiceNum).OrderBy(x => x.WeightedValue);
                                foreach (WmsStkTrayDtlRowsCountResultExt item in trayDtlMatchedRateMd.SomeMatchedBalanceDtl)
                                {
                                    List<WmsStkBalanceDtlResult> trayDtlBalanceTmp = balanceDtlData.FindAll(x => x.TRAY_ID == item.TRAY_ID);
                                    int thisTrayMatchedNum = 0;
                                    foreach (WmsStkBalanceDtlResult mdTmp in trayDtlBalanceTmp)
                                    {
                                        WmsOutInvoiceDtlResult invoiceDtlMd = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO);
                                        decimal demandQty = invoiceDtlMd.INVOICE_DEMAND_QTY;
                                        decimal allocateQty = invoiceDtlMd.AllocatedBalanceQty;
                                        if (allocateQty < demandQty)//发货单已分配数量
                                        {
                                            wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO).AllocatedBalanceQty = allocateQty + mdTmp.QTY;
                                            thisTrayMatchedNum++;
                                        }
                                    }

                                    var isAllocateFinish = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.AllocatedBalanceQty < x.INVOICE_DEMAND_QTY);
                                    if (isAllocateFinish == null)
                                    {
                                        //if (thisTrayMatchedNum > 0)
                                        //{
                                        AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                                        //}
                                        TrayTaskIdx++;
                                        trayAllocateMsg.Add(new WmsStkTrayDtlRowsCountResultExt1
                                        {
                                            BALANCE_ID = item.BALANCE_ID,
                                            TRAY_ID = item.TRAY_ID,
                                            DtlRows = item.DtlRows,
                                            IncludedInInvoiceNum = item.IncludedInInvoiceNum,
                                            WeightedValue = item.WeightedValue,
                                            PalletTaskPriorityIdx = TrayTaskIdx,
                                        });
                                        return SuccessStatus(new WmsBalanceAllocateStatus
                                        {
                                            BalanceDtlResultLst = AllocateBalanceDtlLst,
                                            PalletTaskResultTask = trayAllocateMsg,
                                        });
                                    }
                                    else
                                    {
                                        if (thisTrayMatchedNum > 0)
                                        {
                                            AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                                            TrayTaskIdx++;
                                            trayAllocateMsg.Add(new WmsStkTrayDtlRowsCountResultExt1
                                            {
                                                BALANCE_ID = item.BALANCE_ID,
                                                TRAY_ID = item.TRAY_ID,
                                                DtlRows = item.DtlRows,
                                                IncludedInInvoiceNum = item.IncludedInInvoiceNum,
                                                WeightedValue = item.WeightedValue,
                                                PalletTaskPriorityIdx = TrayTaskIdx,
                                            });
                                        }
                                    }
                                }
                            }

                            #region 20210305孙亚龙注释
                            //foreach (WmsStkTrayDtlRowsCountResult item in trayDtlRows_1)
                            //{
                            //    List<WmsStkBalanceDtlResult> trayDtlBalanceTmp = balanceDtlData.FindAll(x => x.TRAY_ID == item.TRAY_ID);
                            //    foreach (WmsStkBalanceDtlResult mdTmp in trayDtlBalanceTmp)
                            //    {
                            //        WmsOutInvoiceDtlResult invoiceDtlMd = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO);
                            //        decimal demandQty = invoiceDtlMd.INVOICE_DEMAND_QTY;
                            //        decimal allocateQty = invoiceDtlMd.AllocatedBalanceQty;
                            //        if (allocateQty < demandQty)//发货单已分配数量
                            //        {
                            //            wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO).AllocatedBalanceQty = allocateQty + mdTmp.QTY;
                            //        }
                            //    }

                            //    var isAllocateFinish = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.AllocatedBalanceQty < x.INVOICE_DEMAND_QTY);
                            //    if (isAllocateFinish == null)
                            //    {
                            //        AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                            //        return SuccessStatus(AllocateBalanceDtlLst);
                            //    }
                            //    else
                            //    {
                            //        AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                            //    }
                            //} 
                            #endregion
                            
                        }
                        else
                        {
                            //trayDtlRows.OrderByDescending(x => x.IncludedInInvoiceNum);
                            //foreach (WmsStkTrayDtlRowsCountResult item in trayDtlRows)
                            //{
                            //    List<WmsStkBalanceDtlResult> trayDtlBalanceTmp = balanceDtlData.FindAll(x => x.TRAY_ID == item.TRAY_ID);
                            //    foreach (WmsStkBalanceDtlResult mdTmp in trayDtlBalanceTmp)
                            //    {
                            //        WmsOutInvoiceDtlResult invoiceDtlMd = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO);
                            //        if (invoiceDtlMd != null)
                            //        {
                            //            decimal demandQty = invoiceDtlMd.INVOICE_DEMAND_QTY;
                            //            decimal allocateQty = invoiceDtlMd.AllocatedBalanceQty;
                            //            if (allocateQty < demandQty)
                            //            {
                            //                wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO).AllocatedBalanceQty = allocateQty + mdTmp.QTY;
                            //            }
                            //        }
                            //    }

                            //    var isAllocateFinish = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.AllocatedBalanceQty < x.INVOICE_DEMAND_QTY);
                            //    if (isAllocateFinish == null)
                            //    {
                            //        AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                            //        return SuccessStatus(AllocateBalanceDtlLst);
                            //    }
                            //    else
                            //    {
                            //        AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                            //    }
                            //}

                            //return FailMessageStatus<List<WmsStkBalanceDtlResult>>($"库存不足。", AllocateBalanceDtlLst);
                        }

                        List<WmsStkTrayDtlRowsCountResult> trayDtlRows_2 = trayDtlRows.Where(x =>
                            x.IncludedInInvoiceNum < wmsOutInvoice.WmsOutInvoiceDtlList.Count &&
                            x.IncludedInInvoiceNum > 0).ToList();
                        if (trayDtlRows_2.Count > 0)
                        {
                            trayDtlRows_2.OrderByDescending(x => x.IncludedInInvoiceNum);
                            List<WmsStkTrayDtlRowsCountResultExt> stkTrayDtlRowsCountResultExtLst_2 = GetInvoiceOptimalAllocatePallet_WeightedValue(trayDtlRows_1, balanceDtlData, wmsOutInvoice.WmsOutInvoiceDtlList);
                            stkTrayDtlRowsCountResultExtLst_2.OrderBy(x => x.WeightedValue).OrderByDescending(x => x.IncludedInInvoiceNum);
                            foreach (WmsStkTrayDtlRowsCountResultExt item in stkTrayDtlRowsCountResultExtLst_2)
                            {
                                List<WmsStkBalanceDtlResult> trayDtlBalanceTmp = balanceDtlData.FindAll(x => x.TRAY_ID == item.TRAY_ID);
                                int thisTrayMatchedNum = 0;
                                foreach (WmsStkBalanceDtlResult mdTmp in trayDtlBalanceTmp)
                                {
                                    WmsOutInvoiceDtlResult invoiceDtlMd = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO);
                                    if (invoiceDtlMd != null)
                                    {
                                        decimal demandQty = invoiceDtlMd.INVOICE_DEMAND_QTY;
                                        decimal allocateQty = invoiceDtlMd.AllocatedBalanceQty;
                                        if (allocateQty < demandQty)
                                        {
                                            wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO).AllocatedBalanceQty = allocateQty + mdTmp.QTY;
                                            thisTrayMatchedNum++;
                                        }
                                    }
                                }

                                var isAllocateFinish = wmsOutInvoice.WmsOutInvoiceDtlList.Find(x => x.AllocatedBalanceQty < x.INVOICE_DEMAND_QTY);
                                if (isAllocateFinish == null)
                                {
                                    AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                                    TrayTaskIdx++;
                                    trayAllocateMsg.Add(new WmsStkTrayDtlRowsCountResultExt1
                                    {
                                        BALANCE_ID = item.BALANCE_ID,
                                        TRAY_ID = item.TRAY_ID,
                                        DtlRows = item.DtlRows,
                                        IncludedInInvoiceNum = item.IncludedInInvoiceNum,
                                        WeightedValue = item.WeightedValue,
                                        PalletTaskPriorityIdx = TrayTaskIdx,
                                    });
                                    return SuccessStatus(new WmsBalanceAllocateStatus
                                    {
                                        BalanceDtlResultLst = AllocateBalanceDtlLst,
                                        PalletTaskResultTask = trayAllocateMsg,
                                    });
                                }
                                else
                                {
                                    if (thisTrayMatchedNum > 0)
                                    {
                                        AllocateBalanceDtlLst.AddRange(trayDtlBalanceTmp);
                                        TrayTaskIdx++;
                                        trayAllocateMsg.Add(new WmsStkTrayDtlRowsCountResultExt1
                                        {
                                            BALANCE_ID = item.BALANCE_ID,
                                            TRAY_ID = item.TRAY_ID,
                                            DtlRows = item.DtlRows,
                                            IncludedInInvoiceNum = item.IncludedInInvoiceNum,
                                            WeightedValue = item.WeightedValue,
                                            PalletTaskPriorityIdx = TrayTaskIdx,
                                        });
                                    }
                                }
                            }

                            return FailMessageStatus<WmsBalanceAllocateStatus>($"库存不足。", new WmsBalanceAllocateStatus
                            {
                                BalanceDtlResultLst = AllocateBalanceDtlLst,
                                PalletTaskResultTask = trayAllocateMsg,
                            });
                        }
                        else
                        {
                            return FailMessageStatus($"库存不足。", new WmsBalanceAllocateStatus
                            {
                                BalanceDtlResultLst = AllocateBalanceDtlLst,
                                PalletTaskResultTask = trayAllocateMsg,
                            });
                        }

                    }
                    else
                    {
                        return FailMessageStatus<WmsBalanceAllocateStatus>($"未找库存数据。",null);
                    }
                }
                else
                {
                    return FailMessageStatus<WmsBalanceAllocateStatus>($"未找库存数据。", null);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus<WmsBalanceAllocateStatus>($"发货单库存分配发生异常，【{ex.Message}】", null);
            }
        }

        #region 20210305 孙亚龙新增发货单筛选出库托盘最优解的算法
        private WmsTrayDtlMatchedRateMd GetInvoiceOptimalAllocatePallet(List<WmsStkTrayDtlRowsCountResult> tmpWmsStkTrayDtlRows, List<WmsStkBalanceDtlResult> balanceDtlData, List<WmsOutInvoiceDtlResult> WmsOutInvoiceDtlList)
        {
            try
            {
                WmsTrayDtlMatchedRateMd retMd = new WmsTrayDtlMatchedRateMd();
                foreach (WmsStkTrayDtlRowsCountResult item in tmpWmsStkTrayDtlRows)
                {
                    int matchedNum = 0;
                    decimal thisTrayWeightedValue = 0;
                    List<WmsStkBalanceDtlResult> trayDtlBalanceTmp = balanceDtlData.FindAll(x => x.TRAY_ID == item.TRAY_ID);
                    foreach (WmsOutInvoiceDtlResult invoiceItemMd in WmsOutInvoiceDtlList)
                    {
                        WmsStkBalanceDtlResult mdTmp = trayDtlBalanceTmp.Find(x => x.MATERIEL_CODE == invoiceItemMd.MATERIEL_CODE && x.BATCH_NO == invoiceItemMd.BATCH_NO);
                        decimal demandQty = invoiceItemMd.INVOICE_DEMAND_QTY;
                        decimal allocateQty = mdTmp.QTY;
                        if (allocateQty >= demandQty)
                        {
                            matchedNum++;
                        }
                        thisTrayWeightedValue += Math.Abs(allocateQty - demandQty);// 设定匹配度权值
                    }
                    WmsStkTrayDtlRowsCountResultExt tmpMd = new WmsStkTrayDtlRowsCountResultExt
                    {
                        BALANCE_ID = item.BALANCE_ID,
                        TRAY_ID = item.TRAY_ID,
                        DtlRows = item.DtlRows,
                        IncludedInInvoiceNum = item.IncludedInInvoiceNum,
                        WeightedValue = thisTrayWeightedValue
                    };
                    if (matchedNum == WmsOutInvoiceDtlList.Count)
                    {
                        retMd.CompletelyMatchedBalanceDtl.Add(tmpMd);
                    }
                    else
                    {
                        retMd.SomeMatchedBalanceDtl.Add(tmpMd);
                    }
                }
                return retMd;
            }
            catch
            {
                return new WmsTrayDtlMatchedRateMd();
            }
        }
        private List<WmsStkTrayDtlRowsCountResultExt> GetInvoiceOptimalAllocatePallet_WeightedValue(List<WmsStkTrayDtlRowsCountResult> tmpWmsStkTrayDtlRows, List<WmsStkBalanceDtlResult> balanceDtlData, List<WmsOutInvoiceDtlResult> WmsOutInvoiceDtlList)
        {
            try
            {
                List<WmsStkTrayDtlRowsCountResultExt> retList = new List<WmsStkTrayDtlRowsCountResultExt>();
                foreach (WmsStkTrayDtlRowsCountResult item in tmpWmsStkTrayDtlRows)
                {
                    //int matchedNum = 0;
                    decimal thisTrayWeightedValue = 0;
                    List<WmsStkBalanceDtlResult> trayDtlBalanceTmp = balanceDtlData.FindAll(x => x.TRAY_ID == item.TRAY_ID);
                    foreach (WmsStkBalanceDtlResult mdTmp in trayDtlBalanceTmp)
                    {
                        WmsOutInvoiceDtlResult invoiceItemMd = WmsOutInvoiceDtlList.FirstOrDefault(x => x.MATERIEL_CODE == mdTmp.MATERIEL_CODE && x.BATCH_NO == mdTmp.BATCH_NO);
                        if (invoiceItemMd != null)
                        {
                            decimal demandQty = invoiceItemMd.INVOICE_DEMAND_QTY;
                            decimal allocateQty = mdTmp.QTY;
                            thisTrayWeightedValue += Math.Abs(allocateQty - demandQty);// 设定匹配度权值
                        }
                    }

                    WmsStkTrayDtlRowsCountResultExt tmpMd = new WmsStkTrayDtlRowsCountResultExt
                    {
                        BALANCE_ID = item.BALANCE_ID,
                        TRAY_ID = item.TRAY_ID,
                        DtlRows = item.DtlRows,
                        IncludedInInvoiceNum = item.IncludedInInvoiceNum,
                        WeightedValue = thisTrayWeightedValue
                    };
                    retList.Add(tmpMd);
                }
                return retList;
            }
            catch
            {
                return new List<WmsStkTrayDtlRowsCountResultExt>();
            }
        }
        #endregion

        /// <summary>
        /// 发货单审核  -- 手动分配
        /// 查询满足发货单需求的所有库存数据
        /// </summary>
        /// <param name="SearchMd">库存明细查询实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsStkBalanceDtlResult>> InvoiceAllovationBalance(WmsStkBalanceDtlSearchMd SearchMd)
        {
            try
            {
                StringBuilder sqlCondition = new StringBuilder();
                if (!string.IsNullOrEmpty(SearchMd.MaterielMsg))
                {
                    sqlCondition.Append($" AND (MATERIEL_CODE like '%{SearchMd.MaterielMsg}%' OR MATERIEL_NAME like '%{SearchMd.MaterielMsg}%' OR MATERIEL_BARCODE like '%{SearchMd.MaterielMsg}%')");
                }
                if (!string.IsNullOrEmpty(SearchMd.BatchNoMsg))
                {
                    sqlCondition.Append($" AND BATCH_NO = '{SearchMd.BatchNoMsg}'");
                }
                if (!string.IsNullOrEmpty(SearchMd.RegionMsg))
                {
                    sqlCondition.Append($" AND REGION_CODE = '{SearchMd.RegionMsg}'");
                }
                if (!string.IsNullOrEmpty(SearchMd.BinMsg))
                {
                    sqlCondition.Append($" AND (BIN_CODE LIKE '%{SearchMd.BinMsg}%' OR BIN_NAME LIKE '%{SearchMd.BinMsg}%')");
                }
                if (!string.IsNullOrEmpty(SearchMd.PalletMsg))
                {
                    sqlCondition.Append($" AND PALLET_CODE = '{SearchMd.PalletMsg}'");
                }
                string materielCodeList = "";
                string batchNoList = "";
                foreach (WmsOutInvoiceDtlResult item in SearchMd.InvoiceMd.WmsOutInvoiceDtlList)
                {
                    materielCodeList += "'" + item.MATERIEL_CODE + "',";
                    batchNoList += "'" + item.BATCH_NO + "',";
                }
                sqlCondition.Append($" AND MATERIEL_CODE IN ({materielCodeList.Substring(0, materielCodeList.Length - 1)})");
                sqlCondition.Append($" AND BATCH_NO IN ({batchNoList.Substring(0, batchNoList.Length - 1)})");

                string sqlQueryBalanceDtl = $@"
                                                SELECT
	                                                *
                                                FROM
	                                                VW_WMS_STK_BALANCE_DTL
                                                WHERE
	                                                TRAY_STATUS = 55
                                                AND TRAY_DTL_STATUS = 55
                                                AND BALANCE_STATUS = 55
                                                {sqlCondition}
                                            ";
                List<WmsStkBalanceDtlResult> balanceDtlData = new DataRepository<WmsStkBalanceDtlResult>(_dataContext).Query(sqlQueryBalanceDtl).ToList();
                return SuccessStatus(balanceDtlData);
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsStkBalanceDtlResult>>($"发货单手动分配，库存查询发生异常，【{ex.Message}】", null);
            }
        }
        /// <summary>
        /// 发货审核数据提交
        /// </summary>
        /// <param name="checkResult">发货审核实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo SubmitInvoiceCheckResult(WmsOutInvoiceCheckResult checkResult)
        {
            try
            {
                WmsOutInvoiceResult invoiceMdResult = checkResult.InvoiceMdResult;
                #region SQL语句生成
                List<WmsStkTrayDtlRowsCountResultExt1> TrayIdLst = checkResult.TrayIdLst;
                if (checkResult.IsAutoAllocateBalance)
                {
                    //自动分配库存时，按照自动找库存的逻辑顺序生成托盘出库任务。
                    TrayIdLst.OrderBy(x => x.PalletTaskPriorityIdx);
                }
                else
                {
                    //手动分配库存时，需要进行一次托盘库存分配权值计算，再按照托盘下架的权值，进行排序，最后生成出库任务
                    foreach (WmsStkTrayDtlRowsCountResultExt1 item in TrayIdLst)
                    {
                        string sqlQueryBalanceDtl = $@"
                                                SELECT
	                                                *
                                                FROM
	                                                VW_WMS_STK_BALANCE_DTL
                                                WHERE
	                                                TRAY_STATUS = 55
                                                AND TRAY_DTL_STATUS = 55
                                                AND BALANCE_STATUS = 55
                                                AND TRAY_ID = '{item.TRAY_ID}'
                                            ";
                        List<WmsStkBalanceDtlResult> balanceDtlData = new DataRepository<WmsStkBalanceDtlResult>(_dataContext).Query(sqlQueryBalanceDtl).ToList();
                        if (balanceDtlData.Count > 0)
                        {
                            decimal thisTrayWeightedValue = 0;
                            int matchedNum = 0;
                            foreach (WmsStkBalanceDtlResult balDtlMd in balanceDtlData)
                            {
                                WmsOutInvoiceDtlResult invoiceItemMd = invoiceMdResult.WmsOutInvoiceDtlList.FirstOrDefault(x => x.MATERIEL_CODE == balDtlMd.MATERIEL_CODE && x.BATCH_NO == balDtlMd.BATCH_NO);
                                if (invoiceItemMd != null)
                                {
                                    decimal demandQty = invoiceItemMd.INVOICE_DEMAND_QTY;
                                    decimal allocateQty = balDtlMd.QTY;
                                    thisTrayWeightedValue += Math.Abs(allocateQty - demandQty);// 设定匹配度权值
                                    matchedNum++;
                                }
                            }
                            item.BALANCE_ID = balanceDtlData[0].BALANCE_ID;
                            item.DtlRows = balanceDtlData.Count;//托盘明细行数
                            item.IncludedInInvoiceNum = matchedNum;//匹配行数
                            item.WeightedValue = thisTrayWeightedValue;//匹配权值
                        }
                    }
                    //手动分配库存时按照托盘匹配行数倒序、和权值正序进行排序
                    TrayIdLst.OrderByDescending(x => x.IncludedInInvoiceNum).OrderBy(x=>x.WeightedValue);
                }
                List<string> sqlList = new List<string>();
                string sqlUpdateInvoice = $@"
                                            UPDATE WMS_OUT_INVOICE
                                            SET INVOICE_STATUS = 22,
                                             UPDATE_BY = '{invoiceMdResult.UPDATE_BY}',
                                             UPDATE_TIME = GETDATE(),
                                             DATA_VERSION = DATA_VERSION + 1
                                            WHERE
	                                            INVOICE_ID = '{invoiceMdResult.INVOICE_ID}'
                                        ";
                sqlList.Add(sqlUpdateInvoice);
                foreach (WmsStkTrayDtlRowsCountResultExt1 item in TrayIdLst)
                {
                    string sqlQueryBalanceDtl = $@"
                                                SELECT
	                                                *
                                                FROM
	                                                VW_WMS_STK_BALANCE_DTL
                                                WHERE
	                                                TRAY_STATUS = 55
                                                AND TRAY_DTL_STATUS = 55
                                                AND BALANCE_STATUS = 55
                                                AND TRAY_ID = '{item.TRAY_ID}'
                                            ";
                    List<WmsStkBalanceDtlResult> balanceDtlData = new DataRepository<WmsStkBalanceDtlResult>(_dataContext).Query(sqlQueryBalanceDtl).ToList();
                    if (balanceDtlData.Count > 0)
                    {
                        int putdownId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_Id"));
                        string putdownNo = new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_No");
                        string sqlAddPutDown = $@"
                                                INSERT INTO [WMS_OUT_PUTDOWN] (
	                                                [PUTDOWN_ID],
	                                                [PUTDOWN_NO],
	                                                [PUTDOWN_TYPE],
	                                                [TRAY_CODE],
	                                                [PALLET_CODE],
	                                                [SBIN_CODE],
	                                                [EBIN_CODE],
	                                                [PUTDOWN_PRIORITY],
	                                                [PUTDOWN_STATUS],
	                                                [DESCRIBE],
	                                                [CREATE_BY],
	                                                [CREATE_TIME],
	                                                [UPDATE_BY],
	                                                [UPDATE_TIME],
	                                                [DATA_VERSION],
	                                                [REMARKS1],
	                                                [REMARKS2],
	                                                [REMARKS3],
	                                                [REMARKS4],
	                                                [REMARKS5],
	                                                [SOURCE_NO]
                                                )
                                                VALUES
	                                                (
		                                                '{putdownId}',
		                                                '{putdownNo}',
		                                                '{1}',
		                                                '{balanceDtlData[0].TRAY_CODE}',
		                                                '{balanceDtlData[0].PALLET_CODE}',
		                                                '{balanceDtlData[0].BIN_CODE}',
		                                                NULL,
		                                                '{100}',
		                                                '{0}',
		                                                NULL,
		                                                '{invoiceMdResult.UPDATE_BY}',
		                                                getdate(),
		                                                '{invoiceMdResult.UPDATE_BY}',
		                                                getdate(),
		                                                0,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                NULL,
		                                                '{invoiceMdResult.INVOICE_NO}'
	                                                );
                                                ";
                        sqlList.Add(sqlAddPutDown);
                        string sqlUpdateBalance = $@"
                                                    UPDATE WMS_STK_BALANCE
                                                    SET BALANCE_STATUS = 66,
                                                     UPDATE_BY = '{invoiceMdResult.UPDATE_BY}',
                                                     UPDATE_TIME = GETDATE(),
                                                     DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    BALANCE_ID = '{balanceDtlData[0].BALANCE_ID}'
                                                    ";
                        sqlList.Add(sqlUpdateBalance);
                        string sqlUpdateTray = $@"
                                                    UPDATE WMS_STK_TRAY
                                                    SET TRAY_STATUS = 66,
                                                        UPDATE_BY = '{invoiceMdResult.UPDATE_BY}',
                                                        UPDATE_TIME = GETDATE(),
                                                        DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    TRAY_ID = '{balanceDtlData[0].TRAY_ID}'
                                                ";
                        sqlList.Add(sqlUpdateTray);
                        int thisTrayMatchedNum = 0;
                        foreach (WmsStkBalanceDtlResult balDtlMd in balanceDtlData)
                        {
                            WmsOutInvoiceDtlResult invoiceDtlMd = invoiceMdResult.WmsOutInvoiceDtlList.Find(x => x.MATERIEL_CODE == balDtlMd.MATERIEL_CODE && x.BATCH_NO == balDtlMd.BATCH_NO);
                            #region MyRegion
                            if (invoiceDtlMd != null)
                            {
                                decimal demandQty = invoiceDtlMd.INVOICE_DEMAND_QTY;
                                invoiceDtlMd.AllocatedBalanceQty = invoiceDtlMd.AllocatedBalanceQty + balDtlMd.QTY;
                                decimal allocateQty = invoiceDtlMd.AllocatedBalanceQty;
                                if (allocateQty <= demandQty)
                                {
                                    thisTrayMatchedNum++;
                                }
                            }
                            #endregion
                            int invoiceRecordId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("Invoice_Record_Id"));
                            int putdownDtlId = Convert.ToInt32(new DataRepository<object>(_dataContext).GetSequenceMsg("OutPutdown_Dtl_Id"));
                            string sqlAddInvoiceRecord = $@"
                                                            INSERT INTO [WMS_OUT_INVOICE_RECORD] (
	                                                            [INVOICE_RECORD_ID],
	                                                            [INVOICE_ID],
	                                                            [INVOICE_NO],
	                                                            [SOURCE_BILL_NO],
	                                                            [INVOICE_DTL_ID],
	                                                            [REGION_CODE],
	                                                            [REGION_NAME],
	                                                            [BIN_CODE],
	                                                            [BIN_NAME],
	                                                            [TRAY_CODE],
	                                                            [PALLET_CODE],
	                                                            [INVOICE_EXAMINE_USER],
	                                                            [INVOICE_END_TIME],
	                                                            [MATERIEL_CODE],
	                                                            [MATERIEL_NAME],
	                                                            [MATERIEL_BARCODE],
	                                                            [MATERIEL_SPEC],
	                                                            [PACKAGE_CODE],
	                                                            [UNIT_CODE],
	                                                            [BATCH_NO],
	                                                            [SUPPLIER_CODE],
	                                                            [SUPPLIER_NAME],
	                                                            [INVOICE_QTY],
	                                                            [PRODUCT_DATE],
	                                                            [EXP_DATE],
	                                                            [INSPECTION_RESULT],
	                                                            [INVOICE_RECORD_STATUS],
	                                                            [ITEM_STATUS],
	                                                            [DESCRIBE],
	                                                            [CREATE_BY],
	                                                            [CREATE_TIME],
	                                                            [UPDATE_BY],
	                                                            [UPDATE_TIME],
	                                                            [DATA_VERSION],
	                                                            [REMARKS1],
	                                                            [REMARKS2],
	                                                            [REMARKS3],
	                                                            [REMARKS4],
	                                                            [REMARKS5]
                                                            )
                                                            VALUES
	                                                            (
		                                                            '{invoiceRecordId}',
		                                                            '{invoiceMdResult.INVOICE_ID}',
		                                                            '{invoiceMdResult.INVOICE_NO}',
		                                                            '{invoiceMdResult.SOURCE_BILL_NO}',
		                                                            '{(invoiceDtlMd == null ? 0 : invoiceDtlMd.INVOICE_DTL_ID)}',
		                                                            '{balDtlMd.REGION_CODE}',
		                                                            '{balDtlMd.REGION_NAME}',
		                                                            '{balDtlMd.BIN_CODE}',
		                                                            '{balDtlMd.BIN_NAME}',
		                                                            '{balDtlMd.TRAY_CODE}',
		                                                            '{balDtlMd.PALLET_CODE}',
		                                                            '{invoiceMdResult.UPDATE_NAME}',
		                                                            '{invoiceMdResult.INVOICE_END_TIME}',
		                                                            '{balDtlMd.MATERIEL_CODE}',
		                                                            '{balDtlMd.MATERIEL_NAME}',
		                                                            '{balDtlMd.MATERIEL_BARCODE}',
		                                                            '{balDtlMd.MATERIEL_SPEC}',
		                                                            '{balDtlMd.PACKAGE_CODE}',
		                                                            '{balDtlMd.UNIT_CODE}',
		                                                            '{balDtlMd.BATCH_NO}',
		                                                            '{balDtlMd.SUPPLIER_CODE}',
		                                                            '{balDtlMd.SUPPLIER_NAME}',
		                                                            '{balDtlMd.QTY}',
		                                                            '{balDtlMd.PRODUCT_DATE}',
		                                                            '{balDtlMd.EXP_DATE}',
		                                                            '{balDtlMd.INSPECTION_RESULT}',
		                                                            0,
		                                                            '{balDtlMd.ITEM_STATUS}',
		                                                            '{invoiceMdResult.DESCRIBE}',
		                                                            '{invoiceMdResult.UPDATE_BY}',
		                                                            getdate(),
		                                                            '{invoiceMdResult.UPDATE_BY}',
		                                                            getdate(),
		                                                            0,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL,
		                                                            NULL
	                                                            );
                                                        ";
                            sqlList.Add(sqlAddInvoiceRecord);
                            string sqlAddPutDownDtl = $@"
                                                        INSERT INTO [WMS_OUT_PUTDOWN_DTL] (
	                                                        [PUTDOWN_DTL_ID],
	                                                        [PUTDOWN_ID],
	                                                        [MATERIEL_CODE],
	                                                        [MATERIEL_NAME],
	                                                        [MATERIEL_BARCODE],
	                                                        [MATERIEL_SPEC],
	                                                        [PACKAGE_CODE],
	                                                        [UNIT_CODE],
	                                                        [BATCH_NO],
	                                                        [SUPPLIER_CODE],
	                                                        [SUPPLIER_NAME],
	                                                        [PUTDOWN_QTY],
	                                                        [PRODUCT_DATE],
	                                                        [EXP_DATE],
	                                                        [PUTDOWN_DTL_STATUS],
	                                                        [INSPECTION_RESULT],
	                                                        [ITEM_STATUS],
	                                                        [DESCRIBE],
	                                                        [CREATE_BY],
	                                                        [CREATE_TIME],
	                                                        [UPDATE_BY],
	                                                        [UPDATE_TIME],
	                                                        [DATA_VERSION],
	                                                        [REMARKS1],
	                                                        [REMARKS2],
	                                                        [REMARKS3],
	                                                        [REMARKS4],
	                                                        [REMARKS5]
                                                        )
                                                        VALUES
	                                                        (
		                                                        '{putdownDtlId}',
		                                                        '{putdownId}',
		                                                        '{balDtlMd.MATERIEL_CODE}',
		                                                        '{balDtlMd.MATERIEL_NAME}',
		                                                        '{balDtlMd.MATERIEL_BARCODE}',
		                                                        '{balDtlMd.MATERIEL_SPEC}',
		                                                        '{balDtlMd.PACKAGE_CODE}',
		                                                        '{balDtlMd.UNIT_CODE}',
		                                                        '{balDtlMd.BATCH_NO}',
		                                                        '{balDtlMd.SUPPLIER_CODE}',
		                                                        '{balDtlMd.SUPPLIER_NAME}',
		                                                        '{balDtlMd.QTY}',
		                                                        '{balDtlMd.PRODUCT_DATE}',
		                                                        '{balDtlMd.EXP_DATE}',
		                                                        '{0}',
		                                                        '{balDtlMd.INSPECTION_RESULT}',
		                                                        '{balDtlMd.ITEM_STATUS}',
		                                                        NULL,
		                                                        '{invoiceMdResult.UPDATE_BY}',
		                                                        getdate(),
		                                                        '{invoiceMdResult.UPDATE_BY}',
		                                                        getdate(),
		                                                        0,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL,
		                                                        NULL
	                                                        );
                                                        ";
                            sqlList.Add(sqlAddPutDownDtl);
                            string sqlUpdateTrayDtl = $@"
                                                        UPDATE WMS_STK_TRAY_DTL
                                                        SET TRAY_DTL_STATUS = 66,
                                                         UPDATE_BY = '{invoiceMdResult.UPDATE_BY}',
                                                         UPDATE_TIME = GETDATE(),
                                                         DATA_VERSION = DATA_VERSION + 1
                                                        WHERE
	                                                        TRAY_DTL_ID = '{balDtlMd.TRAY_DTL_ID}'
                                                        ";
                            sqlList.Add(sqlUpdateTrayDtl);
                        }
                        string sqlQueryRetreatRegionBinMsg = $@"SELECT
                                                        A.*, B.USER_NAME Create_Name,
                                                        C.USER_NAME Update_Name
                                                        FROM
                                                        BAS_BIN A
                                                        LEFT JOIN SYS_USER B ON A.CREATE_BY = B.USER_ID
                                                        LEFT JOIN SYS_USER C ON A.UPDATE_BY = C.USER_ID
                                                        WHERE
                                                        A.BIN_TYPE = '2' AND A.REGION_CODE = '{balanceDtlData[0].REGION_CODE}'";
                        List<BinResult> resultBinList = new DataRepository<BinResult>(_dataContext).Query(sqlQueryRetreatRegionBinMsg).ToList();
                        string eLocNo = string.Empty;
                        if (thisTrayMatchedNum == balanceDtlData.Count)
                        {
                            eLocNo = "1008";
                        }
                        else
                        {
                            eLocNo = "1001";
                        }
                        #region ToDo ： 添加WMS大任务
                        string taskNo = new DataRepository<object>(_dataContext).GetSequenceMsg("Task_No");
                        string sqlInsertWmsTask = $@"
                                            INSERT INTO [dbo].[WMS_TSK_TASK] (
	                                            [WAREHOUSE_CODE],
	                                            [WAREHOUSE_NAME],
	                                            [TASK_NO],
	                                            [TRAY_CODE],
	                                            [PALLET_CODE],
	                                            [TRAY_LOADED_TYPE],
	                                            [SLOC_CODE],
	                                            [ELOC_CODE],
	                                            [CLOC_CODE],
	                                            [TASK_TYPE],
	                                            [TASK_MSG],
	                                            [ERR_FLAG],
	                                            [TASK_PRIORITY],
	                                            [TASK_STATUS],
	                                            [SBIN_CODE],
	                                            [EBIN_CODE],
	                                            [CMD_NO],
	                                            [ROUTE_CODE],
	                                            [CREATE_BY],
	                                            [CREATE_TIME],
	                                            [UPDATE_BY],
	                                            [UPDATE_TIME],
	                                            [DATA_VERSION],
	                                            [REMARKS1],
	                                            [REMARKS2],
	                                            [REMARKS3],
	                                            [REMARKS4],
	                                            [REMARKS5]
                                            )
                                            VALUES
                                            (
	                                            'WarehouseTest1',
	                                            '仓库测试1',
	                                            '{taskNo}',
	                                            '{balanceDtlData[0].TRAY_CODE}',
	                                            '{balanceDtlData[0].PALLET_CODE}',
	                                            '{1}',
	                                            '{1012}',
	                                            '{eLocNo}',
	                                            '{1012}',
	                                            '{6}',
	                                            NULL,
	                                            '{0}',
	                                            '{100}',
	                                            '{0}',
	                                            '{balanceDtlData[0].BIN_CODE}',
	                                            '{resultBinList[0].BIN_CODE}',
	                                            NULL,
	                                            '',
	                                            '{invoiceMdResult.UPDATE_BY}',
	                                            getdate(),
	                                            '{invoiceMdResult.UPDATE_BY}',
	                                            getdate(),
	                                            0,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL,
	                                            NULL
                                            );
                                         ";
                        sqlList.Add(sqlInsertWmsTask);
                        #endregion

                    }
                    else
                    {
                        return FailMessageStatus($"预分配的托盘信息【组盘ID为：{item.TRAY_ID}】发生变动，无法进行发货审核，请重新选择分配的托盘！");
                    }
                }

                string sqlUpdateInvoiceDtl = $@"
                                                        UPDATE WMS_OUT_INVOICE_DTL
                                                        SET INVOICE_DTL_STATUS = 22,
                                                         UPDATE_BY = '{invoiceMdResult.UPDATE_BY}',
                                                         UPDATE_TIME = GETDATE(),
                                                         DATA_VERSION = DATA_VERSION + 1
                                                        WHERE
	                                                        INVOICE_ID = '{invoiceMdResult.INVOICE_ID}'
                                                        ";
                sqlList.Add(sqlUpdateInvoiceDtl);
                #endregion

                int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                if (row > 0)
                {
                    return SuccessMessageStatus("发货单审核成功！", row);
                }
                else
                {
                    return FailMessageStatus("发货单审核失败！", row);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"提交发货单审核结果发生异常，【{ex.Message}】");
            }
        }

        #region 到货确认
        /// <summary>
        /// 获取托盘到货确认数据
        /// </summary>
        /// <param name="putDownResult">下架单实体类对象</param>
        /// <returns></returns>
        public OperateResultInfo<List<WmsOutInvoiceRecordExtResult>> QueryInvoiceSortData(WmsOutPutDownResult putDownResult)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                string sqlQueryPutDownForPallet = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_OUT_PUTDOWN
                                                    WHERE
	                                                    PALLET_CODE = '{putDownResult.PALLET_CODE}'
                                                    AND PUTDOWN_STATUS = 55
                                                    ";
                List<WmsOutPutDownResult> resultList = new DataRepository<WmsOutPutDownResult>(_dataContext).Query(sqlQueryPutDownForPallet).ToList();
                if (resultList.Count > 0)
                {
                    putDownResult = resultList[0];
                    string sqlQueryWmsPutDownDtl = $@"
                                                    SELECT
	                                                    *
                                                    FROM
	                                                    VW_WMS_OUT_PUTDOWN_DTL
                                                    WHERE
	                                                    PUTDOWN_ID = '{putDownResult.PUTDOWN_ID}'
                                                    ORDER BY PUTDOWN_DTL_STATUS,PUTDOWN_DTL_ID
                                                    ";
                    List<WmsOutPutDownDtlResult> PutDownDtlListResult = new DataRepository<WmsOutPutDownDtlResult>(_dataContext).Query(sqlQueryWmsPutDownDtl).ToList();
                    string sqlQueryWmsInvoiceRecordExt = $@"
                                                            SELECT
	                                                            A.*, B.INVOICE_DEMAND_QTY,
	                                                            B.INVOICE_CONFIRM_QTY,
	                                                            B.PUTDOWN_QTY INVOICE_PUTDOWN_QTY
                                                            FROM
	                                                            VW_WMS_OUT_INVOICE_RECORD A
                                                            LEFT JOIN VW_WMS_OUT_INVOICE_DTL B ON A.INVOICE_DTL_ID = B.INVOICE_DTL_ID
                                                            WHERE
	                                                            A.TRAY_CODE = '{putDownResult.TRAY_CODE}'
                                                         ";
                    List<WmsOutInvoiceRecordExtResult> InvoiceRecordExtListResult = new DataRepository<WmsOutInvoiceRecordExtResult>(_dataContext).Query(sqlQueryWmsInvoiceRecordExt).ToList();
                    string sqlQueryWmsBalanceDtl = $@"
                                                        SELECT
	                                                        *
                                                        FROM
	                                                        VW_WMS_STK_BALANCE_DTL
                                                        WHERE
	                                                        TRAY_CODE = '{putDownResult.TRAY_CODE}'
                                                        AND BALANCE_STATUS = 66
                                                   ";
                    List<WmsStkBalanceDtlResult> BalanceDtlListResult = new DataRepository<WmsStkBalanceDtlResult>(_dataContext).Query(sqlQueryWmsBalanceDtl).ToList();
                    foreach (WmsOutInvoiceRecordExtResult item in InvoiceRecordExtListResult)
                    {
                        WmsOutPutDownDtlResult putdownDtlMd = PutDownDtlListResult.Find(x => x.MATERIEL_CODE == item.MATERIEL_CODE && x.BATCH_NO == item.BATCH_NO);
                        WmsStkBalanceDtlResult balanceDtlMd = BalanceDtlListResult.Find(x => x.MATERIEL_CODE == item.MATERIEL_CODE && x.BATCH_NO == item.BATCH_NO);
                        item.TRAY_QTY = balanceDtlMd.QTY;
                        if ((item.INVOICE_DEMAND_QTY - item.INVOICE_CONFIRM_QTY) > putdownDtlMd.PUTDOWN_QTY)
                        {
                            item.INVOICE_SORT_QTY = putdownDtlMd.PUTDOWN_QTY;
                        }
                        else
                        {
                            item.INVOICE_SORT_QTY = (item.INVOICE_DEMAND_QTY - item.INVOICE_CONFIRM_QTY);
                        }
                    }
                    return SuccessStatus(InvoiceRecordExtListResult);
                }
                else
                {
                    return FailMessageStatus<List<WmsOutInvoiceRecordExtResult>>($"未查询到托盘号：【{putDownResult.PALLET_CODE}】的下架单信息！", null);
                }
                #endregion
            }
            catch (Exception ex)
            {
                return FailMessageStatus<List<WmsOutInvoiceRecordExtResult>>($"查询托盘出库分拣数据处理发生异常，【{ex.Message}】",null);
            }
        }
        /// <summary>
        /// 提交到货确认数据
        /// </summary>
        /// <param name="wmsOutInvoiceRecordExts">发货记录拓展数据列表</param>
        /// <returns></returns>
        public OperateResultInfo SubmitInvoiceSortData(List<WmsOutInvoiceRecordExtResult> wmsOutInvoiceRecordExts)
        {
            try
            {
                #region SQL语句生成
                List<string> sqlList = new List<string>();
                string sqlUpdatePutDown = $@"
                                            UPDATE WMS_OUT_PUTDOWN
                                            SET PUTDOWN_STATUS = 99,
                                            UPDATE_BY = '{wmsOutInvoiceRecordExts[0].UPDATE_BY}',
                                            UPDATE_TIME = GETDATE(),
                                            DATA_VERSION = DATA_VERSION + 1
                                            WHERE
                                            TRAY_CODE = '{wmsOutInvoiceRecordExts[0].TRAY_CODE}';
                                          ";
                sqlList.Add(sqlUpdatePutDown);
                string sqlUpdatePutDownDtl = $@"
                                            UPDATE WMS_OUT_PUTDOWN_DTL
                                            SET PUTDOWN_DTL_STATUS = 99,
                                            UPDATE_BY = '{wmsOutInvoiceRecordExts[0].UPDATE_BY}',
                                            UPDATE_TIME = GETDATE(),
                                            DATA_VERSION = DATA_VERSION + 1
                                            WHERE
                                            PUTDOWN_ID IN (SELECT PUTDOWN_ID FROM WMS_OUT_PUTDOWN WHERE TRAY_CODE = '{wmsOutInvoiceRecordExts[0].TRAY_CODE}');
                                          ";
                sqlList.Add(sqlUpdatePutDownDtl);

                foreach (WmsOutInvoiceRecordExtResult item in wmsOutInvoiceRecordExts)
                {
                    string sqlUpdateInvoiceDtl = $@"
                                                    UPDATE WMS_OUT_INVOICE_DTL
                                                    SET INVOICE_CONFIRM_QTY = INVOICE_CONFIRM_QTY + '{item.INVOICE_QTY}',
                                                     INVOICE_DTL_STATUS = 99,
                                                     UPDATE_BY = '{item.UPDATE_BY}',
                                                     UPDATE_TIME = GETDATE(),
                                                     DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
	                                                    INVOICE_DTL_ID = '{item.INVOICE_DTL_ID}' ;
                                                ";
                    sqlList.Add(sqlUpdateInvoiceDtl);
                }
                string sqlUpdateInvoice = $@"
                                                IF NOT EXISTS (
	                                                SELECT
		                                                1
	                                                FROM
		                                                VW_WMS_OUT_INVOICE_DTL
	                                                WHERE
		                                                INVOICE_ID = '{wmsOutInvoiceRecordExts[0].INVOICE_ID}'
	                                                AND INVOICE_DTL_STATUS < 99
                                                ) UPDATE WMS_OUT_INVOICE
                                                SET INVOICE_STATUS = 99,
                                                 UPDATE_BY = '{wmsOutInvoiceRecordExts[0].UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                INVOICE_ID = '{wmsOutInvoiceRecordExts[0].INVOICE_ID}'
                                                ";
                sqlList.Add(sqlUpdateInvoice);
                string sqlUpdateInvoiceRecord = $@"
                                                UPDATE WMS_OUT_INVOICE_RECORD
                                                SET INVOICE_RECORD_STATUS = 2,
                                                 UPDATE_BY = '{wmsOutInvoiceRecordExts[0].UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                TRAY_CODE = '{wmsOutInvoiceRecordExts[0].TRAY_CODE}'
                                                AND PALLET_CODE = '{wmsOutInvoiceRecordExts[0].PALLET_CODE}'
                                                AND INVOICE_RECORD_STATUS = 1;
                                                ";
                sqlList.Add(sqlUpdateInvoiceRecord);
                string sqlUpdateWmsStkTray = $@"
                                                UPDATE WMS_STK_TRAY
                                                SET TRAY_STATUS = 99,
                                                 UPDATE_BY = '{wmsOutInvoiceRecordExts[0].UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                TRAY_CODE = '{wmsOutInvoiceRecordExts[0].TRAY_CODE}';
                                                ";
                string sqlUpdateWmsStkTrayDtl = $@"
                                                UPDATE WMS_STK_TRAY_DTL
                                                SET TRAY_DTL_STATUS = 99,
                                                 UPDATE_BY = '{wmsOutInvoiceRecordExts[0].UPDATE_BY}',
                                                 UPDATE_TIME = GETDATE(),
                                                 DATA_VERSION = DATA_VERSION + 1
                                                WHERE
	                                                TRAY_ID IN (
		                                                SELECT
			                                                TRAY_ID
		                                                FROM
			                                                WMS_STK_TRAY
		                                                WHERE
			                                                TRAY_CODE = '{wmsOutInvoiceRecordExts[0].TRAY_CODE}'
	                                                );
                                                ";
                string sqlUpdateWmsStkBalance = $@"
                                                    UPDATE WMS_STK_BALANCE
                                                    SET BALANCE_STATUS = 99,
                                                    UPDATE_BY = '{wmsOutInvoiceRecordExts[0].UPDATE_BY}',
                                                    UPDATE_TIME = GETDATE(),
                                                    DATA_VERSION = DATA_VERSION + 1
                                                    WHERE
                                                    TRAY_CODE = '{wmsOutInvoiceRecordExts[0].TRAY_CODE}';
                                                 ";
                sqlList.Add(sqlUpdateWmsStkTray);
                sqlList.Add(sqlUpdateWmsStkTrayDtl);
                sqlList.Add(sqlUpdateWmsStkBalance);

                #region ToDo ： 更新WMS大任务
                //string sqlUpdateWmsTask = $@"

                //                     ";
                //sqlList.Add(sqlUpdateWmsTask);
                #endregion

                #endregion

                int row = new DataRepository<object>(_dataContext).ExecSqlListTran(sqlList);
                if (row > 0)
                {
                    return SuccessMessageStatus("出库分拣操作成功！", row);
                }
                else
                {
                    return FailMessageStatus("出库分拣操作失败！", row);
                }
            }
            catch (Exception ex)
            {
                return FailMessageStatus($"提交到货确认数据发生异常，【{ex.Message}】");
            }
        }
        #endregion
    }
}