πŸ“„ Documentation: 20240522182056_AddWIPTable.cs Migration

Project: IrbyImaging.DbContext
Namespace: IrbyImaging.DbContext.Migrations
Purpose: Adds new columns to an existing mapping table and creates a new WIP table in the database.


πŸ“‘ Table of Contents

  1. Overview & Purpose
  2. Key Functional Areas βš™οΈ
  3. Schema Changes πŸ“Š
  4. Data Model Representation πŸ› οΈ
  5. Dependencies & Technologies πŸ”—
  6. Usage Examples πŸš€
  7. UML Class Diagram 🎨
  8. Conclusion πŸ“Œ

πŸ“ 1. Overview & Purpose

This Entity Framework Core migration introduces:

  • Three new columns in the existing JobAndFieldOfficeMapping table:
    • EquipmentCount
    • POCount
    • WOCount
  • A brand new table WIP (Work-In-Progress) under the irby schema to capture monthly and year‑to‑date metrics for jobs.

This update supports enhanced reporting and tracking of job performance, revenue, cost, margins, unbilled amounts, and man‑hours.


βš™οΈ 2. Key Functional Areas

Up(MigrationBuilder) πŸ—οΈ

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Add columns to existing mapping table
    migrationBuilder.AddColumn<string>(
        name: "EquipmentCount",
        table: "JobAndFieldOfficeMapping",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<string>(
        name: "POCount",
        table: "JobAndFieldOfficeMapping",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<string>(
        name: "WOCount",
        table: "JobAndFieldOfficeMapping",
        type: "nvarchar(max)",
        nullable: true);

    // 2️⃣ Create new 'WIP' table under 'irby' schema
    migrationBuilder.CreateTable(
        name: "WIP",
        schema: "irby",
        columns: table => new
        {
            WIPId          = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            ReportMonth    = table.Column<int>(type: "int", nullable: false),
            ReportYear     = table.Column<int>(type: "int", nullable: false),
            JobNumber      = table.Column<string>(type: "nvarchar(max)", nullable: true),
            JobName        = table.Column<string>(type: "nvarchar(max)", nullable: true),
            State          = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Customer       = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Division       = table.Column<string>(type: "nvarchar(max)", nullable: true),
            TypeOfWork     = table.Column<string>(type: "nvarchar(max)", nullable: true),
            FieldOffice    = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Lead           = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Revenue        = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            Cost           = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            Margin         = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            MarginPct      = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            RevenueYTD     = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            CostYTD        = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            MarginYTD      = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            MarginYTDPct   = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            CIEB           = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            Unbilled       = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            BIEC           = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            ManHours       = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            ParentJob      = table.Column<bool>(type: "bit", nullable: false),
            ChildJob       = table.Column<bool>(type: "bit", nullable: false),
            ParentJobNumber= table.Column<string>(type: "nvarchar(max)", nullable: true),
            updatedBy      = table.Column<string>(type: "nvarchar(max)", nullable: true),
            updatedDate    = table.Column<DateTime>(type: "datetime2", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_WIP", x => x.WIPId);
        });
}
  • Adds three nullable nvarchar(max) columns to an existing table.
  • Creates a comprehensive WIP table to store key performance metrics.

Down(MigrationBuilder) πŸ—‘οΈ

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop the WIP table in 'irby' schema
    migrationBuilder.DropTable(
        name: "WIP",
        schema: "irby");

    // 2️⃣ Remove the three added columns from the mapping table
    migrationBuilder.DropColumn(
        name: "EquipmentCount",
        table: "JobAndFieldOfficeMapping");

    migrationBuilder.DropColumn(
        name: "POCount",
        table: "JobAndFieldOfficeMapping");

    migrationBuilder.DropColumn(
        name: "WOCount",
        table: "JobAndFieldOfficeMapping");
}
  • Reverts schema changes if you roll back the migration.

πŸ“Š 3. Schema Changes

Table Change
JobAndFieldOfficeMapping + EquipmentCount+ POCount+ WOCount
irby.WIP πŸš€ New table with 27 columns capturing WIP metrics

1. JobAndFieldOfficeMapping Table Alterations

Column Type Nullable Description
EquipmentCount nvarchar(max) Yes Count of equipment per job/office
POCount nvarchar(max) Yes Count of purchase orders
WOCount nvarchar(max) Yes Count of work orders

2. New irby.WIP Table Definition

Column Type Nullable Description
WIPId uniqueidentifier No Primary Key (GUID)
ReportMonth int No Month number (1–12)
ReportYear int No Calendar year
JobNumber nvarchar(max) Yes Internal job identifier
JobName nvarchar(max) Yes Descriptive job name
State nvarchar(max) Yes U.S. state or region
Customer nvarchar(max) Yes Customer or contract name
Division nvarchar(max) Yes Business division
TypeOfWork nvarchar(max) Yes Category of work (e.g., Installation)
FieldOffice nvarchar(max) Yes Field office location
Lead nvarchar(max) Yes Project lead or manager
Revenue decimal(18,2) No Revenue for the month
Cost decimal(18,2) No Cost incurred for the month
Margin decimal(18,2) No Revenue βˆ’ Cost
MarginPct decimal(18,2) No Margin percentage
RevenueYTD decimal(18,2) No Year-to-date revenue
CostYTD decimal(18,2) No Year-to-date cost
MarginYTD decimal(18,2) No Year-to-date margin
MarginYTDPct decimal(18,2) No Year-to-date margin percentage
CIEB decimal(18,2) No Contracted Invoiced But not earned (CIEB)
Unbilled decimal(18,2) No Amount not yet billed
BIEC decimal(18,2) No Billed Incurred Earned but not collected
ManHours decimal(18,2) No Total man‑hours logged
ParentJob bit No Flag: Is a parent job?
ChildJob bit No Flag: Is a child job?
ParentJobNumber nvarchar(max) Yes If child, the parent job’s number
updatedBy nvarchar(max) Yes Last user to update the record
updatedDate datetime2 No Timestamp of last update

πŸ› οΈ 4. Data Model Representation

You can represent the irby.WIP table as a C# entity:

namespace IrbyImaging.DbContext.Models
{
    public class WIP
    {
        public Guid WIPId { get; set; }
        public int ReportMonth { get; set; }
        public int ReportYear { get; set; }
        public string JobNumber { get; set; }
        public string JobName { get; set; }
        public string State { get; set; }
        public string Customer { get; set; }
        public string Division { get; set; }
        public string TypeOfWork { get; set; }
        public string FieldOffice { get; set; }
        public string Lead { get; set; }
        public decimal Revenue { get; set; }
        public decimal Cost { get; set; }
        public decimal Margin { get; set; }
        public decimal MarginPct { get; set; }
        public decimal RevenueYTD { get; set; }
        public decimal CostYTD { get; set; }
        public decimal MarginYTD { get; set; }
        public decimal MarginYTDPct { get; set; }
        public decimal CIEB { get; set; }
        public decimal Unbilled { get; set; }
        public decimal BIEC { get; set; }
        public decimal ManHours { get; set; }
        public bool ParentJob { get; set; }
        public bool ChildJob { get; set; }
        public string ParentJobNumber { get; set; }
        public string updatedBy { get; set; }
        public DateTime updatedDate { get; set; }
    }
}

πŸ”— 5. Dependencies & Technologies

  • .NET 6+
  • Entity Framework Core
    • Migration API
    • Microsoft.EntityFrameworkCore.Migrations namespace
  • SQL Server (assumed; uses nvarchar(max), decimal(18,2), uniqueidentifier, etc.)

πŸš€ 6. Usage Examples

Applying the Migration

  1. Add the migration to your project (if not already generated):

    dotnet ef migrations add AddWIPTable
    
  2. Update the database:

    dotnet ef database update
    
    • This executes the Up() method:
      • Alters the mapping table
      • Creates the new irby.WIP table

Rolling Back

  • To undo this migration:

    dotnet ef database update <PreviousMigrationName>
    
  • This executes the Down() method, dropping irby.WIP and removing the three columns.


🎨 7. UML Class Diagram

classDiagram
    class WIP {
      +Guid WIPId
      +int ReportMonth
      +int ReportYear
      +string JobNumber
      +string JobName
      +string State
      +string Customer
      +string Division
      +string TypeOfWork
      +string FieldOffice
      +string Lead
      +decimal Revenue
      +decimal Cost
      +decimal Margin
      +decimal MarginPct
      +decimal RevenueYTD
      +decimal CostYTD
      +decimal MarginYTD
      +decimal MarginYTDPct
      +decimal CIEB
      +decimal Unbilled
      +decimal BIEC
      +decimal ManHours
      +bool ParentJob
      +bool ChildJob
      +string ParentJobNumber
      +string updatedBy
      +DateTime updatedDate
    }

πŸ“Œ 8. Conclusion

This migration enhances the database schema by:

  • Enabling richer reporting on equipment, POs, and work orders via new columns
  • Introducing a dedicated WIP table to capture monthly and YTD financial and operational metrics

By applying this change, your application will be ready to ingest, store, and query detailed Work-In-Progress data, facilitating improved insights and business analytics. If any issues arise, a simple rollback is supported by the Down() method.


Happy coding! ✨