Wms_WebApi/NXWMS.WebApi/NXWMS.Service/NXWMS/Balance/BalanceSearchService.cs

using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using DapperORMCore.String.Consts;
using Microsoft.Extensions.Configuration;
using NXWMS.IService.NXWMS.Balance;
using NXWMS.Model.AppModels.Condition.Balance;
using NXWMS.Model.AppModels.Result.Balance;
using NXWMS.Model.AppModels.Result.Base;
using NXWMS.Model.Common;
using NXWMS.String.Enums;
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.Balance
{
    /// <summary>
    /// 库存查询
    /// </summary>
    [AutoInject(typeof(IBalanceSearchService), InjectType.Scope)]
    public class BalanceSearchService : ServiceBase, IBalanceSearchService
    {
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

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

        /// <summary>
        /// 配置
        /// </summary>
        private IConfiguration _configuration;

        public BalanceSearchService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository)
        {
            this._dataContext = dataRepositoryContext;
            this._configuration = configuration;
            this._iSQLNodeRepository = iSQLNodeRepository;
        }

        /// <summary>
        /// 库存明细查询
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo<PageQueryResultInfo<BalanceDetailSearchResult>> GetDetailList(BalanceDetailSearchCondition info)
        {
            if ((info.PageIndex == 0 || info.PageSize == 0) && info.Id == null && string.IsNullOrWhiteSpace(info.Ids))
            {
                return FailMessageStatus("请传递分页码和分页个数!",
                            new PageQueryResultInfo<BalanceDetailSearchResult>());
            }
            var sql = $@"SELECT MaterielTypeCode=BAS_MATERIEL.MATERIEL_TYPE,MaterielTypeName=BAS_MATERIEL_TYPE.MATERIEL_TYPE_NAME,
                        MaterielCode=BAS_MATERIEL.MATERIEL_CODE,MaterielName=BAS_MATERIEL.MATERIEL_NAME,UnitCode=BAS_UNIT.UNIT_CODE,
                            UnitName=BAS_UNIT.UNIT_NAME,
                        QTY=WMS_STK_TRAY_DTL.QTY,BatchNo=WMS_STK_TRAY_DTL_EXT.BATCH_NO,TrayCode=WMS_STK_TRAY.TRAY_CODE,
                        PalletCode = WMS_STK_BALANCE.PALLET_CODE,PalletName=BAS_PALLET.PALLET_NAME,ProductDate=WMS_STK_TRAY_DTL_EXT.PRODUCT_DATE,
                            ExpDate=WMS_STK_TRAY_DTL_EXT.EXP_DATE,
                        QualityStatus=WMS_STK_TRAY_DTL_EXT.QUALITY_STATUS,QualityStatusName=
                            {_iSQLNodeRepository.GetEnumIntCaseString<QualityStatus>("QUALITY_STATUS", "WMS_STK_TRAY_DTL_EXT")},
                        BalanceStatus=WMS_STK_BALANCE.BALANCE_STATUS,BalanceStatusName=
                            {_iSQLNodeRepository.GetEnumIntCaseString<BalanceStatus>("BALANCE_STATUS", "WMS_STK_BALANCE")},
                        CreateName=(SELECT A.USER_NAME FROM SYS_USER A WHERE A.USER_ID=WMS_STK_BALANCE.CREATE_BY),
                        UpdateName=(SELECT B.USER_NAME FROM SYS_USER B WHERE B.USER_ID=WMS_STK_BALANCE.UPDATE_BY),
                        SupplierCode=WMS_STK_TRAY_DTL_EXT.SUPPLIER_CODE,
                        SupplierName=WMS_STK_TRAY_DTL_EXT.SUPPLIER_NAME,
                        SpecsModel=BAS_MATERIEL.SPECS_MODEL,
                        TrayDtlId=WMS_STK_TRAY_DTL.TRAY_DTL_ID,
                        WMS_STK_BALANCE.* FROM WMS_STK_BALANCE
                        LEFT JOIN WMS_STK_TRAY ON WMS_STK_TRAY.TRAY_CODE=WMS_STK_BALANCE.TRAY_CODE
                        LEFT JOIN WMS_STK_TRAY_DTL ON WMS_STK_TRAY.TRAY_ID=WMS_STK_TRAY_DTL.TRAY_ID
                        LEFT JOIN WMS_STK_TRAY_DTL_EXT ON WMS_STK_TRAY_DTL_EXT.TRAY_DTL_ID=WMS_STK_TRAY_DTL.TRAY_DTL_ID
                       LEFT  JOIN BAS_PALLET ON BAS_PALLET.PALLET_CODE=WMS_STK_TRAY.PALLET_CODE
                        JOIN BAS_MATERIEL ON BAS_MATERIEL.MATERIEL_CODE=WMS_STK_TRAY_DTL.MATERIEL_CODE
                        JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL.MATERIEL_TYPE=BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE
                        JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE=BAS_MATERIEL.UNIT_CODE 
                        WHERE 1=1 ";

            var sqlAndBuilder = new StringBuilder();

            sqlAndBuilder = string.IsNullOrWhiteSpace(info.Ids) ?
                       sqlAndBuilder :
                       sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_ID", info.Ids, DBOperationString._In,
                               "WMS_STK_BALANCE"));
            sqlAndBuilder = info.Id != null ?
                        info.Id > 0 ?
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_ID", info.Id, DBOperationString._ContainIn,
                                "WMS_STK_BALANCE")) :
                        sqlAndBuilder : sqlAndBuilder;
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.RegionCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("REGION_CODE", info.RegionCode, DBOperationString._ContainIn,
                                "WMS_STK_BALANCE"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.QualityStatus) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("QUALITY_STATUS", info.QualityStatus, DBOperationString._ContainIn,
                                "WMS_STK_TRAY_DTL_EXT"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.MaterielType) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("MATERIEL_TYPE", info.MaterielType, DBOperationString._ContainIn,
                                "BAS_MATERIEL"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.SpecsModel) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("SPECS_MODEL", info.SpecsModel, DBOperationString._ContainIn,
                                "BAS_MATERIEL"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.BatchNo) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BATCH_NO", info.BatchNo, DBOperationString._ContainIn,
                                "WMS_STK_TRAY_DTL_EXT"));
            if (info.BalanceStatus == null)
            {
                sqlAndBuilder = sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_STATUS", (int)BalanceStatus.Complete, DBOperationString._SmallIn, "WMS_STK_BALANCE"));
            }
            else if ((int)info.BalanceStatus < (int)BalanceStatus.Complete)
            {
                sqlAndBuilder = sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_STATUS", (int)info.BalanceStatus, DBOperationString._SmallEqual, "WMS_STK_BALANCE"));
            }
            else
            {
                sqlAndBuilder = sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_STATUS", (int)info.BalanceStatus, DBOperationString._Equal, "WMS_STK_BALANCE"));
            }
            //sqlAndBuilder = info.BalanceStatus == null ?
            //            sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_STATUS", (int)BalanceStatus.Complete, DBOperationString._SmallEqual, "WMS_STK_BALANCE")) :
            //            sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_STATUS", (int)info.BalanceStatus, DBOperationString._ContainIn,
            //                    "WMS_STK_BALANCE"));

            sqlAndBuilder = !string.IsNullOrWhiteSpace(info.SupplierCode) ?
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("SUPPLIER_CODE", info.SupplierCode, DBOperationString._Equal,
                                "WMS_STK_TRAY_DTL_EXT")) :
                        sqlAndBuilder;
            //sqlAndBuilder = info.IsUsed == null ?
            //             sqlAndBuilder :
            //             sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("USED_FLAG", info.IsUsed, DBOperationString._Equal,
            //                    "WMS_STK_BALANCE"));
            sqlAndBuilder = info.ProductBeginDate != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("PRODUCT_DATE", info.ProductBeginDate, DBOperationString._LargeEqual,
                                "WMS_STK_TRAY_DTL_EXT"));
            sqlAndBuilder = info.ProductEndDate != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("PRODUCT_DATE", info.ProductEndDate, DBOperationString._SmallEqual,
                                "WMS_STK_TRAY_DTL_EXT"));
            sqlAndBuilder = info.ExpBeginDate != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("EXP_DATE", info.ExpBeginDate, DBOperationString._LargeEqual,
                                "WMS_STK_TRAY_DTL_EXT"));
            sqlAndBuilder = info.ExpEndDate != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("EXP_DATE", info.ExpEndDate, DBOperationString._SmallEqual,
                                "WMS_STK_TRAY_DTL_EXT"));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");

            //信息类条件
            //仓库信息
            var sqlOrBuilder = new StringBuilder();

            sqlOrBuilder = string.IsNullOrWhiteSpace(info.WarehouseInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("WAREHOUSE_CODE", info.WarehouseInfo, DBOperationString._ContainIn,
                                "WMS_STK_BALANCE"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.WarehouseInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("WAREHOUSE_NAME", info.WarehouseInfo, DBOperationString._ContainIn,
                                "WMS_STK_BALANCE"));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //库位信息
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.BinInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("BIN_CODE", info.BinInfo, DBOperationString._ContainIn,
                                "WMS_STK_BALANCE"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.BinInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("BIN_NAME", info.BinInfo, DBOperationString._ContainIn,
                                "WMS_STK_BALANCE"));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //物料信息
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MATERIEL_CODE", info.MaterielInfo, DBOperationString._ContainIn,
                                "BAS_MATERIEL"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MATERIEL_NAME", info.MaterielInfo, DBOperationString._ContainIn,
                                "BAS_MATERIEL"));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //物料信息
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.PalletInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("PALLET_CODE", info.PalletInfo, DBOperationString._ContainIn,
                                "BAS_PALLET"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.PalletInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("PALLET_NAME", info.PalletInfo, DBOperationString._ContainIn,
                                "BAS_PALLET"));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            IEnumerable<BalanceDetailSearchResult> result;
            IEnumerable<BalanceDetailSearchResult> totalResult;
            totalResult = new DataRepository<BalanceDetailSearchResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<BalanceDetailSearchResult>(_dataContext).QueryPage(sql,
                                                "CREATE_TIME", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<BalanceDetailSearchResult>
            {
                RowData = result,
                PageConditionInfo = info,
                TotalCount = totalResult.Count(),
                TotalPageCount = (int)Math.Ceiling((double)totalResult.Count() / info.PageSize)
            });
        }

        /// <summary>
        /// 库存统计查询
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo<PageQueryResultInfo<BalanceStatisticsSearchResult>> GetStatisticsList(BalanceStatisticsSearchCondition info)
        {
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                return FailMessageStatus("请传递分页码和分页个数!",
                            new PageQueryResultInfo<BalanceStatisticsSearchResult>());
            }
            var sql = string.Empty;
            var sqlOrBuilder = new StringBuilder();
            var sqlAndBuilder = new StringBuilder();
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.UnitCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("UnitCode", info.UnitCode, DBOperationString._ContainIn,
                                "Result"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.MaterielType) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("MaterielTypeCode", info.MaterielType, DBOperationString._ContainIn,
                                "Result"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.BatchNo) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BatchNo", info.BatchNo, DBOperationString._ContainIn,
                                "Result"));
            sql = (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");

            //物料
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MaterielCode", info.MaterielInfo, DBOperationString._ContainIn,
                                "Result"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MaterielName", info.MaterielInfo, DBOperationString._ContainIn,
                                "Result"));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //库区
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.RegionInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("REGION_CODE", info.RegionInfo, DBOperationString._ContainIn,
                                "Result"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.RegionInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("REGION_NAME", info.RegionInfo, DBOperationString._ContainIn,
                                "Result"));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");


            sql = $@"SELECT WarehouseCode=Result.WAREHOUSE_CODE,WarehouseName=Result.WAREHOUSE_NAME,
                        RegionCode=Result.REGION_CODE,RegionName=Result.REGION_NAME,
                        MaterielTypeCode,MaterielTypeName,MaterielCode,
                        MaterielName,UnitCode,UnitName,BatchNo,QTY=Count(*) FROM (
                    SELECT 
                        MaterielTypeCode=BAS_MATERIEL.MATERIEL_TYPE,MaterielTypeName=BAS_MATERIEL_TYPE.MATERIEL_TYPE_NAME,
                        MaterielCode=BAS_MATERIEL.MATERIEL_CODE,MaterielName=BAS_MATERIEL.MATERIEL_NAME,UnitCode=BAS_UNIT.UNIT_CODE,UnitName=BAS_UNIT.UNIT_NAME,
                        QTY=WMS_STK_TRAY_DTL.QTY,BatchNo=WMS_STK_TRAY_DTL_EXT.BATCH_NO,TrayCode=WMS_STK_TRAY.TRAY_CODE,
                        PalletCode = BAS_PALLET.PALLET_CODE,PalletName=BAS_PALLET.PALLET_NAME,ProductDate=WMS_STK_TRAY_DTL_EXT.PRODUCT_DATE,ExpDate=WMS_STK_TRAY_DTL_EXT.EXP_DATE,
                        QualityStatus=WMS_STK_TRAY_DTL_EXT.QUALITY_STATUS ,QualityStatusName='',BalanceStatus=WMS_STK_BALANCE.BALANCE_STATUS,BalanceStatusName='',
                        CreateName=(SELECT A.USER_NAME FROM SYS_USER A WHERE A.USER_ID=WMS_STK_BALANCE.CREATE_BY),
                        UpdateName=(SELECT B.USER_NAME FROM SYS_USER B WHERE B.USER_ID=WMS_STK_BALANCE.UPDATE_BY),
                        WMS_STK_BALANCE.* from WMS_STK_BALANCE
                        JOIN WMS_STK_TRAY ON WMS_STK_TRAY.TRAY_CODE=WMS_STK_BALANCE.TRAY_CODE
                        LEFT JOIN WMS_STK_TRAY_DTL ON WMS_STK_TRAY.TRAY_ID=WMS_STK_TRAY_DTL.TRAY_ID
                        LEFT JOIN WMS_STK_TRAY_DTL_EXT ON WMS_STK_TRAY_DTL_EXT.TRAY_DTL_ID=WMS_STK_TRAY_DTL.TRAY_DTL_ID
                        JOIN BAS_PALLET ON BAS_PALLET.PALLET_CODE=WMS_STK_TRAY.PALLET_CODE
                        JOIN BAS_MATERIEL ON BAS_MATERIEL.MATERIEL_ID=WMS_STK_TRAY_DTL.MATERIEL_ID
                        JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL.MATERIEL_TYPE=BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE
                        JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE=BAS_MATERIEL.UNIT_CODE
                        ) Result
                        WHERE 1=1 {sql}
                        GROUP BY Result.WAREHOUSE_CODE,Result.WAREHOUSE_NAME,
                        Result.REGION_CODE,Result.REGION_NAME,
                        MaterielTypeCode,MaterielTypeName,MaterielCode,
                        MaterielName,UnitCode,UnitName,BatchNo ";

            var result = new DataRepository<BalanceStatisticsSearchResult>(_dataContext).QueryPage(sql,
                                                      "RegionCode", info.PageSize, info.PageIndex, true);
            var totalResult = new DataRepository<BalanceStatisticsSearchResult>(_dataContext).Query(sql);

            return SuccessStatus(new PageQueryResultInfo<BalanceStatisticsSearchResult>
            {
                RowData = result,
                PageConditionInfo = info,
                TotalCount = totalResult.Count(),
                TotalPageCount = (int)Math.Ceiling((double)totalResult.Count() / info.PageSize)
            });
        }

        /// <summary>
        /// 主盘点单列表
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo<PageQueryResultInfo<InventoryMainSearchResult>> GetMainInventoryList(InventoryMainSearchCondition info)
        {

            var sql = $@"SELECT 
                        InventoryNo=WMS_STK_INVENTORY.INVENTORY_NO,
                            CreateName=(SELECT A.USER_NAME FROM SYS_USER A WHERE A.USER_ID=WMS_STK_INVENTORY.CREATE_BY),
                            UpdateName=(SELECT B.USER_NAME FROM SYS_USER B WHERE B.USER_ID=WMS_STK_INVENTORY.UPDATE_BY),
                        InventoryTypeName = {_iSQLNodeRepository.GetEnumIntCaseString<InventoryType>("INVENTORY_TYPE", "WMS_STK_INVENTORY")},
                        InventoryModeName = {_iSQLNodeRepository.GetEnumIntCaseString<InventoryMode>("INVENTORY_MODE", "WMS_STK_INVENTORY")},
                        InventoryStatusName = {_iSQLNodeRepository.GetEnumIntCaseString<InventoryStatus>("INVENTORY_STATUS", "WMS_STK_INVENTORY")},
                        ResultFlagName = {_iSQLNodeRepository.GetEnumIntCaseString<ResultFlag>("RESULT_FLAG", "WMS_STK_INVENTORY")},
                        WMS_STK_INVENTORY.* FROM WMS_STK_INVENTORY 
                     
                        WHERE 1=1 ";
            var sqlOrBuilder = new StringBuilder();
            var sqlAndBuilder = new StringBuilder();
            sqlAndBuilder = info.Id != null ?
                        info.Id > 0 ?
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVENTORY_ID", info.Id, DBOperationString._Equal,
                                "WMS_STK_INVENTORY")) :
            sqlAndBuilder : sqlAndBuilder;
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.InventoryNo) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVENTORY_NO", info.InventoryNo, DBOperationString._Equal,
                                "WMS_STK_INVENTORY"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.BatchNo) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BATCH_NO", info.BatchNo, DBOperationString._ContainIn,
                             "WMS_STK_INVENTORY_DTL"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.InventoryMode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVENTORY_MODE", info.InventoryMode, DBOperationString._Equal,
                                "WMS_STK_INVENTORY"));
            sqlAndBuilder = info.InventoryStatus != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVENTORY_STATUS", info.InventoryStatus, DBOperationString._Equal,
                                "WMS_STK_INVENTORY"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.InventoryType) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVENTORY_TYPE", info.InventoryType, DBOperationString._Equal,
                                "WMS_STK_INVENTORY"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.SupplierCode) ?
                       sqlAndBuilder :
                       sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("SUPPLIER_CODE", info.SupplierCode, DBOperationString._ContainIn,
                                "WMS_STK_INVENTORY_DTL"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.PalletInfo) ?
                       sqlAndBuilder :
                       sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("PALLET_CODE", info.PalletInfo, DBOperationString._ContainIn,
                                "WMS_STK_INVENTORY_DTL"));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");

            //物料
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MATERIEL_CODE", info.MaterielInfo, DBOperationString._ContainIn,
                                "WMS_STK_INVENTORY_DTL"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MATERIEL_NAME", info.MaterielInfo, DBOperationString._ContainIn,
                                "WMS_STK_INVENTORY_DTL"));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //库位
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.WarehouseInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("WAREHOUSE_CODE", info.WarehouseInfo, DBOperationString._ContainIn,
                                "BAS_BIN"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.WarehouseInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("WAREHOUSE_NAME", info.WarehouseInfo, DBOperationString._ContainIn,
                                "BAS_BIN"));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");
            IEnumerable<InventoryMainSearchResult> result;
            IEnumerable<InventoryMainSearchResult> totalResult;
            totalResult = new DataRepository<InventoryMainSearchResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<InventoryMainSearchResult>(_dataContext).QueryPage(sql,
                                                "CREATE_TIME", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<InventoryMainSearchResult>
            {
                RowData = result,
                PageConditionInfo = info,
                TotalCount = totalResult.Count(),
                TotalPageCount = (int)Math.Ceiling((double)totalResult.Count() / info.PageSize)
            });
        }

        /// <summary>
        /// 盘点明细单列表
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo<List<InventoryDetailSearchResult>> GetDetailInventoryList(InventoryDetailSearchCondition info)
        {

            if (info.Id == null)
            {
                return FailMessageStatus("参数错误", new List<InventoryDetailSearchResult>());
            }

            var sql = $@"SELECT CreateName=(SELECT A.USER_NAME FROM SYS_USER A WHERE A.USER_ID=WMS_STK_INVENTORY.CREATE_BY),
                        UpdateName=(SELECT B.USER_NAME FROM SYS_USER B WHERE B.USER_ID=WMS_STK_INVENTORY.UPDATE_BY),
                        WMS_STK_INVENTORY_DTL.MATERIEL_CODE,WMS_STK_INVENTORY_DTL.MATERIEL_NAME,
                        WMS_STK_INVENTORY_DTL.INVENTORY_DTL_ID,
                        MaterielTypeCode = BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE,MaterielTypeName = BAS_MATERIEL_TYPE.MATERIEL_TYPE_NAME,
                        BAS_SUPPLIER.SUPPLIER_NAME,BATCH_NO,WMS_STK_INVENTORY_DTL.QTY,WMS_STK_INVENTORY_DTL.ACTUAL_INVENTORY_QTY,
                        WMS_STK_INVENTORY_DTL.INVENTORY_RESULT_QTY,UnitName = BAS_UNIT.UNIT_NAME,WarehouseName = BAS_WAREHOUSE.WAREHOUSE_NAME,WarehouseCode = BAS_WAREHOUSE.WAREHOUSE_CODE,
                        BAS_BIN.AREA_NAME,
                        BAS_BIN.BIN_CODE,WMS_STK_INVENTORY_DTL.TRAY_CODE,WMS_STK_INVENTORY_DTL.PALLET_CODE,
                        InventoryDetailStatusName = {_iSQLNodeRepository.GetEnumIntCaseString<InventoryStatus>("INVENTORY_DTL_STATUS", "WMS_STK_INVENTORY_DTL")},
                        WMS_STK_INVENTORY_DTL.INVENTORY_DTL_STATUS,
                        CREATE_TIME=WMS_STK_INVENTORY.CREATE_TIME,UPDATE_TIME=WMS_STK_INVENTORY.UPDATE_TIME
                        FROM WMS_STK_INVENTORY 
                        JOIN WMS_STK_INVENTORY_DTL ON WMS_STK_INVENTORY.INVENTORY_ID=WMS_STK_INVENTORY_DTL.INVENTORY_ID
                        JOIN BAS_BIN ON BAS_BIN.BIN_CODE=WMS_STK_INVENTORY_DTL.BIN_CODE
                        JOIN BAS_WAREHOUSE ON BAS_BIN.WAREHOUSE_CODE=BAS_WAREHOUSE.WAREHOUSE_CODE
                        JOIN BAS_AREA ON BAS_AREA.AREA_CODE=BAS_BIN.AREA_CODE
                        JOIN BAS_MATERIEL ON BAS_MATERIEL.MATERIEL_CODE=WMS_STK_INVENTORY_DTL.MATERIEL_CODE
                        JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE=BAS_MATERIEL.MATERIEL_TYPE
                        LEFT JOIN BAS_SUPPLIER ON BAS_SUPPLIER.SUPPLIER_CODE=WMS_STK_INVENTORY_DTL.SUPPLIER_CODE
                        JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE=WMS_STK_INVENTORY_DTL.UNIT_CODE 
                        WHERE 1=1 AND WMS_STK_INVENTORY.INVENTORY_ID = {info.Id.Value}";

            if (info.IsGetDifference)
            {
                sql = sql + " AND WMS_STK_INVENTORY_DTL.INVENTORY_RESULT_QTY!=ACTUAL_INVENTORY_QTY";
            }
            var results = new DataRepository<InventoryDetailSearchResult>(_dataContext).Query(sql);
            return SuccessStatus(results.ToList());
        }


        /// <summary>
        /// 获取库存改变记录列表
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo<PageQueryResultInfo<BalanceChangeLogResult>> GetChangeLogList(BalanceChangeLogSearchCondition info)
        {
            var sqlAndBuilder = new StringBuilder();
            var sqlOrBuilder = new StringBuilder();
            var sql = $@"SELECT 
                        CreateName = (SELECT USER_NAME FROM SYS_USER A WHERE A.USER_ID=CREATE_BY),
                        UpdateName = (SELECT USER_NAME FROM SYS_USER B WHERE B.USER_ID=UPDATE_BY),
                        UsedFlagName = {_iSQLNodeRepository.GetEnumIntCaseString<UsedFlag>("USED_FLAG")}, 
                        {info.ItemSQL} FROM WMS_LOG_BALANCE_CHANGE_MSG WHERE 1=1 AND DEL_FLAG = 0 ";
            sqlAndBuilder = info.Id != null ?
                        info.Id > 0 ?
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_CHANGE_MSG_ID", info.Id, DBOperationString._Equal)) :
                        sqlAndBuilder : sqlAndBuilder;
            sqlAndBuilder = info.BalanceStatus != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_STATUS", info.BalanceStatus, DBOperationString._Equal));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.BatchNo) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BATCH_NO", info.BatchNo, DBOperationString._Equal));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.Ids) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BALANCE_CHANGE_MSG_ID", info.Ids, DBOperationString._In));
            sqlAndBuilder = info.InspectionStatus != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INSPECTION_STATUS", info.InspectionStatus, DBOperationString._Equal));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.RegionCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("REGION_CODE", info.RegionCode, DBOperationString._ContainIn));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");
            //物料
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MATERIEL_CODE", info.MaterielInfo, DBOperationString._ContainIn));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MATERIEL_NAME", info.MaterielInfo, DBOperationString._ContainIn));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //库位
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.BinInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("BIN_CODE", info.BinInfo, DBOperationString._ContainIn));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.BinInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("BIN_NAME", info.BinInfo, DBOperationString._ContainIn));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //物料类型
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielTypeInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MATERIEL_TYPE_CODE", info.MaterielTypeInfo, DBOperationString._ContainIn));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.MaterielTypeInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("MATERIEL_TYPE_NAME", info.MaterielTypeInfo, DBOperationString._ContainIn));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //托盘号
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.PalletInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("PALLET_CODE", info.PalletInfo, DBOperationString._ContainIn));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.PalletInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("PALLET_NAME", info.PalletInfo, DBOperationString._ContainIn));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

            //仓库
            sqlOrBuilder = new StringBuilder();
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.WarehouseInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("WAREHOUSE_CODE", info.WarehouseInfo, DBOperationString._ContainIn));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.WarehouseInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("WAREHOUSE_NAME", info.WarehouseInfo, DBOperationString._ContainIn));
            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");


            IEnumerable<BalanceChangeLogResult> result;
            IEnumerable<BalanceChangeLogResult> totalResult;
            totalResult = new DataRepository<BalanceChangeLogResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<BalanceChangeLogResult>(_dataContext).QueryPage(sql,
                                                "CREATE_TIME", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<BalanceChangeLogResult>
            {
                RowData = result,
                PageConditionInfo = info,
                TotalCount = totalResult.Count(),
                TotalPageCount = (int)Math.Ceiling((double)totalResult.Count() / info.PageSize)
            });
        }

        public BinResult GetEmptyBin(string regionCode)
        {

            string strSql = $@"
                                            SELECT
	                                            TOP 1 A.*, B.USER_CODE CreateCode,
	                                            B.USER_NAME CreateName,
	                                            C.USER_CODE UpdateCode,
	                                            C.USER_NAME UpdateName
                                            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.USED_FLAG = 1
                                            AND A.DEL_FLAG = 0
                                            AND A.BIN_CODE NOT IN (
	                                            SELECT
		                                            BIN_CODE
	                                            FROM
		                                            WMS_STK_BALANCE
	                                            WHERE
		                                            BALANCE_STATUS < 99
	                                            AND REGION_CODE = '{regionCode}'
                                            )
                                            AND A.REGION_CODE = '{regionCode}'
                                            ORDER BY
	                                            A.BIN_LAYER desc,
	                                            A.BIN_COLUMN desc,
	                                            A.BIN_ROW desc
                                        ";
            return new DataRepository<BinResult>(_dataContext).QueryFirst(strSql);
        }

        public bool VerifyBin(string RegionCode, string BinCode)
        {
            string strSql = $@"
                                            SELECT
	                                            TOP 1 A.*, B.USER_CODE CreateCode,
	                                            B.USER_NAME CreateName,
	                                            C.USER_CODE UpdateCode,
	                                            C.USER_NAME UpdateName
                                            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.USED_FLAG = 1
                                            AND A.DEL_FLAG = 0
                                            AND A.BIN_CODE NOT IN (
	                                            SELECT
		                                            BIN_CODE
	                                            FROM
		                                            WMS_STK_BALANCE
	                                            WHERE
		                                            BALANCE_STATUS < 99
	                                            AND REGION_CODE = @regionCode
                                            )
                                            AND A.REGION_CODE = @regionCode
                                            AND A.BIN_CODE=@BinCode
                                            ORDER BY
	                                            A.BIN_LAYER desc,
	                                            A.BIN_COLUMN desc,
	                                            A.BIN_ROW desc
                                        ";

            return new DataRepository<object>(_dataContext).ExecuteScalar<int>(strSql, new { RegionCode, BinCode }) > 0;

        }
    }
}