ProductionLineMonitor/ProductionLineMonitor.Application/Services/ExcelService.cs

using Core.Dtos;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
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.Core.Dtos;
using ProductionLineMonitor.Core.IRepositories;
using System;
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)
        {
            string date = dateTime.ToString("yyyy-MM-dd");
            if (dateTime.Hour == 8) {
                date = dateTime.AddDays(-1).ToString("yyyy-MM-dd");
            }

            string machineIdString = "";
            string receiverAppSettingName = "";
            bool isSend = false;
            using (StreamReader file = File.OpenText("D:\\ReportForms\\Email.json"))
            {
                using JsonTextReader reader = new JsonTextReader(file);
                JObject jsonObject = (JObject)JToken.ReadFrom(reader);
                machineIdString = jsonObject["UtilizationRateEmail"]["MachineIDs"].ToString();
                receiverAppSettingName = jsonObject["UtilizationRateEmail"]["ReceiverAppSettingName"].ToString();
                isSend = jsonObject["UtilizationRateEmail"]["IsSend"].ToObject<bool>();
            }

            if (string.IsNullOrEmpty(machineIdString))
            {
                return false;
            }

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

            // 文件夹存储路径
            string folderPath = "D:\\ReportForms";
            if (!Directory.Exists(folderPath))
                Directory.CreateDirectory(folderPath);
            string excelName = $"{DateTime.Now:yyyyMMddHHmmss}.xlsx";
            var path = Path.Combine(folderPath, excelName);

            string[] machineIds = machineIdString.Split(',');
            foreach (var machineId in machineIds)
            {
                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++)
                {
                    worksheet.Cells[i + 2, 1].Value = i + 1;
                    worksheet.Cells[i + 2, 2].Value = machineOEE[i].Date + " " + machineOEE[i].Shift;
                    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 (machineHourDatas != null)
                {
                    if (machineHourDatas.Count == 24)
                    {
                        {
                            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 = machineHourDatas[i].AutoRunTime.Value / 60;
                                autoSum += auto;
                                int alarmTime = machineHourDatas[i].AlarmTime.Value / 60;
                                alarmTimeSum += alarmTime;
                                int idel = machineHourDatas[i].IdleTime.Value / 60;
                                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 = machineHourDatas[i].LoadMATTime.Value / 60;
                                loadTimeSum += loadTime;

                                worksheet.Cells[i + 3, 15].Value = i + 1;
                                worksheet.Cells[i + 3, 16].Value = machineHourDatas[i].Period;
                                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;
                            }

                            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), "安全门,门禁,提示上料,提示卸料");

                            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 = machineHourDatas[i].AutoRunTime.Value / 60;
                                autoSum += auto;
                                int alarmTime = machineHourDatas[i].AlarmTime.Value / 60;
                                alarmTimeSum += alarmTime;
                                int idel = machineHourDatas[i].IdleTime.Value / 60;
                                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 = machineHourDatas[i].LoadMATTime.Value / 60;
                                loadTimeSum += loadTime;

                                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;
                            }

                            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), "安全门,门禁,提示上料,提示卸料");

                            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;
                }

                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 (!isSend)
            {
                return false;
            }

            if (string.IsNullOrEmpty(receiverAppSettingName))
            {
                return false;
            }

            SendMail(
                MailType.Message,
                receiverAppSettingName,
                "",
                "",
                "EQP.EAPAUTO01@eink.com",
                "",
                $"稼动数据 -- {excelName}",
                "",
                new string[] { path },
                Encoding.UTF8);

            return true;
        }
    }
}