AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/FinishedWarehouse/FqcInspectionService.cs

using System.Text;
using Admin.NET.Plugin.AiDOP.Infrastructure;

namespace Admin.NET.Plugin.AiDOP.FinishedWarehouse;

/// <summary>
/// S7 FQC 检验录入/自动判定（Phase 1E）。读写"活"检验单 qms_qcpp_inspbill / qms_qcpp_inspbillst。
///
/// NUMERIC 项目：逐样本录 j1..j50，自动判定按快照 sx/xx 判每个样本(xx≤v≤sx)；
/// NON_NUMERIC 项目：录本次实际合格数(NonNumericalTypeOK)/不合格数(NonNumericalTypeNG)，无 Ac/Re 规则不自动判 → pd 人工确认。
///
/// 硬边界：① 自动判定只读 inspbillst 快照(sx/xx/lrlx)，绝不回查实时检规 qms_ccjygfzb；
///         ② NON_NUMERIC 无接收规则时不默认 NG>0=FAIL，pd 留人工；③ 保存不起流(仅本 service)，
///         首次正式提交走现有 FqcInspBillFlow.submit-result 惰性 StartFlow。tenant 全程 ResolveTenantOrThrow。
/// 本 service 不做提交/审核/处置(提交复用现有流程端点)。
/// </summary>
[ApiDescriptionSettings(Order = 343, Description = "FQC检验录入")]
[Route("api/S7FqcInspection")]
[NonUnify]
public class FqcInspectionService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

    public FqcInspectionService(ISqlSugarClient db, UserManager userManager)
    {
        _db = db;
        _userManager = userManager;
    }

    private long ResolveTenantOrThrow() => AidopTenantScope.ResolveOrThrow(_userManager);
    private const int SampleCols = 50; // j1..j50

    /// <summary>检验单录入详情（活单 qms_qcpp_inspbill 头 + qms_qcpp_inspbillst 明细，含快照与已录入值）。</summary>
    [DisplayName("检验单录入详情")]
    [HttpGet("detail")]
    public async Task<object?> Detail([FromQuery] long inspBillId)
    {
        if (inspBillId <= 0) return null;
        var tenantId = ResolveTenantOrThrow();
        // 头：检验单本体(i) + LEFT JOIN 报检 qms_fqcbj(q, by lydjbh=FBILLNO AND id=hid) 补齐报检申请信息
        var head = (await _db.Ado.SqlQueryAsync<FqcInspHeadRow>(
            @"SELECT i.id AS Id, i.FBILLNO AS BillNo, i.lydjbh AS SourceBillNo, i.wlmc AS MaterialName, i.ggxh AS Spec,
                     i.scph AS ProductionBatchNo, i.sczld AS ProductionOrderNo, i.jgbh AS SpecNo, i.jgbb AS SpecVersion,
                     IFNULL(i.pd,'') AS Pd, i.hgsl AS QualifiedQty, i.bhgsl AS UnqualifiedQty, i.FBILLSTATUS AS BillStatus,
                     i.sczldsl AS OrderQty,
                     q.wlbm AS MaterialCode, q.sl AS ApplyQty, q.yxj AS Priority, q.jfbh AS FireNo,
                     q.FAPPLYUSER AS Applicant, q.FAPPLYTIME AS ApplyTime, q.jyfzr AS OwnerId, q.FCOMMENT AS Remark
              FROM qms_qcpp_inspbill i
              LEFT JOIN qms_fqcbj q ON q.FBILLNO = i.lydjbh AND q.id = i.hid AND q.tenant_id = i.tenant_id
              WHERE i.id=@id AND i.tenant_id=@t LIMIT 1",
            new SugarParameter("@id", inspBillId), new SugarParameter("@t", tenantId))).FirstOrDefault();
        if (head == null) return null;

        // 检验负责人门：仅本人（jyfzr==当前账号）可进入录入检验（查看走 FQC检验结果列表的只读详情，不受此限）
        if (!string.Equals((head.OwnerId ?? "").Trim(), _userManager.UserId.ToString(), StringComparison.Ordinal))
            throw Oops.Oh("检验负责人与当前账号不一致，无法进入录入检验");

        // 检验负责人 jyfzr 双语义：认领/调配写 SysUser id（数字，可能跨租户），检验流程提交写姓名字符串。
        // 数字 → ClearFilter 反解姓名（解不到回落原值）；非数字 → 本就是姓名，直接展示。
        if (!string.IsNullOrWhiteSpace(head.OwnerId))
        {
            if (long.TryParse(head.OwnerId, out var ownerUid))
            {
                var owner = await _db.Queryable<SysUser>().ClearFilter()
                    .Where(u => u.Id == ownerUid)
                    .Select(u => new { u.RealName, u.Account })
                    .FirstAsync();
                head.OwnerName = owner != null ? (owner.RealName ?? owner.Account) : head.OwnerId;
            }
            else
            {
                head.OwnerName = head.OwnerId;
            }
        }

        var jSel = string.Join(",", Enumerable.Range(1, SampleCols).Select(i => $"j{i}"));
        var raw = await _db.Ado.SqlQueryAsync<dynamic>(
            $@"SELECT id, xh, jyxm, IFNULL(lrlx,'') lrlx, IFNULL(sx,'') sx, IFNULL(xx,'') xx, jsyq, jsbz, cyfa, ybl,
                     IFNULL(pd,'') pd, NonNumericalTypeOK, NonNumericalTypeNG, {jSel}
              FROM qms_qcpp_inspbillst WHERE glid=@g AND tenant_id=@t ORDER BY xh, id",
            new SugarParameter("@g", inspBillId), new SugarParameter("@t", tenantId));

        var lines = new List<Dictionary<string, object?>>();
        foreach (var r in raw)
        {
            var d = (IDictionary<string, object?>)r;
            var samples = new List<string?>();
            for (var i = 1; i <= SampleCols; i++) samples.Add(d.TryGetValue($"j{i}", out var v) ? v?.ToString() : null);
            lines.Add(new Dictionary<string, object?>
            {
                ["id"] = d["id"], ["xh"] = d["xh"], ["jyxm"] = d["jyxm"], ["lrlx"] = d["lrlx"],
                ["sx"] = d["sx"], ["xx"] = d["xx"], ["jsyq"] = d["jsyq"], ["jsbz"] = d["jsbz"], ["cyfa"] = d["cyfa"], ["ybl"] = d["ybl"],
                ["pd"] = d["pd"], ["nonNumericalTypeOK"] = d["NonNumericalTypeOK"], ["nonNumericalTypeNG"] = d["NonNumericalTypeNG"],
                ["samples"] = samples,
            });
        }
        return new { head, lines };
    }

    /// <summary>保存录入（草稿，不起流）：逐项写 j1..j50 / 非数值合格·不合格数 / 行 pd。</summary>
    [DisplayName("保存检验录入")]
    [HttpPost("save-detail")]
    public async Task<object> SaveDetail([FromBody] FqcSaveDetailInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        if (input == null || input.InspBillId <= 0 || input.Lines == null || input.Lines.Count == 0)
            throw Oops.Oh("入参非法");
        // 归属校验
        var owns = await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM qms_qcpp_inspbill WHERE id=@id AND tenant_id=@t",
            new SugarParameter("@id", input.InspBillId), new SugarParameter("@t", tenantId));
        if (owns == 0) throw Oops.Oh("检验单不存在或不属于当前租户");

        // 检验负责人门：仅本人（jyfzr==当前账号）可保存录入（防绕过 UI 直接调接口）
        var saveOwner = await _db.Ado.SqlQuerySingleAsync<string>(
            @"SELECT q.jyfzr FROM qms_qcpp_inspbill i
              JOIN qms_fqcbj q ON q.FBILLNO=i.lydjbh AND q.id=i.hid AND q.tenant_id=i.tenant_id
              WHERE i.id=@id AND i.tenant_id=@t LIMIT 1",
            new SugarParameter("@id", input.InspBillId), new SugarParameter("@t", tenantId));
        if (!string.Equals((saveOwner ?? "").Trim(), _userManager.UserId.ToString(), StringComparison.Ordinal))
            throw Oops.Oh("检验负责人与当前账号不一致，无法录入");

        var tran = await _db.Ado.UseTranAsync(async () =>
        {
            foreach (var ln in input.Lines)
            {
                var sets = new StringBuilder();
                var pars = new List<SugarParameter>();
                var samples = ln.Samples ?? new List<string?>();
                for (var i = 1; i <= SampleCols; i++)
                {
                    var v = (i <= samples.Count) ? samples[i - 1] : null;
                    sets.Append($"j{i}=@j{i},");
                    pars.Add(new SugarParameter($"@j{i}", string.IsNullOrWhiteSpace(v) ? null : v.Trim()));
                }
                // 录入类型由检验员在录入页当场选择（不依赖检规预配置）：落检验单明细快照 lrlx，覆盖检规空值
                var lrlx = ln.Lrlx?.Trim();
                if (!string.IsNullOrEmpty(lrlx) && lrlx != "NUMERIC" && lrlx != "NON_NUMERIC")
                    throw Oops.Oh("录入类型仅支持 数值型(NUMERIC)/非数值型(NON_NUMERIC)");
                sets.Append("NonNumericalTypeOK=@ok, NonNumericalTypeNG=@ng, ybl=@ybl, pd=@pd, lrlx=@lrlx");
                pars.Add(new SugarParameter("@ok", (object?)ln.NonNumericalTypeOK ?? DBNull.Value));
                pars.Add(new SugarParameter("@ng", (object?)ln.NonNumericalTypeNG ?? DBNull.Value));
                pars.Add(new SugarParameter("@ybl", (object?)Nz(ln.Ybl) ?? DBNull.Value));
                pars.Add(new SugarParameter("@pd", (object?)Nz(ln.Pd) ?? DBNull.Value));
                pars.Add(new SugarParameter("@lrlx", string.IsNullOrEmpty(lrlx) ? (object)DBNull.Value : lrlx));
                pars.Add(new SugarParameter("@id", ln.Id));
                pars.Add(new SugarParameter("@g", input.InspBillId));
                pars.Add(new SugarParameter("@t", tenantId));
                await _db.Ado.ExecuteCommandAsync(
                    $"UPDATE qms_qcpp_inspbillst SET {sets} WHERE id=@id AND glid=@g AND tenant_id=@t", pars);
            }
        });
        if (!tran.IsSuccess) throw tran.ErrorException ?? Oops.Oh("保存失败");
        return new { ok = true, saved = input.Lines.Count };
    }

    /// <summary>
    /// 自动判定：只读 inspbillst 快照(sx/xx/lrlx)。NUMERIC 且有上/下限 → 逐样本 xx≤v≤sx 判行 pd(合格/不合格)；
    /// NON_NUMERIC 或无上下限规则 → 跳过(pending)，pd 留人工。局部失败不整单失败。
    /// </summary>
    [DisplayName("自动判定")]
    [HttpPost("auto-judge")]
    public async Task<object> AutoJudge([FromBody] FqcAutoJudgeInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        if (input == null || input.InspBillId <= 0) throw Oops.Oh("检验单 id 非法");

        // 负责人门：仅本人(jyfzr==当前账号)可自动判定（独立公开写口，与 SaveDetail 一致，防越权改他人检验结果）
        var owner = await _db.Ado.SqlQuerySingleAsync<string>(
            @"SELECT q.jyfzr FROM qms_qcpp_inspbill i
              JOIN qms_fqcbj q ON q.FBILLNO=i.lydjbh AND q.id=i.hid AND q.tenant_id=i.tenant_id
              WHERE i.id=@id AND i.tenant_id=@t LIMIT 1",
            new SugarParameter("@id", input.InspBillId), new SugarParameter("@t", tenantId));
        if (!string.Equals((owner ?? "").Trim(), _userManager.UserId.ToString(), StringComparison.Ordinal))
            throw Oops.Oh("检验负责人与当前账号不一致，无法自动判定");

        var jSel = string.Join(",", Enumerable.Range(1, SampleCols).Select(i => $"j{i}"));
        var raw = await _db.Ado.SqlQueryAsync<dynamic>(
            $"SELECT id, jyxm, IFNULL(lrlx,'') lrlx, IFNULL(sx,'') sx, IFNULL(xx,'') xx, {jSel} FROM qms_qcpp_inspbillst WHERE glid=@g AND tenant_id=@t",
            new SugarParameter("@g", input.InspBillId), new SugarParameter("@t", tenantId));
        if (!raw.Any()) throw Oops.Oh("检验单无明细");

        int judged = 0, pending = 0;
        var items = new List<object>();
        var tran = await _db.Ado.UseTranAsync(async () =>
        {
            foreach (var r in raw)
            {
                var d = (IDictionary<string, object?>)r;
                var lrlx = d["lrlx"]?.ToString();
                var sxStr = d["sx"]?.ToString(); var xxStr = d["xx"]?.ToString();
                var hasUp = decimal.TryParse(sxStr, out var up);
                var hasLo = decimal.TryParse(xxStr, out var lo);
                // 硬边界②③：非数值 / 无数值规则 → 不自动判，pd 留人工
                if (lrlx != "NUMERIC" || (!hasUp && !hasLo))
                {
                    pending++;
                    items.Add(new { id = d["id"], jyxm = d["jyxm"]?.ToString(), result = "PENDING", reason = "无结构化数值判定条件，请人工确认" });
                    continue;
                }
                var vals = new List<decimal>();
                var anyEntered = false;
                for (var i = 1; i <= SampleCols; i++)
                {
                    var s = d.TryGetValue($"j{i}", out var v) ? v?.ToString() : null;
                    if (string.IsNullOrWhiteSpace(s)) continue;
                    anyEntered = true;
                    if (decimal.TryParse(s.Trim(), out var num)) vals.Add(num);
                }
                if (!anyEntered) { pending++; items.Add(new { id = d["id"], jyxm = d["jyxm"]?.ToString(), result = "PENDING", reason = "尚未录入样本值" }); continue; }
                var pass = vals.All(v => (!hasLo || v >= lo) && (!hasUp || v <= up));
                var pd = pass ? "合格" : "不合格";
                await _db.Ado.ExecuteCommandAsync("UPDATE qms_qcpp_inspbillst SET pd=@pd WHERE id=@id AND glid=@g AND tenant_id=@t",
                    new SugarParameter("@pd", pd), new SugarParameter("@id", d["id"]), new SugarParameter("@g", input.InspBillId), new SugarParameter("@t", tenantId));
                judged++;
                items.Add(new { id = d["id"], jyxm = d["jyxm"]?.ToString(), result = pd });
            }
        });
        if (!tran.IsSuccess) throw tran.ErrorException ?? Oops.Oh("自动判定失败");
        return new { judged, pending, items, message = pending > 0 ? $"已自动判定 {judged} 项，{pending} 项缺结构化判定条件请人工确认" : $"已自动判定 {judged} 项" };
    }

    private static string? Nz(string? s) => string.IsNullOrWhiteSpace(s) ? null : s.Trim();
}

public class FqcInspHeadRow
{
    public long Id { get; set; }
    public string? BillNo { get; set; }
    public string? SourceBillNo { get; set; }
    public string? MaterialName { get; set; }
    public string? Spec { get; set; }
    public string? ProductionBatchNo { get; set; }
    public string? ProductionOrderNo { get; set; }
    public string? SpecNo { get; set; }
    public string? SpecVersion { get; set; }
    public string? Pd { get; set; }
    public decimal? QualifiedQty { get; set; }
    public decimal? UnqualifiedQty { get; set; }
    public string? BillStatus { get; set; }
    // ── 报检申请信息（LEFT JOIN qms_fqcbj）──
    public decimal? OrderQty { get; set; }
    public string? MaterialCode { get; set; }
    public decimal? ApplyQty { get; set; }
    public string? Priority { get; set; }
    public string? FireNo { get; set; }
    public string? Applicant { get; set; }
    public string? ApplyTime { get; set; }
    public string? OwnerId { get; set; }
    public string? OwnerName { get; set; }
    public string? Remark { get; set; }
}

public class FqcSaveDetailInput
{
    public long InspBillId { get; set; }
    public List<FqcSaveDetailLine> Lines { get; set; } = new();
}

public class FqcSaveDetailLine
{
    public long Id { get; set; }
    /// <summary>录入类型（检验员在录入页当场选择）：NUMERIC 数值型 / NON_NUMERIC 非数值型 / 空=未定</summary>
    public string? Lrlx { get; set; }
    public List<string?>? Samples { get; set; }
    public long? NonNumericalTypeOK { get; set; }
    public long? NonNumericalTypeNG { get; set; }
    public string? Ybl { get; set; }
    public string? Pd { get; set; }
}

public class FqcAutoJudgeInput
{
    public long InspBillId { get; set; }
}