Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | 6x 6x | const pool = require('../config/db');
class Audit {
static async create({ establishment_id, user_id, audit_date }) {
const [result] = await pool.query(
'INSERT INTO audits (establishment_id, user_id, audit_date) VALUES (?, ?, ?)',
[establishment_id, user_id, audit_date]
);
return result.insertId;
}
static async findById(id) {
const [rows] = await pool.query(`
SELECT a.*, e.name AS establishment_name, e.location AS establishment_location
FROM audits a
JOIN establishments e ON a.establishment_id = e.id
WHERE a.id = ?
`, [id]);
return rows[0];
}
static async findByUser(user_id) {
const [rows] = await pool.query(`
SELECT a.*, e.name AS establishment_name
FROM audits a
JOIN establishments e ON a.establishment_id = e.id
WHERE a.user_id = ?
ORDER BY a.audit_date DESC
`, [user_id]);
return rows;
}
static async addAspect(audit_id, { aspect_type, is_compliant = false, notes = '' }) {
const [result] = await pool.query(
'INSERT INTO audit_aspects (audit_id, aspect_type, is_compliant, notes) VALUES (?, ?, ?, ?)',
[audit_id, aspect_type, is_compliant, notes]
);
return result.insertId;
}
static async addBiodiversityDetail(aspect_id, { invasive_plants_status, details, action_plan }) {
await pool.query(
'INSERT INTO biodiversity_details (audit_aspect_id, invasive_plants_status, details, action_plan) VALUES (?, ?, ?, ?)',
[aspect_id, invasive_plants_status, details, action_plan]
);
}
static async addEvidence(audit_id, { file_path, file_name, file_type, file_size }, user_id) {
await pool.query(
'INSERT INTO evidence (audit_id, file_path, file_name, file_type, file_size, uploaded_by) VALUES (?, ?, ?, ?, ?, ?)',
[audit_id, file_path, file_name, file_type, file_size, user_id]
);
}
static async submit(audit_id) {
await pool.query(
'UPDATE audits SET status = "submitted", submitted_at = NOW() WHERE id = ?',
[audit_id]
);
}
static async getComplianceHistory(establishment_id) {
const [rows] = await pool.query(`
SELECT
YEAR(audit_date) AS year,
AVG(compliance_score) AS avg_score,
COUNT(*) AS audit_count
FROM audits
WHERE establishment_id = ?
AND status = 'approved'
GROUP BY YEAR(audit_date)
ORDER BY year DESC
LIMIT 3
`, [establishment_id]);
return rows;
}
static async getEvidenceStatus(audit_id) {
const [rows] = await pool.query(`
SELECT
COUNT(*) AS total,
SUM(CASE WHEN file_path IS NOT NULL THEN 1 ELSE 0 END) AS uploaded
FROM audit_evidence_requirements
WHERE audit_id = ?
`, [audit_id]);
return rows[0];
}
}
module.exports = Audit; |