Merge branch 'master' of http://git.gs-mes.com:8080/r/~tjx/NewPdaSqlServer

南骏 池

14 小时以前 8add357b725e95202edd5ef9809afd44518c2c82

 service/QC/OaApi.cs

@@ -1,7 +1,9 @@
// OaApiService.cs
// OaApiService.cs
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Net.Http;
using System.Security.Cryptography;
@@ -31,10 +33,11 @@
        _httpClient.Timeout = TimeSpan.FromSeconds(30);
    }

    public async Task<dynamic> SubmitIQCToOA(dynamic queryObj)
    public  dynamic SubmitIQCToOA(dynamic queryObj)
    {
        // 修改参数验证字段名
        //if (queryObj.userId.IsNullOrEmpty()) throw new Exception("用户id不允许为空");
        if (string.IsNullOrEmpty(queryObj.userId.ToString())) throw new Exception("用户id不允许为空");
        if (string.IsNullOrEmpty(queryObj.qcczdGuid.ToString())) throw new Exception("异常处置单id不能为空");
        //if (queryObj.qcczdGuid.IsNullOrEmpty()) throw new Exception("异常处置单id不能为空"); // 原参数名为 qcczdGuid

        try
@@ -44,12 +47,12 @@
            //var query = JObject.FromObject(queryObj);

            // 1. 注册获取凭证
            var registResult = await GetRegistAsync();
            var registResult =  GetRegistAsync();
            var secret = registResult["secrit"].ToString();
            var spk = registResult["spk"].ToString();

            // 2. 获取访问令牌
            var tokenResult = await ApplyTokenAsync(secret, spk);
            var tokenResult =  ApplyTokenAsync(secret, spk);
            var token = tokenResult["token"].ToString();

            // 3. 准备请求头（移除Content-Type设置）
@@ -62,14 +65,90 @@
            var requestData = BuildRequestData(queryObj);

            // 5. 发送请求
            var response = await PostFormAsync(WorkflowUrl, requestData);
            return JObject.Parse(response);
            var response = PostForm(WorkflowUrl, requestData);

            // 新增日志记录（在返回响应前）
            using (var conn = new SqlConnection(DbHelperSQL.strConn))
            {
                using (var cmd = new SqlCommand("prc_log_create", conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter[] logParams = 
                        {
                            new("@edtUserGuid", SqlDbType.UniqueIdentifier) { Value = Guid.Parse(queryObj.userId.ToString()) },
                            new("@abtGuid", SqlDbType.UniqueIdentifier) { Value = Guid.Parse(queryObj.qcczdGuid.ToString()) },
                            new("@abtTable", SqlDbType.NVarChar, 40) { Value = "Mes_QC_Exceptional" },
                            new("@detail", SqlDbType.NVarChar, 2500) { Value = "提交IQC到OA系统" },
                            new("@hNo", SqlDbType.NVarChar, 100) { Value = "" },
                            new("@SendJson", SqlDbType.NVarChar) { Value = JObject.FromObject(requestData).ToString() },
                            new("@RtnJson", SqlDbType.NVarChar) { Value = response.Content.ToString() }
                        };
                        
                        foreach (var param in logParams)
                            cmd.Parameters.Add(param);
                            
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception logEx)
                    {
                        // 日志记录失败不中断主流程
                        Console.WriteLine($"日志记录失败: {logEx.Message}");
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            var result = JObject.Parse(response.Content.ToString());

            // 新增状态更新（当OA返回成功时）
            if (result.code?.ToString() == "SUCCESS")
            {
                var updateSql = @"UPDATE Mes_QC_Exceptional
SET checkStatus = 1,
    checkDate   = GETDATE(),
    checkBy     = (select top 1 u.[ACCOUNT] from [dbo].[SYS_USER] u where u.guid = @edtUserCode)
WHERE GUID = @inOrderGuid";
                
                using (var conn = new SqlConnection(DbHelperSQL.strConn))
                {
                    using (var cmd = new SqlCommand(updateSql, conn))
                    {
                        try
                        {
                            conn.Open();
                            //cmd.Parameters.AddWithValue("@dt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                            cmd.Parameters.AddWithValue("@edtUserCode", queryObj.userId?.ToString());
                            cmd.Parameters.AddWithValue("@inOrderGuid", queryObj.qcczdGuid?.ToString());
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception updateEx)
                        {
                            Console.WriteLine($"状态更新失败: {updateEx.Message}");
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }

            return result;  // 直接解析为JObject

        }
        catch (Exception ex)
        {
            dynamic error = new ExpandoObject();
            error.Error = true;
            error.Message = ex.Message;
            error.code = "SYSTEM_INNER_ERROR";  // 新增标准错误码
            error.errMsg = ex.Message;         // 修正字段名匹配规范
            error.data = new JObject();
            error.reqFailMsg = new JObject();
            return error;
        }
    }
@@ -106,20 +185,7 @@
    private JArray BuildMainData(dynamic queryObj)
    {
        // 使用参数化查询防止SQL注入
        const string mainSql = @"
SELECT TOP 1 
    C.item_no   AS wlbm,
    C.item_name AS jzmcwlmc,
    D.supp_name AS khgys,
    B.bill_no   AS dhdhmes,
    (SELECT  STRING_AGG(releaseNo+':' + mx.fng_desc+'\n', ',') AS OrderIDs
FROM [Mes_QC_Exceptional_Detail] mx  where mx.pGuid=a.guid
)     AS bhgpqxms
FROM Mes_QC_Exceptional A
LEFT JOIN MES_INV_ITEM_ARN B ON A.aboutGuid = b.guid
LEFT JOIN MES_ITEMS C ON A.itemId = c.item_id
LEFT JOIN MES_SUPPLIER D ON B.supp_id = D.id
WHERE a.guid = @guid";
        const string mainSql = @"EXEC select_oa_BuildMainData @guid";

        var mainData = Db.Ado.SqlQuery<dynamic>(mainSql, new { guid = queryObj.qcczdGuid });

@@ -133,10 +199,15 @@
        // 字段映射配置（字段名 -> 数据库列名）
        var fieldMappings = new Dictionary<string, (string Field, string Default)>
        {
            ["wlbm"] = ("wlbm", "N/A"),
            ["sqr"] = ("sqr", "0"),       // 默认值改为数字
            ["sqrq"] = ("sqrq", ""),       // 空字符串用于日期格式化
            ["szbm"] = ("szbm", "0"),      // 默认值改为数字
            ["szdw"] = ("szdw", "0"),      // 默认值改为数字
            ["ycczdh"] = ("ycczdh", "N/A"),
            ["lh"] = ("lh", "N/A"),
            ["khgys"] = ("khgys", "N/A"),
            ["jzmcwlmc"] = ("jzmcwlmc", "N/A"),
            ["dhdhmes"] = ("dhdhmes", "N/A"),
            ["dhdhtxt"] = ("dhdhtxt", "N/A"),
            ["bhgpqxms"] = ("bhgpqxms", "无缺陷描述")
        };

@@ -144,7 +215,16 @@
        var result = new JArray();
        foreach (var mapping in fieldMappings)
        {
            var value = GetDynamicValue(firstRecord, mapping.Value.Field, mapping.Value.Default);
            var value = mapping.Key switch
            {
                "sqr" or "szbm" or "szdw" => 
                    int.TryParse(GetDynamicValue(firstRecord, mapping.Value.Field, mapping.Value.Default), out int num) 
                        ? num : 0,
                "sqrq" => DateTime.TryParse(GetDynamicValue(firstRecord, mapping.Value.Field, ""), out DateTime date) 
                    ? date.ToString("yyyy-MM-dd") 
                    : DateTime.Now.ToString("yyyy-MM-dd"),
                _ => GetDynamicValue(firstRecord, mapping.Value.Field, mapping.Value.Default)
            };
            result.Add(new JObject
            {
                ["fieldName"] = mapping.Key,
@@ -159,7 +239,7 @@
    'http://192.168.1.145:81/upload/'+url_Path as url
FROM [Mes_QC_Exceptional_Detail] A
LEFT JOIN MES_QA_ITEMS_DETECT_01 B ON A.releaseNo = B.release_no
LEFT JOIN MES_FILE C ON C.parent_Guid = B.guid
RIGHT JOIN MES_FILE C ON C.parent_Guid = B.guid
WHERE pGuid = @guid";

        var fileData = Db.Ado.SqlQuery<dynamic>(fileSql, new { guid = queryObj.qcczdGuid });
@@ -294,8 +374,10 @@
    {
        // 使用参数化查询获取明细数据
        const string mainSql = @"select ROW_NUMBER() over (ORDER BY Jy_Date) AS xh,releaseNo AS jydh, Jy_Date AS jydrq, batchQty as sjsl,
               chouQty AS cys, badQty AS bls, badProb AS bll 
            from [Mes_QC_Exceptional_Detail] 
               chouQty AS cys, badQty AS bls, badProb AS bll,C.USER_NAME AS jyr
            from Mes_QC_Exceptional_Detail A
            LEFT JOIN MES_QA_ITEMS_DETECT_01 B ON A.releaseNo = B.release_no
            LEFT JOIN SYS_USER C ON C.ACCOUNT = B.fcheck_by
            WHERE pGuid = @guid";
    
        var details = Db.Ado.SqlQuery<dynamic>(mainSql, new { guid = queryObj.qcczdGuid });
@@ -310,6 +392,7 @@
            {
                //new JObject { ["fieldName"] = "xh", ["fieldValue"] = record.xh?.ToString() ?? "" },
                new JObject { ["fieldName"] = "jydh", ["fieldValue"] = record.jydh?.ToString() ?? "" },
                 new JObject { ["fieldName"] = "jyr", ["fieldValue"] = record.jyr?.ToString() ?? "" },
                new JObject { 
                    ["fieldName"] = "jydrq", 
                    // 格式化为"yyyy-MM-dd"字符串
@@ -322,7 +405,7 @@
                new JObject { ["fieldName"] = "sjsl", ["fieldValue"] = (float)(record.sjsl ?? 0m) },
                new JObject { ["fieldName"] = "cys", ["fieldValue"] = (float)(record.cys ?? 0) },
                new JObject { ["fieldName"] = "bls", ["fieldValue"] = (float)(record.bls ?? 0) },
                new JObject { ["fieldName"] = "bll", ["fieldValue"] = 1 }
                new JObject { ["fieldName"] = "bll", ["fieldValue"] = 1, }
            };
    
            tableRecords.Add(new JObject
@@ -351,7 +434,7 @@
        };
    }

    private async Task<JObject> GetRegistAsync()
    private  JObject GetRegistAsync()
    {
        using var rsa = new RSACryptoServiceProvider(2048);
        var cpk = Convert.ToBase64String(rsa.ExportRSAPublicKey());
@@ -360,18 +443,18 @@
        _httpClient.DefaultRequestHeaders.Add("appid", AppId);
        _httpClient.DefaultRequestHeaders.Add("cpk", cpk);

        var response = await _httpClient.PostAsync(BaseUrl + "regist", null);
        return JObject.Parse(await response.Content.ReadAsStringAsync());
        var response = _httpClient.PostAsync(BaseUrl + "regist", null).Result;
        return JObject.Parse(response.Content.ReadAsStringAsync().Result);
    }

    private async Task<JObject> ApplyTokenAsync(string secret, string spk)
    private JObject ApplyTokenAsync(string secret, string spk)
    {
        _httpClient.DefaultRequestHeaders.Clear();
        _httpClient.DefaultRequestHeaders.Add("appid", AppId);
        _httpClient.DefaultRequestHeaders.Add("secret", RSAEncrypt(secret, spk));

        var response = await _httpClient.PostAsync(BaseUrl + "applytoken", null);
        return JObject.Parse(await response.Content.ReadAsStringAsync());
        var response = _httpClient.PostAsync(BaseUrl + "applytoken", null).Result;
        return JObject.Parse(response.Content.ReadAsStringAsync().Result);
    }

    private string RSAEncrypt(string data, string publicKey)
@@ -396,7 +479,7 @@
        return encryptedContent;
    }

    private async Task<string> PostFormAsync(string url, Dictionary<string, object> data)
    private dynamic PostForm(string url, Dictionary<string, object> data)
    {
        // 改用FormUrlEncodedContent并正确设置Content-Type
        var formData = new List<KeyValuePair<string, string>>();
@@ -408,8 +491,8 @@
        var content = new FormUrlEncodedContent(formData);
        content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/x-www-form-urlencoded");

        var response = await _httpClient.PostAsync(url, content);
        var responseString = await response.Content.ReadAsStringAsync();
        var response = _httpClient.PostAsync(url, content).Result;
        var responseString = response.Content.ReadAsStringAsync().Result;

        // 添加响应元数据
        var responseInfo = new JObject
@@ -423,11 +506,11 @@
        // 调试输出完整响应信息
        Console.WriteLine($"完整响应：\n{responseInfo.ToString(Formatting.Indented)}");

        return responseInfo.ToString();
        return responseInfo;
    }

    public void Dispose()
    {
        _httpClient?.Dispose();
    }
}
}