Backend Code Challenge

Overview

AE.ShipManagement is a modular .NET 8 solution for managing ship data, supporting CRUD operations, business logic, and RESTful API access. The solution is organized into several projects for clear separation of concerns.

---

Projects

The challenge models a ship management system with these main entities:

• A crew member with a Sign On Date in the past, no Sign Off Date, and an End of Contract Date not yet passed is considered Onboard.
• If the Sign On Date is in the future, the crew member is Planned to join.
• If a crew member has no Sign Off Date, but the current date is more than 30 days past their End of Contract Date, their status is Relief Due.
• If the Sign Off Date is set, the crew member’s service is considered complete (Signed Off).

User and Ship Assignment

Users of the system represent application users who can be assigned to one or more ships. Each user has a name and role within the system. Users can view the ships they are assigned to. The relationship between users and ships is many-to-many, allowing for flexible assignment scenarios.

Financial Data and Accounting

Each ship has associated financial data aligned with its fiscal year settings. The financial data includes budgeted amounts and actual transaction values, organized by accounting periods (monthly) and account numbers from a hierarchical Chart of Accounts (COA). Accounts can be parent (summary) or child (detail) types, forming a tree-like structure. [^2]

Budgets and actuals are recorded for child accounts, and parent account values should be computed by aggregating their child accounts. Budget and actual data may include zero values, always non-negative values, and are stored for specific ship codes, account numbers, and accounting periods. Year-to-date (YTD) calculations must respect each ship’s fiscal year settings, summing values from the start of the fiscal year to the selected period.

---

Sample Data

ships

Code	Name	FiscalYear	Status
SHIP01	Flying Dutchman	0112	Active
SHIP02	Thousand Sunny	0403	Active

Crew Members

CrewMemberId	FirstName	LastName	BirthDate	Nationality
CREW001	Soka	Philip	1980-07-30	Greek
CREW002	Masteros	Philip	1980-07-30	Greek

Crew Service History

CrewMemberId	Rank	ShipCode	SignOnDate	SignOffDate	EndOfContractDate
CREW001	Master	SHIP01	2025-04-05	NULL	2025-07-05
CREW002	Chief Engineer	SHIP01	2025-04-04	NULL	2025-07-04

Chart of Accounts (COA)

• 7000000 OPERATING EXPENSES (Parent)
  • 7100000 AWARD AND GRANT TO INDIVIDUALS (Child of 7000000)

Budget Data

ShipCode	AccountNumber	AccountPeriod	BudgetValue
SHIP01	7135000	2025-01	0
SHIP01	7135000	2025-01	1000
SHIP01	7135000	2025-02	1200
...	...	...	...
SHIP01	7135000	2025-12	900

Account Transactions

ShipCode	AccountNumber	AccountPeriod	ActualValue
SHIP01	7135000	2025-01	300
SHIP01	7135000	2025-01	0
SHIP01	7135000	2025-01	700

---

Challenge Requirements

1. Database Challenge

Refer to the scripts in AE.ShipManagement.DAL\Scripts for the full DDL covering all entities and relationships.

1. Design the Database Schema
   • The Database is based on SQL server express 2022
   • A visual overview of the database structure is provided in the diagram:

• The diagram illustrates tables, columns, keys, and relationships.
• Use this as a reference for understanding and extending the database design.

2. DDL Scripts

   • AE.ShipManagement.DAL\Scripts\01-DDL-Schema.sql

3. Insert Sample Data

   • AE.ShipManagement.DAL\Scripts\02-Sample-Data.sql

4. Stored Procedures / Views

   • 03-Stored-Proc.sql

Financial Data Notes (Summary)

• Budgets and actual transaction amounts are recorded at the child account level only.
• Parent account values must be calculated by aggregating their child accounts.
• Fiscal year codes (e.g., "0112" for Jan-Dec, "0403" for Apr-Mar) define the YTD calculation period.
• Stored procedures must calculate monthly and YTD values for budget and actuals, as well as variances.
• 0 and NULL are not the same. 0 indicates a value of zero (e.g. zero cost). NULL means the value is unknown, missing, or not applicable.
• Only include records where budget or actual values are non-zero.

Example Output (Crew List)

Rank Name	Crew Member ID	First Name	Last Name	Age	Nationality	SignOnDate	Status
Master	CREW001	Soka	Philip	45	Greek	05 Apr 2025	Onboard
Chief Engineer	CREW002	Masteros	Philip	45	Greek	05 Apr 2025	Onboard
Chief Officer	CREW003	John	Masterbear	50	Greek	08 Apr 2025	Onboard
Cadet	CREW004	Bob	Marley	27	Mexican	05 Apr 2025	Onboard
Oiler	CREW005	John	Chena	30	Mexican	08 Apr 2025	Relief Due

Example Output (Financial Report Detail)

COA Description	Account Number	Actual (Jul 2025)	Budget (Jul 2025)	Variance Actual	Actual YTD (Jan-Jul 2025)	Budget YTD (Jan-Jul 2025)	Variance YTD
OPERATING EXPENSE	7000000	3300		3300	9600	9900	0
AWARD AND GRANT TO INDIVIDUALS	7100000	3300	3300	0	9600	9900	0
AWARDS	7120000		1000	1100	-100	3200	3300
SCHOLARSHIPS	7135000	2300	2200	100	6400	6300	100

Access all data only via stored procedures. Use realistic data and ensure correctness of fiscal year–based YTD calculations.

---

2. API Challenge

This project implements the backend REST APIs required by the challenge. The API layer is built in C# and communicates with the database exclusively via stored procedures.

Objectives:

• Build REST APIs in C# that consume your stored procedures.
• Implement endpoints for users, ships, crews, and financial reporting.
• Implement the following user stories (including but not limited to):
  • Add and list users.
  • Add and list ships.
  • Assign ships to users and retrieve ships by user.
  • Retrieve crew lists for ships (per database SP).
  • Retrieve financial reports per ship and accounting period (per database SP).

Summary of what's implemented

• REST endpoints for users, ships, user-ship assignments, crew lists and financial reports.
• All data access is performed only through stored procedures (no inline SQL in the application).
• Input validation, consistent HTTP status codes, and basic error handling.
• xUnit tests covering controllers and service layer logic (mocking database calls).
• Swagger/OpenAPI generation for interactive documentation.
• Optional: JWT-based authentication support (configurable via environment variables).

Project structure (high level)

• AE.ShipManagement.API/ - ASP.NET Core Web API project exposing REST endpoints.
• AE.ShipManagement.API/Controllers - Controllers for Users, Ships, Crew, and Financials.
• AE.ShipManagement.Service - Services that call stored procedures and perform business logic.
• AE.ShipManagement.Contracts - Request/response DTOs used by the API.
• AE.ShipManagement.DAL/ - Data Access layer project, including folder Scripts:DDL, stored procedures, sample data and ERD scripts.
• AE.ShipManagement.Tests/ - xUnit tests for API services.

Key endpoints

• POST /api/users — Create a user. Body: { "name": "...", "role": "..." }.
• GET /api/users — List users (supports paging and search).
• POST /api/ships — Create a ship. Body: { "code": "SHIP01", "name": "...", "fiscalYear": "0112", "status": "Active" }.
• GET /api/ships — List ships.
• POST /api/users/{userId}/ships/{shipCode} — Assign ship to user.
• GET /api/users/{userId}/ships — Get ships assigned to a user.
• GET /api/ships/{shipCode}/crew — Get crew list for a ship. Maps to stored procedure: sp_GetCrewList.
  • Query params: page, pageSize, sortBy, sortDir, search.
• GET /api/ships/{shipCode}/financials — Get financial report for ship and period. Maps to stored procedure: dbo.sp_GetFinancialReportDetail OR dbo.sp_GetFinancialReportSummary.
  • Query params: period (e.g. 2025-07), detail=bool (detail or summary), includeZero=false.

Stored procedure usage

• The API layer of Crew List and Finance Report only executes stored procedures. Each service wraps a stored procedure call and maps results to DTOs respectively.
• All stored procedures accept parameterized inputs — no string concatenation — to prevent SQL injection.

Validation and error handling

• All endpoints validate required fields and return 400 Bad Request on invalid input.
• Not found resources return 404. Server errors return 500 with a safe error message.
• Financial and crew endpoints validate ship existence and return 404 if missing.

WebAPI Authentication

• JWT authentication can be enabled by setting Jwt:Enabled=true and providing Jwt:Key and Jwt:Issuer in environment variables or appsettings.json.
• When enabled, endpoints are protected and require an Authorization: Bearer <token> header.

Testing

• Run unit tests with dotnet test. Tests mock the database access layer so they run without a live database.

Local run

1. Configure connection string in AE.ShipManagement.API/appsettings.Development.json (or set ConnectionStrings__DefaultConnection env var).
2. Apply database scripts from Scripts/ to a SQL Server instance.
3. Start API: dotnet run --project AE.ShipManagement.API/.
4. Browse https://localhost:{port}/swagger for API docs and to call endpoints interactively. Assuming that there is only one version v1 available for APIs at this moment

CI and Docker (nice-to-have)

• A Dockerfile is included for containerizing the API.
• CI pipeline (optional) runs dotnet build and dotnet test.