using Dapper;
using OfficeOpenXml;
using OfficeOpenXml.Table;
using Quartz;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Common.Standard;
namespace XYY.Tool.TimingTask.jobs
{
    public class SendOnlineOutTimeJob : IJob
    {
        public async Task Execute(IJobExecutionContext context)
        {
            try
            {

                string sql = @"select 
								   c.TrackingNumber[运单号],
								   a.ClearanceTime[清关时间],
								   d.TargetTime[轨迹最后更新时间],
								   d.LastGetTime[轨迹最后拉取时间],
								   d.LastMessage[最后轨迹内容],
								   a.LadingBillNumber[提单号],
								   e.PublicName[服务渠道],
								   g.Name[下单渠道],
								   d.Status[状态]
								   from Logistics_LadingBill(nolock)a 
							join Logistics_Box(nolock)b on a.LadingBillNumber=b.LadingBillNumber
							join Logistics_BoxDetail(nolock)c on c.BoxId=b.Id
							join Logistics_Trace(Nolock)d on d.OrderId=c.OrderId 
							join Logistics_Channel(nolock) e on e.Id = d.ChannelId
							join Logistics_Channel(nolock) f on f.id = e.PublicChanneId
							join Logistics_Public(nolock)g on g.Code=f.PublicCode
							where a.ClearanceTime is not null  
							and d.OnlineTime is null and DATEDIFF(HOUR, a.ClearanceTime,getdate())>48
							and DATEDIFF(MONTH,a.ClearanceTime,getdate())<2
							";
                string excelPath = System.IO.Path.Combine(
                 System.AppContext.BaseDirectory, $"行运易上网异常监控-{DateTime.Now.ToString_yyyyMMdd()}.xlsx");
                using (var conn = ConnectionHelper.BuildDbConnection("Server=rm-wz96o72qd45w3d6mvyo.sqlserver.rds.aliyuncs.com,3433;Pooling=true;Max Pool Size=40000;Min Pool Size=0;initial catalog=xyy_wms;uid=xyy;pwd=SsAGn%SK*Ydjeibw;"))
                {
                    conn.Open();
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    var data = conn.Query<OutModel>(sql, null);

                    FileStream existingFile = new FileStream(excelPath, FileMode.Create);
                    using (existingFile)
                    {
                        using (ExcelPackage package = new ExcelPackage(existingFile))
                        {
                            ExcelWorksheet worksheetIn = package.Workbook.Worksheets.Add("Sheet1");
                            // 第二参数为true 则会把 lstData定义的属性名称作为excel标题
                            worksheetIn.Cells.LoadFromCollection(data, true, TableStyles.Medium9);
                            worksheetIn.Cells[worksheetIn.Dimension.Address].AutoFitColumns();
                            package.Save(); //Save the workbook. 
                        }

                    }
                }

                //发送文件
                var hook = new QYWXWebHook("https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=804910e8-c06b-46ec-a460-7e63cc1a9d89");
                string mid = await hook.PostFile(excelPath);
                hook.SendFile(mid);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }
    }

    public class OutModel
    {
        public string 下单渠道
        {
            get; set;
        }

        public string 状态
        {
            get; set;
        }

        public string 服务渠道
        {
            get; set;
        }

        public string 运单号
        {
            get; set;
        }

        public string 清关时间
        {
            get; set;
        }

        public string 轨迹最后更新时间
        {
            get; set;
        }

        public string 轨迹最后拉取时间
        {
            get; set;
        }

        public string 最后轨迹内容
        {
            get; set;
        }

        public string 提单号
        {
            get; set;
        }

    }
}