using Masuit.Tools;
|
using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.Modes;
|
using MES.Service.service.BasicData;
|
using Oracle.ManagedDataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace MES.Service.service.Warehouse
|
{
|
public class WomdaaManager
|
{
|
/// <summary>
|
/// 获取产测登录号对应工单信息
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public DataTable GetTickeInfo(string LineId,string LoginId)
|
{
|
OracleSQLHelper SQLHelper = new();
|
//查工单信息
|
//var sql1 = @"select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from MESLINE_LIKE_CCLOGIN A
|
// join MES_LINE b on A.LINEID=b.ID
|
// left join WOMDAA C on C.DAA015=B.LINE_NO
|
// left join mes_items s on s.id = C.daa002
|
// where LOGINID='" + id + "' and DAA018='已开工' order by DAA016 asc";
|
|
//改为按站位号反查产线，再通过产线查出工单信息
|
//var sql1 = @"select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from MESLINE_LIKE_CCLOGIN A
|
// left join MESLINE_LIKE_CCLOGIN_WORKS A1 on A.id=a1.LOGINID
|
// join MES_LINE b on A.LINEID=b.ID
|
// left join WOMDAA C on C.DAA015=B.LINE_NO
|
// left join mes_items s on s.id = C.daa002
|
// where WORKSNO='" + id + "' and DAA018='已开工' order by DAA016 asc";
|
|
|
//取直接关联线体的产测登录号数据，去取获取工单
|
//var sql1 = "select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from " +
|
// "MESLINE_LIKE_CCLOGIN_WORKS A1" +
|
// " join MES_LINE b on A1.LOGINID=b.ID" +
|
// " left join WOMDAA C on C.DAA015=B.LINE_NO" +
|
// " left join mes_items s on s.id = C.daa002" +
|
// " where WORKSNO='" + id + "' and DAA018='已开工' order by DAA016 asc";
|
//var sql1 = "select DAA001 tickeNo, s.ITEM_NO itemNo, s.ITEM_NAME itemName, s.ITEM_MODEL itemModel, DAA008 plannedNum\r\nfrom WOMDAA C\r\n left join mes_items s on s.id = C.daa002\r\nleft join womcaa A on A.caa001=C.daa021\r\nwhere C.DAA015 = '" + LineId + "'\r\n and DAA018 = '已开工' and A.CAA023='2'\r\norder by DAA016 asc";
|
var sql1 = "select DAA001 tickeNo, s.ITEM_NO itemNo, s.ITEM_NAME itemName, s.ITEM_MODEL itemModel, DAA008 plannedNum\r\nfrom WOMDAA C\r\n left join mes_items s on s.id = C.daa002\r\nleft join womcaa A on A.caa001=C.daa021\r\nleft join MESPROJECT_LIKE_CCLOGIN pr1 on A.PROJECT=pr1.PROJECT_ID\r\nleft join MESLINE_LIKE_CCLOGIN_WORKS2 pr2 on pr1.id=pr2.LOGINID\r\nwhere C.DAA015 = '" + LineId + "' and pr2.WORKSNO='"+ LoginId + "'\r\n and DAA018 = '已开工' and A.CAA023='2'\r\norder by DAA016 asc";
|
return SQLHelper.ExecuteQuery(sql1);
|
}
|
|
|
/// <summary>
|
/// 记录产测过站信息
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
//public bool AddAsnInfo(AsnInfo asnInfo)
|
//{
|
// OracleSQLHelper SQLHelper = new();
|
// //记录产测过站信息
|
// var sql1 = @"INSERT INTO MES_SN_STATUS_DETAILS (ID,TICKET_NO,SN_NO,WORKSTATION_NO,SN_STATE,LOGIN_ID) VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,'" + asnInfo.tickeNo+ "','"+ asnInfo.snNo+ "','"+ asnInfo.workstationNo+ "','"+ asnInfo.snState+ "','"+asnInfo.loginId+"')";
|
// bool result = true;
|
// int i= SQLHelper.ExecuteNonQuery(sql1);
|
// //主表信息插入成功，继续插入项目
|
// if (i > 0) {
|
// //判断项目是否为空，为空不插入
|
// if (asnInfo.failItems.Count>0)
|
// {
|
// //插入逻辑
|
|
// }
|
// else
|
// {
|
// result = true;
|
// }
|
|
|
// } else { result = false; }
|
|
// return result;
|
//}
|
public bool AddAsnInfo(AsnInfo asnInfo)
|
{
|
var sqlHelper = new OracleSQLHelper();
|
bool result = true;
|
|
var (connection, transaction) = sqlHelper.BeginTransaction();
|
|
try
|
{
|
// 1. 主表插入
|
var sql1 = $@"
|
INSERT INTO MES_SN_STATUS_DETAILS
|
(ID, TICKET_NO, SN_NO, WORKSTATION_NO, SN_STATE, LOGIN_ID,TURNOVER_CODE)
|
VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,
|
'{asnInfo.tickeNo}',
|
'{asnInfo.snNo}',
|
'{asnInfo.workstationNo}',
|
'{asnInfo.snState}',
|
'{asnInfo.loginId}','{asnInfo.turnoverCode}')";
|
int i = sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql1);
|
|
// 2. 失败项插入
|
if (i > 0 && asnInfo.failItems?.Count > 0)
|
{
|
foreach (var item in asnInfo.failItems)
|
{
|
var sql2 = $@"
|
INSERT INTO CC_FAIL_ITEMS
|
(ID, MID, DEFECT_ISSUE, ROOT_CAUSE, REPAIR_METHOD, MATERIAL_HANDLING, CREATE_DATE, CREATED_BY)
|
VALUES (SEQ_CC_FAIL_ITEMS.NEXTVAL,
|
SEQ_MES_SN_STATUS_DETAILS_ID.CURRVAL,
|
'{item.DefectIssue}',
|
'{item.RootCause}',
|
'{item.RepairMethod}',
|
'{item.MaterialHandling}',
|
SYSDATE,
|
'{asnInfo.loginId}')";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
|
}
|
}
|
|
// 3. 配件绑定/解绑 & 日志记录
|
if (asnInfo.accessoryList?.Count > 0)
|
{
|
foreach (var accessory in asnInfo.accessoryList)
|
{
|
var now = "SYSDATE";
|
var user = asnInfo.loginId;
|
var snNo = asnInfo.snNo;
|
var partSn = accessory.accessorySn;
|
var partName = accessory.accessoryName;
|
|
if (accessory.bindingState == "0")
|
{
|
// ====== 校验：同一个配件SN是否已绑定 ======
|
var checkSql = $@"
|
SELECT COUNT(1) FROM MES_SN_PART_BINDING
|
WHERE PART_SN_NO = '{partSn}'";
|
var exists = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, checkSql));
|
if (exists > 0)
|
{
|
throw new Exception($"配件SN[{partSn}]已绑定，请先解绑再绑定！");
|
}
|
|
// 绑定
|
var sqlBind = $@"
|
INSERT INTO MES_SN_PART_BINDING
|
(ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER)
|
VALUES (SEQ_MES_SN_PART_BINDING.NEXTVAL,
|
'{snNo}',
|
'{partSn}',
|
'{partName}',
|
{now},
|
'{user}')";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlBind);
|
|
// 日志
|
var sqlLog = $@"
|
INSERT INTO MES_SN_PART_BINDING_LOG
|
(ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER, OP_TYPE)
|
VALUES (SEQ_MES_SN_PART_BINDING_LOG.NEXTVAL,
|
'{snNo}',
|
'{partSn}',
|
'{partName}',
|
{now},
|
'{user}',
|
0)";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlLog);
|
}
|
else if (accessory.bindingState == "1")
|
{
|
// 解绑
|
var sqlUnbind = $@"
|
DELETE FROM MES_SN_PART_BINDING
|
WHERE SN_NO = '{snNo}'
|
AND PART_SN_NO = '{partSn}'";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlUnbind);
|
|
// 日志
|
var sqlLog = $@"
|
INSERT INTO MES_SN_PART_BINDING_LOG
|
(ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER, OP_TYPE)
|
VALUES (SEQ_MES_SN_PART_BINDING_LOG.NEXTVAL,
|
'{snNo}',
|
'{partSn}',
|
'{partName}',
|
{now},
|
'{user}',
|
1)";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlLog);
|
}
|
}
|
}
|
|
transaction.Commit();
|
}
|
catch (Exception)
|
{
|
transaction.Rollback();
|
result = false;
|
throw;
|
}
|
finally
|
{
|
sqlHelper.CloseConnection(connection);
|
}
|
|
return result;
|
}
|
|
|
|
/// <summary>
|
/// 设置中箱码（绑定/解绑 SN）
|
/// </summary>
|
/// <param name="mesMiddleBox">中箱码及SN数据</param>
|
/// <returns>返回执行状态</returns>
|
public string[] SetMediumBoxCode(MesMiddleBox mesMiddleBox)
|
{
|
var sqlHelper = new OracleSQLHelper();
|
string[] msg = new string[2];
|
msg[0] = "true";
|
|
var (connection, transaction) = sqlHelper.BeginTransaction();
|
|
try
|
{
|
if (mesMiddleBox.type.ToUpper() == "A") // 绑定
|
{
|
if (mesMiddleBox.SnList.Count > 0)
|
{
|
string sql1 = $"select count(*) count from MES_MIDDLE_BOX where MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
|
DataTable data = sqlHelper.ExecuteQuery(sql1);
|
|
if (data.Rows[0]["COUNT"].ToString() != "0")
|
{
|
msg[0] = "false";
|
msg[1] = "该中箱码已经绑定SN数据，如需再次绑定请先解绑！中箱码：" + mesMiddleBox.MediumBoxCode;
|
transaction.Rollback();
|
return msg;
|
}
|
|
foreach (var item in mesMiddleBox.SnList)
|
{
|
string sqlInsert = $@"
|
INSERT INTO MES_MIDDLE_BOX (
|
ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY
|
) VALUES (
|
SEQ_MES_MIDDLE_BOX_ID.NEXTVAL,
|
'{mesMiddleBox.MediumBoxCode}',
|
'{mesMiddleBox.LoginId}',
|
'{item.SnNo}',
|
'{item.TicketNo}',
|
SYSDATE,
|
'{mesMiddleBox.LoginId}'
|
)";
|
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlInsert);
|
|
// 日志记录插入：操作类型 0（绑定）
|
string logInsert = $@"
|
INSERT INTO MES_MIDDLE_BOX_LOG (
|
ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY, OPERATION_TYPE
|
) VALUES (
|
MES_MIDDLE_BOX_LOG_ID.NEXTVAL,
|
'{mesMiddleBox.MediumBoxCode}',
|
'{mesMiddleBox.LoginId}',
|
'{item.SnNo}',
|
'{item.TicketNo}',
|
SYSDATE,
|
'{mesMiddleBox.LoginId}',
|
'0'
|
)";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
|
}
|
}
|
else
|
{
|
msg[0] = "false";
|
msg[1] = "SN明细为空，无法绑定！";
|
}
|
}
|
else if (mesMiddleBox.type.ToUpper() == "F") // 解绑
|
{
|
// 查询要解绑的记录
|
string sqlSelect = $@"
|
SELECT * FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
|
|
DataTable dt = sqlHelper.ExecuteQuery(sqlSelect);
|
|
foreach (DataRow row in dt.Rows)
|
{
|
// 插入解绑日志：操作类型 1（解绑）
|
string logInsert = $@"
|
INSERT INTO MES_MIDDLE_BOX_LOG (
|
ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY, OPERATION_TYPE
|
) VALUES (
|
MES_MIDDLE_BOX_LOG_ID.NEXTVAL,
|
'{row["MEDIUMBOXCODE"]}',
|
'{row["LOGIN_ID"]}',
|
'{row["SN_NO"]}',
|
'{row["TICKET_NO"]}',
|
SYSDATE,
|
'{mesMiddleBox.LoginId}',
|
'1'
|
)";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
|
}
|
|
// 执行解绑（删除）
|
string sqlDelete = $@"DELETE FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlDelete);
|
}
|
else
|
{
|
msg[0] = "false";
|
msg[1] = "无效的操作类型！";
|
}
|
|
transaction.Commit();
|
}
|
catch (Exception ex)
|
{
|
transaction.Rollback();
|
msg[0] = "false";
|
msg[1] = ex.Message;
|
throw; // 建议记录日志
|
}
|
finally
|
{
|
sqlHelper.CloseConnection(connection);
|
}
|
|
return msg;
|
}
|
|
|
|
/// <summary>
|
/// 设置栈板码（绑定/解绑 SN）
|
/// </summary>
|
/// <param name="mesPalletBinding">绑定参数</param>
|
/// <returns>返回绑定结果</returns>
|
public string[] SetStackCode(MesPalletBinding mesPalletBinding)
|
{
|
var sqlHelper = new OracleSQLHelper();
|
string[] msg = new string[2];
|
msg[0] = "true";
|
|
var (connection, transaction) = sqlHelper.BeginTransaction();
|
|
try
|
{
|
// 绑定
|
if (mesPalletBinding.Type.ToUpper() == "A")
|
{
|
if (mesPalletBinding.SnList.Count > 0)
|
{
|
string sql1 = $"SELECT COUNT(*) COUNT FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
|
DataTable data = sqlHelper.ExecuteQuery(sql1);
|
|
if (data.Rows[0]["COUNT"].ToString() != "0")
|
{
|
msg[0] = "false";
|
msg[1] = "该栈板码已经绑定SN数据，如需再次绑定请先解绑！栈板码：" + mesPalletBinding.StackCode;
|
transaction.Rollback();
|
return msg;
|
}
|
|
foreach (var item in mesPalletBinding.SnList)
|
{
|
string sqlInsert = $@"
|
INSERT INTO MES_PALLET_BINDING (
|
ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY
|
) VALUES (
|
SEQ_MES_PALLET_BINDING_ID.NEXTVAL,
|
'{mesPalletBinding.StackCode}',
|
'{mesPalletBinding.LoginId}',
|
'{item.SnNo}',
|
'{item.TicketNo}',
|
'{item.MediumBoxCode}',
|
SYSDATE,
|
'{mesPalletBinding.LoginId}'
|
)";
|
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlInsert);
|
|
// 插入绑定日志（操作类型 0）
|
string logInsert = $@"
|
INSERT INTO MES_PALLET_BINDING_LOG (
|
ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, OPERATION_TYPE
|
) VALUES (
|
MES_PALLET_BINDING_LOG_ID.NEXTVAL,
|
'{mesPalletBinding.StackCode}',
|
'{mesPalletBinding.LoginId}',
|
'{item.SnNo}',
|
'{item.TicketNo}',
|
'{item.MediumBoxCode}',
|
SYSDATE,
|
'{mesPalletBinding.LoginId}',
|
'0'
|
)";
|
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
|
}
|
}
|
else
|
{
|
msg[0] = "false";
|
msg[1] = "SN明细为空，请确认！";
|
}
|
}
|
// 解绑
|
else if (mesPalletBinding.Type.ToUpper() == "F")
|
{
|
string sql1 = $"SELECT COUNT(*) COUNT FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}' and IS_INBOUND=1";
|
DataTable data = sqlHelper.ExecuteQuery(sql1);
|
|
if (data.Rows[0]["COUNT"].ToString() != "0")
|
{
|
msg[0] = "false";
|
msg[1] = "该栈板码已经在MES入库，不允许解绑！栈板码：" + mesPalletBinding.StackCode;
|
transaction.Rollback();
|
return msg;
|
}
|
|
// 先查询绑定的记录
|
string sqlSelect = $"SELECT * FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
|
DataTable dt = sqlHelper.ExecuteQuery( sqlSelect);
|
|
foreach (DataRow row in dt.Rows)
|
{
|
// 插入解绑日志（操作类型 1）
|
string logInsert = $@"
|
INSERT INTO MES_PALLET_BINDING_LOG (
|
ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, OPERATION_TYPE
|
) VALUES (
|
MES_PALLET_BINDING_LOG_ID.NEXTVAL,
|
'{row["STACKCODE"]}',
|
'{row["LOGIN_ID"]}',
|
'{row["SN_NO"]}',
|
'{row["TICKET_NO"]}',
|
'{row["MEDIUMBOXCODE"]}',
|
SYSDATE,
|
'{mesPalletBinding.LoginId}',
|
'1'
|
)";
|
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
|
}
|
|
// 删除绑定记录
|
string sqlDelete = $"DELETE FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
|
sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlDelete);
|
}
|
|
// 提交事务
|
transaction.Commit();
|
}
|
catch (Exception ex)
|
{
|
transaction.Rollback();
|
msg[0] = "false";
|
msg[1] = ex.Message;
|
throw; // 建议记录日志
|
}
|
finally
|
{
|
sqlHelper.CloseConnection(connection);
|
}
|
|
return msg;
|
}
|
|
|
/// <summary>
|
/// 处理解绑 / 撤销解绑
|
/// </summary>
|
public bool ProcessSn(UnbindAsnInfoDto dto)
|
{
|
if (dto == null || dto.snList == null || dto.snList.Count == 0)
|
throw new ArgumentException("输入参数无效");
|
|
var sqlHelper = new OracleSQLHelper();
|
bool result = true;
|
var (connection, transaction) = sqlHelper.BeginTransaction();
|
|
try
|
{
|
foreach (var sn in dto.snList)
|
{
|
if (dto.ActionType == 0) // 解绑
|
{
|
string sql = @"
|
UPDATE MES_SN_STATUS_DETAILS
|
SET IS_UNBIND = 1,
|
UNBIND_DATE = SYSDATE,
|
UNBIND_USER = :LoginId
|
WHERE TICKET_NO = :TicketNo
|
AND SN_NO = :SnNo
|
AND NVL(IS_UNBIND, 0) = 0";
|
|
var parameters = new[]
|
{
|
new OracleParameter(":LoginId", dto.loginId),
|
new OracleParameter(":TicketNo", sn.tickeNo),
|
new OracleParameter(":SnNo", sn.snNo)
|
};
|
|
int rows = sqlHelper.ExecuteNonQueryWithTransaction(sql, parameters, connection, transaction);
|
if (rows == 0)
|
{
|
throw new Exception($"解绑失败: 工单[{sn.tickeNo}], SN[{sn.snNo}] 未找到可解绑记录或已解绑！");
|
}
|
}
|
else if (dto.ActionType == 1) // 撤销解绑
|
{
|
string sql = @"
|
UPDATE MES_SN_STATUS_DETAILS
|
SET IS_UNBIND = 0,
|
UNBIND_DATE = NULL,
|
UNBIND_USER = NULL
|
WHERE TICKET_NO = :TicketNo
|
AND SN_NO = :SnNo
|
AND NVL(IS_UNBIND, 0) = 1";
|
|
var parameters = new[]
|
{
|
new OracleParameter(":TicketNo", sn.tickeNo),
|
new OracleParameter(":SnNo", sn.snNo)
|
};
|
|
int rows = sqlHelper.ExecuteNonQueryWithTransaction(sql, parameters, connection, transaction);
|
if (rows == 0)
|
{
|
throw new Exception($"撤销解绑失败: 工单[{sn.tickeNo}], SN[{sn.snNo}] 未找到已解绑记录！");
|
}
|
}
|
else
|
{
|
throw new Exception("无效的操作类型！只能是 0=解绑 或 1=撤销解绑");
|
}
|
}
|
|
transaction.Commit();
|
}
|
catch (Exception ex)
|
{
|
transaction.Rollback();
|
result = false;
|
// 建议这里写日志 ex.Message
|
throw;
|
}
|
finally
|
{
|
connection.Close();
|
}
|
|
return result;
|
}
|
|
}
|
}
|
