<?php
require_once "../include/config.php";
// Create a database connection
$conn = new mysqli($host, $username, $password, $database);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Check if the form is submitted
if (isset($_GET['h'])) {
// Get the value of the "key" parameter
$hash = $_GET['h'];
$sql = "SELECT * FROM assessment_by_assessor where hash = '".$hash."' ";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
$rows = $result->fetch_assoc();
//Get candidate's name
$candidate_name = $rows["for_candidate_name"];
$assessment_id = $rows["assessment_id"];
$count_sql = "SELECT * FROM assessment_by_assessor where for_candidate_name = '".$candidate_name."' and status = 2 and assessment_id = ".$assessment_id;
//echo $count_sql;
$count_result = $conn->query($count_sql);
if ($count_result) {
$num_rows = $count_result->num_rows;
if ($num_rows > 1) {
// Rows were found
echo "Number of rows: " . $num_rows;
// Fetch the first row if needed
$row = $count_result->fetch_assoc();
// ... rest of your code ...
} else {
// No rows found
echo "Only 1 result!";
}
}
exit();
// Decode the JSON into a PHP array
$data = json_decode($rows["data"], true);
//echo $rows["self_or_ext"];
//exit();
// Create csv file name here
if ($rows["self_or_ext"] <> 2) {
$csvname = "Self Assessment by " . preg_replace('/[^\w\-]/', '_', $rows["for_candidate_name"]) .".csv" ;
} else {
$csvname = "External Assessment by " . preg_replace('/[^\w\-]/', '_', $rows["name"]) . " for ". preg_replace('/[^\w\-]/', '_', $rows["for_candidate_name"]) .".csv" ;
}
// Open a CSV file for writing to a temporary location
//$csvFileName = "output.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", "Rating"]);
// 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;
}
}
?>