From f95dd1ce548f2a465d13f70fe4616410a4d77cc5 Mon Sep 17 00:00:00 2001
From: 南骏 池 <chiffly@163.com>
Date: 星期三, 14 五月 2025 08:45:58 +0800
Subject: [PATCH] 1.OA接口优化。 2.携客云接口调用 3.携客云送货单、送货明细、条码信息实体

---
 service/QC/OaApi.cs |  391 +++++++++++++++++++++++++++++++++++++++++++++++--------
 1 files changed, 329 insertions(+), 62 deletions(-)

diff --git a/service/QC/OaApi.cs b/service/QC/OaApi.cs
index e4d2669..60c3601 100644
--- a/service/QC/OaApi.cs
+++ b/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("寮傚父澶勭疆鍗昳d涓嶈兘涓虹┖");
+        //if (queryObj.qcczdGuid.IsNullOrEmpty()) throw new Exception("寮傚父澶勭疆鍗昳d涓嶈兘涓虹┖"); // 鍘熷弬鏁板悕涓� qcczdGuid
 
         try
         {
             // 瑙f瀽璇锋眰鍙傛暟
-            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鍒癘A绯荤粺" },
+                            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());
+
+            // 鏂板鐘舵�佹洿鏂帮紙褰揙A杩斿洖鎴愬姛鏃讹級
+            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;  // 鐩存帴瑙f瀽涓篔Object
+
         }
         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)
     {
         // 浠巕uery鍙傛暟涓幏鍙栧疄闄呬笟鍔℃暟鎹�
         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)
+    {
+        // 浣跨敤鍙傛暟鍖栨煡璇㈤槻姝QL娉ㄥ叆
+        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;
+    }
+
+    // 杈呭姪鏂规硶锛氬畨鍏ㄨ幏鍙杁ynamic瀵硅薄鍊�
+    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)
+    {
+        // 鍋囪杩欐槸鍘焎reatebhgpqxmszp鏂规硶鐨勫疄鐜�
+        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
+    //        {
+    //            // 淇敼鎵�鏈夊瓧娈佃闂柟寮忥紙浠lbm涓轰緥锛�
+    //            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>())
+        {
+            // 淇瀛楁绫诲瀷杞崲锛堟暟鍊肩被鍨嬪幓闄ゅ瓧绗︿覆鍖呰9锛�
+            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骞舵纭缃瓹ontent-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();
     }
-}
\ No newline at end of file
+}

--
Gitblit v1.9.3