Wms_WebApi/NXWMS.WebApi/NXWMS.Service/NXPDA/OutStock/OutStockSearchService.cs

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.NXPDA.OutStock;
using NXWMS.IService.NXWMS.OutStock;
using NXWMS.Model.AppModels.Condition.Balance;
using NXWMS.Model.AppModels.Condition.Base;
using NXWMS.Model.AppModels.Condition.NXPDA.InStock;
using NXWMS.Model.AppModels.Condition.NXPDA.OutStock;
using NXWMS.Model.AppModels.Result.Balance;
using NXWMS.Model.AppModels.Result.Base;
using NXWMS.Model.AppModels.Result.NXPDA.InStock;
using NXWMS.Model.AppModels.Result.NXPDA.OutStock;
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.OutStock
{
    /// <summary>
    /// 入库查询接口
    /// </summary>
    [AutoInject(typeof(IOutStockSearchService), InjectType.Scope)]
    public class OutStockSearchService : ServiceBase, IOutStockSearchService
    {
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

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

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


        private IWmsOutInvoiceService _wmsOutInvoiceService;


        public OutStockSearchService(IDataRepositoryContext dataRepositoryContext, IWmsOutInvoiceService wmsOutInvoiceService, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository)
        {
            this._dataContext = dataRepositoryContext;
            this._configuration = configuration;
            this._iSQLNodeRepository = iSQLNodeRepository;
            this._wmsOutInvoiceService = wmsOutInvoiceService;
        }
         
        public OperateResultInfo<InArrivalNoticePalletMainResult> GetArrivalNoticePalletList(InArrivalNoticePalletCondition info) 
        {

            if (string.IsNullOrWhiteSpace(info.PalletCode))
            {
                return FailMessageStatus("参数错误", new InArrivalNoticePalletMainResult());
            }

            var results=  _wmsOutInvoiceService.QueryInvoiceSortData(new Model.AppModels.Result.OutStock.WmsOutPutDownResult()
            {
                PALLET_CODE = info.PalletCode

            }).Data.ConvertAll((m) => {
                var result = new InArrivalNoticePalletResult()
                {
                    MaterielCode = m.MATERIEL_CODE,
                    QTY = m.TRAY_QTY,
                    MaterielName = m.MATERIEL_NAME,

                };

                return result;

            });





        

            //var sql = $@"SELECT MaterielCode = WMS_OUT_INVOICE_RECORD.MATERIEL_CODE,
            //        MaterielName=WMS_OUT_INVOICE_RECORD.MATERIEL_NAME,QTY=WMS_IN_RECEIPT_RECORD.RECEIPT_QTY,
            //        TwoVessel = BAS_PACKAGE.PACKAGE_NAME 
            //        from WMS_OUT_INVOICE_RECORD
            //        JOIN WMS_OUT_INVOICE ON WMS_OUT_INVOICE_RECORD.INVOICE_ID = WMS_OUT_INVOICE.INVOICE_ID
            //        JOIN BAS_PACKAGE ON WMS_OUT_INVOICE_RECORD.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE
	           //     JOIN WMS_IN_RECEIPT_RECORD ON WMS_IN_RECEIPT_RECORD.MATERIEL_BARCODE =WMS_OUT_INVOICE_RECORD.MATERIEL_BARCODE 
            //        WHERE WMS_OUT_INVOICE.INVOICE_STATUS = 11 
            //        AND WMS_OUT_INVOICE_RECORD.INVOICE_RECORD_STATUS=11 
            //            AND WMS_OUT_INVOICE_RECORD.PALLET_CODE = '{info.PalletCode}'";
            //var results = new DataRepository<InArrivalNoticePalletResult>(_dataContext).Query(sql);
            return SuccessStatus(new InArrivalNoticePalletMainResult
            {
                Details = results,
                //IsEffective = new DataRepository<BAS_PALLET>(_dataContext).Query("PALLET_CODE", info.PalletCode).
                //                Where(s => s.USED_FLAG == 1 && s.DEL_FLAG == 0).Any() &&
                //            new DataRepository<WMS_OUT_INVOICE_RECORD>(_dataContext).Query("PALLET_CODE", info.PalletCode).
                //                Where(s => s.INVOICE_RECORD_STATUS == 1).Any()  //已确认状态下
            });
        }

        public OperateResultInfo<InArrivalNoticeBarCodeResult> GetArrivalNoticeBarCodeInfo(InArrivalNoticeBarCodeCondition info) 
        {



            if (string.IsNullOrWhiteSpace(info.BarCode))
            {
                return FailMessageStatus("参数错误", new InArrivalNoticeBarCodeResult());
            }
            var sql = $@"SELECT MaterielCode=WMS_OUT_INVOICE_DTL.MATERIEL_CODE,
                    MaterielName = WMS_OUT_INVOICE_DTL.MATERIEL_NAME,SpecsModel = BAS_MATERIEL.SPECS_MODEL,
                    BatchNo = WMS_OUT_INVOICE_DTL.BATCH_NO,ArrivalQTY = WMS_IN_RECEIPT_DTL.ARRIVAL_QTY,
                    ReceiptQTY = WMS_IN_RECEIPT_DTL.RECEIPT_DTL_QTY,TwoVessel = BAS_PACKAGE.PACKAGE_NAME,
                    BinCode = WMS_IN_RECEIPT_RECORD.BIN_CODE,BinName = WMS_IN_RECEIPT_RECORD.BIN_NAME,
                    UnitCode = WMS_IN_RECEIPT_RECORD.UNIT_CODE,UnitName = BAS_UNIT.UNIT_NAME,Remark = WMS_OUT_INVOICE_DTL.DESCRIBE,
                    InvoiceDTLId = WMS_OUT_INVOICE_DTL.INVOICE_DTL_ID
			        from WMS_OUT_INVOICE_DTL
			            JOIN WMS_OUT_INVOICE ON WMS_OUT_INVOICE_DTL.INVOICE_ID = WMS_OUT_INVOICE_DTL.INVOICE_ID
			            JOIN BAS_PACKAGE ON WMS_OUT_INVOICE_DTL.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE
			            JOIN BAS_MATERIEL ON BAS_MATERIEL.MATERIEL_CODE=WMS_OUT_INVOICE_DTL.MATERIEL_CODE
			            JOIN WMS_IN_RECEIPT_RECORD ON WMS_IN_RECEIPT_RECORD.MATERIEL_BARCODE =WMS_OUT_INVOICE_DTL.MATERIEL_BARCODE 
			            JOIN WMS_IN_RECEIPT_DTL ON WMS_IN_RECEIPT_DTL.MATERIEL_BARCODE =WMS_OUT_INVOICE_DTL.MATERIEL_BARCODE
			            LEFT JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE = WMS_OUT_INVOICE_DTL.UNIT_CODE 
			            WHERE WMS_OUT_INVOICE.INVOICE_STATUS = 11 
			            AND WMS_OUT_INVOICE_DTL.INVOICE_DTL_STATUS=11 
                    WHERE WMS_OUT_INVOICE_DTL.MATERIEL_BARCODE='{info.BarCode}'";
            if (!string.IsNullOrWhiteSpace(info.Statuss))
            {
                sql = sql + " AND WMS_OUT_INVOICE.INVOICE_STATUS in(" + info.Statuss + ")";
            }
            if (!string.IsNullOrWhiteSpace(info.DTLStatuss))
            {
                sql = sql + " AND WMS_OUT_INVOICE_DTL.INVOICE_DTL_STATUS in(" + info.Statuss + ")";
            }
            var results = new DataRepository<InArrivalNoticeBarCodeResult>(_dataContext).Query(sql).FirstOrDefault();
            return SuccessStatus(results);
        }

        public OperateResultInfo<InArtificialPutDownPalletMainResult> GetPutDownPalletList(InArtificialPutDownCondition info)
        {
            if (string.IsNullOrWhiteSpace(info.PalletCode))
            {
                return FailMessageStatus("参数错误", new InArtificialPutDownPalletMainResult());
            }

            var sql = $@"SELECT MaterielCode = WMS_OUT_PUTDOWN_DTL.MATERIEL_CODE,
                        MaterielName=WMS_OUT_PUTDOWN_DTL.MATERIEL_NAME,QTY=WMS_IN_RECEIPT_RECORD.RECEIPT_QTY,
                        TwoVessel = BAS_PACKAGE.PACKAGE_NAME 
                        from WMS_OUT_PUTDOWN_DTL
                        JOIN WMS_OUT_PUTDOWN ON WMS_OUT_PUTDOWN_DTL.PUTDOWN_ID = WMS_OUT_PUTDOWN.PUTDOWN_ID
                        JOIN BAS_PACKAGE ON WMS_OUT_PUTDOWN_DTL.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE
                        JOIN WMS_IN_RECEIPT_RECORD ON WMS_IN_RECEIPT_RECORD.MATERIEL_BARCODE =WMS_OUT_PUTDOWN_DTL.MATERIEL_BARCODE 
                        WHERE WMS_OUT_PUTDOWN.PUTDOWN_STATUS = 11 
                        AND WMS_OUT_PUTDOWN_DTL.INVOICE_RECORD_STATUS=11 
                            AND WMS_IN_RECEIPT_RECORD.PALLET_CODE = '{info.PalletCode}'";
            var results = new DataRepository<InArtificialPutDownPalletResult>(_dataContext).Query(sql);
            return SuccessStatus(new InArtificialPutDownPalletMainResult
            {
                Details = results,
                IsEffective = new DataRepository<BAS_PALLET>(_dataContext).Query("PALLET_CODE", info.PalletCode).
                                Where(s => s.USED_FLAG == 1 && s.DEL_FLAG == 0).Any() &&
                            new DataRepository<WMS_OUT_INVOICE_RECORD>(_dataContext).Query("PALLET_CODE", info.PalletCode).
                                Where(s => s.INVOICE_RECORD_STATUS == 1).Any()  //已确认状态下
            });
        }

        public OperateResultInfo<InArtificialPutDownBarCodeResult> GetPutDownBarCodeInfo(InArtificialPutDownCondition info)
        {
            if (string.IsNullOrWhiteSpace(info.MaterielBarCodes))
            {
                return FailMessageStatus("参数错误", new InArtificialPutDownBarCodeResult());
            }
            var sql = $@"SELECT MaterielCode=WMS_OUT_PUTDOWN_DTL.MATERIEL_CODE,
                        MaterielName = WMS_OUT_PUTDOWN_DTL.MATERIEL_NAME,SpecsModel = BAS_MATERIEL.SPECS_MODEL,
                        BatchNo = WMS_OUT_PUTDOWN_DTL.BATCH_NO,ArrivalQTY = WMS_IN_RECEIPT_DTL.ARRIVAL_QTY,
                        ReceiptQTY = WMS_IN_RECEIPT_DTL.RECEIPT_DTL_QTY,TwoVessel = BAS_PACKAGE.PACKAGE_NAME,
                        BinCode = WMS_IN_RECEIPT_RECORD.BIN_CODE,BinName = WMS_IN_RECEIPT_RECORD.BIN_NAME,
                        UnitCode = WMS_IN_RECEIPT_RECORD.UNIT_CODE,UnitName = BAS_UNIT.UNIT_NAME,Remark = WMS_OUT_PUTDOWN_DTL.DESCRIBE,
                        PutDownDTLId = WMS_OUT_PUTDOWN_DTL.PUTDOWN_DTL_ID
                        from WMS_OUT_PUTDOWN_DTL
	                        JOIN WMS_OUT_PUTDOWN ON WMS_OUT_PUTDOWN_DTL.PUTDOWN_ID = WMS_OUT_PUTDOWN.PUTDOWN_ID
	                        JOIN BAS_PACKAGE ON WMS_OUT_PUTDOWN_DTL.PACKAGE_CODE = BAS_PACKAGE.PACKAGE_CODE
	                        JOIN BAS_MATERIEL ON BAS_MATERIEL.MATERIEL_CODE=WMS_OUT_PUTDOWN_DTL.MATERIEL_CODE
	                        JOIN WMS_IN_RECEIPT_RECORD ON WMS_IN_RECEIPT_RECORD.MATERIEL_BARCODE =WMS_OUT_PUTDOWN_DTL.MATERIEL_BARCODE 
	                        JOIN WMS_IN_RECEIPT_DTL ON WMS_IN_RECEIPT_DTL.MATERIEL_BARCODE =WMS_OUT_PUTDOWN_DTL.MATERIEL_BARCODE
	                        LEFT JOIN BAS_UNIT ON BAS_UNIT.UNIT_CODE = WMS_OUT_PUTDOWN_DTL.UNIT_CODE 
	                        WHERE WMS_OUT_PUTDOWN.PUTDOWN_STATUS = 11 
	                        AND WMS_OUT_PUTDOWN_DTL.PUTDOWN_DTL_STATUS=11 
	                        AND WMS_OUT_PUTDOWN_DTL.MATERIEL_BARCODE ='{info.MaterielBarCodes}'";
            if (!string.IsNullOrWhiteSpace(info.Statuss))
            {
                sql = sql + " AND WMS_OUT_PUTDOWN.PUTDOWN_STATUS in(" + info.Statuss + ")";
            }
            if (!string.IsNullOrWhiteSpace(info.DTLStatuss))
            {
                sql = sql + " AND WMS_OUT_PUTDOWN.PUTDOWN_DTL_STATUS in(" + info.DTLStatuss + ")";
            }
            var results = new DataRepository<InArtificialPutDownBarCodeResult>(_dataContext).Query(sql).FirstOrDefault();
            return SuccessStatus(results);
        }
    }
}