448 lines
22 KiB
C#
448 lines
22 KiB
C#
using ClosedXML.Excel;
|
|
using Microsoft.Extensions.Configuration;
|
|
using Microsoft.Extensions.Logging;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Traffic.Data.ViewModels;
|
|
using Traffic.Service.Interfaces;
|
|
|
|
namespace Traffic.Service.Implements
|
|
{
|
|
public class FileService : IFileService
|
|
{
|
|
private readonly IConfiguration _configuration;
|
|
private readonly ILogger _logger;
|
|
public FileService(IConfiguration configuration, ILogger<FileService> logger)
|
|
{
|
|
_configuration = configuration;
|
|
_logger = logger;
|
|
}
|
|
|
|
public string ExportGetTycgLog(string tableName, IEnumerable<TycgViewModel> data)
|
|
{
|
|
try
|
|
{
|
|
string fileName = $"{tableName}.xlsx";
|
|
using (var workbook = new XLWorkbook())
|
|
{
|
|
IXLWorksheet worksheet =
|
|
workbook.Worksheets.Add(tableName);
|
|
worksheet.Cell(1, 1).Value = "Id";
|
|
worksheet.Cell(1, 2).Value = "Time";
|
|
worksheet.Cell(1, 3).Value = "ip";
|
|
worksheet.Cell(1, 4).Value = "api";
|
|
var arryList = data.ToArray();
|
|
for (int index = 1; index <= data.Count(); index++)
|
|
{
|
|
worksheet.Cell(index + 1, 1).Value = arryList[index - 1].Id;
|
|
worksheet.Cell(index + 1, 2).Value = arryList[index - 1].Time;
|
|
worksheet.Cell(index + 1, 3).Value = arryList[index - 1].ip;
|
|
worksheet.Cell(index + 1, 4).Value = arryList[index - 1].api;
|
|
}
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
|
|
using (FileStream fs = File.Create(_configuration["EventStatus:ExportExcel"] + fileName))
|
|
{
|
|
fs.Write(content, 0, content.Length);
|
|
return _configuration["EventStatus:ExportExcelUrl"] + fileName;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger.LogError(ex.Message);
|
|
return "匯出失敗";
|
|
}
|
|
}
|
|
|
|
public string ExportEvent(string startTime, string endTime, string eventName, IEnumerable<JoinEventSiteAndUserViewModel> events)
|
|
{
|
|
try
|
|
{
|
|
|
|
string fileName = $"違規事件-{startTime}到{endTime}的{eventName}.xlsx";
|
|
using (var workbook = new XLWorkbook())
|
|
{
|
|
IXLWorksheet worksheet =
|
|
workbook.Worksheets.Add("events");
|
|
worksheet.Cell(1, 1).Value = "County";
|
|
worksheet.Cell(1, 2).Value = "Area";
|
|
worksheet.Cell(1, 3).Value = "SiteId";
|
|
worksheet.Cell(1, 4).Value = "SiteName";
|
|
worksheet.Cell(1, 5).Value = "EventName";
|
|
worksheet.Cell(1, 6).Value = "VehicleName";
|
|
worksheet.Cell(1, 7).Value = "Laws";
|
|
worksheet.Cell(1, 8).Value = "EventID";
|
|
worksheet.Cell(1, 9).Value = "Channel";
|
|
worksheet.Cell(1, 10).Value = "Slot";
|
|
worksheet.Cell(1, 11).Value = "Number";
|
|
//worksheet.Cell(1, 12).Value = "PlateNumber";
|
|
worksheet.Cell(1, 12).Value = "EventType";
|
|
worksheet.Cell(1, 13).Value = "VehicleType";
|
|
worksheet.Cell(1, 14).Value = "Distance";
|
|
worksheet.Cell(1, 15).Value = "Speed";
|
|
worksheet.Cell(1, 16).Value = "TotalPassTime";
|
|
worksheet.Cell(1, 17).Value = "LimitTon";
|
|
worksheet.Cell(1, 18).Value = "Ton";
|
|
worksheet.Cell(1, 19).Value = "InTime";
|
|
worksheet.Cell(1, 20).Value = "AlarmTime";
|
|
worksheet.Cell(1, 21).Value = "OutTime";
|
|
worksheet.Cell(1, 22).Value = "LPRTime";
|
|
worksheet.Cell(1, 23).Value = "IMGPath1";
|
|
worksheet.Cell(1, 24).Value = "VideoPath1";
|
|
worksheet.Cell(1, 25).Value = "IsStay";
|
|
worksheet.Cell(1, 26).Value = "Unsure";
|
|
worksheet.Cell(1, 27).Value = "CheckPunish";
|
|
worksheet.Cell(1, 28).Value = "Punished";
|
|
worksheet.Cell(1, 29).Value = "CompanyCode";
|
|
worksheet.Cell(1, 30).Value = "Rejected";
|
|
worksheet.Cell(1, 31).Value = "Status";
|
|
worksheet.Cell(1, 32).Value = "Ps";
|
|
worksheet.Cell(1, 33).Value = "HandlingTime";
|
|
worksheet.Cell(1, 34).Value = "UploadTime";
|
|
worksheet.Cell(1, 35).Value = "Account";
|
|
var arryList = events.ToArray();
|
|
for (int index = 1; index <= events.Count(); index++)
|
|
{
|
|
worksheet.Cell(index + 1, 1).Value = arryList[index - 1].County;
|
|
worksheet.Cell(index + 1, 2).Value = arryList[index - 1].Area;
|
|
worksheet.Cell(index + 1, 3).Value = arryList[index - 1].SiteId;
|
|
worksheet.Cell(index + 1, 4).Value = arryList[index - 1].SiteName;
|
|
worksheet.Cell(index + 1, 5).Value = arryList[index - 1].EventName;
|
|
worksheet.Cell(index + 1, 6).Value = arryList[index - 1].VehicleName;
|
|
worksheet.Cell(index + 1, 7).Value = arryList[index - 1].Laws;
|
|
worksheet.Cell(index + 1, 8).Value = arryList[index - 1].EventID;
|
|
worksheet.Cell(index + 1, 9).Value = arryList[index - 1].Channel;
|
|
worksheet.Cell(index + 1, 10).Value = arryList[index - 1].Slot;
|
|
worksheet.Cell(index + 1, 11).Value = arryList[index - 1].Number;
|
|
//worksheet.Cell(index + 1, 12).Value = arryList[index - 1].PlateNumber;
|
|
worksheet.Cell(index + 1, 12).Value = arryList[index - 1].EventType;
|
|
worksheet.Cell(index + 1, 13).Value = arryList[index - 1].VehicleType;
|
|
worksheet.Cell(index + 1, 14).Value = arryList[index - 1].Distance;
|
|
worksheet.Cell(index + 1, 15).Value = arryList[index - 1].Speed;
|
|
worksheet.Cell(index + 1, 16).Value = arryList[index - 1].TotalPassTime;
|
|
worksheet.Cell(index + 1, 17).Value = arryList[index - 1].LimitTon;
|
|
worksheet.Cell(index + 1, 18).Value = arryList[index - 1].Ton;
|
|
worksheet.Cell(index + 1, 19).Value = arryList[index - 1].InTime;
|
|
worksheet.Cell(index + 1, 20).Value = arryList[index - 1].AlarmTime;
|
|
worksheet.Cell(index + 1, 21).Value = arryList[index - 1].OutTime;
|
|
worksheet.Cell(index + 1, 22).Value = arryList[index - 1].LPRTime;
|
|
worksheet.Cell(index + 1, 23).Value = arryList[index - 1].IMGPath1;
|
|
worksheet.Cell(index + 1, 24).Value = arryList[index - 1].VideoPath1;
|
|
worksheet.Cell(index + 1, 25).Value = arryList[index - 1].IsStay;
|
|
worksheet.Cell(index + 1, 26).Value = arryList[index - 1].Unsure;
|
|
worksheet.Cell(index + 1, 27).Value = arryList[index - 1].CheckPunish;
|
|
worksheet.Cell(index + 1, 28).Value = arryList[index - 1].Punished;
|
|
worksheet.Cell(index + 1, 29).Value = arryList[index - 1].CompanyCode;
|
|
worksheet.Cell(index + 1, 30).Value = arryList[index - 1].Rejected;
|
|
worksheet.Cell(index + 1, 31).Value = arryList[index - 1].Status;
|
|
worksheet.Cell(index + 1, 32).Value = arryList[index - 1].Ps;
|
|
worksheet.Cell(index + 1, 33).Value = arryList[index - 1].HandlingTime;
|
|
worksheet.Cell(index + 1, 34).Value = arryList[index - 1].UploadTime;
|
|
worksheet.Cell(index + 1, 35).Value = arryList[index - 1].Account;
|
|
}
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
|
|
using (FileStream fs = File.Create(_configuration["EventStatus:ExportExcel"] + fileName))
|
|
{
|
|
fs.Write(content, 0, content.Length);
|
|
return _configuration["EventStatus:ExportExcelUrl"] + fileName;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger.LogError(ex.Message);
|
|
return "匯出失敗";
|
|
}
|
|
}
|
|
|
|
public string ExportGetEventType1(string startTime, string endTime, List<ReportEventType1ViewModel> data)
|
|
{
|
|
try
|
|
{
|
|
string fileName = $"違規事件報表-違規停車-{startTime}到{endTime}.xlsx";
|
|
using (var workbook = new XLWorkbook())
|
|
{
|
|
IXLWorksheet worksheet =
|
|
workbook.Worksheets.Add("events");
|
|
worksheet.Cell(1, 1).Value = "DateTime";
|
|
worksheet.Cell(1, 2).Value = "BusCount";
|
|
worksheet.Cell(1, 3).Value = "CarCount";
|
|
worksheet.Cell(1, 4).Value = "MotorcycleCount";
|
|
worksheet.Cell(1, 5).Value = "TaxiCount";
|
|
worksheet.Cell(1, 6).Value = "HasNumber";
|
|
worksheet.Cell(1, 7).Value = "HasNoNumber";
|
|
var arryList = data.ToArray();
|
|
for (int index = 1; index <= data.Count; index++)
|
|
{
|
|
worksheet.Cell(index + 1, 1).Value = arryList[index - 1].DateTime;
|
|
worksheet.Cell(index + 1, 2).Value = arryList[index - 1].BusCount;
|
|
worksheet.Cell(index + 1, 3).Value = arryList[index - 1].CarCount;
|
|
worksheet.Cell(index + 1, 4).Value = arryList[index - 1].MotorcycleCount;
|
|
worksheet.Cell(index + 1, 5).Value = arryList[index - 1].TaxiCount;
|
|
worksheet.Cell(index + 1, 6).Value = arryList[index - 1].HasNumber;
|
|
worksheet.Cell(index + 1, 7).Value = arryList[index - 1].HasNoNumber;
|
|
}
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
|
|
using (FileStream fs = File.Create(_configuration["EventStatus:ExportExcel"] + fileName))
|
|
{
|
|
fs.Write(content, 0, content.Length);
|
|
return _configuration["EventStatus:ExportExcelUrl"] + fileName;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger.LogError(ex.Message);
|
|
return "匯出失敗";
|
|
}
|
|
}
|
|
|
|
public string ExportGetEventType2(string startTime, string endTime, List<ReportEventType2ViewModel> data)
|
|
{
|
|
try
|
|
{
|
|
string fileName = $"違規事件報表-區間測速-{startTime}到{endTime}.xlsx";
|
|
using (var workbook = new XLWorkbook())
|
|
{
|
|
IXLWorksheet worksheet =
|
|
workbook.Worksheets.Add("events");
|
|
worksheet.Cell(1, 1).Value = "Speed";
|
|
worksheet.Cell(1, 2).Value = "Count";
|
|
var arryList = data.ToArray();
|
|
for (int index = 1; index <= data.Count; index++)
|
|
{
|
|
worksheet.Cell(index + 1, 1).Value = arryList[index - 1].Speed;
|
|
worksheet.Cell(index + 1, 2).Value = arryList[index - 1].Count;
|
|
}
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
|
|
using (FileStream fs = File.Create(_configuration["EventStatus:ExportExcel"] + fileName))
|
|
{
|
|
fs.Write(content, 0, content.Length);
|
|
return _configuration["EventStatus:ExportExcelUrl"] + fileName;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger.LogError(ex.Message);
|
|
return "匯出失敗";
|
|
}
|
|
}
|
|
|
|
public string ExportGetEventType34(string startTime, string endTime, List<ReportEventType34ViewModel> data)
|
|
{
|
|
try
|
|
{
|
|
string fileName = $"違規事件報表-未依標線行駛_路口淨空_闖紅燈-{startTime}到{endTime}.xlsx";
|
|
using (var workbook = new XLWorkbook())
|
|
{
|
|
IXLWorksheet worksheet =
|
|
workbook.Worksheets.Add("events");
|
|
worksheet.Cell(1, 1).Value = "CarCount";
|
|
worksheet.Cell(1, 2).Value = "CoachCount";
|
|
worksheet.Cell(1, 3).Value = "TruckCount";
|
|
var arryList = data.ToArray();
|
|
for (int index = 1; index <= data.Count; index++)
|
|
{
|
|
worksheet.Cell(index + 1, 1).Value = arryList[index - 1].CarCount;
|
|
worksheet.Cell(index + 1, 2).Value = arryList[index - 1].CoachCount;
|
|
worksheet.Cell(index + 1, 3).Value = arryList[index - 1].TruckCount;
|
|
}
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
|
|
using (FileStream fs = File.Create(_configuration["EventStatus:ExportExcel"] + fileName))
|
|
{
|
|
fs.Write(content, 0, content.Length);
|
|
return _configuration["EventStatus:ExportExcelUrl"] + fileName;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger.LogError(ex.Message);
|
|
return "匯出失敗";
|
|
}
|
|
}
|
|
|
|
public string ExportGetEventType6(string startTime, string endTime, List<ReportEventType6ViewModel> data)
|
|
{
|
|
try
|
|
{
|
|
string fileName = $"違規事件報表-大貨車禁行-{startTime}到{endTime}.xlsx";
|
|
using (var workbook = new XLWorkbook())
|
|
{
|
|
IXLWorksheet worksheet =
|
|
workbook.Worksheets.Add("events");
|
|
worksheet.Cell(1, 1).Value = "Ton";
|
|
worksheet.Cell(1, 2).Value = "Count";
|
|
var arryList = data.ToArray();
|
|
for (int index = 1; index <= data.Count; index++)
|
|
{
|
|
worksheet.Cell(index + 1, 1).Value = arryList[index - 1].Ton;
|
|
worksheet.Cell(index + 1, 2).Value = arryList[index - 1].Count;
|
|
}
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
|
|
using (FileStream fs = File.Create(_configuration["EventStatus:ExportExcel"] + fileName))
|
|
{
|
|
fs.Write(content, 0, content.Length);
|
|
return _configuration["EventStatus:ExportExcelUrl"] + fileName;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger.LogError(ex.Message);
|
|
return "匯出失敗";
|
|
}
|
|
}
|
|
|
|
public string ExportGetMalfunction(string startTime, string endTime, List<ReportMalfunctionViewModel> data)
|
|
{
|
|
try
|
|
{
|
|
string fileName = $"設備異常-{startTime}到{endTime}.xlsx";
|
|
using (var workbook = new XLWorkbook())
|
|
{
|
|
IXLWorksheet worksheet =
|
|
workbook.Worksheets.Add("Malfunction");
|
|
worksheet.Cell(1, 1).Value = "時間";
|
|
worksheet.Cell(1, 2).Value = "場域";
|
|
worksheet.Cell(1, 3).Value = "公司";
|
|
worksheet.Cell(1, 4).Value = "設備";
|
|
worksheet.Cell(1, 5).Value = "設備編號";
|
|
worksheet.Cell(1, 6).Value = "訊息";
|
|
var arryList = data.ToArray();
|
|
for (int index = 1; index <= data.Count; index++)
|
|
{
|
|
worksheet.Cell(index + 1, 1).Value = arryList[index - 1].Time;
|
|
worksheet.Cell(index + 1, 2).Value = arryList[index - 1].SiteName;
|
|
worksheet.Cell(index + 1, 3).Value = arryList[index - 1].CompanyCode;
|
|
worksheet.Cell(index + 1, 4).Value = arryList[index - 1].DeviceType;
|
|
worksheet.Cell(index + 1, 5).Value = arryList[index - 1].DeviceID;
|
|
worksheet.Cell(index + 1, 6).Value = arryList[index - 1].Message;
|
|
}
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
|
|
using (FileStream fs = File.Create(_configuration["EventStatus:ExportExcel"] + fileName))
|
|
{
|
|
fs.Write(content, 0, content.Length);
|
|
return _configuration["EventStatus:ExportExcelUrl"] + fileName;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger.LogError(ex.Message);
|
|
return "匯出失敗";
|
|
}
|
|
}
|
|
|
|
public string ExportGetSiteEvent(string startTime, string endTime, ReportSiteEventTypesViewModel data)
|
|
{
|
|
try
|
|
{
|
|
string fileName = $"違規事件統計站店違規數統計表-{startTime}到{endTime}.xlsx";
|
|
using (var workbook = new XLWorkbook())
|
|
{
|
|
IXLWorksheet worksheet =
|
|
workbook.Worksheets.Add("違規事件統計");
|
|
worksheet.Cell(1, 1).Value = "違規項目";
|
|
worksheet.Cell(1, 2).Value = "違規總數";
|
|
worksheet.Cell(1, 3).Value = "未開單";
|
|
worksheet.Cell(1, 4).Value = "裁決中";
|
|
worksheet.Cell(1, 5).Value = "不開單";
|
|
worksheet.Cell(1, 6).Value = "已拒絕";
|
|
worksheet.Cell(1, 7).Value = "已開單";
|
|
var arryList = data.EachEventTypesSiteViewModel.ToArray();
|
|
for (int index = 1; index <= data.EachEventTypesSiteViewModel.Count; index++)
|
|
{
|
|
worksheet.Cell(index + 1, 1).Value = arryList[index - 1].EventName;
|
|
worksheet.Cell(index + 1, 2).Value = arryList[index - 1].EventTotalCount;
|
|
worksheet.Cell(index + 1, 3).Value = arryList[index - 1].NoStatusCount;
|
|
worksheet.Cell(index + 1, 4).Value = arryList[index - 1].CheckPunishCount;
|
|
worksheet.Cell(index + 1, 5).Value = arryList[index - 1].NoPunishCount;
|
|
worksheet.Cell(index + 1, 6).Value = arryList[index - 1].RejectedCount;
|
|
worksheet.Cell(index + 1, 7).Value = arryList[index - 1].PunishCount;
|
|
}
|
|
|
|
IXLWorksheet worksheet2 = workbook.Worksheets.Add("站點違規數統計");
|
|
worksheet2.Cell(1, 1).Value = "站點ID";
|
|
worksheet2.Cell(1, 2).Value = "站點名稱";
|
|
worksheet2.Cell(1, 3).Value = "事件總數";
|
|
worksheet2.Cell(1, 4).Value = "未開單";
|
|
worksheet2.Cell(1, 5).Value = "裁決中";
|
|
worksheet2.Cell(1, 6).Value = "不開單";
|
|
worksheet2.Cell(1, 7).Value = "已拒絕";
|
|
worksheet2.Cell(1, 8).Value = "已開單";
|
|
var arryList2 = data.EachSiteEventTypesViewModel.ToArray();
|
|
for (int index = 1; index <= data.EachSiteEventTypesViewModel.Count; index++)
|
|
{
|
|
worksheet2.Cell(index + 1, 1).Value = arryList2[index - 1].SiteId;
|
|
worksheet2.Cell(index + 1, 2).Value = arryList2[index - 1].SiteName;
|
|
worksheet2.Cell(index + 1, 3).Value = arryList2[index - 1].EventTotalCount;
|
|
worksheet2.Cell(index + 1, 4).Value = arryList2[index - 1].NoStatusCount;
|
|
worksheet2.Cell(index + 1, 5).Value = arryList2[index - 1].CheckPunishCount;
|
|
worksheet2.Cell(index + 1, 6).Value = arryList2[index - 1].NoPunishCount;
|
|
worksheet2.Cell(index + 1, 7).Value = arryList2[index - 1].RejectedCount;
|
|
worksheet2.Cell(index + 1, 8).Value = arryList2[index - 1].PunishCount;
|
|
}
|
|
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
|
|
using (FileStream fs = File.Create(_configuration["EventStatus:ExportExcel"] + fileName))
|
|
{
|
|
fs.Write(content, 0, content.Length);
|
|
return _configuration["EventStatus:ExportExcelUrl"] + fileName;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger.LogError(ex.Message);
|
|
return "匯出失敗";
|
|
}
|
|
}
|
|
}
|
|
}
|