ProductionLineMonitor/ProductionLineMonitor.Application/Services/ExcelService.cs

using Core.Dtos;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.Record;
using NPOI.HSSF.Record.CF;
using OfficeOpenXml;
using OfficeOpenXml.ConditionalFormatting;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;
using ProductionLineMonitor.Application.Services.FaultService;
using ProductionLineMonitor.Application.Services.OEEService;
using ProductionLineMonitor.Application.Services.OEEService.Dtos;
using ProductionLineMonitor.Core.Dtos;
using ProductionLineMonitor.Core.IRepositories;
using ProductionLineMonitor.Core.Models;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Security.Claims;
using System.Text;
using static ProductionLineMonitor.Core.Utils.EmailHelper;

namespace ProductionLineMonitor.Application.Services
{
    public class ExcelService : IExcelService
    {
        private readonly IUnitOfWork _unitOfWork;
        private readonly IOEEService _oeeService;
        private readonly IFaultService _faultService;

        public ExcelService(
            IUnitOfWork unitOfWork,
            IOEEService oEEService,
            IFaultService faultService)
        {
            _unitOfWork = unitOfWork;
            _oeeService = oEEService;
            _faultService = faultService;
        }

        public bool EquipmentOperationReport(DateTime dateTime)
        {
            try
            {
                string date = dateTime.ToString("yyyy-MM-dd");
                if (dateTime.Hour == 8)
                {
                    date = dateTime.AddDays(-1).ToString("yyyy-MM-dd");
                }

                JToken? UtilizationRateEmailList;
                JToken? ExcelChartsSettings;
                using (StreamReader file = File.OpenText("D:\\ReportForms\\Email.json"))
                {
                    using JsonTextReader reader = new JsonTextReader(file);
                    JObject jsonObject = (JObject)JToken.ReadFrom(reader);
                    UtilizationRateEmailList = jsonObject["UtilizationRateEmail"];
                    ExcelChartsSettings = jsonObject["ExcelChartsSettings"];
                }

                if (UtilizationRateEmailList == null || UtilizationRateEmailList.Count() == 0)
                {
                    return false;
                }

                DateTime startTime = DateTime.Parse($"{date} 08:00:00");
                DateTime endTime = startTime.AddDays(1);
                DateTime dataStartTime = endTime.AddDays(-7);

                int count = 0;
                // 文件夹存储路径
                string dateNowStr = DateTime.Now.ToString("yyyyMMddHHmmss");
                string folderPath = $"D:\\ReportForms\\{DateTime.Now:yyyyMM}\\{dateNowStr}";
                foreach (var UtilizationRateEmail in UtilizationRateEmailList)
                {
                    count++;
                    LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} Begin");
                    if ((!(bool)UtilizationRateEmail["IsSend"]))
                    {
                        LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} isSend is False");
                        continue;
                    }

                    string path = Path.Combine(folderPath, $"Group-{count}.xlsx");
                    if (!Directory.Exists(folderPath))
                        Directory.CreateDirectory(folderPath);
                    string[] machineIdString = UtilizationRateEmail["MachineIDs"].ToString().Split(',');
                    if (machineIdString == null || machineIdString.Length == 0)
                    {
                        LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} machineIdString is null or length is 0");
                        continue;
                    }

                    var HideEquipmentProduction = ExcelChartsSettings["HideEquipmentProduction"].ToString().Split(',');

                    //List<int> [产能,稼动,待机,报警,待料，合计]
                    Dictionary<string, List<int>> OEE_Temp1 = new Dictionary<string, List<int>>();
                    //List<int> [运行时间,报警时间,待料时间,换料时间,产能,报警次数]
                    Dictionary<string, List<int>> OEE_Temp2 = new Dictionary<string, List<int>>();
                    //机种
                    List<List<string>> ModuleType = new List<List<string>>();
                    for (int i = 0; i < 24; i++)
                        ModuleType.Add(new List<string>());


                    foreach (var machineId in machineIdString)
                    {
                        var machine = _unitOfWork.MachineRepository.GetById(machineId);
                        var line = _unitOfWork.ProductionLineRepository.FirstOrDefault(x => x.Id == machine.ProductionLineId);
                        var machineOEE = _oeeService.GetOEE(machineId, dataStartTime.ToString("yyyy-MM-dd"), startTime.ToString("yyyy-MM-dd"));
                        var machineHourDatas = _unitOfWork.MachineOutPutPerHourRepository.GetList(
                            x =>
                            x.MachineId == machine.Id &&
                            x.DataTime >= startTime &&
                            x.DataTime < endTime).OrderBy(o => o.DataTime).ToList();

                        FileInfo file = new FileInfo(path);

                        //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                        using ExcelPackage package = new ExcelPackage(file);

                        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add($"{line.Name} {machine.Name}");

                        worksheet.View.ZoomScale = 80;

                        worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                        #region tab header

                        worksheet.Cells[1, 1].Value = "NO";
                        worksheet.Cells[1, 2].Value = "时间";
                        worksheet.Cells[1, 3].Value = "稼动率(%)";
                        worksheet.Cells[1, 4].Value = "待机率(%)";
                        worksheet.Cells[1, 5].Value = "报警率(%)";
                        worksheet.Cells[1, 6].Value = "换料率(%)";
                        worksheet.Cells[1, 7].Value = "产能(pcs)";
                        worksheet.Cells[1, 8].Value = "稼动(min)";
                        worksheet.Cells[1, 9].Value = "待机(min)";
                        worksheet.Cells[1, 10].Value = "报警(min)";
                        worksheet.Cells[1, 11].Value = "换料(min)";
                        worksheet.Cells[1, 12].Value = "合计(min)";

                        worksheet.Cells[1, 15].Value = $"{startTime:yyyy-MM-dd} 早班";
                        worksheet.Cells[1, 15, 1, 24].Merge = true;

                        worksheet.Cells[2, 15].Value = "NO";
                        worksheet.Cells[2, 16].Value = "时段";
                        worksheet.Cells[2, 17].Value = "机种";
                        worksheet.Cells[2, 18].Value = "运行时间";
                        worksheet.Cells[2, 19].Value = "报警时间";
                        worksheet.Cells[2, 20].Value = "待料时间";
                        worksheet.Cells[2, 21].Value = "换料时间";
                        worksheet.Cells[2, 22].Value = "产能";
                        worksheet.Cells[2, 23].Value = "TT";
                        worksheet.Cells[2, 24].Value = "报警次数";

                        worksheet.Cells[16, 15].Value = "NO";
                        worksheet.Cells[16, 16].Value = "故障码";
                        worksheet.Cells[16, 17].Value = "故障详情";
                        worksheet.Cells[16, 24].Value = "报警次数";

                        worksheet.Cells[16, 17, 16, 23].Merge = true;
                        worksheet.Cells[17, 17, 17, 23].Merge = true;
                        worksheet.Cells[18, 17, 18, 23].Merge = true;
                        worksheet.Cells[19, 17, 19, 23].Merge = true;
                        worksheet.Cells[20, 17, 20, 23].Merge = true;
                        worksheet.Cells[21, 17, 21, 23].Merge = true;
                        worksheet.Cells[22, 17, 22, 23].Merge = true;
                        worksheet.Cells[23, 17, 23, 23].Merge = true;
                        worksheet.Cells[24, 17, 24, 23].Merge = true;
                        worksheet.Cells[25, 17, 25, 23].Merge = true;
                        worksheet.Cells[26, 17, 26, 23].Merge = true;

                        worksheet.Cells[1, 26].Value = $"{startTime:yyyy-MM-dd} 夜班";
                        worksheet.Cells[1, 26, 1, 35].Merge = true;

                        worksheet.Cells[2, 26].Value = "NO";
                        worksheet.Cells[2, 27].Value = "时段";
                        worksheet.Cells[2, 28].Value = "机种";
                        worksheet.Cells[2, 29].Value = "运行时间";
                        worksheet.Cells[2, 30].Value = "报警时间";
                        worksheet.Cells[2, 31].Value = "待料时间";
                        worksheet.Cells[2, 32].Value = "换料时间";
                        worksheet.Cells[2, 33].Value = "产能";
                        worksheet.Cells[2, 34].Value = "TT";
                        worksheet.Cells[2, 35].Value = "报警次数";

                        worksheet.Cells[16, 26].Value = "NO";
                        worksheet.Cells[16, 27].Value = "故障码";
                        worksheet.Cells[16, 28].Value = "故障详情";
                        worksheet.Cells[16, 35].Value = "报警次数";

                        worksheet.Cells[16, 28, 16, 34].Merge = true;
                        worksheet.Cells[17, 28, 17, 34].Merge = true;
                        worksheet.Cells[18, 28, 18, 34].Merge = true;
                        worksheet.Cells[19, 28, 19, 34].Merge = true;
                        worksheet.Cells[20, 28, 20, 34].Merge = true;
                        worksheet.Cells[21, 28, 21, 34].Merge = true;
                        worksheet.Cells[22, 28, 22, 34].Merge = true;
                        worksheet.Cells[23, 28, 23, 34].Merge = true;
                        worksheet.Cells[24, 28, 24, 34].Merge = true;
                        worksheet.Cells[25, 28, 25, 34].Merge = true;
                        worksheet.Cells[26, 28, 26, 34].Merge = true;

                        worksheet.Column(1).Width = 5;
                        worksheet.Column(2).Width = 12;
                        worksheet.Column(3).Width = 12;
                        worksheet.Column(4).Width = 12;
                        worksheet.Column(5).Width = 12;
                        worksheet.Column(6).Width = 12;
                        worksheet.Column(7).Width = 12;
                        worksheet.Column(8).Width = 12;
                        worksheet.Column(9).Width = 12;
                        worksheet.Column(10).Width = 12;
                        worksheet.Column(11).Width = 12;
                        worksheet.Column(12).Width = 12;

                        worksheet.Column(13).Width = 2;
                        worksheet.Column(14).Width = 2;

                        worksheet.Column(15).Width = 5;
                        worksheet.Column(16).Width = 12;
                        worksheet.Column(17).Width = 12;
                        worksheet.Column(18).Width = 12;
                        worksheet.Column(19).Width = 12;
                        worksheet.Column(20).Width = 12;
                        worksheet.Column(21).Width = 12;
                        worksheet.Column(22).Width = 12;
                        worksheet.Column(23).Width = 12;
                        worksheet.Column(24).Width = 12;
                        worksheet.Column(22).Width = 12;
                        worksheet.Column(23).Width = 12;

                        worksheet.Column(25).Width = 2;

                        worksheet.Column(26).Width = 5;
                        worksheet.Column(27).Width = 12;
                        worksheet.Column(28).Width = 12;
                        worksheet.Column(29).Width = 12;
                        worksheet.Column(30).Width = 12;
                        worksheet.Column(31).Width = 12;
                        worksheet.Column(32).Width = 12;
                        worksheet.Column(33).Width = 12;
                        worksheet.Column(34).Width = 12;
                        worksheet.Column(35).Width = 12;

                        #endregion

                        for (int i = 0; i < machineOEE.Count; i++)
                        {
                            string dateStr = machineOEE[i].Date + " " + machineOEE[i].Shift;
                            worksheet.Cells[i + 2, 1].Value = i + 1;
                            worksheet.Cells[i + 2, 2].Value = dateStr;
                            worksheet.Cells[i + 2, 3].Value = (int)machineOEE[i].RunTimeRate;
                            worksheet.Cells[i + 2, 4].Value = (int)machineOEE[i].IdelTimeRate;
                            worksheet.Cells[i + 2, 5].Value = (int)machineOEE[i].DownTimeRate;
                            worksheet.Cells[i + 2, 6].Value = (int)machineOEE[i].LoadMATTimeRate;
                            worksheet.Cells[i + 2, 7].Value = machineOEE[i].Outputs;
                            worksheet.Cells[i + 2, 8].Value = machineOEE[i].RunTime / 60;
                            worksheet.Cells[i + 2, 9].Value = machineOEE[i].IdelTime / 60;
                            worksheet.Cells[i + 2, 10].Value = machineOEE[i].DownTime / 60;
                            worksheet.Cells[i + 2, 11].Value = machineOEE[i].LoadMATTime / 60;
                            worksheet.Cells[i + 2, 12].Value
                                = (machineOEE[i].RunTime + machineOEE[i].IdelTime + machineOEE[i].DownTime + machineOEE[i].LoadMATTime) / 60;
                            if (OEE_Temp1.ContainsKey(dateStr))
                            {
                                OEE_Temp1[dateStr][0] += machineOEE[i].Outputs;
                                OEE_Temp1[dateStr][1] += machineOEE[i].RunTime;
                                OEE_Temp1[dateStr][2] += machineOEE[i].IdelTime;
                                OEE_Temp1[dateStr][3] += machineOEE[i].DownTime;
                                OEE_Temp1[dateStr][4] += machineOEE[i].LoadMATTime;
                                OEE_Temp1[dateStr][5]
                                    += machineOEE[i].RunTime + machineOEE[i].IdelTime + machineOEE[i].DownTime + machineOEE[i].LoadMATTime;
                            }
                            else
                            {
                                List<int> list_temp = new List<int>();
                                list_temp.Add(machineOEE[i].Outputs);
                                list_temp.Add(machineOEE[i].RunTime);
                                list_temp.Add(machineOEE[i].IdelTime);
                                list_temp.Add(machineOEE[i].DownTime);
                                list_temp.Add(machineOEE[i].LoadMATTime);
                                list_temp.Add(machineOEE[i].RunTime + machineOEE[i].IdelTime + machineOEE[i].DownTime + machineOEE[i].LoadMATTime);
                                OEE_Temp1.Add(dateStr, list_temp);
                            }
                        }

                        if (machineHourDatas != null)
                        {
                            if (machineHourDatas.Count == 24)
                            {
                                List<string> temp = new List<string>();
                                {
                                    int autoSum = 0;
                                    int alarmTimeSum = 0;
                                    int idelSum = 0;
                                    int outputSum = 0;
                                    double ttSum = 0;
                                    int alarmSum = 0;
                                    int loadTimeSum = 0;
                                    for (int i = 0; i < 12; i++)
                                    {
                                        int auto = (int)Math.Round(machineHourDatas[i].AutoRunTime.Value / 60.0);
                                        autoSum += auto;
                                        int alarmTime = (int)Math.Round(machineHourDatas[i].AlarmTime.Value / 60.0);
                                        alarmTimeSum += alarmTime;
                                        int idel = (int)Math.Round(machineHourDatas[i].IdleTime.Value / 60.0);
                                        idelSum += idel;
                                        int output = machineHourDatas[i].OutPut.Value;
                                        outputSum += output;
                                        double tt = machineHourDatas[i].TT;
                                        //ttSum += tt;
                                        int alarm = machineHourDatas[i].AlarmSum.Value;
                                        alarmSum += alarm;
                                        int loadTime = (int)Math.Round(machineHourDatas[i].LoadMATTime.Value / 60.0);
                                        loadTimeSum += loadTime;

                                        string periodStr = machineHourDatas[i].Period;
                                        string moduleTypeStr = machineHourDatas[i].ModuleType;

                                        if (ModuleType[i] == null || ModuleType[i].Count == 0 || !ModuleType[i].Contains(moduleTypeStr))
                                        {
                                            ModuleType[i].Add(moduleTypeStr);
                                        }

                                        worksheet.Cells[i + 3, 15].Value = i + 1;
                                        worksheet.Cells[i + 3, 16].Value = periodStr;
                                        worksheet.Cells[i + 3, 17].Value = machineHourDatas[i].ModuleType;
                                        worksheet.Cells[i + 3, 18].Value = auto;
                                        worksheet.Cells[i + 3, 19].Value = alarmTime;
                                        worksheet.Cells[i + 3, 20].Value = idel;
                                        worksheet.Cells[i + 3, 21].Value = loadTime;
                                        worksheet.Cells[i + 3, 22].Value = output;
                                        worksheet.Cells[i + 3, 23].Value = Math.Round(tt, 2);
                                        worksheet.Cells[i + 3, 24].Value = alarm;

                                        if (OEE_Temp2.ContainsKey(periodStr))
                                        {
                                            OEE_Temp2[periodStr][0] += auto;
                                            OEE_Temp2[periodStr][1] += alarmTime;
                                            OEE_Temp2[periodStr][2] += idel;
                                            OEE_Temp2[periodStr][3] += loadTime;
                                            OEE_Temp2[periodStr][4] += output;
                                            OEE_Temp2[periodStr][5] += alarm;
                                        }
                                        else
                                        {
                                            List<int> list_temp = new List<int>();
                                            list_temp.Add(auto);
                                            list_temp.Add(alarmTime);
                                            list_temp.Add(idel);
                                            list_temp.Add(loadTime);
                                            list_temp.Add(output);
                                            list_temp.Add(alarm);
                                            OEE_Temp2.Add(periodStr, list_temp);
                                        }

                                    }

                                    worksheet.Cells[15, 15].Value = "合计";
                                    worksheet.Cells[15, 15, 15, 17].Merge = true;
                                    worksheet.Cells[15, 18].Value = autoSum;
                                    worksheet.Cells[15, 19].Value = alarmTimeSum;
                                    worksheet.Cells[15, 20].Value = idelSum;
                                    worksheet.Cells[15, 21].Value = loadTimeSum;
                                    worksheet.Cells[15, 22].Value = outputSum;
                                    if (outputSum != 0)
                                        ttSum = autoSum * 60.0 / outputSum;
                                    else
                                        ttSum = 0;
                                    worksheet.Cells[15, 23].Value = Math.Round(ttSum, 2);
                                    worksheet.Cells[15, 24].Value = alarmSum;

                                    var faults = _faultService.GetFaultFrequencyTop10(machineId, startTime, startTime.AddHours(12), "安全门,门禁,提示上料,提示卸料,门锁,门打开,请选择LOT生产");

                                    for (int i = 0; i < faults.Count; i++)
                                    {
                                        worksheet.Cells[17 + i, 15].Value = i + 1;
                                        worksheet.Cells[17 + i, 16].Value = faults[i].FaultCode;
                                        worksheet.Cells[17 + i, 17].Value = faults[i].FaultInfo;
                                        worksheet.Cells[17 + i, 24].Value = faults[i].Count;
                                    }
                                }

                                {
                                    int autoSum = 0;
                                    int alarmTimeSum = 0;
                                    int idelSum = 0;
                                    int outputSum = 0;
                                    double ttSum = 0;
                                    int alarmSum = 0;
                                    int loadTimeSum = 0;
                                    for (int i = 12; i < 24; i++)
                                    {
                                        int auto = (int)Math.Round(machineHourDatas[i].AutoRunTime.Value / 60.0);
                                        autoSum += auto;
                                        int alarmTime = (int)Math.Round(machineHourDatas[i].AlarmTime.Value / 60.0);
                                        alarmTimeSum += alarmTime;
                                        int idel = (int)Math.Round(machineHourDatas[i].IdleTime.Value / 60.0);
                                        idelSum += idel;
                                        int output = machineHourDatas[i].OutPut.Value;
                                        outputSum += output;
                                        double tt = machineHourDatas[i].TT;
                                        //ttSum += tt;
                                        int alarm = machineHourDatas[i].AlarmSum.Value;
                                        alarmSum += alarm;
                                        int loadTime = (int)Math.Round(machineHourDatas[i].LoadMATTime.Value / 60.0);
                                        loadTimeSum += loadTime;

                                        string periodStr = machineHourDatas[i].Period;
                                        string moduleTypeStr = machineHourDatas[i].ModuleType;

                                        if (ModuleType[i] == null || ModuleType[i].Count == 0 || !ModuleType[i].Contains(moduleTypeStr))
                                        {
                                            ModuleType[i].Add(moduleTypeStr);
                                        }

                                        worksheet.Cells[i - 12 + 3, 26].Value = i + 1;
                                        worksheet.Cells[i - 12 + 3, 27].Value = machineHourDatas[i].Period;
                                        worksheet.Cells[i - 12 + 3, 28].Value = machineHourDatas[i].ModuleType;
                                        worksheet.Cells[i - 12 + 3, 29].Value = auto;
                                        worksheet.Cells[i - 12 + 3, 30].Value = alarmTime;
                                        worksheet.Cells[i - 12 + 3, 31].Value = idel;
                                        worksheet.Cells[i - 12 + 3, 32].Value = loadTime;
                                        worksheet.Cells[i - 12 + 3, 33].Value = output;
                                        worksheet.Cells[i - 12 + 3, 34].Value = Math.Round(tt, 2);
                                        worksheet.Cells[i - 12 + 3, 35].Value = alarm;

                                        if (OEE_Temp2.ContainsKey(periodStr))
                                        {
                                            OEE_Temp2[periodStr][0] += auto;
                                            OEE_Temp2[periodStr][1] += alarmTime;
                                            OEE_Temp2[periodStr][2] += idel;
                                            OEE_Temp2[periodStr][3] += loadTime;
                                            OEE_Temp2[periodStr][4] += output;
                                            OEE_Temp2[periodStr][5] += alarm;
                                        }
                                        else
                                        {
                                            List<int> list_temp = new List<int>();
                                            list_temp.Add(auto);
                                            list_temp.Add(alarmTime);
                                            list_temp.Add(idel);
                                            list_temp.Add(loadTime);
                                            list_temp.Add(output);
                                            list_temp.Add(alarm);
                                            OEE_Temp2.Add(periodStr, list_temp);
                                        }
                                    }

                                    worksheet.Cells[15, 26].Value = "合计";
                                    worksheet.Cells[15, 26, 15, 28].Merge = true;
                                    worksheet.Cells[15, 29].Value = autoSum;
                                    worksheet.Cells[15, 30].Value = alarmTimeSum;
                                    worksheet.Cells[15, 31].Value = idelSum;
                                    worksheet.Cells[15, 32].Value = loadTimeSum;
                                    worksheet.Cells[15, 33].Value = outputSum;
                                    if (outputSum != 0)
                                        ttSum = autoSum * 60.0 / outputSum;
                                    else
                                        ttSum = 0;
                                    worksheet.Cells[15, 34].Value = Math.Round(ttSum, 2);
                                    worksheet.Cells[15, 35].Value = alarmSum;

                                    var faults = _faultService.GetFaultFrequencyTop10(machineId, startTime.AddHours(12), startTime.AddHours(24), "安全门,门禁,提示上料,提示卸料,门锁,门打开,请选择LOT生产");

                                    for (int i = 0; i < faults.Count; i++)
                                    {
                                        worksheet.Cells[17 + i, 26].Value = i + 1;
                                        worksheet.Cells[17 + i, 27].Value = faults[i].FaultCode;
                                        worksheet.Cells[17 + i, 28].Value = faults[i].FaultInfo;
                                        worksheet.Cells[17 + i, 35].Value = faults[i].Count;
                                    }
                                }
                            }
                        }

                        //var faultFrequencys = _faultService.GetFaultFrequencyTop10ByShift(
                        //    machine.Id, dataStartTime, endTime, "安全门,门禁,提示上料,提示卸料,门锁");

                        //var tables = new FaultFrequencyByShiftTable(faultFrequencys);

                        //ExcelWorksheet worksheet1 = package.Workbook.Worksheets.Add($"{line.Name} {machine.Name} 报警");
                        //worksheet1.View.ZoomScale = 80;
                        //worksheet1.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                        //worksheet1.Column(1).Width = 5;
                        //worksheet1.Column(2).Width = 10;
                        //worksheet1.Column(3).Width = 30;

                        //worksheet1.Cells[1, 1].Value = "NO";
                        //worksheet1.Cells[1, 2].Value = "Code";
                        //worksheet1.Cells[1, 3].Value = "Name";

                        //for (int i = 0; i < tables.Shifts.Count(); i++)
                        //{
                        //    worksheet1.Column(i + 4).Width = 20;
                        //    worksheet1.Cells[1, i + 4].Value = tables.Shifts[i];
                        //}

                        //for (int i = 0; i < tables.Nos.Count; i++)
                        //{
                        //    worksheet1.Cells[i + 2, 1].Value = tables.Nos[i];
                        //    worksheet1.Cells[i + 2, 2].Value = tables.FaultCodes[i];
                        //    worksheet1.Cells[i + 2, 3].Value = tables.FaultInfos[i];
                        //    for (int j = 0; j < tables.Shifts.Count(); j++)
                        //    {
                        //        worksheet1.Cells[i + 2, j + 4].Value = tables.Counts[j, i];
                        //    }
                        //}

                        //var databar = worksheet1.Cells["D2:Q33"].ConditionalFormatting.AddDatabar(Color.Red);

                        //// 创建数据条条件格式对象
                        //ExcelConditionalFormattingDataBar dataBarConditionalFormatting = (ExcelConditionalFormattingDataBar)worksheet.Cells["D2:Q33"].ConditionalFormatting;
                        //// 设置渐变填充
                        //dataBarConditionalFormatting.BarFill.Type = ExcelFillStyle.DarkGray;

                        ExcelChartSerie chartSerie;
                        ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnStacked);

                        chart.YAxis.MinValue = 0;
                        chart.YAxis.MaxValue = 100;
                        chart.YAxis.Format = "0 \"%\"";

                        chart.Legend.Position = eLegendPosition.Bottom;
                        chart.Legend.Add();
                        chart.Title.Text = "设备稼动曲线";    //设置图表的名称   
                        chart.SetPosition(540, 20);         //设置图表位置   
                        chart.SetSize(1000, 400);           //设置图表大小   
                        chart.ShowHiddenData = true;

                        chartSerie = chart.Series.Add(worksheet.Cells[2, 3, machineOEE.Count + 1, 3], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
                        chartSerie.HeaderAddress = worksheet.Cells[1, 3];
                        chartSerie = chart.Series.Add(worksheet.Cells[2, 4, machineOEE.Count + 1, 4], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
                        chartSerie.HeaderAddress = worksheet.Cells[1, 4];
                        chartSerie = chart.Series.Add(worksheet.Cells[2, 5, machineOEE.Count + 1, 5], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
                        chartSerie.HeaderAddress = worksheet.Cells[1, 5];
                        chartSerie = chart.Series.Add(worksheet.Cells[2, 6, machineOEE.Count + 1, 6], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
                        chartSerie.HeaderAddress = worksheet.Cells[1, 6];

                        //var chartType2 = chart.PlotArea.ChartTypes.Add(eChartType.Line);
                        //chartType2.UseSecondaryAxis = true;
                        //chartType2.YAxis.Format = "0 \"pcs\"";
                        //chartSerie = chartType2.Series.Add(worksheet.Cells[2, 6, machineOEE.Count + 1, 6], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
                        //chartSerie.HeaderAddress = worksheet.Cells[1, 6];

                        foreach (var item in chart.Series)
                        {
                            var pieSerie = (ExcelBarChartSerie)item;
                            pieSerie.DataLabel.ShowValue = true;
                        }

                        if (HideEquipmentProduction == null || HideEquipmentProduction.Count() == 0 || !HideEquipmentProduction.Contains(machineId))
                        {
                            ExcelChart chart1 = worksheet.Drawings.AddChart("chart1", eChartType.ColumnStacked);
                            chart1.Legend.Position = eLegendPosition.Bottom;
                            chart1.Legend.Add();
                            chart1.Title.Text = "设备产能曲线";    //设置图表的名称   
                            chart1.SetPosition(540, 1030);         //设置图表位置   
                            chart1.SetSize(1000, 400);           //设置图表大小   
                            chart1.ShowHiddenData = true;
                            chart1.YAxis.Format = "0 \"pcs\"";
                            ExcelChartSerie chartSerie1;
                            chartSerie1 = chart1.Series.Add(worksheet.Cells[2, 7, machineOEE.Count + 1, 7], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
                            chartSerie1.HeaderAddress = worksheet.Cells[1, 7];
                            foreach (var item in chart1.Series)
                            {
                                var pieSerie = (ExcelBarChartSerie)item;
                                pieSerie.DataLabel.ShowValue = true;
                            }
                        }

                        package.Save();
                    }
                    if ((bool)UtilizationRateEmail["DataSummary"]["IsAble"])
                    {
                        FileInfo file = new FileInfo(path);

                        using ExcelPackage package = new ExcelPackage(file);

                        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add($"汇总");

                        worksheet.View.ZoomScale = 80;

                        worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                        #region tab header

                        worksheet.Cells[1, 1].Value = "NO";
                        worksheet.Cells[1, 2].Value = "时间";
                        worksheet.Cells[1, 3].Value = "稼动率(%)";
                        worksheet.Cells[1, 4].Value = "待机率(%)";
                        worksheet.Cells[1, 5].Value = "报警率(%)";
                        worksheet.Cells[1, 6].Value = "换料率(%)";
                        worksheet.Cells[1, 7].Value = "产能(pcs)";
                        worksheet.Cells[1, 8].Value = "稼动(min)";
                        worksheet.Cells[1, 9].Value = "待机(min)";
                        worksheet.Cells[1, 10].Value = "报警(min)";
                        worksheet.Cells[1, 11].Value = "换料(min)";
                        worksheet.Cells[1, 12].Value = "合计(min)";

                        worksheet.Cells[1, 15].Value = $"{startTime:yyyy-MM-dd} 早班";
                        worksheet.Cells[1, 15, 1, 24].Merge = true;

                        worksheet.Cells[2, 15].Value = "NO";
                        worksheet.Cells[2, 16].Value = "时段";
                        worksheet.Cells[2, 17].Value = "机种";
                        worksheet.Cells[2, 18].Value = "运行时间";
                        worksheet.Cells[2, 19].Value = "报警时间";
                        worksheet.Cells[2, 20].Value = "待料时间";
                        worksheet.Cells[2, 21].Value = "换料时间";
                        worksheet.Cells[2, 22].Value = "产能";
                        worksheet.Cells[2, 23].Value = "TT";
                        worksheet.Cells[2, 24].Value = "报警次数";

                        worksheet.Cells[1, 26].Value = $"{startTime:yyyy-MM-dd} 夜班";
                        worksheet.Cells[1, 26, 1, 35].Merge = true;

                        worksheet.Cells[2, 26].Value = "NO";
                        worksheet.Cells[2, 27].Value = "时段";
                        worksheet.Cells[2, 28].Value = "机种";
                        worksheet.Cells[2, 29].Value = "运行时间";
                        worksheet.Cells[2, 30].Value = "报警时间";
                        worksheet.Cells[2, 31].Value = "待料时间";
                        worksheet.Cells[2, 32].Value = "换料时间";
                        worksheet.Cells[2, 33].Value = "产能";
                        worksheet.Cells[2, 34].Value = "TT";
                        worksheet.Cells[2, 35].Value = "报警次数";

                        worksheet.Column(1).Width = 5;
                        worksheet.Column(2).Width = 12;
                        worksheet.Column(3).Width = 12;
                        worksheet.Column(4).Width = 12;
                        worksheet.Column(5).Width = 12;
                        worksheet.Column(6).Width = 12;
                        worksheet.Column(7).Width = 12;
                        worksheet.Column(8).Width = 12;
                        worksheet.Column(9).Width = 12;
                        worksheet.Column(10).Width = 12;
                        worksheet.Column(11).Width = 12;
                        worksheet.Column(12).Width = 12;

                        worksheet.Column(13).Width = 2;
                        worksheet.Column(14).Width = 2;

                        worksheet.Column(15).Width = 5;
                        worksheet.Column(16).Width = 12;
                        worksheet.Column(17).Width = 12;
                        worksheet.Column(18).Width = 12;
                        worksheet.Column(19).Width = 12;
                        worksheet.Column(20).Width = 12;
                        worksheet.Column(21).Width = 12;
                        worksheet.Column(22).Width = 12;
                        worksheet.Column(23).Width = 12;
                        worksheet.Column(24).Width = 12;
                        worksheet.Column(22).Width = 12;
                        worksheet.Column(23).Width = 12;

                        worksheet.Column(25).Width = 2;

                        worksheet.Column(26).Width = 5;
                        worksheet.Column(27).Width = 12;
                        worksheet.Column(28).Width = 12;
                        worksheet.Column(29).Width = 12;
                        worksheet.Column(30).Width = 12;
                        worksheet.Column(31).Width = 12;
                        worksheet.Column(32).Width = 12;
                        worksheet.Column(33).Width = 12;
                        worksheet.Column(34).Width = 12;
                        worksheet.Column(35).Width = 12;

                        #endregion

                        List<string> OEE1Keys = new List<string>(OEE_Temp1.Keys);
                        for (int i = 0; i < OEE1Keys.Count(); i++)
                        {
                            int total = OEE_Temp1[OEE1Keys[i]][5];
                            worksheet.Cells[i + 2, 1].Value = i + 1;
                            worksheet.Cells[i + 2, 2].Value = OEE1Keys[i];
                            worksheet.Cells[i + 2, 3].Value = Math.Round(OEE_Temp1[OEE1Keys[i]][1] * 100.0 / total);
                            worksheet.Cells[i + 2, 4].Value = Math.Round(OEE_Temp1[OEE1Keys[i]][2] * 100.0 / total);
                            worksheet.Cells[i + 2, 5].Value = Math.Round(OEE_Temp1[OEE1Keys[i]][3] * 100.0 / total);
                            worksheet.Cells[i + 2, 6].Value = Math.Round(OEE_Temp1[OEE1Keys[i]][4] * 100.0 / total);
                            worksheet.Cells[i + 2, 7].Value = OEE_Temp1[OEE1Keys[i]][0];
                            worksheet.Cells[i + 2, 8].Value = OEE_Temp1[OEE1Keys[i]][1] / 60;
                            worksheet.Cells[i + 2, 9].Value = OEE_Temp1[OEE1Keys[i]][2] / 60;
                            worksheet.Cells[i + 2, 10].Value = OEE_Temp1[OEE1Keys[i]][3] / 60;
                            worksheet.Cells[i + 2, 11].Value = OEE_Temp1[OEE1Keys[i]][4] / 60;
                            worksheet.Cells[i + 2, 12].Value = total / 60;
                        }

                        if (OEE_Temp2 != null && OEE_Temp2.Count == 24)
                        {
                            List<string> OEE2Keys = new List<string>(OEE_Temp2.Keys);
                            {
                                int autoTimeSum = 0;
                                int alarmTimeSum = 0;
                                int idelTimeSum = 0;
                                int loadTimeSum = 0;
                                int outputSum = 0;
                                int alarmSum = 0;
                                double ttSum;
                                for (int i = 0; i < 12; i++)
                                {

                                    int autoTime = OEE_Temp2[OEE2Keys[i]][0];
                                    autoTimeSum += autoTime;
                                    int alarmTime = OEE_Temp2[OEE2Keys[i]][1];
                                    alarmTimeSum += alarmTime;
                                    int idelTime = OEE_Temp2[OEE2Keys[i]][2];
                                    idelTimeSum += idelTime;
                                    int loadTime = OEE_Temp2[OEE2Keys[i]][3];
                                    loadTimeSum += loadTime;
                                    int output = OEE_Temp2[OEE2Keys[i]][4];
                                    outputSum += output;
                                    int alarm = OEE_Temp2[OEE2Keys[i]][5];
                                    alarmSum += alarm;
                                    worksheet.Cells[i + 3, 15].Value = i + 1;
                                    worksheet.Cells[i + 3, 16].Value = OEE2Keys[i];
                                    worksheet.Cells[i + 3, 17].Value = String.Join(",", ModuleType[i]);
                                    worksheet.Cells[i + 3, 18].Value = autoTime;
                                    worksheet.Cells[i + 3, 19].Value = alarmTime;
                                    worksheet.Cells[i + 3, 20].Value = idelTime;
                                    worksheet.Cells[i + 3, 21].Value = loadTimeSum;
                                    worksheet.Cells[i + 3, 22].Value = output;
                                    worksheet.Cells[i + 3, 23].Value = output == 0 ? 0 : Math.Round(OEE_Temp2[OEE2Keys[i]][0] * 60.0 / OEE_Temp2[OEE2Keys[i]][4], 2);
                                    worksheet.Cells[i + 3, 24].Value = alarm;
                                }

                                worksheet.Cells[15, 15].Value = "合计";
                                worksheet.Cells[15, 15, 15, 17].Merge = true;
                                worksheet.Cells[15, 18].Value = autoTimeSum;
                                worksheet.Cells[15, 19].Value = alarmTimeSum;
                                worksheet.Cells[15, 20].Value = idelTimeSum;
                                worksheet.Cells[15, 21].Value = loadTimeSum;
                                worksheet.Cells[15, 22].Value = outputSum;
                                if (outputSum != 0)
                                    ttSum = autoTimeSum * 60.0 / outputSum;
                                else
                                    ttSum = 0;
                                worksheet.Cells[15, 23].Value = Math.Round(ttSum, 2);
                                worksheet.Cells[15, 24].Value = alarmSum;
                            }

                            {
                                int autoTimeSum = 0;
                                int alarmTimeSum = 0;
                                int idelTimeSum = 0;
                                int loadTimeSum = 0;
                                int outputSum = 0;
                                int alarmSum = 0;
                                double ttSum;
                                for (int i = 12; i < 24; i++)
                                {

                                    int autoTime = OEE_Temp2[OEE2Keys[i]][0]; ;
                                    autoTimeSum += autoTime;
                                    int alarmTime = OEE_Temp2[OEE2Keys[i]][1];
                                    alarmTimeSum += alarmTime;
                                    int idelTime = OEE_Temp2[OEE2Keys[i]][2];
                                    idelTimeSum += idelTime;
                                    int loadTime = OEE_Temp2[OEE2Keys[i]][3];
                                    loadTimeSum += loadTime;
                                    int output = OEE_Temp2[OEE2Keys[i]][4];
                                    outputSum += output;
                                    int alarm = OEE_Temp2[OEE2Keys[i]][5];
                                    alarmSum += alarm;
                                    worksheet.Cells[i - 12 + 3, 26].Value = i + 1;
                                    worksheet.Cells[i - 12 + 3, 27].Value = OEE2Keys[i];
                                    worksheet.Cells[i - 12 + 3, 28].Value = String.Join(",", ModuleType[i]);
                                    worksheet.Cells[i - 12 + 3, 29].Value = autoTime;
                                    worksheet.Cells[i - 12 + 3, 30].Value = alarmTime;
                                    worksheet.Cells[i - 12 + 3, 31].Value = idelTime;
                                    worksheet.Cells[i - 12 + 3, 32].Value = loadTimeSum;
                                    worksheet.Cells[i - 12 + 3, 33].Value = output;
                                    worksheet.Cells[i - 12 + 3, 34].Value = output == 0 ? 0 : Math.Round(OEE_Temp2[OEE2Keys[i]][0] * 60.0 / OEE_Temp2[OEE2Keys[i]][4], 2);
                                    worksheet.Cells[i - 12 + 3, 35].Value = alarm;
                                }

                                worksheet.Cells[15, 26].Value = "合计";
                                worksheet.Cells[15, 26, 15, 28].Merge = true;
                                worksheet.Cells[15, 29].Value = autoTimeSum;
                                worksheet.Cells[15, 30].Value = alarmTimeSum;
                                worksheet.Cells[15, 31].Value = idelTimeSum;
                                worksheet.Cells[15, 32].Value = loadTimeSum;
                                worksheet.Cells[15, 33].Value = outputSum;
                                if (outputSum != 0)
                                    ttSum = autoTimeSum * 60.0 / outputSum;
                                else
                                    ttSum = 0;
                                worksheet.Cells[15, 34].Value = Math.Round(ttSum, 2);
                                worksheet.Cells[15, 35].Value = alarmSum;

                            }
                        }

                        ExcelChartSerie chartSerie;
                        ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnStacked);

                        chart.YAxis.MinValue = 0;
                        chart.YAxis.MaxValue = 100;
                        chart.YAxis.Format = "0 \"%\"";

                        chart.Legend.Position = eLegendPosition.Bottom;
                        chart.Legend.Add();
                        chart.Title.Text = "设备稼动曲线";    //设置图表的名称   
                        chart.SetPosition(540, 20);         //设置图表位置   
                        chart.SetSize(1000, 400);           //设置图表大小   
                        chart.ShowHiddenData = true;

                        chartSerie = chart.Series.Add(worksheet.Cells[2, 3, OEE_Temp1.Count() + 1, 3], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
                        chartSerie.HeaderAddress = worksheet.Cells[1, 3];
                        chartSerie = chart.Series.Add(worksheet.Cells[2, 4, OEE_Temp1.Count() + 1, 4], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
                        chartSerie.HeaderAddress = worksheet.Cells[1, 4];
                        chartSerie = chart.Series.Add(worksheet.Cells[2, 5, OEE_Temp1.Count() + 1, 5], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
                        chartSerie.HeaderAddress = worksheet.Cells[1, 5];
                        chartSerie = chart.Series.Add(worksheet.Cells[2, 6, OEE_Temp1.Count() + 1, 6], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
                        chartSerie.HeaderAddress = worksheet.Cells[1, 6];


                        foreach (var item in chart.Series)
                        {
                            var pieSerie = (ExcelBarChartSerie)item;
                            pieSerie.DataLabel.ShowValue = true;
                        }

                        if (!(bool)UtilizationRateEmail["DataSummary"]["HideProductionUtilizationChart"])
                        {
                            ExcelChart chart1 = worksheet.Drawings.AddChart("chart1", eChartType.ColumnStacked);
                            chart1.Legend.Position = eLegendPosition.Bottom;
                            chart1.Legend.Add();
                            chart1.Title.Text = "设备产能曲线";    //设置图表的名称   
                            chart1.SetPosition(540, 1030);         //设置图表位置   
                            chart1.SetSize(1000, 400);           //设置图表大小   
                            chart1.ShowHiddenData = true;
                            chart1.YAxis.Format = "0 \"pcs\"";
                            ExcelChartSerie chartSerie1;
                            chartSerie1 = chart1.Series.Add(worksheet.Cells[2, 7, OEE_Temp1.Count() + 1, 7], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
                            chartSerie1.HeaderAddress = worksheet.Cells[1, 7];
                            foreach (var item in chart1.Series)
                            {
                                var pieSerie = (ExcelBarChartSerie)item;
                                pieSerie.DataLabel.ShowValue = true;
                            }
                        }
                        package.Workbook.Worksheets.MoveToStart("汇总");
                        package.Save();
                    }

                    string ReceiverAppSettingName = UtilizationRateEmail["ReceiverAppSettingName"].ToString();
                    if (ReceiverAppSettingName == null || String.IsNullOrEmpty(ReceiverAppSettingName))
                    {
                        LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} ReceiverAppSettingName is null");
                        continue;
                    }
                    SendMail(
                        MailType.Message,
                        ReceiverAppSettingName,
                        "",
                        "",
                        "EQP.EAPAUTO01@eink.com",
                        "",
                        $"稼动数据 -- {dateNowStr}",
                        "",
                        new string[] { path },
                        new string[] { dateNowStr },
                        Encoding.UTF8);

                    LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} Send over, ReceiverAppSettingName:【{ReceiverAppSettingName}】, file:【{path}】, EmailFileName:【{dateNowStr}】");
                }
                return true;
            }
            catch (Exception ex)
            {
                LogerHelper.RecordLogTxt($"EquipmentOperationReport Exception, Message:{ex.Message}, StackTrace:{ex.StackTrace}");
                return false;
            }
        }
    }
}