357 lines
20 KiB
C#
357 lines
20 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 DealStageDto { public string? Stage { get; set; } public int Count { get; set; } public string? Color { get; set; } }
|
|
public class DocSourceDto { public string? Source { get; set; } public int Count { get; set; } public double Percentage { get; set; } public string? Color { get; set; } }
|
|
public class DocMetricDto { public string? Label { get; set; } public double Current { get; set; } public double Target { get; set; } public double PctValue { get; set; } public string? BarColor { get; set; } }
|
|
public class OrderActivityDto { public string? Category { get; set; } public int Count { get; set; } public string? Color { get; set; } }
|
|
public class RecentOrderDto { public string? OrderNumber { get; set; } public string? Description { get; set; } public decimal Value { get; set; } public string? Stage { get; set; } public string? Status { get; set; } public DateTime? Date { get; set; } }
|
|
public class ActivityFeedDto { public string? Title { get; set; } public string? Detail { get; set; } public string? TimeAgo { get; set; } public string? ChipLabel { get; set; } public string? ChipColor { get; set; } public string? AvatarBg { get; set; } public string? AvatarText { get; set; } }
|
|
public class PipelineSummaryDto { public string? Label { get; set; } public decimal Value { get; set; } public string? IconColorClass { get; set; } public string? FormattedValue { get; set; } }
|
|
public class SalesPerformanceDto { public string? Name { get; set; } public decimal Achievement { get; set; } public int Deals { get; set; } public double WinRate { get; set; } }
|
|
public class PendingInvoiceDto { public string? Number { get; set; } public string? Customer { get; set; } public decimal Amount { get; set; } public string? Status { get; set; } }
|
|
public class RecentLeadDto { public string? LeadTitle { get; set; } public string? Company { get; set; } public decimal Amount { get; set; } public string? Stage { get; set; } }
|
|
|
|
public class DashboardOmsResponse
|
|
{
|
|
public int TotalSalesOrders { get; set; }
|
|
public int TotalPurchaseOrders { get; set; }
|
|
public decimal MonthlySales { get; set; }
|
|
public decimal MonthlyPurchase { get; set; }
|
|
public int TotalCustomers { get; set; }
|
|
public int TotalVendors { get; set; }
|
|
public decimal PendingReceivable { get; set; }
|
|
public decimal TotalPayable { get; set; }
|
|
public decimal TotalReceivable { get; set; }
|
|
public int ActiveDealsCount { get; set; }
|
|
public decimal AvgOrderValue { get; set; }
|
|
public double OrderGrowthRate { get; set; }
|
|
public int PendingSODraft { get; set; }
|
|
public int PendingPODraft { get; set; }
|
|
public int TotalProducts { get; set; }
|
|
public int TotalInvoices { get; set; }
|
|
public int TotalBills { get; set; }
|
|
public double SlaPercentage { get; set; }
|
|
public double CustomerRetention { get; set; }
|
|
public double TargetGrowth { get; set; }
|
|
|
|
public List<DealStageDto> DealStages { get; set; } = new();
|
|
public List<DocSourceDto> OrderSources { get; set; } = new();
|
|
public List<DocMetricDto> OrderMetrics { get; set; } = new();
|
|
public List<OrderActivityDto> OrderActivities { get; set; } = new();
|
|
public List<RecentOrderDto> RecentOrders { get; set; } = new();
|
|
public List<ActivityFeedDto> ActivityFeeds { get; set; } = new();
|
|
public List<PipelineSummaryDto> PipelineSummary { get; set; } = new();
|
|
public List<SalesPerformanceDto> SalesLeaderboard { get; set; } = new();
|
|
public List<PendingInvoiceDto> OverdueInvoices { get; set; } = new();
|
|
public List<RecentLeadDto> RecentVendors { get; set; } = new();
|
|
public List<ScmMetricDto> DocGrid { get; set; } = new();
|
|
public List<ChartDataPoint> RevenueTrend { get; set; } = new();
|
|
|
|
public string FormattedMonthlySales => FormatNumber(MonthlySales);
|
|
public string FormattedMonthlyPurchase => FormatNumber(MonthlyPurchase);
|
|
public string FormattedPipeline => FormatNumber(PendingReceivable);
|
|
public string FormattedPendingPayments => FormatNumber(PendingReceivable);
|
|
public string FormattedTotalPayable => FormatNumber(TotalPayable);
|
|
public string FormattedTotalReceivable => FormatNumber(TotalReceivable);
|
|
public string FormattedAvgOrderValue => FormatNumber(AvgOrderValue);
|
|
public string FormattedTotalSalesOrders => TotalSalesOrders.ToString("N0");
|
|
public string FormattedTotalPurchaseOrders => TotalPurchaseOrders.ToString("N0");
|
|
public string FormattedTotalCustomers => TotalCustomers.ToString("N0");
|
|
|
|
private string FormatNumber(decimal value)
|
|
{
|
|
if (value >= 1000000)
|
|
return (value / 1000000m).ToString("F1") + "M";
|
|
if (value >= 1000)
|
|
return (value / 1000m).ToString("F1") + "K";
|
|
return value.ToString("N0");
|
|
}
|
|
}
|
|
|
|
public record GetOmsDashboardQuery() : IRequest<DashboardOmsResponse>;
|
|
|
|
public class DashboardHandler : IRequestHandler<GetOmsDashboardQuery, DashboardOmsResponse>
|
|
{
|
|
private readonly AppDbContext _context;
|
|
public DashboardHandler(AppDbContext context) => _context = context;
|
|
|
|
public async Task<DashboardOmsResponse> Handle(GetOmsDashboardQuery request, CancellationToken ct)
|
|
{
|
|
var today = DateTime.Today;
|
|
var monthStart = new DateTime(today.Year, today.Month, 1);
|
|
|
|
// Monthly chart data
|
|
var monthlyLabels = new[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
|
|
var monthlyData = new List<ChartDataPoint>();
|
|
|
|
var soRaw = await _context.SalesOrder
|
|
.Where(x => x.OrderDate != null)
|
|
.Select(x => new { x.OrderDate, x.AfterTaxAmount })
|
|
.ToListAsync(ct);
|
|
var poRaw = await _context.PurchaseOrder
|
|
.Where(x => x.OrderDate != null)
|
|
.Select(x => new { x.OrderDate, x.AfterTaxAmount })
|
|
.ToListAsync(ct);
|
|
|
|
var allDates = soRaw.Select(d => d.OrderDate!.Value)
|
|
.Concat(poRaw.Select(d => d.OrderDate!.Value))
|
|
.Select(d => new { d.Year, d.Month })
|
|
.Distinct()
|
|
.OrderByDescending(x => x.Year).ThenByDescending(x => x.Month)
|
|
.Take(6).Reverse()
|
|
.ToList();
|
|
foreach (var ym in allDates)
|
|
{
|
|
var ms = new DateTime(ym.Year, ym.Month, 1);
|
|
var me = ms.AddMonths(1);
|
|
var salesAmount = soRaw.Where(x => x.OrderDate >= ms && x.OrderDate < me).Sum(x => (double)(x.AfterTaxAmount ?? 0));
|
|
var purchaseAmount = poRaw.Where(x => x.OrderDate >= ms && x.OrderDate < me).Sum(x => (double)(x.AfterTaxAmount ?? 0));
|
|
monthlyData.Add(new ChartDataPoint
|
|
{
|
|
Label = monthlyLabels[ym.Month - 1],
|
|
SalesAmount = Math.Round(salesAmount / 1000.0, 1),
|
|
PurchaseAmount = Math.Round(purchaseAmount / 1000.0, 1)
|
|
});
|
|
}
|
|
|
|
// Sequential queries (DbContext is not thread-safe)
|
|
var totalSO = await _context.SalesOrder.CountAsync(ct);
|
|
var totalPO = await _context.PurchaseOrder.CountAsync(ct);
|
|
var totalCust = await _context.Customer.CountAsync(ct);
|
|
var totalVend = await _context.Vendor.CountAsync(ct);
|
|
var totalInv = await _context.Invoice.CountAsync(ct);
|
|
var totalBill = await _context.Bill.CountAsync(ct);
|
|
var totalPR = await _context.PaymentReceive.CountAsync(ct);
|
|
var totalPD = await _context.PaymentDisburse.CountAsync(ct);
|
|
var totalProd = await _context.Set<Product>().CountAsync(ct);
|
|
var pendingSO = await _context.SalesOrder.CountAsync(x => x.OrderStatus == SalesOrderStatus.Draft, ct);
|
|
var pendingPO = await _context.PurchaseOrder.CountAsync(x => x.OrderStatus == PurchaseOrderStatus.Draft, ct);
|
|
var salesMTD = await _context.SalesOrder.Where(x => x.OrderDate >= monthStart).SumAsync(x => x.AfterTaxAmount ?? 0, ct);
|
|
var purchaseMTD = await _context.PurchaseOrder.Where(x => x.OrderDate >= monthStart).SumAsync(x => x.AfterTaxAmount ?? 0, ct);
|
|
var pendingAR = await _context.Invoice.Where(x => x.InvoiceStatus != InvoiceStatus.Confirmed).SumAsync(x => x.SalesOrder!.AfterTaxAmount ?? 0, ct);
|
|
var totalReceivable = await _context.Invoice.Where(x => x.InvoiceStatus == InvoiceStatus.Confirmed).SumAsync(x => x.SalesOrder!.AfterTaxAmount ?? 0, ct);
|
|
var totalPayable = await _context.Bill.Where(x => x.BillStatus == BillStatus.Confirmed).SumAsync(x => x.PurchaseOrder!.AfterTaxAmount ?? 0, ct);
|
|
|
|
// Doc stages (like deal stages)
|
|
var confirmedSO = await _context.SalesOrder.CountAsync(x => x.OrderStatus == SalesOrderStatus.Confirmed, ct);
|
|
var confirmedPO = await _context.PurchaseOrder.CountAsync(x => x.OrderStatus == PurchaseOrderStatus.Confirmed, ct);
|
|
var dealStages = new List<DealStageDto>
|
|
{
|
|
new() { Stage = "Draft SO", Count = pendingSO, Color = "#8b5cf6" },
|
|
new() { Stage = "Confirmed SO", Count = confirmedSO, Color = "#f59e0b" },
|
|
new() { Stage = "Draft PO", Count = pendingPO, Color = "#e11d48" },
|
|
new() { Stage = "Confirmed PO", Count = confirmedPO, Color = "#6366f1" }
|
|
};
|
|
|
|
// Doc sources (order types)
|
|
var totalDocs = totalSO + totalPO + totalInv + totalBill;
|
|
var orderSources = new List<DocSourceDto>
|
|
{
|
|
new() { Source = "Sales Orders", Count = totalSO, Percentage = totalDocs > 0 ? Math.Round((double)totalSO / totalDocs * 100, 1) : 0, Color = "#2563eb" },
|
|
new() { Source = "Purch Orders", Count = totalPO, Percentage = totalDocs > 0 ? Math.Round((double)totalPO / totalDocs * 100, 1) : 0, Color = "#f97316" },
|
|
new() { Source = "Invoices", Count = totalInv, Percentage = totalDocs > 0 ? Math.Round((double)totalInv / totalDocs * 100, 1) : 0, Color = "#ec4899" },
|
|
new() { Source = "Bills", Count = totalBill, Percentage = totalDocs > 0 ? Math.Round((double)totalBill / totalDocs * 100, 1) : 0, Color = "#8b5cf6" },
|
|
new() { Source = "Payments", Count = totalPR + totalPD, Percentage = totalDocs > 0 ? Math.Round((double)(totalPR + totalPD) / totalDocs * 100, 1) : 0, Color = "#06b6d4" }
|
|
};
|
|
|
|
// Order metrics (like conversion metrics)
|
|
var orderMetrics = new List<DocMetricDto>
|
|
{
|
|
new() { Label = "SO Draft→Confirm", Current = confirmedSO, Target = totalSO, PctValue = totalSO > 0 ? Math.Round((double)confirmedSO / totalSO * 100, 1) : 0, BarColor = "#2563eb" },
|
|
new() { Label = "SO→Invoice", Current = totalInv, Target = totalSO, PctValue = totalSO > 0 ? Math.Round((double)totalInv / totalSO * 100, 1) : 0, BarColor = "#f97316" },
|
|
new() { Label = "PO Draft→Confirm", Current = confirmedPO, Target = totalPO, PctValue = totalPO > 0 ? Math.Round((double)confirmedPO / totalPO * 100, 1) : 0, BarColor = "#ec4899" },
|
|
new() { Label = "Overall Fulfillment", Current = totalInv + totalBill, Target = totalSO + totalPO, PctValue = (totalSO + totalPO) > 0 ? Math.Round((double)(totalInv + totalBill) / (totalSO + totalPO) * 100, 1) : 0, BarColor = "#10b981" }
|
|
};
|
|
|
|
// Order activities
|
|
var orderActivities = new List<OrderActivityDto>
|
|
{
|
|
new() { Category = "Sales Orders", Count = totalSO, Color = "#10b981" },
|
|
new() { Category = "Purch Orders", Count = totalPO, Color = "#e11d48" },
|
|
new() { Category = "Invoices", Count = totalInv, Color = "#e11d48" },
|
|
new() { Category = "Total Docs", Count = totalDocs, Color = "#6366f1" }
|
|
};
|
|
|
|
// Recent orders
|
|
var recentOrders = await _context.SalesOrder
|
|
.AsNoTracking()
|
|
.OrderByDescending(x => x.OrderDate)
|
|
.Take(4)
|
|
.Select(x => new RecentOrderDto
|
|
{
|
|
OrderNumber = x.AutoNumber,
|
|
Description = x.Description,
|
|
Value = x.AfterTaxAmount ?? 0,
|
|
Stage = x.OrderStatus.ToString(),
|
|
Status = x.OrderStatus == SalesOrderStatus.Confirmed ? "Posted" : x.OrderStatus == SalesOrderStatus.Cancelled ? "Cancelled" : "Pending",
|
|
Date = x.OrderDate
|
|
}).ToListAsync(ct);
|
|
|
|
// Pipeline summary
|
|
var pipelineSummaryList = new List<PipelineSummaryDto>
|
|
{
|
|
new() { Label = "MTD Sales", Value = salesMTD, FormattedValue = FormatDecimal(salesMTD), IconColorClass = "gc1" },
|
|
new() { Label = "MTD Purchase", Value = purchaseMTD, FormattedValue = FormatDecimal(purchaseMTD), IconColorClass = "gc10" },
|
|
new() { Label = "Avg Order Value", Value = totalSO > 0 ? salesMTD / totalSO : 0, IconColorClass = "gc2" }
|
|
};
|
|
|
|
// Activity feed from recent SO
|
|
var recentSOs = await _context.SalesOrder
|
|
.AsNoTracking()
|
|
.OrderByDescending(x => x.OrderDate).Take(4)
|
|
.Select(x => new { x.AutoNumber, x.OrderDate, x.Customer!.Name })
|
|
.ToListAsync(ct);
|
|
|
|
var activityFeed = recentSOs.Select((so, i) => new ActivityFeedDto
|
|
{
|
|
Title = $"Sales Order #{so.AutoNumber}",
|
|
Detail = $"Customer: {so.Name}",
|
|
TimeAgo = so.OrderDate.HasValue ? $"{(int)(DateTime.Now - so.OrderDate.Value).TotalDays}d ago" : "",
|
|
ChipLabel = "SO Created",
|
|
ChipColor = i % 2 == 0 ? "chip-green" : "chip-indigo",
|
|
AvatarBg = i switch { 0 => "#10b981", 1 => "#2563eb", 2 => "#6366f1", _ => "#f97316" },
|
|
AvatarText = "SO"
|
|
}).ToList();
|
|
|
|
// Customer performance (top customers by count)
|
|
var topCustRaw = await _context.SalesOrder
|
|
.AsNoTracking()
|
|
.Where(x => x.Customer != null)
|
|
.GroupBy(x => x.Customer!.Name)
|
|
.OrderByDescending(g => g.Count())
|
|
.Take(4)
|
|
.Select(g => new { Name = g.Key ?? "N/A", Cnt = g.Count(), Val = g.Sum(x => x.AfterTaxAmount ?? 0) })
|
|
.ToListAsync(ct);
|
|
|
|
var salesLeaderboard = topCustRaw.Select(c => new SalesPerformanceDto
|
|
{
|
|
Name = c.Name,
|
|
Achievement = c.Val,
|
|
Deals = c.Cnt,
|
|
WinRate = totalSO > 0 ? Math.Round((double)c.Cnt / totalSO * 100, 1) : 0
|
|
}).ToList();
|
|
|
|
// Overdue invoices
|
|
var overdueInvoices = await _context.Invoice
|
|
.AsNoTracking()
|
|
.Where(x => x.InvoiceStatus != InvoiceStatus.Confirmed && x.InvoiceStatus != InvoiceStatus.Cancelled)
|
|
.OrderByDescending(x => x.CreatedAt).Take(5)
|
|
.Select(x => new PendingInvoiceDto
|
|
{
|
|
Number = x.AutoNumber,
|
|
Customer = x.SalesOrder!.Customer!.Name,
|
|
Amount = x.SalesOrder!.AfterTaxAmount ?? 0,
|
|
Status = x.InvoiceStatus.ToString()
|
|
}).ToListAsync(ct);
|
|
|
|
// Recent vendors (like recent leads)
|
|
var recentVendors = await _context.Vendor
|
|
.AsNoTracking()
|
|
.OrderBy(x => x.Name)
|
|
.Take(5)
|
|
.Select(x => new RecentLeadDto
|
|
{
|
|
LeadTitle = x.Name,
|
|
Company = x.VendorGroup != null ? x.VendorGroup.Name : "General",
|
|
Amount = 0,
|
|
Stage = "Active"
|
|
}).ToListAsync(ct);
|
|
|
|
// Compute derived values
|
|
var avgOrderValue = totalSO > 0 ? salesMTD / totalSO : 0;
|
|
var orderGrowthRate = totalSO > 0 ? Math.Round((double)totalInv / totalSO * 100, 1) : 42;
|
|
var slaPercentage = totalSO > 0 ? Math.Round((double)confirmedSO / totalSO * 100, 1) : 95;
|
|
var customerRetention = totalCust > 0 ? Math.Round((double)(totalCust - pendingSO) / totalCust * 100, 1) : 92;
|
|
|
|
// Doc grid for metrics matrix
|
|
var docGrid = new List<ScmMetricDto>
|
|
{
|
|
new() { Label = "Sales Orders", Value = totalSO.ToString(), Color = "#2563eb" },
|
|
new() { Label = "Purch Orders", Value = totalPO.ToString(), Color = "#10b981" },
|
|
new() { Label = "Invoices", Value = totalInv.ToString(), Color = "#ec4899" },
|
|
new() { Label = "Bills", Value = totalBill.ToString(), Color = "#8b5cf6" },
|
|
new() { Label = "Pay Receive", Value = totalPR.ToString(), Color = "#f59e0b" },
|
|
new() { Label = "Pay Disburse", Value = totalPD.ToString(), Color = "#06b6d4" },
|
|
new() { Label = "Customers", Value = totalCust.ToString("N0"), Color = "#14b8a6" },
|
|
new() { Label = "Vendors", Value = totalVend.ToString("N0"), Color = "#f97316" },
|
|
new() { Label = "Products", Value = totalProd.ToString("N0"), Color = "#059669" },
|
|
new() { Label = "Draft SO", Value = pendingSO.ToString(), Color = "#e11d48" },
|
|
new() { Label = "Draft PO", Value = pendingPO.ToString(), Color = "#db2777" },
|
|
new() { Label = "Pending AR", Value = FormatDecimal(pendingAR), Color = "#d97706" },
|
|
new() { Label = "Pending AP", Value = FormatDecimal(totalPayable), Color = "#0284c7" },
|
|
new() { Label = "Avg SO Value", Value = FormatDecimal(totalSO > 0 ? salesMTD / totalSO : 0), Color = "#65a30d" },
|
|
new() { Label = "Avg PO Value", Value = FormatDecimal(totalPO > 0 ? purchaseMTD / totalPO : 0), Color = "#9333ea" },
|
|
new() { Label = "SLA Target", Value = "95%", Color = "#ea580c" },
|
|
new() { Label = "Growth Rate", Value = $"{orderGrowthRate:F1}%", Color = "#0d9488" },
|
|
new() { Label = "Retention", Value = $"{customerRetention:F1}%", Color = "#7c3aed" },
|
|
new() { Label = "AR Balance", Value = FormatDecimal(totalReceivable), Color = "#0891b2" },
|
|
new() { Label = "AP Balance", Value = FormatDecimal(totalPayable), Color = "#db2777" }
|
|
};
|
|
|
|
return new DashboardOmsResponse
|
|
{
|
|
TotalSalesOrders = totalSO,
|
|
TotalPurchaseOrders = totalPO,
|
|
MonthlySales = salesMTD,
|
|
MonthlyPurchase = purchaseMTD,
|
|
TotalCustomers = totalCust,
|
|
TotalVendors = totalVend,
|
|
PendingReceivable = pendingAR,
|
|
TotalPayable = totalPayable,
|
|
TotalReceivable = totalReceivable,
|
|
ActiveDealsCount = pendingSO + pendingPO,
|
|
AvgOrderValue = avgOrderValue,
|
|
OrderGrowthRate = orderGrowthRate,
|
|
PendingSODraft = pendingSO,
|
|
PendingPODraft = pendingPO,
|
|
TotalProducts = totalProd,
|
|
TotalInvoices = totalInv,
|
|
TotalBills = totalBill,
|
|
SlaPercentage = slaPercentage,
|
|
CustomerRetention = customerRetention,
|
|
TargetGrowth = 5.0,
|
|
DealStages = dealStages,
|
|
OrderSources = orderSources,
|
|
OrderMetrics = orderMetrics,
|
|
OrderActivities = orderActivities,
|
|
RecentOrders = recentOrders,
|
|
ActivityFeeds = activityFeed,
|
|
PipelineSummary = pipelineSummaryList,
|
|
SalesLeaderboard = salesLeaderboard,
|
|
OverdueInvoices = overdueInvoices,
|
|
RecentVendors = recentVendors,
|
|
DocGrid = docGrid,
|
|
RevenueTrend = monthlyData
|
|
};
|
|
}
|
|
|
|
private static string FormatDecimal(decimal value)
|
|
{
|
|
if (value >= 1000000)
|
|
return (value / 1000000m).ToString("F1") + "M";
|
|
if (value >= 1000)
|
|
return (value / 1000m).ToString("F1") + "K";
|
|
return value.ToString("N0");
|
|
}
|
|
} |