datainserter.php (5130B) download
1<?php
2// Server connection details
3$servername = "86.92.67.21";
4$username = "friedel";
5$password = "hailiwa";
6$dbname = "wap2";
7
8$redis = new Redis();
9$redis->connect('127.0.0.1', 6379);
10
11// Include data validator function
12 include 'datavalidator.php';
13// Create connection
14$db_connection = mysqli_connect($servername, $username, $password, $dbname);
15
16// Check connection
17if (!$db_connection) {
18 die("Connection failed: " . mysqli_connect_error());
19}
20
21// Check if request method is POST
22if ($_SERVER['REQUEST_METHOD'] == 'POST') {
23
24 // Retrieve the raw request body data
25 $request_body = file_get_contents('php://input');
26
27 // Decode the JSON data into a PHP associative array
28 $data = json_decode($request_body, true);
29
30 // Access the "WEATHERDATA" array from the decoded data
31 $weather_data = $data['WEATHERDATA'];
32
33 // Loop through each weather data object and insert it into the database
34 foreach ($weather_data as $weather_obj) {
35 $stn = $weather_obj['STN'];
36 $date = $weather_obj['DATE'];
37 $time = $weather_obj['TIME'];
38 $temp = $weather_obj['TEMP'];
39 $dewp = $weather_obj['DEWP'];
40 $stp = $weather_obj['STP'];
41 $slp = $weather_obj['SLP'];
42 $visib = $weather_obj['VISIB'];
43 $wdsp = $weather_obj['WDSP'];
44 $prcp = $weather_obj['PRCP'];
45 $sndp = $weather_obj['SNDP'];
46 $frshtt = $weather_obj['FRSHTT'];
47 $cldc = $weather_obj['CLDC'];
48 $wnddir = $weather_obj['WNDDIR'];
49
50// $stn = 637200;
51// $date = "2022-02-09";
52// $time = "00:00:58";
53// $temp = 10.1;
54// $dewp = 1.5;
55// $stp = 984.1;
56// $slp = 1012.6;
57// $visib = 23.4;
58// $wdsp = 13.8;
59// $prcp = 0.00;
60// $sndp = 0.0;
61// $frshtt = "000000";
62// $cldc = 96.8;
63// $wnddir = 228;
64
65 // Validate temperature
66 $valid = validate_temperature($stn, $temp, $redis);
67
68 // Prepare SQL INSERT-statement
69 $sql = "INSERT INTO weather_data (station_name,date_time,validated,temperature,dew_point,pressure_sea,pressure_station,visibility,
70 wind_speed,precipitation,snow_depth,events,frost_boolean,rain_boolean,snow_boolean,hail_boolean,thunder_boolean,tornado_boolean,cloud_count,wind_direction)
71 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
72
73 // Create prepared statement with the SQL statement
74 $stmt = mysqli_prepare($db_connection, $sql);
75
76 // Concatenate date and time
77 $datetime = $date . " " . $time;
78
79 // Extract the boolean values from $frshtt
80 $frost = (int)substr($frshtt, 0, 1);
81 $rain = (int)substr($frshtt, 1, 1);
82 $snow = (int)substr($frshtt, 2, 1);
83 $hail = (int)substr($frshtt, 3, 1);
84 $thunder = (int)substr($frshtt, 4, 1);
85 $tornado = (int)substr($frshtt, 5, 1);
86
87 // Bind parameters to prepared statement
88 mysqli_stmt_bind_param($stmt, "isiddddddddsiiiiiidi", $stn, $datetime, $valid, $temp, $dewp, $stp, $slp, $visib, $wdsp, $prcp, $sndp, $frshtt, $frost, $rain, $snow, $hail, $thunder, $tornado, $cldc, $wnddir);
89
90 // Execute prepared statement, only if the data is not within the first 30 entries
91 $result = mysqli_stmt_execute($stmt);
92
93
94 // wanneer $valid -1 is zal deze tot de eerste 30 waarden van een station behoren, en dus niet in de database worden gemikt
95// if ($valid == -1) {
96// echo "entry geskipt";
97// $result = false;
98// }
99
100 // Second Insert for shadow table
101 if ($valid == -1) {
102 $sql2 = "SELECT data_id from weather_data where station_name = ? and date_time = ?";
103 $stmt2 = mysqli_prepare($db_connection, $sql2);
104 mysqli_stmt_bind_param($stmt2, "is", $stn, $datetime);
105 $stmt2->execute();
106 $result = $stmt2->get_result();
107 $row = $result->fetch_assoc();
108 $data_id = $row['data_id'];
109
110 $sql3 = "INSERT INTO weather_data_corrected (station_name, origin_data_id, date_time,temperature,dew_point,pressure_sea,pressure_station,visibility,
111 wind_speed,precipitation,snow_depth,events,frost_boolean,rain_boolean,snow_boolean,hail_boolean,thunder_boolean,tornado_boolean,cloud_count,wind_direction)
112 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
113 $stmt3 = mysqli_prepare($db_connection, $sql3);
114 $new_temp = round($redis->lIndex('average'.$stn, 0), 1);
115 mysqli_stmt_bind_param($stmt3, "iisddddddddsiiiiiidi", $stn, $data_id, $datetime, $new_temp, $dewp, $stp, $slp, $visib, $wdsp, $prcp, $sndp, $frshtt, $frost, $rain, $snow, $hail, $thunder, $tornado, $cldc, $wnddir);
116 $result = mysqli_stmt_execute($stmt3);
117 }
118
119
120 // Check if the insertion was successful
121 if ($result) {
122 echo "Data inserted successfully";
123 }
124 else {
125 echo "Error inserting data: " . mysqli_error($db_connection);
126 }
127 }
128}
129
130// Close the database connection
131mysqli_close($db_connection);
132?>