π 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
- Overview & Purpose
- Key Functional Areas βοΈ
- Schema Changes π
- Data Model Representation π οΈ
- Dependencies & Technologies π
- Usage Examples π
- UML Class Diagram π¨
- Conclusion π
π 1. Overview & Purpose
This Entity Framework Core migration introduces:
- Three new columns in the existing
JobAndFieldOfficeMappingtable:EquipmentCountPOCountWOCount
- A brand new table
WIP(Work-In-Progress) under theirbyschema 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
WIPtable 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.Migrationsnamespace
- SQL Server (assumed; uses
nvarchar(max),decimal(18,2),uniqueidentifier, etc.)
π 6. Usage Examples
Applying the Migration
-
Add the migration to your project (if not already generated):
dotnet ef migrations add AddWIPTable -
Update the database:
dotnet ef database update- This executes the
Up()method:- Alters the mapping table
- Creates the new
irby.WIPtable
- This executes the
Rolling Back
-
To undo this migration:
dotnet ef database update <PreviousMigrationName> -
This executes the
Down()method, droppingirby.WIPand 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! β¨