📄 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
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Domain Models & Data Tables
- SQL Pivot & Transformation Logic
- Usage Examples
- UML Class & Sequence Diagrams
- 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:
- Executes a raw SQL query against the
irby.DocumentImageandirby.DocumentAttributetables - Pivots JSON‐encoded document indexes into relational form
- Calculates aggregates (counts, sums) by director, state, lease type, etc.
- 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:
- Build a
DbContextOptionsBuilder<IrbyContext>configured with a provided SQL connection string - Query the database with
FromSqlInterpolatedto execute a complex pivoting script - Map results to domain entities
- 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:
NumberOfPropertiesMonthlyAVTax,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:
- Drops existing temp tables (
#Documents,#IndexList,#TempIndexList,#FinalList, etc.) - Selects raw document JSON (
DocumentIndexes) into#Documents - Builds an attribute list for pivot columns via
irby.DocumentAttribute - Iterates over each document & index to populate
#IndexListand#TempIndexList - Pivots the combined data into
#FinalList - Aggregates into
#FinalTable(counts, sums) - Joins back to the target table for Id lookup (or
NEWID()for new records) - 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! 🎉