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

using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using Microsoft.Extensions.Configuration;
using NXWMS.IService.NXPDA.Balance;
using NXWMS.Model.AppModels.Condition.NXPDA.Balance;
using NXWMS.Model.AppModels.Result.NXPDA.Balance;
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.NXPDA.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;
        }
       
        public OperateResultInfo<BalanceResult> GetBalanceList(BalanceCondition info)
        {
            var sql = @"SELECT MaterielCode=MATERIEL_CODE,MaterielName=MATERIEL_NAME,
                    QTY,UnitCode=UNIT_CODE,BatchNo=BATCH_NO,BinCode=BIN_CODE FROM VW_WMS_STK_BALANCE_DTL where MATERIEL_CODE=@MaterielCode and BALANCE_STATUS=55 ";
            var results = new DataRepository<object>(_dataContext).Query<BalanceDTLResult>(sql,new { MaterielCode=info.MaterielCode });
            return SuccessStatus(new BalanceResult
            {
                Details = results,
                QTY = results.Sum(s => s.QTY),
                TypeQTY = results.Select(s => s.TypeCode).Distinct().Count(),
            });
        }

        public OperateResultInfo<InventoryPalletResult> GetInventoryPalletList(InventoryPalletCondition info) 
        {
            var sql = $@"SELECT WMS_STK_INVENTORY_DTL.INVENTORY_DTL_ID, VW_WMS_STK_TRAY_DTL.MATERIEL_BARCODE,WMS_STK_INVENTORY.INVENTORY_ID,MaterielCode=WMS_STK_INVENTORY_DTL.MATERIEL_CODE,MaterielName=WMS_STK_INVENTORY_DTL.MATERIEL_NAME,
                    StockQTY = WMS_STK_INVENTORY_DTL.QTY,ActualQTY = WMS_STK_INVENTORY_DTL.ACTUAL_INVENTORY_QTY,
                    TwoVessel = BAS_PACKAGE.PACKAGE_NAME
                    FROM dbo.WMS_STK_INVENTORY
                 
                    LEFT JOIN WMS_STK_INVENTORY_DTL ON WMS_STK_INVENTORY.INVENTORY_ID = WMS_STK_INVENTORY_DTL.INVENTORY_ID
                    LEFT JOIN BAS_PACKAGE ON BAS_PACKAGE.PACKAGE_CODE = WMS_STK_INVENTORY_DTL.PACKAGE_CODE
                    LEFT JOIN BAS_MATERIEL ON WMS_STK_INVENTORY_DTL.MATERIEL_CODE = BAS_MATERIEL.MATERIEL_CODE
                    LEFT JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL.MATERIEL_TYPE = BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE
                    LEFT JOIN 
                        VW_WMS_STK_TRAY_DTL ON VW_WMS_STK_TRAY_DTL.TRAY_CODE = WMS_STK_INVENTORY_DTL.TRAY_CODE 
                 
		            WHERE
		            WMS_STK_INVENTORY.INVENTORY_STATUS in  (11,88) AND 
		            WMS_STK_INVENTORY_DTL.INVENTORY_DTL_STATUS  in  (11,88) AND  
		            WMS_STK_INVENTORY_DTL.PALLET_CODE = '{info.PalletCode}'
		            AND EXISTS(SELECT * FROM WMS_STK_INVENTORY_DTL A WHERE A.INVENTORY_DTL_STATUS=11 AND A.INVENTORY_ID=WMS_STK_INVENTORY.INVENTORY_ID)";
            var results = new DataRepository<InventoryPalletDTLResult>(_dataContext).Query(sql);
            return SuccessStatus(new InventoryPalletResult
            {
                Details = results.ToList(),
                IsEffective = results.Any(),
            });
        }

        public OperateResultInfo<InventoryBarCodeResult> GetInventoryBarCodeInfo(InventoryBarCodeCondition info) 
        {
            var sql = $@"SELECT MaterielCode=WMS_STK_INVENTORY_DTL.MATERIEL_CODE,
                        MaterielName = WMS_STK_INVENTORY_DTL.MATERIEL_NAME,SpecsModel = BAS_MATERIEL.SPECS_MODEL,
                        BatchNo = WMS_STK_INVENTORY_DTL.BATCH_NO,ArrivalQTY = WMS_STK_INVENTORY_DTL.ACTUAL_INVENTORY_QTY,
                        ReceiptQTY = WMS_STK_INVENTORY_DTL.QTY,TwoVessel = BAS_PACKAGE.PACKAGE_NAME,
                        BinCode = WMS_STK_INVENTORY_DTL.BIN_CODE,BinName = WMS_STK_INVENTORY_DTL.BIN_NAME,
                        UnitCode = WMS_STK_INVENTORY_DTL.UNIT_CODE,UnitName = BAS_UNIT.UNIT_NAME,Remark = WMS_STK_INVENTORY_DTL.DESCRIBE,
                        InventoryDtlId = WMS_STK_INVENTORY_DTL.INVENTORY_DTL_ID
                        FROM dbo.WMS_STK_INVENTORY 
                        LEFT JOIN SYS_USER ON WMS_STK_INVENTORY.CREATE_BY = SYS_USER.USER_ID 
                        LEFT JOIN SYS_USER USER1 ON WMS_STK_INVENTORY.UPDATE_BY = SYS_USER.USER_ID 
                        LEFT JOIN WMS_STK_INVENTORY_DTL ON WMS_STK_INVENTORY.INVENTORY_ID = WMS_STK_INVENTORY_DTL.INVENTORY_ID 
                        LEFT JOIN BAS_PACKAGE ON BAS_PACKAGE.PACKAGE_CODE = WMS_STK_INVENTORY_DTL.PACKAGE_CODE	
                        LEFT JOIN BAS_MATERIEL ON WMS_STK_INVENTORY_DTL.MATERIEL_CODE = BAS_MATERIEL.MATERIEL_CODE
                        LEFT JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL.MATERIEL_TYPE = BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE	
                        LEFT JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE=WMS_STK_INVENTORY_DTL.UNIT_CODE
                        LEFT JOIN 
                        VW_WMS_STK_TRAY_DTL ON VW_WMS_STK_TRAY_DTL.TRAY_CODE=WMS_STK_INVENTORY_DTL.TRAY_CODE
                        AND VW_WMS_STK_TRAY_DTL.MATERIEL_BARCODE=WMS_STK_INVENTORY_DTL.MATERIEL_BARCODE
		                        WHERE 
		                        WMS_STK_INVENTORY.INVENTORY_STATUS=55 AND 
		                        WMS_STK_INVENTORY_DTL.INVENTORY_DTL_STATUS=55 AND  
		                        WMS_STK_INVENTORY_DTL.MATERIEL_BARCODE = '{info.MaterielBarCode}'
		                        AND EXISTS(SELECT * FROM WMS_STK_INVENTORY_DTL A WHERE A.INVENTORY_DTL_STATUS=88 AND A.INVENTORY_ID=WMS_STK_INVENTORY.INVENTORY_ID
		                        AND A.ACTUAL_INVENTORY_QTY!=A.QTY) ";
            var results = new DataRepository<InventoryBarCodeResult>(_dataContext).Query(sql).FirstOrDefault();
            return SuccessStatus(results);
        }


        public OperateResultInfo<DifferenceInventoryPalletResult> GetDifferenceInventoryPalletList(DifferenceInventoryPalletCondition info)
        {
            var sql = $@"SELECT MaterielCode=WMS_STK_INVENTORY_DTL.MATERIEL_CODE,MaterielName=WMS_STK_INVENTORY_DTL.MATERIEL_NAME,
                    StockQTY = WMS_STK_INVENTORY_DTL.QTY,ActualQTY = WMS_STK_INVENTORY_DTL.ACTUAL_INVENTORY_QTY,
                    TwoVessel = BAS_PACKAGE.PACKAGE_NAME
                    FROM dbo.WMS_STK_INVENTORY
                    LEFT JOIN SYS_USER ON WMS_STK_INVENTORY.CREATE_BY = SYS_USER.USER_ID
                    LEFT JOIN SYS_USER USER1 ON WMS_STK_INVENTORY.UPDATE_BY = SYS_USER.USER_ID
                    LEFT JOIN WMS_STK_INVENTORY_DTL ON WMS_STK_INVENTORY.INVENTORY_ID = WMS_STK_INVENTORY_DTL.INVENTORY_ID
                    LEFT JOIN BAS_PACKAGE ON BAS_PACKAGE.PACKAGE_CODE = WMS_STK_INVENTORY_DTL.PACKAGE_CODE
                    LEFT JOIN BAS_MATERIEL ON WMS_STK_INVENTORY_DTL.MATERIEL_CODE = BAS_MATERIEL.MATERIEL_CODE
                    LEFT JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL.MATERIEL_TYPE = BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE
                    LEFT JOIN 
                        VW_WMS_STK_TRAY_DTL ON VW_WMS_STK_TRAY_DTL.TRAY_CODE = WMS_STK_INVENTORY_DTL.TRAY_CODE 
                    AND VW_WMS_STK_TRAY_DTL.MATERIEL_BARCODE = WMS_STK_INVENTORY_DTL.MATERIEL_BARCODE
		            WHERE 
		            WMS_STK_INVENTORY.INVENTORY_STATUS=88 AND 
		            WMS_STK_INVENTORY_DTL.INVENTORY_DTL_STATUS=88 AND  
		            WMS_STK_INVENTORY_DTL.PALLET_CODE = '{info.PallectCode}'
		            AND EXISTS(SELECT * FROM WMS_STK_INVENTORY_DTL A WHERE A.INVENTORY_DTL_STATUS=88 AND A.INVENTORY_ID=WMS_STK_INVENTORY.INVENTORY_ID)";
                    //明细存在第一次盘点完成状态,再次盘点就是差异盘点
            var results = new DataRepository<DifferenceInventoryPalletDTLResult>(_dataContext).Query(sql);
            return SuccessStatus(new DifferenceInventoryPalletResult
            {
                Details = results.ToList(),
                IsEffective = results.Any(),
            });
        }

        public OperateResultInfo<DifferenceInventoryBarCodeResult> GetDifferenceInventoryBarCodeInfo(DifferenceInventoryBarCodeCondition info)
        {
            var sql = $@"SELECT MaterielCode=WMS_STK_INVENTORY_DTL.MATERIEL_CODE,
                        MaterielName = WMS_STK_INVENTORY_DTL.MATERIEL_NAME,SpecsModel = BAS_MATERIEL.SPECS_MODEL,
                        BatchNo = WMS_STK_INVENTORY_DTL.BATCH_NO,ArrivalQTY = WMS_STK_INVENTORY_DTL.ACTUAL_INVENTORY_QTY,
                        ReceiptQTY = WMS_STK_INVENTORY_DTL.QTY,TwoVessel = BAS_PACKAGE.PACKAGE_NAME,
                        BinCode = WMS_STK_INVENTORY_DTL.BIN_CODE,BinName = WMS_STK_INVENTORY_DTL.BIN_NAME,
                        UnitCode = WMS_STK_INVENTORY_DTL.UNIT_CODE,UnitName = BAS_UNIT.UNIT_NAME,Remark = WMS_STK_INVENTORY_DTL.DESCRIBE,
                        InventoryDtlId = WMS_STK_INVENTORY_DTL.INVENTORY_DTL_ID
                        FROM dbo.WMS_STK_INVENTORY 
                        LEFT JOIN SYS_USER ON WMS_STK_INVENTORY.CREATE_BY = SYS_USER.USER_ID 
                        LEFT JOIN SYS_USER USER1 ON WMS_STK_INVENTORY.UPDATE_BY = SYS_USER.USER_ID 
                        LEFT JOIN WMS_STK_INVENTORY_DTL ON WMS_STK_INVENTORY.INVENTORY_ID = WMS_STK_INVENTORY_DTL.INVENTORY_ID 
                        LEFT JOIN BAS_PACKAGE ON BAS_PACKAGE.PACKAGE_CODE = WMS_STK_INVENTORY_DTL.PACKAGE_CODE	
                        LEFT JOIN BAS_MATERIEL ON WMS_STK_INVENTORY_DTL.MATERIEL_CODE = BAS_MATERIEL.MATERIEL_CODE
                        LEFT JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL.MATERIEL_TYPE = BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE	
                        LEFT JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE=WMS_STK_INVENTORY_DTL.UNIT_CODE
                        LEFT JOIN 
                        VW_WMS_STK_TRAY_DTL ON VW_WMS_STK_TRAY_DTL.TRAY_CODE=WMS_STK_INVENTORY_DTL.TRAY_CODE
                        AND VW_WMS_STK_TRAY_DTL.MATERIEL_BARCODE=WMS_STK_INVENTORY_DTL.MATERIEL_BARCODE
		                        WHERE 
		                        WMS_STK_INVENTORY.INVENTORY_STATUS=88 AND 
		                        WMS_STK_INVENTORY_DTL.INVENTORY_DTL_STATUS=88 AND  
		                        WMS_STK_INVENTORY_DTL.MATERIEL_BARCODE = '{info.MaterielBarCode}'
		                        AND EXISTS(SELECT * FROM WMS_STK_INVENTORY_DTL A WHERE A.INVENTORY_DTL_STATUS=88 AND A.INVENTORY_ID=WMS_STK_INVENTORY.INVENTORY_ID
		                        AND A.ACTUAL_INVENTORY_QTY!=A.QTY) ";
                    //明细存在第一次盘点完成状态,并且实际盘点数量不等于库存数量的.  88状态  在第一次盘点完成状态下进行差异盘点。
            var results = new DataRepository<DifferenceInventoryBarCodeResult>(_dataContext).Query(sql).FirstOrDefault();
            return SuccessStatus(results);
        }
    }
}