File "getalldata.php"

Full Path: /home/humancap/cl.humancap.com.my/assessment/cl-admin/getalldata.php
File size: 7.26 KB
MIME-type: text/x-php
Charset: utf-8

<?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;
    }
 }      
?>