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 | 353 +++++++++++++++++++++++++++++++++++-----------------------
1 files changed, 210 insertions(+), 143 deletions(-)
diff --git a/MESApplication/Controllers/PLM/PLMController.cs b/MESApplication/Controllers/PLM/PLMController.cs
index 4021a4f..cc87770 100644
--- a/MESApplication/Controllers/PLM/PLMController.cs
+++ b/MESApplication/Controllers/PLM/PLMController.cs
@@ -1,163 +1,230 @@
-锘縰sing ConsoleApp1;
+锘縰sing System.Dynamic;
+using System.Text.RegularExpressions;
+using MES.Service.Dto.service;
using MES.Service.service.PLM;
using MES.Service.util;
using Microsoft.AspNetCore.Mvc;
-using System.Dynamic;
-using System.IO;
-using PdfiumViewer;
-using MES.Service.Dto.service;
-using System.Drawing;
-using System.Collections.Generic;
-using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
-using Microsoft.Extensions.Configuration;
-using System;
-using System.Text.RegularExpressions;
-using IronSoftware.Drawing;
-namespace MESApplication.Controllers.PLM
+namespace MESApplication.Controllers.PLM;
+
+[ApiController]
+[Route("api/PLM")]
+public class PLMController : ControllerBase
{
- [ApiController]
- [Route("api/PLM")]
- public class PLMController : ControllerBase
+ private readonly IConfiguration _configuration;
+ private readonly PLMManager _plmManager = new();
+ private readonly WarehouseDownloadDoc _warehouseDownloadDoc = new();
+
+ public PLMController(IConfiguration configuration)
{
- private readonly PLMManager m = new();
- private readonly WarehouseDownloadDoc wdd = new();
- private readonly IConfiguration _configuration;
+ _configuration = configuration;
+ }
- public PLMController(IConfiguration configuration)
+ [HttpPost("RetrieveDrawings")]
+ public async Task<ResponseResult> RetrieveDrawings(PurchaseInventory dto)
+ {
+ try
{
- _configuration = configuration;
- } // RetrieveDrawings 璋冨彇鍥剧焊
+ dynamic resultInfos = new ExpandoObject();
+ var drawings = _plmManager.RetrieveDrawings(dto.ItemNo);
+ var saveRootDirectory = @"E:\SOPimage";
+ var saveDirectory = Path.Combine(saveRootDirectory, dto.ItemNo);
+
+ // 鍏堝垹闄ゅ綋鍓嶄骇绾�(cx)鐨勬暟鎹拰瀵瑰簲鏂囦欢澶�
+ await DeleteRecordsByProductionLine(dto.Cx); // 鏂板锛氭寜浜х嚎鍒犻櫎璁板綍
+
+ if (Directory.Exists(saveDirectory))
+ Directory.Delete(saveDirectory, true);
- [HttpPost("RetrieveDrawings")]
- public async Task<ResponseResult> RetrieveDrawings(PurchaseInventory dto)
- {
- try
+ Directory.CreateDirectory(saveDirectory);
+
+ // 澶勭悊姣忎釜鍥剧焊
+ foreach (var drawing in drawings)
{
- dynamic resultInfos = new ExpandoObject();
- var retrieveDrawings =
- m.RetrieveDrawings(dto.ItemNo); // 鑷畾涔変繚瀛樻牴鐩綍
- // string saveRootDirectory = @"D:\LTSMES\PLM";
- string saveRootDirectory = @"E:\SOPimage";
- string
- saveDirectory =
- Path.Combine(saveRootDirectory,
- dto.ItemNo); // 鍦ㄥ惊鐜鐞嗘墍鏈� PDF 鏂囦欢涔嬪墠锛屽鏋滆 itemNo 宸插瓨鍦ㄨ〃涓紝鍒欒繘琛屽垹闄ゆ搷浣�
- await DeleteRecordsByItemNo(dto.ItemNo);
- // 鍦ㄥ惊鐜鐞嗘墍鏈� PDF 鏂囦欢涔嬪墠锛屾鏌ュ苟鍒犻櫎鏃ф枃浠跺す锛堝鏋滃瓨鍦級锛岀劧鍚庨噸鏂板垱寤�
- if (Directory.Exists(saveDirectory))
- {
- Directory.Delete(saveDirectory, true);
- }
+ 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 };
+ }
+ catch (Exception ex)
+ {
+ return ResponseResult.ResponseError(ex);
+ }
+ }
+
+ 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);
- foreach (var retrieveDrawing in retrieveDrawings)
+
+ var outputFilePath = Path.Combine(saveDirectory,
+ $"{Path.GetFileNameWithoutExtension(fileName)}.pdf");
+
+ using (var fileStream = new FileStream(outputFilePath, FileMode.Create, FileAccess.Write))
+ {
+ await fileStreamResult.FileStream.CopyToAsync(fileStream);
+ }
+
+ 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)
+ {
+ Console.WriteLine($"PDF 淇濆瓨澶辫触: {ex.Message}");
+ return new { ErrorMessage = "PDF 淇濆瓨澶辫触", Exception = ex.Message };
+ }
+ }
+
+ private string ExtractNumberFromFileName(string fileName)
+ {
+ var match = Regex.Match(fileName, @"_([A-Za-z0-9]+)(?=\(?\.pdf$)");
+ return match.Success ? match.Groups[1].Value : string.Empty;
+ }
+
+ 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();
+
+ // 浣跨敤浜嬪姟纭繚鏁版嵁涓�鑷存��
+ using (var transaction = connection.BeginTransaction())
+ {
+ try
{
- 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 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;
+ }
}
-
- 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)
- {
- try
- {
- // 纭繚鐩綍瀛樺湪
- string saveDirectory = Path.Combine(saveRootDirectory, itemNo);
- if (!Directory.Exists(saveDirectory))
+ catch (Exception)
{
- Directory.CreateDirectory(saveDirectory);
- } // 淇濆瓨 PDF 鏂囦欢
-
- string outputFilePath = Path.Combine(saveDirectory,
- $"{Path.GetFileNameWithoutExtension(fPhysicalFileName)}.pdf");
- using (var fileStream = new FileStream(outputFilePath, FileMode.Create, FileAccess.Write))
- {
- await fileStreamResult.FileStream.CopyToAsync(fileStream);
- }
-
- Console.WriteLine(
- $"PDF 鏂囦欢宸蹭繚瀛�: {outputFilePath}"); // 鐢熸垚鏂扮殑 URL锛堟寚鍚� PDF 鏂囦欢锛�
- string
- url =
- $"http://192.168.1.92:89/{itemNo}/{Path.GetFileName(outputFilePath)}"; // 鎻愬彇鏂囦欢鍚嶄腑鐨勬渶鍚庝竴涓�'_'鍒�'.pdf'涔嬮棿鐨勫瓧姣嶆暟瀛楅儴鍒嗭紝骞跺姞涓娾�滃伐搴忊�濆墠缂�
- string extractedNumber = ExtractNumberFromFileName(fPhysicalFileName);
- string
- process = string.IsNullOrEmpty(extractedNumber)
- ? string.Empty
- : $"宸ュ簭{extractedNumber}"; // 鉁� 鎻掑叆鏁版嵁搴擄紙URL 鎸囧悜 PDF锛屽苟鏇存柊宸ュ簭瀛楁锛�
- await InsertUrlIntoDatabase(url, itemNo, cx, process);
- return new { PdfFilePath = outputFilePath };
- }
- catch (Exception ex)
- {
- 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;
- }
- else
- {
- // 濡傛灉娌℃湁鎵惧埌绗﹀悎鏉′欢鐨勯儴鍒嗭紝杩斿洖绌哄瓧绗︿覆
- return string.Empty;
- }
- } // 灏� URL銆乮temNo銆乧x 鍜屽伐搴忓瓧娈垫彃鍏ュ埌鏁版嵁搴撲腑
-
-
- private async Task InsertUrlIntoDatabase(string url, string itemNo, string cx, string gx)
- {
- string connectionString = _configuration["AppSettings:DataBaseConn"];
- using (OracleConnection connection = new OracleConnection(connectionString))
- {
- await connection.OpenAsync();
- string 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 (OracleCommand insertCommand = new OracleCommand(insertQuery, connection))
- {
- // 浣跨敤 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();
- }
- }
- } // 鍒犻櫎鎸囧畾 itemNo 鐨勮褰�
-
- private async Task DeleteRecordsByItemNo(string itemNo)
- {
- string connectionString = _configuration["AppSettings:DataBaseConn"];
- using (OracleConnection connection = new OracleConnection(connectionString))
- {
- await connection.OpenAsync();
- string deleteQuery = "DELETE FROM MES_SOP_URL_TABLE WHERE ITEM_NO = :item_no";
- using (OracleCommand deleteCommand = new OracleCommand(deleteQuery, connection))
- {
- deleteCommand.Parameters.Add("item_no", OracleDbType.Varchar2).Value = itemNo;
- await deleteCommand.ExecuteNonQueryAsync();
+ transaction.Rollback();
+ throw;
}
}
}
}
-}
\ No newline at end of file
+
+ 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))
+ {
+ deleteCommand.Parameters.Add("item_no", OracleDbType.Varchar2).Value = itemNo;
+ await deleteCommand.ExecuteNonQueryAsync();
+ }
+ }
+ }
+}
+
\ No newline at end of file
--
Gitblit v1.9.3