Diss/05_Database/PHP/save_to_database.php

<?php
// higher max_time is required because of distance calculation
// 300 s = 5 minutes
ini_set('max_execution_time', 300);

// Current filename from script before
if(!empty($out_file_name)) {
	$file_name = $out_file_name;
} else {

	// For Debugging
	// gpsobdlog.de/save_to_database.php
	$file_name = "uploads/Log_20171208_060134.csv";
	// Log_20171128_163056
	// Log_20171206_181312
	// Log_20171207_063511
	// Log_20171207_174242
	// Log_20171208_060134
}

// Start connection to database;
try {
	$pdo = new PDO('mysql:host=localhost;dbname=observer', 'observer', '6BjUdh7n');
}
catch( PDOException $Exception ) {
	try {
		$pdo = new PDO('mysql:host=localhost;dbname=obd_data', 'root', '');
	}
	catch( PDOException $Exception ) {
		echo "Error in connection";
		exit;
	}
}

$file = fopen ($file_name, 'r');
// Erste Zeile mit Informationen
$line = fgetcsv($file, 500, ";", "\"") ;

$new_setup = array();
$new_setup['lat_start']     = $line[6];
$new_setup['long_start']    = $line[8];
$new_setup['alt_start']     = $line[10];
$new_setup['imei']          = $line[12];
$new_setup['vin']           = $line[14];
$new_setup['fuel_type']     = $line[16];
$new_setup['name_drv']      = $line[18];
$new_setup['age_drv']       = $line[20];
$new_setup['gender_drv']    = $line[22];
$new_setup['version']       = $line[24];
$new_setup['obd_logging']   = $line[26];

// set actual time for upload_time
date_default_timezone_set("Europe/Berlin"); 
$new_setup['upload_time']   = date("Y-m-d H:i:s");

$logging_start_time         = $line[28];
$datetime                   = new DateTime(date("Y-m-d H:i:s", ($logging_start_time/1000)));
$datetime->setTimezone(new DateTimeZone('Europe/Berlin'));
$new_setup['logging_start_time'] = $datetime->format("Y-m-d H:i:s");

echo "<br>";
print_r($line);
echo "<br>";
echo "Following setup will be inserted";
echo "<br>";
print_r($new_setup);
echo "<br>";

$statement = $pdo->prepare("INSERT INTO setup (version, imei, vin, logging_start_time, lat_start, long_start, alt_start, fuel_type, name_drv, age_drv, gender_drv, obd_logging, upload_time )VALUES (:version, :imei, :vin, :logging_start_time, :lat_start, :long_start, :alt_start, :fuel_type, :name_drv, :age_drv, :gender_drv, :obd_logging, :upload_time)");
$statement->execute($new_setup);

// important for obd_raw_data
$setup_id = $pdo->lastInsertId();
echo "New setup was inserted with the following id: $setup_id ";
echo "<br>";

// Jump to line 4
$line = fgetcsv($file, 500, ";", "\"");
$line = fgetcsv($file, 500, ";", "\"") ;
$line = fgetcsv($file, 500, ";", "\"") ;

// create array for rawdata
$new_rawdata = array();
// is constant for the whole dataset
$new_rawdata['setup_ID'] = $setup_id;

// for distance calculation
$speed_ms = array();
$delta_time = array();
$temp_time = 0;
$i = 0;

// last location
$lat_end    = -1.0;
$long_end   = -1.0;
$alt_end    = -1.0;

// write SQL statement
$statement = $pdo->prepare("INSERT INTO rawdata (setup_ID, time, time_system_clock, time_utc, latitude, longitude, altitude, gps_speed, x_accel, y_accel, z_accel, x_gyro, y_gyro, z_gyro, compass, number_satelites, accuracy, gps_bearing, speed, engine_load, engine_rpm, maf, accelerator_pedal, ambient_air_temp ) VALUES (:setup_ID, :time, :time_system_clock, :time_utc, :latitude, :longitude, :altitude, :gps_speed, :x_accel, :y_accel, :z_accel, :x_gyro, :y_gyro, :z_gyro, :compass, :number_satelites, :accuracy, :gps_bearing, :speed, :engine_load, :engine_rpm, :maf, :accelerator_pedal, :ambient_air_temp )");

function na_to_null(string $s) { return trim($s) === "NA" ? null : $s; }
// loop for iterating through lines of csv_file
while (($line = fgetcsv($file, 500, ";", "\"")) !== FALSE) {
	// last line is most of the time "userinterface: stop"
	if(count($line) !== 23) {
		break;
	}

	// for calculating distance
	if($new_setup['obd_logging'] == 1) {
		// use obd speed
		$speed_ms[] = $line[17] / 3.6;	// [km/h] to [m/s]
	} else {
		// use gps speed
		$speed_ms[] = $line[6]; // [m/s]
	}

	if($temp_time != 0) {
		// calculate diff-time and save previous time
		$current_time = $line[0] / 1000;	//[ms] to [seconds]
		$delta_time[] = $current_time - $temp_time;
		$temp_time = $current_time;
	} else {
		//firstround temp is not set
		$temp_time = $line[0] / 1000;	//[ms] to [seconds]
	}
	// fill array with csv-information
	$new_rawdata['time']                = $line[0];
	$new_rawdata['time_system_clock']   = $line[1];
	$new_rawdata['time_utc']            = $line[2];
	$new_rawdata['latitude']            = $line[3];
	$new_rawdata['longitude']           = $line[4];
	$new_rawdata['altitude']            = $line[5];
	$new_rawdata['gps_speed']           = $line[6];
	$new_rawdata['x_accel']             = $line[7];
	$new_rawdata['y_accel']             = $line[8];
	$new_rawdata['z_accel']             = $line[9];
	$new_rawdata['x_gyro']              = $line[10];
	$new_rawdata['y_gyro']              = $line[11];
	$new_rawdata['z_gyro']              = $line[12];
	$new_rawdata['compass']             = $line[13];
	$new_rawdata['number_satelites']    = $line[14];
	$new_rawdata['accuracy']            = $line[15];
	$new_rawdata['gps_bearing']         = $line[16];
	$new_rawdata['speed']               = $line[17];
	$new_rawdata['engine_load']         = na_to_null($line[18]);
	$new_rawdata['engine_rpm']          = na_to_null($line[19]);
	$new_rawdata['maf']                 = na_to_null($line[20]);
	$new_rawdata['accelerator_pedal']   = na_to_null($line[21]);
	$new_rawdata['ambient_air_temp']    = na_to_null($line[22]);

	// write into database
	$statement->execute($new_rawdata);
	// Only for row counting
	$i = $i +1;
	//remember last lat/long position
	if( $new_rawdata['latitude'] != 0 AND $new_rawdata['longitude'] != 0) {
		$lat_end = $new_rawdata['latitude'];
		$long_end = $new_rawdata['longitude'];
		$alt_end = $new_rawdata['altitude'];
	}	
}
echo "New lines added to database: $i ";
echo "<br>";

$pieces = array();
for($i = 0; $i < count($delta_time); ++$i) {
	// Calculate all small s
	$pieces[] = $delta_time[$i] * ($speed_ms[$i] + (($speed_ms[$i+1]-$speed_ms[$i])/2) ); 
}
// Sum of s is whole distance -> [m] to [km]
$distance = array_sum($pieces) / 1000;	

echo "Distance was calculated: $distance ";
echo "<br>";
if( $distance < 0) {
	echo "distance was negative -> set to 0 ";echo "<br>";
	$distance = 0;
}

$end_time = new DateTime(date("Y-m-d H:i:s", ($new_rawdata['time']/1000)) ) ; 
$start_time = new DateTime(date("Y-m-d H:i:s", ($logging_start_time/1000)) ) ;// [ms] to [s]        // convert to call diff
echo "<br>";
echo "end time raw data: "; echo ($new_rawdata['time']/1000);
echo "<br>";
echo "ENDTIME";
print_r($end_time);
echo "<br>";
echo "STARTTIME";
print_r($start_time);
echo "<br>";
$diff_time = $end_time->diff($start_time);
print_r($diff_time);
// duration in hh:mm:ss
$duration = sprintf(
		'%02d:%02d:%02d',
		($diff_time->d * 24) + $diff_time->h,
		$diff_time->i,
		$diff_time->s );
// $duration = "$diff_time->h:$diff_time->i:$diff_time->s"; // old

// Update DB, setup table, add distance
$statement = $pdo->prepare("UPDATE setup SET distance=?, lat_end=?, long_end=?, alt_end=?, duration=? WHERE setup_ID=?");
$statement->execute(array($distance, $lat_end, $long_end, $alt_end, $duration, $setup_id));


echo "Finish: close connections and delete file";
echo "<br>";
// Close connection
$statement = null;
$pdo = null;
// Close file
fclose ($file);
// Delete file
if(!empty($out_file_name)) {
	unlink($out_file_name);
}

// vim: set tabstop=4 shiftwidth=4 noexpandtab :
?>