Error: hostcompanyid is required"; print "Back to Employee Roles"; } exit; } $companyName = isset($global_company_name[$hostcompanyid]) ? $global_company_name[$hostcompanyid] : 'Unknown'; $reportDate = date("d M Y", isset($nowis) ? $nowis : time()); // ============================================================ // REPORT-WIDE STATISTICS ACCUMULATOR // Accumulated as roles/processes are rendered, used in Conclusion // ============================================================ $AI_ReportStats = array( 'total_roles' => 0, 'total_processes' => 0, // active (non-responsibility-only) processes 'responsibility_only_total' => 0, // count of responsibility-only processes 'overdue_tasks_total' => 0, 'processes_not_reviewed_24mo' => 0, 'processes_only_one_trained' => 0, 'processes_no_trained_staff' => 0, 'processes_missing_objective' => 0, 'processes_no_audit' => 0, 'processes_no_risk_assessment' => 0, 'processes_low_maturity' => 0, // maturity score < 4 of 6 'dependency_warnings' => array(), // list of "Person linked to X% of role" 'role_risk_summaries' => array(), // per-role indicator summaries 'unacceptable_risks' => array(), // processes with 'Unacceptable' risk code ); // ============================================================ // SHARED LOOKUP DATA - loaded once // ============================================================ // Risk type labels (typeid → description) $riskTypeLabels = array( 1 => 'Input to the Process', 2 => 'Outputs to the Process', 3 => 'Risks to the Process', 4 => 'Steps to Minimise Process Risks', 5 => 'Scope / Definition of Process / Objective', 6 => 'Likelihood of Risk (1-5)', 7 => 'Severity of Risk (1-5)', 8 => 'Measurable Objective', 9 => 'Training Method Required', 10 => 'Verification / Effectiveness', 11 => 'Covid / Pandemic Notes', 12 => 'Risk / Benefit Report', 13 => 'HSE Implications' ); // Frequency / Severity text labels $freqText = array( 1 => '1.Improbable', 2 => '2.Remote', 3 => '3.Occasional', 4 => '4.Probable', 5 => '5.Frequent' ); $sevText = array( 1 => '1.Negligible', 2 => '2.Minor', 3 => '3.Serious', 4 => '4.Critical', 5 => '5.Catastrophic' ); // Cache job titles $jobTitles = array(); $jobSupervisor = array(); $sql = "SELECT id, description, supervisorid, heightofrole, defaultmeeting FROM ISO_job_titles ORDER BY heightofrole DESC, description"; $stmt = pdoquery($sql); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $jobTitles[$row['id']] = $row['description']; $jobSupervisor[$row['id']] = $row['supervisorid']; } // Cache training courses $trainingCourses = array(); $sql = "SELECT id, coursename FROM ISO_training_course"; $stmt = pdoquery($sql); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $trainingCourses[$row['id']] = $row['coursename']; } // Cache interested parties $interestedParties = array(); $sql = "SELECT id, description FROM ISO_InterestedParties"; $stmt = pdoquery($sql); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $interestedParties[$row['id']] = $row['description']; } // ============================================================ // HELPER FUNCTIONS // ============================================================ /** * Risk matrix calculation (matches processrisk.php) */ function AI_riskdefinition($freq, $severity) { $riskarray = array( array("NEEDS SETTING","NEEDS SETTING","NEEDS SETTING","NEEDS SETTING","NEEDS SETTING","NEEDS SETTING"), array("NEEDS SETTING","No Action","No Action","No Action","Risk Benefits","Unacceptable"), array("NEEDS SETTING","No Action","No Action","Risk Benefits","Unacceptable","Unacceptable"), array("NEEDS SETTING","No Action","Risk Benefits","Unacceptable","Unacceptable","Unacceptable"), array("NEEDS SETTING","Risk Benefits","Unacceptable","Unacceptable","Unacceptable","Unacceptable"), array("NEEDS SETTING","Unacceptable","Unacceptable","Unacceptable","Unacceptable","Unacceptable") ); $f = (int)$freq; $s = (int)$severity; if ($f < 0 || $f > 5) $f = 0; if ($s < 0 || $s > 5) $s = 0; return $riskarray[$f][$s]; } /** * Get processes linked to a document via ISO sections OR direct process links. * This is used by document_review.php to build a process list from a document perspective. */ function AI_getProcessesForDocument($docid) { $processes = array(); $seenIds = array(); // Method 1: Via ISO manual sections (doc → ISO section → process) $sql = "SELECT DISTINCT p.id, p.description, p.taskid, p.auditid, p.agendaid, p.trainingcourseid, p.responsibilityonly, p.reviewed, p.reviewedby FROM ISO_job_base_process p JOIN ISO_quality_manual_processes qmp ON p.id = qmp.processid JOIN ISO_quality_manual_filelinks qmf ON qmp.qmid = qmf.qmid WHERE qmf.fileid = ? AND (p.archivedon = 0 OR p.archivedon IS NULL) ORDER BY p.description"; $stmt = pdoquery($sql, array($docid)); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) { $pid = (int)$row['id']; if (!isset($seenIds[$pid])) { $processes[] = $row; $seenIds[$pid] = 1; } } // Method 2: Direct process-document links $sql = "SELECT DISTINCT p.id, p.description, p.taskid, p.auditid, p.agendaid, p.trainingcourseid, p.responsibilityonly, p.reviewed, p.reviewedby FROM ISO_job_base_process p JOIN ISO_job_base_process_files pf ON p.id = pf.processid WHERE pf.docid = ? AND (p.archivedon = 0 OR p.archivedon IS NULL) ORDER BY p.description"; $stmt = pdoquery($sql, array($docid)); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) { $pid = (int)$row['id']; if (!isset($seenIds[$pid])) { $processes[] = $row; $seenIds[$pid] = 1; } } return $processes; } /** * Get processes linked to a role via ISO_job_base_process_title. * This is used by management_review.php to build a process list from a role perspective. */ function AI_getProcessesForRole($titleid) { global $processesByRole; if (isset($processesByRole) && isset($processesByRole[$titleid])) { return $processesByRole[$titleid]; } $sql = "SELECT p.id, p.description, p.taskid, p.auditid, p.agendaid, p.trainingcourseid, p.responsibilityonly, p.reviewed, p.reviewedby FROM ISO_job_base_process p JOIN ISO_job_base_process_title pt ON p.id = pt.processid WHERE pt.titleid = ? AND (p.archivedon = 0 OR p.archivedon IS NULL) ORDER BY p.description"; $stmt = pdoquery($sql, array($titleid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get visible roles for a company (excludes hidden) */ function AI_getRolesForCompany($hostcompanyid) { $sql = "SELECT t.id, t.description, t.supervisorid, t.heightofrole FROM ISO_job_titles t WHERE t.id NOT IN ( SELECT titleid FROM ISO_job_titles_hide_host WHERE hostcompanyid = ? ) ORDER BY t.heightofrole DESC, t.description"; $stmt = pdoquery($sql, array($hostcompanyid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get employees assigned to a role - uses cache when available. */ function AI_getEmployeesForRole($titleid) { global $cache_roleEmployees; $names = array(); if (isset($cache_roleEmployees) && isset($cache_roleEmployees[$titleid])) { foreach ($cache_roleEmployees[$titleid] as $eid) { $names[] = AI_uname($eid); } return $names; } $sql = "SELECT employid FROM ISO_job_titles_employeeid WHERE titleid = ?"; $stmt = pdoquery($sql, array($titleid)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $names[] = getusername($row['employid']); } return $names; } /** * Get risk assessment for a process - uses cache when available. */ function AI_getRiskAssessment($processid) { global $cache_risks; if (isset($cache_risks) && isset($cache_risks[$processid])) { return $cache_risks[$processid]; } if (isset($cache_risks)) return array(); // bulk-loaded but no risks $sql = "SELECT typeid, notes, updatedby, updatedon, reviewedon FROM ISO_job_titles_risks WHERE jobid = ? ORDER BY typeid"; $stmt = pdoquery($sql, array($processid)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); $risks = array(); foreach ($rows as $row) { $risks[$row['typeid']] = array( 'notes' => $row['notes'], 'updatedby' => $row['updatedby'], 'updatedon' => $row['updatedon'], 'reviewedon' => $row['reviewedon'] ); } return $risks; } /** * Get rolling task/audit definition - uses cache when available. */ function AI_getRollingDefinition($id) { global $cache_rollingDef; if ($id <= 0) return null; if (isset($cache_rollingDef) && isset($cache_rollingDef[$id])) { return $cache_rollingDef[$id]; } if (isset($cache_rollingDef)) return null; // bulk-loaded $sql = "SELECT id, subject, type, target_user, recur_time_months, recur_time_weeks, recur_time_days, notes FROM viamedagendafuture WHERE id = ?"; $stmt = pdoquery($sql, array($id)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); return !empty($rows) ? $rows[0] : null; } /** * Get task history limited to last 2 years OR 20 items. * Uses bulk cache when available (caller passes processid in $context). */ function AI_getTaskHistory($taskid, $auditid, $processid = 0) { global $cache_taskHistory; $taskid = (int)$taskid; $auditid = (int)$auditid; if ($taskid <= 0 && $auditid <= 0) return array(); if ($processid > 0 && isset($cache_taskHistory) && isset($cache_taskHistory[$processid])) { return $cache_taskHistory[$processid]; } if ($processid > 0 && isset($cache_taskHistory)) return array(); // bulk-loaded but no history $twoYearsAgo = strtotime('-2 years'); $sql = "SELECT id, subject, completed, investigatedcomplete, createdate, completedate, investigateby, autoissueid, rollingissueagendaid FROM viamedagendahistory WHERE (autoissueid = ? OR autoissueid = ? OR rollingissueagendaid = ? OR rollingissueagendaid = ?) AND createdate > ? ORDER BY createdate DESC LIMIT 20"; $stmt = pdoquery($sql, array($taskid, $auditid, $taskid, $auditid, $twoYearsAgo)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get task history log notes for an issue - uses cache when available. */ function AI_getTaskHistoryLogs($issueid) { global $cache_taskHistoryLogs; $issueid = (int)$issueid; if ($issueid <= 0) return array(); if (isset($cache_taskHistoryLogs) && isset($cache_taskHistoryLogs[$issueid])) { return $cache_taskHistoryLogs[$issueid]; } if (isset($cache_taskHistoryLogs)) return array(); // bulk-loaded but no logs $sql = "SELECT actions, notes, addedby, addedon FROM viamedagendahistorylog WHERE mainissueid = ? ORDER BY addedon DESC, id DESC LIMIT 2"; $stmt = pdoquery($sql, array($issueid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get effectiveness reviews for a process - uses cache when available. */ function AI_getEffectivenessReviews($processid) { global $cache_effectiveness; if (isset($cache_effectiveness) && isset($cache_effectiveness[$processid])) { return $cache_effectiveness[$processid]; } if (isset($cache_effectiveness)) return array(); $sql = "SELECT reviewedon, reviewedby, memo FROM ISO_job_base_process_effective_review WHERE processid = ? ORDER BY reviewedon DESC LIMIT 20"; $stmt = pdoquery($sql, array($processid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get linked ISO sections for a process - uses cache when available. */ function AI_getISOSections($processid) { global $cache_isoSections; if (isset($cache_isoSections) && isset($cache_isoSections[$processid])) { return $cache_isoSections[$processid]; } if (isset($cache_isoSections)) return array(); $sql = "SELECT qm.iso_section, qm.filter, fg.description as filterdesc FROM ISO_quality_manual_processes qmp JOIN ISO_quality_manual qm ON qmp.qmid = qm.id LEFT JOIN ISO_quality_manual_filter_groups fg ON qm.filter = fg.filter WHERE qmp.processid = ? ORDER BY qm.filter, qm.iso_section"; $stmt = pdoquery($sql, array($processid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get interested parties linked to a process - uses cache when available. */ function AI_getProcessInterestedParties($processid) { global $cache_parties; if (isset($cache_parties) && isset($cache_parties[$processid])) { return $cache_parties[$processid]; } if (isset($cache_parties)) return array(); $sql = "SELECT interestedparty FROM ISO_job_base_process_interestedpartys WHERE processid = ?"; $stmt = pdoquery($sql, array($processid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get linked companies for a process - uses cache when available. */ function AI_getProcessCompanies($processid) { global $cache_companies; if (isset($cache_companies) && isset($cache_companies[$processid])) { return $cache_companies[$processid]; } if (isset($cache_companies)) return array(); $sql = "SELECT hostcompanyid FROM ISO_job_base_process_companyid WHERE processid = ?"; $stmt = pdoquery($sql, array($processid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get linked documents for a process - uses cache when available. */ function AI_getProcessDocuments($processid) { global $cache_documents; if (isset($cache_documents) && isset($cache_documents[$processid])) { return $cache_documents[$processid]; } if (isset($cache_documents)) return array(); $sql = "SELECT DISTINCT qmf.docid, qmf.description, qmf.type FROM ISO_job_base_process_files pf JOIN ISO_quality_manual_files qmf ON pf.docid = qmf.docid WHERE pf.processid = ? ORDER BY qmf.description"; $stmt = pdoquery($sql, array($processid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } /** * Get trained employees for a process - uses cache when available. */ function AI_getTrainedEmployees($processid) { global $cache_trainedEmployees; if (isset($cache_trainedEmployees) && isset($cache_trainedEmployees[$processid])) { return $cache_trainedEmployees[$processid]; } if (isset($cache_trainedEmployees)) return array(); $sql = "SELECT employeetrained, trainedby FROM ISO_training_tasks WHERE processid = ?"; $stmt = pdoquery($sql, array($processid)); return $stmt->fetchAll(PDO::FETCH_ASSOC); } // ============================================================ // ANALYTICS / RISK INDICATOR FUNCTIONS // ============================================================ /** * Calculate Role Risk Indicators - uses bulk caches. */ function AI_calculateRoleRiskIndicators($titleid, $processes) { global $cache_risks, $cache_trainedCount, $cache_overdueRolling; $indicators = array( 'linked_processes' => count($processes), 'overdue_tasks' => 0, 'not_reviewed_24mo' => 0, 'one_trained_user' => 0, 'no_trained_staff' => 0, 'missing_objective' => 0, 'no_audit' => 0, 'no_risk_assessment' => 0, ); $cutoff24mo = strtotime('-24 months'); foreach ($processes as $process) { $processid = $process['id']; // Not reviewed in 24 months if ((int)$process['reviewed'] < $cutoff24mo) { $indicators['not_reviewed_24mo']++; } // Trained staff count (from cache) $trainedCount = isset($cache_trainedCount[$processid]) ? (int)$cache_trainedCount[$processid] : 0; if ($trainedCount == 0) { $indicators['no_trained_staff']++; } elseif ($trainedCount == 1) { $indicators['one_trained_user']++; } // Missing measurable objective (typeid 8) - from cache $hasObjective = false; if (isset($cache_risks[$processid]) && isset($cache_risks[$processid][8])) { $notes = $cache_risks[$processid][8]['notes']; if (!empty(trim(strip_tags(str_replace('
', '', $notes))))) { $hasObjective = true; } } if (!$hasObjective) { $indicators['missing_objective']++; } // No audit configured (and not responsibility-only) if ((int)$process['responsibilityonly'] != 1 && (int)$process['auditid'] <= 0) { $indicators['no_audit']++; } // No risk assessment at all - from cache if (!isset($cache_risks[$processid]) || empty($cache_risks[$processid])) { $indicators['no_risk_assessment']++; } // Overdue rolling task - from cache $tid = (int)$process['taskid']; $aid = (int)$process['auditid']; if ($tid > 0 && isset($cache_overdueRolling[$tid])) { $indicators['overdue_tasks'] += (int)$cache_overdueRolling[$tid]; } if ($aid > 0 && isset($cache_overdueRolling[$aid])) { $indicators['overdue_tasks'] += (int)$cache_overdueRolling[$aid]; } } return $indicators; } /** * Check if a rolling task/audit is overdue. * Returns 1 if overdue, 0 otherwise. * Overdue = oldest outstanding instance is past expected next-recurrence date. */ function AI_isTaskOverdue($rollingId) { // Get the rolling definition $def = AI_getRollingDefinition($rollingId); if (!$def) return 0; $months = (int)$def['recur_time_months']; $weeks = (int)$def['recur_time_weeks']; $days = (int)$def['recur_time_days']; // Total recurrence period in seconds $recurSeconds = ($months * 30 + $weeks * 7 + $days) * 86400; if ($recurSeconds <= 0) $recurSeconds = 86400; // safety default 1 day // Check for outstanding instances $sql = "SELECT createdate FROM viamedagendahistory WHERE (autoissueid = ? OR rollingissueagendaid = ?) AND (completedate = '0' OR completedate = '' OR completedate IS NULL) AND (investigatedcomplete = '0' OR investigatedcomplete IS NULL) ORDER BY createdate ASC LIMIT 1"; $stmt = pdoquery($sql, array($rollingId, $rollingId)); $row = $stmt->fetch(PDO::FETCH_ASSOC); if ($row && $row['createdate'] > 0) { $age = time() - (int)$row['createdate']; // Allow 1.5x the recurrence period as grace if ($age > ($recurSeconds * 1.5)) { return 1; } } return 0; } /** * Calculate Dependency / Single-Point-of-Failure Risk for a role - uses bulk cache. */ function AI_calculateDependencyRisk($titleid, $processes, $threshold = 70) { global $cache_trainedEmployees; $warnings = array(); if (empty($processes)) return $warnings; // Build set of distinct process IDs in this role (defensive against duplicates) $distinctProcessIds = array(); foreach ($processes as $process) { $distinctProcessIds[(int)$process['id']] = 1; } $totalProcesses = count($distinctProcessIds); if ($totalProcesses == 0) return $warnings; // employeeToProcesses[empid] = array(processid => 1, ...) - dedup via array keys $employeeToProcesses = array(); // processToEmployees[processid] = array(empid => 1, ...) - dedup via array keys $processToEmployees = array(); foreach ($distinctProcessIds as $pid => $_v) { $rows = isset($cache_trainedEmployees[$pid]) ? $cache_trainedEmployees[$pid] : array(); foreach ($rows as $r) { $eid = (int)$r['employeetrained']; if ($eid <= 0) continue; if (!isset($employeeToProcesses[$eid])) { $employeeToProcesses[$eid] = array(); } $employeeToProcesses[$eid][$pid] = 1; if (!isset($processToEmployees[$pid])) { $processToEmployees[$pid] = array(); } $processToEmployees[$pid][$eid] = 1; } } // High-dependency individuals (count distinct processes per employee) foreach ($employeeToProcesses as $empid => $pidMap) { $cnt = count($pidMap); // Defensive clamp - cnt should never exceed totalProcesses now, but cap anyway if ($cnt > $totalProcesses) $cnt = $totalProcesses; $pct = round(($cnt / $totalProcesses) * 100); if ($pct > 100) $pct = 100; if ($pct >= $threshold) { $name = AI_uname($empid); $warnings[] = array( 'type' => 'high_dependency', 'message' => htmlspecialchars($name)." linked to ".$pct."% (".$cnt." of ".$totalProcesses.") of role processes - single point of failure risk", 'employee' => $name, 'percentage' => $pct ); } } // Processes with only 1 distinct trained employee $solo = 0; foreach ($distinctProcessIds as $pid => $_v) { $eMap = isset($processToEmployees[$pid]) ? $processToEmployees[$pid] : array(); if (count($eMap) == 1) $solo++; } if ($solo > 0) { $warnings[] = array( 'type' => 'solo_processes', 'message' => $solo." process(es) have only 1 trained employee - no backup coverage", 'count' => $solo ); } return $warnings; } /** * Calculate Process Maturity for a single process - uses bulk cache. */ function AI_calculateProcessMaturity($process, $risks, $taskHistoryCount) { global $cache_trainedCount; $processid = $process['id']; $checks = array(); // Has Risk Assessment (any risk fields populated) $hasRisk = false; foreach (array(1, 2, 3, 4, 5) as $tid) { if (isset($risks[$tid]) && !empty(trim(strip_tags(str_replace('
', '', $risks[$tid]['notes']))))) { $hasRisk = true; break; } } $checks['Has Risk Assessment'] = $hasRisk; // Has Audit configured $checks['Has Audit'] = ((int)$process['auditid'] > 0); // Has Measurable Objective (typeid 8) $hasObjective = isset($risks[8]) && !empty(trim(strip_tags(str_replace('
', '', $risks[8]['notes'])))); $checks['Has Measurable Objective'] = $hasObjective; // Has Backup Staff (>1 trained) - from cache $trainedCount = isset($cache_trainedCount[$processid]) ? (int)$cache_trainedCount[$processid] : 0; $checks['Has Backup Staff'] = ($trainedCount > 1); // Reviewed within last 12 months $cutoff12mo = strtotime('-12 months'); $checks['Reviewed <12 Months'] = ((int)$process['reviewed'] >= $cutoff12mo); // Recent Completion Evidence (any completed task in last 12 months) $checks['Recent Completion Evidence'] = ($taskHistoryCount > 0); // If responsibility-only, audit/completion not required - mark as N/A (counted as Yes for score) if ((int)$process['responsibilityonly'] == 1) { $checks['Has Audit'] = 'N/A'; $checks['Recent Completion Evidence'] = 'N/A'; } // Calculate score (out of 6) $score = 0; $applicable = 0; foreach ($checks as $val) { if ($val === true) { $score++; $applicable++; } elseif ($val === false) { $applicable++; } // N/A doesn't count toward applicable } return array( 'checks' => $checks, 'score' => $score, 'applicable' => $applicable, 'percentage' => $applicable > 0 ? round(($score / $applicable) * 100) : 0 ); } // ============================================================ // HTML RENDERING FUNCTIONS // ============================================================ /** * Render report header */ function AI_renderHeader($companyName, $reportDate, $isFullReport, $roleName = '') { $html = ""; $html .= "
"; $html .= "

Management Review Report

"; $html .= "

".htmlspecialchars($companyName)."

"; if ($isFullReport) { $html .= "

Full Company Review - All Roles

"; } else { $html .= "

Role: ".htmlspecialchars($roleName)."

"; } $html .= "

Report Date: ".$reportDate."

"; $html .= "
"; $html .= "
"; return $html; } /** * Render legend / key explaining the colour coding used in Task Completion History tables. * Shown once after the Index. */ function AI_renderLegend() { $html = ""; $html .= "
"; $html .= "

Task Completion History - Colour Key

"; $html .= "

Colour coding in the 'Days to Complete' column reflects performance, not formal sign-off status (some auto-issue tasks never receive formal investigation sign-off).

"; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= "
GreenCompleted in 14 days or less (good)
YellowCompleted in 15 - 60 days (acceptable)
OrangeCompleted in over 60 days (slow)
RedOutstanding - not yet completed (age shown)
"; $html .= "
"; return $html; } /** * Render role section */ function AI_renderRoleSection($role, $hostcompanyid, $jobTitles, $jobSupervisor, $riskTypeLabels, $freqText, $sevText, $trainingCourses, $interestedParties) { global $global_company_name; $titleid = $role['id']; $html = ""; // Role header $html .= "
"; $html .= "

Role: ".htmlspecialchars($role['description'])." (ID #".$titleid.")

"; // Supervisor chain $supervisorChain = array(); $currentSuper = isset($jobSupervisor[$titleid]) ? $jobSupervisor[$titleid] : 0; $depth = 0; while ($currentSuper > 0 && $depth < 10) { if (isset($jobTitles[$currentSuper])) { $supervisorChain[] = $jobTitles[$currentSuper]; $currentSuper = isset($jobSupervisor[$currentSuper]) ? $jobSupervisor[$currentSuper] : 0; } else { break; } $depth++; } $html .= "

Reports To: ".(empty($supervisorChain) ? 'None' : htmlspecialchars(implode(' → ', $supervisorChain)))."

"; // Assigned employees $employees = AI_getEmployeesForRole($titleid); $html .= "

Assigned Employees: ".(empty($employees) ? 'Unassigned' : htmlspecialchars(implode(', ', $employees)))."

"; // Get processes - split into active (with audits/tasks) and responsibility-only $allProcessesForRole = AI_getProcessesForRole($titleid); $processes = array(); // active processes - included in main report $respOnlyProcesses = array(); // responsibility-only - listed separately foreach ($allProcessesForRole as $p) { if ((int)$p['responsibilityonly'] == 1) { $respOnlyProcesses[] = $p; } else { $processes[] = $p; } } if (empty($allProcessesForRole)) { $html .= "

No processes assigned to this role.

"; } else { $html .= "

Processes Assigned: ".count($allProcessesForRole) ." (".count($processes)." active, ".count($respOnlyProcesses)." responsibility-only)

"; // Only proceed with indicators/dependency/render if there are active processes if (!empty($processes)) { // Role Risk Indicators (calculated only on active processes) $indicators = AI_calculateRoleRiskIndicators($titleid, $processes); $html .= AI_renderRoleRiskIndicators($role['description'], $indicators); // Dependency / Single-Point-of-Failure Risk (active processes only) $depWarnings = AI_calculateDependencyRisk($titleid, $processes, 70); $html .= AI_renderDependencyRisk($depWarnings); // Track for stats (active processes only) global $AI_ReportStats; $AI_ReportStats['role_risk_summaries'][] = array( 'role' => $role['description'], 'indicators' => $indicators ); $AI_ReportStats['overdue_tasks_total'] += $indicators['overdue_tasks']; $AI_ReportStats['processes_not_reviewed_24mo'] += $indicators['not_reviewed_24mo']; $AI_ReportStats['processes_only_one_trained'] += $indicators['one_trained_user']; $AI_ReportStats['processes_no_trained_staff'] += $indicators['no_trained_staff']; $AI_ReportStats['processes_missing_objective'] += $indicators['missing_objective']; $AI_ReportStats['processes_no_audit'] += $indicators['no_audit']; $AI_ReportStats['processes_no_risk_assessment'] += $indicators['no_risk_assessment']; foreach ($depWarnings as $dw) { if ($dw['type'] === 'high_dependency') { $AI_ReportStats['dependency_warnings'][] = "[".$role['description']."] ".$dw['message']; } } // Render each active process foreach ($processes as $process) { $html .= AI_renderProcessSection($process, $hostcompanyid, $riskTypeLabels, $freqText, $sevText, $trainingCourses, $interestedParties); } } // Render responsibility-only list at end of role chapter if (!empty($respOnlyProcesses)) { $html .= AI_renderResponsibilityOnlyList($respOnlyProcesses); // Track for conclusion summary global $AI_ReportStats; if (!isset($AI_ReportStats['responsibility_only_total'])) { $AI_ReportStats['responsibility_only_total'] = 0; } $AI_ReportStats['responsibility_only_total'] += count($respOnlyProcesses); } } $html .= "
"; return $html; } /** * Render compact Responsibility-Only Processes list (no audits/tasks - shown after main role processes) */ function AI_renderResponsibilityOnlyList($respOnlyProcesses) { $html = ""; $html .= "
"; $html .= "

Responsibility-Only Processes (".count($respOnlyProcesses).")

"; $html .= "

These processes represent allocated responsibilities only - no rolling task or audit is required, so they are excluded from the main report metrics and conclusion.

"; $html .= ""; $html .= "
"; return $html; } /** * Render Role Risk Indicators block */ function AI_renderRoleRiskIndicators($roleName, $indicators) { $html = ""; $html .= "
"; $html .= "

Role Risk Indicators - ".htmlspecialchars($roleName)."

"; $html .= ""; $html .= "
"; return $html; } /** * Render Dependency Risk warnings block */ function AI_renderDependencyRisk($warnings) { if (empty($warnings)) return ""; $html = ""; $html .= "
"; $html .= "

Dependency / Single-Point-of-Failure Risk

"; $html .= ""; $html .= "
"; return $html; } /** * Render Conclusion / Executive Summary section */ function AI_renderConclusion($stats, $companyName, $isFullReport) { $html = ""; $html .= "
"; $html .= "

Conclusion / Executive Summary

"; $html .= "

Auto-calculated summary of findings across the report.

"; // Top-level numbers $html .= "

Coverage

"; $html .= ""; // Key risks $hasKeyRisks = false; $risksHtml = "

Key Risks

"; $html .= $risksHtml; // Overdue Reviews $html .= "

Overdue Reviews

"; $html .= ""; // Dependency warnings $html .= "

Dependency Warnings

"; if (empty($stats['dependency_warnings'])) { $html .= ""; } else { $html .= ""; } // Missing audits $html .= "

Missing Audits

"; $html .= ""; // Missing measurable objectives $html .= "

Missing Measurable Objectives

"; $html .= ""; // Backup staff coverage $html .= "

Trained Staff Coverage

"; $html .= ""; // Maturity observations $html .= "

Process Maturity Overview

"; $html .= ""; // Per-role indicator summary table if (!empty($stats['role_risk_summaries'])) { $html .= "

Per-Role Risk Indicator Summary

"; $html .= ""; $html .= ""; foreach (array('Role','Processes','Overdue','>24mo Review','Solo Trained','No Trained','Missing Obj','No Audit') as $thLbl) { $html .= ""; } $html .= ""; foreach ($stats['role_risk_summaries'] as $rs) { $i = $rs['indicators']; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; } $html .= "
".htmlspecialchars($thLbl)."
".htmlspecialchars($rs['role'])."".(int)$i['linked_processes']."".((int)$i['overdue_tasks'] > 0 ? "".(int)$i['overdue_tasks']."" : '0')."".((int)$i['not_reviewed_24mo'] > 0 ? "".(int)$i['not_reviewed_24mo']."" : '0')."".((int)$i['one_trained_user'] > 0 ? "".(int)$i['one_trained_user']."" : '0')."".((int)$i['no_trained_staff'] > 0 ? "".(int)$i['no_trained_staff']."" : '0')."".((int)$i['missing_objective'] > 0 ? "".(int)$i['missing_objective']."" : '0')."".((int)$i['no_audit'] > 0 ? "".(int)$i['no_audit']."" : '0')."
"; } $html .= "

End of Management Review Report - ".htmlspecialchars($companyName)."

"; $html .= "
"; return $html; } /** * Render Process Maturity table (compact, embedded in process section) */ function AI_renderProcessMaturity($maturity) { $html = ""; $html .= "

Process Maturity (".$maturity['score']." of ".$maturity['applicable']." - ".$maturity['percentage']."%)

"; $html .= ""; $html .= ""; foreach ($maturity['checks'] as $label => $val) { if ($val === true) { $statusHtml = "Yes"; } elseif ($val === false) { $statusHtml = "No"; } else { $statusHtml = "N/A"; } $html .= ""; } $html .= "
MeasureStatus
".htmlspecialchars($label)."".$statusHtml."
"; return $html; } /** * Render single process section with all details */ function AI_renderProcessSection($process, $hostcompanyid, $riskTypeLabels, $freqText, $sevText, $trainingCourses, $interestedParties) { global $global_company_name; $processid = $process['id']; $html = ""; $html .= "
"; // Strong header banner so the boundary between processes is unmistakable. // Use a 1-cell table so TCPDF reliably renders the background colour in PDF mode. $html .= ""; $html .= ""; $html .= "
"; $html .= "Process ID #".(int)$processid." — ".htmlspecialchars($process['description']).""; $html .= "
"; $html .= "
"; // Process meta $reviewDate = ($process['reviewed'] > 0) ? date("d M Y", $process['reviewed']) : 'Never'; $html .= "

Last Reviewed: ".$reviewDate; if ($process['reviewedby'] > 0) { $html .= " by ".htmlspecialchars(getusername($process['reviewedby'])); } $html .= "

"; if ($process['responsibilityonly'] == 1) { $html .= "

Responsibility-only process (no rolling task or audit).

"; } if ($process['trainingcourseid'] > 0 && isset($trainingCourses[$process['trainingcourseid']])) { $html .= "

Training Course Required: ".htmlspecialchars($trainingCourses[$process['trainingcourseid']])."

"; } // Risk Assessment $risks = AI_getRiskAssessment($processid); // Process Maturity (calculated and shown near top of process) $taskHistoryForMaturity = AI_getTaskHistory($process['taskid'], $process['auditid'], $processid); $maturity = AI_calculateProcessMaturity($process, $risks, count($taskHistoryForMaturity)); $html .= AI_renderProcessMaturity($maturity); // Track stats global $AI_ReportStats; $AI_ReportStats['total_processes']++; if ($maturity['applicable'] > 0 && $maturity['score'] < 4) { $AI_ReportStats['processes_low_maturity']++; } $freqForStats = isset($risks[6]) ? (int)$risks[6]['notes'] : 0; $sevForStats = isset($risks[7]) ? (int)$risks[7]['notes'] : 0; $riskCodeForStats = AI_riskdefinition($freqForStats, $sevForStats); if ($riskCodeForStats === 'Unacceptable') { $AI_ReportStats['unacceptable_risks'][] = "Process #".$processid.": ".$process['description']; } $html .= "

Risk Assessment

"; $html .= ""; $html .= ""; foreach ($riskTypeLabels as $typeid => $label) { $notes = isset($risks[$typeid]) ? $risks[$typeid]['notes'] : ''; $updatedOn = isset($risks[$typeid]) && $risks[$typeid]['updatedon'] > 0 ? date("d M Y", $risks[$typeid]['updatedon']) : ''; $updatedBy = isset($risks[$typeid]) && $risks[$typeid]['updatedby'] > 0 ? getusername($risks[$typeid]['updatedby']) : ''; // For likelihood/severity, show text if ($typeid == 6 && isset($freqText[$notes])) { $notes = $freqText[$notes]; } elseif ($typeid == 7 && isset($sevText[$notes])) { $notes = $sevText[$notes]; } $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; } // Risk code calculation $freq = isset($risks[6]) ? (int)$risks[6]['notes'] : 0; $sev = isset($risks[7]) ? (int)$risks[7]['notes'] : 0; $riskCode = AI_riskdefinition($freq, $sev); $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= "
Risk ItemNotes / ValueLast Updated
".htmlspecialchars($label)."".nl2br(htmlspecialchars(strip_tags($notes)))."".htmlspecialchars($updatedBy." ".$updatedOn)."
Calculated Risk Code".htmlspecialchars($riskCode)." (Frequency=".$freq.", Severity=".$sev.")
"; // ISO Sections $isoSections = AI_getISOSections($processid); if (!empty($isoSections)) { $html .= "

Linked ISO Sections

"; } // Interested Parties $parties = AI_getProcessInterestedParties($processid); if (!empty($parties)) { $html .= "

Interested Parties

"; } // Linked Companies $companies = AI_getProcessCompanies($processid); if (!empty($companies)) { $html .= "

Linked Companies

"; } // Trained Staff $trained = AI_getTrainedEmployees($processid); if (!empty($trained)) { $html .= "

Trained Staff (".count($trained).")

"; } // Linked Documents $docs = AI_getProcessDocuments($processid); if (!empty($docs)) { $html .= "

Associated Documents

"; } // Rolling Task if ($process['taskid'] > 0) { $task = AI_getRollingDefinition($process['taskid']); if ($task) { $html .= "

Rolling Task #".$task['id']."

"; $html .= "

Subject: ".htmlspecialchars($task['subject'])."
"; $html .= "Target User: ".htmlspecialchars($task['target_user'])."
"; $html .= "Recurrence: ".(int)$task['recur_time_months']." Month(s) ".(int)$task['recur_time_weeks']." Week(s) ".(int)$task['recur_time_days']." Day(s)

"; if (!empty($task['notes'])) { $html .= "

Notes:
".nl2br(htmlspecialchars(strip_tags($task['notes'])))."

"; } } } // Rolling Audit if ($process['auditid'] > 0) { $audit = AI_getRollingDefinition($process['auditid']); if ($audit) { $html .= "

Rolling Audit #".$audit['id']."

"; $html .= "

Subject: ".htmlspecialchars($audit['subject'])."
"; $html .= "Target User: ".htmlspecialchars($audit['target_user'])."
"; $html .= "Recurrence: ".(int)$audit['recur_time_months']." Month(s) ".(int)$audit['recur_time_weeks']." Week(s) ".(int)$audit['recur_time_days']." Day(s)

"; if (!empty($audit['notes'])) { $html .= "

Notes:
".nl2br(htmlspecialchars(strip_tags($audit['notes'])))."

"; } } } // Task History (limited to 2yr / 20 items) - reuse the data already fetched for maturity calc $history = $taskHistoryForMaturity; $html .= "

Task Completion History (Last 2 Years, Max 20 Items)

"; if (empty($history)) { $html .= "

No completed tasks in the last 2 years.

"; } else { $html .= ""; $html .= ""; foreach (array('ID','Subject','Created','Completed','Days to Complete','Investigated By') as $thLbl) { $html .= ""; } $html .= ""; foreach ($history as $h) { $createdTs = (int)$h['createdate']; $completedTs = ((int)$h['completedate'] > 0 && $h['completedate'] != '') ? (int)$h['completedate'] : 0; $created = $createdTs > 0 ? date("d M Y", $createdTs) : ''; $completed = $completedTs > 0 ? date("d M Y", $completedTs) : ''; // Days to Complete (or 'Outstanding' if never completed) $daysCell = ''; $bgcolor = '#ffffff'; $rowClass = ''; if ($completedTs > 0 && $createdTs > 0) { $days = max(0, (int)floor(($completedTs - $createdTs) / 86400)); $daysCell = $days." day".($days == 1 ? '' : 's'); if ($days <= 14) { $bgcolor = '#d4edda'; $rowClass = 'row-green'; } elseif ($days <= 60) { $bgcolor = '#fff3cd'; $rowClass = 'row-yellow'; } else { $bgcolor = '#fde4d8'; $rowClass = 'row-orange'; } } else { // Not completed - show outstanding age if ($createdTs > 0) { $daysOpen = max(0, (int)floor((time() - $createdTs) / 86400)); $daysCell = "Outstanding (".$daysOpen." day".($daysOpen == 1 ? '' : 's').")"; } else { $daysCell = "Outstanding"; } $bgcolor = '#f8d7da'; $rowClass = 'row-red'; } $rowAttrs = "class='".$rowClass."' bgcolor='".$bgcolor."' style='background-color: ".$bgcolor.";'"; $cellAttrs = "class='".$rowClass."' bgcolor='".$bgcolor."' style='background-color: ".$bgcolor.";'"; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $logs = AI_getTaskHistoryLogs((int)$h['id']); if (!empty($logs)) { $html .= ""; } } $html .= "
".$thLbl."
".(int)$h['id']."".htmlspecialchars($h['subject'])."".$created."".$completed."".$daysCell."".htmlspecialchars($h['investigateby'])."
"; $html .= ""; foreach ($logs as $log) { $logDate = !empty($log['addedon']) ? date("d M Y H:i", $log['addedon']) : ''; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; } $html .= "
" . htmlspecialchars($logDate) . "" . htmlspecialchars($log['actions']) . "" . htmlspecialchars($log['addedby']) . "" . htmlspecialchars($log['notes']) . "
"; $html .= "
"; } // Effectiveness Reviews $reviews = AI_getEffectivenessReviews($processid); if (!empty($reviews)) { $html .= "

Effectiveness Reviews

"; $html .= ""; $html .= ""; foreach ($reviews as $r) { $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; } $html .= "
DateReviewerMemo
".date("d M Y", $r['reviewedon'])."".htmlspecialchars(getusername($r['reviewedby']))."".nl2br(htmlspecialchars(strip_tags(str_replace("
", "\n", $r['memo']))))."
"; } $html .= "
"; // process-body $html .= "
"; // process-section return $html; } // ============================================================ // MAIN REPORT GENERATION // ============================================================ // Allow up to 5 minutes for full company reports @set_time_limit(300); $isFullReport = ($roleid == '' || $roleid < 1); // Build report content $reportHtml = ""; // Determine which roles to include $rolesToInclude = array(); if ($isFullReport) { $rolesToInclude = AI_getRolesForCompany($hostcompanyid); } else { // Single role if (isset($jobTitles[$roleid])) { $rolesToInclude[] = array( 'id' => $roleid, 'description' => $jobTitles[$roleid], 'supervisorid' => isset($jobSupervisor[$roleid]) ? $jobSupervisor[$roleid] : 0, 'heightofrole' => 0 ); } } // ============================================================ // BULK PRE-FETCH OF ALL PROCESS-RELATED DATA // (Eliminates N+1 query problem - one query per data type instead of per process) // ============================================================ // Step 1: collect all role IDs and their processes in one go $roleIds = array(); foreach ($rolesToInclude as $r) { $roleIds[] = (int)$r['id']; } // Bulk-load processes-per-role mapping $processesByRole = array(); // titleid => array of process rows $allProcesses = array(); // processid => process row (for later lookups) $allProcessIds = array(); if (!empty($roleIds)) { $placeholders = implode(',', array_fill(0, count($roleIds), '?')); $sql = "SELECT p.id, p.description, p.taskid, p.auditid, p.agendaid, p.trainingcourseid, p.responsibilityonly, p.reviewed, p.reviewedby, pt.titleid FROM ISO_job_base_process p JOIN ISO_job_base_process_title pt ON p.id = pt.processid WHERE pt.titleid IN ($placeholders) AND (p.archivedon = 0 OR p.archivedon IS NULL) ORDER BY p.description"; $stmt = pdoquery($sql, $roleIds); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $tid = $row['titleid']; $pid = $row['id']; if (!isset($processesByRole[$tid])) $processesByRole[$tid] = array(); $processesByRole[$tid][] = $row; $allProcesses[$pid] = $row; $allProcessIds[] = $pid; } } $allProcessIds = array_unique($allProcessIds); // Pre-fetch caches keyed by process id $cache_risks = array(); // pid => typeid => array(notes, updatedby, updatedon, reviewedon) $cache_trainedCount = array(); // pid => count $cache_trainedEmployees = array(); // pid => array of [employeetrained, trainedby] $cache_isoSections = array(); // pid => array of [iso_section, filter, filterdesc] $cache_parties = array(); // pid => array of [interestedparty] $cache_companies = array(); // pid => array of [hostcompanyid] $cache_documents = array(); // pid => array of [docid, description, type] $cache_taskHistory = array(); // pid => array of history rows $cache_effectiveness = array(); // pid => array of review rows $cache_rollingDef = array(); // rollingid => row $cache_overdueRolling = array(); // rollingid => 1/0 if (!empty($allProcessIds)) { $pPlace = implode(',', array_fill(0, count($allProcessIds), '?')); // Risks $sql = "SELECT jobid, typeid, notes, updatedby, updatedon, reviewedon FROM ISO_job_titles_risks WHERE jobid IN ($pPlace) ORDER BY typeid"; $stmt = pdoquery($sql, $allProcessIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $pid = $r['jobid']; if (!isset($cache_risks[$pid])) $cache_risks[$pid] = array(); $cache_risks[$pid][$r['typeid']] = array( 'notes' => $r['notes'], 'updatedby' => $r['updatedby'], 'updatedon' => $r['updatedon'], 'reviewedon' => $r['reviewedon'] ); } // Training tasks (deduplicate per (processid, employeetrained) pair so duplicate // rows in ISO_training_tasks don't inflate counts and produce >100% dependency) $sql = "SELECT processid, employeetrained, trainedby FROM ISO_training_tasks WHERE processid IN ($pPlace)"; $stmt = pdoquery($sql, $allProcessIds); $seenTraining = array(); // pid|empid => 1 foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $pid = (int)$r['processid']; $eid = (int)$r['employeetrained']; if ($pid <= 0 || $eid <= 0) continue; $key = $pid.'|'.$eid; if (isset($seenTraining[$key])) continue; // skip duplicate $seenTraining[$key] = 1; if (!isset($cache_trainedEmployees[$pid])) $cache_trainedEmployees[$pid] = array(); $cache_trainedEmployees[$pid][] = $r; $cache_trainedCount[$pid] = isset($cache_trainedCount[$pid]) ? $cache_trainedCount[$pid] + 1 : 1; } // ISO sections $sql = "SELECT qmp.processid, qm.iso_section, qm.filter, fg.description as filterdesc FROM ISO_quality_manual_processes qmp JOIN ISO_quality_manual qm ON qmp.qmid = qm.id LEFT JOIN ISO_quality_manual_filter_groups fg ON qm.filter = fg.filter WHERE qmp.processid IN ($pPlace) ORDER BY qm.filter, qm.iso_section"; $stmt = pdoquery($sql, $allProcessIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $pid = $r['processid']; if (!isset($cache_isoSections[$pid])) $cache_isoSections[$pid] = array(); $cache_isoSections[$pid][] = $r; } // Interested parties $sql = "SELECT processid, interestedparty FROM ISO_job_base_process_interestedpartys WHERE processid IN ($pPlace)"; $stmt = pdoquery($sql, $allProcessIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $pid = $r['processid']; if (!isset($cache_parties[$pid])) $cache_parties[$pid] = array(); $cache_parties[$pid][] = $r; } // Companies $sql = "SELECT processid, hostcompanyid FROM ISO_job_base_process_companyid WHERE processid IN ($pPlace)"; $stmt = pdoquery($sql, $allProcessIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $pid = $r['processid']; if (!isset($cache_companies[$pid])) $cache_companies[$pid] = array(); $cache_companies[$pid][] = $r; } // Documents $sql = "SELECT pf.processid, qmf.docid, qmf.description, qmf.type FROM ISO_job_base_process_files pf JOIN ISO_quality_manual_files qmf ON pf.docid = qmf.docid WHERE pf.processid IN ($pPlace) GROUP BY pf.processid, qmf.docid ORDER BY qmf.description"; $stmt = pdoquery($sql, $allProcessIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $pid = $r['processid']; if (!isset($cache_documents[$pid])) $cache_documents[$pid] = array(); $cache_documents[$pid][] = $r; } // Effectiveness reviews $sql = "SELECT processid, reviewedon, reviewedby, memo FROM ISO_job_base_process_effective_review WHERE processid IN ($pPlace) ORDER BY reviewedon DESC"; $stmt = pdoquery($sql, $allProcessIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $pid = $r['processid']; if (!isset($cache_effectiveness[$pid])) $cache_effectiveness[$pid] = array(); if (count($cache_effectiveness[$pid]) < 20) { // cap at 20 like original $cache_effectiveness[$pid][] = $r; } } } // Pre-fetch rolling task/audit definitions for all referenced taskid/auditid $allRollingIds = array(); foreach ($allProcesses as $p) { if ((int)$p['taskid'] > 0) $allRollingIds[] = (int)$p['taskid']; if ((int)$p['auditid'] > 0) $allRollingIds[] = (int)$p['auditid']; } $allRollingIds = array_unique($allRollingIds); if (!empty($allRollingIds)) { $rPlace = implode(',', array_fill(0, count($allRollingIds), '?')); $sql = "SELECT id, subject, type, target_user, recur_time_months, recur_time_weeks, recur_time_days, notes FROM viamedagendafuture WHERE id IN ($rPlace)"; $stmt = pdoquery($sql, $allRollingIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $cache_rollingDef[$r['id']] = $r; } // Bulk overdue check - oldest outstanding instance per rolling id $sql = "SELECT rollingid, MIN(createdate) as oldest FROM ( SELECT autoissueid as rollingid, createdate FROM viamedagendahistory WHERE autoissueid IN ($rPlace) AND (completedate = '0' OR completedate = '' OR completedate IS NULL) AND (investigatedcomplete = '0' OR investigatedcomplete IS NULL) UNION ALL SELECT rollingissueagendaid as rollingid, createdate FROM viamedagendahistory WHERE rollingissueagendaid IN ($rPlace) AND (completedate = '0' OR completedate = '' OR completedate IS NULL) AND (investigatedcomplete = '0' OR investigatedcomplete IS NULL) ) sub GROUP BY rollingid"; // duplicate placeholder list (it's used twice in the union) $stmt = pdoquery($sql, array_merge($allRollingIds, $allRollingIds)); $oldestByRolling = array(); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $oldestByRolling[$r['rollingid']] = (int)$r['oldest']; } foreach ($allRollingIds as $rid) { $isOver = 0; if (isset($oldestByRolling[$rid]) && isset($cache_rollingDef[$rid])) { $def = $cache_rollingDef[$rid]; $months = (int)$def['recur_time_months']; $weeks = (int)$def['recur_time_weeks']; $days = (int)$def['recur_time_days']; $recurSeconds = ($months * 30 + $weeks * 7 + $days) * 86400; if ($recurSeconds <= 0) $recurSeconds = 86400; $age = time() - $oldestByRolling[$rid]; if ($age > ($recurSeconds * 1.5)) $isOver = 1; } $cache_overdueRolling[$rid] = $isOver; } // Bulk task history (last 2yr / max 20 per rolling id is hard in SQL, // so do per-process when rendering - but cap tightly). We pre-load all // rows in window then filter in PHP. $twoYearsAgo = strtotime('-2 years'); $sql = "SELECT id, subject, completed, investigatedcomplete, createdate, completedate, investigateby, autoissueid, rollingissueagendaid FROM viamedagendahistory WHERE (autoissueid IN ($rPlace) OR rollingissueagendaid IN ($rPlace)) AND createdate > ? ORDER BY createdate DESC"; $stmt = pdoquery($sql, array_merge($allRollingIds, $allRollingIds, array($twoYearsAgo))); $allHistoryRows = $stmt->fetchAll(PDO::FETCH_ASSOC); // Distribute to processes via taskid/auditid lookup foreach ($allProcesses as $pid => $p) { $tid = (int)$p['taskid']; $aid = (int)$p['auditid']; if ($tid <= 0 && $aid <= 0) continue; $matches = array(); foreach ($allHistoryRows as $h) { $hAuto = (int)$h['autoissueid']; $hRoll = (int)$h['rollingissueagendaid']; if (($tid > 0 && ($hAuto === $tid || $hRoll === $tid)) || ($aid > 0 && ($hAuto === $aid || $hRoll === $aid))) { $matches[] = $h; if (count($matches) >= 20) break; } } $cache_taskHistory[$pid] = $matches; } // Collect all history IDs for bulk log pre-fetch $allHistoryIds = array(); foreach ($allHistoryRows as $h) { $allHistoryIds[] = (int)$h['id']; } $allHistoryIds = array_unique($allHistoryIds); // Bulk pre-fetch viamedagendahistorylog for all history IDs $cache_taskHistoryLogs = array(); if (!empty($allHistoryIds)) { $hPlace = implode(',', array_fill(0, count($allHistoryIds), '?')); $sql = "SELECT mainissueid, actions, notes, addedby, addedon FROM viamedagendahistorylog WHERE mainissueid IN ($hPlace) ORDER BY addedon DESC, id DESC"; $stmt = pdoquery($sql, $allHistoryIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $mid = (int)$r['mainissueid']; if (!isset($cache_taskHistoryLogs[$mid])) { $cache_taskHistoryLogs[$mid] = array(); } if (count($cache_taskHistoryLogs[$mid]) < 2) { $cache_taskHistoryLogs[$mid][] = $r; } } } } // Bulk-fetch all employee names for users we'll reference (fewer getusername() calls) $cache_usernames = array(); $userIdsToFetch = array(); foreach ($allProcesses as $p) { if ((int)$p['reviewedby'] > 0) $userIdsToFetch[] = (int)$p['reviewedby']; } foreach ($cache_trainedEmployees as $list) { foreach ($list as $r) { $userIdsToFetch[] = (int)$r['employeetrained']; if ((int)$r['trainedby'] > 0) $userIdsToFetch[] = (int)$r['trainedby']; } } foreach ($cache_effectiveness as $list) { foreach ($list as $r) { if ((int)$r['reviewedby'] > 0) $userIdsToFetch[] = (int)$r['reviewedby']; } } foreach ($cache_risks as $rmap) { foreach ($rmap as $r) { if ((int)$r['updatedby'] > 0) $userIdsToFetch[] = (int)$r['updatedby']; } } $userIdsToFetch = array_unique($userIdsToFetch); if (!empty($userIdsToFetch)) { $uPlace = implode(',', array_fill(0, count($userIdsToFetch), '?')); $sql = "SELECT id, username FROM pw WHERE id IN ($uPlace)"; $stmt = pdoquery($sql, $userIdsToFetch); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $cache_usernames[$r['id']] = $r['username']; } } // Bulk-fetch employees per role (used in role render) $cache_roleEmployees = array(); // titleid => array of employids if (!empty($roleIds)) { $rPlace = implode(',', array_fill(0, count($roleIds), '?')); $sql = "SELECT titleid, employid FROM ISO_job_titles_employeeid WHERE titleid IN ($rPlace)"; $stmt = pdoquery($sql, $roleIds); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $tid = $r['titleid']; if (!isset($cache_roleEmployees[$tid])) $cache_roleEmployees[$tid] = array(); $cache_roleEmployees[$tid][] = $r['employid']; $userIdsToFetch[] = (int)$r['employid']; } // Fetch any new usernames not already cached $newUsers = array(); foreach ($cache_roleEmployees as $list) { foreach ($list as $eid) { if (!isset($cache_usernames[$eid])) $newUsers[] = (int)$eid; } } $newUsers = array_unique($newUsers); if (!empty($newUsers)) { $uPlace = implode(',', array_fill(0, count($newUsers), '?')); $sql = "SELECT id, username FROM pw WHERE id IN ($uPlace)"; $stmt = pdoquery($sql, $newUsers); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $cache_usernames[$r['id']] = $r['username']; } } } // Helper: get cached username, fallback to getusername() function AI_uname($empid) { global $cache_usernames; if ($empid <= 0) return ''; if (isset($cache_usernames[$empid])) return $cache_usernames[$empid]; return getusername($empid); } $primaryRoleName = !$isFullReport && !empty($rolesToInclude) ? $rolesToInclude[0]['description'] : ''; $reportHtml .= AI_renderHeader($companyName, $reportDate, $isFullReport, $primaryRoleName); // Index/TOC $reportHtml .= "
"; $reportHtml .= "

Index

"; $reportHtml .= "
    "; foreach ($rolesToInclude as $role) { $reportHtml .= "
  1. ".htmlspecialchars($role['description'])." (ID #".$role['id'].")
    2. "; } $reportHtml .= "
  2. Conclusion / Executive Summary
    3. "; $reportHtml .= "
"; $reportHtml .= "
"; // Legend - explain the colour coding used in Task Completion History tables $reportHtml .= AI_renderLegend(); // Render each role $AI_ReportStats['total_roles'] = count($rolesToInclude); foreach ($rolesToInclude as $role) { $reportHtml .= AI_renderRoleSection($role, $hostcompanyid, $jobTitles, $jobSupervisor, $riskTypeLabels, $freqText, $sevText, $trainingCourses, $interestedParties); } // ============================================================ // CONCLUSION / EXECUTIVE SUMMARY // ============================================================ $reportHtml .= AI_renderConclusion($AI_ReportStats, $companyName, $isFullReport); // ============================================================ // OUTPUT // ============================================================ if ($outputtype === 'html') { // HTML mode - inline display for debugging ?> Management Review - <?php echo htmlspecialchars($companyName); ?>
← Back to Employee Roles | Download as PDF
SetCreator('Management Review System'); $pdf->SetTitle($reportTitle.' - '.$companyName); $pdf->SetSubject($reportSubtitle); $pdf->setHeaderData('', 0, $reportTitle, $reportSubtitle); $pdf->setHeaderFont(array('helvetica', '', 10)); $pdf->setFooterFont(array('helvetica', '', 8)); $pdf->SetMargins(15, 25, 15); $pdf->SetHeaderMargin(10); $pdf->SetFooterMargin(10); $pdf->SetAutoPageBreak(true, 20); $pdf->SetFont('helvetica', '', 9); $pdf->AddPage(); // TCPDF 6.x supports CSS classes via a "; $pdf->writeHTML($pdfStyles.$reportHtml, true, true, true, true, ''); $filename = 'management_review_'.$hostcompanyid.'_'.$filenameSlug.'_'.date('Ymd').'.pdf'; $pdf->Output($filename, 'D'); } else { print "

PDF generation unavailable

"; print "

TCPDF library not found at expected location.

"; print "

View as HTML instead

"; } } /** * Create a safe filename slug from a string (lowercase, alnum + underscores). */ function AI_makeFileSlug($text) { $slug = strtolower(trim($text)); $slug = preg_replace('/[^a-z0-9]+/', '_', $slug); $slug = trim($slug, '_'); if ($slug === '') $slug = 'report'; if (strlen($slug) > 60) $slug = substr($slug, 0, 60); return $slug; } ?>