From 5ad77f8491b3137238bafe30c2e2d83c60558bdb Mon Sep 17 00:00:00 2001
From: kyy <3283105747@qq.com>
Date: 星期四, 31 七月 2025 16:23:14 +0800
Subject: [PATCH] 修改sop获取数据
---
MESApplication/Controllers/PLM/PLMController.cs | 225 ++++++++++++++++++++++++++++++++++++-------------------
1 files changed, 147 insertions(+), 78 deletions(-)
diff --git a/MESApplication/Controllers/PLM/PLMController.cs b/MESApplication/Controllers/PLM/PLMController.cs
index 404c901..cc87770 100644
--- a/MESApplication/Controllers/PLM/PLMController.cs
+++ b/MESApplication/Controllers/PLM/PLMController.cs
@@ -13,13 +13,13 @@
public class PLMController : ControllerBase
{
private readonly IConfiguration _configuration;
- private readonly PLMManager m = new();
- private readonly WarehouseDownloadDoc wdd = new();
+ private readonly PLMManager _plmManager = new();
+ private readonly WarehouseDownloadDoc _warehouseDownloadDoc = new();
public PLMController(IConfiguration configuration)
{
_configuration = configuration;
- } // RetrieveDrawings 璋冨彇鍥剧焊
+ }
[HttpPost("RetrieveDrawings")]
public async Task<ResponseResult> RetrieveDrawings(PurchaseInventory dto)
@@ -27,70 +27,66 @@
try
{
dynamic resultInfos = new ExpandoObject();
- var retrieveDrawings =
- m.RetrieveDrawings(dto.ItemNo); // 鑷畾涔変繚瀛樻牴鐩綍
- // string saveRootDirectory = @"D:\LTSMES\PLM";
+ var drawings = _plmManager.RetrieveDrawings(dto.ItemNo);
var saveRootDirectory = @"E:\SOPimage";
- var
- saveDirectory =
- Path.Combine(saveRootDirectory,
- dto.ItemNo); // 鍦ㄥ惊鐜鐞嗘墍鏈� PDF 鏂囦欢涔嬪墠锛屽鏋滆 itemNo 宸插瓨鍦ㄨ〃涓紝鍒欒繘琛屽垹闄ゆ搷浣�
- await DeleteRecordsByItemNo(dto.ItemNo);
- // 鍦ㄥ惊鐜鐞嗘墍鏈� PDF 鏂囦欢涔嬪墠锛屾鏌ュ苟鍒犻櫎鏃ф枃浠跺す锛堝鏋滃瓨鍦級锛岀劧鍚庨噸鏂板垱寤�
+ var saveDirectory = Path.Combine(saveRootDirectory, dto.ItemNo);
+
+ // 鍏堝垹闄ゅ綋鍓嶄骇绾�(cx)鐨勬暟鎹拰瀵瑰簲鏂囦欢澶�
+ await DeleteRecordsByProductionLine(dto.Cx); // 鏂板锛氭寜浜х嚎鍒犻櫎璁板綍
+
if (Directory.Exists(saveDirectory))
Directory.Delete(saveDirectory, true);
Directory.CreateDirectory(saveDirectory);
- foreach (var retrieveDrawing in retrieveDrawings)
+
+ // 澶勭悊姣忎釜鍥剧焊
+ foreach (var drawing in drawings)
{
- var streamFile = wdd.SendRequest("Download",
- retrieveDrawing.FRelevantObject);
- var fileStreamResult = File(streamFile,
- "application/octet-stream", retrieveDrawing.FName);
- var saveResult = await SavePdfAsync(fileStreamResult,
- saveRootDirectory, dto.ItemNo, dto.Cx,
- retrieveDrawing.FName);
+ var streamFile = _warehouseDownloadDoc.SendRequest("Download", drawing.FRelevantObject);
+ var fileStreamResult = File(streamFile, "application/octet-stream", drawing.FName);
+ await SavePdfAsync(fileStreamResult, saveRootDirectory, dto.ItemNo, dto.Cx, drawing.FName);
}
- return new ResponseResult
- { status = 0, message = "OK", data = resultInfos };
+ return new ResponseResult { status = 0, message = "OK", data = resultInfos };
}
catch (Exception ex)
{
return ResponseResult.ResponseError(ex);
}
- } // 鉁� 鐩存帴淇濆瓨 PDF锛屼笉鍐嶈浆鎹负鍥剧墖
+ }
- private async Task<dynamic> SavePdfAsync(FileStreamResult fileStreamResult,
- string saveRootDirectory,
- string itemNo, string cx, string fPhysicalFileName)
+ private async Task<dynamic> SavePdfAsync(FileStreamResult fileStreamResult, string saveRootDirectory,
+ string itemNo, string cx, string fileName)
{
try
{
- // 纭繚鐩綍瀛樺湪
var saveDirectory = Path.Combine(saveRootDirectory, itemNo);
if (!Directory.Exists(saveDirectory))
- Directory.CreateDirectory(saveDirectory); // 淇濆瓨 PDF 鏂囦欢
+ Directory.CreateDirectory(saveDirectory);
var outputFilePath = Path.Combine(saveDirectory,
- $"{Path.GetFileNameWithoutExtension(fPhysicalFileName)}.pdf");
- using (var fileStream = new FileStream(outputFilePath,
- FileMode.Create, FileAccess.Write))
+ $"{Path.GetFileNameWithoutExtension(fileName)}.pdf");
+
+ using (var fileStream = new FileStream(outputFilePath, FileMode.Create, FileAccess.Write))
{
await fileStreamResult.FileStream.CopyToAsync(fileStream);
}
- Console.WriteLine(
- $"PDF 鏂囦欢宸蹭繚瀛�: {outputFilePath}"); // 鐢熸垚鏂扮殑 URL锛堟寚鍚� PDF 鏂囦欢锛�
- var
- url =
- $"http://192.168.1.92:89/{itemNo}/{Path.GetFileName(outputFilePath)}"; // 鎻愬彇鏂囦欢鍚嶄腑鐨勬渶鍚庝竴涓�'_'鍒�'.pdf'涔嬮棿鐨勫瓧姣嶆暟瀛楅儴鍒嗭紝骞跺姞涓娾�滃伐搴忊�濆墠缂�
- var extractedNumber = ExtractNumberFromFileName(fPhysicalFileName);
- var
- process = string.IsNullOrEmpty(extractedNumber)
- ? string.Empty
- : $"宸ュ簭{extractedNumber}"; // 鉁� 鎻掑叆鏁版嵁搴擄紙URL 鎸囧悜 PDF锛屽苟鏇存柊宸ュ簭瀛楁锛�
- await InsertUrlIntoDatabase(url, itemNo, cx, process);
+ Console.WriteLine($"PDF 鏂囦欢宸蹭繚瀛�: {outputFilePath}");
+
+ var url = $"http://192.168.1.92:89/{itemNo}/{Path.GetFileName(outputFilePath)}";
+ var extractedNumber = ExtractNumberFromFileName(fileName);
+ var process = string.IsNullOrEmpty(extractedNumber) ? string.Empty : $"宸ュ簭{extractedNumber}";
+
+ // 鎻掑叆璁板綍骞惰幏鍙朓D
+ var newRecordId = await InsertUrlIntoDatabase(url, itemNo, cx, process);
+
+ // 濡傛灉鎻掑叆鎴愬姛锛屾煡璇㈠苟鏇存柊MAC鍦板潃
+ if (newRecordId > 0)
+ {
+ await UpdateMacAddress(newRecordId, cx, process);
+ }
+
return new { PdfFilePath = outputFilePath };
}
catch (Exception ex)
@@ -98,64 +94,137 @@
Console.WriteLine($"PDF 淇濆瓨澶辫触: {ex.Message}");
return new { ErrorMessage = "PDF 淇濆瓨澶辫触", Exception = ex.Message };
}
- } // 鎻愬彇鏂囦欢鍚嶄腑鐨勬渶鍚庝竴涓�'_'鍒�'.pdf'涔嬮棿鐨勫瓧姣嶆暟瀛楅儴鍒嗭紝蹇界暐鎷彿绛夊唴瀹�
+ }
private string ExtractNumberFromFileName(string fileName)
{
- // 浣跨敤姝ｅ垯琛ㄨ揪寮忔彁鍙栨渶鍚庝竴涓�'_'鍒�'.pdf'涔嬮棿鐨勫瓧姣嶆暟瀛楅儴鍒嗭紝蹇界暐鎷彿绛夊瓧绗�
var match = Regex.Match(fileName, @"_([A-Za-z0-9]+)(?=\(?\.pdf$)");
- if (match.Success)
- // 濡傛灉鍖归厤鎴愬姛锛岃繑鍥炴彁鍙栫殑閮ㄥ垎
- return match.Groups[1].Value;
+ return match.Success ? match.Groups[1].Value : string.Empty;
+ }
- // 濡傛灉娌℃湁鎵惧埌绗﹀悎鏉′欢鐨勯儴鍒嗭紝杩斿洖绌哄瓧绗︿覆
- return string.Empty;
- } // 灏� URL銆乮temNo銆乧x 鍜屽伐搴忓瓧娈垫彃鍏ュ埌鏁版嵁搴撲腑
-
-
- private async Task InsertUrlIntoDatabase(string url, string itemNo,
- string cx, string gx)
+ private async Task<long> InsertUrlIntoDatabase(string url, string itemNo, string cx, string gx)
{
var connectionString = _configuration["AppSettings:DataBaseConn"];
using (var connection = new OracleConnection(connectionString))
{
await connection.OpenAsync();
- var insertQuery =
- "INSERT INTO MES_SOP_URL_TABLE (id, URL, ITEM_NO, CX, GX, CREATE_DATE) VALUES (SEQ_SOP_URL.NEXTVAL, :url, :item_no, :cx, :gx, :create_date)";
- using (var insertCommand =
- new OracleCommand(insertQuery, connection))
+
+ // 浣跨敤浜嬪姟纭繚鏁版嵁涓�鑷存��
+ using (var transaction = connection.BeginTransaction())
{
- // 浣跨敤 Parameters.Add 鏂规硶娣诲姞鍙傛暟
- insertCommand.Parameters.Add("url", OracleDbType.Varchar2)
- .Value = url;
- insertCommand.Parameters.Add("item_no", OracleDbType.Varchar2)
- .Value = itemNo;
- insertCommand.Parameters.Add("cx", OracleDbType.Varchar2)
- .Value = cx;
- insertCommand.Parameters.Add("gx", OracleDbType.Varchar2)
- .Value = gx;
- insertCommand.Parameters.Add("create_date", OracleDbType.Date)
- .Value = DateTime.Now;
- await insertCommand.ExecuteNonQueryAsync();
+ try
+ {
+ // 鍏堣幏鍙栧簭鍒楀��
+ var getSeqQuery = "SELECT SEQ_SOP_URL.NEXTVAL FROM DUAL";
+ using (var seqCommand = new OracleCommand(getSeqQuery, connection))
+ {
+ seqCommand.Transaction = transaction;
+ var newId = Convert.ToInt64(await seqCommand.ExecuteScalarAsync());
+
+ // 鎻掑叆璁板綍
+ var insertQuery = @"INSERT INTO MES_SOP_URL_TABLE
+ (id, URL, ITEM_NO, CX, GX, CREATE_DATE, MAC)
+ VALUES (:id, :url, :item_no, :cx, :gx, :create_date, null)";
+
+ using (var insertCommand = new OracleCommand(insertQuery, connection))
+ {
+ insertCommand.Transaction = transaction;
+ insertCommand.Parameters.Add("id", OracleDbType.Int64).Value = newId;
+ insertCommand.Parameters.Add("url", OracleDbType.Varchar2).Value = url;
+ insertCommand.Parameters.Add("item_no", OracleDbType.Varchar2).Value = itemNo;
+ insertCommand.Parameters.Add("cx", OracleDbType.Varchar2).Value = cx;
+ insertCommand.Parameters.Add("gx", OracleDbType.Varchar2).Value = gx;
+ insertCommand.Parameters.Add("create_date", OracleDbType.Date).Value = DateTime.Now;
+
+ await insertCommand.ExecuteNonQueryAsync();
+ }
+
+ transaction.Commit();
+ return newId;
+ }
+ }
+ catch (Exception)
+ {
+ transaction.Rollback();
+ throw;
+ }
}
}
- } // 鍒犻櫎鎸囧畾 itemNo 鐨勮褰�
+ }
+ private async Task UpdateMacAddress(long recordId, string cx, string gx)
+ {
+ var connectionString = _configuration["AppSettings:DataBaseConn"];
+ using (var connection = new OracleConnection(connectionString))
+ {
+ await connection.OpenAsync();
+
+ // 浠庢瘮杈冭〃鏌ヨMAC鍦板潃
+ var selectQuery = @"SELECT MAC FROM MES_SOP_URL_TABLE_COMPARE
+ WHERE CX = :cx AND GX = :gx";
+
+ using (var selectCommand = new OracleCommand(selectQuery, connection))
+ {
+ selectCommand.Parameters.Add("cx", OracleDbType.Varchar2).Value = cx;
+ selectCommand.Parameters.Add("gx", OracleDbType.Varchar2).Value = gx;
+
+ var macAddress = await selectCommand.ExecuteScalarAsync() as string;
+
+ // 濡傛灉鎵惧埌MAC鍦板潃锛屽垯鏇存柊鍒颁富琛�
+ if (!string.IsNullOrEmpty(macAddress))
+ {
+ var updateQuery = @"UPDATE MES_SOP_URL_TABLE
+ SET MAC = :mac
+ WHERE id = :id";
+
+ using (var updateCommand = new OracleCommand(updateQuery, connection))
+ {
+ updateCommand.Parameters.Add("mac", OracleDbType.Varchar2).Value = macAddress;
+ updateCommand.Parameters.Add("id", OracleDbType.Int64).Value = recordId;
+
+ await updateCommand.ExecuteNonQueryAsync();
+ Console.WriteLine($"宸叉洿鏂癐D涓� {recordId} 鐨勮褰曠殑MAC鍦板潃: {macAddress}");
+ }
+ }
+ else
+ {
+ Console.WriteLine($"鏈壘鍒癈X={cx}, GX={gx}瀵瑰簲鐨凪AC鍦板潃");
+ }
+ }
+ }
+ }
+
+ // 鏂板锛氭寜浜х嚎(cx)鍒犻櫎璁板綍
+ private async Task DeleteRecordsByProductionLine(string cx)
+ {
+ var connectionString = _configuration["AppSettings:DataBaseConn"];
+ using (var connection = new OracleConnection(connectionString))
+ {
+ await connection.OpenAsync();
+ var deleteQuery = "DELETE FROM MES_SOP_URL_TABLE WHERE CX = :cx";
+ using (var deleteCommand = new OracleCommand(deleteQuery, connection))
+ {
+ deleteCommand.Parameters.Add("cx", OracleDbType.Varchar2).Value = cx;
+ int deletedCount = await deleteCommand.ExecuteNonQueryAsync();
+ Console.WriteLine($"宸插垹闄や骇绾� {cx} 鐨� {deletedCount} 鏉¤褰�");
+ }
+ }
+ }
+
+ // 淇濈暀鍘熸寜ItemNo鍒犻櫎鐨勬柟娉曪紝浠ュ涓嶆椂涔嬮渶
private async Task DeleteRecordsByItemNo(string itemNo)
{
var connectionString = _configuration["AppSettings:DataBaseConn"];
using (var connection = new OracleConnection(connectionString))
{
await connection.OpenAsync();
- var deleteQuery =
- "DELETE FROM MES_SOP_URL_TABLE WHERE ITEM_NO = :item_no";
- using (var deleteCommand =
- new OracleCommand(deleteQuery, connection))
+ var deleteQuery = "DELETE FROM MES_SOP_URL_TABLE WHERE ITEM_NO = :item_no";
+ using (var deleteCommand = new OracleCommand(deleteQuery, connection))
{
- deleteCommand.Parameters.Add("item_no", OracleDbType.Varchar2)
- .Value = itemNo;
+ deleteCommand.Parameters.Add("item_no", OracleDbType.Varchar2).Value = itemNo;
await deleteCommand.ExecuteNonQueryAsync();
}
}
}
-}
\ No newline at end of file
+}
+
\ No newline at end of file
--
Gitblit v1.9.3