修改sop获取数据

kyy

2025-07-31 5ad77f8491b3137238bafe30c2e2d83c60558bdb

修改sop获取数据

 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}";
            
            // 插入记录并获取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)
@@ -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、itemNo、cx 和工序字段插入到数据库中 


    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($"已更新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))
            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();
            }
        }
    }
}
}
    

 MESApplication/Controllers/RealTimeInventory/RealTimeInventoryController.cs

@@ -6,25 +6,45 @@

namespace MESApplication.Controllers.RealTimeInventory;

/// <summary>
/// 实时库存控制器
/// 负责同步第三方系统库存数据到本地数据库
/// </summary>
[Route("api/[controller]")]
[ApiController]
public class RealTimeInventoryController : ControllerBase
{
    // K3Cloud系统的基础API地址
    private readonly string _baseUrl = "http://122.227.249.70:5050/k3cloud";
    // 数据库连接字符串
    private readonly string _connectionString;

    /// <summary>
    /// 构造函数
    /// 通过依赖注入获取配置信息
    /// </summary>
    /// <param name="configuration">配置对象</param>
    public RealTimeInventoryController(IConfiguration configuration)
    {
        // 从配置中获取数据库连接字符串
        _connectionString = configuration["AppSettings:DataBaseConn"];
    }

    /// <summary>
    /// 同步库存数据API
    /// 从K3Cloud系统获取库存数据并同步到本地数据库
    /// </summary>
    /// <returns>同步结果</returns>
    [HttpPost("syncInventory")]
    public async Task<IActionResult> SyncInventory()
    {
        // 创建自定义HTTP客户端实例
        var httpClient = new HttpClientEx();
        // 设置身份验证API地址
        httpClient.Url =
            $"{_baseUrl}/Kingdee.BOS.WebApi.ServicesStub.AuthService.ValidateUser.common.kdsvc";

        // 准备登录参数
        var parameters = new List<object>
        {
            "6654201b47f099", // 帐套Id
@@ -33,66 +53,92 @@
            2052 // 语言（中文）
        };

        // 序列化参数并设置为请求内容
        httpClient.Content = JsonConvert.SerializeObject(parameters);
        // 发送登录请求并解析结果
        var result = JObject.Parse(httpClient.AsyncRequest())["LoginResultType"]
            .Value<int>();

        // 登录失败返回错误信息
        if (result != 1)
            return BadRequest("登录失败");

        // 获取需要同步的仓库代码列表
        var depotCodes = FetchDepotCodes();
        if (depotCodes.Count == 0)
            return NotFound("未找到有效的DEPOTS_CODE");

        // 清空现有库存数据
        DeleteInventoryData();

        // 将仓库代码列表转换为逗号分隔的字符串
        var depotCodesStr = string.Join(",", depotCodes);
        var hasMoreData = true;
        var pageIndex = 1;

        // 分页获取所有库存数据
        while (hasMoreData)
        {
            // 构建库存查询参数模型
            var model = new InventoryParamModel
            {
                fstocknumbers = depotCodesStr,
                isshowauxprop = true,
                isshowstockloc = true,
                pageindex = pageIndex,
                pagerows = 10000
                fstocknumbers = depotCodesStr,    // 仓库代码
                isshowauxprop = true,             // 是否显示辅助属性
                isshowstockloc = true,            // 是否显示库存位置
                pageindex = pageIndex,            // 当前页码
                pagerows = 10000                  // 每页记录数
            };

            // 设置库存查询API地址
            httpClient.Url =
                $"{_baseUrl}/Kingdee.K3.SCM.WebApi.ServicesStub.InventoryQueryService.GetInventoryData.common.kdsvc";
            // 序列化查询参数
            httpClient.Content =
                JsonConvert.SerializeObject(new List<object> { model });
            // 发送查询请求
            var response = httpClient.AsyncRequest();
            // 反序列化库存数据
            var inventoryData =
                JsonConvert.DeserializeObject<InventoryResponse>(response);

            // 检查是否还有数据
            if (inventoryData?.Data == null || inventoryData.Data.Count == 0)
            {
                hasMoreData = false;
            }
            else
            {
                // 将当前页数据插入数据库
                InsertInventoryData(inventoryData);
                // 准备获取下一页数据
                pageIndex++;
            }
        }

        // 返回同步完成消息
        return Ok("库存数据导入完成");
    }

    /// <summary>
    /// 从本地数据库获取仓库代码列表
    /// 排除特定仓库代码('MJ','CY-MJ')
    /// </summary>
    /// <returns>仓库代码列表</returns>
    private List<string> FetchDepotCodes()
    {
        var depotCodes = new List<string>();
        // 使用Oracle连接
        using (var connection = new OracleConnection(_connectionString))
        {
            try
            {
                // 打开数据库连接
                connection.Open();
                // 查询有效仓库代码的SQL
                var query =
                    "SELECT DEPOTS_CODE FROM MES_INV_ITEM_STOCKS WHERE DEPOTS_CODE IS NOT NULL AND DEPOTS_CODE NOT IN ('MJ','CY-MJ') GROUP BY DEPOTS_CODE";
                
                // 执行查询并读取结果
                using (var cmd = new OracleCommand(query, connection))
                using (var reader = cmd.ExecuteReader())
                {
@@ -109,6 +155,10 @@
        return depotCodes;
    }

    /// <summary>
    /// 清空ERPKCPC表中的现有库存数据
    /// 使用事务确保操作的原子性
    /// </summary>
    private void DeleteInventoryData()
    {
        using (var connection = new OracleConnection(_connectionString))
@@ -116,14 +166,17 @@
            try
            {
                connection.Open();
                // 开始数据库事务
                using (var transaction = connection.BeginTransaction())
                {
                    // 执行删除操作
                    using (var deleteCmd =
                           new OracleCommand("DELETE FROM ERPKCPC", connection))
                    {
                        deleteCmd.ExecuteNonQuery();
                    }

                    // 提交事务
                    transaction.Commit();
                }
            }
@@ -134,6 +187,11 @@
        }
    }

    /// <summary>
    /// 将库存数据插入到ERPKCPC表中
    /// 使用事务确保所有数据要么全部插入成功，要么全部失败
    /// </summary>
    /// <param name="inventoryData">库存数据响应对象</param>
    private void InsertInventoryData(InventoryResponse inventoryData)
    {
        using (var connection = new OracleConnection(_connectionString))
@@ -141,10 +199,13 @@
            try
            {
                connection.Open();
                // 开始数据库事务
                using (var transaction = connection.BeginTransaction())
                {
                    // 遍历每条库存数据
                    foreach (var item in inventoryData.Data)
                    {
                        // 插入SQL语句
                        var query =
                            "INSERT INTO ERPKCPC (ITEM_ID, DEPOT_CODE, FQTY, ITEM_NO, ORGID,Fstockstatusnumber,Fstockstatusname) VALUES (:ITEM_ID, :DEPOT_CODE, :FQTY, :ITEM_NO, :ORGID,:Fstockstatusnumber,:Fstockstatusname)";
                        using (var cmd = new OracleCommand(query, connection))
@@ -159,10 +220,12 @@

                            try
                            {
                                // 执行插入命令
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                // 出错时回滚事务
                                transaction.Rollback();
                                Console.WriteLine("插入库存数据时出错: " + ex.Message);
                                return;
@@ -170,6 +233,7 @@
                        }
                    }

                    // 所有数据插入成功，提交事务
                    transaction.Commit();
                }
            }