AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Controllers/AidopKanbanController.cs

using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Controllers;

[ApiController]
[Route("api/[controller]")]
[AllowAnonymous]
[NonUnify]
public partial class AidopKanbanController : ControllerBase
{
    private readonly ISqlSugarClient _db;

    public AidopKanbanController(ISqlSugarClient db)
    {
        _db = db;
    }

    [HttpGet("home-l1")]
    public async Task<IActionResult> GetHomeL1([FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        const string sql = """
SELECT v.module_code AS ModuleCode, v.metric_code AS MetricCode,
       v.metric_value AS MetricValue, v.target_value AS TargetValue,
       v.status_color AS StatusColor, v.trend_flag AS TrendFlag,
       k.Direction, k.YellowThreshold, k.RedThreshold
FROM ado_s9_kpi_value_l1_day v
LEFT JOIN ado_smart_ops_kpi_master k
  ON k.MetricCode = v.metric_code COLLATE utf8mb4_general_ci AND k.TenantId = v.tenant_id
WHERE v.tenant_id=@tenantId AND v.factory_id=@factoryId AND v.is_deleted=0
  AND v.biz_date = (SELECT MAX(biz_date) FROM ado_s9_kpi_value_l1_day
                    WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0)
ORDER BY v.module_code
""";
        var raw = await _db.Ado.SqlQueryAsync<HomeL1RawDto>(sql, new { tenantId, factoryId });
        var rows = raw.Select(r => new HomeL1Dto
        {
            ModuleCode = r.ModuleCode,
            MetricCode = r.MetricCode,
            MetricValue = r.MetricValue,
            TargetValue = r.TargetValue,
            TrendFlag = r.TrendFlag,
            StatusColor = AidopS4KpiMerge.AchievementLevel(
                r.MetricValue, r.TargetValue,
                r.Direction ?? "higher_is_better",
                r.YellowThreshold, r.RedThreshold)
        }).ToList();
        return Ok(rows);
    }

    [HttpGet("s8-alerts")]
    public async Task<IActionResult> GetS8Alerts([FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        const string sql = """
SELECT DATE_FORMAT(alert_time, '%H:%i') AS Time,
       IFNULL(SUBSTRING_INDEX(metric_code, '_', 1), 'S8') AS Module,
       IFNULL(message, '异常告警') AS Message,
       IFNULL(level_code, 'medium') AS LevelCode
FROM ado_s8_alert_record
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
ORDER BY alert_time DESC
LIMIT 6
""";
        var rows = await _db.Ado.SqlQueryAsync<S8AlertDto>(sql, new { tenantId, factoryId });
        return Ok(rows.Select(x => new
        {
            time = x.Time,
            module = x.Module,
            message = x.Message,
            level = x.LevelCode,
            levelLabel = x.LevelCode switch
            {
                "critical" => "严重",
                "high" => "高",
                "medium" => "中",
                _ => "一般"
            }
        }));
    }

    [HttpGet("module-detail")]
    public async Task<IActionResult> GetModuleDetail([FromQuery] string moduleCode = "S1", [FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        moduleCode = string.IsNullOrWhiteSpace(moduleCode) ? "S1" : moduleCode.ToUpperInvariant();
        const string l2Sql = """
SELECT module_code AS ModuleCode, metric_code AS MetricCode, metric_code AS MetricName, metric_value AS MetricValue,
       target_value AS TargetValue, status_color AS StatusColor, trend_flag AS TrendFlag, NULL AS StatDate
FROM ado_s9_kpi_value_l2_day
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0 AND module_code=@moduleCode
ORDER BY id DESC, metric_code
LIMIT 30
""";
        const string l3Sql = """
SELECT module_code AS ModuleCode, metric_code AS MetricCode, metric_code AS MetricName, metric_value AS MetricValue,
       target_value AS TargetValue, status_color AS StatusColor, trend_flag AS TrendFlag, NULL AS StatDate
FROM ado_s9_kpi_value_l3_day
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0 AND module_code=@moduleCode
ORDER BY id DESC, metric_code
LIMIT 60
""";
        const string alertSql = """
SELECT DATE_FORMAT(alert_time, '%H:%i:%s') AS Time,
       IFNULL(level_code, 'medium') AS LevelCode,
       IFNULL(message, '异常告警') AS Message
FROM ado_s8_alert_record
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
  AND (UPPER(IFNULL(module_code,''))=@moduleCode OR UPPER(IFNULL(SUBSTRING_INDEX(metric_code, '_', 1), ''))=@moduleCode)
ORDER BY alert_time DESC
LIMIT 20
""";
        const string l2FallbackSql = """
SELECT '' AS ModuleCode, metric_code AS MetricCode, metric_code AS MetricName, metric_value AS MetricValue,
       target_value AS TargetValue, status_color AS StatusColor, trend_flag AS TrendFlag, NULL AS StatDate
FROM ado_s9_kpi_value_l2_day
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
ORDER BY id DESC
LIMIT 30
""";
        const string l3FallbackSql = """
SELECT '' AS ModuleCode, metric_code AS MetricCode, metric_code AS MetricName, metric_value AS MetricValue,
       target_value AS TargetValue, status_color AS StatusColor, trend_flag AS TrendFlag, NULL AS StatDate
FROM ado_s9_kpi_value_l3_day
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
ORDER BY id DESC
LIMIT 60
""";
        var l2 = new List<KpiDetailDto>();
        var l3 = new List<KpiDetailDto>();
        var alerts = new List<S8AlertDto>();
        try { l2 = await _db.Ado.SqlQueryAsync<KpiDetailDto>(l2Sql, new { moduleCode, tenantId, factoryId }); }
        catch { l2 = await _db.Ado.SqlQueryAsync<KpiDetailDto>(l2FallbackSql, new { tenantId, factoryId }); }
        try { l3 = await _db.Ado.SqlQueryAsync<KpiDetailDto>(l3Sql, new { moduleCode, tenantId, factoryId }); }
        catch { l3 = await _db.Ado.SqlQueryAsync<KpiDetailDto>(l3FallbackSql, new { tenantId, factoryId }); }
        try { alerts = await _db.Ado.SqlQueryAsync<S8AlertDto>(alertSql, new { moduleCode, tenantId, factoryId }); }
        catch { alerts = new List<S8AlertDto>(); }
        return Ok(new
        {
            moduleCode,
            l2,
            l3,
            alerts = alerts.Select(x => new
            {
                time = x.Time,
                message = x.Message,
                level = x.LevelCode
            })
        });
    }

    /// <summary>
    /// 智慧运营看板基础查询下拉：产品、订单号、产线（来自 Demo 业务表；无租户列时忽略 tenant/factory）。
    /// </summary>
    [HttpGet("smart-ops-filter-options")]
    public async Task<IActionResult> GetSmartOpsFilterOptions([FromQuery] long factoryId = 1)
    {
        _ = factoryId;

        var products = new HashSet<string>(StringComparer.Ordinal);
        var orderNos = new HashSet<string>(StringComparer.Ordinal);
        var lines = new HashSet<string>(StringComparer.Ordinal);

        try
        {
            var op = await _db.Queryable<AdoOrder>()
                .Where(x => x.Product != null && x.Product != "")
                .Select(x => x.Product)
                .Distinct()
                .ToListAsync();
            foreach (var s in op.Where(s => !string.IsNullOrWhiteSpace(s)))
                products.Add(s.Trim());
        }
        catch
        {
            // ignored
        }

        try
        {
            var on = await _db.Queryable<AdoOrder>()
                .Where(x => x.OrderNo != null && x.OrderNo != "")
                .Select(x => x.OrderNo)
                .Distinct()
                .ToListAsync();
            foreach (var s in on.Where(s => !string.IsNullOrWhiteSpace(s)))
                orderNos.Add(s.Trim());
        }
        catch
        {
            // ignored
        }

        try
        {
            var woP = await _db.Queryable<AdoWorkOrder>()
                .Where(x => x.Product != null && x.Product != "")
                .Select(x => x.Product)
                .Distinct()
                .ToListAsync();
            foreach (var s in woP.Where(s => !string.IsNullOrWhiteSpace(s)))
                products.Add(s.Trim());

            var wc = await _db.Queryable<AdoWorkOrder>()
                .Where(x => x.WorkCenter != null && x.WorkCenter != "")
                .Select(x => x.WorkCenter)
                .Distinct()
                .ToListAsync();
            foreach (var s in wc.Where(s => !string.IsNullOrWhiteSpace(s)))
                lines.Add(s.Trim());
        }
        catch
        {
            // ignored
        }

        try
        {
            var pn = await _db.Queryable<AdoPlan>()
                .Where(x => x.ProductName != null && x.ProductName != "")
                .Select(x => x.ProductName)
                .Distinct()
                .ToListAsync();
            foreach (var s in pn.Where(s => !string.IsNullOrWhiteSpace(s)))
                products.Add(s.Trim());
        }
        catch
        {
            // ignored
        }

        return Ok(new
        {
            products = products.OrderBy(x => x, StringComparer.Ordinal).ToList(),
            orderNos = orderNos.OrderBy(x => x, StringComparer.Ordinal).ToList(),
            productionLines = lines.OrderBy(x => x, StringComparer.Ordinal).ToList()
        });
    }

    private sealed class HomeL1Dto
    {
        public string? ModuleCode { get; set; }
        public string? MetricCode { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
        public string? StatusColor { get; set; }
        public string? TrendFlag { get; set; }
    }

    private sealed class HomeL1RawDto
    {
        public string? ModuleCode { get; set; }
        public string? MetricCode { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
        public string? StatusColor { get; set; }
        public string? TrendFlag { get; set; }
        public string? Direction { get; set; }
        public decimal? YellowThreshold { get; set; }
        public decimal? RedThreshold { get; set; }
    }

    private sealed class S8AlertDto
    {
        public string? Time { get; set; }
        public string? Module { get; set; }
        public string? Message { get; set; }
        public string? LevelCode { get; set; }
    }

    private sealed class KpiDetailDto
    {
        public string? ModuleCode { get; set; }
        public string? MetricCode { get; set; }
        public string? MetricName { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
        public string? StatusColor { get; set; }
        public string? TrendFlag { get; set; }
        public DateTime? StatDate { get; set; }
    }
}