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.
403 lines
18 KiB
403 lines
18 KiB
using Microsoft.AspNetCore.Mvc;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using WcfControlMonitorLib;
|
|
|
|
namespace WcfControlMonitorWebLib.Controllers
|
|
{
|
|
[ApiController, Route("api/historytaskquery")]
|
|
public class HistoryTaskQueryController: ControllerBase
|
|
{
|
|
DataView dv = new DataView();
|
|
|
|
/// <summary>
|
|
/// 获取数据
|
|
/// </summary>
|
|
/// <param name="id">调度任务索引</param>
|
|
/// <param name="taskKind">任务类型</param>
|
|
/// <param name="controlTaskKind">作业类型</param>
|
|
/// <param name="barcode">托盘条码</param>
|
|
/// <param name="startDevice">起点</param>
|
|
/// <param name="startCell">起点货位</param>
|
|
/// <param name="endDevice">终点</param>
|
|
/// <param name="endCell">终点货位</param>
|
|
/// <param name="startTimeFrom">开始时间 From</param>
|
|
/// <param name="startTimeTo">开始时间 To</param>
|
|
/// <param name="endTimeFrom">结束时间 From</param>
|
|
/// <param name="endTimeTo">结束时间 To</param>
|
|
/// <param name="sort"></param>
|
|
/// <param name="offset"></param>
|
|
/// <param name="limit"></param>
|
|
/// <returns></returns>
|
|
[HttpGet]
|
|
public IActionResult Get(
|
|
[FromQuery] string id,
|
|
[FromQuery] string taskKind,
|
|
[FromQuery] string controlTaskKind,
|
|
[FromQuery] string barcode,
|
|
[FromQuery] string startDevice,
|
|
[FromQuery] string startCell,
|
|
[FromQuery] string endDevice,
|
|
[FromQuery] string endCell,
|
|
[FromQuery] string startTimeFrom,
|
|
[FromQuery] string startTimeTo,
|
|
[FromQuery] string endTimeFrom,
|
|
[FromQuery] string endTimeTo,
|
|
[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("~"))
|
|
{
|
|
switch (sort.Substring(2))
|
|
{
|
|
//托盘条码 as barcode,调度任务索引 as taskNo,关联任务索引 as relativeControlid,任务类型 as taskType,
|
|
//作业类型 as controlTaskKind,优先级 as level,起点 as startDevice,起点货位 as startCell,终点 as endDevice,
|
|
//终点货位 as endCell,任务状态 as taskStatus,[当前执行设备] as currentLocation,[开始时间] as startTime ,
|
|
//结束时间 as endTime,[故障信息] as errorMessage,异常码 as errorNo,仓库编码 as workArea,逻辑区域 as logicArea
|
|
case "arcode":
|
|
sort = string.Format(format, "托盘条码");
|
|
break;
|
|
case "d":
|
|
sort = string.Format(format, "调度任务索引");
|
|
break;
|
|
case "elativeControlid":
|
|
sort = string.Format(format, "关联任务索引");
|
|
break;
|
|
case "askType":
|
|
sort = string.Format(format, "任务类型");
|
|
break;
|
|
case "ontrolTaskKind":
|
|
sort = string.Format(format, "作业类型");
|
|
break;
|
|
case "evel":
|
|
sort = string.Format(format, "优先级");
|
|
break;
|
|
case "tartDevice":
|
|
sort = string.Format(format, "起点");
|
|
break;
|
|
case "tartCell":
|
|
sort = string.Format(format, "起点货位");
|
|
break;
|
|
case "ndDevice":
|
|
sort = string.Format(format, "终点");
|
|
break;
|
|
case "ndCell":
|
|
sort = string.Format(format, "终点货位");
|
|
break;
|
|
case "askStatus":
|
|
sort = string.Format(format, "任务状态");
|
|
break;
|
|
case "urrentLocation":
|
|
sort = string.Format(format, "当前执行设备");
|
|
break;
|
|
case "tartTime":
|
|
sort = string.Format(format, "开始时间");
|
|
break;
|
|
case "ndTime":
|
|
sort = string.Format(format, "结束时间");
|
|
break;
|
|
case "rrorMessage":
|
|
sort = string.Format(format, "故障信息");
|
|
break;
|
|
case "rrorNo":
|
|
sort = string.Format(format, "异常码");
|
|
break;
|
|
case "orkArea":
|
|
sort = string.Format(format, "仓库编码");
|
|
break;
|
|
case "ogicArea":
|
|
sort = string.Format(format, "逻辑区域");
|
|
break;
|
|
default:
|
|
sort = "调度任务索引";
|
|
break;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
switch (sort.Substring(1))
|
|
{
|
|
case "arcode":
|
|
sort = string.Format(format, "托盘条码");
|
|
break;
|
|
case "d":
|
|
sort = string.Format(format, "调度任务索引");
|
|
break;
|
|
case "elativeControlid":
|
|
sort = string.Format(format, "关联任务索引");
|
|
break;
|
|
case "askType":
|
|
sort = string.Format(format, "任务类型");
|
|
break;
|
|
case "ontrolTaskKind":
|
|
sort = string.Format(format, "作业类型");
|
|
break;
|
|
case "evel":
|
|
sort = string.Format(format, "优先级");
|
|
break;
|
|
case "tartDevice":
|
|
sort = string.Format(format, "起点");
|
|
break;
|
|
case "tartCell":
|
|
sort = string.Format(format, "起点货位");
|
|
break;
|
|
case "ndDevice":
|
|
sort = string.Format(format, "终点");
|
|
break;
|
|
case "ndCell":
|
|
sort = string.Format(format, "终点货位");
|
|
break;
|
|
case "askStatus":
|
|
sort = string.Format(format, "任务状态");
|
|
break;
|
|
case "urrentLocation":
|
|
sort = string.Format(format, "当前执行设备");
|
|
break;
|
|
case "tartTime":
|
|
sort = string.Format(format, "开始时间");
|
|
break;
|
|
case "ndTime":
|
|
sort = string.Format(format, "结束时间");
|
|
break;
|
|
case "rrorMessage":
|
|
sort = string.Format(format, "故障信息");
|
|
break;
|
|
case "rrorNo":
|
|
sort = string.Format(format, "异常码");
|
|
break;
|
|
case "orkArea":
|
|
sort = string.Format(format, "仓库编码");
|
|
break;
|
|
case "ogicArea":
|
|
sort = string.Format(format, "逻辑区域");
|
|
break;
|
|
default:
|
|
sort = "调度任务索引";
|
|
break;
|
|
}
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
sort = "调度任务索引";
|
|
}
|
|
#endregion
|
|
#region 拼接 where 条件
|
|
|
|
var where = "1=1";
|
|
if (!string.IsNullOrEmpty(barcode))
|
|
{
|
|
where = $"{where} AND 托盘条码 = '{barcode}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
where = $"{where} AND 调度任务索引 = {id}";
|
|
}
|
|
if (!string.IsNullOrEmpty(taskKind))
|
|
{
|
|
switch (taskKind)
|
|
{
|
|
case "1":
|
|
taskKind = "调度任务";
|
|
break;
|
|
case "2":
|
|
taskKind = "自动任务";
|
|
break;
|
|
case "3":
|
|
taskKind = "临时任务";
|
|
break;
|
|
case "4":
|
|
taskKind = "手工任务";
|
|
break;
|
|
}
|
|
|
|
where = $"{where} AND 任务类型 = '{taskKind}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(controlTaskKind))
|
|
{
|
|
switch (controlTaskKind)
|
|
{
|
|
case "0":
|
|
controlTaskKind = "手工临时搬运任务";
|
|
break;
|
|
case "1":
|
|
controlTaskKind = "入库";
|
|
break;
|
|
case "2":
|
|
controlTaskKind = "出库";
|
|
break;
|
|
case "3":
|
|
controlTaskKind = "移库";
|
|
break;
|
|
case "4":
|
|
controlTaskKind = "站台间移库";
|
|
break;
|
|
}
|
|
where = $"{where} AND 作业类型 = '{controlTaskKind}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(startDevice))
|
|
{
|
|
where = $"{where} AND 起点 = '{startDevice}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(startCell))
|
|
{
|
|
where = $"{where} AND 起点货位 = '{startCell}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(endDevice))
|
|
{
|
|
where = $"{where} AND 终点 = '{endDevice}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(endCell))
|
|
{
|
|
where = $"{where} AND 终点货位 = '{endCell}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(startTimeFrom))
|
|
{
|
|
where = $"{where} AND 开始时间 >= '{startTimeFrom}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(startTimeTo))
|
|
{
|
|
where = $"{where} AND 开始时间 <= '{startTimeTo}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(endTimeFrom))
|
|
{
|
|
where = $"{where} AND 结束时间 >= '{endTimeFrom}'";
|
|
}
|
|
if (!string.IsNullOrEmpty(endTimeTo))
|
|
{
|
|
where = $"{where} AND 结束时间 <= '{endTimeTo}'";
|
|
}
|
|
|
|
#endregion
|
|
var sql = $"SELECT top {limit} 托盘条码 as barcode,调度任务索引 as taskNo,关联任务索引 as relativeControlid,任务类型 as taskType,作业类型 as controlTaskKind,优先级 as level,起点 as startDevice,起点货位 as startCell,终点 as endDevice,终点货位 as endCell,任务状态 as taskStatus,当前执行设备 as currentLocation,开始时间 as startTime ,结束时间 as endTime,故障信息 as errorMessage,异常码 as errorNo,仓库编码 as workArea,逻辑区域 as logicArea FROM (SELECT 调度任务索引,关联任务索引,任务类型,托盘条码,作业类型,优先级,起点,起点货位,终点,终点货位,任务状态,故障信息,开始时间,结束时间,当前执行设备,异常码,逻辑区域,仓库编码,备注 ,ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM V_manage_task_bak WHERE {where}) AS T WHERE ROW_NUM > {offset}";
|
|
var sqlAll = $"SELECT 托盘条码 as barcode,调度任务索引 as taskNo,关联任务索引 as relativeControlid,任务类型 as taskType,作业类型 as controlTaskKind,优先级 as level,起点 as startDevice,起点货位 as startCell,终点 as endDevice,终点货位 as endCell,任务状态 as taskStatus,当前执行设备 as currentLocation,开始时间 as startTime ,结束时间 as endTime,故障信息 as errorMessage,异常码 as errorNo,仓库编码 as workArea,逻辑区域 as logicArea FROM (SELECT 调度任务索引,关联任务索引,任务类型,托盘条码,作业类型,优先级,起点,起点货位,终点,终点货位,任务状态,故障信息,开始时间,结束时间,当前执行设备,异常码,逻辑区域,仓库编码,备注 ,ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM V_manage_task_bak 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 barcode,调度任务索引 as taskNo,关联任务索引 as relativeControlid,任务类型 as taskType,
|
|
id = Convert.ToString(dv[i]["taskNo"]),
|
|
barcode = Convert.ToString(dv[i]["barcode"]),
|
|
relativeControlid = Convert.ToString(dv[i]["relativeControlid"]),
|
|
taskKind = Convert.ToString(dv[i]["taskType"]),
|
|
//作业类型 as controlTaskKind,优先级 as level,起点 as startDevice,起点货位 as startCell,终点 as endDevice,
|
|
controlTaskKind = Convert.ToString(dv[i]["controlTaskKind"]),
|
|
level = Convert.ToString(dv[i]["level"]),
|
|
startDevice = Convert.ToString(dv[i]["startDevice"]),
|
|
startCell = Convert.ToString(dv[i]["startCell"]),
|
|
endDevice = Convert.ToString(dv[i]["endDevice"]),
|
|
//终点货位 as endCell,任务状态 as taskStatus,[当前执行设备] as currentLocation,[开始时间] as startTime ,
|
|
endCell = Convert.ToString(dv[i]["endCell"]),
|
|
taskStatus = Convert.ToString(dv[i]["taskStatus"]),
|
|
currentLocation = Convert.ToString(dv[i]["currentLocation"]),
|
|
startTime = Convert.ToString(dv[i]["startTime"]),
|
|
//结束时间 as endTime,[故障信息] as errorMessage,异常码 as errorNo,仓库编码 as workArea,逻辑区域 as logicArea
|
|
endTime = Convert.ToString(dv[i]["endTime"]),
|
|
errorMessage = Convert.ToString(dv[i]["errorMessage"]),
|
|
errorNo = Convert.ToString(dv[i]["errorNo"]),
|
|
workArea = Convert.ToString(dv[i]["workArea"]),
|
|
logicArea = Convert.ToString(dv[i]["logicArea"])
|
|
};
|
|
}
|
|
return Ok(new { total = dv1.Count, items });
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取任务类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
[HttpGet("taskkind")]
|
|
public IActionResult Get()
|
|
{
|
|
var sql = $"SELECT F_TaskKindIndex,F_TaskKindName from T_Base_manage_task_kind order by F_TaskKindIndex";
|
|
|
|
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
|
|
{
|
|
id = Convert.ToInt32(dv[i]["F_TaskKindIndex"]),
|
|
name = Convert.ToString(dv[i]["F_TaskKindName"]),
|
|
};
|
|
}
|
|
return Ok(new { total = dv.Count, items });
|
|
}
|
|
/// <summary>
|
|
/// 获取作业类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
[HttpGet("controlTaskKind")]
|
|
public IActionResult GetControlTaskKind()
|
|
{
|
|
var sql = $"SELECT FCODE ,FNAME FROM T_ITEMTASKTYPE";
|
|
|
|
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
|
|
{
|
|
id = Convert.ToInt32(dv[i]["FCODE"]),
|
|
name = Convert.ToString(dv[i]["FNAME"]),
|
|
};
|
|
}
|
|
return Ok(new { total = dv.Count, items });
|
|
}
|
|
|
|
/// <summary>
|
|
/// 批量处理 删除
|
|
/// </summary>
|
|
/// <param name="data">fid</param>
|
|
/// <returns></returns>
|
|
[HttpPost("batch")]
|
|
public IActionResult Batch(
|
|
[FromMember] string[] data)
|
|
{
|
|
int count = 0;
|
|
// 写入数据库
|
|
foreach (var item in data)
|
|
{
|
|
var sql = $"delete from T_Manage_Task_BAK where fid={item}";
|
|
if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
|
|
{
|
|
count++;
|
|
}
|
|
}
|
|
if (count > 0)
|
|
{
|
|
return StatusCode(200);
|
|
}
|
|
else
|
|
{
|
|
return StatusCode(422);
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|