367 lines
15 KiB
C#
367 lines
15 KiB
C#
|
using Backend.Models;
|
|||
|
|
using Microsoft.AspNetCore.Http;
|
|||
|
|
using Microsoft.AspNetCore.Mvc;
|
|||
|
|
using Microsoft.Extensions.Logging;
|
|||
|
|
using Newtonsoft.Json;
|
|||
|
|
using NPOI.HSSF.UserModel;
|
|||
|
|
using NPOI.SS.UserModel;
|
|||
|
|
using NPOI.XSSF.UserModel;
|
|||
|
|
using Repository.BackendRepository.Interface;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.IO;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Threading.Tasks;
|
|||
|
|
|
|||
|
|
namespace Backend.Controllers
|
|||
|
|
{
|
|||
|
|
public class RescueDeviceController : MybaseController<RescueDeviceController>
|
|||
|
|
{
|
|||
|
|
private readonly IBackendRepository backendRepository;
|
|||
|
|
|
|||
|
|
public RescueDeviceController(IBackendRepository backendRepository)
|
|||
|
|
{
|
|||
|
|
this.backendRepository = backendRepository;
|
|||
|
|
}
|
|||
|
|
public IActionResult FireExtinguisher()
|
|||
|
|
{
|
|||
|
|
return View();
|
|||
|
|
}
|
|||
|
|
public IActionResult AED()
|
|||
|
|
{
|
|||
|
|
return View();
|
|||
|
|
}
|
|||
|
|
[HttpPost]
|
|||
|
|
public async Task<ApiResult<List<KeyValue>>> GetFloorByBuild (string Building)
|
|||
|
|
{
|
|||
|
|
ApiResult<List<KeyValue>> apiResult = new ApiResult<List<KeyValue>>();
|
|||
|
|
List<KeyValue> KeyValue = new List<KeyValue>();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
var sqlString = @$"select floor_guid as Value, full_name as Name from floor a where a.deleted = 0 and a.building_guid = '{Building}' and a.status = 0 order by a.priority";
|
|||
|
|
KeyValue = await backendRepository.GetAllAsync<KeyValue>(sqlString);
|
|||
|
|
|
|||
|
|
apiResult.Code = "0000";
|
|||
|
|
apiResult.Data = KeyValue;
|
|||
|
|
}
|
|||
|
|
catch (Exception exception)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9999";
|
|||
|
|
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return apiResult;
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
[HttpPost]
|
|||
|
|
public async Task<ActionResult> RescueDeviceTable(RescueDeviceTableUse post)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
|
|||
|
|
List<RescueDeviceTable> RescueDeviceTables = new List<RescueDeviceTable>();
|
|||
|
|
ApiResult<List<RescueDeviceTable>> apiResult = new ApiResult<List<RescueDeviceTable>>();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
RescueDeviceTables = await backendRepository.GetAllAsync<RescueDeviceTable>("rescue_device", $"building_guid = '{post.build}' and floor_guid in @floors and rescue_device_kind = {post.kind}", new { floors = post.floors }, "floor_name");
|
|||
|
|
apiResult.Code = "0000";
|
|||
|
|
apiResult.Data = RescueDeviceTables;
|
|||
|
|
}
|
|||
|
|
catch (Exception exception)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9999";
|
|||
|
|
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】");
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
|||
|
|
}
|
|||
|
|
var result = Json(new
|
|||
|
|
{
|
|||
|
|
data = apiResult
|
|||
|
|
});
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
[HttpPost]
|
|||
|
|
public async Task<ApiResult<string>> ImportRescueDevice(ImportRescueDevice import)
|
|||
|
|
{
|
|||
|
|
ApiResult<string> apiResult = new ApiResult<string>();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
IWorkbook workbook;
|
|||
|
|
var filename_ext = Path.GetExtension(import.import_file.FileName).ToLower();
|
|||
|
|
if (filename_ext == ".xls")
|
|||
|
|
{
|
|||
|
|
workbook = new HSSFWorkbook(import.import_file.OpenReadStream());
|
|||
|
|
}
|
|||
|
|
else if (filename_ext == ".xlsx")
|
|||
|
|
{
|
|||
|
|
workbook = new XSSFWorkbook(import.import_file.OpenReadStream());
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
workbook = null;
|
|||
|
|
}
|
|||
|
|
if (workbook == null)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9998";
|
|||
|
|
apiResult.Msg = $"{import.import_file.FileName}該檔案失效，請重新操作。";
|
|||
|
|
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
var sqlString = @$"select floor_guid as Value, full_name as Name from floor a where a.deleted = 0 and a.building_guid = '{import.building}' and a.status = 0 order by a.priority";
|
|||
|
|
var floor = await backendRepository.GetAllAsync<KeyValue>(sqlString);
|
|||
|
|
List<Dictionary<string, object>> deviceImports = new List<Dictionary<string, object>>();
|
|||
|
|
var sheet = workbook.GetSheetAt(0);
|
|||
|
|
IRow header = sheet.GetRow(sheet.FirstRowNum);
|
|||
|
|
ICell cell = header.GetCell(1);
|
|||
|
|
if(!string.IsNullOrEmpty(cell.ToString()) && cell.ToString()!= "設備編號")
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9998";
|
|||
|
|
apiResult.Msg = $"{import.import_file.FileName}該檔案格式錯誤，請重新操作。";
|
|||
|
|
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
cell = header.GetCell(0);
|
|||
|
|
if (!string.IsNullOrEmpty(cell.ToString()) && cell.ToString() != "樓層")
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9998";
|
|||
|
|
apiResult.Msg = $"{import.import_file.FileName}該檔案格式錯誤，請重新操作。";
|
|||
|
|
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
cell = header.GetCell(2);
|
|||
|
|
if (!string.IsNullOrEmpty(cell.ToString()) && cell.ToString() != "位置")
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9998";
|
|||
|
|
apiResult.Msg = $"{import.import_file.FileName}該檔案格式錯誤，請重新操作。";
|
|||
|
|
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
List<int> columns = new List<int>();
|
|||
|
|
for(var a = sheet.FirstRowNum + 1;a <= sheet.LastRowNum; a++)
|
|||
|
|
{
|
|||
|
|
var floorname = sheet.GetRow(a).GetCell(0).ToString();
|
|||
|
|
var floorguid = floor.Where(k => k.Name.ToString() == floorname).Select(d =>d.Value).FirstOrDefault();
|
|||
|
|
if(floorguid == null)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9998";
|
|||
|
|
apiResult.Msg = $"{import.import_file.FileName}該檔案樓層名稱錯誤，請確認後重新操作。";
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
Dictionary<string, object> deviceImport = new Dictionary<string, object>();
|
|||
|
|
deviceImport.Add("@rescue_device_guid", Guid.NewGuid().ToString());
|
|||
|
|
deviceImport.Add("@rescue_device_id", sheet.GetRow(a).GetCell(1).ToString());
|
|||
|
|
deviceImport.Add("@floor_guid", floorguid);
|
|||
|
|
deviceImport.Add("@floor_name", sheet.GetRow(a).GetCell(0).ToString());
|
|||
|
|
deviceImport.Add("@location", sheet.GetRow(a).GetCell(2).ToString());
|
|||
|
|
deviceImport.Add("@rescue_device_kind", import.kind);
|
|||
|
|
deviceImport.Add("@building_guid", import.building);
|
|||
|
|
deviceImport.Add("@created_by", myUserInfo.Userinfo_guid);
|
|||
|
|
deviceImport.Add("@created_at", DateTime.Now);
|
|||
|
|
deviceImports.Add(deviceImport);
|
|||
|
|
}
|
|||
|
|
await backendRepository.PurgeOneByGuidWithCustomDBNameAndTable("rescue_device", $"building_guid = '{import.building}' and rescue_device_kind = {import.kind}");
|
|||
|
|
await backendRepository.AddMutiByCustomTable(deviceImports, "rescue_device");
|
|||
|
|
apiResult.Code = "0000";
|
|||
|
|
apiResult.Msg = "匯入成功";
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch(Exception ex)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9999";
|
|||
|
|
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】" + ex.Message);
|
|||
|
|
}
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
[HttpPost]
|
|||
|
|
public async Task<ApiResult<string>> DeletedIt(string guid)
|
|||
|
|
{
|
|||
|
|
ApiResult<string> apiResult = new ApiResult<string>();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
await backendRepository.PurgeOneAsync(guid, "rescue_device", "rescue_device_guid");
|
|||
|
|
apiResult.Code = "0000";
|
|||
|
|
apiResult.Msg = "刪除成功";
|
|||
|
|
}
|
|||
|
|
catch(Exception exception)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9999";
|
|||
|
|
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】");
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
|||
|
|
}
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
[HttpPost]
|
|||
|
|
public async Task<ApiResult<RescueDevice>> GetLocation (string guid)
|
|||
|
|
{
|
|||
|
|
ApiResult<RescueDevice> apiResult = new ApiResult<RescueDevice>();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
var location = await backendRepository.GetOneAsync<RescueDevice>("rescue_device", $"rescue_device_guid = '{guid}'");
|
|||
|
|
|
|||
|
|
if(location == null)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9998";
|
|||
|
|
apiResult.Msg = "資料不存在，請重新操作";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "0000";
|
|||
|
|
apiResult.Data = location;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch(Exception ex)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9999";
|
|||
|
|
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】");
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】" + ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
[HttpPost]
|
|||
|
|
public async Task<ApiResult<string>> SaveFireExtinguisher(SaveFireExtinguisher save)
|
|||
|
|
{
|
|||
|
|
ApiResult<string> apiResult = new ApiResult<string>();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
var a = new Dictionary<string, object>()
|
|||
|
|
{
|
|||
|
|
{"@location",save.location}
|
|||
|
|
};
|
|||
|
|
await backendRepository.UpdateOneByCustomTable(a, "rescue_device", $"rescue_device_guid = '{save.guid}'");
|
|||
|
|
apiResult.Code = "0000";
|
|||
|
|
apiResult.Msg = "儲存成功";
|
|||
|
|
}
|
|||
|
|
catch (Exception exception)
|
|||
|
|
{
|
|||
|
|
apiResult.Code = "9999";
|
|||
|
|
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】");
|
|||
|
|
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
return apiResult;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public FileResult ExportExcel(string post)
|
|||
|
|
{
|
|||
|
|
var postObject = JsonConvert.DeserializeObject<Excel>(post);
|
|||
|
|
var workbook = new XSSFWorkbook();
|
|||
|
|
#region excel設定
|
|||
|
|
IFont font12 = workbook.CreateFont();
|
|||
|
|
font12.FontName = "新細明體";
|
|||
|
|
font12.FontHeightInPoints = 12;
|
|||
|
|
ICellStyle style12 = workbook.CreateCellStyle();
|
|||
|
|
style12.SetFont(font12);
|
|||
|
|
style12.Alignment = HorizontalAlignment.Center;
|
|||
|
|
style12.VerticalAlignment = VerticalAlignment.Center;
|
|||
|
|
IFont font12Times = workbook.CreateFont();
|
|||
|
|
font12Times.FontName = "Times New Roman";
|
|||
|
|
font12Times.FontHeightInPoints = 12;
|
|||
|
|
IFont font18 = workbook.CreateFont();
|
|||
|
|
font18.FontName = "新細明體";
|
|||
|
|
font18.FontHeightInPoints = 18;
|
|||
|
|
font18.IsBold = true;
|
|||
|
|
ICellStyle styleTitle18 = workbook.CreateCellStyle();
|
|||
|
|
styleTitle18.SetFont(font18);
|
|||
|
|
styleTitle18.Alignment = HorizontalAlignment.Center;
|
|||
|
|
styleTitle18.VerticalAlignment = VerticalAlignment.Center;
|
|||
|
|
ICellStyle styleLeft12 = workbook.CreateCellStyle();
|
|||
|
|
styleLeft12.SetFont(font12);
|
|||
|
|
styleLeft12.Alignment = HorizontalAlignment.Left;
|
|||
|
|
styleLeft12.VerticalAlignment = VerticalAlignment.Center;
|
|||
|
|
ICellStyle styleLine12 = workbook.CreateCellStyle();
|
|||
|
|
styleLine12.SetFont(font12);
|
|||
|
|
styleLine12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
|||
|
|
styleLine12.VerticalAlignment = VerticalAlignment.Center;
|
|||
|
|
styleLine12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
|||
|
|
styleLine12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
|||
|
|
styleLine12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
|||
|
|
styleLine12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
|||
|
|
ICellStyle stylein12 = workbook.CreateCellStyle();
|
|||
|
|
stylein12.SetFont(font12Times);
|
|||
|
|
stylein12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
|
|||
|
|
stylein12.VerticalAlignment = VerticalAlignment.Center;
|
|||
|
|
stylein12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
|||
|
|
stylein12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
|||
|
|
stylein12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
|||
|
|
stylein12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
|||
|
|
stylein12.WrapText = true;
|
|||
|
|
#endregion
|
|||
|
|
var devicename = "";
|
|||
|
|
if (postObject.kind == 0)
|
|||
|
|
{
|
|||
|
|
devicename = "滅火器";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
devicename = "AED";
|
|||
|
|
}
|
|||
|
|
var sheet = workbook.CreateSheet(devicename);
|
|||
|
|
var RescueDevices = backendRepository.GetAllAsync<RescueDevice>("rescue_device", $"building_guid = '{postObject.build}' and rescue_device_kind = {postObject.kind}",null, "floor_name");
|
|||
|
|
int RowPosition = 0;
|
|||
|
|
IRow row = sheet.CreateRow(RowPosition);
|
|||
|
|
ICell cell = row.CreateCell(1);
|
|||
|
|
sheet.SetColumnWidth(0, 4 * 160 * 12);
|
|||
|
|
sheet.SetColumnWidth(1, 4 * 160 * 12);
|
|||
|
|
sheet.SetColumnWidth(2, 4 * 160 * 12);
|
|||
|
|
cell.SetCellValue("設備編號");
|
|||
|
|
cell.CellStyle = styleLine12;
|
|||
|
|
cell = row.CreateCell(0);
|
|||
|
|
cell.SetCellValue("樓層");
|
|||
|
|
cell.CellStyle = styleLine12;
|
|||
|
|
cell = row.CreateCell(2);
|
|||
|
|
cell.SetCellValue("位置");
|
|||
|
|
cell.CellStyle = styleLine12;
|
|||
|
|
|
|||
|
|
foreach(var device in RescueDevices.Result)
|
|||
|
|
{
|
|||
|
|
RowPosition += 1;
|
|||
|
|
row = sheet.CreateRow(RowPosition);
|
|||
|
|
for(var a = 0 ; a < 3 ; a++)
|
|||
|
|
{
|
|||
|
|
cell = row.CreateCell(a);
|
|||
|
|
if (a == 1)
|
|||
|
|
{
|
|||
|
|
cell.SetCellValue(device.rescue_device_id);
|
|||
|
|
}
|
|||
|
|
if (a == 0)
|
|||
|
|
{
|
|||
|
|
cell.SetCellValue(device.floor_name);
|
|||
|
|
}
|
|||
|
|
if (a == 2)
|
|||
|
|
{
|
|||
|
|
cell.SetCellValue(device.location);
|
|||
|
|
}
|
|||
|
|
cell.CellStyle = style12;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
var ms = new NpoiMemoryStream
|
|||
|
|
{
|
|||
|
|
AllowClose = false
|
|||
|
|
};
|
|||
|
|
workbook.Write(ms);
|
|||
|
|
ms.Flush();
|
|||
|
|
ms.Seek(0, SeekOrigin.Begin);
|
|||
|
|
|
|||
|
|
|
|||
|
|
return File(ms, "application/vnd.ms-excel", postObject.buildname + "-" + devicename + ".xlsx");
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|