ExcelService.cs 49 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846
  1. using Core.Dtos;
  2. using Microsoft.Extensions.Logging;
  3. using Newtonsoft.Json;
  4. using Newtonsoft.Json.Linq;
  5. using NPOI.HSSF.Record;
  6. using NPOI.HSSF.Record.CF;
  7. using OfficeOpenXml;
  8. using OfficeOpenXml.ConditionalFormatting;
  9. using OfficeOpenXml.Drawing.Chart;
  10. using OfficeOpenXml.Style;
  11. using ProductionLineMonitor.Application.Services.FaultService;
  12. using ProductionLineMonitor.Application.Services.OEEService;
  13. using ProductionLineMonitor.Application.Services.OEEService.Dtos;
  14. using ProductionLineMonitor.Core.Dtos;
  15. using ProductionLineMonitor.Core.IRepositories;
  16. using ProductionLineMonitor.Core.Models;
  17. using System;
  18. using System.Collections.Generic;
  19. using System.Drawing;
  20. using System.IO;
  21. using System.Linq;
  22. using System.Security.Claims;
  23. using System.Text;
  24. using static ProductionLineMonitor.Core.Utils.EmailHelper;
  25. namespace ProductionLineMonitor.Application.Services
  26. {
  27. public class ExcelService : IExcelService
  28. {
  29. private readonly IUnitOfWork _unitOfWork;
  30. private readonly IOEEService _oeeService;
  31. private readonly IFaultService _faultService;
  32. public ExcelService(
  33. IUnitOfWork unitOfWork,
  34. IOEEService oEEService,
  35. IFaultService faultService)
  36. {
  37. _unitOfWork = unitOfWork;
  38. _oeeService = oEEService;
  39. _faultService = faultService;
  40. }
  41. public bool EquipmentOperationReport(DateTime dateTime)
  42. {
  43. try
  44. {
  45. string date = dateTime.ToString("yyyy-MM-dd");
  46. if (dateTime.Hour == 8)
  47. {
  48. date = dateTime.AddDays(-1).ToString("yyyy-MM-dd");
  49. }
  50. JToken? UtilizationRateEmailList;
  51. JToken? ExcelChartsSettings;
  52. using (StreamReader file = File.OpenText("D:\\ReportForms\\Email.json"))
  53. {
  54. using JsonTextReader reader = new JsonTextReader(file);
  55. JObject jsonObject = (JObject)JToken.ReadFrom(reader);
  56. UtilizationRateEmailList = jsonObject["UtilizationRateEmail"];
  57. ExcelChartsSettings = jsonObject["ExcelChartsSettings"];
  58. }
  59. if (UtilizationRateEmailList == null || UtilizationRateEmailList.Count() == 0)
  60. {
  61. return false;
  62. }
  63. DateTime startTime = DateTime.Parse($"{date} 08:00:00");
  64. DateTime endTime = startTime.AddDays(1);
  65. DateTime dataStartTime = endTime.AddDays(-7);
  66. int count = 0;
  67. // 文件夹存储路径
  68. string dateNowStr = DateTime.Now.ToString("yyyyMMddHHmmss");
  69. string folderPath = $"D:\\ReportForms\\{DateTime.Now:yyyyMM}\\{dateNowStr}";
  70. foreach (var UtilizationRateEmail in UtilizationRateEmailList)
  71. {
  72. count++;
  73. LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} Begin");
  74. if ((!(bool)UtilizationRateEmail["IsSend"]))
  75. {
  76. LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} isSend is False");
  77. continue;
  78. }
  79. string path = Path.Combine(folderPath, $"Group-{count}.xlsx");
  80. if (!Directory.Exists(folderPath))
  81. Directory.CreateDirectory(folderPath);
  82. string[] machineIdString = UtilizationRateEmail["MachineIDs"].ToString().Split(',');
  83. if (machineIdString == null || machineIdString.Length == 0)
  84. {
  85. LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} machineIdString is null or length is 0");
  86. continue;
  87. }
  88. var HideEquipmentProduction = ExcelChartsSettings["HideEquipmentProduction"].ToString().Split(',');
  89. //List<int> [产能,稼动,待机,报警,待料,合计]
  90. Dictionary<string, List<int>> OEE_Temp1 = new Dictionary<string, List<int>>();
  91. //List<int> [运行时间,报警时间,待料时间,换料时间,产能,报警次数]
  92. Dictionary<string, List<int>> OEE_Temp2 = new Dictionary<string, List<int>>();
  93. //机种
  94. List<List<string>> ModuleType = new List<List<string>>();
  95. for (int i = 0; i < 24; i++)
  96. ModuleType.Add(new List<string>());
  97. foreach (var machineId in machineIdString)
  98. {
  99. var machine = _unitOfWork.MachineRepository.GetById(machineId);
  100. var line = _unitOfWork.ProductionLineRepository.FirstOrDefault(x => x.Id == machine.ProductionLineId);
  101. var machineOEE = _oeeService.GetOEE(machineId, dataStartTime.ToString("yyyy-MM-dd"), startTime.ToString("yyyy-MM-dd"));
  102. var machineHourDatas = _unitOfWork.MachineOutPutPerHourRepository.GetList(
  103. x =>
  104. x.MachineId == machine.Id &&
  105. x.DataTime >= startTime &&
  106. x.DataTime < endTime).OrderBy(o => o.DataTime).ToList();
  107. var OEEExtendFlag = new[] { false, false, false, false, false };
  108. FileInfo file = new FileInfo(path);
  109. //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
  110. using ExcelPackage package = new ExcelPackage(file);
  111. ExcelWorksheet worksheet = package.Workbook.Worksheets.Add($"{line.Name} {machine.Name}");
  112. worksheet.View.ZoomScale = 80;
  113. worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
  114. #region tab header
  115. worksheet.Cells[1, 1].Value = "NO";
  116. worksheet.Cells[1, 2].Value = "时间";
  117. worksheet.Cells[1, 3].Value = "稼动率(%)";
  118. worksheet.Cells[1, 4].Value = "待机率(%)";
  119. worksheet.Cells[1, 5].Value = "报警率(%)";
  120. worksheet.Cells[1, 6].Value = "换料率(%)";
  121. worksheet.Cells[1, 7].Value = "产能(pcs)";
  122. worksheet.Cells[1, 8].Value = "稼动(min)";
  123. worksheet.Cells[1, 9].Value = "待机(min)";
  124. worksheet.Cells[1, 10].Value = "报警(min)";
  125. worksheet.Cells[1, 11].Value = "换料(min)";
  126. worksheet.Cells[1, 12].Value = "合计(min)";
  127. worksheet.Cells[1, 15].Value = $"{startTime:yyyy-MM-dd} 早班";
  128. worksheet.Cells[1, 15, 1, 24].Merge = true;
  129. worksheet.Cells[2, 15].Value = "NO";
  130. worksheet.Cells[2, 16].Value = "时段";
  131. worksheet.Cells[2, 17].Value = "机种";
  132. worksheet.Cells[2, 18].Value = "运行时间";
  133. worksheet.Cells[2, 19].Value = "报警时间";
  134. worksheet.Cells[2, 20].Value = "待料时间";
  135. worksheet.Cells[2, 21].Value = "换料时间";
  136. worksheet.Cells[2, 22].Value = "产能";
  137. worksheet.Cells[2, 23].Value = "TT";
  138. worksheet.Cells[2, 24].Value = "报警次数";
  139. worksheet.Cells[16, 15].Value = "NO";
  140. worksheet.Cells[16, 16].Value = "故障码";
  141. worksheet.Cells[16, 17].Value = "故障详情";
  142. worksheet.Cells[16, 24].Value = "报警次数";
  143. worksheet.Cells[16, 17, 16, 23].Merge = true;
  144. worksheet.Cells[17, 17, 17, 23].Merge = true;
  145. worksheet.Cells[18, 17, 18, 23].Merge = true;
  146. worksheet.Cells[19, 17, 19, 23].Merge = true;
  147. worksheet.Cells[20, 17, 20, 23].Merge = true;
  148. worksheet.Cells[21, 17, 21, 23].Merge = true;
  149. worksheet.Cells[22, 17, 22, 23].Merge = true;
  150. worksheet.Cells[23, 17, 23, 23].Merge = true;
  151. worksheet.Cells[24, 17, 24, 23].Merge = true;
  152. worksheet.Cells[25, 17, 25, 23].Merge = true;
  153. worksheet.Cells[26, 17, 26, 23].Merge = true;
  154. worksheet.Cells[1, 26].Value = $"{startTime:yyyy-MM-dd} 夜班";
  155. worksheet.Cells[1, 26, 1, 35].Merge = true;
  156. worksheet.Cells[2, 26].Value = "NO";
  157. worksheet.Cells[2, 27].Value = "时段";
  158. worksheet.Cells[2, 28].Value = "机种";
  159. worksheet.Cells[2, 29].Value = "运行时间";
  160. worksheet.Cells[2, 30].Value = "报警时间";
  161. worksheet.Cells[2, 31].Value = "待料时间";
  162. worksheet.Cells[2, 32].Value = "换料时间";
  163. worksheet.Cells[2, 33].Value = "产能";
  164. worksheet.Cells[2, 34].Value = "TT";
  165. worksheet.Cells[2, 35].Value = "报警次数";
  166. worksheet.Cells[16, 26].Value = "NO";
  167. worksheet.Cells[16, 27].Value = "故障码";
  168. worksheet.Cells[16, 28].Value = "故障详情";
  169. worksheet.Cells[16, 35].Value = "报警次数";
  170. worksheet.Cells[16, 28, 16, 34].Merge = true;
  171. worksheet.Cells[17, 28, 17, 34].Merge = true;
  172. worksheet.Cells[18, 28, 18, 34].Merge = true;
  173. worksheet.Cells[19, 28, 19, 34].Merge = true;
  174. worksheet.Cells[20, 28, 20, 34].Merge = true;
  175. worksheet.Cells[21, 28, 21, 34].Merge = true;
  176. worksheet.Cells[22, 28, 22, 34].Merge = true;
  177. worksheet.Cells[23, 28, 23, 34].Merge = true;
  178. worksheet.Cells[24, 28, 24, 34].Merge = true;
  179. worksheet.Cells[25, 28, 25, 34].Merge = true;
  180. worksheet.Cells[26, 28, 26, 34].Merge = true;
  181. worksheet.Column(1).Width = 5;
  182. worksheet.Column(2).Width = 12;
  183. worksheet.Column(3).Width = 12;
  184. worksheet.Column(4).Width = 12;
  185. worksheet.Column(5).Width = 12;
  186. worksheet.Column(6).Width = 12;
  187. worksheet.Column(7).Width = 12;
  188. worksheet.Column(8).Width = 12;
  189. worksheet.Column(9).Width = 12;
  190. worksheet.Column(10).Width = 12;
  191. worksheet.Column(11).Width = 12;
  192. worksheet.Column(12).Width = 12;
  193. worksheet.Column(13).Width = 2;
  194. worksheet.Column(14).Width = 2;
  195. worksheet.Column(15).Width = 5;
  196. worksheet.Column(16).Width = 12;
  197. worksheet.Column(17).Width = 12;
  198. worksheet.Column(18).Width = 12;
  199. worksheet.Column(19).Width = 12;
  200. worksheet.Column(20).Width = 12;
  201. worksheet.Column(21).Width = 12;
  202. worksheet.Column(22).Width = 12;
  203. worksheet.Column(23).Width = 12;
  204. worksheet.Column(24).Width = 12;
  205. worksheet.Column(22).Width = 12;
  206. worksheet.Column(23).Width = 12;
  207. worksheet.Column(25).Width = 2;
  208. worksheet.Column(26).Width = 5;
  209. worksheet.Column(27).Width = 12;
  210. worksheet.Column(28).Width = 12;
  211. worksheet.Column(29).Width = 12;
  212. worksheet.Column(30).Width = 12;
  213. worksheet.Column(31).Width = 12;
  214. worksheet.Column(32).Width = 12;
  215. worksheet.Column(33).Width = 12;
  216. worksheet.Column(34).Width = 12;
  217. worksheet.Column(35).Width = 12;
  218. #endregion
  219. for (int i = 0; i < machineOEE.Count; i++)
  220. {
  221. string dateStr = machineOEE[i].Date + " " + machineOEE[i].Shift;
  222. worksheet.Cells[i + 2, 1].Value = i + 1;
  223. worksheet.Cells[i + 2, 2].Value = dateStr;
  224. worksheet.Cells[i + 2, 3].Value = (int)machineOEE[i].RunTimeRate;
  225. worksheet.Cells[i + 2, 4].Value = (int)machineOEE[i].IdelTimeRate;
  226. worksheet.Cells[i + 2, 5].Value = (int)machineOEE[i].DownTimeRate;
  227. worksheet.Cells[i + 2, 6].Value = (int)machineOEE[i].LoadMATTimeRate;
  228. worksheet.Cells[i + 2, 7].Value = machineOEE[i].Outputs;
  229. worksheet.Cells[i + 2, 8].Value = machineOEE[i].RunTime / 60;
  230. worksheet.Cells[i + 2, 9].Value = machineOEE[i].IdelTime / 60;
  231. worksheet.Cells[i + 2, 10].Value = machineOEE[i].DownTime / 60;
  232. worksheet.Cells[i + 2, 11].Value = machineOEE[i].LoadMATTime / 60;
  233. worksheet.Cells[i + 2, 12].Value
  234. = (machineOEE[i].RunTime + machineOEE[i].IdelTime + machineOEE[i].DownTime + machineOEE[i].LoadMATTime) / 60;
  235. if (OEE_Temp1.ContainsKey(dateStr))
  236. {
  237. OEE_Temp1[dateStr][0] += machineOEE[i].Outputs;
  238. OEE_Temp1[dateStr][1] += machineOEE[i].RunTime;
  239. OEE_Temp1[dateStr][2] += machineOEE[i].IdelTime;
  240. OEE_Temp1[dateStr][3] += machineOEE[i].DownTime;
  241. OEE_Temp1[dateStr][4] += machineOEE[i].LoadMATTime;
  242. OEE_Temp1[dateStr][5]
  243. += machineOEE[i].RunTime + machineOEE[i].IdelTime + machineOEE[i].DownTime + machineOEE[i].LoadMATTime;
  244. }
  245. else
  246. {
  247. List<int> list_temp = new List<int>();
  248. list_temp.Add(machineOEE[i].Outputs);
  249. list_temp.Add(machineOEE[i].RunTime);
  250. list_temp.Add(machineOEE[i].IdelTime);
  251. list_temp.Add(machineOEE[i].DownTime);
  252. list_temp.Add(machineOEE[i].LoadMATTime);
  253. list_temp.Add(machineOEE[i].RunTime + machineOEE[i].IdelTime + machineOEE[i].DownTime + machineOEE[i].LoadMATTime);
  254. OEE_Temp1.Add(dateStr, list_temp);
  255. }
  256. }
  257. if (machineHourDatas != null)
  258. {
  259. if (machineHourDatas.Count == 24)
  260. {
  261. List<string> temp = new List<string>();
  262. {
  263. int autoSum = 0;
  264. int alarmTimeSum = 0;
  265. int idelSum = 0;
  266. int outputSum = 0;
  267. double ttSum = 0;
  268. int alarmSum = 0;
  269. int loadTimeSum = 0;
  270. for (int i = 0; i < 12; i++)
  271. {
  272. int auto = (int)Math.Round(machineHourDatas[i].AutoRunTime.Value / 60.0);
  273. autoSum += auto;
  274. int alarmTime = (int)Math.Round(machineHourDatas[i].AlarmTime.Value / 60.0);
  275. alarmTimeSum += alarmTime;
  276. int idel = (int)Math.Round(machineHourDatas[i].IdleTime.Value / 60.0);
  277. idelSum += idel;
  278. int output = machineHourDatas[i].OutPut.Value;
  279. outputSum += output;
  280. double tt = machineHourDatas[i].TT;
  281. //ttSum += tt;
  282. int alarm = machineHourDatas[i].AlarmSum.Value;
  283. alarmSum += alarm;
  284. int loadTime = (int)Math.Round(machineHourDatas[i].LoadMATTime.Value / 60.0);
  285. loadTimeSum += loadTime;
  286. string periodStr = machineHourDatas[i].Period;
  287. string moduleTypeStr = machineHourDatas[i].ModuleType;
  288. if (ModuleType[i] == null || ModuleType[i].Count == 0 || !ModuleType[i].Contains(moduleTypeStr))
  289. {
  290. ModuleType[i].Add(moduleTypeStr);
  291. }
  292. worksheet.Cells[i + 3, 15].Value = i + 1;
  293. worksheet.Cells[i + 3, 16].Value = periodStr;
  294. worksheet.Cells[i + 3, 17].Value = machineHourDatas[i].ModuleType;
  295. worksheet.Cells[i + 3, 18].Value = auto;
  296. worksheet.Cells[i + 3, 19].Value = alarmTime;
  297. worksheet.Cells[i + 3, 20].Value = idel;
  298. worksheet.Cells[i + 3, 21].Value = loadTime;
  299. worksheet.Cells[i + 3, 22].Value = output;
  300. worksheet.Cells[i + 3, 23].Value = Math.Round(tt, 2);
  301. worksheet.Cells[i + 3, 24].Value = alarm;
  302. if (OEE_Temp2.ContainsKey(periodStr))
  303. {
  304. OEE_Temp2[periodStr][0] += auto;
  305. OEE_Temp2[periodStr][1] += alarmTime;
  306. OEE_Temp2[periodStr][2] += idel;
  307. OEE_Temp2[periodStr][3] += loadTime;
  308. OEE_Temp2[periodStr][4] += output;
  309. OEE_Temp2[periodStr][5] += alarm;
  310. }
  311. else
  312. {
  313. List<int> list_temp = new List<int>();
  314. list_temp.Add(auto);
  315. list_temp.Add(alarmTime);
  316. list_temp.Add(idel);
  317. list_temp.Add(loadTime);
  318. list_temp.Add(output);
  319. list_temp.Add(alarm);
  320. OEE_Temp2.Add(periodStr, list_temp);
  321. }
  322. }
  323. worksheet.Cells[15, 15].Value = "合计";
  324. worksheet.Cells[15, 15, 15, 17].Merge = true;
  325. worksheet.Cells[15, 18].Value = autoSum;
  326. worksheet.Cells[15, 19].Value = alarmTimeSum;
  327. worksheet.Cells[15, 20].Value = idelSum;
  328. worksheet.Cells[15, 21].Value = loadTimeSum;
  329. worksheet.Cells[15, 22].Value = outputSum;
  330. if (outputSum != 0)
  331. ttSum = autoSum * 60.0 / outputSum;
  332. else
  333. ttSum = 0;
  334. worksheet.Cells[15, 23].Value = Math.Round(ttSum, 2);
  335. worksheet.Cells[15, 24].Value = alarmSum;
  336. var faults = _faultService.GetFaultFrequencyTop10(machineId, startTime, startTime.AddHours(12), "安全门,门禁,提示上料,提示卸料,门锁,门打开,请选择LOT生产");
  337. for (int i = 0; i < faults.Count; i++)
  338. {
  339. worksheet.Cells[17 + i, 15].Value = i + 1;
  340. worksheet.Cells[17 + i, 16].Value = faults[i].FaultCode;
  341. worksheet.Cells[17 + i, 17].Value = faults[i].FaultInfo;
  342. worksheet.Cells[17 + i, 24].Value = faults[i].Count;
  343. }
  344. }
  345. {
  346. int autoSum = 0;
  347. int alarmTimeSum = 0;
  348. int idelSum = 0;
  349. int outputSum = 0;
  350. double ttSum = 0;
  351. int alarmSum = 0;
  352. int loadTimeSum = 0;
  353. for (int i = 12; i < 24; i++)
  354. {
  355. int auto = (int)Math.Round(machineHourDatas[i].AutoRunTime.Value / 60.0);
  356. autoSum += auto;
  357. int alarmTime = (int)Math.Round(machineHourDatas[i].AlarmTime.Value / 60.0);
  358. alarmTimeSum += alarmTime;
  359. int idel = (int)Math.Round(machineHourDatas[i].IdleTime.Value / 60.0);
  360. idelSum += idel;
  361. int output = machineHourDatas[i].OutPut.Value;
  362. outputSum += output;
  363. double tt = machineHourDatas[i].TT;
  364. //ttSum += tt;
  365. int alarm = machineHourDatas[i].AlarmSum.Value;
  366. alarmSum += alarm;
  367. int loadTime = (int)Math.Round(machineHourDatas[i].LoadMATTime.Value / 60.0);
  368. loadTimeSum += loadTime;
  369. string periodStr = machineHourDatas[i].Period;
  370. string moduleTypeStr = machineHourDatas[i].ModuleType;
  371. if (ModuleType[i] == null || ModuleType[i].Count == 0 || !ModuleType[i].Contains(moduleTypeStr))
  372. {
  373. ModuleType[i].Add(moduleTypeStr);
  374. }
  375. worksheet.Cells[i - 12 + 3, 26].Value = i + 1;
  376. worksheet.Cells[i - 12 + 3, 27].Value = machineHourDatas[i].Period;
  377. worksheet.Cells[i - 12 + 3, 28].Value = machineHourDatas[i].ModuleType;
  378. worksheet.Cells[i - 12 + 3, 29].Value = auto;
  379. worksheet.Cells[i - 12 + 3, 30].Value = alarmTime;
  380. worksheet.Cells[i - 12 + 3, 31].Value = idel;
  381. worksheet.Cells[i - 12 + 3, 32].Value = loadTime;
  382. worksheet.Cells[i - 12 + 3, 33].Value = output;
  383. worksheet.Cells[i - 12 + 3, 34].Value = Math.Round(tt, 2);
  384. worksheet.Cells[i - 12 + 3, 35].Value = alarm;
  385. if (OEE_Temp2.ContainsKey(periodStr))
  386. {
  387. OEE_Temp2[periodStr][0] += auto;
  388. OEE_Temp2[periodStr][1] += alarmTime;
  389. OEE_Temp2[periodStr][2] += idel;
  390. OEE_Temp2[periodStr][3] += loadTime;
  391. OEE_Temp2[periodStr][4] += output;
  392. OEE_Temp2[periodStr][5] += alarm;
  393. }
  394. else
  395. {
  396. List<int> list_temp = new List<int>();
  397. list_temp.Add(auto);
  398. list_temp.Add(alarmTime);
  399. list_temp.Add(idel);
  400. list_temp.Add(loadTime);
  401. list_temp.Add(output);
  402. list_temp.Add(alarm);
  403. OEE_Temp2.Add(periodStr, list_temp);
  404. }
  405. }
  406. worksheet.Cells[15, 26].Value = "合计";
  407. worksheet.Cells[15, 26, 15, 28].Merge = true;
  408. worksheet.Cells[15, 29].Value = autoSum;
  409. worksheet.Cells[15, 30].Value = alarmTimeSum;
  410. worksheet.Cells[15, 31].Value = idelSum;
  411. worksheet.Cells[15, 32].Value = loadTimeSum;
  412. worksheet.Cells[15, 33].Value = outputSum;
  413. if (outputSum != 0)
  414. ttSum = autoSum * 60.0 / outputSum;
  415. else
  416. ttSum = 0;
  417. worksheet.Cells[15, 34].Value = Math.Round(ttSum, 2);
  418. worksheet.Cells[15, 35].Value = alarmSum;
  419. var faults = _faultService.GetFaultFrequencyTop10(machineId, startTime.AddHours(12), startTime.AddHours(24), "安全门,门禁,提示上料,提示卸料,门锁,门打开,请选择LOT生产");
  420. for (int i = 0; i < faults.Count; i++)
  421. {
  422. worksheet.Cells[17 + i, 26].Value = i + 1;
  423. worksheet.Cells[17 + i, 27].Value = faults[i].FaultCode;
  424. worksheet.Cells[17 + i, 28].Value = faults[i].FaultInfo;
  425. worksheet.Cells[17 + i, 35].Value = faults[i].Count;
  426. }
  427. }
  428. }
  429. }
  430. ExcelChartSerie chartSerie;
  431. ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnStacked);
  432. chart.YAxis.MinValue = 0;
  433. chart.YAxis.MaxValue = 100;
  434. chart.YAxis.Format = "0 \"%\"";
  435. chart.Legend.Position = eLegendPosition.Bottom;
  436. chart.Legend.Add();
  437. chart.Title.Text = "设备稼动曲线"; //设置图表的名称
  438. chart.SetPosition(540, 20); //设置图表位置
  439. chart.SetSize(1000, 400); //设置图表大小
  440. chart.ShowHiddenData = true;
  441. chartSerie = chart.Series.Add(worksheet.Cells[2, 3, machineOEE.Count + 1, 3], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
  442. chartSerie.HeaderAddress = worksheet.Cells[1, 3];
  443. chartSerie = chart.Series.Add(worksheet.Cells[2, 4, machineOEE.Count + 1, 4], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
  444. chartSerie.HeaderAddress = worksheet.Cells[1, 4];
  445. chartSerie = chart.Series.Add(worksheet.Cells[2, 5, machineOEE.Count + 1, 5], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
  446. chartSerie.HeaderAddress = worksheet.Cells[1, 5];
  447. chartSerie = chart.Series.Add(worksheet.Cells[2, 6, machineOEE.Count + 1, 6], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
  448. chartSerie.HeaderAddress = worksheet.Cells[1, 6];
  449. //var chartType2 = chart.PlotArea.ChartTypes.Add(eChartType.Line);
  450. //chartType2.UseSecondaryAxis = true;
  451. //chartType2.YAxis.Format = "0 \"pcs\"";
  452. //chartSerie = chartType2.Series.Add(worksheet.Cells[2, 6, machineOEE.Count + 1, 6], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
  453. //chartSerie.HeaderAddress = worksheet.Cells[1, 6];
  454. foreach (var item in chart.Series)
  455. {
  456. var pieSerie = (ExcelBarChartSerie)item;
  457. pieSerie.DataLabel.ShowValue = true;
  458. }
  459. if (HideEquipmentProduction == null || HideEquipmentProduction.Count() == 0 || !HideEquipmentProduction.Contains(machineId))
  460. {
  461. ExcelChart chart1 = worksheet.Drawings.AddChart("chart1", eChartType.ColumnStacked);
  462. chart1.Legend.Position = eLegendPosition.Bottom;
  463. chart1.Legend.Add();
  464. chart1.Title.Text = "设备产能曲线"; //设置图表的名称
  465. chart1.SetPosition(540, 1030); //设置图表位置
  466. chart1.SetSize(1000, 400); //设置图表大小
  467. chart1.ShowHiddenData = true;
  468. chart1.YAxis.Format = "0 \"pcs\"";
  469. ExcelChartSerie chartSerie1;
  470. chartSerie1 = chart1.Series.Add(worksheet.Cells[2, 7, machineOEE.Count + 1, 7], worksheet.Cells[2, 2, machineOEE.Count + 1, 2]);
  471. chartSerie1.HeaderAddress = worksheet.Cells[1, 7];
  472. foreach (var item in chart1.Series)
  473. {
  474. var pieSerie = (ExcelBarChartSerie)item;
  475. pieSerie.DataLabel.ShowValue = true;
  476. }
  477. }
  478. package.Save();
  479. }
  480. if ((bool)UtilizationRateEmail["DataSummary"]["IsAble"])
  481. {
  482. FileInfo file = new FileInfo(path);
  483. using ExcelPackage package = new ExcelPackage(file);
  484. ExcelWorksheet worksheet = package.Workbook.Worksheets.Add($"汇总");
  485. worksheet.View.ZoomScale = 80;
  486. worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
  487. #region tab header
  488. worksheet.Cells[1, 1].Value = "NO";
  489. worksheet.Cells[1, 2].Value = "时间";
  490. worksheet.Cells[1, 3].Value = "稼动率(%)";
  491. worksheet.Cells[1, 4].Value = "待机率(%)";
  492. worksheet.Cells[1, 5].Value = "报警率(%)";
  493. worksheet.Cells[1, 6].Value = "换料率(%)";
  494. worksheet.Cells[1, 7].Value = "产能(pcs)";
  495. worksheet.Cells[1, 8].Value = "稼动(min)";
  496. worksheet.Cells[1, 9].Value = "待机(min)";
  497. worksheet.Cells[1, 10].Value = "报警(min)";
  498. worksheet.Cells[1, 11].Value = "换料(min)";
  499. worksheet.Cells[1, 12].Value = "合计(min)";
  500. worksheet.Cells[1, 15].Value = $"{startTime:yyyy-MM-dd} 早班";
  501. worksheet.Cells[1, 15, 1, 24].Merge = true;
  502. worksheet.Cells[2, 15].Value = "NO";
  503. worksheet.Cells[2, 16].Value = "时段";
  504. worksheet.Cells[2, 17].Value = "机种";
  505. worksheet.Cells[2, 18].Value = "运行时间";
  506. worksheet.Cells[2, 19].Value = "报警时间";
  507. worksheet.Cells[2, 20].Value = "待料时间";
  508. worksheet.Cells[2, 21].Value = "换料时间";
  509. worksheet.Cells[2, 22].Value = "产能";
  510. worksheet.Cells[2, 23].Value = "TT";
  511. worksheet.Cells[2, 24].Value = "报警次数";
  512. worksheet.Cells[1, 26].Value = $"{startTime:yyyy-MM-dd} 夜班";
  513. worksheet.Cells[1, 26, 1, 35].Merge = true;
  514. worksheet.Cells[2, 26].Value = "NO";
  515. worksheet.Cells[2, 27].Value = "时段";
  516. worksheet.Cells[2, 28].Value = "机种";
  517. worksheet.Cells[2, 29].Value = "运行时间";
  518. worksheet.Cells[2, 30].Value = "报警时间";
  519. worksheet.Cells[2, 31].Value = "待料时间";
  520. worksheet.Cells[2, 32].Value = "换料时间";
  521. worksheet.Cells[2, 33].Value = "产能";
  522. worksheet.Cells[2, 34].Value = "TT";
  523. worksheet.Cells[2, 35].Value = "报警次数";
  524. worksheet.Column(1).Width = 5;
  525. worksheet.Column(2).Width = 12;
  526. worksheet.Column(3).Width = 12;
  527. worksheet.Column(4).Width = 12;
  528. worksheet.Column(5).Width = 12;
  529. worksheet.Column(6).Width = 12;
  530. worksheet.Column(7).Width = 12;
  531. worksheet.Column(8).Width = 12;
  532. worksheet.Column(9).Width = 12;
  533. worksheet.Column(10).Width = 12;
  534. worksheet.Column(11).Width = 12;
  535. worksheet.Column(12).Width = 12;
  536. worksheet.Column(13).Width = 2;
  537. worksheet.Column(14).Width = 2;
  538. worksheet.Column(15).Width = 5;
  539. worksheet.Column(16).Width = 12;
  540. worksheet.Column(17).Width = 12;
  541. worksheet.Column(18).Width = 12;
  542. worksheet.Column(19).Width = 12;
  543. worksheet.Column(20).Width = 12;
  544. worksheet.Column(21).Width = 12;
  545. worksheet.Column(22).Width = 12;
  546. worksheet.Column(23).Width = 12;
  547. worksheet.Column(24).Width = 12;
  548. worksheet.Column(22).Width = 12;
  549. worksheet.Column(23).Width = 12;
  550. worksheet.Column(25).Width = 2;
  551. worksheet.Column(26).Width = 5;
  552. worksheet.Column(27).Width = 12;
  553. worksheet.Column(28).Width = 12;
  554. worksheet.Column(29).Width = 12;
  555. worksheet.Column(30).Width = 12;
  556. worksheet.Column(31).Width = 12;
  557. worksheet.Column(32).Width = 12;
  558. worksheet.Column(33).Width = 12;
  559. worksheet.Column(34).Width = 12;
  560. worksheet.Column(35).Width = 12;
  561. #endregion
  562. List<string> OEE1Keys = new List<string>(OEE_Temp1.Keys);
  563. for (int i = 0; i < OEE1Keys.Count(); i++)
  564. {
  565. int total = OEE_Temp1[OEE1Keys[i]][5];
  566. worksheet.Cells[i + 2, 1].Value = i + 1;
  567. worksheet.Cells[i + 2, 2].Value = OEE1Keys[i];
  568. worksheet.Cells[i + 2, 3].Value = Math.Round(OEE_Temp1[OEE1Keys[i]][1] * 100.0 / total);
  569. worksheet.Cells[i + 2, 4].Value = Math.Round(OEE_Temp1[OEE1Keys[i]][2] * 100.0 / total);
  570. worksheet.Cells[i + 2, 5].Value = Math.Round(OEE_Temp1[OEE1Keys[i]][3] * 100.0 / total);
  571. worksheet.Cells[i + 2, 6].Value = Math.Round(OEE_Temp1[OEE1Keys[i]][4] * 100.0 / total);
  572. worksheet.Cells[i + 2, 7].Value = OEE_Temp1[OEE1Keys[i]][0];
  573. worksheet.Cells[i + 2, 8].Value = OEE_Temp1[OEE1Keys[i]][1] / 60;
  574. worksheet.Cells[i + 2, 9].Value = OEE_Temp1[OEE1Keys[i]][2] / 60;
  575. worksheet.Cells[i + 2, 10].Value = OEE_Temp1[OEE1Keys[i]][3] / 60;
  576. worksheet.Cells[i + 2, 11].Value = OEE_Temp1[OEE1Keys[i]][4] / 60;
  577. worksheet.Cells[i + 2, 12].Value = total / 60;
  578. }
  579. if (OEE_Temp2 != null && OEE_Temp2.Count == 24)
  580. {
  581. List<string> OEE2Keys = new List<string>(OEE_Temp2.Keys);
  582. {
  583. int autoTimeSum = 0;
  584. int alarmTimeSum = 0;
  585. int idelTimeSum = 0;
  586. int loadTimeSum = 0;
  587. int outputSum = 0;
  588. int alarmSum = 0;
  589. double ttSum;
  590. for (int i = 0; i < 12; i++)
  591. {
  592. int autoTime = OEE_Temp2[OEE2Keys[i]][0];
  593. autoTimeSum += autoTime;
  594. int alarmTime = OEE_Temp2[OEE2Keys[i]][1];
  595. alarmTimeSum += alarmTime;
  596. int idelTime = OEE_Temp2[OEE2Keys[i]][2];
  597. idelTimeSum += idelTime;
  598. int loadTime = OEE_Temp2[OEE2Keys[i]][3];
  599. loadTimeSum += loadTime;
  600. int output = OEE_Temp2[OEE2Keys[i]][4];
  601. outputSum += output;
  602. int alarm = OEE_Temp2[OEE2Keys[i]][5];
  603. alarmSum += alarm;
  604. worksheet.Cells[i + 3, 15].Value = i + 1;
  605. worksheet.Cells[i + 3, 16].Value = OEE2Keys[i];
  606. worksheet.Cells[i + 3, 17].Value = String.Join(",", ModuleType[i]);
  607. worksheet.Cells[i + 3, 18].Value = autoTime;
  608. worksheet.Cells[i + 3, 19].Value = alarmTime;
  609. worksheet.Cells[i + 3, 20].Value = idelTime;
  610. worksheet.Cells[i + 3, 21].Value = loadTimeSum;
  611. worksheet.Cells[i + 3, 22].Value = output;
  612. worksheet.Cells[i + 3, 23].Value = output == 0 ? 0 : Math.Round(OEE_Temp2[OEE2Keys[i]][0] * 60.0 / OEE_Temp2[OEE2Keys[i]][4], 2);
  613. worksheet.Cells[i + 3, 24].Value = alarm;
  614. }
  615. worksheet.Cells[15, 15].Value = "合计";
  616. worksheet.Cells[15, 15, 15, 17].Merge = true;
  617. worksheet.Cells[15, 18].Value = autoTimeSum;
  618. worksheet.Cells[15, 19].Value = alarmTimeSum;
  619. worksheet.Cells[15, 20].Value = idelTimeSum;
  620. worksheet.Cells[15, 21].Value = loadTimeSum;
  621. worksheet.Cells[15, 22].Value = outputSum;
  622. if (outputSum != 0)
  623. ttSum = autoTimeSum * 60.0 / outputSum;
  624. else
  625. ttSum = 0;
  626. worksheet.Cells[15, 23].Value = Math.Round(ttSum, 2);
  627. worksheet.Cells[15, 24].Value = alarmSum;
  628. }
  629. {
  630. int autoTimeSum = 0;
  631. int alarmTimeSum = 0;
  632. int idelTimeSum = 0;
  633. int loadTimeSum = 0;
  634. int outputSum = 0;
  635. int alarmSum = 0;
  636. double ttSum;
  637. for (int i = 12; i < 24; i++)
  638. {
  639. int autoTime = OEE_Temp2[OEE2Keys[i]][0]; ;
  640. autoTimeSum += autoTime;
  641. int alarmTime = OEE_Temp2[OEE2Keys[i]][1];
  642. alarmTimeSum += alarmTime;
  643. int idelTime = OEE_Temp2[OEE2Keys[i]][2];
  644. idelTimeSum += idelTime;
  645. int loadTime = OEE_Temp2[OEE2Keys[i]][3];
  646. loadTimeSum += loadTime;
  647. int output = OEE_Temp2[OEE2Keys[i]][4];
  648. outputSum += output;
  649. int alarm = OEE_Temp2[OEE2Keys[i]][5];
  650. alarmSum += alarm;
  651. worksheet.Cells[i - 12 + 3, 26].Value = i + 1;
  652. worksheet.Cells[i - 12 + 3, 27].Value = OEE2Keys[i];
  653. worksheet.Cells[i - 12 + 3, 28].Value = String.Join(",", ModuleType[i]);
  654. worksheet.Cells[i - 12 + 3, 29].Value = autoTime;
  655. worksheet.Cells[i - 12 + 3, 30].Value = alarmTime;
  656. worksheet.Cells[i - 12 + 3, 31].Value = idelTime;
  657. worksheet.Cells[i - 12 + 3, 32].Value = loadTimeSum;
  658. worksheet.Cells[i - 12 + 3, 33].Value = output;
  659. 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);
  660. worksheet.Cells[i - 12 + 3, 35].Value = alarm;
  661. }
  662. worksheet.Cells[15, 26].Value = "合计";
  663. worksheet.Cells[15, 26, 15, 28].Merge = true;
  664. worksheet.Cells[15, 29].Value = autoTimeSum;
  665. worksheet.Cells[15, 30].Value = alarmTimeSum;
  666. worksheet.Cells[15, 31].Value = idelTimeSum;
  667. worksheet.Cells[15, 32].Value = loadTimeSum;
  668. worksheet.Cells[15, 33].Value = outputSum;
  669. if (outputSum != 0)
  670. ttSum = autoTimeSum * 60.0 / outputSum;
  671. else
  672. ttSum = 0;
  673. worksheet.Cells[15, 34].Value = Math.Round(ttSum, 2);
  674. worksheet.Cells[15, 35].Value = alarmSum;
  675. }
  676. }
  677. ExcelChartSerie chartSerie;
  678. ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnStacked);
  679. chart.YAxis.MinValue = 0;
  680. chart.YAxis.MaxValue = 100;
  681. chart.YAxis.Format = "0 \"%\"";
  682. chart.Legend.Position = eLegendPosition.Bottom;
  683. chart.Legend.Add();
  684. chart.Title.Text = "设备稼动曲线"; //设置图表的名称
  685. chart.SetPosition(540, 20); //设置图表位置
  686. chart.SetSize(1000, 400); //设置图表大小
  687. chart.ShowHiddenData = true;
  688. chartSerie = chart.Series.Add(worksheet.Cells[2, 3, OEE_Temp1.Count() + 1, 3], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
  689. chartSerie.HeaderAddress = worksheet.Cells[1, 3];
  690. chartSerie = chart.Series.Add(worksheet.Cells[2, 4, OEE_Temp1.Count() + 1, 4], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
  691. chartSerie.HeaderAddress = worksheet.Cells[1, 4];
  692. chartSerie = chart.Series.Add(worksheet.Cells[2, 5, OEE_Temp1.Count() + 1, 5], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
  693. chartSerie.HeaderAddress = worksheet.Cells[1, 5];
  694. chartSerie = chart.Series.Add(worksheet.Cells[2, 6, OEE_Temp1.Count() + 1, 6], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
  695. chartSerie.HeaderAddress = worksheet.Cells[1, 6];
  696. foreach (var item in chart.Series)
  697. {
  698. var pieSerie = (ExcelBarChartSerie)item;
  699. pieSerie.DataLabel.ShowValue = true;
  700. }
  701. if (!(bool)UtilizationRateEmail["DataSummary"]["HideProductionUtilizationChart"])
  702. {
  703. ExcelChart chart1 = worksheet.Drawings.AddChart("chart1", eChartType.ColumnStacked);
  704. chart1.Legend.Position = eLegendPosition.Bottom;
  705. chart1.Legend.Add();
  706. chart1.Title.Text = "设备产能曲线"; //设置图表的名称
  707. chart1.SetPosition(540, 1030); //设置图表位置
  708. chart1.SetSize(1000, 400); //设置图表大小
  709. chart1.ShowHiddenData = true;
  710. chart1.YAxis.Format = "0 \"pcs\"";
  711. ExcelChartSerie chartSerie1;
  712. chartSerie1 = chart1.Series.Add(worksheet.Cells[2, 7, OEE_Temp1.Count() + 1, 7], worksheet.Cells[2, 2, OEE_Temp1.Count() + 1, 2]);
  713. chartSerie1.HeaderAddress = worksheet.Cells[1, 7];
  714. foreach (var item in chart1.Series)
  715. {
  716. var pieSerie = (ExcelBarChartSerie)item;
  717. pieSerie.DataLabel.ShowValue = true;
  718. }
  719. }
  720. package.Workbook.Worksheets.MoveToStart("汇总");
  721. package.Save();
  722. }
  723. string ReceiverAppSettingName = UtilizationRateEmail["ReceiverAppSettingName"].ToString();
  724. if (ReceiverAppSettingName == null || String.IsNullOrEmpty(ReceiverAppSettingName))
  725. {
  726. LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} ReceiverAppSettingName is null");
  727. continue;
  728. }
  729. SendMail(
  730. MailType.Message,
  731. ReceiverAppSettingName,
  732. "",
  733. "",
  734. "EQP.EAPAUTO01@eink.com",
  735. "",
  736. $"稼动数据 -- {dateNowStr}",
  737. "",
  738. new string[] { path },
  739. new string[] { dateNowStr },
  740. Encoding.UTF8);
  741. LogerHelper.RecordLogTxt($"EquipmentOperationReport Group-{count} Send over, ReceiverAppSettingName:【{ReceiverAppSettingName}】, file:【{path}】, EmailFileName:【{dateNowStr}】");
  742. }
  743. return true;
  744. }
  745. catch (Exception ex)
  746. {
  747. LogerHelper.RecordLogTxt($"EquipmentOperationReport Exception, Message:{ex.Message}, StackTrace:{ex.StackTrace}");
  748. return false;
  749. }
  750. }
  751. }
  752. }