using Masuit.Tools;
|
using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.Modes;
|
using MES.Service.service.BasicData;
|
using Newtonsoft.Json;
|
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 WorkdaaManager
|
{
|
/// <summary>
|
/// 获取产测登录号对应工单信息
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public string GetTickeInfo(string lineNo)
|
{
|
OracleSQLHelper SQLHelper = new();
|
|
try
|
{
|
// 1️⃣ 调用远程接口
|
var url = "http://no2api.dream-maker.com/line/getLineFinishCount";
|
var requestObj = new { lineNo = lineNo };// 要发送的参数对象
|
string requestJson = JsonConvert.SerializeObject(requestObj); // 序列化成 JSON 字符串
|
|
using (var client = new HttpClient()) // 创建 HttpClient 对象
|
{
|
var content = new StringContent(requestJson, Encoding.UTF8, "application/json");
|
var response = client.PostAsync(url, content).Result;// 发送 POST 请求（同步方式）
|
response.EnsureSuccessStatusCode(); // 请求失败抛出异常
|
|
string responseJson = response.Content.ReadAsStringAsync().Result; // 获取接口返回的 JSON 字符串
|
|
// 2️⃣ 解析返回 JSON
|
var apiResult = JsonConvert.DeserializeObject<ApiResult>(responseJson);
|
|
if (apiResult != null && apiResult.code == 0 && apiResult.data != null)
|
{
|
//string lineNo = apiResult.data.lineNo;
|
int quantity = apiResult.data.qualifiedQuantity; // 合格数量
|
|
|
//2.1新表
|
|
//2.2 查询已有数量
|
string selectSql = $"select NVL(sum(Quantity),0) from mes_Report_Subtable where Production_line = '{lineNo}'and TRUNC(CREATE_DATE) = TRUNC(SYSDATE)";
|
int existedQty = Convert.ToInt32(SQLHelper.ExecuteScalar(selectSql));
|
|
//2.3 计算需要插入的数量
|
int insertQty = quantity - existedQty;
|
|
//2.4 插入新记录
|
if (insertQty >= 0)
|
{
|
string insertSql1 = $@"INSERT INTO mes_Report_Subtable (ID, CREATE_DATE, Production_line, Quantity)
|
VALUES (SEQ_MES_REPORT_Subtable_ID.NEXTVAL, SYSDATE, '{lineNo}', {insertQty})";
|
SQLHelper.ExecuteQuery(insertSql1);
|
}
|
|
|
|
|
// 3️⃣ 检查数据库当天是否已有数据
|
var checkSql = $"SELECT COUNT(1) FROM mes_Report " +
|
$"WHERE TRUNC(CREATE_DATE) = TRUNC(SYSDATE) AND Production_line = '{lineNo}'";
|
|
var dt = SQLHelper.ExecuteQuery(checkSql); // 执行 SQL 获取 DataTable
|
int count = 0;
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
count = Convert.ToInt32(dt.Rows[0][0]);// 获取数量
|
}
|
|
// 4️⃣ 如果当天没有数据，则插入；如果有当天数据，则更新
|
if (count == 0)
|
{
|
// 插入新数据
|
var insertSql = $"INSERT INTO mes_Report (ID, CREATE_DATE, Production_line, Quantity) " +
|
$"VALUES (SEQ_MES_REPORT_ID.NEXTVAL,SYSDATE, '{lineNo}', {quantity})";
|
SQLHelper.ExecuteQuery(insertSql);
|
}
|
else
|
{
|
// 更新当天的数据 当天已经有这个线体 → 更新该线体的数据
|
var updateSql = $"UPDATE mes_Report " +
|
$"SET Production_line = '{lineNo}', Quantity = {quantity} , CREATE_DATE = SYSDATE " +
|
$"WHERE TRUNC(CREATE_DATE) = TRUNC(SYSDATE) and Production_line = '{lineNo}' ";
|
SQLHelper.ExecuteQuery(updateSql);
|
}
|
|
// 5️⃣ 返回 OK
|
return "OK";
|
}
|
else
|
{
|
throw new Exception("接口返回错误或数据为空");
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
throw new Exception("调用接口或写入数据库失败：" + ex.Message, ex);
|
}
|
}
|
|
// ---------- 辅助类用于解析 JSON ----------
|
public class ApiResult
|
{
|
public int code { get; set; }
|
public string msg { get; set; }
|
public ApiData data { get; set; }
|
}
|
|
public class ApiData
|
{
|
public string lineNo { get; set; }
|
public int qualifiedQuantity { get; set; }
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/// <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;
|
//}
|
|
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,LINE_NO)
|
// VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,
|
// :ticketNo,
|
// :snNo,
|
// :workstationNo,
|
// :snState,
|
// :loginId,
|
// :turnoverCode,select nvl(DAA015,'') from WOMDAA where DAA001 = ':ticketNo' and ROWNUM=1)";
|
|
// 1. 主表插入
|
var sql1 = @"
|
INSERT INTO MES_SN_STATUS_DETAILS
|
(ID, TICKET_NO, SN_NO, WORKSTATION_NO, SN_STATE, LOGIN_ID, TURNOVER_CODE, LINE_NO)
|
SELECT SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,
|
:ticketNo,
|
:snNo,
|
:workstationNo,
|
:snState,
|
:loginId,
|
:turnoverCode,
|
NVL(DAA015, '')
|
FROM WOMDAA
|
WHERE DAA001 = :ticketNo
|
AND ROWNUM = 1";
|
var parameters1 = new[]
|
{
|
new OracleParameter("ticketNo", asnInfo.tickeNo ?? (object)DBNull.Value),
|
new OracleParameter("snNo", asnInfo.snNo ?? (object)DBNull.Value),
|
new OracleParameter("workstationNo", asnInfo.workstationNo ?? (object)DBNull.Value),
|
new OracleParameter("snState", asnInfo.snState ?? (object)DBNull.Value),
|
new OracleParameter("loginId", asnInfo.loginId ?? (object)DBNull.Value),
|
new OracleParameter("turnoverCode", asnInfo.turnoverCode ?? (object)DBNull.Value)
|
};
|
|
int i = sqlHelper.ExecuteNonQueryWithTransaction(sql1, parameters1, connection, transaction);
|
|
// 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,
|
:defectIssue,
|
:rootCause,
|
:repairMethod,
|
:materialHandling,
|
SYSDATE,
|
:loginId)";
|
|
var parameters2 = new[]
|
{
|
new OracleParameter("defectIssue", item.DefectIssue ?? (object)DBNull.Value),
|
new OracleParameter("rootCause", item.RootCause ?? (object)DBNull.Value),
|
new OracleParameter("repairMethod", item.RepairMethod ?? (object)DBNull.Value),
|
new OracleParameter("materialHandling", item.MaterialHandling ?? (object)DBNull.Value),
|
new OracleParameter("loginId", asnInfo.loginId ?? (object)DBNull.Value)
|
};
|
|
sqlHelper.ExecuteNonQueryWithTransaction(sql2, parameters2, connection, transaction);
|
}
|
}
|
|
// 3. 配件绑定/解绑 & 日志记录
|
if (asnInfo.accessoryList?.Count > 0)
|
{
|
foreach (var accessory in asnInfo.accessoryList)
|
{
|
var snNo = asnInfo.snNo;
|
var partSn = accessory.accessorySn;
|
var partName = accessory.accessoryName;
|
var user = asnInfo.loginId;
|
|
if (accessory.bindingState == "0") // 绑定
|
{
|
// 校验
|
var checkSql = "SELECT COUNT(1) FROM MES_SN_PART_BINDING WHERE PART_SN_NO = :partSn";
|
var checkParams = new[] { new OracleParameter("partSn", partSn ?? (object)DBNull.Value) };
|
var exists = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, checkSql, checkParams));
|
|
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,
|
SYSDATE,
|
:createUser)";
|
|
var bindParams = new[]
|
{
|
new OracleParameter("snNo", snNo ?? (object)DBNull.Value),
|
new OracleParameter("partSn", partSn ?? (object)DBNull.Value),
|
new OracleParameter("partName", partName ?? (object)DBNull.Value),
|
new OracleParameter("createUser", user ?? (object)DBNull.Value)
|
};
|
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlBind, bindParams, connection, transaction);
|
|
|
// 插入日志
|
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,
|
SYSDATE,
|
:createUser,
|
0)";
|
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, bindParams, connection, transaction);
|
}
|
else if (accessory.bindingState == "1") // 解绑
|
{
|
// 删除绑定
|
var sqlUnbind = @"
|
DELETE FROM MES_SN_PART_BINDING
|
WHERE SN_NO = :snNo
|
AND PART_SN_NO = :partSn";
|
|
var unbindParams = new[]
|
{
|
new OracleParameter("snNo", snNo ?? (object)DBNull.Value),
|
new OracleParameter("partSn", partSn ?? (object)DBNull.Value)
|
};
|
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlUnbind, unbindParams, connection, transaction);
|
|
// 插入日志
|
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,
|
SYSDATE,
|
:Puser,
|
1)";
|
|
var logParams = new[]
|
{
|
new OracleParameter("snNo", snNo ?? (object)DBNull.Value),
|
new OracleParameter("partSn", partSn ?? (object)DBNull.Value),
|
new OracleParameter("partName", partName ?? (object)DBNull.Value),
|
new OracleParameter("Puser", user ?? (object)DBNull.Value)
|
};
|
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, logParams, connection, transaction);
|
}
|
}
|
}
|
|
transaction.Commit();
|
}
|
catch (Exception)
|
{
|
transaction.Rollback();
|
result = false;
|
throw;
|
}
|
finally
|
{
|
sqlHelper.CloseConnection(connection);
|
}
|
|
return result;
|
}
|
|
|
|
/// <summary>
|
/// 设置中箱码（绑定/解绑 SN）
|
/// </summary>
|
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)
|
{
|
// 检查是否已存在
|
var sqlCheck = "SELECT COUNT(*) FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = :code";
|
var checkParams = new[] { new OracleParameter(":code", mesMiddleBox.MediumBoxCode) };
|
var count = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, sqlCheck, checkParams));
|
|
if (count > 0)
|
{
|
msg[0] = "false";
|
msg[1] = $"该中箱码已经绑定SN数据，如需再次绑定请先解绑！中箱码：{mesMiddleBox.MediumBoxCode}";
|
transaction.Rollback();
|
return msg;
|
}
|
|
foreach (var item in mesMiddleBox.SnList)
|
{
|
// 插入绑定
|
var 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,
|
:code, :loginId, :snNo, :ticketNo, SYSDATE, :createdBy)";
|
var insertParams = new[]
|
{
|
new OracleParameter(":code", mesMiddleBox.MediumBoxCode),
|
new OracleParameter(":loginId", mesMiddleBox.LoginId),
|
new OracleParameter(":snNo", item.SnNo),
|
new OracleParameter(":ticketNo", item.TicketNo),
|
new OracleParameter(":createdBy", mesMiddleBox.LoginId)
|
};
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlInsert, insertParams, connection, transaction);
|
|
// 插入日志
|
var sqlLog = @"
|
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,
|
:code, :loginId, :snNo, :ticketNo, SYSDATE, :createdBy, 0)";
|
var logParams = new[]
|
{
|
new OracleParameter(":code", mesMiddleBox.MediumBoxCode),
|
new OracleParameter(":loginId", mesMiddleBox.LoginId),
|
new OracleParameter(":snNo", item.SnNo),
|
new OracleParameter(":ticketNo", item.TicketNo),
|
new OracleParameter(":createdBy", mesMiddleBox.LoginId)
|
};
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, logParams, connection, transaction);
|
}
|
}
|
else
|
{
|
msg[0] = "false";
|
msg[1] = "SN明细为空，无法绑定！";
|
}
|
}
|
else if (mesMiddleBox.type.ToUpper() == "F") // 解绑
|
{
|
var sqlSelect = "SELECT * FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = :code";
|
var selectParams = new[] { new OracleParameter(":code", mesMiddleBox.MediumBoxCode) };
|
var dt = sqlHelper.ExecuteDataTable(sqlSelect, CommandType.Text, selectParams);
|
|
foreach (DataRow row in dt.Rows)
|
{
|
var sqlLog = @"
|
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,
|
:code, :loginId, :snNo, :ticketNo, SYSDATE, :createdBy, 1)";
|
var logParams = new[]
|
{
|
new OracleParameter(":code", row["MEDIUMBOXCODE"]),
|
new OracleParameter(":loginId", row["LOGIN_ID"]),
|
new OracleParameter(":snNo", row["SN_NO"]),
|
new OracleParameter(":ticketNo", row["TICKET_NO"]),
|
new OracleParameter(":createdBy", mesMiddleBox.LoginId)
|
};
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, logParams, connection, transaction);
|
}
|
|
var sqlDelete = "DELETE FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = :code";
|
var delParams = new[] { new OracleParameter(":code", mesMiddleBox.MediumBoxCode) };
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlDelete, delParams, connection, transaction);
|
}
|
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>
|
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)
|
{
|
var sqlCheck = "SELECT COUNT(*) FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode";
|
var checkParams = new[] { new OracleParameter(":stackCode", mesPalletBinding.StackCode) };
|
var count = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, sqlCheck, checkParams));
|
|
if (count > 0)
|
{
|
msg[0] = "false";
|
msg[1] = $"该栈板码已经绑定SN数据，如需再次绑定请先解绑！栈板码：{mesPalletBinding.StackCode}";
|
transaction.Rollback();
|
return msg;
|
}
|
|
foreach (var item in mesPalletBinding.SnList)
|
{
|
var 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,
|
:stackCode, :loginId, :snNo, :ticketNo, :mediumBoxCode, SYSDATE, :createdBy)";
|
var insertParams = new[]
|
{
|
new OracleParameter(":stackCode", mesPalletBinding.StackCode),
|
new OracleParameter(":loginId", mesPalletBinding.LoginId),
|
new OracleParameter(":snNo", item.SnNo),
|
new OracleParameter(":ticketNo", item.TicketNo),
|
new OracleParameter(":mediumBoxCode", item.MediumBoxCode),
|
new OracleParameter(":createdBy", mesPalletBinding.LoginId)
|
};
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlInsert, insertParams, connection, transaction);
|
|
var sqlLog = @"
|
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,
|
:stackCode, :loginId, :snNo, :ticketNo, :mediumBoxCode, SYSDATE, :createdBy, 0)";
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, insertParams, connection, transaction);
|
}
|
}
|
else
|
{
|
msg[0] = "false";
|
msg[1] = "SN明细为空，请确认！";
|
}
|
}
|
else if (mesPalletBinding.Type.ToUpper() == "F") // 解绑
|
{
|
var sqlCheck = "SELECT COUNT(*) FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode AND IS_INBOUND = 1";
|
var checkParams = new[] { new OracleParameter(":stackCode", mesPalletBinding.StackCode) };
|
var count = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, sqlCheck, checkParams));
|
|
if (count > 0)
|
{
|
msg[0] = "false";
|
msg[1] = $"该栈板码已经在MES入库，不允许解绑！栈板码：{mesPalletBinding.StackCode}";
|
transaction.Rollback();
|
return msg;
|
}
|
|
var sqlSelect = "SELECT * FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode";
|
var selectParams = new[] { new OracleParameter(":stackCode", mesPalletBinding.StackCode) };
|
var dt = sqlHelper.ExecuteDataTable(sqlSelect, CommandType.Text, selectParams);
|
|
foreach (DataRow row in dt.Rows)
|
{
|
var sqlLog = @"
|
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,
|
:stackCode, :loginId, :snNo, :ticketNo, :mediumBoxCode, SYSDATE, :createdBy, 1)";
|
var logParams = new[]
|
{
|
new OracleParameter(":stackCode", row["STACKCODE"]),
|
new OracleParameter(":loginId", row["LOGIN_ID"]),
|
new OracleParameter(":snNo", row["SN_NO"]),
|
new OracleParameter(":ticketNo", row["TICKET_NO"]),
|
new OracleParameter(":mediumBoxCode", row["MEDIUMBOXCODE"]),
|
new OracleParameter(":createdBy", mesPalletBinding.LoginId)
|
};
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, logParams, connection, transaction);
|
}
|
|
var sqlDelete = "DELETE FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode";
|
var delParams = new[] { new OracleParameter(":stackCode", mesPalletBinding.StackCode) };
|
sqlHelper.ExecuteNonQueryWithTransaction(sqlDelete, delParams, connection, transaction);
|
}
|
|
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;
|
}
|
|
}
|
}
|
