using Cksoft.Data;
using Cksoft.Data.Repository;
using Cksoft.Unity;
using DllEapDal;
using DllEapDal.OFILM;
using DllEapEntity;
using DllEapEntity.OFILM;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace DllEapBll
{
    public class MailNotice
    {
        private static ILogger myloger = AppConfigurtaionServices.MyLog;

        public static async Task<string> DisconnectionNotice(int type)
        {
            string toMail = "";
            string ccMail = "";
            StringBuilder body = new StringBuilder();
            MailSettings mail = null;
            List<FileAttachment> file = new List<FileAttachment>();
            using (IDatabase db = DbFactory.Base("eapslave"))
            {
                var dal = new MailSettingsDal(db);
                string code = AppConfigurtaionServices.Configuration["DisConnNoticeCode"];
                mail = dal.GetMailSettingsByCode(code);
                if (mail == null)
                {
                    return "邮件代码不存在";
                }
                List<MailReceiver> receiverList = dal.GetMailReceiverByMailId(mail.ID).ToList();
                if (receiverList == null || !receiverList.Any())
                {
                    return "未设置收件人";
                }
                toMail = receiverList.Where(r => r.SendType == 1).Select(r => r.MailAddress).Join(";");
                ccMail = receiverList.Where(r => r.SendType == 2).Select(r => r.MailAddress).Join(";");
                //List<MacStatus01> macList = new MacStatusDal(db).GetMacStatus01ByStatusID(1).ToList();
                var dalMac = new MacRecipeDal(db);
                string errorinfo = string.Empty;
                int total;
                var macList = dalMac.GetMacRecipeDtos("", 1, 10000, "MacCode", "asc", out total, ref errorinfo);
                macList = macList.Where(m => !string.IsNullOrEmpty(m.Factory) && !m.Factory.Contains("临时区域"));
                if (macList == null || macList.Count() == 0)
                {
                    return "未查询到设备";
                }
                body.Append($"<h3>{mail.MailBody}</h3>");
                //body.Append($"<h3>{DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd")}</h3>");
                body.Append(@"<table cellpadding='3' style='border-collapse: collapse; border: 1px solid black'>
                                <tr>
                                    <th style='border: 1px solid black'>园区</th>
                                    <th style='border: 1px solid black'>设备类型</th>
                                    <th style='border: 1px solid black'>总数量</th>
                                    <th style='border: 1px solid black'>离线数量</th>
                                    <th style='border: 1px solid black'>离线率</th>
                                </tr>");
                var factoryList = macList.GroupBy(m => m.Factory);
                foreach (var f in factoryList)
                {
                    //每个园区所有状态按机型分组
                    var pCodeList = macList.Where(m => m.Factory == f.Key).GroupBy(m => m.PCode);
                    //每个园区离线状态按机型分组
                    var pCodeDisConnList = macList.Where(m => m.Factory == f.Key && m.StatusID == 1).GroupBy(m => m.PCode);
                    int i = 0;
                    int totalCount = 0;
                    int disConnTotalCount = 0;
                    foreach (var p in pCodeList)
                    {
                        //每个机型的总数
                        int count = p.Count();
                        //每个机型离线的总数
                        int disConnCount = pCodeDisConnList.Where(m => m.Key == p.Key).Any() ? pCodeDisConnList.Where(m => m.Key == p.Key).First().Count() : 0;
                        if (i == 0)
                        {
                            body.Append($@"<tr><td rowspan='{pCodeList.Count() + 1}' style='border: 1px solid black'>{f.Key}</td>
                                                <td style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
                                            </tr>");
                        }
                        else
                        {
                            body.Append($@"<tr>
                                                <td style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
                                            </tr>");
                        }
                        totalCount += count;
                        disConnTotalCount += disConnCount;
                        i++;
                    }
                    body.Append($@"<tr><td style='border: 1px solid black'>总计</td>
                                       <td style='border: 1px solid black'>{totalCount}</td>
                                       <td style='border: 1px solid black'>{disConnTotalCount}</td>
                                        <td style='border: 1px solid black'>{(Convert.ToDouble(disConnTotalCount) * 100 / Convert.ToDouble(totalCount)).ToString("f2")}%</td>
                                  </tr>");
                }
                body.Append("</table>");
                var macListDisConn = macList.Where(m => !string.IsNullOrEmpty(m.Factory) && m.StatusID == 1).OrderBy(m => m.Factory).OrderBy(m => m.MacCode);
                if (macListDisConn.Any())
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("设备ID,设备类型,当前IP,MAC地址,园区,楼层,最新程序名称,连接状态");
                    sb.AppendLine();
                    foreach (var mac in macListDisConn)
                    {
                        sb.Append($"{mac.MacCode},{mac.PCode},{mac.RealIp},{mac.MacAddress},{mac.Factory},{mac.Floor},{mac.ProgramName},{mac.StatusName}");
                        sb.AppendLine();
                    }
                    if (type == 1)
                    {
                        MemoryStream stream = new MemoryStream();
                        StreamWriter write = new StreamWriter(stream, Encoding.UTF8);
                        write.Write(sb.ToString());
                        write.Flush();
                        write.Close();
                        write.Dispose();
                        file.Add(new FileAttachment
                        {
                            FileContent = stream.ToArray(),
                            FileName = DateTime.Today.ToString("yyyy-MM-dd") + mail.MailSubject + ".xls",
                        });
                    }
                    else
                    {
                        string path = mail.MailSubject + ".xls";
                        FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write);
                        StreamWriter write = new StreamWriter(fs, Encoding.UTF8);
                        write.Write(sb.ToString());
                        write.Close();
                        fs.Close();
                        FileInfo fileInfo = new FileInfo(path);
                        file.Add(new FileAttachment
                        {
                            Stream = fileInfo.OpenRead(),
                            FileName = mail.MailSubject + ".xls",
                        });
                    }
                }
            }
            string errorinfoMail = "";
            if (type == 1)
            {
                myloger.LogError($"机台断线邮件发送开始");
                SendMail(mail.MailServer, mail.MailPort, mail.MailAccount, mail.MailPwd, toMail, ccMail, mail.MailSubject, body.ToString(), ref errorinfoMail, "", file, true);
                myloger.LogError($"机台断线邮件发送结束{errorinfoMail}");
                //SendMail("smtp.qq.com", "25", "404808639@qq.com", "kzxrczpnanyycbac", "404808639@qq.com", "", "机台断网邮件监控", errorinfoMail, ref errorinfoMail, "", file, true);

            }
            else
            {
                SendByMailKit(mail.MailServer, mail.MailPort, mail.MailAccount, mail.MailPwd, toMail, ccMail, mail.MailSubject, body.ToString(), ref errorinfoMail, file);
            }
            return errorinfoMail;
        }

        /// <summary>
        /// 机台系统IP与实际IP不一致邮件提醒
        /// </summary>
        /// <returns></returns>
        public static async Task<string> IPDifferentNotice()
        {
            string toMail = "";
            string ccMail = "";
            StringBuilder body = new StringBuilder();
            MailSettings mail = null;
            List<FileAttachment> file = new List<FileAttachment>();
            using (IDatabase db = DbFactory.Base("eapslave"))
            {
                var dal = new MailSettingsDal(db);
                string code = AppConfigurtaionServices.Configuration["IPDifferentNoticeCode"];
                mail = dal.GetMailSettingsByCode(code);
                if (mail == null)
                {
                    return "邮件代码不存在";
                }
                List<MailReceiver> receiverList = dal.GetMailReceiverByMailId(mail.ID).ToList();
                if (receiverList == null || !receiverList.Any())
                {
                    return "未设置收件人";
                }
                toMail = receiverList.Where(r => r.SendType == 1).Select(r => r.MailAddress).Join(";");
                ccMail = receiverList.Where(r => r.SendType == 2).Select(r => r.MailAddress).Join(";");
                var dalMac = new MachineDal(db);
                var macList = dalMac.GetMachineList();
                macList = macList.Where(m => !string.IsNullOrEmpty(m.FactoryName) && !m.FactoryName.Contains("临时区域"));
                var sql = "select a.id from machine a " +
                    "left join macstatus01 b on a.fcode=b.maccode " +
                    "where b.statusid=204906";
                var shutDownMacIds = db.FindList<string>(sql)?.Select(c => Convert.ToInt32(c));
                if (macList == null || macList.Count() == 0)
                {
                    return "未查询到设备";
                }
                body.Append($"<h3>{mail.MailBody}</h3>");
                body.Append(@"<table cellpadding='3' style='border-collapse: collapse; border: 1px solid black'>
                                <tr>
                                    <th style='border: 1px solid black'>园区</th>
                                    <th style='border: 1px solid black'>设备类型</th>
                                    <th style='border: 1px solid black'>总数量</th>
                                    <th style='border: 1px solid black'>ip不一致数量</th>
                                    <th style='border: 1px solid black'>ip不一致率</th>
                                </tr>");
                var factoryList = macList.GroupBy(m => m.FactoryName);
                foreach (var f in factoryList)
                {
                    //每个园区所有机台按机型分组
                    var pCodeList = macList.Where(m => m.FactoryName == f.Key).GroupBy(m => m.MModeCode);
                    //每个园区ip不一致按机型分组
                    var pCodeDisConnList = macList.Where(m => m.FactoryName == f.Key && (string.IsNullOrEmpty(m.RealIP) || !m.RealIP.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).
                Contains(m.IPAddress)) && !shutDownMacIds.Contains(m.ID)).GroupBy(m => m.MModeCode);
                    int i = 0;
                    int totalCount = 0;
                    int disConnTotalCount = 0;
                    foreach (var p in pCodeList)
                    {
                        //每个机型的总数
                        int count = p.Count();
                        //每个机型离线的总数
                        int disConnCount = pCodeDisConnList.Where(m => m.Key == p.Key).Any() ? pCodeDisConnList.Where(m => m.Key == p.Key).First().Count() : 0;
                        if (i == 0)
                        {
                            body.Append($@"<tr><td rowspan='{pCodeList.Count() + 1}' style='border: 1px solid black'>{f.Key}</td>
                                                <td style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
                                            </tr>");
                        }
                        else
                        {
                            body.Append($@"<tr>
                                                <td style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
                                            </tr>");
                        }
                        totalCount += count;
                        disConnTotalCount += disConnCount;
                        i++;
                    }
                    body.Append($@"<tr><td style='border: 1px solid black'>总计</td>
                                       <td style='border: 1px solid black'>{totalCount}</td>
                                       <td style='border: 1px solid black'>{disConnTotalCount}</td>
                                        <td style='border: 1px solid black'>{(Convert.ToDouble(disConnTotalCount) * 100 / Convert.ToDouble(totalCount)).ToString("f2")}%</td>
                                  </tr>");
                }
                body.Append("</table>");
                var macListDisConn = macList.Where(m => string.IsNullOrEmpty(m.RealIP) || !m.RealIP.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).
                Contains(m.IPAddress))
                    .Where(m => !shutDownMacIds.Contains(m.ID))
                    .OrderBy(m => m.FactoryName).OrderBy(m => m.FCode);
                if (macListDisConn.Any())
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("设备ID,设备名称,设备类型,IP地址,实际IP,MAC地址,园区,楼层");
                    sb.AppendLine();
                    foreach (var mac in macListDisConn)
                    {
                        sb.Append($"{mac.FCode},{mac.FName},{mac.MModeCode},{mac.IPAddress},{(string.IsNullOrEmpty(mac.RealIP) ? "" : mac.RealIP.Replace(",", " | "))},{mac.MacAddress},{mac.FactoryName},{mac.Floor}");
                        sb.AppendLine();
                    }
                    MemoryStream stream = new MemoryStream();
                    StreamWriter write = new StreamWriter(stream, Encoding.UTF8);
                    write.Write(sb.ToString());
                    write.Flush();
                    write.Close();
                    write.Dispose();
                    file.Add(new FileAttachment
                    {
                        FileContent = stream.ToArray(),
                        FileName = DateTime.Today.ToString("yyyy-MM-dd") + mail.MailSubject + ".xls",
                    });
                }
            }
            string errorinfoMail = "";
            myloger.LogError($"机台系统IP与实际IP不一致邮件发送开始");
            SendMail(mail.MailServer, mail.MailPort, mail.MailAccount, mail.MailPwd, toMail, ccMail, mail.MailSubject, body.ToString(), ref errorinfoMail, "", file, true);
            myloger.LogError($"机台系统IP与实际IP不一致邮件发送结束{errorinfoMail}");
            return errorinfoMail;
        }

        /// <summary>
        /// AA抛料率未采集预警
        /// </summary>
        /// <returns></returns>
        public static async Task<string> AAMaterialUploadErrorNotice()
        {
            var startTime = DateTime.Now.AddDays(-2).ToString("yyyy-MM-dd 20:30:00");
            var endTime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 20:30:00");
            var errorinfo = string.Empty;
            string toMail = "";
            string ccMail = "";
            StringBuilder body = new StringBuilder();
            MailSettings mail = null;
            List<FileAttachment> file = new List<FileAttachment>();
            using (IDatabase db = DbFactory.Base("eapslave"))
            {
                var dal = new MailSettingsDal(db);
                string code = AppConfigurtaionServices.Configuration["AAMaterialErrorNotice"];
                mail = dal.GetMailSettingsByCode(code);
                if (mail == null)
                {
                    return "邮件代码不存在";
                }
                List<MailReceiver> receiverList = dal.GetMailReceiverByMailId(mail.ID).ToList();
                if (receiverList == null || !receiverList.Any())
                {
                    return "未设置收件人";
                }
                // 测试
                //receiverList = receiverList.Where(c => c.Name == "韩慧鑫").ToList();
                toMail = receiverList.Where(r => r.SendType == 1).Select(r => r.MailAddress).Join(";");
                ccMail = receiverList.Where(r => r.SendType == 2).Select(r => r.MailAddress).Join(";");
                var macDal = new MachineDal(db);
                var aaMacs = macDal.GetMachineList().Where(c => c.FCode.StartsWith("AA") && !c.FactoryName.Contains("临时区域"));
                if (AppConfigurtaionServices.Configuration["IncludeTrialProduce"] == null
                   || !Convert.ToBoolean(AppConfigurtaionServices.Configuration["IncludeTrialProduce"]))
                {
                    aaMacs = aaMacs.Where(c => !c.PlantName.Contains("试产")
                    && !c.Floor.Contains("试产"));
                }
                var sql = $@"select distinct FCode from maccountdetail a 
                             left join maccountmst b on a.MstID=b.ID
                             left join machine c on b.MacID=c.ID where 1=1
                             and b.SDate>'{startTime}' and b.SDate<'{Convert.ToDateTime(startTime).AddHours(12).ToString("yyyy-MM-dd HH:mm:ss")}' 
                             and a.typeid=0 and a.fcount>0
                             and c.FCode like 'AA%'";
                var macCodesN = db.FindList<string>(sql);
                IEnumerable<Machine> runMacsN = new List<Machine>();
                if (macCodesN != null && macCodesN.Count() > 0)
                    runMacsN = aaMacs.Where(c => macCodesN.Contains(c.FCode));

                sql = $@"select distinct FCode from maccountdetail a 
                             left join maccountmst b on a.MstID=b.ID
                             left join machine c on b.MacID=c.ID where 1=1
                             and b.SDate>'{Convert.ToDateTime(endTime).AddHours(-12).ToString("yyyy-MM-dd HH:mm:ss")}' 
                             and b.SDate<'{endTime}' 
                             and a.typeid=0 and a.fcount>0
                             and c.FCode like 'AA%'";
                var macCodesD = db.FindList<string>(sql);
                IEnumerable<Machine> runMacsD = new List<Machine>();
                if (macCodesD != null && macCodesD.Count() > 0)
                    runMacsD = aaMacs.Where(c => macCodesD.Contains(c.FCode));
                var uploadHistoriesN = new List<MachineMaterialMst>();
                var uploadHistoriesD = new List<MachineMaterialMst>();
                if (macCodesN.Any())
                {
                    // 晚班上传文件记录
                    uploadHistoriesN = (await db.FindListForConditionAsync<MachineMaterialMst>
                        ($" and a.fdate='{Convert.ToDateTime(startTime).ToString("yyyy-MM-dd")}' and a.shift='N' and a.fdate='{Convert.ToDateTime(startTime).ToString("yyyy-MM-dd")}' and b.FCode in ({string.Join(",", macCodesN.Select(c => $"'{c}'"))})",
                        ref errorinfo)).ToList();
                }
                if (macCodesD.Any())
                {
                    uploadHistoriesD = (await db.FindListForConditionAsync<MachineMaterialMst>
                  ($" and a.fdate='{Convert.ToDateTime(endTime).ToString("yyyy-MM-dd")}' and a.shift='D' and a.fdate='{Convert.ToDateTime(endTime).ToString("yyyy-MM-dd")}' and b.FCode in ({string.Join(",", macCodesD.Select(c => $"'{c}'"))})",
                  ref errorinfo)).ToList();
                }
                var notUploadMacsN = new List<Machine>();
                var notUploadMacsD = new List<Machine>();
                foreach (var item in runMacsN)
                {
                    if (uploadHistoriesN.All(c => c.MacId != item.ID))
                    {
                        notUploadMacsN.Add(item);
                    }
                }
                foreach (var item in runMacsD)
                {
                    if (uploadHistoriesD.All(c => c.MacId != item.ID))
                    {
                        notUploadMacsD.Add(item);
                    }
                }

                body.Append($"<h3>{mail.MailBody}</h3>");
                body.Append($@"<table style='border-collapse: collapse; border: 1px solid black'>
                                <tr>
                                    <th style='border: 1px solid black' rowspan='2'>园区</th>
                                    <th style='border: 1px solid black' rowspan='2'>厂房</th>
                                    <th style='border: 1px solid black' rowspan='2'>楼层车间</th>
                                    <th style='border: 1px solid black' rowspan='2'>设备总数</th>
                                    <th style='border: 1px solid black' rowspan='1' colspan='3'>{Convert.ToDateTime(endTime).ToString("MM-dd")}白班</th>
                                    <th style='border: 1px solid black' rowspan='1' colspan='3'>{Convert.ToDateTime(startTime).ToString("MM-dd")}晚班</th>
                                    <th style='border: 1px solid black' rowspan='2'>责任人</th>
                                </tr>
                                <tr>
                                    <th style='border: 1px solid black'>稼动设备数</th>
                                    <th style='border: 1px solid black'>未上传机台数</th>
                                    <th style='border: 1px solid black'>未上传率</th>
                                    <th style='border: 1px solid black'>稼动设备数</th>
                                    <th style='border: 1px solid black'>未上传机台数</th>
                                    <th style='border: 1px solid black'>未上传率</th>
                                    
                                </tr>
                             ");
                var notUploadMacs = notUploadMacsD.Concat(notUploadMacsN).Distinct();

                var regionChargeDal = new RegionChargerDal(db);
                var chargeInfoes = regionChargeDal.Get(string.Empty, string.Empty, string.Empty, string.Empty).Result;

                var factoryList = aaMacs.GroupBy(m => m.FactoryName);
                foreach (var f in factoryList)
                {
                    var plantGroups = f.GroupBy(c => new { c.PlantName, c.PlantId });
                    var facCount = f.Select(c => c.FloorId).Distinct().Count();
                    body.Append("<tr>");
                    body.Append($@"<td rowspan='{facCount}' style='border: 1px solid black;text-align:center;'>{f.Key}</td>");
                    for (int i = 0; i < plantGroups.Count(); i++)
                    {
                        var plant = plantGroups.ElementAt(i);
                        var plantCount = plant.Select(c => c.FloorId).Distinct().Count();
                        body.Append($"<td rowspan='{plantCount}' style='border: 1px solid black;text-align:center;'>{plant.Key.PlantName}</td>");
                        var floorGroups = plant.GroupBy(c => new { c.Floor, c.FloorId }).OrderBy(c => c.Key.Floor);
                        for (int j = 0; j < floorGroups.Count(); j++)
                        {
                            var floor = floorGroups.ElementAt(j);
                            var floorTotal = aaMacs.Where(c => c.FloorId == floor.Key.FloorId).Count();
                            var dayRunCount = runMacsD.Where(c => c.FloorId == floor.Key.FloorId)?.Count() ?? 0;
                            var nightRunCount = runMacsN.Where(c => c.FloorId == floor.Key.FloorId)?.Count() ?? 0;
                            var dayErrMacCount = floor.Where(t => notUploadMacsD.Any(c => c.ID == t.ID))?.Count();
                            var nightErrMacCount = floor.Where(t => notUploadMacsN.Any(c => c.ID == t.ID))?.Count();
                            double dayPercent = 0, nightPercent = 0;
                            if (dayRunCount > 0)
                            {
                                dayPercent = (double)dayErrMacCount / dayRunCount * 100;
                            }
                            if (nightRunCount > 0)
                            {
                                nightPercent = (double)nightErrMacCount / nightRunCount * 100;
                            }

                            if (j > 0)
                            {
                                body.Append("<tr>");
                            }
                            body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{floor.Key.Floor}</td>");
                            body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{floorTotal}</td>");
                            body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{dayRunCount}</td>");
                            body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{dayErrMacCount}</td>");
                            if (dayPercent > 50)
                            {
                                body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;background-color:red;'>{dayPercent.ToString("f2")}%</td>");
                            }
                            else
                            {
                                body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{dayPercent.ToString("f2")}%</td>");
                            }
                            body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{nightRunCount}</td>");
                            body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{nightErrMacCount}</td>");
                            if (nightPercent > 50)
                            {
                                body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;background-color:red;'>{nightPercent.ToString("f2")}%</td>");
                            }
                            else
                            {
                                body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{nightPercent.ToString("f2")}%</td>");
                            }

                            var charger = chargeInfoes.data.FirstOrDefault(c => c.Factory == f.Key
                            && c.Plant == plant.Key.PlantName && c.RegionName == floor.Key.Floor)?.ChargeName;
                            charger = string.IsNullOrEmpty(charger) ? "--" : charger;

                            body.Append($"<td rowspan='1' style='border: 1px solid black;text-align:center;'>{charger}</td>");

                            body.Append($"</tr>");
                        }
                    }
                }
                var noUploadDCount = notUploadMacsD?.Count();
                var noUploadNCount = notUploadMacsN?.Count();
                double dRate = 0, nRate = 0;
                if (runMacsD.Count() > 0)
                {
                    dRate = (double)noUploadDCount / runMacsD.Count();
                }
                if (runMacsN.Count() > 0)
                {
                    nRate = (double)noUploadNCount / runMacsN.Count();
                }
                body.Append("<tr>");
                body.Append($"<td style='border: 1px solid black;text-align:center;background-color:#40DCFF;' colspan='3'>合计</td>");
                body.Append($"<td style='border: 1px solid black;text-align:center;background-color:#40DCFF;' colspan='1'>{aaMacs.Count()}</td>");
                body.Append($"<td style='border: 1px solid black;text-align:center;background-color:#40DCFF;' colspan='1'>{runMacsD.Count()}</td>");
                body.Append($"<td style='border: 1px solid black;text-align:center;background-color:#40DCFF;' colspan='1'>{noUploadDCount}</td>");
                if (dRate > 0.2)
                {
                    body.Append($"<td style='border: 1px solid black;text-align:center;background-color:red;' colspan='1'>{(dRate * 100).ToString("f2")}%</td>");
                }
                else
                {
                    body.Append($"<td style='border: 1px solid black;text-align:center;#40DCFF;' colspan='1'>{(dRate * 100).ToString("f2")}%</td>");
                }
                body.Append($"<td style='border: 1px solid black;text-align:center;background-color:#40DCFF;' colspan='1'>{runMacsN.Count()}</td>");
                body.Append($"<td style='border: 1px solid black;text-align:center;background-color:#40DCFF;' colspan='1'>{noUploadNCount}</td>");
                if (nRate > 0.2)
                {
                    body.Append($"<td style='border: 1px solid black;text-align:center;background-color:red;' colspan='1'>{(nRate * 100).ToString("f2")}%</td>");
                }
                else
                {
                    body.Append($"<td style='border: 1px solid black;text-align:center;background-color:#40DCFF;' colspan='1'>{(nRate * 100).ToString("f2")}%</td>");
                }

                body.Append("</td>");
                body.Append("</table>");
                if (notUploadMacs.Any())
                {
                    var macNumbers = await db.FindListForConditionAsync<MachineNumber>($" and " +
                        $"a.macid in ({string.Join(",", notUploadMacs.Select(c => c.ID))})", ref errorinfo);
                    StringBuilder sb = new StringBuilder();
                    sb.Append($"设备ID,设备编号,设备名称,设备类型,IP地址,实际IP,MAC地址,园区,厂房,楼层,{Convert.ToDateTime(startTime).ToString("MM-dd")}晚班,原因,{Convert.ToDateTime(endTime).ToString("MM-dd")}白班,原因,负责人工号,负责人姓名,");
                    sb.AppendLine();
                    notUploadMacs = notUploadMacs.OrderBy(c => c.FactoryName).ThenBy(t => t.PlantName).ThenBy(s => s.Floor)
                          .ThenBy(h => h.FCode);
                    foreach (var mac in notUploadMacs)
                    {
                        var macnumber = macNumbers.FirstOrDefault(c => c.MacId == mac.ID);
                        sb.Append($"{mac.FCode},{macnumber?.MacNumber ?? "--"},{mac.FName},{mac.MModeCode},{mac.IPAddress},{(string.IsNullOrEmpty(mac.RealIP) ? "" : mac.RealIP.Replace(",", " | "))},{mac.MacAddress},{mac.FactoryName},{mac.PlantName},{mac.Floor},");
                        if (notUploadMacsN.Any(c => c.ID == mac.ID))
                        {
                            sb.Append($"未传,");
                            var reason = GetAAMaterialErrorReason(db, mac.FCode, Convert.ToDateTime(endTime), "N");
                            sb.Append(reason + ",");
                        }
                        else
                        {
                            sb.Append(",,");
                        }
                        if (notUploadMacsD.Any(c => c.ID == mac.ID))
                        {
                            sb.Append("未传,");
                            var reason = GetAAMaterialErrorReason(db, mac.FCode, Convert.ToDateTime(endTime), "D");
                            sb.Append(reason + ",");
                        }
                        else
                        {
                            sb.Append(",,");
                        }
                        var ccmMac = GetMachineInfoFromCCM(mac.FCode);
                        if (ccmMac != null)
                        {
                            sb.Append(ccmMac.MENo + ",");
                            sb.Append(ccmMac.MEName + ",");
                        }
                        else
                        {
                            sb.Append("--,");
                            sb.Append("--,");
                        }
                        sb.AppendLine();
                    }
                    MemoryStream stream = new MemoryStream();
                    StreamWriter write = new StreamWriter(stream, Encoding.UTF8);
                    write.Write(sb.ToString());
                    write.Flush();
                    write.Close();
                    write.Dispose();
                    file.Add(new FileAttachment
                    {
                        FileContent = stream.ToArray(),
                        FileName = DateTime.Today.ToString("yyyy-MM-dd") + mail.MailSubject + ".xls",
                    });
                }
            }
            string errorinfoMail = string.Empty;
            myloger.LogError($"AA抛料率预警邮件发送开始");
            SendMail(mail.MailServer, mail.MailPort, mail.MailAccount, mail.MailPwd, toMail, ccMail, mail.MailSubject, body.ToString(), ref errorinfoMail, "", file, true);
            myloger.LogError($"AA抛料率预警邮件发送结束{errorinfoMail}");
            return errorinfoMail;
        }
        static IEnumerable<MacEntity> list = null;
        /// <summary>
        /// 获取抛料文件未上传的原因
        /// </summary>
        /// <param name="db"></param>
        /// <param name="macCode"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        private static string GetAAMaterialErrorReason(IDatabase db, string macCode, DateTime date, string shift)
        {
            string errorinfo = string.Empty;
            var states = db.FindListForCondition<OfAppletlog>($" and a.maccode='{macCode}' " +
                                  $"and a.RecTime>'{date.ToString("yyyy-MM-dd 21:00:00")}' " +
                                  $"and a.RecTime<'{date.ToString("yyyy-MM-dd 22:00:00")}'",
                                  ref errorinfo);
            if (states != null && states.Any(c => c.AppletS == 1))   // 任意一条小程序开启
            {
                return "未生成抛料率文件";
            }
            return "EAP辅助应用未开启";
        }

        /// <summary>
        /// 从CCM获取设备信息
        /// </summary>
        /// <param name="macCode"></param>
        /// <returns></returns>
        private static MacEntity GetMachineInfoFromCCM(string macCode)
        {
            using (IDatabase db = DbFactory.Base("ccmrpt"))
            {
                var sql = $@"select FACTORY as Factory,LOCATION as Floor,ASSET_NAME as MacName,
                            ASSET_MODEL as MacModel,ASSET_CODE as AssetCode,EQP_ID as MacCode,DAOCHANG_DATE as EnterDate,
                            YANSHOU_DATE as CheckDate,YOUXIAO_DATE as ValidDate,ME_NAME as MEName,ME_NO as MENo,WORKSHOP_NAME  as WorkShopName,WORKSHOP_NO as WorkShopNo
                            from ccmrpt.CCMMES_FIXED_ASSETS_V where EQP_ID = '{macCode}'";
                var entity = db.FindList<MacEntity>(sql).FirstOrDefault();
                return entity;
            }

        }

        /// <summary>
        /// 整合实际IP不一致及断线邮件
        /// </summary>
        /// <returns></returns>
        public static async Task<string> IPDifferentAndDiconnectionNotice()
        {
            string toMail = "";
            string ccMail = "";
            StringBuilder body = new StringBuilder();
            MailSettings mail = null;
            List<FileAttachment> file = new List<FileAttachment>();
            using (IDatabase db = DbFactory.Base("eapslave"))
            {
                var dal = new MailSettingsDal(db);
                string code = AppConfigurtaionServices.Configuration["IPDifferentNoticeCode"];
                mail = dal.GetMailSettingsByCode(code);
                if (mail == null)
                {
                    return "邮件代码不存在";
                }
                List<MailReceiver> receiverList = dal.GetMailReceiverByMailId(mail.ID).ToList();
                if (receiverList == null || !receiverList.Any())
                {
                    return "未设置收件人";
                }
                // receiverList = receiverList.Where(c => c.Name == "韩慧鑫").ToList();
                toMail = receiverList.Where(r => r.SendType == 1).Select(r => r.MailAddress).Join(";");
                ccMail = receiverList.Where(r => r.SendType == 2).Select(r => r.MailAddress).Join(";");

                string disconnCode = AppConfigurtaionServices.Configuration["DisConnNoticeCode"];
                var disconnMail = dal.GetMailSettingsByCode(disconnCode);
                var disconnMailBody = disconnMail.MailBody;
                FileAttachment ipAttachment = null;
                //设备查询
                try
                {
                    using (IDatabase ccdb = DbFactory.Base("ccmrpt"))
                    {
                        var ccdal = new DataCenterDal(ccdb);
                        string errorinfo = string.Empty;
                        int total = 0;
                        list = ccdal.GetMacList(1, 30000, "", "FACTORY", "ASC", out total, ref errorinfo);
                    }
                }
                catch (Exception ex)
                {
                    myloger.LogError(ex.ToString());
                }
                IEnumerable<MacRecipeDto> macStates;
                var ipBody = CreateIPDifferentHtml(mail, out ipAttachment, out macStates);

                FileAttachment disconnAttachment = null;
                var disconnBody = CreateDisconnHtml(mail, macStates, out disconnAttachment);
                //var mailBody = mail.MailBody;

                //待机程序未开启
                //  string appletCode = AppConfigurtaionServices.Configuration["AppletCode"];
                //  var appletMail = dal.GetMailSettingsByCode(appletCode);
                FileAttachment appletAttachment = null;
                var appletBody = CreateAppletHtml(mail, out appletAttachment);
                // var appletMainBody = appletMail.MailBody;
                if (string.IsNullOrEmpty(disconnMailBody))
                {
                    disconnMailBody = "<h3>EAP系统异常设备预警</h3>";
                    //ipBody = $"<h3>{ipMailBody}</h3>" + ipBody;
                }
                else
                {
                    disconnMailBody = $"<h3>{disconnMailBody}</h3>";
                }
                //if (string.IsNullOrEmpty(disconnMailBody))
                //{
                //    disconnMailBody = "机台断线信息汇总";
                //    disconnBody = $"<h3>{disconnMailBody}</h3>" + disconnBody;
                //}
                //if (string.IsNullOrEmpty(appletMainBody))
                //{
                //    appletMainBody = "待机程序未开启信息汇总";
                //    appletBody = $"<h3>{appletMainBody}</h3>" + appletBody;
                //}

                //  body.Append("<div style='text-align:center;'>");

                body.Append(disconnMailBody).Append(ipBody);
                // body.Append("</div>");
                //body.Append(ipBody).Append(disconnBody).Append(appletBody);
                if (ipAttachment != null)
                {
                    file.Add(ipAttachment);
                }
                if (disconnAttachment != null)
                {
                    file.Add(disconnAttachment);
                }
                if (appletAttachment != null)
                {
                    file.Add(appletAttachment);
                }
            }
            string errorinfoMail = "";

            myloger.LogError($"机台系统IP与实际IP不一致邮件发送开始");
            SendMail(mail.MailServer, mail.MailPort, mail.MailAccount, mail.MailPwd, toMail, ccMail, "EAP系统异常设备预警", body.ToString(), ref errorinfoMail, "", file, true);
            myloger.LogError($"机台系统IP与实际IP不一致邮件发送结束{errorinfoMail}");
            return errorinfoMail;
        }

        /// <summary>
        /// 生成IP不一致邮件正文和附件
        /// </summary>
        /// <param name="mail"></param>
        /// <param name="attachment"></param>
        /// <returns></returns>
        //public static string CreateIPDifferentHtml(MailSettings mail, out FileAttachment attachment)
        //{
        //    attachment = null;
        //    using (IDatabase db = DbFactory.Base("eapslave"))
        //    {
        //        var dalMac = new MachineDal(db);
        //        var macList = dalMac.GetMachineList();
        //        macList = macList.Where(m => !string.IsNullOrEmpty(m.FactoryName) && !m.FactoryName.Contains("临时区域"));
        //        var sql = "select a.id from machine a " +
        //            "left join macstatus01 b on a.fcode=b.maccode " +
        //            "where b.statusid=204906";
        //        var body = new StringBuilder();
        //        var shutDownMacIds = db.FindList<string>(sql)?.Select(c => Convert.ToInt32(c));
        //        if (macList == null || macList.Count() == 0)
        //        {
        //            return "未查询到设备";
        //        }
        //        body.Append($"<h3>{mail.MailBody}</h3>");
        //        body.Append(@"<table cellpadding='3' style='border-collapse: collapse; border: 1px solid black'>
        //                        <tr>
        //                            <th style='border: 1px solid black'>园区</th>
        //                            <th style='border: 1px solid black'>设备类型</th>
        //                            <th style='border: 1px solid black'>总数量</th>
        //                            <th style='border: 1px solid black'>ip不一致数量</th>
        //                            <th style='border: 1px solid black'>ip不一致率</th>
        //                        </tr>");
        //        var factoryList = macList.GroupBy(m => m.FactoryName);
        //        foreach (var f in factoryList)
        //        {
        //            //每个园区所有机台按机型分组
        //            var pCodeList = macList.Where(m => m.FactoryName == f.Key).GroupBy(m => m.MModeCode);
        //            //每个园区ip不一致按机型分组
        //            var pCodeDisConnList = macList.Where(m => m.FactoryName == f.Key && (string.IsNullOrEmpty(m.RealIP) || !m.RealIP.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).
        //        Contains(m.IPAddress)) && !shutDownMacIds.Contains(m.ID)).GroupBy(m => m.MModeCode);
        //            int i = 0;
        //            int totalCount = 0;
        //            int disConnTotalCount = 0;
        //            foreach (var p in pCodeList)
        //            {
        //                //每个机型的总数
        //                int count = p.Count();
        //                //每个机型离线的总数
        //                int disConnCount = pCodeDisConnList.Where(m => m.Key == p.Key).Any() ? pCodeDisConnList.Where(m => m.Key == p.Key).First().Count() : 0;
        //                if (i == 0)
        //                {
        //                    body.Append($@"<tr><td rowspan='{pCodeList.Count() + 1}' style='border: 1px solid black'>{f.Key}</td>
        //                                        <td style='border: 1px solid black'>{p.Key}</td>       
        //                                        <td style='border: 1px solid black'>{count}</td>
        //                                        <td style='border: 1px solid black'>{disConnCount}</td>
        //                                        <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
        //                                    </tr>");
        //                }
        //                else
        //                {
        //                    body.Append($@"<tr>
        //                                        <td style='border: 1px solid black'>{p.Key}</td>       
        //                                        <td style='border: 1px solid black'>{count}</td>
        //                                        <td style='border: 1px solid black'>{disConnCount}</td>
        //                                        <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
        //                                    </tr>");
        //                }
        //                totalCount += count;
        //                disConnTotalCount += disConnCount;
        //                i++;
        //            }
        //            body.Append($@"<tr><td style='border: 1px solid black'>总计</td>
        //                               <td style='border: 1px solid black'>{totalCount}</td>
        //                               <td style='border: 1px solid black'>{disConnTotalCount}</td>
        //                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnTotalCount) * 100 / Convert.ToDouble(totalCount)).ToString("f2")}%</td>
        //                          </tr>");
        //        }
        //        body.Append("</table>");
        //        var macListDisConn = macList.Where(m => string.IsNullOrEmpty(m.RealIP) || !m.RealIP.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).
        //        Contains(m.IPAddress))
        //            .Where(m => !shutDownMacIds.Contains(m.ID))
        //            .OrderBy(m => m.FactoryName).OrderBy(m => m.FCode);
        //        if (macListDisConn.Any())
        //        {
        //            StringBuilder sb = new StringBuilder();
        //            sb.Append("设备ID,设备名称,设备类型,IP地址,实际IP,MAC地址,园区,楼层");
        //            sb.AppendLine();
        //            foreach (var mac in macListDisConn)
        //            {
        //                sb.Append($"{mac.FCode},{mac.FName},{mac.MModeCode},{mac.IPAddress},{(string.IsNullOrEmpty(mac.RealIP) ? "" : mac.RealIP.Replace(",", " | "))},{mac.MacAddress},{mac.FactoryName},{mac.Floor}");
        //                sb.AppendLine();
        //            }
        //            MemoryStream stream = new MemoryStream();
        //            StreamWriter write = new StreamWriter(stream, Encoding.UTF8);
        //            write.Write(sb.ToString());
        //            write.Flush();
        //            write.Close();
        //            write.Dispose();
        //            attachment = new FileAttachment
        //            {
        //                FileContent = stream.ToArray(),
        //                FileName = DateTime.Today.ToString("yyyy-MM-dd") + mail.MailSubject + ".xls",
        //            };
        //        }
        //        return body.ToString();
        //    }
        //}
        static List<string> macs = new List<string>();
        #region 后加
        /// <summary>
        /// 生成IP不一致邮件正文和附件
        /// </summary>
        /// <param name="mail"></param>
        /// <param name="attachment"></param>
        /// <returns></returns>
        public static string CreateIPDifferentHtml(MailSettings mail, out FileAttachment attachment,
            out IEnumerable<MacRecipeDto> macCon)
        {
            attachment = null;
            macCon = null;
            try
            {
                macs = new List<string>();
                attachment = null;
                using (IDatabase db = DbFactory.Base("eapslave"))
                {
                    var dalOfdisCons = new OfdisconDal(db);
                    var ofdisCons = dalOfdisCons.GetOfdisconExprort(1000);

                    var dalMac = new MachineDal(db);
                    var sql = "select a.id from machine a " +
                       "left join macstatus01 b on a.fcode=b.maccode " +
                       "where b.statusid=204906";
                    var body = new StringBuilder();
                    var shutDownMacIds = db.FindList<string>(sql)?.Select(c => Convert.ToInt32(c));

                    var macList = dalMac.GetMachineList().Where(c => c.PCode != "" && c.PCode != "AOI");
                    macList = macList.Where(m => !string.IsNullOrEmpty(m.FactoryName) && !m.FactoryName.Contains("临时区域") && !m.FactoryName.Contains("闲置") && !m.LineName.Contains("临时区域") && !m.LineName.Contains("闲置"));

                    string errorinfo = string.Empty;
                    int total;
                    var dalConMac = new MacRecipeDal(db);
                    macCon = dalConMac.GetMacRecipeDtos("", 1, 10000, "MacCode", "asc", out total, ref errorinfo);
                    macCon = macCon.Where(m => !string.IsNullOrEmpty(m.Factory) && !m.Factory.Contains("临时区域") && !m.Factory.Contains("闲置") && !m.Line.Contains("临时区域") && !m.Line.Contains("闲置") && !shutDownMacIds.Contains(m.MacId) && m.PCode != "AOI");

                    var factoryList = macList.GroupBy(m => m.FactoryName);
                    #region 去除都是0的Mac
                    foreach (var f in factoryList)
                    {
                        //每个园区所有机台按厂房分组
                        var pCodeList = macList.Where(m => m.FactoryName == f.Key).GroupBy(m => m.PlantName);
                        foreach (var p in pCodeList)
                        {
                            // 工序
                            var pfcodes = macList.Where(m => m.PlantName == p.Key && m.FactoryName == f.Key).GroupBy(m => m.PCode);
                            //断网
                            var pCodeDisConnList = macCon.Where(m => m.PlantName == p.Key && m.Factory == f.Key && m.StatusID == 1).GroupBy(m => m.PCode);
                            //ip不一致按工序分组
                            var pCodeDisIPList = macList.Where(m => m.FactoryName == f.Key && m.PlantName == p.Key && (string.IsNullOrEmpty(m.RealIP) || !m.RealIP.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).
                        Contains(m.IPAddress)) && !shutDownMacIds.Contains(m.ID)).GroupBy(m => m.PCode);
                            // 待机程序未开启
                            var pCodeNoAppletList = macList.Where(m => m.PlantName == p.Key && m.FactoryName == f.Key && m.AppletS != 1 && !shutDownMacIds.Contains(m.ID)).GroupBy(m => m.PCode);
                            //工序
                            foreach (var item in pfcodes)
                            {
                                //每个工序的总数
                                int count = item.Count();
                                //每个工序离线的总数
                                int disConnCount = 0;
                                if (pCodeDisConnList != null)
                                {
                                    disConnCount = pCodeDisConnList.Where(m => m.Key == item.Key).Any() ? pCodeDisConnList.Where(m => m.Key == item.Key).FirstOrDefault().Count() : 0;
                                }
                                //每个工序IP不一致的总数
                                int disIPCount = pCodeDisIPList.Where(m => m.Key == item.Key).Any() ? pCodeDisIPList.Where(m => m.Key == item.Key).First().Count() : 0;
                                //每个工序待机程序未开启的总数
                                int disAppletCount = pCodeNoAppletList.Where(m => m.Key == item.Key).Any() ? pCodeNoAppletList.Where(m => m.Key == item.Key).First().Count() : 0;

                                double disConnCountRate = Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count);
                                double disIPCountRate = Convert.ToDouble(disIPCount) * 100 / Convert.ToDouble(count);
                                double disAppletCountRate = Convert.ToDouble(disAppletCount) * 100 / Convert.ToDouble(count);
                                if (disConnCountRate <= 0 && disIPCountRate <= 0 && disAppletCountRate <= 0)
                                {
                                    foreach (var mac in item)
                                    {
                                        macs.Add(mac.FCode);
                                    }
                                }
                            }
                        }
                    }
                    #endregion
                    if (macs.Count() > 0)
                    {
                        macList = macList.Where(l => !macs.Contains(l.FCode));
                    }
                    if (macList == null || macList.Count() == 0)
                    {
                        return "未查询到设备";
                    }
                    //  body.Append($"<h3>{mail.MailBody}</h3>");
                    body.Append(@"<table cellpadding='3' style='border-collapse: collapse; border: 1px solid black'>
                                <tr height='19' style='height: 14.25pt'>
          <td
            class='xl67'
            height='51'
            width='72'
            rowspan='2'
            style='text-align: center;white-space: normal;background: #8ea9db;border: 0.5pt solid windowtext;
              height: 38.25pt;
              width: 54pt;

              border-right: 0.5pt solid windowtext;
              border-bottom: 0.5pt solid windowtext;
            '
            x:str=''
          >
            园区
          </td>
          <td
            class='xl68'
            width='102'
            rowspan='2'
            style='text-align: center;white-space: normal;background: #8ea9db;border: 0.5pt solid windowtext;
              width: 76.5pt;
              border-right: 0.5pt solid windowtext;
              border-bottom: 0.5pt solid windowtext;
            '
            x:str=''
          >
            厂房
          </td>
          <td
            class='xl68'
            width='88'
            rowspan='2'
            style='text-align: center;white-space: normal;background: #8ea9db;border: 0.5pt solid windowtext;
              width: 66pt;
              border-right: 0.5pt solid windowtext;
              border-bottom: 0.5pt solid windowtext;
            '
            x:str=''
          >
            工序
          </td>
          <td
            class='xl69'
            width='74'
            rowspan='2'
            style='text-align: center;white-space: normal;background: #8ea9db;border-left: 0.5pt solid windowtext;border-top: 0.5pt solid windowtext;border-bottom: 0.5pt solid windowtext;
              width: 55.5pt;
              border-right: none;
              border-bottom: 0.5pt solid windowtext;
            '
            x:str=''
          >
            设备总数
          </td>
          <td
            class='xl70' 
            width='132'
            colspan='2'
            style='text-align: center;
        background: #a5a5a5;
        font-weight: 700;
        border-left: 2pt double windowtext;
        border-top: 2pt double windowtext;
        border-right: 0.5pt solid windowtext;

        border-bottom: 0.5pt solid windowtext;
              width: 99pt;
              border-right: 2pt double windowtext;
              border-bottom: 0.5pt solid windowtext;
            '
            x:str=''
          >
            断网预警
          </td>
          <td
            class='xl72'
            width='132'
            colspan='2'
            style='text-align: center;
        background: #ffc000;
        font-weight: 700;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;

              width: 99pt;
              border-right: none;
              border-bottom: 0.5pt solid windowtext;
            '
            x:str=''
          >
            IP不一致
          </td>
          <td
            class='xl98'
            width='139'
            colspan='2'
            style='text-align: center;
        background: #70ad47;
        font-weight: 700;
        border-left: 2pt double windowtext;
        border-top: 2pt double windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;

              width: 104.25pt;
              border-right: 2pt double windowtext;
              border-bottom: 0.5pt solid windowtext;
            '
            x:str=''
          >
            EAP辅助应用开启
          </td>
          <td
            class='xl100'
            width='112'
            rowspan='2'
            style='text-align: center;
        background: #8ea9db;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;

              width: 84pt;
              border-right: 0.5pt solid windowtext;
              border-bottom: 0.5pt solid windowtext;
            '
            x:str=''
          >
            责任人
          </td>
        </tr>
        <tr height='32' style='height: 24pt'>
          <td class='xl74' style='text-align: center;
        background: #a5a5a5;
        border-left: 2pt double windowtext;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;'>离线数量</td>
          <td class='xl75' style=' text-align: center;
        background: #a5a5a5;
        border-left: 0.5pt solid windowtext;
        border-top: 0.5pt solid windowtext;
        border-right: 2pt double windowtext;
        border-bottom: 0.5pt solid windowtext;'>离线率</td>
          <td class='xl76' style='text-align: center;
        background: #ffc000;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;'>IP异常数</td>
          <td class='xl77' style='text-align: center;
        background: #ffc000;
        border-left: 0.5pt solid windowtext;
        border-top: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;'>异常率</td>
          <td class='xl101' style='text-align: center;
        background: #70ad47;
        border-left: 2pt double windowtext;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;'>未启动数</td>
          <td class='xl102' style='text-align: center;
        background: #70ad47;
        border-left: 0.5pt solid windowtext;
        border-top: 0.5pt solid windowtext;
        border-right: 2pt double windowtext;
        border-bottom: 0.5pt solid windowtext;'>未启动率</td>
        </tr>");
                    // factoryList = macList.GroupBy(m => m.FactoryName);
                    int totalCounts = 0;
                    int disConnTotalCounts = 0;
                    int disIPTotalCounts = 0;
                    int disAppletTotalCounts = 0;

                    // var ofDisCon = CurrentDbContext
                    // 园区
                    foreach (var f in factoryList)
                    {
                        //每个园区所有机台按厂房分组
                        //var pCodeList = macList.Where(m => m.FactoryName == f.Key).GroupBy(m => m.PlantName);
                        var pCodeList = f.GroupBy(m => m.PlantName);
                        int i = 0;
                        int totalCount = 0;
                        int disConnTotalCount = 0;
                        int disIPTotalCount = 0;
                        int disAppletTotalCount = 0;
                        int rows = 0;
                        //厂房
                        foreach (var p in pCodeList)
                        {
                            var pfcodes = macList.Where(m => m.PlantName == p.Key && m.FactoryName == f.Key).GroupBy(m => m.PCode);
                            rows += pfcodes.Count();
                        }
                        foreach (var p in pCodeList)
                        {
                            // 工序
                            var pfcodes = macList.Where(m => m.PlantName == p.Key && m.FactoryName == f.Key).GroupBy(m => m.PCode);
                            //断网
                            var pCodeDisConnList = macCon.Where(m => m.PlantName == p.Key && m.Factory == f.Key && m.StatusID == 1).GroupBy(m => m.PCode);
                            //ip不一致按工序分组
                            var pCodeDisIPList = macList.Where(m => m.FactoryName == f.Key && m.PlantName == p.Key && (string.IsNullOrEmpty(m.RealIP) || !m.RealIP.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).
                        Contains(m.IPAddress)) && !shutDownMacIds.Contains(m.ID)).GroupBy(m => m.PCode);
                            // 待机程序未开启
                            var pCodeNoAppletList = macList.Where(m => m.PlantName == p.Key && m.FactoryName == f.Key && m.AppletS != 1 && !shutDownMacIds.Contains(m.ID)).GroupBy(m => m.PCode);

                            int j = 0;
                            //工序
                            foreach (var item in pfcodes)
                            {
                                //每个工序的总数
                                int count = item.Count();
                                //每个工序离线的总数
                                int disConnCount = 0;
                                if (pCodeDisConnList != null)
                                {
                                    disConnCount = pCodeDisConnList.Where(m => m.Key == item.Key).Any() ? pCodeDisConnList.Where(m => m.Key == item.Key).FirstOrDefault().Count() : 0;
                                }
                                //每个工序IP不一致的总数
                                int disIPCount = pCodeDisIPList.Where(m => m.Key == item.Key).Any() ? pCodeDisIPList.Where(m => m.Key == item.Key).First().Count() : 0;
                                //每个工序待机程序未开启的总数
                                int disAppletCount = pCodeNoAppletList.Where(m => m.Key == item.Key).Any() ? pCodeNoAppletList.Where(m => m.Key == item.Key).First().Count() : 0;

                                double disConnCountRate = Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count);
                                double disIPCountRate = Convert.ToDouble(disIPCount) * 100 / Convert.ToDouble(count);
                                double disAppletCountRate = Convert.ToDouble(disAppletCount) * 100 / Convert.ToDouble(count);
                                //if (disConnCountRate <= 0 && disIPCountRate <= 0 && disAppletCountRate <= 0)
                                //{
                                //    continue;
                                //}
                                string disConn = disConnCountRate >= 9 ? ";background:red" : "";
                                string disIP = disIPCountRate >= 9 ? ";background:red" : "";
                                string disApplet = disAppletCountRate >= 9 ? ";background:red" : "";
                                if (disConnCountRate >= 5)
                                {
                                    disConn += ";font-weight:bold";
                                }
                                if (disIPCountRate >= 5)
                                {
                                    disIP += ";font-weight:bold";
                                }
                                if (disAppletCountRate >= 5)
                                {
                                    disApplet += ";font-weight:bold";
                                }
                                //var charger = chargeInfoes.data.FirstOrDefault(c => c.Factory == f.Key
                                //&& c.Plant == p.Key && c.RegionName == floor.Key.Floor)?.ChargeName;
                                //charger = string.IsNullOrEmpty(charger) ? "--" : charger;
                                if (i == 0)
                                {
                                    body.Append($@"<tr style='text-align: center;'>
                                                <td rowspan='{rows + pCodeList.Count()}' style='border: 1px solid black'>{f.Key}</td>       
                                                <td rowspan='{ pfcodes.Count()}' style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{item.Key}</td>
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black{disConn}'>{disConnCountRate.ToString("f2")}%</td>
                                                <td style='border: 1px solid black'>{disIPCount}</td>
                                                <td style='border: 1px solid black{disIP}'>{disIPCountRate.ToString("f2")}%</td>
                                                <td style='border: 1px solid black'>{disAppletCount}</td>
                                                <td style='border: 1px solid black{disApplet}'>{disAppletCountRate.ToString("f2")}%</td>
<td style='border: 1px solid black'>{ofdisCons?.Where(l => l.plantid == p.Select(c => c.PlantId).FirstOrDefault() && l.pcode == item.Key).Select(l => l.employs).FirstOrDefault()}</td>
                                            </tr>");
                                }
                                else
                                {
                                    if (j == 0)
                                    {
                                        body.Append($@"<tr style='text-align: center;'>
                                                <td rowspan='{ pfcodes.Count()}' style='border: 1px solid black'>{p.Key}</td>    
                                                <td style='border: 1px solid black'>{item.Key}</td>
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                 <td style='border: 1px solid black{disConn}'>{disConnCountRate.ToString("f2")}%</td>
                                                <td style='border: 1px solid black'>{disIPCount}</td>
                                                <td style='border: 1px solid black{disIP}'>{disIPCountRate.ToString("f2")}%</td>
                                                <td style='border: 1px solid black'>{disAppletCount}</td>
                                                <td style='border: 1px solid black{disApplet}'>{disAppletCountRate.ToString("f2")}%</td>
<td style='border: 1px solid black'>{ofdisCons?.Where(l => l.plantid == p.Select(c => c.PlantId).FirstOrDefault() && l.pcode == item.Key).Select(l => l.employs).FirstOrDefault()}</td>
                                            </tr>");
                                    }
                                    else
                                    {
                                        body.Append($@"<tr style='text-align: center;'>
                                                <td style='border: 1px solid black'>{item.Key}</td>
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                 <td style='border: 1px solid black{disConn}'>{disConnCountRate.ToString("f2")}%</td>
                                                <td style='border: 1px solid black'>{disIPCount}</td>
                                                <td style='border: 1px solid black{disIP}'>{disIPCountRate.ToString("f2")}%</td>
                                                <td style='border: 1px solid black'>{disAppletCount}</td>
                                                <td style='border: 1px solid black{disApplet}'>{disAppletCountRate.ToString("f2")}%</td>
<td style='border: 1px solid black'>{ofdisCons?.Where(l => l.plantid == p.Select(c => c.PlantId).FirstOrDefault() && l.pcode == item.Key).Select(l => l.employs).FirstOrDefault()}</td>
                                            </tr>");
                                    }
                                }
                                totalCount += count;
                                disConnTotalCount += disConnCount;
                                disIPTotalCount += disIPCount;
                                disAppletTotalCount += disAppletCount;
                                i++;
                                j++;
                            }

                            double disConnTotalRate = Convert.ToDouble(disConnTotalCount) * 100 / Convert.ToDouble(totalCount == 0 ? 1 : totalCount);
                            double disIPTotalRate = Convert.ToDouble(disIPTotalCount) * 100 / Convert.ToDouble(totalCount == 0 ? 1 : totalCount);
                            double disAppletTotalRate = Convert.ToDouble(disAppletTotalCount) * 100 / Convert.ToDouble(totalCount == 0 ? 1 : totalCount);
                            //if (disConnTotalRate <= 0 && disIPTotalRate <= 0 && disAppletTotalRate <= 0)
                            //{
                            //    continue;
                            //}
                            string disConnTotalRateStyle = disConnTotalRate >= 10 ? ";background:red;font-weight:bold;font-size: 18px;" : "";
                            string disIPTotalRateStyle = disIPTotalRate >= 10 ? ";background:red;font-weight:bold;font-size: 16px;" : "";
                            string disAppletTotalRateStyle = disAppletTotalRate >= 10 ? ";background:red;font-weight:bold;font-size: 18px;" : "";

                            //厂房合计
                            body.Append($@"<tr style='text-align: center;'>
                                                <td style='border: 1px solid black;background: #fce4d6;' colspan='2'>{p.Key}合计</td>     
                                                <td style='border: 1px solid black;background: #fce4d6;'>{totalCount}</td>
                                                <td style='border: 1px solid black;background: #fce4d6;'>{disConnTotalCount}</td>
                                                 <td style='border: 1px solid black;background: #fce4d6;{disConnTotalRateStyle}'>{disConnTotalRate.ToString("f2")}%</td>
                                                <td style='border: 1px solid black;background: #fce4d6;'>{disIPTotalCount}</td>
                                                <td style='border: 1px solid black;background: #fce4d6;{disIPTotalRateStyle}'>{disIPTotalRate.ToString("f2")}%</td>
                                                <td style='border: 1px solid black;background: #fce4d6;'>{disAppletTotalCount}</td>
                                                <td style='border: 1px solid black;background: #fce4d6;{disAppletTotalRateStyle}'>{disAppletTotalRate.ToString("f2")}%</td>
<td style='border: 1px solid black'>{ofdisCons?.Where(l => l.plantid == p.Select(c => c.PlantId).FirstOrDefault() && l.pcode is null).Select(l => l.employs).FirstOrDefault()}</td>
                                            </tr>");
                            j = 0;
                            totalCounts += totalCount;
                            disConnTotalCounts += disConnTotalCount;
                            disIPTotalCounts += disIPTotalCount;
                            disAppletTotalCounts += disAppletTotalCount;

                            totalCount = 0;
                            disConnTotalCount = 0;
                            disIPTotalCount = 0;
                            disAppletTotalCount = 0;
                        }
                        i = 0;
                    }
                    double disConnTotalRates = Convert.ToDouble(disConnTotalCounts) * 100 / Convert.ToDouble(totalCounts);
                    double disIPTotalRates = Convert.ToDouble(disIPTotalCounts) * 100 / Convert.ToDouble(totalCounts);
                    double disAppletTotalRates = Convert.ToDouble(disAppletTotalCounts) * 100 / Convert.ToDouble(totalCounts);
                    string disConnTotalRatesStyle = disConnTotalRates >= 10 ? ";background:red;font-weight:bold;font-size: 18px;" : "";
                    string disIPTotalRatesStyle = disIPTotalRates >= 10 ? ";background:red;font-weight:bold;font-size: 18px;" : "";
                    string disAppletTotalRatesStyle = disAppletTotalRates >= 10 ? ";background:red;font-weight:bold;font-size: 16px;" : "";
                    body.Append($@"<tr height='19' style='height: 14.25pt'>
          <td
            class='xl92'
            height='19'
            colspan='3'
            style='text-align: center;
        background: #ffff00;
        border: 0.5pt solid windowtext;
              height: 14.25pt;
              border-right: 0.5pt solid windowtext;
              border-bottom: 0.5pt solid windowtext;
            '
          >
            总计
          </td>
          <td class='xl93' style='text-align: center;
        background: #ffff00;
        border-left: 0.5pt solid windowtext;
        border-top: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;'>{macList.Count()}</td>
          <td class='xl94' style='text-align: center;
        background: #ffff00;
        border-left: 2pt double windowtext;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 2pt double windowtext;'>{disConnTotalCounts}</td>
          <td class='xl95' style=' text-align: center;
        background: #ffff00;
        border-left: 0.5pt solid windowtext;
        border-top: 0.5pt solid windowtext;
        border-right: 2pt double windowtext;
        border-bottom: 2pt double windowtext;{disConnTotalRatesStyle}'>{disConnTotalRates.ToString("f2")}%</td>
          <td class='xl96' style='text-align: center;
        background: #ffff00;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;'>{disIPTotalCounts}</td>
          <td class='xl93' style=' text-align: center;
        background: #ffff00;
        border-left: 0.5pt solid windowtext;
        border-top: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext;{disIPTotalRatesStyle}'>{disIPTotalRates.ToString("f2")}%</td>
          <td class='xl94' style=' text-align: center;
        background: #ffff00;
        border-left: 2pt double windowtext;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 2pt double windowtext;'>{disAppletTotalCounts}</td>
          <td class='xl95' style='text-align: center;
        background: #ffff00;
        border-left: 0.5pt solid windowtext;
        border-top: 0.5pt solid windowtext;
        border-right: 2pt double windowtext;
        border-bottom: 2pt double windowtext;{disAppletTotalRatesStyle}'>{disAppletTotalRates.ToString("f2")}%</td>
          <td class='xl88' style='text-align: center;
        border-top: 0.5pt solid windowtext;
        border-right: 0.5pt solid windowtext;
        border-bottom: 0.5pt solid windowtext; '></td>
        </tr>");
                    body.Append("</table>");
                    var macListDisConn = macList.Where(m => string.IsNullOrEmpty(m.RealIP) || !m.RealIP.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).
                    Contains(m.IPAddress))
                        .Where(m => !shutDownMacIds.Contains(m.ID))
                        .OrderBy(m => m.FactoryName).OrderBy(m => m.FCode);
                    if (macListDisConn.Any())
                    {
                        var macNumbers = db.FindListForCondition<MachineNumber>($" ", ref errorinfo);
                        StringBuilder sb = new StringBuilder();
                        sb.Append("设备ID,设备编号,设备名称,设备类型,IP地址,实际IP,MAC地址,园区,厂房,楼层,线体,责任人");
                        sb.AppendLine();
                        foreach (var mac in macListDisConn)
                        {
                            var macnumber = macNumbers.FirstOrDefault(c => c.MacId == mac.ID);
                            sb.Append($"{mac.FCode},{macnumber?.MacNumber ?? "--"},{mac.FName},{mac.MModeCode},{mac.IPAddress},{(string.IsNullOrEmpty(mac.RealIP) ? "" : mac.RealIP.Replace(",", " | "))},{mac.MacAddress},{mac.FactoryName},{mac.PlantName},{mac.Floor},{mac.LineName},{list?.Where(l => l.MacCode == mac.FCode.TrimEnd('L').TrimEnd('l').TrimEnd('r').TrimEnd('R')).Select(l => l.MEName).FirstOrDefault()}");
                            sb.AppendLine();
                        }
                        myloger.LogError("ip不一致" + sb.ToString());
                        MemoryStream stream = new MemoryStream();
                        StreamWriter write = new StreamWriter(stream, Encoding.UTF8);
                        write.Write(sb.ToString());
                        write.Flush();
                        write.Close();
                        write.Dispose();
                        attachment = new FileAttachment
                        {
                            FileContent = stream.ToArray(),
                            FileName = DateTime.Today.ToString("yyyy-MM-dd") + "各园区ASM设备IP异常清单" + ".xls",
                        };
                    }
                    myloger.LogError("表格" + body.ToString());
                    return body.ToString();

                }
            }
            catch (Exception ex)
            {
                myloger.LogError(ex.ToString());
                return "";
            }
        }
        #endregion
        public static string CreateAppletHtml(MailSettings mail, out FileAttachment attachment)
        {
            attachment = null;
            using (IDatabase db = DbFactory.Base("eapslave"))
            {
                var dalMac = new MachineDal(db);
                var sql = "select a.id from machine a " +
                      "left join macstatus01 b on a.fcode=b.maccode " +
                      "where b.statusid=204906";
                var shutDownMacIds = db.FindList<string>(sql)?.Select(c => Convert.ToInt32(c));
                var macList = dalMac.GetMachineList();
                macList = macList.Where(m => !macs.Contains(m.FCode) && !string.IsNullOrEmpty(m.FactoryName) &&
                !m.FactoryName.Contains("临时区域") && !m.FactoryName.Contains("闲置")
                && !m.LineName.Contains("临时区域") && !m.LineName.Contains("闲置") && !shutDownMacIds.Contains(m.ID));
                var body = new StringBuilder();
                if (macList == null || macList.Count() == 0)
                {
                    return "未查询到设备";
                }
                body.Append($"<h3></h3>");
                body.Append(@"<table cellpadding='3' style='border-collapse: collapse; border: 1px solid black'>
                                <tr>
                                    <th style='border: 1px solid black'>园区</th>
                                    <th style='border: 1px solid black'>设备类型</th>
                                    <th style='border: 1px solid black'>总数量</th>
                                    <th style='border: 1px solid black'>待机程序未开启数量</th>
                                    <th style='border: 1px solid black'>未开启率</th>
                                </tr>");
                var factoryList = macList.GroupBy(m => m.FactoryName);
                foreach (var f in factoryList)
                {
                    //每个园区所有机台按机型分组
                    var pCodeList = macList.Where(m => m.FactoryName == f.Key).GroupBy(m => m.MModeCode);
                    //每个园区ip不一致按机型分组
                    var pCodeDisConnList = macList.Where(m => m.FactoryName == f.Key && m.AppletS != 1 && !shutDownMacIds.Contains(m.ID)).GroupBy(m => m.MModeCode);
                    int i = 0;
                    int totalCount = 0;
                    int disConnTotalCount = 0;
                    foreach (var p in pCodeList)
                    {
                        //每个机型的总数
                        int count = p.Count();
                        //每个机型未开启的总数
                        int disConnCount = pCodeDisConnList.Where(m => m.Key == p.Key).Any() ? pCodeDisConnList.Where(m => m.Key == p.Key).First().Count() : 0;
                        if (i == 0)
                        {
                            body.Append($@"<tr><td rowspan='{pCodeList.Count() + 1}' style='border: 1px solid black'>{f.Key}</td>
                                                <td style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
                                            </tr>");
                        }
                        else
                        {
                            body.Append($@"<tr>
                                                <td style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
                                            </tr>");
                        }
                        totalCount += count;
                        disConnTotalCount += disConnCount;
                        i++;
                    }
                    body.Append($@"<tr><td style='border: 1px solid black'>总计</td>
                                       <td style='border: 1px solid black'>{totalCount}</td>
                                       <td style='border: 1px solid black'>{disConnTotalCount}</td>
                                        <td style='border: 1px solid black'>{(Convert.ToDouble(disConnTotalCount) * 100 / Convert.ToDouble(totalCount)).ToString("f2")}%</td>
                                  </tr>");
                }
                body.Append("</table>");
                var macListDisConn = macList.Where(m => m.AppletS != 1)
                    .OrderBy(m => m.FactoryName).OrderBy(m => m.FCode);
                if (macListDisConn.Any())
                {
                    string errorinfo = "";
                    var macNumbers = db.FindListForCondition<MachineNumber>($"", ref errorinfo);
                    StringBuilder sb = new StringBuilder();
                    sb.Append("设备ID,设备编号,设备名称,设备类型,IP地址,实际IP,MAC地址,园区,厂房,楼层,线体,责任人");
                    sb.AppendLine();
                    foreach (var mac in macListDisConn)
                    {
                        var macnumber = macNumbers.FirstOrDefault(c => c.MacId == mac.ID);
                        sb.Append($"{mac.FCode},{macnumber?.MacNumber ?? "--"},{mac.FName},{mac.MModeCode},{mac.IPAddress},{(string.IsNullOrEmpty(mac.RealIP) ? "" : mac.RealIP.Replace(",", " | "))},{mac.MacAddress},{mac.FactoryName},{mac.PlantName},{mac.Floor},{mac.LineName},{list?.Where(l => l.MacCode == mac.FCode.TrimEnd('L').TrimEnd('l').TrimEnd('r').TrimEnd('R')).Select(l => l.MEName).FirstOrDefault()}");
                        sb.AppendLine();
                    }
                    myloger.LogError("待机程序未开启" + sb.ToString());
                    MemoryStream stream = new MemoryStream();
                    StreamWriter write = new StreamWriter(stream, Encoding.UTF8);
                    write.Write(sb.ToString());
                    write.Flush();
                    write.Close();
                    write.Dispose();
                    attachment = new FileAttachment
                    {
                        FileContent = stream.ToArray(),
                        FileName = DateTime.Today.ToString("yyyy-MM-dd") + "各园区ASM设备EAP辅助应用未开启清单" + ".xls",
                    };
                }
                return body.ToString();
            }
        }

        public static string CreateDisconnHtml(MailSettings mail, IEnumerable<MacRecipeDto> macList, out FileAttachment attachment)
        {
            attachment = null;
            var body = new StringBuilder();
            using (IDatabase db = DbFactory.Base("eapslave"))
            {
                var dalMac = new MacRecipeDal(db);
                string errorinfo = string.Empty;
                int total;
                var sql = "select a.id from machine a " +
                      "left join macstatus01 b on a.fcode=b.maccode " +
                      "where b.statusid=204906";
                var shutDownMacIds = db.FindList<string>(sql)?.Select(c => Convert.ToInt32(c));
                // var macList = dalMac.GetMacRecipeDtos("", 1, 10000, "MacCode", "asc", out total, ref errorinfo);
                macList = macList.Where(m => !macs.Contains(m.MacCode) && !string.IsNullOrEmpty(m.Factory)
                && !m.Factory.Contains("临时区域") && !m.Factory.Contains("闲置") &&
                !m.Line.Contains("临时区域") && !m.Line.Contains("闲置") &&
                !shutDownMacIds.Contains(m.MacId) && m.PCode != "AOI");
                if (macList == null || macList.Count() == 0)
                {
                    return "未查询到设备";
                }
                body.Append($"<h3></h3>");
                //body.Append($"<h3>{DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd")}</h3>");
                body.Append(@"<table cellpadding='3' style='border-collapse: collapse; border: 1px solid black'>
                                <tr>
                                    <th style='border: 1px solid black'>园区</th>
                                    <th style='border: 1px solid black'>设备类型</th>
                                    <th style='border: 1px solid black'>总数量</th>
                                    <th style='border: 1px solid black'>离线数量</th>
                                    <th style='border: 1px solid black'>离线率</th>
                                </tr>");
                var factoryList = macList.GroupBy(m => m.Factory);
                var regionChargeDal = new RegionChargerDal(db);
                var chargeInfoes = regionChargeDal.Get(string.Empty, string.Empty, string.Empty, string.Empty).Result;
                foreach (var f in factoryList)
                {
                    //每个园区所有状态按机型分组
                    var pCodeList = macList.Where(m => m.Factory == f.Key).GroupBy(m => m.PCode);
                    //每个园区离线状态按机型分组
                    var pCodeDisConnList = macList.Where(m => m.Factory == f.Key && m.StatusID == 1).GroupBy(m => m.PCode);
                    int i = 0;
                    int totalCount = 0;
                    int disConnTotalCount = 0;
                    foreach (var p in pCodeList)
                    {
                        //每个机型的总数
                        int count = p.Count();
                        //每个机型离线的总数
                        int disConnCount = pCodeDisConnList.Where(m => m.Key == p.Key).Any() ? pCodeDisConnList.Where(m => m.Key == p.Key).First().Count() : 0;
                        if (i == 0)
                        {
                            body.Append($@"<tr><td rowspan='{pCodeList.Count() + 1}' style='border: 1px solid black'>{f.Key}</td>
                                                <td style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
                                            </tr>");
                        }
                        else
                        {
                            body.Append($@"<tr>
                                                <td style='border: 1px solid black'>{p.Key}</td>       
                                                <td style='border: 1px solid black'>{count}</td>
                                                <td style='border: 1px solid black'>{disConnCount}</td>
                                                <td style='border: 1px solid black'>{(Convert.ToDouble(disConnCount) * 100 / Convert.ToDouble(count)).ToString("f2")}%</td>
                                            </tr>");
                        }
                        totalCount += count;
                        disConnTotalCount += disConnCount;
                        i++;
                    }
                    body.Append($@"<tr><td style='border: 1px solid black'>总计</td>
                                       <td style='border: 1px solid black'>{totalCount}</td>
                                       <td style='border: 1px solid black'>{disConnTotalCount}</td>
                                        <td style='border: 1px solid black'>{(Convert.ToDouble(disConnTotalCount) * 100 / Convert.ToDouble(totalCount)).ToString("f2")}%</td>
                                  </tr>");
                }
                body.Append("</table>");
                var macListDisConn = macList.Where(m => !string.IsNullOrEmpty(m.Factory) && m.StatusID == 1).OrderBy(m => m.Factory).OrderBy(m => m.MacCode);
                if (macListDisConn.Any())
                {
                    var macNumbers = db.FindListForCondition<MachineNumber>($"", ref errorinfo);
                    StringBuilder sb = new StringBuilder();
                    sb.Append("设备ID,设备编号,设备类型,当前IP,MAC地址,园区,厂房,楼层,线体,最新程序名称,连接状态,责任人");
                    sb.AppendLine();
                    foreach (var mac in macListDisConn)
                    {
                        var macnumber = macNumbers.FirstOrDefault(c => c.MacId == mac.MacId);
                        sb.Append($"{mac.MacCode},{macnumber?.MacNumber ?? "--"},{mac.PCode},{(string.IsNullOrEmpty(mac.RealIp) ? "" : mac.RealIp.Replace(",", " | "))},{mac.MacAddress},{mac.Factory},{mac.PlantName},{mac.Floor},{mac.Line},{mac.ProgramName},{mac.StatusName},{list?.Where(l => l.MacCode == mac.MacCode.TrimEnd('L').TrimEnd('l').TrimEnd('r').TrimEnd('R')).Select(l => l.MEName).FirstOrDefault()}");
                        sb.AppendLine();
                    }
                    myloger.LogError("离线" + sb.ToString());
                    MemoryStream stream = new MemoryStream();
                    StreamWriter write = new StreamWriter(stream, Encoding.UTF8);
                    write.Write(sb.ToString());
                    write.Flush();
                    write.Close();
                    write.Dispose();
                    attachment = new FileAttachment
                    {
                        FileContent = stream.ToArray(),
                        FileName = DateTime.Today.ToString("yyyy-MM-dd") + mail.MailSubject + ".xls",
                    };
                }
            }
            return body.ToString();
        }

        /// <summary>
        /// 发送邮件方法
        /// </summary>
        /// <param name="FromMial">发件人邮箱</param>
        /// <param name="ToMial">收件人邮箱(多个收件人地址用";"号隔开)</param>
        /// <param name="AuthorizationCode">发件人授权码</param>
        /// <param name="ReplyTo">对方回复邮件时默认的接收地址（不设置也是可以的）</param>
        /// <param name="CCMial">//邮件的抄送者(多个抄送人用";"号隔开)</param>
        /// <param name="File_Path">附件的地址</param>
        public static void SendMail(string mailServer, string port, string fromMail, string password, string toMail, string ccMial, string subject, string body, ref string errorinfo, string file_Path = "", List<FileAttachment> fileAttachments = null, bool isBodyHtml = false)
        {
            try
            {
                //实例化一个发送邮件类。
                MailMessage mailMessage = new MailMessage();

                //邮件的优先级，分为 Low, Normal, High，通常用 Normal即可
                mailMessage.Priority = MailPriority.Normal;

                //发件人邮箱地址。
                mailMessage.From = new MailAddress(fromMail);

                //收件人邮箱地址。需要群发就写多个
                //拆分邮箱地址
                List<string> ToMiallist = toMail.Split(';').ToList();
                for (int i = 0; i < ToMiallist.Count; i++)
                {
                    mailMessage.To.Add(new MailAddress(ToMiallist[i]));  //收件人邮箱地址。
                }
                if (ccMial != "" && ccMial != null)
                {
                    List<string> CCMiallist = ccMial.Split(';').ToList();
                    for (int i = 0; i < CCMiallist.Count; i++)
                    {
                        //邮件的抄送者，支持群发
                        mailMessage.CC.Add(new MailAddress(CCMiallist[i]));
                    }
                }
                //如果你的邮件标题包含中文，这里一定要指定，否则对方收到的极有可能是乱码。
                mailMessage.SubjectEncoding = Encoding.UTF8;

                //邮件正文是否是HTML格式
                mailMessage.IsBodyHtml = isBodyHtml;

                //邮件标题。
                mailMessage.Subject = subject;
                mailMessage.SubjectEncoding = Encoding.UTF8;
                //邮件内容。
                mailMessage.Body = body;

                //设置邮件的附件，将在客户端选择的附件先上传到服务器保存一个，然后加入到mail中  
                if (file_Path != "" && file_Path != null)
                {
                    //将附件添加到邮件
                    mailMessage.Attachments.Add(new System.Net.Mail.Attachment(file_Path));
                    //获取或设置此电子邮件的发送通知。
                    mailMessage.DeliveryNotificationOptions = DeliveryNotificationOptions.OnSuccess;
                }
                if (fileAttachments != null && fileAttachments.Count > 0)
                {
                    foreach (var fileAttachment in fileAttachments)
                    {
                        mailMessage.Attachments.Add(new System.Net.Mail.Attachment(new MemoryStream(fileAttachment.FileContent),
                        fileAttachment.FileName));
                    }

                }
                //实例化一个SmtpClient类。
                SmtpClient client = new SmtpClient();
                client.DeliveryMethod = SmtpDeliveryMethod.Network;//指定电子邮件发送方式
                client.Host = mailServer;
                client.Port = string.IsNullOrEmpty(port) ? 25 : Convert.ToInt32(port);
                #region 设置邮件服务器地址
                //在这里我使用的是163邮箱，所以是smtp.163.com，如果你使用的是qq邮箱，那么就是smtp.qq.com。
                // client.Host = "smtp.163.com";
                //if (fromMail.Length != 0)
                //{
                //    //根据发件人的邮件地址判断发件服务器地址   默认端口一般是25
                //    string[] addressor = fromMail.Trim().Split(new Char[] { '@', '.' });
                //    switch (addressor[1])
                //    {
                //        case "163":
                //            client.Host = "smtp.163.com";
                //            break;
                //        case "126":
                //            client.Host = "smtp.126.com";
                //            break;
                //        case "qq":
                //            client.Host = "smtp.qq.com";
                //            break;
                //        case "gmail":
                //            client.Host = "smtp.gmail.com";
                //            break;
                //        case "hotmail":
                //            client.Host = "smtp.live.com";//outlook邮箱
                //            //client.Port = 587;
                //            break;
                //        case "foxmail":
                //            client.Host = "smtp.foxmail.com";
                //            break;
                //        case "sina":
                //            client.Host = "smtp.sina.com.cn";
                //            break;
                //        default:
                //            client.Host = "smtp.exmail.qq.com";//qq企业邮箱
                //            break;
                //    }
                //}
                #endregion

                //使用安全加密连接。
                //client.EnableSsl = true;
                //不和请求一块发送。
                client.UseDefaultCredentials = false;

                //验证发件人身份(发件人的邮箱，邮箱里的生成授权码);
                client.Credentials = new NetworkCredential(fromMail, password);
                //如果发送失败，SMTP 服务器将发送 失败邮件告诉我  
                //mailMessage.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
                //发送
                client.Send(mailMessage);
                //mailMessage.Attachments.Dispose();//释放资源，必须，否则会锁附件文件
            }
            catch (Exception ex)
            {
                errorinfo = "发送失败:" + ex.Message + ";" + (ex.InnerException != null ? ex.InnerException.Message : "");
            }
        }

        public static void SendByMailKit(string mailServer, string port, string fromMail, string password, string toMail, string ccMial, string subject, string body, ref string errorinfo, List<FileAttachment> fileAttachments = null)
        {
            try
            {
                var message = new MimeKit.MimeMessage();
                message.From.Add(new MimeKit.MailboxAddress("from", fromMail));
                List<string> toMaillist = toMail.Split(';').ToList();
                for (int i = 0; i < toMaillist.Count; i++)
                {
                    message.To.Add(new MimeKit.MailboxAddress("to", toMaillist[i]));  //收件人邮箱地址。
                }
                if (ccMial != "" && ccMial != null)
                {
                    List<string> ccMaillist = ccMial.Split(';').ToList();
                    for (int i = 0; i < ccMaillist.Count; i++)
                    {
                        //邮件的抄送者，支持群发
                        message.Cc.Add(new MimeKit.MailboxAddress("cc", ccMaillist[i]));
                    }
                }
                message.Subject = subject;
                //var plain = new MimeKit.TextPart("plain")
                //{
                //    Text = @"不好意思，我在测试程序，Sorry！"
                //};
                var html = new MimeKit.TextPart("html")
                {
                    Text = body
                };

                var alternative = new MimeKit.Multipart("alternative");
                //alternative.Add(plain);
                alternative.Add(html);
                // now create the multipart/mixed container to hold the message text and the
                // image attachment
                var multipart = new MimeKit.Multipart("mixed");
                multipart.Add(alternative);
                if (fileAttachments != null && fileAttachments.Count > 0)
                {
                    foreach (var fileAttachment in fileAttachments)
                    {
                        // create an image attachment for the file located at path
                        var attachment = new MimeKit.MimePart("application", "ms-excel")
                        {
                            Content = new MimeKit.MimeContent(fileAttachment.Stream, MimeKit.ContentEncoding.Default),
                            ContentDisposition = new MimeKit.ContentDisposition(MimeKit.ContentDisposition.Attachment),
                            ContentTransferEncoding = MimeKit.ContentEncoding.Base64,
                            FileName = fileAttachment.FileName
                        };
                        multipart.Add(attachment);
                    }
                }
                message.Body = multipart;
                using (var client = new MailKit.Net.Smtp.SmtpClient())
                {
                    client.CheckCertificateRevocation = false;
                    client.Connect(mailServer, Convert.ToInt32(port), false);

                    // Note: since we don't have an OAuth2 token, disable
                    // the XOAUTH2 authentication mechanism.
                    client.AuthenticationMechanisms.Remove("XOAUTH2");

                    // Note: only needed if the SMTP server requires authentication
                    client.Authenticate(fromMail, password);
                    client.Send(message);
                    client.Disconnect(true);
                }
            }
            catch (Exception ex)
            {
                errorinfo = "发送失败:" + ex.Message + ";" + (ex.InnerException != null ? ex.InnerException.Message : "");
            }
        }

    }

    public class FileAttachment
    {
        public String FileName { get; set; }
        public byte[] FileContent { get; set; }

        public Stream Stream { get; set; }
    }
}