using Dapper;
using Nest;
using NPOI.SS.Formula.Functions;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Reflection.Emit;
using System.Threading.Channels;
using System.Threading.Tasks;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.Channel;
using XYY.Model.Standard;
using XYY.Model.Standard.FeeCount;
using XYY.Model.Standard.Order;

namespace XYY.Data.Standard.FeeCount
{
    /**财务客户费用计算*/
    public interface IFinance_Express_FeechargesRepository : IBaseRepository<ExpressFeechargesModel>
    {
        /// <summary>
        /// 获取订单请求计费数据
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        ///  <param name="customerId"></param>
        /// <returns></returns>
        Task<IEnumerable<ExpressFeechargesModel>> GetExpressFeecharges(DateTime beginDate,DateTime endDate,int customerId);


    }

    public class Finance_Express_FeechargesRepository : BaseRepository<ExpressFeechargesModel>, IFinance_Express_FeechargesRepository
    {
        public Finance_Express_FeechargesRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {
        }

        public async Task<IEnumerable<ExpressFeechargesModel>> GetExpressFeecharges(DateTime beginDate, DateTime endDate, int customerId)
        {   
            string from = beginDate.ToString("yyyy-MM-dd 00:00:00");
            string to = endDate.ToString("yyyy-MM-dd 00:00:00");
            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,OPWeight / 1000.0 as BillingWeight,SystemNo as BillNo,
                            0 as SettlementAmount,TrackingNumber,b.GoodsDeclareFee as GoodsDeclared,'全程派送运费' 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,lc.publiccode as Publiccode,
                            uc.CustomerNumber as CustomerNumber
                            from Order_Order(nolock)a left join User_Customer c on a.CustomerId = c.Id
                            left join Logistics_Channel lc on lc.id = CreateOrderChannelId
                            left join User_Customer uc on uc.Id = c.BranchCompanyId
                            outer apply(select sum(DeclareFee* Quantity) as GoodsDeclareFee from Order_OrderGoods(nolock) where OrderId = a.Id )b
                            where TrackingNumber = '5P34C02986596'";
                            //where receivetime >='{from}' and receivetime <'{to}' and CustomerId = {customerId}";
            var result = await base._unitOfWork.Connection.QueryAsync<ExpressFeechargesModel>(sql, transaction: null, commandTimeout: 900);
            return result;
        }
    }



}
