📊

RetailDash

Multi-location retail analytics and reporting platform

.NET WPF MVVM MaterialDesign Entity Framework

Overview

A comprehensive retail analytics and reporting platform for multi-location businesses. Generates various reports including sales figures, stock levels, newsletter performance, and inventory analysis across multiple store locations.

Key Features

Entity Framework with Complex Queries

Uses EF Core with eager loading to efficiently fetch products across multiple related tables, including brands, variants, stock levels, deliveries, and sales data with date filtering. 

public static async Task<List<ProductDataOutModel>> QueryData(bool isMonthSales = false)
{
    await using var dbContext = new AppDbContext();
    var dateRange = GetDateRange(isMonthSales);

    var products = await dbContext.Products
        .AsNoTracking()
        .Where(p => !p.Brand.Name.ToLower().Contains(excludedBrand))
        .Include(product => product.Brand)
        .Include(product => product.Variants!)
        .ThenInclude(variant => variant.Stocks!
            .Where(s => s.LocationName == "Main Street" || s.LocationName == "Town Centre"))
        .Include(product => product.Variants!)
        .ThenInclude(variant => variant.Deliveries!)
        .Include(product => product.Variants!)
        .ThenInclude(variant => variant.Sales!
            .Where(s => s.Date >= dateRange.Start && s.Date <= dateRange.End))
        .Where(p => p.Variants!.Any(v => v.Sales!.Any()))
        .ToListAsync();

    return products
        .SelectMany(p => ProcessProductVariants(p, dateRange))
        .OrderByDescending(p => p.ProductVariants!
            .Sum(v => v.VariantSales!.Sum(s => s.SaleQuantity)))
        .ToList();
}

Google Analytics 4 Integration

Integrates with Google Analytics Data API to track newsletter campaign performance, including purchases, revenue, and item-level attribution data. 

public async static Task<List<NewsletterDataModel>> QueryData()
{
    string credentialPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "service-account.json");
    Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", credentialPath);
    
    var client = await BetaAnalyticsDataClient.CreateAsync();
    RunReportRequest request = new()
    {
        Property = "properties/" + _propertyId,
        Dimensions = { new Dimension { Name = "sessionSourceMedium" }, },
        Metrics = { new Metric { Name = "totalPurchasers" }, new Metric { Name = "totalRevenue" }, },
        DateRanges = { new DateRange { StartDate = "2023-08-01", EndDate = "today" }, },
    };

    var response = await client.RunReportAsync(request);
    foreach (Row row in response.Rows)
    {
        var ga4Data = new Ga4DataModel
        {
            SourceMedium = ParseSourceMedium(row.DimensionValues[0].Value),
            EcommercePurchases = int.Parse(row.MetricValues[0].Value),
            PurchaseRevenue = decimal.Parse(row.MetricValues[1].Value),
        };
        _ga4DataList.Add(ga4Data);
    }
}

Professional Excel Report Generation

Uses ClosedXML to generate formatted Excel reports with styled headers, borders, and proper page formatting for printing. 

public static void SaveToExcel(List<ProductDataOutModel> reportData, bool isMonthSales)
{
    using var workbook = new XLWorkbook();
    var worksheet = workbook.Worksheets.Add("Sales Report");

    ExcelHelpers.AddTitleRow(worksheet, titleText, 7);
    
    worksheet.Cell(2, 1).Value = "Season";
    worksheet.Cell(2, 2).Value = "Brand";
    worksheet.Cell(2, 3).Value = "Description";
    worksheet.Cell(2, 4).Value = "Colour";
    worksheet.Cell(2, 5).Value = "Sales";
    worksheet.Cell(2, 6).Value = "Stock";
    worksheet.Cell(2, 7).Value = "Last Delivered";

    ExcelHelpers.StyleHeaderRow(worksheet, 7);
    
    foreach (var item in reportData)
    {
        worksheet.Cell(row, 1).Value = item.ProductSeason;
        worksheet.Cell(row, 2).Value = item.ProductBrand;
        worksheet.Cell(row, 3).Value = item.ProductDescription;
        // ... additional cells
        row++;
    }

    ExcelHelpers.ApplyDataBorders(worksheet, worksheet.LastRowUsed()!.RowNumber(), 7);
    ExcelHelpers.ApplyWorksheetFormatting(worksheet, XLPageOrientation.Portrait);
    
    workbook.SaveAs(filePath);
}

Automated Email Report Delivery

Sends generated Excel reports via SMTP with MailKit, supporting TLS and multiple file attachments for weekly automated deliveries. 

public static async Task SendExcelReportAsync(string toEmail)
{
    var config = LoadConfiguration();
    var smtpHost = config["SmtpHost"];
    var smtpPort = int.Parse(config["SmtpPort"]!);
    
    var filePaths = Directory.GetFiles(ZenkiFileManager.GetWeeklyReportsFolder());
    
    var message = new MimeMessage();
    message.From.Add(new MailboxAddress(fromName, fromEmail));
    message.To.Add(MailboxAddress.Parse(toEmail));
    message.Subject = $"Weekly Reports – {DateTime.Now:dd/MM/yyyy}";

    var multipart = new Multipart("mixed");
    foreach (var file in filePaths)
    {
        var attachment = new MimePart("application", "vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            Content = new MimeContent(File.OpenRead(file)),
            FileName = Path.GetFileName(file)
        };
        multipart.Add(attachment);
    }

    using var client = new SmtpClient();
    await client.ConnectAsync(smtpHost, smtpPort, SecureSocketOptions.StartTls);
    await client.AuthenticateAsync(smtpUsername, smtpPassword);
    await client.SendAsync(message);
    await client.DisconnectAsync(true);
}

Multi-Location Stock Analysis

Tracks stock levels across multiple retail locations with configurable product type filtering and season-based analysis. 

public static async Task<List<ProductDataOutModel>> QueryData(string? selectedSeason, bool excludeSeason, bool matchSeasonType)
{
    await using var dbContext = new AppDbContext();
    var allowedTypes = new[] { "boots", "sandals", "shoes", "slippers", "trainers" };

    var products = await dbContext.Products
        .Where(p => new[] { 2, 3 }.Contains(p.Variants!
            .SelectMany(v => v.Stocks!
                .Where(s => s.LocationName == "Main Street" || s.LocationName == "Town Centre"))
            .Sum(s => s.StockQuantity)) && allowedTypes.Contains(p.Type!.ToLower()))
        .Include(p => p.Brand)
        .Include(p => p.Variants)!.ThenInclude(v => v.Stocks)
        .Include(p => p.Variants)!.ThenInclude(v => v.Deliveries)
        .ToListAsync();

    if (matchSeasonType && selectedSeason != "All Seasons")
    {
        var seasonType = selectedSeason?.Split('/')[0].Trim();
        products = products.Where(p => p.Season!.Contains(seasonType!)).ToList();
    }

    return products.SelectMany(p => /* transform to output model */).ToList();
}

Challenges & Solutions