11

啊鑫

2025-07-15 5dc3cc86f5835369cd830f2a83318b9a8d69cf69

 MESApplication/Controllers/RealTimeInventory/RealTimeInventoryController.cs

@@ -1,177 +1,187 @@
using MES.Service.Dto.webApi.RealTimeInventory;

namespace MESApplication.Controllers.RealTimeInventory;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Net;
using System.Text;
using System.Threading.Tasks;

    [Route("api/[controller]")]
    [ApiController]
   
    public class RealTimeInventoryController : ControllerBase
namespace MESApplication.Controllers.RealTimeInventory;

[Route("api/[controller]")]
[ApiController]
public class RealTimeInventoryController : ControllerBase
{
    private readonly string _baseUrl = "http://122.227.249.70:5050/k3cloud";
    private readonly string _connectionString;

    public RealTimeInventoryController(IConfiguration configuration)
    {
        private readonly string _connectionString;
        private readonly string _baseUrl = "http://122.227.249.70:5050/k3cloud";
        _connectionString = configuration["AppSettings:DataBaseConn"];
    }

        public RealTimeInventoryController(IConfiguration configuration)
    [HttpPost("syncInventory")]
    public async Task<IActionResult> SyncInventory()
    {
        var httpClient = new HttpClientEx();
        httpClient.Url =
            $"{_baseUrl}/Kingdee.BOS.WebApi.ServicesStub.AuthService.ValidateUser.common.kdsvc";

        var parameters = new List<object>
        {
            _connectionString = configuration["AppSettings:DataBaseConn"];
        }
            "6654201b47f099", // 帐套Id
            "Administrator", // 用户名
            "Lts88666*", // 密码
            2052 // 语言（中文）
        };

        [HttpPost("syncInventory")]
        public async Task<IActionResult> SyncInventory()
        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)
        {
            HttpClientEx httpClient = new HttpClientEx();
            httpClient.Url = $"{_baseUrl}/Kingdee.BOS.WebApi.ServicesStub.AuthService.ValidateUser.common.kdsvc";

            var parameters = new List<object>
            var model = new InventoryParamModel
            {
                "6654201b47f099", // 帐套Id
                "Administrator", // 用户名
                "Lts88666*", // 密码
                2052 // 语言（中文）
                fstocknumbers = depotCodesStr,
                isshowauxprop = true,
                isshowstockloc = true,
                pageindex = pageIndex,
                pagerows = 10000
            };

            httpClient.Content = JsonConvert.SerializeObject(parameters);
            var result = JObject.Parse(httpClient.AsyncRequest())["LoginResultType"].Value<int>();
            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 (result != 1)
                return BadRequest("登录失败");

            List<string> depotCodes = FetchDepotCodes();
            if (depotCodes.Count == 0)
                return NotFound("未找到有效的DEPOTS_CODE");

            DeleteInventoryData();

            string depotCodesStr = string.Join(",", depotCodes);
            bool hasMoreData = true;
            int pageIndex = 1;

            while (hasMoreData)
            if (inventoryData?.Data == null || inventoryData.Data.Count == 0)
            {
                InventoryParamModel model = new InventoryParamModel
                {
                    fstocknumbers = depotCodesStr,
                    isshowauxprop = true,
                    isshowstockloc = true,
                    pageindex = pageIndex,
                    pagerows = 10000
                };

                httpClient.Url = $"{_baseUrl}/Kingdee.K3.SCM.WebApi.ServicesStub.InventoryQueryService.GetInventoryData.common.kdsvc";
                httpClient.Content = JsonConvert.SerializeObject(new List<object> { model });
                string response = httpClient.AsyncRequest();
                var inventoryData = JsonConvert.DeserializeObject<InventoryResponse>(response);

                if (inventoryData?.Data == null || inventoryData.Data.Count == 0)
                    hasMoreData = false;
                else
                {
                    InsertInventoryData(inventoryData);
                    pageIndex++;
                }
                hasMoreData = false;
            }

            return Ok("库存数据导入完成");
        }

        private List<string> FetchDepotCodes()
        {
            List<string> depotCodes = new List<string>();
            using (var connection = new OracleConnection(_connectionString))
            else
            {
                try
                {
                    connection.Open();
                    string 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())
                    {
                        while (reader.Read())
                            depotCodes.Add(reader.GetString(0));
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("获取仓库代码时出错: " + ex.Message);
                }
            }
            return depotCodes;
        }

        private void DeleteInventoryData()
        {
            using (var connection = new OracleConnection(_connectionString))
            {
                try
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        using (var deleteCmd = new OracleCommand("DELETE FROM ERPKCPC", connection))
                        {
                            deleteCmd.ExecuteNonQuery();
                        }
                        transaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("删除库存数据时出错: " + ex.Message);
                }
                InsertInventoryData(inventoryData);
                pageIndex++;
            }
        }

        private void InsertInventoryData(InventoryResponse inventoryData)
        {
            using (var connection = new OracleConnection(_connectionString))
            {
                try
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        foreach (var item in inventoryData.Data)
                        {
                            string query = "INSERT INTO ERPKCPC (ITEM_ID, DEPOT_CODE, FQTY, ITEM_NO, ORGID) VALUES (:ITEM_ID, :DEPOT_CODE, :FQTY, :ITEM_NO, :ORGID)";
                            using (var cmd = new OracleCommand(query, connection))
                            {
                                cmd.Parameters.Add(new OracleParameter(":ITEM_ID", item.FmaterialId));
                                cmd.Parameters.Add(new OracleParameter(":DEPOT_CODE", item.FstockNumber));
                                cmd.Parameters.Add(new OracleParameter(":FQTY", item.Fqty));
                                cmd.Parameters.Add(new OracleParameter(":ITEM_NO", item.FmaterialNumber));
                                cmd.Parameters.Add(new OracleParameter(":ORGID", item.FstockorgId));
        return Ok("库存数据导入完成");
    }

                                try
                                {
                                    cmd.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    transaction.Rollback();
                                    Console.WriteLine("插入库存数据时出错: " + ex.Message);
                                    return;
                                }
                            }
                        }
                        transaction.Commit();
                    }
                }
                catch (Exception ex)
    private List<string> FetchDepotCodes()
    {
        var depotCodes = new List<string>();
        using (var connection = new OracleConnection(_connectionString))
        {
            try
            {
                connection.Open();
                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())
                {
                    Console.WriteLine("数据库连接出错: " + ex.Message);
                    while (reader.Read())
                        depotCodes.Add(reader.GetString(0));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("获取仓库代码时出错: " + ex.Message);
            }
        }

        return depotCodes;
    }

    private void DeleteInventoryData()
    {
        using (var connection = new OracleConnection(_connectionString))
        {
            try
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var deleteCmd =
                           new OracleCommand("DELETE FROM ERPKCPC", connection))
                    {
                        deleteCmd.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("删除库存数据时出错: " + ex.Message);
            }
        }
    }

    private void InsertInventoryData(InventoryResponse inventoryData)
    {
        using (var connection = new OracleConnection(_connectionString))
        {
            try
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    foreach (var item in inventoryData.Data)
                    {
                        var query =
                            "INSERT INTO ERPKCPC (ITEM_ID, DEPOT_CODE, FQTY, ITEM_NO, ORGID) VALUES (:ITEM_ID, :DEPOT_CODE, :FQTY, :ITEM_NO, :ORGID)";
                        using (var cmd = new OracleCommand(query, connection))
                        {
                            cmd.Parameters.Add(new OracleParameter(":ITEM_ID",
                                item.FmaterialId));
                            cmd.Parameters.Add(
                                new OracleParameter(":DEPOT_CODE",
                                    item.FstockNumber));
                            cmd.Parameters.Add(
                                new OracleParameter(":FQTY", item.Fqty));
                            cmd.Parameters.Add(new OracleParameter(":ITEM_NO",
                                item.FmaterialNumber));
                            cmd.Parameters.Add(
                                new OracleParameter(":ORGID",
                                    item.FstockorgId));

                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                transaction.Rollback();
                                Console.WriteLine("插入库存数据时出错: " + ex.Message);
                                return;
                            }
                        }
                    }

                    transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据库连接出错: " + ex.Message);
            }
        }
    }
}