PRD & Implementation Plan: Executive Summary Email Expansion

This document details the requirements and technical design for expanding the Daily Sales Summary Email Job (SalesSummaryEmailJob.cs) to compile a complete Executive Summary Email.

1. Goals & Requirements

1.1 Goals

Sales Performance Visualization: Add a modern horizontal bar chart below the daily sales summary table to represent Yesterday, Month-To-Date (MTD), and Year-To-Date (YTD) sales.
Cash & Bank Book Summary: List all active bank/cash accounts and their current balances.
Receivables & Payables Summary: Provide total outstanding Accounts Receivable (AR) and Accounts Payable (AP) balances.
Inventory Valuation Summary: Summarize total inventory values for Raw Materials (RM) and Finished Goods (FG).

1.2 User Review Required

Note

Email Client Compatibility & Embedded SVGs To avoid blocking issues (such as external image blockers in outlook/gmail) and to run completely offline without external web API dependencies, we will generate the chart dynamically using inline SVG elements directly embedded in the HTML email body.

Important

Bar Chart Scaling (Logarithmic/Power Scale) Since MTD and YTD values are orders of magnitude larger than daily sales, a standard linear bar chart would make the daily sales bar invisible (e.g. 0.02% of height). We propose a Power-based scaling algorithm (e.g., Math.Pow(value, 0.35)) to map widths. This visual compression ensures all three bars are visible and proportional, while the numeric text labels display the exact amounts.

2. Proposed Changes

Component: ErpCrystal_MFG.Models

[NEW] DashboardCashBankBalance.cs

Model to represent each cash/bank book balance record.

namespace ErpCrystal_MFG.Models;

public class DashboardCashBankBalance
{
    public string BankCode { get; set; } = string.Empty;
    public string BankName { get; set; } = string.Empty;
    public decimal Balance { get; set; }
}

[NEW] DashboardArApBalance.cs

Model to represent receivables vs payables.

namespace ErpCrystal_MFG.Models;

public class DashboardArApBalance
{
    public string ArapType { get; set; } = string.Empty; // 'R' or 'P'
    public decimal Balance { get; set; }
}

[NEW] DashboardInventoryBalance.cs

Model to represent raw material vs finished goods inventory valuation.

namespace ErpCrystal_MFG.Models;

public class DashboardInventoryBalance
{
    public string ItemCategory { get; set; } = string.Empty; // 'RM' or 'FG'
    public decimal Value { get; set; }
}

Component: Repository Layer

[MODIFY] ITaskSchedulerRepository.cs

Add interface declarations for querying the dashboard tables.

    Task<List<DashboardCashBankBalance>> GetCashBankBalances(string dbname, string yearLabel);
    Task<List<DashboardArApBalance>> GetArApBalances(string dbname);
    Task<List<DashboardInventoryBalance>> GetInventoryBalances(string dbname, string yearLabel);

[MODIFY] TaskSchedulerRepository.cs

Implement the query methods targeting dashboard schemas.

    public async Task<List<DashboardCashBankBalance>> GetCashBankBalances(string dbname, string yearLabel)
    {
        var query = @"SELECT bankcode AS BankCode, bankname AS BankName, ISNULL(SUM(amount), 0) AS Balance 
        FROM dash.DashboardCashBankBook 
        WHERE yearlabel = @yearLabel 
        GROUP BY bankcode, bankname 
        ORDER BY bankname";

        using var connection = _DapperContext.SetClientConnection(dbname);
        var data = await connection.QueryAsync<DashboardCashBankBalance>(query, new { yearLabel });
        return data.ToList();
    }

    public async Task<List<DashboardArApBalance>> GetArApBalances(string dbname)
    {
        var query = @"SELECT araptype AS ArapType, ISNULL(SUM(netamount), 0) AS Balance 
        FROM dash.DashboardArap 
        GROUP BY araptype";

        using var connection = _DapperContext.SetClientConnection(dbname);
        var data = await connection.QueryAsync<DashboardArApBalance>(query);
        return data.ToList();
    }

    public async Task<List<DashboardInventoryBalance>> GetInventoryBalances(string dbname, string yearLabel)
    {
        var query = @"SELECT itemcategory AS ItemCategory, ISNULL(SUM(TotalValue), 0) AS Value 
        FROM dash.DashboardInventoryAge 
        WHERE yearLabel = @yearLabel 
        GROUP BY itemcategory";

        using var connection = _DapperContext.SetClientConnection(dbname);
        var data = await connection.QueryAsync<DashboardInventoryBalance>(query, new { yearLabel });
        return data.ToList();
    }

Component: Scheduled Jobs Layer

[MODIFY] SalesSummaryEmailJob.cs

Revise the job to gather all extra metrics, calculate SVG bar dimensions, and construct a premium-styled responsive HTML email body.

            // 1. Fetch Sales Data
            var dailySales = await _ITaskSchedulerRepository.GetDailySales(dbname);
            var monthlySales = await _ITaskSchedulerRepository.GetMonthlySales(dbname);
            var currentYearLabel = _IUtilityMethodsRepository.GetActiveYearlabel().Result;
            var fyStart = _IUtilityMethodsRepository.GetFYDates(currentYearLabel).Result;
            var yearlySales = await _ITaskSchedulerRepository.GetYearlySales(dbname, fyStart.fystart);

            // 2. Fetch Additional Dashboard Metrics
            var cashBankBalances = await _ITaskSchedulerRepository.GetCashBankBalances(dbname, currentYearLabel);
            var arapBalances = await _ITaskSchedulerRepository.GetArApBalances(dbname);
            var inventoryBalances = await _ITaskSchedulerRepository.GetInventoryBalances(dbname, currentYearLabel);

SVG Visual Scaling Construction

            // Calculate SVG widths (Max width 320px)
            double maxVal = (double)Math.Max(yearlySales, Math.Max(monthlySales, dailySales));
            if (maxVal <= 0) maxVal = 1.0;

            double scalePower = 0.35; // Compresses huge scaling differences
            double maxPower = Math.Pow(maxVal, scalePower);

            double dailyPower = dailySales > 0 ? Math.Pow((double)dailySales, scalePower) : 0;
            double monthlyPower = monthlySales > 0 ? Math.Pow((double)monthlySales, scalePower) : 0;
            double yearlyPower = yearlySales > 0 ? Math.Pow((double)yearlySales, scalePower) : 0;

            double dailyWidth = maxPower > 0 ? (dailyPower / maxPower) * 320 : 0;
            double monthlyWidth = maxPower > 0 ? (monthlyPower / maxPower) * 320 : 0;
            double yearlyWidth = maxPower > 0 ? (yearlyPower / maxPower) * 320 : 0;

            // Ensure a minor visible length if amount exists
            if (dailySales > 0 && dailyWidth < 15) dailyWidth = 15;
            if (monthlySales > 0 && monthlyWidth < 15) monthlyWidth = 15;
            if (yearlySales > 0 && yearlyWidth < 15) yearlyWidth = 15;

3. Email Layout & Visual Styling

The summary email will be designed with a premium executive-level color scheme (Deep Slate, Teal, and Warm Gold details):

Dashboard Header: Deep Navy/Slate (#1e293b) with clean, light metadata.
Sales Chart Card: Smooth SVG with distinct color bars (Yesterday: Royal Blue, MTD: Teal Green, YTD: Indigo).
Cash & Bank Balances Table: Zebra striping, right-aligned monetary values, and bold total row.
AR & AP Widget: Multi-column summary with highlights for Accounts Receivable outstanding, Accounts Payable, and Net Working Capital.
Inventory Valuation: Grouped raw materials vs. finished goods card layout.

DPR Under Development Points Maui