320 lines
9.4 KiB
TypeScript
320 lines
9.4 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,
|
|
CreateContractorRateRequest,
|
|
User,
|
|
} from "../types/index.ts";
|
|
|
|
const router = new Router();
|
|
|
|
// Get contractor rates
|
|
router.get(
|
|
"/",
|
|
authenticateToken,
|
|
async (
|
|
ctx: RouterContext<"/", Record<string | number, string | undefined>, State>,
|
|
) => {
|
|
try {
|
|
const params: URLSearchParams = ctx.request.url.searchParams;
|
|
const contractorId: string | null = params.get("contractorId");
|
|
const subDepartmentId: string | null = params.get("subDepartmentId");
|
|
|
|
let query: string = `
|
|
SELECT cr.*,
|
|
u.name as contractor_name, u.username as contractor_username,
|
|
sd.name as sub_department_name,
|
|
d.name as department_name,
|
|
a.unit_of_measurement
|
|
FROM contractor_rates cr
|
|
JOIN users u ON cr.contractor_id = u.id
|
|
LEFT JOIN sub_departments sd ON cr.sub_department_id = sd.id
|
|
LEFT JOIN departments d ON sd.department_id = d.id
|
|
LEFT JOIN activities a ON a.sub_department_id = cr.sub_department_id AND a.name = cr.activity
|
|
WHERE 1=1
|
|
`;
|
|
const queryParams: unknown[] = [];
|
|
|
|
if (contractorId) {
|
|
query += " AND cr.contractor_id = ?";
|
|
queryParams.push(contractorId);
|
|
}
|
|
|
|
if (subDepartmentId) {
|
|
query += " AND cr.sub_department_id = ?";
|
|
queryParams.push(subDepartmentId);
|
|
}
|
|
|
|
query += " ORDER BY cr.effective_date DESC, cr.created_at DESC";
|
|
|
|
const rates = await db.query<ContractorRate[]>(query, queryParams);
|
|
ctx.response.body = rates;
|
|
} catch (error) {
|
|
console.error("Get contractor rates error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
// Get current rate for a contractor + sub-department combination
|
|
router.get(
|
|
"/contractor/:contractorId/current",
|
|
authenticateToken,
|
|
async (
|
|
ctx: RouterContext<
|
|
"/contractor/:contractorId/current",
|
|
{ contractorId: string } & Record<string | number, string | undefined>,
|
|
State
|
|
>,
|
|
) => {
|
|
try {
|
|
const contractorId = ctx.params.contractorId;
|
|
const params = ctx.request.url.searchParams;
|
|
const subDepartmentId = params.get("subDepartmentId");
|
|
|
|
let query: string = `
|
|
SELECT cr.*,
|
|
u.name as contractor_name, u.username as contractor_username,
|
|
sd.name as sub_department_name,
|
|
a.unit_of_measurement
|
|
FROM contractor_rates cr
|
|
JOIN users u ON cr.contractor_id = u.id
|
|
LEFT JOIN sub_departments sd ON cr.sub_department_id = sd.id
|
|
LEFT JOIN activities a ON a.sub_department_id = cr.sub_department_id AND a.name = cr.activity
|
|
WHERE cr.contractor_id = ?
|
|
`;
|
|
const queryParams: unknown[] = [contractorId];
|
|
|
|
if (subDepartmentId) {
|
|
query += " AND cr.sub_department_id = ?";
|
|
queryParams.push(subDepartmentId);
|
|
}
|
|
|
|
query += " ORDER BY cr.effective_date DESC LIMIT 1";
|
|
|
|
const rates = await db.query<ContractorRate[]>(query, queryParams);
|
|
|
|
if (rates.length === 0) {
|
|
ctx.response.status = 404;
|
|
ctx.response.body = { error: "No rate found for contractor" };
|
|
return;
|
|
}
|
|
|
|
ctx.response.body = rates[0];
|
|
} catch (error) {
|
|
console.error("Get current rate error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
// Set contractor rate (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 CreateContractorRateRequest;
|
|
const { contractorId, subDepartmentId, activity, rate, effectiveDate } =
|
|
body;
|
|
|
|
if (!contractorId || !rate || !effectiveDate) {
|
|
ctx.response.status = 400;
|
|
ctx.response.body = {
|
|
error: "Missing required fields (contractorId, rate, effectiveDate)",
|
|
};
|
|
return;
|
|
}
|
|
|
|
// Verify contractor exists
|
|
const contractors = await db.query<User[]>(
|
|
"SELECT * FROM users WHERE id = ? AND role = ?",
|
|
[contractorId, "Contractor"],
|
|
);
|
|
|
|
if (contractors.length === 0) {
|
|
ctx.response.status = 404;
|
|
ctx.response.body = { error: "Contractor not found" };
|
|
return;
|
|
}
|
|
|
|
// Supervisors can only set rates for contractors in their department
|
|
if (
|
|
currentUser.role === "Supervisor" &&
|
|
contractors[0].department_id !== currentUser.departmentId
|
|
) {
|
|
ctx.response.status = 403;
|
|
ctx.response.body = { error: "Contractor not in your department" };
|
|
return;
|
|
}
|
|
|
|
const sanitizedActivity = activity ? sanitizeInput(activity) : null;
|
|
|
|
const result: { insertId: number; affectedRows: number } = await db
|
|
.execute(
|
|
"INSERT INTO contractor_rates (contractor_id, sub_department_id, activity, rate, effective_date) VALUES (?, ?, ?, ?, ?)",
|
|
[
|
|
contractorId,
|
|
subDepartmentId || null,
|
|
sanitizedActivity,
|
|
rate,
|
|
effectiveDate,
|
|
],
|
|
);
|
|
|
|
const newRate: ContractorRate[] = await db.query<ContractorRate[]>(
|
|
`SELECT cr.*,
|
|
u.name as contractor_name, u.username as contractor_username,
|
|
sd.name as sub_department_name,
|
|
a.unit_of_measurement
|
|
FROM contractor_rates cr
|
|
JOIN users u ON cr.contractor_id = u.id
|
|
LEFT JOIN sub_departments sd ON cr.sub_department_id = sd.id
|
|
LEFT JOIN activities a ON a.sub_department_id = cr.sub_department_id AND a.name = cr.activity
|
|
WHERE cr.id = ?`,
|
|
[result.insertId],
|
|
);
|
|
|
|
ctx.response.status = 201;
|
|
ctx.response.body = newRate[0];
|
|
} catch (error) {
|
|
console.error("Set contractor rate error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
// Update contractor rate
|
|
router.put(
|
|
"/:id",
|
|
authenticateToken,
|
|
authorize("Supervisor", "SuperAdmin"),
|
|
async (
|
|
ctx: RouterContext<
|
|
"/:id",
|
|
{ id: string } & Record<string | number, string | undefined>,
|
|
State
|
|
>,
|
|
) => {
|
|
try {
|
|
const rateId = ctx.params.id;
|
|
const body = await ctx.request.body.json() as {
|
|
rate?: number;
|
|
activity?: string;
|
|
effectiveDate?: string;
|
|
};
|
|
const { rate, activity, effectiveDate } = body;
|
|
|
|
const existing = await db.query<ContractorRate[]>(
|
|
"SELECT * FROM contractor_rates WHERE id = ?",
|
|
[rateId],
|
|
);
|
|
|
|
if (existing.length === 0) {
|
|
ctx.response.status = 404;
|
|
ctx.response.body = { error: "Rate not found" };
|
|
return;
|
|
}
|
|
|
|
const updates: string[] = [];
|
|
const params: unknown[] = [];
|
|
|
|
if (rate !== undefined) {
|
|
updates.push("rate = ?");
|
|
params.push(rate);
|
|
}
|
|
if (activity !== undefined) {
|
|
updates.push("activity = ?");
|
|
params.push(sanitizeInput(activity));
|
|
}
|
|
if (effectiveDate !== undefined) {
|
|
updates.push("effective_date = ?");
|
|
params.push(effectiveDate);
|
|
}
|
|
|
|
if (updates.length === 0) {
|
|
ctx.response.status = 400;
|
|
ctx.response.body = { error: "No fields to update" };
|
|
return;
|
|
}
|
|
|
|
params.push(rateId);
|
|
|
|
await db.execute(
|
|
`UPDATE contractor_rates SET ${updates.join(", ")} WHERE id = ?`,
|
|
params,
|
|
);
|
|
|
|
const updatedRate = await db.query<ContractorRate[]>(
|
|
`SELECT cr.*,
|
|
u.name as contractor_name, u.username as contractor_username,
|
|
sd.name as sub_department_name,
|
|
a.unit_of_measurement
|
|
FROM contractor_rates cr
|
|
JOIN users u ON cr.contractor_id = u.id
|
|
LEFT JOIN sub_departments sd ON cr.sub_department_id = sd.id
|
|
LEFT JOIN activities a ON a.sub_department_id = cr.sub_department_id AND a.name = cr.activity
|
|
WHERE cr.id = ?`,
|
|
[rateId],
|
|
);
|
|
|
|
ctx.response.body = updatedRate[0];
|
|
} catch (error) {
|
|
console.error("Update contractor rate error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
// Delete contractor rate
|
|
router.delete(
|
|
"/:id",
|
|
authenticateToken,
|
|
authorize("Supervisor", "SuperAdmin"),
|
|
async (
|
|
ctx: RouterContext<
|
|
"/:id",
|
|
{ id: string } & Record<string | number, string | undefined>,
|
|
State
|
|
>,
|
|
) => {
|
|
try {
|
|
const rateId = ctx.params.id;
|
|
|
|
const existing = await db.query<ContractorRate[]>(
|
|
"SELECT * FROM contractor_rates WHERE id = ?",
|
|
[rateId],
|
|
);
|
|
|
|
if (existing.length === 0) {
|
|
ctx.response.status = 404;
|
|
ctx.response.body = { error: "Rate not found" };
|
|
return;
|
|
}
|
|
|
|
await db.execute("DELETE FROM contractor_rates WHERE id = ?", [rateId]);
|
|
ctx.response.body = { message: "Rate deleted successfully" };
|
|
} catch (error) {
|
|
console.error("Delete contractor rate error:", error);
|
|
ctx.response.status = 500;
|
|
ctx.response.body = { error: "Internal server error" };
|
|
}
|
|
},
|
|
);
|
|
|
|
export default router;
|