using Masuit.Tools;
|
using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.util;
|
using SqlSugar;
|
|
namespace MES.Service.service.QC;
|
|
/// <summary>
|
/// 产线提交服务
|
/// </summary>
|
public class ProductionLineService
|
{
|
private readonly BaseService _baseService = new();
|
|
/// <summary>
|
/// 获取产线提交页面数据（未提交/已提交）
|
/// </summary>
|
/// <param name="queryObj">查询参数</param>
|
/// <returns></returns>
|
public (List<ProductionLinePageResult> item, int TotalCount) GetProductionLinePage(ProductionLineQueryDto queryObj)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
if (queryObj.Status == 0)
|
{
|
// 未提交页面查询
|
return GetUnsubmittedData(queryObj, db);
|
}
|
else
|
{
|
// 已提交页面查询
|
return GetSubmittedData(queryObj, db);
|
}
|
}
|
|
/// <summary>
|
/// 获取未提交数据（从工单表查询，包含不合格的首检单）
|
/// </summary>
|
private (List<ProductionLinePageResult> item, int TotalCount) GetUnsubmittedData(ProductionLineQueryDto queryObj, SqlSugarClient db)
|
{
|
var totalCount = 0;
|
|
//// 第一步：查询工单基础数据
|
//var workOrderSql = @"
|
//SELECT
|
// A.ID,
|
// A.DAA001,
|
// A.DAA002 AS ItemNo,
|
// A.DAA003,
|
// A.DAA004,
|
// A.DAA008,
|
// A.DAA018,
|
// A.DAA019 AS DaA019,
|
// A.DAA020 AS Line,
|
// A.DAA024,
|
// A.PID AS Pbaid,
|
// T.PROJECT_CODE AS ProjecT_CODE,
|
// u.FNAME AS FName,
|
// 0 AS Urgent,
|
// 0 AS IsFirst,
|
// CAST(NULL AS VARCHAR2(50)) AS BillNo,
|
// CAST(NULL AS VARCHAR2(50)) AS Result,
|
// CAST(NULL AS VARCHAR2(50)) AS StatusUser,
|
// CAST(NULL AS DATE) AS CreateTime,
|
// CAST(NULL AS NUMBER) AS InspectionId
|
//FROM WOMDAA A
|
//LEFT JOIN WOMCAA c ON c.CAA001 = A.DAA021
|
//LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
|
//LEFT JOIN MES_UNIT u ON TO_CHAR(u.ID) = TO_CHAR(A.DAA005)
|
//WHERE A.DAA018 LIKE '%已开工%'
|
// AND A.FSTATUS = 1
|
// AND A.F_TYPE = 0
|
// AND CASE
|
// WHEN C.CAA023 = '0' THEN '开立'
|
// WHEN C.CAA023 = '1' THEN '已核准'
|
// WHEN C.CAA023 = '2' THEN '开工'
|
// WHEN C.CAA023 = '3' THEN '完工'
|
// WHEN C.CAA023 = '4' THEN '核准中'
|
// END LIKE '%开工%'";
|
|
// 第一步：查询工单基础数据
|
// 第一步：查询工单基础数据
|
var workOrderSql = @"
|
SELECT
|
A.ID,
|
A.DAA001,
|
item.ITEM_NO AS ItemNo,
|
A.DAA003,
|
A.DAA004,
|
A.DAA008,
|
A.DAA018,
|
A.DAA019 AS DaA019,
|
A.DAA020 AS Line,
|
|
A.PID AS Pbaid,
|
T.PROJECT_CODE AS ProjecT_CODE,
|
u.FNAME AS FName,
|
0 AS Urgent,
|
0 AS IsFirst,
|
CAST(NULL AS VARCHAR2(50)) AS BillNo,
|
CAST(NULL AS VARCHAR2(50)) AS Result,
|
CAST(BE1.FNAME AS VARCHAR2(50)) AS StatusUser,
|
CAST(NULL AS DATE) AS CreateTime,
|
CAST(NULL AS NUMBER) AS InspectionId
|
FROM WOMDAA A
|
LEFT JOIN WOMCAA c ON c.CAA001 = A.DAA021
|
LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
|
LEFT JOIN MES_UNIT u ON TO_CHAR(u.ID) = TO_CHAR(A.DAA005)
|
left join MES_ITEMS item on item.ITEM_ID=c.CAA006
|
LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
|
LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
|
LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
|
WHERE A.DAA018 LIKE '%已开工%'
|
AND A.FSTATUS = 1
|
AND A.F_TYPE = 0
|
AND CASE
|
WHEN C.CAA023 = '0' THEN '开立'
|
WHEN C.CAA023 = '1' THEN '已核准'
|
WHEN C.CAA023 = '2' THEN '开工'
|
WHEN C.CAA023 = '3' THEN '完工'
|
WHEN C.CAA023 = '4' THEN '核准中'
|
END LIKE '%开工%'";
|
|
// 添加搜索条件
|
if (StringUtil.IsNotNullOrEmpty(queryObj.SearchValue))
|
{
|
workOrderSql += " AND (A.DAA001 LIKE :SearchValue OR A.DAA002 LIKE :SearchValue)";
|
}
|
|
var workOrders = db.SqlQueryable<ProductionLinePageResult>(workOrderSql)
|
.AddParameters(new { SearchValue = $"%{queryObj.SearchValue}%" })
|
.ToList();
|
|
if (!workOrders.Any())
|
{
|
return (new List<ProductionLinePageResult>(), 0);
|
}
|
|
// 转换为字符串用于IN查询
|
var workOrderIdStrings = workOrders
|
.Where(w => !string.IsNullOrEmpty(w.ID.ToString()))
|
.Select(w => w.ID)
|
.ToList();
|
|
if (!workOrderIdStrings.Any())
|
{
|
return (new List<ProductionLinePageResult>(), 0);
|
}
|
|
// 第二步：查询这些工单对应的首检单及检验结果
|
var inspectionResultSql = $@"
|
SELECT
|
A.ID,
|
A.BILL_NO AS BillNo,
|
A.PBAID,
|
A.STATUS_USER AS StatusUser,
|
A.CREATE_TIME AS CreateTime,
|
CASE
|
WHEN RES.检验结果 IS NULL THEN '未完成'
|
ELSE RES.检验结果
|
END AS Result
|
FROM (
|
-- 只取每个工单(PBAID)最新一条首检单
|
SELECT *
|
FROM (
|
SELECT
|
R.*,
|
ROW_NUMBER() OVER(PARTITION BY R.PBAID ORDER BY R.CREATE_TIME DESC) AS RN
|
FROM QS_ITEM_IPI_REQ R
|
) WHERE RN = 1
|
) A
|
LEFT JOIN (
|
SELECT
|
PID,
|
CASE
|
WHEN (COUNT(CASE WHEN PASSED = -1 THEN 1 END) > 0) THEN NULL
|
WHEN (COUNT(CASE WHEN PASSED = 1 THEN 1 END) = COUNT(*)) THEN '合格'
|
ELSE '不合格'
|
END AS 检验结果
|
FROM (
|
SELECT
|
A.PID,
|
CASE
|
WHEN (A.IS_PASS IS NULL OR B.完成状态 IS NULL) THEN -1
|
WHEN (NVL(A.IS_PASS, -1) = NVL(B.完成状态, -1) AND A.IS_PASS = 1) THEN 1
|
WHEN (NVL(A.IS_PASS, -1) = NVL(B.完成状态, -1) AND A.IS_PASS = 0) THEN 0
|
ELSE -1
|
END AS PASSED
|
FROM QS_ITEM_IPI_ITEM A
|
LEFT JOIN (
|
SELECT
|
PID,
|
CASE
|
WHEN (COUNT(CASE WHEN FSTAND IS NULL THEN 1 END) > 0) THEN NULL
|
WHEN (COUNT(CASE WHEN FSTAND = '√' THEN 1 END) = COUNT(1)) THEN 1
|
ELSE 0
|
END AS 完成状态
|
FROM QS_ITEM_IPI_ITEM_DETAIL
|
GROUP BY PID
|
) B ON A.ID = B.PID
|
)
|
GROUP BY PID
|
) RES ON RES.PID = A.ID
|
WHERE A.PBAID IN ({string.Join(",", workOrderIdStrings)})";
|
|
var inspections = db.SqlQueryable<InspectionResultDto>(inspectionResultSql).ToList();
|
|
// 第三步：合并数据并过滤
|
var result = (from wo in workOrders
|
join ins in inspections on wo.ID.ToString() equals ins.Pbaid.ToString() into insGroup
|
from ins in insGroup.DefaultIfEmpty()
|
where ins == null || ins.Result == "不合格" // 未提交或不合格的显示
|
select new ProductionLinePageResult
|
{
|
ID = wo.ID,
|
DAA001 = wo.DAA001,
|
ItemNo = wo.ItemNo,
|
DAA003 = wo.DAA003,
|
DAA004 = wo.DAA004,
|
DAA008 = wo.DAA008,
|
DAA018 = wo.DAA018,
|
DaA019 = wo.DaA019,
|
Line = wo.Line,
|
DAA024 = wo.DAA024,
|
ProjecT_CODE = wo.ProjecT_CODE,
|
FName = wo.FName,
|
BillNo = ins?.BillNo,
|
Result = ins?.Result,
|
StatusUser = wo?.StatusUser,
|
CreateTime = ins?.CreateTime,
|
InspectionId = ins?.ID,
|
Pbaid = wo.Pbaid,
|
Urgent = wo.Urgent,
|
IsFirst = wo.IsFirst,
|
SortOrder = ins?.Result == "不合格" ? 1 : 0 // 不合格置顶
|
})
|
.OrderByDescending(x => x.SortOrder)
|
.ThenByDescending(x => x.DAA024)
|
.ToList();
|
|
totalCount = result.Count;
|
|
// 分页
|
var pagedData = result
|
.Skip((queryObj.PageIndex - 1) * queryObj.Limit)
|
.Take(queryObj.Limit)
|
.ToList();
|
|
return (pagedData, totalCount);
|
}
|
|
/// <summary>
|
/// 获取已提交数据（从首检单表查询）
|
/// </summary>
|
private (List<ProductionLinePageResult> item, int TotalCount) GetSubmittedData(ProductionLineQueryDto queryObj, SqlSugarClient db)
|
{
|
var totalCount = 0;
|
|
var sql = @"
|
SELECT
|
A.ID AS InspectionId,
|
A.BILL_NO AS BillNo,
|
A.PBAID AS Pbaid,
|
DA.DAA001,
|
item.ITEM_NO AS ItemNo,
|
DA.DAA003,
|
DA.DAA004,
|
DA.DAA008,
|
DA.DAA018,
|
DA.DAA019 AS DaA019,
|
DA.DAA020 AS Line,
|
A.CREATE_TIME AS CreateTime,
|
NVL(BE3.FNAME, BE1.FNAME) AS StatusUser,
|
A.FSUBMIT,
|
A.CREATE_BY,
|
0 AS Urgent,
|
0 AS IsFirst,
|
T.PROJECT_CODE AS ProjecT_CODE,
|
CASE
|
WHEN 检验结果 IS NULL THEN '未完成'
|
ELSE 检验结果
|
END AS Result
|
FROM QS_ITEM_IPI_REQ A
|
LEFT JOIN WOMDAA DA ON DA.ID = A.PBAID
|
LEFT JOIN WOMCAA c ON c.CAA001 = DA.DAA021
|
LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
|
left join MES_ITEMS item on item.ITEM_ID=c.CAA006
|
--变更后
|
LEFT JOIN INSPECTOR_CHANGE_SJ_LOG chlog
|
ON chlog.INSPECTION_NO = a.BILL_NO AND chlog.IS_VALID = 'Y'
|
LEFT JOIN SYS_USER BE3 ON chlog.INSPECTOR = BE3.FCODE
|
--变更前
|
LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
|
LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
|
LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
|
LEFT JOIN (
|
SELECT
|
PID,
|
CASE
|
WHEN (COUNT(CASE WHEN PASSED = -1 THEN 1 END) > 0) THEN NULL
|
WHEN (COUNT(CASE WHEN PASSED = 1 THEN 1 END) = COUNT(*)) THEN '合格'
|
ELSE '不合格'
|
END AS 检验结果
|
FROM (
|
SELECT
|
A.PID,
|
CASE
|
WHEN (A.IS_PASS IS NULL OR B.完成状态 IS NULL) THEN -1
|
WHEN (NVL(A.IS_PASS, -1) = NVL(B.完成状态, -1) AND A.IS_PASS = 1) THEN 1
|
WHEN (NVL(A.IS_PASS, -1) = NVL(B.完成状态, -1) AND A.IS_PASS = 0) THEN 0
|
ELSE -1
|
END AS PASSED
|
FROM QS_ITEM_IPI_ITEM A
|
LEFT JOIN (
|
SELECT
|
PID,
|
CASE
|
WHEN (COUNT(CASE WHEN FSTAND IS NULL THEN 1 END) > 0) THEN NULL
|
WHEN (COUNT(CASE WHEN FSTAND = '√' THEN 1 END) = COUNT(1)) THEN 1
|
ELSE 0
|
END AS 完成状态
|
FROM QS_ITEM_IPI_ITEM_DETAIL
|
GROUP BY PID
|
) B ON A.ID = B.PID
|
)
|
GROUP BY PID
|
) RES ON RES.PID = A.ID
|
WHERE A.ID IS NOT NULL";
|
|
// 添加搜索条件
|
if (StringUtil.IsNotNullOrEmpty(queryObj.SearchValue))
|
{
|
sql += " AND (A.BILL_NO LIKE :SearchValue OR DA.DAA001 LIKE :SearchValue OR DA.DAA002 LIKE :SearchValue)";
|
}
|
|
sql += " ORDER BY A.CREATE_TIME DESC";
|
|
var data = db.SqlQueryable<ProductionLinePageResult>(sql)
|
.AddParameters(new { SearchValue = $"%{queryObj.SearchValue}%" })
|
.ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
|
|
return (data, totalCount);
|
}
|
|
|
|
/// <summary>
|
/// 提交检验（调用存储过程生成首检单）
|
/// </summary>
|
/// <param name="workOrderId">工单ID</param>
|
/// <param name="userNo">用户账号</param>
|
/// <returns></returns>
|
public (bool success, string message) SubmitInspection(decimal workOrderId, string userNo)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
try
|
{
|
// 定义存储过程参数
|
var pId = new SugarParameter("P_ID", workOrderId);
|
var pUser = new SugarParameter("P_USER", userNo);
|
var pIsh = new SugarParameter("P_ISH", 0); // 0表示首检
|
var pFlag = new SugarParameter("P_FLAG", null, true) { Direction = System.Data.ParameterDirection.Output };
|
var pText = new SugarParameter("P_TEXT", null, true)
|
{
|
Direction = System.Data.ParameterDirection.Output,
|
Size = 255
|
};
|
|
// 调用存储过程
|
db.Ado.UseStoredProcedure().ExecuteCommand(
|
"PRC_SJ_GENERATE",
|
pId, pUser, pIsh, pFlag, pText
|
);
|
|
// 获取输出参数
|
var flag = pFlag.Value != null ? Convert.ToInt32(pFlag.Value) : 1;
|
var text = pText.Value?.ToString() ?? "未知错误";
|
|
if (flag == 0)
|
{
|
return (true, text);
|
}
|
else
|
{
|
return (false, text);
|
}
|
}
|
catch (Exception ex)
|
{
|
return (false, $"提交检验失败: {ex.Message}");
|
}
|
}
|
|
|
|
}
|
