using Microsoft.Extensions.Caching.Distributed;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Data.Standard.Finance;
using XYY.Model.Standard.Enums;
using XYY.Model.Standard.Finance;
using XYY.Common.Standard;
using XYY.Data.Standard.Sys;
using XYY.Model.Standard.Sys;
using XYY.Service.Standard.Channel;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;
using XYY.Model.Standard.Dto.Finance;
using XYY.Service.Standard.Customer;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using XYY.TaskTrack.Standard.TaskModel;
using XYY.TaskTrack.Standard;
using XYY.Model.Standard;
using System.IO;
using XYY.Service.Standard.Finance.Model;
using static XYY.Data.Standard.Finance.Finance_Customer_SupplementRepository;
using DingTalk.Api;
using DingTalk.Api.Request;
using DingTalk.Api.Response;
using Newtonsoft.Json;
using XYY.Model.Standard.DingTalk;
using Nest;
using NPOI.SS.Formula.Functions;
using RestSharp;

namespace XYY.Service.Standard.Finance
{
    public interface IQuotationApproveService
    {
        Task<DingDingProcessResult> HandleQuotationApproved(string dingdingProcessId);
        Task<DingDingProcessResult> HandleQuotationDiscountApproved(string dingdingProcessId);
        Task<long> SaveApprove(Finance_QutationApprove approve);
        Task<bool> SaveApproveDetail(List<Finance_QutationApproveDetail> approveDetail);
        Task<long> SaveApprove(string dingdingProcessId, int[] changeCustomers, int expressId, DateTime setTime, int userId, IEnumerable<View_Quotation> detail, string UploadedFileUrl = "", string Remark = "");
        Task<long> SaveApproveByDiscount(string dingdingProcessId, int userId, DiscountDto dto);

        Task<bool> UpdateApproveStatus(string dingdingProcessId, DateTime approveTime, QutationApproveStatus status, string message = null);
        Task<PageResult<QuotationApproveRecordDto>> QueryApproveRecord(QueryModel queryModel);
        Task<IEnumerable<Finance_QutationApproveDetail>> GetQuotationDetail(int id);
        Task<DingDingProcessResult> HandleQuotationReject(string processInstanceId);
        Task<string> GetGZKDFee(int customerId);

        Task<string> GetCDKDFee(int customerId);
        Task<string> SendNowCustomerQuotaion(FinanceQuotation financeQuotation);
        Task<byte[]> ExportQuotation(int customerId);
        Task<byte[]> ExportQuotation(List<int> customerIds, DateTime time);
        Task UpdateFeeBaseFile(GenerateFeeBaseFileMessage param);
        Task SendGenerateFeeBaseFileMessage(List<int> feeBaseIds, int approveQuotationId, string versionName);
        Task<string> GetGZKDFee2022(int customerId, bool isChengDuQuotation = false);
    }

    public class QuotationApproveService : IQuotationApproveService
    {
        ILogistics_ExpressFeeRangeRepository _rangerepository;
        IFinance_QutationApproveRepository _approveRepository;
        IFinance_QutationApproveDetailRepository _approveDetailRepository;
        IDistributedCache _distributedCache;
        ISys_CountryRepository _countryRepository;
        IChannelService _channelService;
        ILogistics_ExpressFeeBaseRepository _feeBaseRepository;
        ILogistics_Fee_FormulaRepository _formulaRepository;
        ILogistics_ExpressFeeGradsRepository _gradsRepository;
        IExcelHelper _excelHelper;
        ICustomerService _customerService;
        IView_QuotationRepository _quotationRepository;
        IQuotationService _quotationService;
        IMQManager _MQManager;
        IDingDingBaseService dingDingBaseService;
        IFinance_ServiceSurchargeService _serviceSurchargeService;
        IExpressBulkAdjustmentQuoteService _expressBulkAdjustmentQuoteService;

        public QuotationApproveService(
            IDistributedCache distributedCache,
            IChannelService channelService,
            IFinance_QutationApproveRepository approveRepository,
            IFinance_QutationApproveDetailRepository approveDetailRepository,
            ISys_CountryRepository countryRepository,
            ILogistics_ExpressFeeBaseRepository feeBaseRepository,
            ILogistics_ExpressFeeGradsRepository gradsRepository,
            IExcelHelper excelHelper,
            ICustomerService customerService,
            IView_QuotationRepository quotationRepository,
            IQuotationService quotationService,
             IMQManager MQManager
, IDingDingBaseService dingDingBaseService,
ILogistics_Fee_FormulaRepository formulaRepository,
ILogistics_ExpressFeeRangeRepository rangerepository,
IFinance_ServiceSurchargeService serviceSurchargeService,
IExpressBulkAdjustmentQuoteService expressBulkAdjustmentQuoteService)
        {
            _approveRepository = approveRepository;
            _approveDetailRepository = approveDetailRepository;
            _distributedCache = distributedCache;
            _countryRepository = countryRepository;
            _channelService = channelService;
            _feeBaseRepository = feeBaseRepository;
            _gradsRepository = gradsRepository;
            _excelHelper = excelHelper;
            _customerService = customerService;
            _quotationRepository = quotationRepository;
            _quotationService = quotationService;
            _MQManager = MQManager;
            this.dingDingBaseService = dingDingBaseService;
            _formulaRepository = formulaRepository;
            _rangerepository = rangerepository;
            _serviceSurchargeService = serviceSurchargeService;
            _expressBulkAdjustmentQuoteService = expressBulkAdjustmentQuoteService;
        }

        public async Task<long> SaveApprove(Finance_QutationApprove approve)
        {
            return await _approveRepository.InsertAsync(approve);
        }

        public async Task<long> SaveApprove(string dingdingProcessId, int[] changeCustomers, int expressId, DateTime setTime, int userId, IEnumerable<View_Quotation> details, string UploadedFileUrl = "", string Remark = "")
        {
            if (string.IsNullOrEmpty(dingdingProcessId))
            {
                throw new Exception("钉钉审批处理ID不能为空");
            }

            string customerIds = string.Join(",", changeCustomers);

            //这里必须要确保有逗号开头和结尾，因为有时按某个客户id去搜索时候，要用like搜索
            //如果不按逗号作为前后缀，有可能出错,比如 7可能会查询到137的结果
            //但按',7,' like ',137,'这种方式来查询是安全的
            string formattedIds = $",{customerIds},";
            Finance_QutationApprove approve = new Finance_QutationApprove
            {
                CustomerIds = formattedIds,
                EnableTime = setTime,
                ExpressId = expressId,
                UserId = userId,
                DingdingProcessId = dingdingProcessId,
                UploadedFileUrl = UploadedFileUrl,
                Remark = Remark,
            };
            long approveId = await SaveApprove(approve);
            //写死  客户判断，不需要再写详细数据.。附加费不写入详细数据
            if (changeCustomers.Any(x => x == 761) || (Remark?.Contains("附加费") ?? false)) { return approveId; }

            List<Finance_QutationApproveDetail> detailEntities = new List<Finance_QutationApproveDetail>();

            var countries = await _distributedCache.GetAsync("GetAllCountries", async () =>
            {
                var _countries = await _countryRepository.QueryAsync();
                return _countries;
            });

            foreach (var d in details)
            {
                //string[] weightRange = d.重量段?.Split('-');
                string startWeightStr = d.开始重量;
                string endWeightStr = d.结束重量;
                if (!decimal.TryParse(startWeightStr, out decimal startWeight_kg) || !decimal.TryParse(endWeightStr, out decimal endWeight_kg))
                {
                    throw new Exception("开始重量或者结束重量格式有误，应为数值");
                }

                //int startWeight_g = (int)(startWeight_kg * 1000);
                //int endWeight_g = (int)(endWeight_kg * 1000);
                decimal freight = 0m;
                if (!decimal.TryParse(d.运费, out freight))
                {
                    throw new Exception("运费无效");
                }
                if (expressId == 48 || expressId == 50)
                {
                    //操作费即分区，无需处理
                }
                //所有空派的都要加判断,非空派才执行这段
                else if (expressId != 311 && expressId != 312 && expressId != 257 && expressId != 345
                        && !new int[] { 366, 367, 368, 369, 358, 413, 414, 415, 416, 417, 418, 489, 518, 517, 516, 515, 514, 513, 520, 532, 533,567 }.Contains(expressId)
                )
                {
                    decimal operateFee = 0m;
                    if (!decimal.TryParse(d.操作费, out operateFee))
                    {
                        throw new Exception("操作费无效");
                    }
                }
                //特殊的空派渠道，主要是有首续重的小包货的渠道
                else if (new int[] { 518, 517, 516, 515, 514, 513,567 }.Contains(expressId))
                {
                    var frist = countries.FirstOrDefault(x => x.CnName == d.操作费);
                    if (frist == null)
                    {
                        //找不到国家就验证费用值，拼接字符串  0是操作费，1是国家
                        var arry = d.操作费.Split("-");
                        var tempFirst = countries.FirstOrDefault(x => x.CnName == arry[1]);
                        if (tempFirst == null) { throw new Exception(arry[1] + "找不到相关的国家简码"); }
                        d.国家 = tempFirst.Code;//用这个字段暂时记国家
                        decimal operateFee = 0m;
                        if (!decimal.TryParse(arry[0], out operateFee))
                        {
                            throw new Exception("操作费无效");
                        }
                    }
                    else
                    {
                        d.操作费 = frist.Code;
                        d.国家 = frist.Code;//用这个字段暂时记国家
                    }
                }
                //正常空派的流程
                else
                {
                    var frist = countries.FirstOrDefault(x => x.CnName == d.操作费);
                    if (frist == null)
                    {
                        if (expressId != 257 && expressId != 345 && !new int[] { 366, 367, 368, 369, 358, 413, 416, 417, 418, 489, 520, 532, 533 }.Contains(expressId))
                        {
                            throw new Exception(frist + "找不到相关的国家简码");
                        }
                    }

                    else
                        d.操作费 = frist.Code;
                }




                Finance_QutationApproveDetail detail = new Finance_QutationApproveDetail
                {
                    ApproveId = (int)approveId,
                    EndWeight = endWeight_kg,
                    StartWeight = startWeight_kg,
                    Freight = freight,
                    OperateFee = d.操作费,
                    Remark=d.国家,
                };

                detailEntities.Add(detail);
            }

            if (detailEntities.Count > 0) { await SaveApproveDetail(detailEntities); }

            return approveId;
        }

        public async Task<long> SaveApproveByDiscount(string dingdingProcessId, int userId, DiscountDto dto)
        {
            if (string.IsNullOrEmpty(dingdingProcessId))
            {
                throw new Exception("钉钉审批处理ID不能为空");
            }
            string customerIds = string.Join(",", dto.discountData.Select(x => x.customerId).Distinct().ToList());

            string formattedIds = $",{customerIds},";
            Finance_QutationApprove approve = new Finance_QutationApprove
            {
                CustomerIds = formattedIds,
                EnableTime = dto.setTime,
                ExpressId = dto.ExpressId,
                UserId = userId,
                DingdingProcessId = dingdingProcessId,
                UploadedFileUrl = "折扣",//用于判断任务是否重复的依据，与报价调整区别，报价中，该字段可能为文件路径
            };
            long approveId = await SaveApprove(approve);
            return approveId;
        }
        public class PublicNote
        {
            public int GroupOrder { get; set; }
            public string GroupName { get; set; }
            public string SheetName { get; set; }
            public string Name
            {
                get; set;
            }

            public string ReferenceTime
            {
                get; set;
            }

            public string Default
            {
                get; set;
            }
            public string PostalProperty { get; set; }
            public string UpdateTime { get; set; }
            public string UpdateContent { get; set; }
        }


        public async Task GetPublic(Application app, Workbook source)
        {
            GetNote("");
            _Workbook templateWorkBook = app.Workbooks._Open(System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "主文件.xlsx"));
            var tempsheet = (Worksheet)templateWorkBook.ActiveSheet;
            tempsheet.Copy(source.Sheets[1], Type.Missing);
            templateWorkBook.Close();
            var sheet = (Worksheet)source.ActiveSheet;
            object[,] obj = new object[source.Sheets.Count, 5];
            int i = 0;
            foreach (Worksheet item in source.Sheets)
            {
                if (item.Name != "汇总")
                {
                    var note = GetNote(item.Name);
                    if (note != null)
                    {
                        obj[i, 0] = i + 1;
                        obj[i, 1] = item.Name;
                        obj[i, 2] = note.ReferenceTime;
                        obj[i, 3] = note.Default;
                        obj[i, 4] = "点击进入";
                        i++;
                    }
                }
            }
            var range = sheet.get_Range("B3");
            range = range.get_Resize(i, 5);
            range.set_Value(Missing.Value, obj);
            range.Borders.LineStyle = XlLineStyle.xlContinuous;
            range.RowHeight = 50;
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.Font.Size = 11;
            i = 0;

            foreach (Worksheet item in source.Sheets)
            {
                if (item.Name != "汇总")
                {
                    var note = GetNote(item.Name);
                    if (note != null)
                    {
                        i++;
                        var r = sheet.get_Range("F" + (i + 2));
                        sheet.Hyperlinks.Add(r, $"#{note.Name}!A1", Type.Missing, Type.Missing, Type.Missing);
                    }
                }
            }
            range.Font.Underline = false;
            range = sheet.get_Range("C3");
            range = range.get_Resize(i, 1);
            range.Font.Bold = true;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[2];
            range.ColumnWidth = 10.88;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[3];
            range.ColumnWidth = 19.88;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[4];
            range.ColumnWidth = 38;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[5];
            range.ColumnWidth = 59.5;


            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[6];
            range.ColumnWidth = 21.25;
            sheet.Protect("xyy0601");
        }

        public PublicNote GetNote(string name)
        {
            var list = new List<PublicNote>()
            {
                new PublicNote { Name="普货专线",ReferenceTime="7-15工作日",Default="只接普货、航班及时稳定、全程可追踪，签收率高"},
                new PublicNote { Name="带电专线",ReferenceTime="7-15工作日",Default="可走带电带磁等敏感货、航班及时稳定、全程可追踪，签收率高"},
                new PublicNote { Name="快线渠道",ReferenceTime="5-7工作日",Default="时效快、航班及时稳定、全程可追踪，签收率高"},
                new PublicNote { Name="广州FedEx",ReferenceTime="7-9自然日",Default="只接普货、上网快、时效稳定"},
                new PublicNote { Name="广州DHL",ReferenceTime="5-7自然日",Default="只接普货、上网快、时效稳定"},
                new PublicNote { Name="欧洲空派",ReferenceTime="6-8工作日提取派送",Default="国内包机直飞航班、此渠道只接受普货"},
                new PublicNote { Name="加拿大空派",ReferenceTime="8-15个自然日签收",Default="国内包机直飞航班、此渠道只接受普货"},
                new PublicNote { Name="美国空派",ReferenceTime="8-15个自然日签收",Default="国内包机直飞航班、此渠道只接受普货"},
                new PublicNote { Name="美国海派",ReferenceTime="11天航期、24小时快提",Default="固定船班、只接大货"},
                new PublicNote { Name="香港快递",ReferenceTime="2-4自然日",Default="只发香港、时效稳定"},
                new PublicNote { Name="E特快",ReferenceTime="15-20工作日",Default="只接普货、上网快、全程可追踪、一单到底"},
                new PublicNote { Name="普货E邮宝",ReferenceTime="25-30工作日",Default="只接普货、上网快、全程可追踪、一单到底"},
                new PublicNote { Name="特货E邮宝",ReferenceTime="25-30工作日",Default="可走带电带磁等敏感货、上网快、全程可追踪、一单到底"},
                new PublicNote { Name="成都FedEx",ReferenceTime="7-9自然日",Default="只接普货、上网快、时效稳定"},
            };
            return list.Where(x => x.Name == name).FirstOrDefault();
        }

        public async Task<bool> SaveApproveDetail(List<Finance_QutationApproveDetail> approveDetails)
        {
            foreach (var detail in approveDetails)
            {
                if (detail.CreateTime == null)
                {
                    detail.CreateTime = DateTime.Now;
                }
                if (string.IsNullOrWhiteSpace(detail.CreateUserName))
                {
                    detail.CreateUserName = "admin";
                }
            }
            return await _approveDetailRepository.BulkToDBAsync(approveDetails);
        }

        private async Task<string> TryGetCountryCode(int expressId)
        {
            var countries = await _distributedCache.GetAsync("GetAllCountries", async () =>
            {
                var _countries = await _countryRepository.QueryAsync();
                return _countries;
            });

            var publics = await _channelService.CacheGetAllPublic();
            var channels = await _channelService.CacheGetAllChannel();
            var channel = channels?.FirstOrDefault(c => c.Id == expressId);


            var publicChannel = publics.FirstOrDefault(p => p.Code == channel.PublicCode);
            if (publicChannel.ChannelType == ChannelType.FBA)
            {
                return "FBA";
            }
            else
            {

                var channelName = publicChannel?.Name;
                if (string.IsNullOrEmpty(channelName))
                {
                    throw new Exception($"找不到渠道信息:{expressId}");
                }
                var country = countries.FirstOrDefault(c => channelName.Contains(c.CnName));
                if (country == null)
                {
                    if (channelName.Contains("澳洲"))
                    {
                        country = countries.FirstOrDefault(c => c.CnName == "澳大利亚");
                    }
                }

                var code = country?.Code;
                return code;
            }
        }

        private async Task GenAndUpdateFile(List<int> feebaseIds, int approveId, string versionName)
        {
            var approveDetails = await _approveDetailRepository.QueryAsync(i => i.ApproveId == approveId);

            if (approveDetails?.Any() == true)
            {
                List<QutationTemplateModel> list = new List<QutationTemplateModel>();
                foreach (var d in approveDetails)
                {
                    QutationTemplateModel model = new QutationTemplateModel
                    {
                        Freight = d.Freight,
                        EndWeight = d.EndWeight,
                        OperateFee = decimal.Parse(d.OperateFee),
                        StartWeight = d.StartWeight
                    };
                    list.Add(model);
                }

                string date = DateTime.Now.ToString("yyyy-MM-dd");
                string fileName = $"channelFee/{date}/{versionName}.xlsx";

                string file = _excelHelper.OutData<QuationTemplateMapping, QutationTemplateModel>(list, fileName);
                string logcalPath = new Uri(file).LocalPath;

                await _feeBaseRepository.UpdateByConditionAsync(i => i.Id.In(feebaseIds), new { FileUrl = logcalPath });

            }
        }

        public class Link
        {
            /// <summary>
            /// 
            /// </summary>
            public string messageUrl { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string picUrl { get; set; }
            /// <summary>
            /// 测试
            /// </summary>
            public string title { get; set; }
            /// <summary>
            /// 测试
            /// </summary>
            public string text { get; set; }
        }

        public class LinkRoot
        {
            /// <summary>
            /// 
            /// </summary>
            public string msgtype { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public Link link { get; set; }
        }

        public async Task<DingDingProcessResult> HandleQuotationApproved(string dingdingProcessId)
        {
            Finance_QutationApprove approveQuotation = null;
            try
            {
                var approveQuotations = await _approveRepository.QueryAsync(i => i.DingdingProcessId == dingdingProcessId);
                approveQuotation = approveQuotations?.FirstOrDefault();

                if (approveQuotation == null)
                {
                    throw new Exception($"待审核的报价不存在，钉钉审核事项Id:（{dingdingProcessId}）");
                }
                if (approveQuotation.Status != QutationApproveStatus.UnApproved && approveQuotation.Status != QutationApproveStatus.Failed)
                {
                    string status = approveQuotation.Status.GetDescription();
                    throw new Exception($"审批失败:该报价当前状态为:{status}");
                }
                int approveQuotationId = approveQuotation.Id;
                var approveDetails = await _approveDetailRepository.QueryAsync(i => i.ApproveId == approveQuotationId);
                if (approveDetails?.Any() != true && !approveQuotation.CustomerIds.Contains(",761,") && !approveQuotation.Remark.Contains("附加费"))
                {
                    throw new Exception($"报价审批明细不存在");
                }


                string countryCode = await TryGetCountryCode(approveQuotation.ExpressId);
                if (string.IsNullOrEmpty(countryCode)
                    && !approveQuotation.CustomerIds.Contains(",761,")
                    && approveQuotation.ExpressId != 48 && approveQuotation.ExpressId != 50
                    )
                {
                    throw new Exception($"尝试匹配渠道国家失败,ExpressId:{approveQuotation.ExpressId}");
                }

                List<Logistics_ExpressFeeGrads> grads = new List<Logistics_ExpressFeeGrads>();
                List<Logistics_ExpressFeeRange> ranges = new List<Logistics_ExpressFeeRange>();
                if (approveQuotation.ExpressId == 48 || approveQuotation.ExpressId == 50)
                {
                    //附加费单独特别处理，并直接返回
                    if (approveQuotation.Remark?.Contains("附加费") ?? false)
                    {
                        await _serviceSurchargeService.AddServiceSurcharge(approveQuotation);
                        await UpdateApproveStatus(dingdingProcessId, DateTime.Now, QutationApproveStatus.Approved);
                        //生成发送消息MQ
                        foreach (var item in approveQuotation.CustomerIds.Split(',', StringSplitOptions.RemoveEmptyEntries))
                        {
                            await _MQManager.Publish(new FinanceQuotation
                            {
                                CusotmerId = int.Parse(item),
                                CustomerName = string.Empty,
                                EntryTime = approveQuotation.EnableTime.ToString("yyyy年MM月dd日"),
                                UserId = approveQuotation.UserId
                            });
                        }
                        return new DingDingProcessResult { IsSuccess = true }; ;
                    }
                    else
                    {
                        //计费公式处理
                        bool formulaResult = await FeeFormulaIns(approveQuotation, approveDetails);
                        var zones = await _feeBaseRepository.QueryZone(approveQuotation.ExpressId);
                        foreach (var detail in approveDetails)
                        {
                            foreach (var c in zones.Where(x => x.Zone == detail.OperateFee))
                            {
                                Logistics_ExpressFeeRange range = new Logistics_ExpressFeeRange
                                {
                                    Countory = c.CountryCode,
                                    Expressid = approveQuotation.ExpressId,
                                    BaseId = 0,
                                    StartWeight = (int)(detail.StartWeight * 1000),
                                    EndWeight = (int)(detail.EndWeight * 1000),
                                    Fee = detail.Freight,
                                    IsDelete = false,
                                    CreateTime = DateTime.Now,
                                    CreateUserName = "admin"
                                };
                                ranges.Add(range);
                            }
                        }
                        //判断数据是否需要记录，并完成记录动作 改为按钮点击执行
                        //await _expressBulkAdjustmentQuoteService.AddQuoteJob(approveQuotation);
                        //测试使用，停止往后执行
                        //return new DingDingProcessResult { IsSuccess = true }; ;
                    }
                }
                else if (approveQuotation.CustomerIds.Contains(",761,"))
                {
                    grads = await ConvertToExpressFeeGrads(approveQuotation);
                }
                else if (new int[] { 518, 517, 516, 515, 514, 513,567 }.Contains(approveQuotation.ExpressId))//空派渠道存在首续重的奇葩情况
                {
                    //通过备注的国家来区分 分区
                    var approveDetailsGroup = approveDetails.GroupBy(x => x.Remark);
                    foreach (var item in approveDetailsGroup)
                    {
                        foreach (var detail in item)
                        {
                            var freight_g = detail.StartWeight > 1 ? detail.Freight : detail.StartWeight * detail.Freight;
                            var continuedFee = freight_g;
                            int startWeight_g = (int)(detail.StartWeight * 1000);
                            int endWeight_g = (int)(detail.EndWeight * 1000);
                            var firstFee = detail.StartWeight > 1 ? (continuedFee * detail.StartWeight) : decimal.Parse(detail.OperateFee.Split("-")[0]);
                            Logistics_ExpressFeeGrads grad = new Logistics_ExpressFeeGrads
                            {
                                BaseId = 0,
                                FristWeight = startWeight_g,
                                MaxWeight = endWeight_g,
                                ContinuedFee = continuedFee,
                                ContinuedWeight = detail.StartWeight > 1 ? 1000 : 500,//写死非1K即500g
                                Countory = item.Key,
                                Expressid = approveQuotation.ExpressId,
                                FristFee = firstFee,
                                CreateTime = DateTime.Now,
                                CreateUserName = "admin"
                            };
                            grads.Add(grad);
                        }
                    }
                }
                else if (countryCode != "FBA")
                {
                    foreach (var detail in approveDetails)
                    {
                        var freight_g = detail.Freight / 1000;
                        var continuedFee = freight_g;
                        int startWeight_g = (int)(detail.StartWeight * 1000);
                        int endWeight_g = (int)(detail.EndWeight * 1000);
                        var firstFee = (continuedFee * startWeight_g) + decimal.Parse(detail.OperateFee);
                        Logistics_ExpressFeeGrads grad = new Logistics_ExpressFeeGrads
                        {
                            BaseId = 0,
                            FristWeight = startWeight_g,
                            MaxWeight = endWeight_g,
                            ContinuedFee = continuedFee,
                            ContinuedWeight = 1,
                            Countory = countryCode,
                            Expressid = approveQuotation.ExpressId,
                            FristFee = firstFee,
                            CreateTime = DateTime.Now,
                            CreateUserName = "admin"
                        };
                        grads.Add(grad);
                    }
                }
                else
                {
                    foreach (var detail in approveDetails)
                    {
                        var freight_g = detail.Freight;
                        var continuedFee = freight_g;
                        int startWeight_g = (int)(detail.StartWeight * 1000);
                        int endWeight_g = (int)(detail.EndWeight * 1000);
                        var firstFee = (continuedFee * detail.StartWeight);
                        Logistics_ExpressFeeGrads grad = new Logistics_ExpressFeeGrads
                        {
                            BaseId = 0,
                            FristWeight = startWeight_g,
                            MaxWeight = endWeight_g,
                            ContinuedFee = continuedFee,
                            ContinuedWeight = 1000,
                            Countory = detail.OperateFee,
                            Expressid = approveQuotation.ExpressId,
                            FristFee = firstFee,
                            CreateTime = DateTime.Now,
                            CreateUserName = "admin"
                        };
                        grads.Add(grad);

                    }
                }

                string versionName = approveQuotation.EnableTime.ToString("yy-MM-dd");

                var customerFeeBases = await CreateCustomerFeeBases(approveQuotation);

                List<int> feeBaseIds = new List<int>();
                foreach (var feeBase in customerFeeBases)
                {
                    long id = await AddNewFeeBase(feeBase, grads, ranges);
                    feeBaseIds.Add((int)id);
                }

                await UpdateApproveStatus(dingdingProcessId, DateTime.Now, QutationApproveStatus.Approved);
                try
                {
                    var customerIds = customerFeeBases.Select(i => i.CustomerId);
                    var expressId = approveQuotation.ExpressId;
                    //todo:清除报价缓存
                    RefreshExpressBaseCache(customerIds, expressId);

                    await SendGenerateFeeBaseFileMessage(feeBaseIds, approveQuotationId, versionName);

                    //生成发送消息MQ
                    foreach (var item in approveQuotation.CustomerIds.Split(',', StringSplitOptions.RemoveEmptyEntries))
                    {
                        await _MQManager.Publish(new FinanceQuotation
                        {
                            CusotmerId = int.Parse(item),
                            CustomerName = string.Empty,
                            EntryTime = approveQuotation.EnableTime.ToString("yyyy年MM月dd日"),
                            UserId = approveQuotation.UserId
                        });
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return new DingDingProcessResult { IsSuccess = true }; ;

            }
            catch (Exception ex)
            {
                var message = ex.Message;
                await UpdateApproveStatus(dingdingProcessId, DateTime.Now, QutationApproveStatus.Failed, message);

                int userId = 0;
                if (approveQuotation != null)
                {
                    userId = approveQuotation.UserId;
                }
                var ddUserInfo = await dingDingBaseService.GetDingDingUserInfo(userId);
                if (ddUserInfo != null)
                {
                    var ddModel = new
                    {
                        msgtype = "text",
                        text =
                          new
                          {
                              content = $"报价审核失败:{ex.Message}"
                          }
                    };
                    await dingDingBaseService.SendMessage(ddUserInfo.UserId, ddModel);
                }
                else
                {
                    throw new Exception($"审批报价出现异常，尝试发送钉钉消息时失败,找不到钉钉用户信息,UserId:{userId}");
                }

                return new DingDingProcessResult { IsSuccess = false, Message = message }; ;
            }

        }

        #region 邮政特殊报价解析
        public async Task<List<Logistics_ExpressFeeGrads>> ConvertToExpressFeeGrads(Finance_QutationApprove approveQuotation)
        {
            List<Logistics_ExpressFeeGrads> expressFeeGrads = new List<Logistics_ExpressFeeGrads>();
            System.Net.WebClient client = new System.Net.WebClient();
            var dataByte = client.DownloadData(approveQuotation.UploadedFileUrl);
            var channels = await _channelService.CacheGetAllChannel();
            var publics = await _channelService.CacheGetAllPublic();

            var channel = channels.Where(x => x.Id == approveQuotation.ExpressId).FirstOrDefault();
            var publicChannel = publics.Where(x => x.Code == channel.PublicCode).FirstOrDefault();

            var quotations = _quotationService.ImportExcelToChinaPostPublicJson(dataByte, publicChannel.Name);
            foreach (var item in quotations)
            {
                int FristWeight = approveQuotation.ExpressId == 106 ? (int)(Convert.ToDecimal(item.起重) * 1000) : Convert.ToInt32(item.起重);
                int ContinuedWeight = approveQuotation.ExpressId == 106 ? (int)(Convert.ToDecimal(item.续重) * 1000) : Convert.ToInt32(item.续重);
                int MaxWeight = approveQuotation.ExpressId == 106 ? (int)(Convert.ToDecimal(item.限重) * 1000) : Convert.ToInt32(item.限重);
                decimal ContinuedFee = approveQuotation.ExpressId == 106 ? Convert.ToDecimal(item.运费) : Convert.ToDecimal(item.运费) / 1000.00M;
                decimal FristFee = approveQuotation.ExpressId == 106 ? Convert.ToDecimal(item.操作费) : Convert.ToDecimal(item.操作费) + ContinuedFee * FristWeight;
                Logistics_ExpressFeeGrads model = new Logistics_ExpressFeeGrads()
                {
                    BaseId = 0,
                    FristWeight = FristWeight,
                    MaxWeight = MaxWeight,
                    ContinuedFee = ContinuedFee,
                    ContinuedWeight = ContinuedWeight,
                    Countory = item.二字简码,
                    Expressid = approveQuotation.ExpressId,
                    FristFee = FristFee,
                    CreateTime = DateTime.Now,
                    CreateUserName = "admin",
                };
                expressFeeGrads.Add(model);
            }
            return expressFeeGrads;
        }
        /// <summary>
        /// 折扣数据解析
        /// </summary>
        /// <param name="approveQuotation"></param>
        /// <returns></returns>
        public async Task<List<Logistics_Fee_Other>> ConvertToFeeOthers(Finance_QutationApprove approveQuotation)
        {
            try
            {
                string token = await dingDingBaseService.GetTokenAsCache();
                IDingTalkClient client = new DefaultDingTalkClient("https://oapi.dingtalk.com/topapi/processinstance/get");
                OapiProcessinstanceGetRequest req = new OapiProcessinstanceGetRequest();
                req.ProcessInstanceId = approveQuotation.DingdingProcessId;
                OapiProcessinstanceGetResponse rsp = client.Execute(req, token);

                var customerall = await _customerService.CacheGetAllCustomers();
                //数据解析 
                List<Logistics_Fee_Other> others = new List<Logistics_Fee_Other>();
                foreach (var item in rsp.ProcessInstance.FormComponentValues)
                {
                    if (item.Name == "折扣详情")
                    {
                        var value_value = JsonConvert.DeserializeObject<List<form_value_tablefieid>>(item.Value);
                        if (value_value.Count == 0) { continue; }
                        foreach (var value in value_value)
                        {
                            Logistics_Fee_Other other = new Logistics_Fee_Other()
                            {
                                ExpressId = approveQuotation.ExpressId,
                                FeeType = 4,
                                EffectiveDate = approveQuotation.EnableTime,
                                CreateTime = DateTime.Now,
                                CreateUserName = approveQuotation.CreateUserName,
                                IsEffective = true,
                                IsPercentByCodHandCost = true,
                            };
                            foreach (var ext in value.rowValue)
                            {
                                if (ext.label == "客户")
                                {
                                    var customer = customerall.Where(x => x.CompanyName == ext.value.ToString())?.FirstOrDefault();
                                    if (customer == null) { throw new Exception($"客户名称匹配错误【{ext.value}】"); }
                                    other.CustomerId = customer.Id;
                                }
                                else if (ext.label == "折扣")
                                {
                                    other.Price = Convert.ToInt32(ext.value) / 100.0M;
                                }
                            }
                            others.Add(other);
                        }
                    }
                }
                return others;
            }
            catch (Exception ex)
            {
                throw new Exception("折扣审批，解析申请数据异常：" + ex.Message);
            }
        }
        #endregion
        #region fedex 和dhl 报价计费公式处理
        private async Task<bool> FeeFormulaIns(Finance_QutationApprove approveQuotation, IEnumerable<Finance_QutationApproveDetail> approveDetails)
        {
            bool result = true;
            try
            {
                var customerIds = approveQuotation.CustomerIds.Split(',', StringSplitOptions.RemoveEmptyEntries);
                string sql = $"select * from Logistics_Fee_Formula(nolock) where ExpressId={approveQuotation.ExpressId} and EffectiveDate='{approveQuotation.EnableTime}' and CustomerId in ({string.Join(',', customerIds)})  ";
                var formulas = await _formulaRepository.QueryBySqlAsync(sql);
                List<Logistics_Fee_Formula> newFormulas = new List<Logistics_Fee_Formula>();
                foreach (var item in customerIds)
                {
                    var temp = formulas.Where(x => x.CustomerId.ToString() == item)?.FirstOrDefault();
                    if (temp == null)
                    {
                        Logistics_Fee_Formula model = new Logistics_Fee_Formula()
                        {
                            ExpressId = approveQuotation.ExpressId,
                            Formula = "@原价+@原价*@燃油费",
                            EffectiveDate = approveQuotation.EnableTime,
                            CustomerId = Convert.ToInt32(item),
                            CreateTime = DateTime.Now,
                            Remark = "快递报价审批，系统自动写入",
                        };
                        newFormulas.Add(model);
                    }
                }
                if (newFormulas.Count > 0) { await _formulaRepository.BulkToDBAsync(newFormulas); }
            }
            catch (Exception ex)
            {
                result = false;
                throw new Exception("计费公式初始化发生异常！" + ex.Message);
            }
            return result;
        }
        #endregion

        public async Task<DingDingProcessResult> HandleQuotationDiscountApproved(string dingdingProcessId)
        {
            Finance_QutationApprove approveQuotation = null;
            try
            {
                var approveQuotations = await _approveRepository.QueryAsync(i => i.DingdingProcessId == dingdingProcessId);
                approveQuotation = approveQuotations?.FirstOrDefault();

                if (approveQuotation == null)
                {
                    throw new Exception($"待审核的报价不存在，钉钉审核事项Id:（{dingdingProcessId}）");
                }
                if (approveQuotation.Status != QutationApproveStatus.UnApproved)
                {
                    string status = approveQuotation.Status.GetDescription();
                    throw new Exception($"审批失败:该报价当前状态为:{status}");
                }
                var FeeOthers = await ConvertToFeeOthers(approveQuotation);

                //旧数据处理，设置为无效
                await _approveRepository.UpdateFeeOthersTo(FeeOthers);

                await _approveRepository.BulkFeeOthers(FeeOthers);

                List<int> customers = new List<int>();
                customers = FeeOthers.Select(i => i.CustomerId).ToList();

                //如果使用折扣价的话，代表已经接受了公开价处理流程，我们写入一个公开价引入过程。
                await AddFeeBase(approveQuotation, customers);


                await UpdateApproveStatus(dingdingProcessId, DateTime.Now, QutationApproveStatus.Approved);
                try
                {
                    var customerIds = FeeOthers.Select(i => i.CustomerId);
                    var expressId = approveQuotation.ExpressId;
                    //todo:重加载报价、折扣、公式缓存
                    RefreshExpressBaseCache(customerIds, expressId, "BatchRefreshOtherRedisCache");
                    RefreshExpressBaseCache(customerIds, expressId, "RefreshFormulaRedisCache");
                    RefreshExpressBaseCache(customerIds, expressId, "BatchRefreshExpressBaseRedisCache");

                    //生成发送消息MQ
                    foreach (var item in approveQuotation.CustomerIds.Split(',', StringSplitOptions.RemoveEmptyEntries))
                    {
                        await _MQManager.Publish(new FinanceQuotation
                        {
                            CusotmerId = int.Parse(item),
                            CustomerName = string.Empty,
                            EntryTime = approveQuotation.EnableTime.ToString("yyyy年MM月dd日"),
                            UserId = approveQuotation.UserId
                        });
                    }

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return new DingDingProcessResult { IsSuccess = true };
            }
            catch (Exception ex)
            {
                var message = ex.Message;
                await UpdateApproveStatus(dingdingProcessId, DateTime.Now, QutationApproveStatus.Failed, message);

                int userId = 0;
                if (approveQuotation != null)
                {
                    userId = approveQuotation.UserId;
                }
                var ddUserInfo = await dingDingBaseService.GetDingDingUserInfo(userId);
                if (ddUserInfo != null)
                {
                    var ddModel = new
                    {
                        msgtype = "text",
                        text =
                          new
                          {
                              content = $"折扣审核失败:{ex.Message}"
                          }
                    };
                    await dingDingBaseService.SendMessage(ddUserInfo.UserId, ddModel);
                }
                else
                {
                    throw new Exception($"审批折扣出现异常，尝试发送钉钉消息时失败,找不到钉钉用户信息,UserId:{userId}");
                }

                return new DingDingProcessResult { IsSuccess = false, Message = message };
            }
        }

        private async Task AddFeeBase(Finance_QutationApprove approveQuotation, List<int> customers)
        {
            string sql =
                @"select CustomerId from 
                            Logistics_ExpressFeeBase(nolock) 
                      where CustomerId in @CustomerId and ExpressId = @ExpressId and CalculateType = 3 and EnableTime = @EnableTime";
            var list = await _feeBaseRepository.QueryBySqlAsync(sql,
                new
                {
                    CustomerId = customers,
                    ExpressId = approveQuotation.ExpressId,
                    EnableTime = approveQuotation.EnableTime
                });

            string sql2 = @"select CustomerId 
                    from Logistics_Fee_Formula(nolock)
                    where CustomerId in @CustomerId and ExpressId = @ExpressId ";
            var list2 = await _feeBaseRepository.QueryBySqlAsync(sql2, new
            {
                CustomerId = customers,
                ExpressId = approveQuotation.ExpressId,
            });


            //查找公开价
            var refBaseExpress = (await _feeBaseRepository.QueryAsync(x => x.CustomerId == 761
            && x.ExpressId == approveQuotation.ExpressId))
            .OrderByDescending(x => x.EnableTime)
            .FirstOrDefault();
            //查找公式
            var refFromath = (await _formulaRepository.QueryAsync(x => x.CustomerId == 761
            && x.ExpressId == approveQuotation.ExpressId))
            .OrderByDescending(x => x.EffectiveDate).FirstOrDefault();
            if (refFromath != null)
            {
                int[] notHasNewFromath = customers.Where(x => !list2.Any(y => y.CustomerId == x)).ToArray();
                var formList = notHasNewFromath.Select(x => new Logistics_Fee_Formula
                {
                    EffectiveDate = refFromath.EffectiveDate,
                    CustomerId = x,
                    ExpressId = approveQuotation.ExpressId,
                    Formula = refFromath.Formula,
                    CreateTime = DateTime.Now
                }).ToList();
                await _formulaRepository.BulkToDBAsync(formList);
            }


            if (refBaseExpress != null)
            {
                int[] notHasNewFeeBase = customers.Where(x => !list.Any(y => y.CustomerId == x)).ToArray();

                var AddList = notHasNewFeeBase.Select(x => new Logistics_ExpressFeeBase
                {
                    BaseExpressId = refBaseExpress.Id,
                    ExpressId = approveQuotation.ExpressId,
                    CustomerId = x,
                    Currency = "RMB",
                    EnableTime = approveQuotation.EnableTime,
                    AreaType = AreaType.无,
                    CalculateType = CalculateType.引用计费,
                    FileUrl = String.Empty,
                    IsEnable = true,
                    Versions = int.Parse(approveQuotation.EnableTime.ToString("yyyyMMdd")),
                    VersionsName = approveQuotation.EnableTime.ToString("yyyyMMdd")
                }).ToList();
                if (AddList != null && AddList.Count > 0)
                {
                    await _feeBaseRepository.BulkToDBAsync(AddList);
                }
            }
            else
            {
                throw new Exception("未找到公开价配置");
            }

        }

        private void RefreshExpressBaseCache(IEnumerable<int> customerIds, int expressId, string method = "BatchRefreshExpressBaseRedisCache")
        {
            string url = "http://120.24.149.148:9503";
            RestSharp.RestClient restClient = new RestSharp.RestClient(url);
            RestSharp.RestRequest request = new RestSharp.RestRequest($"api/RedisCacheMange/{method}", RestSharp.Method.POST);
            request.AddHeader("Authorization", "token A2DGA5DGAD2DGC6CEB59F383A6615GE2");
            var response = request.AddJsonBody(new
            {
                CustomerIds = customerIds,
                ExpressId = expressId
            });
            restClient.Execute(request);
        }

        public async Task SendGenerateFeeBaseFileMessage(List<int> feeBaseIds, int approveQuotationId, string versionName)
        {
            var msg = new GenerateFeeBaseFileMessage
            {
                ApproveId = approveQuotationId,
                FeeBaseIds = feeBaseIds,
                FeeBaseVersionName = versionName
            };
            //await UpdateFeeBaseFile(msg);
            await _MQManager.Publish(msg);
        }

        //private async Task DeleteDuplicateFees(Finance_QutationApprove approveQuotation)
        //{
        //    var customerIds = approveQuotation.CustomerIds.Split(',', StringSplitOptions.RemoveEmptyEntries);

        //    DateTime now = DateTime.Now.Date;
        //    int expressId = approveQuotation.ExpressId;

        //    List<int> customerIdList = new List<int>();
        //    foreach (var customerIdStr in customerIds)
        //    {
        //        if (!int.TryParse(customerIdStr, out int customerId))
        //        {
        //            throw new Exception($"删除重复报价时出错，无效的客户ID：{customerIdStr}");
        //        }
        //        customerIdList.Add(customerId);
        //    }

        //    var existsUnEnableFees = await _feeBaseRepository.QueryAsync(i => i.IsEnable == true && i.CustomerId.In(customerIdList) && i.ExpressId == expressId && i.EnableTime >= now);
        //    if (existsUnEnableFees?.Any() == true)
        //    {
        //        var ids = existsUnEnableFees.Select(i => i.Id);
        //        await _feeBaseRepository.DeleteAsync(i => i.Id.In(ids));
        //    }


        //}

        public async Task<bool> UpdateApproveStatus(string dingdingProcessId, DateTime approveTime, QutationApproveStatus status, string message = null)
        {
            return await _approveRepository.UpdateByConditionAsync(i => i.DingdingProcessId == dingdingProcessId, new { Status = status, ApproveTime = approveTime, ProcessMessage = message });
        }

        public async Task UpdateFeeBaseFile(GenerateFeeBaseFileMessage param)
        {
            if (param == null)
                throw new Exception("参数不能为空");
            if (param.FeeBaseIds?.Any() != true)
                throw new Exception("FeeBase Id不能为空");
            if (param.ApproveId <= 0)
                throw new Exception("Quotation Approve Id无效");
            if (string.IsNullOrWhiteSpace(param.FeeBaseVersionName))
                throw new Exception("参数VersionName不能为空");

            await GenAndUpdateFile(param.FeeBaseIds, param.ApproveId, param.FeeBaseVersionName);
        }

        public async Task<long> AddNewFeeBase(Logistics_ExpressFeeBase feeBase, List<Logistics_ExpressFeeGrads> grads, List<Logistics_ExpressFeeRange> rangs)
        {
            var _base = (await _feeBaseRepository.QueryBySqlAsync(
                $"select * from Logistics_ExpressFeeBase(nolock) where EnableTime='{feeBase.EnableTime}' and ExpressId={feeBase.ExpressId} and CustomerId={feeBase.CustomerId}"))
                .FirstOrDefault();

            if (_base != null)
            //相同的报价，我们直接清掉
            {
                await _feeBaseRepository.DeleteByIdAsync(_base.Id);
                await _gradsRepository.UpdateByConditionAsync(x => x.BaseId == _base.Id, new { IsDelete = true });
                //if (feeBase.EnableTime > DateTime.Now)
                //{
                //    //var _base = (await _feeBaseRepository.QueryAsync(x => x.EnableTime == feeBase.EnableTime && x.ExpressId == feeBase.ExpressId && x.CustomerId == feeBase.CustomerId)).First();

                //    await _feeBaseRepository.DeleteByIdAsync(_base.Id);
                //    await _gradsRepository.UpdateByConditionAsync(x => x.BaseId == _base.Id, new { IsDelete = true });
                //}
                //else
                //{
                //    //throw new Exception("已生效时间无法再次修改");
                //}
            }

            //if (await _feeBaseRepository.IsExistsAsync(x => x.VersionsName == feeBase.VersionsName && x.ExpressId == feeBase.ExpressId && x.CustomerId == feeBase.CustomerId))
            //    throw new Exception("同一渠道不可存在两个版本名称一样的报价");

            long baseId = await _feeBaseRepository.InsertAsync(feeBase);
            if (grads != null && grads.Count > 0)
            {

                grads.ForEach(g =>
                {
                    g.BaseId = (int)baseId;
                });

                await _gradsRepository.BulkToDBAsync(grads);
            }
            else if (rangs != null && rangs.Count > 0)
            {
                rangs.ForEach(g =>
                {
                    g.BaseId = (int)baseId;
                });
                await _rangerepository.BulkToDBAsync(rangs);
            }

            ///todo:插入操作记录

            //如果生效时间早于现在，则重置生效时间之后揽收的订单的费用
            //使"客户运费补充"SQL作业可以重新计算运费
            if (feeBase.EnableTime < DateTime.Now)
            {
                int customerId = feeBase.CustomerId;
                int channelId = feeBase.ExpressId;
                DateTime enableTime = feeBase.EnableTime;
                DateTime endTime = DateTime.Now;

                //检查是否存在生效时间大于当前生效的报价。
                //如果存在，则只重设当前生效时间至找到的报价的生效时间这段时间内的计费
                var existsQuotes = await _feeBaseRepository.QueryAsync(f =>
                 f.EnableTime > enableTime && f.CustomerId == customerId && f.ExpressId == channelId && f.IsEnable == true);
                if (existsQuotes?.Any() == true)
                {
                    var quote = existsQuotes.OrderBy(f => f.EnableTime).First();
                    endTime = quote.EnableTime;
                }

                await ResetOrderFee(customerId, channelId, enableTime, endTime);
            }

            return baseId;
        }

        private async Task<int> ResetOrderFee(int customerId, int channelId, DateTime feeEnableTime, DateTime endTime)
        {
            string sql =
    @"UPDATE Order_Fee SET OPCFreight=NULL 
FROM Order_Fee f inner join Order_Order o on f.OrderId = o.Id
where o.CustomerId = @CustomerId and o.CreateOrderChannelId = @CreateOrderChannelId and o.ReceiveTime >= @EnableTime and o.ReceiveTime<@EndTime";

            var param = new { CustomerId = customerId, CreateOrderChannelId = channelId, EnableTime = feeEnableTime, EndTime = endTime };
            int result = await _feeBaseRepository.ExecuteSqlAsync(sql, param);
            return result;
        }

        private async Task<List<Logistics_ExpressFeeBase>> CreateCustomerFeeBases(Finance_QutationApprove approveQuotation)
        {
            var customerIds = approveQuotation.CustomerIds.Split(',', StringSplitOptions.RemoveEmptyEntries);
            List<Logistics_ExpressFeeBase> result = new List<Logistics_ExpressFeeBase>();

            string versionName = approveQuotation.EnableTime.ToString("yy.MM.dd");

            foreach (var customerIdStr in customerIds)
            {
                if (!int.TryParse(customerIdStr, out int customerId))
                {
                    throw new Exception($"客户ID必须为数字:{customerIdStr}");
                }

                int expressId = approveQuotation.ExpressId;
                var currentVersion = await _feeBaseRepository.ExecuteScalarAsync<int>("select max(Versions) Versions from Logistics_ExpressFeeBase where expressId=@expressId and customerId=@customerId", new { expressId = expressId, customerId = customerId });
                var version = currentVersion + 1;

                Logistics_ExpressFeeBase feeBase = new Logistics_ExpressFeeBase
                {
                    AreaType = (
                    approveQuotation.ExpressId == 257
                    || approveQuotation.ExpressId == 345
                    || new int[] { 366, 367, 368, 369, 358, 413, 414, 415, 416, 417, 418, 489, 518, 517, 516, 515, 514, 513, 520,532,533 }.Contains(expressId)
                    ) ? AreaType.分区 : AreaType.无,
                    CalculateType = CalculateType.梯度重量计费,
                    Currency = "CNY",
                    CustomerId = customerId,
                    ExpressId = approveQuotation.ExpressId,
                    EnableTime = approveQuotation.EnableTime,
                    FileUrl = string.Empty,//为保证api速度，生成文件的逻辑移到mq中进行
                    IsEnable = true,
                    Versions = version,
                    VersionsName = versionName,

                };
                if (approveQuotation.ExpressId == 48 || approveQuotation.ExpressId == 50)
                {
                    feeBase.CalculateType = CalculateType.重量区间计费;
                }
                result.Add(feeBase);
            }
            return result;
        }

        private string getTempFilePath(string name)
        {
            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, name + ".xlsx");
        }

        public async Task<string> GetCDKDFee(int customerId)
        {
            MSExcelHelper exlcelHelepr = new MSExcelHelper();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.DisplayAlerts = false;
            try
            {
                var user = (await _customerService.CacheGetAllCustomers()).FirstOrDefault(x => x.Id == customerId);
                string formatterName = $"行运易 For{(string.IsNullOrEmpty(user.Abbreviation) ? user.CompanyName : user.Abbreviation)} 2022年05月01日生效({DateTime.Now.ToString("HHmmss")})";

                string filename = getTempFilePath(formatterName);
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度

                /*wbs固定*/
                _Workbook templateWorkBook = app.Workbooks._Open(System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "成都-固定.xlsx"));
                var activeBook = app.Workbooks.Add(Missing.Value);  //获取工作薄 
                var templateEUtaxSheet = (Worksheet)templateWorkBook.Worksheets[1];
                var activeEUTaxSheet = (Worksheet)activeBook.Worksheets[1];
                templateEUtaxSheet.Copy(activeEUTaxSheet, Type.Missing);
                var fedexzone = (Worksheet)templateWorkBook.Worksheets[5];
                var dhlzone = (Worksheet)templateWorkBook.Worksheets[6];
                var ozkpSheet = (Worksheet)templateWorkBook.Worksheets[7];
                var cdFedexZone = (Worksheet)templateWorkBook.Worksheets[2];
                await SetDHL(customerId, exlcelHelepr, activeBook, dhlzone);
                await SetGZFedex(customerId, exlcelHelepr, activeBook, fedexzone);
                await SetCDFedex(customerId, exlcelHelepr, activeBook, cdFedexZone);
                await SetEub(customerId, "普货E邮宝", "JSEUB", 26, exlcelHelepr, activeBook);
                await SetEub(customerId, "特货E邮宝", "THeub", 126, exlcelHelepr, activeBook);
                var templateITZone = (Worksheet)templateWorkBook.Worksheets[3];
                templateITZone.Copy(Type.Missing, (Worksheet)activeBook.ActiveSheet);
                var templateOtherFee = (Worksheet)templateWorkBook.Worksheets[4];
                templateOtherFee.Copy(Type.Missing, (Worksheet)activeBook.ActiveSheet);

                await CreateZX_KX_DDSheets(customerId, app, activeBook, ozkpSheet, true, false, showSurcharge: false);

                await GetPublic(app, activeBook);

                //activeEUTaxSheet.Visible = XlSheetVisibility.xlSheetVeryHidden;
                //activeEUTaxSheet.Move(Type.Missing, templateOtherFee);
                app.Columns.AutoFit();
                ((_Worksheet)activeBook.Worksheets[1]).Activate();

                activeBook.SaveCopyAs(filename);
                activeBook.Close(false, Type.Missing, Type.Missing);
                templateWorkBook.Close(false, Type.Missing, Type.Missing);
                return filename;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                app.Quit();
                MSExcelHelper.Kill(app);
                app = null;
            }
        }

        public async Task<string> GetGZKDFee(int customerId)
        {
            MSExcelHelper exlcelHelepr = new MSExcelHelper();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                var user = (await _customerService.CacheGetAllCustomers()).FirstOrDefault(x => x.Id == customerId);
                string formatterName = $"行运易 For{(string.IsNullOrEmpty(user.Abbreviation) ? user.CompanyName : user.Abbreviation)} 2022年05月01日生效({DateTime.Now.ToString("HHmmss")})";

                string filename = getTempFilePath(formatterName);
                //System.IO.File.Create(filename);
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度

                /*wbs固定*/
                _Workbook templateWorkBook = app.Workbooks._Open(System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "固定.xlsx"));

                var activeBook = app.Workbooks.Add(Missing.Value);  //获取工作薄 

                var ozkpAttrSheet = (Worksheet)templateWorkBook.Worksheets[7];

                var templateEUtaxSheet = (Worksheet)templateWorkBook.Worksheets[3];

                var activeEUTaxSheet = (Worksheet)activeBook.Worksheets[1];

                await CreateZX_KX_DDSheets(customerId, app, activeBook, ozkpAttrSheet, showSurcharge: true);

                var templateFedexZone = (Worksheet)templateWorkBook.Worksheets[1];
                await SetGZFedex(customerId, exlcelHelepr, activeBook, templateFedexZone);

                var dhlZone = (Worksheet)templateWorkBook.Worksheets[2];
                await SetDHL(customerId, exlcelHelepr, activeBook, dhlZone);

                await SetETK(customerId, exlcelHelepr, activeBook);
                await SetHK(customerId, exlcelHelepr, activeBook);

                await SetEub(customerId, "普货E邮宝", "JSEUB", 26, exlcelHelepr, activeBook);
                await SetEub(customerId, "特货E邮宝", "THeub", 126, exlcelHelepr, activeBook);

                var templateITZone = (Worksheet)templateWorkBook.Worksheets[5];
                templateITZone.Copy(Type.Missing, (Worksheet)activeBook.ActiveSheet);


                templateEUtaxSheet.Copy(Type.Missing, (Worksheet)activeBook.ActiveSheet);

                var otherFee = (Worksheet)templateWorkBook.Worksheets[6];
                otherFee.Copy(Type.Missing, (Worksheet)activeBook.ActiveSheet);

                await GetPublic(app, activeBook);

                ((_Worksheet)activeBook.Worksheets[1]).Activate();
                activeBook.SaveCopyAs(filename);
                activeBook.Close(false, Type.Missing, Type.Missing);
                templateWorkBook.Close(false, Type.Missing, Type.Missing);
                return filename;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                app.Quit();
                MSExcelHelper.Kill(app);
                app = null;
            }

        }

        /// <summary>
        /// 生成专线，美国快线，带电专线的sheet
        /// </summary>
        /// <param name="customerId"></param>
        /// <param name="app"></param>
        /// <param name="beforeSheet">插入到哪个sheet之后</param>
        /// <returns></returns>
        private async Task CreateZX_KX_DDSheets(int customerId, Application app, Workbook wb, Worksheet ozkpsheets, bool includeKX = true, bool includeDD = true, bool iscludeFBA = true, bool showSurcharge = true)
        {
            var quotations = await QueryCustomerQuotation(customerId);
            quotations = quotations.Where(i => i.生效时间 != "停用");
            var expData = quotations.Where(i => i.线路名称.Contains("快线"));
            var electrictData = quotations.Where(i => i.线路名称.Contains("带电"));
            var fbaData =
                quotations.Where(i => i.线路名称 == "欧洲空派VAT"
                || i.线路名称 == "欧洲空派包税"
                || i.线路名称 == "美国空派专线"
                || i.线路名称 == "加拿大空派专线"
                || i.线路名称 == "美森限时达海派"
                || i.线路名称 == "美森正班船海派A价"
                || i.线路名称 == "美森正班船海派B价"
                || i.线路名称 == "美森加班船海派"
                );
            var specialLineData = quotations
                .Except(expData)
                .Except(fbaData)
                .Except(electrictData)
                .OrderBy(x => x.线路名称)
                .ThenBy(x => x.开始重量).ToList();

            var sheet1 = GetSpecialLineSheet(specialLineData, app, showSurcharge);
            sheet1.Copy(wb.Worksheets[1], Type.Missing);

            if (electrictData?.Any() == true && includeDD)
            {
                var sheet3 = GetElectriLineExpSheet(electrictData, app, showSurcharge);
                sheet3.Copy(Type.Missing, wb.ActiveSheet);
            }

            if (expData?.Any() == true && includeKX)
            {
                var sheet2 = GetUsExpSheet(expData, app, showSurcharge);
                sheet2.Copy(Type.Missing, wb.ActiveSheet);
            }
            if (fbaData?.Any() == true && iscludeFBA)
            {
                GetFbaEXpSheet(fbaData, app, wb, ozkpsheets);
            }
        }

        private void GetFbaEXpSheet(IEnumerable<View_Quotation> expData, Application app, Workbook wb, Worksheet ozkpsheets)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "欧洲空派报价.xlsx");
            string usFbaTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "美国空派专线.xlsx");
            string caFabTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "加拿大空派专线.xlsx");
            string ushpFbaTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "美国海派价格.xlsx");
            var fileGenerator = new QuotationFileGenerator();
            fileGenerator.MakeEUKPFile(app, wb, expData.Where(x => x.ExpressId == 311 || x.ExpressId == 312), templateFile, ozkpsheets);
            fileGenerator.MakeUSKPFile(app, wb, expData.Where(x => x.ExpressId == 257), usFbaTemplateFile);
            fileGenerator.MakeCAKPFile(app, wb, expData.Where(x => x.ExpressId == 345), caFabTemplateFile);
            fileGenerator.MakeUSHPFile(app, wb, expData.Where(x =>
            new int[] { 366, 367, 368, 369 }.Contains(x.ExpressId)), ushpFbaTemplateFile);
        }

        private System.Drawing.Color l = System.Drawing.Color.FromArgb(169, 208, 142);
        private System.Drawing.Color y = System.Drawing.Color.FromArgb(255, 255, 0);
        private System.Drawing.Color r = System.Drawing.Color.FromArgb(255, 0, 0);
        private async Task SetZD(int customerId, MSExcelHelper tableMapping, _Workbook wb)
        {
            //中东专线
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 92);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var Sheet = (Worksheet)wb.Sheets.Add(wb.ActiveSheet, Type.Missing, 1, XlSheetType.xlWorksheet);
                Sheet.Name = "中东专线";
                var list = await _feeBaseRepository.GetDataTableAsync($@"
                with t1 as(
                select  convert(decimal(18,1), EndWeight/1000.0) as [重量(KG)],b.CnName,fee
	                from
		                Logistics_ExpressFeeRange(nolock) a
		                join Sys_Country(nolock) b on a.Countory=b.Code
	                where BaseId={feeBase.Id}  
                )
                select * from t1 b pivot(max(fee) 
	                for CnName in([阿联酋],[巴林],[阿曼],[科威特],[沙特阿拉伯],[卡塔尔]))a");


                tableMapping.OutLocalTableData(list, Sheet, "A2", _color: l);
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A1", Missing.Value);
                range.Font.Size = 20;
                range.set_Value(Missing.Value, new object[1, 1] { { "中东专线\r\n渠道代码MDSL2" } });
                var r = range.get_Resize(1, 7);
                r.Merge();
                r.RowHeight = 80;
                r.Font.Bold = true;
                r.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                r.VerticalAlignment = XlVAlign.xlVAlignCenter;

                Microsoft.Office.Interop.Excel.Range range2 = Sheet.get_Range("A2", Missing.Value);
                range2 = range.get_Resize(list.Rows.Count + 2, 7);
                range2.ColumnWidth = 15;
                range2.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range2.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range2.Font.Size = 14;

                //设置旺季附加费
                AddWJFJF(Sheet, list, customerId);


            }
            void AddWJFJF(Worksheet Sheet, System.Data.DataTable list, int customerId)
            {
                bool lhj = new int[] { 221, 252 }.Contains(customerId);
                double fee1 = lhj ? 22 : 20;
                double fee2 = lhj ? 11 : 10;

                var index = list.Rows.Count + 5;
                System.Drawing.Color y = System.Drawing.Color.FromArgb(255, 255, 0);
                System.Drawing.Color r = System.Drawing.Color.FromArgb(255, 0, 0);
                MangerSetValue(Sheet, index, y, "旺季附加费", align: XlHAlign.xlHAlignCenter);
                MangerSetValue(Sheet, ++index, null, $"1.旺季附加费RMB{fee1}/KG收取的国家范围：阿联酋/阿曼/巴林/科威特");
                MangerSetValue(Sheet, ++index, null, $"2.旺季附加费RMB{fee2}/KG收取的国家范围：沙特。");
                index += 2;
                MangerSetValue(Sheet, index, null,
                    "自2020年12月31日起，阿联酋、巴林、卡塔尔、科威特、沙特阿拉伯关税支付由DDU（收件人支付关税）模式变更为DDP（发件人支付关税）模式，其他国家不变。", rowCount: 3);
                index += 2;
                MangerSetValue(Sheet, ++index, null,
                    "阿联酋：按照申报价值收取5%VAT，不接受单票申报价值超273USD以上的包裹。")
                    .get_Characters(1, 3).Font.Color = r;
                MangerSetValue(Sheet, ++index, null,
                    "巴林：收取关税预付手续费21RMB/票，按照申报价值收取5%VAT，不接受单票申报价值超273USD以上的包裹。")
                    .get_Characters(1, 2).Font.Color = r; ;
                MangerSetValue(Sheet, ++index, null,
                    "科威特：收取关税预付手续费35RMB/票，按照申报价值收取6.25%VAT，不接受单票申报价值超300USD以上的包裹。")
                    .get_Characters(1, 3).Font.Color = r; ;
                MangerSetValue(Sheet, ++index, null,
                    "卡塔尔：收取关税预付手续费59RMB/票，不接受单票申报价值超800USD以上的包裹。")
                    .get_Characters(1, 3).Font.Color = r; ;
                MangerSetValue(Sheet, ++index, null,
                    "沙特阿拉伯：按照申报价值收取15%VAT，不接受单票申报价值超260USD以上的包裹。")
                    .get_Characters(1, 5).Font.Color = r;
                MangerSetValue(Sheet, ++index, null, "阿曼：申报价值大于USD24，需缴纳的税费：申报价值x5%")
                    .get_Characters(1, 2).Font.Color = r; ;
                index += 3;
                MangerSetValue(Sheet, ++index, null,
                    "特别提示：\r\n如客户一旦同意接受我公司服务，即我司则视为客户已详细阅读过此价格表备注内容以及我司托运条款，并接受各条款的约束。\r\n专线操作要求以及注意事项\r\n1：报价表包含燃油附加费。\r\n2：包裹类型：一票一件。\r\n3：专线渠道不支持海外退回，只支持退回当地海外仓上架仓储、转寄或者当地销毁，具体费用以运价表为准\r\n4：专线报价不包含目的地的关税以及其他杂费，如收件人拒付、发件人弃件所产生的仓储费，更改地址费，罚款以及其它清关杂费等，我司将直接向发货人收取。\r\n5：沙特所有所有进口的货物必须有Made in China的标志，如衣服需要有车缝水洗标。\r\n6：沙特进口的如下货物且申报金额高于USD265的，需要提供SASO 、装箱单、发票以及产地证加签，\r\n大概物品如下：电子类：电脑、手提电脑、平板电脑、电脑显示器、移动电话设备、手机配件.汽车零部件：\r\n电器类：加热器、电视、录像机、冰箱等.电气设备：电器开关、变压器、断路器等；\r\n为了方便目的港清关需求，请提供货物清单，清单请务必提供货物品名的HS CODE.\r\n7：阿联酋偏远区域为（Ras Al Khaimah，Fujairah，Um Al Quaiwan，Free Zones & ODA，AL AIN）偏远附加费为RMB10.0 / 票；有权利追加收取偏远附加费；\r\n8：计费重规则：\r\n计费重：实际毛重与体积重取最大值；\r\n体积重：长cm X 宽cm X 高cm / 6000；\r\n计算单位：0.5KG，不足0.5KG，按0.5KG计费重。\r\n计费重量限重：0 < W≤30KG\r\n纸箱尺寸限制：120CM * 80CM * 80CM；单边长不超过120CM\r\nPACK袋装(标准尺寸36CM * 28CM)的不计体积量，纸箱包装的计材积重。\r\n9：赔偿相关事项：\r\n1）赔偿受理截止时间：发货后45天内必须提出申请。\r\n2）理赔标准：丢件退运费 + 货件采购成本，最大值赔偿且单票包裹最高赔偿金额不超过100 / 票，不承担货件因不实申报所引起的一切责任\r\n3）索赔资料要求：客户需提供索赔函和必要的包裹资料信息开查如：收件人信息、内件品名及申报价值等，经我司确认丢件，按赔偿标准赔偿；\r\n10：因运输环节较多，请确保货物本身及包装的牢固性，易碎品破损由发件人负责。\r\n11：专线是个人物品快件清关模式，不接受包裹内件数超过3PCS的同样产品的包裹。\r\n12：禁止将商业件当做个人物品清关冲货，因为商业件所需的相关证明收件人多数无法提供，如提供的资料当地海关不认可将没有办法清关，无法清关的货物有两种方式处理，销毁和退到迪拜分公司，一旦当地海关回复不能当地销毁，将强制性的退运产生的费用由发件人承担。\r\n13：只接受电池功率不超100W的内置电池，配套电池产品。不接收纯电池类（手机电池、笔记本电池、相机电池、背夹电池、充电宝、移动电源等）", rowCount: 30);
            }

            Microsoft.Office.Interop.Excel.Range MangerSetValue(Worksheet Sheet,
                int index,
                System.Drawing.Color? color,
                string value,
                int height = 0,
                XlHAlign align = XlHAlign.xlHAlignLeft,
                int rowCount = 1)
            {
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A" + index, Missing.Value);
                range.set_Value(Missing.Value, new object[1, 1] { { value } });
                range = range.get_Resize(rowCount, 7);
                range.Merge();
                range.Font.Size = 14;
                range.WrapText = true;
                range.HorizontalAlignment = align;
                range.VerticalAlignment = XlVAlign.xlVAlignTop;
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                if (color != null)
                    range.Interior.Color = color;
                return range;
            }
        }

        private async Task SetDHL(int customerId, MSExcelHelper exlcelHelepr, _Workbook wb, Worksheet dhlOtherFee)
        {
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 50);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var Sheet = (Worksheet)wb.Sheets.Add(Type.Missing, wb.ActiveSheet, 1, XlSheetType.xlWorksheet);
                Sheet.Name = "广州DHL";
                var list = await _feeBaseRepository.GetDataTableAsync($@"
                 if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                   drop table #temp_fedex
	                select  convert(decimal(18,1), EndWeight/1000.0) as [公斤/国家],b.分区,convert(decimal(18,4) ,fee)fee
	                    into #temp_fedex
	                    from
		                    Logistics_ExpressFeeRange(nolock) a
							join logistics_expressdhlzone(nolock) b on a.Countory=b.code
	                    where BaseId={feeBase.Id}
	                    order by EndWeight
                    declare @countrys nvarchar(max)
                    set @countrys= stuff((select  distinct ',['+ 分区+']'   from #temp_fedex  order by  ',['+ 分区+']' for xml path('')),1,1,'')
                    print @countrys 
                    declare @sql nvarchar(max) = 'select * from #temp_fedex b pivot(max(fee) 
	                    for 分区 in('+@countrys+'))a order by [公斤/国家]'
                    exec(@sql) 
                      ");
                exlcelHelepr.OutLocalTableData(list, Sheet, "A3", _color: this.l);
                /*每列宽*/
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A3", Missing.Value);
                range = range.get_Resize(list.Rows.Count + 1, 27);
                range.ColumnWidth = 9.13;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Size = 11;
                var tr = MangerSetValue(Sheet, 1, null, "广州DHL",
                      rowCount: 1, align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter);
                tr.Font.Size = 20;
                tr = MangerSetValue(Sheet, 2, null, "渠道代码DHL",
                    rowCount: 1, align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter);
                tr.Font.Size = 20;
                AddDHLRemark(Sheet, list.Rows.Count, customerId);
                dhlOtherFee.Copy(Type.Missing, (Worksheet)wb.ActiveSheet);
            }
        }



        private async Task SetGZFedex(int customerId, MSExcelHelper exlcelHelepr, _Workbook wb, Worksheet fedexZone)
        {
            //GzFedex
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 48);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var Sheet = (Worksheet)wb.Sheets.Add(Type.Missing, wb.ActiveSheet, 1, XlSheetType.xlWorksheet);
                Sheet.Name = "广州FedEx";
                var list = await _feeBaseRepository.GetDataTableAsync($@" 
                           if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                                           drop table #temp_fedex
	                                        select  distinct  convert(decimal(18,1), EndWeight/1000.0) as [公斤/国家],b.分区,fee
	                                            into #temp_fedex
	                                            from
		                                            Logistics_ExpressFeeRange(nolock) a
							                        join logistics_fedexzone(nolock) b on a.Countory=b.二字代码
	                                            where BaseId= {feeBase.Id} 
	                                            order by b.分区,convert(decimal(18,1), EndWeight/1000.0)
                                            declare @countrys nvarchar(max)
                                            set @countrys= stuff((select  distinct ',['+ 分区+']'   from #temp_fedex order by ',['+ 分区+']'  for xml path('')),1,1,'') 
                                            print @countrys 
                                            declare @sql nvarchar(max) = 'select * from #temp_fedex b pivot(max(fee) 
	                                            for 分区 in('+@countrys+'))a order by [公斤/国家]'
                                            exec(@sql) 
                      ");
                exlcelHelepr.OutLocalTableData(list, Sheet, "A2", _color: this.l);
                /*每列宽*/
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A2", Missing.Value);
                range = range.get_Resize(list.Rows.Count + 1, 27);
                range.ColumnWidth = 9.13;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Size = 11;
                var tr = MangerSetValue(Sheet, 1, null, "渠道代码：FedEx",
                      rowCount: 1, align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter);
                tr.Font.Size = 20;
                AddRemark(Sheet, list.Rows.Count, customerId);
                fedexZone.Copy(Type.Missing, (Worksheet)wb.ActiveSheet);
            }
        }


        private async Task SetCDFedex(int customerId, MSExcelHelper exlcelHelepr, _Workbook wb, Worksheet fedexZone)
        {
            //成都Fedex-Pak
            var fedexPakBases = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 232);
            var fedexPakBase = fedexPakBases.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            //成都Fedex-非Pak
            var fedexBases = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 231);
            var fedexBase = fedexBases.OrderByDescending(x => x.EnableTime).FirstOrDefault();

            if (fedexPakBase != null && fedexBase != null)
            {
                var Sheet = (Worksheet)wb.Sheets.Add(Type.Missing, wb.ActiveSheet, 1, XlSheetType.xlWorksheet);
                Sheet.Name = "成都FedEx";
                var fedexPakList = await _feeBaseRepository.GetDataTableAsync($@"
                if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                   drop table #temp_fedex
	                select  convert(decimal(18,1), EndWeight/1000.0) as [公斤/国家],b.分区,fee
	                    into #temp_fedex
	                    from
		                    Logistics_ExpressFeeRange(nolock) a
                            join logistics_fedexzone(nolock) b on a.Countory=b.二字代码
	                    where BaseId={fedexPakBase.Id} 
	                    order by EndWeight,b.分区
                    declare @countrys nvarchar(max)
                    set @countrys= stuff((select  distinct ',['+ 分区+']'   from #temp_fedex  order by ',['+ 分区+']' for xml path('')),1,1,'')
                    print @countrys 
                    declare @sql nvarchar(max) = 'select * from #temp_fedex b pivot(max(fee) 
	                    for 分区 in('+@countrys+'))a order by [公斤/国家]'
                    exec(@sql)
                    print @sql 
                      ");
                exlcelHelepr.OutLocalTableData(fedexPakList, Sheet, "B2", _color: this.l, hasHeader: true);
                var fedexList = await _feeBaseRepository.GetDataTableAsync($@"
               if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                   drop table #temp_fedex
	                select  convert(decimal(18,1), EndWeight/1000.0) as [公斤/国家],b.分区,fee
	                    into #temp_fedex
	                    from
		                    Logistics_ExpressFeeRange(nolock) a
                            join logistics_fedexzone(nolock) b on a.Countory=b.二字代码
	                    where BaseId={fedexBase.Id} 
	                    order by EndWeight,b.分区
                    declare @countrys nvarchar(max)
                    set @countrys= stuff((select  distinct ',['+ 分区+']'   from #temp_fedex order by ',['+ 分区+']' for xml path('')),1,1,'')
                    print @countrys 
                    declare @sql nvarchar(max) = 'select * from #temp_fedex b pivot(max(fee) 
	                    for 分区 in('+@countrys+'))a order by [公斤/国家]'
                    exec(@sql)
                    print @sql 
                      ");
                exlcelHelepr.OutLocalTableData(fedexList, Sheet, "B" + (fedexPakList.Rows.Count + 2), hasHeader: false);
                /*每列宽*/
                int rowCount = fedexList.Rows.Count + fedexPakList.Rows.Count;
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A2", Missing.Value);
                range = range.get_Resize(rowCount + 1, 27);
                range.ColumnWidth = 9.13;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Size = 11;
                var tr = MangerSetValue(Sheet, 1, null, " 成都联邦价格（不含燃油和旺季附加费）",
                      rowCount: 1, align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter);
                tr.Font.Size = 20;

                /*合并列名*/
                Microsoft.Office.Interop.Excel.Range headRange = Sheet.get_Range("A2", Missing.Value);
                headRange = headRange.get_Resize(1, 2);
                headRange.Merge();

                Microsoft.Office.Interop.Excel.Range noPackColumn = Sheet.get_Range("A" + (fedexPakList.Rows.Count + 3), Missing.Value);
                noPackColumn = noPackColumn.get_Resize(fedexList.Rows.Count, 1);
                noPackColumn.Merge();
                noPackColumn.set_Value(Missing.Value, new object[1, 1] { { "包裹\r\n（体积除以5000）" } });
                noPackColumn.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                noPackColumn.VerticalAlignment = XlVAlign.xlVAlignCenter;
                noPackColumn.Borders.LineStyle = XlLineStyle.xlContinuous;

                Microsoft.Office.Interop.Excel.Range pakColumn = Sheet.get_Range("A3", Missing.Value);
                pakColumn = pakColumn.get_Resize(fedexPakList.Rows.Count, 1);
                pakColumn.Merge();
                pakColumn.set_Value(Missing.Value, new object[1, 1] { { "快递袋\r\n（不计抛）" } });
                pakColumn.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                pakColumn.VerticalAlignment = XlVAlign.xlVAlignCenter;
                pakColumn.Borders.LineStyle = XlLineStyle.xlContinuous;

                AddRemark(Sheet, rowCount, customerId);
                fedexZone.Copy(Type.Missing, (Worksheet)wb.ActiveSheet);

            }
        }

        private void AddDHLRemark(Worksheet Sheet, int rowcount, int customerId)
        {
            bool lhj = new int[] { 221, 252 }.Contains(customerId);
            double fee = lhj ? 16 : 14;
            int index = rowcount + 6;
            var j = MangerSetValue(Sheet, index, color: y, $"注：以上价格为dhl净价，不包裹燃油附件费、紧急附加费、其他杂费项目。", align: XlHAlign.xlHAlignCenter,
                vAlign: XlVAlign.xlVAlignCenter);
            j.Font.Size = 14;
            j.Font.Bold = true;
            j.Font.Color = r;
            setValue(Sheet, ++index, "注意事项：");
            setValue(Sheet, ++index, "燃油附加费：附加费百分比于每周一调整，具体可以参照官网https://mydhlplus.dhl.com/cn/zh/ship/surcharges.html#/fuel_surcharge。");
            setValue(Sheet, ++index, "计算体积重量：长度 x 宽度 x 高度 (厘米)/5000(厘米)");
            setValue(Sheet, ++index, "运费计算：净价+净价*燃油");
            setValue(Sheet, ++index, "附加费：疫情附加费+疫情附加费*燃油");
            setValue(Sheet, ++index, "运费按照0.5每公斤往上叠加计算，疫情附加费按照每公斤往上叠加计算");
        }

        private void AddRemark(Worksheet Sheet, int rowcount, int customerId)
        {
            bool lhj = new int[] { 221, 252 }.Contains(customerId);
            double fee = lhj ? 16 : 14;
            int index = rowcount + 5;
            var j = MangerSetValue(Sheet, index, color: y, $"另加收紧急情形附加费 (ESS){fee}元/KG（最低收费{fee}元/票）", align: XlHAlign.xlHAlignCenter,
                vAlign: XlVAlign.xlVAlignCenter);
            j.Font.Size = 14;
            j.Font.Bold = true;
            j.Font.Color = r;

            setValue(Sheet, ++index, "净费率后跟加号“”表示净费率反映的是最低费用。");
            setValue(Sheet, ++index, "所有价格不包括任何税项。有关服务附加费和燃油附加费，详情请见服务附加费和其它注意事项 (http://www.fedex.com/cn/rates/surcharge_otherinfo.html)。");
            setValue(Sheet, ++index, "准时送达保证并不适用于此价目表的寄件。");
            ++index;
            setValue(Sheet, ++index, "服务附加费和其它注意事项。");
            setValue(Sheet, ++index, "燃油附加费：附加费百分比于每周一调整，该百分比遵照美国墨西哥湾沿岸航空燃料指标（US Gulf Coast Jet Fuel Index）并基于总运输费计算得出（登录 https://www.fedex.com/zh-cn/home.html查看普通燃油附加费费率和计算示例）。");
            setValue(Sheet, ++index, "服务附加费：");
            setValue(Sheet, ++index, "第三方付费的附加费用：总运费的2.5％");
            setValue(Sheet, ++index, "第三方收件人附加费：每笔空运提单人民币99元");
            setValue(Sheet, ++index, "指定清关代理人：每笔空运提单人民币96元或每公斤人民币10.7元，取其金额较高者收取");
            setValue(Sheet, ++index, "星期六递送附加费：每笔空运提单人民币150元");
            setValue(Sheet, ++index, "*联邦快递对超出其快递员取件和派送区域（超范围取件（OPA）和超范围派送（ODA）地区）的货件加收附加费。超范围取件费（OPA）和超范围派送费（ODA）不适用于联邦快递10公斤或25公斤快递箱货件。");
            setValue(Sheet, ++index, "需要间接签收：每单非重货快递服务需要支付人民币30元的特殊处理费用（适用于目 的地为美国/加拿大住宅地区且托运申报价值小于500 美元/500 加元的货件)");
            setValue(Sheet, ++index, "需要直接签收：每单非重货快递服务需要支付人民币30元的特殊处理费用（不适用于目 的地为美国/加拿大且托运申报价值大于或等于500 美元/500 加元的货件)  ");
            setValue(Sheet, ++index, "需要成年人签收：每单非重货快递服务需要支付人民币33的特殊处理费用。请注意，当选定成人签名为签名选项，FedEx不会把包裹转投到其他地址。");
            setValue(Sheet, ++index, "附加服务费-尺寸：此附加费适用于任何最长单边超过121厘米的包裹；或任何次长单边超过76厘米的包裹。国际特早快递服务(IF)、国际优先快递服务(IP)和国际经济快递服务(IE)的每笔空运提单收取人民币38元。如果该货件已收取超大货件的费用，则该附加服务费将不适用。");
            setValue(Sheet, ++index, "附加服务费–重货：此附加费适用于最长单边超过157厘米的重货服务包裹。国际优先快递重货服务(IPF)和国际经济快递重货服务(IEF)的每笔空运提单收取 人民币516元。");
            setValue(Sheet, ++index, "超大货件收费：此附加费适用于货件中含有最长单边超过243厘米或最长单边长度和其余两边的周长超过330厘米的包裹。国际特早快递服务 (IF)、国际优先快递服务(IP)和国际经济快递服务(IE)的每笔空运提单收取人民币516元。");
            setValue(Sheet, ++index, "非堆叠货件附加费：针对联邦快递国际优先快递重货服务及联邦快递国际经济快递重货服务, 每货件1233元。");
            setValue(Sheet, ++index, "关税和税金：如果需对货件征收关税和税款，而且为应支付方代为支付了这些费用，那么该支付方必须将该等关税和税款返还给，并支付按货件类型和其目的地收取的附加费。");
            setValue(Sheet, ++index, "声明价值附加费：联邦快递对货件灭失、损坏或延误的责任不超过20美金/公斤或100美金/件。具体的限额可能以“特别提款权(SDR) ”或当地货币表示，而具体金额因不同国家/地区和货币而不同。");
            setValue(Sheet, ++index, "如果您希望联邦快递对货件实际价值承担较大比例的赔偿责任，则您必须在您的空运提单上声明货件价值并支付“声明价值附加费”。如货件声明价值超出下列较高者：（i）人民币830元或（ii）人民币75.28元/磅，则对中国大陆出口预付/进口");
            setValue(Sheet, ++index, "到付货件征收“声明价值附加费”，每增加人民币830元加收人民币10.1元（或以百分比计算）。货件声明价值不得超过以下限额。详情请洽客户服务部。");
            setValue(Sheet, ++index, "声明价值最高限额（可能因货件目的地的不同而有所差别）：");
            setValue(Sheet, ++index, "联邦快递快递封/快递袋：100美金（或当地货币相当的金额）");
            setValue(Sheet, ++index, "国际优先快递服务(IP)/国际经济快递服务(IE)：50000美金（或当地货币相当的金额）");
            setValue(Sheet, ++index, "国际优先快递重货服务(IPF)/国际经济快递重货服务(IEF)：100000美金（或当地货币相当的金额） ");
            setValue(Sheet, ++index, "针对贵重物品如艺术品、古董、玻璃器皿、珠宝、贵金属、皮草等：1000美金或每公斤20美金（取其金额较高者）");
            setValue(Sheet, ++index, "货件计重注意事项：");
            setValue(Sheet, ++index, "货件的总重量没有限制。单一包裹的最大重量可以有所调整。详情请洽客户服务热线或与销售代表联系。运费之计算以一张空运提单上所列明的总重量为准，不足一累计单位以一累计单位计算。这种计算方法可能导致尺寸重量（体积重量）的计算出现差异。请注意，联邦快递发票上列明的价格应是最终和正式的价格。");

            setValue(Sheet, ++index, "若所交运之货件的体积重量超过其实际重量，则运费按照体积重量收取，国际优先快递或国际经济快递的体积重量（公斤）计算方法如下。 详情请洽客户服务热线或与销售代表联系。");
            setValue(Sheet, ++index, "长度 x 宽度 x 高度 (厘米或英寸)/5000(厘米) 或305(英寸)");
            setValue(Sheet, ++index, "1公斤的最低计费重量将适用于FedEx小型快遞盒﹑FedEx中型快遞盒和FedEx大型快遞盒﹐而4公斤的最低计费重量将适用于FedEx快遞筒。若所讬运之货件使用FedEx包装物料，则运费或采用体积重量计算收取。");



        }


        Microsoft.Office.Interop.Excel.Range setValue(Worksheet Sheet, int index, string value)
        {
            Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A" + index, Missing.Value);
            range.set_Value(Missing.Value, new object[1, 1] { { value } });
            range.ColumnWidth = 10;
            range = range.get_Resize(1, 27);
            //range.WrapText = true;
            range.Merge();
            return range;
        }

        private Microsoft.Office.Interop.Excel.Range MangerSetValue(Worksheet Sheet,
             int index,
             System.Drawing.Color? color,
             string value,
             XlHAlign align = XlHAlign.xlHAlignLeft,
             XlVAlign vAlign = XlVAlign.xlVAlignTop,
             int rowCount = 1,
             int columnCount = 20)
        {
            Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A" + index, Missing.Value);
            range.set_Value(Missing.Value, new object[1, 1] { { value } });
            range = range.get_Resize(rowCount, columnCount);
            range.Merge();
            //range.Font.Size = 14;
            range.WrapText = true;
            range.HorizontalAlignment = align;
            range.VerticalAlignment = vAlign;
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            if (color != null)
                range.Interior.Color = color;
            return range;
        }
        private async Task SetETK(int customerId, MSExcelHelper exlcelHelepr, _Workbook wb)
        {
            //E特快
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 106);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var Sheet = (Worksheet)wb.Sheets.Add(Type.Missing, wb.ActiveSheet, 1, XlSheetType.xlWorksheet);
                Sheet.Name = "E特快";
                var list = await _feeBaseRepository.GetDataTableAsync($@"
	                select ROW_NUMBER() OVER (order by b.Code) [序号],b.CnName[国家],b.Code[二字代码], convert(decimal(18,3),a.FristFee)[首重(元/50G)], convert(decimal(18,3),a.ContinuedFee)[续重(元/50G)] from Logistics_ExpressFeeGrads(nolock)a 
	                join Sys_Country(nolock) b on a.Countory=b.Code and b.CnName<>'阿联酋'
	                where BaseId={feeBase.Id}  and a.IsDelete=0 order by b.Code");
                exlcelHelepr.OutLocalTableData(list, Sheet, "B7", _color: l);
                AddRemark(Sheet, list);
            }
            void AddRemark(Worksheet Sheet, System.Data.DataTable list)
            {
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("B2", Missing.Value);
                range = range.get_Resize(list.Rows.Count + 6, 5);
                range.ColumnWidth = 20;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Size = 12;

                System.Drawing.Color l = System.Drawing.Color.FromArgb(169, 208, 142);
                var tr = MangerSetValue(Sheet, 1, null, "行运易E特快报价",
                      rowCount: 6, align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter);
                tr.Font.Size = 30;
                tr.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
            }

            Microsoft.Office.Interop.Excel.Range MangerSetValue(Worksheet Sheet,
            int index,
            System.Drawing.Color? color,
            string value,
            XlHAlign align = XlHAlign.xlHAlignLeft,
            XlVAlign vAlign = XlVAlign.xlVAlignTop,
            int rowCount = 1)
            {
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("B" + index, Missing.Value);
                range.set_Value(Missing.Value, new object[1, 1] { { value } });
                range = range.get_Resize(rowCount, 5);
                range.Merge();
                //range.Font.Size = 14;
                range.WrapText = true;
                range.HorizontalAlignment = align;
                range.VerticalAlignment = vAlign;
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                if (color != null)
                    range.Interior.Color = color;
                return range;
            }
        }

        private async Task SetEub(int customerId, string name, string code, int expressId, MSExcelHelper tableMapping, _Workbook wb)
        {
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == expressId);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null && feeBase.EnableTime > DateTime.Parse("2022-01-01"))
            {
                var Sheet = (Worksheet)wb.Sheets.Add(Type.Missing, wb.ActiveSheet, 1, XlSheetType.xlWorksheet);
                Sheet.Name = name;
                var list = await _feeBaseRepository.GetDataTableAsync(
                    $@"select b.CnName as 路向,b.Code as 简码, convert(decimal(14,2), a.FristFee -a.FristWeight*A.ContinuedFee) as [元/件], convert(decimal(14,2),a.ContinuedFee/a.ContinuedWeight*1000) as [元/千克]
                        ,a.FristWeight as [起重/克],a.MaxWeight as [限重/克]  from Logistics_ExpressFeeGrads(nolock)a
                    left join Sys_Country(nolock) b on a.Countory=b.Code and b.CnName<>'大不列颠联合王国' and b.CnName<>'俄罗斯联邦' and b.CnName<>'中国，香港特别行政区' and b.CnName<>'美国'
	                    where  BaseID =  {feeBase.Id} 
						order by Code,FristWeight ");
                tableMapping.OutLocalTableData(list, Sheet, "A3", _color: l);
                AddRemark(Sheet, list, feeBase.EnableTime.ToString_yyyyMMdd(), name, code);
            }
            void AddRemark(Worksheet Sheet, System.Data.DataTable list, string time, string name, string code)
            {
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A3", Missing.Value);
                range = range.get_Resize(list.Rows.Count + 3, 6);
                range.ColumnWidth = 15;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Size = 12;

                var tr = MangerSetValue(Sheet, 1, "A", null, name,
                    rowCount: 1, columnCount: 6, align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter);
                tr.Font.Size = 16;
                tr.Font.Bold = true;
                tr.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;


                var tr2 = MangerSetValue(Sheet, 2, "A", null, "渠道代码：" + code,
                    rowCount: 1, columnCount: 6, align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter);
                tr2.Font.Size = 16;
                tr2.Font.Bold = true;
                tr2.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
                string strtime = !name.Contains("特货") ? $"{time}生效全折" : $"{time}生效通折1.2折";
                MangerSetValue(Sheet,
                    3, "J", System.Drawing.Color.Yellow, strtime,
                    align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter, rowCount: 3, columnCount: 4);
            }

            Microsoft.Office.Interop.Excel.Range MangerSetValue(Worksheet Sheet,
            int index,
            string columnName,
            System.Drawing.Color? color,
            string value,
            XlHAlign align = XlHAlign.xlHAlignLeft,
            XlVAlign vAlign = XlVAlign.xlVAlignTop,
            int rowCount = 1,
            int columnCount = 2)
            {
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range(columnName + index, Missing.Value);
                range.set_Value(Missing.Value, new object[1, 1] { { value } });
                range = range.get_Resize(rowCount, columnCount);
                range.Merge();
                //range.Font.Size = 14;
                range.WrapText = true;
                range.HorizontalAlignment = align;
                range.VerticalAlignment = vAlign;
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                if (color != null)
                    range.Interior.Color = color;
                return range;
            }
        }

        private async Task SetHK(int customerId, MSExcelHelper tableMapping, _Workbook wb)
        {
            //香港
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 49);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var Sheet = (Worksheet)wb.Sheets.Add(Type.Missing, wb.ActiveSheet, 1, XlSheetType.xlWorksheet);
                Sheet.Name = "香港快递";
                var list = await _feeBaseRepository.GetDataTableAsync($@" 
	                    select convert(decimal(18,1),endweight/1000.0) as [重量/KG],fee 价格 from Logistics_ExpressFeeRange
	                    where BaseId={feeBase.Id} ");
                tableMapping.OutLocalTableData(list, Sheet, "B5", _color: l);
                AddRemark(Sheet, list);
            }
            void AddRemark(Worksheet Sheet, System.Data.DataTable list)
            {
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("B2", Missing.Value);
                range = range.get_Resize(list.Rows.Count + 4, 7);
                range.ColumnWidth = 15;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Size = 12;
                var tr = MangerSetValue(Sheet, 1, "B", null, "行运易香港快递报价",
                    rowCount: 4, align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter);
                tr.Font.Size = 16;
                tr.Font.Bold = true;
                tr.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
                var i = list.Rows.Count + 6;
                MangerSetValue(Sheet, list.Rows.Count + 7, "B", l, "注：医院、部队、岛屿每票加收30元",
                    align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter, rowCount: 2);

                MangerSetValue(Sheet,
                    6, "E", System.Drawing.Color.Yellow, "疫情附加费：每公斤5元（最低收取5元）", align: XlHAlign.xlHAlignCenter, vAlign: XlVAlign.xlVAlignCenter, rowCount: 3, columnCount: 4);
            }

            Microsoft.Office.Interop.Excel.Range MangerSetValue(Worksheet Sheet,
            int index,
            string columnName,
            System.Drawing.Color? color,
            string value,
            XlHAlign align = XlHAlign.xlHAlignLeft,
            XlVAlign vAlign = XlVAlign.xlVAlignTop,
            int rowCount = 1,
            int columnCount = 2)
            {
                Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range(columnName + index, Missing.Value);
                range.set_Value(Missing.Value, new object[1, 1] { { value } });
                range = range.get_Resize(rowCount, columnCount);
                range.Merge();
                //range.Font.Size = 14;
                range.WrapText = true;
                range.HorizontalAlignment = align;
                range.VerticalAlignment = vAlign;
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                if (color != null)
                    range.Interior.Color = color;
                return range;
            }
        }

        public async Task<PageResult<QuotationApproveRecordDto>> QueryApproveRecord(QueryModel queryModel)
        {
            var pageResult = await _approveRepository.QueryPagerAsync(queryModel);
            PageResult<QuotationApproveRecordDto> dtoPage = new PageResult<QuotationApproveRecordDto>
            {
                PageIndex = pageResult.PageIndex,
                PageSize = pageResult.PageSize,
                TotalCount = pageResult.TotalCount,
            };
            if (pageResult?.Result?.Any() == true)
            {
                var dtoList = new List<QuotationApproveRecordDto>();
                var channels = await _channelService.CacheGetAllChannel();
                var publics = await _channelService.CacheGetAllPublic();
                var allCustomers = await _customerService.CacheGetAllCustomers();
                foreach (var record in pageResult.Result)
                {
                    int channelId = record.ExpressId;
                    var channel = channels.FirstOrDefault(c => c.Id == channelId);
                    var publiChannel = publics.FirstOrDefault(p => p.Code == channel.PublicCode);
                    var publicChannelName = publiChannel.Name;

                    var customerIds = record.CustomerIds.SplitToList<int>();
                    var customers = allCustomers.Where(c => customerIds.Contains(c.Id));
                    var customerNames = string.Join(",", customers.Select(c => c.CompanyName));
                    QuotationApproveRecordDto dto = new QuotationApproveRecordDto
                    {
                        DingdingProcessId = record.DingdingProcessId,
                        ChannelName = publicChannelName,
                        Status = record.Status,
                        CreateTime = record.CreateTime,
                        CreateUserName = record.CreateUserName,
                        CustomerIds = customerIds,
                        ProcessMessage = record.ProcessMessage,
                        ExpressId = record.ExpressId,
                        Id = record.Id,
                        EnableTime = record.EnableTime,
                        CustomerNames = customerNames,
                        Details = new List<Finance_QutationApproveDetail>()

                    };
                    dtoList.Add(dto);
                }

                dtoPage.Result = dtoList;
            }

            return dtoPage;
        }

        public async Task<IEnumerable<Finance_QutationApproveDetail>> GetQuotationDetail(int id)
        {
            var result = await _approveDetailRepository.QueryAsync(d => d.ApproveId == id);
            return result;
        }

        public async Task<DingDingProcessResult> HandleQuotationReject(string processInstanceId)
        {
            DateTime approveTime = DateTime.Now;
            QutationApproveStatus status = QutationApproveStatus.Reject;
            bool ok = await _approveRepository.UpdateByConditionAsync(i => i.DingdingProcessId == processInstanceId, new { Status = status, ApproveTime = approveTime });
            DingDingProcessResult result = new DingDingProcessResult
            {
                IsSuccess = ok
            };
            return result;
        }

        public Worksheet GetSpecialLineSheet(IEnumerable<View_Quotation> quotationData, Microsoft.Office.Interop.Excel.Application app, bool showSurcharge = true)
        {
            //byte[] xlsxData = await Excustomer.Abbreviation ?? portQuotation(customerId);

            var fileGenerator = new QuotationFileGenerator();
            var data = fileGenerator.MakeSpecialLineFileFromTemplate(quotationData, showSurcharge);
            var temFile = System.IO.Path.GetTempFileName();
            System.IO.File.WriteAllBytes(temFile, data);
            _Workbook templateWorkBook = app.Workbooks._Open(temFile);
            var sheet = (Worksheet)templateWorkBook.Worksheets[1];
            sheet.Name = "普货专线";
            return sheet;
        }

        public Worksheet GetUsExpSheet(IEnumerable<View_Quotation> quotationData, Microsoft.Office.Interop.Excel.Application app, bool showSurcharge = true)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "美国快线报价模板.xlsx");
            var fileGenerator = new QuotationFileGenerator();
            var data = fileGenerator.MakeQuotationFile(quotationData, templateFile, showSurcharge);
            var temFile = System.IO.Path.GetTempFileName();
            System.IO.File.WriteAllBytes(temFile, data);
            _Workbook templateWorkBook = app.Workbooks._Open(temFile);
            var sheet = (Worksheet)templateWorkBook.Worksheets[1];
            sheet.Name = "快线渠道";
            return sheet;
        }

        public Worksheet GetElectriLineExpSheet(IEnumerable<View_Quotation> quotationData, Microsoft.Office.Interop.Excel.Application app, bool showSurcharge = true)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "带电专线.xlsx");
            var fileGenerator = new QuotationFileGenerator();
            var data = fileGenerator.MakeQuotationFile(quotationData, templateFile, showSurcharge);
            var temFile = System.IO.Path.GetTempFileName();
            System.IO.File.WriteAllBytes(temFile, data);
            _Workbook templateWorkBook = app.Workbooks._Open(temFile);
            var sheet = (Worksheet)templateWorkBook.Worksheets[1];
            sheet.Name = "带电专线";
            return sheet;
        }


        public async Task<byte[]> ExportQuotation(int customerId)
        {
            var quotation = await QueryCustomerQuotation(customerId);
            var expData = quotation.Where(i => i.线路名称.Contains("专线"));
            var fileGenerator = new QuotationFileGenerator();
            var data = fileGenerator.MakeSpecialLineFileFromTemplate(expData);
            return data;
        }

        public async Task<byte[]> ExportQuotation(List<int> customerIds, DateTime time)
        {
            var allCustomers = await _customerService.CacheGetAllCustomers();

            var customers = allCustomers.Where(c => customerIds.Contains(c.Id));
            List<FileStream> fileStreams = new List<FileStream>();
            List<ZipItem> zipItems = new List<ZipItem>();
            foreach (var customer in customers)
            {
                bool isChengDuQuotation = IsChengDuQuotation(customer);
                var fileFullName = await GenerateQuotationFile(customer.Id, isChengDuQuotation);

                string fileName = $"行运易 For{(customer.Abbreviation ?? customer.CompanyName)} {time.ToString_yyyyMMdd()}生效.xlsx";
                FileStream fs = File.OpenRead(fileFullName);
                ZipItem zipItem = new ZipItem(fileName, fs);
                zipItems.Add(zipItem);
            }

            MemoryStream ms = (MemoryStream)ZipHelper.ZipStream(zipItems);
            var data = ms.ToArray();
            foreach (var stream in fileStreams)
            {
                if (stream != null)
                {
                    stream.Close();
                    stream.Dispose();
                }
            }
            return data;
        }

        private bool IsChengDuQuotation(User_Customer customer)
        {
            bool isChengDuQuotation = customer.SalesmanUserId == 339;
            return isChengDuQuotation;
        }

        private async Task<IEnumerable<View_Quotation>> QueryCustomerQuotation(int customerId)
        {
            string sql = "select * from View_Quotation(nolock) where CustomerId=@CustomerId ";
            return await _quotationRepository.QueryBySqlAsync(sql, new { CustomerId = customerId });

        }

        private async Task<string> GenerateQuotationFile(int customerId, bool isChengDuQuotation)
        {
            string file = string.Empty;
            //if (isChengDuQuotation)
            //{
            //    file = await GetCDKDFee(customerId);
            //}
            //else
            //{
            //    file = await GetGZKDFee2022(customerId);
            //}
            file = await GetGZKDFee2022(customerId, isChengDuQuotation);
            return file;
        }

        public async Task<string> SendNowCustomerQuotaion(FinanceQuotation financeQuotation)
        {
            string message = string.Empty;
            try
            {
                var customer = (await _customerService.CacheGetAllCustomers()).FirstOrDefault(x => x.Id == financeQuotation.CusotmerId);
                bool isChengDuQuotation = IsChengDuQuotation(customer);
                string file = await GenerateQuotationFile(financeQuotation.CusotmerId, isChengDuQuotation);
                string mid = await dingDingBaseService.UpdateFile(file, $"行运易 For{(customer.Abbreviation ?? customer.CompanyName)} {financeQuotation.EntryTime}生效.xlsx");
                var u = await dingDingBaseService.GetDingDingUserInfo(financeQuotation.UserId);
                string userId = u.UserId + ",575513315932546996,01172649383421044015";
                message = await dingDingBaseService.SendMessage(userId, new
                {
                    msgtype = "file",
                    file =
                     new
                     {
                         media_id = mid
                     }
                });
                //邮政公开价客户
                if (financeQuotation.CusotmerId == 761)
                {
                    string msg = $"EUB公开价更新，请及时更新折扣！生效时间：{financeQuotation.EntryTime}";
                    sendDingMessageToChat(msg, "邮政公开价更新");
                }
                //文件发送后进行删除
                if (System.IO.File.Exists(file))
                    System.IO.File.Delete(file);
            }
            catch (Exception ex)
            {
                //发送失败的钉钉消息
                string msg = $"报价文件发送异常： \r\n 请求数据：{JsonConvert.SerializeObject(financeQuotation)} \r\n 请求结果：{ex.Message}";
                sendDingMessageToChat(msg, "定时任务失败");
            }
            return message;
        }
        private bool sendDingMessageToChat(string msg, string TargetName)
        {
            bool isSucess = false;
            var client = new RestClient("http://120.24.149.148:9505/WarningNotice/NoticeText");
            client.Timeout = -1;
            var request = new RestRequest(Method.POST);
            request.AddHeader("Content-Type", "application/json");
            var ddModel = new
            {
                ServiceType = "钉钉",
                TargetName = TargetName,
                Content = msg,
            };
            var body = JsonConvert.SerializeObject(ddModel);
            request.AddParameter("application/json", body, ParameterType.RequestBody);
            IRestResponse response = client.Execute(request);
            if (response.StatusCode == System.Net.HttpStatusCode.OK)
            {
                isSucess = true;
            }
            return isSucess;
        }
        #region 新模板写入数据

        #region 入口
        public async Task<string> GetGZKDFee2022(int customerId, bool isChengDuQuotation = false)
        {
            MSExcelHelper exlcelHelepr = new MSExcelHelper();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                var user = (await _customerService.CacheGetAllCustomers()).FirstOrDefault(x => x.Id == customerId);
                string formatterName = $"行运易 For{(string.IsNullOrEmpty(user.Abbreviation) ? user.CompanyName : user.Abbreviation)} 2022年05月01日生效({DateTime.Now.ToString("HHmmss")})";

                string filename = getTempFilePath(formatterName);
                //System.IO.File.Create(filename);
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度

                var activeBook = app.Workbooks.Add(Missing.Value);  //获取工作薄 

                await CreateZX_KX_DDSheets2022(customerId, app, activeBook, showSurcharge: true);

                await SetGZFedex2022(customerId, app, activeBook);
                if (isChengDuQuotation) { await SetCDFedex2022(customerId, app, activeBook); }//成都独有
                await SetDHL2022(customerId, app, activeBook);
                await SetHK2022(customerId, app, activeBook);

                //写个中间方法判断兼容 新旧版本报价
                await EubJudge(customerId, app, activeBook);


                //处理部分共用sheet内容
                var fileGenerator = new QuotationFileGenerator();
                string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", $"快递杂费报价.xlsx");
                fileGenerator.MakeCommonFile2022(app, activeBook, templateFile);

                //汇总里面处理共用的sheet
                await GetPublic2022(app, activeBook);

                ((_Worksheet)activeBook.Worksheets[1]).Activate();
                activeBook.SaveCopyAs(filename);
                activeBook.Close(false, Type.Missing, Type.Missing);

                return filename;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                app.Quit();
                MSExcelHelper.Kill(app);
                app = null;
            }

        }
        #endregion

        #region sheet 汇总
        public PublicNote GetNote2022(string name)
        {
            var list = new List<PublicNote>()
            {
                new PublicNote { GroupName="专线类",Name="普货专线",SheetName="普货专线",ReferenceTime="7-10工作日",Default="只接普货、航班及时稳定、全程可追踪，签收率高",PostalProperty="普货、弱磁类产品",UpdateTime="2022.7.12",},
                new PublicNote { GroupName="专线类",Name="带电专线",SheetName="带电专线",ReferenceTime="7-10工作日",Default="航班时效稳定、全程可追踪、签收率高",PostalProperty="弱磁类产品、内置电池等敏感货",UpdateTime="2022.10.29",},
                new PublicNote { GroupName="专线类",Name="普货快线",SheetName="普货快线",ReferenceTime="5-7工作日",Default="时效快、航班时效稳定、全程可追踪、签收率高",PostalProperty="普货、弱磁类产品",UpdateTime="2022.7.13",},

                 new PublicNote { GroupName="FBA头程",Name="美国空派",SheetName="美国空派",ReferenceTime="5-7个自然天提取派送",Default="国内包机直飞航班，时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2023.4.19",},
                   new PublicNote { GroupName="FBA头程",Name="美国空派-经济",SheetName="美国空派经济",ReferenceTime="6-8工作日提取派送",Default="国内包机直飞航班，性价比高",PostalProperty="普货、弱磁类产品",UpdateTime="2023.5.5",},
                   new PublicNote { GroupName="FBA头程",Name="美国空卡",SheetName="美国空卡",ReferenceTime="6-8工作日提取派送",Default="国内包机直飞航班，性价比高，时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2023.4.19",},
                new PublicNote { GroupName="FBA头程",Name="欧洲空派",SheetName="欧洲空派",ReferenceTime="6-8工作日提取派送",Default="国内固定板直飞航班、性价比高，时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2022.10.20",},
                 new PublicNote { GroupName="FBA头程",Name="加拿大空派",SheetName="加拿大空派",ReferenceTime="5-8个自然日提取",Default="航班时效稳定、尾程专车派送、性价比高",PostalProperty="普货、弱磁类产品",UpdateTime="2022.10.27",},
                 new PublicNote { GroupName="FBA头程",Name="美国-美森海派",SheetName="美森海派",ReferenceTime="19个自然日左右提取",Default="美森正班船/加班船、性价比高，时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2022.10.20",},
                  new PublicNote { GroupName="FBA头程",Name="美国海派专线",SheetName="美国海派专线",ReferenceTime="35个自然日左右提取",Default="OA联盟船、性价比高，时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2022.10.20",},
                   new PublicNote { GroupName="FBA头程",Name="欧洲海派",SheetName="欧洲海派",ReferenceTime="35-40个自然日提取",Default="OA联盟船、性价比高，时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2022.10.27",},
                    new PublicNote { GroupName="FBA头程",Name="美国-美森卡派",SheetName="美森卡派",ReferenceTime="13-18个自然日",Default="美森正班船/加班船、专车提取、性价比高，时效稳定",PostalProperty="普货、弱磁类产品",},
                     new PublicNote { GroupName="FBA头程",Name="美国海卡专线",SheetName="美国海卡专线",ReferenceTime="35-40个自然日",Default="OA联盟船、专车提取、性价比高，时效稳定",PostalProperty="普货、弱磁类产品",},

                  new PublicNote { GroupName="国际快递",Name="FedEx",SheetName="FedEx",ReferenceTime="4-7自然日",Default="上网提取快、签收时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2022.10.29",},
                new PublicNote { GroupName="国际快递",Name="DHL",SheetName = "广州DHL", ReferenceTime="4-7自然日",Default="上网提取快、签收时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2022.10.29",},
                new PublicNote { GroupName="国际快递",Name="香港快递",SheetName = "香港快递", ReferenceTime="2-4自然日",Default="只发香港、时效稳定",PostalProperty="普货、弱磁类产品",UpdateTime="2022.8.1",},
                 new PublicNote { GroupName="国际快递",Name="成都FedEx",SheetName = "成都FedEx", ReferenceTime="4-7自然日",Default="上网提取快、签收时效稳定",PostalProperty="普货、弱磁类产品",},
                    new PublicNote { GroupName="国际快递",Name="美国快递普货",SheetName = "美国快递普货", ReferenceTime="3-5工作日",Default="上网提取快、签收时效稳定",PostalProperty="普货、弱磁类产品",},
                       new PublicNote { GroupName="国际快递",Name="美国快递特货",SheetName = "美国快递特货", ReferenceTime="3-5工作日",Default="上网提取快、签收时效稳定",PostalProperty="可走带电,指甲胶化妆品",},

                  new PublicNote { GroupName="邮政渠道",Name="普货E邮宝",SheetName="普货E邮宝",ReferenceTime="10-25工作日",Default="次日上网、全程可追踪、一单到底",PostalProperty="普货",UpdateTime="2022.11.2",},
                new PublicNote { GroupName="邮政渠道",Name="邮政E邮宝A",SheetName="邮政E邮宝A",ReferenceTime="10-25工作日",Default="次日上网、全程可追踪、一单到底",PostalProperty="普货",UpdateTime="2022.11.2",},
                new PublicNote { GroupName="邮政渠道",Name="邮政E邮宝B",SheetName="邮政E邮宝B",ReferenceTime="12-28工作日",Default="4-5天上网、全程可追踪、一单到底",PostalProperty="普货",UpdateTime="2022.11.2",},
                new PublicNote { GroupName="邮政渠道",Name="特货E邮宝",SheetName = "特货E邮宝", ReferenceTime="10-25工作日",Default="次日上网、全程可追踪、一单到底",PostalProperty="带电、带磁等敏感货",UpdateTime="2022.10.25",},
                new PublicNote { GroupName="邮政渠道",Name="E特快",SheetName="E特快",ReferenceTime="15-30工作日",Default="次日上网、全程可追踪、一单到底",PostalProperty="普货",UpdateTime="2022.10.25",},
                 new PublicNote { GroupName="邮政渠道",Name="荷兰邮政小包",SheetName="荷兰邮政小包",ReferenceTime="10-20工作日",Default="可接普货和内电产品、电子烟；渠道稳定安全",PostalProperty="弱磁类产品、内置电池等敏感货",UpdateTime="2023.03.02",},

            };
            return list.Where(x => x.SheetName == name).FirstOrDefault();
        }
        public async Task GetPublic2022(Application app, Workbook source)
        {
            _Workbook templateWorkBook = app.Workbooks._Open(System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "主文件.xlsx"));
            var tempsheet = (Worksheet)templateWorkBook.ActiveSheet;
            tempsheet.Copy(source.Sheets[1], Type.Missing);
            templateWorkBook.Close();
            var sheet = (Worksheet)source.ActiveSheet;
            object[,] obj = new object[source.Sheets.Count, 8];
            int i = 0;
            foreach (Worksheet item in source.Sheets)
            {
                if (item.Name != "报价表目录")
                {
                    var note = GetNote2022(item.Name);
                    if (note != null)
                    {
                        obj[i, 0] = note.GroupName;
                        obj[i, 1] = note.Name;
                        obj[i, 2] = note.ReferenceTime;
                        obj[i, 3] = "进入报价表";
                        obj[i, 4] = note.Default;
                        obj[i, 5] = note.PostalProperty;
                        obj[i, 6] = note.UpdateTime;
                        obj[i, 7] = note.UpdateContent;
                        i++;
                    }
                }
            }
            var range = sheet.get_Range("A4");
            range = range.get_Resize(i, 8);
            range.set_Value(Missing.Value, obj);
            range.Borders.LineStyle = XlLineStyle.xlContinuous;
            range.RowHeight = 77;
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.Font.Size = 24;
            i = 0;

            foreach (Worksheet item in source.Sheets)
            {
                if (item.Name != "报价表目录")
                {
                    var note = GetNote2022(item.Name);
                    if (note != null)
                    {
                        i++;
                        var r = sheet.get_Range("D" + (i + 3));
                        sheet.Hyperlinks.Add(r, $"#{note.SheetName}!A1", Type.Missing, Type.Missing, Type.Missing);
                        r.Font.Size = 24;
                    }
                }
            }
            range.Font.Underline = false;
            range = sheet.get_Range("B4");
            range = range.get_Resize(i, 1);
            range.Font.Bold = true;

            range = sheet.get_Range("A4");
            range = range.get_Resize(i, 1);
            range.Font.Bold = true;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[1];
            range.ColumnWidth = 24.25;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[2];
            range.ColumnWidth = 37.85;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[3];
            range.ColumnWidth = 38.31;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[4];
            range.ColumnWidth = 30.09;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[5];
            range.ColumnWidth = 105.09;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[6];
            range.ColumnWidth = 67.12;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[7];
            range.ColumnWidth = 35.13;

            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[8];
            range.ColumnWidth = 29.94;

            //处理合并单元格
            var fileGenerator = new QuotationFileGenerator();
            fileGenerator.MergeCell(sheet, 4, "A", new string[] { "A" }, source.Sheets.Count);

            sheet.Protect("xyy0601");
        }
        #endregion

        #region 专线类
        private async Task CreateZX_KX_DDSheets2022(int customerId, Application app, Workbook wb, bool includeKX = true, bool includeDD = true, bool iscludeFBA = true, bool showSurcharge = true)
        {
            var quotations = await QueryCustomerQuotation(customerId);
            quotations = quotations.Where(i => i.生效时间 != "停用");
            var expData = quotations.Where(i => i.线路名称.Contains("快线"));

            var usThData = quotations.Where(i => i.线路名称.Contains("美国专线特惠"));

            var electrictData = quotations.Where(i => i.线路名称.Contains("带电"));
            var postalParcel = quotations.Where(i => i.线路名称.Contains("荷兰邮政小包"));//只是为了排除数据
            var fbaData =
                quotations.Where(i => i.线路名称 == "欧洲空派VAT"
                || i.线路名称 == "欧洲空派包税"
                || i.线路名称 == "美国空派普货专线(体积/6000计费)"//257
                || i.线路名称 == "美国空派普货专线(体积/5000计费)"//489
                || i.线路名称 == "美森加班船海派"  //369
                || i.线路名称 == "美森正班船海派"   //413
                || i.线路名称 == "美国海派专线"   //358
                || i.线路名称 == "欧洲海派包税"   //414
                || i.线路名称 == "欧洲海派VAT"   //415
                || i.线路名称 == "美森正班船海卡"   //416
                || i.线路名称 == "美森加班船海卡"   //417
                || i.线路名称 == "美国海卡专线"   //418
                || i.线路名称 == "加拿大空派专线"   //345
                || i.线路名称 == "广州UPS/5000"  //513
                || i.线路名称 == "广州DHL/5000"  //514
                || i.线路名称 == "大陆FEDEX IP/5000"  //515
                || i.线路名称 == "大陆FEDEX IE/5000"  //567
                || i.线路名称 == "香港UPS/5000"  //516
                || i.线路名称 == "香港DHL/5000"  //517
                || i.线路名称 == "香港FEDEX IP/5000"  //518
                || i.线路名称 == "美国空卡普货专线(体积/6000计费)"  //520
                || i.线路名称 == "美国空派普货经济专线(体积/6000计费)"  //532
                || i.线路名称 == "美国空派普货经济专线(体积/5000计费)"  //533
                );
            var specialLineData = quotations
                .Except(expData)
                .Except(fbaData)
                .Except(electrictData)
                .Except(postalParcel)
                .Except(usThData)
                .OrderBy(x => x.线路名称)
                .ThenBy(x => x.开始重量).ToList();

            var sheet1 = GetSpecialLineSheet2022(specialLineData, app, wb, showSurcharge);
            //sheet1.Copy(wb.ActiveSheet, Type.Missing);

            if (usThData?.Any() == true)
            {
                GetUSTHSheet2022(usThData, app, wb, showSurcharge);
            }

            if (electrictData?.Any() == true && includeDD)
            {
                var sheet3 = GetElectriLineExpSheet2022(electrictData, app, wb, showSurcharge);
                //sheet3.Copy(Type.Missing, wb.ActiveSheet);
            }

            if (expData?.Any() == true && includeKX)
            {
                var sheet2 = GetUsExpSheet2022(expData, app, wb, showSurcharge);
                //sheet2.Copy(Type.Missing, wb.ActiveSheet);
            }
            if (fbaData?.Any() == true && iscludeFBA)
            {
                GetFbaEXpSheet2022(fbaData, app, wb);
            }



            //处理配套固定sheet
            var fileGenerator = new QuotationFileGenerator();
            string fixationFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "专线_固定.xlsx");
            fileGenerator.BatchCopySheet(app, wb, fixationFile);

        }
        /// <summary>
        /// 普货专线
        /// </summary>
        /// <param name="quotationData"></param>
        /// <param name="app"></param>
        /// <param name="showSurcharge"></param>
        /// <returns></returns>
        public Worksheet GetSpecialLineSheet2022(IEnumerable<View_Quotation> quotationData, Microsoft.Office.Interop.Excel.Application app, Workbook wb, bool showSurcharge = true)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "普货专线.xlsx");
            var fileGenerator = new QuotationFileGenerator();
            var sheet = fileGenerator.MakeQuotationFile2022(quotationData, app, wb, templateFile, showSurcharge: showSurcharge, IsFirst: true);
            return sheet;
        }
        /// <summary>
        /// 美国专线特惠
        /// </summary>
        /// <param name="quotationData"></param>
        /// <param name="app"></param>
        /// <param name="showSurcharge"></param>
        /// <returns></returns>
        public Worksheet GetUSTHSheet2022(IEnumerable<View_Quotation> quotationData, Microsoft.Office.Interop.Excel.Application app, Workbook wb, bool showSurcharge = true)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美国专线特惠.xlsx");
            var fileGenerator = new QuotationFileGenerator();
            var sheet = fileGenerator.MakeQuotationFile2022(quotationData, app, wb, templateFile, showSurcharge: showSurcharge, IsFirst: false);
            return sheet;
        }
        /// <summary>
        /// 带电专线
        /// </summary>
        /// <param name="quotationData"></param>
        /// <param name="app"></param>
        /// <param name="showSurcharge"></param>
        /// <returns></returns>
        public Worksheet GetElectriLineExpSheet2022(IEnumerable<View_Quotation> quotationData, Microsoft.Office.Interop.Excel.Application app, Workbook wb, bool showSurcharge = true)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "带电专线.xlsx");
            var fileGenerator = new QuotationFileGenerator();
            var sheet = fileGenerator.MakeQuotationFile2022(quotationData, app, wb, templateFile, showSurcharge: showSurcharge);
            return sheet;
        }
        /// <summary>
        /// 普货快线
        /// </summary>
        /// <param name="quotationData"></param>
        /// <param name="app"></param>
        /// <param name="showSurcharge"></param>
        /// <returns></returns>
        public Worksheet GetUsExpSheet2022(IEnumerable<View_Quotation> quotationData, Microsoft.Office.Interop.Excel.Application app, Workbook wb, bool showSurcharge = true)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "普货快线.xlsx");
            var fileGenerator = new QuotationFileGenerator();
            var sheet = fileGenerator.MakeQuotationFile2022(quotationData, app, wb, templateFile, showSurcharge: showSurcharge);
            return sheet;
        }
        #endregion
        #region FBA头程
        private void GetFbaEXpSheet2022(IEnumerable<View_Quotation> expData, Application app, Workbook wb)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "欧洲空派.xlsx");
            string usFbaTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美国空派.xlsx");
            string mshpTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美森海派.xlsx");
            string ushpFbaTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美国海派专线.xlsx");
            string euhpTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "欧洲海派.xlsx");
            string mshkTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美森卡派.xlsx");
            string ushkTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美国海卡专线.xlsx");
            string caFbaTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "加拿大空派专线.xlsx");
            string p5kFbaTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "fba美国快递普货.xlsx");
            string t5kFbaTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "fba美国快递特货.xlsx");
            string uskkTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美国空卡.xlsx");
            string usECOFbaTemplateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美国空派经济.xlsx");


            var fileGenerator = new QuotationFileGenerator();
            bool isMakeEUKPFile2022 = fileGenerator.MakeEUKPFile2022(app, wb, expData.Where(x => x.ExpressId == 311 || x.ExpressId == 312), templateFile);
            bool isMakeUSKPFile2022 = fileGenerator.MakeUSKPFile2022(app, wb, expData.Where(x => x.ExpressId == 257 || x.ExpressId == 489), usFbaTemplateFile);
            bool isMakeUSECOKPFile2022 = fileGenerator.MakeUSECOKPFile2022(app, wb, expData.Where(x => x.ExpressId == 532 || x.ExpressId == 533), usECOFbaTemplateFile);
            bool isMakeUSKKFile2022 = fileGenerator.MakeUSKKFile2022(app, wb, expData.Where(x => x.ExpressId == 520), uskkTemplateFile);
            bool isMakeUSMSHPFile2022 = fileGenerator.MakeUSMSHPFile2022(app, wb, expData.Where(x => x.ExpressId == 413 || x.ExpressId == 369), mshpTemplateFile);
            bool isMakeUSHPFile2022 = fileGenerator.MakeUSHPFile2022(app, wb, expData.Where(x => x.ExpressId == 358), ushpFbaTemplateFile);
            bool isMakeEUHPFile2022 = fileGenerator.MakeEUHPFile2022(app, wb, expData.Where(x => x.ExpressId == 414 || x.ExpressId == 415), euhpTemplateFile);
            bool isMakeUSMSHKFile2022 = fileGenerator.MakeUSMSHKFile2022(app, wb, expData.Where(x => x.ExpressId == 416 || x.ExpressId == 417), mshkTemplateFile);
            bool isMakeUSHKFile2022 = fileGenerator.MakeUSHKFile2022(app, wb, expData.Where(x => x.ExpressId == 418), ushkTemplateFile);
            bool isMakeCAKPFile2022 = fileGenerator.MakeCAKPFile2022(app, wb, expData.Where(x => x.ExpressId == 345), caFbaTemplateFile);
            bool isMakep5kFile2022 = fileGenerator.MakeUS5KFile2022(app, wb, expData.Where(x => x.ExpressId == 513 || x.ExpressId == 514 || x.ExpressId == 515||x.ExpressId==567), p5kFbaTemplateFile);
            bool isMaket5kFile2022 = fileGenerator.MakeUS5KFile2022(app, wb, expData.Where(x => x.ExpressId == 516 || x.ExpressId == 517 || x.ExpressId == 518), t5kFbaTemplateFile);
           

            //多个sheet 会共用，抽取出来，在外部处理固定sheet
            if (isMakeUSKPFile2022 || isMakeUSMSHPFile2022 || isMakeUSHPFile2022 || isMakeUSMSHKFile2022 || isMakeUSHKFile2022)
            {
                //处理配套固定sheet
                string fixationFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "美国空派_固定.xlsx");
                fileGenerator.BatchCopySheet(app, wb, fixationFile);
            }
            if (isMakeEUKPFile2022 || isMakeEUHPFile2022)
            {
                //处理配套固定sheet
                string fixationFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "欧洲空派_固定.xlsx");
                fileGenerator.BatchCopySheet(app, wb, fixationFile);
            }
        }
        #endregion
        #region 国际快递

        private async Task SetGZFedex2022(int customerId, Application app, Workbook wb)
        {
            //GzFedex
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 48);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var list = await _feeBaseRepository.GetDataTableAsync($@" 
                           if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                                           drop table #temp_fedex
	                                        select  distinct  convert(decimal(18,1), EndWeight/1000.0) as [公斤/国家],b.分区,fee
	                                            into #temp_fedex
	                                            from
		                                            Logistics_ExpressFeeRange(nolock) a
							                        join logistics_fedexzone(nolock) b on a.Countory=b.二字代码
	                                            where BaseId= {feeBase.Id} 
	                                            order by b.分区,convert(decimal(18,1), EndWeight/1000.0)
                                            declare @countrys nvarchar(max)
                                            set @countrys= stuff((select  distinct ',['+ 分区+']'   from #temp_fedex order by ',['+ 分区+']'  for xml path('')),1,1,'') 
                                            print @countrys 
                                            declare @sql nvarchar(max) = 'select * from #temp_fedex b pivot(max(fee) 
	                                            for 分区 in('+@countrys+'))a order by [公斤/国家]'
                                            exec(@sql) 
                      ");

                List<int> BranchCompanyId = new List<int>() { 387, 389, 390, 719 };
                string field = string.Empty;

                if (BranchCompanyId.Contains(customerId)) { field = "a.Id"; } else { field = "a.BranchCompanyId"; }

                bool isNewServiceSurcharge = false;
                string IsTZ =
                  @"select distinct   F1 AS Id from f_splitstr((select STUFF((select ''+CustomerId from Logistics_ExpressSettlementAdjustment(nolock) where ExpressId=48 for xml path('')),1,1,'')),',') where ''!=F1";
                var tzlist = await _feeBaseRepository.QueryBySqlAsync(IsTZ);
                if (tzlist.Any(x => x.Id == customerId) || BranchCompanyId.Contains(customerId))
                {
                    isNewServiceSurcharge = true;
                }

                var additionalCharge = await _feeBaseRepository.GetDataTableAsync($@"  select CountryCode,d.CnName,b.分区,FristFee from  Finance_ServiceSurchargeDetail(nolock) c
 left join Logistics_FedexZone(nolock) b on b.二字代码=c.CountryCode
 left join Sys_Country(nolock) d on d.Code=c.CountryCode and d.IsEnable=1
 where c.BaseId=(select top 1 b.Id from User_Customer(nolock) a left join Finance_ServiceSurcharge(nolock) b on b.CustomerId={field} where a.Id={customerId} and ExpressId=48 order by b.EnableTime,b.Id desc) ");

                string sql = $"select top 1 * from Logistics_ExpressSettlementAdjustment where CustomerId like '%,{customerId},%'  and ExpressId=48";
                var adjData = await _feeBaseRepository.GetDataTableAsync(sql);
                bool isNewFedex = false;
                if (adjData != null && adjData.Rows.Count > 0) { isNewFedex = true; }


                var fileGenerator = new QuotationFileGenerator();
                string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "FedEx.xlsx");
                fileGenerator.MakeGZFedexFile2022(app, wb, list, templateFile, feeBase.EnableTime, isNewFedex);

                string elementFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "FedEx_固定10.xlsx");
                fileGenerator.MakeGZFedexElementFile2022(app, wb, isNewServiceSurcharge?additionalCharge:null, elementFile);

            }

        }
        private async Task SetDHL2022(int customerId, Application app, Workbook wb)
        {
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 50);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var list = await _feeBaseRepository.GetDataTableAsync($@"
                 if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                   drop table #temp_fedex
	                select  convert(decimal(18,1), EndWeight/1000.0) as [公斤/国家],b.分区,convert(decimal(18,4) ,fee)fee
	                    into #temp_fedex
	                    from
		                    Logistics_ExpressFeeRange(nolock) a
							join logistics_expressdhlzone(nolock) b on a.Countory=b.code
	                    where BaseId={feeBase.Id}
	                    order by EndWeight
                    declare @countrys nvarchar(max)
                    set @countrys= stuff((select  distinct ',['+ 分区+']'   from #temp_fedex  order by  ',['+ 分区+']' for xml path('')),1,1,'')
                    print @countrys 
                    declare @sql nvarchar(max) = 'select * from #temp_fedex b pivot(max(fee) 
	                    for 分区 in('+@countrys+'))a order by [公斤/国家]'
                    exec(@sql) 
                      ");

                #region 附加费取值处理

                List<int> BranchCompanyId = new List<int>() { 387, 389, 390, 719 };
                string field = string.Empty;
                if (BranchCompanyId.Contains(customerId)) { field = "a.Id"; } else { field = "a.BranchCompanyId"; }

                string IsTZ =
                    @"select distinct   F1 AS Id from f_splitstr((select STUFF((select ''+CustomerId from Logistics_ExpressSettlementAdjustment(nolock) where ExpressId=50 for xml path('')),1,1,'')),',') where ''!=F1";
                var tzlist = await _feeBaseRepository.QueryBySqlAsync(IsTZ);

                var additionalChargSql = @"
                         select CountryCode,d.CnName,b.分区,FristFee from  Finance_ServiceSurchargeDetail(nolock) c
                        left join Logistics_ExpressDHLZone(nolock) b on b.Code=c.CountryCode
                        left join Sys_Country(nolock) d on d.Code=c.CountryCode and d.IsEnable=1
                        where c.BaseId=(select top 1  Id from Finance_ServiceSurcharge(nolock)  where ExpressId=50 and ISNULL(CustomerId,0)=0 order by EnableTime desc,Id desc)
                        and c.IsDelete =0
                        ";
                if (tzlist.Any(x => x.Id == customerId) || BranchCompanyId.Contains(customerId))
                {
                    additionalChargSql = $@"
                            select CountryCode,d.CnName,b.分区,FristFee from  Finance_ServiceSurchargeDetail(nolock) c
                                left join Logistics_ExpressDHLZone(nolock) b on b.Code=c.CountryCode
                                left join Sys_Country(nolock) d on d.Code=c.CountryCode and d.IsEnable=1
                            where c.BaseId=(select top 1 b.Id from User_Customer(nolock) a left join Finance_ServiceSurcharge(nolock) b on b.CustomerId={field} where a.Id={customerId} and ExpressId=50 order by b.EnableTime desc,b.Id desc) ";
                }
                var additionalCharge = await _feeBaseRepository.GetDataTableAsync(additionalChargSql);
                #endregion

                var fileGenerator = new QuotationFileGenerator();
                string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "DHL.xlsx");
                fileGenerator.MakeDHLFile2022(app, wb, list, templateFile, feeBase.EnableTime);

                string elementFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "DHL_固定10.xlsx");
                fileGenerator.MakeDHLElementFile2022(app, wb, additionalCharge, elementFile);
            }
        }
        private async Task SetHK2022(int customerId, Application app, Workbook wb)
        {
            //香港
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 49);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var list = await _feeBaseRepository.GetDataTableAsync($@" 
	                    select ROW_NUMBER()over(order by fee)rn, '香港' Area,convert(decimal(18,1),endweight/1000.0) as [重量/KG],fee 价格 from Logistics_ExpressFeeRange
	                    where BaseId={feeBase.Id} ");

                var fileGenerator = new QuotationFileGenerator();
                string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "香港快递.xlsx");
                fileGenerator.MakeHKFile2022(app, wb, list, templateFile);
            }
        }
        private async Task SetCDFedex2022(int customerId, Application app, Workbook wb)
        {
            //成都Fedex-Pak
            var fedexPakBases = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 232);
            var fedexPakBase = fedexPakBases.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            //成都Fedex-非Pak
            var fedexBases = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 231);
            var fedexBase = fedexBases.OrderByDescending(x => x.EnableTime).FirstOrDefault();

            if (fedexPakBase != null && fedexBase != null)
            {
                //  快递袋\r\n（不计抛）
                var fedexPakList = await _feeBaseRepository.GetDataTableAsync($@"
                if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                   drop table #temp_fedex
	                select  convert(decimal(18,1), EndWeight/1000.0) as [公斤/国家],b.分区,fee
	                    into #temp_fedex
	                    from
		                    Logistics_ExpressFeeRange(nolock) a
                            join logistics_fedexzone(nolock) b on a.Countory=b.二字代码
	                    where BaseId={fedexPakBase.Id} 
	                    order by EndWeight,b.分区
                    declare @countrys nvarchar(max)
                    set @countrys= stuff((select  distinct ',['+ 分区+']'   from #temp_fedex  order by ',['+ 分区+']' for xml path('')),1,1,'')
                    print @countrys 
                    declare @sql nvarchar(max) = 'select * from #temp_fedex b pivot(max(fee) 
	                    for 分区 in('+@countrys+'))a order by [公斤/国家]'
                    exec(@sql)
                    print @sql 
                      ");
                // 包裹\r\n（体积除以5000）
                var fedexList = await _feeBaseRepository.GetDataTableAsync($@"
               if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                   drop table #temp_fedex
	                select  convert(decimal(18,1), EndWeight/1000.0) as [公斤/国家],b.分区,fee
	                    into #temp_fedex
	                    from
		                    Logistics_ExpressFeeRange(nolock) a
                            join logistics_fedexzone(nolock) b on a.Countory=b.二字代码
	                    where BaseId={fedexBase.Id} 
	                    order by EndWeight,b.分区
                    declare @countrys nvarchar(max)
                    set @countrys= stuff((select  distinct ',['+ 分区+']'   from #temp_fedex order by ',['+ 分区+']' for xml path('')),1,1,'')
                    print @countrys 
                    declare @sql nvarchar(max) = 'select * from #temp_fedex b pivot(max(fee) 
	                    for 分区 in('+@countrys+'))a order by [公斤/国家]'
                    exec(@sql)
                    print @sql 
                      ");

                var fileGenerator = new QuotationFileGenerator();
                string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "成都FedEx.xlsx");
                fileGenerator.MakeCDFedexFile2022(app, wb, fedexPakList, fedexList, templateFile);
            }
        }
        #endregion

        #region 邮政渠道
        private async Task EubJudge(int customerId, Application app, Workbook wb)// string name, string code, int expressId, 
        {
            //写死渠道值，26  126 106
            string sql = @$"declare @intTable table(
	ExpressId int
)
insert into @intTable values(26),(126),(106)

select b.* from @intTable a
outer apply (select top 1 * from Logistics_ExpressFeeBase where CustomerId={customerId} and ExpressId=a.ExpressId order by EnableTime desc)b ";
            var list = await _feeBaseRepository.QueryBySqlAsync(sql);

            var feeBase26 = list.Where(x => x.ExpressId == 26)?.FirstOrDefault();
            var feeBase126 = list.Where(x => x.ExpressId == 126)?.FirstOrDefault();
            var feeBase106 = list.Where(x => x.ExpressId == 106)?.FirstOrDefault();
            if (feeBase26 != null && feeBase26.BaseExpressId > 0)
            {
                await SetPEub202210B(customerId, "普货E邮宝", "JSEUB", 26, app, wb);
                await SetPEub202210A(customerId, "普货E邮宝", "EUB-A", 437, app, wb);
            }
            else
            {
                await SetEub2022(customerId, "普货E邮宝", "JSEUB", 26, app, wb);
            }
            if (feeBase126 != null && feeBase126.BaseExpressId > 0)
            {
                await SetTEub202210(customerId, "特货E邮宝", "THeub", 126, app, wb);
            }
            else
            {
                await SetEub2022(customerId, "特货E邮宝", "THeub", 126, app, wb);
            }

            if (feeBase106 != null && feeBase106.BaseExpressId > 0)
            {
                await SetETK202210(customerId, app, wb);
            }
            else
            {
                await SetETK2022(customerId, app, wb);
            }
            //增加一个特殊的邮政渠道报价文件
            await SetNLEub2023(customerId, 495, app, wb);

        }
        private async Task SetETK2022(int customerId, Application app, Workbook wb)
        {
            //E特快
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == 106);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null)
            {
                var list = await _feeBaseRepository.GetDataTableAsync($@"
	                select ROW_NUMBER() OVER (order by b.Code) [序号],b.CnName[国家],b.Code[二字代码], convert(decimal(18,3),a.FristFee)[首重(元/50G)], convert(decimal(18,3),a.ContinuedFee)[续重(元/50G)] from Logistics_ExpressFeeGrads(nolock)a 
	                join Sys_Country(nolock) b on a.Countory=b.Code and b.CnName<>'阿联酋'
	                where BaseId={feeBase.Id}  and a.IsDelete=0 order by b.Code");
                var fileGenerator = new QuotationFileGenerator();
                string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "E特快.xlsx");
                fileGenerator.MakeETKFile2022(app, wb, list, templateFile);
            }
        }
        private async Task SetETK202210(int customerId, Application app, Workbook wb)
        {
            //E特快
            var list = await _feeBaseRepository.GetDataTableAsync($@"select r [序号],[国家],二字简码,操作费  [首重(元/50G)],(运费*续重) [续重(元/50G)]
,case when 二字简码='US' and 限重='1.000' then '0KG<重量≤1KG' when 二字简码='US' and 限重='3.000' then'1.001KG<重量≤3KG' when 二字简码='US' and 限重='30.000' then'3.001KG<重量≤30KG' else '' end [备注]
from  View_ChinaPostQuotation where ExpressId=106
order by r");
            var fileGenerator = new QuotationFileGenerator();
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "E特快10.xlsx");
            fileGenerator.MakeETKFile202210(app, wb, list, templateFile);
        }
        private async Task SetEub2022(int customerId, string name, string code, int expressId, Application app, Workbook wb)
        {
            var fees = await _feeBaseRepository.QueryAsync(x => x.CustomerId == customerId && x.ExpressId == expressId);
            var feeBase = fees.OrderByDescending(x => x.EnableTime).FirstOrDefault();
            if (feeBase != null && feeBase.EnableTime > DateTime.Parse("2022-01-01"))
            {
                var list = await _feeBaseRepository.GetDataTableAsync(
                    $@"select b.CnName as 路向,b.Code as 简码, convert(decimal(14,2), a.FristFee -a.FristWeight*A.ContinuedFee) as [元/件], convert(decimal(14,2),a.ContinuedFee/a.ContinuedWeight*1000) as [元/千克]
                        ,a.FristWeight as [起重/克],a.MaxWeight as [限重/克]  from Logistics_ExpressFeeGrads(nolock)a
                    left join Sys_Country(nolock) b on a.Countory=b.Code and b.CnName<>'大不列颠联合王国' and b.CnName<>'俄罗斯联邦' and b.CnName<>'中国，香港特别行政区' and b.CnName<>'美国'
	                    where  BaseID =  {feeBase.Id} 
						order by Code,FristWeight ");
                var fileGenerator = new QuotationFileGenerator();
                string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", $"{name}.xlsx");
                fileGenerator.MakeEUBFile2022(app, wb, list, templateFile);
            }

        }
        private async Task SetTEub202210(int customerId, string name, string code, int expressId, Application app, Workbook wb)
        {
            var list = await _feeBaseRepository.QueryAsync<EubFile_202210>(
                    $@"select b.StateCnName[洲名],a.国家 as 路向,a.二字简码 简码,a.操作费 [元件],a.运费 [元千克],a.FristWeight [起重克],a.MaxWeight as [限重克],'' [备注]
from View_ChinaPostQuotation a
 left join Sys_Country(nolock) b on a.二字简码=b.Code and b.IsEnable=1 
 where a.ExpressId={expressId}");//126  固定值
            var fileGenerator = new QuotationFileGenerator();
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", $"特货E邮宝10.xlsx");
            fileGenerator.MakeEUBFile202210(app, wb, list.ToList(), templateFile);

        }
        private async Task SetPEub202210A(int customerId, string name, string code, int expressId, Application app, Workbook wb)
        {
            var list = await _feeBaseRepository.QueryAsync<EubFile_202210>(
                    $@"select b.StateCnName[洲名],a.国家 as 路向,a.二字简码 简码,a.操作费 [元件],a.运费 [元千克],a.FristWeight [起重克],a.MaxWeight as [限重克],(select top 1 CONVERT(nvarchar(50),convert(decimal(18,0),Price*100))+'%' from Logistics_Fee_Other where CustomerId = {customerId} and ExpressId ={expressId} and FeeType = 4 and IsEffective=1  order by EffectiveDate desc) [折扣]
from View_ChinaPostQuotation a
 left join Sys_Country(nolock) b on a.二字简码=b.Code and b.IsEnable=1 
 where a.ExpressId={expressId}");//437  固定值
            var fileGenerator = new QuotationFileGenerator();
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", $"邮政E邮宝A.xlsx");
            fileGenerator.MakePEUBFile202210(app, wb, list.ToList(), templateFile);

        }
        private async Task SetPEub202210B(int customerId, string name, string code, int expressId, Application app, Workbook wb)
        {
            var list = await _feeBaseRepository.QueryAsync<EubFile_202210>(
                    $@"select b.StateCnName[洲名],a.国家 as 路向,a.二字简码 简码,a.操作费 [元件],a.运费 [元千克],a.FristWeight [起重克],a.MaxWeight as [限重克],(select top 1 CONVERT(nvarchar(50),convert(decimal(18,0),Price*100))+'%' from Logistics_Fee_Other where CustomerId = {customerId} and ExpressId ={expressId} and FeeType = 4 and IsEffective=1 order by EffectiveDate desc) [折扣]
from View_ChinaPostQuotation a
 left join Sys_Country(nolock) b on a.二字简码=b.Code and b.IsEnable=1 
 where a.ExpressId={expressId}");//26  固定值
            var fileGenerator = new QuotationFileGenerator();
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", $"邮政E邮宝B.xlsx");
            fileGenerator.MakePEUBFile202210(app, wb, list.ToList(), templateFile);

        }
        private async Task SetNLEub2023(int customerId, int expressId, Application app, Workbook wb)
        {
            var list = await _feeBaseRepository.QueryAsync<NLEubFile_2023>(
                    $@"select ROW_NUMBER()over(order by StateCnName) rn,a.*,b.StateCnName,b.CnName from View_Quotation a 
 left join Sys_Country_ByNL(nolock) b on a.Countory=b.Code and b.IsEnable=1 
where a.ExpressId={expressId} and a.CustomerId={customerId}");//495  固定值
            var fileGenerator = new QuotationFileGenerator();
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", $"荷兰邮政.xlsx");
            fileGenerator.MakeNLEUBFile2023(app, wb, list.ToList(), templateFile);

        }
        #endregion

        #endregion
    }
}
