using MES.Service.Dto.webApi.RealTimeInventory;
|
using Microsoft.AspNetCore.Mvc;
|
using Newtonsoft.Json;
|
using Newtonsoft.Json.Linq;
|
using Oracle.ManagedDataAccess.Client;
|
|
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
|
"Administrator", // 用户名
|
"Lts88666*", // 密码
|
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 // 每页记录数
|
};
|
|
// 设置库存查询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())
|
{
|
while (reader.Read())
|
depotCodes.Add(reader.GetString(0));
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine("获取仓库代码时出错: " + ex.Message);
|
}
|
}
|
|
return depotCodes;
|
}
|
|
/// <summary>
|
/// 清空ERPKCPC表中的现有库存数据
|
/// 使用事务确保操作的原子性
|
/// </summary>
|
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);
|
}
|
}
|
}
|
|
/// <summary>
|
/// 将库存数据插入到ERPKCPC表中
|
/// 使用事务确保所有数据要么全部插入成功，要么全部失败
|
/// </summary>
|
/// <param name="inventoryData">库存数据响应对象</param>
|
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)
|
{
|
// 插入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))
|
{
|
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));
|
cmd.Parameters.Add(new OracleParameter(":Fstockstatusnumber",item.Fstockstatusnumber));
|
cmd.Parameters.Add(new OracleParameter(":Fstockstatusname",item.Fstockstatusname));
|
|
try
|
{
|
// 执行插入命令
|
cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
// 出错时回滚事务
|
transaction.Rollback();
|
Console.WriteLine("插入库存数据时出错: " + ex.Message);
|
return;
|
}
|
}
|
}
|
|
// 所有数据插入成功，提交事务
|
transaction.Commit();
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine("数据库连接出错: " + ex.Message);
|
}
|
}
|
}
|
}
|
