You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
445 lines
23 KiB
445 lines
23 KiB
using Microsoft.AspNetCore.Mvc;
|
|
using OfficeOpenXml;
|
|
//using OfficeOpenXml;
|
|
using System;
|
|
using System.Data;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using WcfControlMonitorLib;
|
|
|
|
|
|
namespace WcfControlMonitorWebLib.Controllers
|
|
{
|
|
[ApiController, Route("api/historyerrorquery")]
|
|
public class HistoryErrorQueryController : ControllerBase
|
|
{
|
|
DataView dv = new DataView();
|
|
|
|
/// <summary>
|
|
/// 获取数据
|
|
/// </summary>
|
|
/// <param name="workArea">仓库编码</param>
|
|
/// <param name="deviceIndex">设备索引</param>
|
|
/// <param name="deviceErrorIndex">故障代码</param>
|
|
/// <param name="startDateTimeFrom">发生时间 From</param>
|
|
/// <param name="startDateTimeTo">发生时间 To</param>
|
|
/// <param name="endDateTimeFrom">结束时间 From</param>
|
|
/// <param name="endDateTimeTo">结束时间 To</param>
|
|
/// <param name="sort"></param>
|
|
/// <param name="offset"></param>
|
|
/// <param name="limit"></param>
|
|
/// <returns></returns>
|
|
[HttpGet]
|
|
public IActionResult Get(
|
|
[FromQuery] string workArea,
|
|
[FromQuery] string deviceIndex,
|
|
[FromQuery] string deviceErrorIndex,
|
|
[FromQuery] string startDateTimeFrom,
|
|
[FromQuery] string startDateTimeTo,
|
|
[FromQuery] string endDateTimeFrom,
|
|
[FromQuery] string endDateTimeTo,
|
|
[FromQuery] string sort,
|
|
[FromQuery] int? offset,
|
|
[FromQuery] int? limit)
|
|
{
|
|
|
|
if (offset == null)
|
|
{
|
|
offset = 0;
|
|
}
|
|
if (limit == null)
|
|
{
|
|
limit = 30;
|
|
}
|
|
#region 排序信息需要转一下
|
|
if (sort?.Length > 1)
|
|
{
|
|
var format = sort?.StartsWith("~") == true ? "{0} DESC" : "{0}";
|
|
if ((bool)sort?.StartsWith("~"))
|
|
{
|
|
//[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
|
|
//[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
|
|
//[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
|
|
switch (sort.Substring(2))
|
|
{
|
|
case "orkArea":
|
|
sort = string.Format(format, "仓库编码");
|
|
break;
|
|
case "escription":
|
|
sort = string.Format(format, "仓库名称");
|
|
break;
|
|
case "eviceIndex":
|
|
sort = string.Format(format, "设备索引");
|
|
break;
|
|
case "eviceName":
|
|
sort = string.Format(format, "设备名称");
|
|
break;
|
|
case "askNo":
|
|
sort = string.Format(format, "设备指令索引");
|
|
break;
|
|
case "rrorName":
|
|
sort = string.Format(format, "故障名称");
|
|
break;
|
|
case "rrorType":
|
|
sort = string.Format(format, "故障类型");
|
|
break;
|
|
case "rrorLevel":
|
|
sort = string.Format(format, "故障级别");
|
|
break;
|
|
case "tartDateTime":
|
|
sort = string.Format(format, "发生时间");
|
|
break;
|
|
case "ndDateTime":
|
|
sort = string.Format(format, "结束时间");
|
|
break;
|
|
default:
|
|
sort = "序号";
|
|
break;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
switch (sort.Substring(1))
|
|
{
|
|
case "orkArea":
|
|
sort = string.Format(format, "仓库编码");
|
|
break;
|
|
case "escription":
|
|
sort = string.Format(format, "仓库名称");
|
|
break;
|
|
case "eviceIndex":
|
|
sort = string.Format(format, "设备索引");
|
|
break;
|
|
case "eviceName":
|
|
sort = string.Format(format, "设备名称");
|
|
break;
|
|
case "askNo":
|
|
sort = string.Format(format, "设备指令索引");
|
|
break;
|
|
case "rrorName":
|
|
sort = string.Format(format, "故障名称");
|
|
break;
|
|
case "rrorType":
|
|
sort = string.Format(format, "故障类型");
|
|
break;
|
|
case "rrorLevel":
|
|
sort = string.Format(format, "故障级别");
|
|
break;
|
|
case "tartDateTime":
|
|
sort = string.Format(format, "发生时间");
|
|
break;
|
|
case "ndDateTime":
|
|
sort = string.Format(format, "结束时间");
|
|
break;
|
|
default:
|
|
sort = "序号";
|
|
break;
|
|
}
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
sort = "序号";
|
|
}
|
|
#endregion
|
|
#region 拼接 where 条件
|
|
//[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
|
|
//[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
|
|
//[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
|
|
var where = "1=1";
|
|
if (!string.IsNullOrEmpty(workArea))
|
|
{
|
|
where = $"{where} AND 仓库编码 = {workArea}";
|
|
}
|
|
if (!string.IsNullOrEmpty(deviceIndex))
|
|
{
|
|
where = $"{where} AND 设备索引 = {deviceIndex}";
|
|
}
|
|
if (!string.IsNullOrEmpty(deviceErrorIndex))
|
|
{
|
|
where = $"{where} AND 故障代码 = {deviceErrorIndex}";
|
|
}
|
|
if (!string.IsNullOrEmpty(startDateTimeFrom))
|
|
{
|
|
where = $"{where} AND 发生时间 >= '{startDateTimeFrom}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(startDateTimeTo))
|
|
{
|
|
where = $"{where} AND 发生时间 <= '{startDateTimeTo}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(endDateTimeFrom))
|
|
{
|
|
where = $"{where} AND 结束时间 >= '{endDateTimeFrom}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(endDateTimeTo))
|
|
{
|
|
where = $"{where} AND 结束时间 <= '{endDateTimeTo}'";
|
|
}
|
|
|
|
#endregion
|
|
var sql = $"SELECT top {limit} [序号] as ID, [仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime FROM (SELECT [序号], [仓库编码] ,[仓库名称],[设备索引],[设备名称],[设备指令索引],[故障代码],[故障名称],[故障类型],[故障级别],[发生时间],[结束时间],[ 故障时间(分钟)],ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM V_Device_Error_log WHERE {where}) AS T WHERE ROW_NUM > {offset}";
|
|
var sqlAll = $"SELECT [序号] as ID, [仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime FROM (SELECT [序号], [仓库编码] ,[仓库名称],[设备索引],[设备名称],[设备指令索引],[故障代码],[故障名称],[故障类型],[故障级别],[发生时间],[结束时间],[ 故障时间(分钟)],ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM V_Device_Error_log WHERE {where}) AS T WHERE ROW_NUM > 0";
|
|
|
|
DataView dv1 = CStaticClass.dbo.ExceSQL(sqlAll.ToString()).Tables[0].DefaultView;//满足的条件的所有数据 返回总记录条数用
|
|
|
|
dv = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
|
|
|
|
var items = new dynamic[dv.Count];
|
|
for (int i = 0; i < dv.Count; i++)
|
|
{
|
|
items[i] = new
|
|
{
|
|
//[序号] as ID,
|
|
//[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
|
|
//[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
|
|
//[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
|
|
id= Convert.ToInt32(dv[i]["ID"]),
|
|
workArea = Convert.ToInt32(dv[i]["WorkArea"]),
|
|
description = Convert.ToString(dv[i]["Description"]),
|
|
deviceIndex = Convert.ToInt32(dv[i]["DeviceIndex"]),
|
|
deviceName = Convert.ToString(dv[i]["DeviceName"]),
|
|
taskNo = Convert.ToString(dv[i]["TaskNo"]),
|
|
deviceErrorIndex = Convert.ToInt32(dv[i]["DeviceErrorIndex"]),
|
|
errorName = Convert.ToString(dv[i]["ErrorName"]),
|
|
errorType = Convert.ToString(dv[i]["ErrorType"]),
|
|
errorLevel = Convert.ToString(dv[i]["ErrorLevel"]),
|
|
startDateTime = Convert.ToString(dv[i]["StartDateTime"]),
|
|
endDateTime = Convert.ToString(dv[i]["EndDateTime"]),
|
|
errorTime = Convert.ToString(dv[i]["ErrorTime"]),
|
|
};
|
|
}
|
|
return Ok(new { total = dv1.Count, items });
|
|
}
|
|
|
|
/// <summary>
|
|
/// 导出excel
|
|
/// 暂时给关闭了 有些卡
|
|
/// 可以根据条件导出 也可以全部导出 没考虑好
|
|
/// </summary>
|
|
/// <param name="sort"></param>
|
|
/// <param name="offset"></param>
|
|
/// <param name="limit"></param>
|
|
/// <returns></returns>
|
|
[HttpGet("excel")]
|
|
[ProducesResponseType(200)]
|
|
public async Task<IActionResult> Export(
|
|
//[FromQuery] string workArea,
|
|
//[FromQuery] string deviceIndex,
|
|
//[FromQuery] string deviceErrorIndex,
|
|
//[FromQuery] string startDateTimeFrom,
|
|
//[FromQuery] string startDateTimeTo,
|
|
//[FromQuery] string endDateTimeFrom,
|
|
//[FromQuery] string endDateTimeTo,
|
|
[FromQuery] string sort,
|
|
[FromQuery] int? offset,
|
|
[FromQuery] int? limit)
|
|
{
|
|
if (offset == null)
|
|
{
|
|
offset = 0;
|
|
}
|
|
if (limit == null)
|
|
{
|
|
limit = 30;
|
|
}
|
|
#region 排序信息需要转一下
|
|
//if (sort?.Length > 1)
|
|
//{
|
|
// var format = sort?.StartsWith("~") == true ? "{0} DESC" : "{0}";
|
|
// if ((bool)sort?.StartsWith("~"))
|
|
// {
|
|
// //[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
|
|
// //[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
|
|
// //[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
|
|
// switch (sort.Substring(2))
|
|
// {
|
|
// case "orkArea":
|
|
// sort = string.Format(format, "仓库编码");
|
|
// break;
|
|
// case "escription":
|
|
// sort = string.Format(format, "仓库名称");
|
|
// break;
|
|
// case "eviceIndex":
|
|
// sort = string.Format(format, "设备索引");
|
|
// break;
|
|
// case "eviceName":
|
|
// sort = string.Format(format, "设备名称");
|
|
// break;
|
|
// case "askNo":
|
|
// sort = string.Format(format, "设备指令索引");
|
|
// break;
|
|
// case "rrorName":
|
|
// sort = string.Format(format, "故障名称");
|
|
// break;
|
|
// case "rrorType":
|
|
// sort = string.Format(format, "故障类型");
|
|
// break;
|
|
// case "rrorLevel":
|
|
// sort = string.Format(format, "故障级别");
|
|
// break;
|
|
// case "tartDateTime":
|
|
// sort = string.Format(format, "发生时间");
|
|
// break;
|
|
// case "ndDateTime":
|
|
// sort = string.Format(format, "结束时间");
|
|
// break;
|
|
// default:
|
|
// sort = "序号";
|
|
// break;
|
|
// }
|
|
// }
|
|
// else
|
|
// {
|
|
// switch (sort.Substring(1))
|
|
// {
|
|
// case "orkArea":
|
|
// sort = string.Format(format, "仓库编码");
|
|
// break;
|
|
// case "escription":
|
|
// sort = string.Format(format, "仓库名称");
|
|
// break;
|
|
// case "eviceIndex":
|
|
// sort = string.Format(format, "设备索引");
|
|
// break;
|
|
// case "eviceName":
|
|
// sort = string.Format(format, "设备名称");
|
|
// break;
|
|
// case "askNo":
|
|
// sort = string.Format(format, "设备指令索引");
|
|
// break;
|
|
// case "rrorName":
|
|
// sort = string.Format(format, "故障名称");
|
|
// break;
|
|
// case "rrorType":
|
|
// sort = string.Format(format, "故障类型");
|
|
// break;
|
|
// case "rrorLevel":
|
|
// sort = string.Format(format, "故障级别");
|
|
// break;
|
|
// case "tartDateTime":
|
|
// sort = string.Format(format, "发生时间");
|
|
// break;
|
|
// case "ndDateTime":
|
|
// sort = string.Format(format, "结束时间");
|
|
// break;
|
|
// default:
|
|
// sort = "序号";
|
|
// break;
|
|
// }
|
|
// }
|
|
|
|
//}
|
|
//else
|
|
//{
|
|
// sort = "序号";
|
|
//}
|
|
#endregion
|
|
var where = "1=1";
|
|
//if (!string.IsNullOrEmpty(workArea))
|
|
//{
|
|
// where = $"{where} AND 仓库编码 = {workArea}";
|
|
//}
|
|
//if (!string.IsNullOrEmpty(deviceIndex))
|
|
//{
|
|
// where = $"{where} AND 设备索引 = {deviceIndex}";
|
|
//}
|
|
//if (!string.IsNullOrEmpty(deviceErrorIndex))
|
|
//{
|
|
// where = $"{where} AND 故障代码 = {deviceErrorIndex}";
|
|
//}
|
|
//if (!string.IsNullOrEmpty(startDateTimeFrom))
|
|
//{
|
|
// where = $"{where} AND 发生时间 >= '{startDateTimeFrom}'";
|
|
//}
|
|
//if (!string.IsNullOrEmpty(startDateTimeTo))
|
|
//{
|
|
// where = $"{where} AND 发生时间 <= '{startDateTimeTo}'";
|
|
//}
|
|
//if (!string.IsNullOrEmpty(endDateTimeFrom))
|
|
//{
|
|
// where = $"{where} AND 结束时间 >= '{endDateTimeFrom}'";
|
|
//}
|
|
//if (!string.IsNullOrEmpty(endDateTimeTo))
|
|
//{
|
|
// where = $"{where} AND 结束时间 <= '{endDateTimeTo}'";
|
|
//}
|
|
//var sql = $"SELECT top {limit} [序号] as ID, [仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime FROM (SELECT [序号], [仓库编码] ,[仓库名称],[设备索引],[设备名称],[设备指令索引],[故障代码],[故障名称],[故障类型],[故障级别],[发生时间],[结束时间],[ 故障时间(分钟)],ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM V_Device_Error_log WHERE {where}) AS T WHERE ROW_NUM > {offset}";
|
|
var sql = $"SELECT [序号] as ID, [仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime FROM (SELECT [序号], [仓库编码] ,[仓库名称],[设备索引],[设备名称],[设备指令索引],[故障代码],[故障名称],[故障类型],[故障级别],[发生时间],[结束时间],[ 故障时间(分钟)],ROW_NUMBER() OVER (ORDER BY 序号) AS ROW_NUM FROM V_Device_Error_log WHERE 1=1) AS T WHERE ROW_NUM > 0";
|
|
dv = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
|
|
|
|
using var package = new ExcelPackage();
|
|
var worksheet = package.Workbook.Worksheets.Add("Sheet1");
|
|
var column = 0;
|
|
worksheet.Cells[1, ++column].Value = "序号";
|
|
worksheet.Cells[1, ++column].Value = "仓库编码";
|
|
worksheet.Cells[1, ++column].Value = "仓库名称";
|
|
worksheet.Cells[1, ++column].Value = "设备索引";
|
|
worksheet.Cells[1, ++column].Value = "设备名称";
|
|
worksheet.Cells[1, ++column].Value = "设备指令索引";
|
|
worksheet.Cells[1, ++column].Value = "故障代码";
|
|
worksheet.Cells[1, ++column].Value = "故障名称";
|
|
//worksheet.Cells[1, ++column].Value = "故障类型";
|
|
//worksheet.Cells[1, ++column].Value = "故障级别";
|
|
worksheet.Cells[1, ++column].Value = "发生时间";
|
|
worksheet.Cells[1, ++column].Value = "结束时间";
|
|
worksheet.Cells[1, ++column].Value = "故障时间(分钟)";
|
|
|
|
for (int i = 0; i < dv.Count; i++)
|
|
{
|
|
column = 0;
|
|
//[序号] as ID,
|
|
//[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
|
|
//[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
|
|
//[故障级别] AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToInt32(dv[i]["ID"]);
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToInt32(dv[i]["WorkArea"]);
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["Description"]);
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToInt32(dv[i]["DeviceIndex"]);
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["DeviceName"]);
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["TaskNo"]);
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToInt32(dv[i]["DeviceErrorIndex"]);
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["ErrorName"]);
|
|
//worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["ErrorType"]);
|
|
//worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["ErrorLevel"]);
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["StartDateTime"]);
|
|
worksheet.Cells[i + 2, column].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["EndDateTime"]);
|
|
worksheet.Cells[i + 2, column].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
|
|
worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["ErrorTime"]);
|
|
|
|
}
|
|
return File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
|
|
}
|
|
|
|
/// <summary>
|
|
/// 批量处理
|
|
/// </summary>
|
|
/// <param name="data">ID 数组</param>
|
|
/// <returns></returns>
|
|
[HttpPost("batch")]
|
|
public IActionResult Batch(
|
|
[FromMember] string[] data)
|
|
{
|
|
int count = 0;
|
|
// 写入数据库
|
|
foreach (var item in data)
|
|
{
|
|
var sql = $"delete from T_Base_Device_Error_Log where id={item}";
|
|
if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
|
|
{
|
|
count++;
|
|
}
|
|
}
|
|
if (count > 0)
|
|
{
|
|
return StatusCode(200);
|
|
}
|
|
else
|
|
{
|
|
return StatusCode(422);
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|