	<?php session_start();
	ini_set('display_errors', 1);
	ini_set('display_startup_errors', 1);
	error_reporting(E_ALL);
	$_SESSION['loggedinuser'];
	$loggedinuser=$_SESSION['loggedinuser'];
	//check if the user is logged in
	if (isset($_SESSION['loggedin']) && $_SESSION['loggedin'] == true) {
	} else {
	header('Location: index.php');
	}
	date_default_timezone_set("Africa/Nairobi");
	?>
	<!DOCTYPE html>
	<html lang="en">
	<head>
	<meta charset="utf-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<meta name="description" content="">
	<meta name="author" content="">
	<link rel="icon" href="../images/favicon.png">

	<title>Deadly Force::People killed by the police in Kenya</title>
	<!-- Bootstrap core CSS -->
	<link href="../css/bootstrap.css" rel="stylesheet">
	<link href="../css/zebra_datepicker_metallic.css" rel="stylesheet">
	<!-- Bootstrap theme -->
	<link href="../css/bootswatch_min.css" rel="stylesheet">

	<!-- Just for debugging purposes. Don't actually copy these 2 lines! -->
	<!--[if lt IE 9]><script src="../../assets/js/ie8-responsive-file-warning.js"></script><![endif]-->
	<script src="../../assets/js/ie-emulation-modes-warning.js"></script>

	<!-- HTML5 shim and Respond.js IE8 support of HTML5 elements and media queries -->
	<!--[if lt IE 9]>
	<script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
	<script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
	<![endif]-->
	

	</head>

	<body role="document">
	<!-- Fixed navbar -->
	<div class="navbar navbar-default navbar-fixed-top" role="navigation">
	<div class="container">
	<div class="navbar-header">
	<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target=".navbar-collapse">
	<span class="sr-only">Toggle navigation</span>
	<span class="icon-bar"></span>
	<span class="icon-bar"></span>
	<span class="icon-bar"></span>
	</button>
	<a class="navbar-brand" href="index.php">
	<img src="../images/newsplex_logo.png" width="145" height="70" alt="nation newsplex"/>
	<div class="header-slogan">
	<span class="big-slogan">Deadly force</span><br>
	<span class="slogan">People killed by the police in Kenya</span>
	</div>
	</a>
	</div>
	<div class="navbar-collapse collapse">
	<ul class="nav navbar-nav">
	<li><a href="../tip.php">Submit tip</a></li>
	<li><a href="../index.php#database">Database</a></li>
	<li><a href="../about.php">About</a></li>
	<li><a href="../articles.php">Read articles</a></li>
	</ul>
	</li>
	</ul>
	</div><!--/.nav-collapse -->
	</div>
	</div>
	<div class="container main">

	<!-- Containers
	================================================== -->
	<div class="bs-docs-section">

	<div class="row">
	<div class="col-lg-12">
	<p><h1>Import police killing victims data &raquo;</h1></p>
	<div class="bs-component">
	<div class="jumbotron">
	<div id="form">


	<?php

	//Upload File
	if (isset($_POST['submit'])) {
	
	//get selected year and delete all records from db
	$selectedyear=trim($_POST['selectedyear']);
	$servername = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "newsplex_db";
	
	//try {
	//$conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbuser, $dbpass);
	// set the PDO error mode to exception
	//$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	//delete records for that year
	//$sthandler = $conn->prepare("delete from police_killings where year(date_of_incident) = :selectedyear");
	//$sthandler->bindParam(':selectedyear', $selectedyear);
	//$sthandler->execute();
	//}
	//catch(PDOException $e)
	//{
	//echo "error" . "<br>" . $e->getMessage();
	//}

	//$conn = null;

	//upload the file to tmp directory for conversion
	$main_dir = "http://".$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF']);
	$upload_dir = $_SERVER['DOCUMENT_ROOT'] .  dirname($_SERVER['PHP_SELF']).'/tmp';
	$upload_url = '/';

	$temp_name = $_FILES['filename']['tmp_name'];
	$file_name = $_FILES['filename']['name'];

	$file_name=strtolower($file_name);

	$file_name = preg_replace('/\s+/', '_', $file_name);	

	$file_path = $upload_dir.$upload_url.$file_name;
	$mydir = '/'.'tmp/'.$file_name;
	//echo $mydir."</br>";
	$link=$main_dir.$mydir;

	if(($_FILES["filename"]["type"] != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
	&& ($_FILES["filename"]["type"] != "application/vnd.ms-excel"))
	{
	echo "<div class=\"alert alert-danger\">Files must be either be in Excel format!</div>";
	exit;
	}

	if(($_FILES["filename"]["size"]/1024 > 8000))
	{
	echo "<div class=\"alert alert-danger\">File size must not exceeed 7MB!</div>";
	exit;
	}

	if(move_uploaded_file($temp_name, $file_path))
	{
	echo "<div class=\"alert alert-success\">File upload successful !</div>"."<br>";
	echo "Link: "."<a href=\"#\" onclick=\"document.getElementById('txtpath').value='$link';\">$link</a>"."<br/>";
	echo "File: " . $file_name . "<br>";
	echo "Type: " . $_FILES["filename"]["type"] . "<br>";
	echo "Size: " . ($_FILES["filename"]["size"] / 1024) . " kB<br>";
    }
	//end of file upload

	//convert to csv
	require_once('../libraries/phpexcel/Classes/PHPExcel.php');

	function convertXLStoCSV($infile,$outfile)
	{
	$fileType = PHPExcel_IOFactory::identify($infile);
	$objReader = PHPExcel_IOFactory::createReader($fileType);

	$objReader->setReadDataOnly(false);  
	$objPHPExcel = $objReader->load($infile);   

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
	$objWriter->save($outfile);
	}

	$filetoconvert=$mydir ='tmp/'.$file_name;
	$newfilename=substr($file_name,0,strrpos($file_name,'.'));
	$fileoutput='tmp/'.$newfilename.'.csv';
	//Usage:
	convertXLStoCSV($filetoconvert,$fileoutput);


	if (is_uploaded_file($link)) {
	echo "<h1>" . "File: ". $link ." uploaded successfully." . "</h1>";
	}

	//Import uploaded file to Database
	$handle = fopen($fileoutput, "r");

	$count = 0;
	while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
	$count++; 
	if ($count<2) { continue; }
	$date=$data[5];
	$incidentdate = new DateTime($date);
	$incident_date = date_format($incidentdate, 'Y-m-d');
	$savedate=trim(date("Y-m-d H:i:s"));
	$date_modified=trim(date("Y-m-d H:i:s"));
	$modified_by=$loggedinuser;
	
	//convert special characters from the spreadsheet
	$data0=htmlspecialchars($data[0]);
	$data1=htmlspecialchars($data[1]);
	$data2=htmlspecialchars($data[2]);
	$data3=htmlspecialchars($data[3]);
	$data4=htmlspecialchars($data[4]);
	$data5=htmlspecialchars($data[5]);
	$data6=htmlspecialchars($data[6]);
	$data7=htmlspecialchars($data[7]);
	$data8=htmlspecialchars($data[8]);
	$data9=htmlspecialchars($data[9]);
	$data10=htmlspecialchars($data[10]);
	$data11=htmlspecialchars($data[11]);
	$data12=htmlspecialchars($data[12]);
	$data13=htmlspecialchars($data[13]);
	
	$servername = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "deadly_force";
	
	try{
	$conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbuser, $dbpass,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
	// set the PDO error mode to exception
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	
	//try to insert using pdo
	$sthandler = $conn->prepare("insert into police_killings (victim_names,victim_gender,victim_age,victim_occupation,date_of_incident,incident_county_or_town,officer_involved,victim_armed_unarmed,police_weapon,entry_in_occurence_book,status_of_case,incident_summary,source,keywords,entered_by,date_entered,date_modified,modified_by) values (:names,:gender,:age,:occupation,:incident_date,:incident_town,:officer_involved,:victim_armed,:police_weapon,:occurence_book,:case_status,:summary,:source,:keywords,:loggedinuser,:savedate,:date_modified,:modified_by)");
	$sthandler->bindParam(':names', $data1);
	$sthandler->bindParam(':gender', $data3);
	$sthandler->bindParam(':age', $data2);
	$sthandler->bindParam(':occupation', $data4);
	$sthandler->bindParam(':incident_date', $incident_date);
	$sthandler->bindParam(':incident_town', $data0);
	$sthandler->bindParam(':officer_involved', $data6);
	$sthandler->bindParam(':victim_armed', $data7);
	$sthandler->bindParam(':police_weapon', $data8);
	$sthandler->bindParam(':occurence_book', $data9);
	$sthandler->bindParam(':case_status', $data10);
	$sthandler->bindParam(':summary', $data11);
	$sthandler->bindParam(':source', $data12);
	$sthandler->bindParam(':keywords', $data13);
	$sthandler->bindParam(':loggedinuser', $loggedinuser);
	$sthandler->bindParam(':savedate', $savedate);
	$sthandler->bindParam(':date_modified', $date_modified);
	$sthandler->bindParam(':modified_by', $modified_by);
	$sthandler->execute();

	
	}
	catch(PDOException $e)
	{
	echo "error" . "<br>" . $e->getMessage();
	}
	
    $conn = null;
	}
    echo "<div class=\"bs-component\">";
	echo "<div class=\"alert alert-dismissable alert-success\">";
	echo "<button type=\"button\" class=\"close\" data-dismiss=\"alert\">&times;</button>";
	echo "<strong>Victim details successfully added!.</strong>";
	echo "</div>";
	echo "</div>";
	fclose($handle);
	//delete the temporary file
	$filetodelete='tmp/'.$file_name;
	unlink($filetodelete);
	unlink($fileoutput);
	//print "Import done";

	//view upload form
	}else {

	print "<h3>Upload victims data excel sheet by browsing to file and clicking on Upload</h3><br />\n";

	print "<form enctype='multipart/form-data' action='' method='post'>";
	
	?>
    <div class="col-lg-2">
	<select class="form-control" name="selectedyear" id="selectedyear" required>
    <?php
        $currentYear = (int)date('Y');
        for ($i = $currentYear; $i >= 1900; $i--) {
            echo '<option value="' . $i . '"' . ($i == $currentYear ? ' selected="selected"' : '') . '>' . $i . '</option>';
        }
    ?>
    </select>
	</div>
	
	<?php
	print "<input size='50' type='file' name='filename' required>&nbsp;&nbsp;&nbsp;";

	print "<input type='submit' name='submit' value='Upload' class='btn btn-primary'>";
	
	print "<p>";
	
	print "<div class='row'>";
	
	print "<div class='form-group'>";
	
	print "<div class='col-lg-12'>";
	
	print "&nbsp;&nbsp;&nbsp;&nbsp;<a class= 'btn btn-warning' href='logout.php'>Logout</a>";
	
	print "</div>";
	
	print "</div>";
	
	print "</div>";
	
	print "</p>";
	
	
	print "</form>";

	}

	?>
	</div>
	</div>

	</div>
	</div>
	</div> 
	</div> <!-- /container -->

	<!-- Bootstrap core JavaScript
	================================================== -->
	<!-- Placed at the end of the document so the pages load faster -->
	<script src="../js/jquery.min.js"></script>
	<script src="../js/bootstrap.min.js"></script>
	<script src="../js/bootstrapValidator.js"></script>
	<script src="../js/bootswatch.js"></script>
	<script src="../js/docs.min.js"></script>
	<script src="../js/zebra_datepicker.js"></script>
	<script>
	$('#incident_date').Zebra_DatePicker();
	</script>
	<!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
	<script src="../js/ie10-viewport-bug-workaround.js"></script>
	<!--file input-->
	<script src="../js/bootstrap.file-input.js"></script>
	<script>
	$('input[type=file]').bootstrapFileInput();
	$('.file-inputs').bootstrapFileInput();
	</script>
	</body>
	</html>