using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Microsoft.EntityFrameworkCore; using eprintServer.Data; using eprintServer.Enums; using eprintServer.DTOs; using eprintServer.Responses; using QuestPDF.Fluent; using QuestPDF.Helpers; using QuestPDF.Infrastructure; namespace eprintServer.Services; public class StatsService { private readonly DataContext _context; private readonly EmailService _emailService; public StatsService(DataContext context, EmailService emailService) { _context = context; _emailService = emailService; } public async Task GetDashboardStatsAsync() { var stats = new DashboardStatsDto(); // 1. Total Orders count try { stats.TotalOrders = await _context.Orders.CountAsync(); } catch { stats.TotalOrders = 0; } // 2. Total Revenue try { // Now that the DB is set to Integer, we can query directly stats.TotalRevenue = await _context.Invoices .Where(i => i.Status == InvoiceStatus.PAID) .SumAsync(i => (double)i.TotalAmount); } catch { stats.TotalRevenue = 0; } // 3. Total Customers try { stats.TotalCustomers = await _context.Users .Where(u => u.Role == RoleEnum.ROLE_USER) .CountAsync(); } catch { stats.TotalCustomers = 0; } // 4. Pending Orders count try { stats.PendingOrders = await _context.Orders .Where(o => o.Status == OrderStatus.PENDING) .CountAsync(); } catch { stats.PendingOrders = 0; } return stats; } public async Task ProcessAndSendReport(ReportDateEum type, string adminEmail) { var metadata = GetReportMetadata(type); var stats = await GetAdminStatsAsync(metadata.start, metadata.end); byte[] pdf = GenerateStatsPdf(stats, metadata.title); var emailService = new EmailService(); // In real project, use Dependency Injection await emailService.SendReportEmailAsync(pdf, type, adminEmail); } public async Task GetAdminStatsAsync(DateTime startDate, DateTime endDate) { var response = new AdminStatsResponse(); // CRITICAL FIX: Ensure input dates are UTC and normalize them // .Date resets the "Kind" to Unspecified, so we must re-specify it as Utc var start = DateTime.SpecifyKind(startDate.Date, DateTimeKind.Utc); var end = DateTime.SpecifyKind(endDate.Date.AddDays(1).AddTicks(-1), DateTimeKind.Utc); // 1. Total Orders try { response.TotalOrder = await _context.Orders .CountAsync(o => o.Timestamp >= start && o.Timestamp <= end); } catch { response.TotalOrder = 0; } // 2. Total Paid Orders try { response.TotalPaidOrder = await _context.Invoices .CountAsync(i => i.Timestamp >= start && i.Timestamp <= end && i.Status == InvoiceStatus.PAID); } catch { response.TotalPaidOrder = 0; } // 3. Total Paid Amount try { var totalAmount = await _context.Invoices .Where(i => i.Timestamp >= start && i.Timestamp <= end && i.Status == InvoiceStatus.PAID) .SumAsync(i => i.TotalAmount); response.TotalPaidAmount = (double)totalAmount; } catch { response.TotalPaidAmount = 0.0; } // 4. Detailed Order Items if (response.TotalPaidAmount > 0) { try { var paidInvoices = await _context.Invoices .Include(i => i.InvoiceItems).ThenInclude(ii => ii.SellableProduct).ThenInclude(p => p.Names) .Include(i => i.InvoiceItems).ThenInclude(ii => ii.CustomizableProduct).ThenInclude(p => p.Names) .Where(i => i.Timestamp >= start && i.Timestamp <= end && i.Status == InvoiceStatus.PAID) .ToListAsync(); foreach (var invoice in paidInvoices) { foreach (var item in invoice.InvoiceItems) { string name = "Unknown Product"; if (item.CustomizableProduct != null) { name = item.CustomizableProduct.Names .FirstOrDefault(n => n.LanguageTag == "en")?.Value ?? name; } else if (item.SellableProduct != null) { name = item.SellableProduct.Names .FirstOrDefault(n => n.LanguageTag == "en")?.Value ?? name; } response.OrderItems.Add(new OrderItemStats { Name = name, Quantity = (double)item.Quantity }); } } } catch { response.OrderItems = new List(); } } // 5. Refund Amounts try { var totalRefund = await _context.Invoices .Where(i => i.RefundDate >= start && i.RefundDate <= end && i.Status == InvoiceStatus.REFUNDED) .SumAsync(i => i.TotalAmount); response.RefundAmounts = (double)totalRefund; } catch { response.RefundAmounts = 0.0; } // 6. Total New Customers try { response.TotalNewCustomers = await _context.Users .CountAsync(u => u.Timestamp >= start && u.Timestamp <= end && u.Role == RoleEnum.ROLE_USER); } catch { response.TotalNewCustomers = 0; } return response; } public byte[] GenerateStatsPdf(AdminStatsResponse stats, string reportTitle) { QuestPDF.Settings.License = LicenseType.Community; return Document.Create(container => { container.Page(page => { page.Size(PageSizes.A4); page.Margin(1, Unit.Centimetre); page.PageColor(Colors.White); page.DefaultTextStyle(x => x.FontSize(10).FontFamily(Fonts.Verdana)); // Header page.Header().Row(row => { row.RelativeItem().Column(col => { col.Item().Text(reportTitle).FontSize(16).SemiBold().FontColor(Colors.Blue.Medium); col.Item().Text($"Generated on: {DateTime.Now:f}").FontSize(9).Italic(); }); }); // Content page.Content().PaddingVertical(1, Unit.Centimetre).Column(column => { column.Spacing(15); column.Item().PaddingBottom(5).Text("Summary Statistics").FontSize(14).SemiBold(); // Replacement for the obsolete Grid column.Item().Table(table => { table.ColumnsDefinition(columns => { columns.RelativeColumn(); columns.RelativeColumn(); columns.RelativeColumn(); }); // Row 1 table.Cell().Padding(5).Element(Block).Text($"Total Orders: {stats.TotalOrder}"); table.Cell().Padding(5).Element(Block).Text($"Paid Orders: {stats.TotalPaidOrder}"); table.Cell().Padding(5).Element(Block).Text($"New Customers: {stats.TotalNewCustomers}"); // Row 2 table.Cell().Padding(5).Element(Block).Text($"Paid Amount: {stats.TotalPaidAmount:N2} OMR"); table.Cell().Padding(5).Element(Block).Text($"Refunded: {stats.RefundAmounts:N2} OMR").FontColor(Colors.Red.Medium); table.Cell().Padding(5).Element(EmptyBlock); // This was missing }); // Products Table if (stats.OrderItems != null && stats.OrderItems.Any()) { column.Item().PaddingTop(10).Text("Product Breakdown").FontSize(14).SemiBold(); column.Item().Table(table => { table.ColumnsDefinition(cols => { cols.RelativeColumn(); cols.ConstantColumn(80); }); table.Header(header => { header.Cell().BorderBottom(1).PaddingVertical(5).Text("Name").SemiBold(); header.Cell().BorderBottom(1).PaddingVertical(5).Text("Qty").SemiBold(); }); foreach (var item in stats.OrderItems) { table.Cell().BorderBottom(1).BorderColor(Colors.Grey.Lighten3).PaddingVertical(5).Text(item.Name); table.Cell().BorderBottom(1).BorderColor(Colors.Grey.Lighten3).PaddingVertical(5).Text(item.Quantity.ToString()); } }); } }); }); }).GeneratePdf(); } // --- HELPER METHODS --- private IContainer Block(IContainer container) { return container .Border(1) .BorderColor(Colors.Grey.Lighten2) .Background(Colors.Grey.Lighten4) .Padding(10) .AlignCenter() .AlignMiddle(); } private IContainer EmptyBlock(IContainer container) { return container.Background(Colors.White); } public (DateTime start, DateTime end, string title) GetReportMetadata(ReportDateEum type) { // Use UtcNow instead of Now DateTime now = DateTime.UtcNow; DateTime start; DateTime end; string titlePrefix; switch (type) { case ReportDateEum.DAILY: start = now.Date; end = now.Date.AddDays(1).AddTicks(-1); // End of today titlePrefix = "Daily Report"; break; case ReportDateEum.WEEKLY: int diff = (7 + (now.DayOfWeek - DayOfWeek.Sunday)) % 7; start = now.AddDays(-1 * diff).Date; end = start.AddDays(7).AddTicks(-1); // End of the 7th day titlePrefix = "Weekly Report"; break; case ReportDateEum.MONTHLY: start = new DateTime(now.Year, now.Month, 1); end = start.AddMonths(1).AddTicks(-1); titlePrefix = "Monthly Report"; break; case ReportDateEum.YEARLY: start = new DateTime(now.Year, 1, 1); end = new DateTime(now.Year, 12, 31, 23, 59, 59); titlePrefix = "Yearly Report"; break; default: start = now.Date; end = now.Date; titlePrefix = "Stats Report"; break; } // CRITICAL FIX: Explicitly specify UTC Kind for PostgreSQL compatibility start = DateTime.SpecifyKind(start, DateTimeKind.Utc); end = DateTime.SpecifyKind(end, DateTimeKind.Utc); string dateRangeStr = start.Date == end.Date ? start.ToString("dd/MM/yyyy") : $"{start:dd/MM/yyyy} to {end:dd/MM/yyyy}"; return (start, end, $"This is a {titlePrefix} of Print Dashboard ({dateRangeStr})"); } /* public async Task> GetProductStatsAsync( bool isSellable, DateTime? startFilterDate, DateTime? endFilterDate, int page, int size) { // 1. Base Query with Filtered Invoices var query = _context.OrderItems .Where(oi => oi.Invoice != null && oi.Invoice.Status == InvoiceStatus.PAID); // 2. Apply Date Filters if (startFilterDate.HasValue) query = query.Where(oi => oi.Invoice.Timestamp >= startFilterDate.Value); if (endFilterDate.HasValue) query = query.Where(oi => oi.Invoice.Timestamp <= endFilterDate.Value); // 3. Define the source and GROUP BY ID ONLY to avoid translation issues IQueryable statsQuery; if (isSellable) { statsQuery = query .Where(oi => oi.SellableProductId != null) .GroupBy(oi => oi.SellableProductId) // Group by the foreign key (int) .Select(g => new ProductStatDto { // We pick the Names from the first item in the group Names = g.First().SellableProduct.Names, UnitsSold = g.Sum(oi => oi.Quantity), Revenue = g.Sum(oi => (decimal)oi.Quantity * oi.UnitPrice) }); } else { statsQuery = query .Where(oi => oi.CustomizableProductId != null) .GroupBy(oi => oi.CustomizableProductId) // Group by the foreign key (long) .Select(g => new ProductStatDto { Names = g.First().CustomizableProduct.Names, UnitsSold = g.Sum(oi => oi.Quantity), Revenue = g.Sum(oi => (decimal)oi.Quantity * oi.UnitPrice) }); } // 4. Order by Revenue Descending var orderedQuery = statsQuery.OrderByDescending(x => x.Revenue); // 5. Total Count for Pagination var totalCount = await orderedQuery.CountAsync(); // 6. Final Result with Pagination var items = await orderedQuery .Skip((page - 1) * size) .Take(size) .ToListAsync(); return new PagedResult(items, totalCount, page, size); } */ public async Task> GetProductStatsAsync( bool? isSellable, // Changed to bool? to support: True (Sellable), False (Custom), Null (Both) DateTime? startFilterDate, DateTime? endFilterDate, int page, int size) { // 1. Base query for Paid Invoices var baseQuery = _context.OrderItems .Where(oi => oi.Invoice != null && oi.Invoice.Status == InvoiceStatus.PAID); // 2. Apply Date Filters if (startFilterDate.HasValue) baseQuery = baseQuery.Where(oi => oi.Invoice.Timestamp >= startFilterDate.Value); if (endFilterDate.HasValue) baseQuery = baseQuery.Where(oi => oi.Invoice.Timestamp <= endFilterDate.Value); // 3. Prepare Sub-Queries // Query for Sellable Products var sellableQuery = baseQuery .Where(oi => oi.SellableProductId != null) .GroupBy(oi => oi.SellableProductId) .Select(g => new ProductStatDto { Names = g.First().SellableProduct.Names, UnitsSold = g.Sum(oi => oi.Quantity), Revenue = g.Sum(oi => (decimal)oi.Quantity * oi.UnitPrice) }); // Query for Customizable Products var customQuery = baseQuery .Where(oi => oi.CustomizableProductId != null) .GroupBy(oi => oi.CustomizableProductId) .Select(g => new ProductStatDto { Names = g.First().CustomizableProduct.Names, UnitsSold = g.Sum(oi => oi.Quantity), Revenue = g.Sum(oi => (decimal)oi.Quantity * oi.UnitPrice) }); // 4. Combine based on input IQueryable finalQuery; if (isSellable == true) { finalQuery = sellableQuery; } else if (isSellable == false) { finalQuery = customQuery; } else { // "Both" - Combine results from both tables finalQuery = sellableQuery.Union(customQuery); } // 5. Order and Paginate // Note: OrderBy must happen after Union var orderedQuery = finalQuery.OrderByDescending(x => x.Revenue); var totalCount = await orderedQuery.CountAsync(); var items = await orderedQuery .Skip((page - 1) * size) .Take(size) .ToListAsync(); return new PagedResult(items, totalCount, page, size); } public async Task GetGeneralDashboardStatsAsync() { var stats = new GeneralStatsResponse(); // 1. Total Revenue: Only from Invoices with Status PAID try { stats.TotalRevenue = (double)await _context.Invoices .Where(i => i.Status == InvoiceStatus.PAID) .SumAsync(i => i.TotalAmount); } catch { stats.TotalRevenue = 0; } // 2. Total Orders: All entries in Order table try { stats.TotalOrders = await _context.Orders.CountAsync(); } catch { stats.TotalOrders = 0; } // 3. Average Order Amount: Based on Order table try { if (stats.TotalOrders > 0) { stats.AvgOrdersAmount = (double)await _context.Orders .AverageAsync(o => o.TotalAmount); } } catch { stats.AvgOrdersAmount = 0; } // 4. Total Users Number: Where Role is ROLE_USER try { stats.TotalUsersNumber = await _context.Users .Where(u => u.Role == RoleEnum.ROLE_USER) .CountAsync(); } catch { stats.TotalUsersNumber = 0; } // 5. Order Status Counts: Grouped by the Status Enum try { var groupedData = await _context.Orders .GroupBy(o => o.Status) .Select(g => new { Status = g.Key, Count = g.Count() }) .ToListAsync(); // Ensure all enum statuses are represented even if count is 0 foreach (OrderStatus status in Enum.GetValues(typeof(OrderStatus))) { var match = groupedData.FirstOrDefault(x => x.Status == status); stats.OrderStatusCounts.Add(new StatusCountDto { StatusName = status.ToString(), Count = match?.Count ?? 0 }); } } catch { /* Handled to prevent blocking other stats */ } return stats; } }