dev-chiefworks
101 lines
2.8 KiB
PHP
101 lines
2.8 KiB
PHP
<?php
|
|
|
|
/**
|
|
* The purpose of this script: Remove duplicated weather records in weather table. (Remove duplicated records, only keep lowest weather id)
|
|
* Keep in mind: Don't deleted records exisiting in trip_weather table.
|
|
*/
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] script is starting.\n";
|
|
|
|
require('../../backend.php');
|
|
|
|
$db_host = $savvyext->cfgReadChar('database.host');
|
|
$db_name = $savvyext->cfgReadChar('database.name');
|
|
$db_user = $savvyext->cfgReadChar('database.user');
|
|
$db_pass = $savvyext->cfgReadChar('database.pass');
|
|
$db_port = $savvyext->cfgReadLong('database.port');
|
|
$connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
|
|
$con = pg_connect($connstr) or die ("Could not connect to server\n");
|
|
|
|
$query = "
|
|
SELECT
|
|
date,
|
|
time,
|
|
latitude,
|
|
longitude,
|
|
min(id) as lowest_weather_id,
|
|
COUNT(*) as count
|
|
FROM
|
|
weather
|
|
WHERE
|
|
id NOT IN (
|
|
SELECT DISTINCT weather_id
|
|
FROM
|
|
trip_weather
|
|
)
|
|
GROUP BY
|
|
date,
|
|
time,
|
|
latitude,
|
|
longitude
|
|
HAVING COUNT(*) > 1;
|
|
";
|
|
|
|
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
|
|
$rows = pg_num_rows($rs);
|
|
|
|
if ($rows == 0) {
|
|
echo "[".date("Y-m-d H:i:s")."] Does not any records to delete.\n";
|
|
} else {
|
|
while ($row = pg_fetch_assoc($rs)) {
|
|
$date = $row['date'];
|
|
$time = $row['time'];
|
|
$lat = $row['latitude'];
|
|
$long = $row['longitude'];
|
|
$duplicated_row_num = $row['count'];
|
|
$lowest_weather_id = $row['lowest_weather_id'];
|
|
|
|
removeDuplicatedWeatherData($date, $time, $lat, $long, $duplicated_row_num, $lowest_weather_id);
|
|
}
|
|
}
|
|
|
|
pg_close($con);
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] Script completed.\n";
|
|
|
|
|
|
/********************* FUNCTIONs ************************* */
|
|
function removeDuplicatedWeatherData(string $date, string $time, string $lat, string $long, int $duplicated_row_num, int $lowest_weather_id)
|
|
{
|
|
global $con;
|
|
$query = "
|
|
DELETE FROM weather a
|
|
WHERE a.id IN (
|
|
SELECT
|
|
id
|
|
FROM
|
|
weather
|
|
WHERE
|
|
date = '" . $date . "'
|
|
AND time = '" . $time . "'
|
|
AND latitude = '" . $lat . "'
|
|
AND longitude = '" . $long . "'
|
|
AND id NOT IN (
|
|
SELECT
|
|
DISTINCT weather_id
|
|
FROM
|
|
trip_weather
|
|
)
|
|
)
|
|
AND a.id <> $lowest_weather_id
|
|
";
|
|
|
|
pg_query($con, $query) or die("Cannot execute query: $query\n");
|
|
|
|
$deleted_row_num = $duplicated_row_num - 1;
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] Deleted $deleted_row_num records with date = $date, time = $time, COOR = ($lat, $long).\n";
|
|
}
|
|
|
|
?>
|