using Newtonsoft.Json.Linq;
|
using Pub_Class;
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Data.SqlClient;
|
using System.Web.Http;
|
using WebAPI.Models;
|
using Newtonsoft.Json;
|
|
namespace WebAPI.Controllers.生产管理
|
{
|
//设备状态分布Controller
|
public class Sc_HEquipStateDistributionController : ApiController
|
{
|
public DBUtility.ClsPub.Enum_BillStatus BillStatus;
|
|
private json objJsonResult = new json();
|
SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
|
DataSet ds;
|
//获取系统参数
|
Pub_Class.ClsXt_SystemParameter oSystemParameter = new Pub_Class.ClsXt_SystemParameter();
|
|
|
|
#region 设备分布 设备绑定的生产资源上正在生产的工单
|
|
[Route("Sc_HEquipStateDistribution/Sc_HEquipStateDistribution_Souce")]
|
[HttpGet]
|
public object Sc_HEquipStateDistribution_Souce(string HDeptID, string user, int OrganizationID)
|
{
|
try
|
{
|
string sql1 = string.Format("exec h_p_sc_HDeptSouceMOList {0},{1}", HDeptID, OrganizationID);
|
|
ds = oCN.RunProcReturn(sql1, "h_p_sc_HDeptSouceMOList");
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 设备分布 设备绑定的生产资源上正在生产的工单 合并 稼动率和OEE
|
|
[Route("Sc_HEquipStateDistribution/Sc_HEquipStateDistribution_Souce_MergeOEEReport")]
|
[HttpGet]
|
public object Sc_HEquipStateDistribution_Souce_MergeOEEReport(string HDeptID, string user, int OrganizationID)
|
{
|
try
|
{
|
string sql1 = string.Format("exec h_p_sc_HDeptSouceMOList_MergeOEEReport {0},{1}", HDeptID, OrganizationID);
|
|
ds = oCN.RunProcReturn(sql1, "h_p_sc_HDeptSouceMOList_MergeOEEReport");
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
|
#region 设备状态分步下推设备点检参数表
|
[Route("Sc_HEquipStateDistribution/Get_EquipICMOTechParamList_Json")]
|
[HttpGet]
|
public object Get_EquipICMOTechParamList_Json(string sWhere, string user)
|
{
|
DataSet ds;
|
try
|
{
|
List<object> columnNameList = new List<object>();
|
|
Dictionary<object, object> dic = Newtonsoft.Json.JsonConvert.DeserializeObject<Dictionary<object, object>>(sWhere);
|
|
string HEquipID = dic["HEquipID"].ToString();
|
string HICMOInterID = dic["HICMOInterID"].ToString();
|
string HICMOEntryID = dic["HICMOEntryID"].ToString();
|
|
ds = oCN.RunProcReturn("exec h_p_Sc_HEquipStateDistribution_TechParam '" + HEquipID + "','" + HICMOInterID + "','"+ HICMOEntryID + "'", "h_p_Sc_HEquipStateDistribution_TechParam");
|
|
//添加列名
|
foreach (DataColumn col in ds.Tables[0].Columns)
|
{
|
Type dataType = col.DataType;
|
string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
|
columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
|
}
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds.Tables[0];
|
objJsonResult.list = columnNameList;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "没有返回任何记录！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 设备状态分步 产线+部门 查询 下方四个图表数据
|
[Route("Sc_HEquipStateDistribution/Get_EquipStateDistribution_FourReport")]
|
[HttpGet]
|
public object Get_EquipStateDistribution_FourReport(string sWhere, string user)
|
{
|
DataSet ds;
|
try
|
{
|
List<object> columnNameList = new List<object>();
|
|
Dictionary<object, object> dic = Newtonsoft.Json.JsonConvert.DeserializeObject<Dictionary<object, object>>(sWhere);
|
|
string HDeptID = dic["HDeptID"].ToString();
|
string HSourceID = dic["HSourceID"].ToString();
|
string HEquipID = dic["HEquipID"].ToString();
|
|
|
if (string.IsNullOrWhiteSpace(HEquipID))
|
{
|
ds = oCN.RunProcReturn("exec h_p_Sc_FourStateDistributionReport '" + HDeptID + "','" + HSourceID + "'", "h_p_Sc_FourStateDistributionReport");
|
}else
|
{
|
ds = oCN.RunProcReturn("exec h_p_Sc_FourStateDistributionReport '" + HDeptID + "','" + HSourceID + "'" + $",'{HEquipID}'", "h_p_Sc_FourStateDistributionReport");
|
}
|
|
//添加列名
|
foreach (DataColumn col in ds.Tables[0].Columns)
|
{
|
Type dataType = col.DataType;
|
string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
|
columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
|
}
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds;
|
objJsonResult.list = columnNameList;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "没有返回任何记录！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 设备运行切片，获取部门-设备 树状图
|
private class HEquipTreeList_DAQ_Node
|
{
|
public int id { get; set; }
|
public string title { get; set; }
|
public List<HEquipTreeList_DAQ_Node> children { get; set; }
|
}
|
|
|
[Route("DAQ_EquipRunningSlice/GetHEquipTreeList_DAQ")]
|
[HttpGet]
|
public object Sc_HEquipStateDistribution_Souce(string sWhere)
|
{
|
try
|
{
|
List<HEquipTreeList_DAQ_Node> treeArr = new List<HEquipTreeList_DAQ_Node>();
|
// 记录部门是否已经加入treeArr(不可重复加入)
|
HashSet<String> HDeptSet = new HashSet<String>();
|
|
ds = oCN.RunProcReturn($@"select hmainid HEquipID, 设备名称 HEquipName, HDeptID HDeptID, 使用部门 HDeptName from h_v_Gy_EquipFileMainList
|
{sWhere}
|
order by HEquipName asc", "h_v_Gy_EquipFileMainList");
|
|
foreach(DataRow row in ds.Tables[0].Rows)
|
{
|
int HDeptID = int.Parse(row["HDeptID"].ToString());
|
string HDeptName = row["HDeptName"] as string;
|
int HEquipID = int.Parse(row["HEquipID"].ToString());
|
string HEquipName = row["HEquipName"] as string;
|
|
if (!HDeptSet.Contains(row["HDeptID"].ToString()))
|
{
|
// 没有部门对应的一级节点，则添加一级节点
|
treeArr.Add(new HEquipTreeList_DAQ_Node
|
{
|
id = int.Parse(row["HDeptID"].ToString()),
|
title = row["HDeptName"].ToString(),
|
children = new List<HEquipTreeList_DAQ_Node>()
|
});
|
|
// 部门ID添加到Set，确保唯一性
|
HDeptSet.Add(row["HDeptID"].ToString());
|
}
|
|
// 部门对印的一级节点已经设置好，设置二级节点
|
HEquipTreeList_DAQ_Node Node = treeArr.First(item => item.id == HDeptID);
|
Node.children.Add(new HEquipTreeList_DAQ_Node
|
{
|
id = HEquipID,
|
title = HEquipName,
|
children = new List<HEquipTreeList_DAQ_Node>()
|
});
|
}
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "查询成功";
|
objJsonResult.data = JArray.FromObject(treeArr);
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.Message.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 设备运行切片 返回设备状态 图表数据 (饼图)
|
[Route("DAQ_EquipRunningSlice/GetHEquipStatusReportList_DAQ")]
|
[HttpGet]
|
public object GetHEquipStatusReportList_DAQ(string HEquipID, string HDeptID, string HBeginDate, string HEndDate, string user)
|
{
|
try
|
{
|
List<object> columnNameList = new List<object>();
|
|
string sql = "select * from h_v_Sb_EquipRunningStatusReport where 1 = 1";
|
if(!string.IsNullOrWhiteSpace(HEquipID) && HEquipID != "0")
|
{
|
sql += $" and HEquipID = {HEquipID}";
|
}
|
if(!string.IsNullOrWhiteSpace(HDeptID) && HDeptID != "0")
|
{
|
sql += $" and HDeptID = {HDeptID}";
|
}
|
if(!string.IsNullOrWhiteSpace(HBeginDate) && !string.IsNullOrWhiteSpace(HEndDate))
|
{
|
sql += $"and (所属日期 between CONVERT(DATETIME, '{HBeginDate} 00:00:00', 120) AND CONVERT(DATETIME, '{HEndDate} 23:59:59', 120))";
|
}
|
|
ds = oCN.RunProcReturn(sql, "h_v_Sb_EquipRunningStatusReport");
|
|
//添加列名
|
foreach (DataColumn col in ds.Tables[0].Columns)
|
{
|
Type dataType = col.DataType;
|
string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
|
columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
|
}
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds.Tables[0];
|
objJsonResult.list = columnNameList;
|
return objJsonResult;
|
}
|
catch (Exception ex)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "查询数据异常，请与管理员联系！" + ex.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 设备运行切片 返回设备状态 图表数据 (时序图)
|
[Route("DAQ_EquipRunningSlice/GetHEquipStatusReportList2_DAQ")]
|
[HttpGet]
|
public object GetHEquipStatusReportList2_DAQ(string HEquipID, string HDeptID, string HBeginDate, string HEndDate, string user)
|
{
|
try
|
{
|
List<object> columnNameList = new List<object>();
|
|
ds = oCN.RunProcReturn($@"exec h_p_DAQ_EquipTimeLineReport {HEquipID}, {HDeptID}, N'{HBeginDate}', N'{HEndDate}'", "h_p_DAQ_EquipTimeLineReport");
|
|
|
// 初始化结果 JObject
|
JObject result = new JObject();
|
// 按时间对表查询出来的数据进行分组
|
DataTable table = ds.Tables[0];
|
var groupedData = table.Rows.Cast<DataRow>()
|
.GroupBy(row =>
|
{
|
// 取出时间字段并转为日期（自动忽略时分秒）
|
DateTime time = Convert.ToDateTime(row["时间"]);
|
return time.ToString("yyyy-MM-dd"); // 分组 Key
|
});
|
|
foreach (var group in groupedData)
|
{
|
string dateKey = group.Key;
|
JArray dataArray = new JArray();
|
|
foreach (DataRow row in group)
|
{
|
JObject rowObj = new JObject();
|
|
// 把这一行的所有列都转成 ECharts可以读取的data对象
|
foreach (DataColumn col in table.Columns)
|
{
|
|
rowObj[col.ColumnName] = row[col] != DBNull.Value
|
? JToken.FromObject(row[col])
|
: null;
|
}
|
|
dataArray.Add(rowObj);
|
}
|
|
// 加入最终结果
|
result[dateKey] = dataArray;
|
}
|
|
//添加列名
|
foreach (DataColumn col in ds.Tables[0].Columns)
|
{
|
Type dataType = col.DataType;
|
string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
|
columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
|
}
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = result;
|
objJsonResult.list = columnNameList;
|
return objJsonResult;
|
}
|
catch (Exception ex)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "查询数据异常，请与管理员联系！" + ex.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 设备驾驶舱 根据部门查询对应的设备
|
|
[Route("Sc_HEquipStateDistribution/Get_HEquipStateList")]
|
[HttpGet]
|
public object Get_HEquipStateList(string HDeptID, string user, int OrganizationID)
|
{
|
try
|
{
|
string sql1 = string.Format("exec h_p_sc_HDeptEquipList {0},{1}", HDeptID, OrganizationID);
|
|
ds = oCN.RunProcReturn(sql1, "h_p_sc_HDeptEquipList");
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
}
|
}
|
