using DapperORMCore.Context.DataContext; using DapperORMCore.Model.CoreModel; using DapperORMCore.Repository.IRepositorys; using DapperORMCore.String.Consts; using Microsoft.Extensions.Configuration; using NXWMS.DataAccess.Entity; using NXWMS.IService.NXPDA.InStock; using NXWMS.IService.NXWMS.Inspection; using NXWMS.Model.AppModels.Condition.Balance; using NXWMS.Model.AppModels.Condition.Base; using NXWMS.Model.AppModels.Condition.NXPDA.InStock; using NXWMS.Model.AppModels.Result.Balance; using NXWMS.Model.AppModels.Result.Base; using NXWMS.Model.AppModels.Result.Inspection; using NXWMS.Model.AppModels.Result.NXPDA.InStock; using NXWMS.Model.Common; using NXWMS.Service; using NXWMS.String.Enums; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using WestDistance.DapperORM.Repository.Repositorys; namespace NXWMS.Service.NXPDA.InStock { /// /// 入库查询接口 /// [AutoInject(typeof(IInStockSearchService), InjectType.Scope)] public class InStockSearchService : ServiceBase, IInStockSearchService { /// /// 系统操作仓储中转 /// private IDataRepositoryContext _dataContext; /// /// SQL节点仓储 /// private ISQLNodeRepository _iSQLNodeRepository; /// /// 配置 /// private IConfiguration _configuration; private IWmsQaInspectionService _wmsQaInspectionService; public InStockSearchService(IDataRepositoryContext dataRepositoryContext, IWmsQaInspectionService wmsQaInspectionService, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository) { this._dataContext = dataRepositoryContext; this._configuration = configuration; this._iSQLNodeRepository = iSQLNodeRepository; this._wmsQaInspectionService = wmsQaInspectionService; } public OperateResultInfo> GetReceiptList(InReceiptCondition info) { //if (string.IsNullOrWhiteSpace(info.No) && info.Date == null && string.IsNullOrWhiteSpace(info.SupplierName)) //{ // return FailMessageStatus("参数错误", new List()); //} var sqlAndBuilder = new StringBuilder(); var sqlOrBuilder = new StringBuilder(); //var sql = $@"SELECT DISTINCT NO=WMS_IN_RECEIPT.RECEIPT_NO,DATE=WMS_IN_RECEIPT.RECEIPT_TIME,SupplierName=WMS_IN_RECEIPT_DTL.SUPPLIER_NAME, // WMS_IN_RECEIPT.RECEIPT_STATUS,WMS_IN_RECEIPT.RECEIPT_TYPE, // SYS_USER.USER_NAME,WMS_IN_RECEIPT.UPDATE_TIME, // Type = {_iSQLNodeRepository.GetEnumIntCaseString("RECEIPT_TYPE", "WMS_IN_RECEIPT")}, // Status = {_iSQLNodeRepository.GetEnumIntCaseString("RECEIPT_STATUS", "WMS_IN_RECEIPT")}, // UserName = SYS_USER.USER_NAME, // WMS_IN_RECEIPT.RECEIPTER from WMS_IN_RECEIPT // JOIN WMS_IN_RECEIPT_DTL ON WMS_IN_RECEIPT.RECEIPT_ID=WMS_IN_RECEIPT_DTL.RECEIPT_ID // LEFT JOIN SYS_USER ON SYS_USER.USER_CODE=WMS_IN_RECEIPT.RECEIPTER // WHERE WMS_IN_RECEIPT.RECEIPT_STATUS NOT IN (111,0)"; var sql = $@" SELECT DISTINCT WMS_IN_RECEIPT.RECEIPT_NO as NO, WMS_IN_RECEIPT.RECEIPT_TIME AS Date, WMS_IN_RECEIPT.RECEIPT_STATUS ,WMS_IN_RECEIPT.RECEIPT_TYPE, SYS_USER.USER_NAME,WMS_IN_RECEIPT.UPDATE_TIME, SYS_USER.USER_NAME as UserName, WMS_IN_RECEIPT.RECEIPTER, c.DICTIONARY_ITEM_NAME AS Type,d.DICTIONARY_ITEM_NAME as Status from WMS_IN_RECEIPT JOIN WMS_IN_RECEIPT_DTL ON WMS_IN_RECEIPT.RECEIPT_ID = WMS_IN_RECEIPT_DTL.RECEIPT_ID LEFT JOIN SYS_USER ON SYS_USER.USER_CODE = WMS_IN_RECEIPT.RECEIPTER left join (select * from BAS_DICTIONARY where DICTIONARY_CODE='ReceiptTypeDesc') as c on WMS_IN_RECEIPT.RECEIPT_TYPE=c.DICTIONARY_ITEM_CODE left join (select * from BAS_DICTIONARY where DICTIONARY_CODE='ReceiptOrderStatusDesc') as d on WMS_IN_RECEIPT.RECEIPT_STATUS=d.DICTIONARY_ITEM_CODE Where WMS_IN_RECEIPT.RECEIPT_STATUS NOT IN(111) and RECEIPT_DTL_QTY<>RECEIPT_TRAY_QTY "; sqlAndBuilder = string.IsNullOrWhiteSpace(info.No) ? sqlAndBuilder : sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("RECEIPT_NO", info.No, DBOperationString._ContainIn, "WMS_IN_RECEIPT")); if (info.Date != null) { sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("RECEIPT_TIME", info.Date.Value.ToString("yyyy-MM-dd"), DBOperationString._LargeEqual, "WMS_IN_RECEIPT")); sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("RECEIPT_TIME", info.Date.Value.AddDays(1).ToString("yyyy-MM-dd"), DBOperationString._SmallEqual, "WMS_IN_RECEIPT")); } sqlAndBuilder = string.IsNullOrWhiteSpace(info.SupplierName) ? sqlAndBuilder : sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("SUPPLIER_NAME", info.SupplierName, DBOperationString._ContainIn, "WMS_IN_RECEIPT_DTL")); sql = sql + (sqlAndBuilder.Length > 0 ? sqlAndBuilder.ToString() : ""); var results = new DataRepository(_dataContext).Query(sql); return SuccessStatus(results.ToList()); } public OperateResultInfo GetReceiptDTLList(InReceiptCondition info) { if (string.IsNullOrWhiteSpace(info.No) && info.Date == null && string.IsNullOrWhiteSpace(info.SupplierName)) { return FailMessageStatus("参数错误", new InReceiptDTLMainResult()); } var sqlAndBuilder = new StringBuilder(); var sqlOrBuilder = new StringBuilder(); var sql = $@"SELECT DISTINCT BatchNo=WMS_IN_RECEIPT_DTL.BATCH_NO,QTY=WMS_IN_RECEIPT_DTL.ARRIVAL_QTY,MaterielCode=WMS_IN_RECEIPT_DTL.MATERIEL_CODE, MaterielName=WMS_IN_RECEIPT_DTL.MATERIEL_NAME,UnitName=BAS_UNIT.UNIT_NAME,OperateTime=WMS_IN_RECEIPT_DTL.UPDATE_TIME, WMS_IN_RECEIPT.RECEIPT_NO, WMS_IN_RECEIPT.RECEIPTER from WMS_IN_RECEIPT JOIN WMS_IN_RECEIPT_DTL ON WMS_IN_RECEIPT.RECEIPT_ID=WMS_IN_RECEIPT_DTL.RECEIPT_ID LEFT JOIN SYS_USER ON SYS_USER.USER_CODE=WMS_IN_RECEIPT.RECEIPTER JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE=WMS_IN_RECEIPT_DTL.UNIT_CODE WHERE WMS_IN_RECEIPT.RECEIPT_STATUS NOT IN (111,0) AND WMS_IN_RECEIPT.RECEIPT_NO='{info.No}'"; var results = new DataRepository(_dataContext).Query(sql); return SuccessStatus(new InReceiptDTLMainResult { inReceiptDTLResults = results.ToList(), QTY = results.Sum(s => s.QTY), TypeQTY = results.Count() }); } public OperateResultInfo> GetReceiptPalletList(InReceiptPalletCondition info) { if (string.IsNullOrWhiteSpace(info.PalletCode)) { return FailMessageStatus("参数错误", new List()); } var palletResult = new DataRepository(_dataContext).Query("PALLET_CODE", info.PalletCode) .Where(s => s.USED_FLAG == 1 && s.DEL_FLAG == 0).Any(); if (!palletResult) { return FailMessageStatus("托盘编码不存在,请重新录入!", new List()); } var sql = $@"SELECT MaterielCode = WMS_IN_RECEIPT_RECORD.MATERIEL_CODE, MaterielName=WMS_IN_RECEIPT_RECORD.MATERIEL_NAME,QTY=WMS_IN_RECEIPT_RECORD.RECEIPT_QTY, TwoVessel = BAS_PACKAGE.PACKAGE_NAME from WMS_IN_RECEIPT_RECORD JOIN WMS_IN_RECEIPT ON WMS_IN_RECEIPT_RECORD.RECEIPT_NO = WMS_IN_RECEIPT.RECEIPT_NO JOIN BAS_PACKAGE ON WMS_IN_RECEIPT_RECORD.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE WHERE WMS_IN_RECEIPT.RECEIPT_STATUS = 55 AND WMS_IN_RECEIPT_RECORD.PALLET_CODE = '{info.PalletCode}'"; var results = new DataRepository(_dataContext).Query(sql); return SuccessStatus(results.ToList()); } public OperateResultInfo GetReceiptBarCodeInfo(InArrivalBarCodeCondition info) { var sql = $@"SELECT MaterielCode=WMS_IN_RECEIPT_DTL.MATERIEL_CODE, RECEIPT_NO=WMS_IN_RECEIPT.RECEIPT_NO, MaterielName = WMS_IN_RECEIPT_DTL.MATERIEL_NAME,SpecsModel = BAS_MATERIEL.SPECS_MODEL, BatchNo = WMS_IN_RECEIPT_DTL.BATCH_NO,ArrivalQTY = WMS_IN_RECEIPT_DTL.ARRIVAL_QTY, ReceiptTrayQty=WMS_IN_RECEIPT_DTL.RECEIPT_TRAY_QTY, ReceiptQTY = WMS_IN_RECEIPT_DTL.RECEIPT_DTL_QTY,TwoVessel = BAS_PACKAGE.PACKAGE_NAME, BinCode = BAS_BIN.BIN_CODE,BinName = BAS_BIN.BIN_NAME, UnitCode = BAS_UNIT.UNIT_CODE,UnitName = BAS_UNIT.UNIT_NAME,Remark = WMS_IN_RECEIPT_DTL.DESCRIBE, ReceiptDtlId = WMS_IN_RECEIPT_DTL.RECEIPT_DTL_ID FROM WMS_IN_RECEIPT JOIN WMS_IN_RECEIPT_DTL ON WMS_IN_RECEIPT.RECEIPT_ID = WMS_IN_RECEIPT_DTL.RECEIPT_ID JOIN BAS_MATERIEL ON WMS_IN_RECEIPT_DTL.MATERIEL_CODE = BAS_MATERIEL.MATERIEL_CODE LEFT JOIN BAS_PACKAGE ON WMS_IN_RECEIPT_DTL.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE LEFT JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE = WMS_IN_RECEIPT_DTL.UNIT_CODE LEFT JOIN WMS_IN_RECEIPT_RECORD ON WMS_IN_RECEIPT_RECORD.ARRIVAL_NO = WMS_IN_RECEIPT.ARRIVAL_NO AND WMS_IN_RECEIPT_RECORD.MATERIEL_BARCODE = WMS_IN_RECEIPT_DTL.MATERIEL_BARCODE LEFT JOIN BAS_BIN ON WMS_IN_RECEIPT_RECORD.BIN_CODE = BAS_BIN.BIN_CODE where WMS_IN_RECEIPT_DTL.MATERIEL_CODE='{info.MaterielCode}' and WMS_IN_RECEIPT_DTL.RECEIPT_DTL_ID={info.ReceiptDtlId}"; if (!string.IsNullOrWhiteSpace(info.Statuss)) { sql = sql + " AND WMS_IN_RECEIPT.RECEIPT_STATUS in(" + info.Statuss + ")"; } if (!string.IsNullOrWhiteSpace(info.DTLStatuss)) { sql = sql + " AND WMS_IN_RECEIPT_DTL.RECEIPT_DTL_STATUS in(" + info.Statuss + ")"; } var results = new DataRepository(_dataContext).Query(sql).FirstOrDefault(); return SuccessStatus(results); } public OperateResultInfo GetArtificialPutAwayList(InArtificialPutAwayCondition info) { if (string.IsNullOrWhiteSpace(info.PalletCode) || string.IsNullOrWhiteSpace(info.BinCode)) { return FailMessageStatus("参数错误", new InArtificialPutAwayDTLResult()); } var sql = $@"SELECT MaterielCode = WMS_IN_RECEIPT_RECORD.MATERIEL_CODE, MaterielName=WMS_IN_RECEIPT_RECORD.MATERIEL_NAME,QTY=WMS_IN_RECEIPT_RECORD.RECEIPT_QTY, TwoVessel = BAS_PACKAGE.PACKAGE_NAME from WMS_IN_RECEIPT_RECORD JOIN WMS_IN_RECEIPT ON WMS_IN_RECEIPT_RECORD.RECEIPT_NO = WMS_IN_RECEIPT.RECEIPT_NO JOIN BAS_PACKAGE ON WMS_IN_RECEIPT_RECORD.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE WHERE WMS_IN_RECEIPT.RECEIPT_STATUS = 99 AND WMS_IN_RECEIPT_RECORD.PALLET_CODE = '{info.PalletCode}' AND WMS_IN_RECEIPT_RECORD.BIN_CODE = '{info.BinCode}'"; var results = new DataRepository(_dataContext).Query(sql); sql = $@"SELECT WMS_IN_RECEIPT_RECORD.* from WMS_IN_RECEIPT_RECORD JOIN WMS_IN_RECEIPT ON WMS_IN_RECEIPT_RECORD.RECEIPT_NO = WMS_IN_RECEIPT.RECEIPT_NO JOIN BAS_PACKAGE ON WMS_IN_RECEIPT_RECORD.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE WHERE WMS_IN_RECEIPT_RECORD.PALLET_CODE = '{info.PalletCode}' AND WMS_IN_RECEIPT_RECORD.BIN_CODE = '{info.BinCode}'"; var resultsTemp = new DataRepository(_dataContext).Query(sql); return SuccessStatus(new InArtificialPutAwayDTLResult { InArtificialPutAwayList = results.ToList(), IsEffective = resultsTemp.Any() }); } public OperateResultInfo GetInspectionPalletList(InInspectionPalletCondition info) { if (string.IsNullOrWhiteSpace(info.PalletCode)) { return FailMessageStatus("参数错误", new InInspectionPalletMainResult()); } var sql = $@"SELECT MaterielCode = WMS_QA_INSPECTION_DTL.MATERIEL_CODE, MaterielName=WMS_QA_INSPECTION_DTL.MATERIEL_NAME,QTY=WMS_IN_RECEIPT_RECORD.RECEIPT_QTY, TwoVessel = BAS_PACKAGE.PACKAGE_NAME, Result = {_iSQLNodeRepository.GetEnumIntCaseString("INSPECTION_RESULT", "WMS_QA_INSPECTION_DTL")} from WMS_QA_INSPECTION_DTL JOIN WMS_QA_INSPECTION ON WMS_QA_INSPECTION_DTL.INSPECTION_ID = WMS_QA_INSPECTION_DTL.INSPECTION_ID JOIN BAS_PACKAGE ON WMS_QA_INSPECTION_DTL.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE JOIN WMS_IN_RECEIPT_RECORD ON WMS_IN_RECEIPT_RECORD.MATERIEL_BARCODE =WMS_QA_INSPECTION_DTL.MATERIEL_BARCODE WHERE WMS_QA_INSPECTION.INSPECTION_STATUS = 11 AND WMS_QA_INSPECTION_DTL.INSPECTION_DTL_STATUS=11 AND WMS_IN_RECEIPT_RECORD.PALLET_CODE = '{info.PalletCode}'"; var results = new DataRepository(_dataContext).Query(sql); return SuccessStatus(new InInspectionPalletMainResult { Details = results, IsEffective = new DataRepository(_dataContext).Query("PALLET_CODE", info.PalletCode). Where(s => s.USED_FLAG == 1 && s.DEL_FLAG == 0).Any() && new DataRepository(_dataContext).Query("PALLET_CODE", info.PalletCode). Where(s => s.RECEIPT_RECORD_STATUS == 0 && s.INSPECTION_RESULT == "WAIT").Any() }); } /// /// /// /// /// public OperateResultInfo GetInspectionBarCodeInfo(InInspectionBarCodeCondition info) { if (string.IsNullOrWhiteSpace(info.BarCode)) { return FailMessageStatus("参数错误", new InInspectionBarCodeResult()); } var sql = $@" select * from ( SELECT g.RECEIPT_DTL_QTY, A.INSPECTION_DTL_ID, A.NEWID, A.INSPECTION_ID, D.INSPECTION_NO, F.MATERIEL_TYPE_CODE, F.MATERIEL_TYPE_NAME, A.MATERIEL_CODE, A.MATERIEL_NAME, A.MATERIEL_BARCODE, A.MATERIEL_SPEC, A.UNIT_CODE, A.BATCH_NO, A.PACKAGE_CODE, A.SUPPLIER_CODE, A.SUPPLIER_NAME, A.PRODUCT_DATE, A.EXP_DATE, A.REGION_CODE, A.REGION_NAME, A.BIN_CODE, A.BIN_NAME, A.BILL_NO, A.BILL_DTL_ID, A.TRAY_CODE, A.PALLET_CODE, A.TRAY_DTL_ID, A.INSPECTION_RESULT, CASE A.INSPECTION_RESULT WHEN 'Wait' THEN '未质检' WHEN 'OK' THEN '质检合格' WHEN 'NG' THEN '质检不合格' ELSE '未知' END AS INSPECTION_RESULT_NAME, A.INSPECTION_DTL_STATUS, CASE A.INSPECTION_DTL_STATUS WHEN 0 THEN '初始创建' WHEN 11 THEN '质检审核' WHEN 55 THEN '质检中' WHEN 77 THEN '结果确认完成' WHEN 99 THEN '质检完成' WHEN 100 THEN '强制完成' WHEN 111 THEN '已删除' ELSE '未知状态' END AS INSPECTION_DTL_STATUS_NAME, A.ITEM_STATUS, CASE A.ITEM_STATUS WHEN 1 THEN '正常品' WHEN 2 THEN '临期品' WHEN 3 THEN '过期品' ELSE '未知' END AS ITEM_STATUS_NAME, A.DESCRIBE, A.CREATE_BY, B.USER_NAME AS CREATE_NAME, A.CREATE_TIME, A.UPDATE_BY, C.USER_NAME AS UPDATE_NAME, A.UPDATE_TIME, A.DATA_VERSION, A.REMARKS1, A.REMARKS2, A.REMARKS3, A.REMARKS4, A.REMARKS5 FROM dbo.WMS_QA_INSPECTION_DTL AS A LEFT OUTER JOIN dbo.SYS_USER AS B ON A.CREATE_BY = B.USER_ID LEFT OUTER JOIN dbo.SYS_USER AS C ON A.UPDATE_BY = C.USER_ID LEFT OUTER JOIN dbo.WMS_QA_INSPECTION AS D ON A.INSPECTION_ID = D.INSPECTION_ID LEFT OUTER JOIN dbo.BAS_MATERIEL AS E ON A.MATERIEL_CODE = E.MATERIEL_CODE LEFT OUTER JOIN dbo.BAS_MATERIEL_TYPE AS F ON E.MATERIEL_TYPE = F.MATERIEL_TYPE_CODE LEFT JOIN VW_WMS_IN_RECEIPT_DTL AS G ON A.BILL_NO=G.RECEIPT_NO AND BILL_DTL_ID=G.RECEIPT_DTL_ID) as tbl WHERE INSPECTION_DTL_STATUS = 0 AND INSPECTION_RESULT = 'Wait' and INSPECTION_NO='{info.InspectionCode}' and MATERIEL_BARCODE='{info.BarCode}'"; var results = new DataRepository(_dataContext).Query(sql).FirstOrDefault(); return SuccessStatus(results); } /// /// 按单号查询可以录入的单据 /// /// /// public OperateResultInfo CheckInspection(string InspectionCode) { var page = this._wmsQaInspectionService.GetWmsQaInspectionListForPage(new Model.AppModels.Condition.Inspection.WmsQaInspectionSearchMd() { InspectionNoMsg = InspectionCode, PageNum = 1, EveryPageQty = 1, }); if (page.Data != null && page.Data.Count > 0) { return SuccessStatus(_wmsQaInspectionService.GetWmsQaInspectionDtlListForID(new WmsQaInspectionResult() { INSPECTION_ID = page.Data[0].INSPECTION_ID }).Data); ; } return SuccessStatus(page.Data != null && page.Data.Count() > 0 ? page.Data.FirstOrDefault() : null); //var strSQL = " select * from WMS_QA_INSPECTION where INSPECTION_NO=@InspectionCode and INSPECTION_STATUS=0"; // var result = new DataRepository(_dataContext).ExecuteScalar(strSQL, new { InspectionCode }); // return SuccessStatus(result); } } }