📄 LegalDashboardManager.cs Documentation

Welcome to the LegalDashboardManager documentation! This manager contains a suite of ETL‑style methods that extract, transform, and load lease‑ and contract‑related metrics into your database. It leverages raw SQL pivoting on document metadata and uses Entity Framework Core to persist the results.


🗂 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Domain Models & Data Tables
  5. SQL Pivot & Transformation Logic
  6. Usage Examples
  7. UML Class & Sequence Diagrams
  8. Conclusion

🔎 Overview & Purpose

The LegalDashboardManager is a background job component designed to refresh various dashboard metrics related to legal documents (leases, contracts, amendments, etc.). Each method:

  1. Executes a raw SQL query against the irby.DocumentImage and irby.DocumentAttribute tables
  2. Pivots JSON‐encoded document indexes into relational form
  3. Calculates aggregates (counts, sums) by director, state, lease type, etc.
  4. Upserts the results into corresponding EF Core entities

🎯 Use Case

  • Run nightly to ensure the legal dashboard reflects the latest contract and lease statistics
  • Automate ingestion of scanned/legal documents into business metrics

🛠 Dependencies & Technologies

Technology Purpose
.NET 6 / C# Language & runtime
Entity Framework Core (EF Core) ORM for upsert operations
Microsoft.EntityFrameworkCore.SqlServer SQL Server provider for raw SQL
IrbyImaging.DomainClasses Domain model assemblies (e.g., DirectorLeaseInfo)
SQL PIVOT & temporary tables Transform JSON arrays into relational data

🔑 Key Functional Areas

The class exposes eight public methods; all share a common pattern:

  1. Build a DbContextOptionsBuilder<IrbyContext> configured with a provided SQL connection string
  2. Query the database with FromSqlInterpolated to execute a complex pivoting script
  3. Map results to domain entities
  4. Upsert into the underlying tables via context.SaveChanges()

Below is a high‑level summary:

1. UpdateDirectorLeaseResults 📈

  • Purpose: Aggregate lease costs by Director
  • Output Entity: DirectorLeaseInfo
  • Metrics:
    • NumberOfProperties
    • MonthlyAVTax, MonthlyBaseRent, MonthlyInsurancePayment, MonthlySalesTax, MonthlyTotalCost
public void UpdateDirectorLeaseResults(string sqlConnectionString);

2. UpdatePropertyResults 🏠

  • Purpose: Compute total monthly costs by State
  • Output Entity: StatePropertyCosts
  • Metric:
    • MonthlyTotal
public void UpdatePropertyResults(string sqlConnectionString);

3. UpdateNumberOfProperties 🗂

  • Purpose: Count properties by Lease Purpose
  • Output Entity: LeasePropertyDetails
  • Metrics:
    • NumberOfProperties
    • Monthly cost breakdown
public void UpdateNumberOfProperties(string sqlConnectionString);

4. UpdateLeaseDetails 📋

  • Purpose: Load detailed lease metadata per document
  • Output Entity: LeaseDetails
  • Fields:
    • City, County, Landlord, LeaseCode, LeaseTerm, StreetAddress, ZipCode, etc.
    • Financials: AV Tax, Base Rent, Insurance, Sales Tax, Total Cost
public void UpdateLeaseDetails(string sqlConnectionString);

5. UpdateDirectorDetails 👔

  • Purpose: Count properties per Director and State
  • Output Entity: DirectorProperties
  • Metrics:
    • NumberOfProperties
public void UpdateDirectorDetails(string sqlConnectionString);

6. UpdateContractTypeDetails 📝

  • Purpose: Count contracts by Type of Work and State
  • Output Entity: ContractTypeDetails
  • Metrics:
    • NumberOfContracts
public void UpdateContractTypeDetails(string sqlConnectionString);

7. UpdateContractTypeNumber 🔢

  • Purpose: Count number of documents per DocumentType
  • Output Entity: ContractTypeNumber
  • Metric:
    • TypeCount
public void UpdateContractTypeNumber(string sqlConnectionString);

8. UpdateContractsByLead 👤

  • Purpose: Count contracts by Lead (Director)
  • Output Entity: ContractsByLead
  • Metric:
    • NumberOfContracts
public void UpdateContractsByLead(string sqlConnectionString);

🏷 Domain Models & Data Tables

Entity Key Fields
DirectorLeaseInfo Director, NumberOfProperties, MonthlyAVTax, … MonthlyTotalCost
StatePropertyCosts State, MonthlyTotal
LeasePropertyDetails LeasePurpose, NumberOfProperties, … MonthlyTotalCost
LeaseDetails DocumentImageId, City, County, Landlord, LeaseCode, … QuantaReviewed
DirectorProperties State, Director, NumberOfProperties
ContractTypeDetails State, TypeOfWork, NumberOfContracts
ContractTypeNumber DocumentType, TypeCount
ContractsByLead Director, NumberOfContracts

🔄 SQL Pivot & Transformation Logic

Each method executes a templated SQL script that:

  1. Drops existing temp tables (#Documents, #IndexList, #TempIndexList, #FinalList, etc.)
  2. Selects raw document JSON (DocumentIndexes) into #Documents
  3. Builds an attribute list for pivot columns via irby.DocumentAttribute
  4. Iterates over each document & index to populate #IndexList and #TempIndexList
  5. Pivots the combined data into #FinalList
  6. Aggregates into #FinalTable (counts, sums)
  7. Joins back to the target table for Id lookup (or NEWID() for new records)
  8. Returns a flat result set for EF to consume via FromSqlInterpolated(...)

💡 Usage Examples

// 1. Configure your DbContext in Startup.cs (or DI container):
services.AddDbContext<IrbyContext>(options =>
    options.UseSqlServer(configuration.GetConnectionString("IrbyDatabase")));

// 2. Register the manager:
services.AddTransient<LegalDashboardManager>();

// 3. In a background service or controller:
public class DashboardJob
{
    private readonly LegalDashboardManager _manager;
    private readonly IConfiguration _config;

    public DashboardJob(LegalDashboardManager manager, IConfiguration config)
    {
        _manager = manager;
        _config = config;
    }

    public async Task RunAllUpdates()
    {
        string conn = _config.GetConnectionString("IrbyDatabase");
        // Sequentially refresh all dashboard metrics
        _manager.UpdateDirectorLeaseResults(conn);
        _manager.UpdatePropertyResults(conn);
        _manager.UpdateNumberOfProperties(conn);
        _manager.UpdateLeaseDetails(conn);
        _manager.UpdateDirectorDetails(conn);
        _manager.UpdateContractTypeDetails(conn);
        _manager.UpdateContractTypeNumber(conn);
        _manager.UpdateContractsByLead(conn);
    }
}

📊 UML Class & Sequence Diagrams

Class Diagram (Mermaid)

classDiagram
    class LegalDashboardManager {
        - IrbyContext context
        + UpdateDirectorLeaseResults(sqlConnectionString)
        + UpdatePropertyResults(sqlConnectionString)
        + UpdateNumberOfProperties(sqlConnectionString)
        + UpdateLeaseDetails(sqlConnectionString)
        + UpdateDirectorDetails(sqlConnectionString)
        + UpdateContractTypeDetails(sqlConnectionString)
        + UpdateContractTypeNumber(sqlConnectionString)
        + UpdateContractsByLead(sqlConnectionString)
    }
    LegalDashboardManager --> IrbyContext

Sequence (High‑Level)

sequenceDiagram
    participant JobScheduler
    participant LegalDashboardManager
    participant DbContext as IrbyContext
    Note over JobScheduler: Trigger nightly job
    JobScheduler ->> LegalDashboardManager: UpdateXXX(connString)
    activate LegalDashboardManager
    LegalDashboardManager ->> DbContext: FromSqlInterpolated(SQL script)
    DbContext -->> LegalDashboardManager: List<DomainResult>
    loop For each result
        LegalDashboardManager ->> DbContext: FindOrCreate(Entity)
        LegalDashboardManager ->> DbContext: SaveChanges()
    end
    deactivate LegalDashboardManager

✅ Conclusion

The LegalDashboardManager is your one-stop ETL engine for transforming legal document metadata into actionable dashboard metrics. By combining SQL‐level pivoting with EF Core upserts, it provides:

  • ✅ Scalability: efficient pivot logic in SQL Server
  • ✅ Maintainability: reusable methods per metric
  • ✅ Consistency: atomic SaveChanges after each batch

Use these methods in your scheduled background jobs to keep your legal dashboards up to date! 🎉