对添加巡检单物料编码进行去重

fcx

4 天以前 a619299816f2f632c7c6ba3a1bb869e2c212797d

 StandardPda/MES.Service/service/QC/LljService.cs

@@ -6,6 +6,7 @@
using MES.Service.util;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Oracle.ManagedDataAccess.Client;
using SqlSugar;
using System.Data;
using System.Security.Cryptography;
@@ -345,6 +346,8 @@
                DbType.String, ParameterDirection.Input),
            new("PI_REMARK", sysSubmit.PI_REMARK,
                DbType.String, ParameterDirection.Input),
               new("PI_SAMPLE_QTY", sysSubmit.PI_SAMPLE_QTY,
                DbType.String, ParameterDirection.Input),
            outputResult,
            outputMessage
        };
@@ -353,7 +356,51 @@

        // 使用 SqlSugar 执行存储过程
        db.Ado.ExecuteCommand(
            "BEGIN PRC_MES_IQC_QC_SYSSUBMIT(:PI_BILLNO,:PI_USER,:PI_SAMPLE_STATUS,:PI_SAMPLE_TYPE,:PI_SAMPLE_REASON,:PI_INSPECT_TYPE,:PI_SAMPLE_PREPARATION,:PI_URGENCY,:PI_FIRMWAREVERSION,:PI_HARDWAREVERSION,:PI_REMARK, :o_Result, :o_Msg); END;",
            "BEGIN PRC_MES_IQC_QC_SYSSUBMIT(:PI_BILLNO,:PI_USER,:PI_SAMPLE_STATUS,:PI_SAMPLE_TYPE,:PI_SAMPLE_REASON,:PI_INSPECT_TYPE,:PI_SAMPLE_PREPARATION,:PI_URGENCY,:PI_FIRMWAREVERSION,:PI_HARDWAREVERSION,:PI_REMARK,:PI_SAMPLE_QTY, :o_Result, :o_Msg); END;",
            parameters.ToArray());

        // 获取输出参数的值
        var resultValue = outputResult.Value?.ToString();
        var messageValue = outputMessage.Value?.ToString();
        var msg = new string[2];
        msg[0] = resultValue;
        msg[1] = messageValue;
        return msg;
    }


    public string[] SaveYCCZSubmit(YCCZSubmit sysSubmit)
    {
        // 定义输出参数
        var outputResult = new SugarParameter("o_Result", null,
            DbType.Int32, ParameterDirection.Output,
            4000);

        var outputMessage = new SugarParameter("o_Msg", null,
            DbType.String,
            ParameterDirection.Output, 4000);

        // 定义输入参数
        var parameters = new List<SugarParameter>
        {
            new("PI_BILLNO", sysSubmit.PI_BILLNO,
                DbType.String, ParameterDirection.Input),
            new("PI_USER", sysSubmit.PI_USER,
                DbType.String, ParameterDirection.Input),
            new("PTOTAL_QTY", sysSubmit.PI_QTY,
                DbType.String, ParameterDirection.Input),
            new("PNG_QTY", sysSubmit.PI_BADQTY,
                DbType.String, ParameterDirection.Input),

            outputResult,
            outputMessage
        };

        var db = SqlSugarHelper.GetInstance();

        // 使用 SqlSugar 执行存储过程
        db.Ado.ExecuteCommand(
            "BEGIN PRC_MES_IQC_QC_YCCZ(:PI_BILLNO,:PI_USER,:PTOTAL_QTY,:PNG_QTY,:o_Result, :o_Msg); END;",
            parameters.ToArray());

        // 获取输出参数的值
@@ -1458,29 +1505,97 @@
        return SQLHelper.ExecuteQuery(sql1);
    }

    //public void saveYzxBDlist(string id, DataTable DB)
    //{
    //    OracleSQLHelper SQLHelper = new();
    //    //先把结果清空
    //    var sql1 =
    //        string.Format(
    //            @"update MES_IQC_CONSISTENCY_ck set ISCHACK=0 where PID='" +
    //            id + "' ");
    //    SQLHelper.ExecuteQuery(sql1);
    //    var rowId = "";
    //    for (var i = 0; i < DB.Rows.Count; i++)
    //        rowId += "'" + DB.Rows[i]["id"] + "',";
    //    rowId = rowId.Substring(0, rowId.Length - 1);


    //    //修改结果
    //    var sql2 =
    //        string.Format(
    //            @"update MES_IQC_CONSISTENCY_ck set ISCHACK=1 where PID='" +
    //            id + "'and id in(" + rowId + ") ");
    //    SQLHelper.ExecuteQuery(sql2);
    //}

    public void saveYzxBDlist(string id, DataTable DB)
    {
        OracleSQLHelper SQLHelper = new();
        //先把结果清空
        var sql1 =
            string.Format(
                @"update MES_IQC_CONSISTENCY_ck set ISCHACK=0 where PID='" +
                id + "' ");
        SQLHelper.ExecuteQuery(sql1);
        var rowId = "";
        for (var i = 0; i < DB.Rows.Count; i++)
            rowId += "'" + DB.Rows[i]["id"] + "',";
        rowId = rowId.Substring(0, rowId.Length - 1);
        var sqlHelper = new OracleSQLHelper();

        using (var connection = new OracleConnection(AppsettingsUtility.Settings.DataBaseConn))
        {
            connection.Open();
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    // 1. 清空 ISCHACK
                    var sql1 = @"UPDATE MES_IQC_CONSISTENCY_ck 
                             SET ISCHACK = 0 
                             WHERE PID = :pid";

        //修改结果
        var sql2 =
            string.Format(
                @"update MES_IQC_CONSISTENCY_ck set ISCHACK=1 where PID='" +
                id + "'and id in(" + rowId + ") ");
        SQLHelper.ExecuteQuery(sql2);
                    using (var cmd1 = new OracleCommand(sql1, connection))
                    {
                        cmd1.Transaction = transaction;
                        cmd1.Parameters.Add(new OracleParameter(":pid", id));
                        cmd1.ExecuteNonQuery();
                    }

                    // 2. 如果 DataTable 里有要更新的 id
                    if (DB != null && DB.Rows.Count > 0)
                    {
                        // 拼接 in 条件（参数化）
                        var inParams = new List<string>();
                        var parameters = new List<OracleParameter>
                    {
                        new OracleParameter(":pid", id)
                    };

                        for (int i = 0; i < DB.Rows.Count; i++)
                        {
                            string paramName = ":id" + i;
                            inParams.Add(paramName);
                            parameters.Add(new OracleParameter(paramName, DB.Rows[i]["id"]));
                        }

                        var sql2 = $@"
                        UPDATE MES_IQC_CONSISTENCY_ck 
                           SET ISCHACK = 1 
                         WHERE PID = :pid 
                           AND id IN ({string.Join(",", inParams)})";

                        using (var cmd2 = new OracleCommand(sql2, connection))
                        {
                            cmd2.Transaction = transaction;
                            cmd2.Parameters.AddRange(parameters.ToArray());
                            cmd2.ExecuteNonQuery();
                        }
                    }

                    // 全部成功，提交
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 出错回滚
                    transaction.Rollback();
                    throw new Exception("执行 saveYzxBDlist 出错: " + ex.Message, ex);
                }
            }
        }
    }


    public string[] YzxImgVerify(string id)
    {
        var msgStr = new string[2];