using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Hosting.Internal;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.XSSF.UserModel;
using NPOI.XWPF.UserModel;
using Ropin.Inspection.Api.Common;
using Ropin.Inspection.Common.Helper;
using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Common;
using Ropin.Inspection.Model.SearchModel;
using Ropin.Inspection.Model.ViewModel;
using Ropin.Inspection.Service;
using Ropin.Inspection.Service.Interface;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Threading.Tasks;
namespace Ropin.Inspection.Api.Controllers
{
/// <summary>
/// 巡检记录
/// </summary>
public class TispRecordController : BaseController
{
public ILogger<TispRecordController> _logger { get; }
private readonly ITispRecordService _tispRecordService;
private readonly ITispRecordImageService _tispRecordImageService;
//private readonly IWebHostEnvironment _hostingEnvironment;
private readonly IWebHostEnvironment _hostingEnvironment;
private readonly IPushMsgService _pushMsgService;
private readonly ITispRecordItemService _tispRecordItemService;
private readonly ITpntStoreService _TpntStoreService;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="tispRecordService"></param>
/// <param name="tispRecordImageService"></param>
/// <param name="hostingEnvironment"></param>
/// <param name="logger"></param>
public TispRecordController(ITispRecordService tispRecordService, IPushMsgService pushMsgService, ITispRecordImageService tispRecordImageService, IWebHostEnvironment hostingEnvironment, ILogger<TispRecordController> logger, ITispRecordItemService tispRecordItemService,ITpntStoreService TpntStoreService)
{
_tispRecordService = tispRecordService;
_tispRecordImageService = tispRecordImageService;
_hostingEnvironment = hostingEnvironment;
_logger = logger;
_pushMsgService = pushMsgService;
_tispRecordItemService = tispRecordItemService;
_TpntStoreService = TpntStoreService;
}
/// <summary>
/// 通过ID获取巡检点信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpGet("GetRecordAsync/{id}")]
public async Task<ApiResult> GetRecordAsync(Guid id)
{
if (Guid.Empty == id)
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
TispRecordViewModel record = await _tispRecordService.GetByIdAsync(id);
return new ApiResult<TispRecordViewModel>(record);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
///// <summary>
///// 获取所有巡检
///// </summary>
///// <returns></returns>
//[HttpGet("GetRecordsAsync")]
//public async Task<ApiResult> GetRecordsAsync()
//{
// try
// {
// var recordList = await _tispRecordService.GetAllAsync();
// return new ApiResult<IEnumerable<TispRecordViewModel>>(recordList);
// }
// catch (Exception ex)
// {
// return new ApiResult(ReturnCode.GeneralError, ex.Message);
// }
//}
/// <summary>
/// 条件获取巡检记录
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("GetRecordsConditionAsync")]
public async Task<ApiResult> GetRecordsConditionAsync(TispRecordSearchModel searchModel)
{
if (searchModel == null || string.IsNullOrEmpty(searchModel.C_StoreCode))
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
var recordList = await _tispRecordService.GetRecordsConditionAsync(searchModel);
return new ApiResult<PagesModel<TispRecordDetailViewModel>>(new PagesModel<TispRecordDetailViewModel>(recordList?.ToList(), searchModel));
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 创建巡检记录
/// </summary>
/// <param name="record"></param>
/// <returns></returns>
[Route("CreateRecordAsync")]
[HttpPost]
public async Task<ApiResult> CreateRecordAsync(TispRecordCreateViewModel record)
{
if (record == null || record.TispRecordItemList == null || !record.TispRecordItemList.Any())
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
await _tispRecordService.CreateRecordAsync(record);
if (record.TispRecordItemList.Where(i => i.C_Status == "0").Any())
{
//没有设备ID不能发消息
//await _pushMsgService.PushAlarmMsgAsync(new TpushMsgModel
//{
// C_DevStoreCode = "",
// C_MsgTypeCode = "MSG_TYPE_002",
// Msg = record.TispRecordItemList.Where(i => i.C_Status == "0").Select(x => x.C_InspectionContent).First(),
// Subject = "点检异常",
// DevNumber = record.C_SpotCode.ToString(),
// DevName = "未知",
// GenerationType = 2,
// msgStatus = 0,
//}, "点检异常");
//await _pushMsgService.PushAlarmMsgAsync(new TpushMsgModel
//{
// C_DevStoreCode = updateModel.C_DevStoreCode,
// C_MsgTypeCode = "MSG_TYPE_009",
// Msg = updateModel.C_Name + " " + updateModel.C_Remark,
// Subject = updateModel.C_Status == "5" ? "维保取消，设备名：" : "维保确认，设备名：" + updateModel.C_DevStoreName,
// DevNumber = updateModel.C_DevStoreCode,
// DevName = updateModel.C_DevStoreName,
//});
}
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message + ex.InnerException);
}
return new ApiResult(ReturnCode.Success);
}
/// <summary>
/// 创建巡检记录，带照片
/// </summary>
/// <param name="records"></param>
/// <param name="Files"></param>
/// <returns></returns>
[Route("CreateRecordAndImageAsync")]
[HttpPost]
public async Task<ApiResult> CreateRecordAndImageAsync(IEnumerable<TispRecordViewModel> records, IFormCollection Files)
{
if (records == null)
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
await _tispRecordService.CreateRecordAndImageAsync(records);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
return new ApiResult(ReturnCode.Success);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpDelete("DeleteRecordAsync/{id}")]
public async Task<ApiResult> DeleteRecordAsync(Guid id)
{
if (Guid.Empty == id)
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
await _tispRecordService.DeleteAsync(id);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
return new ApiResult(ReturnCode.Success);
}
/// <summary>
/// 更新巡检记录
/// </summary>
/// <param name="id"></param>
/// <param name="updateModel"></param>
/// <returns></returns>
[HttpPut("UpdateRecordAsync/{id}")]
public async Task<ApiResult> UpdateSpotAsync(Guid id, TispRecordUpdateViewModel updateModel)
{
if (Guid.Empty == id)
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
await _tispRecordService.UpdateAsync(id, updateModel);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
return new ApiResult(ReturnCode.Success);
}
/// <summary>
/// 上传巡检图片
/// </summary>
/// <param name="id">巡检记录Id</param>
/// <param name="Files"></param>
/// <returns></returns>
[HttpPost("UploadRecordImageAsync/{id}")]
public async Task<ApiResult> UploadRecordImageAsync(Guid id, IFormCollection Files)
{
try
{
//var form = Request.Form;//直接从表单里面获取文件名不需要参数
string dd = Files["File"];
var form = Files;//定义接收类型的参数
Hashtable hash = new Hashtable();
IFormFileCollection cols = Request.Form.Files;
if (cols == null || cols.Count == 0)
{
return new ApiResult(ReturnCode.GeneralError, "没有上传文件");
}
foreach (IFormFile file in cols)
{
//定义图片数组后缀格式
string[] LimitPictureType = { ".JPG", ".JPEG", ".GIF", ".PNG", ".BMP" };
//获取图片后缀是否存在数组中
string currentPictureExtension = Path.GetExtension(file.FileName).ToUpper();
if (LimitPictureType.Contains(currentPictureExtension))
{
//为了查看图片就不在重新生成文件名称了
var dateDirectory = DateTime.Now.ToString("yyyyMM");
var relativePath = Path.Combine("wwwroot/uploads/images/", dateDirectory);
var directoryPath = Path.Combine(Directory.GetCurrentDirectory(), relativePath);
var fileRelativePath = Path.Combine(relativePath + "/", file.FileName);
if (!Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
}
var path = Path.Combine(directoryPath + "/", file.FileName);
//var path = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", filePath);
////文件存储路径
//var new_path = string.Format("/uploads/images/" + dateName + "/", file.FileName);
////获取当前web目录
//var webRootPath = _hostingEnvironment.WebRootPath;
//var path = webRootPath + new_path;
using (var stream = new FileStream(path, FileMode.Create))
{
//图片路径保存到数据库里面去
bool flage = true;
await _tispRecordImageService.CreateOneAsync(new TispRecordImageViewModel{ C_RecordItemCode = id,C_ImageURL = fileRelativePath, C_Status= '1' });
if (flage == true)
{
//再把文件保存的文件夹中
file.CopyTo(stream);
hash.Add("file", "/" + fileRelativePath);
}
}
}
else
{
return new ApiResult(ReturnCode.GeneralError, "请上传指定格式的图片");
}
}
return new ApiResult<Hashtable>(new Hashtable(hash));
//return new ApiResult(ReturnCode.Success, "上传成功");
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message + ex.InnerException);
}
}
/// <summary>
/// 获取所有巡检点的巡检状况
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpGet("GetAllSpotRecordAsync")]
[AllowAnonymous]
public async Task<ApiResult> GetAllSpotRecordAsync([FromQuery] AllSpotRecordSearchModel searchModel)
{
try
{
if (null == searchModel || string.IsNullOrEmpty(searchModel.C_StoreCode))
{
return new ApiResult(ReturnCode.ArgsError);
}
searchModel.IsPagination = false;
IEnumerable<AllSpotRecordWithDevViewModel> allSpotRecordList = await _tispRecordService.GetAllSpotRecordAsync(searchModel);
return new ApiResult<PagesModel<AllSpotRecordWithDevViewModel>>(new PagesModel<AllSpotRecordWithDevViewModel>(allSpotRecordList?.ToList(), searchModel));
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 获取巡检员的分配的巡检点巡检情况
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpGet("GetUserSpotRecordAsync")]
public async Task<ApiResult> GetUserSpotRecordAsync([FromQuery] UserSpotRecordSearchModel searchModel)
{
try
{
if (null == searchModel || string.IsNullOrEmpty(searchModel.C_StoreCode))
{
return new ApiResult(ReturnCode.ArgsError);
}
IEnumerable<AllSpotRecordViewModel> allSpotRecordList = await _tispRecordService.GetUserSpotRecordAsync(searchModel);
return new ApiResult<PagesModel<AllSpotRecordViewModel>>(new PagesModel<AllSpotRecordViewModel>(allSpotRecordList?.ToList(), searchModel));
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 获取用户当天已经巡检过的巡检点数
/// </summary>
/// <param name="usrId"></param>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetUserTodaySpotRecordCountAsync/{usrId}/{storeCode}")]
public async Task<ApiResult> GetUserTodaySpotRecordCountAsync(Guid usrId,string storeCode)
{
try
{
int result = await _tispRecordService.GetUserTodaySpotRecordCountAsync(usrId, storeCode);
return new ApiResult<int>(result);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 获取用户当天已经巡检过的巡检点记录
/// </summary>
/// <param name="usrId"></param>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetUserTodaySpotRecordsAsync/{usrId}/{storeCode}")]
public async Task<ApiResult> GetUserTodaySpotRecordsAsync(Guid usrId, string storeCode)
{
try
{
IEnumerable<AllSpotRecordViewModel> allSpotRecordList = await _tispRecordService.GetUserTodaySpotRecordsAsync(usrId, storeCode);
return new ApiResult<IEnumerable<AllSpotRecordViewModel>>(allSpotRecordList);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 获取用户当天还未巡检过的巡检点数
/// </summary>
/// <param name="usrId"></param>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetUserTodayNotSpotRecordCountAsync/{usrId}/{storeCode}")]
public async Task<ApiResult> GetUserTodayNotSpotRecordCountAsync(Guid usrId, string storeCode)
{
try
{
int result = await _tispRecordService.GetUserTodayNotSpotRecordCountAsync(usrId, storeCode);
return new ApiResult<int>(result);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 获取用户当天还未巡检过的巡检点
/// </summary>
/// <param name="usrId"></param>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetUserTodayNotSpotRecordsAsync/{usrId}/{storeCode}")]
public async Task<ApiResult> GetUserTodayNotSpotRecordsAsync(Guid usrId,string storeCode)
{
if (Guid.Empty.Equals(usrId) || string.IsNullOrEmpty(storeCode))
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
IEnumerable<AllSpotRecordViewModel> allSpotRecordList = await _tispRecordService.GetUserTodayNotSpotRecordsAsync(usrId, storeCode);
return new ApiResult<IEnumerable<AllSpotRecordViewModel>>(allSpotRecordList);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 最近三十天巡检统计
/// </summary>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetRecords30DaysStatisticsAsync/{storeCode}")]
public async Task<ApiResult> GetRecords30DaysStatisticsAsync(string storeCode)
{
try
{
TispRecord30DaysStatisticsViewModel statistics = await _tispRecordService.GetRecords30DaysStatisticsAsync(storeCode);
return new ApiResult<TispRecord30DaysStatisticsViewModel>(statistics);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 最近7天异常处理统计
/// </summary>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetAlarmRecovery7DaysStatisticsAsync/{storeCode}")]
public async Task<ApiResult> GetAlarmRecovery7DaysStatisticsAsync(string storeCode)
{
try
{
TispAlarmRecovery7DaysStatisticsViewModel statistics = await _tispRecordService.GetAlarmRecovery7DaysStatisticsAsync(storeCode);
return new ApiResult<TispAlarmRecovery7DaysStatisticsViewModel>(statistics);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 取当天关于巡检点的正常，异常统计
/// </summary>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetTodaySpotStatisticsAsync/{storeCode}")]
public async Task<ApiResult> GetTodaySpotStatisticsAsync(string storeCode)
{
try
{
TodaySpotStatistics statistics = await _tispRecordService.GetTodaySpotStatisticsAsync(storeCode);
return new ApiResult<TodaySpotStatistics>(statistics);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 取当天关于巡检点内容的正常，异常，恢复统计
/// </summary>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetTodaySpotContentStatisticsAsync/{storeCode}")]
public async Task<ApiResult> GetTodaySpotContentStatisticsAsync(string storeCode)
{
try
{
TodaySpotContentStatistics statistics = await _tispRecordService.GetTodaySpotContentStatisticsAsync(storeCode);
return new ApiResult<TodaySpotContentStatistics>(statistics);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 最近12个月巡检次数统计
/// </summary>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetRecord12MonthStatisticsAsync/{storeCode}")]
public async Task<ApiResult> GetRecord12MonthStatisticsAsync(string storeCode)
{
try
{
Record12MonthStatisticsViewModel statistics = await _tispRecordService.GetRecord12MonthStatisticsAsync(storeCode);
return new ApiResult<Record12MonthStatisticsViewModel>(statistics);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 最近12个月巡检次数统计,大屏
/// </summary>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetRecord12MonthStatisticsFullScreenAsync/{storeCode}")]
[AllowAnonymous]
public async Task<IEnumerable<FullScreenRecordItem>> GetRecord12MonthStatisticsFullScreenAsync(string storeCode)
{
if (string.IsNullOrEmpty(storeCode))
{
return null;
}
try
{
var content = await _tispRecordService.GetRecord12MonthStatisticsFullScreenAsync(storeCode);
return content;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 最近12个月点检、维保、维修次数统计,大屏
/// </summary>
/// <param name="storeCode"></param>
/// <returns></returns>
[HttpGet("GetAllDevOpsRecord12MonthStatisticsFullScreenAsync/{storeCode}")]
[AllowAnonymous]
public async Task<IEnumerable<FullScreenRecordItem>> GetAllDevOpsRecord12MonthStatisticsFullScreenAsync(string storeCode)
{
if (string.IsNullOrEmpty(storeCode))
{
return null;
}
try
{
var content = await _tispRecordService.GetAllDevOpsRecord12MonthStatisticsFullScreenAsync(storeCode);
return content;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 发送邮件(附件为巡检记录)
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("SendRecordsToEmail")]
public async Task SendRecordsToEmail(SendRecordToEmailModel searchModel)
{
if (searchModel == null || string.IsNullOrEmpty(searchModel.C_StoreCode))
{
return;
}
try
{
var recordList = await _tispRecordService.GetRecordsConditionAsync(searchModel);
string zip = @"wwwroot\\ZipFile";
if (!System.IO.Directory.Exists(zip))
{
Directory.CreateDirectory(zip);
}
string zipPaht = zip + "\\点检记录下载_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".zip";
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5));
var titleRow = sheet.CreateRow(0);
titleRow.Height = 20 * 25;
NPOI.SS.UserModel.ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue("设备点检记录");
//第一行字体样式
IFont font = workbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 16;
font.FontName = "宋体";
ICellStyle titleCellStyle = workbook.CreateCellStyle();
titleCellStyle.SetFont(font);
titleCellStyle.Alignment = HorizontalAlignment.Center; //字体居中
//边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCell.CellStyle = titleCellStyle;
var headRow = sheet.CreateRow(1);
//headRow.CreateCell(0).SetCellValue("序 号");
headRow.CreateCell(0).SetCellValue("点检点名称");
headRow.CreateCell(1).SetCellValue("点检编号");
headRow.CreateCell(2).SetCellValue("点检人");
headRow.CreateCell(3).SetCellValue("点检时间");
headRow.CreateCell(4).SetCellValue("状态");
headRow.CreateCell(5).SetCellValue("备注");
//第二行,列名
IFont font1 = workbook.CreateFont();
font1.IsBold = true;
font1.FontHeightInPoints = 12;
font1.FontName = "宋体";
ICellStyle headCellStyle = workbook.CreateCellStyle();
headCellStyle.SetFont(font1);
//边框
headCellStyle.BorderBottom = BorderStyle.Thin;
headCellStyle.BorderLeft = BorderStyle.Thin;
headCellStyle.BorderRight = BorderStyle.Thin;
headCellStyle.BorderTop = BorderStyle.Thin;
foreach (var item in headRow.Cells)
{
item.CellStyle = headCellStyle;
}
int start = 2;
IFont font3 = workbook.CreateFont();
font3.FontHeightInPoints = 9;
font3.FontName = "宋体";
ICellStyle contentCellStyle = workbook.CreateCellStyle();
contentCellStyle.SetFont(font3);
//边框
contentCellStyle.BorderBottom = BorderStyle.Thin;
contentCellStyle.BorderLeft = BorderStyle.Thin;
contentCellStyle.BorderRight = BorderStyle.Thin;
contentCellStyle.BorderTop = BorderStyle.Thin;
foreach (var item in recordList)
{
if (item!=null)
{
var row = sheet.CreateRow(start);
row.CreateCell(0).SetCellValue(item.C_Name);
row.CreateCell(1).SetCellValue(item.C_Number);
row.CreateCell(2).SetCellValue(item.C_CreateByName);
row.CreateCell(3).SetCellValue(item.D_CreateOn.ToString("yyyy-MM-dd hh:mm:ss"));
switch (item.C_Status)
{
case "0":
row.CreateCell(4).SetCellValue("禁用");
break;
case "1":
row.CreateCell(4).SetCellValue("正常");
break;
case "2":
row.CreateCell(4).SetCellValue("异常");
break;
case "3":
row.CreateCell(4).SetCellValue("确认异常");
break;
case "4":
row.CreateCell(4).SetCellValue("取消异常");
break;
default:
row.CreateCell(4).SetCellValue("未知状态");
break;
}
row.CreateCell(5).SetCellValue(item.C_Remark);
start++;
foreach (var cell in row.Cells)
{
cell.CellStyle = contentCellStyle;
}
}
}
// 自适应单元格
for (int i = 0; i < sheet.LastRowNum; i++)
{
sheet.AutoSizeRow(i);
}
for (int i = 0; i < 7; i++)
{
sheet.AutoSizeColumn(i, true);
}
using (var zipFile = new FileStream(zipPaht, FileMode.Create))
{
// 列表Excel
using (var memoryStream = new NpoiMemoryStream())
{
workbook.Write(memoryStream);
byte[] excelData = memoryStream.ToArray();
using (var archive = new ZipArchive(zipFile, ZipArchiveMode.Update))
{
archive.CreateEntry("点检记录报表.xlsx").Open().Write(excelData, 0, excelData.Length);
}
}
}
//内容word
foreach (var cell in recordList.ToList())
{
if (cell != null)
{
var repairOrders = await _tispRecordItemService.GetRecordItemsByRecordIdAsync(cell.C_ID);
List<List<TispRecordItemDetailViewModel>> recordItems = repairOrders.ToList();
if (recordItems.Count > 0)
{
XWPFDocument doc = new XWPFDocument();
var paragraph = doc.CreateParagraph();
var run1 = paragraph.CreateRun();
run1.FontSize = 18;
int serialNum = 1;
run1.SetText("点检主题：" + cell.C_Name);
foreach (var itemList in recordItems)
{
if (itemList==null)
{
continue;
}
var p0 = doc.CreateParagraph();
var run0 = p0.CreateRun();
run0.FontSize = 16;
run0.SetText(" " + serialNum.ToString() + "、"+ itemList.FirstOrDefault()?.C_Name);
int serialNum1 = 1;
foreach (var item in itemList)
{
if(item==null)continue;
var p1 = doc.CreateParagraph();
var run = p1.CreateRun();
run.FontSize = 14;
string status = string.Empty;
//1 = 正常；0 = 异常；2 = 恢复；3 = 确认异常；4 = 取消异常
switch (item.C_Status)
{
case "1":
status = "正常";
break;
case "2":
status = "异常已处理";
break;
case "3":
status = "确认异常";
break;
case "4":
status = "取消异常";
break;
case "0":
status = "巡检异常";
break;
}
run.SetText(" " + serialNum.ToString() + "." + serialNum1.ToString());
run.AddCarriageReturn();
run.AppendText(" 操作时间：" + item.D_CreateOn.ToString("yyyy-MM-dd HH:mm"));
run.AddCarriageReturn();
run.AppendText(" 状态：" + status);
run.AddCarriageReturn();
run.AppendText(" 处理人员：" + item.ReportUserName);
if (item.RecordImageList != null && item.RecordImageList.Count() > 0)
{
run.AddCarriageReturn();
run.AppendText(" 现场拍照：");
foreach (var path in item.RecordImageList)
{
string pathFile = path.C_ImageURL;
if (pathFile != null)
{
if (pathFile[0].ToString() == @"/")
{
pathFile = pathFile.Substring(1);
}
if (!System.IO.File.Exists(pathFile))
{
pathFile = @"wwwroot/error.png";
}
FileStream fileStream = new FileStream(pathFile, FileMode.Open, FileAccess.Read);
run.AddCarriageReturn();
run.AddPicture(fileStream, 6, pathFile, Units.ToEMU(100), Units.ToEMU(100));
}
}
}
run.AddCarriageReturn();
run.AppendText(" 现场描述：" + item.C_Remark);
serialNum1++;
}
serialNum++;
}
using (var zipFile = new FileStream(zipPaht, FileMode.OpenOrCreate))
{
using (var memoryStream = new NpoiMemoryStream())
{
doc.Write(memoryStream);
byte[] excelData = memoryStream.ToArray();
using (var archive = new ZipArchive(zipFile, ZipArchiveMode.Update))
{
archive.CreateEntry("点检记录/" + cell.C_ID + ".docx").Open().Write(excelData, 0, excelData.Length);
}
}
}
}
}
}
var zipFile1 = new FileStream(zipPaht, FileMode.Open);
string emailName = $"{searchModel.DevName} 设备点检记录 {searchModel.Start.ToString("yyyy-MM-dd HH:mm:ss")}至{searchModel.End.ToString("yyyy-MM-dd HH:mm:ss")}";
EmailHelper.SendEmail(searchModel.Mails, emailName, "", "报表见附件", $"{emailName}.zip", "application/zip", zipFile1);
System.IO.File.Delete(zipPaht);
//using var stream = new NpoiMemoryStream();
//workbook.Write(stream);
//stream.Seek(0, SeekOrigin.Begin);
//EmailHelper.SendEmail(searchModel.Mails, $"{searchModel.Start}-{searchModel.End}设备点检记录报表", "", "报表见附件", $"{searchModel.Start}-{searchModel.End}设备点检记录报表.xlsx", "application/vnd.ms-excel", stream);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 导出点检记录压缩包
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("ExportZip")]
[AllowAnonymous]
public async Task<IActionResult> ExportZip(TispRecordSearchModel searchModel)
{
var recordList = await _tispRecordService.GetRecordsConditionAsync(searchModel);
string zip = @"wwwroot\\ZipFile";
if (!System.IO.Directory.Exists(zip))
{
Directory.CreateDirectory(zip);
}
string zipPaht = zip + "\\点检记录下载.zip";
System.IO.File.Delete(zipPaht);
TpntStoreViewModel spotModel = null;
if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
{
Guid guid = Guid.Parse(searchModel.C_StoreCode);
spotModel = await _TpntStoreService.GetByIdAsync(guid);
}
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region
//标题加粗居中.
IFont font = workbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 25;
font.FontName = "宋体";
ICellStyle titleCellStyle = workbook.CreateCellStyle();
titleCellStyle.SetFont(font);
titleCellStyle.Alignment = HorizontalAlignment.Center; //字体居中
//边框
titleCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
titleCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
titleCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
titleCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
titleCellStyle.WrapText = true;
#endregion
#region
//表头加粗居中.
IFont font1 = workbook.CreateFont();
font1.IsBold = true;
font1.FontHeightInPoints = 14;
font1.FontName = "宋体";
ICellStyle headCellStyle = workbook.CreateCellStyle();
headCellStyle.SetFont(font1);
headCellStyle.Alignment = HorizontalAlignment.Center; //字体居中
//边框
headCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headCellStyle.WrapText = true;
#endregion
#region
//行.
IFont font2 = workbook.CreateFont();
font2.IsBold = false;
font2.FontHeightInPoints = 12;
font2.FontName = "宋体";
ICellStyle rowCellStyle = workbook.CreateCellStyle();
rowCellStyle.SetFont(font2);
rowCellStyle.Alignment = HorizontalAlignment.Center; //字体居中
//边框
rowCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
rowCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
rowCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
rowCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
rowCellStyle.WrapText = true;
#endregion
#region 超链接
// 创建样式
ICellStyle styleLInk = workbook.CreateCellStyle();
// 设置字体颜色为蓝色
IFont fontLink = workbook.CreateFont();
fontLink.IsBold = false;
fontLink.FontHeightInPoints = 12;
fontLink.FontName = "宋体";
fontLink.Color = IndexedColors.Green.Index;
styleLInk.SetFont(fontLink);
// 设置下划线
fontLink.Underline = FontUnderlineType.Single;
styleLInk.SetFont(fontLink);
styleLInk.Alignment = HorizontalAlignment.Center; //字体居中
//边框
styleLInk.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleLInk.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleLInk.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleLInk.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleLInk.WrapText = true;
#endregion
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7));
IRow title = sheet.CreateRow(0);
title.Height = 30 * 25;
title.CreateCell(0).SetCellValue("点检记录报表" + (spotModel == null ? "" : "(" + spotModel.C_Name + ")"));
foreach (var item in title.Cells)
{
item.CellStyle = titleCellStyle;
}
IRow head = sheet.CreateRow(1);
head.CreateCell(0).SetCellValue("序号");
head.CreateCell(1).SetCellValue("点检点名称");
head.CreateCell(2).SetCellValue("点检点编号");
head.CreateCell(3).SetCellValue("点检人");
head.CreateCell(4).SetCellValue("点检时间");
head.CreateCell(5).SetCellValue("状态");
head.CreateCell(6).SetCellValue("备注");
head.CreateCell(7).SetCellValue("附件");
foreach (var item in head.Cells)
{
item.CellStyle = headCellStyle;
}
int rowNumber = 2; int number = 1;
if (recordList.Any() && recordList.FirstOrDefault() != null)
{
foreach (var x in recordList)
{
IRow content = sheet.CreateRow(rowNumber);
content.CreateCell(0).SetCellValue(number);
content.CreateCell(1).SetCellValue(x.C_Name);
content.CreateCell(2).SetCellValue(x.C_Number);
content.CreateCell(3).SetCellValue(x.C_CreateByName);
content.CreateCell(4).SetCellValue(x.D_CreateOn.ToString("yyyy-MM-dd HH:mm"));
string statusVal = "";
switch (x.C_Status)
{
case "0": statusVal = "禁用"; break;
case "1": statusVal = "正常"; break;
case "2": statusVal = "异常"; break;
case "3": statusVal = "确认异常"; break;
case "4": statusVal = "取消异常"; break;
default: statusVal = "未知状态"; break;
}
content.CreateCell(5).SetCellValue(statusVal);
content.CreateCell(6).SetCellValue(x.C_Remark);
//超链接
NPOI.SS.UserModel.ICell cell = content.CreateCell(7);
cell.SetCellValue("附件");
IHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = ("点检记录/" + x.C_ID + ".docx");
cell.Hyperlink = link;
foreach (var item in content.Cells)
{
item.CellStyle = rowCellStyle;
}
// 应用样式到单元格
cell.CellStyle = styleLInk;
rowNumber++; number++;
}
}
else
{
IRow content1 = sheet.CreateRow(rowNumber);
content1.CreateCell(0).SetCellValue("暂无数据");
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 7));
}
for (int i = 0; i <= 7; i++)
{
if (i == 0)
{
sheet.SetColumnWidth(i, 2000);
}
else if (i == 1)
{
sheet.SetColumnWidth(i, 7000);
}
else
{
sheet.SetColumnWidth(i, 4500);
}
}
using (var zipFile = new FileStream(zipPaht, FileMode.Create))
{
// 列表Excel
using (var memoryStream = new NpoiMemoryStream())
{
workbook.Write(memoryStream);
byte[] excelData = memoryStream.ToArray();
using (var archive = new ZipArchive(zipFile, ZipArchiveMode.Update))
{
archive.CreateEntry("点检记录报表.xlsx").Open().Write(excelData, 0, excelData.Length);
}
}
}
//内容word
foreach (var cell in recordList.ToList())
{
if (cell != null)
{
var repairOrders = await _tispRecordItemService.GetRecordItemsByRecordIdAsync(cell.C_ID);
List<List<TispRecordItemDetailViewModel>> recordItems = repairOrders.ToList();
if (recordItems.Count > 0)
{
XWPFDocument doc = new XWPFDocument();
var paragraph = doc.CreateParagraph();
var run1 = paragraph.CreateRun();
run1.FontSize = 18;
int serialNum = 1;
run1.SetText("点检主题：" + cell.C_Name);
foreach (var itemList in recordItems)
{
if (itemList == null) { continue; }
var p0 = doc.CreateParagraph();
var run0 = p0.CreateRun();
run0.FontSize = 16;
run0.SetText(" " + serialNum.ToString() + "、" + itemList.FirstOrDefault()?.C_Name);
int serialNum1 = 1;
foreach (var item in itemList)
{
if(item == null) { continue; }
var p1 = doc.CreateParagraph();
var run = p1.CreateRun();
run.FontSize = 14;
string status = string.Empty;
//1 = 正常；0 = 异常；2 = 恢复；3 = 确认异常；4 = 取消异常
switch (item.C_Status)
{
case "1":
status = "正常";
break;
case "2":
status = "异常已处理";
break;
case "3":
status = "确认异常";
break;
case "4":
status = "取消异常";
break;
case "0":
status = "巡检异常";
break;
}
run.SetText(" " + serialNum.ToString() + "." + serialNum1.ToString());
run.AddCarriageReturn();
run.AppendText(" 操作时间：" + item.D_CreateOn.ToString("yyyy-MM-dd HH:mm"));
run.AddCarriageReturn();
run.AppendText(" 状态：" + status);
run.AddCarriageReturn();
run.AppendText(" 处理人员：" + item.ReportUserName);
if (item.RecordImageList != null && item.RecordImageList.Count() > 0)
{
run.AddCarriageReturn();
run.AppendText(" 现场拍照：");
foreach (var path in item.RecordImageList)
{
string pathFile = path.C_ImageURL;
if (pathFile != null)
{
if (pathFile[0].ToString() == @"/")
{
pathFile = pathFile.Substring(1);
}
if (!System.IO.File.Exists(pathFile))
{
pathFile = @"wwwroot/error.png";
}
FileStream fileStream = new FileStream(pathFile, FileMode.Open, FileAccess.Read);
run.AddCarriageReturn();
run.AddPicture(fileStream, 6, pathFile, Units.ToEMU(100), Units.ToEMU(100));
}
}
}
run.AddCarriageReturn();
run.AppendText(" 现场描述：" + item.C_Remark);
serialNum1++;
}
serialNum++;
}
using (var zipFile = new FileStream(zipPaht, FileMode.OpenOrCreate))
{
using (var memoryStream = new NpoiMemoryStream())
{
doc.Write(memoryStream);
byte[] excelData = memoryStream.ToArray();
using (var archive = new ZipArchive(zipFile, ZipArchiveMode.Update))
{
archive.CreateEntry("点检记录/" + cell.C_ID + ".docx").Open().Write(excelData, 0, excelData.Length);
}
}
}
}
}
}
var zipFile1 = new FileStream(zipPaht, FileMode.Open);
// 将压缩后的数据作为响应返回给客户端（这里假设你使用的是HTTP GET请求）
return File(zipFile1, "application/zip", "点检记录报表.zip"); // 这里假设你的文件类型是.xlsx的GZip压缩文件。你可以根据需要调整MIME类型和文件扩展名。
}
}
}