using System.Data;
|
using System.Globalization;
|
using System.Text;
|
using MES.Service.Dto.service;
|
using Microsoft.Extensions.Configuration;
|
using Newtonsoft.Json;
|
using Oracle.ManagedDataAccess.Client;
|
|
namespace MES.Service.service.Warehouse;
|
|
#region 接口返回数据结构定义
|
|
public class WhqohResponse
|
{
|
public Header Header { get; set; }
|
public List<BodyItem> Body { get; set; }
|
}
|
|
public class Header
|
{
|
public int Code { get; set; }
|
public bool Success { get; set; }
|
public string Message { get; set; }
|
}
|
|
public class BodyItem
|
{
|
public string ItemId { get; set; }
|
public List<Detail> Detail { get; set; }
|
}
|
|
public class Detail
|
{
|
public string WhId { get; set; }
|
public string UomId { get; set; }
|
public string CurrentQuantity { get; set; }
|
public string AvailableQuantity { get; set; }
|
public string Project { get; set; }
|
public string LotCode { get; set; }
|
}
|
|
#endregion
|
|
public class whqohDtoManager
|
{
|
private static readonly IConfiguration configuration =
|
new ConfigurationBuilder()
|
.SetBasePath(Directory.GetCurrentDirectory())
|
.AddJsonFile("appsettings.json", true, true)
|
.Build();
|
|
// Oracle 数据库连接字符串
|
private static readonly string connectionString =
|
configuration["AppSettings:DataBaseConn"];
|
|
// 接口地址
|
private static readonly string apiUrl =
|
"http://" + configuration["AppSettings:U9IP"] + "/api/getWhqoh";
|
|
public static async Task<bool> GetWhqoh(WhqohDto mesItemQtRKDto)
|
{
|
// 1. 请求接口
|
var requestBody = new[]
|
{
|
new
|
{
|
ItemId = mesItemQtRKDto.itemId, WhId = "", Project = "",
|
LotCode = ""
|
}
|
};
|
|
var jsonRequest = JsonConvert.SerializeObject(requestBody);
|
|
using var http = new HttpClient();
|
var response = await http.PostAsync(apiUrl,
|
new StringContent(jsonRequest, Encoding.UTF8, "application/json"));
|
|
var responseStr = await response.Content.ReadAsStringAsync();
|
|
// 2. 反序列化接口返回 JSON
|
var result = JsonConvert.DeserializeObject<WhqohResponse>(responseStr);
|
|
if (result?.Header.Success != true || result.Body == null)
|
{
|
Console.WriteLine("接口返回失败: " + result?.Header?.Message);
|
return false;
|
}
|
|
// 3. 扁平化并去重
|
var records =
|
new HashSet<(string ItemId, string WhId, string UomId, string
|
Project, string LotCode)>();
|
var distinctList =
|
new List<(string ItemId, string WhId, string UomId, decimal
|
CurrentQty, decimal AvailableQty, string Project, string LotCode
|
)>();
|
|
foreach (var body in result.Body)
|
foreach (var detail in body.Detail)
|
{
|
var key = (body.ItemId, detail.WhId, detail.UomId,
|
detail.Project ?? "", detail.LotCode ?? "");
|
if (records.Add(key)) // 去重
|
distinctList.Add((
|
body.ItemId,
|
detail.WhId,
|
detail.UomId,
|
decimal.Parse(detail.CurrentQuantity,
|
CultureInfo.InvariantCulture),
|
decimal.Parse(detail.AvailableQuantity,
|
CultureInfo.InvariantCulture),
|
detail.Project ?? "",
|
detail.LotCode ?? ""
|
));
|
}
|
|
// 4. 先删后插
|
using var conn = new OracleConnection(connectionString);
|
await conn.OpenAsync();
|
using var tran = conn.BeginTransaction();
|
|
try
|
{
|
var deleteSql = "";
|
// 删除所有旧数据（按需求调整范围，可以加 WHERE）
|
if (mesItemQtRKDto.itemId == "" || mesItemQtRKDto.itemId == null)
|
deleteSql = "DELETE FROM WHQOH_DETAIL";
|
else
|
deleteSql = "DELETE FROM WHQOH_DETAIL where ITEM_ID='" +
|
mesItemQtRKDto.itemId + "'";
|
|
using (var delCmd = new OracleCommand(deleteSql, conn))
|
{
|
delCmd.Transaction = tran;
|
await delCmd.ExecuteNonQueryAsync();
|
}
|
|
// 插入新数据
|
var insertSql = @"
|
INSERT INTO WHQOH_DETAIL
|
(ID, ITEM_ID, WH_ID, UOM_ID, CURRENT_QUANTITY, AVAILABLE_QUANTITY, PROJECT_CODE, LOT_CODE)
|
VALUES
|
(SEQ_WHQOH_DETAIL.NEXTVAL, :ItemId, :WhId, :UomId, :CurrentQty, :AvailableQty, :Project, :LotCode)";
|
|
using var cmd = new OracleCommand(insertSql, conn);
|
cmd.Transaction = tran;
|
cmd.ArrayBindCount = distinctList.Count;
|
|
cmd.Parameters.Add(":ItemId", OracleDbType.Varchar2,
|
distinctList.Select(x => x.ItemId).ToArray(),
|
ParameterDirection.Input);
|
cmd.Parameters.Add(":WhId", OracleDbType.Varchar2,
|
distinctList.Select(x => x.WhId).ToArray(),
|
ParameterDirection.Input);
|
cmd.Parameters.Add(":UomId", OracleDbType.Varchar2,
|
distinctList.Select(x => x.UomId).ToArray(),
|
ParameterDirection.Input);
|
cmd.Parameters.Add(":CurrentQty", OracleDbType.Decimal,
|
distinctList.Select(x => x.CurrentQty).ToArray(),
|
ParameterDirection.Input);
|
cmd.Parameters.Add(":AvailableQty", OracleDbType.Decimal,
|
distinctList.Select(x => x.AvailableQty).ToArray(),
|
ParameterDirection.Input);
|
cmd.Parameters.Add(":Project", OracleDbType.Varchar2,
|
distinctList.Select(x => x.Project).ToArray(),
|
ParameterDirection.Input);
|
cmd.Parameters.Add(":LotCode", OracleDbType.Varchar2,
|
distinctList.Select(x => x.LotCode).ToArray(),
|
ParameterDirection.Input);
|
|
var affected = await cmd.ExecuteNonQueryAsync();
|
tran.Commit();
|
|
Console.WriteLine($"批量处理成功，共 {affected} 条数据被插入。");
|
return true;
|
}
|
catch (Exception ex)
|
{
|
tran.Rollback();
|
Console.WriteLine("批量处理失败: " + ex.Message);
|
return false;
|
}
|
}
|
}
|
