using Microsoft.AspNetCore.Mvc;
|
using SqlSugar;
|
using WIDESEA_Core;
|
using WIDESEA_Model.Models;
|
|
namespace WIDESEA_WMSServer.Controllers.Dashboard
|
{
|
/// <summary>
|
/// 仪表盘
|
/// </summary>
|
[Route("api/Dashboard")]
|
[ApiController]
|
public class DashboardController : ControllerBase
|
{
|
private readonly ISqlSugarClient _db;
|
|
public DashboardController(ISqlSugarClient db)
|
{
|
_db = db;
|
}
|
|
/// <summary>
|
/// 总览数据
|
/// </summary>
|
[HttpGet("Overview")]
|
public async Task<WebResponseContent> Overview()
|
{
|
try
|
{
|
var today = DateTime.Today;
|
var firstDayOfMonth = new DateTime(today.Year, today.Month, 1);
|
|
// 今日入库数
|
var todayInbound = await _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= today && t.TaskType >= 500 && t.TaskType < 600)
|
.CountAsync();
|
|
// 今日出库数
|
var todayOutbound = await _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= today && t.TaskType >= 100 && t.TaskType < 200)
|
.CountAsync();
|
|
// 本月入库数
|
var monthInbound = await _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= firstDayOfMonth && t.TaskType >= 500 && t.TaskType < 600)
|
.CountAsync();
|
|
// 本月出库数
|
var monthOutbound = await _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= firstDayOfMonth && t.TaskType >= 100 && t.TaskType < 200)
|
.CountAsync();
|
|
// 当前总库存
|
var totalStock = await _db.Queryable<Dt_StockInfo>().CountAsync();
|
|
return WebResponseContent.Instance.OK(null, new
|
{
|
TodayInbound = todayInbound,
|
TodayOutbound = todayOutbound,
|
MonthInbound = monthInbound,
|
MonthOutbound = monthOutbound,
|
TotalStock = totalStock
|
});
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"总览数据获取失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 每日统计
|
/// </summary>
|
/// <remarks>
|
/// 注意：数据在 SQL 层过滤后，在应用层按日期分组。
|
/// SqlSugar 的 GroupBy 不支持对 .Date 这样的计算列直接生成 SQL GROUP BY，
|
/// 因此采用此方式以确保跨数据库兼容性。
|
/// </remarks>
|
[HttpGet("DailyStats")]
|
public async Task<WebResponseContent> DailyStats([FromQuery] int days = 30)
|
{
|
try
|
{
|
if (days <= 0) days = 30;
|
if (days > 365) days = 365;
|
|
var startDate = DateTime.Today.AddDays(-days + 1);
|
|
var query = await _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= startDate)
|
.Select(t => new { t.InsertTime, t.TaskType })
|
.ToListAsync();
|
|
var result = query
|
.GroupBy(t => t.InsertTime.Date)
|
.Select(g => new
|
{
|
Date = g.Key.ToString("yyyy-MM-dd"),
|
Inbound = g.Count(t => t.TaskType >= 500 && t.TaskType < 600),
|
Outbound = g.Count(t => t.TaskType >= 100 && t.TaskType < 200)
|
})
|
.OrderBy(x => x.Date)
|
.ToList();
|
|
return WebResponseContent.Instance.OK(null, result);
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"每日统计获取失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 每周统计
|
/// </summary>
|
/// <remarks>
|
/// 注意：数据在 SQL 层过滤后，在应用层按 ISO 8601 周键分组。
|
/// 周键为 "YYYY-Www" 格式，无法直接在 SQL 层用 GROUP BY 实现。
|
/// </remarks>
|
[HttpGet("WeeklyStats")]
|
public async Task<WebResponseContent> WeeklyStats([FromQuery] int weeks = 12)
|
{
|
try
|
{
|
if (weeks <= 0) weeks = 12;
|
|
var startDate = DateTime.Today.AddDays(-weeks * 7);
|
|
var query = await _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= startDate)
|
.Select(t => new { t.InsertTime, t.TaskType })
|
.ToListAsync();
|
|
var result = query
|
.GroupBy(t => GetWeekKey(t.InsertTime))
|
.Select(g => new
|
{
|
Week = g.Key,
|
Inbound = g.Count(t => t.TaskType >= 500 && t.TaskType < 600),
|
Outbound = g.Count(t => t.TaskType >= 100 && t.TaskType < 200)
|
})
|
.OrderBy(x => x.Week)
|
.ToList();
|
|
return WebResponseContent.Instance.OK(null, result);
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"每周统计获取失败: {ex.Message}");
|
}
|
}
|
|
private string GetWeekKey(DateTime date)
|
{
|
// 获取周一开始的周 (ISO 8601)
|
var diff = (7 + (date.DayOfWeek - DayOfWeek.Monday)) % 7;
|
var monday = date.AddDays(-diff);
|
var weekNum = System.Globalization.CultureInfo.InvariantCulture
|
.Calendar.GetWeekOfYear(monday, System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
|
return $"{monday.Year}-W{weekNum:D2}";
|
}
|
|
/// <summary>
|
/// 每月统计
|
/// </summary>
|
/// <remarks>
|
/// 注意：数据在 SQL 层过滤后，在应用层按年月分组。
|
/// SqlSugar 的 GroupBy 不支持匿名对象 (Year, Month) 直接映射到 SQL GROUP BY，
|
/// 因此采用此方式以确保跨数据库兼容性。
|
/// </remarks>
|
[HttpGet("MonthlyStats")]
|
public async Task<WebResponseContent> MonthlyStats([FromQuery] int months = 12)
|
{
|
try
|
{
|
if (months <= 0) months = 12;
|
|
var startDate = DateTime.Today.AddMonths(-months + 1);
|
startDate = new DateTime(startDate.Year, startDate.Month, 1);
|
|
var query = await _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= startDate)
|
.Select(t => new { t.InsertTime, t.TaskType })
|
.ToListAsync();
|
|
var result = query
|
.GroupBy(t => new { t.InsertTime.Year, t.InsertTime.Month })
|
.Select(g => new
|
{
|
Month = $"{g.Key.Year}-{g.Key.Month:D2}",
|
Inbound = g.Count(t => t.TaskType >= 500 && t.TaskType < 600),
|
Outbound = g.Count(t => t.TaskType >= 100 && t.TaskType < 200)
|
})
|
.OrderBy(x => x.Month)
|
.ToList();
|
|
return WebResponseContent.Instance.OK(null, result);
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"每月统计获取失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 库存库龄分布
|
/// </summary>
|
[HttpGet("StockAgeDistribution")]
|
public async Task<WebResponseContent> StockAgeDistribution()
|
{
|
try
|
{
|
var today = DateTime.Today;
|
|
// 使用 SQL 直接分组统计，避免加载所有数据到内存
|
var result = new[]
|
{
|
new { Range = "7天内", Count = await _db.Queryable<Dt_StockInfo>().Where(s => SqlFunc.DateDiff(DateType.Day, s.CreateDate, today) <= 7).CountAsync() },
|
new { Range = "7-30天", Count = await _db.Queryable<Dt_StockInfo>().Where(s => SqlFunc.DateDiff(DateType.Day, s.CreateDate, today) > 7 && SqlFunc.DateDiff(DateType.Day, s.CreateDate, today) <= 30).CountAsync() },
|
new { Range = "30-90天", Count = await _db.Queryable<Dt_StockInfo>().Where(s => SqlFunc.DateDiff(DateType.Day, s.CreateDate, today) > 30 && SqlFunc.DateDiff(DateType.Day, s.CreateDate, today) <= 90).CountAsync() },
|
new { Range = "90天以上", Count = await _db.Queryable<Dt_StockInfo>().Where(s => SqlFunc.DateDiff(DateType.Day, s.CreateDate, today) > 90).CountAsync() }
|
};
|
|
return WebResponseContent.Instance.OK(null, result);
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"库存库龄分布获取失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 各仓库库存分布
|
/// </summary>
|
/// <remarks>
|
/// 使用 SQL GROUP BY 在数据库层面聚合，避免加载全部库存记录到内存。
|
/// </remarks>
|
[HttpGet("StockByWarehouse")]
|
public async Task<WebResponseContent> StockByWarehouse()
|
{
|
try
|
{
|
// 查询仓库名称
|
var warehouses = await _db.Queryable<Dt_Warehouse>()
|
.Select(w => new { w.WarehouseId, w.WarehouseName })
|
.ToListAsync();
|
var warehouseDict = warehouses.ToDictionary(w => w.WarehouseId, w => w.WarehouseName);
|
|
// 使用 SQL GROUP BY 在数据库层面聚合，仅返回聚合结果
|
var stockGroups = await _db.Queryable<Dt_StockInfo>()
|
.GroupBy(s => s.WarehouseId)
|
.Select(s => new { s.WarehouseId, Count = SqlFunc.AggregateCount(s.Id) })
|
.ToListAsync();
|
|
var result = stockGroups
|
.Select(g => new
|
{
|
Warehouse = warehouseDict.TryGetValue(g.WarehouseId, out var name) ? name : $"仓库{g.WarehouseId}",
|
Count = g.Count
|
})
|
.ToList();
|
|
return WebResponseContent.Instance.OK(null, result);
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"各仓库库存分布获取失败: {ex.Message}");
|
}
|
}
|
}
|
}
|
