XYY.Core/XYY.Service.JobManage/BatchRechargingJobGetService.cs

using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using XYY.Core.Standard.Data.Infrastructure;

namespace XYY.Service.JobManage
{
    public interface IBatchRechargingJobGetService : IJobGetService<ExpressFeechargesModel>
    {

    }

    public class BatchRechargingJobGetService : IBatchRechargingJobGetService
    {
        IUnitOfWork unitOfWork;

        public BatchRechargingJobGetService(IUnitOfWork unitOfWork)
        {
            this.unitOfWork = unitOfWork;
        }

        public List<BaseJobModel<ExpressFeechargesModel>> GetAndSendJobs()
        {

            DateTime start = DateTime.Now.AddDays(-7), end = DateTime.Now;
            int[] sealUserId = new int[] { 846, 1314 };
            string sql = @"select       Width,Height,Length,IsResidential,
                                            CustomerId,
                                            ReceiverCountryCode as CountryCode,
                                            ChannelId as ExpressId,
                                            CreateOrderChannelId as PublicExpressId,
                                            OPWeight as BillingWeight,a.Id as OrderId,
                                            receivetime as AccountingDate,
                                            receivetime as TransactionTime,
                                            case when customerid = 683 then ActualWeight/1000.0 else OPWeight/1000.0 end as BillingWeight,
                                            SystemNo as BillNo,
                                            0 as SettlementAmount,
                                            TrackingNumber,
                                            b.GoodsDeclareFee as GoodsDeclared,
                                            b.GoodsDeclaredCurreny,
                                            '全程派送运费' as BillType,
                                            '支出' as IncomeOrESxpense,
                                            case when  IOSS ='' or IOSS is null  then 0 else 1 end as HasIOSS,
                                            ReceiverZipCode as ZipCode,
                                            c.BranchCompanyId as BranchCompanyCustomerId,
                                            a.IsOnline,
                                            a.TrackingNumber 
                                         from Order_Order(nolock)a
                                           left join User_Customer c on a.CustomerId=c.Id  
                                          outer apply (select sum(DeclareFee * Quantity) as GoodsDeclareFee,max(DeclareCurrency)as  GoodsDeclaredCurreny from Order_OrderGoods(nolock) where OrderId=a.Id )b

                                         where ReceiveTime between @start and @end and SealUserId in @sealUserId";
            var list = this.unitOfWork.Connection.Query<ExpressFeechargesModel>(sql, new { start = start, end = end, sealUserId = sealUserId }, this.unitOfWork.Transaction);

            return list.Select(x => new BaseJobModel<ExpressFeechargesModel>
            {
                Data = x,
                JobModelId = Guid.NewGuid().ToString(),
                JobType = "Recharging",
                Key1 = x.BillNo,
                Key2 = null,
                StartTime = DateTime.Now,
            }).ToList();
        }

    }
}