📚 Migration Documentation:

20240711171136_AddNewAccountsTablesAndColumnsToJobTable.cs

Location:

IrbyImaging.DbContext/Migrations/
20240711171136_AddNewAccountsTablesAndColumnsToJobTable.cs

📖 Table of Contents

  1. 🔍 Overview & Purpose
  2. ⚙️ Dependencies & Technologies
  3. 🛠️ Migration Structure
  4. 🚀 Key Functional Areas
  5. 🗄️ Data Models
  6. 🔧 Usage Examples
  7. 🖼️ UML/Class Diagram
  8. 💡 Conclusion

🔍 Overview & Purpose

Migration Name: AddNewAccountsTablesAndColumnsToJobTable

Created On: 2024‑07‑11

Purpose:
This Entity Framework Core migration augments the existing Job table with additional address and budget‑related fields, while also introducing two new tables (AccountBalances and AccountLedger) under the irby schema. These enhancements enable the application to:

  • Capture detailed job location and budget metrics.
  • Store monthly financial snapshots (AccountBalances).
  • Track individual ledger entries for audit and reporting (AccountLedger).

⚙️ Dependencies & Technologies

  • .NET 6+ / .NET Core
  • Entity Framework Core (Migrations API)
  • SQL Server (T-SQL type mappings)
  • C# 10 (nullable disabled context)

🛠️ Migration Structure

public partial class AddNewAccountsTablesAndColumnsToJobTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder) { /* apply changes */ }

    protected override void Down(MigrationBuilder migrationBuilder) { /* revert changes */ }
}
  • Up: Defines schema changes when applying the migration.
  • Down: Rolls back the changes if you remove the migration.

🚀 Key Functional Areas

Up Method (Applying Changes)

  1. Add Columns to irby.Job

    • Address (Line1, Line2, City, State, Zip, Country)
    • Budget fields (BeginningBudget, BudgetApproved, BudgetRequested, BudgetChange, CommitmentChange)
    • Progress & metadata (PercentComplete, PostingEdit, SuretyDescription)
  2. Create irby.AccountBalances

    • Holds monthly summary: revenues, costs, labor hours, etc.
  3. Create irby.AccountLedger

    • Stores detailed transactions: invoices, batches, descriptions, GL dates, etc.
protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1. Add new columns to Job
    migrationBuilder.AddColumn<string>("AddressLine1",  schema: "irby", table: "Job", type: "nvarchar(max)", nullable: true);
    // ... other columns ...

    // 2. Create AccountBalances table
    migrationBuilder.CreateTable(
        name: "AccountBalances",
        schema: "irby",
        columns: table => new
        {
            AccountBalanceId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            ReportingYear    = table.Column<int>(type: "int", nullable: false),
            // ... other columns ...
            FieldOffice       = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Lead              = table.Column<string>(type: "nvarchar(max)", nullable: true)
        },
        constraints: table => table.PrimaryKey("PK_AccountBalances", x => x.AccountBalanceId)
    );

    // 3. Create AccountLedger table
    migrationBuilder.CreateTable(
        name: "AccountLedger",
        schema: "irby",
        columns: table => new
        {
            AccountLedgerId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            ReportingYear   = table.Column<int>(type: "int", nullable: false),
            // ... other columns ...
            ProjectManager   = table.Column<string>(type: "nvarchar(max)", nullable: true),
            JobType          = table.Column<string>(type: "nvarchar(max)", nullable: true)
        },
        constraints: table => table.PrimaryKey("PK_AccountLedger", x => x.AccountLedgerId)
    );
}

Down Method (Reverting Changes)

  • Drops AccountBalances and AccountLedger tables.
  • Removes all newly added columns from irby.Job.
protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(name: "AccountBalances", schema: "irby");
    migrationBuilder.DropTable(name: "AccountLedger",   schema: "irby");

    // Remove added columns from Job
    migrationBuilder.DropColumn("AddressLine1",    schema: "irby", table: "Job");
    // ... drop all other columns ...
}

🗄️ Data Models

Job Table Enhancements

Column Name Type Nullable Default Value Description
AddressLine1 nvarchar(max) Yes NULL Primary street address
AddressLine2 nvarchar(max) Yes NULL Secondary address line
City nvarchar(max) Yes NULL City name
State nvarchar(max) Yes NULL State or province
ZipCode nvarchar(max) Yes NULL Postal code
Country nvarchar(max) Yes NULL Country name
BeginningBudget decimal(18,2) No 0.00 Budget at job start
BudgetApproved decimal(18,2) No 0.00 Approved budget
BudgetRequested decimal(18,2) No 0.00 Requested additional budget
BudgetChange decimal(18,2) No 0.00 Net change in budget
CommitmentChange decimal(18,2) No 0.00 Change in purchase commitments
PercentComplete nvarchar(max) Yes NULL Textual or % completion indicator
PostingEdit nvarchar(max) Yes NULL Notes on posting adjustments
SuretyDescription nvarchar(max) Yes NULL Bonding / surety company details

AccountBalances Table

Column Name Type Nullable Description
AccountBalanceId uniqueidentifier No Primary Key (GUID)
ReportingYear int No Year of the report
ReportingMonth int No Month number (1–12)
JobNumber nvarchar(max) Yes Unique job code
JobName nvarchar(max) Yes Human‑readable job title
CostCode nvarchar(max) Yes Project cost code
CostCodeDescription nvarchar(max) Yes Description of the cost code
AccountingCode nvarchar(max) Yes Internal accounting category
LedgerType nvarchar(max) Yes Type of ledger (e.g., AP, AR)
ObjectAccount nvarchar(max) Yes GL object account number
Revenue decimal(18,2) No Recognized revenue
Cost decimal(18,2) No Recorded cost
ManHours decimal(18,2) No Labor hours logged
State, Customer, TypeOfWork, FieldOffice, Lead
nvarchar(max) Yes Additional dimensions for slicing and dicing financial data

AccountLedger Table

Column Name Type Nullable Description
AccountLedgerId uniqueidentifier No Primary Key (GUID)
ReportingYear int No Year of the ledger entry
ReportingMonth int No Month number
JobNumber, JobName
nvarchar(max) Yes Job identifiers
AccountId nvarchar(max) Yes General ledger account ID
Amount decimal(18,2) No Debit or credit amount
Description nvarchar(max) Yes Explanation or narrative
Remark nvarchar(max) Yes Free‑form notes
ObjectAccount, CostCode, CostCodeDescription
nvarchar(max) Yes Cost classification
ReverseOrVoid nvarchar(max) Yes Flag for reversal or voided transactions
Offset nvarchar(max) Yes Offset account
InvoiceDate datetime2 Yes Invoice posting date
InvoiceNumber, PurchaseOrder, ReceiptNumber
nvarchar(max) Yes Reference numbers
GLDate datetime2 No General ledger date
BatchDate datetime2 No Batch posting date
BatchNumber, DocumentNumber, DocumentType, SerialNumber
nvarchar(max) Yes Document tracking
State, Customer, TypeOfWork, FieldOffice, Lead, Manager, ProjectEstimator,
Superintendent, ProjectManager, JobType
nvarchar(max) Yes Extended metadata

🔧 Usage Examples

  1. Add & Apply Migration
    dotnet ef migrations add AddNewAccountsTablesAndColumnsToJobTable
    dotnet ef database update
    
  2. Rollback
    dotnet ef database update LastGoodMigrationName
    
  3. Generate SQL Script
    dotnet ef migrations script 20240711171136_AddNewAccountsTablesAndColumnsToJobTable
    

🖼️ UML/Class Diagram

classDiagram
    class Job {
      +string AddressLine1
      +string AddressLine2
      +string City
      +string State
      +string ZipCode
      +string Country
      +decimal BeginningBudget
      +decimal BudgetApproved
      +decimal BudgetRequested
      +decimal BudgetChange
      +decimal CommitmentChange
      +string PercentComplete
      +string PostingEdit
      +string SuretyDescription
    }
    
    class AccountBalances {
      +Guid   AccountBalanceId
      +int    ReportingYear
      +int    ReportingMonth
      +string JobNumber
      +string JobName
      +string CostCode
      +string CostCodeDescription
      +string AccountingCode
      +string LedgerType
      +string ObjectAccount
      +decimal Revenue
      +decimal Cost
      +decimal ManHours
      +string State
      +string Customer
      +string TypeOfWork
      +string FieldOffice
      +string Lead
    }
    
    class AccountLedger {
      +Guid   AccountLedgerId
      +int    ReportingYear
      +int    ReportingMonth
      +string JobNumber
      +string JobName
      +string AccountId
      +decimal Amount
      +string Description
      +string Remark
      +string ObjectAccount
      +string CostCode
      +string CostCodeDescription
      +string ReverseOrVoid
      +string Offset
      +DateTime? InvoiceDate
      +string InvoiceNumber
      +string PurchaseOrder
      +string ReceiptNumber
      +DateTime GLDate
      +DateTime BatchDate
      +string BatchNumber
      +string DocumentNumber
      +string DocumentType
      +string SerialNumber
      +string State
      +string Customer
      +string TypeOfWork
      +string FieldOffice
      +string Lead
      +string Manager
      +string ProjectEstimator
      +string Superintendent
      +string ProjectManager
      +string JobType
    }

💡 Conclusion

This migration strengthens the Job domain by embedding address and budget management directly into the core schema. Additionally, the two newly introduced tables—AccountBalances and AccountLedger—provide a robust foundation for:

  • ✅ Financial reporting
  • ✅ Detailed audit trails
  • ✅ Scalable accounting integrations

By following the usage examples above, you can seamlessly integrate these enhancements into your database, enabling richer analytics and tighter fiscal controls in the Irby Imaging system.