
using Dapper;
using iTextSharp.text.pdf.parser.clipper;
using Microsoft.Office.Interop.Excel;
using Nest;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;
using XYY.Core.Standard.Mvc;
using XYY.Data.Standard.Channel;
using XYY.Data.Standard.Finance;
using XYY.Data.Standard.Order;
using XYY.Model.Standard;
using XYY.Model.Standard.Enums;
using XYY.Model.Standard.Finance;
using XYY.Model.Standard.Order;
using XYY.Model.Standard.User;
using XYY.Model.Standard.User.Enums;
using XYY.Service.Standard.Customer;
using static XYY.Service.Standard.Finance.CustomerDeliveyBillService;

namespace XYY.Service.Standard.Finance
{
    public interface ICustomerDeliveyBillService
    {
        Task<CollectDeliveryBillResult> AddDeliveryBill(Finance_CustomerDeliveryBill bill);

        Task<CollectDeliveryBillResult> ReProcessDeliveryBill(Finance_CustomerDeliveryBill bill);
        

        Task<AddDeliveryBillResult> AddDeliveryBillAsFile(Finance_CustomerDeliveryBill bill);
        Task DeleteBill(int billId);
        Task<List<Finance_CustomerDeliveryBillDetail>> GetConfirmeds(int billId);
        Task<byte[]> GetConfirmedsData(int billId);
        Task<List<Finance_CustomerDeliveryBill>> GetDataAsStatus(int status);
        Task<List<Finance_CustomerDeliveryBillDetail>> GetDeductions(int billId);
        Task<byte[]> GetDeductionsData(int billId);
        Task<byte[]> GetClaimData(int billId);
        Task<Dictionary<int, int>> GetGroupCounts();
        Task<List<Finance_CustomerDeliveryBillDetail>> GetNotConfirmeds(int billId);
        Task<byte[]> GetNotConfirmedsData(int billId);
        Task<List<Finance_CustomerDeliveryBillDetail>> GetOriginals(int billId);
        Task<byte[]> GetOriginalsData(int billId);
        Task<List<Finance_Logistics_LadingBillLogDto>> GetPaymentLog(int billId);
        Task<List<Finance_CustomerDeliveryBillDetail>> GetSupplements(int billId);
        Task<List<WaitCreateBillCustomer>> GetWaitCreateBill(int billType);
        Task<byte[]> GetSupplementsData(int billId);
        Task InputSupplementOrDeduction(int billId, string url, int type);
        Task InputWeightAndCalculation(int billId, string url);
        List<Finance_CustomerDeliveryBillDetail> LoadDetailInput(string url);
        List<Finance_CustomerDeliveryBillDetailInput> LoadDetailSimpleInput(string url);
        byte[] OutData(List<Finance_CustomerDeliveryBillDetail> list);
        byte[] OutDataSimple(List<Finance_CustomerDeliveryBillDetail> list);

        Task<byte[]> GetOutCustomer(int billId, int outType, bool isYC = false);
        Task Pass(int billId, bool isRealTime = false);
        Task PostPayment(int Id, decimal PostPayment);
        Task Rereject(int billId);
        Task UpdateConfirmed(int billId, string url);
        Task CalcBillClaimedAmount(int billId, DateTime claimConfirmFrom, DateTime claimConfirmTo);
        Task<PageResult<Finance_CustomerDeliveryBill>> GetRemittancePageResult(QueryModel queryModel);

        Task<CustomerDeliveryPanel> GetCustomerDeliveryPanelAsync();
        Task<CustomerDeliveryPanelDetail> GetCustomerDeliveryPanelDetail();
        Task<byte[]> DownloadRemittance(QueryModel queryModel);
        Task<ApiJsonModel> GenerateFirstBills(int firstCustomer = 0, DateTime? From = null, DateTime? To = null);
    }

    public class CustomerDeliveyBillService : ICustomerDeliveyBillService
    {
        IFinance_CustomerDeliveryBillRepository _deliveyBillRepository;
        IExcelHelper _excelHelper;
        IView_CustomerBillClaimDetailRepository _customerBillClaimDetailRepository;
        IFinance_CustomerDeliveryBillRepository _customerDeliveryBillRepository;
        IFinance_Customer_SupplementService _supplementService;
        ILogistics_ChannelRepository _channelRepository;
        ILogistics_ClaimRecordRepository _claimRecordRepository;
        ILogistics_PublicRepository _publicRepository;
        ICustomerService _customerService;
        IOrderRepository _orderRepository;
        IUnitOfWork _unitOfWork;
        IFinance_Logistics_LadingBillRepository _ladingBillRepository;

        public CustomerDeliveyBillService(IFinance_CustomerDeliveryBillRepository deliveyBillRepository,
            IExcelHelper excelHelper,
            IView_CustomerBillClaimDetailRepository customerBillClaimDetailRepository,
            IFinance_Customer_SupplementService supplementService,
              ILogistics_ChannelRepository channelRepository,
               ILogistics_ClaimRecordRepository claimRecordRepository,
               ILogistics_PublicRepository publicRepository,
               IOrderRepository orderRepository,
                ICustomerService customerService, IUnitOfWork unitOfWork
            , IFinance_Logistics_LadingBillRepository ladingBillRepository, IFinance_CustomerDeliveryBillRepository customerDeliveryBillRepository)
        {
            _deliveyBillRepository = deliveyBillRepository;
            if (!System.IO.Directory.Exists(tempPath))
                System.IO.Directory.CreateDirectory(tempPath);
            _excelHelper = excelHelper;
            _orderRepository = orderRepository;
            _customerBillClaimDetailRepository = customerBillClaimDetailRepository;
            _supplementService = supplementService;
            _channelRepository = channelRepository;
            _claimRecordRepository = claimRecordRepository;
            _publicRepository = publicRepository;
            _customerService = customerService;
            _unitOfWork = unitOfWork;
            _ladingBillRepository = ladingBillRepository;
            _customerDeliveryBillRepository = customerDeliveryBillRepository;
        }


        public async Task<CustomerDeliveryPanel> GetCustomerDeliveryPanelAsync()
        {
            return await _deliveyBillRepository.GetCustomerDeliveryPanelAsync();
        }
        public List<Finance_CustomerDeliveryBillDetail> LoadDetailInput(string url)
        {
            string filePath = tempPath + "\\" + Guid.NewGuid() + ".xlsx";
            System.Net.WebClient webClient = new System.Net.WebClient();
            byte[] data = webClient.DownloadData(url);
            return _excelHelper.LoadDataByByte<OutDataInfo, Finance_CustomerDeliveryBillDetail>(data);
        }

        public List<Finance_CustomerDeliveryBillDetailInput> LoadDetailSimpleInput(string url)
        {
            string filePath = tempPath + "\\" + Guid.NewGuid() + ".xlsx";
            System.Net.WebClient webClient = new System.Net.WebClient();
            byte[] data = webClient.DownloadData(url);
            return _excelHelper.LoadDataByByte<InputDataSimpleInfo, Finance_CustomerDeliveryBillDetailInput>(data);
        }

        public async Task<CollectDeliveryBillResult> AddDeliveryBill(Finance_CustomerDeliveryBill bill)
        {
            var user = (await _customerService.CacheGetAllCustomers()).FirstOrDefault(x => x.Id == bill.CustomerId);
            bill.DeductionNode = (int)user.DeductionNode;
            bool ss = user.DeductionNode == DeductionNode.实时扣费;
            CollectDeliveryBillResult result = new CollectDeliveryBillResult();
            try
            {
                 await _deliveyBillRepository.AddDeliveryBill(bill, ss);
                if (user.DeductionNode != DeductionNode.实时扣费)
                {
                    int days = DateTime.DaysInMonth(bill.EndTime.Year, bill.EndTime.Month);

                    List<Finance_CustomerDeliveryBillDetailInput> customerDeliveryBillDetailInputs = new List<Finance_CustomerDeliveryBillDetailInput>();
                    //fba运费
                    var fabFee =
                        (await _unitOfWork.QueryBySqlAsync<Finance_ChargesDetail>(
                    $@"select BillingWeight,AmountinOriginalCurrency,Id,Remark,FeeDescription,BillNo from Finance_ChargesDetail(nolock) where  
                        TransactionTime >= '{bill.StartTime.ToString_yyyyMMdd()}' AND
                        TransactionTime <= '{bill.EndTime.AddDays(1)}'
                        AND  (BillType = N'FBA杂费' )  AND  (CustomerId = {bill.CustomerId} ) AND Offset=0")).ToList();

                    var fbaFeeInsert = fabFee.Select(x => new Finance_CustomerDeliveryBillDetailInput
                    {
                        Weight = (decimal)x.BillingWeight,
                        Amount = (decimal)x.AmountinOriginalCurrency,
                        ConfirmedAmount = (decimal)x.AmountinOriginalCurrency,
                        BillId = bill.Id,
                        Remark = x.FeeDescription + " " + x.Remark,
                        FinanceCustomerSupplementType = FinanceCustomerSupplementOrDeductionType.补_FBA杂费,
                        OrderRemark = String.Empty,
                        TrackingNumber = x.BillNo
                    });
                    customerDeliveryBillDetailInputs.AddRange(fbaFeeInsert);

                    //如果帐单时间是该月最后一天，我们添加该月所有的杂费项
                    string month = bill.EndTime.ToString("yyyy-MM");
                    //杂费
                    var fee1 = await _supplementService.Load_Customer_Supplement(bill.CustomerId, FinanceCustomerSupplementOrDeductionType.补_杂费, bill.StartTime, bill.EndTime);
                    //退件费
                    var fee2 = await _supplementService.Load_Customer_Supplement(bill.CustomerId, FinanceCustomerSupplementOrDeductionType.补_退件费, bill.StartTime, bill.EndTime);
                    //重派费
                    var fee3 = await _supplementService.Load_Customer_Supplement(bill.CustomerId, FinanceCustomerSupplementOrDeductionType.补_重派费, bill.StartTime, bill.EndTime);
                    //退回退件费(仓库退给客户)
                    var fee4 = await _customerDeliveryBillRepository.Load_Return_Fee(bill.CustomerId, bill.StartTime, bill.EndTime);

                    var fees = new List<Finance_Customer_Supplement>();
                    fees.AddRange(fee1);
                    fees.AddRange(fee2);
                    fees.AddRange(fee3);
                    customerDeliveryBillDetailInputs.AddRange(fees.Select(x => new Finance_CustomerDeliveryBillDetailInput
                    {
                        Weight = 0,
                        Amount = x.Fee,
                        ConfirmedAmount = x.Fee,
                        BillId = bill.Id,
                        Remark = x.Describe ?? x.OrderRemark,
                        FinanceCustomerSupplementType = x.FinanceCustomerSupplementType,
                        OrderRemark = x.OrderRemark,
                        TrackingNumber = x.TrackingNumber
                    }));
                    customerDeliveryBillDetailInputs.AddRange(fee4.Select(x => new Finance_CustomerDeliveryBillDetailInput
                    {
                        Weight = 0,
                        Amount = x.ConfirmedAmount > 0 ? x.ConfirmedAmount + x.ConfirmedEUTariff + x.AttachFee : x.Amount + x.EUTariff + x.Amount,
                        ConfirmedAmount = x.ConfirmedAmount > 0 ? x.ConfirmedAmount + x.ConfirmedEUTariff + x.AttachFee : x.Amount + x.EUTariff + x.Amount,
                        BillId = bill.Id,
                        Remark = "仓库退件",
                        FinanceCustomerSupplementType = FinanceCustomerSupplementOrDeductionType.扣_退件退回,
                        CreateTime = DateTime.Now,
                        OrderRemark = String.Empty,
                        TrackingNumber = x.TrackingNumber
                    }));
                    await _deliveyBillRepository.InputSupplementOrDeduction(bill.Id,
                        customerDeliveryBillDetailInputs.Where(x => x.FinanceCustomerSupplementType.ToString().StartsWith("补")).ToList(), 2, true);
                    await _deliveyBillRepository.InputSupplementOrDeduction(bill.Id,
                        customerDeliveryBillDetailInputs.Where(x => x.FinanceCustomerSupplementType.ToString().StartsWith("扣")).ToList(), 1, true);
                }
                else
                {
                    //杂费
                    var fee1 = await _supplementService.Load_Customer_Supplement(bill.CustomerId,
                        FinanceCustomerSupplementOrDeductionType.补_杂费, bill.StartTime, bill.EndTime);
                    //退件费
                    var fee2 = await _supplementService.Load_Customer_Supplement(bill.CustomerId,
                        FinanceCustomerSupplementOrDeductionType.补_退件费, bill.StartTime, bill.EndTime);
                    //重派费
                    var fee3 = await _supplementService.Load_Customer_Supplement(bill.CustomerId, FinanceCustomerSupplementOrDeductionType.补_重派费,
                        bill.StartTime, bill.EndTime);
                    //扣_重量差异费
                    var fee4 = await _supplementService.Load_Customer_Supplement(bill.CustomerId, FinanceCustomerSupplementOrDeductionType.扣_重量差异费,
                        bill.StartTime, bill.EndTime);
                    //扣_时间差异费
                    var fee5 = await _supplementService.Load_Customer_Supplement(bill.CustomerId, FinanceCustomerSupplementOrDeductionType.扣_时间差异费,
                        bill.StartTime, bill.EndTime);
                    //扣_杂费
                    var fee6 = await _supplementService.Load_Customer_Supplement(bill.CustomerId, FinanceCustomerSupplementOrDeductionType.扣_杂费,
                        bill.StartTime, bill.EndTime);
                    //扣_索赔
                    var fee7 = await _supplementService.Load_Customer_Supplement(bill.CustomerId, FinanceCustomerSupplementOrDeductionType.扣_索赔,
                        bill.StartTime, bill.EndTime);

                    var fees = new List<Finance_Customer_Supplement>();
                    fees.AddRange(fee1);
                    fees.AddRange(fee2);
                    fees.AddRange(fee3);
                    fees.AddRange(fee4);
                    fees.AddRange(fee5);
                    fees.AddRange(fee6);
                    fees.AddRange(fee7);

                    List<Finance_CustomerDeliveryBillDetailInput> customerDeliveryBillDetailInputs = new List<Finance_CustomerDeliveryBillDetailInput>();
                    customerDeliveryBillDetailInputs.AddRange(fees.Select(x => new Finance_CustomerDeliveryBillDetailInput
                    {
                        Weight = 0,
                        Amount = x.Fee,
                        ConfirmedAmount = x.Fee,
                        BillId = bill.Id,
                        Remark = x.Describe ?? x.OrderRemark,
                        FinanceCustomerSupplementType = x.FinanceCustomerSupplementType,
                        OrderRemark = x.OrderRemark,
                        TrackingNumber = x.TrackingNumber
                    }));
                    await _deliveyBillRepository.InputSupplementOrDeduction(bill.Id,
                        customerDeliveryBillDetailInputs.Where(x => x.FinanceCustomerSupplementType.ToString().StartsWith("扣_索赔")).ToList(), 3);

                    await _deliveyBillRepository.InputSupplementOrDeduction(bill.Id,
                        customerDeliveryBillDetailInputs.Where(x => x.FinanceCustomerSupplementType.ToString().StartsWith("补")).ToList(), 2);

                    await _deliveyBillRepository.InputSupplementOrDeduction(bill.Id,
                        customerDeliveryBillDetailInputs.Where(x => x.FinanceCustomerSupplementType.ToString() != "扣_索赔" &&
                        x.FinanceCustomerSupplementType.ToString().StartsWith("扣")).ToList(), 1);
                    await _deliveyBillRepository.Pass(bill.Id, true, true);
                }
                //带更新状态
                await _deliveyBillRepository.UpdateCustomerDeliveryBillState(bill.Id, BillState.finished,false);
                result.Success = true; 
            } 
            catch (Exception e)
            {
                _unitOfWork.Rollback();

                await _deliveyBillRepository.UpdateCustomerDeliveryBillState(bill.Id, BillState.exception,true);
                result.Success= false;
                result.Message = e.Message;
            }
            return result;
        }

        public async Task<CollectDeliveryBillResult> ReProcessDeliveryBill(Finance_CustomerDeliveryBill bill)
        {
            await _deliveyBillRepository.UpdateCustomerDeliveryBillState(bill.Id, BillState.reProcess,false);
            //删除账单明细
            await _deliveyBillRepository.DeleteDetails(bill.Id);
            var data = await AddDeliveryBill(bill);
            return data;
        }


        public class InputBillAsFileMap : MSExcelClassMapping<Finance_CustomerDeliveryBillDetailInput2>
        {
            public InputBillAsFileMap()
            {
                Map(x => x.TrackingNumber).Name("跟踪号");
                Map(x => x.Weight).Name("实际称重(kg)").Formattext(ExcelFormattextHelper.DefaultAmount);
                Map(x => x.Amount).Name("称重计费");
                Map(x => x.ConfirmedAmount).Name("称重计费");
                Map(x => x.Remark).Name("备注");
                Map(x => x.Surcharge).Name("附加费");
            }
        }

        public async Task<AddDeliveryBillResult> AddDeliveryBillAsFile(Finance_CustomerDeliveryBill bill)
        {
            string filePath = tempPath + "\\" + Guid.NewGuid() + ".xlsx";
            System.Net.WebClient webClient = new System.Net.WebClient();
            byte[] data = webClient.DownloadData(bill.ServiceBillUrl);
            var list = _excelHelper.LoadDataByByte<InputBillAsFileMap, Finance_CustomerDeliveryBillDetailInput2>(data);
            foreach (var x in list)
            {
                if (x.TrackingNumber.Length == 9 && (x.TrackingNumber.StartsWith("7") || x.TrackingNumber.StartsWith("8")))
                {
                    x.TrackingNumber = "CT" + x.TrackingNumber + "CN";
                }
            }

            string fileName = bill.ServiceBillUrl.Substring(bill.ServiceBillUrl.LastIndexOf('/') + 1);
            fileName = fileName.Substring(0, fileName.LastIndexOf('.'));
            bill.Name = fileName;
            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"(\d*\.\d*)");
            if (!regex.IsMatch(bill.Name))
            {
                throw new Exception("帐单中包含日期，如【行运易-震宇8.19-8.31账单】");
            }
            else
            {
                var group = regex.Matches(bill.Name);
                DateTime start = new DateTime(2020, int.Parse(group[0].Groups[1].Value.Split('.')[0]), int.Parse(group[0].Groups[1].Value.Split('.')[1]));
                DateTime end = new DateTime(2020, int.Parse(group[1].Groups[1].Value.Split('.')[0]), int.Parse(group[1].Groups[1].Value.Split('.')[1]));
                bill.StartTime = start;
                bill.EndTime = end;
            }

            return await _deliveyBillRepository.AddDeliveryBillAsFile(bill, list);
        }

        public async Task<List<Finance_CustomerDeliveryBillDetail>> GetConfirmeds(int billId)
        {
            return await _deliveyBillRepository.GetDetail(billId, 0, true);
        }


        public async Task<byte[]> GetConfirmedsData(int billId)
        {
            var list = await GetConfirmeds(billId);
            return OutData(list);
        }



        public async Task<List<Finance_CustomerDeliveryBill>> GetDataAsStatus(int status)
        {
            return await _deliveyBillRepository.GetDataAsStatus(status);
        }

        public async Task<List<Finance_CustomerDeliveryBillDetail>> GetDeductions(int billId)
        {
            return await _deliveyBillRepository.GetDetail(billId, 1, null);
        }

        public async Task<byte[]> GetDeductionsData(int billId)
        {
            var list = await GetDeductions(billId);
            return OutDataSimple(list);
        }



        public async Task<byte[]> GetClaimData(int billId)
        {
            var list = await _deliveyBillRepository.GetDetail(billId, (int)ServiceFeeType.索赔, null);
            return OutDataSimple(list);
        }


        public async Task<Dictionary<int, int>> GetGroupCounts()
        {
            return await _deliveyBillRepository.GetGroupCounts();
        }

        public async Task<List<Finance_CustomerDeliveryBillDetail>> GetNotConfirmeds(int billId)
        {
            return await _deliveyBillRepository.GetDetail(billId, 0, false);
        }


        public async Task<byte[]> GetNotConfirmedsData(int billId)
        {
            var list = await GetNotConfirmeds(billId);
            return OutData(list);
        }



        public async Task<List<Finance_CustomerDeliveryBillDetail>> GetOriginals(int billId)
        {
            return await _deliveyBillRepository.GetDetail(billId, 0, null);
        }



        readonly string tempPath = AppContext.BaseDirectory + "\\temp";
        public async Task<byte[]> GetOriginalsData(int billId)
        {
            var list = await GetOriginals(billId);
            return OutData(list);
        }


        public byte[] OutData(List<Finance_CustomerDeliveryBillDetail> list)
        {
            return _excelHelper.OutDataAsByte<OutDataInfo, Finance_CustomerDeliveryBillDetail>(list);
        }

        public class TJDataInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public TJDataInfo()
            {
                Map(x => x.No).Name("序号");
                Map(x => x.WeightTime).Name("日期");
                Map(x => x.CustomerName).Name("客户");
                Map(x => x.ChannelName).Name("渠道");
                Map(x => x.CustomerOrderNo).Name("客单号");
                Map(x => x.SystemNo).Name("内部单号");
                Map(x => x.TrackingNumber).Name("运单号");
                Map(x => x.OrderRemark).Name("退件原因");
                Map(x => x.Amount).Name("金额");
                Map(x => x.Remark).Name("明细");
            }
            public override int RemarkIndex()
            {
                return 7;
            }
        }


        public class SPDataInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public SPDataInfo()
            {
                Map(x => x.No).Name("序号");
                Map(x => x.CustomerName).Name("客户名称");
                Map(x => x.ChannelName).Name("渠道");
                Map(x => x.TrackingNumber).Name("跟踪号");
                Map(x => x.TransferNumber).Name("中转单号");
                Map(x => x.CustomerOrderNo).Name("客户单号");
                Map(x => x.WeightTime).Name("称重时间");
                Map(x => x.Amount).Name("成本");
                Map(x => x.GoodsDeclaredVal).Name("申报价值");
                Map(x => x.GoodsDeclaredCurrency).Name("申报币种");
                Map(x => x.AttachFee).Name("运费");
                Map(x => x.GoodsDeclaredVal).Name("申报价值（人民币）");
                Map(x => x.ConfirmedAmount).Name("总金额");
                Map(x => x.OrderRemark).Name("索赔方案");
                Map(x => x.Remark).Name("索赔原因");
            }
            public override int RemarkIndex()
            {
                return 0;
            }
        }

        public class CPDataInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public CPDataInfo()
            {
                Map(x => x.No).Name("序号");
                Map(x => x.CustomerName).Name("客户");
                Map(x => x.ChannelName).Name("客户渠道");
                Map(x => x.CustomerOrderNo).Name("客单号");
                Map(x => x.SystemNo).Name("中转单号");
                Map(x => x.TrackingNumber).Name("跟踪号");
                Map(x => x.WeightTime).Name("称重时间");
                Map(x => x.OrderRemark).Name("重派单号");
                Map(x => x.Remark).Name("明细");
                Map(x => x.Weight).Name("计费重量");
                Map(x => x.Amount).Name("金额");
            }
            public override int RemarkIndex()
            {
                return 9;
            }
        }
        public class CKTJDataInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public CKTJDataInfo()
            {
                Map(x => x.No).Name("序号");
                Map(x => x.CustomerName).Name("客户");
                Map(x => x.ChannelName).Name("渠道");
                Map(x => x.WeightTime).Name("称重时间");
                Map(x => x.CustomerOrderNo).Name("客单号");
                Map(x => x.SystemNo).Name("内部单号");
                Map(x => x.TransferNumber).Name("转单号");
                Map(x => x.TrackingNumber).Name("跟踪号");
                Map(x => x.Weight).Name("实际称重");
                Map(x => x.Amount).Name("金额");
                Map(x => x.Remark).Name("明细");
            }
            public override int RemarkIndex()
            {
                return 8;
            }
        }
        public class ZFDataInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public ZFDataInfo()
            {
                Map(x => x.No).Name("序号");
                Map(x => x.CustomerName).Name("客户");
                Map(x => x.ChannelName).Name("客户渠道");
                Map(x => x.CustomerOrderNo).Name("客单号");
                Map(x => x.SystemNo).Name("中转单号");
                Map(x => x.TrackingNumber).Name("跟踪号");
                Map(x => x.Remark).Name("明细");
                Map(x => x.Amount).Name("金额");
            }
            public override int RemarkIndex()
            {
                return 6;
            }
        }

        public class SRDataInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public SRDataInfo()
            {
                Map(x => x.No).Name("序号");
                Map(x => x.TrackingNumber).Name("收款单号");
                Map(x => x.OrderRemark).Name("付款类型");
                Map(x => x.WeightTime).Name("付款时间");
                Map(x => x.AttachFee).Name("支付金额");
                Map(x => x.GoodsDeclaredCurrency).Name("支付币种");
                Map(x => x.EUTariffRate).Name("结算汇率");
                Map(x => x.Amount).Name("结算金额(￥)");
            }
            public override int RemarkIndex()
            {
                return 6;
            }
        }

        public class FBADataInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public FBADataInfo()
            {
                Map(x => x.No).Name("序号");
                Map(x => x.WeightTime).Name("发货日期");
                Map(x => x.CustomerOrderNo).Name("客单号");
                Map(x => x.CustomerName).Name("客户");
                Map(x => x.TrackingNumber).Name("跟踪单号");
                Map(x => x.ChannelName).Name("物流渠道");
                Map(x => x.CountryCode).Name("目的国家");
                Map(x => x.ZipCode).Name("邮编");
                //Map(x => x.Empty1).Name("品名");
                Map(x => x.BoxQty).Name("件数");
                Map(x => x.SysWeight).Name("称重重量");
                Map(x => x.Weight).Name("计费重/立方数");
                Map(x => x.OPCServiceCharge).Name("单价");
                Map(x => x.SysAmount).Name("运费");
                Map(x => x.AttachFee).Name("产品性质附加费");
                Map(x => x.SumAmount).Name("总金额");
                Map(x => x.OtherFee).Name("费用说明");
            }
        }

        public class FBADataYCInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public FBADataYCInfo()
            {
                Map(x => x.ZT).Name("货代名称");
                Map(x => x.WeightTime).Name("收货日期");
                Map(x => x.CustomerOrderNo).Name("Shipment ID（FBA#)");
                Map(x => x.TrackingNumber).Name("跟踪单号");
                Map(x => x.ChannelName).Name("物流渠道");
                Map(x => x.CountryCode).Name("目的国家");
                Map(x => x.ZipCode).Name("仓库代码/邮编");
                Map(x => x.BoxQty).Name("件数");
                Map(x => x.SysWeight).Name("计费重/立方数");
                Map(x => x.OPCServiceCharge).Name("单价");
                Map(x => x.SysAmount).Name("运费");
                Map(x => x.EUTariff).Name("关税");
                Map(x => x.AttachFee).Name("产品性质附加费");
                Map(x => x.Empty1).Name("出口报关费");
                Map(x => x.Empty2).Name("进口清关费");
                Map(x => x.Empty3).Name("商检费");
                Map(x => x.Empty4).Name("其他");
                Map(x => x.SumAmount).Name("总金额");
                Map(x => x.OtherFee).Name("费用说明");
            }
        }

        public class OutDataInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {

            public OutDataInfo()
            {
                Map(x => x.CustomerOrderNo).Name("客户单号");
                Map(x => x.CustomerName).Name("客户");
                Map(x => x.DeliveryChannelName).Name("服务渠道");
                Map(x => x.ChannelName).Name("下单渠道");
                Map(x => x.CountryCode).Name("目的国家");
                Map(x => x.City).Name("城市");
                Map(x => x.ZipCode).Name("邮编");
                Map(x => x.WeightTime).Name("称重时间");
                Map(x => x.SystemNo).Name("内部单号");
                Map(x => x.TransferNumber).Name("转单号");
                Map(x => x.TrackingNumber).Name("跟踪号");
                Map(x => x.OPCVersion).Name("报价版本");
                Map(x => x.OPCServiceCharge).Name("公斤价");
                Map(x => x.OPCOperationFee).Name("操作费");
                Map(x => x.CustomerWeight).Name("客户重量").Formattext(ExcelFormattextHelper.DefaultAmount);
                Map(x => x.SysWeight).Name("实际称重").Formattext(ExcelFormattextHelper.DefaultAmount);
                Map(x => x.Amount).Name("金额");
                Map(x => x.SysAmount).Name("称重计费");
                Map(x => x.ConfirmedAmount).Name("确认金额");
                Map(x => x.Weight).Name("确认重量").Formattext(ExcelFormattextHelper.DefaultAmount);
                Map(x => x.Length).Name("长");
                Map(x => x.Width).Name("宽");
                Map(x => x.Height).Name("高");
                Map(x => x.AttachFee).Name("附加费");
                Map(x => x.EUTariff).Name("税费");
                Map(x => x.IOSS).Name("IOSS");
                Map(x => x.GoodsDeclaredVal).Name("申报总额");
                Map(x => x.GoodsDeclaredCurrency).Name("申报币种");
                Map(x => x.IsOnline).Name("线上订单");
                Map(x => x.EUTariffRate).Name("欧盟税率");
                Map(x => x.Remark).Name("备注");
            }

            public OutDataInfo(decimal USDToCNY, decimal EURToCNY)
            {
                Map(x => x.CustomerOrderNo).Name("客户单号");
                Map(x => x.CustomerName).Name("客户");
                Map(x => x.DeliveryChannelName).Name("服务渠道");
                Map(x => x.ChannelName).Name("下单渠道");
                Map(x => x.CountryCode).Name("目的国家");
                Map(x => x.City).Name("城市");
                Map(x => x.ZipCode).Name("邮编");
                Map(x => x.WeightTime).Name("称重时间");
                Map(x => x.SystemNo).Name("内部单号");
                Map(x => x.TransferNumber).Name("转单号");
                Map(x => x.TrackingNumber).Name("跟踪号");
                Map(x => x.OPCVersion).Name("报价版本");
                Map(x => x.OPCServiceCharge).Name("公斤价");
                Map(x => x.OPCOperationFee).Name("操作费");
                Map(x => x.CustomerWeight).Name("客户重量").Formattext(ExcelFormattextHelper.DefaultAmount);
                Map(x => x.SysWeight).Name("实际称重").Formattext(ExcelFormattextHelper.DefaultAmount);
                Map(x => x.Amount).Name("金额");
                Map(x => x.SysAmount).Name("称重计费");
                Map(x => x.ConfirmedAmount).Name("确认金额");
                Map(x => x.Weight).Name("确认重量").Formattext(ExcelFormattextHelper.DefaultAmount);
                Map(x => x.Length).Name("长");
                Map(x => x.Width).Name("宽");
                Map(x => x.Height).Name("高");
                //Map(x => x.AttachFee).Name("附加费").Annotation("美元汇率:" + USDToCNY.ToString("0.0000"));
                Map(x => x.AttachFee).Name("附加费");
                Map(x => x.EUTariff).Name("税费").Annotation("欧元汇率:" + EURToCNY.ToString("0.0000"));
                Map(x => x.IOSS).Name("IOSS");
                Map(x => x.GoodsDeclaredVal).Name("申报总额");
                Map(x => x.GoodsDeclaredCurrency).Name("申报币种");
                Map(x => x.IsOnline).Name("线上订单");
                Map(x => x.EUTariffRate).Name("欧盟税率");
                Map(x => x.Remark).Name("备注");
            }
        }

        public byte[] OutDataSimple(List<Finance_CustomerDeliveryBillDetail> list)
        {
            return _excelHelper.OutDataAsByte<OutDataSimpleInfo, Finance_CustomerDeliveryBillDetail>(list);
        }

        public class OutDataSimpleInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetail>
        {
            public OutDataSimpleInfo()
            {
                Map(x => x.TrackingNumber).Name("运单号");
                Map(x => x.Amount).Name("金额");
                Map(x => x.Remark).Name("备注");
            }
        }


        public class InputDataSimpleInfo : MSExcelClassMapping<Finance_CustomerDeliveryBillDetailInput>
        {
            public InputDataSimpleInfo()
            {
                Map(x => x.TrackingNumber).Name("运单号");
                Map(x => x.Amount).Name("金额");
                Map(x => x.Remark).Name("备注");
            }
        }
        public async Task<List<Finance_CustomerDeliveryBillDetail>> GetSupplements(int billId)
        {
            return await _deliveyBillRepository.GetDetail(billId, 2, null);
        }

        public async Task<byte[]> GetSupplementsData(int billId)
        {
            var list = await GetSupplements(billId);
            return OutDataSimple(list);
        }

        public async Task InputSupplementOrDeduction(int billId, string url, int type)
        {
            var list = LoadDetailSimpleInput(url);
            await _deliveyBillRepository.InputSupplementOrDeduction(billId, list, type);
        }

        public async Task UpdateConfirmed(int billId, string url)
        {
            var list = LoadDetailInput(url);
            List<Finance_CustomerDeliveryBillDetailInput> input = new List<Finance_CustomerDeliveryBillDetailInput>();
            foreach (var item in list)
            {
                input.Add(new Finance_CustomerDeliveryBillDetailInput
                {
                    Amount = item.ConfirmedAmount,
                    ConfirmedAmount = item.ConfirmedAmount,
                    Remark = item.Remark,
                    Weight = item.Weight,
                    TrackingNumber = item.TrackingNumber,
                    AttachFee = item.AttachFee
                });
            }
            await _deliveyBillRepository.UpdateConfirmed(billId, input);
        }

        public async Task PostPayment(int Id, decimal PostPayment)
        {
            var bill = await _deliveyBillRepository.GetAsync(Id);
            if (bill.Payment + PostPayment > bill.Receivables)
                throw new Exception("收款金额不可大于应收金额");
            bill.Payment += PostPayment;
            if (bill.Payment == bill.Receivables)
            {
                //更新结款完成
                bill.Status = FiannceServiceBillStatus.无欠款;
            }
            await _deliveyBillRepository.UpdateAsync(bill);
        }

        public async Task Pass(int billId, bool isRealTime = false)
        {
            await _deliveyBillRepository.Pass(billId, isRealTime);
        }

        public async Task Rereject(int billId)
        {
            await _deliveyBillRepository.Rereject(billId);
        }

        public async Task<List<Finance_Logistics_LadingBillLogDto>> GetPaymentLog(int billId)
        {
            return await _deliveyBillRepository.GetPaymentLog(billId);
        }

        public async Task DeleteBill(int billId)
        {
            await _deliveyBillRepository.DeleteByIdAsync(billId);
            await _deliveyBillRepository.DeleteDetails(billId);
        }

        public async Task InputWeightAndCalculation(int billId, string url)
        {
            var list = LoadDetailInput(url);
            await _deliveyBillRepository.UpdatetWeightAndFee(list, 1, 10000, Guid.NewGuid().ToString(), billId);
        }

        public async Task CalcBillClaimedAmount(int billId, DateTime claimConfirmFrom, DateTime claimConfirmTo)
        {
            if (billId == 0)
                throw new Exception("账单ID无效");
            if (claimConfirmTo < claimConfirmFrom)
                throw new Exception("结束时间不能小于开始时间");

            var bill = await _deliveyBillRepository.GetDeliveryBillById(billId);
            if (bill == null)
                throw new Exception("无此ID的账单");

            int customerId = bill.CustomerId;

            var details = await _customerBillClaimDetailRepository.QueryAsync(i => i.CustomerId == customerId && i.FinanceConfirmTime >= claimConfirmFrom && i.FinanceConfirmTime <= claimConfirmTo);
            if (details?.Any() == true)
            {
                List<Finance_CustomerDeliveryBillDetail> batch = new List<Finance_CustomerDeliveryBillDetail>();
                foreach (var d in details)
                {
                    Finance_CustomerDeliveryBillDetail detail = new Finance_CustomerDeliveryBillDetail
                    {
                        BillId = billId,
                        Amount = d.Amount,
                        AttachFee = 0,
                        ChannelName = d.DeliveryChannelName,
                        FeeType = ServiceFeeType.索赔,
                        ConfirmedAmount = d.ConfirmedAmount == null ? 0 : d.ConfirmedAmount.Value,
                        City = d.City,
                        Confirmed = true,
                        CountryCode = d.CountryCode,
                        CreateTime = d.CreateTime,
                        CreateUserName = d.CreateUserName,
                        CustomerName = d.CustomerName,
                        CustomerOrderNo = d.CustomerOrderNo,
                        CustomerWeight = d.CustomerWeight,
                        DeliveryChannelName = string.Empty,
                        Height = d.Height,
                        Length = d.Length,
                        OrderId = d.OrderId,
                        OrderRemark = d.OrderRemark,
                        SysAmount = d.SysAmount,
                        SystemNo = d.SystemNo,
                        TrackingNumber = d.TrackingNumber,
                        TransferNumber = d.TransferNumber,
                        SysWeight = d.SysWeight,
                        Weight = d.Weight,
                        ZipCode = d.ZipCode,
                        Width = d.Width,
                        WeightTime = d.WeightTime == default(DateTime) ? new DateTime(1900, 1, 1) : d.WeightTime,
                        Remark = d.Remark,
                        UpdateTime = d.UpdateTime,
                        UpdateUserName = d.UpdateUserName
                    };


                    batch.Add(detail);
                }

                string deleteSql = "delete from Finance_CustomerDeliveryBillDetail where BillId=@BillId and FeeType=3";
                await _deliveyBillRepository.ExecuteSqlAsync(deleteSql, new { BillId = billId });
                _deliveyBillRepository.BatchAddDetails(billId, batch);

                var totalAmount = batch.Sum(i => i.ConfirmedAmount);

                await _deliveyBillRepository.UpdateByConditionAsync(i => i.Id == billId, new { ClaimAmount = totalAmount });

            }



        }

        public class OutFeeGroup
        {
            public List<Finance_CustomerDeliveryBillDetail> Deatils
            {
                get; set;
            }

            public string FeeName
            {
                get; set;
            }

            public int Qty
            {
                get; set;
            }

            public string Company
            {
                get; set;
            }

            public decimal Fee
            {
                get; set;
            }

            public decimal FeeSum
            {
                get; set;
            }
        }

        private class FbaPoll
        {
            public int OrderId
            {
                get; set;
            }

            public int Qty
            {
                get; set;
            }
        }

        /// <summary>
        /// 导出客户帐单
        /// </summary>
        /// <param name="billId">帐单序号</param>
        /// <param name="outType">帐单类型</param>
        /// <returns></returns>
        public async Task<byte[]> GetOutCustomer(int billId, int outType, bool isYC = false)
        {
            #region 数据获取

            string sql = @"select * from Finance_CustomerDeliveryBill(nolock) where Id=@billId";
            var data = await _deliveyBillRepository.QueryBySqlAsync(sql, new { billId = billId });
            var fbaChannels = (await _publicRepository.QueryBySqlAsync("select Name from Logistics_Public(nolock) where ChannelType=3")).Select(x => x.Name).ToArray();


            var customerName =
                (await _customerService.CacheGetAllCustomers()).Where(x => x.Id == data.First().CustomerId).FirstOrDefault()?.CompanyName;
            //取运费帐单
            var shippingList = await _deliveyBillRepository.GetDetail(billId, 0, null);
            //排除FBA帐单
            var shippingFeeList2 = shippingList.Where(x => fbaChannels.Contains(x.ChannelName)).ToList();

            //补款杂费
            var otherFee = await _deliveyBillRepository.GetDetail(billId, 2, null);

            if (shippingFeeList2.Count > 0)
            {
                //查找箱子信息
                var arrays = shippingFeeList2.Select(x => x.OrderId).ToArray();
                var boxInfos = await _unitOfWork.QueryBySqlAsync<FbaPoll>($@"
                            select a.OrderId,count(0)Qty from Order_MpsOrder(nolock)a
                                                          join Order_MpsOrderBox(nolock)b on a.id = b.MpsOrderId
                            where a.OrderId in ({string.Join(',', arrays)})
                            group by a.OrderId");

                //FBA杂费处理
                var fbaList = otherFee.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.补_FBA杂费);
                shippingFeeList2.ForEach(x =>
                {
                    x.BoxQty = boxInfos.FirstOrDefault(y => y.OrderId == x.OrderId)?.Qty ?? 0;
                    x.AttachFee = otherFee.Where(y => y.OrderId == x.OrderId).Sum(y => y.Amount);
                    x.OtherFee = String.Join(',', otherFee.Where(y => y.OrderId == x.OrderId).Select(y => y.Remark));
                    x.SumAmount = x.AttachFee + x.SysAmount;
                });
            }
            shippingList = shippingList.Where(x => !fbaChannels.Contains(x.ChannelName)).ToList();
            var rate = _deliveyBillRepository.GetUSDAndEURToCny(data.First().StartTime, data.First().EndTime);
            List<OutFeeGroup> outFeeGroups = new List<OutFeeGroup>();
            //运费
            if (outType == 1)
            {
                if (data.First().Status == 0)
                {
                    if (shippingList.Count > 0)
                    {
                        outFeeGroups.Add(new OutFeeGroup
                        {
                            FeeName = "运费",
                            Qty = shippingList.Count,
                            Fee = data.First().SysFreigh,
                            FeeSum = data.First().SysFreigh,
                            Deatils = shippingList
                        });
                    }
                    if (shippingFeeList2.Count > 0)
                    {
                        outFeeGroups.Add(new OutFeeGroup
                        {
                            FeeName = "FBA运费",
                            Qty = shippingFeeList2.Count,
                            Fee = data.First().SysFreigh,
                            FeeSum = data.First().SysFreigh,
                            Deatils = shippingFeeList2
                        });
                    }
                }
                else
                {
                    if (shippingList.Count > 0)
                    {
                        outFeeGroups.Add(new OutFeeGroup
                        {
                            FeeName = "运费",
                            Qty = shippingList.Count,
                            Fee = data.First().ConfirmedFreigh,
                            FeeSum = data.First().ConfirmedFreigh,
                            Deatils = shippingList
                        });
                    }

                    if (shippingFeeList2.Count > 0)
                    {
                        outFeeGroups.Add(new OutFeeGroup
                        {
                            FeeName = "FBA运费",
                            Qty = shippingFeeList2.Count,
                            Fee = data.First().ConfirmedFreigh,
                            FeeSum = data.First().ConfirmedFreigh,
                            Deatils = shippingFeeList2
                        });
                    }
                }
            }
            else if (outType == 2)
            {
                var groups = shippingList.GroupBy(x => x.ChannelName);
                foreach (var g in groups)
                {
                    if (data.First().Status == 0)
                    {
                        outFeeGroups.Add(new OutFeeGroup
                        {
                            FeeName = g.Key,
                            Qty = g.Count(),
                            Fee = g.Sum(x => x.SysAmount + x.EUTariff + x.AttachFee),
                            FeeSum = g.Sum(x => x.SysAmount + x.EUTariff + x.AttachFee),
                            Deatils = g.ToList()
                        });
                    }
                    else
                    {
                        outFeeGroups.Add(new OutFeeGroup
                        {
                            FeeName = g.Key,
                            Qty = g.Count(),
                            Fee = g.Sum(x => x.ConfirmedAmount + x.ConfirmedEUTariff + x.AttachFee),
                            FeeSum = g.Sum(x => x.ConfirmedAmount + x.ConfirmedEUTariff + x.AttachFee),
                            Deatils = g.ToList()
                        });
                    }
                }
            }
            var zfList = otherFee.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.补_杂费);
            var tjList = otherFee.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.补_退件费);
            var cpList = otherFee.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.补_重派费);

            //扣款杂费
            var kkList = await _deliveyBillRepository.GetDetail(billId, 1, null);
            //运费退回
            var kkyunfei = kkList.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.扣_运费退回);
            var kkzf = kkList.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.扣_杂费);
            var kksjcy = kkList.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.扣_时间差异费);
            var kkzlcy = kkList.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.扣_重量差异费);
            var cktj = kkList.Where(x => x.FinanceCustomerSupplementType == FinanceCustomerSupplementOrDeductionType.扣_退件退回);
            var listRaplte = await _deliveyBillRepository.Get_Repeat_Fee(billId);
            DateTime start = data.First().StartTime, end = data.First().EndTime;


            string month = end.ToString("yyyy-MM");
            IEnumerable<Logistics_ClaimRecord> spList = new List<Logistics_ClaimRecord>();

            int days = DateTime.DaysInMonth(end.Year, end.Month);
            //if (days == end.Day)
            spList = await _claimRecordRepository.QueryBySqlAsync(
               @$"select * from Logistics_ClaimRecord(nolock) where CreateTime between '{start}' and '{end.AddDays(1)}'  
                        and isnull(Solution,0) !=0 and ClaimStatus=0 and CustomerId={data.First().CustomerId}");

            if (cktj.Count() > 0)
            {
                outFeeGroups.Add(new OutFeeGroup
                {
                    FeeName = "扣款-仓库退件",
                    Qty = cktj.Count(),
                    Fee = cktj.Sum(x => x.Amount),
                    FeeSum = cktj.Sum(x => x.Amount),
                    Deatils = cktj.ToList()
                });
            }
            if (zfList.Count() > 0)
            {
                outFeeGroups.Add(new OutFeeGroup
                {
                    FeeName = "补款-杂费",
                    Qty = zfList.Count(),
                    Fee = zfList.Sum(x => x.Amount),
                    FeeSum = zfList.Sum(x => x.Amount),
                    Deatils = zfList.ToList()
                });
            }

            if (tjList.Count() > 0)
            {
                outFeeGroups.Add(new OutFeeGroup
                {
                    FeeName = "补款-退件",
                    Qty = tjList.Count(),
                    Fee = tjList.Sum(x => x.Amount),
                    FeeSum = tjList.Sum(x => x.Amount),
                    Deatils = tjList.ToList()
                });
            }
            if (cpList.Count() > 0)
            {
                outFeeGroups.Add(new OutFeeGroup
                {
                    FeeName = "补款-重派",
                    Qty = cpList.Count(),
                    Fee = cpList.Sum(x => x.Amount),
                    FeeSum = cpList.Sum(x => x.Amount),
                    Deatils = cpList.ToList()
                });
            }
            if (kksjcy.Count() > 0)
            {
                outFeeGroups.Add(new OutFeeGroup
                {
                    FeeName = "扣款-时间差异",
                    Qty = kksjcy.Count(),
                    Fee = kksjcy.Sum(x => x.Amount),
                    FeeSum = kksjcy.Sum(x => x.Amount),
                    Deatils = kksjcy.ToList()
                });
            }
            if (kkzlcy.Count() > 0)
            {
                outFeeGroups.Add(new OutFeeGroup
                {
                    FeeName = "扣款-重量差异",
                    Qty = kkzlcy.Count(),
                    Fee = kkzlcy.Sum(x => x.Amount),
                    FeeSum = kkzlcy.Sum(x => x.Amount),
                    Deatils = kkzlcy.ToList()
                });
            }
            if (kkzf.Count() > 0)
            {
                outFeeGroups.Add(new OutFeeGroup
                {
                    FeeName = "扣款-杂费",
                    Qty = kkzf.Count(),
                    Fee = kkzf.Sum(x => x.Amount),
                    FeeSum = kkzf.Sum(x => x.Amount),
                    Deatils = kkzf.ToList()
                });
            }
            if (kkyunfei.Count() > 0)
            {
                decimal f1 = kkyunfei.Sum(x => (x.Amount + x.AttachFee + x.ConfirmedEUTariff));
                outFeeGroups.Add(new OutFeeGroup
                {
                    FeeName = "扣款-运费退回",
                    Qty = kkyunfei.Count(),
                    Fee = f1,
                    FeeSum = f1,
                    Deatils = kkyunfei.ToList()
                });
            }
            if (spList.Count() > 0)
            {
                var channels = await _channelRepository.QueryAsync();
                var allPublic = await _publicRepository.QueryAsync();
                var spgroup = new OutFeeGroup
                {
                    Qty = spList.Count(),
                    FeeName = "索赔",
                    Fee = 0,
                    FeeSum = 0,
                    Deatils = spList.Select(x => new Finance_CustomerDeliveryBillDetail
                    {
                        CustomerOrderNo = x.CustomerOrderNo,
                        CustomerName = customerName,
                        TrackingNumber = x.TrackingNumber,
                        TransferNumber = x.TransferNumber,
                        WeightTime = x.ReceiveTime == null ? DateTime.MinValue : x.ReceiveTime.Value,
                        Amount = x.Cost,
                        GoodsDeclaredVal = x.TotalValue,
                        GoodsDeclaredCurrency = x.Currency,
                        AttachFee = x.Freight,
                        ChannelName = x.ChannelId.ToString(),
                        OrderRemark = x.SolutionName,
                        Remark = x.Remark
                    }).ToList()
                };

                foreach (var item in spgroup.Deatils)
                {
                    var c = channels.Where(y => y.Id == int.Parse(item.ChannelName)).FirstOrDefault();
                    if (c != null)
                    {
                        var pc = channels.Where(x => x.Id == c.PublicChanneId).FirstOrDefault();
                        if (pc != null)
                        {
                            var pubName = allPublic.Where(x => x.Code == pc.PublicCode).FirstOrDefault()?.Name;
                            item.ChannelName = pubName;
                        }

                    }
                }

                outFeeGroups.Add(spgroup);
            }
            #endregion
            #region excel写入
            //主表 
            Workbooks wbs;
            Application app;
            _Workbook wb;
            app = new Application();
            try
            {
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度
                wbs = app.Workbooks; //获取工作薄 
                wb = wbs.Add(Missing.Value);

                int qty = outFeeGroups.Count;

                Microsoft.Office.Interop.Excel.Worksheet sh = (Worksheet)wb.Sheets[1];
                sh.Name = "汇总";
                var range = sh.get_Range("A1", Missing.Value);
                range = range.get_Resize(1, 5);
                range.Merge();

                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.RowHeight = 40;
                range.Font.Size = 24;


                range.Value2 = "";
                var range2 = sh.get_Range("A2", Missing.Value);
                range2 = range2.get_Resize(1, 5);
                range2.Merge();

                range2.Font.Bold = true;
                range2.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range2.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range2.RowHeight = 40;
                range2.Font.Size = 18;
                var h = new object[1, 5];
                h[0, 0] = "帐单明细";
                range2.set_Value(Missing.Value, h);


                var range3 = sh.get_Range("A3");
                range3 = range3.get_Resize(1, 5);
                range3.RowHeight = 40;
                range3.Font.Bold = true;
                var header = new object[1, 5];
                header[0, 0] = "客户名称：" + customerName;
                header[0, 1] = "";
                header[0, 2] = "";
                header[0, 3] = "";
                header[0, 4] = "帐单日期：" + data.First().StartTime.ToString("yyyy.MM.dd") + "-" + data.First().EndTime.ToString("dd");
                range3.set_Value(Missing.Value, header);



                var range7 = sh.get_Range("A4").get_Resize(1, 5);
                range7.Font.Bold = true;
                range7.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range7.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range7.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range7.Interior.Color = System.Drawing.Color.FromArgb(191, 191, 191);
                range7.RowHeight = 40;
                range7.ColumnWidth = 30;

                var header2 = new object[1, 5];
                header2[0, 0] = "收费明细";
                header2[0, 1] = "数量";
                header2[0, 2] = "单位";
                header2[0, 3] = "金额（元）";
                header2[0, 4] = "合计（元）";
                range7.set_Value(Missing.Value, header2);

                object[,] groupData = new object[outFeeGroups.Count, 5];
                var range9 = sh.get_Range("A5");
                range9 = range9.get_Resize(outFeeGroups.Count, 5);
                range9.Font.Bold = true;
                range9.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range9.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range9.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range9.RowHeight = 40;
                range9.ColumnWidth = 30;
                for (int i = 0; i < outFeeGroups.Count(); i++)
                {
                    groupData[i, 0] = outFeeGroups[i].FeeName;
                    groupData[i, 1] = outFeeGroups[i].Qty;
                    groupData[i, 2] = "件";
                    groupData[i, 3] = outFeeGroups[i].Fee;
                    groupData[i, 4] = outFeeGroups[i].FeeSum;
                }
                range9.set_Value(Missing.Value, groupData);
                var rt9 = sh.get_Range("D5");
                rt9 = rt9.get_Resize(outFeeGroups.Count, 2);
                rt9.NumberFormat = "#,##0.00";


                var range6 = sh.get_Range("A" + (5 + outFeeGroups.Count), Missing.Value);
                range6 = range6.get_Resize(1, 4);
                range6.Merge();
                range6.Font.Bold = true;
                range6.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range6.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range6.RowHeight = 30;
                range9.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                var header3 = new object[1, 4];
                header3[0, 0] = "合计";
                range6.set_Value(Missing.Value, header3);
                range6.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;


                var rangeaa = sh.get_Range("E" + (5 + outFeeGroups.Count), Missing.Value);
                rangeaa = rangeaa.get_Resize(1, 1);
                rangeaa.Font.Bold = true;
                rangeaa.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rangeaa.VerticalAlignment = XlVAlign.xlVAlignCenter;
                rangeaa.RowHeight = 30;
                var header4 = new object[1, 1];
                header4[0, 0] = outFeeGroups.Sum(x => x.FeeSum);
                rangeaa.set_Value(Missing.Value, header4);
                rangeaa.NumberFormat = "#,##0.00";
                rangeaa.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                var range4 = sh.get_Range("A" + (7 + outFeeGroups.Count), Missing.Value);
                range4 = range4.get_Resize(1, 5);
                range4.Merge();
                range4.Font.Bold = true;
                range4.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                range4.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range4.RowHeight = 30;
                var header5 = new object[1, 5];
                header5[0, 0] = "如对以上款项有疑问，请致电我司对账,如无疑问,请安排付款事宜,谢谢!";
                range4.set_Value(Missing.Value, header5);


                var range5 = sh.get_Range("A" + (8 + outFeeGroups.Count), Missing.Value);
                range5 = range5.get_Resize(4, 2);
                range5.Merge();
                range5.Font.Bold = true;
                range5.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                range5.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range5.RowHeight = 50;
                range5.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                var range52 = sh.get_Range("A" + (8 + outFeeGroups.Count + 4), Missing.Value);
                range52 = range52.get_Resize(4, 2);
                range52.Merge();
                range52.Font.Bold = true;
                range52.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                range52.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range52.RowHeight = 50;
                range52.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;



                MSExcelHelper excelHelper = new MSExcelHelper();
                for (var i = 0; i < outFeeGroups.Count; i++)
                {
                    var g = outFeeGroups[i];
                    Worksheet Sheet = (Worksheet)wb.Sheets.Add(Type.Missing, wb.ActiveSheet, 1, XlSheetType.xlWorksheet);
                    if (g.FeeName != "FBA运费") { Sheet.Name = g.FeeName; }
                    if (g.FeeName == "付款")
                    {
                        var mapping = new SRDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, SRDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName == "FBA运费")
                    {
                        if (!customerName.Contains("英驰"))
                        {
                            for (int j = 1; j < g.Deatils.Count + 1; j++)
                            {
                                g.Deatils[j - 1].Weight = Math.Ceiling(g.Deatils[j - 1].SysWeight);

                            }
                        }
                        else
                        {
                            g.Deatils.ForEach(x => x.ZT = "行运易");
                            var mapping = new FBADataYCInfo();
                            excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, FBADataYCInfo>(g.Deatils, mapping, Sheet);
                        }
                        if (isYC)
                        {
                            MakeCustomerYCFile(app, wb, (Worksheet)wb.ActiveSheet, g.Deatils, customerName, data.First().StartTime.ToString("yyyy.MM.dd") + "-" + data.First().EndTime.ToString("dd"));
                        }
                        else
                        {
                            MakeCustomerFile(app, wb, (Worksheet)wb.ActiveSheet, g.Deatils, customerName, data.First().StartTime.ToString("yyyy.MM.dd") + "-" + data.First().EndTime.ToString("dd"));
                        }
                        //if (customerName.Contains("英驰"))
                        //{
                        //    g.Deatils.ForEach(x => x.ZT = "行运易");
                        //    var mapping = new FBADataYCInfo();
                        //    excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, FBADataYCInfo>(g.Deatils, mapping, Sheet);
                        //}
                        //else
                        //{
                        //    for (int j = 1; j < g.Deatils.Count + 1; j++)
                        //    {
                        //        g.Deatils[j - 1].No = j;
                        //        g.Deatils[j - 1].Weight = Math.Ceiling(g.Deatils[j - 1].SysWeight);

                        //    }
                        //    var mapp = new FBADataInfo();
                        //    excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, FBADataInfo>(g.Deatils, mapp, Sheet);
                        //}
                    }
                    else if (!g.FeeName.Contains("补款") && !g.FeeName.Contains("索赔") && !g.FeeName.Contains("扣款"))
                    {
                        List<int> index = g.Deatils
                            .Select((e, i) => new { OrderId = e.OrderId, index = i })
                            .Where(x => listRaplte.Select(x => x.OrderId).ToArray().Contains(x.OrderId))
                            .Select(x => x.index).ToList();
                        var mapping = new OutDataInfo(rate.Item1, rate.Item2);
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, OutDataInfo>(g.Deatils, mapping, Sheet, index);
                    }
                    else if (g.FeeName.Contains("补款-杂费"))
                    {
                        var mapping = new ZFDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, ZFDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("补款-FBA杂费"))
                    {
                        var mapping = new ZFDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, ZFDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("补款-退件"))
                    {
                        var mapping = new TJDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, TJDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("补款-重派"))
                    {
                        var mapping = new CPDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, CPDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("扣款-运费退回"))
                    {
                        var mapping = new OutDataInfo(rate.Item1, rate.Item2);
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, OutDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("扣款-仓库退件"))
                    {
                        var mapping = new CKTJDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, CKTJDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("扣款-杂费"))
                    {
                        var mapping = new ZFDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, ZFDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("扣款-时间差异"))
                    {
                        var mapping = new ZFDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, ZFDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("扣款-重量差异"))
                    {
                        var mapping = new ZFDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, ZFDataInfo>(g.Deatils, mapping, Sheet);
                    }
                    else if (g.FeeName.Contains("索赔"))
                    {
                        var mapping = new SPDataInfo();
                        excelHelper.SetSheet<Finance_CustomerDeliveryBillDetail, SPDataInfo>(g.Deatils, mapping, Sheet);
                    }
                }

                string filename = getTempFilePath();
                wb.SaveCopyAs(filename);
                byte[] r = System.IO.File.ReadAllBytes(filename);
                return r;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                app.Quit(); //关闭EXCEL
            }
            #endregion
        }
        #region fba客户帐单模板导出
        public bool MakeCustomerFile(Application app, _Workbook source, Worksheet sheet, List<Finance_CustomerDeliveryBillDetail> details, string customerName, string billTime)
        {
            if (details == null || details.Count == 0) { return false; }
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "客户帐单模板", "普通客户.xlsx");
            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(source.ActiveSheet, Type.Missing);
            //tempsheet.Copy(Type.Missing, source.ActiveSheet);
            sheet = (Worksheet)source.ActiveSheet;

            #region 优先写入固定数据
            sheet.Range["B6"].Value = customerName;
            sheet.Range["N6"].Value = billTime;
            sheet.Range["O9"].Value = details.Sum(x => x.SumAmount);
            sheet.Range["O9"].NumberFormat = "#,##0.00";
            #endregion

            int contentRowIdx = 8; int rowCount = details.Count(); var templateBehindContentEndCol = getIndexFromChar("P");
            if (details.Count > 1)
            {
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2);
            }
            object[,] mm = new object[rowCount, templateBehindContentEndCol.Item1];
            for (int i = 0; i < rowCount; i++)
            {
                mm[i, 0] = details[i].WeightTime.ToString_yyyyMMddHHmmss();
                mm[i, 1] = details[i].SystemNo;
                mm[i, 2] = details[i].CustomerOrderNo;
                mm[i, 3] = details[i].TrackingNumber;
                mm[i, 4] = details[i].ChannelName;
                mm[i, 5] = details[i].CountryCode;
                mm[i, 6] = details[i].ZipCode;
                mm[i, 7] = details[i].BoxQty;
                mm[i, 8] = "";
                mm[i, 9] = "";
                mm[i, 10] = details[i].Weight;
                mm[i, 11] = details[i].OPCServiceCharge;
                mm[i, 12] = details[i].SysAmount;
                mm[i, 13] = details[i].AttachFee;
                mm[i, 14] = details[i].SumAmount;
                mm[i, 15] = details[i].OtherFee;
            }
            var range = sheet.get_Range("A8");
            range = range.get_Resize(rowCount, templateBehindContentEndCol.Item1);
            range.set_Value(Missing.Value, mm);

            return true;
        }
        public bool MakeCustomerYCFile(Application app, _Workbook source, Worksheet sheet, List<Finance_CustomerDeliveryBillDetail> details, string customerName, string billTime)
        {
            if (details == null || details.Count == 0) { return false; }
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "客户帐单模板", "英驰模板.xlsx");
            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(source.ActiveSheet, Type.Missing);
            sheet = (Worksheet)source.ActiveSheet;

            #region 优先写入固定数据
            sheet.Range["B6"].Value = customerName;
            sheet.Range["O6"].Value = billTime;
            sheet.Range["U9"].Value = details.Sum(x => x.SumAmount);
            sheet.Range["U9"].NumberFormat = "#,##0.00";
            #endregion

            int contentRowIdx = 8; int rowCount = details.Count(); var templateBehindContentEndCol = getIndexFromChar("V");
            if (details.Count > 1)
            {
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2);
            }
            object[,] mm = new object[rowCount, templateBehindContentEndCol.Item1];
            for (int i = 0; i < rowCount; i++)
            {
                mm[i, 0] = "行运易";
                mm[i, 1] = details[i].WeightTime.ToString_yyyyMMddHHmmss();
                mm[i, 2] = details[i].SystemNo;
                mm[i, 3] = details[i].CustomerOrderNo;
                mm[i, 4] = details[i].TrackingNumber;
                mm[i, 5] = details[i].ChannelName;
                mm[i, 6] = details[i].CountryCode;
                mm[i, 7] = details[i].ZipCode;
                mm[i, 8] = details[i].BoxQty;
                mm[i, 9] = "";
                mm[i, 10] = "";
                mm[i, 11] = details[i].SysWeight;
                mm[i, 12] = details[i].OPCServiceCharge;
                mm[i, 13] = details[i].SysAmount;
                mm[i, 14] = details[i].EUTariff;
                mm[i, 15] = details[i].AttachFee;
                mm[i, 16] = details[i].Empty1;
                mm[i, 17] = details[i].Empty2;
                mm[i, 18] = details[i].Empty3;
                mm[i, 19] = details[i].Empty4;
                mm[i, 20] = details[i].SumAmount;
                mm[i, 21] = details[i].OtherFee;
            }
            var range = sheet.get_Range("A8");
            range = range.get_Resize(rowCount, templateBehindContentEndCol.Item1);
            range.set_Value(Missing.Value, mm);

            return true;
        }
        public void InsertRows2022(Worksheet tempSheet, int fromRowIndex, int rowCount, string startCol, string endCol)
        {

            //模板保留一行用于复制,可减少样式代码
            Microsoft.Office.Interop.Excel.Range RngToCopy = tempSheet.Range[startCol + (fromRowIndex) + ":" + endCol + (fromRowIndex)].EntireRow;
            //复制行插入
            Microsoft.Office.Interop.Excel.Range RngToInsert = tempSheet.Range[startCol + (fromRowIndex + 1) + ":" + endCol + (fromRowIndex + rowCount - 1)].EntireRow;
            RngToInsert.Insert(XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy(Type.Missing));
        }
        Tuple<int, string> getIndexFromChar(string indexOfChar, int offset = 0)
        {
            String uid = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z";
            List<string> uidArray = uid.Split(",").ToList();
            int index = uidArray.IndexOf(indexOfChar.ToLower());

            string result = uidArray.Skip(index + offset).Take(1).FirstOrDefault();
            int resultIndex = uidArray.IndexOf(result.ToLower()) + 1;//最终获取值从1开始算，下标从0开始，故+1
            return new Tuple<int, string>(resultIndex, result);

        }
        #endregion
        private string getTempFilePath()
        {
            string basePath = System.IO.Path.Combine(System.AppContext.BaseDirectory, "temp");
            if (!System.IO.Directory.Exists(basePath))
                System.IO.Directory.CreateDirectory(basePath);
            return System.IO.Path.Combine(basePath, Guid.NewGuid().ToString() + ".xlsx");
        }

        public async Task<PageResult<Finance_CustomerDeliveryBill>> GetRemittancePageResult(QueryModel queryModel)
        {
            if (queryModel.QueryParamer == null)
            {
                queryModel.QueryParamer = new List<QueryParamer>();
            }
            queryModel.QueryParamer.Add(new QueryParamer { Filed = "Status", Method = "GrE", Value = FiannceServiceBillStatus.结算中.GetValue().ToString() });
            return await _deliveyBillRepository.GetRemittancePageResult(queryModel);
        }

        public async Task<List<WaitCreateBillCustomer>> GetWaitCreateBill(int billType)
        {
            IEnumerable < WaitCreateBillCustomer > waitCreateBillCustomers = await _deliveyBillRepository.GetWaitCreateBill(billType);

           var waitCreateBillCustomerList = new List<WaitCreateBillCustomer>();
            //生成账单
            if (null != waitCreateBillCustomers && waitCreateBillCustomers.Any())
            {
                List<Finance_CustomerDeliveryBill> finance_CustomerDeliveryBills = new List<Finance_CustomerDeliveryBill>();
                string billTmpId = Guid.NewGuid().ToString();
                foreach (var waitCreateBillCustomer in waitCreateBillCustomers)
                {
                    Finance_CustomerDeliveryBill finance_CustomerDeliveryBill = new Finance_CustomerDeliveryBill();
                    finance_CustomerDeliveryBill.Name  = waitCreateBillCustomer.CustomerName + waitCreateBillCustomer.Start.ToString("yyyy.MM.dd") + "-" + waitCreateBillCustomer.End.ToString("dd");
                    finance_CustomerDeliveryBill.StartTime = waitCreateBillCustomer.Start;
                    finance_CustomerDeliveryBill.EndTime = waitCreateBillCustomer.End;
                    finance_CustomerDeliveryBill.CustomerId = waitCreateBillCustomer.CustomerId;
                    finance_CustomerDeliveryBill.BillTmpId= billTmpId;
                    //新增状态
                    finance_CustomerDeliveryBill.BillState = 0;
                    finance_CustomerDeliveryBill.CreateTime = DateTime.Now;
                    finance_CustomerDeliveryBills.Add(finance_CustomerDeliveryBill);
                }
                IEnumerable<Finance_CustomerDeliveryBill> finance_CustomerDeliverieList = await _deliveyBillRepository.AddDeliveryBillBatch(finance_CustomerDeliveryBills);
                if(null != finance_CustomerDeliverieList && finance_CustomerDeliverieList.Any())
                {
                    waitCreateBillCustomerList = waitCreateBillCustomers.ToList();
                    waitCreateBillCustomerList.ForEach(item => {
                        Finance_CustomerDeliveryBill finance_CustomerDeliveryBill = finance_CustomerDeliverieList.FirstOrDefault(bItem => bItem.CustomerId == item.CustomerId);
                        item.BillId = finance_CustomerDeliveryBill.Id;
                        item.BillName = finance_CustomerDeliveryBill.Name;
                    });
                }
            }
            return waitCreateBillCustomerList;
        }

        public async Task<CustomerDeliveryPanelDetail> GetCustomerDeliveryPanelDetail()
        {
            return await _deliveyBillRepository.GetCustomerDeliveryPanelDetail();
        }

        public async Task<byte[]> DownloadRemittance(QueryModel queryModel)
        {
            var list =
                (await _deliveyBillRepository.GetRemittance(queryModel));
            var msExcelHelper = new MSExcelHelper();
            return msExcelHelper.OutDataAsByte<CustomerRemittanceMapping, CustomerRemittance>(list);
        }

        public class CustomerRemittanceMapping : MSExcelClassMapping<CustomerRemittance>
        {
            public CustomerRemittanceMapping()
            {
                Map(x => x.Abbreviation).Name("客户简称");
                Map(x => x.StartTime).Name("帐期开始").Formattext("yyyy-MM-dd");
                Map(x => x.EndTime).Name("帐期结束").Formattext("yyyy-MM-dd");
                Map(x => x.CreateTime).Name("账单日").Formattext("yyyy-MM-dd");
                Map(x => x.LastRepaymentDate).Name("最后还款日").Formattext("yyyy-MM-dd");
                Map(x => x.BalanceOfPreviousPeriod).Name("上期余额");
                Map(x => x.CurrentPeriod).Name("本期付款");
                Map(x => x.CustomerRevenue).Name("本期收入");
                Map(x => x.CustomerExpenditure).Name("本期支出");
                Map(x => x.ClosingBalance).Name("期末余额");
                Map(x => x.Arrears).Name("本期应还金额");
                Map(x => x.Repayment).Name("已还金额");
                Map(x => x.AmountToBeRepaid).Name("剩余待还");
                Map(x => x.Status).Name("状态");
                Map(x => x.CurrentOverdueDays).Name("逾期天数");
            }
        }

        #region 头程账单处理
        /// <summary>
        /// 日期判断逻辑
        /// </summary>
        /// <param name="From"></param>
        /// <param name="To"></param>
        public List<User_Customer> GetFirstCustomer(DateTime? From, DateTime? To, out List<Tuple<int, DateTime, DateTime>> startAndEnd, int customerId = 0)
        {
            DateTime currentTime = DateTime.Now;
            List<int> tempBillingCycle = new List<int>();
            //只获取头程数据
            var customers = _customerService.GetFirstCustomers();
            List<User_Customer> needCustomers = new List<User_Customer>();
            if (From.HasValue && To.HasValue && customerId > 0)
            {
                needCustomers = customers.Result.Where(x => x.Id == customerId).ToList();
                startAndEnd = new List<Tuple<int, DateTime, DateTime>>() { new Tuple<int, DateTime, DateTime>(needCustomers.FirstOrDefault().BillingCycle, Convert.ToDateTime(From.Value.ToShortDateString()), Convert.ToDateTime(To.Value.ToShortDateString())) };

            }
            else
            {
                tempBillingCycle.Add(2);//肯定有日结2
                startAndEnd = new List<Tuple<int, DateTime, DateTime>>() { new Tuple<int, DateTime, DateTime>(2, Convert.ToDateTime(currentTime.AddDays(-1).ToShortDateString()), Convert.ToDateTime(currentTime.ToShortDateString())) };
                //1号  半月4、月5
                if (currentTime.Day == 1)
                {
                    tempBillingCycle.AddRange(new List<int>() { 4, 5 });
                    startAndEnd.Add(new Tuple<int, DateTime, DateTime>(4, Convert.ToDateTime(currentTime.AddMonths(-1).ToString("yyyy-MM") + "-16"), Convert.ToDateTime(currentTime.ToShortDateString())));//半月
                    startAndEnd.Add(new Tuple<int, DateTime, DateTime>(5, Convert.ToDateTime(currentTime.AddMonths(-1).ToShortDateString()), Convert.ToDateTime(currentTime.ToShortDateString())));//月
                }
                //16号 半月4
                else if (currentTime.Day == 16)
                {
                    tempBillingCycle.Add(4);
                    startAndEnd.Add(new Tuple<int, DateTime, DateTime>(4, Convert.ToDateTime(currentTime.AddMonths(-1).ToString("yyyy-MM") + "-01"), Convert.ToDateTime(currentTime.ToShortDateString())));
                }
                //周结3
                if (currentTime.DayOfWeek == DayOfWeek.Monday)
                {
                    tempBillingCycle.Add(3);
                    startAndEnd.Add(new Tuple<int, DateTime, DateTime>(3, Convert.ToDateTime(currentTime.AddDays(-7).ToString("yyyy-MM") + "-01"), Convert.ToDateTime(currentTime.ToShortDateString())));
                }
                needCustomers = customers.Result.Where(x => (tempBillingCycle.Contains(x.BillingCycle))).ToList();
            }
            return needCustomers;
        }
        public async Task<ApiJsonModel> GenerateFirstBills(int firstCustomer = 0, DateTime? From = null, DateTime? To = null)
        {
            var tempCustomer = GetFirstCustomer(From, To, out List<Tuple<int, DateTime, DateTime>> startAndEnd, firstCustomer);
            var error = GenerateFirstBills(tempCustomer, startAndEnd).Result;
            if (error.Count == 0)
            {
                return ApiJsonModel.OK();
            }
            else { return ApiJsonModel.Error("执行错误", error); }
        }
        public async Task<List<Tuple<int, DateTime, DateTime, string>>> GenerateFirstBills(List<User_Customer> user_Customers, List<Tuple<int, DateTime, DateTime>> startAndEnd)
        {
            List<Tuple<int, DateTime, DateTime, string>> errorData = new List<Tuple<int, DateTime, DateTime, string>>();//错误值返回时，int为customerId
            var oldBills = _deliveyBillRepository.GetCustomerDeliveryBillByNew(user_Customers.Select(x => x.Id)).Result;
            List<Finance_CustomerDeliveryBill> toDBData = new List<Finance_CustomerDeliveryBill>();
            foreach (var item in user_Customers)
            {
                var tempStartAndEnd = startAndEnd.Where(x => x.Item1 == item.BillingCycle).FirstOrDefault();
                var tempOldBill = oldBills.Where(x => x.CustomerId == item.Id)?.FirstOrDefault();//存在空值，客户帐单
                var tempAccount = oldBills.Where(x => x.CustomerInfoId == item.Id)?.FirstOrDefault();//存在空值，客户帐户
                if (tempAccount == null) { continue; }//略过无帐户数据
                var ChargesDetails = await _deliveyBillRepository.GetChargesDetails(item.Id, tempStartAndEnd.Item2, tempStartAndEnd.Item3);
                try
                {
                    if (tempOldBill != null && tempOldBill.EndTime > tempStartAndEnd.Item2) { throw new Exception($"时间执行有误，上期截止:{tempOldBill.EndTime}"); }
                    var income = ChargesDetails.Where(x => x.IncomeOrESxpense == "收入").ToList();
                    var expend = ChargesDetails.Where(x => x.IncomeOrESxpense == "支出").ToList();
                    var differenceValue = income.Sum(x => x.SettlementAmount) - expend.Sum(x => x.SettlementAmount);

                    var balance = tempOldBill == null ?
                        0 + differenceValue :
                       tempOldBill?.ClosingBalance + differenceValue;
                    Finance_CustomerDeliveryBill tempModle = new Finance_CustomerDeliveryBill()
                    {
                        LastRepaymentDate = DateTime.Now.AddDays(tempOldBill?.RepaymentTermDay ?? (tempAccount?.RepaymentTermDay ?? 0)),
                        ClosingBalance = (decimal)balance,
                        Arrears = (decimal)balance > 0 ? 0 : -(decimal)balance,
                        AmountToBeRepaid = (decimal)balance > 0 ? 0 : -(decimal)balance,
                        //CustomerRevenue = (decimal)(income.Sum(x => x.SettlementAmount)),//头程客户无收入
                        CustomerExpenditure = (decimal)(expend.Sum(x => x.SettlementAmount)),
                        BalanceOfPreviousPeriod = tempOldBill?.ClosingBalance ?? 0,
                        Name = item.CompanyName + tempStartAndEnd.Item2.ToShortDateString() + "_" + tempStartAndEnd.Item3.ToShortDateString(),
                        Curreny = "CNY",//不管货币种类，只用人民币进行结算
                        Status = balance > 0 ? FiannceServiceBillStatus.无欠款 : FiannceServiceBillStatus.结算中,
                        StartTime = tempStartAndEnd.Item2,
                        EndTime = tempStartAndEnd.Item3.AddDays(-1),//因为都是超出一天执行，但是区间应该是在前一天59分59秒
                        CustomerId = item.Id,
                        DeductionNode = (int)item.DeductionNode,
                        CreateTime = DateTime.Now,
                        BillTypeValue = (int)BillTypeEnum.头程帐单,
                        Receivables = differenceValue >= 0 ? 0 : -(decimal)differenceValue,
                        CurrentPeriod = (decimal)income.Sum(x => x.SettlementAmount),
                    };
                    if (tempModle.LastRepaymentDate <= DateTime.Now) { tempModle.CurrentOverdueDays = (int)((TimeSpan)(tempModle.LastRepaymentDate - DateTime.Now)).TotalDays; } else { tempModle.CurrentOverdueDays = 0; }
                    await _ladingBillRepository.UpdateFirstBillCusetomerChequesStatus(item.Id, tempStartAndEnd.Item2, tempStartAndEnd.Item3);
                    toDBData.Add(tempModle);
                }
                catch (Exception ex)
                {
                    errorData.Add(new Tuple<int, DateTime, DateTime, string>(item.Id, tempStartAndEnd.Item2, tempStartAndEnd.Item3, ex.Message));
                }
            }
            if (toDBData.Count > 0) { await _deliveyBillRepository.BulkToDBAsync(toDBData); }
            return errorData;
        }

      


        #endregion
    }
}

