File "uploader.php"

Full Path: /home/humancap/cl.humancap.com.my/assessment/cl-admin/uploader.php
File size: 12.03 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);
}

 //check for session
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_FILES["csv_file"])) {
   $csvFile = $_FILES["csv_file"]["tmp_name"];

  echo "<br>Upload Process Starting....<br><br>";
  //echo $csvFile;

  // Read CSV file
    $csvData = array_map('str_getcsv', file($csvFile));

    // Assuming the first row contains headers
    $headers = array_map('trim', array_shift($csvData));

    $count = 1;

    foreach ($csvData as $row) {
      echo "<br>".$count. ". ";
        $competencyName = $row[array_search('Competency', $headers)];
        $competencyNama = $row[array_search('Kompetensi', $headers)];
        $behavioralIndicatorName = $row[array_search('Behavioral Indicator', $headers)];
        $behavioralIndicatorNama = $row[array_search('Penunjuk Tingkah Laku', $headers)];

        // Check if Competency exists
        $competencyId = getCompetencyId($competencyName, $competencyNama, $conn);

        if (!$competencyId) {
            // Competency doesn't exist, insert into competency table
            $competencyId = insertCompetency($competencyName, $competencyNama, $conn);
        } else {
          echo "<span style='color: red;'>The Competency $competencyName | $competencyNama exist!</span><br>";
        }

      // Check if Behavioral Indicator exists
      $behavioralIndicatorExists = checkBehavioralIndicatorExists($behavioralIndicatorName, $behavioralIndicatorNama, $conn);

      if (!$behavioralIndicatorExists) {
          // Behavioral Indicator doesn't exist, insert into behavioral_indicator table
          insertBehavioralIndicator($behavioralIndicatorName, $behavioralIndicatorNama, $competencyId, $conn);
          
      } else {
          // Behavioral Indicator already exists, you may handle this case as needed
          echo "<span style='color: red;'>The Behavioral Indicator $behavioralIndicatorName | $behavioralIndicatorNama exist!</span><br>";
      }
      $count = $count + 1;
    }

    echo "<br>Upload process has ended! <br>";
    echo "<br><a href='" . $_SERVER['HTTP_REFERER'] . "'>Click here to go back!</a><br>";   
  exit();

} else {

    // GET PROJECT DATA
    $sql = "SELECT name, nama, username FROM project where id = ".$_SESSION["project_id"];
    $project_data = $conn->query($sql);
    $row = $project_data->fetch_assoc();   

    $projectname = $row["name"];
    $projectnama = $row["nama"];
    $projectusername = $row["username"];

    // GET BIs DATA
    $sql2 = "SELECT bi.id, bi.name, bi.nama, bi.competency_id, c.name as competency, c.nama as kompetensi FROM behavioral_indicator as bi 
             INNER JOIN competency as c ON bi.competency_id = c.id ORDER BY competency asc";
    $bi_data = $conn->query($sql2);


    // Get the current URL
    $fullURL = "https://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";

    // Parse the URL
    $parsedURL = parse_url($fullURL);

    // Rebuild the URL without the query string
    $finalURL = $parsedURL['scheme'] . '://' . $parsedURL['host'] . $parsedURL['path'];

    //echo "Current URL without query string: " . $finalURL;
}

function getCompetencyId($competencyName, $competencyNama, $conn) {

    $competencyName = mysqli_real_escape_string($conn, $competencyName);
    $competencyNama = mysqli_real_escape_string($conn, $competencyNama);

    $query = "SELECT id FROM competency WHERE name = '$competencyName' OR nama = '$competencyNama'";
    $result = $conn->query($query);
 
    if ($result && $result->num_rows > 0) {
        // Competency exists, fetch the id
        $row = $result->fetch_assoc();
        return $row['id'];
    } else {
        return false;
    }
}

function insertCompetency($competencyName, $competencyNama, $conn) {
    $competencyName = mysqli_real_escape_string($conn, $competencyName);
    $competencyNama = mysqli_real_escape_string($conn, $competencyNama);

    $query = "INSERT INTO competency (name, nama, type_id, field_id) VALUES ('$competencyName', '$competencyNama', 1 , 3)";
    if ($conn->query($query) === TRUE) {
        // If the query executed successfully, return the id of the inserted competency
        echo "<span style='color: green;'>The Competency $competencyName | $competencyNama was inserted successfully!</span><br>";
        return $conn->insert_id;
    } else {
        // If an error occurred during insertion, you might want to handle it accordingly
        echo "Error: " . $query . "<br>" . $conn->error;
        return false;
    }
}

function insertBehavioralIndicator($behavioralIndicatorName, $behavioralIndicatorNama, $competencyId, $conn) {

    $behavioralIndicatorName = mysqli_real_escape_string($conn, $behavioralIndicatorName);
    $behavioralIndicatorNama = mysqli_real_escape_string($conn, $behavioralIndicatorNama);

    $query = "INSERT INTO behavioral_indicator (name, nama, competency_id) VALUES ('$behavioralIndicatorName', '$behavioralIndicatorNama', $competencyId)";
    
    if ($conn->query($query) === TRUE) {
        // If the query executed successfully, you may perform any additional actions if needed
        echo "<span style='color: green;'>The Behavioral Indicator $behavioralIndicatorName | $behavioralIndicatorNama was inserted successfully!</span><br>";
    } else {
        // If an error occurred during insertion, you might want to handle it accordingly
        echo "Error: " . $query . "<br>" . $conn->error;
    }
}

function checkBehavioralIndicatorExists($behavioralIndicatorName, $behavioralIndicatorNama, $conn) {

    $behavioralIndicatorName = mysqli_real_escape_string($conn, $behavioralIndicatorName);
    $behavioralIndicatorNama = mysqli_real_escape_string($conn, $behavioralIndicatorNama);

    $query = "SELECT id FROM behavioral_indicator WHERE name = '$behavioralIndicatorName' OR nama = '$behavioralIndicatorNama'";
    $result = $conn->query($query);
 
    return ($result && $result->num_rows > 0);
}

?>


<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta content="width=device-width, initial-scale=1.0" name="viewport">

  <title>Add Position</title>
  <meta content="" name="description">
  <meta content="" name="keywords">

  <!-- Favicons -->
  <link href="assets/img/favicon.png" rel="icon">
  <link href="assets/img/apple-touch-icon.png" rel="apple-touch-icon">

  <!-- Google Fonts -->
  <link href="https://fonts.gstatic.com" rel="preconnect">
  <link href="https://fonts.googleapis.com/css?family=Open+Sans:300,300i,400,400i,600,600i,700,700i|Nunito:300,300i,400,400i,600,600i,700,700i|Poppins:300,300i,400,400i,500,500i,600,600i,700,700i" rel="stylesheet">
  
  <!-- Vendor CSS Files -->
 
  <link href="assets/vendor/bootstrap-icons/bootstrap-icons.css" rel="stylesheet">
  <link href="assets/vendor/boxicons/css/boxicons.min.css" rel="stylesheet">
  <link href="assets/vendor/quill/quill.snow.css" rel="stylesheet">
  <link href="assets/vendor/quill/quill.bubble.css" rel="stylesheet">
  <link href="assets/vendor/remixicon/remixicon.css" rel="stylesheet">
  <link href="assets/vendor/simple-datatables/style.css" rel="stylesheet">

  <!-- Styles -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/select2-bootstrap-5-theme@1.3.0/dist/select2-bootstrap-5-theme.min.css" />
<!-- Or for RTL support -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/select2-bootstrap-5-theme@1.3.0/dist/select2-bootstrap-5-theme.rtl.min.css" />

<!-- Scripts -->
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.0/dist/jquery.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>


  <!-- Template Main CSS File -->
  <link href="assets/css/style.css" rel="stylesheet">
</head>

<body>
  <!-- Initialize the Bootstrap SelectPicker -->
  <script>
    $(document).ready(function() {
          $('.js-example-basic-multiple').select2();
    });
  </script>

  <?php require_once "../include/header.php"; ?>
  <?php require_once "../include/sidebar.php"; ?>

  <main id="main" class="main">

    
    <div class="pagetitle">
      <h1>Competency & Behavioral Indicators Uploader</h1>
      <!--<nav>
        <ol class="breadcrumb">
          <li class="breadcrumb-item"><a href="index.html">Position List</a></li>
          <li class="breadcrumb-item active">Add</li>
        </ol>
      </nav>-->
    </div>

    <section class="section">
      <div class="row">
        <div class="col-lg-12">
          <div class="card">
            <div class="card-body">
              <p>&nbsp;</p>
              <!--<h5 class="card-title">Multi Columns Form</h5>-->
                        <form method="post" enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF'];?>">
              <!-- Multi Columns Form -->
                <div class="col-md-10">
                  <h2>Uploader</h2>

              <label for="csv_file">Select CSV File:</label>
              <input type="file" name="csv_file" accept=".csv" required>
          
                </div>
                <p>&nbsp;</p>
                <div class="text-left">
                  <button type="submit" class="btn btn-primary">Click here to begin uploading</button>
                </div>   
                </form>
                <hr>
                <div class="col-md-10">
                  <h3>How to upload?</h3>

              Step 1: Prepare .csv upload file (Download template <a href='template/Competency and Behavioral Indicators Upload - CSV Template.csv' target="_blank">here</a>) <br>
              Step 2: Click on choose file and select your .csv file <br>
              Step 3: Click on the button "Click here to begin uploading" file <br>
          
                </div>
                <hr>
                <div class="col-md-10">
                  <h3>Frequently Asked Questions (FAQ)</h3>
                    
              <b>Q:</b> Must the .csv file be filled up with data for both languages? <br>
              <b>A:</b> Yes! <br><br>

              <b>Q:</b> We only have data for 1 language. Is there a way to do mass translations? <br>
              <b>A:</b> Yes! Create a Google Spreadsheet and apply the formulas (below).<br> Once the formula is applied for one cell, drag the cell to apply on all rows. <br>
                 &nbsp;&nbsp;&nbsp;Translate English to BM   = PROPER(GOOGLETRANSLATE(A2, "en", "ms")) <br>
                 &nbsp;&nbsp;&nbsp;Translate BM to English   = PROPER(GOOGLETRANSLATE(A2, "ms", "en")) <br><br>
          
                </div>                
            </div>
          </div>
        </div>
        </div>
      </div>
    </section>
  </main><!-- End #main -->

  <!-- ======= Footer ======= -->
  <footer id="footer" class="footer">

  </footer><!-- End Footer -->

  <a href="#" class="back-to-top d-flex align-items-center justify-content-center"><i class="bi bi-arrow-up-short"></i></a>

  <!-- Vendor JS Files -->
  <script src="assets/vendor/apexcharts/apexcharts.min.js"></script>
  <script src="assets/vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
  <script src="assets/vendor/chart.js/chart.umd.js"></script>
  <script src="assets/vendor/echarts/echarts.min.js"></script>
  <script src="assets/vendor/quill/quill.min.js"></script>
  <script src="assets/vendor/simple-datatables/simple-datatables.js"></script>
  <script src="assets/vendor/tinymce/tinymce.min.js"></script>
  <script src="assets/vendor/php-email-form/validate.js"></script>

  <!-- Template Main JS File -->
  <script src="assets/js/main.js"></script>

</body>
</html>