πŸ“š Table of Contents

  1. Overview & Purpose
  2. Migration Class: AddMechanicTimesheet
  3. Database Schema Changes
  4. UML Class Diagram
  5. Dependencies & Technologies
  6. How to Apply / Rollback Migration
  7. Usage Examples
  8. Sample DbContext Entity
  9. Conclusion

πŸ” Overview & Purpose

This migration, AddMechanicTimesheet, adds a new table MechanicTimesheet to the irby schema in the database.
It is part of the Entity Framework Core (EF Core) migrations pipeline and is designed to:

  • Store timesheet entries for mechanics.
  • Track working hours, job details, cost types, and approval status.
  • Provide a historical log of mechanic work sessions for payroll and reporting.

πŸ›  Migration Class: AddMechanicTimesheet

namespace IrbyImaging.DbContext.Migrations
{
    public partial class AddMechanicTimesheet : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Create table...
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // Drop table...
        }
    }
}

2.1 Up Method

The Up method applies the migration: it creates the MechanicTimesheet table with all specified columns and primary key.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "MechanicTimesheet",
        schema: "irby",
        columns: table => new
        {
            Id          = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            EmployeeId  = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            SubmittedBy = table.Column<string>(type: "nvarchar(max)", nullable: true),
            StartTime   = table.Column<string>(type: "nvarchar(max)", nullable: true),
            EndTime     = table.Column<string>(type: "nvarchar(max)", nullable: true),
            StartDate   = table.Column<DateTime>(type: "datetime2", nullable: false),
            Job         = table.Column<string>(type: "nvarchar(max)", nullable: true),
            PayCode     = table.Column<string>(type: "nvarchar(max)", nullable: true),
            CostType    = table.Column<string>(type: "nvarchar(max)", nullable: true),
            UnitNumber  = table.Column<string>(type: "nvarchar(max)", nullable: true),
            WorkOrder   = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Description = table.Column<string>(type: "nvarchar(max)", nullable: true),
            RegularTime = table.Column<string>(type: "nvarchar(max)", nullable: true),
            OverTime    = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Status      = table.Column<int>(type: "int", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_MechanicTimesheet", x => x.Id);
        }
    );
}

2.2 Down Method

The Down method reverses the migration: it drops the MechanicTimesheet table.

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(
        name: "MechanicTimesheet",
        schema: "irby"
    );
}

πŸ’Ύ Database Schema Changes

3.1 MechanicTimesheet Table Definition

Table Schema Table Name
irby MechanicTimesheet

Purpose: Store mechanics’ daily time entries, job codes, descriptions, and status for payroll and reporting.

3.2 Columns & Constraints

Column Name Data Type Nullable Description
Id uniqueidentifier No Primary Key (GUID)
EmployeeId uniqueidentifier No Reference to the employee (GUID)
SubmittedBy nvarchar(max) Yes User who submitted the entry
StartTime nvarchar(max) Yes Shift start time (e.g., "08:00 AM")
EndTime nvarchar(max) Yes Shift end time (e.g., "05:00 PM")
StartDate datetime2 No Date of the work session
Job nvarchar(max) Yes Job code or title
PayCode nvarchar(max) Yes Payroll code (regular, overtime, etc.)
CostType nvarchar(max) Yes Cost allocation type
UnitNumber nvarchar(max) Yes Equipment or unit number
WorkOrder nvarchar(max) Yes Associated work order number
Description nvarchar(max) Yes Free-text description of tasks performed
RegularTime nvarchar(max) Yes Regular hours worked (e.g., "8:00")
OverTime nvarchar(max) Yes Overtime hours worked (e.g., "2:00")
Status int No Entry status (e.g., Pending = 0, Approved = 1, Rejected)
  • Primary Key: PK_MechanicTimesheet (Id)
  • Schema: objects are created under the irby schema.

🏷 UML Class Diagram

classDiagram
    class MechanicTimesheet {
        +Guid Id
        +Guid EmployeeId
        +string SubmittedBy
        +string StartTime
        +string EndTime
        +DateTime StartDate
        +string Job
        +string PayCode
        +string CostType
        +string UnitNumber
        +string WorkOrder
        +string Description
        +string RegularTime
        +string OverTime
        +int Status
    }

πŸ”§ Dependencies & Technologies

  • .NET 6+ / .NET Core
  • Entity Framework Core
  • Microsoft.EntityFrameworkCore.Migrations
  • SQL Server (or other relational DB supporting schemas)
  • C# 10 (nullable context disabled in this migration)

βš™οΈ How to Apply / Rollback Migration

Using the CLI:

  1. Add Migration

    dotnet ef migrations add AddMechanicTimesheet \
        --project IrbyImaging.DbContext \
        --startup-project IrbyImaging.Api
    
  2. Update Database

    dotnet ef database update --project IrbyImaging.DbContext
    
  3. Rollback (to previous)

    dotnet ef database update <PreviousMigrationName>
    

πŸ“ Usage Examples

Inserting a Timesheet Entry

using var context = new IrbyDbContext();

var entry = new MechanicTimesheet
{
    Id          = Guid.NewGuid(),
    EmployeeId  = someEmployeeId,
    SubmittedBy = "jdoe",
    StartTime   = "08:00 AM",
    EndTime     = "05:00 PM",
    StartDate   = DateTime.UtcNow.Date,
    Job         = "Engine Overhaul",
    PayCode     = "REG",
    CostType    = "Labor",
    UnitNumber  = "U-1234",
    WorkOrder   = "WO-5678",
    Description = "Replaced pistons and sealed leaks.",
    RegularTime = "8:00",
    OverTime    = "0:00",
    Status      = 0 // Pending
};

context.MechanicTimesheet.Add(entry);
context.SaveChanges();

Querying Entries

var approved = context.MechanicTimesheet
    .Where(t => t.Status == 1)
    .OrderBy(t => t.StartDate)
    .ToList();

πŸ“‚ Sample DbContext Entity

Although the migration defines the table, you’ll typically have an entity class in your DbContext:

[Table("MechanicTimesheet", Schema = "irby")]
public class MechanicTimesheet
{
    public Guid Id { get; set; }
    public Guid EmployeeId { get; set; }
    public string? SubmittedBy { get; set; }
    public string? StartTime { get; set; }
    public string? EndTime { get; set; }
    public DateTime StartDate { get; set; }
    public string? Job { get; set; }
    public string? PayCode { get; set; }
    public string? CostType { get; set; }
    public string? UnitNumber { get; set; }
    public string? WorkOrder { get; set; }
    public string? Description { get; set; }
    public string? RegularTime { get; set; }
    public string? OverTime { get; set; }
    public int Status { get; set; }
}

And in your DbContext:

public class IrbyDbContext : DbContext
{
    public DbSet<MechanicTimesheet> MechanicTimesheet { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("irby");
        modelBuilder.Entity<MechanicTimesheet>()
                    .HasKey(m => m.Id);
        // Additional config...
    }
}

🎯 Conclusion

This migration seamlessly integrates a Mechanic Timesheet feature into the existing database schema. By applying AddMechanicTimesheet, you empower your application to:

  • Capture mechanic labor details.
  • Support payroll calculations (regular vs. overtime).
  • Maintain an audit trail of timesheet submissions.

Make sure to maintain matching entity definitions in your codebase, run migrations as part of your deployment pipeline, and handle the Status codes in your business logic (e.g., enum for better readability).

Happy coding! πŸ› οΈπŸš€