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]
/// <summary>
/// 组织架构
/// </summary>
public class TsysOrganizeController : BaseController
{
public ILogger<TsysOrganizeController> _logger { get; }
private readonly ITsysOrganizeService _service;
private readonly ITpntStoreOrgService _TpntStoreOrgService;
private readonly ITsysUserService _tsysUserService;
private readonly ITdevDevStoreService _TdevDevStoreService;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="service"></param>
/// <param name="logger"></param>
public TsysOrganizeController(ITsysOrganizeService service, ILogger<TsysOrganizeController> logger, ITpntStoreOrgService TpntStoreOrgService, ITsysUserService tsysUserService, ITdevDevStoreService TdevDevStoreService)
{
_service = service;
_logger = logger;
_TpntStoreOrgService = TpntStoreOrgService;
_tsysUserService = tsysUserService;
_TdevDevStoreService = TdevDevStoreService;
}
/// <summary>
/// 获取所有的组织架构，状态为非禁用
/// </summary>
/// <returns></returns>
[HttpGet("GetOrganizeListTreeAsync")]
[ResponseCache(Duration = 60)]
public async Task<ApiResult> GetListTreeAsync()
{
try
{
var list = await _service.GetOrganizeListTreeAsync();
return new ApiResult<List<TsysOrganizeTree>>(list);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 获取所有的组织架构
/// </summary>
/// <returns></returns>
[HttpGet("GetAllOrganizeListTreeAsync")]
public async Task<ApiResult> GetAllOrganizeListTreeAsync()
{
try
{
var list = await _service.GetListTreeAsync();
return new ApiResult<List<TsysOrganizeTree>>(list);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 通过名称获取组织架构
/// </summary>
/// <returns></returns>
[HttpGet("GetAllOrganizeListTreeByNameAsync/{name}")]
public async Task<ApiResult> GetAllOrganizeListTreeByNameAsync(string name)
{
try
{
var list = await _service.GetListTreeAsync();
if(string.IsNullOrWhiteSpace(name))
return new ApiResult<List<TsysOrganizeTree>>(list);
else
return new ApiResult<List<TsysOrganizeTree>>(list.Where(x=>x.C_Name.Contains(name)).ToList());
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 获取组织架构,通过License和类型
/// </summary>
/// <returns></returns>
[HttpGet("GetOrgsByAsync")]
public async Task<ApiResult> GetOrgsByAsync()
{
try
{
var list = await _service.GetOrgsByAsync();
return new ApiResult<TsysOrg>(list);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 通过组织架构Code获取单个组织架构信息
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
[HttpGet("GetOrganizeAsync/{code}")]
public async Task<ApiResult> GetOrganizeAsync(Guid code)
{
if (Guid.Empty == code)
{
return new ApiResult(ReturnCode.GeneralError);
}
try
{
var Organize = await _service.GetByIdAsync(code);
return new ApiResult<TsysOrganizeViewModel>(Organize);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 创建组织架构
/// </summary>
/// <param name="Organize"></param>
/// <returns></returns>
[Route("CreateOrganizeAsync")]
[HttpPost]
public async Task<ApiResult> 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);
}
/// <summary>
/// 删除组织架构
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpDelete("DeleteOrganizeAsync/{id}")]
public async Task<ApiResult> 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);
}
/// <summary>
/// 更新组织架构
/// </summary>
/// <param name="id"></param>
/// <param name="updateModel"></param>
/// <returns></returns>
[HttpPut("UpdateOrganizeAsync/{id}")]
public async Task<ApiResult> 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);
}
/// <summary>
/// 通过组织架构ID取组织架构树
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpGet("GetByOrganizeTreeCodeAsync/{id}")]
public async Task<ApiResult> GetByOrganizeCodeAsync(Guid id)
{
if (Guid.Empty == id)
{
return new ApiResult(ReturnCode.GeneralError);
}
try
{
var OrganizeTree = await _service.GetByOrganizeCodeAsync(id);
return new ApiResult<List<TsysOrganizeTree>>(OrganizeTree);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 发送邮件(设备、人员、业主信息)
/// </summary>
/// <param name="orgCode"></param>
/// <returns></returns>
[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<string> 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;
}
}
}
}