修改sop获取数据

kyy

2025-07-31 5ad77f8491b3137238bafe30c2e2d83c60558bdb

 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();
                }
            }