<?php
// Initialize the session
session_start();

// Check if the user is logged in, if not then redirect him to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
header("location: login.php");
exit;
}

//code to import xlsx
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

require_once 'DataSource.php';
$db = new DataSource();
$conn = $db->getConnection();
require_once ('vendor/autoload.php');

if (isset($_POST["import"])) {

$allowedFileType = [
'application/vnd.ms-excel',
'text/xls',
'text/xlsx',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
];

if (in_array($_FILES["file"]["type"], $allowedFileType)) {

$targetPath = 'uploads/' . date('d-m-yy').'-'.$_FILES['file']['name'];
move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

$Reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

$spreadSheet = $Reader->load($targetPath);
$excelSheet = $spreadSheet->getActiveSheet();
$spreadSheetAry = $excelSheet->toArray();
$sheetCount = count($spreadSheetAry);

for ($i = 1; $i <= $sheetCount; $i ++) {
$region = "";
if (isset($spreadSheetAry[$i][0])) {
$region = mysqli_real_escape_string($conn, $spreadSheetAry[$i][0]);
}
$registration_date = "";
if (isset($spreadSheetAry[$i][1])) {
$registration_date = mysqli_real_escape_string($conn, $spreadSheetAry[$i][1]);
}
$total = "";
if (isset($spreadSheetAry[$i][2])) {
$total = mysqli_real_escape_string($conn, $spreadSheetAry[$i][2]);
}
$male = "";
if (isset($spreadSheetAry[$i][3])) {
$male = mysqli_real_escape_string($conn, $spreadSheetAry[$i][3]);
}
$female = "";
if (isset($spreadSheetAry[$i][4])) {
$female = mysqli_real_escape_string($conn, $spreadSheetAry[$i][4]);
}
$minors = "";
if (isset($spreadSheetAry[$i][5])) {
$minors = mysqli_real_escape_string($conn, $spreadSheetAry[$i][5]);
}
$voluntaryreturns = "";
if (isset($spreadSheetAry[$i][6])) {
$voluntaryreturns = mysqli_real_escape_string($conn, $spreadSheetAry[$i][6]);
}
$involuntaryreturns = "";
if (isset($spreadSheetAry[$i][7])) {
$involuntaryreturns = mysqli_real_escape_string($conn, $spreadSheetAry[$i][7]);
}

if (! empty($region) || ! empty($registration_date) || ! empty($total) || ! empty($male) || ! empty($female) || ! empty($minors) || ! empty($voluntaryreturns) || ! empty($involuntaryreturns)) {
$query = "insert into ksa(region,registration_date,total,male,female,minors,voluntaryreturns,involuntaryreturns)values('".$region."','".$registration_date."','".$total."','".$male."','".$female."','".$minors."','".$voluntaryreturns."','".$voluntaryreturns."') on duplicate key update total='".$total."',male='".$male."',female='".$female."',voluntaryreturns='".$voluntaryreturns."',involuntaryreturns='".$involuntaryreturns."'";
$result = mysqli_query($conn, $query);
if (!empty($result)) {
$type = "success";
$message = "Excel Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing Excel Data";//$conn -> error;
}
}
}
} else {
$type = "error";
$message = "Invalid File Type. Upload Excel File.";
}
}
//code to import xlsx
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Dashboard::Areas Of Origin</title>
<link rel="stylesheet" href="css/bootstrap.css">
<link rel="stylesheet" href="css/dataTables.bootstrap4.min.css">
<link href="css/bootstrap-datepicker.css" type="text/css" rel="stylesheet">
<!-- Font Awesome Icons -->
<link rel="stylesheet" href="fontawesome-free/css/all.min.css">
<link href="https://fonts.googleapis.com/css2?family=Roboto:ital,wght@0,100;0,300;0,400;0,500;0,700;0,900;1,100;1,300;1,400;1,700;1,900&display=swap" rel="stylesheet"> 
<link href="css/custom.css" rel="stylesheet"> 
</head>
<body>
<div class="container">
<div class="page-header">
<h3>Hi, <b><?php echo htmlspecialchars($_SESSION["username"]); ?></b>. Manage Areas Of Origin Data.</h3>
</div>

<p>
<div class="row">
<div class="col-md-12">
<button class="btn btn-info btn-sm"><a  href="#"><i class='fa fa-map-marker'></i> Home Map</a></button>
<button class="btn btn-info btn-sm"><a  href="mrcs.php"><i class='fa fa-home'></i> MRCs</a></button>
<button class="btn btn-info btn-sm"><a  href="ksa.php"><i class='fa fa-oil-can'></i> KSA</a></button>
<button class="btn btn-info btn-sm"><a  href="ethiopia.php"><i class='fa fa-flag'></i> Ethiopia</a></button>
<button class="btn btn-info btn-sm"><a  href="ksaduration.php"><i class='fa fa-clock'></i> Duration in KSA</a></button>
<button class="btn btn-info btn-sm"><a  href="originareas.php"><i class='fa fa-inbox'></i> Origin</a></button>
<button class="btn btn-info btn-sm"><a  href="hardships.php"><i class='fa fa-map'></i> Hardships</a></button>
</div>
</div>
</p>

<h4>Import Excel File </h4>
<div class="outer-container">
<form action="" method="post" name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">
<div>
<input type="file" name="file" id="file" accept=".xls,.xlsx" class="nicefileinput nice"/>
<button type="submit" id="submit" name="import" class="btn-submit">Import</button>
</div>
</form>
</div>
<div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>

<hr>

<button class="btn btn-primary btn-sm float-right">
<a class="addksarecord"  href="addksarecord.php">
<i class="fa fa-plus-circle"></i> 
Add Record
</a>
</button>
   
<table id="ksa" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<th>Entry</th>
<th>Region</th>
<th>Date</th>
<th>Total</th>
<th>Male</th>
<th>Female</th>
<th>Children</th>
<th style="width:10%">Voluntary R</th>
<th>Involuntary R</th>
<th>Action</th>
</tr>
</thead>
<tbody>

<?php
require('config.php');
$stmt = $pdo->prepare("select * from ksa");
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$id=$row['id'];
$region=$row['region'];
$registration_date = $row['registration_date'];
$total=$row['total'];
$male = $row['male'];
$female = $row['female'];
$minors=$row['minors'];
$voluntaryreturns=$row['voluntaryreturns'];
$involuntaryreturns=$row['involuntaryreturns'];
echo "<tr>";
echo "<td>";
echo $id;
echo "</td>";
echo "<td>";
echo $region;
echo "</td>";
echo "<td>";
echo $registration_date;
echo "</td>";
echo "<td>";
echo $total;
echo "</td>";
echo "<td>";
echo $male;
echo "</td>";
echo "<td>";
echo $female;
echo "</td>";
echo "<td>";
echo $minors;
echo "</td>";
echo "<td>";
echo $voluntaryreturns;
echo "</td>";
echo "<td>";
echo $involuntaryreturns;
echo "</td>";
echo "<td>";
echo "<button class='btn btn-primary btn-sm'>
<a class='editksarecord'  href='editksarecord.php?id=$id'>
<i class='far fa-edit'></i> 
Edit
</a>
</button>
<button class='btn btn-danger btn-sm' id='delete_'>
<span class='delete' data-id='$id'>
<i class='fas fa-trash-alt'></i>
Delete
</span>
</button>";
echo "</td>";
echo "</tr>";
}
$pdo=null;
?>

</tbody>
</table>
<hr>

<div class="footer">
<a href="logout.php" class="btn btn-danger">Sign Out </a>
</div><!--footer-->
</div><!--container-->
<script src="js/jquery-3.5.1.js"></script>
<!-- Bootstrap 4 -->
<script src="js/bootstrap.bundle.js"></script>
<script src="js/jquery.dataTables.min.js"></script>
<script src="js/dataTables.bootstrap4.min.js"></script>
<script src="js/bootbox.all.js"></script>
<script src="js/bootstrap-datepicker.js"></script>
<script src="js/jquery.nicefileinput.js"></script>
<script>
$(document).ready(function() {
$("input[type=file]").nicefileinput({
	label : 'File...'
});
$('#ksa').DataTable();
});
</script>
<script>
$(document).on("click", ".delete", function(e) {
var el = this;
var delete_id=($(this).attr('data-id'));
bootbox.confirm({
title: "Delete record?",
message: "Are you sure you want to delete?",
buttons: {
cancel: {
label: '<i class="fa fa-times"></i> Cancel',
className: 'btn btn-light',
},
confirm: {
label: '<i class="fa fa-check"></i> Confirm',
className: 'btn btn-success',
}
},
callback: function (result) {
if (result===true){
//code to delete here
$.ajax({
url: 'deleteksarecord.php', // url where to submit the request
type : "POST", // type of action POST || GET
dataType : 'json', // data type
data : { delete_id: delete_id },
success : function(response) {
if(response == 1){
// Remove row from HTML Table
$(el).closest('tr').css('background','tomato');
$(el).closest('tr').fadeOut(800,function(){
$(this).remove();
});
bootbox.alert("Record deleted!");
}
else{
bootbox.alert("Invalid ID!");
}
},
})
//end code to delete

}
//console.log('This was logged in the callback: ' + result);
}
});
});   
</script>
<!-- Modal -->
<div id="editksaModal" class="modal fade" role="dialog">
<div class="modal-dialog">
<!-- Modal content-->
<div class="modal-content">
</div>
</div>
</div>
<script>
$('#ksa').on('click', '.editksarecord', function (e) { //to prevent losing action on next page
	var tablerow=$(this).closest('tr').index();
	 localStorage.setItem("tablerow", tablerow);
      e.preventDefault();//prevent page going to href
      $('#editksaModal').modal('show').find('.modal-content').load($(this).attr('href'));
    });
</script>

<!-- Modal -->
<div id="addksaModal" class="modal fade" role="dialog">
<div class="modal-dialog">
<!-- Modal content-->
<div class="modal-content">
</div>
</div>
</div>
<script>
$('.addksarecord').on( "click", function(e) { //to prevent losing action on next page
      e.preventDefault();//prevent page going to href
      $('#addksaModal').modal('show').find('.modal-content').load($(this).attr('href'));
    });
</script>

</body>
</html>