XYY.Core/3.Service/XYY.Service.Standard.First/LadingTrackingService.cs

using System.Threading.Tasks;
using XYY.Core.Standard.AliYun;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;
using XYY.TaskTrack.Standard.TaskModel;
using System.Linq;
using System.Collections.Generic;
using XYY.Data.Standard.First;
using XYY.TaskTrack.Standard;
using System;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.Task;
using XYY.Data.Standard.Tasks;
using XYY.Data.Standard.Order;
using Dapper;
using XYY.Data.Model.Standard.Tracking;
using XYY.Service.Standard.First.Model;
using XYY.Model.Standard.First;
using RestSharp;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using XYY.Service.Standard.First.DB;
using XYY.Common.Standard;
using System.Diagnostics;
using XYY.Service.Standard.First.Mapping;
using System.Threading;
using XYY.Service.Standard.First.Mail;

namespace XYY.Service.Standard.First
{
    public class LadingTrackingService : ILadingTrackingService
    {
        private IAliYunPostFileSerivce _aliYunPostFileSerivce;
        private IExcelHelper _excelHelper;
        private ILogistics_LadingBillRepository _logistics_LadingBillRepository;
        private ITaskTrackDataRepository _taskTrackData;
        private IOrderRepository _orderRepository;
        private IUnitOfWork _unitOfWork;
        private IMQManager _MQManager;
        private IFirstDB _firstDB;
        private IOrderMailService _orderMailService;

        public LadingTrackingService(IExcelHelper excelHelper,
            IAliYunPostFileSerivce aliYunPostFileSerivce,
            ILogistics_LadingBillRepository logistics_LadingBillRepository,
            ITaskTrackDataRepository taskTrackData,
            IMQManager MQManager,
            IOrderRepository orderRepository,
            IUnitOfWork unitOfWork,
            IFirstDB firstDB,
            IOrderMailService orderMailService)
        {
            _excelHelper = excelHelper;
            _aliYunPostFileSerivce = aliYunPostFileSerivce;
            _logistics_LadingBillRepository = logistics_LadingBillRepository;
            _taskTrackData = taskTrackData;
            _MQManager = MQManager;
            _orderRepository = orderRepository;
            _unitOfWork = unitOfWork;
            _firstDB = firstDB;
            _orderMailService = orderMailService;
        }

        /// <summary>
        /// 添加批量上传的任务
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <returns></returns>
        public async Task<BatchJobResult> AddBatchJob(System.IO.Stream formFile)
        {
            BatchJobResult batchJobResult = new BatchJobResult();
            var list = _excelHelper.LoadDataAsStream<BatchMapping, Job_LadingBillLogisticsItem>(formFile);
            list = list.Where(x => !string.IsNullOrEmpty(x.LadingBillNumber)).ToList();
            var ladingBillNumbers = list.Select(x => x.LadingBillNumber).ToArray();
            list =
                list.GroupBy(x => x.LadingBillNumber).Select(x =>
                x.OrderByDescending(y => y.DepartTime).ThenByDescending(y => y.ClearanceTime).ThenByDescending(y => y.ArrivalTime)
                .First()).ToList();
            var waitLaddingBills = await _logistics_LadingBillRepository.QueryBill(ladingBillNumbers);
            var notFinds = ladingBillNumbers.Where(x => !waitLaddingBills.Any(y => y.LadingBillNumber.Trim() == x)).Select(x => new Job_LadingBillLogisticsItem { LadingBillNumber = x }).ToList();
            if (notFinds.Count > 0)
            {
                //未找到的提单号，生成一个Excel文件返回
                string url = _excelHelper.OutData<NotFindMapping, Job_LadingBillLogisticsItem>(notFinds);
                batchJobResult.ErrorFileUrl = url;
            }
            //提前循环，判断起飞和到达时间
            foreach (var i in list)
            {
                var tempWaitBills = waitLaddingBills.Where(x => x.LadingBillNumber == i.LadingBillNumber && x.IsDeleted == false).FirstOrDefault();
                if (tempWaitBills == null) { continue; }
                //始终更新创建时间
                i.CreateTime = tempWaitBills.CreateTime.Value;
                //赋值轨迹抓取的数据
                if (tempWaitBills.DepartTime.HasValue && !string.IsNullOrEmpty(tempWaitBills.DepartRemark) && tempWaitBills.DepartRemark.Contains("轨迹抓取更新"))
                {
                    i.DepartTime = tempWaitBills.DepartTime;
                }
                if (tempWaitBills.ArrivalTime.HasValue && !string.IsNullOrEmpty(tempWaitBills.ArrivalRemark) && tempWaitBills.ArrivalRemark.Contains("轨迹抓取更新"))
                {
                    i.ArrivalTime = tempWaitBills.ArrivalTime;
                }
                //如果清关时间和起飞时间一样的话，起飞轨迹会比清关轨迹排在前边，导致轨迹顺序混乱，
                //所以要加100毫秒。加100毫秒的目的保证正确排序，但两者时分秒显示能保持一致
                if (i.ClearanceTime.HasValue && i.DepartTime.HasValue && i.ClearanceTime.Value == i.DepartTime.Value)
                {
                    i.ClearanceTime = i.ClearanceTime.Value.AddMilliseconds(100);
                }
            }
            list = list.Where(x => waitLaddingBills.Any(y => y.LadingBillNumber == x.LadingBillNumber && (
        x.CustomsClearanceTime != y.CustomsClearanceTime ||
        x.ArrivalTime != y.ArrivalTime
        || x.ClearanceTime != y.ClearanceTime
        || x.DepartTime != y.DepartTime
        || x.LeaveWarehouseTime != y.LeaveWarehouseTime
        || x.TrafficTime != y.TrafficTime))).ToList();

            List<Job_LadingBillLogisticsItem> list2 = new List<Job_LadingBillLogisticsItem>();

            //判断是否是补数据
            foreach (var f in list)
            {
                var item = waitLaddingBills.Where(x => x.LadingBillNumber == f.LadingBillNumber).FirstOrDefault();
                //系统存在提单
                //if (
                //    (!item.ArrivalTime.HasValue && f.ArrivalTime.HasValue && f.ArrivalTime < DateTime.Now) ||
                //    (!item.ClearanceTime.HasValue && f.ClearanceTime.HasValue && f.ClearanceTime < DateTime.Now) ||
                //    (!item.DepartTime.HasValue && f.DepartTime.HasValue && f.DepartTime.Value < DateTime.Now) ||
                //    (!item.TrafficTime.HasValue && f.TrafficTime.HasValue && f.TrafficTime.Value < DateTime.Now)

                //    )
                //{
                bool isAdd = false;
                //增加更新节点判断
                if (!item.ArrivalTime.HasValue && f.ArrivalTime.HasValue && f.ArrivalTime < DateTime.Now) { f.updatedNode.Add("ArrivalTime"); isAdd = true; }
                if (!item.ClearanceTime.HasValue && f.ClearanceTime.HasValue && f.ClearanceTime < DateTime.Now) { f.updatedNode.Add("ClearanceTime"); isAdd = true; }
                if (!item.DepartTime.HasValue && f.DepartTime.HasValue && f.DepartTime.Value < DateTime.Now) { f.updatedNode.Add("DepartTime"); isAdd = true; }
                if (!item.TrafficTime.HasValue && f.TrafficTime.HasValue && f.TrafficTime.Value < DateTime.Now) { f.updatedNode.Add("TrafficTime"); isAdd = true; }
                //如果提单时间离港小于当前时间，我们不再推送
                if (isAdd) { list2.Add(f); }




                //}
            }
            //校验数据时间，只校验需要补的数据
            var verify = await LadingTimeVerify(waitLaddingBills, list);
            if (!verify.Item1) { return verify.Item2; }
            //邮件数据记录
            await _orderMailService.AddClearanceMail(list);
            //待上传列表
            TaskTrackModel taskTrackModel = new TaskTrackModel()
            {
                WaitQty = list.Count,
                Name = "batchTrace" + Guid.NewGuid().ToString(),
                Summary = list.Count
            };

            batchJobResult.JobId = taskTrackModel.Name;
            await _taskTrackData.InitTaskTrack(taskTrackModel);
            //内部数据更新
            await _MQManager.Publish(new Job_LadingBillLogistics { Jobs = list });
            //通知第三方Api数据更新

            await _MQManager.Publish(new Job_Customer_FristLog { Jobs = list2 });
            return batchJobResult;
        }

        public async Task<Tuple<bool, BatchJobResult>> LadingTimeVerify(List<Logistics_LadingBill> waitLaddingBills, List<Job_LadingBillLogisticsItem> jobs)
        {
            List<LadingTimeError> error = new List<LadingTimeError>();
            foreach (var item in jobs)
            {
                var tempWaitBills = waitLaddingBills.Where(x => x.LadingBillNumber == item.LadingBillNumber).FirstOrDefault();
                if (item.CustomsClearanceTime.HasValue && item.CustomsClearanceTime < tempWaitBills.CreateTime)
                {
                    error.Add(new LadingTimeError() { LadingBillNumber = item.LadingBillNumber, ErrorMessage = "国内报关时间不应小于提单创建时间" });
                }
                else if (item.DeliveryTime.HasValue && item.CustomsClearanceTime.HasValue && item.DeliveryTime < item.CustomsClearanceTime)
                {
                    error.Add(new LadingTimeError() { LadingBillNumber = item.LadingBillNumber, ErrorMessage = "交航时间不应小于国内报关时间" });
                }
                else if (item.DeliveryTime.HasValue && item.DepartTime.HasValue && item.DepartTime < item.DeliveryTime)
                {
                    error.Add(new LadingTimeError() { LadingBillNumber = item.LadingBillNumber, ErrorMessage = "离港时间不应小于交航时间" });
                }
                else if (item.ArrivalTime.HasValue && item.DepartTime.HasValue && item.ArrivalTime < item.DepartTime)
                {
                    error.Add(new LadingTimeError() { LadingBillNumber = item.LadingBillNumber, ErrorMessage = "到港时间不应小于离港时间" });
                }
                else if (item.ArrivalTime.HasValue && item.ClearanceTime.HasValue && item.ClearanceTime < item.ArrivalTime)
                {
                    error.Add(new LadingTimeError() { LadingBillNumber = item.LadingBillNumber, ErrorMessage = "清关时间不应小于到港时间" });
                }
            }
            if (error != null && error.Count > 0)
            {
                BatchJobResult batchJobResult = new BatchJobResult();
                string url = _excelHelper.OutData<TimeErrorMapping, LadingTimeError>(error);
                batchJobResult.ErrorFileUrl = url;
                return Tuple.Create(false, batchJobResult);
            }
            else
            {
                return Tuple.Create(true, new BatchJobResult());
            }
        }
        public async Task TestPublic()
        {
            await _MQManager.Publish(new Job_Customer_FristLog
            {
                Jobs = new List<Job_LadingBillLogisticsItem>{new Job_LadingBillLogisticsItem{
             LadingBillNumber="test111",
             ClearanceTime =DateTime.Parse("2021-08-19 14:22:09"),
             ArrivalTime=DateTime.Now,
             DepartTime=DateTime.Now
            } }
            });
        }

        public async Task TestSendLog(Frist_Log fristLog)
        {
            await _MQManager.Publish(fristLog);
        }

        public async Task TrackSupplement(TrackSupplementRequest param)
        {
            await _MQManager.Publish(param);
        }


        public async Task SendInitHKXiKeOrders()
        {
            //上网轨迹
            string sql = @" select a.CustomerOrderNo from order_order(nolock)a  join Logistics_Trace(nolock)b on a.TrackingNumber = b.TrackingNumber
							  where a.CustomerId = 691 and b.OnlineTime is not null";
            var orders = await _orderRepository.QueryBySqlAsync(sql);
            var log = new Frist_Log
            {
                Groups = new List<Data.Model.Standard.Tracking.Logistics_TraceGroup>()
            };

            foreach (var order in orders)
            {
                var group = new Data.Model.Standard.Tracking.Logistics_TraceGroup
                {
                    CustomerOrderNo = order.CustomerOrderNo,
                    List = new List<Data.Model.Standard.Tracking.EsLogisticsTrackingLog>
                    {

                    }
                };
                log.Groups.Add(group);
                if (order.ReceiveTime.HasValue)
                {
                    group.List.Add(new Data.Model.Standard.Tracking.EsLogisticsTrackingLog
                    {
                        TriggerTime = order.ReceiveTime.Value,
                        Location = "guangzhou",
                        Status = 8,
                        StatusString = "Item inbound in sorting center."
                    });
                }
            }

            //XiKeApi xiKeApi = new XiKeApi();
            //var erros = await xiKeApi.Send(log.Groups);
            //await AddErrors(erros, "称重");


            //装箱数据
            //foreach (var order in orders)
            //{
            //    var group = new Data.Model.Standard.Tracking.Logistics_TraceGroup
            //    {
            //        CustomerOrderNo = order.CustomerOrderNo,
            //        List = new List<Data.Model.Standard.Tracking.EsLogisticsTrackingLog>
            //        {

            //        }
            //    };
            //    log.Groups.Add(group);
            //    if (order.ReceiveTime.HasValue)
            //    {
            //        group.List.Add(new Data.Model.Standard.Tracking.EsLogisticsTrackingLog
            //        {
            //            TriggerTime = order.ReceiveTime.Value,
            //            Location = "guangzhou",
            //            Status = 2,
            //            StatusString = "Item inbound in sorting center."
            //        });
            //    }

            //    if (order.SendOutTime.HasValue)
            //    {
            //        group.List.Add(new Data.Model.Standard.Tracking.EsLogisticsTrackingLog
            //        {
            //            TriggerTime = order.SendOutTime.Value,
            //            Location = "guangzhou",
            //            Status = 3,
            //            StatusString = "Item outbound in sorting center."
            //        });
            //    }
            //}

            //头程推送、称重、装箱
            //string sql = @"select * from order_order(nolock) where customerid = 691 and orderStatus>1";
            //var orders = await _orderRepository.QueryBySqlAsync(sql);
            //var log = new Frist_Log
            //{
            //    Groups = new List<Data.Model.Standard.Tracking.Logistics_TraceGroup>()
            //};

            ////装箱数据
            //foreach (var order in orders)
            //{
            //    var group = new Data.Model.Standard.Tracking.Logistics_TraceGroup
            //    {
            //        CustomerOrderNo = order.CustomerOrderNo,
            //        List = new List<Data.Model.Standard.Tracking.EsLogisticsTrackingLog>
            //        {

            //        }
            //    };
            //        log.Groups.Add(group);
            //    if (order.ReceiveTime.HasValue)
            //    {
            //        group.List.Add(new Data.Model.Standard.Tracking.EsLogisticsTrackingLog
            //        {
            //            TriggerTime = order.ReceiveTime.Value,
            //            Location = "guangzhou",
            //            Status = 2,
            //            StatusString = "Item inbound in sorting center."
            //        });
            //    } 

            //    if (order.SendOutTime.HasValue)
            //    {   
            //        group.List.Add(new Data.Model.Standard.Tracking.EsLogisticsTrackingLog
            //        {
            //            TriggerTime = order.SendOutTime.Value,
            //            Location = "guangzhou",
            //            Status = 3,
            //            StatusString = "Item outbound in sorting center."
            //        });
            //    }
            //}

            //XiKeApi xiKeApi = new XiKeApi();
            //var erros = await xiKeApi.Send(log.Groups);
            //await AddErrors(erros, "称重");


            ////头程轨迹
            //string fristSql = @"select c.LadingBillNumber,a.TrackingNumber,a.Id,a.ReceiverCountryCode,d.DepartTime,d.ArrivalTime,d.ClearanceTime,a.CustomerOrderNo from Order_Order(nolock) a
            //                  join Logistics_Boxdetail(nolock) b on a.id = b.orderid join logistics_box(nolock) c on b.boxid=c.id
            //                  join Logistics_LadingBill(nolock) d on d.LadingBillNumber = c.LadingBillNumber
            //               where  d.IsDeleted=0
            //                  and (a.CustomerId = 691)";

            //List<dynamic> firstTrackings = new List<dynamic>();
            //using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(_unitOfWork.ConnectionAddress))
            //{
            //    connection.Open();
            //    firstTrackings = (await connection.QueryAsync<dynamic>(fristSql, null, null, null, null)).ToList();
            //}

            //var groups = firstTrackings.GroupBy(x => new Job_LadingBillLogisticsItem
            //{
            //    LadingBillNumber = x.LadingBillNumber,
            //    ArrivalTime = x.ArrivalTime,
            //    DepartTime = x.DepartTime,
            //    ClearanceTime = x.ClearanceTime
            //});

            //foreach (var ladingBillNumber
            //    in groups)
            //{
            //    if (ladingBillNumber.ToList().Count > 0)
            //    {
            //        var logs =
            //            JobSubscribe.JobSubscribe.GetLogs2(ladingBillNumber.ToList(), ladingBillNumber.Key);

            //        xiKeApi = new XiKeApi();
            //        erros = await xiKeApi.Send(logs);
            //        await AddErrors(erros, "头程");
            //    }
            //}



        }

        public async Task AddErrors(List<Logistics_TraceGroup> erros, string target)
        {
            if (erros.Count > 0)
            {
                Frist_Log log = new Frist_Log();
                log.Groups = erros;
                //持久化
                await _unitOfWork.InsertAsync<ErroJobLog>(new ErroJobLog
                {
                    ActionUrl = "http://120.24.149.148:9520/api/BatchTracking/TestSendLog",
                    IsExecute = true,
                    Request = Newtonsoft.Json.JsonConvert.SerializeObject(log),
                    Target = target,
                    CreateTime = DateTime.Now
                });
            }
        }

        #region 对接51Track 航空订单
        public async Task<List<Lading51TrackResult>> Lading51TrackService(List<string> ladingNumber, bool IsTest = false)
        {
            List<Lading51TrackResult> trackResults = new List<Lading51TrackResult>();
            List<WaitQueryData> dbData = await GetWaitToQueryTrack(ladingNumber);
            if (dbData == null || dbData.Count == 0) { throw new Exception("51Track 航空订单，无提单数据可查询"); }
            var groupData = dbData.GroupBy(x => new { x.LadingBillNumber }).ToList();
            //取出数据逻辑的条件
            List<Lading51TrackRules> rules = await GetLading51TrackRules();
            Stopwatch stopWatch = new Stopwatch();
            double TotalMilliseconds = 3000;//默认不停，执行过快会重复赋值
            //mq数据
            List<Job_LadingBillLogisticsItem> list2 = new List<Job_LadingBillLogisticsItem>() { };
            foreach (var item in groupData)
            {
                try
                {
                    if (1000 - TotalMilliseconds > 0)
                    {
                        System.Threading.Thread.Sleep(2000 - (int)TotalMilliseconds);
                        TotalMilliseconds = 0;
                    }
                    stopWatch.Start();
                    var trackResult = await QueryBy51Track(IsTest ? "618-62659726" : item.Key.LadingBillNumber);//用测试提单测，会默认一个提单的轨迹去更
                    var esModel = await ConvertToEsModel(trackResult, item.ToList(), rules);
                    if (esModel.Count == 0)
                    {
                        TimeSpan ts = stopWatch.Elapsed;
                        TotalMilliseconds = ts.TotalMilliseconds;
                        stopWatch.Reset();
                        continue;
                    }
                    var waitUpdate = JsonConvert.DeserializeObject<List<Logistics_TraceGroup>>(JsonConvert.SerializeObject(esModel));
                    await EsTrackUpdate(waitUpdate);
                    await ExecuteUpdateLadingTrackTime(trackResult, item.ToList());
                    //添加mq任务
                    #region  添加mq任务
                    var tempLadingInfo = item.FirstOrDefault();
                    var tempEsInfo = waitUpdate.FirstOrDefault();
                    //这里只会添加4和5两种轨迹
                    Job_LadingBillLogisticsItem job = new Job_LadingBillLogisticsItem();
                    bool isAdd = false;
                    if (tempEsInfo.List.Any(y => y.Status == 4))
                    {
                        //起飞时间
                        job.LadingBillNumber = tempLadingInfo.LadingBillNumber;
                        job.DepartTime = tempEsInfo.List.Where(x => x.Status == 4).OrderByDescending(x => x.TriggerTime).FirstOrDefault().TriggerTime;
                        job.updatedNode.Add("DepartTime"); isAdd = true;
                    }
                    if (tempEsInfo.List.Any(y => y.Status == 5))
                    {
                        //到达时间
                        job.LadingBillNumber = tempLadingInfo.LadingBillNumber;
                        job.ArrivalTime = tempEsInfo.List.Where(x => x.Status == 5).OrderByDescending(x => x.TriggerTime).FirstOrDefault().TriggerTime;
                        job.updatedNode.Add("ArrivalTime"); isAdd = true;
                    }
                    if (isAdd) { list2.Add(job); }
                    #endregion
                    //ExecuteSendLogs(esModel, dbData.Where(x => x.CustomerId == 7 || x.CustomerId == 691).ToList());//写死判断，只有细刻需要发送此api
                    //判断时间
                    TimeSpan ts1 = stopWatch.Elapsed;
                    TotalMilliseconds = ts1.TotalMilliseconds;
                    stopWatch.Reset();
                }
                catch (Exception ex)
                {
                    trackResults.Add(new Lading51TrackResult() { LadingBillNumber = item.Key.LadingBillNumber, errorMessage = ex.Message });
                    TimeSpan ts = stopWatch.Elapsed;
                    TotalMilliseconds = ts.TotalMilliseconds;
                    stopWatch.Reset();
                }
            }
            if (list2.Count > 0) { await _MQManager.Publish(new Job_Customer_FristLog { Jobs = list2 }); }
            return trackResults;
        }
        public async Task testDez()
        {
            string json = @"{
    ""meta"": {
        ""code"": 200,
        ""type"": ""Success"",
        ""message"": ""Request Success""
    },
    ""data"": {
        ""618-62659726"": {
            ""track_number"": ""618-62659726"",
            ""return_data"": {
                ""status_number"": 4,
                ""origin"": ""CAN"",
                ""destination"": ""CDG"",
                ""piece"": ""36"",
                ""weight"": ""628.0"",
                ""track_info"": [
                    {
                        ""plan_date"": ""2023-06-28 15:36:00"",
                        ""actual_date"": ""2023-06-28 15:36:00"",
                        ""event"": ""Shipment Delivered"",
                        ""station"": ""CDG"",
                        ""flight_number"": ""-"",
                        ""status"": ""DLV"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-28 11:03:00"",
                        ""actual_date"": ""2023-06-28 11:03:00"",
                        ""event"": ""Shipment Ready for Pick-up"",
                        ""station"": ""CDG"",
                        ""flight_number"": ""-"",
                        ""status"": ""NFD"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-28 11:03:00"",
                        ""actual_date"": ""2023-06-28 11:03:00"",
                        ""event"": ""Shipment Checked Into Warehouse"",
                        ""station"": ""CDG"",
                        ""flight_number"": ""SQ0336"",
                        ""status"": """",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-28 09:40:00"",
                        ""actual_date"": ""2023-06-28 09:40:00"",
                        ""event"": ""Document Delivered"",
                        ""station"": ""CDG"",
                        ""flight_number"": ""-"",
                        ""status"": ""AWD"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-28 07:40:00"",
                        ""actual_date"": ""2023-06-28 07:40:00"",
                        ""event"": ""Cleared By Customs"",
                        ""station"": ""CDG"",
                        ""flight_number"": ""-"",
                        ""status"": """",
                        ""piece"": ""36"",
                        ""weight"": ""-""
                    },
                    {
                        ""plan_date"": ""2023-06-28 07:28:00"",
                        ""actual_date"": ""2023-06-28 07:28:00"",
                        ""event"": ""Flight Arrived"",
                        ""station"": ""CDG"",
                        ""flight_number"": ""SQ0336"",
                        ""status"": ""ARR"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-28 00:17:00"",
                        ""actual_date"": ""2023-06-28 00:17:00"",
                        ""event"": ""Flight Departed"",
                        ""station"": ""SIN"",
                        ""flight_number"": ""SQ0336"",
                        ""status"": ""DEP"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-27 12:15:00"",
                        ""actual_date"": ""2023-06-27 12:15:00"",
                        ""event"": ""Shipment Checked Into Warehouse"",
                        ""station"": ""SIN"",
                        ""flight_number"": ""SQ7821"",
                        ""status"": """",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-27 11:55:00"",
                        ""actual_date"": ""2023-06-27 11:55:00"",
                        ""event"": ""Flight Arrived"",
                        ""station"": ""SIN"",
                        ""flight_number"": ""SQ7821"",
                        ""status"": ""ARR"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-27 05:11:00"",
                        ""actual_date"": ""2023-06-27 05:11:00"",
                        ""event"": ""Flight Departed"",
                        ""station"": ""CAN"",
                        ""flight_number"": ""SQ7821"",
                        ""status"": ""DEP"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-26 10:40:00"",
                        ""actual_date"": ""2023-06-26 10:40:00"",
                        ""event"": ""Freight On Hand"",
                        ""station"": ""CAN"",
                        ""flight_number"": ""-"",
                        ""status"": ""FOH"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    },
                    {
                        ""plan_date"": ""2023-06-26 10:40:00"",
                        ""actual_date"": ""2023-06-26 10:40:00"",
                        ""event"": ""Shipment Received"",
                        ""station"": ""CAN"",
                        ""flight_number"": ""-"",
                        ""status"": ""RCF"",
                        ""piece"": ""36"",
                        ""weight"": ""628.0""
                    }
                ],
                ""flight_info"": {
                    ""SQ7821"": {
                        ""depart_station"": ""CAN"",
                        ""arrival_station"": ""SIN"",
                        ""plan_depart_time"": ""2023-06-27 05:55:00"",
                        ""depart_time"": null,
                        ""plan_arrival_time"": ""2023-06-27 09:50:00"",
                        ""arrival_time"": null
                    },
                    ""SQ0336"": {
                        ""depart_station"": ""SIN"",
                        ""arrival_station"": ""CDG"",
                        ""plan_depart_time"": ""2023-06-28 00:15:00"",
                        ""depart_time"": null,
                        ""plan_arrival_time"": ""2023-06-28 07:35:00"",
                        ""arrival_time"": null
                    }
                },
                ""flight_info_new"": [
                    {
                        ""plan_arrival_time"": ""2023-06-27 09:50:00"",
                        ""plan_depart_time"": ""2023-06-27 05:55:00"",
                        ""depart_station"": ""CAN"",
                        ""arrival_station"": ""SIN"",
                        ""flight_number"": ""SQ7821"",
                        ""status"": ""Confirmed""
                    },
                    {
                        ""plan_arrival_time"": ""2023-06-28 07:35:00"",
                        ""plan_depart_time"": ""2023-06-28 00:15:00"",
                        ""depart_station"": ""SIN"",
                        ""arrival_station"": ""CDG"",
                        ""flight_number"": ""SQ0336"",
                        ""status"": ""Confirmed""
                    }
                ],
                ""flight_way_station"": [
                    ""CAN"",
                    ""SIN"",
                    ""CDG""
                ],
                ""last_event"": ""2023-06-28 15:36:00, Shipment Delivered, CDG, -""
            },
            ""airline_info"": {
                ""name"": ""新加坡"",
                ""url"": ""http://www.siacargo.com/ccn/ShipmentTrack.aspx"",
                ""track_url"": ""http://www.siacargo.com/ccn/ShipmentTrack.aspx"",
                ""trackpage_url"": ""https://www.51tracking.com/aircargo/cn/618-62659726""
            }
        }
    }
}";
            var data = TrackDeserializeObject("618-62659726", json);
        }
        #region db数据处理
        /// <summary>
        /// 获取待查数据
        /// 过滤非快线渠道
        /// </summary>
        /// <param name="ladingNumber"></param>
        /// <returns></returns>
        private async Task<List<WaitQueryData>> GetWaitToQueryTrack(List<string> ladingNumber)
        {
            string ladingNumberWhere = string.Empty;
            if (ladingNumber != null && ladingNumber.Count > 0) { ladingNumberWhere = " and t.LadingBillNumber in @param "; }
            List<string> ladingNumber3 = new List<string>() { "784", "180", "205", "160", "880", "235", "057", "618" };
            string sql = @"  select t.LadingBillNumber,t.OriginPort,t.Destination,t.ServiceName,b.TrackingNumber,b.OrderId,c.CustomerOrderNo,c.ReceiverCountryCode,c.CustomerId
              ,t.DepartTime,t.TrafficTime
   from Logistics_LadingBill(nolock) t 
 left join Logistics_Box(nolock) a on a.LadingBillNumber=t.LadingBillNumber
 left join Logistics_BoxDetail(nolock) b on b.BoxId=a.Id
 left join Order_Order(nolock) c on c.Id=b.OrderId
 where t.CreateTime>='2023-03-01' and t.IsDeleted=0 and ''=ISNULL(t.ArrivalTime,'') and ''!=ISNULL(b.TrackingNumber,'')  and ''!=ISNULL(t.ServiceName,'')
 and ''!=ISNULL(t.OriginPort,'')  and ''!=ISNULL(t.Destination,'') and left(t.LadingBillNumber,3) in @ladingNumber3 " + ladingNumberWhere;
            var result = (await _unitOfWork.QueryBySqlAsync<WaitQueryData>(sql, 300, new { ladingNumber3 = ladingNumber3, param = ladingNumber })).ToList();
            return result;
        }
        /// <summary>
        /// 更新提单轨迹时间
        /// 起飞、到达、
        /// </summary>
        /// <returns></returns>
        private async Task<bool> ExecuteUpdateLadingTrackTime(TrackResult trackResult, List<WaitQueryData> dbData)
        {
            if (dbData == null || dbData.Count == 0) { return false; }
            var prot1 = dbData.FirstOrDefault()?.Destination;
            var prot2 = dbData.FirstOrDefault()?.OriginPort;
            var track_infos = trackResult.data.ladingInfo.return_data.flight_Infos;
            if (track_infos == null || track_infos.Count == 0) { return false; }
            var OriginPortTrack = track_infos.Where(x => prot2 == x.depart_station)?.OrderBy(x => Convert.ToDateTime(x.depart_time))?.FirstOrDefault();
            var DestinationTrack = track_infos.Where(x => prot1 == x.arrival_station)?.OrderBy(x => Convert.ToDateTime(x.arrival_time))?.FirstOrDefault();

            string sql = string.Empty;
            if (OriginPortTrack != null && !string.IsNullOrEmpty(OriginPortTrack.depart_time))
            {
                string updateTrafficTime = string.Empty;
                if (!dbData.FirstOrDefault().TrafficTime.HasValue)
                {
                    updateTrafficTime = $",TrafficTime='{Convert.ToDateTime(OriginPortTrack.depart_time).AddHours(-8)}' ";
                }
                sql = $"update Logistics_LadingBill set DepartTime='{OriginPortTrack.depart_time}',DepartRemark='51轨迹抓取更新,{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'{updateTrafficTime} where LadingBillNumber='{dbData.FirstOrDefault().LadingBillNumber}' and ISNULL(DepartTime,'')='';";
            }
            if (DestinationTrack != null && !string.IsNullOrEmpty(DestinationTrack.arrival_time))
            {
                sql += $"update Logistics_LadingBill set ArrivalTime='{DestinationTrack.arrival_time}',ArrivalRemark='51轨迹抓取更新,{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' where LadingBillNumber='{dbData.FirstOrDefault().LadingBillNumber}' and ISNULL(ArrivalTime,'')='';";
            }
            if (!string.IsNullOrEmpty(sql)) { await _unitOfWork.ExecuteAsync(sql); }
            return false;
        }
        private async Task<List<Lading51TrackRules>> GetLading51TrackRules()
        {
            string sql = " select * from Logistics_Lading51TrackRules ";
            var data = await _unitOfWork.QueryBySqlAsync<Lading51TrackRules>(sql);
            return data.ToList();
        }
        #endregion
        #region 51API对接
        public async Task<TrackResult> QueryBy51Track(string ladingNumber)
        {
            var client = new RestClient("http://api.51tracking.com");
            var request = new RestRequest("/v2/trackings/aircargo", Method.POST);
            request.AddHeader("Tracking-Api-Key", "e0febb2a-eef1-45a3-b534-3dda7465fb4e");
            request.AddHeader("Content-Type", "application/json");
            TrackRequestBody body = new TrackRequestBody() { track_number = ladingNumber };
            request.AddJsonBody(body);
            var response = await client.ExecuteAsync(request);
            if (response.StatusCode == System.Net.HttpStatusCode.OK)
            {
                var MetaStatus = JsonConvert.DeserializeObject<StatusResult>(response.Content);
                if (MetaStatus.meta.code == 200)
                {
                    //进一步解析
                    var result = JsonConvert.DeserializeObject<TrackResult>(response.Content);
                    result.data.ladingInfo = TrackDeserializeObject(ladingNumber, response.Content);
                    return result;
                }
                else
                {
                    throw new Exception($"51Track查询航空订单错误:{MetaStatus.meta.message}");
                }
            }
            else
            {
                throw new Exception($"51TrackApi请求错误:{response.StatusCode},{response.Content}");
            }
        }
        private ladingInfo TrackDeserializeObject(string ladingNumber, string Content)
        {
            var jo = JsonConvert.DeserializeObject<JObject>(Content);
            var data = (jo["data"] as JObject).GetValue(ladingNumber);
            var ladingInfo = JsonConvert.DeserializeObject<ladingInfo>(data.ToString());
            List<string> flight_numbers = ladingInfo.return_data.flight_info_new?.Select(x => x.flight_number)?.Distinct()?.ToList();
            if (flight_numbers == null) { return ladingInfo; }
            List<flight_info> infos = new List<flight_info>();
            foreach (var item in flight_numbers)
            {
                var info = (data["return_data"]["flight_info"] as JObject).GetValue(item);
                if (info != null)
                {
                    flight_info _info = JsonConvert.DeserializeObject<flight_info>(info.ToString());
                    infos.Add(_info);
                }
            }
            ladingInfo.return_data.flight_Infos = infos;
            return ladingInfo;
        }
        #endregion
        #region 轨迹处理
        private async Task<List<Logistics_TraceGroup>> ConvertToEsModel(TrackResult trackResult, List<WaitQueryData> dbData, List<Lading51TrackRules> rules)
        {
            List<Track_infoItem> tracks = new List<Track_infoItem>();
            List<Logistics_TraceGroup> insertLogs = new List<Logistics_TraceGroup>();
            var track_infos = trackResult.data.ladingInfo.return_data.flight_Infos;
            var trackInfos = trackResult.data.ladingInfo.return_data.track_info;
            Time51Value OriginPortTrack = null;
            Time51Value DestinationTrack = null;
            if (trackInfos != null && trackInfos.Count > 0
                && rules.Any(x => dbData.FirstOrDefault().LadingBillNumber.StartsWith(x.LadingNumber3)))
            {
                //一个单号只能有一种逻辑
                var rule = rules.Where(x => dbData.FirstOrDefault().LadingBillNumber.StartsWith(x.LadingNumber3)).FirstOrDefault();
                var tempOriginPortTrack = trackInfos
                    .Where(x => dbData.FirstOrDefault().OriginPort?.Trim() == x.station?.Trim())
                    ?.OrderBy(x => Convert.ToDateTime(x.actual_date?.Trim()))?.FirstOrDefault();
                OriginPortTrack = new Time51Value() { dateTime = tempOriginPortTrack?.actual_date };

                var tempDestinationTrack = trackInfos
                  .Where(x => dbData.FirstOrDefault().Destination?.Trim() == x.station?.Trim())
                  ?.OrderBy(x => Convert.ToDateTime(x.actual_date?.Trim()))?.FirstOrDefault();
                DestinationTrack = new Time51Value() { dateTime = tempDestinationTrack?.actual_date };

            }
            else if (track_infos != null && track_infos.Count > 0)
            {
                var tempOriginPortTrack = track_infos.Where(x => dbData.FirstOrDefault().OriginPort?.Trim() == x.depart_station?.Trim() && !string.IsNullOrEmpty(x?.depart_time?.Trim()))?.OrderBy(x => Convert.ToDateTime(x?.depart_time?.Trim()))?.FirstOrDefault();
                OriginPortTrack = new Time51Value() { dateTime = tempOriginPortTrack?.depart_time };

                var tempDestinationTrack = track_infos.Where(x => dbData.FirstOrDefault().Destination?.Trim() == x.arrival_station?.Trim()&&!string.IsNullOrEmpty(x?.arrival_time?.Trim()))?.OrderBy(x => Convert.ToDateTime(x?.arrival_time?.Trim()))?.FirstOrDefault();
                DestinationTrack = new Time51Value() { dateTime = tempDestinationTrack?.arrival_time };
            }
            //判断是否有时间数据，无则跳过 两者皆空
            if ((OriginPortTrack == null && DestinationTrack == null) || (string.IsNullOrEmpty(OriginPortTrack?.dateTime) && string.IsNullOrEmpty(DestinationTrack?.dateTime)))
            {
                return insertLogs;
            }
            else
            {
                //有则判断时间，是否符合，不符合发送微信通知
                var temp = dbData.FirstOrDefault();
                if (temp.TrafficTime.HasValue && !string.IsNullOrEmpty(OriginPortTrack?.dateTime) && Convert.ToDateTime(OriginPortTrack.dateTime) < temp.TrafficTime)
                {
                    sendMessageToWeChat("51提单轨迹同步", $"提单号：【{temp.LadingBillNumber}】,交航时间大于起飞时间，异常！", new List<string>() { "LiuZhengKai", "fanlihang" });
                }
                else if (temp.DepartTime.HasValue && !string.IsNullOrEmpty(DestinationTrack?.dateTime) && Convert.ToDateTime(DestinationTrack.dateTime) < temp.DepartTime)
                {
                    sendMessageToWeChat("51提单轨迹同步", $"提单号：【{temp.LadingBillNumber}】,起飞时间大于到达时间，异常！", new List<string>() { "LiuZhengKai", "fanlihang" });
                }
            }
            foreach (var tn in dbData)
            {
                var g = new Logistics_TraceGroup
                {
                    TrackingNumber = tn.TrackingNumber,
                    CustomerOrderNo = tn.CustomerOrderNo,
                    List = new List<EsLogisticsTrackingLog>()
                };
                if (OriginPortTrack != null && !string.IsNullOrEmpty(OriginPortTrack.dateTime) && !tn.DepartTime.HasValue)
                {
                    //没有交航时间，额外补一条轨迹,补一条
                    if (!tn.TrafficTime.HasValue)
                    {
                        g.List.Add(new EsLogisticsTrackingLog
                        {
                            CreateTime = DateTime.Now,
                            TriggerTime = Convert.ToDateTime(OriginPortTrack.dateTime).AddHours(-8),
                            Status = 4,
                            Location = "广州",
                            OrderId = tn.OrderId,
                            ChannelId = 0,
                            TrackingNumber = tn.TrackingNumber,
                            StatusString = "Arrived Export Airport.",//写死轨迹信息
                            Uid = Guid.NewGuid().ToString()
                        });
                    }
                    g.List.Add(new EsLogisticsTrackingLog
                    {
                        CreateTime = DateTime.Now,
                        TriggerTime = Convert.ToDateTime(OriginPortTrack.dateTime),
                        Status = 4,
                        Location = "广州",
                        OrderId = tn.OrderId,
                        ChannelId = 0,
                        TrackingNumber = tn.TrackingNumber,
                        StatusString = "Item is ready for departure.",//写死轨迹信息
                        Uid = Guid.NewGuid().ToString()
                    });
                }
                if (DestinationTrack != null && !string.IsNullOrEmpty(DestinationTrack.dateTime))
                {
                    g.List.Add(new EsLogisticsTrackingLog
                    {
                        CreateTime = DateTime.Now,
                        TriggerTime = Convert.ToDateTime(DestinationTrack.dateTime),
                        Status = 5,
                        Location = tn.ReceiverCountryCode,
                        OrderId = tn.OrderId,
                        ChannelId = 0,
                        TrackingNumber = tn.TrackingNumber,
                        StatusString = "Item is being presented to customs.",//写死轨迹信息
                        Uid = Guid.NewGuid().ToString()
                    });
                }
                if (g.List.Count > 0) { insertLogs.Add(g); }
            }
            return insertLogs;
        }
        private async Task EsTrackUpdate(List<Logistics_TraceGroup> logistics)
        {
            bool isSuccess = await _firstDB.InsertAndUpdate(logistics);
        }
        //发送轨迹日志
        private TrackingEventType ToEventCode(int code)
        {
            switch (code)
            {
                case 5:
                    return TrackingEventType.Arrive;
                case 4:
                    return TrackingEventType.Departure;
                case 6:
                    return TrackingEventType.Clearance;
                default:
                    throw new Exception("转换失败");
            }
        }
        private void ExecuteSendLogs(List<Logistics_TraceGroup> logistics, List<WaitQueryData> queryDatas)
        {
            if (queryDatas.Count > 0)
            {
                List<string> CustomerOrderNoS = queryDatas.Select(x => x.CustomerOrderNo).Distinct().ToList();
                var apiData = logistics.Where(x => CustomerOrderNoS.Contains(x.CustomerOrderNo)).ToList();
                var logs2 = apiData.SelectMany(x => x.List.Select(y => new
                {
                    CustomerNo = x.CustomerOrderNo,
                    TrackingEventType = ToEventCode(y.Status),
                    Key = x.CustomerOrderNo + "_" + ToEventCode(y.Status),
                    EventLoaction = y.Location,
                    EventContent = y.StatusString,
                    EventTime = y.TriggerTime,
                    GetTime = DateTime.Now.ToString_yyyyMMddHHmmss()
                }));
                for (int i = 0; i < Math.Ceiling(logs2.Count() / 120.0); i++)
                {
                    var logs3 = logs2.Skip(120 * i).Take(120).ToList();
                    SendXiKeLogsApi(logs3);
                }
            }
        }
        private static void SendXiKeLogsApi(dynamic dynamic)
        {
            RestSharp.RestClient rc = new RestSharp.RestClient("http://120.24.149.148:9520/");
            RestSharp.RestRequest rq = new RestSharp.RestRequest("/api/TrackingLog/AddLog", RestSharp.Method.POST);
            rq.AddHeader("Authorization", "token 132A7468DE079C6CEB59F383A661E612");

            if (dynamic.Count > 0)
            {
                rq.AddJsonBody(dynamic);
                //最长30秒超时
                rq.Timeout = 30 * 1000;
                var res = rc.Execute(rq);
                if (res.StatusCode == System.Net.HttpStatusCode.OK)
                {

                }
                else
                {
                    throw new Exception(res.ErrorMessage ?? res.Content);
                }
            }
        }
        #endregion
        #endregion
        #region  头程轨迹匹配规则
        public async Task<long> AddFirstTrackMatchRule(Logistics_FirstTrackMatch dto)
        {
            if ((await _unitOfWork.IsExistsAsync<Logistics_FirstTrackMatch>(x => x.LadingCode == dto.LadingCode && x.status == dto.status))) { throw new Exception("已存在相同提单号与状态的规则，不能重复添加"); }
            long Id = await _unitOfWork.InsertAsync(dto);
            return Id;
        }
        public async Task<bool> DeleteFirstTrackMatchRule(int Id)
        {
            bool result = await _unitOfWork.DeleteByIdAsync<Logistics_FirstTrackMatch>(Id);
            return result;
        }
        public async Task<bool> UpdateFirstTrackMatchRule(Logistics_FirstTrackMatch dto)
        {
            if ((await _unitOfWork.IsExistsAsync<Logistics_FirstTrackMatch>(x => x.LadingCode == dto.LadingCode && x.status == dto.status && x.Id != dto.Id))) { throw new Exception("已存在相同提单号与状态的规则，不能重复添加"); }
            bool result = await _unitOfWork.UpdateAsync(dto);
            return result;
        }
        public async Task<PageResult<Logistics_FirstTrackMatch>> GetFirstTrackMatchRules(QueryModel queryModel)
        {
            var pageResult = await _unitOfWork.GetPagingListAsync<Logistics_FirstTrackMatch>(queryModel);
            return pageResult;
        }
        public async Task<List<Logistics_FirstTrackMatch>> GetAllFirstTrackMatchRules(List<string> LadingCode)
        {
            string sqlWhere = string.Empty;
            if (LadingCode != null && LadingCode.Count > 0) { sqlWhere = $" and LadingCode in @LadingCode "; }
            string sql = "select *from Logistics_FirstTrackMatch where 1=1 " + sqlWhere;
            var data = await _unitOfWork.QueryBySqlAsync<Logistics_FirstTrackMatch>(sql, null, new { LadingCode = LadingCode });
            return data.ToList();
        }
        public async Task<NodeTimeData> GetNodeTime(NodeTimeData data)
        {
            var dbData = (await _unitOfWork.QueryAsync<Logistics_LadingBill>(x => x.LadingBillNumber == data.LadingBillNumber));
            var trackData = await QueryBy51Track(data.LadingBillNumber);
            var prot1 = dbData?.FirstOrDefault()?.Destination;
            var prot2 = dbData?.FirstOrDefault()?.OriginPort;
            var track_infos = trackData.data.ladingInfo.return_data.flight_Infos;
            if (string.IsNullOrEmpty(prot1) || string.IsNullOrEmpty(prot2)) { throw new Exception("提单号不正确，或提单口岸信息不全，无法匹配查询！"); }
            if (track_infos == null || track_infos.Count == 0) { throw new Exception("提单起飞落地时间尚未更新，请过段时间再查！"); }
            var OriginPortTrack = track_infos.Where(x => prot2.Trim() == x.depart_station.Trim())?.OrderBy(x => Convert.ToDateTime(x?.depart_time?.Trim()))?.FirstOrDefault();
            var DestinationTrack = track_infos.Where(x => prot1.Trim() == x.arrival_station.Trim())?.OrderBy(x => Convert.ToDateTime(x?.arrival_time?.Trim()))?.FirstOrDefault();
            if (OriginPortTrack != null && !string.IsNullOrEmpty(OriginPortTrack.depart_time))
            {
                data.Code1Time = OriginPortTrack.depart_time;
            }
            if (DestinationTrack != null && !string.IsNullOrEmpty(DestinationTrack.arrival_time))
            {
                data.Code2Time = DestinationTrack.arrival_time;
            }
            return data;
        }
        #endregion
        #region 企微发送消息
        public static void sendMessageToWeChat(string jobName, string message, List<string> WeChatId)
        {
            int count = 0; bool isSucess = false;
            do
            {
                try
                {
                    var client = new RestClient("http://120.24.149.148:9505/WarningNotice/NoticeTextToUser");
                    client.Timeout = -1;
                    var request = new RestRequest(Method.POST);
                    request.AddHeader("Content-Type", "application/json");
                    var weChatModel = new
                    {
                        ServiceType = "微信",
                        TargetName = jobName,
                        Content = "【" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "】 " + (!string.IsNullOrEmpty(message) ? "执行结果，" + message : "定时任务完成"),
                        UserId = WeChatId,
                    };

                    var body = JsonConvert.SerializeObject(weChatModel);
                    request.AddParameter("application/json", body, ParameterType.RequestBody);
                    IRestResponse response = client.Execute(request);
                    if (response.StatusCode == System.Net.HttpStatusCode.OK)
                    {
                        isSucess = true;
                    }
                    else
                    {
                        count++;
                    }
                }
                catch (Exception ex)
                {
                    count++;
                }
                Console.WriteLine($"【{jobName}】企微消息发送：{(isSucess ? "成功" : "失败")}");
                //睡眠再继续
                if (count <= 3 && !isSucess) { Thread.Sleep(TimeSpan.FromSeconds(30)); }
            }
            while (count <= 3 && !isSucess);
        }
        #endregion
    }
}