<?php
ini_set('display_errors', 'On');
error_reporting(E_ERROR);

/*The following code creates four csv files from cloud db.*/

session_start();
$email = $_SESSION['email'];

// include db config class
require_once __DIR__ . '/db_config.php';
	
// connecting to db
$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);
mysqli_set_charset($mysqli, "utf8");

$success = 0;

//get user_id from email
if($stmt = $mysqli->prepare("SELECT USER_ID FROM USER_ID WHERE EMAIL = ?")){
	$stmt->bind_param("s", $email);	
	$stmt->execute();
	$stmt->bind_result($temp_user_id);
	while($stmt->fetch()){
		$user_id = $temp_user_id;
	}
	
	if(isset($user_id) && $user_id>0){
		//create Log.csv
		$sql = "SELECT _ID 'ID', VEHID 'VEHICLE', ODO, QTY, PARTIAL_FILL, MISSED_FILL, COST 'TOTAL COST', DIST 'DISTANCE', CONSUMPTION, OCTANE, FUEL_BRAND 'FUEL BRAND', FILLING_STATION 'FILLING STATION', NOTES, DAY, MONTH, YEAR, RECEIPT 'RECEIPT PATH', TYPE, SERVICE_TYPE FROM LOG_TABLE WHERE TYPE IN (0, 1, 2) AND USER_ID = ".$user_id;
		$result = mysqli_query($mysqli, $sql);
		
		if($result){
			$fp = fopen('../CSVFiles/'.$email.'_Log.csv', 'w');
			
			$row = mysqli_fetch_assoc($result);
			$fields = array_keys($row);
			fputcsv($fp, $fields);
			
			do{
				fputcsv($fp, $row);
			}
			while($row = mysqli_fetch_assoc($result));						  
			
			fclose($fp);	
			
			//create Trip_Log.csv
			$sql = "SELECT _ID 'ID', VEHID 'VEHICLE', ODO, QTY, PARTIAL_FILL, MISSED_FILL, COST 'TOTAL COST', DIST 'DISTANCE', CONSUMPTION, OCTANE, FUEL_BRAND 'FUEL BRAND', FILLING_STATION 'FILLING STATION', NOTES, DAY, MONTH, YEAR, RECEIPT 'RECEIPT PATH', TYPE, SERVICE_TYPE FROM LOG_TABLE WHERE TYPE = 3 AND USER_ID = ".$user_id;
			$result = mysqli_query($mysqli, $sql);
			
			$fp = fopen('../CSVFiles/'.$email.'_Trip_Log.csv', 'w');
			
			$row = mysqli_fetch_assoc($result);
			$fields = array_keys($row);
			fputcsv($fp, $fields);
			
			do{
				fputcsv($fp, $row);
			}
			while($row = mysqli_fetch_assoc($result));
			
			fclose($fp);			
			
			//create Vehicle.csv
			$sql = "SELECT _ID 'ID', MAKE, MODEL, LIC 'LICENCE', VEHID 'VEHICLE', YEAR, VIN, INSURANCE_NO 'INSURANCE', NOTES, PICTURE 'IMAGE PATH', CUSTOM_SPECIFICATIONS 'CUSTOM SPECS' FROM VEH_TABLE WHERE USER_ID = ".$user_id;
			$result = mysqli_query($mysqli, $sql);
			
			if($result){
				$fp = fopen('../CSVFiles/'.$email.'_Vehicles.csv', 'w');
			
				$row = mysqli_fetch_assoc($result);
				$fields = array_keys($row);
				fputcsv($fp, $fields);
				
				do{
					fputcsv($fp, $row);
				}
				while($row = mysqli_fetch_assoc($result));
				
				fclose($fp);
				
				//bundle them in a zip file
				$zip = new ZipArchive();
				$zip_file_name = $email.'_Backup.zip';
				$file_path = '../CSVFiles/';
				$log_csv = $email.'_Log.csv';
				$trip_log_csv = $email.'_Trip_Log.csv';
				$veh_log_csv = $email.'_Vehicles.csv';
				
				if(file_exists($file_path.$zip_file_name)) {
        				unlink($file_path.$zip_file_name); 
				}
				
				if ($zip->open($file_path.$zip_file_name, ZIPARCHIVE::CREATE) != TRUE) {
        				die ("Could not open zip file");
				}
				
				$zip->addFile($file_path.$log_csv, $log_csv);
				$zip->addFile($file_path.$trip_log_csv, $trip_log_csv);
				$zip->addFile($file_path.$veh_log_csv, $veh_log_csv);

				// close and save archive
				$zip->close(); 
				
				//then send the headers to force download the zip file
				while (ob_get_level()) {
            				ob_end_clean();
        			}
       				ob_start();
				header("Content-type: application/zip"); 
				header("Content-Disposition: attachment; filename=$zip_file_name"); 
				header("Content-length: " . filesize($zip_file_name));
				header("Pragma: no-cache"); 
				header("Expires: 0"); 
				ob_flush();
       				ob_clean();
				readfile($file_path.$zip_file_name);

				//delete files
				if(file_exists($file_path.$log_csv)) {
        				unlink($file_path.$log_csv); 
				}
				
				if(file_exists($file_path.$trip_log_csv)) {
        				unlink($file_path.$trip_log_csv); 
				}
				
				if(file_exists($file_path.$veh_log_csv)) {
        				unlink($file_path.$veh_log_csv); 
				}
				
				if(file_exists($file_path.$zip_file_name)) {
        				unlink($file_path.$zip_file_name); 
				}
				
				//flag as success
				$success = 1;
			}
		}
		else{
			$success = 0;
		}		
	}
	else{
		$success = 0;
	}
	
	// Close the prepared statement.
	if($stmt != null){
		$stmt->close(); 
	} 
}
else{
	$success = 0;
}

mysqli_close($mysqli);

if($success == 0){
	echo "Failed to export data";
}

?>