service/QC/OaApi.cs
@@ -1,18 +1,26 @@
// 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;
using System.Text;
using System.Threading.Tasks;
using NewPdaSqlServer.entity.Base;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Org.BouncyCastle.Crypto.Parameters;
using Org.BouncyCastle.Security;
using NewPdaSqlServer.DB;
using SqlSugar;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
using NewPdaSqlServer.entity;
using System.Linq;
public class OaApiService : IDisposable
public class OaApiService : Repository<dynamic>
{
    private readonly HttpClient _httpClient;
    private const string AppId = "303233EF-AC39-40C1-8364-AC989C6258A5";
@@ -27,16 +35,15 @@
    public async Task<dynamic> SubmitIQCToOA(dynamic queryObj)
    {
        //if (unity.userName.IsNullOrEmpty()) throw new Exception("用户名不允许为空");
        //if (unity.sectionCode.IsNullOrEmpty()) throw new Exception("库位编号不允许为空");
        //if (unity.barcode.IsNullOrEmpty()) throw new Exception("条码不允许为空");
        //if (unity.billNo.IsNullOrEmpty()) throw new Exception("申请单号不允许为空");
        // 修改参数验证字段名
        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
        {
            // 解析请求参数
            var query = queryObj;
            //var query = queryObj;
            //var query = JObject.FromObject(queryObj);
            // 1. 注册获取凭证
@@ -55,61 +62,294 @@
            _httpClient.DefaultRequestHeaders.Add("userid", RSAEncrypt("1268", spk));
            // 4. 构建请求体
            var requestData = BuildRequestData();
            var requestData = BuildRequestData(queryObj);
            // 5. 发送请求
            var response = await PostFormAsync(WorkflowUrl, requestData);
            return JObject.Parse(response);
            // 新增日志记录(在返回响应前)
            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;
        }
    }
    private Dictionary<string, object> BuildRequestData()
    private Dictionary<string, object> BuildRequestData(dynamic queryObj)
    {
        // 从query参数中获取实际业务数据
        return new Dictionary<string, object>
        {
            {"mainData", BuildMainData()},
            {"requestName", $"IQC流程-{DateTime.Now:yyyyMMddHHmmss}"},
            {"mainData", BuildMainData(queryObj)},
            {"requestName", $"IQC异常处置单流程-{DateTime.Now:yyyyMMddHHmmss}"},
            {"workflowId", "379"},
            //{"otherParams", BuildOtherParams()},
            //{"detailData", BuildDetailData(query)}
            {"detailData", BuildDetailData(queryObj)}
        };
    }
    private JArray BuildMainData()
    private JArray createbhgpqxmszp(dynamic files)
    {
        // 示例字段,根据实际业务需求调整
        return new JArray
        var attachments = new JArray();
        foreach (var file in files ?? Enumerable.Empty<dynamic>())
        {
            new JObject
            attachments.Add(new JObject
            {
                ["fieldName"] = "wlbm",
                ["fieldValue"] = "1.01.01.001"
            },
            new JObject
            {
                ["fieldName"] = "khgys",
                ["fieldValue"] = "陈雪年"
            },
            new JObject
            {
                ["fieldName"] = "bhgpqxms",
                ["fieldValue"] = "外观不合格,尺寸不合格,颜色不符合"
            },
            new JObject
            {
                ["fieldName"] = "dhdhmes",
                ["fieldValue"] = "MES001"
            }
        };
                // 修正字段名称匹配标准格式
                ["filePath"] = file.url?.ToString() ?? string.Empty,
                ["fileName"] = file.FileName?.ToString() ?? "未命名文件"
            });
        }
        return attachments;
    }
    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";
        var mainData = Db.Ado.SqlQuery<dynamic>(mainSql, new { guid = queryObj.qcczdGuid });
        // 更精确的数据存在性检查
        if (mainData == null || !mainData.Any())
        {
            throw new Exception($"未找到异常处置单[{queryObj.qcczdGuid}]相关数据");
        }
        var firstRecord = mainData.First();
        // 字段映射配置(字段名 -> 数据库列名)
        var fieldMappings = new Dictionary<string, (string Field, string Default)>
        {
            ["wlbm"] = ("wlbm", "N/A"),
            ["khgys"] = ("khgys", "N/A"),
            ["jzmcwlmc"] = ("jzmcwlmc", "N/A"),
            ["dhdhmes"] = ("dhdhmes", "N/A"),
            ["bhgpqxms"] = ("bhgpqxms", "无缺陷描述")
        };
        // 动态生成字段集合
        var result = new JArray();
        foreach (var mapping in fieldMappings)
        {
            var value = GetDynamicValue(firstRecord, mapping.Value.Field, mapping.Value.Default);
            result.Add(new JObject
            {
                ["fieldName"] = mapping.Key,
                ["fieldValue"] = value
            });
        }
        // 处理文件数据
        const string fileSql = @"
SELECT
    release_no+'-图片'+CAST(row_number() over (order by release_no) AS VARCHAR(20)) AS FileName,
    '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
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 });
        var files = CreateFileData(fileData);
        // 添加文件字段
        result.Add(new JObject
        {
            ["fieldName"] = "bhgpqxmszp",
            ["fieldValue"] = files
        });
        return result;
    }
    // 辅助方法:安全获取dynamic对象值
    private static string GetDynamicValue(dynamic record, string field, string defaultValue)
    {
        try
        {
            return ((IDictionary<string, object>)record)[field]?.ToString() ?? defaultValue;
        }
        catch
        {
            return defaultValue;
        }
    }
    // 文件数据生成方法(示例保持原逻辑)
    private JArray CreateFileData(IEnumerable<dynamic> fileData)
    {
        // 假设这是原createbhgpqxmszp方法的实现
        return new JArray(fileData.Select(f => new JObject
        {
            ["fileName"] = f.FileName,
            ["filePath"] = f.url
        }));
    }
    //    // 修改BuildMainData中的字段赋值
    //    private JArray BuildMainData(dynamic queryObj)
    //    {
    //        var sql = string.Format(@"select TOP 1 C.item_no   AS wlbm,
    //       C.item_name AS jzmcwlmc,
    //       D.supp_name AS khgys,
    //       B.bill_no   AS dhdhmes,
    //       B.remark    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 = '{0}'", queryObj.qcczdGuid);
    //        var tBiqcczd = Db.Ado.SqlQuery<dynamic>(sql);
    //        // 修正数据获取逻辑
    //        if (tBiqcczd == null)
    //        {
    //            throw new Exception($"未找到异常处置单[{queryObj.qcczdGuid}]相关数据");
    //        }
    //        var data = tBiqcczd; // 转换为字典访问
    //        var wlbm = data[0]["wlbm"];
    //        var sql1 = string.Format(@"select release_no+'-图片'+CAST(row_number() over (order by release_no) AS VARCHAR(20)) AS FileName, '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
    //WHERE pGuid = '{0}'", queryObj.qcczdGuid);
    //        var tBdataFile = Db.Ado.SqlQuery<dynamic>(sql1);
    //        var data_file = createbhgpqxmszp(tBdataFile);
    //        return new JArray
    //        {
    //            // 修改所有字段访问方式(以wlbm为例)
    //            new JObject
    //            {
    //                ["fieldName"] = "wlbm",
    //                ["fieldValue"] = data?["wlbm"]?.ToString() ?? "N/A"  // 使用字典方式访问
    //            },
    //            new JObject
    //            {
    //                ["fieldName"] = "khgys",
    //                ["fieldValue"] = data?["khgys"]?.ToString() ?? "N/A"  // 统一字典访问
    //            },
    //            new JObject
    //            {
    //                ["fieldName"] = "jzmcwlmc",
    //                ["fieldValue"] = data?["jzmcwlmc"]?.ToString() ?? "N/A"  // 统一字典访问
    //            },
    //            new JObject
    //            {
    //                ["fieldName"] = "dhdhmes",
    //                ["fieldValue"] = data?["dhdhmes"]?.ToString() ?? "N/A"  // 统一字典访问
    //            },
    //            new JObject
    //            {
    //                ["fieldName"] = "bhgpqxms",
    //                ["fieldValue"] = data?["bhgpqxms"]?.ToString() ?? "无缺陷描述"  // 统一字典访问
    //            },
    //            new JObject
    //            {
    //                ["fieldName"] = "bhgpqxmszp",
    //                ["fieldValue"] = data_file  // 使用生成的附件数组
    //            },
    //        };
    //    }
    private JArray BuildAttachments(JToken attachments)
    {
@@ -129,27 +369,54 @@
        return array;
    }
    private JArray BuildDetailData(JObject query)
    private JArray BuildDetailData(dynamic queryObj)
    {
        // 示例明细数据,根据实际业务调整
        // 使用参数化查询获取明细数据
        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]
            WHERE pGuid = @guid";
        var details = Db.Ado.SqlQuery<dynamic>(mainSql, new { guid = queryObj.qcczdGuid });
        var tableRecords = new JArray();
        // 遍历每条明细数据
        foreach (var record in details ?? Enumerable.Empty<dynamic>())
        {
            // 修正字段类型转换(数值类型去除字符串包裹)
            var fields = new JArray
            {
                //new JObject { ["fieldName"] = "xh", ["fieldValue"] = record.xh?.ToString() ?? "" },
                new JObject { ["fieldName"] = "jydh", ["fieldValue"] = record.jydh?.ToString() ?? "" },
                new JObject {
                    ["fieldName"] = "jydrq",
                    // 格式化为"yyyy-MM-dd"字符串
                    ["fieldValue"] = (record.jydrq != null
                        ? Convert.ToDateTime(record.jydrq).ToString("yyyy-MM-dd ")
                        : DateTime.Now.ToString("yyyy-MM-dd"))
                },
                // 数值字段保持数字类型而非字符串
                // 修正数值类型转换方式
                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 }
            };
            tableRecords.Add(new JObject
            {
                ["recordOrder"] = 0,  // 实际业务中可能需要根据行号设置
                ["workflowRequestTableFields"] = fields
            });
        }
        return new JArray
        {
            new JObject
            {
                ["tableDBName"] = "formtable_main_24_dt1",
                ["workflowRequestTableRecords"] = new JArray
                {
                    new JObject
                    {
                        ["recordOrder"] = "0",
                        ["workflowRequestTableFields"] = new JArray
                        {
                            new JObject { ["fieldName"] = "xm", ["fieldValue"] = query["inspector"] ?? "默认检验员" },
                            new JObject { ["fieldName"] = "bh", ["fieldValue"] = query["batchNo"] ?? "默认批次号" },
                            new JObject { ["fieldName"] = "dz", ["fieldValue"] = query["location"] ?? "默认地点" }
                        }
                    }
                }
                ["tableDBName"] = "formtable_main_33_dt1",
                ["workflowRequestTableRecords"] = tableRecords
            }
        };
    }
@@ -208,7 +475,7 @@
        return encryptedContent;
    }
    private async Task<string> PostFormAsync(string url, Dictionary<string, object> data)
    private async Task<dynamic> PostFormAsync(string url, Dictionary<string, object> data)
    {
        // 改用FormUrlEncodedContent并正确设置Content-Type
        var formData = new List<KeyValuePair<string, string>>();
@@ -216,13 +483,13 @@
        {
            formData.Add(new KeyValuePair<string, string>(item.Key, item.Value.ToString()));
        }
        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 responseInfo = new JObject
        {
@@ -231,15 +498,15 @@
            ["Headers"] = JToken.FromObject(response.Headers),
            ["Content"] = responseString
        };
        // 调试输出完整响应信息
        Console.WriteLine($"完整响应:\n{responseInfo.ToString(Formatting.Indented)}");
        return responseInfo.ToString();
        return responseInfo;
    }
    public void Dispose()
    {
        _httpClient?.Dispose();
    }
}
}