产测解绑sn代码提交

zjh

2025-08-23 c8ec9ec6d9d149eaac6ad78bedd980c3be1c3b5f

 StandardPda/MES.Service/service/Warehouse/WomdaaManager.cs

@@ -3,6 +3,7 @@
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;
@@ -19,7 +20,7 @@
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetTickeInfo(string id)
        public DataTable GetTickeInfo(string LineId,string LoginId)
        {
            OracleSQLHelper SQLHelper = new();
            //查工单信息
@@ -39,12 +40,14 @@


            //取直接关联线体的产测登录号数据，去取获取工单
            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 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);
        }

@@ -83,50 +86,123 @@
        {
            var sqlHelper = new OracleSQLHelper();
            bool result = true;
            //

            var (connection, transaction) = sqlHelper.BeginTransaction();

            try
            {
                // 主表插入
                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}')";

                // 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}')";

                        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 ex)
            catch (Exception)
            {
                transaction.Rollback();
                result = false;
                throw; // 建议记录日志
                throw;
            }
            finally
            {
@@ -135,6 +211,8 @@

            return result;
        }



        /// <summary>
        /// 设置中箱码（绑定/解绑 SN）
@@ -388,5 +466,91 @@
        }


        /// <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;
        }

    }
}