using Dapper;
using Nest;
using RestSharp;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Serialization;
using XYY.Common.Standard;
using XYY.Core.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;
using XYY.Core.Standard.Mvc;
using XYY.Data.Model.Standard.Tracking;
using XYY.Data.Model.Standard.Tracking.Dto;
using XYY.ServiceStandard.TrackingService.Model;

namespace XYY.Service.Standard.TrackingService
{
    public interface ITrackingService
    {
        Task<ApiJsonModel> Insert(List<PostTrackingRequest> postTrackingRequest);
        Task<List<TrackingDto>> Query(List<string> orderNumbers);

        Task<ApiJsonModel> AsyncUpdateStatus(Dictionary<string, string> message, string auth);

        Task<ApiJsonModel> LogHandError(List<Tracking_HandErrorLog> logs);
        Task<List<Logistics_Trace_SupplementLog>> AddTrackingTrajectory(List<Logistics_Trace_SupplementLog> input);
        Task<ApiJsonModel> AddTrackingTrajectoryErrors(List<Logistics_Trace_SupplementLog> logs);
        /// <summary>
        /// 获取待补充节点
        /// 出于性能考虑，我们每次仅返回1000条左右的记录
        /// </summary>
        /// <returns></returns>
        Task<List<Logistics_Trace_SupplementLog>> GetWaitSupplementTracking();
        Task deleteEs(string TrackingNumber);
        Task SupplementaryFailureTrajectory(DateTime date);
        ApiJsonModel Input(System.IO.Stream stream);

    }

    public class TrackingService : ITrackingService
    {
        private ElasticClient client;
        private ElasticClient _supplementClient;
        private readonly IUnitOfWork _unitOfWork;

        public TrackingService(TrackingES es, IUnitOfWork unitOfWork)
        {
            client = es.GetElasticClient();
            _supplementClient = es.GetSupplementElasticClient();
            _unitOfWork = unitOfWork;
        }


        public async Task<List<TrackingDto>> Query(List<string> orderNumbers)
        {
            string api = "http://192.168.3.6/api/tracking/query";
            string orderNumbersStr = string.Join(",", orderNumbers);
            RestClient restClient = new RestClient();
            RestRequest request = new RestRequest(api, Method.GET);
            request.AddParameter("numbers", orderNumbersStr);
            var result = await restClient.ExecuteAsync<List<TrackingDto>>(request);
            return result.Data;
        }

        public async Task<ApiJsonModel> Insert(List<PostTrackingRequest> postTrackingRequest)
        {
            List<Logistics_TraceGroup> groups =
                postTrackingRequest.Select(x => new Logistics_TraceGroup
                {
                    TrackingNumber = x.TrackingNumber,
                    List = x.List.Select(y =>
                    new EsLogisticsTrackingLog
                    {
                        Status = 5,
                        StatusString = y.Event,
                        TriggerTime = y.TragetTime,
                        Location = y.Location,
                        TrackingNumber = x.TrackingNumber
                    }).ToList()
                }
                ).ToList();
            groups.ForEach(x =>
            {
                if (x.List.Count > 0)
                {
                    x.List.First().Status = NodeHelper.ConvertIntStatus(postTrackingRequest.First(y => y.TrackingNumber == x.TrackingNumber)?.Status);
                }
            });
            var data = await client.IndexManyAsync(groups);

            if (data.Errors)
            {
                return new ApiJsonModel
                {
                    success = false,
                    message = "error",
                    error = Newtonsoft.Json.JsonConvert.SerializeObject(data.ItemsWithErrors.Select(x => new { TrackingNumber = x.Id, Error = x.Error.ToString() }))
                };
            }
            else
            {
                var dbResult = await AsyncDB(postTrackingRequest);
                if (dbResult.success)
                    return ApiJsonModel.OK();
                else
                    return dbResult;
            }
        }



        /// <summary>
        /// 同步状态
        /// </summary>
        /// <param name="message">
        /// Key 同步单号
        /// Message 同步错误内容
        /// </param>
        /// <returns></returns>
        public async Task<ApiJsonModel> AsyncUpdateStatus(Dictionary<string, string> message, string auth)
        {

            try
            {
                if (_unitOfWork.Connection != null && _unitOfWork.Connection.State != System.Data.ConnectionState.Open)
                    _unitOfWork.Connection.Open();

                var list = message.Select(x => new
                {
                    IsUpdate = true,
                    TrackingNumber = x.Key,
                    LastMessage = x.Value,
                    Auth = auth
                });

                string tableName = "Logistics_Trace";
                if (list.Count() > 0)
                {
                    var frist = list.First();
                    string columns = DataXmlUpdateHelper.GetColumns(frist);
                    string sets = DataXmlUpdateHelper.Sets(frist);
                    string body = string.Join("\r\n", list.ToList().Select(x => DataXmlUpdateHelper.XmlValues(x)));
                    string sql = $@"DECLARE @idoc int
                                            DECLARE @doc varchar(max)
                                            SET @doc ='
                                            <?xml version=""1.0"" encoding=""gb2312"" ?>
                                            <ROOT>
                                               {body}
                                            </ROOT>' 
                                            EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
                                            SELECT * into #temp_searchs
                                            FROM   OPENXML (@idoc, '/ROOT/item',2)
                                                     WITH ({columns})
                                            update a set {sets} from {tableName}(nolock)a join #temp_searchs b on a.TrackingNumber = b.TrackingNumber ";
                    try
                    {
                        await _unitOfWork.ExecuteAsync(sql);
                    }
                    catch (Exception ex)
                    {
                        return ApiJsonModel.Error("数据保存失败" + ex.Message);
                    }
                    finally
                    {
                        if (_unitOfWork.Connection != null)
                            _unitOfWork.Connection.Close();
                    }
                }

                return ApiJsonModel.OK();
            }
            catch (Exception ex)
            {
                return ApiJsonModel.Error("数据库保存出错" + ex.Message);
            }
        }
        private async Task<ApiJsonModel> AsyncDB(List<PostTrackingRequest> requests)
        {
            try
            {
                if (_unitOfWork.Connection != null)
                    _unitOfWork.Connection.Open();
                var list = requests.Select(x => new
                {
                    FailCNMessage = x.FailCNMessage,
                    FailMessage = x.FailMessage,
                    FailTime = x.FailTime,
                    LastGetTime = x.LastGetTime ?? DateTime.Now,
                    Delivered = x.Delivered,
                    LastMessage = x.LastMessage,
                    OnlineDay = x.OnlineDay,
                    OnlineTime = x.OnlineTime,
                    PreInternetAccessDay = x.PreInternetAccessDay,
                    PreInternetAccessTime = x.PreInternetAccessTime,
                    SendFialEmail = x.SendFialEmail,
                    SignDay = x.SignDay,
                    SignTime = x.SignTime,
                    Status = x.Status,
                    StrStatus = x.StrStatus,
                    TargetTime = x.TargetTime,
                    TrackingNumber = x.TrackingNumber,
                    DeliveryTime = x.DeliveryTime
                });
                string tableName = "Logistics_Trace";
                if (list.Count() > 0)
                {
                    var frist = list.First();
                    string columns = DataXmlUpdateHelper.GetColumns(frist);
                    string sets = DataXmlUpdateHelper.Sets(frist);
                    string body = string.Join("\r\n", list.ToList().Select(x => DataXmlUpdateHelper.XmlValues(x)));
                    string sql = $@"DECLARE @idoc int
                                            DECLARE @doc varchar(max)
                                            SET @doc ='
                                            <?xml version=""1.0"" encoding=""gb2312"" ?>
                                            <ROOT>
                                            {body}
                                            </ROOT>' 
                                            EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
                                            SELECT * into #temp_searchs
                                            FROM   OPENXML (@idoc, '/ROOT/item',2)
                                                     WITH ({columns})
                                            update a set {sets} from {tableName}(nolock)a join #temp_searchs b on a.TrackingNumber = b.TrackingNumber ";
                    try
                    {
                        await _unitOfWork.ExecuteAsync(sql);
                    }
                    catch (Exception ex)
                    {
                        return ApiJsonModel.Error("数据保存失败" + ex.Message);
                    }
                    finally
                    {
                        if (_unitOfWork.Connection != null)
                            _unitOfWork.Connection.Close();
                    }
                }
                return ApiJsonModel.OK();
            }
            catch (Exception ex)
            {
                return ApiJsonModel.Error("数据库保存出错" + ex.Message);
            }
        }

        /// <summary>
        /// 记录错误日志
        /// </summary>
        /// <param name="logs"></param>
        /// <returns></returns>
        public async Task<ApiJsonModel> LogHandError(List<Tracking_HandErrorLog> logs)
        {
            await _unitOfWork.BulkToDBAsync(logs);
            return ApiJsonModel.OK();
        }
        #region 补充轨迹
        public async Task<List<Logistics_Trace_SupplementLog>> AddTrackingTrajectory(List<Logistics_Trace_SupplementLog> input)
        {
            if (input == null || input.Count <= 0) { return input; }
            string columns = DataXmlUpdateHelper.GetColumns(input.First());
            string body = string.Join("\r\n", input.ToList().Select(x => DataXmlUpdateHelper.XmlValues(x)));
            string sql = $@"DECLARE @idoc int
                                            DECLARE @doc varchar(max)
                                            SET @doc ='
                                            <?xml version=""1.0"" encoding=""gb2312"" ?>
                                            <ROOT>
                                               {body}
                                            </ROOT>' 
                                            EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
                                            SELECT * into #temp_searchs
                                            FROM   OPENXML (@idoc, '/ROOT/item',2)
                                            WITH ({columns})
                                            select a.TrackingNumber,a.Id,a.ReceiverCountryCode,a.CustomerOrderNo,a.CustomerId from Order_Order(nolock) a right join #temp_searchs b on a.TrackingNumber = b.TrackingNumber ";
            try
            {
                IEnumerable<dynamic> firstTrackings = await _unitOfWork.QueryBySqlAsync<dynamic>(sql);
                List<Logistics_TraceGroup> datas = new List<Logistics_TraceGroup>();
                foreach (var item in input)
                {
                    var temp = firstTrackings.Where(x => x.TrackingNumber == item.TrackingNumber)?.FirstOrDefault();
                    if (temp == null) { continue; }
                    var log = GetLog(temp, item.TrackSupplementText, item.TrackSupplementTime, item.TrackSupplementLocation == TrackSupplementLocation.国内.GetValue() ? temp.CustomerId == 698 ? "CN 广州操作中心" : "china" : temp.ReceiverCountryCode);//698特殊处理
                    datas.Add(log);
                }
                return await InsertAndUpdate(datas, input);
            }
            catch (Exception ex)
            {
                Console.WriteLine(sql);
                throw ex;
            }
        }
        private Logistics_TraceGroup GetLog(dynamic order, string StatusString, DateTime trajectoryTime, string locaiton)
        {
            var g = new Logistics_TraceGroup
            {
                TrackingNumber = order.TrackingNumber,
                CustomerOrderNo = order.CustomerOrderNo,
                List = new List<EsLogisticsTrackingLog>()
            };

            g.List.Add(new EsLogisticsTrackingLog
            {
                CreateTime = DateTime.Now,
                TriggerTime = trajectoryTime,
                Status = 5,
                StatusString = StatusString,
                Location = locaiton ?? order.ReceiverCountryCode,
                OrderId = order.Id,
                ChannelId = 0,
                TrackingNumber = order.TrackingNumber,
                Uid = Guid.NewGuid().ToString()
            });
            return g;
        }
        private async Task<List<Logistics_Trace_SupplementLog>> InsertAndUpdate(List<Logistics_TraceGroup> UpdateTraceGroups2, List<Logistics_Trace_SupplementLog> input)
        {
            var batchQty = 1000.0;
            for (int i = 0; i < Math.Ceiling(UpdateTraceGroups2.Count / batchQty); i++)
            {
                var UpdateTraceGroups = UpdateTraceGroups2.Skip(i * (int)batchQty).Take((int)batchQty).ToList();
                List<Logistics_TraceGroup> updateList = new List<Logistics_TraceGroup>();
                List<Logistics_TraceGroup> insertList = new List<Logistics_TraceGroup>();
                foreach (var item in UpdateTraceGroups)
                {
                    var response = _supplementClient.DocumentExists<Logistics_TraceGroup>(new DocumentPath<Logistics_TraceGroup>(new Id(item.TrackingNumber)));
                    if (response.Exists)
                    {
                        var response2 = await _supplementClient.GetAsync<Logistics_TraceGroup>(item.TrackingNumber);
                        if (response2.Source != null && response2.Source.List != null)
                        {
                            for (int j = item.List.Count - 1; j >= 0; j--)
                            {
                                if (response2.Source.List.Any(x => x.TriggerTime == item.List[j].TriggerTime && x.StatusString == item.List[j].StatusString))
                                {
                                    item.List.RemoveAt(j);
                                }
                            }
                            item.List.AddRange(response2.Source.List);
                        }
                        updateList.Add(item);
                    }
                    else
                    {
                        insertList.Add(item);
                    }
                }
                var tempArry = insertList.Concat(updateList).Select(x => x.TrackingNumber).Distinct().ToList();
                try
                {
                    var descriptor = new BulkDescriptor();
                    descriptor.CreateMany(insertList);
                    descriptor.UpdateMany(updateList, (x, y) => new BulkUpdateOperation<Logistics_TraceGroup, Logistics_TraceGroup>(y, y));
                    var result = _supplementClient.Bulk(descriptor);
                    if (result.Errors) { throw new Exception(); }
                    input.ForEach(x =>
                    {
                        if (tempArry.Contains(x.TrackingNumber)) { x.IsSuccess = true; }
                    });
                }
                catch (Exception ex)
                {
                    input.ForEach(x =>
                    {
                        if (tempArry.Contains(x.TrackingNumber)) { x.IsSuccess = false; x.ErrorMsg = ex.Message; }
                    });
                }
            }
            return input;
        }
        public async Task deleteEs(string TrackingNumber)
        {
            string sql = "select TrackingNumber from Logistics_Trace_SupplementLog where TrackingNumber=" + TrackingNumber;
            var list = await _unitOfWork.QueryBySqlAsync<string>(sql);
            var bulkQuest = new BulkRequest() { Operations = new List<IBulkOperation>() };
            foreach (var item in list)
            {
                bulkQuest.Operations.Add(new BulkDeleteOperation<Logistics_TraceGroup>(item));
                //var result = await _supplementClient.DeleteAsync<Logistics_TraceGroup>(item);
            }
            var result = client.Bulk(bulkQuest);
        }
        public async Task SupplementaryFailureTrajectory(DateTime date)
        {
            string sql = @$"with temp1 as(
select distinct CreateTime from Logistics_Trace_SupplementLog nolock where CreateTime>='{date}' and CreateTime<'{date.AddDays(1)}' 
),temp2 as(
select b.* from temp1
outer apply (select top 1* from Logistics_Trace_SupplementLog nolock where CreateTime=temp1.CreateTime) b
)
select *from temp2";
            var dbData = await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql);
            List<DateTime> times = new List<DateTime>();
            foreach (var item in dbData)
            {
                var response2 = await _supplementClient.GetAsync<Logistics_TraceGroup>(item.TrackingNumber);
                if (response2.Found && response2.Source != null && response2.Source.List != null)
                {

                    if (response2.Source.List.All(x => x.TriggerTime != item.TrackSupplementTime && x.StatusString != item.TrackSupplementText))
                    {
                        if (item.CreateTime.HasValue) { times.Add(item.CreateTime ?? DateTime.Now); }
                    }
                }
            }
            string sql2 = @"select a.* from Logistics_Trace_SupplementLog a
left join Order_Order b on b.TrackingNumber=a.TrackingNumber
where a.CreateTime in @times and b.LogisticsStatus!=7 and a.IsSuccess=1 and a.IsCover=0";
            var data2 = await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql2, null, new { times = times });
            List<Logistics_Trace_SupplementLog> logs = new List<Logistics_Trace_SupplementLog>();
            foreach (var item in data2)
            {
                logs.Add(new Logistics_Trace_SupplementLog()
                {
                    TrackingNumber = item.TrackingNumber,
                    JudgmentNode = item.JudgmentNode,
                    TrackSupplementTime = item.TrackSupplementTime,
                    TrackSupplementText = item.TrackSupplementText,
                    TrackSupplementLocation = item.TrackSupplementLocation,
                });
            }
            await AddTrackingTrajectory(logs);
        }

        public async Task<ApiJsonModel> AddTrackingTrajectoryErrors(List<Logistics_Trace_SupplementLog> logs)
        {
            await _unitOfWork.BulkToDBAsync(logs);
            return ApiJsonModel.OK();
        }

        public async Task<List<Logistics_Trace_SupplementLog>> GetWaitSupplementTracking()
        {
            List<Logistics_Trace_SupplementLog> results = new List<Logistics_Trace_SupplementLog>();
            //称重节点数据              
            string sql =
                $@" with t1 as(
                        select dateadd(hour,b.TrackSupplementHour, ReceiveTime) TrackSupplementTime,JudgmentNode,TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a                             
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.ChannelId=b.SupplementTarge and b.SupplementType = 3
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=1
                            join User_Customer(nolock)c on a.CustomerId =c.Id and c.NotSupplementTracking =0
	                        where ReceiveTime is not null and SendOutTime is null and  ReceiveTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and a.ReceiveTime>'2023-01-01'
                        union
                        select dateadd(hour,b.TrackSupplementHour, ReceiveTime) TrackSupplementTime,JudgmentNode,TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.CreateOrderChannelId=b.SupplementTarge and b.SupplementType = 2
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=1
                            join User_Customer(nolock)c on a.CustomerId =c.Id and c.NotSupplementTracking =0
	                        where ReceiveTime is not null and SendOutTime is null and  ReceiveTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=1
	                        )
	                        and a.ReceiveTime>'2023-12-01'
                        union 
                        select dateadd(hour,b.TrackSupplementHour, ReceiveTime) TrackSupplementTime,JudgmentNode,TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on b.SupplementType = 1 and JudgmentNode=1
	                        and b.IsDelete=0 and b.IsEnable=1 	
                            join User_Customer(nolock)c on a.CustomerId =c.Id and c.NotSupplementTracking =0
	                        where ReceiveTime is not null and SendOutTime is null and  ReceiveTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=1
	                        )	
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)b where b.SupplementType=2 and a.CreateOrderChannelId = b.SupplementTarge
		                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=1
	                        )
	                        and a.ReceiveTime>'2023-12-01' )
                        select * from t1 where not exists(
	                        select * from Logistics_Trace_SupplementLog(Nolock)b where t1.TrackingNumber = b.TrackingNumber and t1.JudgmentNode = b.JudgmentNode 
                        )";
            results.AddRange(await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql, commandTimeout: 120));
            if (results.Count > 1000)
                return results.Take(1000).ToList();
            //装箱节点数据
            sql = @"with t1 as(
                        select dateadd(hour,b.TrackSupplementHour, d.DeliveryTime) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 							
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.ChannelId=b.SupplementTarge and b.SupplementType = 3				
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=2
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId
                            join User_Customer(nolock)e on a.CustomerId =e.Id and e.NotSupplementTracking =0
	                        where d.DeliveryTime is not null and d.LadingBillNumber is null and  DeliveryTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and a.ReceiveTime>'2023-12-01'
                        union
                        select dateadd(hour,b.TrackSupplementHour,  d.DeliveryTime) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.CreateOrderChannelId=b.SupplementTarge and b.SupplementType = 2
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=2
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId  		
                            join User_Customer(nolock)e on a.CustomerId =e.Id and e.NotSupplementTracking =0	
	                        where DeliveryTime is not null and d.LadingBillNumber is null and  DeliveryTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=2
	                        )
	                        and a.ReceiveTime>'2023-12-01'
                        union 
                        select dateadd(hour,b.TrackSupplementHour,  d.DeliveryTime) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on b.SupplementType = 1 and JudgmentNode=2
	                        and b.IsDelete=0 and b.IsEnable=1 	
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId  
                            join User_Customer(nolock)e on a.CustomerId =e.Id and e.NotSupplementTracking =0
	                        where DeliveryTime is not null and d.LadingBillNumber is null and  DeliveryTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=2
	                        )	
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)b where b.SupplementType=2 and a.CreateOrderChannelId = b.SupplementTarge
		                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=2
	                        )
	                        and a.ReceiveTime>'2023-12-01')
                        select * from t1 where not exists(
	                        select * from Logistics_Trace_SupplementLog(Nolock)b where t1.TrackingNumber = b.TrackingNumber and t1.JudgmentNode = b.JudgmentNode 
                        )";
            results.AddRange(await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql, commandTimeout: 120));
            if (results.Count > 1000)
                return results.Take(1000).ToList();

            //国内转运
            sql = @"  with t1 as(
                            select dateadd(hour,b.TrackSupplementHour,  e.CreateTime) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 							
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.ChannelId=b.SupplementTarge and b.SupplementType = 3				
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=3
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId 	and d.LadingBillNumber is not null				
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where e.CreateTime is not null and e.DepartTime is null and  e.CreateTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and a.ReceiveTime>'2023-01-01' and a.CustomerId<>294
                        union
                        select dateadd(hour,b.TrackSupplementHour,  e.CreateTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.CreateOrderChannelId=b.SupplementTarge and b.SupplementType = 2
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=3
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId and d.LadingBillNumber is not null
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where  e.CreateTime  is not null and e.DepartTime is null and  e.CreateTime < DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=3
	                        )
	                        and a.ReceiveTime>'2023-01-01' and a.CustomerId<>294
                        union 
                        select dateadd(hour,b.TrackSupplementHour,  e.CreateTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on b.SupplementType = 1 and JudgmentNode=3
	                        and b.IsDelete=0 and b.IsEnable=1 	
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId   	and d.LadingBillNumber is not null
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where  e.CreateTime  is not null and e.DepartTime is null and  e.CreateTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=3
	                        )	
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)b where b.SupplementType=2 and a.CreateOrderChannelId = b.SupplementTarge
		                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=3
	                        )
	                        and a.ReceiveTime>'2023-01-01'  and a.CustomerId<>294

                        )
                        select * from t1 where not exists(
	                        select * from Logistics_Trace_SupplementLog(Nolock)b where t1.TrackingNumber = b.TrackingNumber and t1.JudgmentNode = b.JudgmentNode 
                        )";
            results.AddRange(await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql, commandTimeout: 120));
            if (results.Count > 1000)
                return results.Take(1000).ToList();

            //离港时间
            sql = @" with t1 as(
                        select dateadd(hour,b.TrackSupplementHour,  e.DepartTime) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 							
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.ChannelId=b.SupplementTarge and b.SupplementType = 3				
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=4
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId 	and d.LadingBillNumber is not null				
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where e.DepartTime is not null and e.ArrivalTime is null and  e.DepartTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and a.ReceiveTime>'2023-01-01'
                        union
                        select dateadd(hour,b.TrackSupplementHour,  e.DepartTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.CreateOrderChannelId=b.SupplementTarge and b.SupplementType = 2
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=4
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId and d.LadingBillNumber is not null
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where  e.DepartTime  is not null and e.ArrivalTime is null and  e.DepartTime < DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=4
	                        )
	                        and a.ReceiveTime>'2023-01-01'
                        union 
                        select dateadd(hour,b.TrackSupplementHour,  e.DepartTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on b.SupplementType = 1 and JudgmentNode=4
	                        and b.IsDelete=0 and b.IsEnable=1 	
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId   	and d.LadingBillNumber is not null
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where  e.DepartTime  is not null and e.ArrivalTime is null and  e.DepartTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=4
	                        )	
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)b where b.SupplementType=2 and a.CreateOrderChannelId = b.SupplementTarge
		                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=4
	                        )
	                        and a.ReceiveTime>'2023-01-01' 
                        )
                        select * from t1 where not exists(
	                        select * from Logistics_Trace_SupplementLog(Nolock)b where t1.TrackingNumber = b.TrackingNumber and t1.JudgmentNode = b.JudgmentNode 
                        )";
            results.AddRange(await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql, commandTimeout: 120));
            if (results.Count > 1000)
                return results.Take(1000).ToList();

            //到港时间
            sql = @" with t1 as(
                        select dateadd(hour,b.TrackSupplementHour,  e.ArrivalTime) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 							
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.ChannelId=b.SupplementTarge and b.SupplementType = 3				
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=5
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId 	and d.LadingBillNumber is not null				
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where e.ArrivalTime is not null and e.ClearanceTime is null and  e.ArrivalTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and a.ReceiveTime>'2023-01-01'
                        union
                        select dateadd(hour,b.TrackSupplementHour,  e.ArrivalTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.CreateOrderChannelId=b.SupplementTarge and b.SupplementType = 2
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=5
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId and d.LadingBillNumber is not null
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where  e.ArrivalTime  is not null and e.ClearanceTime is null and  e.ArrivalTime < DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=5
	                        )
	                        and a.ReceiveTime>'2023-01-01'
                        union 
                        select dateadd(hour,b.TrackSupplementHour,  e.ArrivalTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on b.SupplementType = 1 and JudgmentNode=5
	                        and b.IsDelete=0 and b.IsEnable=1 	
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId   	and d.LadingBillNumber is not null
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
                            join User_Customer(nolock)f on a.CustomerId =f.Id and f.NotSupplementTracking =0
	                        where  e.ArrivalTime  is not null and e.ClearanceTime is null and  e.ArrivalTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=5
	                        )	
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)b where b.SupplementType=2 and a.CreateOrderChannelId = b.SupplementTarge
		                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=5
	                        )
	                        and a.ReceiveTime>'2023-01-01'  
                        )
                        select * from t1 where not exists(
	                        select * from Logistics_Trace_SupplementLog(Nolock)b where t1.TrackingNumber = b.TrackingNumber and t1.JudgmentNode = b.JudgmentNode 
                        )";
            results.AddRange(await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql, commandTimeout: 120));
            if (results.Count > 1000)
                return results.Take(1000).ToList();

            //清关时间
            sql = @"  with t1 as(
                        select dateadd(hour,b.TrackSupplementHour,  e.ClearanceTime) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 							
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.ChannelId=b.SupplementTarge and b.SupplementType = 3				
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=6
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId 	and d.LadingBillNumber is not null				
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
							 left join Logistics_Trace(nolock)f on f.TrackingNumber=a.TrackingNumber
                            join User_Customer(nolock)g on a.CustomerId =g.Id and g.NotSupplementTracking =0
	                        where e.ClearanceTime is not null and f.OnlineTime is null and  e.ClearanceTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and a.ReceiveTime>'2023-01-01' and f.Status<>'delivered'
                        union
                        select dateadd(hour,b.TrackSupplementHour,  e.ClearanceTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.CreateOrderChannelId=b.SupplementTarge and b.SupplementType = 2
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=6
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId and d.LadingBillNumber is not null
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
							 left join Logistics_Trace(nolock)f on f.TrackingNumber=a.TrackingNumber
                            join User_Customer(nolock)g on a.CustomerId =g.Id and g.NotSupplementTracking =0
	                        where  e.ClearanceTime  is not null and f.OnlineTime is null and  e.ClearanceTime < DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=6
	                        )
	                        and a.ReceiveTime>'2023-01-01'
                             and f.Status<>'delivered'
                        union 
                        select dateadd(hour,b.TrackSupplementHour,  e.ClearanceTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on b.SupplementType = 1 and JudgmentNode=6
	                        and b.IsDelete=0 and b.IsEnable=1 	
							join Logistics_BoxDetail(nolock)c on c.OrderId=a.Id
							 join  Logistics_Box(nolock)d on d.id=c.BoxId   	and d.LadingBillNumber is not null
							 join Logistics_LadingBill(nolock)e on e.LadingBillNumber = d.LadingBillNumber and e.IsDeleted = 0
							 left join Logistics_Trace(nolock)f on f.TrackingNumber=a.TrackingNumber
                            join User_Customer(nolock)g on a.CustomerId =g.Id and g.NotSupplementTracking =0
	                        where  e.ClearanceTime  is not null and f.OnlineTime is null and  e.ClearanceTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=6
	                        )	
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)b where b.SupplementType=2 and a.CreateOrderChannelId = b.SupplementTarge
		                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=6
	                        )
	                        and a.ReceiveTime>'2023-01-01' 
                            and f.Status<>'delivered'
                        )
                        select * from t1 where not exists(
	                        select * from Logistics_Trace_SupplementLog(Nolock)b where t1.TrackingNumber = b.TrackingNumber and t1.JudgmentNode = b.JudgmentNode 
                        )";
            results.AddRange(await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql, commandTimeout: 120));
            if (results.Count > 1000)
                return results.Take(1000).ToList();

            //上网时间
            sql = @" 
                         with t1 as(
                            select dateadd(hour,b.TrackSupplementHour,  f.OnlineTime) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 							
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.ChannelId=b.SupplementTarge and b.SupplementType = 3				
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=7
					 
							 left join Logistics_Trace(nolock)f on f.TrackingNumber=a.TrackingNumber
	                        where f.OnlineTime is not null and  f.TargetTime<=f.OnlineTime and  f.OnlineTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and a.ReceiveTime>'2023-01-01'
                        union
                        select dateadd(hour,b.TrackSupplementHour,  f.OnlineTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on a.CreateOrderChannelId=b.SupplementTarge and b.SupplementType = 2
	                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=7
						 
							 left join Logistics_Trace(nolock)f on f.TrackingNumber=a.TrackingNumber
	                        where  f.OnlineTime  is not null and f.TargetTime<=f.OnlineTime  and f.OnlineTime < DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=7
	                        )
	                        and a.ReceiveTime>'2023-01-01'
                        union 
                        select dateadd(hour,b.TrackSupplementHour,  f.OnlineTime ) TrackSupplementTime,JudgmentNode,a.TrackingNumber,b.TrackSupplementLocation,case when b.TrackSupplementLocation =1 then 'China' else ReceiverCountryCode end as position ,b.TrackSupplementText from 
	                        Order_Order(nolock)a 
	                        join Logistics_Trace_SupplementConfig(nolock)b on b.SupplementType = 1 and JudgmentNode=7
	                        and b.IsDelete=0 and b.IsEnable=1 	
						 
							 left join Logistics_Trace(nolock)f on f.TrackingNumber=a.TrackingNumber
	                        where  f.OnlineTime  is not null and  f.TargetTime<=f.OnlineTime  and  f.OnlineTime< DATEADD(hour,0-b.NoUpdateHour,getdate())
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)c where c.SupplementType=3 and a.ChannelId = c.SupplementTarge
		                        and c.IsDelete=0 and c.IsEnable=1 and JudgmentNode=7
	                        )	
	                        and not exists(
		                        select id from Logistics_Trace_SupplementConfig(nolock)b where b.SupplementType=2 and a.CreateOrderChannelId = b.SupplementTarge
		                        and b.IsDelete=0 and b.IsEnable=1 and JudgmentNode=7
	                        )
	                        and a.ReceiveTime>'2023-01-01'
                        )
                        select * from t1 where not exists(
	                        select * from Logistics_Trace_SupplementLog(Nolock)b where t1.TrackingNumber = b.TrackingNumber and t1.JudgmentNode = b.JudgmentNode 
                        ) ";
            results.AddRange(await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLog>(sql, commandTimeout: 120));
            if (results.Count > 1000)
                return results.Take(1000).ToList();

            //特殊物流补充，内容写死 
            results.AddRange(await GetLogisticsByCustomer());
            if (results.Count > 1000)
                return results.Take(1000).ToList();


            return results;

        }
        #region 特殊物流轨迹补充

        /// <summary>
        /// 特殊客户轨迹补充
        /// </summary>
        /// <returns></returns>
        private async Task<List<Logistics_Trace_SupplementLog>> GetLogisticsByCustomer()
        {
            //特殊客户取数  （深圳市天图通逊供应链有限公司   698）补充节点都是7
            List<Logistics_Trace_SupplementLog> result = new List<Logistics_Trace_SupplementLog>();

            string sql = @$"select a.ReceiveTime,a.TrackingNumber,c.TrackSupplementTime,c.TrackSupplementText from Order_Order(nolock) a
left join Logistics_Trace(nolock) b on b.TrackingNumber=a.TrackingNumber
outer apply(select top 1 *from Logistics_Trace_SupplementLog(nolock) where TrackingNumber=a.TrackingNumber order by TrackSupplementTime)c
where a.CustomerId=698 and 'XYY'!=left(a.TrackingNumber,3) and ''=ISNULL(b.OnlineTime,'') and ''!=ISNULL(a.ReceiveTime,'') and '{TrackSupplementTextEnum.邮局转运.GetDescription()}'!=isnull(c.TrackSupplementText,'') ";
            var dbData = (await _unitOfWork.QueryBySqlAsync<Trace_SupplementByCustomer>(sql, commandTimeout: 120)).ToList();
            DateTime today = DateTime.Now;
            foreach (var item in dbData)
            {
                int weekNum = (int)item.ReceiveTime.DayOfWeek;//0-6  周日是0

                int node = !string.IsNullOrEmpty(item.TrackSupplementText) ?
                    (int)EnumHelper.GetEnumByDescription<TrackSupplementTextEnum>(item.TrackSupplementText) + 1
                    : 1;
                while (node <= (int)TrackSupplementTextEnum.邮局转运)
                {
                    var tempData = BuildData(weekNum, item, node);
                    if (tempData.TrackSupplementTime > today) { node = 99999; }
                    else { result.Add(tempData.Copy<Logistics_Trace_SupplementLog>()); node += 1; }
                };
            }
            return result;
        }
        private Logistics_Trace_SupplementLog BuildData(int weekNum, Trace_SupplementByCustomer waitLog, int node)
        {
            Logistics_Trace_SupplementLog log = new Logistics_Trace_SupplementLog()
            {
                TrackingNumber = waitLog.TrackingNumber,
                JudgmentNode = 7,
                TrackSupplementLocation = 1,//1国内 默认，2国外
            };
            switch (node)
            {
                case (int)TrackSupplementTextEnum.邮局转运:
                    log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(3);
                    log.TrackSupplementText = TrackSupplementTextEnum.邮局转运.GetDescription();
                    break;
                    //case (int)TrackSupplementTextEnum.报关:
                    //    log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(6);
                    //    log.TrackSupplementText = TrackSupplementTextEnum.报关.GetDescription();
                    //    break;
                    //case (int)TrackSupplementTextEnum.离开中国:
                    //    if (new int[] { 1, 2 }.Contains(weekNum)) { log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(10); }
                    //    else { log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(8); }
                    //    log.TrackSupplementText = TrackSupplementTextEnum.离开中国.GetDescription();
                    //    break;
                    //case (int)TrackSupplementTextEnum.落地:
                    //    log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(15);
                    //    log.TrackSupplementText = TrackSupplementTextEnum.落地.GetDescription();
                    //    log.TrackSupplementLocation = 2;
                    //    break;
                    //case (int)TrackSupplementTextEnum.清关:
                    //    if (new int[] { 1, 2 }.Contains(weekNum)) { log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(21); }
                    //    else if (new int[] {3, 4 }.Contains(weekNum)) { log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(19); }
                    //    else { log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(17); }
                    //    log.TrackSupplementText = TrackSupplementTextEnum.清关.GetDescription();
                    //    log.TrackSupplementLocation = 2;
                    //    break;
                    //case (int)TrackSupplementTextEnum.交UPS:
                    //    if (new int[] { 1, 2 }.Contains(weekNum)) { log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(24); }
                    //    else if (new int[] { 3, 4 }.Contains(weekNum)) { log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(22); }
                    //    else { log.TrackSupplementTime = waitLog.ReceiveTime.AddDays(20); }
                    //    log.TrackSupplementText = TrackSupplementTextEnum.交UPS.GetDescription();
                    //    log.TrackSupplementLocation = 2;
                    //    break;
            }
            return log;
        }
        #endregion
        private class WatiSupplementLog
        {
            public string TrackingNumber
            {
                get; set;
            }

            public DateTime OldTime
            {
                get; set;
            }

            public int ChannelId
            {
                get; set;
            }

            public int CreateOrderChannelId
            {
                get; set;
            }
        }


        public class Mapping : MSExcelClassMapping<Logistics_Trace_SupplementLog>
        {
            public Mapping()
            {
                Map(x => x.TrackingNumber).Name("lm_tracking_no");
                Map(x => x.TrackSupplementText).Name("eventMsg");
                Map(x => x.TrackSupplementTime).Name("op_time");
                Map(x => x.Remark).Name("op_position");
            }
        }

        public ApiJsonModel Input(Stream stream)
        {
            if (stream == null)
                throw new Exception("未传入文件内容");
            MSExcelHelper helper = new MSExcelHelper();
            var trList = helper.LoadDataAsStream<Mapping, Logistics_Trace_SupplementLog>(stream);
            var trGroup = trList.GroupBy(x => x.TrackingNumber);
            //trGroup = trGroup.Where(x => x.Key != null);
            List<Logistics_TraceGroup> insertList = new List<Logistics_TraceGroup>();
            foreach (var tr in trGroup)
            {
                if (tr != null&&tr.Key!=null)
                {
                    insertList.Add(new Logistics_TraceGroup
                    {
                        TrackingNumber = tr.Key,
                        List = tr.Select(x => new EsLogisticsTrackingLog
                        {
                            Status = 5,
                            StatusString = x.TrackSupplementText.Trim(),
                            TriggerTime = x.TrackSupplementTime,
                            Location = x.Remark,
                            TrackingNumber = tr.Key
                        }).ToList()
                    }); 
                } 
            }

            var descriptor = new BulkDescriptor();
            var data = _supplementClient.IndexMany(insertList);
            if (data.Errors)
            {
            }
            return new ApiJsonModel
            {
                success = true

            };
        }


        #endregion
       
    }
}