XYY.Core/3.Service/XYY.ServiceStandard.TrackingService/TraceSupplementLogService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Data.Model.Standard.Tracking;
using Dapper;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;
using System.ComponentModel;
using XYY.Data.Model.Standard.Tracking.TrackingTraces;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using System.IO;

namespace XYY.Service.Standard.TrackingService
{
    public interface ITraceSupplementLogService
    {
        Task<PageResult<Logistics_Trace_SupplementLogView>> QueryData(QueryModel queryModel);
        Task<byte[]> DownloadSupplementLog(QueryModel queryModel);
        Task<byte[]> DownloadLogs(QueryModel queryModel);
    }
    public class TraceSupplementLogService : ITraceSupplementLogService
    {
        private readonly IUnitOfWork _unitOfWork;
        public TraceSupplementLogService(IUnitOfWork unitOfWork)
        {
            this._unitOfWork = unitOfWork;
        }
        public async Task<PageResult<Logistics_Trace_SupplementLogView>> QueryData(QueryModel queryModel)
        {
            string sql = "select * from Logistics_Trace_SupplementLog(nolock)";
            return await _unitOfWork.GetPagingListAsync<Logistics_Trace_SupplementLogView>(queryModel, sql);
        }
        public async Task<byte[]> DownloadSupplementLog(QueryModel queryModel)
        {
            string sql = "select * from Logistics_Trace_SupplementLog(nolock) where 1=1" + _unitOfWork.GetWhere(queryModel.QueryParamer);
            var list = await _unitOfWork.QueryBySqlAsync<Logistics_Trace_SupplementLogView>(sql);
            MSExcelHelper excelHelper = new MSExcelHelper();
            var data = excelHelper.OutDataAsByte<D_SupplementLogView, Logistics_Trace_SupplementLogView>(list.ToList());
            return data;
        }
        private class D_SupplementLogView : MSExcelClassMapping<Logistics_Trace_SupplementLogView>
        {
            public D_SupplementLogView()
            {
                Map(x => x.TrackingNumber).Name("跟踪单号");
                Map(x => x.JudgmentNodeStr).Name("补充节点");
                Map(x => x.TrackSupplementLocationStr).Name("补充位置");
                Map(x => x.TrackSupplementTimeStr).Name("补充时间");
                Map(x => x.TrackSupplementText).Name("补充内容");
                Map(x => x.IsSuccessStr).Name("执行结果");
                Map(x => x.ErrorMsg).Name("错误原因");
            }
        }
        #region 下载轨迹日志
        public async Task<byte[]> DownloadLogs(QueryModel queryModel)
        {
            string excelPath = System.IO.Path.Combine(
      System.AppContext.BaseDirectory, DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            var orders = await GetOrderInfo(queryModel);
            //文件保存到相应的地址
            await GenerateLogsFile(orders, excelPath);
            var bytes = File.ReadAllBytes(excelPath);
            File.Delete(excelPath);
            return bytes;

        }
        private async Task<List<dynamic>> GetOrderInfo(QueryModel queryModel)
        {
            string where = _unitOfWork.GetWhere(queryModel.QueryParamer);
            string sql = $@"select a.CustomerOrderNo,a.TrackingNumber,a.SystemNo,c.LadingBillNumber
                            from order_order(nolock)a 
                                join logistics_boxDetail(nolock)b on a.id=b.orderId 
                                join logistics_box(nolock)c on c.Id=b.BoxId
                where a.CreateOrderChannelId=109 " + where;
            List<dynamic> orders = (await _unitOfWork.QueryBySqlAsync<dynamic>(sql)).ToList();
            if (orders != null && orders.Count > 0) { return orders; } else { throw new Exception("没有订单数据可被执行！"); }

        }
        private async Task GenerateLogsFile(List<dynamic> orders, string excelPath)
        {
            ESRequest request = new ESRequest("http://120.24.149.148:9526", "l_log", "log", "mget");

            string strData = request.ExecuteQeury("{\"ids\":[" + string.Join(",", orders.Select(x => "\"" + x.CustomerOrderNo + "_" + TrackingEventType.InStock + "\"")) + "]}");
            var list = Newtonsoft.Json.JsonConvert.DeserializeObject<TrackingTraceDoc>(strData);
            List<TrackingMonitor> logs = new List<TrackingMonitor>();

            Dictionary<TrackingEventType, List<int>> ErrorRowIndexs = new Dictionary<TrackingEventType, List<int>>();
            int index = 1;
            ErrorRowIndexs.Add(TrackingEventType.InStock, new List<int>());

            foreach (var item in list.docs)
            {
                if (item.found)
                {
                    string customerOrderNO = item._id.Split('_')[0];
                    TrackingMonitor trackingMonitor = new TrackingMonitor();
                    trackingMonitor.InStockGetTime = item._source.GetTime;
                    trackingMonitor.InStockSendTime = item._source.SendTime;
                    trackingMonitor.InStockTargetTime = item._source.EventTime;
                    trackingMonitor.InStockSendRetMsg = item._source.SendMessage;
                    trackingMonitor.CustomerOrderNO = customerOrderNO;
                    trackingMonitor.RowIndex = index;
                    var s = orders.FirstOrDefault(x => x.CustomerOrderNo == customerOrderNO);
                    if (s != null)
                    {
                        trackingMonitor.SystemNo = s.SystemNo;
                        trackingMonitor.TrackingNumber = s.TrackingNumber;
                        trackingMonitor.LadingBillNumber = s.LadingBillNumber;
                    }

                    if (trackingMonitor.InStockTargetTime != null
                        && trackingMonitor.InStockSendTime != null
                        && (trackingMonitor.InStockSendTime - trackingMonitor.InStockTargetTime).Value.TotalHours > 24
                        )
                    {
                        ErrorRowIndexs[TrackingEventType.InStock].Add(trackingMonitor.RowIndex);
                    }
                    logs.Add(trackingMonitor);
                    index++;
                }
            }

            ErrorRowIndexs.Add(TrackingEventType.OutStock, new List<int>());
            string strData2 = request.ExecuteQeury("{\"ids\":[" + string.Join(",", orders.Select(x => "\"" + x.CustomerOrderNo + "_" + TrackingEventType.OutStock + "\"")) + "]}");
            var list2 = Newtonsoft.Json.JsonConvert.DeserializeObject<TrackingTraceDoc>(strData2);
            foreach (var item in list2.docs)
            {
                if (item.found)
                {
                    string customerOrderNO = item._id.Split('_')[0];
                    TrackingMonitor trackingMonitor = logs.FirstOrDefault(x => x.CustomerOrderNO == customerOrderNO);
                    if (trackingMonitor != null)
                    {
                        trackingMonitor.OutStockGetTime = item._source.GetTime;
                        trackingMonitor.OutStockSendTime = item._source.SendTime;
                        trackingMonitor.OutStockTargetTime = item._source.EventTime;
                        trackingMonitor.OutStockSendRetMsg = item._source.SendMessage;

                        if (trackingMonitor.OutStockTargetTime != null
                            && trackingMonitor.OutStockSendTime != null
                            && (trackingMonitor.OutStockSendTime - trackingMonitor.OutStockTargetTime).Value.TotalHours > 24
                            )
                        {
                            ErrorRowIndexs[TrackingEventType.OutStock].Add(trackingMonitor.RowIndex);
                        }
                    }
                }
            }

            ErrorRowIndexs.Add(TrackingEventType.Traffic, new List<int>());
            string strData3 = request.ExecuteQeury("{\"ids\":[" + string.Join(",", orders.Select(x => "\"" + x.CustomerOrderNo + "_" + TrackingEventType.Traffic + "\"")) + "]}");
            var list3 = Newtonsoft.Json.JsonConvert.DeserializeObject<TrackingTraceDoc>(strData3);
            foreach (var item in list3.docs)
            {
                if (item.found)
                {
                    string customerOrderNO = item._id.Split('_')[0];
                    TrackingMonitor trackingMonitor = logs.FirstOrDefault(x => x.CustomerOrderNO == customerOrderNO);
                    if (trackingMonitor != null)
                    {
                        trackingMonitor.TrafficGetTime = item._source.GetTime;
                        trackingMonitor.TrafficSendTime = item._source.SendTime;
                        trackingMonitor.TrafficTargetTime = item._source.EventTime;
                        trackingMonitor.TrafficSendRetMsg = item._source.SendMessage;
                    }

                    if (trackingMonitor.TrafficTargetTime != null
                     && trackingMonitor.TrafficSendTime != null
                     && (trackingMonitor.TrafficSendTime - trackingMonitor.TrafficTargetTime).Value.TotalHours > 24
                     )
                    {
                        ErrorRowIndexs[TrackingEventType.Traffic].Add(trackingMonitor.RowIndex);
                    }
                }
            }


            ErrorRowIndexs.Add(TrackingEventType.Departure, new List<int>());
            string strData4 = request.ExecuteQeury("{\"ids\":[" + string.Join(",", orders.Select(x => "\"" + x.CustomerOrderNo + "_" + TrackingEventType.Departure + "\"")) + "]}");
            var list4 = Newtonsoft.Json.JsonConvert.DeserializeObject<TrackingTraceDoc>(strData4);
            foreach (var item in list4.docs)
            {
                if (item.found)
                {
                    string customerOrderNO = item._id.Split('_')[0];
                    TrackingMonitor trackingMonitor = logs.FirstOrDefault(x => x.CustomerOrderNO == customerOrderNO);
                    if (trackingMonitor != null)
                    {
                        trackingMonitor.DepartureGetTime = item._source.GetTime;
                        trackingMonitor.DepartureSendTime = item._source.SendTime;
                        trackingMonitor.DepartureTargetTime = item._source.EventTime;
                        trackingMonitor.DepartureSendRetMsg = item._source.SendMessage;
                    }

                    if (trackingMonitor.DepartureTargetTime != null
                        && trackingMonitor.DepartureSendTime != null
                        && (trackingMonitor.DepartureSendTime - trackingMonitor.DepartureTargetTime).Value.TotalHours > 24
                        )
                    {
                        ErrorRowIndexs[TrackingEventType.Departure].Add(trackingMonitor.RowIndex);
                    }

                }
            }


            ErrorRowIndexs.Add(TrackingEventType.Arrive, new List<int>());
            string strData5 = request.ExecuteQeury("{\"ids\":[" + string.Join(",", orders.Select(x => "\"" + x.CustomerOrderNo + "_" + TrackingEventType.Arrive + "\"")) + "]}");
            var list5 = Newtonsoft.Json.JsonConvert.DeserializeObject<TrackingTraceDoc>(strData5);
            foreach (var item in list5.docs)
            {
                if (item.found)
                {
                    string customerOrderNO = item._id.Split('_')[0];
                    TrackingMonitor trackingMonitor = logs.FirstOrDefault(x => x.CustomerOrderNO == customerOrderNO);
                    if (trackingMonitor != null)
                    {
                        trackingMonitor.ArriveGetTime = item._source.GetTime;
                        trackingMonitor.ArriveSendTime = item._source.SendTime;
                        trackingMonitor.ArriveTargetTime = item._source.EventTime;
                        trackingMonitor.ArriveSendRetMsg = item._source.SendMessage;
                    }

                    if (trackingMonitor.ArriveTargetTime != null
                        && trackingMonitor.ArriveSendTime != null
                        && (trackingMonitor.ArriveSendTime - trackingMonitor.ArriveTargetTime).Value.TotalHours > 24
                        )
                    {
                        ErrorRowIndexs[TrackingEventType.Arrive].Add(trackingMonitor.RowIndex);
                    }
                }
            }

            ErrorRowIndexs.Add(TrackingEventType.Clearance, new List<int>());
            string strData6 = request.ExecuteQeury("{\"ids\":[" + string.Join(",", orders.Take(100).Select(x => "\"" + x.CustomerOrderNo + "_" + TrackingEventType.Clearance + "\"")) + "]}");
            var list6 = Newtonsoft.Json.JsonConvert.DeserializeObject<TrackingTraceDoc>(strData6);
            foreach (var item in list6.docs)
            {
                if (item.found)
                {
                    string customerOrderNO = item._id.Split('_')[0];
                    TrackingMonitor trackingMonitor = logs.FirstOrDefault(x => x.CustomerOrderNO == customerOrderNO);
                    if (trackingMonitor != null)
                    {
                        trackingMonitor.ClearanceGetTime = item._source.GetTime;
                        trackingMonitor.ClearanceSendTime = item._source.SendTime;
                        trackingMonitor.ClearanceTargetTime = item._source.EventTime;
                        trackingMonitor.ClearanceSendRetMsg = item._source.SendMessage;
                    }

                    if (trackingMonitor.ClearanceTargetTime != null
                        && trackingMonitor.ClearanceSendTime != null
                        && (trackingMonitor.ClearanceSendTime - trackingMonitor.ClearanceTargetTime).Value.TotalHours > 24
                        )
                    {
                        ErrorRowIndexs[TrackingEventType.Clearance].Add(trackingMonitor.RowIndex);
                    }
                }
            }


            ErrorRowIndexs.Add(TrackingEventType.Online, new List<int>());
            string strData7 = request.ExecuteQeury("{\"ids\":[" + string.Join(",", orders.Select(x => "\"" + x.CustomerOrderNo + "_" + TrackingEventType.Online + "\"")) + "]}");
            var list7 = Newtonsoft.Json.JsonConvert.DeserializeObject<TrackingTraceDoc>(strData7);
            foreach (var item in list7.docs)
            {
                if (item.found)
                {
                    string customerOrderNO = item._id.Split('_')[0];
                    TrackingMonitor trackingMonitor = logs.FirstOrDefault(x => x.CustomerOrderNO == customerOrderNO);
                    if (trackingMonitor != null)
                    {
                        trackingMonitor.OnlineGetTime = item._source.GetTime;
                        trackingMonitor.OnlineSendTime = item._source.SendTime;
                        trackingMonitor.OnlineTargetTime = item._source.EventTime;
                        trackingMonitor.OnlineSendRetMsg = item._source.SendMessage;
                    }
                    if (trackingMonitor?.OnlineTargetTime != null
                        && trackingMonitor?.OnlineSendTime != null
                        && (trackingMonitor?.OnlineSendTime - trackingMonitor?.OnlineTargetTime).Value.TotalHours > 24
                        )
                    {
                        ErrorRowIndexs[TrackingEventType.Online].Add(trackingMonitor.RowIndex);
                    }

                }
            }

            ErrorRowIndexs.Add(TrackingEventType.Sign, new List<int>());
            string strData8 = request.ExecuteQeury("{\"ids\":[" + string.Join(",", orders.Select(x => "\"" + x.CustomerOrderNo + "_" + TrackingEventType.Sign + "\"")) + "]}");
            var list8 = Newtonsoft.Json.JsonConvert.DeserializeObject<TrackingTraceDoc>(strData8);
            foreach (var item in list8.docs)
            {
                if (item.found)
                {
                    string customerOrderNO = item._id.Split('_')[0];
                    TrackingMonitor trackingMonitor = logs.FirstOrDefault(x => x.CustomerOrderNO == customerOrderNO);
                    if (trackingMonitor != null)
                    {
                        trackingMonitor.SignGetTime = item._source.GetTime;
                        trackingMonitor.SignSendTime = item._source.SendTime;
                        trackingMonitor.SignTargetTime = item._source.EventTime;
                        trackingMonitor.SignSendRetMsg = item._source.SendMessage;
                    }

                    if (trackingMonitor.SignTargetTime != null
                        && trackingMonitor.SignSendTime != null
                        && (trackingMonitor.SignSendTime - trackingMonitor.SignTargetTime).Value.TotalHours > 24
                        )
                    {
                        ErrorRowIndexs[TrackingEventType.Sign].Add(trackingMonitor.RowIndex);
                    }


                }
            }
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;

            FileStream existingFile = new FileStream(excelPath, FileMode.Create);
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {

                // 邮编	区 
                ExcelWorksheet worksheetIn = package.Workbook.Worksheets.Add("Sheet1");
                // 第二参数为true 则会把 lstData定义的属性名称作为excel标题
                worksheetIn.Cells.LoadFromCollection(logs, true, TableStyles.Medium13);
                var color = System.Drawing.ColorTranslator.FromHtml("#FFE699");
                int glaStart = 5;
                foreach (var item in ErrorRowIndexs)
                {
                    int cStart = glaStart + ((int)item.Key - 1) * 4 + 1;
                    foreach (var r in item.Value)
                    {
                        worksheetIn.Cells[r + 1, cStart].Style.Fill.PatternType = ExcelFillStyle.Solid;
                        worksheetIn.Cells[r + 1, cStart + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                        worksheetIn.Cells[r + 1, cStart + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
                        worksheetIn.Cells[r + 1, cStart + 3].Style.Fill.PatternType = ExcelFillStyle.Solid;
                        worksheetIn.Cells[r + 1, cStart].Style.Fill.BackgroundColor.SetColor(color);
                        worksheetIn.Cells[r + 1, cStart + 1].Style.Fill.BackgroundColor.SetColor(color);
                        worksheetIn.Cells[r + 1, cStart + 2].Style.Fill.BackgroundColor.SetColor(color);
                        worksheetIn.Cells[r + 1, cStart + 3].Style.Fill.BackgroundColor.SetColor(color);
                    }
                }
                foreach (var item in ErrorRowIndexs.Keys)
                {
                    int cStart = glaStart + ((int)item - 1) * 4 + 1;
                    worksheetIn.Column(cStart).Width = 15;
                    worksheetIn.Column(cStart + 1).Width = 15;
                    worksheetIn.Column(cStart + 2).Width = 15;
                    worksheetIn.Column(cStart).Style.Numberformat.Format = "MM-dd HH:mm";
                    worksheetIn.Column(cStart + 1).Style.Numberformat.Format = "MM-dd HH:mm";
                    worksheetIn.Column(cStart + 2).Style.Numberformat.Format = "MM-dd HH:mm";


                }
                worksheetIn.View.FreezePanes(1, 6);
                worksheetIn.Column(2).Width = 30;
                worksheetIn.Column(3).Width = 20;
                worksheetIn.Column(4).Width = 35;
                worksheetIn.Column(5).Width = 20;
                package.Save(); //Save the workbook.
            }
            existingFile.Close();
        }
        #endregion
    }
}