TrainService/dataPull.php at master · robert-hucks/TrainService · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
<?php
	include("httpful.phar");
	include("dbConnection.php");

	function checkNull($item) {
		if(is_null($item)) {
			return 0;
		} else {
			return $item;
		}
	}

	//$sql = "SELECT * FROM Stations LIMIT 0, 5";
	$sql = "SELECT * FROM Stations WHERE TLC = 'WAT' OR TLC = 'HWY' OR TLC = 'TAM'";

	$result = mysqli_query($link, $sql);

	while ($row = mysqli_fetch_assoc($result)) {

		echo "Station Name: " . $row['Station'] . "</br>";

		// Delete rows from TrainTimes table of current station
		$sqlDel = "DELETE FROM `TrainTimes` WHERE `TLC` = '" . $row['TLC'] . "'";

		//echo "delete SQL: " . $sqlDel . "</br>";

		mysqli_query($link, $sqlDel) or die("Delete query failed");

		// Add times to the TrainTime table
		$url = "http://transportapi.com/v3/uk/train/station/" . $row['TLC'] . "/live.json?app_id=03bf8009&app_key=d9307fd91b0247c607e098d5effedc97&train_status=passenger";
		//$url = "http://transportapi.com/v3/uk/train/station/WAT/live.json?app_id=03bf8009&app_key=d9307fd91b0247c607e098d5effedc97&train_status=passenger";

		$response = \Httpful\Request::get($url)->send();

		//echo "Count of items to add: " . count($response->body->departures->all) . "</br>";

		//echo "Complete JSON object response</br>";
		//print("<pre>");
		//print_r($response);
		//print("</pre>");

		foreach($response->body->departures as $departure) {
			$data = array();
			//echo "For every Departure</br>";
			foreach($departure as $train) {

				//echo "For every train</br>";
				//print("<pre>");
				//print_r($train);
				//print("</pre>");

				$sqlInsert = "INSERT INTO `TrainTimes`(`TLC`, `Service`, `TrainUID`, `Platform`, `Operator`, `Aimed_Dep_Date`, `Aimed_Arr_Date`, `Aimed_Pass_Time`, `Origin_Name`, `Source`, `Destination_Name`) VALUES (";
				$sqlInsert .= "'" . $row['TLC'] . "', ";
				$sqlInsert .= $train->service . ", ";
				$sqlInsert .= "'" . $train->train_uid . "', ";
				$sqlInsert .= checkNull($train->platform) . ", ";
				$sqlInsert .= "'" . $train->operator . "', ";
				$sqlInsert .= "'" . $train->aimed_departure_time . "', ";
				$sqlInsert .= "'" . $train->aimed_arrival_time . "', ";
				$sqlInsert .= "'" . $train->aimed_pass_time . "', ";
				$sqlInsert .= "'" . $train->origin_name . "', ";
				$sqlInsert .= "'" . $train->source . "', ";
				$sqlInsert .= "'" . $train->destination_name . "')";

				//echo "SQL: " . $sqlInsert . "</br>";
				mysqli_query($link, $sqlInsert) or die("INSERT query failed");

				$data[] = $train->operator;
			}

			//print("<pre>");
			//print_r($data);
			//print("</pre>");

			$tmp = array_count_values($data);
			foreach (array_unique($data) as $value) {
				echo "#" . $value . " : " . $tmp[$value] . "</br>";
				$statSQL = "INSERT INTO `StationLineHistory` (`TLC`, `Operator`, `Count`, `Date`) VALUES ('" . $row['TLC'] . "', '" . $value . "', '" . $tmp[$value] . "', CURDATE())";
				echo $statSQL . "</br>";
				mysqli_query($link, $statSQL);
			}
		}

		//print("<pre>");
		//print_r($response->body);
		//print("</pre>");
		//echo("-------------");
	}
	mysqli_close($link);
?>