using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; 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.Common; using Ropin.Inspection.Model; using Ropin.Inspection.Model.ViewModel; using Ropin.Inspection.Service.Interface; using System; using System.Collections.Generic; using System.IO.Compression; using System.IO; using System.Linq; using System.Threading.Tasks; using Ropin.Inspection.Service; using Ropin.Inspection.Model.SearchModel; using ICSharpCode.SharpZipLib.Zip; using Ropin.Inspection.Model.SearchModel.SYS; namespace Ropin.Inspection.Api.Controllers { [AllowAnonymous] /// /// 组织架构 /// public class TsysOrganizeController : BaseController { public ILogger _logger { get; } private readonly ITsysOrganizeService _service; private readonly ITpntStoreOrgService _TpntStoreOrgService; private readonly ITsysUserService _tsysUserService; private readonly ITdevDevStoreService _TdevDevStoreService; /// /// 构造函数 /// /// /// public TsysOrganizeController(ITsysOrganizeService service, ILogger logger, ITpntStoreOrgService TpntStoreOrgService, ITsysUserService tsysUserService, ITdevDevStoreService TdevDevStoreService) { _service = service; _logger = logger; _TpntStoreOrgService = TpntStoreOrgService; _tsysUserService = tsysUserService; _TdevDevStoreService = TdevDevStoreService; } /// /// 获取所有的组织架构,状态为非禁用 /// /// [HttpGet("GetOrganizeListTreeAsync")] [ResponseCache(Duration = 60)] public async Task GetListTreeAsync() { try { var list = await _service.GetOrganizeListTreeAsync(); return new ApiResult>(list); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } } /// /// 获取所有的组织架构 /// /// [HttpGet("GetAllOrganizeListTreeAsync")] public async Task GetAllOrganizeListTreeAsync() { try { var list = await _service.GetListTreeAsync(); return new ApiResult>(list); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } } /// /// 通过名称获取组织架构 /// /// [HttpGet("GetAllOrganizeListTreeByNameAsync/{name}")] public async Task GetAllOrganizeListTreeByNameAsync(string name) { try { var list = await _service.GetListTreeAsync(); if(string.IsNullOrWhiteSpace(name)) return new ApiResult>(list); else return new ApiResult>(list.Where(x=>x.C_Name.Contains(name)).ToList()); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } } /// /// 获取组织架构,通过License和类型 /// /// [HttpGet("GetOrgsByAsync")] public async Task GetOrgsByAsync() { try { var list = await _service.GetOrgsByAsync(); return new ApiResult(list); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } } /// /// 通过组织架构Code获取单个组织架构信息 /// /// /// [HttpGet("GetOrganizeAsync/{code}")] public async Task GetOrganizeAsync(Guid code) { if (Guid.Empty == code) { return new ApiResult(ReturnCode.GeneralError); } try { var Organize = await _service.GetByIdAsync(code); return new ApiResult(Organize); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } } /// /// 创建组织架构 /// /// /// [Route("CreateOrganizeAsync")] [HttpPost] public async Task CreateOrganizeAsync(TsysOrganizeCreateViewModel Organize) { if (Organize == null) { return new ApiResult(ReturnCode.ArgsError); } try { await _service.CreateAsync(Organize); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } return new ApiResult(ReturnCode.Success); } /// /// 删除组织架构 /// /// /// [HttpDelete("DeleteOrganizeAsync/{id}")] public async Task DeleteOrganizeAsync(Guid id) { if (Guid.Empty == id) { return new ApiResult(ReturnCode.GeneralError); } try { await _service.DeleteAsync(id); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } return new ApiResult(ReturnCode.Success); } /// /// 更新组织架构 /// /// /// /// [HttpPut("UpdateOrganizeAsync/{id}")] public async Task UpdateOrganizeAsync(Guid id, TsysOrganizeUpdateViewModel updateModel) { if (Guid.Empty == id) { return new ApiResult(ReturnCode.GeneralError); } try { await _service.UpdateAsync(id, updateModel); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } return new ApiResult(ReturnCode.Success); } /// /// 通过组织架构ID取组织架构树 /// /// /// [HttpGet("GetByOrganizeTreeCodeAsync/{id}")] public async Task GetByOrganizeCodeAsync(Guid id) { if (Guid.Empty == id) { return new ApiResult(ReturnCode.GeneralError); } try { var OrganizeTree = await _service.GetByOrganizeCodeAsync(id); return new ApiResult>(OrganizeTree); } catch (Exception ex) { return new ApiResult(ReturnCode.GeneralError, ex.Message); } } /// /// 发送邮件(设备、人员、业主信息) /// /// /// [HttpPost("SendDevUserStoreToEmail")] public async Task SendDevUserStoreToEmail(SendToEmailModel model) { try { TpntStoreSearchModel searchModel=new TpntStoreSearchModel(); searchModel.orgCode = model.orgCode; var contentList = await _TpntStoreOrgService.GetStoresByOrgCodeAsync(searchModel);//业主信息 TsysUserSearchByNameModel searchUserModel=new TsysUserSearchByNameModel() { C_Status="1",G_OrganizeCode=model.orgCode,IsPagination=false}; var userList = await _tsysUserService.GetByNameAsync(searchUserModel);//人员 IWorkbook workbook = new XSSFWorkbook(); #region //第一行字体样式 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; #endregion #region //第二行,列名 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; #endregion #region 内容 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; #endregion #region 人员 ISheet sheet = workbook.CreateSheet("人员信息"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 11)); var titleRow = sheet.CreateRow(0); titleRow.Height = 20 * 25; NPOI.SS.UserModel.ICell titleCell = titleRow.CreateCell(0); titleCell.SetCellValue("人员信息"); titleCell.CellStyle = titleCellStyle; var headRow = sheet.CreateRow(1); headRow.CreateCell(0).SetCellValue("姓 名"); headRow.CreateCell(1).SetCellValue("性 别"); headRow.CreateCell(2).SetCellValue("身份证号码"); headRow.CreateCell(3).SetCellValue("生 日"); headRow.CreateCell(4).SetCellValue("联系地址"); headRow.CreateCell(5).SetCellValue("所属组织"); headRow.CreateCell(6).SetCellValue("岗 位"); headRow.CreateCell(7).SetCellValue("角 色"); headRow.CreateCell(8).SetCellValue("手机号码"); headRow.CreateCell(9).SetCellValue("Email"); headRow.CreateCell(10).SetCellValue("状 态"); headRow.CreateCell(11).SetCellValue("备 注"); foreach (var item in headRow.Cells) { item.CellStyle = headCellStyle; } int start = 2; if (userList!=null&&userList.Count()>0) { foreach (var item in userList) { if (item != null) { List roleList = null; if (item.Roles != null && item.Roles.Count() > 0) { roleList= item.Roles.Select(t => t.RoleName).ToList(); }; var row = sheet.CreateRow(start); row.CreateCell(0).SetCellValue(item.C_Name); row.CreateCell(1).SetCellValue(item.I_Render==1?"男": "女"); row.CreateCell(2).SetCellValue(item.C_IDNum); row.CreateCell(3).SetCellValue(item.D_BirthDay?.ToString("yyyy-MM-dd")); row.CreateCell(4).SetCellValue(item.C_Address); row.CreateCell(5).SetCellValue(item.OrgName); row.CreateCell(6).SetCellValue(item.C_Post); row.CreateCell(7).SetCellValue((roleList!=null&&roleList.Count>0)?string.Join(",",roleList):""); row.CreateCell(8).SetCellValue(item.C_Mobile); row.CreateCell(9).SetCellValue(item.C_Email); row.CreateCell(10).SetCellValue(item.C_Status=="1"?"正常":"禁用"); row.CreateCell(11).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 < 12; i++) { sheet.AutoSizeColumn(i, true); } #endregion #region 业主 ISheet sheet1 = workbook.CreateSheet("业主信息"); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8)); var titleRow1 = sheet1.CreateRow(0); titleRow1.Height = 20 * 25; NPOI.SS.UserModel.ICell titleCell1 = titleRow1.CreateCell(0); titleCell1.SetCellValue("业主信息"); titleCell1.CellStyle = titleCellStyle; var headRow1 = sheet1.CreateRow(1); headRow1.CreateCell(0).SetCellValue("名 称"); headRow1.CreateCell(1).SetCellValue("简 称"); headRow1.CreateCell(2).SetCellValue("网点类型"); headRow1.CreateCell(3).SetCellValue("行政区名称"); headRow1.CreateCell(4).SetCellValue("地 址"); headRow1.CreateCell(5).SetCellValue("GPS地址"); headRow1.CreateCell(6).SetCellValue("大屏GPS"); headRow1.CreateCell(7).SetCellValue("状 态"); headRow1.CreateCell(8).SetCellValue("备 注"); foreach (var item in headRow1.Cells) { item.CellStyle = headCellStyle; } int start1 = 2; if (contentList != null && contentList.Count() > 0) { foreach (var item in contentList) { if (item != null) { var row = sheet1.CreateRow(start1); row.CreateCell(0).SetCellValue(item.C_Name); row.CreateCell(1).SetCellValue(item.C_SName); row.CreateCell(2).SetCellValue(item.TypeName); row.CreateCell(3).SetCellValue(item.AreaName); row.CreateCell(4).SetCellValue(item.C_Address); row.CreateCell(5).SetCellValue(item.C_GPS); row.CreateCell(6).SetCellValue(item.C_LargeScreenGPS); row.CreateCell(7).SetCellValue(item.C_Status == "1" ? "正常" : "禁用"); row.CreateCell(8).SetCellValue(item.C_Remark); start1++; foreach (var cell in row.Cells) { cell.CellStyle = contentCellStyle; } } } } // 自适应单元格 for (int i = 0; i < sheet1.LastRowNum; i++) { sheet1.AutoSizeRow(i); } for (int i = 0; i < 9; i++) { sheet1.AutoSizeColumn(i, true); } #endregion #region 设备 if (contentList != null && contentList.Count() > 0) { foreach (var item in contentList) { if (item != null) { TdevDevStoreSearchModel searchDevModel = new TdevDevStoreSearchModel() { C_StoreCode =item.C_Code}; var devList = await _TdevDevStoreService.GetConditionAsync(searchDevModel); string sheetName = item.C_Name + "-设备信息"; ISheet sheetDev = workbook.CreateSheet(sheetName); sheetDev.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 11)); var titleRowDev = sheetDev.CreateRow(0); titleRowDev.Height = 20 * 25; NPOI.SS.UserModel.ICell titleCellDev = titleRowDev.CreateCell(0); titleCellDev.SetCellValue(sheetName); titleCellDev.CellStyle = titleCellStyle; var headRowDev = sheetDev.CreateRow(1); headRowDev.CreateCell(0).SetCellValue("名 称"); headRowDev.CreateCell(1).SetCellValue("设备编码"); headRowDev.CreateCell(2).SetCellValue("合同编号"); headRowDev.CreateCell(3).SetCellValue("运维开始"); headRowDev.CreateCell(4).SetCellValue("运维结束"); headRowDev.CreateCell(5).SetCellValue("设备二维码地址"); headRowDev.CreateCell(6).SetCellValue("设备地址"); headRowDev.CreateCell(7).SetCellValue("动态设备图片"); headRowDev.CreateCell(8).SetCellValue("静态设备图片"); headRowDev.CreateCell(9).SetCellValue("创建时间"); headRowDev.CreateCell(10).SetCellValue("状 态"); headRowDev.CreateCell(11).SetCellValue("备 注"); foreach (var itemDev in headRowDev.Cells) { itemDev.CellStyle = headCellStyle; } int startDev = 2; foreach (var dev in devList) { if (dev != null) { var row = sheetDev.CreateRow(startDev); row.HeightInPoints = 70; row.CreateCell(0).SetCellValue(dev.C_Name); row.CreateCell(1).SetCellValue(dev.C_NumberCode); row.CreateCell(2).SetCellValue(dev.C_ContractNumber); row.CreateCell(3).SetCellValue(dev.D_DevOpsStart?.ToString("yyyy-MM-dd")); row.CreateCell(4).SetCellValue(dev.D_DevOpsEnd?.ToString("yyyy-MM-dd")); if (System.IO.File.Exists(dev.C_DevQRUrl)) { // 创建一个画图的对象 IDrawing drawing = sheetDev.CreateDrawingPatriarch(); // 获取要插入图片的单元格位置 NPOI.SS.UserModel.ICell cell = row.CreateCell(5); // 把图片加入到工作簿中 byte[] imageBytes = null; using (FileStream fs = new FileStream(dev.C_DevQRUrl, FileMode.Open)) { imageBytes = new byte[fs.Length]; fs.Read(imageBytes, 0, (int)fs.Length); } int pictureIdx = workbook.AddPicture(imageBytes, NPOI.SS.UserModel.PictureType.JPEG); // 创建图片对象,并设置其属性 XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(new XSSFClientAnchor(0, 0, 1, 1, (short)cell.ColumnIndex, cell.RowIndex, (short)(cell.ColumnIndex + 1), cell.RowIndex + 1), pictureIdx); } else { row.CreateCell(5).SetCellValue(""); } row.CreateCell(6).SetCellValue(dev.C_GPS); if (System.IO.File.Exists(dev.C_Url)) { // 创建一个画图的对象 IDrawing drawing = sheetDev.CreateDrawingPatriarch(); // 获取要插入图片的单元格位置 NPOI.SS.UserModel.ICell cell = row.CreateCell(7); // 把图片加入到工作簿中 byte[] imageBytes = null; using (FileStream fs = new FileStream(dev.C_DevQRUrl, FileMode.Open)) { imageBytes = new byte[fs.Length]; fs.Read(imageBytes, 0, (int)fs.Length); } int pictureIdx = workbook.AddPicture(imageBytes, NPOI.SS.UserModel.PictureType.JPEG); // 创建图片对象,并设置其属性 XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(new XSSFClientAnchor(0, 0, 1, 1, (short)cell.ColumnIndex, cell.RowIndex, (short)(cell.ColumnIndex + 1), cell.RowIndex + 1), pictureIdx); } else { row.CreateCell(7).SetCellValue(""); } if (System.IO.File.Exists(dev.C_StaticUrl)) { // 创建一个画图的对象 IDrawing drawing = sheetDev.CreateDrawingPatriarch(); // 获取要插入图片的单元格位置 NPOI.SS.UserModel.ICell cell = row.CreateCell(8); // 把图片加入到工作簿中 byte[] imageBytes = null; using (FileStream fs = new FileStream(dev.C_DevQRUrl, FileMode.Open)) { imageBytes = new byte[fs.Length]; fs.Read(imageBytes, 0, (int)fs.Length); } int pictureIdx = workbook.AddPicture(imageBytes, NPOI.SS.UserModel.PictureType.JPEG); // 创建图片对象,并设置其属性 XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(new XSSFClientAnchor(0, 0, 1, 1, (short)cell.ColumnIndex, cell.RowIndex, (short)(cell.ColumnIndex + 1), cell.RowIndex + 1), pictureIdx); } else { row.CreateCell(8).SetCellValue(""); } row.CreateCell(9).SetCellValue(dev.D_CreateOn.ToString("yyyy-MM-dd HH:mm:ss")); string status = ""; switch (dev.C_Status) { case "0": status = "禁用";break; case "1": status = "分配、业主已购买"; break; case "2": status = "启运"; break; case "3": status = "停运"; break; case "4": status = "报警"; break; case "5": status = "报废"; break; case "6": status = "预警"; break; default: status = ""; break; } row.CreateCell(10).SetCellValue(status); row.CreateCell(11).SetCellValue(dev.C_Remark); startDev++; foreach (var cell in row.Cells) { cell.CellStyle = contentCellStyle; } } } // 自适应单元格 //for (int i = 0; i < sheetDev.LastRowNum; i++) //{ // sheetDev.AutoSizeRow(i); //} for (int i = 0; i < 12; i++) { sheetDev.AutoSizeColumn(i, true); } } } } #endregion using (var stream = new NpoiMemoryStream()) { workbook.Write(stream); stream.Seek(0, SeekOrigin.Begin); //return File(stream, "pplication/vnd.ms-excel", $"{model.OrgName}-设备清单记录报表.xlsx"); EmailHelper.SendEmail(model.Mails, $"{model.OrgName}-设备清单记录报表", "", "报表见附件", $"{model.OrgName}-设备清单记录报表.xlsx", "application/vnd.ms-excel", stream); } } catch (Exception ex) { throw; } } } }