using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard;
using XYY.Model.Standard.Finance;
using Dapper;
using XYY.Model.Standard.Logistics;
using NPOI.SS.Formula.Functions;
using XYY.Model.Standard.Enums;
using RestSharp;
using Microsoft.AspNetCore.Http.Connections.Internal;
using Newtonsoft.Json;
using XYY.Data.Standard.Finance;
using Microsoft.Office.Interop.Excel;
using Aliyun.OSS;
using AspectCore.DynamicProxy;
using XYY.Core.Standard.Mvc;
using System.IO;
using XYY.TaskTrack.Standard.TaskModel;
using ICSharpCode.SharpZipLib.Checksums; 
using NPOI.OpenXml4Net.OPC.Internal;
using Nest;

namespace XYY.Service.Standard.Finance
{
    /// <summary>
    /// 处理批量生成报价文件业务
    /// </summary>
    public interface IExpressBulkAdjustmentQuoteService
    {
        Task AddQuoteJob(Finance_QutationApprove qutationApprove);
        Task<Tuple<bool, string>> GenerateQuotation();
        Task<Tuple<bool, string>> GenerateCustomerFile(int fileCount);
        Task<Tuple<bool, string>> GenerateZipFile();
        Task AddQuoteJobBySales(int userId, int channelId);
    }
    public class ExpressBulkAdjustmentQuoteService : IExpressBulkAdjustmentQuoteService
    {
        IUnitOfWork _unitOfWork;
        IQuotationService _quotationService;
        ILogistics_ExpressFeeBaseRepository _feeBaseRepository;
        ILogistics_ExpressFeeGradsRepository _gradsRepository;
        ILogistics_ExpressFeeRangeRepository _rangerepository;
        IDingDingBaseService _dingDingBaseService;

        public ExpressBulkAdjustmentQuoteService(IUnitOfWork unitOfWork, IQuotationService quotationService, ILogistics_ExpressFeeBaseRepository feeBaseRepository, ILogistics_ExpressFeeGradsRepository gradsRepository, ILogistics_ExpressFeeRangeRepository rangerepository, IDingDingBaseService dingDingBaseService)
        {
            _unitOfWork = unitOfWork;
            _quotationService = quotationService;
            _feeBaseRepository = feeBaseRepository;
            _gradsRepository = gradsRepository;
            _rangerepository = rangerepository;
            _dingDingBaseService = dingDingBaseService;
        }
        #region 数据获取
        /// <summary>
        /// 获取需要执行的数据
        /// 取之前的数据
        /// </summary>
        /// <returns></returns>
        public async Task<ExpressBulkAdjustmentExcuteDto> GetGenerateData(AdjustmentQuoteLogStatuEnum status)
        {
            //
            string sql = $@" select top 1 *from Logistics_ExpressBulkAdjustmentQuoteLog(nolock) where AdjustmentQuoteLogStatus={(int)status}   order by Id ";
            var logInfo = (await _unitOfWork.QueryBySqlAsync<Logistics_ExpressBulkAdjustmentQuoteLog>(sql))?.FirstOrDefault();
            List<Logistics_ExpressBulkAdjustmentQuoteLogDetail> details = new List<Logistics_ExpressBulkAdjustmentQuoteLogDetail>();
            if (logInfo != null)
            {
                string getDetailsSql = $" select *from Logistics_ExpressBulkAdjustmentQuoteLogDetail where LogId={logInfo.Id} ";
                details = (await _unitOfWork.QueryBySqlAsync<Logistics_ExpressBulkAdjustmentQuoteLogDetail>(getDetailsSql)).ToList();
            }
            return new ExpressBulkAdjustmentExcuteDto() { logInfo = logInfo, details = details };
        }
        /// <summary>
        /// 调接口取生成数据
        /// </summary>
        /// <returns></returns>
        public async Task<List<CustomerQuotationData>> GetQuotationDetails(int ExpressId, List<Logistics_ExpressBulkAdjustmentQuoteLogDetail> details)
        {
            List<CustomerQuotationData> result = new List<CustomerQuotationData>();
            foreach (var item in details.Where(x => x.DetailStatus == AdjustmentQuoteLogStatuEnum.draft))
            {
                string getSettlementAdjustmentDetailSql = $@"
select * from Logistics_ExpressSettlementAdjustmentDetail(nolock) where AdjustmentId = (select top 1 Id from Logistics_ExpressSettlementAdjustment(nolock) where ExpressId={ExpressId} and CustomerId like '%,{item.CustomerId},%' order by id desc) ";
                var detailList = (await _unitOfWork.QueryBySqlAsync<Logistics_ExpressSettlementAdjustmentDetail>(getSettlementAdjustmentDetailSql)).ToList();
                Logistics_ExpressSettlementAdjustmentDto dto = new Logistics_ExpressSettlementAdjustmentDto()
                {
                    ExpressId = ExpressId,
                    CustomerId = new int[] { item.CustomerId },
                    Details = detailList,
                };
                var client = new RestClient("http://localhost:9521/api/FinanceQuotation/GetExpressFeeAsAdjustment");
                client.Timeout = -1;
                var request = new RestRequest(Method.POST);
                request.AddHeader("Authorization", "token 57AEDE5487260443D6BF2182D31286B1");
                request.AddJsonBody(dto);
                IRestResponse response = client.Execute(request);
                if (response.StatusCode == System.Net.HttpStatusCode.OK)
                {
                    var json = JsonConvert.DeserializeObject<ApiJsonModel>(response.Content);
                    var data = JsonConvert.DeserializeObject<ExpressFeeAsAdjustmentResult>(json.data.ToString());
                    result.Add(new CustomerQuotationData()
                    {
                        CustomerId = item.CustomerId,
                        AdjustmentId = detailList.FirstOrDefault().AdjustmentId,
                        quotations = data.detail
                    });
                }
                else
                {
                    result.Add(new CustomerQuotationData()
                    {
                        CustomerId = item.CustomerId,
                        AdjustmentId = detailList.FirstOrDefault().AdjustmentId,
                        quotations = new List<View_Quotation>()
                    });//添加空数据，判断为异常
                }
            }
            return result;
        }
        #endregion
        #region 流程安全控制
        public async Task AddQuoteJobBySales(int userId, int channelId)
        {
            if (new int[] { 454, 755 }.Contains(userId)) { userId = 164; }//测试使用，更改用户id以获取客户
            if (channelId != 48 && channelId != 50) { throw new Exception("当前仅fedex和DHL可以进行该设置，请重新选择渠道，查询后使用！"); }
            //获取销售名下的所有调整客户
            string getBatchCustomerSql = $@"select a.CompanyName CustomerCompanyName,a.id CustomerId,b.Id SalesmanUserId,b.Phone,b.NickName,c.Id BranchCompanyId,c.CompanyName BranchCompanyName from User_Customer(nolock) a
left join User_Info(nolock) b on b.id=a.SalesmanUserId
left join User_Customer(nolock) c on c.Id=a.BranchCompanyId
left join (select distinct 10 Id,F1 from f_splitstr((select STUFF((select ''+CustomerId from Logistics_ExpressSettlementAdjustment(nolock) where ExpressId={channelId} for xml path('')),1,1,'')),',') where ''!=F1)e on e.F1=a.Id
where ''!=isnull(c.CompanyName,'') and b.Id>0 and e.Id>0 and a.SalesmanUserId={userId} ";
            List<ExpressBulkAdjustmentQuoteDto> quoteDtos = (await _unitOfWork.QueryBySqlAsync<ExpressBulkAdjustmentQuoteDto>(getBatchCustomerSql)).ToList();
            if (quoteDtos == null || quoteDtos.Count == 0) { throw new Exception("没有任何已调整过运费的用户，无法使用此功能！"); }
            //手动创建批次号
            string batchNo = DateTime.Now.ToString("yyyyMMddHHmmss");
            Logistics_ExpressBulkAdjustmentQuoteLog log = new Logistics_ExpressBulkAdjustmentQuoteLog()
            {
                BatchNo = batchNo,
                ExpressId = channelId,
                SalesmanUserId = userId,
                EnableTime = DateTime.Now,//字段无使用，仅记录意义
                AdjustmentQuoteLogStatus = AdjustmentQuoteLogStatuEnum.draft,
                Description = $@"用户[{userId}],手动点击一键更新运费,更新渠道：[{channelId}]结算价"
            };
            int logId = Convert.ToInt32((await _unitOfWork.InsertAsync(log)));
            if (logId==0) { throw new Exception("一键更新运费未能正确执行，请联系技术查看"); }
            if (logId > 0)
            {
                List<Logistics_ExpressBulkAdjustmentQuoteLogDetail> details = new List<Logistics_ExpressBulkAdjustmentQuoteLogDetail>();
                foreach (var detail in quoteDtos)
                {
                    Logistics_ExpressBulkAdjustmentQuoteLogDetail logDetail = new Logistics_ExpressBulkAdjustmentQuoteLogDetail()
                    {
                        LogId = logId,
                        CustomerId = detail.CustomerId,
                        CompanyName = detail.CustomerCompanyName,
                        DetailStatus = AdjustmentQuoteLogStatuEnum.draft,
                    };
                    details.Add(logDetail);
                }
                await _unitOfWork.BulkToDBAsync(details);
            }
        }
        /// <summary>
        /// 审批自动添加任务
        /// 已停止使用
        /// </summary>
        /// <param name="qutationApprove"></param>
        /// <returns></returns>
        public async Task AddQuoteJob(Finance_QutationApprove qutationApprove)
        {
            List<string> customerIds = qutationApprove.CustomerIds.Split(',', StringSplitOptions.RemoveEmptyEntries).ToList();
            string getCustomerSql = $"select *from User_Customer where Type=1 and Id in ({string.Join(",", customerIds)}) ";
            var customerInfos = await _unitOfWork.QueryBySqlAsync<User_Customer>(getCustomerSql);
            if (customerInfos != null && customerInfos.Count() > 0)
            {
                string getBatchCustomerSql = $@"select a.CompanyName CustomerCompanyName,a.id CustomerId,b.Id SalesmanUserId,b.Phone,b.NickName,c.Id BranchCompanyId,c.CompanyName BranchCompanyName from User_Customer(nolock) a
left join User_Info(nolock) b on b.id=a.SalesmanUserId
left join User_Customer(nolock) c on c.Id=a.BranchCompanyId
left join (select distinct 10 Id,F1 from f_splitstr((select STUFF((select ''+CustomerId from Logistics_ExpressSettlementAdjustment(nolock) where ExpressId={qutationApprove.ExpressId} for xml path('')),1,1,'')),',') where ''!=F1)e on e.F1=a.Id
where ''!=isnull(c.CompanyName,'') and b.Id>0 and e.Id>0 and a.BranchCompanyId in ({string.Join(",", customerInfos.Select(x => x.Id).Distinct().ToList())})";
                List<ExpressBulkAdjustmentQuoteDto> quoteDtos = (await _unitOfWork.QueryBySqlAsync<ExpressBulkAdjustmentQuoteDto>(getBatchCustomerSql)).ToList();
                //按销售分组，写入数据，存在一个销售多分公司的情况
                var sales = quoteDtos.GroupBy(x => x.SalesmanUserId).ToList();
                //手动创建批次号
                string batchNo = DateTime.Now.ToString("yyyyMMddHHmmss");
                foreach (var item in sales)
                {
                    Logistics_ExpressBulkAdjustmentQuoteLog log = new Logistics_ExpressBulkAdjustmentQuoteLog()
                    {
                        BatchNo = batchNo,
                        ExpressId = qutationApprove.ExpressId,
                        SalesmanUserId = item.Key,
                        EnableTime = qutationApprove.EnableTime,
                        AdjustmentQuoteLogStatus = AdjustmentQuoteLogStatuEnum.draft,
                        Description = $@"[{qutationApprove.CreateUserName}]申请修改渠道：[{qutationApprove.ExpressId}],结算价，申请Id:[{qutationApprove.Id}]"
                    };
                    int logId = Convert.ToInt32((await _unitOfWork.InsertAsync(log)));
                    List<Logistics_ExpressBulkAdjustmentQuoteLogDetail> details = new List<Logistics_ExpressBulkAdjustmentQuoteLogDetail>();
                    foreach (var detail in quoteDtos.Where(x => x.SalesmanUserId == item.Key))
                    {
                        Logistics_ExpressBulkAdjustmentQuoteLogDetail logDetail = new Logistics_ExpressBulkAdjustmentQuoteLogDetail()
                        {
                            LogId = logId,
                            CustomerId = detail.CustomerId,
                            CompanyName = detail.CustomerCompanyName,
                            DetailStatus = AdjustmentQuoteLogStatuEnum.draft,
                        };
                        details.Add(logDetail);
                    }
                    await _unitOfWork.BulkToDBAsync(details);
                }

            }

        }
        /// <summary>
        /// 检验渠道最新任务
        /// 如果有，旧数据标为过期
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<Tuple<bool, string>> VerifyAdjustmentQuote(int Id, int ExpressId, int SalesmanUserId)
        {
            string dataSql = $@"select top 1 *from Logistics_ExpressBulkAdjustmentQuoteLog(nolock) where Id!={Id} and AdjustmentQuoteLogStatus not in ({(int)AdjustmentQuoteLogStatuEnum.delete},{(int)AdjustmentQuoteLogStatuEnum.success}) and ExpressId={ExpressId} and SalesmanUserId={SalesmanUserId} order by id desc ";
            Logistics_ExpressBulkAdjustmentQuoteLog newData = (await _unitOfWork.QueryBySqlAsync<Logistics_ExpressBulkAdjustmentQuoteLog>(dataSql))?.FirstOrDefault();
            if (newData == null) { return Tuple.Create<bool, string>(true, "无新数据，继续执行"); }
            else if (newData.Id > Id)
            {
                string deleteSql = $@" update Logistics_ExpressBulkAdjustmentQuoteLog set AdjustmentQuoteLogStatus={(int)AdjustmentQuoteLogStatuEnum.delete} where Id!={newData.Id} and AdjustmentQuoteLogStatus not in ({(int)AdjustmentQuoteLogStatuEnum.delete},{(int)AdjustmentQuoteLogStatuEnum.success}) and ExpressId={ExpressId} and SalesmanUserId={SalesmanUserId} ";
                await _unitOfWork.ExecuteAsync(deleteSql);
                return Tuple.Create<bool, string>(false, "已有新数据，中断执行");
            }
            return Tuple.Create<bool, string>(true, "可以继续执行");
        }
        /// <summary>
        /// 状态变更控制
        /// 不考虑删除改为正常，下次执行仍会验证，可正常中止
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="tuples"></param>
        /// <returns></returns>
        public async Task UpdateQuoteJob(ExpressBulkAdjustmentExcuteDto dto, List<Tuple<int, bool, string>> tuples)
        {
            //用于缩小更新范围
            List<Logistics_ExpressBulkAdjustmentQuoteLogDetail> updateDetails = new List<Logistics_ExpressBulkAdjustmentQuoteLogDetail>();
            //更新此次执行状态
            foreach (var item in dto.details)
            {
                item.ExecuteCount += 1;
                var result = tuples.Where(x => x.Item1 == item.CustomerId)?.FirstOrDefault();
                //空不处理
                if (result == null) { continue; }
                if (!result.Item2)
                {
                    //失败仅更新说明
                    item.ResultMsg = result.Item3;
                }
                else
                {
                    //成功更新状态，清空执行次数
                    item.ExecuteCount = 0;
                    item.ResultMsg = "";
                    if (item.DetailStatus == AdjustmentQuoteLogStatuEnum.draft) { item.DetailStatus = AdjustmentQuoteLogStatuEnum.dataReady; }
                    else if (item.DetailStatus == AdjustmentQuoteLogStatuEnum.dataReady) { item.DetailStatus = AdjustmentQuoteLogStatuEnum.fileReady; }
                    else if (item.DetailStatus == AdjustmentQuoteLogStatuEnum.fileReady) { item.DetailStatus = AdjustmentQuoteLogStatuEnum.success; }
                }
                updateDetails.Add(item);
            }
            //总任务状态判断，重跑需要手动修改状态，并清空任务详细中的执行次数
            List<AdjustmentQuoteLogStatuEnum> Status = dto.details.Select(x => x.DetailStatus).Distinct().OrderBy(x => (int)x).ToList();
            if (tuples.All(x => x.Item2)) { dto.logInfo.AdjustmentQuoteLogStatus = Status.FirstOrDefault(); }
            else
            {
                //多个状态，取最小状态，且全部执行次数大于3 改失败
                if (dto.details.Where(x => x.DetailStatus == Status.FirstOrDefault()).Any(x => x.ExecuteCount > 3))
                {
                    dto.logInfo.AdjustmentQuoteLogStatus = AdjustmentQuoteLogStatuEnum.fail;
                    dto.logInfo.ResultMsg = "请查看详细任务，有失败过多任务，请人为干预！";
                }
            }
            //更新数据
            await _unitOfWork.UpdateAsync(dto.logInfo);
            //比逐条更新稍微快点
            string sql = " update Logistics_ExpressBulkAdjustmentQuoteLogDetail set DetailStatus = @DetailStatus,ExecuteCount=@ExecuteCount,FileUrl=@FileUrl,ResultMsg=@ResultMsg,ExpressSettlementAdjustmentId=@ExpressSettlementAdjustmentId,BaseId=@BaseId,IsDeleteFile=@IsDeleteFile  where Id = @Id ";
            await _unitOfWork.ExecuteAsync(sql, updateDetails);
        }
        #endregion
        #region 报价数据生成并写入
        public async Task<Tuple<bool, string>> GenerateQuotation()
        {
            var excuteData = await GetGenerateData(AdjustmentQuoteLogStatuEnum.draft);
            try
            {
                if (excuteData.logInfo == null || excuteData.details == null || excuteData.details.Count == 0) { return Tuple.Create<bool, string>(true, $"无此类型数据{AdjustmentQuoteLogStatuEnum.draft}"); }
                var verifyData = await VerifyAdjustmentQuote(excuteData.logInfo.Id, excuteData.logInfo.ExpressId, excuteData.logInfo.SalesmanUserId);
                if (!verifyData.Item1) { return verifyData; }
                //获取所有客户的运费调整方案
                List<CustomerQuotationData> dicQuotation = await GetQuotationDetails(excuteData.logInfo.ExpressId, excuteData.details);
                //批量处理生成数据，并写入表
                var HandleQuotationApprovedResult = await HandleQuotationApproved(excuteData.logInfo.ExpressId, excuteData.logInfo.EnableTime, dicQuotation);
                //更新数据变更，使用的逻辑
                excuteData.details.ForEach(x =>
                {
                    var tempQuotation = dicQuotation.Where(y => y.CustomerId == x.CustomerId).FirstOrDefault();
                    x.ExpressSettlementAdjustmentId = tempQuotation.AdjustmentId;
                    x.BaseId = tempQuotation.FeeBaseId;
                });
                //处理结果记录，任务状态更新
                await UpdateQuoteJob(excuteData, HandleQuotationApprovedResult);
            }
            catch (Exception ex)
            {
                return Tuple.Create<bool, string>(false, $"\r\n Id:{excuteData.logInfo.Id}请求结果异常，需要技术查看！" + ex.Message);
            }
            return Tuple.Create<bool, string>(true, "执行成功！");
        }
        public async Task<List<Tuple<int, bool, string>>> HandleQuotationApproved(int ExpressId, DateTime EnableTime, List<CustomerQuotationData> dicQuotation)
        {
            List<Tuple<int, bool, string>> tuples = new List<Tuple<int, bool, string>>();
            //List<int> feeBaseIds = new List<int>();
            var zones = await _feeBaseRepository.QueryZone(ExpressId);
            foreach (var item in dicQuotation)
            {
                if (item.quotations == null || item.quotations.Count == 0) { tuples.Add(Tuple.Create<int, bool, string>(item.CustomerId, false, "执行失败！未获取到数据")); continue; };
                List<Logistics_ExpressFeeRange> ranges = new List<Logistics_ExpressFeeRange>();
                foreach (var detail in item.quotations)
                {
                    foreach (var c in zones.Where(x => x.Zone == detail.操作费))
                    {
                        Logistics_ExpressFeeRange range = new Logistics_ExpressFeeRange
                        {
                            Countory = c.CountryCode,
                            Expressid = ExpressId,
                            BaseId = 0,
                            StartWeight = (int)(Convert.ToDecimal(detail.开始重量) * 1000),
                            EndWeight = (int)(Convert.ToDecimal(detail.结束重量) * 1000),
                            Fee = Convert.ToDecimal(detail.运费),
                            IsDelete = false,
                            CreateTime = DateTime.Now,
                            CreateUserName = "admin"
                        };
                        ranges.Add(range);
                    }
                }
                string versionName = EnableTime.ToString("yy-MM-dd");
                try
                {
                    var customerFeeBases = await CreateCustomerFeeBases(item.CustomerId, ExpressId, EnableTime, versionName);
                    long id = await AddNewFeeBase(customerFeeBases, null, ranges);
                    //feeBaseIds.Add((int)id);
                    item.FeeBaseId = (int)id;
                    tuples.Add(Tuple.Create<int, bool, string>(item.CustomerId, true, "执行成功！"));
                }
                catch (Exception ex)
                {
                    tuples.Add(Tuple.Create<int, bool, string>(item.CustomerId, false, "执行失败！" + ex.Message));
                }

            }
            return tuples;
        }
        private async Task<Logistics_ExpressFeeBase> CreateCustomerFeeBases(int customerId, int expressId, DateTime EnableTime, string versionName)
        {
            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 = AreaType.无,
                CalculateType = CalculateType.重量区间计费,
                Currency = "CNY",
                CustomerId = customerId,
                ExpressId = expressId,
                EnableTime = EnableTime,
                FileUrl = string.Empty,//为保证api速度，生成文件的逻辑移到mq中进行
                IsEnable = true,
                Versions = version,
                VersionsName = versionName,

            };
            return feeBase;
        }
        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 });

            }
            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;
        }
        #endregion
        #region  报价文件生成
        public async Task<Tuple<bool, string>> GenerateCustomerFile(int fileCount)
        {
            var excuteData = await GetGenerateData(AdjustmentQuoteLogStatuEnum.dataReady);
            try
            {
                if (excuteData.logInfo == null || excuteData.details == null || excuteData.details.Count == 0) { return Tuple.Create<bool, string>(true, $"无此类型数据{AdjustmentQuoteLogStatuEnum.dataReady}"); }
                var verifyData = await VerifyAdjustmentQuote(excuteData.logInfo.Id, excuteData.logInfo.ExpressId, excuteData.logInfo.SalesmanUserId);
                if (!verifyData.Item1) { return verifyData; }
                List<Tuple<int, bool, string>> result = new List<Tuple<int, bool, string>>();
                //循环调用生成文件数据
                foreach (var item in excuteData.details.Where(x => x.DetailStatus == AdjustmentQuoteLogStatuEnum.dataReady).Take(fileCount))
                {
                    //保证业务独立性，避开循环引用，改http请求调用接口
                    var response = await GetKDFeeRequest(excuteData.logInfo.BatchNo, excuteData.logInfo.SalesmanUserId, excuteData.logInfo.EnableTime, item);
                    if (response.Item2) { item.FileUrl = response.Item3; }
                    result.Add(response);
                }
                //处理结果记录，任务状态更新
                await UpdateQuoteJob(excuteData, result);
            }
            catch (Exception ex)
            {
                return Tuple.Create<bool, string>(false, $"\r\n Id:{excuteData.logInfo.Id}请求结果异常，需要技术查看！" + ex.Message);
            }
            return Tuple.Create<bool, string>(true, "执行成功！");
        }
        public async Task<Tuple<int, bool, string>> GetKDFeeRequest(string BatchNo, int SalesmanUserId, DateTime EnbleTime, Logistics_ExpressBulkAdjustmentQuoteLogDetail detail)
        {
            string excelName = $"行运易 For{detail.CompanyName} {EnbleTime.ToString("MM-dd")}生效";
            bool isChengDuQuotation = IsChengDuQuotation(SalesmanUserId);
            var client = new RestClient($"http://localhost:9521/api/FinanceQuotation/GetKDFee?customerId={detail.CustomerId}&isChengDuQuotation={isChengDuQuotation}");
            client.Timeout = -1;
            var request = new RestRequest(Method.GET);
            IRestResponse response = client.Execute(request);
            if (response.StatusCode == System.Net.HttpStatusCode.OK)
            {
                try
                {
                    //本地存储文件
                    var fileBytes = response.RawBytes;
                    string path = getTempFilePath(excelName, BatchNo, SalesmanUserId);
                    FileStream file = new FileStream(path, FileMode.Create);
                    file.Write(fileBytes, 0, fileBytes.Length);
                    file.Dispose();
                    return Tuple.Create<int, bool, string>(detail.CustomerId, true, path);
                }
                catch (Exception ex)
                {
                    return Tuple.Create<int, bool, string>(detail.CustomerId, false, "保存文件失败！" + ex.Message);
                }
            }
            else
            {
                return Tuple.Create<int, bool, string>(detail.CustomerId, false, "生成文件失败！" + response.Content);
            }
        }
        private string getTempFilePath(string name, string BatchNo, int SalesmanUserId)
        {
            string basePath = System.IO.Path.Combine(System.AppContext.BaseDirectory, "batchtemp", $"Job_{BatchNo}", $"SalesmanUserId_{SalesmanUserId}");
            if (!System.IO.Directory.Exists(basePath))
                System.IO.Directory.CreateDirectory(basePath);
            return System.IO.Path.Combine(basePath, name + ".xlsx");
        }
        private bool IsChengDuQuotation(int SalesmanUserId)
        {
            bool isChengDuQuotation = SalesmanUserId == 339;
            return isChengDuQuotation;
        }
        #endregion
        #region 报价文件合并，发送钉钉
        public async Task<Tuple<bool, string>> GenerateZipFile()
        {
            var excuteData = await GetGenerateData(AdjustmentQuoteLogStatuEnum.fileReady);
            try
            {
                if (excuteData.logInfo == null || excuteData.details == null || excuteData.details.Count == 0) { return Tuple.Create<bool, string>(true, $"无此类型数据{AdjustmentQuoteLogStatuEnum.dataReady}"); }
                var verifyData = await VerifyAdjustmentQuote(excuteData.logInfo.Id, excuteData.logInfo.ExpressId, excuteData.logInfo.SalesmanUserId);
                if (!verifyData.Item1) { return verifyData; }
                List<Tuple<int, bool, string>> result = new List<Tuple<int, bool, string>>();
                //压缩文件，全部成功或失败
                var zipResult = MergeFilesToZip(excuteData.details);
                if (zipResult.Item1)
                {
                    foreach (var zip in zipResult.Item2)
                    {
                        string mid = await _dingDingBaseService.UpdateFile(zip, "结算价调整自动生成客户报价.zip");
                        var u = await _dingDingBaseService.GetDingDingUserInfo(excuteData.logInfo.SalesmanUserId);//excuteData.logInfo.SalesmanUserId
                        string userId = u.UserId + ",575513315932546996";
                        var message = await _dingDingBaseService.SendMessage(userId, new
                        {
                            msgtype = "file",
                            file =
                             new
                             {
                                 media_id = mid
                             }
                        });
                        //发送合并文件后，删除文件
                        foreach (var item in excuteData.details)
                        {
                            if (System.IO.File.Exists(item.FileUrl))
                                System.IO.File.Delete(item.FileUrl);

                            result.Add(Tuple.Create<int, bool, string>(item.CustomerId, true, "执行成功"));
                            item.IsDeleteFile = true;
                        }
                        excuteData.logInfo.ZipFileUrl = string.Join(";", zipResult.Item2);
                    }

                }
                else
                {
                    foreach (var item in excuteData.details)
                    {
                        result.Add(Tuple.Create<int, bool, string>(item.CustomerId, false, "合并文件异常!" + zipResult.Item2));
                    }
                }
                //处理结果记录，任务状态更新
                await UpdateQuoteJob(excuteData, result);
            }
            catch (Exception ex)
            {
                return Tuple.Create<bool, string>(false, $"\r\n Id:{excuteData.logInfo.Id}请求结果异常，需要技术查看！" + ex.Message);
            }
            return Tuple.Create<bool, string>(true, "执行成功！");
        }
        private Tuple<bool, List<string>> MergeFilesToZip(List<Logistics_ExpressBulkAdjustmentQuoteLogDetail> details)
        {
            string excelPath = details.FirstOrDefault().FileUrl;
            string filePath = excelPath.Substring(0, excelPath.LastIndexOf(@"\"));
            string zipPath = System.IO.Path.Combine(filePath, $"结算价调整自动生成客户报价{DateTime.Now.ToString("yyyyMMddHHmmss")}.zip");
            List<string> result = new List<string>();
            try
            {
                if (!System.IO.Directory.Exists(filePath))
                    System.IO.Directory.CreateDirectory(filePath);

                result = XYY.Common.Standard.ZipHelper.ZipPath(filePath, zipPath, ".zip", 10 * 1024 * 1024);
            }
            catch (Exception ex)
            {
                return Tuple.Create<bool, List<string>>(false, new List<string>() { "压缩文件异常." + ex.Message });
            }
            return Tuple.Create<bool, List<string>>(true, result);
        }
        #endregion
    }
}
