Files
GX.O3.WMS/WebApi/.svn/pristine/ec/ec68a135c46b51134aaa695e2748b06ff75b527a.svn-base

3968 lines
204 KiB
Plaintext
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 Newtonsoft.Json.Linq;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web.Http;
using System.Xml;
namespace GXWebApi.Controllers
{
/// <summary>
/// 采购物料交付计划看板 - 控制器
/// 包含:产品BOM主数据、生产排程维护、BOM递归展开、物料需求预计算、物料交付维护、到货计划、看板展示
/// </summary>
public class DeliverController : ApiController
{
private static readonly ILog Logger = LogManager.GetLogger(typeof(DeliverController));
/// <summary>
/// 重算任务进度(按排程Id存储,供前端轮询展示进度条)
/// </summary>
private class RecalcProgress
{
public int ScheduleId { get; set; }
public int Percent { get; set; }
public string Stage { get; set; }
public string Status { get; set; } // running / success / error
public string Message { get; set; }
public DateTime StartTime { get; set; }
}
private static readonly System.Collections.Concurrent.ConcurrentDictionary<int, RecalcProgress> RecalcProgressMap =
new System.Collections.Concurrent.ConcurrentDictionary<int, RecalcProgress>();
#region 辅助方法
private DBHelpe db = new DBHelpe();
private string Sql(string s)
{
if (string.IsNullOrEmpty(s)) return "";
// 统一金蝶物料数据中的全角引号(' U+FF07)与智能引号(' U+2018 / ' U+2019)为 ASCII 单引号,
// 避免脏数据(如规格型号里的引号)破坏 SQL 拼接或在看板显示异常
s = s.Replace("\uFF07", "'").Replace("\u2018", "'").Replace("\u2019", "'");
return s.Replace("'", "''");
}
/// <summary>
/// 安全获取字符串值
/// </summary>
private string Str(object o)
{
return o == null || o == DBNull.Value ? "" : o.ToString();
}
/// <summary>
/// 安全获取decimal值
/// </summary>
private decimal Dec(object o)
{
if (o == null || o == DBNull.Value) return 0;
decimal d;
return decimal.TryParse(o.ToString(), out d) ? d : 0;
}
/// <summary>
/// 安全获取int值
/// </summary>
private int Int(object o)
{
if (o == null || o == DBNull.Value) return 0;
int i;
return int.TryParse(o.ToString(), out i) ? i : 0;
}
/// <summary>
/// 格式化日期:取日期部分
/// </summary>
private string DateStr(object o)
{
if (o == null || o == DBNull.Value) return "";
if (o is DateTime dt) return dt.ToString("yyyy-MM-dd");
string s = o.ToString();
int idx = s.IndexOf(' ');
return idx > 0 ? s.Substring(0, idx) : s;
}
/// <summary>
/// 格式化数字:去掉多余0
/// </summary>
private string NumStr(object o)
{
decimal d = Dec(o);
if (d == (int)d) return ((int)d).ToString();
return d.ToString("0.##");
}
/// <summary>
/// 交付风险严重度排序(数值越小越严重):断线 > 预警 > 安全
/// </summary>
private int RiskOrder(string risk)
{
switch (risk)
{
case "断线": return 0;
case "预警": return 1;
case "安全": return 2;
default: return 3;
}
}
#endregion
#region ===== 产品BOM主数据 =====
/// <summary>
/// 获取产品BOM主数据列表
/// </summary>
[HttpGet]
public IHttpActionResult GetBomMasterList(string keyword = "", int status = -1, int page = 1, int pageSize = 20)
{
try
{
string where = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(keyword))
where += $" AND (ProductCode LIKE '%{Sql(keyword)}%' OR ProductName LIKE N'%{Sql(keyword)}%') ";
if (status >= 0)
where += $" AND Status = {status} ";
// 统计总记录数
DataTable dtCount = db.ExecuteTable($"SELECT COUNT(*) AS CNT FROM Product_BOM_Master {where}");
int total = Int(dtCount.Rows[0]["CNT"]);
if (page < 1) page = 1;
if (pageSize <= 0) pageSize = 20;
int offset = (page - 1) * pageSize;
DataTable dt = db.ExecuteTable($"SELECT * FROM Product_BOM_Master {where} ORDER BY ProductCode OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["Id"] = r["Id"].ToStringEx(),
["ProductCode"] = r["ProductCode"].ToStringEx(),
["BOMVersion"] = r["BOMVersion"].ToStringEx(),
["ProductName"] = r["ProductName"].ToStringEx(),
["Status"] = r["Status"].ToStringEx()
});
}
return Ok(new { success = true, data = list, total = total, page = page, pageSize = pageSize });
}
catch (Exception ex)
{
Logger.Error("GetBomMasterList异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 保存产品BOM主数据(新增/更新)
/// </summary>
[HttpPost]
public IHttpActionResult SaveBomMaster([FromBody] JObject obj)
{
try
{
int id = obj["Id"].ToStringEx().ToInt();
string productCode = obj["ProductCode"]?.ToString() ?? "";
string bomVersion = obj["BOMVersion"]?.ToString() ?? "";
string productName = obj["ProductName"]?.ToString() ?? "";
int status = obj["Status"].ToStringEx().ToInt();
if (status == 0 && obj["Status"].ToStringEx() == "") status = 1;
if (string.IsNullOrEmpty(productCode) || string.IsNullOrEmpty(bomVersion))
return Ok(new { success = false, message = "产品编码和BOM版本不能为空" });
if (id > 0)
{
db.ExecuteNonQuery($@"UPDATE Product_BOM_Master SET
ProductCode='{Sql(productCode)}', BOMVersion='{Sql(bomVersion)}',
ProductName=N'{Sql(productName)}', Status={status}, UpdateTime=GETDATE() WHERE Id={id}");
}
else
{
// 查重
DataTable dt = db.ExecuteTable($"SELECT Id FROM Product_BOM_Master WHERE ProductCode='{Sql(productCode)}' AND BOMVersion='{Sql(bomVersion)}'");
if (dt.Rows.Count > 0)
return Ok(new { success = false, message = "该产品编码+BOM版本已存在" });
db.ExecuteNonQuery($@"INSERT INTO Product_BOM_Master (ProductCode,BOMVersion,ProductName,Status)
VALUES ('{Sql(productCode)}','{Sql(bomVersion)}',N'{Sql(productName)}',{status})");
}
return Ok(new { success = true, message = "保存成功" });
}
catch (Exception ex)
{
Logger.Error("SaveBomMaster异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 删除产品BOM主数据
/// </summary>
[HttpPost]
public IHttpActionResult DeleteBomMaster([FromBody] JObject obj)
{
try
{
int id = obj["Id"].ToStringEx().ToInt();
// 先查出要删除的产品编码和BOM版本,用于同时删除明细
DataTable masterDt = db.ExecuteTable($"SELECT ProductCode, BOMVersion FROM Product_BOM_Master WHERE Id={id}");
if (masterDt.Rows.Count == 0)
return Ok(new { success = false, message = "记录不存在" });
DataRow masterRow = masterDt.Rows[0];
string productCode = masterRow["ProductCode"].ToStringEx();
string bomVersion = masterRow["BOMVersion"].ToStringEx();
db.ExecuteNonQuery($"DELETE FROM Product_BOM_Master WHERE Id={id}");
db.ExecuteNonQuery($"DELETE FROM Product_BOM_Detail WHERE ProductCode='{Sql(productCode)}' AND BOMVersion='{Sql(bomVersion)}'");
return Ok(new { success = true, message = "删除成功" });
}
catch (Exception ex)
{
Logger.Error("DeleteBomMaster异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取某产品的BOM明细(多级树快照)
/// </summary>
[HttpGet]
public IHttpActionResult GetBomDetail(string productCode, string bomVersion = "")
{
try
{
string where = $" WHERE ProductCode='{Sql(productCode)}' AND ISNULL(IsVirtual,0)=0 ";
if (!string.IsNullOrEmpty(bomVersion)) where += $" AND BOMVersion='{Sql(bomVersion)}' ";
DataTable dt = db.ExecuteTable($"SELECT * FROM Product_BOM_Detail {where} ORDER BY Level, ParentMaterialId, ChildMaterialId");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["Id"] = r["Id"].ToStringEx(),
["ProductCode"] = r["ProductCode"].ToStringEx(),
["BOMVersion"] = r["BOMVersion"].ToStringEx(),
["ParentMaterialId"] = r["ParentMaterialId"].ToStringEx(),
["ChildMaterialId"] = r["ChildMaterialId"].ToStringEx(),
["UsageQty"] = NumStr(r["UsageQty"]),
["Level"] = r["Level"].ToStringEx(),
["MaterialName"] = r["MaterialName"].ToStringEx(),
["Spec"] = r["Spec"].ToStringEx(),
["Unit"] = r["Unit"].ToStringEx()
});
}
return Ok(new { success = true, data = list, total = list.Count });
}
catch (Exception ex)
{
Logger.Error("GetBomDetail异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 从金蝶BOM表递归展开并保存某产品的BOM明细
/// 所有表FMATERIALID关联T_BD_MATERIAL.FINTERID,这里做编码->内码映射,业务表统一存物料编码FNUMBER
/// </summary>
[HttpPost]
public IHttpActionResult ExpandBom([FromBody] JObject obj)
{
try
{
string productCode = obj["ProductCode"]?.ToString() ?? "";
string bomVersion = obj["BOMVersion"]?.ToString() ?? "";
if (string.IsNullOrEmpty(productCode))
return Ok(new { success = false, message = "产品编码不能为空" });
string msg;
int inserted = EnsureProductRegistered(productCode, bomVersion, out msg);
if (inserted < 0)
return Ok(new { success = false, message = msg });
return Ok(new { success = true, message = $"BOM展开成功,共{inserted}条子物料明细", total = inserted, version = bomVersion });
}
catch (Exception ex)
{
Logger.Error("ExpandBom异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 确保产品已登记到BOM主数据并已展开BOM明细。
/// 若产品未登记则自动登记(静默);若未展开BOM则自动展开。
/// 返回:>=0 表示成功(值为展开的BOM明细条数),-1 表示失败(msg为失败原因)。
/// </summary>
private int EnsureProductRegistered(string productCode, string bomVersion, out string msg, RecalcProgress p = null)
{
msg = "";
if (string.IsNullOrEmpty(productCode)) { msg = "产品编码不能为空"; return -1; }
db.BeginTransaction();
try
{
// 应用锁:防止并发展开同一产品BOM导致删除/插入交错、数据重复
string lockName = $"BOMExpand_{productCode}_{bomVersion}";
db.ExecuteNonQuery($"EXEC sp_getapplock @Resource='{Sql(lockName)}', @LockMode='Exclusive', @LockTimeout=30000");
// 产品编码 -> 物料内码 映射(固定使用 FCOMPANYID=986610 的组织视图)
DataTable matMap = db.ExecuteTable($@"
SELECT TOP 1 FINTERID, FNUMBER, FNAME FROM T_BD_MATERIAL
WHERE FNUMBER='{Sql(productCode)}' AND FDELETED=0 AND FCOMPANYID=986610");
if (matMap.Rows.Count == 0)
{
db.ExecuteNonQuery($"EXEC sp_releaseapplock @Resource='{Sql(lockName)}'");
db.RollbackTransaction();
msg = $"未在物料主数据[T_BD_MATERIAL]中找到产品编码[{productCode}],请确认产品编码是否正确";
return -1;
}
string productName = matMap.Rows[0]["FNAME"].ToStringEx();
// 从金蝶BOM表查找该产品(FCOMPANYID=986610)的BOM主表,取"审核通过+最新审核"
DataTable bomMain = db.ExecuteTable($@"
SELECT TOP 1 FINTERID, FBILLNO FROM T_BD_BOM
WHERE FMATERIALID='{Sql(matMap.Rows[0]["FINTERID"].ToStringEx())}' AND FDELETED=0
AND FSTATUS=40 AND FCHECKDATE IS NOT NULL
AND (FBILLNO='{Sql(bomVersion)}' OR '{Sql(bomVersion)}'='')
ORDER BY FCHECKDATE DESC, FINTERID DESC");
// 固定基准组织为 FCOMPANYID=986610(该组织 BOM 为最新维护版本)
string baseCompanyId = "986610";
if (bomMain.Rows.Count == 0)
{
db.ExecuteNonQuery($"EXEC sp_releaseapplock @Resource='{Sql(lockName)}'");
db.RollbackTransaction();
msg = $"未在金蝶BOM表中找到产品[{productCode}]的BOM,请确认该产品已建立BOM";
return -1;
}
string version = bomMain.Rows[0]["FBILLNO"].ToStringEx();
if (string.IsNullOrEmpty(bomVersion)) bomVersion = version;
// 确保产品已登记到 BOM 主数据(未登记则自动登记,不影响流程)
DataTable existM = db.ExecuteTable($"SELECT Id FROM Product_BOM_Master WHERE ProductCode='{Sql(productCode)}' AND BOMVersion='{Sql(bomVersion)}'");
if (existM.Rows.Count == 0)
{
db.ExecuteNonQuery($@"INSERT INTO Product_BOM_Master (ProductCode,BOMVersion,ProductName,Status)
VALUES ('{Sql(productCode)}','{Sql(bomVersion)}',N'{Sql(productName)}',1)");
}
// 清空并重新展开BOM明细(幂等:始终以金蝶为准刷新快照)
db.ExecuteNonQuery($"DELETE FROM Product_BOM_Detail WHERE ProductCode='{Sql(productCode)}' AND (BOMVersion='' OR BOMVersion='{Sql(bomVersion)}')");
List<Dictionary<string, object>> expanded = new List<Dictionary<string, object>>();
int expandCount = 0;
RecursiveExpand(productCode, bomVersion, productCode, "", 1, 1m, expanded, new List<string>(), baseCompanyId, ref expandCount, p);
int inserted = InsertBomDetail(productCode, bomVersion, expanded);
db.ExecuteNonQuery($"EXEC sp_releaseapplock @Resource='{Sql(lockName)}'");
db.CommitTransaction();
return inserted;
}
catch (Exception ex)
{
Logger.Error($"EnsureProductRegistered展开BOM异常: {productCode}/{bomVersion}", ex);
db.RollbackTransaction();
throw;
}
}
/// <summary>
/// 确保BOM主数据存在(即使金蝶无BOM也登记,便于后续手动维护/展开)
/// </summary>
private void EnsureBomMasterRegistered(string productCode, string bomVersion)
{
if (string.IsNullOrEmpty(productCode) || string.IsNullOrEmpty(bomVersion)) return;
DataTable existM = db.ExecuteTable($"SELECT Id FROM Product_BOM_Master WHERE ProductCode='{Sql(productCode)}' AND BOMVersion='{Sql(bomVersion)}'");
if (existM.Rows.Count == 0)
{
// 尝试从金蝶取产品名称,取不到则用编码
string productName = productCode;
DataTable mat = db.ExecuteTable($"SELECT TOP 1 FNAME FROM T_BD_MATERIAL WHERE FNUMBER='{Sql(productCode)}' AND FDELETED=0 AND FCOMPANYID = 986610");
if (mat.Rows.Count > 0) productName = mat.Rows[0]["FNAME"].ToStringEx();
db.ExecuteNonQuery($@"INSERT INTO Product_BOM_Master (ProductCode,BOMVersion,ProductName,Status)
VALUES ('{Sql(productCode)}','{Sql(bomVersion)}',N'{Sql(productName)}',1)");
}
}
/// <summary>
/// 递归展开BOM树(与金蝶物料清单正查一致:同一物料在不同位置出现时保留全部记录)。
/// 下钻规则(与金蝶正查对齐):父项BOM明细上的子项FBOMID(子项BOM版本)有值→该子项有下级,
/// 且下钻时直接使用FBOMID指定的BOM;FBOMID无值→视为叶子(字段未同步期间回退按"最新审核BOM"兜底)。
/// pathMaterials 为当前展开路径上的物料内码集合(按路径防环,防止循环引用BOM)。
/// </summary>
private void RecursiveExpand(string productCode, string bomVersion, string parentCode, string parentName,
int level, decimal pathUsage, List<Dictionary<string, object>> result, List<string> pathMaterials,
string baseCompanyId, ref int expandedCount, RecalcProgress p = null, string forcedBomInterid = null)
{
// 确定父物料本次使用的BOM内码:
// 1) 上层通过子项FBOMID指定了BOM → 直接使用(金蝶正查规则)
// 2) 未指定(根节点或FBOMID未同步)→ 按编码+基准组织找物料,取"审核通过+最新审核"的BOM
string bomInterid;
if (!string.IsNullOrEmpty(forcedBomInterid))
{
bomInterid = forcedBomInterid;
}
else
{
DataTable parentMat = db.ExecuteTable($@"
SELECT TOP 1 FINTERID FROM T_BD_MATERIAL
WHERE FNUMBER='{Sql(parentCode)}' AND FCOMPANYID='{Sql(baseCompanyId)}' AND FDELETED=0");
if (parentMat.Rows.Count == 0) return;
string parentMaterialFinterid = parentMat.Rows[0]["FINTERID"].ToStringEx();
DataTable bomDt = db.ExecuteTable($@"
SELECT TOP 1 FINTERID FROM T_BD_BOM
WHERE FMATERIALID='{Sql(parentMaterialFinterid)}' AND FDELETED=0
AND FSTATUS=40 AND FCHECKDATE IS NOT NULL
ORDER BY FCHECKDATE DESC, FINTERID DESC");
if (bomDt.Rows.Count == 0) return;
bomInterid = bomDt.Rows[0]["FINTERID"].ToStringEx();
}
// 查询该BOM下所有子项,并关联T_BD_MATERIAL取子物料编码/名称/规格
// FBOMID:金蝶子项BOM版本字段(正查下钻依据)
DataTable dt = db.ExecuteTable($@"
SELECT E.FMATERIALID AS ChildFinterid, E.FNUMERATOR, E.FDENOMINATOR, E.FISFEED,
E.FBOMID AS ChildBomId,
B.FBILLNO AS FVERSION,
C.FNUMBER AS ChildCode, C.FNAME AS ChildName, C.FSPECIFICATION AS ChildSpec, C.FERPCLSID AS ChildErpClsId,
P.FNUMBER AS ParentCode
FROM T_BD_BOMENTRY E
INNER JOIN T_BD_BOM B ON E.FINTERID = B.FINTERID AND B.FDELETED=0
LEFT JOIN T_BD_MATERIAL C ON E.FMATERIALID = C.FINTERID AND C.FDELETED=0
LEFT JOIN T_BD_MATERIAL P ON B.FMATERIALID = P.FINTERID AND P.FDELETED=0
WHERE E.FINTERID='{Sql(bomInterid)}' AND E.FDELETED=0 AND E.FISFEED=0");
if (dt.Rows.Count == 0) return;
foreach (DataRow r in dt.Rows)
{
string childFinterid = r["ChildFinterid"].ToStringEx();
if (string.IsNullOrEmpty(childFinterid)) continue;
// 防环:子物料已在当前展开路径上(循环引用BOM),跳过;
// 同一物料在不同位置/分支出现时正常保留(与金蝶正查一致)
if (pathMaterials.Contains(childFinterid)) continue;
// 子物料编码(若T_BD_MATERIAL无该物料则用内码兜底)
string childCode = r["ChildCode"].ToStringEx();
if (string.IsNullOrEmpty(childCode)) childCode = childFinterid;
// 父物料编码(用于记录,防止路径混淆)
string parentCodeForRecord = r["ParentCode"].ToStringEx();
if (string.IsNullOrEmpty(parentCodeForRecord)) parentCodeForRecord = parentCode;
decimal numerator = Dec(r["FNUMERATOR"]);
decimal denominator = Dec(r["FDENOMINATOR"]);
if (denominator == 0) denominator = 1;
decimal usage = numerator / denominator;
if (usage == 0) usage = 1;
decimal totalUsage = pathUsage * usage;
string materialName = r["ChildName"].ToStringEx();
string spec = r["ChildSpec"].ToStringEx();
// 物料属性(5=虚拟件,9=配置件):不入快照,但下级继续展开且用量正常传递
// 用量倍数已乘进 totalUsage(通过 pathUsage 累乘),下一级递归照常进行
string childErpClsId = r["ChildErpClsId"]?.ToStringEx() ?? "";
bool isVirtualOrConfig = childErpClsId == "5" || childErpClsId == "9";
// 子项BOM版本(金蝶FBOMID):有值→该子项有下级,且下钻时用这个BOM;
// 无值→视为叶子。字段未同步期间(全为空)回退按"基准组织下有无审核BOM"兜底判断
string childBomId = r["ChildBomId"].ToStringEx();
int hasChild;
if (!string.IsNullOrEmpty(childBomId))
{
hasChild = 1;
}
else
{
DataTable childBom = db.ExecuteTable($@"
SELECT COUNT(*) AS CNT FROM T_BD_BOM B
INNER JOIN T_BD_MATERIAL M ON B.FMATERIALID=M.FINTERID
WHERE M.FNUMBER='{Sql(childCode)}' AND M.FCOMPANYID='{Sql(baseCompanyId)}' AND B.FDELETED=0
AND B.FSTATUS=40 AND B.FCHECKDATE IS NOT NULL");
hasChild = childBom.Rows.Count > 0 ? Int(childBom.Rows[0]["CNT"]) : 0;
}
// 所有子件(含虚拟/配置件)都写入快照,打 IsVirtual 标记。
// 虚拟件必须入快照,否则 BuildTotalUsage 无法从父节点递归到孙节点,
// 用量倍数会丢失。需求计算时再按 IsVirtual 过滤。
result.Add(new Dictionary<string, object>
{
["ProductCode"] = productCode,
["BOMVersion"] = bomVersion,
["ParentMaterialId"] = parentCodeForRecord,
["ChildMaterialId"] = childCode,
["UsageQty"] = usage,
["Level"] = level,
["MaterialName"] = materialName,
["Spec"] = spec,
["Unit"] = "",
["TotalUsage"] = totalUsage,
["IsLeaf"] = hasChild == 0 ? 1 : 0,
["ChildFinterid"] = childFinterid,
["IsVirtual"] = isVirtualOrConfig ? 1 : 0
});
expandedCount++;
if (p != null)
{
p.Message = $"正在展开BOM... 已展开 {expandedCount} 条子物料";
p.Percent = 5 + Math.Min(expandedCount / 50, 50);
}
// 递归展开下级(自制件),传递新的展开路径;FBOMID有值时强制用该BOM下钻
// 虚拟/配置件也递归:其用量已乘入 totalUsage,下级通过 ParentMaterialId 正常接力
if (hasChild > 0 && level < 10)
{
List<string> newPath = new List<string>(pathMaterials);
newPath.Add(childFinterid);
RecursiveExpand(productCode, bomVersion, childCode, materialName, level + 1, totalUsage, result, newPath, baseCompanyId, ref expandedCount, p,
string.IsNullOrEmpty(childBomId) ? null : childBomId);
}
}
}
/// <summary>
/// 批量插入BOM明细(逐条执行,失败时精确记录具体记录,便于定位含特殊字符的脏数据)
/// </summary>
private int InsertBomDetail(string productCode, string bomVersion, List<Dictionary<string, object>> rows)
{
int count = 0;
foreach (var row in rows)
{
string childId = row["ChildMaterialId"].ToString();
string parentId = row["ParentMaterialId"].ToString();
string matName = row["MaterialName"]?.ToString() ?? "";
string spec = row["Spec"]?.ToString() ?? "";
string unit = row["Unit"]?.ToString() ?? "";
decimal usageQty = Dec(row["UsageQty"]);
int level = Int(row["Level"]);
int isVirtual = Int(row["IsVirtual"]);
string oneSql = $@"INSERT INTO Product_BOM_Detail (ProductCode,BOMVersion,ParentMaterialId,ChildMaterialId,UsageQty,Level,MaterialName,Spec,Unit,IsVirtual)
VALUES ('{Sql(productCode)}','{Sql(bomVersion)}','{Sql(parentId)}','{Sql(childId)}',
{usageQty},{level},N'{Sql(matName)}',N'{Sql(spec)}','{Sql(unit)}',{isVirtual});";
try
{
db.ExecuteNonQuery(oneSql);
count++;
}
catch (Exception ex)
{
Logger.Error($@"InsertBomDetail执行失败 productCode={productCode} bomVersion={bomVersion} 已成功{count}条
失败记录: ChildMaterialId={childId} ParentMaterialId={parentId} MaterialName={matName} Spec={spec} Unit={unit} IsVirtual={isVirtual} UsageQty={usageQty} Level={level}
该条SQL: {oneSql}", ex);
throw;
}
}
return count;
}
#endregion
#region ===== 生产排程维护 =====
/// <summary>
/// 获取生产排程列表
/// </summary>
[HttpGet]
public IHttpActionResult GetScheduleList(string productCode = "", string bomVersion = "", string productType = "", string status = "", string keyword = "", int page = 1, int pageSize = 20)
{
try
{
string where = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(productCode)) where += $" AND S.ProductCode='{Sql(productCode)}' ";
if (!string.IsNullOrEmpty(bomVersion)) where += $" AND S.BOMVersion='{Sql(bomVersion)}' ";
if (!string.IsNullOrEmpty(productType)) where += $" AND ISNULL(S.ProductType,N'') LIKE '%{Sql(productType)}%' ";
if (!string.IsNullOrEmpty(status)) where += $" AND S.Status=N'{Sql(status)}' ";
if (!string.IsNullOrEmpty(keyword)) where += $" AND (S.ProductCode LIKE '%{Sql(keyword)}%' OR M.ProductName LIKE N'%{Sql(keyword)}%') ";
// 统计总记录数
DataTable dtCount = db.ExecuteTable($@"
SELECT COUNT(*) AS CNT
FROM Production_Schedule S
LEFT JOIN Product_BOM_Master M ON S.ProductCode=M.ProductCode AND S.BOMVersion=M.BOMVersion
{where}");
int total = Int(dtCount.Rows[0]["CNT"]);
if (page < 1) page = 1;
if (pageSize <= 0) pageSize = 20;
int offset = (page - 1) * pageSize;
DataTable dt = db.ExecuteTable($@"
SELECT S.Id, S.ProductCode, S.BOMVersion, S.PlanStartDate, S.PlanEndDate, S.Status, S.Remark, S.CreateUser, S.CreateTime,
ISNULL(S.ProductType,N'') AS ProductType,
ISNULL(S.Factory,N'') AS Factory,
ISNULL(S.Region,N'') AS Region,
ISNULL(S.Country,N'') AS Country,
M.ProductName,
(SELECT ISNULL(SUM(DailyOutput),0) FROM Production_Schedule_Detail D WHERE D.ScheduleId=S.Id) AS TotalOutput,
(SELECT ISNULL(MAX(DailyOutput),0) FROM Production_Schedule_Detail D WHERE D.ScheduleId=S.Id) AS MaxDaily,
(SELECT ISNULL(COUNT(*),0) FROM Production_Schedule_Detail D WHERE D.ScheduleId=S.Id) AS DayCount
FROM Production_Schedule S
LEFT JOIN Product_BOM_Master M ON S.ProductCode=M.ProductCode AND S.BOMVersion=M.BOMVersion
{where}
ORDER BY S.PlanStartDate DESC, S.Id DESC
OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["Id"] = r["Id"].ToStringEx(),
["ProductCode"] = r["ProductCode"].ToStringEx(),
["BOMVersion"] = r["BOMVersion"].ToStringEx(),
["ProductName"] = r["ProductName"].ToStringEx(),
["ProductType"] = r["ProductType"].ToStringEx(),
["Factory"] = r["Factory"].ToStringEx(),
["Region"] = r["Region"].ToStringEx(),
["Country"] = r["Country"].ToStringEx(),
["PlanStartDate"] = DateStr(r["PlanStartDate"]),
["PlanEndDate"] = DateStr(r["PlanEndDate"]),
["Status"] = r["Status"].ToStringEx(),
["Remark"] = r["Remark"].ToStringEx(),
["CreateUser"] = r["CreateUser"].ToStringEx(),
["TotalOutput"] = NumStr(r["TotalOutput"]),
["MaxDaily"] = NumStr(r["MaxDaily"]),
["DayCount"] = r["DayCount"].ToStringEx()
});
}
return Ok(new { success = true, data = list, total = total, page = page, pageSize = pageSize });
}
catch (Exception ex)
{
Logger.Error("GetScheduleList异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取排程详情(含每日产量明细)
/// </summary>
[HttpGet]
public IHttpActionResult GetScheduleDetail(int id)
{
try
{
DataTable dt = db.ExecuteTable($@"
SELECT S.*, M.ProductName FROM Production_Schedule S
LEFT JOIN Product_BOM_Master M ON S.ProductCode=M.ProductCode AND S.BOMVersion=M.BOMVersion
WHERE S.Id={id}");
if (dt.Rows.Count == 0)
return Ok(new { success = false, message = "排程不存在" });
DataRow r = dt.Rows[0];
// 每日产量
DataTable dtDetail = db.ExecuteTable($@"
SELECT PlanDate, DailyOutput FROM Production_Schedule_Detail
WHERE ScheduleId={id} ORDER BY PlanDate");
List<Dictionary<string, object>> daily = new List<Dictionary<string, object>>();
foreach (DataRow dr in dtDetail.Rows)
{
daily.Add(new Dictionary<string, object>
{
["PlanDate"] = DateStr(dr["PlanDate"]),
["DailyOutput"] = NumStr(dr["DailyOutput"])
});
}
return Ok(new
{
success = true,
data = new
{
Id = r["Id"].ToStringEx(),
ProductCode = r["ProductCode"].ToStringEx(),
BOMVersion = r["BOMVersion"].ToStringEx(),
ProductName = r["ProductName"].ToStringEx(),
ProductType = r["ProductType"].ToStringEx(),
Factory = r["Factory"].ToStringEx(),
Region = r["Region"].ToStringEx(),
Country = r["Country"].ToStringEx(),
PlanStartDate = DateStr(r["PlanStartDate"]),
PlanEndDate = DateStr(r["PlanEndDate"]),
Status = r["Status"].ToStringEx(),
Remark = r["Remark"].ToStringEx(),
CreateUser = r["CreateUser"].ToStringEx(),
daily = daily
}
});
}
catch (Exception ex)
{
Logger.Error("GetScheduleDetail异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 保存排程(新增/更新),保存后自动触发BOM展开与需求计算
/// </summary>
[HttpPost]
public IHttpActionResult SaveSchedule([FromBody] JObject obj)
{
try
{
int id = obj["Id"].ToStringEx().ToInt();
string productCode = obj["ProductCode"]?.ToString() ?? "";
string bomVersion = obj["BOMVersion"]?.ToString() ?? "";
string productType = obj["ProductType"]?.ToString() ?? "";
string factory = obj["Factory"]?.ToString() ?? "";
string region = obj["Region"]?.ToString() ?? "";
string country = obj["Country"]?.ToString() ?? "";
string planStart = obj["PlanStartDate"]?.ToString() ?? "";
string planEnd = obj["PlanEndDate"]?.ToString() ?? "";
string status = obj["Status"]?.ToString() ?? "待开始";
string remark = obj["Remark"]?.ToString() ?? "";
string createUser = obj["CreateUser"]?.ToString() ?? "";
JArray daily = obj["daily"] as JArray ?? new JArray();
if (string.IsNullOrEmpty(productCode) || string.IsNullOrEmpty(bomVersion))
return Ok(new { success = false, message = "产品编码和BOM版本不能为空" });
if (string.IsNullOrEmpty(planStart) || string.IsNullOrEmpty(planEnd))
return Ok(new { success = false, message = "计划开始日和计划完成日不能为空" });
if (id > 0)
{
db.ExecuteNonQuery($@"UPDATE Production_Schedule SET
ProductCode='{Sql(productCode)}', BOMVersion='{Sql(bomVersion)}',
ProductType=N'{Sql(productType)}',
Factory=N'{Sql(factory)}', Region=N'{Sql(region)}', Country=N'{Sql(country)}',
PlanStartDate='{planStart}', PlanEndDate='{planEnd}',
Status=N'{Sql(status)}', Remark=N'{Sql(remark)}', UpdateTime=GETDATE() WHERE Id={id}");
}
else
{
DataTable dt = db.ExecuteTable($@"SELECT Id FROM Production_Schedule
WHERE ProductCode='{Sql(productCode)}' AND BOMVersion='{Sql(bomVersion)}' AND PlanStartDate='{planStart}'");
if (dt.Rows.Count > 0)
{
id = Int(dt.Rows[0]["Id"]);
db.ExecuteNonQuery($@"UPDATE Production_Schedule SET ProductType=N'{Sql(productType)}', Factory=N'{Sql(factory)}', Region=N'{Sql(region)}', Country=N'{Sql(country)}', UpdateTime=GETDATE() WHERE Id={id}");
}
else
{
db.ExecuteNonQuery($@"INSERT INTO Production_Schedule (ProductCode,BOMVersion,ProductType,Factory,Region,Country,PlanStartDate,PlanEndDate,Status,Remark,CreateUser)
VALUES ('{Sql(productCode)}','{Sql(bomVersion)}',N'{Sql(productType)}',N'{Sql(factory)}',N'{Sql(region)}',N'{Sql(country)}','{planStart}','{planEnd}',N'{Sql(status)}',N'{Sql(remark)}','{Sql(createUser)}')");
id = GetLastIdentity();
}
}
// 先确保BOM主数据存在(本地登记,便于后续手动维护/展开)
EnsureBomMasterRegistered(productCode, bomVersion);
// 保存每日产量明细
SaveScheduleDetail(id, productCode, bomVersion, daily);
// 只保存数据,不做BOM展开/需求计算/物料交付处理(由物料交付页"重算交付数据"统一执行)
return Ok(new { success = true, message = "排程保存成功", data = new { Id = id } });
}
catch (Exception ex)
{
Logger.Error("SaveSchedule异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
private int GetLastIdentity()
{
DataTable dt2 = db.ExecuteTable("SELECT IDENT_CURRENT('Production_Schedule') AS ID");
return Int(dt2.Rows[0]["ID"]);
}
/// <summary>
/// 保存排程每日产量明细
/// </summary>
private void SaveScheduleDetail(int scheduleId, string productCode, string bomVersion, JArray daily)
{
// 删除旧明细
db.ExecuteNonQuery($"DELETE FROM Production_Schedule_Detail WHERE ScheduleId={scheduleId}");
// 记录变更日志
foreach (var item in daily)
{
string date = item["PlanDate"]?.ToString() ?? "";
string output = item["DailyOutput"]?.ToString() ?? "0";
if (string.IsNullOrEmpty(date)) continue;
decimal outDec;
if (!decimal.TryParse(output, out outDec)) outDec = 0;
if (outDec > 0)
{
db.ExecuteNonQuery($@"INSERT INTO Production_Schedule_Detail (ScheduleId,ProductCode,BOMVersion,PlanDate,DailyOutput)
VALUES ({scheduleId},'{Sql(productCode)}','{Sql(bomVersion)}','{date}',{outDec})");
}
}
}
/// <summary>
/// 删除排程
/// </summary>
[HttpPost]
public IHttpActionResult DeleteSchedule([FromBody] JObject obj)
{
try
{
// 支持单个 Id 或批量 Ids
var idList = new List<int>();
if (obj["Ids"] != null && obj["Ids"].Type == JTokenType.Array)
{
foreach (var t in obj["Ids"]) idList.Add(t.ToString().ToInt());
}
else if (obj["Id"] != null)
{
int single = obj["Id"].ToStringEx().ToInt();
if (single > 0) idList.Add(single);
}
if (idList.Count == 0) return Ok(new { success = false, message = "未指定要删除的排程" });
foreach (int id in idList)
{
// 删除前先取该排程的产品类型,用于从交付记录的ProductTypes中移除
DataTable schDt = db.ExecuteTable($"SELECT ISNULL(ProductType,N'') AS ProductType FROM Production_Schedule WHERE Id={id}");
string productType = schDt.Rows.Count > 0 ? schDt.Rows[0]["ProductType"].ToStringEx().Trim() : "";
db.ExecuteNonQuery($"DELETE FROM Production_Schedule WHERE Id={id}");
db.ExecuteNonQuery($"DELETE FROM Production_Schedule_Detail WHERE ScheduleId={id}");
// 该排程涉及的子项物料
DataTable mats = db.ExecuteTable($"SELECT DISTINCT ChildMaterialId FROM Material_Requirement WHERE ScheduleId={id}");
db.ExecuteNonQuery($"DELETE FROM Material_Requirement WHERE ScheduleId={id}");
// 全局物料维度:交付记录保留,但需重算风险/停线日;无任何其他排程引用的物料可清理
foreach (DataRow m in mats.Rows)
{
string childId = m["ChildMaterialId"].ToStringEx();
bool stillUsed = Int(db.ExecuteTable(
$"SELECT COUNT(*) AS CNT FROM Material_Requirement WHERE ChildMaterialId='{Sql(childId)}'").Rows[0]["CNT"]) > 0;
if (!stillUsed)
{
db.ExecuteNonQuery($"DELETE FROM Arrival_Plan WHERE DeliveryId IN (SELECT Id FROM Material_Delivery WHERE ChildMaterialId='{Sql(childId)}')");
db.ExecuteNonQuery($"DELETE FROM Material_Delivery WHERE ChildMaterialId='{Sql(childId)}'");
}
else if (!string.IsNullOrEmpty(productType))
{
// 从 ProductTypes 中移除本排程的产品类型
DataTable dDt = db.ExecuteTable($"SELECT Id, ProductTypes FROM Material_Delivery WHERE ChildMaterialId='{Sql(childId)}'");
if (dDt.Rows.Count > 0)
{
var typeSet = new HashSet<string>(dDt.Rows[0]["ProductTypes"].ToStringEx().Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
typeSet.Remove(productType);
db.ExecuteNonQuery($"UPDATE Material_Delivery SET ProductTypes=N'{Sql(string.Join(",", typeSet))}', UpdateTime=GETDATE() WHERE Id={Int(dDt.Rows[0]["Id"])}");
}
}
}
}
return Ok(new { success = true, message = "删除成功" });
}
catch (Exception ex)
{
Logger.Error("DeleteSchedule异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取排程看板数据(所有排程的每日产量,按排程聚合)
/// </summary>
[HttpGet]
public IHttpActionResult GetScheduleBoard()
{
try
{
// 全局日期范围
DataTable dtRange = db.ExecuteTable("SELECT MIN(PlanStartDate) AS StartD, MAX(PlanEndDate) AS EndD FROM Production_Schedule");
string start = dtRange.Rows.Count > 0 ? DateStr(dtRange.Rows[0]["StartD"]) : "";
string end = dtRange.Rows.Count > 0 ? DateStr(dtRange.Rows[0]["EndD"]) : "";
// 所有排程 + 每日产量明细
DataTable dt = db.ExecuteTable($@"
SELECT S.Id, S.ProductCode, S.BOMVersion, S.Status, M.ProductName, S.PlanStartDate, S.PlanEndDate,
ISNULL(S.ProductType,N'') AS ProductType,
S.Factory, S.Region, S.Country,
D.PlanDate, D.DailyOutput
FROM Production_Schedule S
LEFT JOIN Product_BOM_Master M ON S.ProductCode=M.ProductCode AND S.BOMVersion=M.BOMVersion
LEFT JOIN Production_Schedule_Detail D ON S.Id=D.ScheduleId
ORDER BY S.PlanStartDate, S.Id, D.PlanDate");
// 按排程聚合
Dictionary<int, Dictionary<string, object>> map = new Dictionary<int, Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
int sid = Int(r["Id"]);
if (!map.ContainsKey(sid))
{
int bomExpand = Int(db.ExecuteTable($"SELECT COUNT(*) AS CNT FROM Product_BOM_Detail WHERE ProductCode='{Sql(r["ProductCode"].ToStringEx())}' AND BOMVersion='{Sql(r["BOMVersion"].ToStringEx())}'").Rows[0]["CNT"]);
map[sid] = new Dictionary<string, object>
{
["Id"] = sid,
["ProductCode"] = r["ProductCode"].ToStringEx(),
["BOMVersion"] = r["BOMVersion"].ToStringEx(),
["ProductName"] = r["ProductName"].ToStringEx(),
["ProductType"] = r["ProductType"].ToStringEx(),
["Status"] = r["Status"].ToStringEx(),
["Factory"] = r["Factory"].ToStringEx(),
["Region"] = r["Region"].ToStringEx(),
["Country"] = r["Country"].ToStringEx(),
["PlanStartDate"] = DateStr(r["PlanStartDate"]),
["PlanEndDate"] = DateStr(r["PlanEndDate"]),
["BOMExpandCount"] = bomExpand,
["TotalOutput"] = 0m,
["Daily"] = new List<Dictionary<string, object>>()
};
}
var row = map[sid];
if (r["PlanDate"] != System.DBNull.Value)
{
decimal outDec = Dec(r["DailyOutput"]);
var dailyList = (List<Dictionary<string, object>>)row["Daily"];
dailyList.Add(new Dictionary<string, object>
{
["PlanDate"] = DateStr(r["PlanDate"]),
["DailyOutput"] = NumStr(outDec)
});
row["TotalOutput"] = (decimal)row["TotalOutput"] + outDec;
}
}
return Ok(new { success = true, data = map.Values.ToList(), startDate = start, endDate = end });
}
catch (Exception ex)
{
Logger.Error("GetScheduleBoard异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取排程甘特图数据(所有排程的每日产量)
/// </summary>
[HttpGet]
public IHttpActionResult GetScheduleGantt()
{
try
{
// 获取全局日期范围
DataTable dtRange = db.ExecuteTable("SELECT MIN(PlanStartDate) AS StartD, MAX(PlanEndDate) AS EndD FROM Production_Schedule");
string start = dtRange.Rows.Count > 0 ? DateStr(dtRange.Rows[0]["StartD"]) : "";
string end = dtRange.Rows.Count > 0 ? DateStr(dtRange.Rows[0]["EndD"]) : "";
DataTable dt = db.ExecuteTable(@"
SELECT S.Id, S.ProductCode, S.BOMVersion, S.Status, M.ProductName, S.PlanStartDate, S.PlanEndDate,
D.PlanDate, D.DailyOutput
FROM Production_Schedule S
LEFT JOIN Product_BOM_Master M ON S.ProductCode=M.ProductCode AND S.BOMVersion=M.BOMVersion
LEFT JOIN Production_Schedule_Detail D ON S.Id=D.ScheduleId
ORDER BY S.PlanStartDate, S.Id, D.PlanDate");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["Id"] = r["Id"].ToStringEx(),
["ProductCode"] = r["ProductCode"].ToStringEx(),
["BOMVersion"] = r["BOMVersion"].ToStringEx(),
["ProductName"] = r["ProductName"].ToStringEx(),
["Status"] = r["Status"].ToStringEx(),
["PlanStartDate"] = DateStr(r["PlanStartDate"]),
["PlanEndDate"] = DateStr(r["PlanEndDate"]),
["PlanDate"] = DateStr(r["PlanDate"]),
["DailyOutput"] = NumStr(r["DailyOutput"])
});
}
return Ok(new { success = true, data = list, startDate = start, endDate = end });
}
catch (Exception ex)
{
Logger.Error("GetScheduleGantt异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region ===== BOM展开与需求计算 =====
/// <summary>
/// 重新计算某排程的物料需求(BOM展开 × 每日产量)
/// </summary>
private void RecalcScheduleRequirement(int scheduleId, string productCode, string bomVersion, RecalcProgress p = null)
{
void SetStage(string stage, int percent)
{
if (p != null) { p.Stage = stage; p.Percent = percent; }
}
// 删除旧需求明细
db.ExecuteNonQuery($"DELETE FROM Material_Requirement WHERE ScheduleId={scheduleId}");
// 获取该产品BOM明细(已展开)
SetStage("正在读取BOM明细...", 58);
DataTable bom = db.ExecuteTable($@"
SELECT ParentMaterialId, ChildMaterialId, UsageQty, Level, MaterialName, Spec, Unit, IsVirtual
FROM Product_BOM_Detail
WHERE ProductCode='{Sql(productCode)}' AND BOMVersion='{Sql(bomVersion)}'");
if (bom.Rows.Count == 0) { SetStage("无BOM明细", 100); return; }
// 获取每日产量
DataTable daily = db.ExecuteTable($"SELECT PlanDate, DailyOutput FROM Production_Schedule_Detail WHERE ScheduleId={scheduleId}");
if (daily.Rows.Count == 0) { SetStage("无每日产量", 100); return; }
// 计算每个子物料的总用量(同物料在BOM多个位置出现时,所有路径用量连乘后累加)
SetStage("正在计算物料总用量...", 62);
Dictionary<string, decimal> totalUsageMap = new Dictionary<string, decimal>();
BuildTotalUsage(productCode, bomVersion, 1m, totalUsageMap, new List<string>());
// 按物料聚合基础信息(同一物料在BOM多个位置出现时合并为一条需求,层级取最大与交付表一致)
// 虚拟件/配置件(IsVirtual=1)不参与需求计算,跳过
// 同时构建一个"非虚拟件 childId 集合",作为 BuildTotalUsage 的下级递归白名单,
// 防止虚拟件即使被递归遍历到,也不会有孙辈被误用 productCode 起步直接命中
Dictionary<string, DataRow> matInfo = new Dictionary<string, DataRow>();
Dictionary<string, int> matLevel = new Dictionary<string, int>();
HashSet<string> realChildIds = new HashSet<string>();
foreach (DataRow br in bom.Rows)
{
string cId = br["ChildMaterialId"].ToStringEx();
// IsVirtual 是 SQL Server BIT 列,读出来是 Boolean;
// 不能用 ToInt()(会把 "True" 解析失败返回0),必须用 Convert.ToInt32
int isVirtual = Convert.ToInt32(br["IsVirtual"]);
if (isVirtual == 1) continue; // 虚拟件/配置件不参与需求计算
realChildIds.Add(cId);
int lv = Int(br["Level"]);
if (!matInfo.ContainsKey(cId))
{
matInfo[cId] = br;
matLevel[cId] = lv;
}
else if (lv > matLevel[cId])
{
matLevel[cId] = lv;
}
}
// 按天×物料写入需求明细(每物料每天一条,用量为多路径累加的总用量)
List<string> insertSql = new List<string>();
foreach (DataRow dr in daily.Rows)
{
string planDate = DateStr(dr["PlanDate"]);
decimal dailyOutput = Dec(dr["DailyOutput"]);
if (dailyOutput <= 0) continue;
foreach (var kv in matInfo)
{
string childId = kv.Key;
DataRow br = kv.Value;
string matName = br["MaterialName"].ToStringEx();
string spec = br["Spec"].ToStringEx();
string unit = br["Unit"].ToStringEx();
int level = matLevel[childId];
decimal totalUsage = totalUsageMap.ContainsKey(childId) ? totalUsageMap[childId] : 0;
if (totalUsage <= 0) continue;
decimal req = dailyOutput * totalUsage;
insertSql.Add($@"INSERT INTO Material_Requirement (ScheduleId,ProductCode,BOMVersion,ChildMaterialId,MaterialName,Spec,Unit,BOMLevel,BOMUsageQty,PlanDate,DailyRequirement)
VALUES ({scheduleId},'{Sql(productCode)}','{Sql(bomVersion)}','{Sql(childId)}',N'{Sql(matName)}',N'{Sql(spec)}','{Sql(unit)}',{level},{totalUsage},'{planDate}',{req})");
}
}
if (insertSql.Count > 0)
db.ExecuteNonQuery(string.Join(";", insertSql));
// 只重算本排程的物料需求;物料交付/库存/停线日由物料交付页"重算交付数据"统一执行
SetStage("完成", 100);
}
/// <summary>
/// 从金蝶即时库存按仓库分组带出震泽/晋江/资阳库存,并从生产交付表取线边仓库存(LineSideStock),全量物料维度。
/// 同时统计:已收货数量(ReceivedQty = 收料通知单实收 - 采购入库单实收 - 采购退料单实退,上月1号至今;入库/退料单按明细源单编号关联收料通知单号并按物料匹配)、量产在途(MassInTransit)、研发在途(ResearchInTransit)。
/// 已收货计入可用库存,参与预计停线日/交付风险计算。
/// 在途 = 采购申请单剩余(申请量-已下PO量) + 采购订单未收货(订单量-收料已收货),均来自金蝶(今年已审核)。
/// 震泽:CK059/CK031;晋江:CK005/CK015;资阳:CK032/CK049
/// </summary>
private void AutoSyncErpStockGlobal()
{
int maxRetry = 3;
for (int attempt = 1; attempt <= maxRetry; attempt++)
{
try
{
AutoSyncErpStockGlobalCore();
if (attempt > 1) Logger.Info($"AutoSyncErpStockGlobal 第{attempt}次重试成功");
return;
}
catch (System.Data.SqlClient.SqlException ex) when (ex.Number == 1205 || ex.Number == 1222)
{
Logger.Warn($"AutoSyncErpStockGlobal 死锁/超时(attempt={attempt}/3): {ex.Message}");
if (attempt == maxRetry) throw;
System.Threading.Thread.Sleep(200 * attempt); // 200ms, 400ms
}
}
}
private void AutoSyncErpStockGlobalCore()
{
// 上月1号(收料统计起始日)
DateTime firstOfLastMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1);
string recvStart = firstOfLastMonth.ToString("yyyy-MM-dd");
// 今年1月1日(在途统计起始日)
string yearStart = new DateTime(DateTime.Now.Year, 1, 1).ToString("yyyy-MM-dd");
// 优化:把每条 Material_Delivery 都跑 6 次 OUTER APPLY(1197×6=7182 次查询),
// 改为先在临时表里按物料+维度聚合好库存/在途/供应商,UPDATE 时只 JOIN 一次。
// 临时表幂等:每次重算时 DROP/重建。
string sql = $@"
-- 1) 三地库存(按物料+仓库组合聚合)
IF OBJECT_ID('tempdb..#StockByMat') IS NOT NULL DROP TABLE #StockByMat;
SELECT M.FNUMBER AS MaterialCode,
SUM(CASE WHEN S.FNUMBER IN ('CK059','CK031') THEN ISNULL(I.FQTY,0) ELSE 0 END) AS ZhenZeStock,
SUM(CASE WHEN S.FNUMBER IN ('CK005','CK015') THEN ISNULL(I.FQTY,0) ELSE 0 END) AS JinJiangStock,
SUM(CASE WHEN S.FNUMBER IN ('CK032','CK049') THEN ISNULL(I.FQTY,0) ELSE 0 END) AS ZiYangStock
INTO #StockByMat
FROM T_BD_MATERIAL M
INNER JOIN T_STK_INVENTORY I ON M.FINTERID=I.FMATERIALID
INNER JOIN T_BD_STOCK S ON I.FSTOCKID=S.FINTERID
WHERE M.FDELETED=0 AND S.FNUMBER IN ('CK059','CK031','CK005','CK015','CK032','CK049')
GROUP BY M.FNUMBER;
CREATE CLUSTERED INDEX IX_StockByMat ON #StockByMat(MaterialCode);
-- 2) 收料(按物料聚合,限定起始日)
IF OBJECT_ID('tempdb..#RecvByMat') IS NOT NULL DROP TABLE #RecvByMat;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(E.FACTRECEIVEQTY,0)) AS ReceivedQty
INTO #RecvByMat
FROM T_PUR_RECEIVEENTRY E
INNER JOIN T_PUR_RECEIVE H ON E.FINTERID=H.FINTERID
INNER JOIN T_BD_MATERIAL M ON E.FMATERIALID=M.FINTERID
WHERE H.FDELETED=0 AND H.FDATE>='{recvStart}'
GROUP BY M.FNUMBER;
CREATE CLUSTERED INDEX IX_RecvByMat ON #RecvByMat(MaterialCode);
-- 2.1) 采购入库单实收(明细源单编号=收料通知单号,按物料聚合),从已收货中扣减
IF OBJECT_ID('tempdb..#InStockByMat') IS NOT NULL DROP TABLE #InStockByMat;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(E.FREALQTY,0)) AS InStockQty
INTO #InStockByMat
FROM T_STK_INSTOCKENTRY E
INNER JOIN T_PUR_RECEIVE H ON E.FSRCBILLNO=H.FBILLNO
INNER JOIN T_BD_MATERIAL M ON E.FMATERIALID=M.FINTERID
WHERE H.FDELETED=0 AND H.FDATE>='{recvStart}'
GROUP BY M.FNUMBER;
CREATE CLUSTERED INDEX IX_InStockByMat ON #InStockByMat(MaterialCode);
-- 2.2) 采购退料单实退(明细源单编号=收料通知单号,按物料聚合),从已收货中扣减
IF OBJECT_ID('tempdb..#MrbByMat') IS NOT NULL DROP TABLE #MrbByMat;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(E.FRMREALQTY,0)) AS MrbQty
INTO #MrbByMat
FROM T_PUR_MRBENTRY E
INNER JOIN T_PUR_RECEIVE H ON E.FSRCBILLNO=H.FBILLNO
INNER JOIN T_BD_MATERIAL M ON E.FMATERIALID=M.FINTERID
WHERE H.FDELETED=0 AND H.FDATE>='{recvStart}'
GROUP BY M.FNUMBER;
CREATE CLUSTERED INDEX IX_MrbByMat ON #MrbByMat(MaterialCode);
-- 2.3) 最终已收货 = 收料实收 - 入库实收 - 退料实退(按物料聚合,作为唯一口径供 UPDATE 复用)
IF OBJECT_ID('tempdb..#ReceivedFinal') IS NOT NULL DROP TABLE #ReceivedFinal;
SELECT
ISNULL(RV.MaterialCode, ISNULL(INST.MaterialCode, MRB.MaterialCode)) AS MaterialCode,
ISNULL(RV.ReceivedQty,0) - ISNULL(INST.InStockQty,0) - ISNULL(MRB.MrbQty,0) AS ReceivedQty
INTO #ReceivedFinal
FROM #RecvByMat RV
FULL OUTER JOIN #InStockByMat INST ON RV.MaterialCode=INST.MaterialCode
FULL OUTER JOIN #MrbByMat MRB ON ISNULL(RV.MaterialCode,INST.MaterialCode)=MRB.MaterialCode;
CREATE CLUSTERED INDEX IX_ReceivedFinal ON #ReceivedFinal(MaterialCode);
-- 3) 量产在途:分两部分聚合(申请单剩余 + PO未收)
-- 3.1 申请单聚合(量产=CGSQD01/20)
IF OBJECT_ID('tempdb..#ReqMass') IS NOT NULL DROP TABLE #ReqMass;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(S1.FQTY,0)) AS ReqQty
INTO #ReqMass
FROM P_PUR_Requisition S
INNER JOIN P_PUR_RequisitionENTRY S1 ON S.FINTERID=S1.FINTERID
INNER JOIN T_BAS_BILLTYPE S2 ON S.FBILLTYPEID=S2.FINTERID
INNER JOIN T_BD_MATERIAL M ON S1.FMATERIALID=M.FINTERID
WHERE S.FSTATUS='40' AND S.FDATE>='{yearStart}' AND S.FDELETED=0
AND S2.FNUMBER IN ('CGSQD01_SYS','CGSQD20_SYS')
GROUP BY M.FNUMBER;
CREATE CLUSTERED INDEX IX_ReqMass ON #ReqMass(MaterialCode);
-- 3.2 量产PO(CGDD01/02/21排除GSSH)
IF OBJECT_ID('tempdb..#PoMass') IS NOT NULL DROP TABLE #PoMass;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(S1.FQTY,0)) AS PoQty
INTO #PoMass
FROM T_PUR_POORDER S
INNER JOIN T_PUR_POORDERENTRY S1 ON S.FINTERID=S1.FINTERID
INNER JOIN T_BAS_BILLTYPE S2 ON S.FBILLTYPEID=S2.FINTERID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY S3 ON S1.F_RESEARCHPROJECT=S3.FINTERID
INNER JOIN T_BD_MATERIAL M ON S1.FMATERIALID=M.FINTERID
WHERE S.FSTATUS='40' AND S.FDATE>='{yearStart}' AND S.FDELETED=0
AND S2.FNUMBER IN ('CGDD01_SYS','CGDD02_SYS','CGDD21_SYS') AND ISNULL(S3.FNUMBER,'')<>'GSSH'
GROUP BY M.FNUMBER;
CREATE CLUSTERED INDEX IX_PoMass ON #PoMass(MaterialCode);
-- 3.3 量产收料(关联量产PO单号,限定起始日)
IF OBJECT_ID('tempdb..#RecvMass') IS NOT NULL DROP TABLE #RecvMass;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(E.FACTRECEIVEQTY,0)) AS RecvQty
INTO #RecvMass
FROM T_PUR_RECEIVEENTRY E
INNER JOIN T_PUR_RECEIVE H ON E.FINTERID=H.FINTERID
INNER JOIN T_BD_MATERIAL M ON E.FMATERIALID=M.FINTERID
WHERE H.FDELETED=0 AND M.FNUMBER IS NOT NULL
AND EXISTS (
SELECT 1 FROM T_PUR_POORDER S
INNER JOIN T_PUR_POORDERENTRY S1 ON S.FINTERID=S1.FINTERID
INNER JOIN T_BAS_BILLTYPE S2 ON S.FBILLTYPEID=S2.FINTERID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY S3 ON S1.F_RESEARCHPROJECT=S3.FINTERID
WHERE S.FBILLNO=E.FORDERBILLNO AND S.FSTATUS='40' AND S.FDATE>='{yearStart}'
AND S2.FNUMBER IN ('CGDD01_SYS','CGDD02_SYS','CGDD21_SYS') AND ISNULL(S3.FNUMBER,'')<>'GSSH'
)
GROUP BY M.FNUMBER;
CREATE CLUSTERED INDEX IX_RecvMass ON #RecvMass(MaterialCode);
-- 3.4 量产在途最终聚合:MAX(0, 申请-PO) + MAX(0, PO-收料)
IF OBJECT_ID('tempdb..#MassTransit') IS NOT NULL DROP TABLE #MassTransit;
SELECT
ISNULL(a.MaterialCode,b.MaterialCode) AS MaterialCode,
ISNULL(CASE WHEN a.ReqQty-b.PoQty>0 THEN a.ReqQty-b.PoQty ELSE 0 END,0)
+ ISNULL(CASE WHEN b.PoQty-c.RecvQty>0 THEN b.PoQty-c.RecvQty ELSE 0 END,0) AS MassInTransit
INTO #MassTransit
FROM #ReqMass a
FULL OUTER JOIN #PoMass b ON a.MaterialCode=b.MaterialCode
FULL OUTER JOIN #RecvMass c ON ISNULL(a.MaterialCode,b.MaterialCode)=c.MaterialCode;
CREATE CLUSTERED INDEX IX_MassTransit ON #MassTransit(MaterialCode);
-- 4) 研发在途:CGSQD07 + CGDD11
IF OBJECT_ID('tempdb..#ReqRes') IS NOT NULL DROP TABLE #ReqRes;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(S1.FQTY,0)) AS ReqQty
INTO #ReqRes
FROM P_PUR_Requisition S
INNER JOIN P_PUR_RequisitionENTRY S1 ON S.FINTERID=S1.FINTERID
INNER JOIN T_BAS_BILLTYPE S2 ON S.FBILLTYPEID=S2.FINTERID
INNER JOIN T_BD_MATERIAL M ON S1.FMATERIALID=M.FINTERID
WHERE S.FSTATUS='40' AND S.FDATE>='{yearStart}' AND S.FDELETED=0
AND S2.FNUMBER='CGSQD07_SYS'
GROUP BY M.FNUMBER;
IF OBJECT_ID('tempdb..#PoRes') IS NOT NULL DROP TABLE #PoRes;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(S1.FQTY,0)) AS PoQty
INTO #PoRes
FROM T_PUR_POORDER S
INNER JOIN T_PUR_POORDERENTRY S1 ON S.FINTERID=S1.FINTERID
INNER JOIN T_BAS_BILLTYPE S2 ON S.FBILLTYPEID=S2.FINTERID
INNER JOIN T_BD_MATERIAL M ON S1.FMATERIALID=M.FINTERID
WHERE S.FSTATUS='40' AND S.FDATE>='{yearStart}' AND S.FDELETED=0
AND S2.FNUMBER='CGDD11_SYS'
GROUP BY M.FNUMBER;
IF OBJECT_ID('tempdb..#RecvRes') IS NOT NULL DROP TABLE #RecvRes;
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(E.FACTRECEIVEQTY,0)) AS RecvQty
INTO #RecvRes
FROM T_PUR_RECEIVEENTRY E
INNER JOIN T_PUR_RECEIVE H ON E.FINTERID=H.FINTERID
INNER JOIN T_BD_MATERIAL M ON E.FMATERIALID=M.FINTERID
WHERE H.FDELETED=0 AND M.FNUMBER IS NOT NULL
AND EXISTS (
SELECT 1 FROM T_PUR_POORDER S
INNER JOIN T_BAS_BILLTYPE S2 ON S.FBILLTYPEID=S2.FINTERID
WHERE S.FBILLNO=E.FORDERBILLNO AND S.FSTATUS='40' AND S.FDATE>='{yearStart}'
AND S2.FNUMBER='CGDD11_SYS'
)
GROUP BY M.FNUMBER;
IF OBJECT_ID('tempdb..#ResTransit') IS NOT NULL DROP TABLE #ResTransit;
SELECT
ISNULL(a.MaterialCode,b.MaterialCode) AS MaterialCode,
ISNULL(CASE WHEN a.ReqQty-b.PoQty>0 THEN a.ReqQty-b.PoQty ELSE 0 END,0)
+ ISNULL(CASE WHEN b.PoQty-c.RecvQty>0 THEN b.PoQty-c.RecvQty ELSE 0 END,0) AS ResearchInTransit
INTO #ResTransit
FROM #ReqRes a
FULL OUTER JOIN #PoRes b ON a.MaterialCode=b.MaterialCode
FULL OUTER JOIN #RecvRes c ON ISNULL(a.MaterialCode,b.MaterialCode)=c.MaterialCode;
CREATE CLUSTERED INDEX IX_ResTransit ON #ResTransit(MaterialCode);
-- 5) 供应商/采购员聚合(共用同一份PO明细)
IF OBJECT_ID('tempdb..#PoPerson') IS NOT NULL DROP TABLE #PoPerson;
SELECT M.FNUMBER AS MaterialCode, S1.FNAME AS SupplierName, S2.FNAME AS PurchaserName
INTO #PoPerson
FROM T_PUR_POORDER S
INNER JOIN T_PUR_POORDERENTRY SE ON S.FINTERID=SE.FINTERID
INNER JOIN T_BD_MATERIAL M ON SE.FMATERIALID=M.FINTERID
INNER JOIN T_BAS_BILLTYPE BT ON S.FBILLTYPEID=BT.FINTERID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY S3 ON SE.F_RESEARCHPROJECT=S3.FINTERID
LEFT JOIN T_BD_SUPPLIER S1 ON S.FSUPPLYID=S1.FINTERID
LEFT JOIN T_BD_OPERATORENTRY S2 ON S.FPURCHASERID=S2.FDETAILID
WHERE S.FSTATUS='40' AND S.FDATE>='{yearStart}'
AND BT.FNUMBER IN ('CGDD01_SYS','CGDD02_SYS','CGDD21_SYS','CGDD11_SYS')
AND (BT.FNUMBER='CGDD11_SYS' OR ISNULL(S3.FNUMBER,'')<>'GSSH')
AND (S1.FNAME IS NOT NULL OR S2.FNAME IS NOT NULL);
CREATE CLUSTERED INDEX IX_PoPerson ON #PoPerson(MaterialCode);
IF OBJECT_ID('tempdb..#SupByMat') IS NOT NULL DROP TABLE #SupByMat;
SELECT MaterialCode,
(SELECT STUFF((SELECT ','+SupplierName FROM #PoPerson p2 WHERE p2.MaterialCode=p1.MaterialCode AND p2.SupplierName IS NOT NULL GROUP BY SupplierName ORDER BY SupplierName FOR XML PATH('')),1,1,'')) AS Supplier,
(SELECT STUFF((SELECT ','+PurchaserName FROM #PoPerson p2 WHERE p2.MaterialCode=p1.MaterialCode AND p2.PurchaserName IS NOT NULL GROUP BY PurchaserName ORDER BY PurchaserName FOR XML PATH('')),1,1,'')) AS Purchaser
INTO #SupByMat
FROM (SELECT DISTINCT MaterialCode FROM #PoPerson) p1;
CREATE CLUSTERED INDEX IX_SupByMat ON #SupByMat(MaterialCode);
-- 6) 一次 UPDATE:JOIN 所有临时表
UPDATE D SET
D.ZhenZeStock = ISNULL(K.ZhenZeStock,0),
D.JinJiangStock = ISNULL(K.JinJiangStock,0),
D.ZiYangStock = ISNULL(K.ZiYangStock,0),
D.LineSideStock = ISNULL((SELECT TOP 1 LineSideStock FROM Production_Delivery P WHERE P.MaterialCode=D.ChildMaterialId),0),
D.ERPStockQty = ISNULL(K.ZhenZeStock,0) + ISNULL(K.JinJiangStock,0) + ISNULL(K.ZiYangStock,0),
D.MassInTransit = ISNULL(MT.MassInTransit,0),
D.ResearchInTransit = ISNULL(RT.ResearchInTransit,0),
D.InTransitQty = ISNULL(MT.MassInTransit,0) + ISNULL(RT.ResearchInTransit,0),
D.AvailableStock = ISNULL(K.ZhenZeStock,0) + ISNULL(K.JinJiangStock,0) + ISNULL(K.ZiYangStock,0)
+ ISNULL(MT.MassInTransit,0) + ISNULL(RT.ResearchInTransit,0)
+ ISNULL((SELECT TOP 1 LineSideStock FROM Production_Delivery P WHERE P.MaterialCode=D.ChildMaterialId),0)
+ ISNULL(RF.ReceivedQty,0),
D.ReceivedQty = ISNULL(RF.ReceivedQty,0),
D.Supplier = LEFT(ISNULL(SP.Supplier,N''),400),
D.Purchaser = LEFT(ISNULL(SP.Purchaser,N''),100),
D.UpdateTime = GETDATE()
FROM Material_Delivery D
LEFT JOIN #StockByMat K ON K.MaterialCode=D.ChildMaterialId
LEFT JOIN #ReceivedFinal RF ON RF.MaterialCode=D.ChildMaterialId
LEFT JOIN #MassTransit MT ON MT.MaterialCode=D.ChildMaterialId
LEFT JOIN #ResTransit RT ON RT.MaterialCode=D.ChildMaterialId
LEFT JOIN #SupByMat SP ON SP.MaterialCode=D.ChildMaterialId;
";
db.ExecuteNonQuery(sql);
}
/// <summary>
/// 自动计算每个子物料的预计停线日(全量物料维度)。
/// 需求:汇总所有生产排程对该物料的每日需求(只统计明细日期>=当天的产量,按物料+日期累计)。
/// 逻辑:从当天开始逐日累加(可用库存 + 当日到货 - 当日需求),库存<0的首日即为预计停线日。
/// 可用库存 = 三地库存 + 量产在途 + 研发在途 + 线边库存 + 已收货(ReceivedQty)。
/// </summary>
private void CalcStopDateGlobal()
{
// 全部交付记录
DataTable deliv = db.ExecuteTable($@"
SELECT D.Id, D.ChildMaterialId, D.AvailableStock, D.ExpectedArrivalDate
FROM Material_Delivery D");
// 日期窗口:从当天开始 ~ 所有排程最晚完成日
DataTable winDt = db.ExecuteTable(@"
SELECT MAX(PlanEndDate) AS WinEnd FROM Production_Schedule");
DateTime winStart = DateTime.Now.Date;
DateTime winEnd = GetDate(winDt.Rows[0]["WinEnd"]);
if (winEnd < winStart) winEnd = winStart;
foreach (DataRow dRow in deliv.Rows)
{
int dId = Int(dRow["Id"]);
string childId = dRow["ChildMaterialId"].ToStringEx();
decimal stock = Dec(dRow["AvailableStock"]); // 可用库存=三地+在途+线边+已收货
// 跨排程汇总该物料的每日需求(只统计明细日期>=当天的产量)
DataTable req = db.ExecuteTable($@"
SELECT PlanDate, SUM(DailyRequirement) AS DailyRequirement FROM Material_Requirement
WHERE ChildMaterialId='{Sql(childId)}' AND PlanDate>='{winStart:yyyy-MM-dd}'
GROUP BY PlanDate ORDER BY PlanDate");
Dictionary<string, decimal> reqMap = new Dictionary<string, decimal>();
foreach (DataRow rr in req.Rows) reqMap[DateStr(rr["PlanDate"])] = Dec(rr["DailyRequirement"]);
// 获取到货计划(预计到货日 -> 到货数量)
DataTable arrival = db.ExecuteTable($@"
SELECT ExpectedArrivalDate, ArrivalQty FROM Arrival_Plan
WHERE DeliveryId={dId} AND ExpectedArrivalDate IS NOT NULL");
Dictionary<string, decimal> arrivalMap = new Dictionary<string, decimal>();
foreach (DataRow ar in arrival.Rows)
{
string ad = DateStr(ar["ExpectedArrivalDate"]);
if (arrivalMap.ContainsKey(ad)) arrivalMap[ad] += Dec(ar["ArrivalQty"]);
else arrivalMap[ad] = Dec(ar["ArrivalQty"]);
}
// 逐日累加,找出库存<0的首日
string stopDate = "";
decimal running = stock;
DateTime cursor = winStart;
while (cursor <= winEnd)
{
string ds = cursor.ToString("yyyy-MM-dd");
decimal dayReq = reqMap.ContainsKey(ds) ? reqMap[ds] : 0;
decimal dayArrival = arrivalMap.ContainsKey(ds) ? arrivalMap[ds] : 0;
running = running + dayArrival - dayReq;
if (running < 0)
{
stopDate = ds;
break;
}
cursor = cursor.AddDays(1);
}
string setStop = string.IsNullOrEmpty(stopDate) ? "NULL" : $"'{stopDate}'";
db.ExecuteNonQuery($"UPDATE Material_Delivery SET ExpectedStopDate={setStop}, UpdateTime=GETDATE() WHERE Id={dId}");
}
// 停线日算完后,批量重判交付风险:断线(到货晚于停线/有停线无到货) / 预警(到货=停线) / 安全(到货早于停线或不停线)
db.ExecuteNonQuery($@"
UPDATE Material_Delivery SET
DeliveryRisk = CASE
WHEN ExpectedStopDate IS NULL THEN N'安全'
WHEN ExpectedArrivalDate IS NULL THEN N'断线'
WHEN ExpectedArrivalDate > ExpectedStopDate THEN N'断线'
WHEN ExpectedArrivalDate = ExpectedStopDate THEN N'预警'
ELSE N'安全'
END,
UpdateTime=GETDATE()");
}
/// <summary>
/// 解析日期
/// </summary>
private DateTime GetDate(object o)
{
DateTime d;
return DateTime.TryParse(o?.ToString(), out d) ? d : DateTime.MinValue;
}
/// <summary>
/// 构建BOM树中每个子物料相对根成品的总用量(同物料多路径用量累加)。
/// path 为当前遍历路径上的物料编码(按路径防环,防止循环引用BOM)。
/// </summary>
private void BuildTotalUsage(string parentId, string bomVersion, decimal pathUsage,
Dictionary<string, decimal> map, List<string> path)
{
if (path.Count > 20) return; // 防止过深
DataTable dt = db.ExecuteTable($@"
SELECT ChildMaterialId, UsageQty, IsVirtual FROM Product_BOM_Detail
WHERE ParentMaterialId='{Sql(parentId)}' AND BOMVersion='{Sql(bomVersion)}'");
foreach (DataRow r in dt.Rows)
{
string childId = r["ChildMaterialId"].ToStringEx();
// IsVirtual 是 SQL Server BIT 列,读出来是 Boolean;
// 不能用 ToInt()(会把 "True" 解析失败返回0),必须用 Convert.ToInt32
int isVirtual = Convert.ToInt32(r["IsVirtual"]);
// 防环:子物料已在当前路径上(循环引用),跳过
if (path.Contains(childId)) continue;
decimal usage = Dec(r["UsageQty"]);
decimal total = pathUsage * usage;
// 虚拟件/配置件不参与需求计算(不写入 Material_Requirement),
// 但用量正常累乘,继续递归展开下级
if (isVirtual == 0)
{
if (map.ContainsKey(childId))
map[childId] += total; // 同物料多路径累加
else
map[childId] = total;
}
List<string> newPath = new List<string>(path);
newPath.Add(childId);
BuildTotalUsage(childId, bomVersion, total, map, newPath);
}
}
/// <summary>
/// 从需求明细同步交付主表(全局物料维度):
/// 交付记录不再按排程/产品维度拆分,而是按子项物料汇总——所有生产排程的子项物料合并到同一条交付记录上。
/// ProductTypes 字段累计该物料关联的所有产品类型(来自各生产排程的产品类型维护值)。
/// </summary>
private void SyncDeliveryFromRequirement(int scheduleId, string productCode, string bomVersion)
{
// 先清空该排程的旧交付记录(避免存量虚拟件/配置件残留)
db.ExecuteNonQuery($"DELETE FROM Material_Delivery WHERE ScheduleId={scheduleId}");
// 本排程涉及的全部子项物料(含层级、名称)
DataTable req = db.ExecuteTable($@"
SELECT DISTINCT ChildMaterialId, MAX(MaterialName) AS MaterialName, MAX(BOMLevel) AS BOMLevel
FROM Material_Requirement WHERE ScheduleId={scheduleId}
GROUP BY ChildMaterialId");
// 本排程产品的产品类型(用于累加进 ProductTypes)
DataTable schDt = db.ExecuteTable($"SELECT ISNULL(ProductType,N'') AS ProductType FROM Production_Schedule WHERE Id={scheduleId}");
string productType = schDt.Rows.Count > 0 ? schDt.Rows[0]["ProductType"].ToStringEx().Trim() : "";
int ok = 0, fail = 0;
foreach (DataRow r in req.Rows)
{
string childId = r["ChildMaterialId"].ToStringEx();
string matName = r["MaterialName"].ToStringEx();
int level = Int(r["BOMLevel"]);
try
{
// 按 (ScheduleId, ChildMaterialId) 唯一,不同排程的同名子物料独立成行
DataTable exist = db.ExecuteTable($@"
SELECT TOP 1 Id FROM Material_Delivery
WHERE ScheduleId={scheduleId} AND ChildMaterialId='{Sql(childId)}'");
if (exist.Rows.Count > 0)
{
int dId = Int(exist.Rows[0]["Id"]);
db.ExecuteNonQuery($@"UPDATE Material_Delivery SET
BOMLevel={level}, MaterialName=N'{Sql(matName)}', ProductTypes=N'{Sql(productType)}', UpdateTime=GETDATE()
WHERE Id={dId}");
}
else
{
db.ExecuteNonQuery($@"INSERT INTO Material_Delivery (ScheduleId,ProductCode,BOMVersion,ChildMaterialId,MaterialName,BOMLevel,ProductTypes,DeliveryRisk)
VALUES ({scheduleId},'{Sql(productCode)}','{Sql(bomVersion)}','{Sql(childId)}',N'{Sql(matName)}',{level},N'{Sql(productType)}','安全')");
}
ok++;
}
catch (Exception ex)
{
fail++;
Logger.Error($"SyncDeliveryFromRequirement 失败 ScheduleId={scheduleId} ChildMaterialId='{childId}' MaterialName='{matName}': {ex.Message}");
}
}
Logger.Info($"SyncDeliveryFromRequirement ScheduleId={scheduleId} req={req.Rows.Count} ok={ok} fail={fail}");
}
/// <summary>
/// 手动触发某排程重新展开BOM(异步执行,立即返回;前端轮询GetRecalcProgress查看进度)
/// </summary>
[HttpPost]
public IHttpActionResult RecalcSchedule([FromBody] JObject obj)
{
try
{
int scheduleId = obj["ScheduleId"].ToStringEx().ToInt();
DataTable dt = db.ExecuteTable($"SELECT ProductCode, BOMVersion FROM Production_Schedule WHERE Id={scheduleId}");
if (dt.Rows.Count == 0)
return Ok(new { success = false, message = "排程不存在" });
// 已在重算中则拒绝重复提交(超过30分钟仍为running视为线程已失效,允许重新提交)
if (RecalcProgressMap.TryGetValue(scheduleId, out var running) && running.Status == "running"
&& (DateTime.Now - running.StartTime).TotalMinutes < 30)
return Ok(new { success = false, message = "该排程正在重算中,请稍候" });
string productCode = dt.Rows[0]["ProductCode"].ToStringEx();
string bomVersion = dt.Rows[0]["BOMVersion"].ToStringEx();
// 初始化进度
RecalcProgressMap[scheduleId] = new RecalcProgress
{
ScheduleId = scheduleId,
Percent = 0,
Stage = "准备重算",
Status = "running",
Message = "",
StartTime = DateTime.Now
};
// 后台线程执行重算,立即返回
System.Threading.ThreadPool.QueueUserWorkItem(_ =>
{
try
{
RunRecalc(scheduleId, productCode, bomVersion);
}
catch (Exception ex)
{
Logger.Error("RecalcSchedule后台执行异常:", ex);
if (RecalcProgressMap.TryGetValue(scheduleId, out var p))
{
p.Status = "error";
p.Stage = "重算失败";
p.Message = ex.Message;
}
}
});
return Ok(new { success = true, taskId = scheduleId, message = "已开始重算,请稍候" });
}
catch (Exception ex)
{
Logger.Error("RecalcSchedule异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 查询某排程重算任务的进度(供前端轮询)
/// </summary>
[HttpGet]
public IHttpActionResult GetRecalcProgress(int scheduleId)
{
if (RecalcProgressMap.TryGetValue(scheduleId, out var p))
{
return Ok(new
{
success = true,
scheduleId = scheduleId,
percent = p.Percent,
stage = p.Stage,
status = p.Status,
message = p.Message
});
}
// 任务不存在(可能已完成并清理),返回终态,前端据此停止轮询
return Ok(new
{
success = true,
scheduleId = scheduleId,
percent = 100,
stage = "已完成",
status = "success",
message = "重算任务已完成"
});
}
/// <summary>
/// 后台执行重算:展开BOM -> 重算需求 -> 同步交付,并更新进度
/// </summary>
private void RunRecalc(int scheduleId, string productCode, string bomVersion)
{
var p = RecalcProgressMap[scheduleId];
try
{
// 阶段1:展开BOM
p.Stage = "正在从金蝶展开BOM...";
p.Percent = 5;
p.Message = "正在展开BOM... 已展开 0 条子物料";
string regMsg;
int regResult = EnsureProductRegistered(productCode, bomVersion, out regMsg, p);
if (regResult < 0)
{
p.Status = "error";
p.Stage = "BOM展开失败";
p.Message = regMsg;
return;
}
p.Stage = $"BOM展开完成,共{regResult}条明细";
p.Percent = 55;
// 阶段2:重算需求(内部会继续更新进度)
RecalcScheduleRequirement(scheduleId, productCode, bomVersion, p);
p.Status = "success";
p.Percent = 100;
p.Stage = "完成";
p.Message = $"需求重算完成,BOM展开{regResult}条子物料明细";
}
catch (Exception ex)
{
Logger.Error("RunRecalc异常:", ex);
p.Status = "error";
p.Stage = "重算失败";
p.Message = ex.Message;
}
finally
{
// 延迟清理进度记录,避免内存泄漏(前端轮询到终态后即停止)
System.Threading.Tasks.Task.Delay(TimeSpan.FromMinutes(10)).ContinueWith(t =>
{
RecalcProgressMap.TryRemove(scheduleId, out var removed);
});
}
}
#region ===== 物料交付全局重算 =====
/// <summary>
/// 物料交付全局重算任务进度(key固定为0,单实例)
/// </summary>
private static readonly ConcurrentDictionary<int, RecalcProgress> DeliveryCalcMap = new ConcurrentDictionary<int, RecalcProgress>();
private const int DeliveryCalcKey = 0;
/// <summary>
/// 全局重算物料交付数据(异步执行,立即返回;前端轮询GetDeliveryCalcProgress查看进度)。
/// 流程:收集有效排程(计划完成日>=当天) -> 补展开缺失BOM -> 重算各排程需求 -> 按排程同步交付主表
/// -> 批量同步金蝶三地库存 -> 全局计算停线日与风险(需求只统计明细日期>=当天的产量)
/// </summary>
[HttpPost]
public IHttpActionResult RecalcDelivery()
{
try
{
if (DeliveryCalcMap.TryGetValue(DeliveryCalcKey, out var running) && running.Status == "running"
&& (DateTime.Now - running.StartTime).TotalMinutes < 60)
return Ok(new { success = false, message = "物料交付正在重算中,请稍候" });
DeliveryCalcMap[DeliveryCalcKey] = new RecalcProgress
{
ScheduleId = 0,
Percent = 0,
Stage = "准备重算",
Status = "running",
Message = "",
StartTime = DateTime.Now
};
System.Threading.ThreadPool.QueueUserWorkItem(_ =>
{
try { RunDeliveryRecalc(); }
catch (Exception ex)
{
Logger.Error("RecalcDelivery后台执行异常:", ex);
if (DeliveryCalcMap.TryGetValue(DeliveryCalcKey, out var p))
{ p.Status = "error"; p.Stage = "重算失败"; p.Message = ex.Message; }
}
});
return Ok(new { success = true, message = "已开始重算物料交付数据,请稍候" });
}
catch (Exception ex)
{
Logger.Error("RecalcDelivery异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 查询物料交付全局重算任务进度(供前端轮询)
/// </summary>
[HttpGet]
public IHttpActionResult GetDeliveryCalcProgress()
{
if (DeliveryCalcMap.TryGetValue(DeliveryCalcKey, out var p))
return Ok(new { success = true, percent = p.Percent, stage = p.Stage, status = p.Status, message = p.Message });
return Ok(new { success = true, percent = 100, stage = "已完成", status = "success", message = "" });
}
/// <summary>
/// 后台执行全局重算
/// </summary>
private void RunDeliveryRecalc()
{
var p = DeliveryCalcMap[DeliveryCalcKey];
var sw = System.Diagnostics.Stopwatch.StartNew();
try
{
string todayStr = DateTime.Now.Date.ToString("yyyy-MM-dd");
// 阶段1:收集有效排程(计划完成日>=当天)
p.Stage = "正在收集有效生产排程...";
p.Percent = 5;
DataTable schDt = db.ExecuteTable($@"
SELECT Id, ProductCode, BOMVersion FROM Production_Schedule
WHERE PlanEndDate>='{todayStr}' ORDER BY Id");
int schCount = schDt.Rows.Count;
// 阶段1.5:清理已过期排程(计划完成日<当天)的下游交付/需求数据,避免数据累积。
// 注意:保留 Production_Schedule 主表、Production_Schedule_Detail 排程明细、Schedule_Change_Log 变更日志,供历史查看。
p.Stage = "正在清理过期排程的下游数据...";
p.Percent = 6;
DataTable expiredDt = db.ExecuteTable($@"
SELECT Id FROM Production_Schedule WHERE PlanEndDate<'{todayStr}'");
if (expiredDt.Rows.Count > 0)
{
string expiredIds = string.Join(",", expiredDt.Rows.Cast<DataRow>().Select(r => r["Id"].ToString()));
try
{
db.BeginTransaction();
int delArrival = db.ExecuteNonQuery($@"
DELETE FROM Arrival_Plan WHERE DeliveryId IN (SELECT Id FROM Material_Delivery WHERE ScheduleId IN ({expiredIds}))");
int delDelivery = db.ExecuteNonQuery($@"
DELETE FROM Material_Delivery WHERE ScheduleId IN ({expiredIds})");
int delRequirement = db.ExecuteNonQuery($@"
DELETE FROM Material_Requirement WHERE ScheduleId IN ({expiredIds})");
db.CommitTransaction();
Logger.Info($"RunDeliveryRecalc 清理过期排程 排程{expiredDt.Rows.Count}个 Ids=[{expiredIds}] 到货计划{delArrival} 交付{delDelivery} 需求{delRequirement}(排程主表/明细/变更日志保留)");
}
catch (Exception ex)
{
db.RollbackTransaction();
Logger.Error($"RunDeliveryRecalc 清理过期排程失败: {ex.Message}");
throw;
}
}
if (schCount == 0)
{
p.Status = "success"; p.Percent = 100; p.Stage = "完成";
p.Message = "没有计划完成日>=当天的生产排程,未做任何处理";
return;
}
// 阶段2+3:补展开缺失BOM、重算各排程需求
int done = 0;
foreach (DataRow sr in schDt.Rows)
{
done++;
int sid = Int(sr["Id"]);
string code = sr["ProductCode"].ToStringEx();
string ver = sr["BOMVersion"].ToStringEx();
p.Stage = $"({done}/{schCount}) 正在处理排程 [{code}] ...";
p.Percent = 10 + (done - 1) * 50 / schCount;
EnsureBomMasterRegistered(code, ver);
// BOM明细缺失才从金蝶展开(已有明细不重复查金蝶)
// 注意:IsVirtual=1(虚拟件/配置件)不算有效明细,需重新展开以剔除
DataTable bomChk = db.ExecuteTable($"SELECT ISNULL(SUM(CASE WHEN ISNULL(IsVirtual,0)=0 THEN 1 ELSE 0 END),0) AS CNT FROM Product_BOM_Detail WHERE ProductCode='{Sql(code)}' AND BOMVersion='{Sql(ver)}'");
if (bomChk.Rows.Count == 0 || Int(bomChk.Rows[0]["CNT"]) == 0)
{
string regMsg;
EnsureProductRegistered(code, ver, out regMsg);
}
// 重算该排程的物料需求(纯本地SQL)
RecalcScheduleRequirement(sid, code, ver);
p.Percent = 10 + done * 50 / schCount;
}
// 阶段4:按排程同步交付主表(累计物料、产品类型)
p.Stage = "正在同步物料交付主表...";
p.Percent = 65;
foreach (DataRow sr in schDt.Rows)
{
SyncDeliveryFromRequirement(Int(sr["Id"]), sr["ProductCode"].ToStringEx(), sr["BOMVersion"].ToStringEx());
}
// 阶段5:批量同步金蝶三地库存+线边+可用库存
p.Stage = "正在同步金蝶库存(震泽/晋江/资阳)...";
p.Percent = 80;
AutoSyncErpStockGlobal();
// 阶段6:全局重算预计到货日(取最早未过期的批次;全部过期或无批次则清空)
// 必须在阶段7之前调用,保证 CalcStopDateGlobal 内的风险判读到最新的 ExpectedArrivalDate
p.Stage = "正在重算预计到货日...";
p.Percent = 90;
int refreshedArrival = 0;
DataTable delDt = db.ExecuteTable($@"SELECT Id FROM Material_Delivery WHERE ScheduleId IN (SELECT Id FROM Production_Schedule WHERE PlanEndDate>='{todayStr}')");
foreach (DataRow r in delDt.Rows)
{
UpdateDeliveryArrivalDate(Int(r["Id"]));
refreshedArrival++;
}
Logger.Info($"RunDeliveryRecalc 全量重算预计到货日 完成{refreshedArrival}条");
// 阶段7:全局计算停线日与风险(用最新的 ExpectedArrivalDate 判风险)
p.Stage = "正在计算预计停线日与交付风险...";
p.Percent = 95;
CalcStopDateGlobal();
// 统计涉及物料数
DataTable matCnt = db.ExecuteTable($@"
SELECT COUNT(DISTINCT ChildMaterialId) AS CNT FROM Material_Requirement
WHERE ScheduleId IN (SELECT Id FROM Production_Schedule WHERE PlanEndDate>='{todayStr}')");
int matCount = matCnt.Rows.Count > 0 ? Int(matCnt.Rows[0]["CNT"]) : 0;
sw.Stop();
p.Status = "success";
p.Percent = 100;
p.Stage = "完成";
p.Message = $"重算完成:有效排程{schCount}个,物料{matCount}种,耗时{sw.Elapsed.TotalSeconds:F0}秒";
}
finally
{
System.Threading.Tasks.Task.Delay(TimeSpan.FromMinutes(30)).ContinueWith(t =>
{
DeliveryCalcMap.TryRemove(DeliveryCalcKey, out var removed);
});
}
}
#endregion
/// <summary>
/// 下载批量到货计划导入模板(Excel HTML 格式,可直接用 Excel 打开)
/// 格式:A列=子项物料编码,B列起=日期列(X月X日),每个有数量>=1的格子生成1条到货计划
/// </summary>
[HttpGet]
public IHttpActionResult DownloadBatchArrivalTemplate()
{
try
{
DataTable dt = new DataTable();
dt.Columns.Add("子项物料编码");
// 日期列范围:当前月开始,下月末结束
DateTime today = DateTime.Today;
DateTime start = new DateTime(today.Year, today.Month, 1);
int days = DateTime.DaysInMonth(start.Year, start.Month) + DateTime.DaysInMonth(start.AddMonths(1).Year, start.AddMonths(1).Month);
for (int i = 0; i < days; i++)
{
DateTime d = start.AddDays(i);
dt.Columns.Add(d.Month + "月" + d.Day + "日");
}
// 4 行空示例
for (int r = 0; r < 4; r++)
{
dt.Rows.Add(dt.NewRow());
}
byte[] bytes = ExcelHelper.ExportHtmlTable(dt, "到货计划导入模板");
return ExcelHelper.DownloadResult(bytes, "到货计划导入模板.xls");
}
catch (Exception ex)
{
Logger.Error("DownloadBatchArrivalTemplate异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 下载生产排程导入模板(Excel HTML 格式,可直接用 Excel 打开)
/// 模板不再包含"计划开始日/计划完成日"列;导入时根据维护了产量的日期列自动推算
/// </summary>
[HttpGet]
public IHttpActionResult DownloadScheduleTemplate()
{
try
{
DataTable dt = new DataTable();
dt.Columns.Add("产品编码");
dt.Columns.Add("BOM版本");
dt.Columns.Add("产品类型");
dt.Columns.Add("工厂");
dt.Columns.Add("区域");
dt.Columns.Add("国家");
dt.Columns.Add("状态");
// 动态生成日期列:当前月开始到下月结束(约 30 天)
DateTime today = DateTime.Today;
DateTime start = new DateTime(today.Year, today.Month, 1);
int days = DateTime.DaysInMonth(start.Year, start.Month) + DateTime.DaysInMonth(start.AddMonths(1).Year, start.AddMonths(1).Month);
for (int i = 0; i < days; i++)
{
DateTime d = start.AddDays(i);
dt.Columns.Add(d.Month + "月" + d.Day + "日");
}
// 示例数据行(演示 3 个产品,每个产品的起止日期不同;具体填哪些列就以哪些列为起止日)
DataRow r1 = dt.NewRow();
r1["产品编码"] = "CG0S175E0000120";
r1["BOM版本"] = "V1.2.0.135.00";
r1["产品类型"] = "S1";
r1["工厂"] = "";
r1["区域"] = "";
r1["国家"] = "";
r1["状态"] = "待开始";
r1[today.Month + "月1日"] = "20";
r1[today.Month + "月2日"] = "10";
r1[today.Month + "月3日"] = "20";
r1[today.Month + "月4日"] = "10";
r1[today.Month + "月5日"] = "20";
dt.Rows.Add(r1);
DataRow r2 = dt.NewRow();
r2["产品编码"] = "CGM41G2E0000100-T";
r2["BOM版本"] = "V1.0.0.113.00";
r2["产品类型"] = "M4";
r2["工厂"] = "";
r2["区域"] = "";
r2["国家"] = "";
r2["状态"] = "待开始";
r2[today.Month + "月2日"] = "30";
r2[today.Month + "月3日"] = "20";
r2[today.Month + "月4日"] = "10";
r2[today.Month + "月5日"] = "30";
r2[today.Month + "月6日"] = "30";
r2[today.Month + "月7日"] = "30";
r2[today.Month + "月8日"] = "30";
r2[today.Month + "月9日"] = "30";
r2[today.Month + "月10日"] = "30";
dt.Rows.Add(r2);
DataRow r3 = dt.NewRow();
r3["产品编码"] = "CG0TK98A0000300";
r3["BOM版本"] = "V3.0.0.003.00";
r3["产品类型"] = "梯控";
r3["工厂"] = "";
r3["区域"] = "";
r3["国家"] = "";
r3["状态"] = "待开始";
r3[today.Month + "月3日"] = "20";
r3[today.Month + "月4日"] = "20";
r3[today.Month + "月5日"] = "10";
r3[today.Month + "月6日"] = "20";
r3[today.Month + "月7日"] = "20";
r3[today.Month + "月8日"] = "20";
r3[today.Month + "月9日"] = "10";
r3[today.Month + "月10日"] = "30";
dt.Rows.Add(r3);
byte[] bytes = ExcelHelper.ExportHtmlTable(dt, "排程导入模板");
return ExcelHelper.DownloadResult(bytes, "生产排程导入模板.xls");
}
catch (Exception ex)
{
Logger.Error("DownloadScheduleTemplate异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 导入排程Excel(支持 .xls XML 和 .xlsx ZIP 格式)
/// </summary>
[HttpPost]
public IHttpActionResult ImportScheduleExcel()
{
try
{
var body = Request.Content.ReadAsStringAsync().Result;
JObject obj = JObject.Parse(body);
string fileB64 = obj["FileContent"]?.ToString() ?? "";
string fileName = obj["FileName"]?.ToString() ?? "";
if (string.IsNullOrEmpty(fileB64))
return Ok(new { success = false, message = "文件内容不能为空" });
byte[] fileBytes = Convert.FromBase64String(fileB64);
List<string[]> rows = new List<string[]>();
bool isXlsx = fileName.ToLower().EndsWith(".xlsx");
if (isXlsx)
rows = ReadXlsxRows(fileBytes);
else
rows = ReadXlsXmlRows(fileBytes);
if (rows.Count < 2)
return Ok(new { success = false, message = "数据行不足,请检查模板" });
// 解析表头,定位列
string[] header = rows[0];
Func<string, int> findCol = (name) =>
{
for (int i = 0; i < header.Length; i++)
{
if (!string.IsNullOrEmpty(header[i]) && header[i].Trim().IndexOf(name, StringComparison.OrdinalIgnoreCase) >= 0)
return i;
}
return -1;
};
int colProduct = findCol("产品编码");
int colVersion = findCol("BOM版本");
int colType = findCol("产品类型");
int colFactory = findCol("工厂");
int colRegion = findCol("区域");
int colCountry = findCol("国家");
int colStart = findCol("计划开始日");
int colEnd = findCol("计划完成日");
int colStatus = findCol("状态");
if (colProduct < 0 || colVersion < 0)
return Ok(new { success = false, message = "模板列不完整,需包含:产品编码、BOM版本、产品类型、状态、以及按天的产量列" });
// 解析日期列(表头格式如 8/1、8/3(一)、2026-08-01)
Dictionary<int, string> dateColMap = new Dictionary<int, string>();
string startDate = "";
for (int i = 0; i < header.Length; i++)
{
string h = (header[i] ?? "").Trim();
if (string.IsNullOrEmpty(h)) continue;
string d = ParseHeaderDate(h);
if (!string.IsNullOrEmpty(d))
{
dateColMap[i] = d;
if (string.IsNullOrEmpty(startDate)) startDate = d;
}
}
if (dateColMap.Count == 0)
return Ok(new { success = false, message = "未找到日期产量列,表头需包含日期(如 8/1、2026-08-01)" });
int successCount = 0, failCount = 0;
List<string> failDetails = new List<string>();
// 按 产品编码+BOM版本 聚合:同一组合的多行产量合并累加后只保存一条,
// 避免同键多行互相覆盖导致产量丢失、以及排程条数与Excel行数对不上
List<string> blockKeys = new List<string>();
Dictionary<string, string[]> blockInfo = new Dictionary<string, string[]>();
Dictionary<string, Dictionary<string, decimal>> blockDaily = new Dictionary<string, Dictionary<string, decimal>>();
for (int i = 1; i < rows.Count; i++)
{
string[] cells = rows[i];
Func<int, string> cv = (idx) => idx >= 0 && idx < cells.Length ? (cells[idx] ?? "").Trim() : "";
string product = cv(colProduct);
string version = cv(colVersion);
// 产品信息行:产品编码+BOM版本都非空
if (!string.IsNullOrEmpty(product) && !string.IsNullOrEmpty(version))
{
string key = product + "|" + version;
if (!blockDaily.ContainsKey(key))
{
blockKeys.Add(key);
blockDaily[key] = new Dictionary<string, decimal>();
string status = cv(colStatus);
if (string.IsNullOrEmpty(status)) status = "待开始";
// [0]产品编码 [1]版本 [2]类型 [3]工厂 [4]区域 [5]国家 [6]状态 [7]开始日 [8]完成日
blockInfo[key] = new[] { product, version, cv(colType), cv(colFactory), cv(colRegion), cv(colCountry), status, cv(colStart), cv(colEnd) };
}
// 同一组合的后续行:产量继续累加到同一组
ParseDailyRow(cells, dateColMap, blockDaily[key]);
}
else if (blockKeys.Count > 0)
{
// 产量数据行:属于最近出现的产品组合
ParseDailyRow(cells, dateColMap, blockDaily[blockKeys[blockKeys.Count - 1]]);
}
}
// 统一保存(同产品+版本仅一条,产量为各行之和;开始/完成日为空时由产量日期自动推算)
foreach (string key in blockKeys)
{
string[] info = blockInfo[key];
try
{
SaveImportedSchedule(info[0], info[1], info[2], info[3], info[4], info[5], info[7], info[8], info[6], blockDaily[key]);
successCount++;
}
catch (Exception ex)
{
failCount++;
failDetails.Add(ex.Message);
}
}
return Ok(new { success = true, message = $"导入完成:成功{successCount}个产品" + (failCount > 0 ? $",失败{failCount}个:" + string.Join(";", failDetails) : ""), successCount = successCount, failCount = failCount });
}
catch (Exception ex)
{
Logger.Error("ImportScheduleExcel异常:", ex);
return Ok(new { success = false, message = "导入失败:" + ex.Message });
}
}
/// <summary>
/// 保存导入的排程
/// </summary>
private void SaveImportedSchedule(string product, string version, string productType, string factory, string region, string country, string start, string end, string status, Dictionary<string, decimal> daily)
{
// 模板不再要求开始/结束日列,从维护了产量的日期列自动推算
if (string.IsNullOrEmpty(start) || string.IsNullOrEmpty(end))
{
if (daily == null || daily.Count == 0)
throw new Exception("计划开始/完成日为空,且无任何日期列产量,无法推算");
var dates = daily.Keys.OrderBy(x => x).ToList();
if (string.IsNullOrEmpty(start)) start = dates.First();
if (string.IsNullOrEmpty(end)) end = dates.Last();
}
// 校验产品BOM是否存在于金蝶 T_BD_BOM,且使用组织=986610(失败时给出可定位的详细原因)
DataTable chk = db.ExecuteTable($@"
SELECT TOP 1 B.FINTERID
FROM T_BD_BOM B
LEFT JOIN T_BD_MATERIAL M ON B.FMATERIALID=M.FINTERID
WHERE M.FNUMBER='{Sql(product)}' AND B.FBILLNO='{Sql(version)}' AND B.FCOMPANYID=986610 AND B.FDELETED=0");
if (chk.Rows.Count == 0)
throw new Exception(BuildBomCheckError(product, version));
// 金侧BOM校验通过后,自动登记到本地 Product_BOM_Master(不影响正常流程)
EnsureBomMasterRegistered(product, version);
// 去重:产品编码+BOM版本+计划开始日
DataTable exist = db.ExecuteTable($@"SELECT Id FROM Production_Schedule
WHERE ProductCode='{Sql(product)}' AND BOMVersion='{Sql(version)}' AND PlanStartDate='{start}'");
int id;
if (exist.Rows.Count > 0)
{
id = Int(exist.Rows[0]["Id"]);
db.ExecuteNonQuery($@"UPDATE Production_Schedule SET ProductType=N'{Sql(productType)}', Factory=N'{Sql(factory)}', Region=N'{Sql(region)}', Country=N'{Sql(country)}', PlanEndDate='{end}', Status=N'{Sql(status)}', UpdateTime=GETDATE() WHERE Id={id}");
}
else
{
db.ExecuteNonQuery($@"INSERT INTO Production_Schedule (ProductCode,BOMVersion,ProductType,Factory,Region,Country,PlanStartDate,PlanEndDate,Status,CreateUser)
VALUES ('{Sql(product)}','{Sql(version)}',N'{Sql(productType)}',N'{Sql(factory)}',N'{Sql(region)}',N'{Sql(country)}','{start}','{end}',N'{Sql(status)}','')");
id = GetLastIdentity();
}
// 保存每日产量
db.ExecuteNonQuery($"DELETE FROM Production_Schedule_Detail WHERE ScheduleId={id}");
foreach (var kv in daily)
{
if (kv.Value > 0)
db.ExecuteNonQuery($@"INSERT INTO Production_Schedule_Detail (ScheduleId,ProductCode,BOMVersion,PlanDate,DailyOutput)
VALUES ({id},'{Sql(product)}','{Sql(version)}','{kv.Key}',{kv.Value})");
}
// 只保存数据,不做需求计算(由物料交付页"重算交付数据"统一执行)
}
/// <summary>
/// BOM校验失败时生成可定位的错误信息(含产品编码、BOM版本、金蝶可用版本、删除状态)
/// </summary>
private string BuildBomCheckError(string product, string version)
{
string head = $"产品[{product}] 版本[{version}]";
try
{
// 1) 物料编码是否存在
DataTable mat = db.ExecuteTable($"SELECT TOP 1 FINTERID FROM T_BD_MATERIAL WHERE FNUMBER='{Sql(product)}'");
if (mat.Rows.Count == 0)
return $"{head}:产品编码在金蝶物料(T_BD_MATERIAL)中不存在,请核对Excel里的产品编码";
// 2) 同编码+同版本在组织986610下的记录(忽略删除标记)
DataTable same = db.ExecuteTable($@"
SELECT B.FDELETED FROM T_BD_BOM B
LEFT JOIN T_BD_MATERIAL M ON B.FMATERIALID=M.FINTERID
WHERE M.FNUMBER='{Sql(product)}' AND B.FBILLNO='{Sql(version)}' AND B.FCOMPANYID=986610");
if (same.Rows.Count > 0)
{
bool allDeleted = true;
foreach (DataRow r in same.Rows)
{
string fd = (r["FDELETED"] ?? "").ToString().Trim();
if (!(fd == "1" || fd.Equals("True", StringComparison.OrdinalIgnoreCase)))
{
allDeleted = false;
break;
}
}
if (allDeleted)
return $"{head}:BOM存在但已删除(FDELETED=1),请到金蝶恢复有效BOM;组织986610下当前有效版本:{NullOrNone(GetBomVersions(product, true))}";
}
// 3) 版本不存在:列出该产品在986610下的所有版本与有效版本
string all = GetBomVersions(product, false);
if (string.IsNullOrEmpty(all))
return $"{head}:该产品编码在组织986610下没有任何BOM记录,请先在金蝶创建并审核BOM";
return $"{head}:BOM版本不存在;金蝶组织986610下该产品的版本有:{all};其中有效(FDELETED=0):{NullOrNone(GetBomVersions(product, true))}";
}
catch (Exception ex)
{
Logger.Error("BuildBomCheckError异常:", ex);
return $"{head}:不存在于BOM主数据(组织986610),请先维护";
}
}
/// <summary>
/// 取该产品在组织986610下的BOM版本列表(最多10个,已排序)
/// </summary>
private string GetBomVersions(string product, bool validOnly)
{
string delCond = validOnly ? " AND B.FDELETED=0" : "";
DataTable dt = db.ExecuteTable($@"
SELECT DISTINCT B.FBILLNO FROM T_BD_BOM B
LEFT JOIN T_BD_MATERIAL M ON B.FMATERIALID=M.FINTERID
WHERE M.FNUMBER='{Sql(product)}' AND B.FCOMPANYID=986610{delCond}");
List<string> vs = new List<string>();
foreach (DataRow r in dt.Rows)
{
string v = (r["FBILLNO"] ?? "").ToString().Trim();
if (!string.IsNullOrEmpty(v) && !vs.Contains(v)) vs.Add(v);
}
vs.Sort();
if (vs.Count > 10)
return string.Join("、", vs.Take(10)) + $" 等{vs.Count}个";
return string.Join("、", vs);
}
private string NullOrNone(string s)
{
return string.IsNullOrEmpty(s) ? "无" : s;
}
/// <summary>
/// 解析单行产量数据
/// </summary>
private void ParseDailyRow(string[] cells, Dictionary<int, string> dateColMap, Dictionary<string, decimal> daily)
{
foreach (var kv in dateColMap)
{
int idx = kv.Key;
string date = kv.Value;
if (idx >= cells.Length) continue;
string v = (cells[idx] ?? "").Trim();
if (string.IsNullOrEmpty(v) || v == "-") continue;
decimal d;
if (decimal.TryParse(v, out d) && d > 0)
{
if (daily.ContainsKey(date)) daily[date] += d;
else daily[date] = d;
}
}
}
/// <summary>
/// 解析表头日期(支持 8/1、8/1(六)、2026-08-01、2026/08/01、9月1日)
/// </summary>
private string ParseHeaderDate(string h)
{
// 去掉括号备注如 (六)
int b = h.IndexOf('(');
if (b > 0) h = h.Substring(0, b);
string t = h.Trim();
if (string.IsNullOrEmpty(t)) return "";
DateTime dt;
// 完整日期(如 2026-08-01、2026/08/01)
if (DateTime.TryParse(t, out dt))
return dt.ToString("yyyy-MM-dd");
int year = DateTime.Now.Year;
// 中文格式:9月1日、9月1 号、9 月 1 日
Match mc = Regex.Match(t, @"^\s*(\d{1,2})\s*月\s*(\d{1,2})\s*[日号]?\s*$");
if (mc.Success)
{
int m, d;
if (int.TryParse(mc.Groups[1].Value, out m) && int.TryParse(mc.Groups[2].Value, out d))
{
try { return new DateTime(year, m, d).ToString("yyyy-MM-dd"); }
catch { return ""; }
}
}
// M/d 短格式(如 8/1、8-1)
string[] parts = t.Split(new[]{'/', '-', '.'}, StringSplitOptions.RemoveEmptyEntries);
if (parts.Length == 2)
{
int m, d;
if (int.TryParse(parts[0], out m) && int.TryParse(parts[1], out d))
{
try { return new DateTime(year, m, d).ToString("yyyy-MM-dd"); }
catch { return ""; }
}
}
return "";
}
/// <summary>
/// 读取 .xls XML 格式
/// </summary>
private List<string[]> ReadXlsXmlRows(byte[] fileBytes)
{
return ExcelHelper.ReadXlsXmlRows(fileBytes);
}
/// <summary>
/// 读取 .xlsx ZIP 格式
/// </summary>
private List<string[]> ReadXlsxRows(byte[] fileBytes)
{
return ExcelHelper.ReadXlsxRows(fileBytes);
}
private int ColRefToIndex(string refAttr)
{
return ExcelHelper.ColRefToIndex(refAttr);
}
#endregion
#region ===== 物料交付维护 =====
/// <summary>
/// 获取物料交付列表(分页,全局物料维度)
/// 日需求=所有生产排程对该物料的每日需求累计;ProductTypes=该物料关联的所有产品类型
/// </summary>
[HttpGet]
public IHttpActionResult GetDeliveryList(string productCode = "", string productType = "", string supplier = "",
string purchaser = "", string risk = "", string bomLevel = "", string keyword = "", int page = 1, int pageSize = 50)
{
try
{
string where = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(productType))
where += $" AND D.ProductTypes LIKE '%{Sql(productType)}%' ";
if (!string.IsNullOrEmpty(supplier)) where += $" AND D.Supplier=N'{Sql(supplier)}' ";
if (!string.IsNullOrEmpty(purchaser)) where += $" AND D.Purchaser=N'{Sql(purchaser)}' ";
if (!string.IsNullOrEmpty(risk)) where += $" AND D.DeliveryRisk=N'{Sql(risk)}' ";
if (!string.IsNullOrEmpty(bomLevel)) where += $" AND D.BOMLevel={Int(bomLevel)} ";
if (!string.IsNullOrEmpty(keyword)) where += $" AND (D.ChildMaterialId LIKE '%{Sql(keyword)}%' OR D.MaterialName LIKE N'%{Sql(keyword)}%') ";
// 统计总记录数
DataTable dtCount = db.ExecuteTable($"SELECT COUNT(*) AS CNT FROM Material_Delivery D {where}");
int total = Int(dtCount.Rows[0]["CNT"]);
int offset = (page - 1) * pageSize;
DataTable dt = db.ExecuteTable($@"
SELECT D.*,
(SELECT ISNULL(SUM(R.DailyRequirement),0) FROM Material_Requirement R
WHERE R.ChildMaterialId=D.ChildMaterialId AND R.PlanDate>=CONVERT(date,GETDATE())) AS DailyRequirement,
(SELECT MIN(PlanDate) FROM Material_Requirement R
WHERE R.ChildMaterialId=D.ChildMaterialId AND R.PlanDate>=CONVERT(date,GETDATE())) AS FirstNeedDate
FROM Material_Delivery D
{where}
ORDER BY D.BOMLevel, D.ChildMaterialId
OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["Id"] = r["Id"].ToStringEx(),
["ScheduleId"] = r["ScheduleId"].ToStringEx(),
["ProductCode"] = r["ProductCode"].ToStringEx(),
["BOMVersion"] = r["BOMVersion"].ToStringEx(),
["ChildMaterialId"] = r["ChildMaterialId"].ToStringEx(),
["MaterialName"] = r["MaterialName"].ToStringEx(),
["BOMLevel"] = r["BOMLevel"].ToStringEx(),
["ProductTypes"] = r["ProductTypes"].ToStringEx(),
["Supplier"] = r["Supplier"].ToStringEx(),
["Purchaser"] = r["Purchaser"].ToStringEx(),
["ExpectedStopDate"] = DateStr(r["ExpectedStopDate"]),
["ExpectedArrivalDate"] = DateStr(r["ExpectedArrivalDate"]),
["DeliveryRisk"] = r["DeliveryRisk"].ToStringEx(),
["ZhenZeStock"] = NumStr(r["ZhenZeStock"]),
["JinJiangStock"] = NumStr(r["JinJiangStock"]),
["ZiYangStock"] = NumStr(r["ZiYangStock"]),
["ERPStockQty"] = NumStr(r["ERPStockQty"]),
["InTransitQty"] = NumStr(r["InTransitQty"]),
["LineSideStock"] = NumStr(r["LineSideStock"]),
["MassInTransit"] = NumStr(r["MassInTransit"]),
["ResearchInTransit"] = NumStr(r["ResearchInTransit"]),
["ReceivedQty"] = NumStr(r["ReceivedQty"]),
["AvailableStock"] = NumStr(r["AvailableStock"]),
["DeliveryProgress"] = r["DeliveryProgress"].ToStringEx(),
["Remark"] = r["Remark"].ToStringEx(),
["DailyRequirement"] = NumStr(r["DailyRequirement"]),
["FirstNeedDate"] = DateStr(r["FirstNeedDate"])
});
}
return Ok(new { success = true, data = list, total = total, page = page, pageSize = pageSize });
}
catch (Exception ex)
{
Logger.Error("GetDeliveryList异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取交付详情(含到货计划)
/// </summary>
[HttpGet]
public IHttpActionResult GetDeliveryDetail(int id)
{
try
{
DataTable dt = db.ExecuteTable($"SELECT * FROM Material_Delivery WHERE Id={id}");
if (dt.Rows.Count == 0)
return Ok(new { success = false, message = "交付记录不存在" });
DataRow r = dt.Rows[0];
// 到货计划
DataTable dtArrival = db.ExecuteTable($"SELECT * FROM Arrival_Plan WHERE DeliveryId={id} ORDER BY BatchNo");
List<Dictionary<string, object>> arrivals = new List<Dictionary<string, object>>();
foreach (DataRow ar in dtArrival.Rows)
{
arrivals.Add(new Dictionary<string, object>
{
["Id"] = ar["Id"].ToStringEx(),
["BatchNo"] = ar["BatchNo"].ToStringEx(),
["ExpectedArrivalDate"] = DateStr(ar["ExpectedArrivalDate"]),
["ActualArrivalDate"] = DateStr(ar["ActualArrivalDate"]),
["ArrivalQty"] = NumStr(ar["ArrivalQty"]),
["Status"] = ar["Status"].ToStringEx()
});
}
return Ok(new
{
success = true,
data = new
{
Id = r["Id"].ToStringEx(),
ScheduleId = r["ScheduleId"].ToStringEx(),
ProductCode = r["ProductCode"].ToStringEx(),
BOMVersion = r["BOMVersion"].ToStringEx(),
ChildMaterialId = r["ChildMaterialId"].ToStringEx(),
MaterialName = r["MaterialName"].ToStringEx(),
BOMLevel = r["BOMLevel"].ToStringEx(),
Supplier = r["Supplier"].ToStringEx(),
Purchaser = r["Purchaser"].ToStringEx(),
ExpectedStopDate = DateStr(r["ExpectedStopDate"]),
ExpectedArrivalDate = DateStr(r["ExpectedArrivalDate"]),
DeliveryRisk = r["DeliveryRisk"].ToStringEx(),
ZhenZeStock = NumStr(r["ZhenZeStock"]),
JinJiangStock = NumStr(r["JinJiangStock"]),
ZiYangStock = NumStr(r["ZiYangStock"]),
ERPStockQty = NumStr(r["ERPStockQty"]),
InTransitQty = NumStr(r["InTransitQty"]),
LineSideStock = NumStr(r["LineSideStock"]),
MassInTransit = NumStr(r["MassInTransit"]),
ResearchInTransit = NumStr(r["ResearchInTransit"]),
ReceivedQty = NumStr(r["ReceivedQty"]),
AvailableStock = NumStr(r["AvailableStock"]),
DeliveryProgress = r["DeliveryProgress"].ToStringEx(),
Remark = r["Remark"].ToStringEx(),
arrivals = arrivals
}
});
}
catch (Exception ex)
{
Logger.Error("GetDeliveryDetail异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 保存物料交付信息
/// </summary>
[HttpPost]
public IHttpActionResult SaveDelivery([FromBody] JObject obj)
{
try
{
int id = obj["Id"].ToStringEx().ToInt();
string supplier = obj["Supplier"]?.ToString() ?? "";
string purchaser = obj["Purchaser"]?.ToString() ?? "";
string expectedStop = obj["ExpectedStopDate"]?.ToString() ?? "";
// ExpectedArrivalDate 不再从表单接收,由系统根据 Arrival_Plan 自动重算(避免前端传值污染主表)
string expectedArrival = "";
string deliveryRisk = obj["DeliveryRisk"]?.ToString() ?? "";
decimal lineSide = Dec(obj["LineSideStock"]);
string progress = obj["DeliveryProgress"]?.ToString() ?? "";
string remark = obj["Remark"]?.ToString() ?? "";
if (id <= 0) return Ok(new { success = false, message = "交付ID无效" });
// 震泽/晋江/资阳库存、量产在途、研发在途均由金蝶自动同步,不接收表单值;可用库存=三地+量产在途+研发在途+线边
decimal zhenZe = 0, jinJiang = 0, ziYang = 0, massInTransit = 0, researchInTransit = 0;
DataTable dDt = db.ExecuteTable($"SELECT ISNULL(ZhenZeStock,0) AS ZhenZeStock, ISNULL(JinJiangStock,0) AS JinJiangStock, ISNULL(ZiYangStock,0) AS ZiYangStock, ISNULL(MassInTransit,0) AS MassInTransit, ISNULL(ResearchInTransit,0) AS ResearchInTransit FROM Material_Delivery WHERE Id={id}");
if (dDt.Rows.Count > 0)
{
zhenZe = Dec(dDt.Rows[0]["ZhenZeStock"]);
jinJiang = Dec(dDt.Rows[0]["JinJiangStock"]);
ziYang = Dec(dDt.Rows[0]["ZiYangStock"]);
massInTransit = Dec(dDt.Rows[0]["MassInTransit"]);
researchInTransit = Dec(dDt.Rows[0]["ResearchInTransit"]);
}
decimal erpStock = zhenZe + jinJiang + ziYang;
decimal inTransit = massInTransit + researchInTransit;
decimal available = erpStock + inTransit + lineSide;
// 不再 UPDATE ExpectedArrivalDate,由下方 UpdateDeliveryArrivalDate(id) 自动重算
// DeliveryRisk 也先置空(避免把过期日期错误地判为"安全"),最后由 RecalcDeliveryStopDate 重判
db.ExecuteNonQuery($@"UPDATE Material_Delivery SET
Supplier=N'{Sql(supplier)}', Purchaser=N'{Sql(purchaser)}',
DeliveryRisk=NULL, ERPStockQty={erpStock}, InTransitQty={inTransit},
LineSideStock={lineSide}, AvailableStock={available},
DeliveryProgress=N'{Sql(progress)}', Remark=N'{Sql(remark)}', UpdateTime=GETDATE()
WHERE Id={id}");
// 保存后由系统自动重算停线日并重判风险(停线日不作为手工输入,由系统计算)
RecalcDeliveryStopDate(id);
// 同时根据当前 Arrival_Plan 重算预计到货日(取最早未过期的批次;全部过期或无批次则清空)
UpdateDeliveryArrivalDate(id);
// 顺序:先重算停线日和风险(用旧 ExpectedArrivalDate),再重算到货日,最后再判一次风险(用新 ExpectedArrivalDate)
RecalcDeliveryRiskOnly(id);
return Ok(new { success = true, message = "保存成功" });
}
catch (Exception ex)
{
Logger.Error("SaveDelivery异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region ===== 生产交付(线边仓库存维护) =====
/// <summary>
/// 获取生产交付列表(分页,筛选:关键字/日期范围,支持按Id取单条用于编辑回填)
/// </summary>
[HttpGet]
public IHttpActionResult GetProductionDeliveryList(string keyword = "", string dateStart = "", string dateEnd = "", int id = 0, int page = 1, int pageSize = 50)
{
try
{
string where = " WHERE 1=1 ";
if (id > 0) where += $" AND Id={id} ";
if (!string.IsNullOrEmpty(keyword))
where += $" AND (MaterialCode LIKE '%{Sql(keyword)}%' OR MaterialName LIKE N'%{Sql(keyword)}%') ";
if (!string.IsNullOrEmpty(dateStart)) where += $" AND StockDate>='{Sql(dateStart)}' ";
if (!string.IsNullOrEmpty(dateEnd)) where += $" AND StockDate<='{Sql(dateEnd)}' ";
DataTable dtCount = db.ExecuteTable($"SELECT COUNT(*) AS CNT FROM Production_Delivery {where}");
int total = Int(dtCount.Rows[0]["CNT"]);
int offset = (page - 1) * pageSize;
DataTable dt = db.ExecuteTable($@"
SELECT Id, MaterialCode, MaterialName, LineSideStock, StockDate, UpdateTime
FROM Production_Delivery {where}
ORDER BY MaterialCode
OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["Id"] = r["Id"].ToStringEx(),
["MaterialCode"] = r["MaterialCode"].ToStringEx(),
["MaterialName"] = r["MaterialName"].ToStringEx(),
["LineSideStock"] = NumStr(r["LineSideStock"]),
["StockDate"] = DateStr(r["StockDate"]),
["UpdateTime"] = DateStr(r["UpdateTime"])
});
}
return Ok(new { success = true, data = list, total = total, page = page, pageSize = pageSize });
}
catch (Exception ex)
{
Logger.Error("GetProductionDeliveryList异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 保存生产交付记录(新增或按物料编码覆盖更新)
/// </summary>
[HttpPost]
public IHttpActionResult SaveProductionDelivery([FromBody] JObject obj)
{
try
{
int id = obj["Id"].ToStringEx().ToInt();
string materialCode = obj["MaterialCode"]?.ToString().Trim() ?? "";
string materialName = obj["MaterialName"]?.ToString().Trim() ?? "";
decimal lineSideStock = Dec(obj["LineSideStock"]?.ToString() ?? "0");
string stockDate = obj["StockDate"]?.ToString() ?? "";
if (string.IsNullOrEmpty(materialCode))
return Ok(new { success = false, message = "物料编号不能为空" });
if (id > 0)
{
// 编辑:按Id更新,同时保证物料编码唯一性(若编码已被其他记录占用则拒绝)
DataTable dup = db.ExecuteTable($"SELECT Id FROM Production_Delivery WHERE MaterialCode='{Sql(materialCode)}' AND Id<>{id}");
if (dup.Rows.Count > 0)
return Ok(new { success = false, message = $"物料编号[{materialCode}]已存在其他记录,不能重复" });
string setDate = string.IsNullOrEmpty(stockDate) ? "NULL" : $"'{stockDate}'";
db.ExecuteNonQuery($@"UPDATE Production_Delivery SET
MaterialCode='{Sql(materialCode)}', MaterialName=N'{Sql(materialName)}',
LineSideStock={lineSideStock}, StockDate={setDate}, UpdateTime=GETDATE()
WHERE Id={id}");
return Ok(new { success = true, message = "保存成功" });
}
else
{
// 新增:按物料编码覆盖(存在则更新日期与库存数,不存在则插入)
DataTable exist = db.ExecuteTable($"SELECT Id FROM Production_Delivery WHERE MaterialCode='{Sql(materialCode)}'");
if (exist.Rows.Count > 0)
{
int existId = Int(exist.Rows[0]["Id"]);
string setDate = string.IsNullOrEmpty(stockDate) ? "NULL" : $"'{stockDate}'";
db.ExecuteNonQuery($@"UPDATE Production_Delivery SET
MaterialName=N'{Sql(materialName)}', LineSideStock={lineSideStock},
StockDate={setDate}, UpdateTime=GETDATE() WHERE Id={existId}");
return Ok(new { success = true, message = "该物料编号已存在,已覆盖更新" });
}
string setDateNew = string.IsNullOrEmpty(stockDate) ? "NULL" : $"'{stockDate}'";
db.ExecuteNonQuery($@"INSERT INTO Production_Delivery (MaterialCode,MaterialName,LineSideStock,StockDate)
VALUES ('{Sql(materialCode)}',N'{Sql(materialName)}',{lineSideStock},{setDateNew})");
return Ok(new { success = true, message = "新增成功" });
}
}
catch (Exception ex)
{
Logger.Error("SaveProductionDelivery异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 删除生产交付记录(支持多选删除)
/// </summary>
[HttpPost]
public IHttpActionResult DeleteProductionDelivery([FromBody] JObject obj)
{
try
{
var ids = obj["Ids"]?.ToObject<List<int>>() ?? new List<int>();
if (ids.Count == 0)
return Ok(new { success = false, message = "请选择要删除的记录" });
string idsStr = string.Join(",", ids);
db.ExecuteNonQuery($"DELETE FROM Production_Delivery WHERE Id IN ({idsStr})");
return Ok(new { success = true, message = $"已删除{ids.Count}条记录" });
}
catch (Exception ex)
{
Logger.Error("DeleteProductionDelivery异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 导入生产交付Excel(按物料编码覆盖:存在则更新名称/库存数/日期,不存在则新增)
/// 模板列:物料编号、物料名称、线边仓库存数、日期
/// </summary>
[HttpPost]
public IHttpActionResult ImportProductionDelivery()
{
try
{
var body = Request.Content.ReadAsStringAsync().Result;
JObject obj = JObject.Parse(body);
string fileB64 = obj["FileContent"]?.ToString() ?? "";
string fileName = obj["FileName"]?.ToString() ?? "";
if (string.IsNullOrEmpty(fileB64))
return Ok(new { success = false, message = "文件内容不能为空" });
byte[] fileBytes = Convert.FromBase64String(fileB64);
List<string[]> rows;
bool isXlsx = fileName.ToLower().EndsWith(".xlsx");
if (isXlsx)
rows = ReadXlsxRows(fileBytes);
else
rows = ReadXlsXmlRows(fileBytes);
if (rows.Count < 2)
return Ok(new { success = false, message = "数据行不足,请检查模板" });
// 解析表头,定位列
string[] header = rows[0];
Func<string, int> findCol = (name) =>
{
for (int i = 0; i < header.Length; i++)
{
if (!string.IsNullOrEmpty(header[i]) && header[i].Trim().IndexOf(name, StringComparison.OrdinalIgnoreCase) >= 0)
return i;
}
return -1;
};
int colCode = findCol("物料编号");
int colName = findCol("物料名称");
int colStock = findCol("线边仓库存数");
int colDate = findCol("日期");
if (colCode < 0 || colStock < 0)
return Ok(new { success = false, message = "模板列不完整,需包含:物料编号、线边仓库存数" });
int addCount = 0, updateCount = 0, failCount = 0;
List<string> failDetails = new List<string>();
for (int i = 1; i < rows.Count; i++)
{
string[] cells = rows[i];
Func<int, string> cv = (idx) => idx >= 0 && idx < cells.Length ? (cells[idx] ?? "").Trim() : "";
string code = cv(colCode);
if (string.IsNullOrEmpty(code)) continue;
try
{
string name = colName >= 0 ? cv(colName) : "";
decimal stock = Dec(cv(colStock));
string dateStr = colDate >= 0 ? cv(colDate) : "";
// 日期格式化(兼容 yyyy-MM-dd / yyyy/M/d / Excel序列等常见格式)
string setDate = "NULL";
if (!string.IsNullOrEmpty(dateStr))
{
DateTime d;
if (DateTime.TryParse(dateStr, out d))
setDate = $"'{d:yyyy-MM-dd}'";
}
DataTable exist = db.ExecuteTable($"SELECT Id FROM Production_Delivery WHERE MaterialCode='{Sql(code)}'");
if (exist.Rows.Count > 0)
{
int existId = Int(exist.Rows[0]["Id"]);
db.ExecuteNonQuery($@"UPDATE Production_Delivery SET
MaterialName=N'{Sql(name)}', LineSideStock={stock}, StockDate={setDate}, UpdateTime=GETDATE()
WHERE Id={existId}");
updateCount++;
}
else
{
db.ExecuteNonQuery($@"INSERT INTO Production_Delivery (MaterialCode,MaterialName,LineSideStock,StockDate)
VALUES ('{Sql(code)}',N'{Sql(name)}',{stock},{setDate})");
addCount++;
}
}
catch (Exception ex)
{
failCount++;
failDetails.Add($"[{code}] {ex.Message}");
}
}
return Ok(new { success = true, message = $"导入完成:新增{addCount}条,覆盖更新{updateCount}条" + (failCount > 0 ? $",失败{failCount}条:" + string.Join(";", failDetails) : ""), addCount = addCount, updateCount = updateCount, failCount = failCount });
}
catch (Exception ex)
{
Logger.Error("ImportProductionDelivery异常:", ex);
return Ok(new { success = false, message = "导入失败:" + ex.Message });
}
}
/// <summary>
/// 下载生产交付导入模板(含示例数据)
/// </summary>
[HttpGet]
public IHttpActionResult DownloadProductionDeliveryTemplate()
{
try
{
DataTable dt = new DataTable();
dt.Columns.Add("物料编号");
dt.Columns.Add("物料名称");
dt.Columns.Add("线边仓库存数");
dt.Columns.Add("日期");
DataRow row = dt.NewRow();
row["物料编号"] = "100401001";
row["物料名称"] = "示例物料(导入前请删除本行)";
row["线边仓库存数"] = "100";
row["日期"] = "2026-08-25";
dt.Rows.Add(row);
byte[] bytes = ExcelHelper.ExportHtmlTable(dt, "生产交付导入模板");
return ExcelHelper.DownloadResult(bytes, "生产交付导入模板.xls");
}
catch (Exception ex)
{
Logger.Error("DownloadProductionDeliveryTemplate异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 导出生产交付列表为Excel
/// </summary>
[HttpGet]
public IHttpActionResult ExportProductionDeliveryExcel(string keyword = "", string dateStart = "", string dateEnd = "")
{
try
{
string where = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(keyword))
where += $" AND (MaterialCode LIKE '%{Sql(keyword)}%' OR MaterialName LIKE N'%{Sql(keyword)}%') ";
if (!string.IsNullOrEmpty(dateStart)) where += $" AND StockDate>='{Sql(dateStart)}' ";
if (!string.IsNullOrEmpty(dateEnd)) where += $" AND StockDate<='{Sql(dateEnd)}' ";
DataTable dt = db.ExecuteTable($@"
SELECT MaterialCode, MaterialName, LineSideStock, StockDate
FROM Production_Delivery {where}
ORDER BY MaterialCode");
DataTable exportDt = new DataTable();
exportDt.Columns.Add("物料编号"); exportDt.Columns.Add("物料名称");
exportDt.Columns.Add("线边仓库存数"); exportDt.Columns.Add("日期");
foreach (DataRow r in dt.Rows)
{
exportDt.Rows.Add(
r["MaterialCode"].ToStringEx(), r["MaterialName"].ToStringEx(),
NumStr(r["LineSideStock"]), DateStr(r["StockDate"]));
}
byte[] bytes = ExcelHelper.ExportHtmlTable(exportDt, "生产交付");
return ExcelHelper.DownloadResult(bytes, "生产交付_" + DateTime.Now.ToString("yyyyMMdd") + ".xls");
}
catch (Exception ex)
{
Logger.Error("ExportProductionDeliveryExcel异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
/// <summary>
/// 保存到货计划批次
/// </summary>
[HttpPost]
public IHttpActionResult SaveArrivalPlan([FromBody] JObject obj)
{
try
{
int deliveryId = obj["DeliveryId"].ToStringEx().ToInt();
int id = obj["Id"].ToStringEx().ToInt();
int batchNo = obj["BatchNo"].ToStringEx().ToInt();
string expectedDate = obj["ExpectedArrivalDate"]?.ToString() ?? "";
string actualDate = obj["ActualArrivalDate"]?.ToString() ?? "";
decimal arrivalQty = Dec(obj["ArrivalQty"]);
string status = obj["Status"]?.ToString() ?? "待发货";
if (deliveryId <= 0) return Ok(new { success = false, message = "交付ID无效" });
string setExpected = string.IsNullOrEmpty(expectedDate) ? "NULL" : $"'{expectedDate}'";
string setActual = string.IsNullOrEmpty(actualDate) ? "NULL" : $"'{actualDate}'";
if (id > 0)
{
db.ExecuteNonQuery($@"UPDATE Arrival_Plan SET
BatchNo={batchNo}, ExpectedArrivalDate={setExpected}, ActualArrivalDate={setActual},
ArrivalQty={arrivalQty}, Status=N'{Sql(status)}', UpdateTime=GETDATE() WHERE Id={id}");
}
else
{
// 获取当前最大批次
DataTable dt = db.ExecuteTable($"SELECT ISNULL(MAX(BatchNo),0)+1 AS NextNo FROM Arrival_Plan WHERE DeliveryId={deliveryId}");
int nextNo = Int(dt.Rows[0]["NextNo"]);
if (batchNo <= 0) batchNo = nextNo;
db.ExecuteNonQuery($@"INSERT INTO Arrival_Plan (DeliveryId,BatchNo,ExpectedArrivalDate,ActualArrivalDate,ArrivalQty,Status)
VALUES ({deliveryId},{batchNo},{setExpected},{setActual},{arrivalQty},N'{Sql(status)}')");
}
// 更新交付主表的预计到货日期 = 最近一次未到货批次
UpdateDeliveryArrivalDate(deliveryId);
// 到货计划变化后重算停线日
RecalcDeliveryStopDate(deliveryId);
// 停线日/到货日都更新完后,用最新的 ExpectedArrivalDate 重判一次风险
RecalcDeliveryRiskOnly(deliveryId);
return Ok(new { success = true, message = "保存成功" });
}
catch (Exception ex)
{
Logger.Error("SaveArrivalPlan异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 批量导入到货计划(Excel 模板:第 1 列=子项物料编码,后续列=日期X月X日;每个有数量>=1 的格子生成 1 条批次)
/// 只保存数据,不触发到货日/停线日重算(统一交给重算交付数据按钮)
/// </summary>
[HttpPost]
public IHttpActionResult ImportBatchArrivalPlan([FromBody] JObject obj)
{
try
{
string fileBase64 = obj["FileBase64"]?.ToString() ?? "";
if (string.IsNullOrEmpty(fileBase64)) return Ok(new { success = false, message = "文件为空" });
byte[] fileBytes;
try { fileBytes = Convert.FromBase64String(fileBase64); }
catch { return Ok(new { success = false, message = "文件Base64解码失败" }); }
List<string[]> rows = ExcelHelper.ReadXlsXmlRows(fileBytes);
if (rows == null || rows.Count < 2)
return Ok(new { success = false, message = "文件无有效数据行" });
// 表头解析
string[] header = rows[0];
List<int> dateColIdx = new List<int>();
List<string> dateColVal = new List<string>();
for (int c = 1; c < header.Length; c++)
{
string h = (header[c] ?? "").Trim();
if (string.IsNullOrEmpty(h)) continue;
string d = ParseHeaderDate(h);
if (string.IsNullOrEmpty(d))
return Ok(new { success = false, message = $"第 {c + 1} 列表头[{h}]无法识别为日期" });
dateColIdx.Add(c);
dateColVal.Add(d);
}
if (dateColIdx.Count == 0)
return Ok(new { success = false, message = "未找到任何日期列" });
// 按子物料编码分组收集 (date,qty)
Dictionary<string, List<KeyValuePair<string, decimal>>> matMap = new Dictionary<string, List<KeyValuePair<string, decimal>>>();
for (int r = 1; r < rows.Count; r++)
{
string[] cells = rows[r];
if (cells == null || cells.Length == 0) continue;
string code = (cells.Length > 0 ? (cells[0] ?? "").Trim() : "");
if (string.IsNullOrEmpty(code)) continue;
if (!matMap.ContainsKey(code))
matMap[code] = new List<KeyValuePair<string, decimal>>();
for (int k = 0; k < dateColIdx.Count; k++)
{
int idx = dateColIdx[k];
if (idx >= cells.Length) continue;
string raw = (cells[idx] ?? "").Trim();
if (string.IsNullOrEmpty(raw)) continue;
if (!decimal.TryParse(raw, out decimal q)) continue;
if (q <= 0) continue;
matMap[code].Add(new KeyValuePair<string, decimal>(dateColVal[k], q));
}
}
if (matMap.Count == 0)
return Ok(new { success = false, message = "未找到任何有效的到货计划数据" });
// 查询 Material_Delivery 中所有存在的子物料对应 DeliveryId
string codes = string.Join(",", matMap.Keys.Select(x => $"'{Sql(x)}'"));
DataTable dtMap = db.ExecuteTable($"SELECT Id, ChildMaterialId FROM Material_Delivery WHERE ChildMaterialId IN ({codes})");
Dictionary<string, int> deliveryMap = new Dictionary<string, int>();
foreach (DataRow dr in dtMap.Rows)
{
string c = dr["ChildMaterialId"].ToStringEx().Trim();
int id = Int(dr["Id"]);
if (!deliveryMap.ContainsKey(c)) deliveryMap[c] = id;
}
int totalInserted = 0;
int totalDeleted = 0;
List<string> miss = new List<string>();
List<int> touchedDelivery = new List<int>();
var inv = System.Globalization.CultureInfo.InvariantCulture;
foreach (var kv in matMap)
{
string code = kv.Key;
if (!deliveryMap.ContainsKey(code)) { miss.Add(code); continue; }
int deliveryId = deliveryMap[code];
touchedDelivery.Add(deliveryId);
var list = kv.Value.OrderBy(x => x.Key).ToList();
// 重复导入:先清掉该 DeliveryId 的旧到货计划,再按新模板重新插入(批次号从 1 开始)
int deleted = db.ExecuteNonQuery($"DELETE FROM Arrival_Plan WHERE DeliveryId={deliveryId}");
totalDeleted += deleted;
int nextNo = 0;
foreach (var item in list)
{
nextNo++;
db.ExecuteNonQuery($@"INSERT INTO Arrival_Plan (DeliveryId, BatchNo, ExpectedArrivalDate, ArrivalQty, Status)
VALUES ({deliveryId}, {nextNo}, '{item.Key}', {item.Value.ToString(inv)}, N'待发货')");
totalInserted++;
}
}
// 导入后刷新各交付主表的预计到货日(取最早未到货批次;到货计划被清空则清空预计到货日)
foreach (int did in touchedDelivery) UpdateDeliveryArrivalDate(did);
string msg = $"导入完成,清除 {totalDeleted} 个旧批次,新增 {totalInserted} 个到货批次,涉及 {matMap.Count - miss.Count} 个物料";
if (miss.Count > 0) msg += $";未找到交付记录的物料:{string.Join("、", miss)}";
return Ok(new { success = true, message = msg, inserted = totalInserted, deleted = totalDeleted, notFound = miss });
}
catch (Exception ex)
{
Logger.Error("ImportBatchArrivalPlan异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 删除到货计划批次
/// </summary>
[HttpPost]
public IHttpActionResult DeleteArrivalPlan([FromBody] JObject obj)
{
try
{
int id = obj["Id"].ToStringEx().ToInt();
DataTable dt = db.ExecuteTable($"SELECT DeliveryId FROM Arrival_Plan WHERE Id={id}");
int deliveryId = dt.Rows.Count > 0 ? Int(dt.Rows[0]["DeliveryId"]) : 0;
db.ExecuteNonQuery($"DELETE FROM Arrival_Plan WHERE Id={id}");
if (deliveryId > 0)
{
UpdateDeliveryArrivalDate(deliveryId);
RecalcDeliveryStopDate(deliveryId);
RecalcDeliveryRiskOnly(deliveryId);
}
return Ok(new { success = true, message = "删除成功" });
}
catch (Exception ex)
{
Logger.Error("DeleteArrivalPlan异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 更新交付主表的预计到货日期 = 最早一个"未过期"批次的预计到货日期
/// 业务口径:Arrival_Plan 每条记录都视为"会到货",不再依据 Status 判断
/// </summary>
private void UpdateDeliveryArrivalDate(int deliveryId)
{
// 该交付是否还存在任何到货计划批次
DataTable anyDt = db.ExecuteTable($"SELECT COUNT(*) AS CNT FROM Arrival_Plan WHERE DeliveryId={deliveryId}");
int anyCount = anyDt.Rows.Count > 0 ? Int(anyDt.Rows[0]["CNT"]) : 0;
// 到货计划已全部删除 → 清空预计到货日,避免残留旧日期
if (anyCount == 0)
{
db.ExecuteNonQuery($"UPDATE Material_Delivery SET ExpectedArrivalDate=NULL WHERE Id={deliveryId}");
return;
}
// 取最早一个"未过期"批次的预计到货日
// 排除条件:ExpectedArrivalDate 为空 或 <今天(避免过期日期被选中)
string todayStr = DateTime.Now.Date.ToString("yyyy-MM-dd");
DataTable dt = db.ExecuteTable($@"
SELECT TOP 1 ExpectedArrivalDate FROM Arrival_Plan
WHERE DeliveryId={deliveryId}
AND ExpectedArrivalDate IS NOT NULL
AND ExpectedArrivalDate >= '{todayStr}'
ORDER BY ExpectedArrivalDate ASC");
if (dt.Rows.Count > 0)
{
string date = DateStr(dt.Rows[0]["ExpectedArrivalDate"]);
db.ExecuteNonQuery($@"UPDATE Material_Delivery SET ExpectedArrivalDate='{date}' WHERE Id={deliveryId}");
}
else
{
// 没有"未到货且未过期"的批次(要么全部已到货,要么全部过期)
// → 清空预计到货日,避免主表残留过期日期(如 9/1 那种已经过了的日期)
db.ExecuteNonQuery($"UPDATE Material_Delivery SET ExpectedArrivalDate=NULL WHERE Id={deliveryId}");
}
// 备注:原"还有到货计划但全部已到货:保留原预计到货日不动"逻辑已移除,
// 改为统一清空,避免过期日期长期残留导致风险被误判。
}
/// <summary>
/// 到货计划变化后,重算该交付记录的停线日(风险判定交由 UpdateDeliveryArrivalDate 之后的 RecalcDeliveryRiskOnly 处理)
/// </summary>
private void RecalcDeliveryStopDate(int deliveryId)
{
DataTable dt = db.ExecuteTable($@"
SELECT D.ScheduleId, D.BOMVersion FROM Material_Delivery D WHERE D.Id={deliveryId}");
if (dt.Rows.Count == 0) return;
// 重算停线日(全局口径:只统计明细日期>=当天的需求)
CalcStopDateGlobal();
}
/// <summary>
/// 仅重判交付风险(不重算停线日)。用于 ExpectedArrivalDate 变更后立即刷新风险。
/// </summary>
private void RecalcDeliveryRiskOnly(int deliveryId)
{
DataTable dr = db.ExecuteTable($@"
SELECT D.ExpectedStopDate, D.ExpectedArrivalDate FROM Material_Delivery D WHERE D.Id={deliveryId}");
if (dr.Rows.Count == 0) return;
DataRow r = dr.Rows[0];
string stop = DateStr(r["ExpectedStopDate"]);
string arrival = DateStr(r["ExpectedArrivalDate"]);
string risk;
if (string.IsNullOrEmpty(stop)) risk = "安全";
else if (string.IsNullOrEmpty(arrival)) risk = "断线";
else
{
DateTime sd, ad;
if (DateTime.TryParse(stop, out sd) && DateTime.TryParse(arrival, out ad))
{
if (ad > sd) risk = "断线";
else if (ad == sd) risk = "预警";
else risk = "安全";
}
else risk = "断线";
}
db.ExecuteNonQuery($"UPDATE Material_Delivery SET DeliveryRisk=N'{risk}', UpdateTime=GETDATE() WHERE Id={deliveryId}");
}
#region ===== 看板展示 =====
/// <summary>
/// 获取看板统计卡片数据(全局物料维度)
/// </summary>
[HttpGet]
public IHttpActionResult GetBoardStats(string productType = "", string risk = "")
{
try
{
string where = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(productType)) where += $" AND D.ProductTypes LIKE '%{Sql(productType)}%' ";
if (!string.IsNullOrEmpty(risk)) where += $" AND D.DeliveryRisk=N'{Sql(risk)}' ";
DataTable dt = db.ExecuteTable($@"
SELECT
COUNT(*) AS Total,
SUM(CASE WHEN D.DeliveryRisk='断线' THEN 1 ELSE 0 END) AS Danger,
SUM(CASE WHEN D.DeliveryRisk='预警' THEN 1 ELSE 0 END) AS Warn,
SUM(CASE WHEN D.DeliveryRisk='安全' OR D.DeliveryRisk='' OR D.DeliveryRisk IS NULL THEN 1 ELSE 0 END) AS Safe
FROM Material_Delivery D {where}");
int total = Int(dt.Rows[0]["Total"]);
int danger = Int(dt.Rows[0]["Danger"]);
int warn = Int(dt.Rows[0]["Warn"]);
int safe = Int(dt.Rows[0]["Safe"]);
return Ok(new
{
success = true,
data = new
{
total = total,
danger = danger,
warn = warn,
safe = safe
}
});
}
catch (Exception ex)
{
Logger.Error("GetBoardStats异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取看板分组摘要(按产品类型分组统计,全局物料维度)
/// </summary>
[HttpGet]
public IHttpActionResult GetBoardGroups()
{
try
{
// 按产品类型拆分统计(ProductTypes 为逗号分隔多值)
DataTable dt = db.ExecuteTable(@"
SELECT T.TypeName AS ProductType,
COUNT(DISTINCT D.Id) AS MaterialCount,
SUM(CASE WHEN D.DeliveryRisk='断线' THEN 1 ELSE 0 END) AS DangerCount,
SUM(CASE WHEN D.DeliveryRisk='预警' THEN 1 ELSE 0 END) AS WarnCount
FROM Material_Delivery D
CROSS APPLY (
SELECT LTRIM(RTRIM(value)) AS TypeName
FROM STRING_SPLIT(ISNULL(NULLIF(D.ProductTypes,N''),N'未分类'), ',')
) T
GROUP BY T.TypeName
ORDER BY T.TypeName");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
int danger = Int(r["DangerCount"]);
int warn = Int(r["WarnCount"]);
string status;
if (danger > 0) status = "需处理";
else if (warn > 0) status = "关注";
else status = "正常";
list.Add(new Dictionary<string, object>
{
["ProductType"] = r["ProductType"].ToStringEx(),
["MaterialCount"] = r["MaterialCount"].ToStringEx(),
["DangerCount"] = r["DangerCount"].ToStringEx(),
["WarnCount"] = r["WarnCount"].ToStringEx(),
["Status"] = status
});
}
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("GetBoardGroups异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取看板明细(全局物料维度,可按产品类型筛选)
/// </summary>
[HttpGet]
public IHttpActionResult GetBoardDetail(string productType = "", string risk = "", string keyword = "", int page = 1, int pageSize = 100)
{
try
{
string where = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(productType)) where += $" AND D.ProductTypes LIKE '%{Sql(productType)}%' ";
if (!string.IsNullOrEmpty(risk)) where += $" AND D.DeliveryRisk=N'{Sql(risk)}' ";
if (!string.IsNullOrEmpty(keyword)) where += $" AND (D.ChildMaterialId LIKE '%{Sql(keyword)}%' OR D.MaterialName LIKE N'%{Sql(keyword)}%') ";
DataTable dtCount = db.ExecuteTable($"SELECT COUNT(*) AS CNT FROM Material_Delivery D {where}");
int total = Int(dtCount.Rows[0]["CNT"]);
int offset = (page - 1) * pageSize;
DataTable dt = db.ExecuteTable($@"
SELECT D.*,
(SELECT ISNULL(SUM(DailyRequirement),0) FROM Material_Requirement R
WHERE R.ChildMaterialId=D.ChildMaterialId) AS TotalRequirement,
(SELECT ISNULL(MAX(DailyRequirement),0) FROM Material_Requirement R
WHERE R.ChildMaterialId=D.ChildMaterialId) AS DailyReq,
(SELECT MIN(PlanDate) FROM Material_Requirement R
WHERE R.ChildMaterialId=D.ChildMaterialId) AS FirstNeedDate,
(SELECT MAX(PlanDate) FROM Material_Requirement R
WHERE R.ChildMaterialId=D.ChildMaterialId) AS LastNeedDate,
(SELECT ISNULL(SUM(ArrivalQty),0) FROM Arrival_Plan A WHERE A.DeliveryId=D.Id AND A.Status='已到货') AS ArrivedQty
FROM Material_Delivery D
{where}
ORDER BY D.BOMLevel, D.ChildMaterialId
OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
// 风险等级
string riskLevel = r["DeliveryRisk"].ToStringEx();
if (string.IsNullOrEmpty(riskLevel)) riskLevel = "安全";
list.Add(new Dictionary<string, object>
{
["Id"] = r["Id"].ToStringEx(),
["ProductTypes"] = r["ProductTypes"].ToStringEx(),
["ChildMaterialId"] = r["ChildMaterialId"].ToStringEx(),
["MaterialName"] = r["MaterialName"].ToStringEx(),
["BOMLevel"] = r["BOMLevel"].ToStringEx(),
["DailyReq"] = NumStr(r["DailyReq"]),
["TotalRequirement"] = NumStr(r["TotalRequirement"]),
["AvailableStock"] = NumStr(r["AvailableStock"]),
["InTransitQty"] = NumStr(r["InTransitQty"]),
["ExpectedStopDate"] = DateStr(r["ExpectedStopDate"]),
["ExpectedArrivalDate"] = DateStr(r["ExpectedArrivalDate"]),
["Supplier"] = r["Supplier"].ToStringEx(),
["DeliveryRisk"] = riskLevel,
["FirstNeedDate"] = DateStr(r["FirstNeedDate"]),
["LastNeedDate"] = DateStr(r["LastNeedDate"]),
["ArrivedQty"] = NumStr(r["ArrivedQty"])
});
}
return Ok(new { success = true, data = list, total = total, page = page, pageSize = pageSize });
}
catch (Exception ex)
{
Logger.Error("GetBoardDetail异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取甘特图数据(子物料的每日需求与到货)
/// </summary>
[HttpGet]
public IHttpActionResult GetGanttData(string productType = "", string risk = "断线,预警")
{
try
{
// 获取日期范围
DataTable dtRange = db.ExecuteTable("SELECT MIN(PlanStartDate) AS StartD, MAX(PlanEndDate) AS EndD FROM Production_Schedule");
string start = dtRange.Rows.Count > 0 ? DateStr(dtRange.Rows[0]["StartD"]) : "";
string end = dtRange.Rows.Count > 0 ? DateStr(dtRange.Rows[0]["EndD"]) : "";
string where = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(productType)) where += $" AND D.ProductTypes LIKE '%{Sql(productType)}%' ";
if (!string.IsNullOrEmpty(risk))
{
string[] risks = risk.Split(',');
List<string> riskCond = new List<string>();
foreach (var rk in risks) riskCond.Add($"D.DeliveryRisk=N'{Sql(rk)}'");
where += $" AND ({string.Join(" OR ", riskCond)}) ";
}
DataTable dt = db.ExecuteTable($@"
SELECT D.Id, D.ProductCode, D.BOMVersion, D.ChildMaterialId, D.MaterialName, D.BOMLevel, D.DeliveryRisk,
D.ExpectedStopDate, D.ExpectedArrivalDate,
R.PlanDate, R.DailyRequirement,
A.BatchNo, A.ExpectedArrivalDate AS ArrivalExpectedDate, A.ActualArrivalDate, A.ArrivalQty, A.Status AS ArrivalStatus
FROM Material_Delivery D
LEFT JOIN Material_Requirement R ON D.ScheduleId=R.ScheduleId AND D.ChildMaterialId=R.ChildMaterialId AND D.BOMLevel=R.BOMLevel
LEFT JOIN Arrival_Plan A ON D.Id=A.DeliveryId
{where}
ORDER BY D.ProductCode, D.BOMLevel, D.ChildMaterialId, R.PlanDate");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["DeliveryId"] = r["Id"].ToStringEx(),
["ProductCode"] = r["ProductCode"].ToStringEx(),
["BOMVersion"] = r["BOMVersion"].ToStringEx(),
["ChildMaterialId"] = r["ChildMaterialId"].ToStringEx(),
["MaterialName"] = r["MaterialName"].ToStringEx(),
["BOMLevel"] = r["BOMLevel"].ToStringEx(),
["DeliveryRisk"] = r["DeliveryRisk"].ToStringEx(),
["ExpectedStopDate"] = DateStr(r["ExpectedStopDate"]),
["ExpectedArrivalDate"] = DateStr(r["ExpectedArrivalDate"]),
["PlanDate"] = DateStr(r["PlanDate"]),
["DailyRequirement"] = NumStr(r["DailyRequirement"]),
["ArrivalExpectedDate"] = DateStr(r["ArrivalExpectedDate"]),
["ActualArrivalDate"] = DateStr(r["ActualArrivalDate"]),
["ArrivalQty"] = NumStr(r["ArrivalQty"]),
["ArrivalStatus"] = r["ArrivalStatus"].ToStringEx()
});
}
return Ok(new { success = true, data = list, startDate = start, endDate = end });
}
catch (Exception ex)
{
Logger.Error("GetGanttData异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 看板首页 4 卡片统计
/// </summary>
[HttpGet]
public IHttpActionResult GetBoardSummary()
{
try
{
string todayStr = DateTime.Now.ToString("yyyy-MM-dd");
// 只统计未结束排程(计划完成日>=今天)的物料
DataTable dt = db.ExecuteTable($@"
SELECT D.DeliveryRisk, COUNT(*) AS CNT
FROM Material_Delivery D
WHERE D.ScheduleId IN (SELECT Id FROM Production_Schedule WHERE PlanEndDate>='{todayStr}')
GROUP BY D.DeliveryRisk");
int total = 0, broken = 0, warn = 0, safe = 0;
foreach (DataRow r in dt.Rows)
{
int cnt = Int(r["CNT"]);
total += cnt;
string risk = r["DeliveryRisk"].ToStringEx();
if (risk == "断线") broken = cnt;
else if (risk == "预警") warn = cnt;
else if (risk == "安全") safe = cnt;
}
return Ok(new { success = true, total = total, broken = broken, warn = warn, safe = safe });
}
catch (Exception ex)
{
Logger.Error("GetBoardSummary异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 看板首页 风险下钻(点击卡片后查看明细,支持分页/搜索/过滤)
/// </summary>
[HttpGet]
public IHttpActionResult GetBoardRiskList(string risk = "", string productType = "", string keyword = "", string supplier = "", string purchaser = "", int page = 1, int pageSize = 50)
{
try
{
if (page < 1) page = 1;
if (pageSize < 1 || pageSize > 100000) pageSize = 50;
int offset = (page - 1) * pageSize;
string todayStr = DateTime.Now.ToString("yyyy-MM-dd");
string where = " WHERE S.PlanEndDate>=@today ";
if (!string.IsNullOrEmpty(risk)) where += $" AND D.DeliveryRisk=N'{Sql(risk)}' ";
if (!string.IsNullOrEmpty(productType)) where += $" AND (','+ISNULL(D.ProductTypes,'')+',') LIKE '%,{Sql(productType)},%' ";
if (!string.IsNullOrEmpty(supplier)) where += $" AND ISNULL(D.Supplier,'') LIKE N'%{Sql(supplier)}%' ";
if (!string.IsNullOrEmpty(purchaser)) where += $" AND ISNULL(D.Purchaser,'') LIKE N'%{Sql(purchaser)}%' ";
if (!string.IsNullOrEmpty(keyword))
{
string kw = Sql(keyword);
where += $" AND (D.ChildMaterialId LIKE '%{kw}%' OR D.MaterialName LIKE N'%{kw}%' OR S.ProductCode LIKE '%{kw}%') ";
}
string whereFinal = where.Replace("@today", "'" + todayStr + "'");
// 1) 查询全部符合条件的交付记录(先按物料编码合并,再做内存分页,避免同一物料跨页拆分)
DataTable dt = db.ExecuteTable($@"
SELECT D.Id, D.ChildMaterialId, D.MaterialName, D.BOMLevel, D.DeliveryRisk,
D.AvailableStock, D.ExpectedStopDate, D.ExpectedArrivalDate,
D.ZhenZeStock, D.JinJiangStock, D.ZiYangStock,
D.MassInTransit, D.ResearchInTransit, D.LineSideStock, D.ReceivedQty,
D.ERPStockQty, D.Remark, D.ProductTypes,
S.ProductCode, S.BOMVersion,
D.Purchaser, D.Supplier
FROM Material_Delivery D
INNER JOIN Production_Schedule S ON D.ScheduleId=S.Id
{whereFinal}
ORDER BY (CASE D.DeliveryRisk WHEN N'断线' THEN 0 WHEN N'预警' THEN 1 WHEN N'安全' THEN 2 ELSE 3 END),
D.ChildMaterialId");
// 3) 批量聚合"该物料用于哪些产品"(按 ChildMaterialId 聚合 Product_BOM_Detail.ProductCode,去重)
Dictionary<string, List<string>> prodCodeMap = new Dictionary<string, List<string>>();
if (dt.Rows.Count > 0)
{
var matIds = dt.Rows.Cast<DataRow>().Select(r => "'" + Sql(r["ChildMaterialId"].ToString()) + "'").Distinct();
string matIdList = string.Join(",", matIds);
DataTable pcDt = db.ExecuteTable($@"
SELECT DISTINCT BD.ChildMaterialId, BD.ProductCode
FROM Product_BOM_Detail BD
WHERE BD.ChildMaterialId IN ({matIdList}) AND ISNULL(BD.IsVirtual,0)=0
ORDER BY BD.ProductCode");
foreach (DataRow pr in pcDt.Rows)
{
string mid = pr["ChildMaterialId"].ToString();
string pc = pr["ProductCode"].ToStringEx();
if (!prodCodeMap.ContainsKey(mid)) prodCodeMap[mid] = new List<string>();
if (!string.IsNullOrEmpty(pc) && !prodCodeMap[mid].Contains(pc)) prodCodeMap[mid].Add(pc);
}
}
// 4) 批量聚合:申请单总量(量产+研发,直接求和)/ 订单总量(量产+研发,直接求和)/ 到货计划数量
// 口径参考 AutoSyncErpStockGlobalCore 重算逻辑:申请/PO 均取今年已审核(FSTATUS='40')单据,按物料直接求和不扣减;
// 到货计划按交付ID聚合 Arrival_Plan 全部批次数量(每条批次都视为会到货)
Dictionary<string, decimal> reqQtyMap = new Dictionary<string, decimal>();
Dictionary<string, decimal> poQtyMap = new Dictionary<string, decimal>();
Dictionary<string, decimal> arrivalQtyMap = new Dictionary<string, decimal>();
Dictionary<string, decimal> peakDailyMap = new Dictionary<string, decimal>();
if (dt.Rows.Count > 0)
{
string yearStart = new DateTime(DateTime.Now.Year, 1, 1).ToString("yyyy-MM-dd");
var matIdList2 = dt.Rows.Cast<DataRow>().Select(r => "'" + Sql(r["ChildMaterialId"].ToString()) + "'").Distinct();
string matIds2 = string.Join(",", matIdList2);
// 申请单总量(量产 CGSQD01/20 + 研发 CGSQD07)
DataTable reqDt = db.ExecuteTable($@"
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(S1.FQTY,0)) AS ReqQty
FROM P_PUR_Requisition S
INNER JOIN P_PUR_RequisitionENTRY S1 ON S.FINTERID=S1.FINTERID
INNER JOIN T_BAS_BILLTYPE S2 ON S.FBILLTYPEID=S2.FINTERID
INNER JOIN T_BD_MATERIAL M ON S1.FMATERIALID=M.FINTERID
WHERE S.FSTATUS='40' AND S.FDATE>='{yearStart}' AND S.FDELETED=0
AND S2.FNUMBER IN ('CGSQD01_SYS','CGSQD20_SYS','CGSQD07_SYS')
AND M.FNUMBER IN ({matIds2})
GROUP BY M.FNUMBER");
foreach (DataRow rr in reqDt.Rows)
reqQtyMap[rr["MaterialCode"].ToString()] = Dec(rr["ReqQty"]);
// 订单总量(量产 CGDD01/02/21 排除GSSH + 研发 CGDD11)
DataTable poDt = db.ExecuteTable($@"
SELECT M.FNUMBER AS MaterialCode, SUM(ISNULL(S1.FQTY,0)) AS PoQty
FROM T_PUR_POORDER S
INNER JOIN T_PUR_POORDERENTRY S1 ON S.FINTERID=S1.FINTERID
INNER JOIN T_BAS_BILLTYPE S2 ON S.FBILLTYPEID=S2.FINTERID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY S3 ON S1.F_RESEARCHPROJECT=S3.FINTERID
INNER JOIN T_BD_MATERIAL M ON S1.FMATERIALID=M.FINTERID
WHERE S.FSTATUS='40' AND S.FDATE>='{yearStart}' AND S.FDELETED=0
AND ((S2.FNUMBER IN ('CGDD01_SYS','CGDD02_SYS','CGDD21_SYS') AND ISNULL(S3.FNUMBER,'')<>'GSSH')
OR S2.FNUMBER='CGDD11_SYS')
AND M.FNUMBER IN ({matIds2})
GROUP BY M.FNUMBER");
foreach (DataRow rr in poDt.Rows)
poQtyMap[rr["MaterialCode"].ToString()] = Dec(rr["PoQty"]);
// 到货计划数量(按交付ID聚合,与重算逻辑一致:只统计有预计到货日的批次)
var delIdList = dt.Rows.Cast<DataRow>().Select(r => r["Id"].ToString()).Distinct();
string delIds = string.Join(",", delIdList);
DataTable arrDt = db.ExecuteTable($@"
SELECT DeliveryId, SUM(ISNULL(ArrivalQty,0)) AS ArrivalQty
FROM Arrival_Plan
WHERE DeliveryId IN ({delIds}) AND ExpectedArrivalDate IS NOT NULL
GROUP BY DeliveryId");
foreach (DataRow rr in arrDt.Rows)
arrivalQtyMap[rr["DeliveryId"].ToString()] = Dec(rr["ArrivalQty"]);
// 峰值单日需求:口径与停线日计算一致——未结束排程中,按日期汇总该物料需求后取单日最大
// (不能把各排程的"第一条需求"直接相加,各排程起始日期不同会导致跨日期拼数)
DataTable peakDt = db.ExecuteTable($@"
SELECT T.ChildMaterialId, MAX(T.DayQty) AS PeakDaily
FROM (
SELECT R.ChildMaterialId, R.PlanDate, SUM(ISNULL(R.DailyRequirement,0)) AS DayQty
FROM Material_Requirement R
INNER JOIN Production_Schedule S ON R.ScheduleId=S.Id
WHERE R.ChildMaterialId IN ({matIds2})
AND R.PlanDate >= '{todayStr}'
AND S.PlanEndDate >= '{todayStr}'
GROUP BY R.ChildMaterialId, R.PlanDate
) T
GROUP BY T.ChildMaterialId");
foreach (DataRow rr in peakDt.Rows)
peakDailyMap[rr["ChildMaterialId"].ToString()] = Dec(rr["PeakDaily"]);
}
// 4) 按物料编码合并(相同物料:日需求取峰值单日需求(与停线日计算同口径,按日期汇总后取最大);
// 预计停线日/预计到货日取最早;
// 库存类字段重算时为物料级(同一物料各产品行相同),取最大即可;
// 供应商/采购员/产品类型/BOM版本/备注 去重后逗号分隔;风险取最严重)
List<string> matOrder = new List<string>();
Dictionary<string, List<DataRow>> matGroups = new Dictionary<string, List<DataRow>>();
foreach (DataRow r in dt.Rows)
{
string mid = r["ChildMaterialId"].ToString();
if (!matGroups.ContainsKey(mid)) { matGroups[mid] = new List<DataRow>(); matOrder.Add(mid); }
matGroups[mid].Add(r);
}
List<Dictionary<string, object>> aggList = new List<Dictionary<string, object>>();
foreach (string mid in matOrder)
{
List<DataRow> rows = matGroups[mid];
string stopDate = "", arrDate = "";
string riskVal = rows[0]["DeliveryRisk"].ToStringEx();
string materialName = "";
int bomLevel = int.MaxValue;
decimal arrivalSum = 0;
HashSet<string> supSet = new HashSet<string>(), purSet = new HashSet<string>(),
ptSet = new HashSet<string>(), bvSet = new HashSet<string>(), rmkSet = new HashSet<string>();
foreach (DataRow r in rows)
{
string sd = DateStr(r["ExpectedStopDate"]);
if (!string.IsNullOrEmpty(sd) && (string.IsNullOrEmpty(stopDate) || string.Compare(sd, stopDate) < 0)) stopDate = sd;
string ad = DateStr(r["ExpectedArrivalDate"]);
if (!string.IsNullOrEmpty(ad) && (string.IsNullOrEmpty(arrDate) || string.Compare(ad, arrDate) < 0)) arrDate = ad;
string rk = r["DeliveryRisk"].ToStringEx();
if (RiskOrder(rk) < RiskOrder(riskVal)) riskVal = rk;
string mn = r["MaterialName"].ToStringEx();
if (mn.Length > materialName.Length) materialName = mn;
int bl = Int(r["BOMLevel"]);
if (bl < bomLevel) bomLevel = bl;
string sp = r["Supplier"].ToStringEx(); if (!string.IsNullOrEmpty(sp)) supSet.Add(sp);
string pu = r["Purchaser"].ToStringEx(); if (!string.IsNullOrEmpty(pu)) purSet.Add(pu);
string pt = r["ProductTypes"].ToStringEx(); if (!string.IsNullOrEmpty(pt)) ptSet.Add(pt);
string bv = r["BOMVersion"].ToStringEx(); if (!string.IsNullOrEmpty(bv)) bvSet.Add(bv);
string rm = r["Remark"].ToStringEx(); if (!string.IsNullOrEmpty(rm)) rmkSet.Add(rm);
string did = r["Id"].ToString();
if (arrivalQtyMap.ContainsKey(did)) arrivalSum += arrivalQtyMap[did];
}
// 三个新增字段(参考重算逻辑口径):
// 库存 = 待拣数量(已收货 ReceivedQty) + 即时库存(ERPStockQty 三地合计) + 线边仓(LineSideStock)
// 在途 = 申请单总量 + 订单总量 + 即时库存
// 到货计划 = 即时库存 + 到货计划数量
decimal erpStock = rows.Max(r => Dec(r["ERPStockQty"]));
decimal received = rows.Max(r => Dec(r["ReceivedQty"]));
decimal lineSide = rows.Max(r => Dec(r["LineSideStock"]));
decimal reqQty = reqQtyMap.ContainsKey(mid) ? reqQtyMap[mid] : 0;
decimal poQty = poQtyMap.ContainsKey(mid) ? poQtyMap[mid] : 0;
decimal arrQty = arrivalSum;
string productCodes = prodCodeMap.ContainsKey(mid) ? string.Join(",", prodCodeMap[mid]) : "";
aggList.Add(new Dictionary<string, object>
{
["ChildMaterialId"] = mid,
["MaterialName"] = materialName,
["BOMLevel"] = bomLevel == int.MaxValue ? "" : bomLevel.ToString(),
["DeliveryRisk"] = riskVal,
["AvailableStock"] = NumStr(rows.Max(r => Dec(r["AvailableStock"]))),
["ExpectedStopDate"] = stopDate,
["ExpectedArrivalDate"] = arrDate,
["ZhenZeStock"] = NumStr(rows.Max(r => Dec(r["ZhenZeStock"]))),
["JinJiangStock"] = NumStr(rows.Max(r => Dec(r["JinJiangStock"]))),
["ZiYangStock"] = NumStr(rows.Max(r => Dec(r["ZiYangStock"]))),
["MassInTransit"] = NumStr(rows.Max(r => Dec(r["MassInTransit"]))),
["ResearchInTransit"] = NumStr(rows.Max(r => Dec(r["ResearchInTransit"]))),
["LineSideStock"] = NumStr(lineSide),
["ReceivedQty"] = NumStr(received),
["StockQty"] = NumStr(received + erpStock + lineSide),
["InTransitQty"] = NumStr(reqQty + poQty + erpStock),
["ArrivalPlanQty"] = NumStr(erpStock + arrQty),
["Remark"] = string.Join(",", rmkSet),
["ProductType"] = string.Join(",", ptSet),
["ProductCode"] = productCodes,
["BOMVersion"] = string.Join(",", bvSet),
["Purchaser"] = string.Join(",", purSet),
["Supplier"] = string.Join(",", supSet),
["DailyRequirement"] = NumStr(peakDailyMap.ContainsKey(mid) ? peakDailyMap[mid] : 0)
});
}
// 5) 内存分页(合并后的行数即总数)
int total = aggList.Count;
List<Dictionary<string, object>> list = aggList.Skip(offset).Take(pageSize).ToList();
return Ok(new { success = true, data = list, total = total, page = page, pageSize = pageSize });
}
catch (Exception ex)
{
Logger.Error("GetBoardRiskList异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 看板首页 甘特图(按物料聚合每日需求/到货)
/// 优化:日期范围从今天开始,去除历史日期
/// </summary>
[HttpGet]
public IHttpActionResult GetBoardGantt(string productType = "", string risk = "", string onlyToday = "true")
{
try
{
string todayStr = DateTime.Now.ToString("yyyy-MM-dd");
// 1) 计算日期范围:当月 1 号 ~ 当月最后一天
DateTime nowDt = DateTime.Now;
DateTime monthStart = new DateTime(nowDt.Year, nowDt.Month, 1);
DateTime monthEnd = monthStart.AddMonths(1).AddDays(-1);
string start = monthStart.ToString("yyyy-MM-dd");
string end = monthEnd.ToString("yyyy-MM-dd");
// 2) 取物料清单(按风险/产品类型过滤)
string matWhere = $" WHERE S.PlanEndDate>='{todayStr}' ";
if (!string.IsNullOrEmpty(risk)) matWhere += $" AND D.DeliveryRisk=N'{Sql(risk)}' ";
if (!string.IsNullOrEmpty(productType)) matWhere += $" AND (','+ISNULL(D.ProductTypes,'')+',' LIKE '%,{Sql(productType)},%') ";
DataTable matDt = db.ExecuteTable($@"
SELECT D.Id, D.ScheduleId, D.ChildMaterialId, D.MaterialName, D.BOMLevel, D.DeliveryRisk,
D.AvailableStock, D.ExpectedStopDate, D.ExpectedArrivalDate,
S.ProductCode, S.BOMVersion
FROM Material_Delivery D
INNER JOIN Production_Schedule S ON D.ScheduleId=S.Id
{matWhere}
ORDER BY (CASE D.DeliveryRisk WHEN N'断线' THEN 0 WHEN N'预警' THEN 1 ELSE 2 END), D.ChildMaterialId");
// 3) 批量取需求(Material_Requirement 关联键 = ScheduleId+ChildMaterialId)
Dictionary<string, Dictionary<string, double>> reqMap = new Dictionary<string, Dictionary<string, double>>();
if (matDt.Rows.Count > 0)
{
var schIds = matDt.Rows.Cast<DataRow>().Select(r => r["ScheduleId"].ToString()).Distinct();
string schIdList = string.Join(",", schIds);
var matIds = matDt.Rows.Cast<DataRow>().Select(r => "'" + Sql(r["ChildMaterialId"].ToString()) + "'");
string matIdList = string.Join(",", matIds);
DataTable reqDt = db.ExecuteTable($@"
SELECT R.ScheduleId, R.ChildMaterialId, R.PlanDate, R.DailyRequirement
FROM Material_Requirement R
WHERE R.ScheduleId IN ({schIdList}) AND R.ChildMaterialId IN ({matIdList}) AND R.PlanDate>='{start}'");
foreach (DataRow r in reqDt.Rows)
{
string key = r["ScheduleId"].ToString() + "_" + r["ChildMaterialId"].ToString();
string date = DateStr(r["PlanDate"]);
double qty = Convert.ToDouble(r["DailyRequirement"] is DBNull ? 0 : r["DailyRequirement"]);
if (!reqMap.ContainsKey(key)) reqMap[key] = new Dictionary<string, double>();
reqMap[key][date] = qty;
}
}
// 4) 批量取到货
Dictionary<int, List<Dictionary<string, object>>> arrMap = new Dictionary<int, List<Dictionary<string, object>>>();
if (matDt.Rows.Count > 0)
{
var ids = matDt.Rows.Cast<DataRow>().Select(r => r["Id"].ToString());
string idList = string.Join(",", ids);
DataTable arrDt = db.ExecuteTable($@"
SELECT A.DeliveryId, A.ExpectedArrivalDate, A.ArrivalQty
FROM Arrival_Plan A
WHERE A.DeliveryId IN ({idList}) AND A.ExpectedArrivalDate>='{start}'");
foreach (DataRow r in arrDt.Rows)
{
int did = Int(r["DeliveryId"]);
string date = DateStr(r["ExpectedArrivalDate"]);
double qty = Convert.ToDouble(r["ArrivalQty"] is DBNull ? 0 : r["ArrivalQty"]);
if (!arrMap.ContainsKey(did)) arrMap[did] = new List<Dictionary<string, object>>();
arrMap[did].Add(new Dictionary<string, object>
{
["date"] = date,
["qty"] = qty
});
}
}
// 5) 组装结果
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in matDt.Rows)
{
int did = Int(r["Id"]);
string reqKey = r["ScheduleId"].ToString() + "_" + r["ChildMaterialId"].ToString();
list.Add(new Dictionary<string, object>
{
["DeliveryId"] = did,
["ChildMaterialId"] = r["ChildMaterialId"].ToStringEx(),
["MaterialName"] = r["MaterialName"].ToStringEx(),
["BOMLevel"] = r["BOMLevel"].ToStringEx(),
["DeliveryRisk"] = r["DeliveryRisk"].ToStringEx(),
["AvailableStock"] = NumStr(r["AvailableStock"]),
["ExpectedStopDate"] = DateStr(r["ExpectedStopDate"]),
["ExpectedArrivalDate"] = DateStr(r["ExpectedArrivalDate"]),
["ProductCode"] = r["ProductCode"].ToStringEx(),
["BOMVersion"] = r["BOMVersion"].ToStringEx(),
["Requirement"] = reqMap.ContainsKey(reqKey) ? reqMap[reqKey] : new Dictionary<string, double>(),
["Arrival"] = arrMap.ContainsKey(did) ? arrMap[did] : new List<Dictionary<string, object>>()
});
}
return Ok(new { success = true, data = list, startDate = start, endDate = end, today = todayStr });
}
catch (Exception ex)
{
Logger.Error("GetBoardGantt异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取共用料汇总(按子物料编码分组)
/// </summary>
[HttpGet]
public IHttpActionResult GetSharedMaterial(string childMaterialId = "")
{
try
{
string where = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(childMaterialId)) where += $" AND D.ChildMaterialId='{Sql(childMaterialId)}' ";
DataTable dt = db.ExecuteTable($@"
SELECT D.ChildMaterialId, MAX(D.MaterialName) AS MaterialName,
COUNT(DISTINCT D.ProductCode) AS ProductCount,
SUM(CASE WHEN D.DeliveryRisk='断线' THEN 1 ELSE 0 END) AS DangerCount,
SUM(CASE WHEN D.DeliveryRisk='预警' THEN 1 ELSE 0 END) AS WarnCount,
ISNULL(SUM(I.FQTY),0) AS TotalStock
FROM Material_Delivery D
LEFT JOIN T_BD_MATERIAL M ON D.ChildMaterialId=M.FNUMBER AND M.FDELETED=0 AND M.FCOMPANYID = 986610
LEFT JOIN T_STK_INVENTORY I ON M.FINTERID=I.FMATERIALID
{where}
GROUP BY D.ChildMaterialId
ORDER BY D.ChildMaterialId");
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["ChildMaterialId"] = r["ChildMaterialId"].ToStringEx(),
["MaterialName"] = r["MaterialName"].ToStringEx(),
["ProductCount"] = r["ProductCount"].ToStringEx(),
["DangerCount"] = r["DangerCount"].ToStringEx(),
["WarnCount"] = r["WarnCount"].ToStringEx(),
["TotalStock"] = NumStr(r["TotalStock"])
});
}
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("GetSharedMaterial异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 自动登记产品到BOM主数据并展开BOM(生产排程选产品时静默调用)
/// </summary>
[HttpPost]
public IHttpActionResult AutoRegisterProduct([FromBody] JObject obj)
{
try
{
string productCode = obj["ProductCode"]?.ToString() ?? "";
string bomVersion = obj["BOMVersion"]?.ToString() ?? "";
if (string.IsNullOrEmpty(productCode))
return Ok(new { success = false, message = "产品编码不能为空" });
string msg;
int inserted = EnsureProductRegistered(productCode, bomVersion, out msg);
if (inserted < 0)
return Ok(new { success = false, message = msg, registered = false });
// 返回该产品信息(用于前端回填BOM版本、产品名称)
DataTable m = db.ExecuteTable($@"SELECT M.ProductCode, M.BOMVersion, M.ProductName FROM Product_BOM_Master M
WHERE M.ProductCode='{Sql(productCode)}' AND M.BOMVersion=(SELECT TOP 1 BOMVersion FROM Product_BOM_Master WHERE ProductCode='{Sql(productCode)}' ORDER BY UpdateTime DESC)");
string name = "";
string ver = bomVersion;
if (m.Rows.Count > 0)
{
name = m.Rows[0]["ProductName"].ToStringEx();
ver = m.Rows[0]["BOMVersion"].ToStringEx();
}
return Ok(new { success = true, message = "产品已登记并展开BOM", registered = true, productCode = productCode, bomVersion = ver, productName = name, total = inserted });
}
catch (Exception ex)
{
Logger.Error("AutoRegisterProduct异常:", ex);
return Ok(new { success = false, message = ex.Message, registered = false });
}
}
/// <summary>
/// 从金蝶物料主数据分页查询(仿金蝶数据查询窗口:支持多条件筛选、默认2000行)
/// </summary>
[HttpGet]
public IHttpActionResult GetKingdeeMaterials(string codeOp = "包含", string codeVal = "",
string nameOp = "包含", string nameVal = "", string specOp = "", string specVal = "",
string category = "", int page = 1, int pageSize = 2000)
{
try
{
// 操作符转SQL LIKE
Func<string, string, string, string> buildCond = (col, op, val) =>
{
if (string.IsNullOrEmpty(val)) return "";
string v = Sql(val);
switch (op)
{
case "包含": return $" AND {col} LIKE N'%{v}%' ";
case "不包含": return $" AND {col} NOT LIKE N'%{v}%' ";
case "等于": return $" AND {col} = N'{v}' ";
case "不等于": return $" AND {col} <> N'{v}' ";
case "开始于": return $" AND {col} LIKE N'{v}%' ";
case "结束于": return $" AND {col} LIKE N'%{v}' ";
case "为空": return $" AND ({col} IS NULL OR {col}='') ";
case "不为空": return $" AND ({col} IS NOT NULL AND {col}<>'') ";
default: return $" AND {col} LIKE N'%{v}%' ";
}
};
string where = " WHERE M.FDELETED=0 AND M.FNUMBER<>'' and FCOMPANYID = 986610 ";
where += buildCond("M.FNUMBER", codeOp, codeVal);
where += buildCond("M.FNAME", nameOp, nameVal);
where += buildCond("M.FSPECIFICATION", specOp, specVal);
if (!string.IsNullOrEmpty(category))
where += $" AND EXISTS(SELECT 1 FROM T_BD_MATERIAL_CATEGORY C WHERE C.FINTERID=M.FCATEGORYID AND C.FNAME LIKE N'%{Sql(category)}%') ";
if (page <= 0) page = 1;
if (pageSize <= 0) pageSize = 2000;
if (pageSize > 5000) pageSize = 5000;
// 总数
DataTable dtCount = db.ExecuteTable($"SELECT COUNT(*) AS CNT FROM T_BD_MATERIAL M {where}");
int total = Int(dtCount.Rows[0]["CNT"]);
int offset = (page - 1) * pageSize;
// 是否启用BOM版本:通过T_BD_BOM是否存在未删除的版本判断(辅助列)
DataTable dt = db.ExecuteTable($@"
SELECT M.FNUMBER, M.FNAME, M.FSPECIFICATION,
(SELECT TOP 1 FBILLNO FROM T_BD_BOM WHERE FMATERIALID=M.FINTERID AND FDELETED=0 ORDER BY FBILLNO) AS BomVersion,
(SELECT COUNT(*) FROM T_BD_BOM WHERE FMATERIALID=M.FINTERID AND FDELETED=0) AS BomCount
FROM T_BD_MATERIAL M
{where}
ORDER BY M.FNUMBER
OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY");
List<object> list = new List<object>();
foreach (DataRow r in dt.Rows)
{
list.Add(new
{
code = r["FNUMBER"].ToStringEx(),
name = r["FNAME"].ToStringEx(),
spec = r["FSPECIFICATION"].ToStringEx(),
category = "",
bomVersion = r["BomVersion"].ToStringEx(),
bomEnabled = Int(r["BomCount"]) > 0
});
}
return Ok(new { success = true, data = list, total = total, page = page, pageSize = pageSize });
}
catch (Exception ex)
{
Logger.Error("GetKingdeeMaterials异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
/// <summary>
/// 获取下拉选项数据(产品编码、BOM版本、来源地、供应商、采购员)
/// </summary>
[HttpGet]
public IHttpActionResult GetOptions()
{
try
{
List<object> products = new List<object>();
DataTable dtP = db.ExecuteTable("SELECT DISTINCT ProductCode FROM Product_BOM_Master ORDER BY ProductCode");
foreach (DataRow r in dtP.Rows) products.Add(r[0].ToStringEx());
List<object> versions = new List<object>();
DataTable dtV = db.ExecuteTable("SELECT DISTINCT BOMVersion FROM Product_BOM_Master ORDER BY BOMVersion");
foreach (DataRow r in dtV.Rows) versions.Add(r[0].ToStringEx());
List<object> suppliers = new List<object>();
DataTable dtS = db.ExecuteTable("SELECT DISTINCT Supplier FROM Material_Delivery WHERE Supplier<>'' ORDER BY Supplier");
foreach (DataRow r in dtS.Rows) suppliers.Add(r[0].ToStringEx());
List<object> purchasers = new List<object>();
DataTable dtPu = db.ExecuteTable("SELECT DISTINCT Purchaser FROM Material_Delivery WHERE Purchaser<>'' ORDER BY Purchaser");
foreach (DataRow r in dtPu.Rows) purchasers.Add(r[0].ToStringEx());
// 产品类型:取生产排程中人为维护的类型值
List<object> productTypes = new List<object>();
DataTable dtT = db.ExecuteTable("SELECT DISTINCT ProductType FROM Production_Schedule WHERE ISNULL(ProductType,N'')<>'' ORDER BY ProductType");
foreach (DataRow r in dtT.Rows) productTypes.Add(r[0].ToStringEx());
return Ok(new { success = true, data = new { products = products, versions = versions, suppliers = suppliers, purchasers = purchasers, productTypes = productTypes } });
}
catch (Exception ex)
{
Logger.Error("GetOptions异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
}
}