Merge branch 'master' of http://43.142.96.171:8080/r/~tjx/XB_MES_API

zjh

4 天以前 a27f32ecbfc7390ceb9e3a8d8651c57ada88bfa0

 StandardPda/MES.Service/service/Warehouse/whqohDtoManager.cs

@@ -1,168 +1,184 @@
using Masuit.Tools;
using MES.Service.DB;
using System.Data;
using System.Globalization;
using System.Text;
using MES.Service.Dto.service;
using MES.Service.Modes;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using System.Net.Http;
using System.Globalization;
using System.IO;

namespace MES.Service.service.Warehouse
namespace MES.Service.service.Warehouse;

#region 接口返回数据结构定义

public class WhqohResponse
{
    #region 接口返回数据结构定义
    public class WhqohResponse
    {
        public Header Header { get; set; }
        public List<BodyItem> Body { get; set; }
    }
    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 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 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 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; }
}

    public class whqohDtoManager
    {
        private static IConfiguration configuration = new ConfigurationBuilder()
#endregion

public class whqohDtoManager
{
    private static readonly IConfiguration configuration =
        new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
            .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";
    // Oracle 数据库连接字符串
    private static readonly string connectionString =
        configuration["AppSettings:DataBaseConn"];

        public static async Task<bool> GetWhqoh(WhqohDto mesItemQtRKDto)
    // 接口地址
    private static readonly string apiUrl =
        "http://" + configuration["AppSettings:U9IP"] + "/api/getWhqoh";

    public static async Task<bool> GetWhqoh(WhqohDto mesItemQtRKDto)
    {
        // 1. 请求接口
        var requestBody = new[]
        {
            // 1. 请求接口
            var requestBody = new[]
            new
            {
                new { ItemId = mesItemQtRKDto.itemId, WhId = "", Project = "", LotCode = "" }
            };
                ItemId = mesItemQtRKDto.itemId, WhId = "", Project = "",
                LotCode = ""
            }
        };

            string jsonRequest = JsonConvert.SerializeObject(requestBody);
        var jsonRequest = JsonConvert.SerializeObject(requestBody);

            using var http = new HttpClient();
            var response = await http.PostAsync(apiUrl,
                new StringContent(jsonRequest, Encoding.UTF8, "application/json"));
        using var http = new HttpClient();
        var response = await http.PostAsync(apiUrl,
            new StringContent(jsonRequest, Encoding.UTF8, "application/json"));

            string responseStr = await response.Content.ReadAsStringAsync();
        var responseStr = await response.Content.ReadAsStringAsync();

            // 2. 反序列化接口返回 JSON
            var result = JsonConvert.DeserializeObject<WhqohResponse>(responseStr);
        // 2. 反序列化接口返回 JSON
        var result = JsonConvert.DeserializeObject<WhqohResponse>(responseStr);

            if (result?.Header.Success != true || result.Body == null)
        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))
            {
                Console.WriteLine("接口返回失败: " + result?.Header?.Message);
                return false;
                delCmd.Transaction = tran;
                await delCmd.ExecuteNonQueryAsync();
            }

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

                // 插入新数据
                string insertSql = @"
            // 插入新数据
            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;
            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(), System.Data.ParameterDirection.Input);
                cmd.Parameters.Add(":WhId", OracleDbType.Varchar2, distinctList.Select(x => x.WhId).ToArray(), System.Data.ParameterDirection.Input);
                cmd.Parameters.Add(":UomId", OracleDbType.Varchar2, distinctList.Select(x => x.UomId).ToArray(), System.Data.ParameterDirection.Input);
                cmd.Parameters.Add(":CurrentQty", OracleDbType.Decimal, distinctList.Select(x => x.CurrentQty).ToArray(), System.Data.ParameterDirection.Input);
                cmd.Parameters.Add(":AvailableQty", OracleDbType.Decimal, distinctList.Select(x => x.AvailableQty).ToArray(), System.Data.ParameterDirection.Input);
                cmd.Parameters.Add(":Project", OracleDbType.Varchar2, distinctList.Select(x => x.Project).ToArray(), System.Data.ParameterDirection.Input);
                cmd.Parameters.Add(":LotCode", OracleDbType.Varchar2, distinctList.Select(x => x.LotCode).ToArray(), System.Data.ParameterDirection.Input);
            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);

                int affected = await cmd.ExecuteNonQueryAsync();
                tran.Commit();
            var affected = await cmd.ExecuteNonQueryAsync();
            tran.Commit();

                Console.WriteLine($"批量处理成功，共 {affected} 条数据被插入。");
                return true;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("批量处理失败: " + ex.Message);
                return false;
            }
            Console.WriteLine($"批量处理成功，共 {affected} 条数据被插入。");
            return true;
        }
        catch (Exception ex)
        {
            tran.Rollback();
            Console.WriteLine("批量处理失败: " + ex.Message);
            return false;
        }
    }
}
}