<?php
require_once "../include/config.php";
// Start a session to manage user authentication
session_start();
// Check if the user is not authenticated (no session variable)
if (!isset($_SESSION["authenticated"]) || $_SESSION["authenticated"] !== true) {
// Redirect to the login page
//header("Location: index.php");
//exit;
}
// Create a database connection
$conn = new mysqli($host, $username, $password, $database);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$csvname = "All Assessment data.csv" ;
$csvFile = fopen($csvname, "w");
// Check if the file was successfully opened
if (!$csvFile) {
die("Unable to open CSV file for writing.");
}
// Write the CSV header row
fputcsv($csvFile, ["ID", "Kompetensi", "Soalan", "Competency", "Questions", "Self Rating", "Assessor 1 Rating", "Consultant Rating", "Candidate Name", "Position"]);
if (isset($_SESSION["project_id"])) {
// Get the value of the "key" parameter
//$project_id = $_GET['h'];
// To Do
// Select distinct candidate name and position and pass into the sql to loop
$dsql = "SELECT DISTINCT(for_candidate_name), assessment_id FROM assessment_by_assessor as aa inner join assessment as a ON aa.assessment_id = a.id WHERE a.project_id = 3 and aa.status = 2 ORDER BY aa.for_candidate_name asc";
$dresult = $conn->query($dsql);
if ($dresult->num_rows > 0) {
while ($drows = $dresult->fetch_assoc()) {
$sql = "SELECT * FROM assessment_by_assessor as aa
INNER JOIN assessment as a ON aa.assessment_id = a.id
WHERE aa.for_candidate_name = '".$drows['for_candidate_name']."'
AND aa.assessment_id = '".$drows["assessment_id"]."'
AND a.project_id = '".$_SESSION["project_id"]."'
AND aa.status = 2
ORDER BY aa.for_candidate_name ASC";
$result = $conn->query($sql);
if ($result instanceof mysqli_result && $result->num_rows > 0) {
$groupedData = [];
while ($rows = $result->fetch_assoc()) {
$candidateName = $rows["for_candidate_name"];
$position = $rows["name"];
$jsonData = json_decode($rows["data"], true);
foreach ($jsonData as $qid => $value) {
$id = substr($qid, 1);
$selfValue = ($rows["self_or_ext"] == 1) ? $value : null;
$extValue = ($rows["self_or_ext"] == 2) ? $value : null;
// Group the data by question ID
if (!isset($groupedData[$id])) {
$groupedData[$id] = [];
}
// Store the values for each question ID
$groupedData[$id][] = ["Self Value" => $selfValue, "Ext Value" => $extValue, "Candidate Name" => $candidateName, "Position" => $position];
}
}
// Output the grouped data in table format
//echo "<table border='1'><tr><th>ID</th><th>Kompetensi</th><th>Soalan</th><th>Competency</th><th>Questions</th><th>Self Rating</th><th>Assessor 1 Rating</th><th>Consultant Rating</th><th>Candidate Name</th><th>Position</th></tr>";
foreach ($groupedData as $gid => $values) {
// Initialize variables to store Self Value, Ext Value, and Candidate Name for each QID
$selfValue = '';
$extValue = '';
$candidateName = '';
$position = '';
$soalan = '';
$kompetensi = '';
$question = '';
$competency = '';
// Iterate over the values for the current QID
foreach ($values as $entry) {
//$bi_id = substr($gid, 1);
// Prepare an SQL query to fetch the question name
$bisql = "SELECT bi.nama as bi_nama, bi.name as bi_name, c.nama as com_nama, c.name as com_name FROM behavioral_indicator as bi inner join competency as c on bi.competency_id = c.id WHERE bi.id = $gid";
// Execute the query
$biresult = $conn->query($bisql);
if ($biresult->num_rows > 0) {
$row = $biresult->fetch_assoc();
$soalan = $row["bi_nama"];
$kompetensi = $row["com_nama"];
$question = $row["bi_name"];
$competency = $row["com_name"];
} else {
echo "Question not found in the database";
}
//$kompetensi = $entry['kompetensi'];
$selfValue .= ($entry['Self Value'] !== null) ? $entry['Self Value'] : '';
$extValue .= ($entry['Ext Value'] !== null) ? $entry['Ext Value'] : '';
$candidateName = $entry['Candidate Name'];
$position = $entry['Position'];
}
if ($extValue == '') {
$extValue = 0;
}
if ($selfValue == '') {
$selfValue = 0;
}
// Output each row with QID, Self Value, Ext Value, and Candidate Name
//echo "<tr><td>$gid</td><td>$kompetensi</td><td>$soalan</td><td>$competency</td><td>$question</td><td>$selfValue</td><td>$extValue</td><td>0</td><td>$candidateName</td><td>$position</td></tr>";
fputcsv($csvFile, [$gid, $kompetensi, $soalan, $competency, $question, $selfValue, $extValue, 0, $candidateName, $position]);
}
}
}
fclose($csvFile);
// Close the database connection
$conn->close();
// Set appropriate headers for file download
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="' . $csvname . '"');
header('Pragma: no-cache');
readfile($csvname);
// Delete the temporary CSV file
unlink($csvname);
exit();
// Loop through each question in $data
foreach ($data as $question => $rating) {
// Extract the question ID (e.g., '157' from 'q157')
$questionId = substr($question, 1);
// Prepare an SQL query to fetch the question name
$sql = "SELECT bi.nama as bi_nama, bi.name as bi_name, c.nama as com_nama, c.name as com_name FROM behavioral_indicator as bi inner join competency as c on bi.competency_id = c.id WHERE bi.id = $questionId";
// Execute the query
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Fetch the question name from the result
$row = $result->fetch_assoc();
$soalan = $row["bi_nama"];
$kompetensi = $row["com_nama"];
$question = $row["bi_name"];
$competency = $row["com_name"];
fputcsv($csvFile, [$questionId, $kompetensi, $soalan, $competency, $question, $rating]);
} else {
fputcsv($csvFile, [$questionId, "Question not found in the database", $rating]);
}
}
// Close the CSV file
fclose($csvFile);
// Close the database connection
$conn->close();
// Set appropriate headers for file download
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="' . $csvname . '"');
header('Pragma: no-cache');
readfile($csvname);
// Delete the temporary CSV file
unlink($csvname);
exit;
} else {
// Invalid login
// Redirect the user back to the login page with an error message
header("Location: candidates.php");
exit;
}
}
?>