434 lines
14 KiB
TypeScript
434 lines
14 KiB
TypeScript
import { Router, type RouterContext, type State } from "@oak/oak";
|
|
import { db } from "../config/database.ts";
|
|
import {
|
|
authenticateToken,
|
|
authorize,
|
|
getCurrentUser,
|
|
} from "../middleware/auth.ts";
|
|
import { sanitizeInput } from "../middleware/security.ts";
|
|
import type {
|
|
ContractorRate,
|
|
CreateWorkAllocationRequest,
|
|
JWTPayload,
|
|
WorkAllocation,
|
|
} from "../types/index.ts";
|
|
|
|
const router = new Router();
|
|
|
|
// Get all work allocations
|
|
router.get(
|
|
"/",
|
|
authenticateToken,
|
|
async (
|
|
ctx: RouterContext<"/", Record<string | number, string | undefined>, State>,
|
|
) => {
|
|
try {
|
|
const currentUser: JWTPayload = getCurrentUser(ctx);
|
|
const params: URLSearchParams = ctx.request.url.searchParams;
|
|
const employeeId: string | null = params.get("employeeId");
|
|
const status: string | null = params.get("status");
|
|
const departmentId: string | null = params.get("departmentId");
|
|
|
|
let query: string = `
|
|
SELECT wa.*,
|
|
e.name as employee_name, e.username as employee_username,
|
|
s.name as supervisor_name,
|
|
c.name as contractor_name,
|
|
sd.name as sub_department_name,
|
|
d.name as department_name
|
|
FROM work_allocations wa
|
|
JOIN users e ON wa.employee_id = e.id
|
|
JOIN users s ON wa.supervisor_id = s.id
|
|
JOIN users c ON wa.contractor_id = c.id
|
|
LEFT JOIN sub_departments sd ON wa.sub_department_id = sd.id
|
|
LEFT JOIN departments d ON e.department_id = d.id
|
|
WHERE 1=1
|
|
`;
|
|
const queryParams: unknown[] = [];
|
|
|
|
// Role-based filtering
|
|
if (currentUser.role === "Supervisor") {
|
|
query += " AND wa.supervisor_id = ?";
|
|
queryParams.push(currentUser.id);
|
|
} else if (currentUser.role === "Employee") {
|
|
query += " AND wa.employee_id = ?";
|
|
queryParams.push(currentUser.id);
|
|
} else if (currentUser.role === "Contractor") {
|
|
query += " AND wa.contractor_id = ?";
|
|
queryParams.push(currentUser.id);
|
|
}
|
|
|
|
if (employeeId) {
|
|
query += " AND wa.employee_id = ?";
|
|
queryParams.push(employeeId);
|
|
}
|
|
|
|
if (status) {
|
|
query += " AND wa.status = ?";
|
|
queryParams.push(status);
|
|
}
|
|
|
|
if (departmentId) {
|
|
query += " AND e.department_id = ?";
|
|
queryParams.push(departmentId);
|
|
}
|
|
|
|
query += " ORDER BY wa.assigned_date DESC, wa.created_at DESC";
|
|
|
|
const allocations = await db.query<WorkAllocation[]>(query, queryParams);
|
|
ctx.response.body = allocations;
|
|
} catch (error) {
|
|
console.error("Get work allocations error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
// Get work allocation by ID
|
|
router.get("/:id", authenticateToken, async (ctx: RouterContext<"/:id">) => {
|
|
try {
|
|
const allocationId: string | undefined = ctx.params.id;
|
|
|
|
const allocations: WorkAllocation[] = await db.query<WorkAllocation[]>(
|
|
`SELECT wa.*,
|
|
e.name as employee_name, e.username as employee_username,
|
|
s.name as supervisor_name,
|
|
c.name as contractor_name,
|
|
sd.name as sub_department_name,
|
|
d.name as department_name
|
|
FROM work_allocations wa
|
|
JOIN users e ON wa.employee_id = e.id
|
|
JOIN users s ON wa.supervisor_id = s.id
|
|
JOIN users c ON wa.contractor_id = c.id
|
|
LEFT JOIN sub_departments sd ON wa.sub_department_id = sd.id
|
|
LEFT JOIN departments d ON e.department_id = d.id
|
|
WHERE wa.id = ?`,
|
|
[allocationId],
|
|
);
|
|
|
|
if (allocations.length === 0) {
|
|
ctx.response.status = 404;
|
|
ctx.response.body = { error: "Work allocation not found" };
|
|
return;
|
|
}
|
|
|
|
ctx.response.body = allocations[0];
|
|
} catch (error) {
|
|
console.error("Get work allocation error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
});
|
|
|
|
// Create work allocation (Supervisor or SuperAdmin)
|
|
router.post(
|
|
"/",
|
|
authenticateToken,
|
|
authorize("Supervisor", "SuperAdmin"),
|
|
async (
|
|
ctx: RouterContext<"/", Record<string | number, string | undefined>, State>,
|
|
) => {
|
|
try {
|
|
const currentUser = getCurrentUser(ctx);
|
|
const body = await ctx.request.body.json() as CreateWorkAllocationRequest;
|
|
const {
|
|
employeeId,
|
|
contractorId,
|
|
subDepartmentId,
|
|
activity,
|
|
description,
|
|
assignedDate,
|
|
rate,
|
|
units,
|
|
totalAmount,
|
|
departmentId,
|
|
} = body;
|
|
|
|
if (!employeeId || !contractorId || !assignedDate) {
|
|
ctx.response.status = 400;
|
|
ctx.response.body = { error: "Missing required fields" };
|
|
return;
|
|
}
|
|
|
|
// Verify employee exists
|
|
let employeeQuery = "SELECT * FROM users WHERE id = ?";
|
|
const employeeParams: unknown[] = [employeeId];
|
|
|
|
if (currentUser.role === "Supervisor") {
|
|
employeeQuery += " AND department_id = ?";
|
|
employeeParams.push(currentUser.departmentId);
|
|
}
|
|
|
|
const employees = await db.query<{ id: number }[]>(
|
|
employeeQuery,
|
|
employeeParams,
|
|
);
|
|
|
|
if (employees.length === 0) {
|
|
ctx.response.status = 403;
|
|
ctx.response.body = {
|
|
error: "Employee not found or not in your department",
|
|
};
|
|
return;
|
|
}
|
|
|
|
// Use provided rate or get contractor's current rate
|
|
let finalRate = rate;
|
|
if (!finalRate) {
|
|
const rates = await db.query<ContractorRate[]>(
|
|
"SELECT rate FROM contractor_rates WHERE contractor_id = ? ORDER BY effective_date DESC LIMIT 1",
|
|
[contractorId],
|
|
);
|
|
finalRate = rates.length > 0 ? rates[0].rate : null;
|
|
}
|
|
|
|
const sanitizedActivity = activity ? sanitizeInput(activity) : null;
|
|
const sanitizedDescription = description
|
|
? sanitizeInput(description)
|
|
: null;
|
|
|
|
const result = await db.execute(
|
|
`INSERT INTO work_allocations
|
|
(employee_id, supervisor_id, contractor_id, sub_department_id, activity, description, assigned_date, rate, units, total_amount)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
[
|
|
employeeId,
|
|
currentUser.id,
|
|
contractorId,
|
|
subDepartmentId || null,
|
|
sanitizedActivity,
|
|
sanitizedDescription,
|
|
assignedDate,
|
|
finalRate,
|
|
units || null,
|
|
totalAmount || null,
|
|
],
|
|
);
|
|
|
|
const newAllocation = await db.query<WorkAllocation[]>(
|
|
`SELECT wa.*,
|
|
e.name as employee_name, e.username as employee_username,
|
|
s.name as supervisor_name,
|
|
c.name as contractor_name,
|
|
sd.name as sub_department_name,
|
|
d.name as department_name
|
|
FROM work_allocations wa
|
|
JOIN users e ON wa.employee_id = e.id
|
|
JOIN users s ON wa.supervisor_id = s.id
|
|
JOIN users c ON wa.contractor_id = c.id
|
|
LEFT JOIN sub_departments sd ON wa.sub_department_id = sd.id
|
|
LEFT JOIN departments d ON e.department_id = d.id
|
|
WHERE wa.id = ?`,
|
|
[result.insertId],
|
|
);
|
|
|
|
ctx.response.status = 201;
|
|
ctx.response.body = newAllocation[0];
|
|
} catch (error) {
|
|
console.error("Create work allocation error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
// Update work allocation status (Supervisor or SuperAdmin)
|
|
router.put(
|
|
"/:id/status",
|
|
authenticateToken,
|
|
authorize("Supervisor", "SuperAdmin"),
|
|
async (ctx) => {
|
|
try {
|
|
const currentUser = getCurrentUser(ctx);
|
|
const allocationId = ctx.params.id;
|
|
const body = await ctx.request.body.json() as {
|
|
status: string;
|
|
completionDate?: string;
|
|
};
|
|
const { status, completionDate } = body;
|
|
|
|
if (!status) {
|
|
ctx.response.status = 400;
|
|
ctx.response.body = { error: "Status required" };
|
|
return;
|
|
}
|
|
|
|
// Verify allocation exists and user has access
|
|
let query = "SELECT * FROM work_allocations WHERE id = ?";
|
|
const params: unknown[] = [allocationId];
|
|
|
|
if (currentUser.role === "Supervisor") {
|
|
query += " AND supervisor_id = ?";
|
|
params.push(currentUser.id);
|
|
}
|
|
|
|
const allocations = await db.query<WorkAllocation[]>(query, params);
|
|
|
|
if (allocations.length === 0) {
|
|
ctx.response.status = 403;
|
|
ctx.response.body = {
|
|
error: "Work allocation not found or access denied",
|
|
};
|
|
return;
|
|
}
|
|
|
|
await db.execute(
|
|
"UPDATE work_allocations SET status = ?, completion_date = ? WHERE id = ?",
|
|
[status, completionDate || null, allocationId],
|
|
);
|
|
|
|
const updatedAllocation = await db.query<WorkAllocation[]>(
|
|
`SELECT wa.*,
|
|
e.name as employee_name, e.username as employee_username,
|
|
s.name as supervisor_name,
|
|
c.name as contractor_name,
|
|
sd.name as sub_department_name,
|
|
d.name as department_name
|
|
FROM work_allocations wa
|
|
JOIN users e ON wa.employee_id = e.id
|
|
JOIN users s ON wa.supervisor_id = s.id
|
|
JOIN users c ON wa.contractor_id = c.id
|
|
LEFT JOIN sub_departments sd ON wa.sub_department_id = sd.id
|
|
LEFT JOIN departments d ON e.department_id = d.id
|
|
WHERE wa.id = ?`,
|
|
[allocationId],
|
|
);
|
|
|
|
ctx.response.body = updatedAllocation[0];
|
|
} catch (error) {
|
|
console.error("Update work allocation error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
// Update work allocation units (Supervisor, Contractor, or SuperAdmin)
|
|
router.put(
|
|
"/:id/units",
|
|
authenticateToken,
|
|
authorize("Supervisor", "Contractor", "SuperAdmin"),
|
|
async (ctx) => {
|
|
try {
|
|
const currentUser = getCurrentUser(ctx);
|
|
const allocationId = ctx.params.id;
|
|
const body = await ctx.request.body.json() as {
|
|
units?: number;
|
|
completedUnits?: number;
|
|
markComplete?: boolean;
|
|
};
|
|
const { units, completedUnits, markComplete } = body;
|
|
|
|
if (units === undefined && completedUnits === undefined) {
|
|
ctx.response.status = 400;
|
|
ctx.response.body = { error: "Units or completedUnits required" };
|
|
return;
|
|
}
|
|
|
|
// Verify allocation exists and user has access
|
|
let query = "SELECT * FROM work_allocations WHERE id = ?";
|
|
const params: unknown[] = [allocationId];
|
|
|
|
if (currentUser.role === "Supervisor") {
|
|
query += " AND supervisor_id = ?";
|
|
params.push(currentUser.id);
|
|
} else if (currentUser.role === "Contractor") {
|
|
query += " AND contractor_id = ?";
|
|
params.push(currentUser.id);
|
|
}
|
|
|
|
const allocations = await db.query<WorkAllocation[]>(query, params);
|
|
|
|
if (allocations.length === 0) {
|
|
ctx.response.status = 403;
|
|
ctx.response.body = {
|
|
error: "Work allocation not found or access denied",
|
|
};
|
|
return;
|
|
}
|
|
|
|
const allocation = allocations[0];
|
|
const newUnits = units !== undefined ? units : allocation.units;
|
|
const newCompletedUnits = completedUnits !== undefined ? completedUnits : (allocation as any).completed_units || 0;
|
|
const rate = allocation.rate || 0;
|
|
const newTotalAmount = newCompletedUnits * rate;
|
|
|
|
// Determine status: mark as Completed if markComplete is true
|
|
const newStatus = markComplete ? "Completed" : allocation.status;
|
|
const completionDate = markComplete ? new Date().toISOString().split("T")[0] : allocation.completion_date;
|
|
|
|
await db.execute(
|
|
"UPDATE work_allocations SET units = ?, completed_units = ?, total_amount = ?, status = ?, completion_date = ? WHERE id = ?",
|
|
[newUnits, newCompletedUnits, newTotalAmount, newStatus, completionDate, allocationId],
|
|
);
|
|
|
|
const updatedAllocation = await db.query<WorkAllocation[]>(
|
|
`SELECT wa.*,
|
|
e.name as employee_name, e.username as employee_username,
|
|
s.name as supervisor_name,
|
|
c.name as contractor_name,
|
|
sd.name as sub_department_name,
|
|
d.name as department_name
|
|
FROM work_allocations wa
|
|
JOIN users e ON wa.employee_id = e.id
|
|
JOIN users s ON wa.supervisor_id = s.id
|
|
JOIN users c ON wa.contractor_id = c.id
|
|
LEFT JOIN sub_departments sd ON wa.sub_department_id = sd.id
|
|
LEFT JOIN departments d ON e.department_id = d.id
|
|
WHERE wa.id = ?`,
|
|
[allocationId],
|
|
);
|
|
|
|
ctx.response.body = updatedAllocation[0];
|
|
} catch (error) {
|
|
console.error("Update work allocation units error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
// Delete work allocation (Supervisor or SuperAdmin)
|
|
router.delete(
|
|
"/:id",
|
|
authenticateToken,
|
|
authorize("Supervisor", "SuperAdmin"),
|
|
async (ctx) => {
|
|
try {
|
|
const currentUser = getCurrentUser(ctx);
|
|
const allocationId = ctx.params.id;
|
|
|
|
// Verify allocation exists and user has access
|
|
let query = "SELECT * FROM work_allocations WHERE id = ?";
|
|
const params: unknown[] = [allocationId];
|
|
|
|
if (currentUser.role === "Supervisor") {
|
|
query += " AND supervisor_id = ?";
|
|
params.push(currentUser.id);
|
|
}
|
|
|
|
const allocations = await db.query<WorkAllocation[]>(query, params);
|
|
|
|
if (allocations.length === 0) {
|
|
ctx.response.status = 403;
|
|
ctx.response.body = {
|
|
error: "Work allocation not found or access denied",
|
|
};
|
|
return;
|
|
}
|
|
|
|
await db.execute("DELETE FROM work_allocations WHERE id = ?", [
|
|
allocationId,
|
|
]);
|
|
ctx.response.body = { message: "Work allocation deleted successfully" };
|
|
} catch (error) {
|
|
console.error("Delete work allocation error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
export default router;
|