<?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> </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> </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>
Translate English to BM = PROPER(GOOGLETRANSLATE(A2, "en", "ms")) <br>
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>