王明 / Food-Labeling-Management-Platform

  using System.Globalization;
  using System.Text.Json;

  using FoodLabeling.Application.Contracts.Dtos.Dashboard;
  using FoodLabeling.Application.Contracts.IServices;

  using FoodLabeling.Application.Helpers;

  using FoodLabeling.Application.Services.DbModels;
  using FoodLabeling.Domain.Entities;

  using SqlSugar;

  using Volo.Abp.Application.Services;
  using Yi.Framework.Rbac.Domain.Entities;
  using Yi.Framework.SqlSugarCore.Abstractions;
  
  namespace FoodLabeling.Application.Services;
  
  /// <summary>
  /// Dashboard 统计服务（美国版）
  /// </summary>
  public class DashboardAppService : ApplicationService, IDashboardAppService
  {
      private readonly ISqlSugarDbContext _dbContext;
      private readonly ISqlSugarRepository<LocationAggregateRoot, Guid> _locationRepository;
      private readonly ISqlSugarRepository<UserAggregateRoot, Guid> _userRepository;
  
      public DashboardAppService(
          ISqlSugarDbContext dbContext,
          ISqlSugarRepository<LocationAggregateRoot, Guid> locationRepository,
          ISqlSugarRepository<UserAggregateRoot, Guid> userRepository)
      {
          _dbContext = dbContext;
          _locationRepository = locationRepository;
          _userRepository = userRepository;
      }
  
      /// <inheritdoc />
      public async Task<DashboardOverviewOutputDto> GetOverviewAsync()
      {
          var now = DateTime.Now;
          var todayStart = now.Date;
          var tomorrowStart = todayStart.AddDays(1);
          var yesterdayStart = todayStart.AddDays(-1);
          var weekStart = todayStart.AddDays(-6);
          var prevWeekStart = todayStart.AddDays(-13);
  

          var db = _dbContext.SqlSugarClient;
          var scopeLocationIds = await DashboardScopeHelper.ResolveDashboardLocationIdsAsync(CurrentUser, _dbContext);
          var platformAdminUserIds = await DashboardScopeHelper.GetPlatformAdminUserIdStringsAsync(db);
  
          var printTodayQuery = DashboardScopeHelper.ApplyPrintTaskLocationScope(
              db.Queryable<FlLabelPrintTaskDbEntity>(), scopeLocationIds);
          var printedToday = await printTodayQuery

              .CountAsync(x => x.CreationTime >= todayStart && x.CreationTime < tomorrowStart);
  

          var printedYesterday = await DashboardScopeHelper.ApplyPrintTaskLocationScope(
                  db.Queryable<FlLabelPrintTaskDbEntity>(), scopeLocationIds)

              .CountAsync(x => x.CreationTime >= yesterdayStart && x.CreationTime < todayStart);
  
          var activeTemplates = await _dbContext.SqlSugarClient.Queryable<FlLabelTemplateDbEntity>()
              .CountAsync(x => !x.IsDeleted && x.State);
          var activeTemplatesPrevWeek = await _dbContext.SqlSugarClient.Queryable<FlLabelTemplateDbEntity>()
              .CountAsync(x => !x.IsDeleted && x.State && x.CreationTime < weekStart);
  

          var activeUsers = await DashboardScopeHelper.CountScopedTeamMembersAsync(
              db, scopeLocationIds, platformAdminUserIds, activeOnly: true, createdBefore: null);
          var activeUsersPrevWeek = await DashboardScopeHelper.CountScopedTeamMembersAsync(
              db, scopeLocationIds, platformAdminUserIds, activeOnly: true, createdBefore: weekStart);

          var locations = await DashboardScopeHelper.CountScopedLocationsAsync(db, scopeLocationIds, null);
          var locationsPrevWeek = await DashboardScopeHelper.CountScopedLocationsAsync(
              db, scopeLocationIds, weekStart);

          var people = await DashboardScopeHelper.CountScopedTeamMembersAsync(
              db, scopeLocationIds, platformAdminUserIds, activeOnly: false, createdBefore: null);
          var peoplePrevWeek = await DashboardScopeHelper.CountScopedTeamMembersAsync(
              db, scopeLocationIds, platformAdminUserIds, activeOnly: false, createdBefore: weekStart);

          var productQuery = db.Queryable<FlProductDbEntity>().Where(x => !x.IsDeleted);
          if (scopeLocationIds is not null)
          {
              if (scopeLocationIds.Count == 0)
              {
                  productQuery = productQuery.Where(_ => false);
              }
              else
              {
                  productQuery = productQuery.Where(p =>
                      SqlFunc.Subqueryable<FlLocationProductDbEntity>()
                          .Where(lp => lp.ProductId == p.Id && scopeLocationIds.Contains(lp.LocationId))
                          .Any());
              }
          }
  
          var products = await productQuery.CountAsync();

          // fl_product 当前实体未映射 CreationTime，无法按时间切分对比，先回退为同口径总量对比
          var productsPrevWeek = products;
  

          var weeklyPrintQuery = DashboardScopeHelper.ApplyPrintTaskLocationScope(
              db.Queryable<FlLabelPrintTaskDbEntity>(), scopeLocationIds);
          var weeklyPrintRaw = await weeklyPrintQuery

              .Where(x => x.CreationTime >= weekStart && x.CreationTime < tomorrowStart)
              .Select(x => x.CreationTime)
              .ToListAsync();
  
          var weeklyDict = weeklyPrintRaw
              .GroupBy(x => x.Date)
              .ToDictionary(g => g.Key, g => g.Count());
  
          var weeklyTrend = Enumerable.Range(0, 7)
              .Select(i =>
              {
                  var d = weekStart.AddDays(i).Date;
                  return new DashboardDailyTrendPointDto
                  {
                      Date = d.ToString("yyyy-MM-dd"),
                      Value = weeklyDict.TryGetValue(d, out var v) ? v : 0
                  };
              })
              .ToList();
  
          var categories = await _dbContext.SqlSugarClient.Queryable<FlLabelCategoryDbEntity>()
              .Where(x => !x.IsDeleted && x.State)
              .ToListAsync();
  
          var labelCategoryIds = categories.Select(x => x.Id).ToList();
          var labelRows = labelCategoryIds.Count == 0
              ? new List<string?>()
              : await _dbContext.SqlSugarClient.Queryable<FlLabelDbEntity>()
                  .Where(x => !x.IsDeleted && x.LabelCategoryId != null && labelCategoryIds.Contains(x.LabelCategoryId))
                  .Select(x => x.LabelCategoryId)
                  .ToListAsync();
  
          var labelCountByCategory = labelRows
              .Where(x => !string.IsNullOrWhiteSpace(x))
              .GroupBy(x => x!)
              .ToDictionary(g => g.Key, g => g.Count());
  
          var categoryDistributionTotal = labelCountByCategory.Values.Sum();
          var categoryDistribution = categories
              .Select(c =>
              {
                  var count = labelCountByCategory.TryGetValue(c.Id, out var v) ? v : 0;
                  var ratio = categoryDistributionTotal == 0
                      ? 0m
                      : Math.Round(count * 100m / categoryDistributionTotal, 2);
                  return new DashboardCategoryDistributionDto
                  {
                      CategoryId = c.Id,
                      CategoryName = c.CategoryName,
                      Count = count,
                      Ratio = ratio
                  };
              })
              .Where(x => x.Count > 0)
              .OrderByDescending(x => x.Count)
              .ThenBy(x => x.CategoryName)
              .ToList();
  

          const int recentLabelsTake = 10;

          var recentBaseQuery = DashboardScopeHelper.ApplyPrintTaskLocationScope(
              db.Queryable<FlLabelPrintTaskDbEntity>(), scopeLocationIds);
          var recentRaw = await recentBaseQuery

              .LeftJoin<FlLabelDbEntity>((t, l) => t.LabelId == l.Id)
              .LeftJoin<FlProductDbEntity>((t, l, p) => t.ProductId == p.Id)
              .LeftJoin<FlLabelTemplateDbEntity>((t, l, p, tpl) => t.TemplateId == tpl.Id)
              .OrderBy((t, l, p, tpl) => SqlFunc.IsNull(t.PrintedAt, t.CreationTime), OrderByType.Desc)
              .Take(recentLabelsTake)
              .Select((t, l, p, tpl) => new
              {
                  t.Id,
                  LabelCode = l.LabelCode,
                  LabelName = l.LabelName,
                  ProductName = p.ProductName,
                  tpl.Width,
                  tpl.Height,
                  tpl.Unit,
                  t.PrintInputJson,
                  PrintedAt = SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                  t.CreatedBy
              })
              .ToListAsync();
  
          var recentUserIds = recentRaw
              .Select(x => x.CreatedBy)
              .Where(x => !string.IsNullOrWhiteSpace(x))
              .Select(x => x!.Trim())
              .Distinct()
              .Select(x => Guid.TryParse(x, out var g) ? g : (Guid?)null)
              .Where(x => x.HasValue)
              .Select(x => x!.Value)
              .ToList();
  
          var recentUserMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
          if (recentUserIds.Count > 0)
          {
              var users = await _userRepository._DbQueryable
                  .Where(u => !u.IsDeleted && recentUserIds.Contains(u.Id))
                  .Select(u => new { u.Id, u.Name, u.UserName })
                  .ToListAsync();
              foreach (var u in users)
              {
                  var display = !string.IsNullOrWhiteSpace(u.Name) ? u.Name.Trim() : u.UserName.Trim();
                  recentUserMap[u.Id.ToString()] = string.IsNullOrWhiteSpace(display) ? "无" : display;
              }
          }
  
          var recentLabels = recentRaw.Select(x =>
          {
              var displayName = !string.IsNullOrWhiteSpace(x.ProductName)
                  ? x.ProductName.Trim()
                  : (string.IsNullOrWhiteSpace(x.LabelName) ? "无" : x.LabelName.Trim());
              var printedAt = x.PrintedAt ?? DateTime.MinValue;
              var status = ResolveRecentLabelStatus(x.PrintInputJson);
              return new DashboardRecentLabelItemDto
              {
                  TaskId = x.Id,
                  LabelCode = string.IsNullOrWhiteSpace(x.LabelCode) ? "无" : x.LabelCode.Trim(),
                  DisplayName = displayName,
                  PrintedByUserId = x.CreatedBy?.Trim(),
                  PrintedByName = ResolveRecentUserName(recentUserMap, x.CreatedBy),
                  PrintedAt = printedAt,
                  Status = status,
                  LabelTypeBadge = FormatTemplateBadge(x.Width, x.Height, x.Unit)
              };
          }).ToList();
  

          var labelsPrintedTodayCard = BuildMetricCard("labelsPrintedToday", "Labels Printed Today", printedToday, printedYesterday);
          var activeTemplatesCard = BuildMetricCard("activeTemplates", "Active Templates", activeTemplates, activeTemplatesPrevWeek);
          var activeUsersCard = BuildMetricCard("activeUsers", "Active Users", activeUsers, activeUsersPrevWeek);
          var locationsCard = BuildMetricCard("locations", "Locations", locations, locationsPrevWeek);
          var peopleCard = BuildMetricCard("people", "People", people, peoplePrevWeek);
          var productsCard = BuildMetricCard("products", "Products", products, productsPrevWeek);
  
          var output = new DashboardOverviewOutputDto
          {
              LabelsPrintedToday = labelsPrintedTodayCard,
              ActiveTemplates = activeTemplatesCard,
              ActiveUsers = activeUsersCard,
              Locations = locationsCard,
              People = peopleCard,
              Products = productsCard,
              MetricCards = new List<DashboardMetricCardDto>
              {
                  labelsPrintedTodayCard,
                  activeTemplatesCard,
                  activeUsersCard,
                  locationsCard,
                  peopleCard,
                  productsCard
              },
              WeeklyPrintVolume = weeklyTrend,
              CategoryDistribution = categoryDistribution,
              CategoryDistributionTotal = categoryDistributionTotal,
              ByCategory = categoryDistribution,
              ByCategoryTotal = categoryDistributionTotal,

              RecentLabels = recentLabels,

              GeneratedAt = now
          };
  
          return output;
      }
  

      private static string ResolveRecentUserName(Dictionary<string, string> map, string? createdBy)
      {
          if (string.IsNullOrWhiteSpace(createdBy))
          {
              return "无";
          }
  
          return map.TryGetValue(createdBy.Trim(), out var n) ? n : "无";
      }
  
      /// <summary>
      /// 依据 PrintInputJson 中的保质期字段与「当前日期」比较得到 active/expired。
      /// </summary>
      private static string ResolveRecentLabelStatus(string? printInputJson)
      {
          if (!TryParseExpiryDate(printInputJson, out var expiryDate))
          {
              return "active";
          }
  
          var today = DateTime.Now.Date;
          return expiryDate.Date < today ? "expired" : "active";
      }
  
      private static bool TryParseExpiryDate(string? printInputJson, out DateTime expiryDate)
      {
          expiryDate = default;
          if (string.IsNullOrWhiteSpace(printInputJson))
          {
              return false;
          }
  
          try
          {
              using var doc = JsonDocument.Parse(printInputJson);
              if (doc.RootElement.ValueKind != JsonValueKind.Object)
              {
                  return false;
              }
  
              foreach (var prop in doc.RootElement.EnumerateObject())
              {
                  var key = prop.Name.Trim();
                  if (!key.Equals("expiryDate", StringComparison.OrdinalIgnoreCase) &&
                      !key.Equals("expiry", StringComparison.OrdinalIgnoreCase) &&
                      !key.Equals("expirationDate", StringComparison.OrdinalIgnoreCase))
                  {
                      continue;
                  }
  
                  var v = prop.Value;
                  if (v.ValueKind == JsonValueKind.String)
                  {
                      var s = v.GetString();
                      if (!string.IsNullOrWhiteSpace(s) &&
                          DateTime.TryParse(s, CultureInfo.InvariantCulture,
                              DateTimeStyles.AssumeLocal | DateTimeStyles.AllowWhiteSpaces, out var dt))
                      {
                          expiryDate = dt;
                          return true;
                      }
                  }
                  else if (v.ValueKind == JsonValueKind.Number && v.TryGetInt64(out var unix))
                  {
                      expiryDate = DateTimeOffset.FromUnixTimeSeconds(unix).LocalDateTime;
                      return true;
                  }
              }
          }
          catch
          {
              return false;
          }
  
          return false;
      }
  
      private static string FormatTemplateBadge(decimal w, decimal h, string? unit)
      {
          var u = (unit ?? "inch").Trim().ToLowerInvariant();
          var ws = w.ToString(CultureInfo.InvariantCulture);
          var hs = h.ToString(CultureInfo.InvariantCulture);
          return u is "inch" or "in"
              ? $"{ws}\"x{hs}\""
              : $"{ws}x{hs}{u}";
      }
  

      private static DashboardMetricCardDto BuildMetricCard(string key, string title, int value, int previousValue)
      {
          var changeValue = value - previousValue;
          var changeRate = previousValue <= 0
              ? (value > 0 ? 100m : 0m)
              : Math.Round(changeValue * 100m / previousValue, 2);
  
          return new DashboardMetricCardDto
          {
              Key = key,
              Title = title,
              Value = value,
              PreviousValue = previousValue,
              ChangeValue = changeValue,
              ChangeRate = changeRate
          };
      }
  }