using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using DapperORMCore.String.Consts;
using Microsoft.Extensions.Configuration;
using NXWMS.Code.Extends;
using NXWMS.IService.NXWMS.Balance;
using NXWMS.IService.NXWMS.Report;
using NXWMS.Model.AppModels.Condition.Balance;
using NXWMS.Model.AppModels.Condition.Report;
using NXWMS.Model.AppModels.Result.Balance;
using NXWMS.Model.AppModels.Result.Report;
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;
using TaskStatus = NXWMS.String.Enums.TaskStatus;

namespace NXWMS.Service.NXWMS.Report
{
    /// <summary>
    /// 报告查询
    /// </summary>
    [AutoInject(typeof(IReportSearchService), InjectType.Scope)]
    public class ReportSearchService : ServiceBase, IReportSearchService
    {
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

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

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

        public ReportSearchService(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<InstockDayStatisticsResult>> GetInstockDayStatisticsList(InstockDayStatisticsCondition info)
        {
            var sql = string.Empty;
            var sqlAndBuilder = new StringBuilder();

            sqlAndBuilder = string.IsNullOrWhiteSpace(info.InstockNo) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("RECEIPT_NO", info.InstockNo, DBOperationString._ContainIn));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.MaterielCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("MATERIEL_CODE", info.MaterielCode, DBOperationString._ContainIn));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.MaterielTypeCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("MATERIEL_TYPE_CODE", info.MaterielTypeCode, DBOperationString._Equal));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.BillType) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("RECEIPT_TYPE", info.BillType, DBOperationString._Equal));

            sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("CreateDate", info.BeginTime, DBOperationString._LargeEqual));
            sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("CreateDate", info.EndTime, DBOperationString._SmallEqual));

            //BillTypeName =
            //                { _iSQLNodeRepository.GetEnumIntCaseString<InstockType>("BALANCE_STATUS", "WMS_STK_BALANCE")},
            sql = $@"
                    SELECT QTY=SUM(ISNULL(QTY,0)),InstockNo,BillType,
                    LineNumber,InstockTime,InstockBillCreateTime,
                    MaterielCode,MaterielName,
                    MaterielTypeCode,MaterielTypeName,
                    PackageCode,PackageName,UnitCode,UnitName,
                    Volume,Weight,
                    SupplierContract,SupplierCode,SupplierName,
                    ProductDate,ExpDate,BalanceStatus FROM (
                    SELECT InstockNo=WMS_IN_RECEIPT.RECEIPT_NO,BillType=RECEIPT_TYPE,LineNumber=WMS_IN_RECEIPT_DTL.RECEIPT_DTL_ID,InstockTime=WMS_IN_RECEIPT.RECEIPT_TIME,InstockBillCreateTime=WMS_IN_RECEIPT.CREATE_TIME,
                    MaterielCode=WMS_IN_RECEIPT_DTL.MATERIEL_CODE,MaterielName=WMS_IN_RECEIPT_DTL.MATERIEL_NAME,
                    MaterielTypeCode=BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE,MaterielTypeName=BAS_MATERIEL_TYPE.MATERIEL_TYPE_NAME,
                    PackageCode=BAS_PACKAGE.PACKAGE_CODE,PackageName=BAS_PACKAGE.PACKAGE_NAME,UnitCode=BAS_UNIT.UNIT_CODE,UnitName=BAS_UNIT.UNIT_NAME,
                    QTY=WMS_IN_RECEIPT_DTL.PUTAWAY_QTY,
                    Volume=BAS_MATERIEL.VOLUME,Weight=BAS_MATERIEL.WEIGHT,
                    SupplierContract=BAS_SUPPLIER.CONTRACT,SupplierCode=BAS_SUPPLIER.SUPPLIER_CODE,SupplierName=WMS_IN_RECEIPT_DTL.SUPPLIER_NAME,
                    ProductDate=WMS_IN_RECEIPT_DTL.PRODUCT_DATE,ExpDate=WMS_IN_RECEIPT_DTL.EXP_DATE,BalanceStatus = WMS_STK_BALANCE.BALANCE_STATUS,
                    CreateDate=Convert(varchar(100),WMS_IN_RECEIPT.CREATE_TIME,23)
                    FROM WMS_IN_RECEIPT
                    JOIN WMS_IN_RECEIPT_DTL ON WMS_IN_RECEIPT.RECEIPT_ID=WMS_IN_RECEIPT_DTL.RECEIPT_ID
                    JOIN WMS_IN_RECEIPT_RECORD ON WMS_IN_RECEIPT_RECORD.RECEIPT_ID=WMS_IN_RECEIPT.RECEIPT_ID AND 
			                    WMS_IN_RECEIPT_RECORD.RECEIPT_DTL_ID=WMS_IN_RECEIPT_DTL.RECEIPT_DTL_ID
                    JOIN BAS_MATERIEL ON BAS_MATERIEL.MATERIEL_CODE=WMS_IN_RECEIPT_DTL.MATERIEL_CODE
                    JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL.MATERIEL_TYPE=BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE 
                    JOIN BAS_PACKAGE ON WMS_IN_RECEIPT_DTL.PACKAGE_CODE=BAS_PACKAGE.PACKAGE_CODE 
                    JOIN BAS_UNIT ON WMS_IN_RECEIPT_DTL.UNIT_CODE=BAS_UNIT.UNIT_CODE 
                    JOIN BAS_SUPPLIER ON WMS_IN_RECEIPT_DTL.SUPPLIER_CODE=BAS_SUPPLIER.SUPPLIER_CODE 
                    JOIN WMS_STK_BALANCE ON WMS_IN_RECEIPT_RECORD.TRAY_CODE=WMS_STK_BALANCE.TRAY_CODE 
			                    AND WMS_IN_RECEIPT_RECORD.PALLET_CODE=WMS_STK_BALANCE.PALLET_CODE 
                    ) TEMP
                    WHERE 1=1
                    GROUP BY 
                    InstockNo,BillType,LineNumber,InstockTime,InstockBillCreateTime,
                    MaterielCode,MaterielName,
                    MaterielTypeCode,MaterielTypeName,
                    PackageCode,PackageName,UnitCode,UnitName,
                    Volume,Weight,
                    SupplierContract,SupplierCode,SupplierName,
                    ProductDate,ExpDate,BalanceStatus,CreateDate
                    ";

            IEnumerable<InstockDayStatisticsResult> result;
            IEnumerable<InstockDayStatisticsResult> totalResult;
            totalResult = new DataRepository<InstockDayStatisticsResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<InstockDayStatisticsResult>(_dataContext).QueryPage(sql,
                                                "InstockTime", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<InstockDayStatisticsResult>
            {
                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<OutStockDayStatisticsResult>> GetOutStockDayStatisticsList(OutStockDayStatisticsCondition info)
        {
            var sql = string.Empty;
            var sqlAndBuilder = new StringBuilder();

            sqlAndBuilder = string.IsNullOrWhiteSpace(info.OutStockNo) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVOICE_NO", info.OutStockNo, DBOperationString._ContainIn));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.MaterielCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("MATERIEL_CODE", info.MaterielCode, DBOperationString._ContainIn));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.MaterielTypeCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("MATERIEL_TYPE_CODE", info.MaterielTypeCode, DBOperationString._Equal));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.BillType) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("INVOICE_TYPE", info.BillType, DBOperationString._Equal));

            sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("CreateDate", info.BeginTime, DBOperationString._LargeEqual));
            sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("CreateDate", info.EndTime, DBOperationString._SmallEqual));

            sql = $@"
                        SELECT QTY=SUM(ISNULL(QTY,0)),OutstockNo,BillType,LineNumber,OutstockTime,OutstockBillCreateTime,
                        MaterielCode,MaterielName,
                        MaterielTypeCode,MaterielTypeName,
                        PackageCode,PackageName,UnitCode,UnitName,
                        Volume,Weight,
                        SupplierContract,SupplierCode,SupplierName,
                        ProductDate,ExpDate,BalanceStatus FROM (
                        SELECT OutstockNo=WMS_OUT_INVOICE.INVOICE_NO,BillType=WMS_OUT_INVOICE.INVOICE_TYPE,LineNumber=WMS_OUT_INVOICE_DTL.INVOICE_DTL_ID,
                        OutstockTime=WMS_OUT_INVOICE.INVOICE_END_TIME,OutstockBillCreateTime=WMS_OUT_INVOICE.CREATE_TIME,
                        MaterielCode=WMS_OUT_INVOICE_DTL.MATERIEL_CODE,MaterielName=WMS_OUT_INVOICE_DTL.MATERIEL_NAME,
                        MaterielTypeCode=BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE,MaterielTypeName=BAS_MATERIEL_TYPE.MATERIEL_TYPE_NAME,
                        PackageCode=BAS_PACKAGE.PACKAGE_CODE,PackageName=BAS_PACKAGE.PACKAGE_NAME,UnitCode=BAS_UNIT.UNIT_CODE,UnitName=BAS_UNIT.UNIT_NAME,
                        QTY=WMS_OUT_INVOICE_DTL.PUTDOWN_QTY,
                        Volume=BAS_MATERIEL.VOLUME,Weight=BAS_MATERIEL.WEIGHT,
                        SupplierContract=BAS_SUPPLIER.CONTRACT,SupplierCode=BAS_SUPPLIER.SUPPLIER_CODE,SupplierName=WMS_OUT_INVOICE_DTL.SUPPLIER_NAME,
                        ProductDate=WMS_OUT_INVOICE_DTL.PRODUCT_DATE,ExpDate=WMS_OUT_INVOICE_DTL.EXP_DATE,BalanceStatus = WMS_STK_BALANCE.BALANCE_STATUS,
                        CreateDate=Convert(varchar(100),WMS_OUT_INVOICE.CREATE_TIME,23)
                        FROM WMS_OUT_INVOICE
                        JOIN WMS_OUT_INVOICE_DTL ON WMS_OUT_INVOICE.INVOICE_ID=WMS_OUT_INVOICE_DTL.INVOICE_ID
                        JOIN WMS_OUT_INVOICE_RECORD ON WMS_OUT_INVOICE_RECORD.INVOICE_ID=WMS_OUT_INVOICE.INVOICE_ID AND 
			                        WMS_OUT_INVOICE_RECORD.INVOICE_DTL_ID=WMS_OUT_INVOICE_DTL.INVOICE_DTL_ID
                        JOIN BAS_MATERIEL ON BAS_MATERIEL.MATERIEL_CODE=WMS_OUT_INVOICE_DTL.MATERIEL_CODE
                        JOIN BAS_MATERIEL_TYPE ON BAS_MATERIEL.MATERIEL_TYPE=BAS_MATERIEL_TYPE.MATERIEL_TYPE_CODE 
                        JOIN BAS_PACKAGE ON WMS_OUT_INVOICE_DTL.PACKAGE_CODE=BAS_PACKAGE.PACKAGE_CODE 
                        JOIN BAS_UNIT ON WMS_OUT_INVOICE_DTL.UNIT_CODE=BAS_UNIT.UNIT_CODE 
                        JOIN BAS_SUPPLIER ON WMS_OUT_INVOICE_DTL.SUPPLIER_CODE=BAS_SUPPLIER.SUPPLIER_CODE 
                        JOIN WMS_STK_BALANCE ON WMS_OUT_INVOICE_RECORD.TRAY_CODE=WMS_STK_BALANCE.TRAY_CODE 
			                        AND WMS_OUT_INVOICE_RECORD.PALLET_CODE=WMS_STK_BALANCE.PALLET_CODE 
                        ) TEMP
                        WHERE 1=1
                        GROUP BY 
                        OutstockNo,BillType,LineNumber,OutstockTime,OutstockBillCreateTime,
                        MaterielCode,MaterielName,
                        MaterielTypeCode,MaterielTypeName,
                        PackageCode,PackageName,UnitCode,UnitName,
                        Volume,Weight,
                        SupplierContract,SupplierCode,SupplierName,
                        ProductDate,ExpDate,BalanceStatus,CreateDate 
                    ";

            IEnumerable<OutStockDayStatisticsResult> result;
            IEnumerable<OutStockDayStatisticsResult> totalResult;
            totalResult = new DataRepository<OutStockDayStatisticsResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<OutStockDayStatisticsResult>(_dataContext).QueryPage(sql,
                                                "OutstockTime", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<OutStockDayStatisticsResult>
            {
                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<WarehouseInventoryResult>> GetWarehouseInventoryList(WarehouseInventoryCondition info)
        {
            var sql = string.Empty;
            var sqlTemp = string.Empty;
            var sqlAndBuilder = new StringBuilder();
            var sqlOrBuilder = new StringBuilder();

            sqlAndBuilder = string.IsNullOrWhiteSpace(info.BatchNo) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BATCH_NO", info.BatchNo, DBOperationString._ContainIn,
                        "VM_WMS_WAREHOUSE_INVENTORY"
                        ));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.UnitCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("UNIT_CODE", info.UnitCode, DBOperationString._ContainIn,
                        "VM_WMS_WAREHOUSE_INVENTORY"
                        ));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");


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

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

            sql = $@"
                        SELECT * FROM (
                        SELECT TEMP.WAREHOUSE_CODE,TEMP.WAREHOUSE_NAME,
                        TEMP.MATERIEL_TYPE_NAME,TEMP.MATERIEL_NAME,TEMP.UNIT_NAME,TEMP.BATCH_NO,
                        InStockQTY = SUM(CASE WHEN TEMP.TYPE='IN' THEN TEMP.QTY ELSE 0 END),
                        OutStockQTY = SUM(CASE WHEN TEMP.TYPE='OUT' THEN TEMP.QTY ELSE 0 END),
                        OpeningInventoryQTY=WMS_STK_TRAY_DTL.QTY+(
			                        SELECT 
			                        QTY = (CASE WHEN  TYPE='OUT' THEN 0-QTY ELSE QTY END)
				                        FROM VM_WMS_WAREHOUSE_INVENTORY TEMP1
			                        WHERE  TEMP1.TO_DATE>GETDATE() AND
			                        TEMP1.TRAY_CODE=WMS_STK_BALANCE.TRAY_CODE 
			                        AND TEMP1.MATERIEL_CODE=WMS_STK_TRAY_DTL.MATERIEL_CODE),
                        FinalInventoryQTY=WMS_STK_TRAY_DTL.QTY+(
			                        SELECT 
			                        QTY = (CASE WHEN  TYPE='OUT' THEN 0-QTY ELSE QTY END) FROM 
				                        VM_WMS_WAREHOUSE_INVENTORY TEMP1
			                        WHERE 
			                        TEMP1.TO_DATE>GETDATE() AND
			                        TEMP1.TRAY_CODE=WMS_STK_BALANCE.TRAY_CODE 
			                        AND TEMP1.MATERIEL_CODE=WMS_STK_TRAY_DTL.MATERIEL_CODE)
                        FROM WMS_STK_BALANCE 
                        JOIN WMS_STK_TRAY ON WMS_STK_TRAY.TRAY_CODE=WMS_STK_BALANCE.TRAY_CODE
                        JOIN WMS_STK_TRAY_DTL ON WMS_STK_TRAY.TRAY_ID=WMS_STK_TRAY_DTL.TRAY_ID
                        JOIN VM_WMS_WAREHOUSE_INVENTORY TEMP ON WMS_STK_TRAY.TRAY_CODE=TEMP.TRAY_CODE AND 
                            WMS_STK_TRAY_DTL.MATERIEL_CODE=TEMP.MATERIEL_CODE
                        WHERE 1=1 {sql}
                        GROUP BY TEMP.WAREHOUSE_CODE,TEMP.WAREHOUSE_NAME,
                        TEMP.MATERIEL_TYPE_NAME,TEMP.MATERIEL_NAME,TEMP.UNIT_NAME,TEMP.BATCH_NO,
                        WMS_STK_BALANCE.TRAY_CODE,WMS_STK_TRAY_DTL.MATERIEL_CODE,WMS_STK_TRAY_DTL.QTY
                        ) INTEMP
                    WHERE 1=1 
                ";
            sqlAndBuilder = new StringBuilder();
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.UnitCode) ?
                      sqlAndBuilder :
                      sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("FinalInventoryQTY", info.FinalInventoryQTY, DBOperationString._ContainIn,
                      "INTEMP"
                      ));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.UnitCode) ?
                      sqlAndBuilder :
                      sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("InStockQTY", info.InStockQTY, DBOperationString._ContainIn,
                      "INTEMP"
                      ));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.UnitCode) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("OutStockQTY", info.OutStockQTY, DBOperationString._ContainIn,
                    "INTEMP"
                    ));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");
            IEnumerable<WarehouseInventoryResult> result;
            IEnumerable<WarehouseInventoryResult> totalResult;
            totalResult = new DataRepository<WarehouseInventoryResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<WarehouseInventoryResult>(_dataContext).QueryPage(sql,
                                                "WAREHOUSE_CODE", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<WarehouseInventoryResult>
            {
                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<BinUseSituationResult>> GetBinUseSituationList(BinUseSituationCondition info)
        {
            var sql = string.Empty;
            var sqlTemp = string.Empty;
            var sqlTemp1 = string.Empty;
            var sqlAndBuilder = new StringBuilder();
            var 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("MATERIEL_NAME", info.WarehouseInfo, DBOperationString._ContainIn));
            sqlTemp1 = sqlTemp1 + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");

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

            sqlAndBuilder = new StringBuilder();
            sqlAndBuilder = info.BinQTY > 0 ? sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BinQTY", info.BinQTY, DBOperationString._ContainIn, "TEMP"
                     ));
            sqlAndBuilder = info.IdleBinQTY > 0 ? sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IdleBinQTY", info.IdleBinQTY, DBOperationString._ContainIn, "TEMP"
                    ));
            sqlAndBuilder = info.InstockOccupyQTY > 0 ? sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("InstockOccupyQTY", info.InstockOccupyQTY, DBOperationString._ContainIn, "TEMP"
                    ));
            sqlAndBuilder = info.DisableOccupyQTY > 0 ? sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("InstockOccupyQTY", info.DisableOccupyQTY, DBOperationString._ContainIn, "TEMP"
                    ));
            sqlAndBuilder = info.OutstockOccupyQTY > 0 ? sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("OutstockOccupyQTY", info.OutstockOccupyQTY, DBOperationString._ContainIn, "TEMP"
                    ));
            sqlAndBuilder = info.EmplyBinQTY > 0 ? sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("EmplyBinQTY", info.EmplyBinQTY, DBOperationString._ContainIn, "TEMP"
                    ));
            sqlAndBuilder = info.FullBinQTY > 0 ? sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("FullBinQTY", info.FullBinQTY, DBOperationString._ContainIn, "TEMP"
                    ));
            sqlAndBuilder = info.IdleBinQTY > 0 ? sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IdleBinQTY", info.IdleBinQTY, DBOperationString._ContainIn, "TEMP"
                    ));
            sqlTemp = (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");
            sql = $@" 
                        SELECT * FROM (SELECT BAS_BIN.AREA_CODE,BAS_BIN.AREA_NAME,REGION_CODE,REGION_NAME,
                        BAS_BIN.WAREHOUSE_CODE,BAS_BIN.WAREHOUSE_NAME,
                        BinQTY=(SELECT COUNT(1) FROM BAS_BIN WHERE USED_FLAG=1),
                        IdleBinQTY=(SELECT COUNT(DISTINCT BAS_BIN.BIN_CODE) 
			                        FROM BAS_BIN JOIN WMS_STK_BALANCE B1 ON BAS_BIN.BIN_CODE=BAS_BIN.BIN_CODE
			                        WHERE B1.BALANCE_STATUS IN(0,100,111) 
			                        AND USED_FLAG=1),
                        EmplyBinQTY=(SELECT COUNT(DISTINCT B1.BIN_CODE) FROM WMS_STK_BALANCE B1
			                        JOIN WMS_STK_TRAY T1 ON T1.TRAY_CODE=B1.TRAY_CODE
			                        JOIN WMS_STK_TRAY_DTL T2 ON T1.TRAY_ID=T2.TRAY_ID
			                        WHERE B1.BALANCE_STATUS NOT IN(0,100) AND BAS_BIN.USED_FLAG=1 
			                        AND T1.TRAY_LOADED_TYPE=3
			                        AND B1.BALANCE_STATUS IN(99)
			                        ),
                        FullBinQTY=(SELECT COUNT(DISTINCT B1.BIN_CODE) FROM WMS_STK_BALANCE B1
			                        JOIN WMS_STK_TRAY T1 ON T1.TRAY_CODE=B1.TRAY_CODE
			                        JOIN WMS_STK_TRAY_DTL T2 ON T1.TRAY_ID=T2.TRAY_ID
			                        WHERE B1.BALANCE_STATUS NOT IN(0,100) AND BAS_BIN.USED_FLAG=1 
			                        AND T1.TRAY_LOADED_TYPE=1
			                        AND B1.BALANCE_STATUS IN(99)),
                        InstockOccupyQTY=
			                        (SELECT COUNT(DISTINCT B1.BIN_CODE) FROM WMS_STK_BALANCE B1
			                        JOIN WMS_STK_TRAY T1 ON T1.TRAY_CODE=B1.TRAY_CODE
			                        JOIN WMS_STK_TRAY_DTL T2 ON T1.TRAY_ID=T2.TRAY_ID
			                        WHERE B1.BALANCE_STATUS NOT IN(0,100) AND BAS_BIN.USED_FLAG=1 
			                        AND B1.BALANCE_STATUS IN(11,22,33)
			                        AND T2.TRAY_DTL_STATUS IN(11,22,33)),
                        OutstockOccupyQTY=
			                        (SELECT COUNT(DISTINCT B1.BIN_CODE) FROM WMS_STK_BALANCE B1 
			                        JOIN WMS_STK_TRAY T1 ON T1.TRAY_CODE=B1.TRAY_CODE
			                        JOIN WMS_STK_TRAY_DTL T2 ON T1.TRAY_ID=T2.TRAY_ID
			                        WHERE B1.BALANCE_STATUS NOT IN(0,100) 
			                        AND BAS_BIN.BIN_CODE=B1.BIN_CODE and BAS_BIN.USED_FLAG=1 
			                        AND B1.BALANCE_STATUS IN(66,77,88)
			                        AND T2.TRAY_DTL_STATUS IN(66,77,88)),
                        DisableOccupyQTY=
			                        (SELECT COUNT(DISTINCT B1.BIN_CODE) FROM BAS_BIN
			                        JOIN WMS_STK_BALANCE B1 ON BAS_BIN.BIN_CODE=B1.BIN_CODE
			                        JOIN WMS_STK_TRAY T1 ON T1.TRAY_CODE=B1.TRAY_CODE
			                        JOIN WMS_STK_TRAY_DTL T2 ON T1.TRAY_ID=T2.TRAY_ID
			                        WHERE B1.BALANCE_STATUS NOT IN(0,100) AND BAS_BIN.USED_FLAG=1 
			                        AND B1.BALANCE_STATUS IN(111)
			                        AND T2.TRAY_DTL_STATUS IN(111))
                        FROM BAS_BIN WHERE USED_FLAG=1 {sqlTemp1}
                        GROUP BY 
                        BAS_BIN.AREA_CODE,BAS_BIN.AREA_NAME,
                        BAS_BIN.WAREHOUSE_CODE,BAS_BIN.WAREHOUSE_NAME,
                        BAS_BIN.USED_FLAG,BAS_BIN.BIN_CODE,REGION_CODE,REGION_NAME
                    ) TEMP
                    WHERE 1=1 {sqlTemp}
                ";
            IEnumerable<BinUseSituationResult> result;
            IEnumerable<BinUseSituationResult> totalResult;
            totalResult = new DataRepository<BinUseSituationResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<BinUseSituationResult>(_dataContext).QueryPage(sql,
                                                "WAREHOUSE_CODE", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<BinUseSituationResult>
            {
                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<WMSTaskReportResult>> GetWMSTaskReportList(WMSTaskReportCondition info)
        {

            var sql = string.Empty;
            var sqlOrBuilder = new StringBuilder();
            var sqlAndBuilder = new StringBuilder();

            sql = $@"SELECT WMS_TSK_TASK.WAREHOUSE_CODE,WMS_TSK_TASK.WAREHOUSE_NAME,
                        WMS_TSK_TASK.TASK_NO,WMS_TSK_TASK.TRAY_CODE,WMS_TSK_TASK.PALLET_CODE,
                        WMS_TSK_TASK.TRAY_LOADED_TYPE,WMS_TSK_TASK.SLOC_CODE,WMS_TSK_TASK.ELOC_CODE,WMS_TSK_TASK.CLOC_CODE,
                        WMS_TSK_TASK.TASK_TYPE,WMS_TSK_TASK.TASK_STATUS,WMS_TSK_TASK.SBIN_CODE,WMS_TSK_TASK.EBIN_CODE,
                        TASK_DETAIL.CMD_NO,WMS_TSK_TASK.TASK_PRIORITY,WMS_TSK_TASK.TASK_MSG,
                        TrayLoadedTypeName=
                            {_iSQLNodeRepository.GetEnumIntCaseString<TrayLoadedType>("TRAY_LOADED_TYPE", "WMS_TSK_TASK")},
                        TaskTypeName=
                            {_iSQLNodeRepository.GetEnumIntCaseString<TaskType>("TASK_TYPE", "WMS_TSK_TASK")},
                        TaskStatusName=
                            {_iSQLNodeRepository.GetEnumIntCaseString<TaskStatus>("TASK_STATUS", "WMS_TSK_TASK")},
                        WMS_TSK_TASK.CREATE_TIME
                        FROM WMS_TSK_TASK
                        JOIN (
                        SELECT TYPE = 'CRN', TASK_NO, CMD_NO, TRAY_STATUS, DEV_NO = CRN_DEV_NO, CMD_TYPE, ACTIVE_TYPE, CMD_STATUS, CMD_LOG, ERR_FLAG ,PALLET_CODE
                        FROM WCS_CRN_CMD
                        WHERE EXISTS(
                        SELECT * FROM(
                        SELECT TASK_NO, CMD_NO, ID = MAX(CRN_CMD_ID) FROM WCS_CRN_CMD
                        WHERE FINISH_TIME IS NULL GROUP BY TASK_NO, CMD_NO) TEMP WHERE TEMP.ID = WCS_CRN_CMD.CRN_CMD_ID)
                        UNION
                        SELECT TYPE = 'TRAN', TASK_NO, CMD_NO, TRAY_STATUS, DEV_NO = TRAN_DEV_NO, CMD_TYPE, ACTIVE_TYPE, CMD_STATUS, CMD_LOG, ERR_FLAG ,PALLET_CODE
                        FROM WCS_TRAN_CMD
                        WHERE EXISTS(
                        SELECT * FROM(
                        SELECT TASK_NO, CMD_NO, ID = MAX(TRAN_CMD_ID) FROM WCS_TRAN_CMD
                        WHERE FINISH_TIME IS NULL GROUP BY TASK_NO, CMD_NO) TEMP WHERE TEMP.ID = WCS_TRAN_CMD.TRAN_CMD_ID)
                        UNION
                        SELECT TYPE = 'AGV', TASK_NO, CMD_NO, TRAY_STATUS, DEV_NO = AGV_CMD_ID, CMD_TYPE, ACTIVE_TYPE, CMD_STATUS, CMD_LOG, ERR_FLAG ,PALLET_CODE
                        FROM WCS_AGV_CMD
                        WHERE EXISTS(
                        SELECT * FROM(
                        SELECT TASK_NO, CMD_NO, ID = MAX(AGV_CMD_ID) FROM WCS_AGV_CMD
                        WHERE FINISH_TIME IS NULL GROUP BY TASK_NO, CMD_NO) TEMP WHERE TEMP.ID = WCS_AGV_CMD.AGV_CMD_ID)
                        ) TASK_DETAIL
                        ON TASK_DETAIL.TASK_NO = WMS_TSK_TASK.TASK_NO
                        LEFT JOIN BAS_PALLET ON BAS_PALLET.PALLET_CODE=TASK_DETAIL.PALLET_CODE
                        LEFT JOIN BAS_BIN EBAS_BIN ON WMS_TSK_TASK.EBIN_CODE = EBAS_BIN.BIN_CODE
                        LEFT JOIN BAS_BIN SBAS_BIN ON WMS_TSK_TASK.SBIN_CODE = SBAS_BIN.BIN_CODE
                    WHERE 1=1 
                    ";
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.ElocBinCode) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("EBIN_CODE", info.ElocBinCode, DBOperationString._Equal, "WMS_TSK_TASK"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.SlocBinCode) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("SBIN_CODE", info.SlocBinCode, DBOperationString._Equal, "WMS_TSK_TASK"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.ElocCode) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("ELOC_CODE", info.ElocCode, DBOperationString._Equal, "WMS_TSK_TASK"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.SlocCode) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("SLOC_CODE", info.SlocCode, DBOperationString._Equal, "WMS_TSK_TASK"));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.TaskNo) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("TASK_NO", info.TaskNo, DBOperationString._ContainIn, "WMS_TSK_TASK"));
            sqlAndBuilder = info.TaskStatus == null ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("TASK_STATUS", info.TaskStatus, DBOperationString._Equal, "WMS_TSK_TASK"));
            sqlAndBuilder = info.TaskType == null ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("TASK_TYPE", info.TaskType, DBOperationString._Equal, "WMS_TSK_TASK"));
            sqlAndBuilder = info.LoadedStatus == null ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("TRAY_LOADED_TYP", info.LoadedStatus, DBOperationString._Equal, "WMS_TSK_TASK"));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");


            //托盘号
            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<WMSTaskReportResult> result;
            IEnumerable<WMSTaskReportResult> totalResult;
            totalResult = new DataRepository<WMSTaskReportResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<WMSTaskReportResult>(_dataContext).QueryPage(sql,
                                                "CREATE_TIME", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<WMSTaskReportResult>
            {
                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<FaultStatisticsReportResult>> GetFaultStatisticsReportList(FaultStatisticsReportCondition info)
        {
            var sql = string.Empty;
            var sqlOrBuilder = new StringBuilder();
            var sqlAndBuilder = new StringBuilder();

            sql = $@"SELECT 
                        DevTypeCode = WCS_BAS_DEV_TYPE.DEV_TYPE_CODE,DevTypeName = WCS_BAS_DEV_TYPE.DEV_TYPE_NAME,
                        DevCode = WCS_BAS_DEV.DEV_CODE,DevName = WCS_BAS_DEV.DEV_NAME,
                        FaultLevelName='',
                        WMS_LOG_FAULT_MSG.* FROM WMS_LOG_FAULT_MSG
                        JOIN WCS_BAS_DEV ON WMS_LOG_FAULT_MSG.FAULT_DEV_CODE=WCS_BAS_DEV.DEV_CODE
                        JOIN WCS_BAS_DEV_TYPE ON WCS_BAS_DEV.DEV_TYPE_CODE=WCS_BAS_DEV_TYPE.DEV_TYPE_CODE
                        WHERE 1=1 
                    ";
            //{ _iSQLNodeRepository.GetEnumIntCaseString<FaultLevel>("FAULT_LEVEL", "WMS_LOG_FAULT_MSG")}, 
            //库位
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.DevInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("DEV_CODE", info.DevInfo, DBOperationString._ContainIn, "WCS_BAS_DEV"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.DevInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("DEV_NAME", info.DevInfo, DBOperationString._ContainIn, "WCS_BAS_DEV"));
            //库位类型
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.DevTypeInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("DEV_TYPE_CODE", info.DevTypeInfo, DBOperationString._ContainIn, "WCS_BAS_DEV_TYPE"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.DevTypeInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("DEV_TYPE_NAME", info.DevTypeInfo, DBOperationString._ContainIn, "WCS_BAS_DEV_TYPE"));
            //报警信息
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.FaultInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("FAULT_NO", info.FaultInfo, DBOperationString._ContainIn, "WMS_LOG_FAULT_MSG"));
            sqlOrBuilder = string.IsNullOrWhiteSpace(info.FaultInfo) ?
                        sqlOrBuilder :
                        sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("FAULT_DESC", info.FaultInfo, DBOperationString._ContainIn, "WMS_LOG_FAULT_MSG"));

            sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : "");


            sqlAndBuilder = info.FaultBeginTimeLen > 0 ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("FAULT_TIME_LEN", info.FaultBeginTimeLen, DBOperationString._LargeEqual, "WMS_LOG_FAULT_MSG"));
            sqlAndBuilder = info.FaultEndTimeLen > 0 ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("FAULT_TIME_LEN", info.FaultEndTimeLen, DBOperationString._SmallEqual, "WMS_LOG_FAULT_MSG"));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");

            IEnumerable<FaultStatisticsReportResult> result;
            IEnumerable<FaultStatisticsReportResult> totalResult;
            totalResult = new DataRepository<FaultStatisticsReportResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<FaultStatisticsReportResult>(_dataContext).QueryPage(sql,
                                                "CREATE_TIME", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<FaultStatisticsReportResult>
            {
                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<FrequencyStatisticsResult>> GetFrequencyStatisticsReportList(FrequencyStatisticsCondition info)
        {
            var scheduleConfigList = new List<ScheduleConfig>()
                    .AddGet(new ScheduleConfig
                    {
                        ScheduleCode = "Day",
                        ScheduleName = "白班",
                        DetailList = new List<ScheduleDetailConfig>()
                        .AddGet(new ScheduleDetailConfig
                        {
                            BeginTime = "8:00",
                            EndTime = "10:00",
                        })
                        .AddGet(new ScheduleDetailConfig
                        {
                            BeginTime = "10:00",
                            EndTime = "12:00",
                        })
                        .AddGet(new ScheduleDetailConfig
                        {
                            BeginTime = "12:00",
                            EndTime = "14:00",
                        })
                        .AddGet(new ScheduleDetailConfig
                        {
                            BeginTime = "14:00",
                            EndTime = "16:00",
                        })
                        .AddGet(new ScheduleDetailConfig
                        {
                            BeginTime = "16:00",
                            EndTime = "18:00",
                        })
                        .AddGet(new ScheduleDetailConfig
                        {
                            BeginTime = "18:00",
                            EndTime = "20:00",
                        })
                    });
            var sql = string.Empty;
            var sqlOrBuilder = new StringBuilder();
            var sqlAndBuilder = new StringBuilder();
            var sqlTempList = new List<string>();
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.ScheduleCode) ?
                    sqlAndBuilder :
                    sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("schedule_code", info.ScheduleCode, DBOperationString._LargeEqual, "WMS_LOG_FAULT_MSG"));

            var scheduleInfo = scheduleConfigList.Where(s => s.ScheduleCode == info.ScheduleCode).FirstOrDefault();

            if (scheduleInfo == null)
            {
                return FailMessageStatus("排单编码错误!", new PageQueryResultInfo<FrequencyStatisticsResult>());
            }

            if (!string.IsNullOrWhiteSpace(info.SectionStr))
            {
                scheduleInfo.DetailList = scheduleInfo.DetailList.Where(s => s.BeginTime + "-" + s.EndTime == info.SectionStr).ToList();
            }
            //排班
            for (int i = 0; i < scheduleInfo.DetailList.Count; i++)
            {
                sqlTempList.Add(string.Format(@"SELECT Date = '{0}',Schedule = '{1}',
	                        ScheduleTimeSection = '{2}-{3}',
                            ReceiptOrderQTY=
	                            (SELECT COUNT(*) FROM WMS_IN_RECEIPT 
	                            WHERE RECEIPT_STATUS!=111 AND CONVERT(VARCHAR(100),UPDATE_TIME,23) = '{0}'
		                            AND CONVERT(VARCHAR(100),UPDATE_TIME,8) BETWEEN
			                            '{2}' AND '{3}'),
                            ReceiptTrayQTY=
	                            (SELECT COUNT(DISTINCT WMS_STK_TRAY.NEWID) FROM WMS_IN_RECEIPT 
		                            JOIN WMS_IN_RECEIPT_DTL ON WMS_IN_RECEIPT.RECEIPT_ID=WMS_IN_RECEIPT_DTL.RECEIPT_ID
		                            JOIN WMS_IN_RECEIPT_RECORD ON WMS_IN_RECEIPT_RECORD.RECEIPT_DTL_ID=WMS_IN_RECEIPT_DTL.RECEIPT_DTL_ID
		                            JOIN WMS_STK_TRAY ON WMS_IN_RECEIPT_RECORD.TRAY_CODE=WMS_STK_TRAY.TRAY_CODE
			                            WHERE RECEIPT_STATUS!=111 AND WMS_IN_RECEIPT_DTL.RECEIPT_DTL_STATUS!=111
				                            AND WMS_IN_RECEIPT_RECORD.RECEIPT_RECORD_STATUS!=2
				                            AND CONVERT(VARCHAR(100),WMS_IN_RECEIPT.UPDATE_TIME,23) = '{0}'
		                            AND CONVERT(VARCHAR(100),WMS_IN_RECEIPT.UPDATE_TIME,8) BETWEEN
			                            '{2}' AND '{3}'),
                            InvoiceQTY=
	                            (SELECT COUNT(*) FROM WMS_OUT_INVOICE 
		                            WHERE INVOICE_STATUS!=111
		                            AND CONVERT(VARCHAR(100),WMS_OUT_INVOICE.UPDATE_TIME,23) = '{0}'
		                            AND CONVERT(VARCHAR(100),WMS_OUT_INVOICE.UPDATE_TIME,8) BETWEEN
			                            '{2}' AND '{3}'),
                            InvoiceExamineQTY=
	                            (SELECT COUNT(*) FROM WMS_OUT_INVOICE 
		                            WHERE INVOICE_STATUS=11
		                            AND CONVERT(VARCHAR(100),WMS_OUT_INVOICE.UPDATE_TIME,23) = '{0}'
		                            AND CONVERT(VARCHAR(100),WMS_OUT_INVOICE.UPDATE_TIME,8) BETWEEN
			                            '{2}' AND '{3}'),
                            PutAwayQTY=
	                            (SELECT COUNT(*) FROM WMS_IN_PUTAWAY
		                            WHERE PUTAWAY_STATUS!='111'
		                            AND CONVERT(VARCHAR(100),WMS_IN_PUTAWAY.UPDATE_TIME,23) = '{0}'
		                            AND CONVERT(VARCHAR(100),WMS_IN_PUTAWAY.UPDATE_TIME,8) BETWEEN
			                            '{2}' AND '{3}'),
                            PutDownQTY=
	                            (SELECT COUNT(*) FROM WMS_OUT_PUTDOWN
		                            WHERE PUTDOWN_STATUS!='111'
		                            AND CONVERT(VARCHAR(100),WMS_OUT_PUTDOWN.UPDATE_TIME,23) = '{0}'
		                            AND CONVERT(VARCHAR(100),WMS_OUT_PUTDOWN.UPDATE_TIME,8) BETWEEN
			                            '{2}' AND '{3}')", info.Date.ToShortDateString(), scheduleInfo.ScheduleName,
                                        scheduleInfo.DetailList[i].BeginTime, scheduleInfo.DetailList[i].EndTime
                )); 
            }

            sql = string.Join("\r\n UNION ", sqlTempList);
            IEnumerable<FrequencyStatisticsResult> result;
            IEnumerable<FrequencyStatisticsResult> totalResult;
            totalResult = new DataRepository<FrequencyStatisticsResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<FrequencyStatisticsResult>(_dataContext).QueryPage(sql, "ScheduleTimeSection", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<FrequencyStatisticsResult>
            {
                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<EarlyWarningReportResult>> GetEarlyWarningReportList(EarlyWarningReportCondition info)
        {
            var sql = string.Empty;
            var sqlOrBuilder = new StringBuilder();
            var sqlAndBuilder = new StringBuilder();

            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),
                        SendFlagName = '未发送', 
                        {info.ItemSQL} FROM WMS_LOG_EARLY_WARNING WHERE 1=1 ";
            sqlAndBuilder = info.WarningBeginTime != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetOrCondition("WARNING_TIME", info.WarningBeginTime, DBOperationString._LargeEqual));
            sqlAndBuilder = info.WarningEndTime != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetOrCondition("WARNING_TIME", info.WarningEndTime, DBOperationString._SmallEqual));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.WarningParam) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetOrCondition("WARNING_PARAM", info.WarningParam, DBOperationString._ContainIn));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.WarningDescribe) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetOrCondition("WARNING_DESCRIBE", info.WarningDescribe, DBOperationString._ContainIn)); 
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");
            IEnumerable<EarlyWarningReportResult> result;
            IEnumerable<EarlyWarningReportResult> totalResult;
            totalResult = new DataRepository<EarlyWarningReportResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<EarlyWarningReportResult>(_dataContext).QueryPage(sql, "WARNING_TIME", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<EarlyWarningReportResult>
            {
                RowData = result,
                PageConditionInfo = info,
                TotalCount = totalResult.Count(),
                TotalPageCount = (int)Math.Ceiling((double)totalResult.Count() / info.PageSize)
            }); 
        }
    }
}