Files

433 lines
19 KiB
C#
Executable File
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
using GXWebApi.Common;
using log4net;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web.Http;
namespace GXWebApi.Controllers
{
/// <summary>
/// MES 扫码物料追溯看板
/// 数据来源:GXWMS 库 ODK_ScanCodeIntercept / ODK_ScanCodeInterceptEntry
/// </summary>
public class MesController : ApiController
{
private static readonly ILog Logger = LogManager.GetLogger(typeof(MesController));
/// <summary>
/// 获取看板配置(自动刷新间隔,秒)
/// </summary>
[HttpGet]
public IHttpActionResult GetConfig()
{
int seconds = 600; // 默认10分钟
try
{
string val = ConfigurationManager.AppSettings["MesAutoRefreshSeconds"];
if (!string.IsNullOrEmpty(val) && int.TryParse(val.Trim(), out int tmp))
{
seconds = tmp < 0 ? 0 : tmp; // 不允许负数,0表示关闭
}
}
catch (Exception ex)
{
Logger.Error("读取MesAutoRefreshSeconds配置异常:", ex);
}
return Ok(new { success = true, data = new { autoRefreshSeconds = seconds } });
}
/// <summary>
/// 获取全部机型汇总数据(对比视图)
/// </summary>
[HttpGet]
public IHttpActionResult GetDashboardData()
{
try
{
DBHelpe db = DBHelpe.GetInstance();
// 1. 按机型汇总主表(仅统计当天)
string sqlMachine = @"
SELECT FType,
COUNT(*) AS ProductCount,
ISNULL(SUM(FNeedQty), 0) AS TotalNeedQty,
ISNULL(SUM(FRealityQty), 0) AS TotalRealityQty,
SUM(CASE WHEN FRealityQty >= FNeedQty THEN 1 ELSE 0 END) AS NormalCount,
SUM(CASE WHEN FRealityQty > 0 AND FRealityQty < FNeedQty THEN 1 ELSE 0 END) AS AbnormalCount,
SUM(CASE WHEN FRealityQty = 0 THEN 1 ELSE 0 END) AS NotStartCount
FROM ODK_ScanCodeIntercept
WHERE FDate >= CONVERT(date, GETDATE())
GROUP BY FType
ORDER BY FType";
DataTable dtMachine = db.ExecuteTable(sqlMachine);
// 2. 按机型+工序汇总子表扫码数量(仅统计当天)
string sqlProcess = @"
SELECT A.FType,
B.FProcessNumber,
B.FProcessName,
ISNULL(SUM(B.FQty), 0) AS TotalQty
FROM ODK_ScanCodeIntercept A
INNER JOIN ODK_ScanCodeInterceptEntry B ON A.FINTERID = B.FINTERID
WHERE A.FDate >= CONVERT(date, GETDATE())
GROUP BY A.FType, B.FProcessNumber, B.FProcessName
ORDER BY A.FType, B.FProcessNumber";
DataTable dtProcess = db.ExecuteTable(sqlProcess);
var machines = new List<object>();
int totalCount = 0;
foreach (DataRow mRow in dtMachine.Rows)
{
string fType = mRow["FType"].ToStringEx();
int count = mRow["ProductCount"].ToInt();
int totalNeed = mRow["TotalNeedQty"].ToInt();
int totalReality = mRow["TotalRealityQty"].ToInt();
int normalCount = mRow["NormalCount"].ToInt();
totalCount += count;
// 找出该机型所有工序的最大扫码数(用于条形图相对宽度)
int maxProcessQty = 0;
var processList = new List<object>();
foreach (DataRow pRow in dtProcess.Rows)
{
if (pRow["FType"].ToStringEx() != fType) continue;
int qty = pRow["TotalQty"].ToInt();
if (qty > maxProcessQty) maxProcessQty = qty;
}
foreach (DataRow pRow in dtProcess.Rows)
{
if (pRow["FType"].ToStringEx() != fType) continue;
int qty = pRow["TotalQty"].ToInt();
processList.Add(new
{
code = pRow["FProcessNumber"].ToStringEx(),
name = pRow["FProcessName"].ToStringEx(),
scanned = qty,
barPct = maxProcessQty > 0 ? Math.Round((double)qty / maxProcessQty * 100, 1) : 0
});
}
machines.Add(new
{
fType = fType,
count = count,
totalNeedQty = totalNeed,
totalRealityQty = totalReality,
coverage = count > 0 ? Math.Round((double)normalCount / count * 100, 1) : 0,
status = new
{
normal = normalCount,
abnormal = mRow["AbnormalCount"].ToInt(),
notstart = mRow["NotStartCount"].ToInt()
},
processes = processList
});
}
return Ok(new { success = true, data = new { machines = machines, totalCount = totalCount } });
}
catch (Exception ex)
{
Logger.Error("GetDashboardData异常:", ex);
return Ok(new { success = false, message = "获取数据异常: " + ex.Message });
}
}
/// <summary>
/// 获取单机型详情(含产品列表、异常列表、工序汇总)
/// </summary>
[HttpGet]
public IHttpActionResult GetMachineDetail(string fType)
{
try
{
WmsCommonFunction.UpUserCount("20260817001", "MES物料扫码追溯看板");
string safeType = fType.Replace("'", "''");
DBHelpe db = DBHelpe.GetInstance();
// 1. 机型汇总(仅统计当天)
string sqlSummary = $@"
SELECT COUNT(*) AS ProductCount,
ISNULL(SUM(FNeedQty), 0) AS TotalNeedQty,
ISNULL(SUM(FRealityQty), 0) AS TotalRealityQty,
SUM(CASE WHEN FRealityQty >= FNeedQty THEN 1 ELSE 0 END) AS NormalCount,
SUM(CASE WHEN FRealityQty > 0 AND FRealityQty < FNeedQty THEN 1 ELSE 0 END) AS AbnormalCount,
SUM(CASE WHEN FRealityQty = 0 THEN 1 ELSE 0 END) AS NotStartCount
FROM ODK_ScanCodeIntercept
WHERE FType = '{safeType}'
AND FDate >= CONVERT(date, GETDATE())";
DataTable dtSummary = db.ExecuteTable(sqlSummary);
if (dtSummary.Rows.Count == 0)
return Ok(new { success = false, message = "未找到该机型数据" });
DataRow sRow = dtSummary.Rows[0];
int count = sRow["ProductCount"].ToInt();
int totalNeed = sRow["TotalNeedQty"].ToInt();
int totalReality = sRow["TotalRealityQty"].ToInt();
// 2. 工序汇总(仅统计当天)
string sqlProcess = $@"
SELECT B.FProcessNumber, B.FProcessName, ISNULL(SUM(B.FQty), 0) AS TotalQty
FROM ODK_ScanCodeIntercept A
INNER JOIN ODK_ScanCodeInterceptEntry B ON A.FINTERID = B.FINTERID
WHERE A.FType = '{safeType}'
AND A.FDate >= CONVERT(date, GETDATE())
GROUP BY B.FProcessNumber, B.FProcessName
ORDER BY B.FProcessNumber";
DataTable dtProcess = db.ExecuteTable(sqlProcess);
var processList = new List<object>();
int maxProcessQty = 0;
foreach (DataRow pRow in dtProcess.Rows)
{
int qty = pRow["TotalQty"].ToInt();
if (qty > maxProcessQty) maxProcessQty = qty;
}
foreach (DataRow pRow in dtProcess.Rows)
{
int qty = pRow["TotalQty"].ToInt();
processList.Add(new
{
code = pRow["FProcessNumber"].ToStringEx(),
name = pRow["FProcessName"].ToStringEx(),
scanned = qty,
barPct = maxProcessQty > 0 ? Math.Round((double)qty / maxProcessQty * 100, 1) : 0
});
}
// 3. 产品列表 + 每个产品的缺失工序(仅统计当天)
string sqlProducts = $@"
SELECT FINTERID, FNO, FSN, FNeedQty, FRealityQty, FDate
FROM ODK_ScanCodeIntercept
WHERE FType = '{safeType}'
AND FDate >= CONVERT(date, GETDATE())
ORDER BY FDate DESC";
DataTable dtProducts = db.ExecuteTable(sqlProducts);
// 4. 近一周每日扫码率趋势(按 FDate 分组统计每天实扫/应扫总量)
string sqlTrend = $@"
SELECT CONVERT(varchar(10), FDate, 120) AS ScanDay,
ISNULL(SUM(FRealityQty), 0) AS DayRealityQty,
ISNULL(SUM(FNeedQty), 0) AS DayNeedQty
FROM ODK_ScanCodeIntercept
WHERE FType = '{safeType}'
AND FDate >= DATEADD(day, -6, CONVERT(date, GETDATE()))
GROUP BY CONVERT(varchar(10), FDate, 120)
ORDER BY ScanDay";
DataTable dtTrend = db.ExecuteTable(sqlTrend);
// 构造近7天序列(无数据的日期补0),X轴仅显示 月-日
var trend = new List<object>();
var trendMap = new Dictionary<string, KeyValuePair<int, int>>();
foreach (DataRow tRow in dtTrend.Rows)
{
trendMap[tRow["ScanDay"].ToStringEx()] = new KeyValuePair<int, int>(
tRow["DayRealityQty"].ToInt(),
tRow["DayNeedQty"].ToInt()
);
}
DateTime todayTrend = DateTime.Today;
for (int i = 6; i >= 0; i--)
{
DateTime day = todayTrend.AddDays(-i);
string key = day.ToString("yyyy-MM-dd");
int dReality = 0, dNeed = 0;
if (trendMap.ContainsKey(key))
{
dReality = trendMap[key].Key;
dNeed = trendMap[key].Value;
}
trend.Add(new
{
date = key,
label = day.ToString("MM-dd"),
needQty = dNeed,
realityQty = dReality,
ratio = dNeed > 0 ? Math.Round((double)dReality / dNeed * 100, 1) : 0
});
}
// 5. 一次性查出该机型所有产品的子表数据(避免 N+1 查询,仅统计当天)
string sqlAllEntries = $@"
SELECT A.FINTERID, B.FProcessNumber, B.FQty
FROM ODK_ScanCodeIntercept A
INNER JOIN ODK_ScanCodeInterceptEntry B ON A.FINTERID = B.FINTERID
WHERE A.FType = '{safeType}'
AND A.FDate >= CONVERT(date, GETDATE())";
DataTable dtAllEntries = db.ExecuteTable(sqlAllEntries);
// 构建 finterid -> [(工序编号, FQty)] 映射
var entryMap = new Dictionary<string, List<KeyValuePair<string, int>>>();
foreach (DataRow eRow in dtAllEntries.Rows)
{
string fid = eRow["FINTERID"].ToStringEx();
if (!entryMap.ContainsKey(fid))
entryMap[fid] = new List<KeyValuePair<string, int>>();
entryMap[fid].Add(new KeyValuePair<string, int>(
eRow["FProcessNumber"].ToStringEx(),
eRow["FQty"].ToInt()
));
}
var products = new List<object>();
foreach (DataRow pRow in dtProducts.Rows)
{
string fInterID = pRow["FINTERID"].ToStringEx();
string fno = pRow["FNO"].ToStringEx();
string fsn = pRow["FSN"].ToStringEx();
int need = pRow["FNeedQty"].ToInt();
int reality = pRow["FRealityQty"].ToInt();
string status = reality >= need ? "normal" : (reality > 0 ? "abnormal" : "notstart");
// 解析产线(车号前缀,如 517-GSS-xxxx -> 517)
string line = "";
if (fno.Contains("-")) line = fno.Split('-')[0];
// 解析时间
DateTime? dt = pRow["FDate"].ToDateTimeNullable();
string fDateStr = dt.HasValue ? dt.Value.ToString("yyyy-MM-dd HH:mm:ss") : "--";
// 计算缺失工序(子表中 FQty = 0 的工序编号)
string missingProcs = "";
if (status == "notstart")
{
missingProcs = "全部";
}
else if (entryMap.ContainsKey(fInterID))
{
var missing = new List<string>();
foreach (var kv in entryMap[fInterID])
{
if (kv.Value == 0) missing.Add(kv.Key);
}
missingProcs = string.Join(",", missing);
}
products.Add(new
{
fInterID = fInterID,
carNo = fno,
sn = fsn,
fType = fType,
expected = need,
actual = reality,
status = status,
fDate = fDateStr,
missingProcs = missingProcs,
line = line
});
}
return Ok(new
{
success = true,
data = new
{
fType = fType,
count = count,
totalNeedQty = totalNeed,
totalRealityQty = totalReality,
coverage = count > 0 ? Math.Round((double)sRow["NormalCount"].ToInt() / count * 100, 1) : 0,
status = new
{
normal = sRow["NormalCount"].ToInt(),
abnormal = sRow["AbnormalCount"].ToInt(),
notstart = sRow["NotStartCount"].ToInt()
},
processes = processList,
products = products,
trend = trend
}
});
}
catch (Exception ex)
{
Logger.Error("GetMachineDetail异常:", ex);
return Ok(new { success = false, message = "获取数据异常: " + ex.Message });
}
}
/// <summary>
/// 获取单个产品的工序级扫码详情
/// </summary>
[HttpGet]
public IHttpActionResult GetProductDetail(string fInterID)
{
try
{
string safeId = fInterID.Replace("'", "''");
DBHelpe db = DBHelpe.GetInstance();
// 1. 产品主表信息
string sqlProduct = $@"
SELECT FINTERID, FNO, FSN, FType, FNeedQty, FRealityQty, FDate
FROM ODK_ScanCodeIntercept
WHERE FINTERID = '{safeId}'";
DataTable dtProduct = db.ExecuteTable(sqlProduct);
if (dtProduct.Rows.Count == 0)
return Ok(new { success = false, message = "产品不存在" });
DataRow row = dtProduct.Rows[0];
int need = row["FNeedQty"].ToInt();
int reality = row["FRealityQty"].ToInt();
string status = reality >= need ? "normal" : (reality > 0 ? "abnormal" : "notstart");
DateTime? dt = row["FDate"].ToDateTimeNullable();
string fDateStr = dt.HasValue ? dt.Value.ToString("yyyy-MM-dd HH:mm:ss") : "--";
// 2. 工序级扫码明细
string sqlEntries = $@"
SELECT FEntryID, FProcessNumber, FProcessName, FQty
FROM ODK_ScanCodeInterceptEntry
WHERE FINTERID = '{safeId}'
ORDER BY FProcessNumber";
DataTable dtEntries = db.ExecuteTable(sqlEntries);
var processList = new List<object>();
foreach (DataRow eRow in dtEntries.Rows)
{
int qty = eRow["FQty"].ToInt();
processList.Add(new
{
entryId = eRow["FEntryID"].ToStringEx(),
code = eRow["FProcessNumber"].ToStringEx(),
name = eRow["FProcessName"].ToStringEx(),
qty = qty,
scanned = qty > 0
});
}
return Ok(new
{
success = true,
data = new
{
fInterID = fInterID,
carNo = row["FNO"].ToStringEx(),
sn = row["FSN"].ToStringEx(),
fType = row["FType"].ToStringEx(),
expected = need,
actual = reality,
status = status,
fDate = fDateStr,
processes = processList
}
});
}
catch (Exception ex)
{
Logger.Error("GetProductDetail异常:", ex);
return Ok(new { success = false, message = "获取数据异常: " + ex.Message });
}
}
}
}