Files
GX.O3.WMS/WebApi/.svn/pristine/f5/f561234bfde489f624a17f577feee5aaca92a978.svn-base

2252 lines
113 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 Kingdee.BOS.WebApi.Client;
using log4net;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Web.Http;
namespace GXWebApi.Controllers
{
public class ProcurementController : ApiController
{
private static readonly ILog Logger = LogManager.GetLogger(typeof(ProcurementController));
#region 金蝶登录配置
private K3CloudApiClient GetKingdeeClient()
{
string acctID = "20191111204222978";
string userID = "sysmes";
string password = "Admin@123";
string erpURL = "https://gaoxian.ik3cloud.com/k3cloud/";
int erpLaType = 2052;
K3CloudApiClient client = new K3CloudApiClient(erpURL);
var loginResult = client.ValidateLogin(acctID, userID, password, erpLaType);
var resultType = JObject.Parse(loginResult)["LoginResultType"].Value<int>();
if (resultType != 1)
throw new Exception("登录金蝶云星空失败!");
return client;
}
private List<List<object>> ExecuteKingdeeQuery(K3CloudApiClient client, string formId, string fieldKeys, string filterString, string orderString = "", int topRowCount = 0, int startRow = 0, int limit = 5000)
{
JObject queryData = new JObject
{
["FormId"] = formId,
["FieldKeys"] = fieldKeys,
["OrderString"] = orderString,
["FilterString"] = filterString,
["TopRowCount"] = topRowCount,
["StartRow"] = startRow,
["Limit"] = limit
};
var result = client.ExecuteBillQuery(queryData.ToString());
if (result != null && result.Count > 0)
{
string firstCell = result[0][0].ToStringEx();
if (firstCell.StartsWith("{\""))
{
try
{
JObject errObj = JObject.Parse(firstCell);
if (errObj["Result"] != null)
{
var errors = errObj["Result"]["ResponseStatus"]?["Errors"];
if (errors != null && errors.HasValues)
{
string errMsg = errors[0]["Message"]?.ToString() ?? "金蝶查询返回错误";
throw new Exception(errMsg);
}
}
}
catch (JsonReaderException) { }
}
}
return result;
}
private string SafeGet(List<object> row, int index)
{
if (row == null || index < 0 || index >= row.Count)
return "";
object val = row[index];
return val == null ? "" : val.ToString();
}
/// <summary>
/// 格式化日期:取空格前的日期部分,"2026/1/8 0:00:00" 返回 "2026/1/8"
/// </summary>
private string FormatDate(string raw)
{
if (string.IsNullOrEmpty(raw)) return "";
int idx = raw.IndexOf(' ');
return idx > 0 ? raw.Substring(0, idx) : raw;
}
/// <summary>
/// 格式化计划交付日期:当日期为无效零日期(如 1900-01-00)时返回空字符串
/// </summary>
private string FormatPlanDate(string raw)
{
if (string.IsNullOrEmpty(raw)) return "";
string d = FormatDate(raw);
if (d == "1900-01-00" || d.StartsWith("1900-00-") || d.StartsWith("0000-"))
return "";
return d;
}
#endregion
#region 获取OA研发物料采购申请单列表
[HttpGet]
public IHttpActionResult GetRequestList(string lcbh = "", string sqrq = "", string yfxm = "", string reqName = "", string sqrxm = "", string djzk = "", string wlbmCode = "", string cncgfzr = "", string yfcgfzr = "", string gys = "", int page = 1, int pageSize = 20)
{
MySqlUtils mySqlUtils = new MySqlUtils();
try
{
WmsCommonFunction.UpUserCount("20260724001","研发采购物料管理");
string OAConnection = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = OAConnection;
mySqlUtils.GetConnection();
string pmTimeSubSql = @"(
SELECT CONCAT(wrl.operatedate, ' ', wrl.operatetime)
FROM workflow_requestlog wrl
WHERE wrl.requestid = S.requestId
AND wrl.nodeid = '844'
AND wrl.operatedate IS NOT NULL
AND wrl.logtype IN ('0','2')
ORDER BY wrl.operatedate DESC, wrl.operatetime DESC
LIMIT 1
) AS pmAuditTime";
StringBuilder sql = new StringBuilder();
sql.Append($@"SELECT
S.id, S.requestId, S.lcbh, S.djbh,
DATE_FORMAT(S.sqrq, '%Y-%m-%d') AS sqrq,
S2.REQUESTNAME, S3.lastname AS sqrxm, S4.yfxm AS yfxmmc,
(SELECT ywzt FROM formtable_main_23_dt1 WHERE mainid = S.id ORDER BY xh LIMIT 1) AS djzk,
{pmTimeSubSql}
FROM formtable_main_23 S
LEFT JOIN workflow_requestbase S2 ON S.requestId = S2.REQUESTID
LEFT JOIN Hrmresource S3 ON S.sqr = S3.ID
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id");
List<string> wheres = new List<string> { "YEAR(S.sqrq) = YEAR(CURDATE())" , "S.lcbh like 'YFCGSQ%'" };
if (!string.IsNullOrEmpty(lcbh))
{
wheres.Add($"S.lcbh LIKE '%{lcbh}%'");
}
if (!string.IsNullOrEmpty(sqrq))
{
wheres.Add($"S.sqrq = '{sqrq}'");
}
if (!string.IsNullOrEmpty(yfxm))
{
wheres.Add($"S4.yfxm LIKE '%{yfxm}%'");
}
if (!string.IsNullOrEmpty(reqName))
{
wheres.Add($"S2.REQUESTNAME LIKE '%{reqName}%'");
}
if (!string.IsNullOrEmpty(sqrxm))
{
wheres.Add($"S3.lastname LIKE '%{sqrxm}%'");
}
if (!string.IsNullOrEmpty(djzk))
{
if (djzk == "0")
wheres.Add("((SELECT ywzt FROM formtable_main_23_dt1 WHERE mainid = S.id ORDER BY xh LIMIT 1) IS NULL OR (SELECT ywzt FROM formtable_main_23_dt1 WHERE mainid = S.id ORDER BY xh LIMIT 1) = '0')");
else
wheres.Add($"(SELECT ywzt FROM formtable_main_23_dt1 WHERE mainid = S.id ORDER BY xh LIMIT 1) = '{djzk}'");
}
// 空值筛选辅助:__EMPTY__ 匹配 IS NULL OR = '',否则 LIKE 匹配
Func<string, string, string> detailCond = (val, field) =>
{
if (string.IsNullOrEmpty(val)) return null;
if (val == "空")
return $"EXISTS (SELECT 1 FROM formtable_main_23_dt1 d WHERE d.mainid = S.id AND (d.{field} IS NULL OR d.{field} = ''))";
return $"EXISTS (SELECT 1 FROM formtable_main_23_dt1 d WHERE d.mainid = S.id AND d.{field} LIKE '%{val.Replace("'","''")}%')";
};
Action<string, string> addCond = (val, field) => { var c = detailCond(val, field); if (c != null) wheres.Add(c); };
if (!string.IsNullOrEmpty(wlbmCode) && wlbmCode != "空")
{
wheres.Add($"EXISTS (SELECT 1 FROM formtable_main_23_dt1 d JOIN uf_wlbm w ON d.wlbmyf = w.id WHERE d.mainid = S.id AND w.wlbm LIKE '%{wlbmCode.Replace("'","''")}%')");
}
addCond(cncgfzr, "cncgfzr");
addCond(yfcgfzr, "yfcgfzr");
addCond(gys, "gys");
sql.Append(" WHERE " + string.Join(" AND ", wheres));
sql.Append(" ORDER BY S.sqrq DESC, S.lcbh DESC");
sql.Append($" LIMIT {(page - 1) * pageSize},{pageSize}");
DataTable dt = mySqlUtils.ExecQuery(sql.ToString());
StringBuilder countSql = new StringBuilder(@"
SELECT COUNT(0) AS total FROM formtable_main_23 S
LEFT JOIN workflow_requestbase S2 ON S.requestId = S2.REQUESTID
LEFT JOIN Hrmresource S3 ON S.sqr = S3.ID
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id");
countSql.Append(" WHERE " + string.Join(" AND ", wheres));
DataTable dtCount = mySqlUtils.ExecQuery(countSql.ToString());
int total = dtCount.Rows.Count > 0 ? Convert.ToInt32(dtCount.Rows[0]["total"]) : 0;
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["id"] = row["id"].ToStringEx(),
["requestId"] = row["requestId"].ToStringEx(),
["lcbh"] = row["lcbh"].ToStringEx(),
["djbh"] = row["djbh"].ToStringEx(),
["sqrq"] = row["sqrq"].ToStringEx(),
["REQUESTNAME"] = row["REQUESTNAME"].ToStringEx(),
["sqrxm"] = row["sqrxm"].ToStringEx(),
["yfxmmc"] = row["yfxmmc"].ToStringEx(),
["pmAuditTime"] = row["pmAuditTime"].ToStringEx(),
["djzk"] = row["djzk"].ToStringEx()
});
}
return Ok(new { success = true, data = list, total, page, pageSize });
}
catch (Exception ex)
{
Logger.Error("获取采购申请单列表异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
finally { mySqlUtils.Dispose(); }
}
#endregion
#region 看板列表(物料级明细列表)
[HttpGet]
public IHttpActionResult GetDashboardList(string lcbhNo = "", string sqrxm = "", string reqName = "", string yfxmmc = "", string matCode = "", string matName = "", string ywzt = "", string cncgfzr = "", string yfcgfzr = "", int page = 1, int pageSize = 20)
{
MySqlUtils mySqlUtils = new MySqlUtils();
try
{
WmsCommonFunction.UpUserCount("20260724002", "研发采购物料看板");
string OAConnection = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = OAConnection;
mySqlUtils.GetConnection();
StringBuilder sql = new StringBuilder();
sql.Append($@"SELECT
S.id, S.requestId, S.lcbh, S.djbh,
DATE_FORMAT(S.sqrq, '%Y-%m-%d') AS sqrq,
S2.REQUESTNAME AS reqName, S3.lastname AS sqrxm, S4.yfxm AS yfxmmc,
S1.xh, S5.wlbm AS wlbmCode, S1.wlmc, S1.sqsl, S1.ywzt,
S1.cncgfzr, S1.yfcgfzr,
DATE_FORMAT(S1.cnjhjfrq, '%Y-%m-%d') AS cnjhjfrq,
DATE_FORMAT(S1.yfjhjfrq, '%Y-%m-%d') AS yfjhjfrq
FROM formtable_main_23 S
LEFT JOIN workflow_requestbase S2 ON S.requestId = S2.REQUESTID
LEFT JOIN Hrmresource S3 ON S.sqr = S3.ID
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
LEFT JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id");
List<string> wheres = new List<string> { "YEAR(S.sqrq) = YEAR(CURDATE())", "S.lcbh like 'YFCGSQ%'" };
if (!string.IsNullOrEmpty(lcbhNo))
{
wheres.Add($"S.lcbh LIKE '%{lcbhNo}%'");
}
if (!string.IsNullOrEmpty(sqrxm))
{
wheres.Add($"S3.lastname LIKE '%{sqrxm}%'");
}
if (!string.IsNullOrEmpty(reqName))
{
wheres.Add($"S2.REQUESTNAME LIKE '%{reqName}%'");
}
if (!string.IsNullOrEmpty(yfxmmc))
{
wheres.Add($"S4.yfxm LIKE '%{yfxmmc}%'");
}
if (!string.IsNullOrEmpty(matCode))
{
wheres.Add($"S5.wlbm LIKE '%{matCode}%'");
}
if (!string.IsNullOrEmpty(matName))
{
wheres.Add($"S1.wlmc LIKE '%{matName}%'");
}
if (!string.IsNullOrEmpty(ywzt))
{
// 支持逗号分隔多选
var ywArr = ywzt.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (ywArr.Length == 1)
wheres.Add($"S1.ywzt = '{ywzt}'");
else
wheres.Add($"S1.ywzt IN ({string.Join(",", ywArr.Select(x => "'" + x + "'"))})");
}
if (!string.IsNullOrEmpty(cncgfzr))
{
wheres.Add($"S1.cncgfzr LIKE '%{cncgfzr}%'");
}
if (!string.IsNullOrEmpty(yfcgfzr))
{
wheres.Add($"S1.yfcgfzr LIKE '%{yfcgfzr}%'");
}
sql.Append(" WHERE " + string.Join(" AND ", wheres));
sql.Append(" ORDER BY S.sqrq DESC, S.lcbh DESC, CAST(S1.xh AS UNSIGNED) ASC");
sql.Append($" LIMIT {(page - 1) * pageSize},{pageSize}");
DataTable dt = mySqlUtils.ExecQuery(sql.ToString());
// 总数查询
StringBuilder countSql = new StringBuilder(@"
SELECT COUNT(0) AS total FROM formtable_main_23 S
LEFT JOIN workflow_requestbase S2 ON S.requestId = S2.REQUESTID
LEFT JOIN Hrmresource S3 ON S.sqr = S3.ID
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
LEFT JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id");
countSql.Append(" WHERE " + string.Join(" AND ", wheres));
DataTable dtCount = mySqlUtils.ExecQuery(countSql.ToString());
int total = dtCount.Rows.Count > 0 ? Convert.ToInt32(dtCount.Rows[0]["total"]) : 0;
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["id"] = row["id"].ToStringEx(),
["requestId"] = row["requestId"].ToStringEx(),
["lcbh"] = row["lcbh"].ToStringEx(),
["djbh"] = row["djbh"].ToStringEx(),
["sqrq"] = row["sqrq"].ToStringEx(),
["reqName"] = row["reqName"].ToStringEx(),
["sqrxm"] = row["sqrxm"].ToStringEx(),
["yfxmmc"] = row["yfxmmc"].ToStringEx(),
["xh"] = row["xh"].ToStringEx(),
["wlbmCode"] = row["wlbmCode"].ToStringEx(),
["wlmc"] = row["wlmc"].ToStringEx(),
["sqsl"] = row["sqsl"].ToStringEx(),
["ywzt"] = row["ywzt"].ToStringEx(),
["cncgfzr"] = row["cncgfzr"].ToStringEx(),
["yfcgfzr"] = row["yfcgfzr"].ToStringEx(),
["cnjhjfrq"] = FormatPlanDate(row["cnjhjfrq"].ToStringEx()),
["yfjhjfrq"] = FormatPlanDate(row["yfjhjfrq"].ToStringEx())
});
}
return Ok(new { success = true, data = list, total, page, pageSize });
}
catch (Exception ex)
{
Logger.Error("获取看板列表异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
finally { mySqlUtils.Dispose(); }
}
#endregion
#region 获取OA采购申请单明细物料(含维护字段)
[HttpGet]
public IHttpActionResult GetRequestDetail(string requestId)
{
MySqlUtils mySqlUtils = new MySqlUtils();
try
{
if (string.IsNullOrEmpty(requestId))
return Ok(new { success = false, message = "参数 requestId 不能为空" });
string OAConnection = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = OAConnection;
mySqlUtils.GetConnection();
string sql = $@"SELECT
S1.id, S1.mainid, S1.xh, S1.wlbmyf,
S5.wlbm AS wlbmCode, S1.wlmc, S1.wlgg, S1.sqsl, S1.dhrq,
S1.wlbz, S1.onebj, S1.onezje, S1.twobj, S1.twozje,
S1.gys, S1.cncgfzr, S1.yfcgfzr, S1.ywzt, S1.jffxdj, S1.jfjz,
DATE_FORMAT(S1.cnjhjfrq, '%Y-%m-%d') AS cnjhjfrq,
DATE_FORMAT(S1.yfjhjfrq, '%Y-%m-%d') AS yfjhjfrq
FROM formtable_main_23_dt1 S1
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
WHERE S1.mainid = (SELECT id FROM formtable_main_23 WHERE requestId = '{requestId}' LIMIT 1)
ORDER BY CAST(S1.xh AS UNSIGNED) ASC";
DataTable dt = mySqlUtils.ExecQuery(sql);
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["id"] = row["id"].ToStringEx(),
["mainid"] = row["mainid"].ToStringEx(),
["xh"] = row["xh"].ToStringEx(),
["wlbmyf"] = row["wlbmyf"].ToStringEx(),
["wlbmCode"] = row["wlbmCode"].ToStringEx(),
["wlmc"] = row["wlmc"].ToStringEx(),
["wlgg"] = row["wlgg"].ToStringEx(),
["sqsl"] = row["sqsl"].ToStringEx(),
["dhrq"] = FormatDate(row["dhrq"]?.ToStringEx() ?? ""),
["wlbz"] = row["wlbz"].ToStringEx(),
["onebj"] = row["onebj"].ToStringEx(),
["onezje"] = row["onezje"].ToStringEx(),
["twobj"] = row["twobj"].ToStringEx(),
["twozje"] = row["twozje"].ToStringEx(),
["gys"] = row["gys"].ToStringEx(),
["cncgfzr"] = row["cncgfzr"].ToStringEx(),
["yfcgfzr"] = row["yfcgfzr"].ToStringEx(),
["ywzt"] = row["ywzt"].ToStringEx(),
["jffxdj"] = row["jffxdj"].ToStringEx(),
["jfjz"] = row["jfjz"].ToStringEx(),
["cnjhjfrq"] = FormatPlanDate(row["cnjhjfrq"].ToStringEx()),
["yfjhjfrq"] = FormatPlanDate(row["yfjhjfrq"].ToStringEx())
});
}
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("获取采购申请单明细异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
finally { mySqlUtils.Dispose(); }
}
#endregion
#region 批量获取多个申请单的物料明细
[HttpGet]
public IHttpActionResult GetMultiRequestDetail(string requestIds)
{
MySqlUtils mySqlUtils = new MySqlUtils();
try
{
if (string.IsNullOrEmpty(requestIds))
return Ok(new { success = false, message = "参数 requestIds 不能为空" });
// requestIds格式: "id1,id2,id3"
string[] ids = requestIds.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (ids.Length == 0)
return Ok(new { success = false, message = "请选择至少一个申请单" });
string idList = string.Join(",", ids);
string OAConnection = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = OAConnection;
mySqlUtils.GetConnection();
string sql = $@"SELECT
S.requestId, S.lcbh, S.djbh,
DATE_FORMAT(S.sqrq, '%Y-%m-%d') AS sqrq,
S2.REQUESTNAME AS reqName,
S1.id, S1.xh, S5.wlbm AS wlbmCode, S1.wlmc, S1.wlgg, S1.sqsl, S1.dhrq,
S1.wlbz, S1.onebj, S1.onezje, S1.twobj, S1.twozje,
S1.gys, S1.cncgfzr, S1.yfcgfzr, S1.ywzt, S1.jffxdj, S1.jfjz,
DATE_FORMAT(S1.cnjhjfrq, '%Y-%m-%d') AS cnjhjfrq,
DATE_FORMAT(S1.yfjhjfrq, '%Y-%m-%d') AS yfjhjfrq
FROM formtable_main_23_dt1 S1
LEFT JOIN formtable_main_23 S ON S1.mainid = S.id
LEFT JOIN workflow_requestbase S2 ON S.requestId = S2.REQUESTID
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
WHERE S.id IN ({idList})
ORDER BY S.lcbh, CAST(S1.xh AS UNSIGNED)";
DataTable dt = mySqlUtils.ExecQuery(sql);
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["requestId"] = row["requestId"].ToStringEx(),
["lcbh"] = row["lcbh"].ToStringEx(),
["djbh"] = row["djbh"].ToStringEx(),
["sqrq"] = row["sqrq"].ToStringEx(),
["reqName"] = row["reqName"].ToStringEx(),
["id"] = row["id"].ToStringEx(),
["xh"] = row["xh"].ToStringEx(),
["wlbmCode"] = row["wlbmCode"].ToStringEx(),
["wlmc"] = row["wlmc"].ToStringEx(),
["wlgg"] = row["wlgg"].ToStringEx(),
["sqsl"] = row["sqsl"].ToStringEx(),
["dhrq"] = row["dhrq"]?.ToStringEx()?.Length >= 10 ? row["dhrq"].ToStringEx().Substring(0, 10) : row["dhrq"].ToStringEx(),
["wlbz"] = row["wlbz"].ToStringEx(),
["onebj"] = row["onebj"].ToStringEx(),
["onezje"] = row["onezje"].ToStringEx(),
["twobj"] = row["twobj"].ToStringEx(),
["twozje"] = row["twozje"].ToStringEx(),
["gys"] = row["gys"].ToStringEx(),
["cncgfzr"] = row["cncgfzr"].ToStringEx(),
["yfcgfzr"] = row["yfcgfzr"].ToStringEx(),
["ywzt"] = row["ywzt"].ToStringEx(),
["jffxdj"] = row["jffxdj"].ToStringEx(),
["jfjz"] = row["jfjz"].ToStringEx(),
["cnjhjfrq"] = FormatPlanDate(row["cnjhjfrq"].ToStringEx()),
["yfjhjfrq"] = FormatPlanDate(row["yfjhjfrq"].ToStringEx())
});
}
// 计算汇总
decimal sumOne = 0, sumTwo = 0;
foreach (var item in list)
{
decimal one = 0, two = 0;
decimal.TryParse(item["onezje"]?.ToString() ?? "0", out one);
decimal.TryParse(item["twozje"]?.ToString() ?? "0", out two);
sumOne += one;
sumTwo += two;
}
return Ok(new { success = true, data = list, totalOne = sumOne, totalTwo = sumTwo });
}
catch (Exception ex)
{
Logger.Error("批量获取明细异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
finally { mySqlUtils.Dispose(); }
}
#endregion
#region 保存明细维护字段
[HttpPost]
public IHttpActionResult SaveDetail([FromBody] JObject obj)
{
MySqlUtils mySqlUtils = new MySqlUtils();
try
{
JArray items = obj["items"] as JArray;
if (items == null || items.Count == 0)
return Ok(new { success = false, message = "明细数据不能为空" });
string OAConnection = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = OAConnection;
mySqlUtils.GetConnection();
foreach (var item in items)
{
string id = item["id"]?.ToString();
if (string.IsNullOrEmpty(id)) continue;
string wlbz = item["wlbz"]?.ToString() ?? "";
string onebj = item["onebj"]?.ToString() ?? "0";
string onezje = item["onezje"]?.ToString() ?? "0";
string twobj = item["twobj"]?.ToString() ?? "0";
string twozje = item["twozje"]?.ToString() ?? "0";
string gys = item["gys"]?.ToString() ?? "";
string cncgfzr = item["cncgfzr"]?.ToString() ?? "";
string yfcgfzr = item["yfcgfzr"]?.ToString() ?? "";
string ywzt = item["ywzt"]?.ToString() ?? "";
string jffxdj = item["jffxdj"]?.ToString() ?? "";
string jfjz = item["jfjz"]?.ToString() ?? "";
string cnjhjfrq = item["cnjhjfrq"]?.ToString() ?? "";
string yfjhjfrq = item["yfjhjfrq"]?.ToString() ?? "";
string sql = $@"UPDATE formtable_main_23_dt1 SET
wlbz = '{wlbz.Replace("'", "''")}',
onebj = {onebj}, onezje = {onezje},
twobj = {twobj}, twozje = {twozje},
gys = '{gys.Replace("'", "''")}',
cncgfzr = '{cncgfzr.Replace("'", "''")}',
yfcgfzr = '{yfcgfzr.Replace("'", "''")}',
ywzt = '{ywzt.Replace("'", "''")}',
jffxdj = '{jffxdj.Replace("'", "''")}',
jfjz = '{jfjz.Replace("'", "''")}'";
if (!string.IsNullOrEmpty(cnjhjfrq))
sql += $", cnjhjfrq = '{cnjhjfrq}'";
if (!string.IsNullOrEmpty(yfjhjfrq))
sql += $", yfjhjfrq = '{yfjhjfrq}'";
sql += $" WHERE id = {id}";
mySqlUtils.ExecNonQuery(sql);
}
return Ok(new { success = true, message = "保存成功" });
}
catch (Exception ex)
{
Logger.Error("保存明细异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
finally { mySqlUtils.Dispose(); }
}
#endregion
#region 查询采购订单(通过主表PUR_PurchaseOrder + 分录FPOOrderEntry过滤)
/// <summary>
/// 根据申请单号(djbh)查询关联的采购订单
/// 通过分录字段 FPOOrderEntry.FSrcBillNo 过滤
/// </summary>
[HttpGet]
public IHttpActionResult GetPurchaseOrders(string reqBillNo, string pmAuditTime = "")
{
try
{
if (string.IsNullOrEmpty(reqBillNo))
return Ok(new { success = false, message = "参数 reqBillNo 不能为空" });
K3CloudApiClient client = GetKingdeeClient();
DateTime pmDate;
DateTime.TryParse(pmAuditTime, out pmDate);
string fieldKeys = "FID,FBILLNO,FDATE,FDocumentStatus"
+ ",FPOOrderEntry_FEntryID,FSrcBillNo";
string filter = $"FSrcBillNo = '{reqBillNo}' AND FDocumentStatus = 'C'";
var result = ExecuteKingdeeQuery(client, "PUR_PurchaseOrder", fieldKeys, filter, "FBILLNO", 0, 0, 500);
HashSet<string> poBillNos = new HashSet<string>();
if (result != null)
{
foreach (var row in result)
{
string bn = SafeGet(row, 1);
if (!string.IsNullOrEmpty(bn)) poBillNos.Add(bn);
}
}
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (var poBn in poBillNos)
{
string hf = $"FBILLNO = '{poBn}' AND FDocumentStatus = 'C'";
var hr = ExecuteKingdeeQuery(client, "PUR_PurchaseOrder",
"FID,FBILLNO,FDATE,FDocumentStatus",
hf, "", 0, 0, 1);
if (hr != null && hr.Count > 0)
{
var r = hr[0];
string poDateStr = FormatDate(SafeGet(r, 2));
int transferDays = 0;
DateTime poDate;
if (DateTime.TryParse(poDateStr, out poDate) && pmDate != DateTime.MinValue)
{
transferDays = (int)(poDate.Date - pmDate.Date).TotalDays;
}
list.Add(new Dictionary<string, object>
{
["FID"] = SafeGet(r, 0),
["FBILLNO"] = SafeGet(r, 1),
["FDATE"] = poDateStr,
["FDocumentStatus"] = SafeGet(r, 3),
["transferDays"] = transferDays
});
}
}
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("查询采购订单异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 查询采购订单明细
[HttpGet]
public IHttpActionResult GetPurchaseOrderEntry(string poBillNo)
{
try
{
if (string.IsNullOrEmpty(poBillNo))
return Ok(new { success = false, message = "参数 poBillNo 不能为空" });
K3CloudApiClient client = GetKingdeeClient();
// 通过主表查分录:0=FBILLNO, 1=FPOOrderEntry_FEntryID, 2=FMaterialId.FNumber,
// 3=FMaterialId.FName, 4=FMaterialId.FSPECIFICATION,
// 5=FQty, 6=FDate, 7=FSrcBillNo
string fieldKeys = "FBILLNO"
+ ",FPOOrderEntry_FEntryID"
+ ",FMaterialId.FNumber"
+ ",FMaterialId.FName"
+ ",FMaterialId.FSPECIFICATION"
+ ",FQty"
+ ",FDate"
+ ",FSrcBillNo";
var result = ExecuteKingdeeQuery(client, "PUR_PurchaseOrder",
fieldKeys, $"FBILLNO = '{poBillNo}'", "FPOOrderEntry_FEntryID", 0, 0, 500);
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
if (result != null)
{
foreach (var row in result)
{
list.Add(new Dictionary<string, object>
{
["FPOBILLNO"] = SafeGet(row, 0),
["FENTRYID"] = SafeGet(row, 1),
["MATERIAL_NUMBER"] = SafeGet(row, 2),
["MATERIAL_NAME"] = SafeGet(row, 3),
["SPECIFICATION"] = SafeGet(row, 4),
["Qty"] = SafeGet(row, 5),
["FDate"] = FormatDate(SafeGet(row, 6)),
["FSrcBillNo"] = SafeGet(row, 7)
});
}
}
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("查询采购订单明细异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 查询收料通知单(通过主表PUR_ReceiveBill + 分录FDetailEntity过滤)
[HttpGet]
public IHttpActionResult GetReceiveNotices(string poBillNo)
{
try
{
if (string.IsNullOrEmpty(poBillNo))
return Ok(new { success = false, message = "参数 poBillNo 不能为空" });
K3CloudApiClient client = GetKingdeeClient();
// 通过收料通知单分录 FSrcBillNo 过滤
string fieldKeys = "FID,FBILLNO,FDATE,FDocumentStatus"
+ ",FDetailEntity_FEntryID,FSrcBillNo";
string filter = $"FSrcBillNo = '{poBillNo}' AND FDocumentStatus = 'C'";
var entryResult = ExecuteKingdeeQuery(client, "PUR_ReceiveBill", fieldKeys, filter, "", 0, 0, 500);
HashSet<string> rnBillNos = new HashSet<string>();
if (entryResult != null)
{
foreach (var row in entryResult)
{
string bn = SafeGet(row, 1);
if (!string.IsNullOrEmpty(bn)) rnBillNos.Add(bn);
}
}
// 查询每个RN的抬头信息
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (var rnBn in rnBillNos)
{
string hf = $"FBILLNO = '{rnBn}' AND FDocumentStatus = 'C'";
var hr = ExecuteKingdeeQuery(client, "PUR_ReceiveBill",
"FID,FBILLNO,FDATE,FDocumentStatus",
hf, "", 0, 0, 1);
if (hr != null && hr.Count > 0)
{
var r = hr[0];
list.Add(new Dictionary<string, object>
{
["FID"] = SafeGet(r, 0),
["FBILLNO"] = SafeGet(r, 1),
["FDATE"] = FormatDate(SafeGet(r, 2)),
["FDocumentStatus"] = SafeGet(r, 3)
});
}
}
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("查询收料通知单异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 查询收料通知单明细
[HttpGet]
public IHttpActionResult GetReceiveNoticeEntry(string rnBillNo)
{
try
{
if (string.IsNullOrEmpty(rnBillNo))
return Ok(new { success = false, message = "参数 rnBillNo 不能为空" });
K3CloudApiClient client = GetKingdeeClient();
// 通过主表查分录:0=FBILLNO, 1=FDetailEntity_FEntryID, 2=FMaterialId.FNumber,
// 3=FMaterialId.FName, 4=FMaterialId.FSPECIFICATION,
// 5=FActReceiveQty, 6=FDate, 7=FSrcBillNo, 8=FSrcEntryId
string fieldKeys = "FBILLNO"
+ ",FDetailEntity_FEntryID"
+ ",FMaterialId.FNumber"
+ ",FMaterialId.FName"
+ ",FMaterialId.FSPECIFICATION"
+ ",FActReceiveQty"
+ ",FDate"
+ ",FSrcBillNo"
+ ",FSrcEntryId";
var result = ExecuteKingdeeQuery(client, "PUR_ReceiveBill",
fieldKeys, $"FBILLNO = '{rnBillNo}'", "FDetailEntity_FEntryID", 0, 0, 500);
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
if (result != null)
{
foreach (var row in result)
{
list.Add(new Dictionary<string, object>
{
["FRECEIVEBILLNO"] = SafeGet(row, 0),
["FENTRYID"] = SafeGet(row, 1),
["MATERIAL_NUMBER"] = SafeGet(row, 2),
["MATERIAL_NAME"] = SafeGet(row, 3),
["SPECIFICATION"] = SafeGet(row, 4),
["Qty"] = SafeGet(row, 5),
["FDate"] = FormatDate(SafeGet(row, 6)),
["FSrcBillNo"] = SafeGet(row, 7),
["FSrcEntryId"] = SafeGet(row, 8)
});
}
}
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("查询收料通知单明细异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 分录级追溯汇总
[HttpGet]
public IHttpActionResult GetTraceSummary(string reqBillNo)
{
try
{
if (string.IsNullOrEmpty(reqBillNo))
return Ok(new { success = false, message = "参数 reqBillNo 不能为空" });
#region Step1: 获取OA申请单物料明细
MySqlUtils mySqlUtils = new MySqlUtils();
DataTable dtOAItems;
try
{
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string sql = $@"SELECT S1.xh,S5.wlbm AS wlbmCode,S1.wlmc,S1.wlgg,S1.sqsl,S1.dhrq,S1.cnjhjfrq
FROM formtable_main_23_dt1 S1
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
WHERE S1.mainid = (SELECT id FROM formtable_main_23 WHERE djbh = '{reqBillNo}' LIMIT 1)
ORDER BY S1.xh";
dtOAItems = mySqlUtils.ExecQuery(sql);
}
finally { mySqlUtils.Dispose(); }
#endregion
K3CloudApiClient client = GetKingdeeClient();
#region Step2: 查询金蝶采购申请单分录
var reqHeader = ExecuteKingdeeQuery(client, "PUR_Requisition",
"FID,FBILLNO", $"FBILLNO = '{reqBillNo}' AND FDocumentStatus = 'C'", "", 0, 0, 1);
Dictionary<string, string> reqEntryMatMap = new Dictionary<string, string>();
Dictionary<string, int> reqEntrySeqMap = new Dictionary<string, int>();
List<string> reqEntryIds = new List<string>();
string reqFID = "";
if (reqHeader != null && reqHeader.Count > 0)
{
reqFID = SafeGet(reqHeader[0], 0);
// 查分录 PUR_RequisitionEntry 不能独立查,需通过主表查
// 0=FID, 1=FBILLNO, 2=FEntity_FEntryID, 3=FMaterialId.FNumber, 4=FEntity_FSeq
var reqEntries = ExecuteKingdeeQuery(client, "PUR_Requisition",
"FID,FBILLNO,FEntity_FEntryID,FMaterialId.FNumber,FEntity_FSeq",
$"FBILLNO = '{reqBillNo}' AND FDocumentStatus = 'C'", "FEntity_FSeq", 0, 0, 200);
if (reqEntries != null)
{
foreach (var row in reqEntries)
{
string entryId = SafeGet(row, 2);
string matNo = SafeGet(row, 3);
string seq = SafeGet(row, 4);
if (!string.IsNullOrEmpty(entryId))
{
reqEntryMatMap[entryId] = matNo;
int s = 0; int.TryParse(seq, out s);
reqEntrySeqMap[entryId] = s;
reqEntryIds.Add(entryId);
}
}
}
}
// OA物料 → 金蝶申请单分录 映射
Dictionary<int, string> oaRowToReqEntry = new Dictionary<int, string>();
Dictionary<string, Queue<string>> matToReqEntries = new Dictionary<string, Queue<string>>();
foreach (var eid in reqEntryIds)
{
string mat = reqEntryMatMap.ContainsKey(eid) ? reqEntryMatMap[eid] : "";
if (!matToReqEntries.ContainsKey(mat))
matToReqEntries[mat] = new Queue<string>();
matToReqEntries[mat].Enqueue(eid);
}
for (int i = 0; i < dtOAItems.Rows.Count; i++)
{
string oaMat = dtOAItems.Rows[i]["wlbmCode"].ToStringEx();
if (matToReqEntries.ContainsKey(oaMat) && matToReqEntries[oaMat].Count > 0)
oaRowToReqEntry[i] = matToReqEntries[oaMat].Dequeue();
}
#endregion
#region Step3: 查询PO分录 (通过主表PUR_PurchaseOrder + 分录FPOOrderEntry)
Dictionary<string, List<Dictionary<string, object>>> reqEntryToPOEntries =
new Dictionary<string, List<Dictionary<string, object>>>();
// 0=FBILLNO, 1=FPOOrderEntry_FEntryID, 2=FSrcBillNo,
// 3=FMaterialId.FNumber, 4=FQty, 5=FDate,
// 6=FPOOrderEntry_Link_FSId, 7=FPOOrderEntry_Link_FBaseUnitQty
string poFieldKeys = "FBILLNO"
+ ",FPOOrderEntry_FEntryID"
+ ",FSrcBillNo"
+ ",FMaterialId.FNumber"
+ ",FQty"
+ ",FDate"
+ ",FPOOrderEntry_Link_FSId"
+ ",FPOOrderEntry_Link_FBaseUnitQty";
var poEntries = ExecuteKingdeeQuery(client, "PUR_PurchaseOrder",
poFieldKeys, $"FSrcBillNo = '{reqBillNo}' AND FDocumentStatus = 'C'",
"FPOOrderEntry_FEntryID", 0, 0, 500);
HashSet<string> poBillNos = new HashSet<string>();
if (poEntries != null)
{
foreach (var row in poEntries)
{
string poBillNo = SafeGet(row, 0);
string poEntryId = SafeGet(row, 1);
string matNo = SafeGet(row, 3);
string qtyStr = SafeGet(row, 4);
string dateRaw = SafeGet(row, 5);
string linkFSId = SafeGet(row, 6);
string linkQty = SafeGet(row, 7);
if (!string.IsNullOrEmpty(poBillNo)) poBillNos.Add(poBillNo);
string actualSrcEntryId = linkFSId;
string actualQty = !string.IsNullOrEmpty(linkQty) ? linkQty : qtyStr;
var poEntry = new Dictionary<string, object>
{
["poBillNo"] = poBillNo,
["poEntryId"] = poEntryId,
["srcEntryId"] = actualSrcEntryId,
["matNumber"] = matNo,
["qty"] = actualQty,
["date"] = FormatDate(dateRaw)
};
if (!reqEntryToPOEntries.ContainsKey(actualSrcEntryId))
reqEntryToPOEntries[actualSrcEntryId] = new List<Dictionary<string, object>>();
reqEntryToPOEntries[actualSrcEntryId].Add(poEntry);
}
}
#endregion
#region Step4: 查询RN分录 (通过主表PUR_ReceiveBill + 分录FDetailEntity)
Dictionary<string, List<Dictionary<string, object>>> poEntryToRNEntries =
new Dictionary<string, List<Dictionary<string, object>>>();
HashSet<string> rnBillNos = new HashSet<string>();
foreach (var poBn in poBillNos)
{
if (string.IsNullOrEmpty(poBn)) continue;
// 0=FBILLNO,1=FDetailEntity_FEntryID,2=FSrcBillNo,3=FSrcEntryId,
// 4=FMaterialId.FNumber,5=FActReceiveQty,6=FDate,
// 7=FDetailEntity_Link_FSId,8=FDetailEntity_Link_FBaseUnitQty
string rnFieldKeys = "FBILLNO"
+ ",FDetailEntity_FEntryID"
+ ",FSrcBillNo"
+ ",FSrcEntryId"
+ ",FMaterialId.FNumber"
+ ",FActReceiveQty"
+ ",FDate"
+ ",FDetailEntity_Link_FSId"
+ ",FDetailEntity_Link_FBaseUnitQty";
var rnEntries = ExecuteKingdeeQuery(client, "PUR_ReceiveBill",
rnFieldKeys, $"FSrcBillNo = '{poBn}' AND FDocumentStatus = 'C'",
"FDetailEntity_FEntryID", 0, 0, 500);
if (rnEntries != null)
{
foreach (var row in rnEntries)
{
string rnBillNo = SafeGet(row, 0);
string rnEntryId = SafeGet(row, 1);
string srcBillNo = SafeGet(row, 2);
string srcEntryId = SafeGet(row, 3);
string matNo = SafeGet(row, 4);
string qtyStr = SafeGet(row, 5);
string dateRaw = SafeGet(row, 6);
string linkFSId = SafeGet(row, 7);
string linkQty = SafeGet(row, 8);
if (!string.IsNullOrEmpty(rnBillNo)) rnBillNos.Add(rnBillNo);
string actualSrcEntryId = !string.IsNullOrEmpty(linkFSId) ? linkFSId : srcEntryId;
string actualQty = !string.IsNullOrEmpty(linkQty) ? linkQty : qtyStr;
var rnEntry = new Dictionary<string, object>
{
["rnBillNo"] = rnBillNo,
["rnEntryId"] = rnEntryId,
["srcEntryId"] = actualSrcEntryId,
["matNumber"] = matNo,
["qty"] = actualQty,
["date"] = FormatDate(dateRaw)
};
if (!poEntryToRNEntries.ContainsKey(actualSrcEntryId))
poEntryToRNEntries[actualSrcEntryId] = new List<Dictionary<string, object>>();
poEntryToRNEntries[actualSrcEntryId].Add(rnEntry);
}
}
}
// 收料通知单抬头信息
Dictionary<string, Dictionary<string, string>> rnHeaderMap = new Dictionary<string, Dictionary<string, string>>();
foreach (var rnBn in rnBillNos)
{
var hr = ExecuteKingdeeQuery(client, "PUR_ReceiveBill",
"FBILLNO,FDATE",
$"FBILLNO = '{rnBn}' AND FDocumentStatus = 'C'", "", 0, 0, 1);
if (hr != null && hr.Count > 0)
{
string dateVal = FormatDate(SafeGet(hr[0], 1));
rnHeaderMap[rnBn] = new Dictionary<string, string>
{
["date"] = dateVal,
["supplierName"] = ""
};
}
}
#endregion
#region Step5: 逐行追溯汇总
List<Dictionary<string, object>> summaryList = new List<Dictionary<string, object>>();
List<Dictionary<string, object>> traceDetailList = new List<Dictionary<string, object>>();
for (int i = 0; i < dtOAItems.Rows.Count; i++)
{
DataRow oaRow = dtOAItems.Rows[i];
string oaMatCode = oaRow["wlbmCode"].ToStringEx();
decimal reqQty = 0;
decimal.TryParse(oaRow["sqsl"].ToStringEx(), out reqQty);
decimal receivedQty = 0;
string latestDate = "";
if (oaRowToReqEntry.ContainsKey(i))
{
string reqEntryId = oaRowToReqEntry[i];
if (reqEntryToPOEntries.ContainsKey(reqEntryId))
{
foreach (var poEntry in reqEntryToPOEntries[reqEntryId])
{
string poEntryId = poEntry["poEntryId"].ToString();
if (poEntryToRNEntries.ContainsKey(poEntryId))
{
foreach (var rnEntry in poEntryToRNEntries[poEntryId])
{
decimal rnQty = 0;
decimal.TryParse(rnEntry["qty"].ToString(), out rnQty);
receivedQty += rnQty;
string rnDate = rnEntry["date"].ToString();
if (!string.IsNullOrEmpty(rnDate) && string.Compare(rnDate, latestDate) > 0)
latestDate = rnDate;
traceDetailList.Add(new Dictionary<string, object>
{
["oaXh"] = oaRow["xh"].ToStringEx(),
["oaMatCode"] = oaMatCode,
["reqEntryId"] = reqEntryId,
["poBillNo"] = poEntry["poBillNo"],
["poEntryId"] = poEntryId,
["poQty"] = poEntry["qty"],
["rnBillNo"] = rnEntry["rnBillNo"],
["rnQty"] = rnQty,
["rnDate"] = rnDate
});
}
}
}
}
}
decimal remainQty = reqQty - receivedQty;
string status = receivedQty >= reqQty ? "已完成" : (receivedQty > 0 ? "部分收货" : "未收货");
// 计算延期天数
int projectDelay = 0, stdDelay = 0;
if (!string.IsNullOrEmpty(latestDate))
{
DateTime latestDt;
if (DateTime.TryParse(latestDate, out latestDt))
{
// 项目目标交付延期 = 策略计划交付日期(cnjhjfrq) - 最近到货日期
string cnjhjfrqStr = oaRow["cnjhjfrq"].ToStringEx();
DateTime cnjhjfrqDt;
if (DateTime.TryParse(cnjhjfrqStr, out cnjhjfrqDt))
{
projectDelay = (int)(cnjhjfrqDt.Date - latestDt.Date).TotalDays;
}
// 标准交付延期 = 交付日期(dhrq) - 最近到货日期
string dhrqStr = oaRow["dhrq"].ToStringEx();
DateTime dhrqDt;
if (DateTime.TryParse(dhrqStr, out dhrqDt))
{
stdDelay = (int)(dhrqDt.Date - latestDt.Date).TotalDays;
}
}
}
summaryList.Add(new Dictionary<string, object>
{
["xh"] = oaRow["xh"].ToStringEx(),
["wlbmCode"] = oaMatCode,
["wlmc"] = oaRow["wlmc"].ToStringEx(),
["wlgg"] = oaRow["wlgg"].ToStringEx(),
["sqsl"] = reqQty,
["yssl"] = receivedQty,
["syst"] = remainQty,
["latestReceiveDate"] = latestDate,
["status"] = status,
["projectDelay"] = projectDelay,
["stdDelay"] = stdDelay
});
}
List<Dictionary<string, object>> rnList = new List<Dictionary<string, object>>();
foreach (var rnBn in rnBillNos)
{
var hdr = rnHeaderMap.ContainsKey(rnBn) ? rnHeaderMap[rnBn] : null;
rnList.Add(new Dictionary<string, object>
{
["rnBillNo"] = rnBn,
["rnDate"] = hdr != null ? hdr["date"] : "",
["supplierName"] = hdr != null ? hdr["supplierName"] : ""
});
}
#endregion
return Ok(new
{
success = true,
reqBillNo,
purchaseOrders = poBillNos.ToList(),
receiveNotices = rnList,
traceDetails = traceDetailList,
summary = summaryList
});
}
catch (Exception ex)
{
Logger.Error("获取追溯汇总异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 导出xlsx(维护数据模板,只导出选中行)
[HttpPost]
public IHttpActionResult ExportXlsx()
{
try
{
var body = Request.Content.ReadAsStringAsync().Result;
JObject obj = JObject.Parse(body);
JArray idsArr = obj["ids"] as JArray;
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
DataTable dt = new DataTable();
if (idsArr != null && idsArr.Count > 0)
{
string idList = string.Join(",", idsArr.Select(id => id.ToString()));
string sql = $@"SELECT S.lcbh,
DATE_FORMAT(S.sqrq, '%Y-%m-%d') AS sqrq,
S4.yfxm AS yfxmmc,
S2.REQUESTNAME AS reqName,
S3.lastname AS sqrxm,
S1.xh,S5.wlbm AS wlbmCode,S1.wlmc,S1.wlgg,S1.sqsl,S1.dhrq,
S1.wlbz,S1.onebj,S1.onezje,S1.twobj,S1.twozje,
S1.gys,S1.cncgfzr,S1.yfcgfzr,S1.ywzt,S1.jffxdj,S1.jfjz,
DATE_FORMAT(S1.cnjhjfrq,'%Y-%m-%d') AS cnjhjfrq,
DATE_FORMAT(S1.yfjhjfrq,'%Y-%m-%d') AS yfjhjfrq
FROM formtable_main_23_dt1 S1
LEFT JOIN formtable_main_23 S ON S1.mainid = S.id
LEFT JOIN workflow_requestbase S2 ON S.requestId = S2.REQUESTID
LEFT JOIN Hrmresource S3 ON S.sqr = S3.ID
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
WHERE S.id IN ({idList})
ORDER BY S.lcbh,S1.xh";
dt = mySqlUtils.ExecQuery(sql);
}
mySqlUtils.Dispose();
// 生成XML Spreadsheet 2003格式(xls),Excel可直接打开
var sb = new StringBuilder();
sb.AppendLine("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
sb.AppendLine("<?mso-application progid=\"Excel.Sheet\"?>");
sb.AppendLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sb.AppendLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
sb.AppendLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
sb.AppendLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
sb.AppendLine(" <Worksheet ss:Name=\"Sheet1\">");
sb.AppendLine(" <Table>");
// 表头
string[] headers = { "流程编号","申请日期","研发项目","专项名称","申请人","明细序号","物料编号","物料名称","规格型号","申请数量","要求交付日期","物料标准L/T","第一轮报价","第一轮总金额","第二轮报价","第二轮总金额","供应商","策略采购负责人","研发采购负责人","业务状态","交付风险","交付节奏","计划交付日期(策略采购)","计划交付日期(研发采购)" };
sb.AppendLine(" <Row>");
foreach (var h in headers)
sb.AppendLine($" <Cell><Data ss:Type=\"String\">{EscapeXml(h)}</Data></Cell>");
sb.AppendLine(" </Row>");
Dictionary<string, string> ywztMap = new Dictionary<string, string> { {"1","采购订单-转单追踪"},{"2","供应链-待供应商报价"},{"3","供应链-待ED审批"},{"4","供应链-ED驳回,重新议价"},{"5","供应链-ED已审批"},{"6","研发-采购申请驳回(无图纸/方案没确认)"},{"7","研发-采购申请撤回(改需求)"},{"8","研发-采购申请取消"},{"9","研发-量产复用件"} };
Dictionary<string, string> jffxdjMap = new Dictionary<string, string> { {"1","超高"},{"2","高"},{"3","中"},{"4","低"},{"5","已完成"},{"6","取消"} };
Dictionary<string, string> jfjzMap = new Dictionary<string, string> { {"1","整批交付"},{"2","分批交付"},{"3","取消"} };
foreach (DataRow row in dt.Rows)
{
string ywztVal = ywztMap.ContainsKey(row["ywzt"].ToStringEx()) ? ywztMap[row["ywzt"].ToStringEx()] : "";
string jffxdjVal = jffxdjMap.ContainsKey(row["jffxdj"].ToStringEx()) ? jffxdjMap[row["jffxdj"].ToStringEx()] : "";
string jfjzVal = jfjzMap.ContainsKey(row["jfjz"].ToStringEx()) ? jfjzMap[row["jfjz"].ToStringEx()] : "";
string[] vals = {
row["lcbh"].ToStringEx(),
FormatDate(row["sqrq"].ToStringEx()), row["yfxmmc"].ToStringEx(), row["reqName"].ToStringEx(), row["sqrxm"].ToStringEx(),
row["xh"].ToStringEx(), row["wlbmCode"].ToStringEx(), row["wlmc"].ToStringEx(), row["wlgg"].ToStringEx(), row["sqsl"].ToStringEx(),
row["dhrq"]?.ToStringEx()?.Length >= 10 ? row["dhrq"].ToStringEx().Substring(0, 10) : row["dhrq"].ToStringEx(),
row["wlbz"].ToStringEx(), row["onebj"].ToStringEx(), row["onezje"].ToStringEx(), row["twobj"].ToStringEx(), row["twozje"].ToStringEx(),
row["gys"].ToStringEx(), row["cncgfzr"].ToStringEx(), row["yfcgfzr"].ToStringEx(),
ywztVal, jffxdjVal, jfjzVal,
FormatDate(row["cnjhjfrq"].ToStringEx()), FormatDate(row["yfjhjfrq"].ToStringEx())
};
sb.AppendLine(" <Row>");
foreach (var v in vals)
sb.AppendLine($" <Cell><Data ss:Type=\"String\">{EscapeXml(v)}</Data></Cell>");
sb.AppendLine(" </Row>");
}
sb.AppendLine(" </Table>");
sb.AppendLine(" </Worksheet>");
sb.AppendLine("</Workbook>");
return Ok(new { success = true, data = sb.ToString(), total = dt.Rows.Count });
}
catch (Exception ex)
{
Logger.Error("导出异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
private string EscapeXml(string val)
{
if (string.IsNullOrEmpty(val)) return "";
return val.Replace("&", "&amp;").Replace("<", "&lt;").Replace(">", "&gt;").Replace("\"", "&quot;").Replace("'", "&apos;");
}
#endregion
#region 导入(支持.xls XML格式 和 .xlsx ZIP格式)
[HttpPost]
public IHttpActionResult ImportXlsx()
{
try
{
var body = Request.Content.ReadAsStringAsync().Result;
JObject obj = JObject.Parse(body);
string fileB64 = obj["file"]?.ToString() ?? "";
if (string.IsNullOrEmpty(fileB64))
return Ok(new { success = false, message = "文件内容不能为空" });
byte[] fileBytes = Convert.FromBase64String(fileB64);
List<string[]> rows = new List<string[]>();
// 直接通过 Stream 加载 XmlDocument,避免编码转换问题
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
try
{
using (var ms = new System.IO.MemoryStream(fileBytes))
{
doc.Load(ms);
}
}
catch (System.Xml.XmlException ex)
{
// 失败时返回前30字节的十六进制用于诊断
string hexDump = BitConverter.ToString(fileBytes.Take(60).ToArray());
return Ok(new { success = false, message = "XML格式错误:" + ex.Message + ",前30字节:" + hexDump });
}
// 读取行数据
var ns = new System.Xml.XmlNamespaceManager(doc.NameTable);
ns.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
var rowNodes = doc.SelectNodes("//ss:Row", ns);
if (rowNodes != null)
{
foreach (System.Xml.XmlNode rowNode in rowNodes)
{
var cellNodes = rowNode.SelectNodes("ss:Cell", ns);
List<string> rowVals = new List<string>();
if (cellNodes != null)
foreach (System.Xml.XmlNode cell in cellNodes)
{
var dataNode = cell.SelectSingleNode("ss:Data", ns);
rowVals.Add(dataNode?.InnerText ?? "");
}
rows.Add(rowVals.ToArray());
}
}
if (rows.Count < 2)
return Ok(new { success = false, message = "数据行不足" });
// 映射字典
Dictionary<string, string> ywztMap = new Dictionary<string, string> { {"采购订单-转单追踪","1"},{"供应链-待供应商报价","2"},{"供应链-待ED审批","3"},{"供应链-ED驳回,重新议价","4"},{"供应链-ED已审批","5"},{"研发-采购申请驳回(无图纸/方案没确认)","6"},{"研发-采购申请撤回(改需求)","7"},{"研发-采购申请取消","8"},{"研发-量产复用件","9"} };
Dictionary<string, string> jffxdjMap = new Dictionary<string, string> { {"超高","1"},{"高","2"},{"中","3"},{"低","4"},{"已完成","5"},{"取消","6"} };
Dictionary<string, string> jfjzMap = new Dictionary<string, string> { {"整批交付","1"},{"分批交付","2"},{"取消","3"} };
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
int successCount = 0, failCount = 0;
List<string> failDetails = new List<string>();
for (int i = 1; i < rows.Count; i++)
{
string rowLabel = $"第{i + 1}行";
try
{
string[] cells = rows[i];
// 安全获取单元格值(Excel XML可能省略末尾空单元格)
Func<int, string> cv = (idx) => idx < cells.Length ? (cells[idx] ?? "").Trim() : "";
string lcbh = cv(0);
string xh = cv(5);
string wlbm = cv(6);
if (string.IsNullOrEmpty(lcbh) || string.IsNullOrEmpty(xh) || string.IsNullOrEmpty(wlbm))
{ failDetails.Add($"{rowLabel}:流程编号/行号/物料编码为空"); failCount++; continue; }
// 实际Excel列索引: 0=流程编号,1=申请日期,2=研发项目,3=专项名称,4=申请人,5=明细序号,6=物料编号,7=物料名称,8=规格型号,9=申请数量,10=要求交付日期,11=物料标准L/T,12=第一轮报价,13=第一轮总金额(忽略,计算),14=第二轮报价,15=第二轮总金额(忽略,计算),16=供应商,17=策略采购负责人,18=研发采购负责人,19=业务状态,20=交付风险,21=交付节奏,22=计划交付日期(策略采购),23=计划交付日期(研发采购)
string sqsl = cv(9);
string wlbz = cv(11);
string onebj = cv(12);
string twobj = cv(14);
string gys = cv(16);
string cncgfzr = cv(17);
string yfcgfzr = cv(18);
string ywztText = cv(19);
string jffxdjText = cv(20);
string jfjzText = cv(21);
string cnjhjfrq = cv(22);
string yfjhjfrq = cv(23);
string ywzt = ywztMap.ContainsKey(ywztText) ? ywztMap[ywztText] : "0";
string jffxdj = jffxdjMap.ContainsKey(jffxdjText) ? jffxdjMap[jffxdjText] : "0";
string jfjz = jfjzMap.ContainsKey(jfjzText) ? jfjzMap[jfjzText] : "0";
// 金额字段:onezje/twozje 不读Excel,由报价*申请数量计算得出
decimal sqslDec, onebjDec, twobjDec;
decimal.TryParse(sqsl, out sqslDec);
decimal.TryParse(onebj, out onebjDec);
decimal.TryParse(twobj, out twobjDec);
decimal onezje = onebjDec * sqslDec;
decimal twozje = twobjDec * sqslDec;
// 字段值
string onebjVal = string.IsNullOrEmpty(onebj) ? "0" : onebj;
string onezjeVal = onezje.ToString("0.##");
string twobjVal = string.IsNullOrEmpty(twobj) ? "0" : twobj;
string twozjeVal = twozje.ToString("0.##");
string ywztVal = string.IsNullOrEmpty(ywzt) ? "0" : ywzt;
string jffxdjVal = string.IsNullOrEmpty(jffxdj) ? "0" : jffxdj;
string jfjzVal = string.IsNullOrEmpty(jfjz) ? "0" : jfjz;
// 日期字段:支持 yyyy-MM-dd 文本 或 Excel序列号(如46260→2026-07-24)
Func<string, string> parseDate = (val) =>
{
if (string.IsNullOrEmpty(val)) return "";
DateTime dt;
if (DateTime.TryParseExact(val, "yyyy-MM-dd", null, System.Globalization.DateTimeStyles.None, out dt))
return dt.ToString("yyyy-MM-dd");
int serial;
if (int.TryParse(val, out serial) && serial > 60)
return new DateTime(1899, 12, 30).AddDays(serial).ToString("yyyy-MM-dd");
return "";
};
string cnjhjfrqVal = parseDate(cnjhjfrq);
string yfjhjfrqVal = parseDate(yfjhjfrq);
string sql = $@"UPDATE formtable_main_23_dt1 S1
LEFT JOIN formtable_main_23 S ON S1.mainid = S.id
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
SET S1.wlbz='{wlbz.Replace("'","''")}',S1.onebj={onebjVal},S1.onezje={onezjeVal},S1.twobj={twobjVal},S1.twozje={twozjeVal},
S1.gys='{gys.Replace("'","''")}',S1.cncgfzr='{cncgfzr.Replace("'","''")}',S1.yfcgfzr='{yfcgfzr.Replace("'","''")}',
S1.ywzt={ywztVal},S1.jffxdj={jffxdjVal},S1.jfjz={jfjzVal}";
if (!string.IsNullOrEmpty(cnjhjfrqVal)) sql += $",S1.cnjhjfrq='{cnjhjfrqVal}'";
if (!string.IsNullOrEmpty(yfjhjfrqVal)) sql += $",S1.yfjhjfrq='{yfjhjfrqVal}'";
sql += $" WHERE S.lcbh='{lcbh.Replace("'","''")}' AND S1.xh={xh} AND S5.wlbm='{wlbm.Replace("'","''")}'";
int affected = mySqlUtils.ExecNonQuery(sql);
if (affected > 0) successCount++;
else { failDetails.Add($"{rowLabel}({lcbh}/{xh}/{wlbm}):未匹配到数据"); failCount++; }
}
catch (Exception ex) { failDetails.Add($"{rowLabel}:{ex.Message}"); failCount++; }
}
mySqlUtils.Dispose();
string failMsg = failCount > 0 ? "\n失败明细:\n" + string.Join("\n", failDetails) : "";
return Ok(new { success = true, message = $"导入完成:成功 {successCount} 条,失败 {failCount} 条{failMsg}" });
}
catch (Exception ex)
{
Logger.Error("导入异常:", ex);
return Ok(new { success = false, message = "导入异常:" + ex.Message });
}
}
#endregion
#region 采购看板(按月+业务状态汇总)
[HttpGet]
public IHttpActionResult GetProcurementDashboard()
{
try
{
WmsCommonFunction.UpUserCount("20260728001", "采购看板");
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string sql = @"
SELECT DATE_FORMAT(S.sqrq, '%Y-%m') AS month, S1.ywzt, COUNT(*) AS cnt
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
WHERE YEAR(S.sqrq) = YEAR(CURDATE())
GROUP BY DATE_FORMAT(S.sqrq, '%Y-%m'), S1.ywzt
ORDER BY month, S1.ywzt";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
// ywzt映射
Dictionary<string, string> ywztMap = new Dictionary<string, string> {
{"1","采购订单-转单追踪"},{"2","供应链-待供应商报价"},{"3","供应链-待ED审批"},{"4","供应链-ED驳回,重新议价"},
{"5","供应链-ED已审批"},{"6","研发-采购申请驳回"},{"7","研发-采购申请撤回"},{"8","研发-采购申请取消"},{"9","研发-量产复用件"}
};
// 按月份+ywzt组织数据
Dictionary<string, Dictionary<string, int>> monthData = new Dictionary<string, Dictionary<string, int>>();
Dictionary<string, int> totalByYwzt = new Dictionary<string, int>();
HashSet<string> monthSet = new HashSet<string>();
HashSet<string> ywztSet = new HashSet<string>();
foreach (DataRow row in dt.Rows)
{
string month = row["month"].ToStringEx();
string ywzt = row["ywzt"].ToStringEx();
int cnt = Convert.ToInt32(row["cnt"]);
if (string.IsNullOrEmpty(ywzt)) ywzt = "0";
if (!monthData.ContainsKey(month)) monthData[month] = new Dictionary<string, int>();
monthData[month][ywzt] = cnt;
monthSet.Add(month);
ywztSet.Add(ywzt);
if (!totalByYwzt.ContainsKey(ywzt)) totalByYwzt[ywzt] = 0;
totalByYwzt[ywzt] += cnt;
}
// 排序
var months = monthSet.OrderBy(m => m).ToList();
var ywzts = ywztSet.OrderBy(y => y).ToList();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (var m in months)
{
var row = new Dictionary<string, object> { ["month"] = m };
foreach (var y in ywzts)
row[y] = monthData[m].ContainsKey(y) ? monthData[m][y] : 0;
list.Add(row);
}
// 汇总行
var totalRow = new Dictionary<string, object> { ["month"] = "合计" };
foreach (var y in ywzts)
totalRow[y] = totalByYwzt.ContainsKey(y) ? totalByYwzt[y] : 0;
list.Add(totalRow);
return Ok(new { success = true, data = list, ywzts, ywztMap });
}
catch (Exception ex)
{
Logger.Error("获取采购看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 采购看板二级列表(按yfxm汇总+cncgfzr分布)
[HttpGet]
public IHttpActionResult GetProcurementDashboardDetail(string month, string ywzt)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string ywCond = ywzt == "0" ? "(S1.ywzt IS NULL OR S1.ywzt = '' OR S1.ywzt = '0')" : $"S1.ywzt = '{ywzt}'";
string sql = $@"
SELECT S4.yfxm AS yfxmmc, S1.twobj, S1.cncgfzr, S1.id
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE DATE_FORMAT(S.sqrq, '%Y-%m') = '{month}'
AND {ywCond}
ORDER BY S4.yfxm, S1.xh";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
// 按 yfxm 分组汇总
Dictionary<string, Dictionary<string, object>> groupData = new Dictionary<string, Dictionary<string, object>>();
Dictionary<string, int> cncgfzrTotal = new Dictionary<string, int>();
HashSet<string> cncgfzrSet = new HashSet<string>();
foreach (DataRow row in dt.Rows)
{
string yfxm = row["yfxmmc"].ToStringEx();
if (string.IsNullOrEmpty(yfxm)) yfxm = "(未指定项目)";
string twobj = row["twobj"].ToStringEx();
string cncgfzr = row["cncgfzr"].ToStringEx();
if (!groupData.ContainsKey(yfxm))
groupData[yfxm] = new Dictionary<string, object> { ["yfxmmc"] = yfxm, ["total"] = 0, ["hasPrice"] = 0, ["noPrice"] = 0 };
var g = groupData[yfxm];
g["total"] = (int)g["total"] + 1;
decimal tw;
bool hasPrice = decimal.TryParse(twobj, out tw) && tw > 0;
if (hasPrice)
g["hasPrice"] = (int)g["hasPrice"] + 1;
else if (string.IsNullOrEmpty(cncgfzr))
g["noPrice"] = (int)g["noPrice"] + 1; // 无价格且无负责人 → 未分配采购策略
else
{
// 无价格但有负责人 → 归到对应负责人列
string key = $"_cg_{cncgfzr}";
g[key] = g.ContainsKey(key) ? (int)g[key] + 1 : 1;
}
// 收集所有策略采购负责人(用于生成列)
if (!string.IsNullOrEmpty(cncgfzr))
cncgfzrSet.Add(cncgfzr);
}
var cncgfzrList = cncgfzrSet.OrderBy(c => c).ToList();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (var kv in groupData.OrderBy(k => k.Key))
{
var row = kv.Value;
foreach (var cg in cncgfzrList)
{
string key = $"_cg_{cg}";
row[key] = row.ContainsKey(key) ? row[key] : 0;
}
list.Add(row);
}
// 总计行
var totalRow = new Dictionary<string, object> { ["yfxmmc"] = "合计" };
int sumTotal = 0, sumHas = 0, sumNo = 0;
foreach (var row in list)
{
sumTotal += (int)row["total"];
sumHas += (int)row["hasPrice"];
sumNo += (int)row["noPrice"];
}
totalRow["total"] = sumTotal;
totalRow["hasPrice"] = sumHas;
totalRow["noPrice"] = sumNo;
foreach (var cg in cncgfzrList)
{
int c = 0;
foreach (var row in list)
c += (int)row[$"_cg_{cg}"];
totalRow[$"_cg_{cg}"] = c;
}
list.Add(totalRow);
return Ok(new { success = true, data = list, cncgfzrList });
}
catch (Exception ex)
{
Logger.Error("获取采购看板二级列表异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 采购看板三级列表(按时间范围+ywzt汇总)
[HttpGet]
public IHttpActionResult GetProcurementDashboardDetailItems(string month, string yfxm)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string sql = $@"
SELECT S1.id, S1.ywzt,
DATEDIFF(CURDATE(), LEFT(wrl.operatedate, 10)) AS dayDiff
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN workflow_requestlog wrl ON wrl.requestid = S.requestId AND wrl.logtype IN ('0','2')
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE DATE_FORMAT(S.sqrq, '%Y-%m') = '{month}'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND S1.ywzt IN ('2','3')
ORDER BY wrl.operatedate DESC
LIMIT 10000";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
// 按 ywzt + 时间范围 汇总
int[] ywzts = { 2, 3 };
string[] ranges = { "3以内", "3-7天", "7天以上" };
Dictionary<string, Dictionary<string, int>> summary = new Dictionary<string, Dictionary<string, int>>();
foreach (int y in ywzts)
{
summary[y.ToString()] = new Dictionary<string, int>();
foreach (var r in ranges) summary[y.ToString()][r] = 0;
}
foreach (DataRow row in dt.Rows)
{
string ywzt = row["ywzt"].ToStringEx();
string dayDiffStr = row["dayDiff"].ToStringEx();
int dayDiff;
if (!int.TryParse(dayDiffStr, out dayDiff)) continue;
string range;
if (dayDiff <= 3) range = "3以内";
else if (dayDiff <= 7) range = "3-7天";
else range = "7天以上";
if (summary.ContainsKey(ywzt) && summary[ywzt].ContainsKey(range))
summary[ywzt][range]++;
}
return Ok(new { success = true, summary, yfxm, month });
}
catch (Exception ex)
{
Logger.Error("获取采购看板三级列表异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
// 三级页面内超链接对应yfxm下的其他ywzt
[HttpGet]
public IHttpActionResult GetProcurementDashboardYwztSummary(string month, string yfxm, string ywzt)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string sql = $@"
SELECT DISTINCT S1.ywzt
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE DATE_FORMAT(S.sqrq, '%Y-%m') = '{month}'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND S1.ywzt IS NOT NULL AND S1.ywzt != ''";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
return Ok(new { success = true, data = dt });
}
catch (Exception ex)
{
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 采购看板四级列表(明细数据)
[HttpGet]
public IHttpActionResult GetProcurementDashboardLevel4(string month, string yfxm, string ywzt, string rangeType)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
// 时间范围条件
string rangeCond;
if (rangeType == "3以内") rangeCond = "DATEDIFF(CURDATE(), LEFT(wrl.operatedate, 10)) <= 3";
else if (rangeType == "3-7天") rangeCond = "DATEDIFF(CURDATE(), LEFT(wrl.operatedate, 10)) BETWEEN 4 AND 7";
else rangeCond = "DATEDIFF(CURDATE(), LEFT(wrl.operatedate, 10)) > 7";
string sql = $@"
SELECT S.lcbh, S.djbh, S1.xh, S5.wlbm AS wlbmCode, S1.wlmc, S1.sqsl, S1.onebj, S1.twobj,
S1.gys, S1.cncgfzr, S1.yfcgfzr, S1.ywzt,
DATE_FORMAT(S1.dhrq, '%Y-%m-%d') AS dhrq,
LEFT(wrl.operatedate, 10) AS pmDate,
DATEDIFF(CURDATE(), LEFT(wrl.operatedate, 10)) AS dayDiff
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
LEFT JOIN workflow_requestlog wrl ON wrl.requestid = S.requestId AND wrl.logtype IN ('0','2')
WHERE DATE_FORMAT(S.sqrq, '%Y-%m') = '{month}'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND S1.ywzt = '{ywzt}'
AND {rangeCond}
ORDER BY wrl.operatedate DESC, S.lcbh, S1.xh";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["lcbh"] = row["lcbh"].ToStringEx(),
["djbh"] = row["djbh"].ToStringEx(),
["xh"] = row["xh"].ToStringEx(),
["wlbmCode"] = row["wlbmCode"].ToStringEx(),
["wlmc"] = row["wlmc"].ToStringEx(),
["sqsl"] = row["sqsl"].ToStringEx(),
["onebj"] = row["onebj"].ToStringEx(),
["twobj"] = row["twobj"].ToStringEx(),
["gys"] = row["gys"].ToStringEx(),
["cncgfzr"] = row["cncgfzr"].ToStringEx(),
["yfcgfzr"] = row["yfcgfzr"].ToStringEx(),
["ywzt"] = row["ywzt"].ToStringEx(),
["dhrq"] = row["dhrq"].ToStringEx(),
["pmDate"] = row["pmDate"].ToStringEx(),
["dayDiff"] = row["dayDiff"].ToStringEx()
});
}
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("获取采购看板四级列表异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 采购物料交付状态看板
[HttpGet]
public IHttpActionResult GetDeliveryDashboard()
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string sql = @"
SELECT S4.yfxm AS yfxmmc,
COUNT(*) AS total,
SUM(CASE WHEN S2.id IS NULL THEN 1 ELSE 0 END) AS noOrder,
SUM(CASE WHEN S1.cnjhjfrq IS NULL OR S1.cnjhjfrq = '' THEN 1 ELSE 0 END) AS noTarget,
SUM(CASE WHEN S1.yfjhjfrq IS NULL OR S1.yfjhjfrq = '' THEN 1 ELSE 0 END) AS noPlan,
SUM(CASE WHEN S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND S1.yfjhjfrq >= CURDATE() THEN 1 ELSE 0 END) AS inProgress,
SUM(CASE WHEN S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND S1.yfjhjfrq < CURDATE() THEN 1 ELSE 0 END) AS overdue,
SUM(CASE WHEN S1.ywzt IN ('8','9') THEN 1 ELSE 0 END) AS cancelled
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN formtable_main_39_dt1 S2 ON S.djbh = S2.cgsqdjbh AND S1.wlbmcjd = S2.wlbm
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE YEAR(S.sqrq) = YEAR(CURDATE()) AND S.lcbh LIKE 'YFCGSQ%' AND S4.yfxm IS NOT NULL AND S4.yfxm != ''
GROUP BY S4.yfxm
ORDER BY S4.yfxm";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
int tNoOrder=0,tNoTarget=0,tNoPlan=0,tInProgress=0,tOverdue=0,tCancelled=0;
foreach (DataRow r in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["yfxmmc"] = r["yfxmmc"].ToStringEx(),
["total"] = Convert.ToInt32(r["total"]),
["noOrder"] = Convert.ToInt32(r["noOrder"]),
["noTarget"] = Convert.ToInt32(r["noTarget"]),
["noPlan"] = Convert.ToInt32(r["noPlan"]),
["inProgress"] = Convert.ToInt32(r["inProgress"]),
["overdue"] = Convert.ToInt32(r["overdue"]),
["cancelled"] = Convert.ToInt32(r["cancelled"])
});
tNoOrder += Convert.ToInt32(r["noOrder"]);
tNoTarget += Convert.ToInt32(r["noTarget"]);
tNoPlan += Convert.ToInt32(r["noPlan"]);
tInProgress += Convert.ToInt32(r["inProgress"]);
tOverdue += Convert.ToInt32(r["overdue"]);
tCancelled += Convert.ToInt32(r["cancelled"]);
}
list.Add(new Dictionary<string, object>
{
["yfxmmc"] = "合计",
["total"] = dt.Rows.Count,
["noOrder"] = tNoOrder,["noTarget"] = tNoTarget,["noPlan"] = tNoPlan,
["inProgress"] = tInProgress,["overdue"] = tOverdue,["cancelled"] = tCancelled
});
return Ok(new { success = true, data = list });
}
catch (Exception ex)
{
Logger.Error("获取交付看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 交付看板1号二级(按研发采购负责人汇总)
[HttpGet]
public IHttpActionResult GetDeliveryLevel21(string yfxm, string type)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string cond = type == "noTarget"
? "(S1.cnjhjfrq IS NULL OR S1.cnjhjfrq = '')"
: "(S1.yfjhjfrq IS NULL OR S1.yfjhjfrq = '')";
string sql = $@"
SELECT IFNULL(S1.yfcgfzr, '') AS yfcgfzr, COUNT(*) AS cnt
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE YEAR(S.sqrq) = YEAR(CURDATE()) AND S.lcbh LIKE 'YFCGSQ%'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND {cond}
GROUP BY IFNULL(S1.yfcgfzr, '')
ORDER BY cnt DESC";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
int total = 0;
foreach (DataRow r in dt.Rows)
{
int c = Convert.ToInt32(r["cnt"]);
list.Add(new Dictionary<string, object> { ["yfcgfzr"] = r["yfcgfzr"].ToStringEx(), ["cnt"] = c });
total += c;
}
list.Add(new Dictionary<string, object> { ["yfcgfzr"] = "合计", ["cnt"] = total });
return Ok(new { success = true, data = list, yfxm, type });
}
catch (Exception ex)
{
Logger.Error("获取1号二级看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 交付看板1号三级(明细数据)
[HttpGet]
public IHttpActionResult GetDeliveryLevel31(string yfxm, string type, string yfcgfzr)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string cond = type == "noTarget"
? "(S1.cnjhjfrq IS NULL OR S1.cnjhjfrq = '')"
: "(S1.yfjhjfrq IS NULL OR S1.yfjhjfrq = '')";
string yfcgfzrCond = string.IsNullOrEmpty(yfcgfzr)
? "(S1.yfcgfzr IS NULL OR S1.yfcgfzr = '')"
: $"S1.yfcgfzr = '{yfcgfzr.Replace("'","''")}'";
string sql = $@"
SELECT S.lcbh, S.djbh, S1.xh, S5.wlbm AS wlbmCode, S1.wlmc, S1.sqsl,
S1.gys, S1.cncgfzr, S1.yfcgfzr, S1.ywzt,
DATE_FORMAT(S1.dhrq, '%Y-%m-%d') AS dhrq
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE YEAR(S.sqrq) = YEAR(CURDATE()) AND S.lcbh LIKE 'YFCGSQ%'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND {cond}
AND {yfcgfzrCond}
ORDER BY S.lcbh, S1.xh";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["lcbh"] = row["lcbh"].ToStringEx(),
["djbh"] = row["djbh"].ToStringEx(),
["xh"] = row["xh"].ToStringEx(),
["wlbmCode"] = row["wlbmCode"].ToStringEx(),
["wlmc"] = row["wlmc"].ToStringEx(),
["sqsl"] = row["sqsl"].ToStringEx(),
["gys"] = row["gys"].ToStringEx(),
["cncgfzr"] = row["cncgfzr"].ToStringEx(),
["yfcgfzr"] = row["yfcgfzr"].ToStringEx(),
["ywzt"] = row["ywzt"].ToStringEx(),
["dhrq"] = row["dhrq"].ToStringEx()
});
}
return Ok(new { success = true, data = list, yfxm, type, yfcgfzr });
}
catch (Exception ex)
{
Logger.Error("获取1号三级看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 交付看板2号二级(按供应商+时间范围汇总)
[HttpGet]
public IHttpActionResult GetDeliveryLevel22(string yfxm, string type)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string cond;
if (type == "inProgress")
cond = "S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND S1.yfjhjfrq >= CURDATE()";
else
cond = "S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND S1.yfjhjfrq < CURDATE()";
string sql = $@"
SELECT S1.gys,
SUM(CASE WHEN DATEDIFF(S1.yfjhjfrq, CURDATE()) <= 3 THEN 1 ELSE 0 END) AS r1,
SUM(CASE WHEN DATEDIFF(S1.yfjhjfrq, CURDATE()) BETWEEN 4 AND 7 THEN 1 ELSE 0 END) AS r2,
SUM(CASE WHEN DATEDIFF(S1.yfjhjfrq, CURDATE()) BETWEEN 8 AND 14 THEN 1 ELSE 0 END) AS r3,
SUM(CASE WHEN DATEDIFF(S1.yfjhjfrq, CURDATE()) > 14 THEN 1 ELSE 0 END) AS r4
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE YEAR(S.sqrq) = YEAR(CURDATE()) AND S.lcbh LIKE 'YFCGSQ%'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND S1.gys IS NOT NULL AND S1.gys != ''
AND {cond}
GROUP BY S1.gys
ORDER BY (r1+r2+r3+r4) DESC";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
int t1=0,t2=0,t3=0,t4=0;
foreach (DataRow r in dt.Rows)
{
int c1=Convert.ToInt32(r["r1"]),c2=Convert.ToInt32(r["r2"]),c3=Convert.ToInt32(r["r3"]),c4=Convert.ToInt32(r["r4"]);
list.Add(new Dictionary<string, object> {["gys"]=r["gys"].ToStringEx(),["r1"]=c1,["r2"]=c2,["r3"]=c3,["r4"]=c4});
t1+=c1;t2+=c2;t3+=c3;t4+=c4;
}
list.Add(new Dictionary<string, object> {["gys"]="合计",["r1"]=t1,["r2"]=t2,["r3"]=t3,["r4"]=t4});
return Ok(new { success = true, data = list, yfxm, type });
}
catch (Exception ex)
{
Logger.Error("获取2号二级看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 交付看板2号三级(明细数据)
[HttpGet]
public IHttpActionResult GetDeliveryLevel32(string yfxm, string type, string gys, string range)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string cond;
if (type == "inProgress")
cond = "S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND S1.yfjhjfrq >= CURDATE()";
else
cond = "S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND S1.yfjhjfrq < CURDATE()";
string rangeCond;
int days = int.Parse(range.Replace("d", ""));
if (days == 3) rangeCond = "DATEDIFF(S1.yfjhjfrq, CURDATE()) <= 3";
else if (days == 7) rangeCond = "DATEDIFF(S1.yfjhjfrq, CURDATE()) BETWEEN 4 AND 7";
else if (days == 14) rangeCond = "DATEDIFF(S1.yfjhjfrq, CURDATE()) BETWEEN 8 AND 14";
else rangeCond = "DATEDIFF(S1.yfjhjfrq, CURDATE()) > 14";
string sql = $@"
SELECT S.lcbh, S.djbh, S1.xh, S5.wlbm AS wlbmCode, S1.wlmc, S1.sqsl,
S1.gys, S1.cncgfzr, S1.yfcgfzr, S1.ywzt,
DATE_FORMAT(S1.dhrq, '%Y-%m-%d') AS dhrq,
S1.yfjhjfrq,
DATEDIFF(S1.yfjhjfrq, CURDATE()) AS dayDiff
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE YEAR(S.sqrq) = YEAR(CURDATE()) AND S.lcbh LIKE 'YFCGSQ%'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND S1.gys = '{gys.Replace("'","''")}'
AND {cond} AND {rangeCond}
ORDER BY S1.yfjhjfrq, S.lcbh, S1.xh";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["lcbh"] = row["lcbh"].ToStringEx(),
["djbh"] = row["djbh"].ToStringEx(),
["xh"] = row["xh"].ToStringEx(),
["wlbmCode"] = row["wlbmCode"].ToStringEx(),
["wlmc"] = row["wlmc"].ToStringEx(),
["sqsl"] = row["sqsl"].ToStringEx(),
["gys"] = row["gys"].ToStringEx(),
["cncgfzr"] = row["cncgfzr"].ToStringEx(),
["yfcgfzr"] = row["yfcgfzr"].ToStringEx(),
["ywzt"] = row["ywzt"].ToStringEx(),
["dhrq"] = row["dhrq"].ToStringEx(),
["yfjhjfrq"] = row["yfjhjfrq"].ToStringEx(),
["dayDiff"] = row["dayDiff"].ToStringEx()
});
}
return Ok(new { success = true, data = list, yfxm, type, gys, range });
}
catch (Exception ex)
{
Logger.Error("获取2号三级看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 交付看板未转单二级(按时间范围汇总,无采购订单物料)
[HttpGet]
public IHttpActionResult GetDeliveryNoOrder2(string yfxm)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string sql = $@"
SELECT S4.yfxm AS yfxmmc,
SUM(CASE WHEN S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NOT NULL AND DATEDIFF(S1.yfjhjfrq, CURDATE()) <= 1 THEN 1 ELSE 0 END) AS r1,
SUM(CASE WHEN S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NOT NULL AND DATEDIFF(S1.yfjhjfrq, CURDATE()) BETWEEN 2 AND 3 THEN 1 ELSE 0 END) AS r2,
SUM(CASE WHEN S1.yfjhjfrq IS NULL OR S1.yfjhjfrq = '' OR DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NULL OR DATEDIFF(S1.yfjhjfrq, CURDATE()) > 3 THEN 1 ELSE 0 END) AS r3
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN formtable_main_39_dt1 S2 ON S.djbh = S2.cgsqdjbh AND S1.wlbmcjd = S2.wlbm
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE YEAR(S.sqrq) = YEAR(CURDATE()) AND S.lcbh LIKE 'YFCGSQ%'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND S2.id IS NULL
GROUP BY S4.yfxm";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
int t1=0,t2=0,t3=0;
foreach (DataRow r in dt.Rows)
{
int c1=Convert.ToInt32(r["r1"]),c2=Convert.ToInt32(r["r2"]),c3=Convert.ToInt32(r["r3"]);
list.Add(new Dictionary<string, object> {["yfxmmc"]=r["yfxmmc"].ToStringEx(),["r1"]=c1,["r2"]=c2,["r3"]=c3});
t1+=c1;t2+=c2;t3+=c3;
}
list.Add(new Dictionary<string, object> {["yfxmmc"]="合计",["r1"]=t1,["r2"]=t2,["r3"]=t3});
return Ok(new { success = true, data = list, yfxm });
}
catch (Exception ex)
{
Logger.Error("获取未转单二级看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 交付看板未转单三级(按研发采购负责人+时间范围汇总)
[HttpGet]
public IHttpActionResult GetDeliveryNoOrder3(string yfxm, string range)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string rangeCond;
int days = int.Parse(range.Replace("d",""));
if (days == 1) rangeCond = "(S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NOT NULL AND DATEDIFF(S1.yfjhjfrq, CURDATE()) <= 1)";
else if (days == 3) rangeCond = "(S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NOT NULL AND DATEDIFF(S1.yfjhjfrq, CURDATE()) BETWEEN 2 AND 3)";
else rangeCond = "(S1.yfjhjfrq IS NULL OR S1.yfjhjfrq = '' OR DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NULL OR DATEDIFF(S1.yfjhjfrq, CURDATE()) > 3)";
string sql = $@"
SELECT IFNULL(S1.yfcgfzr, '') AS yfcgfzr, COUNT(*) AS cnt
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN formtable_main_39_dt1 S2 ON S.djbh = S2.cgsqdjbh AND S1.wlbmcjd = S2.wlbm
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE YEAR(S.sqrq) = YEAR(CURDATE()) AND S.lcbh LIKE 'YFCGSQ%'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND S2.id IS NULL
AND {rangeCond}
GROUP BY IFNULL(S1.yfcgfzr, '')
ORDER BY cnt DESC";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
int total = 0;
foreach (DataRow r in dt.Rows)
{
int c = Convert.ToInt32(r["cnt"]);
list.Add(new Dictionary<string, object> {["yfcgfzr"]=r["yfcgfzr"].ToStringEx(),["cnt"]=c});
total += c;
}
list.Add(new Dictionary<string, object> {["yfcgfzr"]="合计",["cnt"]=total});
return Ok(new { success = true, data = list, yfxm, range });
}
catch (Exception ex)
{
Logger.Error("获取未转单三级看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
#region 交付看板未转单四级(明细数据)
[HttpGet]
public IHttpActionResult GetDeliveryNoOrder4(string yfxm, string range, string yfcgfzr)
{
try
{
MySqlUtils mySqlUtils = new MySqlUtils();
string oaConn = ConfigurationManager.ConnectionStrings["OAConnection"].ConnectionString;
mySqlUtils.ConnectionString = oaConn;
mySqlUtils.GetConnection();
string rangeCond;
int days = int.Parse(range.Replace("d",""));
if (days == 1) rangeCond = "(S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NOT NULL AND DATEDIFF(S1.yfjhjfrq, CURDATE()) <= 1)";
else if (days == 3) rangeCond = "(S1.yfjhjfrq IS NOT NULL AND S1.yfjhjfrq != '' AND DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NOT NULL AND DATEDIFF(S1.yfjhjfrq, CURDATE()) BETWEEN 2 AND 3)";
else rangeCond = "(S1.yfjhjfrq IS NULL OR S1.yfjhjfrq = '' OR DATEDIFF(S1.yfjhjfrq, CURDATE()) IS NULL OR DATEDIFF(S1.yfjhjfrq, CURDATE()) > 3)";
string yfcCond = string.IsNullOrEmpty(yfcgfzr)
? "(S1.yfcgfzr IS NULL OR S1.yfcgfzr = '')"
: $"S1.yfcgfzr = '{yfcgfzr.Replace("'","''")}'";
string sql = $@"
SELECT S.lcbh, S.djbh, S1.xh, S5.wlbm AS wlbmCode, S1.wlmc, S1.sqsl,
S1.gys, S1.cncgfzr, S1.yfcgfzr, S1.ywzt,
DATE_FORMAT(S1.dhrq, '%Y-%m-%d') AS dhrq,
S1.yfjhjfrq,
DATEDIFF(S1.yfjhjfrq, CURDATE()) AS dayDiff
FROM formtable_main_23 S
JOIN formtable_main_23_dt1 S1 ON S.id = S1.mainid
LEFT JOIN formtable_main_39_dt1 S2 ON S.djbh = S2.cgsqdjbh AND S1.wlbmcjd = S2.wlbm
LEFT JOIN uf_wlbm S5 ON S1.wlbmyf = S5.id
LEFT JOIN uf_gxyfxm S4 ON S.yfxm = S4.id
WHERE YEAR(S.sqrq) = YEAR(CURDATE()) AND S.lcbh LIKE 'YFCGSQ%'
AND S4.yfxm = '{yfxm.Replace("'","''")}'
AND S2.id IS NULL
AND {rangeCond} AND {yfcCond}
ORDER BY S1.yfjhjfrq, S.lcbh, S1.xh";
DataTable dt = mySqlUtils.ExecQuery(sql);
mySqlUtils.Dispose();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in dt.Rows)
{
list.Add(new Dictionary<string, object>
{
["lcbh"] = row["lcbh"].ToStringEx(),
["djbh"] = row["djbh"].ToStringEx(),
["xh"] = row["xh"].ToStringEx(),
["wlbmCode"] = row["wlbmCode"].ToStringEx(),
["wlmc"] = row["wlmc"].ToStringEx(),
["sqsl"] = row["sqsl"].ToStringEx(),
["gys"] = row["gys"].ToStringEx(),
["cncgfzr"] = row["cncgfzr"].ToStringEx(),
["yfcgfzr"] = row["yfcgfzr"].ToStringEx(),
["ywzt"] = row["ywzt"].ToStringEx(),
["dhrq"] = row["dhrq"].ToStringEx(),
["yfjhjfrq"] = row["yfjhjfrq"].ToStringEx(),
["dayDiff"] = row["dayDiff"].ToStringEx()
});
}
return Ok(new { success = true, data = list, yfxm, range, yfcgfzr });
}
catch (Exception ex)
{
Logger.Error("获取未转单四级看板异常:", ex);
return Ok(new { success = false, message = ex.Message });
}
}
#endregion
}
}