using DingTalk.Api;
using DingTalk.Api.Request;
using DingTalk.Api.Response;
using Microsoft.Extensions.Caching.Distributed;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Data.Standard;
using XYY.Data.Standard.Channel;
using XYY.Data.Standard.Finance;
using XYY.Model.Standard.Finance;
using XYY.Model.Standard.Enums;
using Microsoft.Office.Interop.Excel;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;
using System.Reflection;
using XYY.Core.Standard.Data.Infrastructure;
using Dapper;
namespace XYY.Service.Standard.Finance
{
    public interface IQuotationService
    {
        Task<IEnumerable<View_Quotation>> QueryData(QueryModel queryModel);
        Task<IEnumerable<View_Quotation>> QueryChinaPostPublicData(int channelId);
        Task<IEnumerable<View_Quotation>> QueryViewExpressSettlement(QueryModel queryModel);
        Task<string> SendDingDing(int[] CustomerIds, int ExpressId, DateTime time, int userId, string remark, IEnumerable<View_Quotation> quotations, CspaceFile cspaceFile);
        List<View_Quotation> ImportExcelToJson(byte[] data);
        Task<IEnumerable<View_ChinaPostQuotationDiscount>> QueryChinaPostPublicDiscount(QueryModel queryMdoel);
        List<View_Quotation> InputExpressSettlement(byte[] data);
        string ConvertSettlementView(List<View_Quotation> quotations);
        List<View_Quotation> ImportExcelToChinaPostPublicJson(byte[] data, string sheetName);
        Task<List<CustomerDiscount>> ImportExcelToChinaPostDiscountDataJson(System.IO.Stream formFile);
        Task<string> SendDingDingByDiscount(DiscountDto dto, int userId);
        Task<IEnumerable<dynamic>> QueryExpressSettlement(QueryModel queryModel);
        Task<List<string>> GetExpressZones(int expressId);
        Task<Logistics_ExpressFeeBase> QueryExpressFeeBase(QueryModel queryModel);
    }

    public partial class QuotationService : IQuotationService
    {
        ILogistics_PublicRepository _publicRepository;
        IUserCustomerRepository _userCustomerRepository;
        IView_QuotationRepository _quotationRepository;
        ILogistics_ExpressFeeBaseRepository _feeBaseRepository;
        IFinance_QutationApproveRepository _approveRepository;
        IView_ChinaPostQuotationDiscountRepository _chinaPostQuotationDiscountRepository;
        IDingDingBaseService dingDingBaseService;
        IUnitOfWork _unitOfWork;

        public QuotationService(IView_QuotationRepository quotationRepository,
            IUserCustomerRepository userCustomerRepository,
            ILogistics_PublicRepository publicRepository,
            IUserInfoRepository userInfoRepository, IDistributedCache cache,
            ILogistics_ExpressFeeBaseRepository feeBaseRepository,
            IFinance_QutationApproveRepository approveRepository
, IDingDingBaseService dingDingBaseService, IView_ChinaPostQuotationDiscountRepository chinaPostQuotationDiscountRepository, IUnitOfWork unitOfWork)
        {
            _quotationRepository = quotationRepository;
            _userCustomerRepository = userCustomerRepository;
            _publicRepository = publicRepository;
            _feeBaseRepository = feeBaseRepository;
            _approveRepository = approveRepository;
            this.dingDingBaseService = dingDingBaseService;
            _chinaPostQuotationDiscountRepository = chinaPostQuotationDiscountRepository;
            _unitOfWork = unitOfWork;
        }

        private int getNumber(string str)
        {
            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"\d+");
            return int.Parse(regex.Match(str).Value);
        }

        public List<View_Quotation> ImportExcelToChinaPostPublicJson(byte[] data, string sheetName)
        {
            var excelHelper = new MSExcelHelper();
            string fileName = excelHelper.getTempFilePath();
            System.IO.File.WriteAllBytes(fileName, data);
            List<View_Quotation> quotations = new List<View_Quotation>();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度
                Workbooks wbs = app.Workbooks; //获取工作薄
                _Workbook wb = wbs.Add(fileName);
                Microsoft.Office.Interop.Excel.Range range;
                //取相关的Sheet

                Worksheet sh = null;
                for (int i = 1; i < wb.Sheets.Count + 1; i++)
                {
                    var s = (Worksheet)wb.Sheets[i];
                    if (s.Name == sheetName)
                    {
                        sh = s;
                    }
                }
                if (sh == null)
                {
                    throw new Exception("未找到工作薄" + sheetName);
                }
                //取到结尾
                range = sh.get_Range("A5", Missing.Value);
                var rowCount = excelHelper.GetRowCount(sh);
                var colCount = range.get_End(XlDirection.xlToRight).Column;
                if (rowCount > 0)
                {
                    int codeNumber = 2, ffNumber = 3, cfNumber = 4, fwNumber = 5, cwNumber = 6;

                    int rowStart = 1;
                    if (!sheetName.Contains("E特快"))
                    {
                        range = range.get_Resize(rowCount, colCount);
                        object[,] value22 = (object[,])range.Value2;
                        for (int i = rowStart; i < rowCount + 1; i++)
                        {
                            if (value22[i, 3] != null)
                            {

                                View_Quotation quotation = new View_Quotation
                                {
                                    //二字简码 = value22[i, 2].ToString(),
                                    操作费 = value22[i, 3].ToString(),
                                    运费 = value22[i, 4].ToString(),
                                    起重 = value22[i, 5].ToString(),
                                    限重 = value22[i, 6].ToString()
                                };
                                if (value22[i, 2] != null)
                                {
                                    quotation.二字简码 = value22[i, 2].ToString();
                                    quotation.国家 = value22[i, 1].ToString();
                                }
                                else if (quotations.Count > 0)
                                {
                                    quotation.二字简码 = quotations[quotations.Count - 1].二字简码;
                                    quotation.国家 = quotations[quotations.Count - 1].国家;
                                }
                                quotation.续重 = "1";
                                quotations.Add(quotation);
                            }
                        }
                    }
                    else
                    {
                        range = sh.get_Range("A4", Missing.Value);
                        rowCount = excelHelper.GetRowCount(sh);
                        colCount = 6;
                        range = range.get_Resize(rowCount, colCount);
                        object[,] value22 = (object[,])range.Value2;
                        for (int i = rowStart; i < rowCount + 1; i++)
                        {
                            if (value22[i, 2] != null)
                            {

                                View_Quotation quotation = new View_Quotation
                                {
                                    //二字简码 = value22[i, 2].ToString(),
                                    操作费 = value22[i, 4].ToString(),
                                    运费 = value22[i, 5].ToString(),
                                };
                                if (value22[i, 3] != null)
                                {
                                    quotation.二字简码 = value22[i, 3].ToString();
                                    quotation.国家 = value22[i, 2].ToString();
                                }
                                else if (quotations.Count > 0)
                                {
                                    quotation.二字简码 = quotations[quotations.Count - 1].二字简码;
                                    quotation.国家 = quotations[quotations.Count - 1].国家;
                                }
                                if (value22[i, 6] != null)
                                {
                                    string remark = value22[i, 6].ToString();
                                    System.Text.RegularExpressions.Regex regex
                                        = new System.Text.RegularExpressions.Regex(@"(\d\.?\d*)");
                                    if (regex.Match(remark).Success)
                                    {
                                        string start = regex.Matches(remark)[0].Value;
                                        string end = regex.Matches(remark)[1].Value;
                                        quotation.起重 = start == "0" ? "0.05" : start;
                                        quotation.限重 = end;
                                        quotation.续重 = "0.05";
                                    }

                                }
                                else
                                {
                                    quotation.起重 = "0.05";
                                    quotation.续重 = "0.05";
                                    quotation.限重 = "30";
                                }
                                quotations.Add(quotation);
                            }
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                app.Quit();
            }
            return quotations;
        }

        public string ConvertSettlementView(List<View_Quotation> quotations)
        {
            var weights = quotations.GroupBy(x => x.结束重量);
            //首行
            string lines = "[";
            foreach (var item in weights)
            {
                string itemLine = "{\"公斤/分区\":\"" + item.Key + "\"";
                foreach (var d in item)
                {
                    itemLine += ",\"" + d.操作费 + "\":\"" +Decimal.Round(Convert.ToDecimal(d.运费),3) + "\"";
                }
                itemLine += "},";
                lines += itemLine;
            }
            lines = lines.Trim(',');
            lines += "]";
            return lines;
        }

        public List<View_Quotation> InputExpressSettlement(byte[] data)
        {
            var excelHelper = new MSExcelHelper();
            string fileName = excelHelper.getTempFilePath();
            System.IO.File.WriteAllBytes(fileName, data);
            List<View_Quotation> quotations = new List<View_Quotation>();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度
                Workbooks wbs = app.Workbooks; //获取工作薄
                _Workbook wb = wbs.Add(fileName);
                Microsoft.Office.Interop.Excel.Range range;
                //仅取第一sheet
                Worksheet sh = (Worksheet)wb.Sheets[1];
                //取到结尾
                range = sh.get_Range("A1", Missing.Value);
                var rowCount = excelHelper.GetRowCount(sh);
                var colCount = range.get_End(XlDirection.xlToRight).Column;

                if (rowCount > 0)
                {
                    range = range.get_Resize(rowCount, colCount);
                    object[,] value22 = (object[,])range.Value2;
                    for (int i = 1; i <= rowCount; i++)
                    {
                        for (int j = 1; j <= colCount; j++)
                        {
                            if (i > 1 && j > 1)
                            {
                                View_Quotation quotation = new View_Quotation();
                                quotation.运费 = value22[i, j].ToString();
                                int start = 0;
                                int end = 0;
                                quotation.操作费 = value22[1, j].ToString();
                                if (i > 2)
                                {
                                    quotation.开始重量 = value22[i - 1, 1].ToString();
                                }
                                else
                                {
                                    quotation.开始重量 = "0";
                                }
                                quotation.结束重量 = value22[i, 1].ToString();
                                quotations.Add(quotation);
                            }
                        }
                    }
                }
                return quotations;
            }
            catch (Exception ex)
            {
                throw new Exception("解析快递文件失败" + ex.Message);
            }
            finally
            {
                app.Quit();
            }
        }

        public List<View_Quotation> ImportExcelToJson(byte[] data)
        {
            var excelHelper = new MSExcelHelper();
            string fileName = excelHelper.getTempFilePath();
            System.IO.File.WriteAllBytes(fileName, data);
            List<View_Quotation> quotations = new List<View_Quotation>();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度
                Workbooks wbs = app.Workbooks; //获取工作薄
                _Workbook wb = wbs.Add(fileName);
                Microsoft.Office.Interop.Excel.Range range;
                //仅取第一sheet
                Worksheet sh = (Worksheet)wb.Sheets[1];
                //取到结尾
                range = sh.get_Range("A1", Missing.Value);
                var rowCount = excelHelper.GetRowCount(sh);
                var colCount = range.get_End(XlDirection.xlToRight).Column;

                if (rowCount > 0)
                {
                    range = range.get_Resize(rowCount, colCount);
                    object[,] value22 = (object[,])range.Value2;
                    for (int i = 1; i <= rowCount; i++)
                    {
                        for (int j = 1; j <= colCount; j++)
                        {
                            if (i > 1 && j > 1)
                            {
                                View_Quotation quotation = new View_Quotation();
                                quotation.操作费 = value22[i, 1].ToString();
                                int start = 0;
                                int end = 0;
                                string strWeight = value22[1, j].ToString();
                                if (j != colCount)
                                {
                                    start = getNumber(strWeight.Split('-')[0]);
                                    end = getNumber(strWeight.Split('-')[1]);
                                }
                                else
                                {
                                    start = getNumber(strWeight);
                                    end = 99999;
                                }
                                quotation.开始重量 = start.ToString();
                                quotation.结束重量 = end.ToString();
                                quotation.运费 = value22[i, j].ToString();
                                quotations.Add(quotation);
                            }
                        }
                    }
                    return quotations;
                }
                else
                {
                    throw new Exception("表格不可为空");
                }
            }
            catch (Exception ex)
            {
                app.Quit();
                throw ex;
            }
        }

        public async Task<IEnumerable<View_Quotation>> QueryChinaPostPublicData(int channelId)
        {
            string sql = "select * from View_ChinaPostQuotation(nolock) where 1=1 and ExpressId = " + channelId + "  order by [跟单员],[客户],[线路名称],[二字简码],[起重]";
            return await _quotationRepository.QueryBySqlAsync(sql);
        }

        public async Task<IEnumerable<View_Quotation>> QueryViewExpressSettlement(QueryModel queryModel)
        {
            string sql = "";
            string where = GetESWhere(queryModel, out var expressId);
            if (expressId == 48)
            {
                sql = $@"if exists(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type = 'U')
                                           drop table #temp_fedex; 
                             with t1 as (
                                select ROW_NUMBER() over(partition by customerid, expressid order by enabletime desc) r,*
                                from Logistics_ExpressFeeBase(nolock)
                                where
                                 CustomerId > 0)
                                select  distinct  convert(decimal(18,1), StartWeight/1000.0) as 开始重量,convert(decimal(18,1), EndWeight/1000.0) as 结束重量,b.分区 as 操作费,convert(decimal(18,3),fee) as 运费
                                  into #temp_fedex
	                                                                                                        from 
													                                                            t1  join
		                                                                                                        Logistics_ExpressFeeRange(nolock) a on a.BaseId=t1.id
							                                                                                    join logistics_fedexzone(nolock) b on a.Countory=b.二字代码
	                                                                                                            where t1.r=1 {where}
                                                                                                                order by b.分区,convert(decimal(18,1), EndWeight/1000.0)
select *from #temp_fedex
                            ";
            }
            else if (expressId == 50)
            {
                sql = $@"if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                                           drop table #temp_fedex; 
                             with t1 as (
	                            select ROW_NUMBER() over( partition by customerid,expressid order by enabletime desc) r,* 
	                            from Logistics_ExpressFeeBase(nolock) where 
	                             CustomerId>0)

	                                                                    select  distinct  convert(decimal(18,1), StartWeight/1000.0) as 开始重量,convert(decimal(18,1), EndWeight/1000.0) as 结束重量,b.分区 as 操作费,convert(decimal(18,3),fee) as 运费
	                                                                        into #temp_fedex
	                                                                        from 
													                            t1  join
		                                                                        Logistics_ExpressFeeRange(nolock) a on a.BaseId=t1.id
							                                                    join Logistics_ExpressDHLZone(nolock) b on a.Countory=b.Code
	                                                                        where t1.r=1 {where}
	                                                                        order by b.分区,convert(decimal(18,1), EndWeight/1000.0)
select *from #temp_fedex    ";
            }
            else
            {
                throw new Exception("快递当前仅允许批量调整Fedex/DHL费用");
            }
            return (await _unitOfWork.QueryBySqlAsync<View_Quotation>(sql, 0, null)).ToList();
        }

        private string GetESWhere(QueryModel queryModel, out int expressId)
        {
            var isSeal = queryModel.QueryParamer.Any(x => x.Filed == "SealId" && x.Value == "True");

            int sealId = 0;
            if (isSeal)
            {
                var customerQuery = queryModel.QueryParamer.FirstOrDefault(x => x.Filed == "customerids");
                if (customerQuery != null)
                {
                    var cs =
                          _userCustomerRepository.QueryBySqlAsync($"select distinct  BranchCompanyId as Id from User_Customer(nolock) where Id in ({customerQuery.Value})").Result;
                    if (cs.Count() > 1)
                    {
                        throw new Exception("每次调整仅可针对单个分公司的客户");
                    }
                    else if (cs.Count() == 0)
                    {
                        throw new Exception($"未找到调整客户信息{customerQuery.Value}");
                    }
                    else
                    {
                        sealId = cs.First().Id;
                    }
                }
                else
                {
                    throw new Exception("必须选择调价客户");
                }
            }



            if (queryModel != null && queryModel.QueryParamer != null)
            {
                queryModel.QueryParamer = queryModel.QueryParamer.Where(x => x.Filed == "ExpressId" || x.Filed == "CustomerId").ToList();
            }
            var ExpressId = queryModel.QueryParamer.FirstOrDefault(x => x.Filed == "ExpressId");
            if (ExpressId != null)
            {
                ExpressId.Filed = "t1.ExpressId";
                ExpressId.Method = "Eq";
            }
            if (sealId != 0)
            {
                if (queryModel != null && queryModel.QueryParamer != null && queryModel.QueryParamer.Count > 0)
                {
                    var fildeCustomerId = queryModel.QueryParamer.FirstOrDefault(x => x.Filed == "CustomerId");
                    if (fildeCustomerId != null)
                    {
                        fildeCustomerId.Value = sealId.ToString();
                    }
                    else
                    {
                        queryModel.QueryParamer.Add(new QueryParamer
                        {
                            Filed = "CustomerId",
                            Method = "Eq",
                            Value = sealId.ToString()
                        });
                    }
                }
            }
            expressId = int.Parse(ExpressId.Value);
            return _quotationRepository.GetWhere(queryModel.QueryParamer);
        }

        public async Task<IEnumerable<dynamic>> QueryExpressSettlement(QueryModel queryModel)
        {

            //fedex
            string where = GetESWhere(queryModel, out var expressId);
            string sql = "";
            if (expressId == 48)
            {
                ///我们查找分公司的结算价
                sql = $@" if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                                           drop table #temp_fedex; 
                             with t1 as (
	                            select ROW_NUMBER() over( partition by customerid,expressid order by enabletime desc) r,* 
	                            from Logistics_ExpressFeeBase(nolock) where 
	                             CustomerId>0)

	                                                                    select  distinct  convert(decimal(18,1), EndWeight/1000.0) as [公斤/分区],b.分区,fee
	                                                                        into #temp_fedex
	                                                                        from 
													                            t1  join
		                                                                        Logistics_ExpressFeeRange(nolock) a on a.BaseId=t1.id
							                                                    join logistics_fedexzone(nolock) b on a.Countory=b.二字代码
	                                                                        where t1.r=1 {where}
	                                                                        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) ";
            }
            else if (expressId == 50)
            {
                sql = $@" if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_fedex') and type='U')
                                           drop table #temp_fedex; 
                             with t1 as (
	                            select ROW_NUMBER() over( partition by customerid,expressid order by enabletime desc) r,* 
	                            from Logistics_ExpressFeeBase(nolock) where 
	                             CustomerId>0)

	                                                                    select  distinct  convert(decimal(18,1), EndWeight/1000.0) as [公斤/分区],b.分区,fee
	                                                                        into #temp_fedex
	                                                                        from 
													                            t1  join
		                                                                        Logistics_ExpressFeeRange(nolock) a on a.BaseId=t1.id
							                                                    join Logistics_ExpressDHLZone(nolock) b on a.Countory=b.Code
	                                                                        where t1.r=1 {where}
	                                                                        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) ";

            }
            else
            {
                throw new Exception("快递当前仅允许批量调整Fedex/DHL费用");
            }

            return (await _unitOfWork.QueryBySqlAsync<dynamic>(sql, 0, null)).ToList();
        }
        /// <summary>
        /// 获取生效时间显示
        /// 存在版本问题，需在 QueryExpressSettlement 之后执行
        /// </summary>
        /// <param name="queryModel"></param>
        /// <returns></returns>
        public async Task<Logistics_ExpressFeeBase> QueryExpressFeeBase(QueryModel queryModel)
        {
            if (queryModel.QueryParamer != null&&queryModel.QueryParamer.Any(x => x.Filed == "CustomerId") && queryModel.QueryParamer.Any(x => x.Filed == "ExpressId"))
            {
                string CustomerId= queryModel.QueryParamer.Where(x => x.Filed == "CustomerId").FirstOrDefault().Value;
                string ExpressId = queryModel.QueryParamer.Where(x => x.Filed == "ExpressId").FirstOrDefault().Value;
                return (await _feeBaseRepository.QueryFeeBase(CustomerId,ExpressId)).FirstOrDefault();
            }
            return null;

        }

        public async Task<IEnumerable<View_Quotation>> QueryData(QueryModel queryModel)
        {
            string where = "";
            if (queryModel != null && queryModel.QueryParamer != null)
                where = _quotationRepository.GetWhere(queryModel.QueryParamer);

            string sql = "select * from View_Quotation(nolock) where 1=1 " + where + " order by [跟单员],[客户],[线路名称],len(重量段), 重量段,[操作费]";
            return await _quotationRepository.QueryBySqlAsync(sql);
        }
        public async Task<string> SendDingDing(int[] CustomerIds, int ExpressId, DateTime time,
            int userId, string remark, IEnumerable<View_Quotation> quotations, CspaceFile cspaceFile)
        {
            string token = await dingDingBaseService.GetTokenAsCache();

            bool isAnySettlement = false;
            //是否为结算客户
            if (new int[] { 387, 389, 390 }.Any(x => CustomerIds.Any(y => x == y)))
            {
                isAnySettlement = true;
            }
            if (isAnySettlement)
            {
                if ((time - DateTime.Now).TotalSeconds <= 12 * 60 * 60)
                {
                    //throw new Exception("结算价调整必须提前12小时申请");
                }
            }

            //1.每个客户每个渠道只能有一条待审批记录
            foreach (var customerId in CustomerIds)
            {
                string formattedId = $",{customerId},";
                var unapprovedRecords = await _approveRepository.QueryAsync(i => i.CustomerIds.Contains(formattedId) && i.ExpressId == ExpressId && i.Status == QutationApproveStatus.UnApproved);
                if (unapprovedRecords?.Any() == true)
                {
                    throw new Exception("该客户该渠道存在待审批的报价，请先处理");
                }
            }


            var existsFeeBases = await _feeBaseRepository.QueryAsync(f => f.IsEnable == true && f.ExpressId == ExpressId && f.EnableTime >= time && f.CustomerId.In(CustomerIds));
            if (existsFeeBases?.Any() == true)
            {
                var customerFeeGroups = existsFeeBases.GroupBy(f => f.CustomerId);
                foreach (var group in customerFeeGroups)
                {
                    if (group.Any(f => f.EnableTime.Date != time.Date))
                    {

                    }
                }
            }


            var channel = (await _publicRepository.QueryBySqlAsync("select b.Name from logistics_channel(nolock)a join logistics_public(nolock)b on a.PublicCode=B.Code where a.Id=" + ExpressId)).First();
            var customers = await _userCustomerRepository.QueryAsync(x => x.Id.In(CustomerIds));

            var userInfo = await dingDingBaseService.GetDingDingUserInfo(userId);

            IDingTalkClient client = new DefaultDingTalkClient("https://oapi.dingtalk.com/topapi/processinstance/create");
            OapiProcessinstanceCreateRequest req = new OapiProcessinstanceCreateRequest();
            req.AgentId = 1362321125L;
            req.ProcessCode = "PROC-02B98871-B1EF-4087-9875-2663C5564921";
            req.OriginatorUserId = userInfo.UserId;
            req.DeptId = userInfo.DepId;

            List<OapiProcessinstanceCreateRequest.FormComponentValueVoDomain> list2
                = new List<OapiProcessinstanceCreateRequest.FormComponentValueVoDomain>();
            OapiProcessinstanceCreateRequest.FormComponentValueVoDomain objCustomer
                = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
            list2.Add(objCustomer);
            objCustomer.Name = "客户";
            objCustomer.Value = string.Join(", ", customers.Select(x => x.CompanyName));

            OapiProcessinstanceCreateRequest.FormComponentValueVoDomain objExpress = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
            list2.Add(objExpress);
            objExpress.Name = "线路名称";
            objExpress.Value = channel.Name;

            OapiProcessinstanceCreateRequest.FormComponentValueVoDomain timeExpress = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
            list2.Add(timeExpress);
            timeExpress.Name = "生效时间";
            timeExpress.Value = time.ToString_yyyyMMdd();

            if (!string.IsNullOrEmpty(remark))
            {
                OapiProcessinstanceCreateRequest.FormComponentValueVoDomain remarkExpress = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
                list2.Add(remarkExpress);
                remarkExpress.Name = "备注说明";
                remarkExpress.Value = remark;
            }

            if (cspaceFile != null)
            {
                OapiProcessinstanceCreateRequest.FormComponentValueVoDomain attr = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
                attr.Name = "附件";
                var attrValues = new List<CspaceFile>
                {
                    cspaceFile
                };
                attr.Value = Newtonsoft.Json.JsonConvert.SerializeObject(attrValues);
                list2.Add(attr);
            }
            OapiProcessinstanceCreateRequest.FormComponentValueVoDomain detailExpress = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
            list2.Add(detailExpress);
            detailExpress.Name = "明细";
            detailExpress.Value = @"";
            // 写死  判断客户为 761,渠道为fedex和dhl 不显示明细
            List<int> channelJude = new List<int>() { 48, 50 };
            if ((!CustomerIds.Any(x => x == 761) && quotations != null) && (!channelJude.Contains(ExpressId)))
            {
                var datas = quotations.Select(x =>
                    new List<FeesDetails>
                    {
                    new FeesDetails { name = "重量段" , value= x.开始重量+"-"+x.结束重量},
                    new FeesDetails{ name = "运费/KG",value=x.运费},
                    new FeesDetails{ name="操作费/国家",value=x.操作费}
                    });
                List<List<FeesDetails>> feesDetails = datas.ToList();
                detailExpress.Value = Newtonsoft.Json.JsonConvert.SerializeObject(datas);
            }
            //上传钉盘
            req.FormComponentValues_ = list2;
            OapiProcessinstanceCreateResponse rsp = client.Execute(req, token);
            return rsp.ProcessInstanceId;
        }
        public async Task<string> SendDingDingByDiscount(DiscountDto dto, int userId)
        {
            string token = await dingDingBaseService.GetTokenAsCache();
            //1.每个客户每个渠道只能有一条待审批记录
            foreach (var item in dto.discountData)
            {
                string formattedId = $",{item.customerId},";
                var unapprovedRecords = await _approveRepository.QueryAsync(i => i.CustomerIds.Contains(formattedId) && i.ExpressId == dto.ExpressId && i.Status == QutationApproveStatus.UnApproved && i.UploadedFileUrl == "折扣");
                if (unapprovedRecords?.Any() == true)
                {
                    throw new Exception("该客户该渠道存在待审批的折扣，请先处理");
                }
            }

            var channel = (await _publicRepository.QueryBySqlAsync("select b.Name from logistics_channel(nolock)a join logistics_public(nolock)b on a.PublicCode=B.Code where a.Id=" + dto.ExpressId)).First();

            var userInfo = await dingDingBaseService.GetDingDingUserInfo(userId);

            IDingTalkClient client = new DefaultDingTalkClient("https://oapi.dingtalk.com/topapi/processinstance/create");
            OapiProcessinstanceCreateRequest req = new OapiProcessinstanceCreateRequest();
            req.AgentId = 1362321125L;
            req.ProcessCode = "PROC-F982729E-24E9-458B-ABB4-AB7B93685081";
            req.OriginatorUserId = userInfo.UserId;
            req.DeptId = userInfo.DepId;

            List<OapiProcessinstanceCreateRequest.FormComponentValueVoDomain> list2
                = new List<OapiProcessinstanceCreateRequest.FormComponentValueVoDomain>();
            OapiProcessinstanceCreateRequest.FormComponentValueVoDomain objCustomer
                = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();


            OapiProcessinstanceCreateRequest.FormComponentValueVoDomain objExpress = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
            list2.Add(objExpress);
            objExpress.Name = "线路名称";
            objExpress.Value = channel.Name;

            OapiProcessinstanceCreateRequest.FormComponentValueVoDomain timeExpress = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
            list2.Add(timeExpress);
            timeExpress.Name = "生效时间";
            timeExpress.Value = dto.setTime.ToString_yyyyMMdd();

            OapiProcessinstanceCreateRequest.FormComponentValueVoDomain detailExpress = new OapiProcessinstanceCreateRequest.FormComponentValueVoDomain();
            list2.Add(detailExpress);
            detailExpress.Name = "折扣详情";
            detailExpress.Value = @"";

            var datas = dto.discountData.Select(x =>
                new List<FeesDetails>
                {
                    new FeesDetails { name = "客户" , value= x.CompanyName},
                    new FeesDetails{ name = "折扣",value=x.Discount.ToString()},
                });
            List<List<FeesDetails>> feesDetails = datas.ToList();
            detailExpress.Value = Newtonsoft.Json.JsonConvert.SerializeObject(datas);

            //上传钉盘
            req.FormComponentValues_ = list2;
            OapiProcessinstanceCreateResponse rsp = client.Execute(req, token);
            return rsp.ProcessInstanceId;
        }

        public async Task<IEnumerable<View_ChinaPostQuotationDiscount>> QueryChinaPostPublicDiscount(QueryModel queryModel)
        {
            string where = "";
            if (queryModel != null && queryModel.QueryParamer != null)
                where = _quotationRepository.GetWhere(queryModel.QueryParamer);

            string sql = "select * from View_ChinaPostQuotationDiscount(nolock) where 1=1 " + where + " order by [客户],[生效时间]";
            return await _chinaPostQuotationDiscountRepository.QueryBySqlAsync(sql);
        }

        public async Task<List<CustomerDiscount>> ImportExcelToChinaPostDiscountDataJson(System.IO.Stream formFile)
        {
            MSExcelHelper excelHelper = new MSExcelHelper();
            var list = excelHelper.LoadDataAsStream<CustomerDiscountMpping, CustomerDiscount>(formFile);
            List<string> customerName = list.Select(x => x.CompanyName).Distinct().ToList();
            if (list.Count != customerName.Count) { throw new Exception("存在重复客户信息，请检查导入的文件！"); }
            //获取全部客户数据
            var customerInfo = await _userCustomerRepository.QueryAsync();
            List<string> errorName = new List<string>();
            foreach (var item in list)
            {
                var customer = customerInfo.Where(x => x.CompanyName == item.CompanyName)?.FirstOrDefault();
                if (customer == null) { errorName.Add(item.CompanyName); } else { item.customerId = customer.Id; }
            }
            if (errorName.Count > 0) { throw new Exception($"下列客户名称不正确：{string.Join(",", errorName)}"); }
            return list;
        }

        public async Task<List<string>> GetExpressZones(int expressId)
        {
            if (expressId == 48)
            {
                string sql = "select distinct 分区 from logistics_fedexzone(nolock)";
                return (await _unitOfWork.QueryBySqlAsync<string>(sql)).ToList();
            }
            else if (expressId == 50)
            {
                string sql = "select distinct 分区 from logistics_expressdhlzone(nolock)";
                return (await _unitOfWork.QueryBySqlAsync<string>(sql)).ToList();
            }
            else
            {
                throw new Exception($"找不到渠道Id{expressId}的分区");
            }
        }

        private class FeesDetails
        {
            public string name
            {
                get; set;
            }

            public string value
            {
                get; set;
            }
        }
    }
}