using 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 Oracle.ManagedDataAccess.Client;
|
|
namespace MESApplication.Controllers.PLM;
|
|
[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)
|
{
|
_configuration = configuration;
|
}
|
|
[HttpPost("RetrieveDrawings")]
|
public async Task<ResponseResult> RetrieveDrawings(PurchaseInventory dto)
|
{
|
try
|
{
|
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);
|
|
Directory.CreateDirectory(saveDirectory);
|
|
// 处理每个图纸
|
foreach (var drawing in drawings)
|
{
|
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);
|
|
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}";
|
|
// 插入记录并获取ID
|
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 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;
|
}
|
}
|
}
|
}
|
|
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($"已更新ID为 {recordId} 的记录的MAC地址: {macAddress}");
|
}
|
}
|
else
|
{
|
Console.WriteLine($"未找到CX={cx}, GX={gx}对应的MAC地址");
|
}
|
}
|
}
|
}
|
|
// 新增：按产线(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();
|
}
|
}
|
}
|
}
|
|
