File "getexceldata.php"

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

<?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(); 

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