using Microsoft.AspNetCore.Authorization;
|
using Microsoft.AspNetCore.Mvc;
|
using SqlSugar;
|
using WIDESEA_Common.LocationEnum;
|
using WIDESEA_Common.StockEnum;
|
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"), AllowAnonymous]
|
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>
|
[HttpGet("DailyStats"), AllowAnonymous]
|
public async Task<WebResponseContent> DailyStats([FromQuery] int days = 10)
|
{
|
try
|
{
|
if (days <= 0) days = 30;
|
if (days > 365) days = 365;
|
|
var startDate = DateTime.Today.AddDays(-days + 1);
|
var endDate = DateTime.Today; // 包含今天
|
|
// 指定要统计的仓库（巷道号）
|
var specifiedRoadways = new List<string>
|
{
|
"GWSC1", "CWSC1", "HCSC1", "ZJSC1", "FJSC1"
|
};
|
|
var query = await _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= startDate && t.InsertTime <= endDate)
|
.Where(t => specifiedRoadways.Contains(t.Roadway)) // 只查询指定巷道号的数据
|
.Select(t => new { t.InsertTime, t.TaskType, t.Roadway })
|
.ToListAsync();
|
|
// 生成日期范围
|
var allDates = new List<DateTime>();
|
for (var date = startDate; date <= endDate; date = date.AddDays(1))
|
{
|
allDates.Add(date);
|
}
|
|
// 按巷道号和日期分组统计
|
var groupedData = query
|
.GroupBy(t => new { t.Roadway, Date = t.InsertTime.Date })
|
.Select(g => new
|
{
|
Roadway = g.Key.Roadway,
|
Date = g.Key.Date,
|
Inbound = g.Count(t => t.TaskType >= 200 && t.TaskType < 300),
|
Outbound = g.Count(t => t.TaskType >= 100 && t.TaskType < 200)
|
})
|
.ToList();
|
|
// 构建结果：每个指定仓库对应一个日期列表
|
var result = specifiedRoadways.Select(roadway =>
|
{
|
// 获取该巷道号的分组数据字典
|
var roadwayData = groupedData
|
.Where(g => g.Roadway == roadway)
|
.ToDictionary(x => x.Date, x => x);
|
|
// 补全缺失日期，确保每天都有数据（默认为0）
|
var dailyStats = allDates.Select(date =>
|
{
|
if (roadwayData.TryGetValue(date, out var data))
|
{
|
return new
|
{
|
Date = date.ToString("MM-dd"),
|
Inbound = data.Inbound,
|
Outbound = data.Outbound
|
};
|
}
|
else
|
{
|
return new
|
{
|
Date = date.ToString("MM-dd"),
|
Inbound = 0,
|
Outbound = 0
|
};
|
}
|
})
|
.OrderBy(x => x.Date)
|
.ToList();
|
|
return new
|
{
|
Roadway = roadway,
|
DailyStats = dailyStats
|
};
|
})
|
.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"), AllowAnonymous]
|
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 >= 200 && t.TaskType < 300),
|
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}";
|
}
|
|
/// <remarks>
|
/// 按年月统计入站和出站任务数量
|
/// </remarks>
|
[HttpGet("MonthlyStats"), AllowAnonymous]
|
public async Task<WebResponseContent> MonthlyStats(int months, string roadway)
|
{
|
try
|
{
|
if (months <= 0) months = 12;
|
|
var startDate = DateTime.Today.AddMonths(-months + 1);
|
startDate = new DateTime(startDate.Year, startDate.Month, 1);
|
|
// 仓库名称映射
|
var roadwayNames = new Dictionary<string, string>
|
{
|
|
{ "GWSC1", "高温1号仓库" },
|
{ "CWSC1", "常温1号仓库" },
|
{ "HCSC1", "分容1号仓库" },
|
{ "FJSC1", "负极卷1号仓库" },
|
{ "ZJSC1", "正极卷1号仓库" },
|
};
|
|
// 构建查询
|
var query = _db.Queryable<Dt_Task_Hty>()
|
.Where(t => t.InsertTime >= startDate);
|
|
// 如果指定了道路，添加道路过滤条件
|
if (!string.IsNullOrEmpty(roadway))
|
{
|
query = query.Where(t => t.Roadway == roadway);
|
}
|
|
var monthlyStats = await query
|
.GroupBy(t => new { t.InsertTime.Year, t.InsertTime.Month })
|
.Select(t => new
|
{
|
Year = t.InsertTime.Year,
|
Month = t.InsertTime.Month,
|
Inbound = SqlFunc.AggregateSum(
|
SqlFunc.IIF(t.TaskType >= 200 && t.TaskType < 300, 1, 0)
|
),
|
Outbound = SqlFunc.AggregateSum(
|
SqlFunc.IIF(t.TaskType >= 100 && t.TaskType < 200, 1, 0)
|
)
|
})
|
.OrderBy(t => t.Year)
|
.OrderBy(t => t.Month)
|
.ToListAsync();
|
|
// 生成所有需要统计的月份列表
|
var allMonths = new List<DateTime>();
|
var currentMonth = startDate;
|
var endMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
|
|
while (currentMonth <= endMonth)
|
{
|
allMonths.Add(currentMonth);
|
currentMonth = currentMonth.AddMonths(1);
|
}
|
|
// 将查询结果转换为字典，方便查找
|
var statsDict = monthlyStats.ToDictionary(
|
s => $"{s.Year}-{s.Month:D2}",
|
s => new { s.Inbound, s.Outbound }
|
);
|
|
// 构建完整的结果列表，包含所有月份
|
var result = new List<object>();
|
foreach (var month in allMonths)
|
{
|
var monthKey = $"{month.Year}-{month.Month:D2}";
|
|
if (statsDict.TryGetValue(monthKey, out var stat))
|
{
|
result.Add(new
|
{
|
Month = monthKey,
|
Inbound = stat.Inbound,
|
Outbound = stat.Outbound,
|
Roadway = roadway,
|
RoadwayName = !string.IsNullOrEmpty(roadway) && roadwayNames.ContainsKey(roadway)
|
? roadwayNames[roadway]
|
: null
|
});
|
}
|
else
|
{
|
result.Add(new
|
{
|
Month = monthKey,
|
Inbound = 0,
|
Outbound = 0,
|
Roadway = roadway,
|
RoadwayName = !string.IsNullOrEmpty(roadway) && roadwayNames.ContainsKey(roadway)
|
? roadwayNames[roadway]
|
: null
|
});
|
}
|
}
|
|
return WebResponseContent.Instance.OK(null, result);
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"每月统计获取失败: {ex.Message}");
|
}
|
}
|
/// <summary>
|
/// 库存库龄分布
|
/// </summary>
|
[HttpGet("StockAgeDistribution"), AllowAnonymous]
|
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"), AllowAnonymous]
|
public async Task<WebResponseContent> StockByWarehouse()
|
{
|
try
|
{
|
// 查询所有仓库信息
|
var warehouses = await _db.Queryable<Dt_Warehouse>()
|
.Select(w => new { w.WarehouseId, w.WarehouseName })
|
.ToListAsync();
|
|
// 查询所有货位信息，按仓库分组统计总数
|
var locationGroups = await _db.Queryable<Dt_LocationInfo>()
|
.GroupBy(l => l.WarehouseId)
|
.Select(l => new
|
{
|
WarehouseId = l.WarehouseId,
|
TotalLocations = SqlFunc.AggregateCount(l.Id)
|
})
|
.ToListAsync();
|
|
// 查询状态不为Free的货位信息（有货货位），按仓库分组统计
|
var occupiedLocationGroups = await _db.Queryable<Dt_LocationInfo>()
|
.Where(l => l.LocationStatus != (int)LocationStatusEnum.Free)
|
.GroupBy(l => l.WarehouseId)
|
.Select(l => new
|
{
|
WarehouseId = l.WarehouseId,
|
OccupiedLocations = SqlFunc.AggregateCount(l.Id)
|
})
|
.ToListAsync();
|
|
// 将仓库信息与货位统计信息合并
|
var result = warehouses.Select(w =>
|
{
|
var totalLocations = locationGroups.FirstOrDefault(lg => lg.WarehouseId == w.WarehouseId)?.TotalLocations ?? 0;
|
var occupiedLocations = occupiedLocationGroups.FirstOrDefault(og => og.WarehouseId == w.WarehouseId)?.OccupiedLocations ?? 0;
|
var emptyLocations = totalLocations - occupiedLocations;
|
|
var occupiedPercentage = totalLocations > 0 ? Math.Round((double)occupiedLocations / totalLocations * 100, 0) : 0.0;
|
var emptyPercentage = totalLocations > 0 ? Math.Round((double)emptyLocations / totalLocations * 100, 0) : 0.0;
|
|
return new
|
{
|
Warehouse = w.WarehouseName,
|
Total = totalLocations,
|
HasStock = occupiedLocations,
|
NoStock = emptyLocations,
|
HasStockPercentage = $"{occupiedPercentage}%",
|
NoStockPercentage = $"{emptyPercentage}%"
|
};
|
}).ToList();
|
|
return WebResponseContent.Instance.OK(null, result);
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"各仓库库存分布获取失败: {ex.Message}");
|
}
|
}
|
/// <summary>
|
/// 查询各仓库电池/有货数量和空托盘数量
|
/// </summary>
|
/// <remarks>
|
/// 仓库ID规则：1=高温库, 2=常温库, 3=化成库, 6/7=极卷库
|
/// <br/>
|
/// 统计规则：
|
/// <br/>
|
/// - 高温/常温/化成库：统计 电池数量(StockStatus=6) 和 空托盘数量(StockStatus=22)
|
/// <br/>
|
/// - 极卷库(6/7)：统计 有货数量(StockStatus≠22) 和 空托盘数量(StockStatus=22)
|
/// <br/>
|
/// 通过返回数据中的 StockStatus 和 Count 可以进一步查询明细电池。
|
/// </remarks>
|
[HttpGet("StockAndTrayCount"), AllowAnonymous]
|
public async Task<WebResponseContent> StockAndTrayCount()
|
{
|
try
|
{
|
var warehouseIds = new[] { 1, 2, 3, 6, 7 };
|
|
var warehouseNames = new Dictionary<int, string>
|
{
|
{ 1, "高温库" },
|
{ 2, "常温库" },
|
{ 3, "化成库" },
|
{ 6, "极卷库" },
|
{ 7, "极卷库" }
|
};
|
|
var result = new List<object>();
|
|
foreach (var warehouseId in warehouseIds)
|
{
|
var warehouseName = warehouseNames.GetValueOrDefault(warehouseId, $"仓库{warehouseId}");
|
|
if (warehouseId == 6 || warehouseId == 7)
|
{
|
var totalCount = await _db.Queryable<Dt_StockInfo>()
|
.Where(s => s.WarehouseId == warehouseId)
|
.CountAsync();
|
|
var emptyTrayCount = await _db.Queryable<Dt_StockInfo>()
|
.Where(s => s.WarehouseId == warehouseId && s.StockStatus == (int)StockStatusEmun.空托盘库存)
|
.CountAsync();
|
|
result.Add(new
|
{
|
WarehouseId = warehouseId,
|
WarehouseName = warehouseName,
|
HasGoodsCount = totalCount - emptyTrayCount,
|
EmptyTrayCount = emptyTrayCount,
|
});
|
}
|
else
|
{
|
var batteryCount = await _db.Queryable<Dt_StockInfo>()
|
.Where(s => s.WarehouseId == warehouseId && s.StockStatus == (int)StockStatusEmun.入库完成)
|
.LeftJoin<Dt_StockInfoDetail>((s, d) => s.Id == d.StockId)
|
.CountAsync();
|
|
var emptyTrayCount = await _db.Queryable<Dt_StockInfo>()
|
.Where(s => s.WarehouseId == warehouseId && s.StockStatus == (int)StockStatusEmun.空托盘库存)
|
.CountAsync();
|
|
result.Add(new
|
{
|
WarehouseId = warehouseId,
|
WarehouseName = warehouseName,
|
BatteryCount = batteryCount,
|
EmptyTrayCount = emptyTrayCount,
|
});
|
}
|
}
|
|
return WebResponseContent.Instance.OK(null, result);
|
}
|
catch (Exception ex)
|
{
|
return WebResponseContent.Instance.Error($"电池和空托盘数量查询失败: {ex.Message}");
|
}
|
}
|
}
|
}
|
