Wms_WebApi/NXWMS.WebApi/NXWMS.Service/NXPDA/InStock/InStockSearchService.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.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
{
    /// <summary>
    /// 入库查询接口
    /// </summary>
    [AutoInject(typeof(IInStockSearchService), InjectType.Scope)]
    public class InStockSearchService : ServiceBase, IInStockSearchService
    {
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

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

        /// <summary>
        /// 配置
        /// </summary>
        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<List<InReceiptResult>> GetReceiptList(InReceiptCondition info)
        {
            //if (string.IsNullOrWhiteSpace(info.No) && info.Date == null && string.IsNullOrWhiteSpace(info.SupplierName))
            //{
            //    return FailMessageStatus("参数错误", new List<InReceiptResult>());
            //}
            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<InstockType>("RECEIPT_TYPE", "WMS_IN_RECEIPT")},
            //            Status = {_iSQLNodeRepository.GetEnumIntCaseString<InReceiptStatus>("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<InReceiptResult>(_dataContext).Query(sql);
            return SuccessStatus(results.ToList());
        }

        public OperateResultInfo<InReceiptDTLMainResult> 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<InReceiptDTLResult>(_dataContext).Query(sql);
            return SuccessStatus(new InReceiptDTLMainResult
            {
                inReceiptDTLResults = results.ToList(),
                QTY = results.Sum(s => s.QTY),
                TypeQTY = results.Count()
            });
        }



        public OperateResultInfo<List<InReceiptPalletResult>> GetReceiptPalletList(InReceiptPalletCondition info)
        {

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

            var palletResult = new DataRepository<BAS_PALLET>(_dataContext).Query("PALLET_CODE", info.PalletCode)
                            .Where(s => s.USED_FLAG == 1 && s.DEL_FLAG == 0).Any();
            if (!palletResult)
            {
                return FailMessageStatus("托盘编码不存在,请重新录入!", new List<InReceiptPalletResult>());
            }

            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<InReceiptPalletResult>(_dataContext).Query(sql);
            return SuccessStatus(results.ToList());
        }

        public OperateResultInfo<InArrivalBarCodeResult> 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<InArrivalBarCodeResult>(_dataContext).Query(sql).FirstOrDefault();
            return SuccessStatus(results);
        }

        public OperateResultInfo<InArtificialPutAwayDTLResult> 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<InArtificialPutAwayResult>(_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<InArtificialPutAwayResult>(_dataContext).Query(sql);
            return SuccessStatus(new InArtificialPutAwayDTLResult { InArtificialPutAwayList = results.ToList(), IsEffective = resultsTemp.Any() });
        }

        public OperateResultInfo<InInspectionPalletMainResult> 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<InspectionResultStatus>("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<InInspectionPalletResult>(_dataContext).Query(sql);
            return SuccessStatus(new InInspectionPalletMainResult
            {
                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_IN_RECEIPT_RECORD>(_dataContext).Query("PALLET_CODE", info.PalletCode).
                            Where(s => s.RECEIPT_RECORD_STATUS == 0 && s.INSPECTION_RESULT == "WAIT").Any()
            });
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public OperateResultInfo<InInspectionBarCodeResult> 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<InInspectionBarCodeResult>(_dataContext).Query(sql).FirstOrDefault();
            return SuccessStatus(results);
        }
        /// <summary>
        /// 按单号查询可以录入的单据
        /// </summary>
        /// <param name="InspectionCode"></param>
        /// <returns></returns>
        public OperateResultInfo<WmsQaInspectionResult> 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<WmsQaInspectionResult>(_dataContext).ExecuteScalar(strSQL, new { InspectionCode });
            // return SuccessStatus(result);
        }
    }
}