<?php

/*Database Connection*/
include("dbconnection.php");

error_reporting(E_ALL);
set_time_limit(0);
date_default_timezone_set('Australia/Victoria');

/*include PhpSpreadsheet library*/
require 'PhpSpreadsheet/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$task = '';
if (isset($_POST['action'])) {
	$task = $_POST['action'];
} else if (isset($_GET['action'])) {
	$task = $_GET['action'];
}

switch($task) {
	
	case 'InsertData' :
		InsertData($pdo);
		break;
		
	default :
		echo "{failure:true}";
		break;
}

function InsertData($pdo) {

	/*Import File Directory*/
	$FileDir = "excel-import/";

	/*Import File Name*/
	$FileName = $FileDir.$_POST['FileName'];

	/*Import File Load*/
	$spreadsheet = IOFactory::load($FileName);

	/*Set Active Sheet Index*/
	$spreadsheet -> setActiveSheetIndex(0);
	
	/*Row and Column starting Index*/
	//$maxCell = $spreadsheet -> getActiveSheet() -> getHighestRowAndColumn();
	//$DataList = $spreadsheet -> getActiveSheet() -> rangeToArray('A2:' . $maxCell['column'] . $maxCell['row']);
	$DataList = $spreadsheet -> getActiveSheet() -> rangeToArray('A2:L15900');

	/*Row count*/
	$TotalRow = count($DataList);
	
	if($TotalRow>0){
		
		try {

			$pdo->beginTransaction();
			
			// Empty the table before inserting new data
            $pdo->exec("DELETE FROM certificates_imported");
			
			/*Foreach Loop*/
			foreach ($DataList as $key => $row) {
				
				/*Excel Column Index*/
				$certificate_number = $row[0];
				$course_attendee = replaceComma($row[1]);
				//$expiry_date = date('Y-m-d',strtotime($row[9]));
				$expiry_date = $row[9];
				$course_name = replaceComma($row[5]);
				$student_id = replaceComma($row[2]);
				
				/*Insert/Update sql*/
				//$query = "INSERT INTO certificates_imported(certificate_number, course_attendee, student_id, course_name, expiry_date) VALUES ('".$certificate_number."', '".$course_attendee."', '".$student_id."', '".$course_name."', '".$expiry_date."');";
				//$pdo->query($query);
				
				/* Safer way to execute query */
				$sql = "INSERT INTO certificates_imported (certificate_number, course_attendee, student_id, course_name, expiry_date) VALUES (:certificate_number, :course_attendee, :student_id, :course_name, :expiry_date)";
                
                $stmt = $pdo->prepare($sql);
                
                $stmt->bindParam(':certificate_number', $certificate_number);
                $stmt->bindParam(':course_attendee', $course_attendee);
                $stmt->bindParam(':student_id', $student_id);
                $stmt->bindParam(':course_name', $course_name);
                $stmt->bindParam(':expiry_date', $expiry_date);
                
                $stmt->execute();

			}
			
			$pdo->commit();	
			
			/*Success Message*/
			$msgList["msgType"] = 'success';
			$msgList['msg'] = '<span class="done">The file uploaded successfully. </span>See the result: <a target="_blank" href="ps-excel-imported-data-list.php">click here</a>';

		} catch (Exception $e) {
			
			$pdo->rollBack();
			
			/*Error Message*/
			$msgList["msgType"] = 'error';
			$msgList['msg'] = '<span class="red">'.$e -> getMessage().'</span>';
		}
		
	}else{
		
		/*Error Message*/
		$msgList["msgType"] = 'error';
		$msgList['msg'] = '<span class="red">There are no data in your Excel File. Please enter data in your Excel File.</span>';
	}
	
	echo json_encode($msgList);
}

/*Comma Replace Function */
function replaceComma($value) {

    if ($value != '0'){
        return (is_null($value) || empty($value) ? "NULL" : str_replace(',', '', $value));
    }else{
        return $value;
	}
}

?>