649 lines
30 KiB
C#
649 lines
30 KiB
C#
using Indotalent.Infrastructure.Database;
|
|
using Indotalent.Data.Entities;
|
|
using Indotalent.Data.Enums;
|
|
using MediatR;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace Indotalent.Features.Root.Home.Cqrs;
|
|
|
|
public class ChartDataPoint
|
|
{
|
|
public string? Label { get; set; }
|
|
public double SalesAmount { get; set; }
|
|
public double PurchaseAmount { get; set; }
|
|
}
|
|
|
|
public class ScmMetricDto
|
|
{
|
|
public string? Label { get; set; }
|
|
public string? Value { get; set; }
|
|
public string? Color { get; set; }
|
|
public double Percentage { get; set; }
|
|
}
|
|
|
|
public class ScmActivityDto
|
|
{
|
|
public string? Module { get; set; }
|
|
public string? Number { get; set; }
|
|
public string? Partner { get; set; }
|
|
public double Qty { get; set; }
|
|
public string? Status { get; set; }
|
|
public DateTime? Date { get; set; }
|
|
public string? Description { get; set; }
|
|
}
|
|
|
|
public class RecentPurchaseOrderDto
|
|
{
|
|
public DateTime? OrderDate { get; set; }
|
|
public string? Number { get; set; }
|
|
public string? VendorName { get; set; }
|
|
public decimal Amount { get; set; }
|
|
public string? Status { get; set; }
|
|
public int ItemCount { get; set; }
|
|
}
|
|
|
|
public class RecentGoodsReceiveDto
|
|
{
|
|
public DateTime? ReceiveDate { get; set; }
|
|
public string? Number { get; set; }
|
|
public string? PONumber { get; set; }
|
|
public string? Status { get; set; }
|
|
}
|
|
|
|
public class StatusCountDto
|
|
{
|
|
public string? Status { get; set; }
|
|
public int Count { get; set; }
|
|
public string? Color { get; set; }
|
|
}
|
|
|
|
public class DashboardScmResponse
|
|
{
|
|
public int PendingPR { get; set; }
|
|
public int PendingSQ { get; set; }
|
|
public int PendingGR { get; set; }
|
|
public int PendingDO { get; set; }
|
|
public int ActiveCustomer { get; set; }
|
|
public int ActiveVendor { get; set; }
|
|
public double InboundQtyMTD { get; set; }
|
|
public double OutboundQtyMTD { get; set; }
|
|
public decimal SalesMTD { get; set; }
|
|
public decimal PurchaseMTD { get; set; }
|
|
public double ChainAccuracyIndex { get; set; }
|
|
public decimal TotalReceivable { get; set; }
|
|
public decimal TotalPayable { get; set; }
|
|
public int TotalPR { get; set; }
|
|
public int TotalPO { get; set; }
|
|
public int TotalGR { get; set; }
|
|
public int TotalDO { get; set; }
|
|
|
|
public List<ChartDataPoint> MonthlySalesVsPurchase { get; set; } = new();
|
|
public List<ChartDataPoint> WeeklySalesVsPurchase { get; set; } = new();
|
|
|
|
public List<ScmMetricDto> DocGrid { get; set; } = new();
|
|
public List<ScmActivityDto> GlobalFlowLog { get; set; } = new();
|
|
public List<ScmMetricDto> TopProductsByQty { get; set; } = new();
|
|
public List<ScmMetricDto> CustGroupStats { get; set; } = new();
|
|
public List<ScmMetricDto> VendGroupStats { get; set; } = new();
|
|
public List<ScmMetricDto> CustCategoryStats { get; set; } = new();
|
|
public List<ScmMetricDto> VendCategoryStats { get; set; } = new();
|
|
public List<ScmMetricDto> PayMethodStats { get; set; } = new();
|
|
public List<ScmMetricDto> WhseInventoryLevel { get; set; } = new();
|
|
public List<ScmMetricDto> CashFlowMTD { get; set; } = new();
|
|
public List<ScmMetricDto> StockValuePerGroup { get; set; } = new();
|
|
public List<ScmMetricDto> VendorPOTotals { get; set; } = new();
|
|
public List<ScmMetricDto> ProductCategories { get; set; } = new();
|
|
public List<StatusCountDto> POStatusBreakdown { get; set; } = new();
|
|
public List<StatusCountDto> GRStatusBreakdown { get; set; } = new();
|
|
public List<StatusCountDto> PRStatusBreakdown { get; set; } = new();
|
|
public List<StatusCountDto> DOStatusBreakdown { get; set; } = new();
|
|
public List<RecentPurchaseOrderDto> RecentPOs { get; set; } = new();
|
|
public List<RecentGoodsReceiveDto> RecentGRs { get; set; } = new();
|
|
|
|
public string FormattedSales => FormatCurrency(SalesMTD);
|
|
public string FormattedPurchase => FormatCurrency(PurchaseMTD);
|
|
public string FormattedReceivable => FormatCurrency(TotalReceivable);
|
|
public string FormattedPayable => FormatCurrency(TotalPayable);
|
|
private static string FormatCurrency(decimal v) => v >= 1000000 ? "$" + (v / 1000000m).ToString("F2") + "M" : "$" + v.ToString("N0");
|
|
}
|
|
|
|
public record GetScmDashboardQuery() : IRequest<DashboardScmResponse>;
|
|
|
|
public class DashboardHandler : IRequestHandler<GetScmDashboardQuery, DashboardScmResponse>
|
|
{
|
|
private readonly AppDbContext _context;
|
|
public DashboardHandler(AppDbContext context) => _context = context;
|
|
|
|
public async Task<DashboardScmResponse> Handle(GetScmDashboardQuery request, CancellationToken ct)
|
|
{
|
|
var start = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
|
|
|
|
// --- Inventory Transactions (materialize with FK navigation via projection, no .Include) ---
|
|
var txRaw = await _context.InventoryTransaction.AsNoTracking()
|
|
.Where(x => x.MovementDate >= start)
|
|
.Select(x => new
|
|
{
|
|
x.MovementDate,
|
|
x.TransType,
|
|
x.Status,
|
|
x.Movement,
|
|
x.QtySCSys,
|
|
x.QtySCDelta,
|
|
x.WarehouseId,
|
|
x.ProductId,
|
|
x.ModuleCode,
|
|
x.ModuleName,
|
|
x.ModuleNumber
|
|
})
|
|
.ToListAsync(ct);
|
|
|
|
var warehouseIds = txRaw.Where(x => x.WarehouseId != null).Select(x => x.WarehouseId).Distinct().ToList();
|
|
var productIds = txRaw.Where(x => x.ProductId != null).Select(x => x.ProductId).Distinct().ToList();
|
|
|
|
var warehouses = await _context.Warehouse.AsNoTracking()
|
|
.Where(w => warehouseIds.Contains(w.Id))
|
|
.Select(w => new { w.Id, w.Name })
|
|
.ToListAsync(ct);
|
|
|
|
var products = await _context.Product.AsNoTracking()
|
|
.Where(p => productIds.Contains(p.Id))
|
|
.Select(p => new { p.Id, p.Name, p.ProductGroupId, p.UnitPrice })
|
|
.ToListAsync(ct);
|
|
|
|
var productGroupIds = products.Where(p => p.ProductGroupId != null).Select(p => p.ProductGroupId).Distinct().ToList();
|
|
var productGroups = await _context.ProductGroup.AsNoTracking()
|
|
.Where(g => productGroupIds.Contains(g.Id))
|
|
.Select(g => new { g.Id, g.Name })
|
|
.ToListAsync(ct);
|
|
|
|
var warehouseMap = warehouses.ToDictionary(w => w.Id, w => w.Name ?? "Main");
|
|
var productMap = products.ToDictionary(p => p.Id, p => p.Name ?? "N/A");
|
|
var productPriceMap = products.ToDictionary(p => p.Id, p => p.UnitPrice ?? 0);
|
|
var groupMap = productGroups.ToDictionary(g => g.Id, g => g.Name ?? "General");
|
|
|
|
// Chain Accuracy Index
|
|
var countData = txRaw.Where(x => x.ModuleCode == "COUNT").ToList();
|
|
double accuracy = 100;
|
|
if (countData.Count != 0)
|
|
{
|
|
var sys = countData.Sum(x => x.QtySCSys ?? 0);
|
|
var delta = countData.Sum(x => Math.Abs(x.QtySCDelta ?? 0));
|
|
accuracy = sys > 0 ? Math.Max(0, 100 - (delta / sys * 100)) : 100;
|
|
}
|
|
|
|
// Inbound / Outbound MTD
|
|
var inboundQty = txRaw.Where(x => x.TransType == InventoryTransType.In && x.Status == InventoryTransactionStatus.Confirmed)
|
|
.Sum(x => x.Movement ?? 0);
|
|
var outboundQty = txRaw.Where(x => x.TransType == InventoryTransType.Out && x.Status == InventoryTransactionStatus.Confirmed)
|
|
.Sum(x => x.Movement ?? 0);
|
|
|
|
// Whse Inventory Level
|
|
var whseLevel = txRaw
|
|
.GroupBy(x => x.WarehouseId)
|
|
.Select(g => new ScmMetricDto
|
|
{
|
|
Label = warehouseMap.GetValueOrDefault(g.Key ?? "", "Main"),
|
|
Value = g.Sum(x => (x.Movement ?? 0) * (x.TransType == InventoryTransType.In ? 1 : -1)).ToString("N0")
|
|
}).ToList();
|
|
|
|
// Stock Value Per Group
|
|
var stockValGroup = txRaw
|
|
.GroupBy(x => productIds.Contains(x.ProductId ?? "") ? groupMap.GetValueOrDefault(
|
|
products.FirstOrDefault(p => p.Id == x.ProductId)?.ProductGroupId ?? "", "General") : "General")
|
|
.Select(g => new ScmMetricDto
|
|
{
|
|
Label = g.Key,
|
|
Value = g.Sum(x =>
|
|
{
|
|
var price = productPriceMap.GetValueOrDefault(x.ProductId ?? "", 0);
|
|
return (decimal)((x.Movement ?? 0) * (x.TransType == InventoryTransType.In ? 1 : -1)) * price;
|
|
}).ToString("N0")
|
|
}).ToList();
|
|
|
|
// --- Customer Groups (no .Include - use CustomerGroupId FK) ---
|
|
var custRaw = await _context.Customer.AsNoTracking()
|
|
.Select(x => new { x.CustomerGroupId })
|
|
.ToListAsync(ct);
|
|
var custGroupIds = custRaw.Where(x => x.CustomerGroupId != null).Select(x => x.CustomerGroupId).Distinct().ToList();
|
|
var custGroupsDb = await _context.CustomerGroup.AsNoTracking()
|
|
.Where(g => custGroupIds.Contains(g.Id))
|
|
.Select(g => new { g.Id, g.Name })
|
|
.ToListAsync(ct);
|
|
var custGroupMap = custGroupsDb.ToDictionary(g => g.Id, g => g.Name ?? "General");
|
|
var custGroups = custRaw
|
|
.GroupBy(x => custGroupMap.GetValueOrDefault(x.CustomerGroupId ?? "", "General"))
|
|
.Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
|
|
.ToList();
|
|
|
|
// --- Vendor Groups (no .Include - use VendorGroupId FK) ---
|
|
var vendRaw = await _context.Vendor.AsNoTracking()
|
|
.Select(x => new { x.VendorGroupId })
|
|
.ToListAsync(ct);
|
|
var vendGroupIds = vendRaw.Where(x => x.VendorGroupId != null).Select(x => x.VendorGroupId).Distinct().ToList();
|
|
var vendGroupsDb = await _context.VendorGroup.AsNoTracking()
|
|
.Where(g => vendGroupIds.Contains(g.Id))
|
|
.Select(g => new { g.Id, g.Name })
|
|
.ToListAsync(ct);
|
|
var vendGroupMap = vendGroupsDb.ToDictionary(g => g.Id, g => g.Name ?? "General");
|
|
var vendGroups = vendRaw
|
|
.GroupBy(x => vendGroupMap.GetValueOrDefault(x.VendorGroupId ?? "", "General"))
|
|
.Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
|
|
.ToList();
|
|
|
|
// --- Customer Categories ---
|
|
var custCatRaw = await _context.Customer.AsNoTracking()
|
|
.Select(x => new { x.CustomerCategoryId })
|
|
.ToListAsync(ct);
|
|
var custCatIds = custCatRaw.Where(x => x.CustomerCategoryId != null).Select(x => x.CustomerCategoryId).Distinct().ToList();
|
|
var custCatDb = await _context.CustomerCategory.AsNoTracking()
|
|
.Where(c => custCatIds.Contains(c.Id))
|
|
.Select(c => new { c.Id, c.Name })
|
|
.ToListAsync(ct);
|
|
var custCatMap = custCatDb.ToDictionary(c => c.Id, c => c.Name ?? "General");
|
|
var custCats = custCatRaw
|
|
.GroupBy(x => custCatMap.GetValueOrDefault(x.CustomerCategoryId ?? "", "General"))
|
|
.Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
|
|
.ToList();
|
|
|
|
// --- Vendor Categories ---
|
|
var vendCatRaw = await _context.Vendor.AsNoTracking()
|
|
.Select(x => new { x.VendorCategoryId })
|
|
.ToListAsync(ct);
|
|
var vendCatIds = vendCatRaw.Where(x => x.VendorCategoryId != null).Select(x => x.VendorCategoryId).Distinct().ToList();
|
|
var vendCatDb = await _context.VendorCategory.AsNoTracking()
|
|
.Where(c => vendCatIds.Contains(c.Id))
|
|
.Select(c => new { c.Id, c.Name })
|
|
.ToListAsync(ct);
|
|
var vendCatMap = vendCatDb.ToDictionary(c => c.Id, c => c.Name ?? "General");
|
|
var vendCats = vendCatRaw
|
|
.GroupBy(x => vendCatMap.GetValueOrDefault(x.VendorCategoryId ?? "", "General"))
|
|
.Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
|
|
.ToList();
|
|
|
|
// --- Payment Methods (no .Include) ---
|
|
var payRaw = await _context.PaymentReceive.AsNoTracking()
|
|
.Select(x => new { x.PaymentMethodId })
|
|
.ToListAsync(ct);
|
|
var payMethodIds = payRaw.Where(x => x.PaymentMethodId != null).Select(x => x.PaymentMethodId).Distinct().ToList();
|
|
var payMethodDb = await _context.PaymentMethod.AsNoTracking()
|
|
.Where(m => payMethodIds.Contains(m.Id))
|
|
.Select(m => new { m.Id, m.Name })
|
|
.ToListAsync(ct);
|
|
var payMethodMap = payMethodDb.ToDictionary(m => m.Id, m => m.Name ?? "Cash");
|
|
var payMethods = payRaw
|
|
.GroupBy(x => payMethodMap.GetValueOrDefault(x.PaymentMethodId ?? "", "Cash"))
|
|
.Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
|
|
.ToList();
|
|
|
|
// --- Top Products by Qty (no .Include - use ProductId FK) ---
|
|
var soiRaw = await _context.SalesOrderItem.AsNoTracking()
|
|
.Select(x => new { x.ProductId, x.Quantity })
|
|
.ToListAsync(ct);
|
|
var topProdIds = soiRaw.Where(x => x.ProductId != null).Select(x => x.ProductId).Distinct().ToList();
|
|
var topProdDb = await _context.Product.AsNoTracking()
|
|
.Where(p => topProdIds.Contains(p.Id))
|
|
.Select(p => new { p.Id, p.Name })
|
|
.ToListAsync(ct);
|
|
var topProdMap = topProdDb.ToDictionary(p => p.Id, p => p.Name ?? "N/A");
|
|
var topProds = soiRaw
|
|
.Where(x => x.ProductId != null)
|
|
.GroupBy(x => x.ProductId!)
|
|
.Select(g => new ScmMetricDto { Label = topProdMap.GetValueOrDefault(g.Key, "N/A"), Value = g.Sum(x => x.Quantity ?? 0).ToString("N0") })
|
|
.OrderByDescending(x => double.Parse(x.Value ?? "0"))
|
|
.Take(10)
|
|
.ToList();
|
|
|
|
// --- Global Flow Log ---
|
|
var globalLogs = await _context.InventoryTransaction
|
|
.AsNoTracking()
|
|
.OrderByDescending(x => x.MovementDate)
|
|
.Take(30)
|
|
.Select(x => new ScmActivityDto
|
|
{
|
|
Module = x.ModuleName,
|
|
Number = x.ModuleNumber,
|
|
Partner = x.WarehouseId,
|
|
Qty = x.Movement ?? 0,
|
|
Status = x.Status.ToString(),
|
|
Date = x.MovementDate,
|
|
Description = x.ModuleCode
|
|
}).ToListAsync(ct);
|
|
var logWhIds = globalLogs.Where(x => x.Partner != null).Select(x => x.Partner).Distinct().ToList();
|
|
var logWhDb = await _context.Warehouse.AsNoTracking()
|
|
.Where(w => logWhIds.Contains(w.Id))
|
|
.Select(w => new { w.Id, w.Name })
|
|
.ToListAsync(ct);
|
|
var logWhMap = logWhDb.ToDictionary(w => w.Id, w => w.Name ?? "N/A");
|
|
foreach (var log in globalLogs)
|
|
{
|
|
if (log.Partner != null && logWhMap.TryGetValue(log.Partner, out var whName))
|
|
log.Partner = whName;
|
|
}
|
|
|
|
// --- Doc Grid ---
|
|
var sqCount = await _context.SalesQuotation.CountAsync(ct);
|
|
var soCount = await _context.SalesOrder.CountAsync(ct);
|
|
var prCount = await _context.PurchaseRequisition.CountAsync(ct);
|
|
var poCount = await _context.PurchaseOrder.CountAsync(ct);
|
|
var grCount = await _context.GoodsReceive.CountAsync(ct);
|
|
var doCount = await _context.DeliveryOrder.CountAsync(ct);
|
|
var srCount = await _context.SalesReturn.CountAsync(ct);
|
|
var prRetCount = await _context.PurchaseReturn.CountAsync(ct);
|
|
var invCount = await _context.Invoice.CountAsync(ct);
|
|
var billCount = await _context.Bill.CountAsync(ct);
|
|
var cnCount = await _context.CreditNote.CountAsync(ct);
|
|
var dnCount = await _context.DebitNote.CountAsync(ct);
|
|
var payRecCount = await _context.PaymentReceive.CountAsync(ct);
|
|
var payDisbCount = await _context.PaymentDisburse.CountAsync(ct);
|
|
|
|
var docGrid = new List<ScmMetricDto>
|
|
{
|
|
new() { Label = "Sales Quotation", Value = sqCount.ToString(), Color = "#3b82f6" },
|
|
new() { Label = "Sales Order", Value = soCount.ToString(), Color = "#10b981" },
|
|
new() { Label = "Purchase Req", Value = prCount.ToString(), Color = "#f59e0b" },
|
|
new() { Label = "Purchase Order", Value = poCount.ToString(), Color = "#ef4444" },
|
|
new() { Label = "Goods Receive", Value = grCount.ToString(), Color = "#64748b" },
|
|
new() { Label = "Delivery Order", Value = doCount.ToString(), Color = "#06b6d4" },
|
|
new() { Label = "Sales Return", Value = srCount.ToString(), Color = "#8b5cf6" },
|
|
new() { Label = "Purch. Return", Value = prRetCount.ToString(), Color = "#f43f5e" },
|
|
new() { Label = "Invoice", Value = invCount.ToString(), Color = "#14b8a6" },
|
|
new() { Label = "Bill", Value = billCount.ToString(), Color = "#f97316" },
|
|
new() { Label = "Credit Note", Value = cnCount.ToString(), Color = "#ec4899" },
|
|
new() { Label = "Debit Note", Value = dnCount.ToString(), Color = "#0ea5e9" },
|
|
new() { Label = "Pay Receive", Value = payRecCount.ToString(), Color = "#84cc16" },
|
|
new() { Label = "Pay Disburse", Value = payDisbCount.ToString(), Color = "#d946ef" }
|
|
};
|
|
|
|
// --- Monthly Sales vs Purchase ---
|
|
var monthlyLabels = new[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
|
|
var soMonths = await _context.SalesOrder
|
|
.Where(x => x.OrderDate != null)
|
|
.Select(x => x.OrderDate!.Value)
|
|
.ToListAsync(ct);
|
|
var poMonths = await _context.PurchaseOrder
|
|
.Where(x => x.OrderDate != null)
|
|
.Select(x => x.OrderDate!.Value)
|
|
.ToListAsync(ct);
|
|
var allDates = soMonths.Concat(poMonths)
|
|
.Select(d => new { d.Year, d.Month })
|
|
.Distinct()
|
|
.OrderByDescending(x => x.Year).ThenByDescending(x => x.Month)
|
|
.Take(6)
|
|
.Reverse()
|
|
.ToList();
|
|
var monthlyData = new List<ChartDataPoint>();
|
|
foreach (var ym in allDates)
|
|
{
|
|
var monthStartDt = new DateTime(ym.Year, ym.Month, 1);
|
|
var monthEndDt = monthStartDt.AddMonths(1);
|
|
var salesAmount = await _context.SalesOrder
|
|
.Where(x => x.OrderDate >= monthStartDt && x.OrderDate < monthEndDt)
|
|
.SumAsync(x => (double?)x.AfterTaxAmount ?? 0, ct);
|
|
var purchaseAmount = await _context.PurchaseOrder
|
|
.Where(x => x.OrderDate >= monthStartDt && x.OrderDate < monthEndDt)
|
|
.SumAsync(x => (double?)x.AfterTaxAmount ?? 0, ct);
|
|
monthlyData.Add(new ChartDataPoint
|
|
{
|
|
Label = monthlyLabels[ym.Month - 1],
|
|
SalesAmount = Math.Round(salesAmount / 1000.0, 1),
|
|
PurchaseAmount = Math.Round(purchaseAmount / 1000.0, 1)
|
|
});
|
|
}
|
|
|
|
// --- Weekly Sales vs Purchase ---
|
|
var soWeeks = await _context.SalesOrder
|
|
.Where(x => x.OrderDate != null)
|
|
.Select(x => x.OrderDate!.Value)
|
|
.ToListAsync(ct);
|
|
var poWeeks = await _context.PurchaseOrder
|
|
.Where(x => x.OrderDate != null)
|
|
.Select(x => x.OrderDate!.Value)
|
|
.ToListAsync(ct);
|
|
var allWeekStarts = soWeeks.Concat(poWeeks)
|
|
.Select(d => d.AddDays(-(int)d.DayOfWeek))
|
|
.Distinct()
|
|
.OrderByDescending(w => w)
|
|
.Take(6)
|
|
.Reverse()
|
|
.ToList();
|
|
var weeklyData = new List<ChartDataPoint>();
|
|
foreach (var weekStart in allWeekStarts)
|
|
{
|
|
var weekEnd = weekStart.AddDays(7);
|
|
var salesAmount = await _context.SalesOrder
|
|
.Where(x => x.OrderDate >= weekStart && x.OrderDate < weekEnd)
|
|
.SumAsync(x => (double?)x.AfterTaxAmount ?? 0, ct);
|
|
var purchaseAmount = await _context.PurchaseOrder
|
|
.Where(x => x.OrderDate >= weekStart && x.OrderDate < weekEnd)
|
|
.SumAsync(x => (double?)x.AfterTaxAmount ?? 0, ct);
|
|
weeklyData.Add(new ChartDataPoint
|
|
{
|
|
Label = $"Wk {weekStart:MM/dd}",
|
|
SalesAmount = Math.Round(salesAmount / 1000.0, 1),
|
|
PurchaseAmount = Math.Round(purchaseAmount / 1000.0, 1)
|
|
});
|
|
}
|
|
|
|
// --- Vendor PO Totals ---
|
|
var poVendorRaw = await _context.PurchaseOrder.AsNoTracking()
|
|
.Select(x => new { x.VendorId, x.AfterTaxAmount })
|
|
.ToListAsync(ct);
|
|
var poVendorIds = poVendorRaw.Where(x => x.VendorId != null).Select(x => x.VendorId).Distinct().ToList();
|
|
var poVendorDb = await _context.Vendor.AsNoTracking()
|
|
.Where(v => poVendorIds.Contains(v.Id))
|
|
.Select(v => new { v.Id, v.Name })
|
|
.ToListAsync(ct);
|
|
var poVendorMap = poVendorDb.ToDictionary(v => v.Id, v => v.Name ?? "N/A");
|
|
var vendorPOTotals = poVendorRaw
|
|
.Where(x => x.VendorId != null)
|
|
.GroupBy(x => x.VendorId!)
|
|
.Select(g => new ScmMetricDto
|
|
{
|
|
Label = poVendorMap.GetValueOrDefault(g.Key, "N/A"),
|
|
Value = g.Sum(x => x.AfterTaxAmount ?? 0).ToString("N0"),
|
|
Color = "#6366f1"
|
|
})
|
|
.OrderByDescending(x => decimal.TryParse(x.Value, out var v) ? v : 0)
|
|
.Take(15)
|
|
.ToList();
|
|
|
|
// --- PO Status Breakdown ---
|
|
var poStatusRaw = await _context.PurchaseOrder.AsNoTracking()
|
|
.Select(x => x.OrderStatus)
|
|
.ToListAsync(ct);
|
|
var poStatusBreakdown = poStatusRaw
|
|
.GroupBy(x => x)
|
|
.Select(g => new StatusCountDto
|
|
{
|
|
Status = g.Key.ToString(),
|
|
Count = g.Count(),
|
|
Color = GetPOStatusColor(g.Key.ToString())
|
|
}).ToList();
|
|
|
|
// --- GR Status Breakdown ---
|
|
var grStatusRaw = await _context.GoodsReceive.AsNoTracking()
|
|
.Select(x => x.Status)
|
|
.ToListAsync(ct);
|
|
var grStatusBreakdown = grStatusRaw
|
|
.GroupBy(x => x)
|
|
.Select(g => new StatusCountDto
|
|
{
|
|
Status = g.Key.ToString(),
|
|
Count = g.Count(),
|
|
Color = GetGRStatusColor(g.Key.ToString())
|
|
}).ToList();
|
|
|
|
// --- PR Status Breakdown ---
|
|
var prStatusRaw = await _context.PurchaseRequisition.AsNoTracking()
|
|
.Select(x => x.RequisitionStatus)
|
|
.ToListAsync(ct);
|
|
var prStatusBreakdown = prStatusRaw
|
|
.GroupBy(x => x)
|
|
.Select(g => new StatusCountDto
|
|
{
|
|
Status = g.Key.ToString(),
|
|
Count = g.Count(),
|
|
Color = GetPOStatusColor(g.Key.ToString())
|
|
}).ToList();
|
|
|
|
// --- DO Status Breakdown ---
|
|
var doStatusRaw = await _context.DeliveryOrder.AsNoTracking()
|
|
.Select(x => x.Status)
|
|
.ToListAsync(ct);
|
|
var doStatusBreakdown = doStatusRaw
|
|
.GroupBy(x => x)
|
|
.Select(g => new StatusCountDto
|
|
{
|
|
Status = g.Key.ToString(),
|
|
Count = g.Count(),
|
|
Color = GetGRStatusColor(g.Key.ToString())
|
|
}).ToList();
|
|
|
|
// --- Recent POs ---
|
|
var recentPOsRaw = await _context.PurchaseOrder.AsNoTracking()
|
|
.OrderByDescending(x => x.OrderDate)
|
|
.Take(10)
|
|
.Select(x => new
|
|
{
|
|
x.OrderDate,
|
|
x.AutoNumber,
|
|
x.VendorId,
|
|
x.AfterTaxAmount,
|
|
x.OrderStatus,
|
|
ItemCount = x.PurchaseOrderItemList != null ? x.PurchaseOrderItemList.Count : 0
|
|
})
|
|
.ToListAsync(ct);
|
|
var recentPOVendorIds = recentPOsRaw.Where(x => x.VendorId != null).Select(x => x.VendorId).Distinct().ToList();
|
|
var recentPOVendorDb = await _context.Vendor.AsNoTracking()
|
|
.Where(v => recentPOVendorIds.Contains(v.Id))
|
|
.Select(v => new { v.Id, v.Name })
|
|
.ToListAsync(ct);
|
|
var recentPOVendorMap = recentPOVendorDb.ToDictionary(v => v.Id, v => v.Name ?? "N/A");
|
|
var recentPOs = recentPOsRaw.Select(x => new RecentPurchaseOrderDto
|
|
{
|
|
OrderDate = x.OrderDate,
|
|
Number = x.AutoNumber,
|
|
VendorName = recentPOVendorMap.GetValueOrDefault(x.VendorId ?? "", "N/A"),
|
|
Amount = x.AfterTaxAmount ?? 0,
|
|
Status = x.OrderStatus.ToString(),
|
|
ItemCount = x.ItemCount
|
|
}).ToList();
|
|
|
|
// --- Recent GRs ---
|
|
var recentGRsRaw = await _context.GoodsReceive.AsNoTracking()
|
|
.OrderByDescending(x => x.ReceiveDate)
|
|
.Take(10)
|
|
.Select(x => new
|
|
{
|
|
x.ReceiveDate,
|
|
x.AutoNumber,
|
|
x.PurchaseOrderId,
|
|
x.Status
|
|
})
|
|
.ToListAsync(ct);
|
|
var recentGRPOIds = recentGRsRaw.Where(x => x.PurchaseOrderId != null).Select(x => x.PurchaseOrderId).Distinct().ToList();
|
|
var recentGRPODb = await _context.PurchaseOrder.AsNoTracking()
|
|
.Where(po => recentGRPOIds.Contains(po.Id))
|
|
.Select(po => new { po.Id, po.AutoNumber })
|
|
.ToListAsync(ct);
|
|
var recentGRPOMap = recentGRPODb.ToDictionary(po => po.Id, po => po.AutoNumber ?? "N/A");
|
|
var recentGRs = recentGRsRaw.Select(x => new RecentGoodsReceiveDto
|
|
{
|
|
ReceiveDate = x.ReceiveDate,
|
|
Number = x.AutoNumber,
|
|
PONumber = recentGRPOMap.GetValueOrDefault(x.PurchaseOrderId ?? "", "N/A"),
|
|
Status = x.Status.ToString()
|
|
}).ToList();
|
|
|
|
// --- Product Categories for donut ---
|
|
var prodCatRaw = await _context.Product.AsNoTracking()
|
|
.Select(x => new { x.ProductGroupId })
|
|
.ToListAsync(ct);
|
|
var prodCatIds = prodCatRaw.Where(x => x.ProductGroupId != null).Select(x => x.ProductGroupId).Distinct().ToList();
|
|
var prodCatDb = await _context.ProductGroup.AsNoTracking()
|
|
.Where(g => prodCatIds.Contains(g.Id))
|
|
.Select(g => new { g.Id, g.Name })
|
|
.ToListAsync(ct);
|
|
var prodCatMap = prodCatDb.ToDictionary(g => g.Id, g => g.Name ?? "General");
|
|
var productCategories = prodCatRaw
|
|
.GroupBy(x => prodCatMap.GetValueOrDefault(x.ProductGroupId ?? "", "General"))
|
|
.Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
|
|
.ToList();
|
|
|
|
// --- Invoice Receivable ---
|
|
var totalReceivable = await _context.Invoice.AsNoTracking()
|
|
.Where(x => x.InvoiceStatus == InvoiceStatus.Confirmed || x.InvoiceStatus == InvoiceStatus.PartialPaid)
|
|
.SumAsync(x => x.SalesOrder != null ? (x.SalesOrder.AfterTaxAmount ?? 0) : 0, ct);
|
|
|
|
// --- Bill Payable ---
|
|
var totalPayable = await _context.Bill.AsNoTracking()
|
|
.Where(x => x.BillStatus == BillStatus.Confirmed || x.BillStatus == BillStatus.PartialPaid)
|
|
.SumAsync(x => x.PurchaseOrder != null ? (x.PurchaseOrder.AfterTaxAmount ?? 0) : 0, ct);
|
|
|
|
var res = new DashboardScmResponse
|
|
{
|
|
PendingPR = await _context.PurchaseRequisition.CountAsync(x => x.RequisitionStatus == PurchaseRequisitionStatus.Draft, ct),
|
|
PendingSQ = await _context.SalesQuotation.CountAsync(x => x.QuotationStatus == SalesQuotationStatus.Draft, ct),
|
|
PendingGR = await _context.GoodsReceive.CountAsync(x => x.Status == GoodsReceiveStatus.Draft, ct),
|
|
PendingDO = await _context.DeliveryOrder.CountAsync(x => x.Status == DeliveryOrderStatus.Draft, ct),
|
|
ActiveCustomer = await _context.Customer.CountAsync(ct),
|
|
ActiveVendor = await _context.Vendor.CountAsync(ct),
|
|
InboundQtyMTD = inboundQty,
|
|
OutboundQtyMTD = outboundQty,
|
|
SalesMTD = await _context.SalesOrder.Where(x => x.OrderDate >= start).SumAsync(x => x.AfterTaxAmount ?? 0, ct),
|
|
PurchaseMTD = await _context.PurchaseOrder.Where(x => x.OrderDate >= start).SumAsync(x => x.AfterTaxAmount ?? 0, ct),
|
|
TotalReceivable = totalReceivable,
|
|
TotalPayable = totalPayable,
|
|
ChainAccuracyIndex = Math.Round(accuracy, 1),
|
|
TotalPR = prCount,
|
|
TotalPO = poCount,
|
|
TotalGR = grCount,
|
|
TotalDO = doCount,
|
|
DocGrid = docGrid,
|
|
GlobalFlowLog = globalLogs,
|
|
TopProductsByQty = topProds,
|
|
CustGroupStats = custGroups,
|
|
VendGroupStats = vendGroups,
|
|
CustCategoryStats = custCats,
|
|
VendCategoryStats = vendCats,
|
|
PayMethodStats = payMethods,
|
|
WhseInventoryLevel = whseLevel,
|
|
StockValuePerGroup = stockValGroup,
|
|
CashFlowMTD = new List<ScmMetricDto>
|
|
{
|
|
new() { Label = "Inflow (Receive)", Value = (await _context.PaymentReceive.Where(x => x.PaymentDate >= start).SumAsync(x => x.PaymentAmount ?? 0, ct)).ToString("N0"), Color = "#10b981" },
|
|
new() { Label = "Outflow (Disburse)", Value = (await _context.PaymentDisburse.Where(x => x.PaymentDate >= start).SumAsync(x => x.PaymentAmount ?? 0, ct)).ToString("N0"), Color = "#ef4444" }
|
|
},
|
|
MonthlySalesVsPurchase = monthlyData,
|
|
WeeklySalesVsPurchase = weeklyData,
|
|
VendorPOTotals = vendorPOTotals,
|
|
ProductCategories = productCategories,
|
|
POStatusBreakdown = poStatusBreakdown,
|
|
GRStatusBreakdown = grStatusBreakdown,
|
|
PRStatusBreakdown = prStatusBreakdown,
|
|
DOStatusBreakdown = doStatusBreakdown,
|
|
RecentPOs = recentPOs,
|
|
RecentGRs = recentGRs
|
|
};
|
|
|
|
return res;
|
|
}
|
|
|
|
private static string GetPOStatusColor(string status) => status switch
|
|
{
|
|
"Draft" => "#f59e0b",
|
|
"Confirmed" => "#2563eb",
|
|
"Closed" => "#10b981",
|
|
"Cancelled" => "#e11d48",
|
|
_ => "#64748b"
|
|
};
|
|
|
|
private static string GetGRStatusColor(string status) => status switch
|
|
{
|
|
"Draft" => "#f59e0b",
|
|
"Confirmed" => "#2563eb",
|
|
"Closed" => "#10b981",
|
|
"Cancelled" => "#e11d48",
|
|
_ => "#64748b"
|
|
};
|
|
} |