XYY.Core/2.Data/XYY.Data.Strandard/Finance/IFinance_Logistics_LadingBillRepository.cs

using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard;
using XYY.Model.Standard.Finance;

namespace XYY.Data.Standard.Finance
{

    public interface IFinance_Logistics_LadingBillRepository : IBaseRepository<Finance_Logistics_LadingBill>
    {
        Task<Dictionary<int, int>> GetGroupCounts();

        Task<PageResult<View_Finance_Logistics_LadingBill>> GetData(QueryModel queryModel);

        new Task<PageResult<View_Finance_Logistics_LadingBill>> QueryPager(QueryModel queryModel);

        Task<View_Finance_Logistics_LadingBill> GetInfo(string billNumber);

        Task UpdateDetailFristFee(string billNumber);

        Task UpdateDetailClareFee(string billNumber);

        Task<List<View_FinanceBill2>> GetView_FinanceBills(int channelId, int customerId, DateTime start, DateTime end, SettlementCompany? SettlementCompany);
        Task<List<View_FinanceBill2>> GetBillsDetails(int channelId, int customerId, DateTime start, DateTime end, SettlementCompany? SettlementCompany);
        Task<List<Finance_Logistics_LadingBillLog>> GetLadingBillLogs(int BillId, FinanceIncidentalTypeEnum type);
        Task<List<EFFinanceBill>> GetEFFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId);
        Task<List<EFFinanceGroupBill>> GetEFFinanceBillGroupAsChannel(DateTime start, DateTime end);
        Task<List<EFFinanceGroupBill>> GetEFFinanceBillGroupAsCustomer(DateTime start, DateTime end);
        Task<List<View_FinanceBill2>> GetEFFinanceBillDetails(DateTime start, DateTime end, int channelId, int customerId);
        Task<List<EFFinanceGroupBill>> GetFinanceBillGroupAsChannel(DateTime start, DateTime end);
        Task<List<EFFinanceGroupBill>> GetFinanceBillGroupAsCustomer(DateTime start, DateTime end);
        Task<List<EFFinanceBill>> GetFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId);
        Task<List<View_FinanceBill2>> GetFinanceBillDetails(DateTime start, DateTime end, int channelId, int customerId);
        Task<List<View_FinanceBill_WithBranchCompany>> GetFinanceReportDetails(DateTime? receiveStart, DateTime? receiveTimeEnd, int? channelId, int? customerId, int? branchCompanyId, List<string> trackingNumbers, List<string> transferNumbers, List<string> ladingBillNumbers);
        Task<List<View_FinanceBill_WithBranchCompany>> GetFinanceSimpleReportDetails(DateTime? receiveStart, DateTime? receiveTimeEnd, int? channelId, int? customerId, int? branchCompanyId, List<string> trackingNumbers, List<string> transferNumbers, List<string> ladingBillNumbers);
        Task<PageResult<View_FinanceBill_WithBranchCompany>> QueryFinanceReportDetailPage(QueryModel queryModel);

        Task<List<CompletionRate>> GetCompletionRate(DateTime start, DateTime end, int channelId, int customerId);
        Task<FinanceBillDetailWeightDifferenceGroup> GetFinanceBillDetailWeightDifferenceGroup(DateTime start, DateTime end, int channelId, int customerId);
        Task<List<FinanceBillDetailWeightDifference>> GetDifferenceDetailsAsync(DateTime start, DateTime end, int channelId, int customerId);
        /// <summary>
        /// 更新状态
        /// 收款金额
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="aboutCusetomerCheques">是否更新金额</param>
        /// <returns></returns>
        Task UpdateFirstBillCusetomerChequesStatus(int CustomerId, DateTime start, DateTime end);
        Task CompleteFirstBillCusetomerChequesStatus(int CustomerId, DateTime end);
        Task<IEnumerable<Finance_Logistics_LadingBill>> GetLadingBillByChargesDetail(DateTime end, int customerId);

    }
    public class Finance_Logistics_LadingBillRepository : BaseRepository<Finance_Logistics_LadingBill>, IFinance_Logistics_LadingBillRepository
    {
        public Finance_Logistics_LadingBillRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {

        }

        public async Task DeleteBill(int v, int billId)
        {
            await DeleteByIdAsync(billId);
            await _unitOfWork.Connection.ExecuteAsync("delete Finance_ServiceDeliveryBillDetail where BillId=" + billId, null, _unitOfWork.Transaction);
        }

        public async Task<PageResult<View_Finance_Logistics_LadingBill>> GetData(QueryModel queryModel)
        {
            return await _unitOfWork.GetPagingListAsync<View_Finance_Logistics_LadingBill>(queryModel);
        }

        public async Task<View_Finance_Logistics_LadingBill> GetInfo(string billNumber)
        {
            return (await _unitOfWork.QueryBySqlAsync<View_Finance_Logistics_LadingBill>("select * from View_Finance_Logistics_LadingBillInfo where LadingBillNumber='" + billNumber + "'")).FirstOrDefault();
        }

        public async Task<Dictionary<int, int>> GetGroupCounts()
        {
            string sql = @"select Status as [Key],count(0) as [Value] from Finance_Logistics_LadingBill group by Status";
            return (await _unitOfWork.QueryBySqlAsync<dynamic>(sql)).ToDictionary(x => (int)x.Key, x => (int)x.Value);
        }

        public new async Task<PageResult<View_Finance_Logistics_LadingBill>> QueryPager(QueryModel queryModel)
        {
            return await _unitOfWork.GetPagingListAsync<View_Finance_Logistics_LadingBill>(queryModel);
        }

        public async Task UpdateDetailFristFee(string billNumber)
        {
            await _unitOfWork.ExecuteStoredProcedureAsync("updateDetailFristFee", new { billNumber = billNumber });
        }

        public async Task UpdateDetailClareFee(string billNumber)
        {
            await _unitOfWork.ExecuteStoredProcedureAsync("updateDetailClareFee", new { billNumber = billNumber });
        }


        public async Task<List<View_FinanceBill2>> GetBillsDetails(int channelId, int customerId, DateTime start, DateTime end, SettlementCompany? SettlementCompany)
        {
            string where = "";
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;
            if (SettlementCompany.HasValue)
                where += " and SettlementCompany = " + SettlementCompany.GetValue();

            string sql = $@"
select
                             h.LadingBillNumber[提单号],
								a.TrackingNumber[跟踪号], 

	                         	a.OPWeight[称重重量],a.ReceiveTime[称重时间],lp.[Name] as [渠道名称],f.CompanyName[所属客户],
	                           case  when b.ConfirmedAmount = 0 or b.ConfirmedAmount is null
									then d.OPCFreight else b.ConfirmedAmount end  + isnull(b.AttachFee,0) [收入],
	                             case  when c.ConfirmedAmount = 0 or c.ConfirmedAmount is null 
									then d.OPSFreight else c.ConfirmedAmount end  * isnull(g.ToCNY,0) [末端派送], 
								d.OPSCurreny,
	                            isnull(ef.OperateCost,0)[营运成本],
                                case when h.FirstTransferFeeCost = 0 or h.FirstTransferFeeCost is null
									then ef.FirstTransferPrice  * a.OPWeight /1000.0 else  h.FirstTransferFeeCost end [一级中转],
                                case when h.SecondTransferFeeCost = 0 or h.SecondTransferFeeCost is null
									then ef.SecondTransferPrice  * a.OPWeight /1000.0 else h.SecondTransferFeeCost end  [二级中转],
                                case when h.DomesticClearFeeCost = 0 or h.DomesticClearFeeCost is null
									then ef.ClearanceUnitWeightPrice * a.OPWeight /1000.0
									else h.DomesticClearFeeCost end [出口报关],  
                                case when h.FristFeeCost = 0 or h.FristFeeCost is null then
									ef. FirstLegUnitPrice * a.OPWeight/1000 else  h.FristFeeCost end  [空运成本], 
                                case when h.OverseasClearFeeCost =0 or h.OverseasClearFeeCost is null
									then ef.ClearanceUnitWeightPrice * a.OPWeight/1000.0 * isnull(effc.ToCNY,0) else h.OverseasClearFeeCost * isnull(ocfc.ToCny,0) end[清关成本],									
                                case when h.TaxFeeCost = 0 or h.TaxFeeCost is null then
									 ef.TaxPrice * a.OPWeight /1000 * isnull(eftax.ToCNY,0) else isnull(h.TaxFeeCost,0)*isnull(tfc.ToCny,0) end [关税],
                                case when h.OverSeaTransferFeeCost =0 or h.OverseasClearFeeCost is null
								then ef.OverSeaTransferPrice * a.OPWeight/1000.0 * ISNULL(eftfc.ToCNY,0)
									else isnull(h.OverSeaTransferFeeCost,0)*isnull(ostfc.ToCny,0) end [海外中转],
                                case when h.Subsidy =0 or h.Subsidy is null 
									then ef.SubsidyPrice * isnull(efsc.ToCNY,0) else isnull(h.Subsidy,0)*isnull(sc.ToCny,0) end[补贴]
                            from Order_Order(nolock) a  
                        left join
                            (select sum(isnull( ConfirmedAmount,0)) as ConfirmedAmount,TrackingNumber,sum(isnull(AttachFee,0)) as AttachFee from Finance_CustomerDeliveryBillDetail(nolock)group by trackingnumber) b on a.TrackingNumber=b.TrackingNumber
                        left join
                            (select sum(isnull( ConfirmedAmount,0)) as ConfirmedAmount,TrackingNumber   from Finance_ServiceDeliveryBillDetail(nolock)group by trackingnumber) c on c.TrackingNumber=a.TrackingNumber
                        left join
                            Order_Fee(nolock) d on d.OrderId = a.Id
                        left join
	                        Logistics_Channel(nolock) e on e.id=a.CreateOrderChannelId
                        left join
	                        User_Customer(nolock) f on f.Id=a.CustomerId
                        left join 
	                        Finance_Rate(nolock) g on g.Currency = d.OPSCurreny
								and convert(date,a.ReceiveTime) between
								 convert(date,	g.StartDate) and convert(date, g.EndDate)
                        left join
	                        Finance_OrderCost(nolock) h on h.OrderId = a.Id
						left join Logistics_Public(nolock) lp on lp.Code = e.PublicCode
						left join Finance_Logistics_LadingBill(nolock) llb on llb.BillNumber = h.LadingBillNumber
                        outer apply( select top 1 * from Finance_EstimateFee(nolock) ef where ef.ChannelId = lp.Id 
                            and ef.EffectiveDate<a.ReceiveTime order by ef.EffectiveDate desc)ef
                        left join 
	                        Finance_Rate(nolock) ocfc on ocfc.Currency = h.OverseasClearFeeCurreny  and 
								convert(date, llb.OverseasClearBillTime)
									between  convert(date, ocfc.StartDate) and convert(date, ocfc.EndDate)
                        left join
	                        Finance_Rate(nolock) tfc on tfc.Currency = h.TaxFeeCurreny and 
							convert(date, llb.TaxBillTime) 
								between convert(date, tfc.StartDate) and convert(date, tfc.EndDate)
                        left join
	                        Finance_Rate(nolock) ostfc on ostfc.Currency = h.OverSeaTransferFeeCurreny and 
							convert(date, llb.OverSeaTransferBillTime) 
								between convert(date, ostfc.StartDate) and convert(date, ostfc.EndDate)
                        left join
	                        Finance_Rate(nolock) sc on sc.Currency = h.SubsidyCurreny and  
							convert(date, llb.SubsidyBillTime)
								between convert(date, sc.StartDate) and convert(date,sc.EndDate)
						---预计汇率
                        left join 
	                        Finance_Rate(nolock) effc on effc.Currency = ef.ClearancePriceCurrency  and 
								convert(date,a.ReceiveTime)
									between  convert(date, effc.StartDate) and convert(date, effc.EndDate)
                        left join
	                        Finance_Rate(nolock) eftax on eftax.Currency = ef.TaxPriceCurrency and 
							convert(date,a.ReceiveTime) 
								between convert(date, eftax.StartDate) and convert(date, eftax.EndDate)
                        left join
	                        Finance_Rate(nolock) eftfc on eftfc.Currency = ef.OverSeaTransferPriceCurrency  and 
							convert(date, a.ReceiveTime) 
								between convert(date, eftfc.StartDate) and convert(date, eftfc.EndDate)
                        left join
	                        Finance_Rate(nolock) efsc on efsc.Currency = ef.SubsidyPriceCurrency and  
							convert(date, a.ReceiveTime)
								between convert(date, efsc.StartDate) and convert(date,efsc.EndDate)
	
	

                            where a.ReceiveTime between '{start.ToString_yyyyMMdd()}' and '{end.ToString_yyyyMMdd()}' {where} 
	                        and a.OrderStatus>1
                            ";
            return (await _unitOfWork.QueryBySqlAsync<View_FinanceBill2>(sql, 360)).ToList();
        }

        public async Task<List<EFFinanceBill>> GetEFFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId)
        {
            string where = "";
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;
            string sql = $@"
                            select CONVERT(nvarchar(10),称重时间, 120)称重时间,sum(毛利) as 毛利,
                                   sum(收入)收入,sum(毛利)/sum(收入)毛利占比 from EFFinanceBillDetail 
								where 称重时间 between '{start}' and '{end.AddDays(1)}' {where} 
							group by CONVERT(nvarchar(10),称重时间, 120)
							order by CONVERT(nvarchar(10),称重时间, 120)
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }

        public async Task<List<View_FinanceBill2>> GetEFFinanceBillDetails(DateTime start, DateTime end, int channelId, int customerId)
        {
            string where = "";
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;
            string sql = $@"
                            select * from EFFinanceBillDetail(nolock)
								where 称重时间 between '{start}' and '{end.AddDays(1)}' {where}  
							";
            return (await _unitOfWork.QueryBySqlAsync<View_FinanceBill2>(sql, 360)).ToList();
        }


        public async Task<List<EFFinanceBill>> GetEFFinanceBillCustomerEveryDay(
            DateTime start,
            DateTime end)
        {
            string sql = $@"
                            select [所属客户],CONVERT(nvarchar(10),称重时间, 120)称重时间,sum(毛利) as 毛利,sum(收入)收入,sum(毛利)/sum(收入)毛利占比 from EFFinanceBillDetail 
								where 称重时间 between '{start}' and '{end.AddDays(1)}' and 收入>0
							group by CONVERT(nvarchar(10),称重时间, 120),[所属客户]
							order by CONVERT(nvarchar(10),称重时间, 120) 
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }

        public async Task<List<EFFinanceBill>> GetEFFinanceBillChannelEveryDay(
            DateTime start,
            DateTime end)
        {
            string sql = $@"
                            select [渠道名称],CONVERT(nvarchar(10),称重时间, 120)称重时间,sum(毛利) as 毛利,sum(收入)收入,sum(毛利)/sum(收入)毛利占比 from EFFinanceBillDetail 
								where 称重时间 between '{start}' and '{end.AddDays(1)}' and 收入>0
							group by CONVERT(nvarchar(10),称重时间, 120),[渠道名称]
							order by CONVERT(nvarchar(10),称重时间, 120) 
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }

        public async Task<List<EFFinanceGroupBill>> GetEFFinanceBillGroupAsChannel(DateTime start, DateTime end)
        {
            string sql = $@"
                                select [渠道名称],
								    sum(收入) 预计收入,
                                    count(0) 收货单量,
                                    sum(称重重量/1000.0) 收货重量,
								    sum(毛利) as 毛利,
								    sum(毛利)/sum(收入)毛利占比,
                                     sum(毛利)/count(0) 单件毛利,CreateOrderChannelId
								from EFFinanceBillDetail
                                where 称重时间 between '{start}' and '{end.AddDays(1)}'
							    	and 收入>0
								    group by [渠道名称],CreateOrderChannelId
								    order by SUM([收入]) desc";
            var allChannelData = (await _unitOfWork.QueryBySqlAsync<EFFinanceGroupBill>(sql, 360)).ToList();
            var everyDay = await GetEFFinanceBillChannelEveryDay(start, end);
            foreach (var custometData in allChannelData)
            {
                custometData.EveryDayBill = everyDay.Where(x => x.渠道名称 == custometData.渠道名称).ToList();
            }
            return allChannelData;
        }

        public async Task<List<EFFinanceGroupBill>> GetEFFinanceBillGroupAsCustomer(DateTime start, DateTime end)
        {
            string sql = $@"
                                select [所属客户],
                                    sum(收入) 预计收入,
                                    count(0) 收货单量,
                                    sum(称重重量/1000.0) 收货重量,
								    sum(毛利) as 毛利,
								    sum(毛利)/sum(收入)毛利占比,
                                     sum(毛利)/count(0) 单件毛利,CustomerId
								from EFFinanceBillDetail
                                where 称重时间 between '{start}' and '{end.AddDays(1)}'
							    	and 收入>0
								    group by [所属客户],CustomerId
								    order by SUM([收入]) desc";
            var allCustomerData = (await _unitOfWork.QueryBySqlAsync<EFFinanceGroupBill>(sql, 360)).ToList();
            var everyDay = await GetEFFinanceBillCustomerEveryDay(start, end);
            foreach (var custometData in allCustomerData)
            {
                custometData.EveryDayBill = everyDay.Where(x => x.所属客户 == custometData.所属客户).ToList();
            }
            return allCustomerData;
        }



        public async Task<List<EFFinanceBill>> GetFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId)
        {
            string where = "";
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;
            string sql = $@"
                            select CONVERT(nvarchar(10),称重时间, 120)称重时间,sum(毛利) as 毛利,
                                   sum(收入)收入,								    
								    case when sum(isnull(收入,0))=0 then 0 else sum(毛利)/sum(收入) end 毛利占比 from FinanceBillDetail 
								where 称重时间 between '{start}' and '{end.AddDays(1)}' {where} 
							group by CONVERT(nvarchar(10),称重时间, 120)
							order by CONVERT(nvarchar(10),称重时间, 120)
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }

        public async Task<List<View_FinanceBill2>> GetFinanceBillDetails(DateTime start, DateTime end, int channelId, int customerId)
        {
            string where = "";
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;
            string sql = $@"
                            select * from FinanceBillDetail(nolock)
								where 称重时间 between '{start}' and '{end.AddDays(1)}' {where}  
							";
            return (await _unitOfWork.QueryBySqlAsync<View_FinanceBill2>(sql, 360)).ToList();
        }

        private string CreateFinanceReportDetailWhere(DateTime? receiveStart, DateTime? receiveTimeEnd, int? channelId, int? customerId, int? branchCompanyId, List<string> trackingNumbers, List<string> transferNumbers, List<string> ladingBillNumbers)
        {
            string where = "";
            if (channelId != null && channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != null && customerId != 0)
                where += " and  CustomerId = " + customerId;
            if (branchCompanyId != null && branchCompanyId != 0)
                where += " and  BranchCompanyId = " + branchCompanyId;
            if (trackingNumbers?.Any() == true)
            {
                var p = string.Join(",", trackingNumbers.Select(i => $"'{i}'"));
                where += $" and  跟踪号 in ({p})";
            }
            if (transferNumbers?.Any() == true)
            {
                var p = string.Join(",", transferNumbers.Select(i => $"'{i}'"));
                where += $" and  订单号 in ({p})";
            }
            if (ladingBillNumbers?.Any() == true)
            {
                var p = string.Join(",", ladingBillNumbers.Select(i => $"'{i}'"));
                where += $" and  提单号 in ({p})";
            }
            if (receiveStart.HasValue)
            {
                var start = receiveStart.Value.ToString("yyyy-MM-dd 00:00:00");
                var p = $" and 称重时间 >='{start}'";
                where += p;
            }
            if (receiveTimeEnd.HasValue)
            {
                var end = receiveTimeEnd.Value.AddDays(1);
                var endStr = end.ToString("yyyy-MM-dd 00:00:00");
                var p = $" and 称重时间 <'{endStr}'";
                where += p;

            }
            return where;
        }

        public async Task<List<View_FinanceBill_WithBranchCompany>> GetFinanceSimpleReportDetails(DateTime? receiveStart, DateTime? receiveTimeEnd, int? channelId, int? customerId, int? branchCompanyId, List<string> trackingNumbers, List<string> transferNumbers, List<string> ladingBillNumbers)
        {
            string where = CreateFinanceReportDetailWhere(receiveStart, receiveTimeEnd, channelId, customerId, branchCompanyId, trackingNumbers, transferNumbers, ladingBillNumbers);
            where += " and PublicChannelId not in(9,15,16,21,32,38,39,42,43,54,55,56,63,64,65) and CustomerId not in(227,294)";
            string sql = $@"select * from View_FinanceBill_WithBranchCompanySimple(nolock) where 1=1 {where} ";
            return (await _unitOfWork.QueryBySqlAsync<View_FinanceBill_WithBranchCompany>(sql, 360)).ToList();
        }

        public async Task<List<View_FinanceBill_WithBranchCompany>> GetFinanceReportDetails(DateTime? receiveStart, DateTime? receiveTimeEnd, int? channelId, int? customerId, int? branchCompanyId, List<string> trackingNumbers, List<string> transferNumbers, List<string> ladingBillNumbers)
        {
            string where = CreateFinanceReportDetailWhere(receiveStart, receiveTimeEnd, channelId, customerId, branchCompanyId,  trackingNumbers,  transferNumbers, ladingBillNumbers);
            string sql = $@"select * from View_FinanceBill_WithBranchCompany(nolock) where 1=1 {where} ";
            return (await _unitOfWork.QueryBySqlAsync<View_FinanceBill_WithBranchCompany>(sql, 360)).ToList();
        }

        public async Task<PageResult<View_FinanceBill_WithBranchCompany>> QueryFinanceReportDetailPage(QueryModel queryModel)
        {
            return await _unitOfWork.GetPagingListAsync<View_FinanceBill_WithBranchCompany>(queryModel);

        }

        public async Task<List<FinanceBillDetailWeightDifference>> GetDifferenceDetailsAsync(DateTime start, DateTime end, int channelId, int customerId)
        {
            string where = "";
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;

            string sql = $"select * from FinanceBillDetailWeightDifference(nolock)  where 称重时间 between '{start}' and '{end}' {where}";
            return (await _unitOfWork.QueryBySqlAsync<FinanceBillDetailWeightDifference>(sql)).ToList();
        }

        public async Task<FinanceBillDetailWeightDifferenceGroup> GetFinanceBillDetailWeightDifferenceGroup(DateTime start, DateTime end, int channelId, int customerId)
        {
            string where = "";
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;
            string sql = $@"select
	                        avg(末端派送差异) as 末端派送差异_票,
	                        avg(营运成本差异) as 营运成本差异_票,
	                        avg(一级中转差异) as 一级中转差异_票,
	                        avg(二级中转差异) as 二级中转差异_票,
	                        avg(出口报关差异) as 出口报关差异_票,
	                        avg(空运成本差异) as 空运成本差异_票,
	                        avg(海外清关差异) as 海外清关差异_票,
	                        avg(关税差异) as 关税差异_票,
	                        avg(海外中转差异) as 海外中转差异_票,
	                        avg(补贴差异) as 补贴差异_票,
	 
	                        sum(末端派送差异)/(sum(称重重量)/1000.0) as 末端派送差异_KG,
	                        avg(营运成本差异)/(sum(称重重量)/1000.0) as 营运成本差异_KG,
	                        avg(一级中转差异)/(sum(称重重量)/1000.0) as 一级中转差异_KG,
	                        avg(二级中转差异)/(sum(称重重量)/1000.0) as 二级中转差异_KG,
	                        avg(出口报关差异)/(sum(称重重量)/1000.0) as 出口报关差异_KG,
	                        avg(空运成本差异)/(sum(称重重量)/1000.0) as 空运成本差异_KG,
	                        avg(海外清关差异)/(sum(称重重量)/1000.0) as 海外清关差异_KG,
	                        avg(关税差异)/(sum(称重重量)/1000.0) as 关税差异_KG,
	                        avg(海外中转差异)/(sum(称重重量)/1000.0) as 海外中转差异_KG,
	                        avg(补贴差异)/(sum(称重重量)/1000.0) as 补贴差异_KG,

	                         case when  sum(末端派送)=0 then 1 else  sum(末端派送差异)/SUM(末端派送) end as 末端派送差异_比例,
	                          case when sum(营运成本)=0 then 1 else sum(营运成本差异)/SUM(营运成本)end as 营运成本差异_比例,
	                          case when sum(一级中转)=0 then 1 else sum(一级中转差异)/SUM(一级中转)end  as 一级中转差异_比例,
	                          case when sum(二级中转)=0 then 1 else sum(二级中转差异)/SUM(二级中转)end as 二级中转差异_比例,
	                          case when sum(出口报关)=0 then 1 else sum(出口报关差异)/SUM(出口报关)end as 出口报关差异_比例,
	                          case when sum(空运成本)=0 then 1 else sum(空运成本差异)/SUM(空运成本)end as 空运成本差异_比例,
	                          case when sum(海外清关)=0 then 1 else sum(海外清关差异)/SUM(海外清关)end as 海外清关差异_比例,
	                          case when sum(关税差异)=0 then 1 else sum(关税差异)/SUM(关税差异)end as 关税差异_比例,
	                          case when sum(海外中转)=0 then 1 else sum(海外中转差异)/SUM(海外中转)end as 海外中转差异_比例,
	                          case when sum(补贴)=0 then 1 else sum(补贴差异)/SUM(补贴)end  as 补贴差异_比例 
	                        from FinanceBillDetailWeightDifference(nolock)
                        where 称重时间 between '{start}' and '{end}' {where}
                        ";
            return (await _unitOfWork.QueryBySqlAsync<FinanceBillDetailWeightDifferenceGroup>(sql, 360)).FirstOrDefault();
        }

        public async Task<List<EFFinanceBill>> GetFinanceBillCustomerEveryDay(
            DateTime start,
            DateTime end)
        {
            string sql = $@"
                            select [所属客户],CONVERT(nvarchar(10),称重时间, 120)称重时间,sum(毛利) as 毛利,sum(收入)收入,								    
								    case when sum(isnull(收入,0))=0 then 0 else sum(毛利)/sum(收入) end 毛利占比 from FinanceBillDetail 
								where 称重时间 between '{start}' and '{end.AddDays(1)}'  
							group by CONVERT(nvarchar(10),称重时间, 120),[所属客户]
							order by CONVERT(nvarchar(10),称重时间, 120) 
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }

        public async Task<List<EFFinanceBill>> GetFinanceBillChannelEveryDay(
            DateTime start,
            DateTime end)
        {
            string sql = $@"
                            select [渠道名称],CONVERT(nvarchar(10),称重时间, 120)称重时间,sum(毛利) as 毛利,sum(收入)收入,								   
								    case when sum(isnull(收入,0))=0 then 0 else sum(毛利)/sum(收入) end 毛利占比 from FinanceBillDetail 
								where 称重时间 between '{start}' and '{end.AddDays(1)}' 
							group by CONVERT(nvarchar(10),称重时间, 120),[渠道名称]
							order by CONVERT(nvarchar(10),称重时间, 120) 
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }

        public async Task<List<EFFinanceGroupBill>> GetFinanceBillGroupAsChannel(DateTime start, DateTime end)
        {
            string sql = $@"
                                select [渠道名称],
								    sum(收入) 预计收入,
                                    count(0) 收货单量,
                                    sum(称重重量/1000.0) 收货重量,
								    sum(毛利) as 毛利,
								    case when sum(isnull(收入,0))=0 then 0 else sum(毛利)/sum(收入) end 毛利占比,
                                     sum(毛利)/count(0) 单件毛利,CreateOrderChannelId
								from FinanceBillDetail
                                where 称重时间 between '{start}' and '{end.AddDays(1)}' 
								    group by [渠道名称],CreateOrderChannelId
								    order by SUM([收入]) desc";
            var allChannelData = (await _unitOfWork.QueryBySqlAsync<EFFinanceGroupBill>(sql, 360)).ToList();
            var everyDay = await GetEFFinanceBillChannelEveryDay(start, end);
            foreach (var custometData in allChannelData)
            {
                custometData.EveryDayBill = everyDay.Where(x => x.渠道名称 == custometData.渠道名称).ToList();
            }
            return allChannelData;
        }

        public async Task<List<EFFinanceGroupBill>> GetFinanceBillGroupAsCustomer(DateTime start, DateTime end)
        {
            string sql = $@"
                                select [所属客户],
                                    sum(收入) 预计收入,
                                    count(0) 收货单量,
                                    sum(称重重量/1000.0) 收货重量,
								    sum(毛利) as 毛利,
								    case when sum(isnull(收入,0))=0 then 0 else sum(毛利)/sum(收入) end 毛利占比,
                                     sum(毛利)/count(0) 单件毛利,CustomerId
								from FinanceBillDetail
                                where 称重时间 between '{start}' and '{end.AddDays(1)}' 
								    group by [所属客户],CustomerId
								    order by SUM([收入]) desc";
            var allCustomerData = (await _unitOfWork.QueryBySqlAsync<EFFinanceGroupBill>(sql, 360)).ToList();
            var everyDay = await GetEFFinanceBillCustomerEveryDay(start, end);
            foreach (var custometData in allCustomerData)
            {
                custometData.EveryDayBill = everyDay.Where(x => x.所属客户 == custometData.所属客户).ToList();
            }
            return allCustomerData;
        }


        public async Task<List<View_FinanceBill2>> GetView_FinanceBills(int channelId, int customerId, DateTime start, DateTime end, SettlementCompany? SettlementCompany)
        {
            string where = "";
            //if (Finance_Companys.HasValue && Finance_Companys > 0)
            //{
            //    if (Finance_Companys == FinanceCompanys.行运易)
            //    {
            //        where += " and CustomerId <>1" +
            //            " and CreateOrderChannelId <> 48 and CreateOrderChannelId<>49 and CreateOrderChannelId<>50 and CreateOrderChannelId<>101 and CreateOrderChannelId<>106";
            //    }
            //    else if (Finance_Companys == FinanceCompanys.逸达)
            //    {
            //        where += " and CustomerId =1 and CreateOrderChannelId <> 48 and CreateOrderChannelId<>49 and CreateOrderChannelId<>50 and CreateOrderChannelId<>101 and CreateOrderChannelId<>106";
            //    }
            //    else if (Finance_Companys == FinanceCompanys.朗天)
            //    {
            //        where += " and (CreateOrderChannelId = 48 or CreateOrderChannelId = 49 or CreateOrderChannelId = 50 or CreateOrderChannelId = 101 or CreateOrderChannelId = 106)";
            //    }
            //}
            //else
            //{
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;
            if (SettlementCompany.HasValue)
                where += " and SettlementCompany = " + SettlementCompany.GetValue();

            //}
            string sql = $@"
                            select  
							 
	                         	e.PublicName as [渠道名称],f.CompanyName[所属客户],
	                           sum( case  when b.ConfirmedAmount = 0 or b.ConfirmedAmount is null
									then d.OPCFreight else b.ConfirmedAmount end  + isnull(b.AttachFee,0)) [收入],
	                            sum( case  when c.ConfirmedAmount = 0 or c.ConfirmedAmount is null 
									then d.OPSFreight else c.ConfirmedAmount end  * isnull(g.ToCNY,0)) [末端派送],  
	                            sum( isnull(ef.OperateCost,0))[营运成本],
                                sum( case when h.FirstTransferFeeCost = 0 or h.FirstTransferFeeCost is null
									then ef.FirstTransferPrice  * a.OPWeight /1000.0 else  h.FirstTransferFeeCost end) [一级中转],
                                sum( case when h.SecondTransferFeeCost = 0 or h.SecondTransferFeeCost is null
									then ef.SecondTransferPrice  * a.OPWeight /1000.0 else h.SecondTransferFeeCost end)  [二级中转],
                                sum( case when h.DomesticClearFeeCost = 0 or h.DomesticClearFeeCost is null
									then ef.ClearanceUnitWeightPrice * a.OPWeight /1000.0
									else h.DomesticClearFeeCost end) [出口报关],  
                                sum( case when h.FristFeeCost = 0 or h.FristFeeCost is null then
									ef. FirstLegUnitPrice * a.OPWeight/1000 else  h.FristFeeCost end ) [空运成本], 
                               sum(  case when h.OverseasClearFeeCost =0 or h.OverseasClearFeeCost is null
									then ef.ClearanceUnitWeightPrice * a.OPWeight/1000.0 * isnull(effc.ToCNY,0) else h.OverseasClearFeeCost * isnull(ocfc.ToCny,0) end)[清关成本],									
                                sum( case when h.TaxFeeCost = 0 or h.TaxFeeCost is null then
									 ef.TaxPrice * a.OPWeight /1000 * isnull(eftax.ToCNY,0) else isnull(h.TaxFeeCost,0)*isnull(tfc.ToCny,0) end) [关税],
                               sum( case when h.OverSeaTransferFeeCost =0 or h.OverseasClearFeeCost is null
								then ef.OverSeaTransferPrice * a.OPWeight/1000.0 * ISNULL(eftfc.ToCNY,0)
									else isnull(h.OverSeaTransferFeeCost,0)*isnull(ostfc.ToCny,0) end) [海外中转],
                               sum(  case when h.Subsidy =0 or h.Subsidy is null 
									then ef.SubsidyPrice * isnull(efsc.ToCNY,0) else isnull(h.Subsidy,0)*isnull(sc.ToCny,0) end)[补贴]
                            from Order_Order(nolock) a  
                        left join
                            (select sum(isnull( ConfirmedAmount,0)) as ConfirmedAmount,TrackingNumber,sum(isnull(AttachFee,0)) as AttachFee from Finance_CustomerDeliveryBillDetail(nolock)group by trackingnumber) b on a.TrackingNumber=b.TrackingNumber
                        left join
                            (select sum(isnull( ConfirmedAmount,0)) as ConfirmedAmount,TrackingNumber   from Finance_ServiceDeliveryBillDetail(nolock)group by trackingnumber) c on c.TrackingNumber=a.TrackingNumber
                        left join
                            Order_Fee(nolock) d on d.OrderId = a.Id
                        left join
	                        Logistics_Channel(nolock) e on e.id=a.CreateOrderChannelId
                        left join
	                        User_Customer(nolock) f on f.Id=a.CustomerId
                        left join 
	                        Finance_Rate(nolock) g on g.Currency = d.OPSCurreny
								and convert(date,a.ReceiveTime) between
								 convert(date,	g.StartDate) and convert(date, g.EndDate)
                        left join
	                        Finance_OrderCost(nolock) h on h.OrderId = a.Id
						left join Logistics_Public(nolock) lp on lp.Code = e.PublicCode
						left join Finance_Logistics_LadingBill(nolock) llb on llb.BillNumber = h.LadingBillNumber
                        outer apply( select top 1 * from Finance_EstimateFee(nolock) ef where ef.ChannelId = lp.Id and ef.EffectiveDate<a.ReceiveTime order by ef.EffectiveDate desc)ef
                        left join 
	                        Finance_Rate(nolock) ocfc on ocfc.Currency = h.OverseasClearFeeCurreny  and 
								convert(date, llb.OverseasClearBillTime)
									between  convert(date, ocfc.StartDate) and convert(date, ocfc.EndDate)
                        left join
	                        Finance_Rate(nolock) tfc on tfc.Currency = h.TaxFeeCurreny and 
							convert(date, llb.TaxBillTime) 
								between convert(date, tfc.StartDate) and convert(date, tfc.EndDate)
                        left join
	                        Finance_Rate(nolock) ostfc on ostfc.Currency = h.OverSeaTransferFeeCurreny and 
							convert(date, llb.OverSeaTransferBillTime) 
								between convert(date, ostfc.StartDate) and convert(date, ostfc.EndDate)
                        left join
	                        Finance_Rate(nolock) sc on sc.Currency = h.SubsidyCurreny and  
							convert(date, llb.SubsidyBillTime)
								between convert(date, sc.StartDate) and convert(date,sc.EndDate)
						---预计汇率
                        left join 
	                        Finance_Rate(nolock) effc on effc.Currency = ef.ClearancePriceCurrency  and 
								convert(date,a.ReceiveTime)
									between  convert(date, effc.StartDate) and convert(date, effc.EndDate)
                        left join
	                        Finance_Rate(nolock) eftax on eftax.Currency = ef.TaxPriceCurrency and 
							convert(date,a.ReceiveTime) 
								between convert(date, eftax.StartDate) and convert(date, eftax.EndDate)
                        left join
	                        Finance_Rate(nolock) eftfc on eftfc.Currency = ef.OverSeaTransferPriceCurrency  and 
							convert(date, a.ReceiveTime) 
								between convert(date, eftfc.StartDate) and convert(date, eftfc.EndDate)
                        left join
	                        Finance_Rate(nolock) efsc on efsc.Currency = ef.SubsidyPriceCurrency and  
							convert(date, a.ReceiveTime)
								between convert(date, efsc.StartDate) and convert(date,efsc.EndDate)
	
	

                            where a.ReceiveTime between '{start.ToString_yyyyMMdd()}' and '{end.ToString_yyyyMMdd()}' {where}
	                        and a.OrderStatus>1
                            group by e.PublicName,f.CompanyName ";
            return (await _unitOfWork.QueryBySqlAsync<View_FinanceBill2>(sql, 360)).ToList();
        }

        public async Task<List<Finance_Logistics_LadingBillLog>> GetLadingBillLogs(int BillId, FinanceIncidentalTypeEnum type)
        {
            string name = "";
            switch (type)
            {
                case FinanceIncidentalTypeEnum.头程杂费:
                    name = "PaymentFristFee";
                    break;
                case FinanceIncidentalTypeEnum.国内清关杂费:
                    name = "PaymentDomesticClearFee";
                    break;
                case FinanceIncidentalTypeEnum.国外清关杂费:
                    name = "PaymentOverseasClearFee";
                    break;
                case FinanceIncidentalTypeEnum.收款:
                    name = "CusetomerCheques";
                    break;
                default:
                    name = "";
                    break;
            }

            string sql = $@"
                        select a.OldValue,a.NewValue,a.CreateTime ,b.NewValue as Remark from Finance_Logistics_LadingBillLog(nolock) a left join Finance_Logistics_LadingBillLog(nolock)b on a.CreateTime=b.CreateTime and b.field= 'Remark' 
                        where a.Id={BillId} and a.Field = 'PaymentFristFee' ";
            var list = (await _unitOfWork.QueryBySqlAsync<Finance_Logistics_LadingBillLog>(sql)).ToList();
            return list;
        }

        public Task<List<EFFinanceBill>> GetEFFinanceBillGroup(DateTime start, DateTime end)
        {
            throw new NotImplementedException();
        }

        public async Task<List<CompletionRate>> GetCompletionRate(DateTime start, DateTime end, int channelId, int customerId)
        {
            string where = "";
            if (channelId != 0)
                where += " and CreateOrderChannelId = " + channelId;
            if (customerId != 0)
                where += " and CustomerId = " + customerId;
            string sql = $@"
                                select 
                                      sum(case when 末端派送 =0 then 0 else 1 end) /convert(decimal(18,2), count(0))  as 末端派送,
                                      sum(case when 营运成本 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 营运成本,
                                      sum(case when 一级中转 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 一级中转,
                                      sum(case when 二级中转 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 二级中转,
                                      sum(case when 出口报关 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 出口报关,
                                      sum(case when 空运成本 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 空运成本,
                                      sum(case when 海外清关 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 海外清关,
                                      sum(case when 关税 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 关税,
                                      sum(case when 海外中转 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 海外中转,
                                      sum(case when 补贴 =0 then 0 else 1 end) / convert(decimal(18,2), count(0))  as 补贴
								from FinanceBillDetail
                                where 称重时间 between '{start}' and '{end.AddDays(1)} {where}' 
								     ";
            var allCustomerData = (await _unitOfWork.QueryBySqlAsync<CompletionRate>(sql, 360)).ToList();
            return allCustomerData;
        }
        public async Task UpdateFirstBillCusetomerChequesStatus(int CustomerId, DateTime start, DateTime end)
        {
            string aboutCusetomerChequesSql = string.Empty;
            int CusetomerChequesStatus = (int)PaymentStatus.结款中;
            string sql = @$" update Finance_Logistics_LadingBill set CusetomerChequesStatus={CusetomerChequesStatus}{aboutCusetomerChequesSql}
 from (select BillNo from Finance_ChargesDetail where Offset=0 and CustomerId=@CustomerId and AccountingDate>=@start and AccountingDate<@end and IncomeOrESxpense='支出'  and BillType='客户头程账单费用' 
) b
 where Finance_Logistics_LadingBill.BillNumber=b.BillNo ";
            await _unitOfWork.ExecuteAsync(sql, new { CustomerId = CustomerId, start = start, end = end });
        }
        public async Task CompleteFirstBillCusetomerChequesStatus(int CustomerId, DateTime end)
        {
            string aboutCusetomerChequesSql = string.Empty;
            int CusetomerChequesStatus = (int)PaymentStatus.结款结束;
            string sql = @$" update Finance_Logistics_LadingBill set CusetomerChequesStatus={CusetomerChequesStatus},CusetomerCheques=CusetomerFee
 from (select BillNo from Finance_ChargesDetail b left join Finance_Logistics_LadingBill a on b.BillNo=a.BillNumber where b.Offset=0 and b.CustomerId=@CustomerId  and b.AccountingDate<@end and b.IncomeOrESxpense='支出'  and b.BillType='客户头程账单费用' and a.CusetomerChequesStatus={(int)PaymentStatus.结款中}
) b
 where Finance_Logistics_LadingBill.BillNumber=b.BillNo ";
            await _unitOfWork.ExecuteAsync(sql, new { CustomerId = CustomerId, end = end });
        }
        public async Task<IEnumerable<Finance_Logistics_LadingBill>> GetLadingBillByChargesDetail(DateTime end, int customerId)
        {
            string sql = @$" select a.* from Finance_Logistics_LadingBill(nolock) a 
 left join Finance_ChargesDetail(nolock) b on b.BillNo=a.BillNumber
  where b.Offset=0 and b.CustomerId=@customerId and b.AccountingDate<@end and b.IncomeOrESxpense='支出'  and b.BillType='客户头程账单费用'
  and a.CusetomerChequesStatus={(int)PaymentStatus.结款中}
  order by a.id desc";
            return await _unitOfWork.QueryBySqlAsync<Finance_Logistics_LadingBill>(sql, null, new { customerId = customerId, end = end });
        }
    }
}