diff --git a/api/bounds.php b/api/bounds.php index 4f6e314..5253abc 100755 --- a/api/bounds.php +++ b/api/bounds.php @@ -1,78 +1,76 @@ . */ require '../load.php'; -expect('db'); - $results = []; if( isset( $_GET['lat_n'], $_GET['lat_s'], $_GET['lng_e'], $_GET['lng_w'] ) ) { $lat_n = & $_GET['lat_n']; $lat_s = & $_GET['lat_s']; $lng_e = & $_GET['lng_e']; $lng_w = & $_GET['lng_w']; - $results = $db->getResults( + $results = query_results( sprintf( "SELECT station.station_ID, station.station_lat, station.station_lon, " . "stationowner.stationowner_uid, stationowner.stationowner_name " . - "FROM {$db->getTables('station', 'stationowner')} " . + "FROM {$GLOBALS[JOIN]('station', 'stationowner')} " . "WHERE station.station_lat BETWEEN %f AND %f " . "AND station.station_lon BETWEEN %f AND %f " . "AND station.stationowner_ID = stationowner.stationowner_ID", $lat_s, $lat_n, $lng_w, $lng_e ), 'Station' ); $n_results = count( $results ); $results->error = $n_results > 90; if(! $results->error) { // Get prices for($i=0; $i<$n_results; $i++) { - $results[$i]->prices = $db->getResults( + $results[$i]->prices = query_results( sprintf( "SELECT DISTINCT price.price_value, price.price_self, " . "fuel.fuel_uid, fuel.fuel_name " . - "FROM {$db->getTables('price', 'fuel')} " . + "FROM {$GLOBALS[JOIN]('price', 'fuel')} " . "WHERE price.station_ID = %d " . "AND price.fuel_ID = fuel.fuel_ID " . "ORDER BY price.price_value", $results[$i]->station_ID ), 'Price' ); } } } http_json_header(); echo json_encode( $results, isset( $_GET['pretty'] ) ? JSON_PRETTY_PRINT : 0 ); diff --git a/cli/import-mise-functions.php b/cli/import-mise-functions.php index d86a99d..fcaf66a 100755 --- a/cli/import-mise-functions.php +++ b/cli/import-mise-functions.php @@ -1,234 +1,234 @@ . */ function indexed_array($rows, $property_index, $property_return) { $indexed = []; foreach($rows as $row) { $indexed[ $row->{$property_index} ] = $row->{$property_return}; } return $indexed; } function get_station_ID($station_miseID, $station_name = null, $station_type = null, $station_address = null, $station_lat = null, $station_lon = null, $comune_ID = null, $stationowner_ID = null, $fuelprovider_ID = null) { - global $db, $stations; + global $stations; if( isset( $stations[ $station_miseID ] ) ) { return $stations[ $station_miseID ]; } if( $station_name === null ) { throw new Exception( _("La stazione miseID %d doveva essere già stata inserita"), $station_miseID ); } if($station_type === 'Altro') { $station_type = 'ALTRO'; } elseif($station_type === 'Strada Statale') { $station_type = 'STRADA_STATALE'; } elseif($station_type === 'Autostradale') { $station_type = 'AUTOSTRADALE'; } else { throw new Exception( sprintf( "Tipo di stazione non prevista: %s", esc_html( $station_type ) ) ); } - $db->insertRow('station', [ + insert_row('station', [ new DBCol('station_miseID', $station_miseID, 'd'), new DBCol('station_name', $station_name, 's'), new DBCol('station_type', $station_type, 's'), new DBCol('station_address', $station_address, 's'), new DBCol('station_lat', $station_lat, 'f'), new DBCol('station_lon', $station_lon, 'f'), new DBCol('comune_ID', $comune_ID, 'd'), new DBCol('stationowner_ID', $stationowner_ID, 'd'), new DBCol('fuelprovider_ID', $fuelprovider_ID, 'd') ] ); - $station = $db->getRow( + $station = query_row( sprintf( "SELECT station_ID, station_miseID " . - "FROM {$db->getTable('station')} " . + "FROM {$GLOBALS[T]('station')} " . "WHERE station_ID = %d", - $db->getLastInsertedID() + last_inserted_ID() ), 'Station' ); return $stations[ $station->station_miseID ] = $station->station_ID; } function get_comune_ID($comune_uid, $comune_name, $provincia_name) { - global $db, $comuni; + global $comuni; if( isset( $comuni[ $comune_uid ] ) ) { return $comuni[ $comune_uid ]; } - $db->insertRow('comune', [ + insert_row('comune', [ new DBCol('comune_uid', $comune_uid, 's'), new DBCol('comune_name', $comune_name, 's') ] ); - $comune = $db->getRow( + $comune = query_row( sprintf( "SELECT comune_ID, comune_uid " . - "FROM {$db->getTable('comune')} " . + "FROM {$GLOBALS[T]('comune')} " . "WHERE comune_ID = %d", - $db->getLastInsertedID() + last_inserted_ID() ), 'Comune' ); - $db->insertRow('rel_provincia_comune', [ + insert_row('rel_provincia_comune', [ new DBCol( 'provincia_ID', get_provincia_ID( generate_slug($provincia_name), $provincia_name ), 'd' ), new DBCol('comune_ID', $comune->comune_ID, 'd') ] ); return $comuni[ $comune->comune_uid ] = $comune->comune_ID; } function get_fuelprovider_ID($fuelprovider_uid, $fuelprovider_name) { - global $db, $fuelproviders; + global $fuelproviders; if( isset( $fuelproviders[ $fuelprovider_uid ] ) ) { return $fuelproviders[ $fuelprovider_uid ]; } - $db->insertRow('fuelprovider', [ + insert_row('fuelprovider', [ new DBCol('fuelprovider_uid', $fuelprovider_uid, 's'), new DBCol('fuelprovider_name', $fuelprovider_name, 's') ] ); - $fuelprovider = $db->getRow( + $fuelprovider = query_row( sprintf( "SELECT fuelprovider_ID, fuelprovider_uid " . - "FROM {$db->getTable('fuelprovider')} " . + "FROM {$GLOBALS[T]('fuelprovider')} " . "WHERE fuelprovider_ID = %d", - $db->getLastInsertedID() + last_inserted_ID() ), 'Fuelprovider' ); return $fuelproviders[ $fuelprovider->fuelprovider_uid ] = $fuelprovider->fuelprovider_ID; } function get_stationowner_ID($stationowner_uid, $stationowner_name) { - global $db, $stationowners; + global $stationowners; if( isset( $stationowners[ $stationowner_uid ] ) ) { return $stationowners[ $stationowner_uid ]; } // Lol if( ($pos = strpos($stationowner_name, ' IL REGISTRO IMPRESE NON GARANTISCE') ) !== false ) { $stationowner_name = substr($stationowner_name, 0, $pos); $stationowner_uid = generate_slug( $stationowner_name ); $stationowner_note = substr($stationowner_name, $pos); } else { $stationowner_note = null; } // Another check if( isset( $stationowners[ $stationowner_uid ] ) ) { return $stationowners[ $stationowner_uid ]; } - $db->insertRow('stationowner', [ + insert_row('stationowner', [ new DBCol('stationowner_uid', $stationowner_uid, 's'), new DBCol('stationowner_name', $stationowner_name, 's'), new DBCol('stationowner_note', $stationowner_note, 'snull') ] ); - $stationowner = $db->getRow( + $stationowner = query_row( sprintf( "SELECT stationowner_ID, stationowner_uid " . - "FROM {$db->getTable('stationowner')} " . + "FROM {$GLOBALS[T]('stationowner')} " . "WHERE stationowner_ID = %d", - $db->getLastInsertedID() + last_inserted_ID() ), 'Stationowner' ); return $stationowners[ $stationowner->stationowner_uid ] = $stationowner->stationowner_ID;; } function get_fuel_ID($fuel_uid, $fuel_name) { - global $db, $fuels; + global $fuels; if( isset( $fuels[ $fuel_uid ] ) ) { return $fuels[ $fuel_uid ]; } - $db->insertRow('fuel', [ + insert_row('fuel', [ new DBCol('fuel_uid', $fuel_uid, 's'), new DBCol('fuel_name', $fuel_name, 's') ] ); - $fuel = $db->getRow( + $fuel = query_row( sprintf( "SELECT fuel_ID, fuel_uid " . - "FROM {$db->getTable('fuel')} " . + "FROM {$GLOBALS[T]('fuel')} " . "WHERE fuel_ID = %d", - $db->getLastInsertedID() + last_inserted_ID() ), 'Fuel' ); return $fuels[ $fuel->fuel_uid ] = $fuel->fuel_ID; } function get_provincia_ID($provincia_uid, $provincia_name) { - global $db, $provincie; + global $provincie; if( isset( $provincie[ $provincia_uid ] ) ) { return $provincie[ $provincia_uid ]; } - $db->insertRow('provincia', [ + insert_row('provincia', [ new DBCol('provincia_uid', $provincia_uid, 's'), new DBCol('provincia_name', $provincia_name, 's') ] ); - $provincia = $db->getRow( + $provincia = query_row( sprintf( "SELECT provincia_ID, provincia_uid " . - "FROM {$db->getTable('provincia')} " . + "FROM {$GLOBALS[T]('provincia')} " . "WHERE provincia_ID = %d", - $db->getLastInsertedID() + last_inserted_ID() ), 'Provincia' ); return $provincie[ $provincia->provincia_uid ] = $provincia->provincia_ID; } /** * 11/12/2015 20:30 => 2015-12-11 20:30:00 * * @return string MySQL/MariaDB datetime */ function itdate2datetime($time) { $dateTime = DateTime::createFromFormat('d/m/Y H:i:s', $time); return $dateTime->format('Y-m-d H:i:s'); } diff --git a/cli/import-mise.php b/cli/import-mise.php index 42b8312..5632b61 100755 --- a/cli/import-mise.php +++ b/cli/import-mise.php @@ -1,148 +1,146 @@ . */ /* * Manually download data from Ministero dello Sviluppo Economico * http://www.sviluppoeconomico.gov.it/index.php/it/open-data/elenco-dataset/2032336-carburanti-prezzi-praticati-e-anagrafica-degli-impianti * * Files: * http://www.sviluppoeconomico.gov.it/images/exportCSV/prezzo_alle_8.csv * http://www.sviluppoeconomico.gov.it/images/exportCSV/anagrafica_impianti_attivi.csv * * The files are licensed under the terms of the Italian Open Data License v2.0 * http://www.dati.gov.it/iodl/2.0/ */ require __DIR__ . '/../load.php'; require __DIR__ . '/import-mise-functions.php'; -expect('db'); - -//$db->query("TRUNCATE {$db->getTable('rel_provincia_comune')}"); -//$db->query("TRUNCATE {$db->getTable('provincia')}"); -//$db->query("TRUNCATE {$db->getTable('comune')}"); -//$db->query("TRUNCATE {$db->getTable('station')}"); -//$db->query("TRUNCATE {$db->getTable('stationowner')}"); -//$db->query("TRUNCATE {$db->getTable('fuelprovider')}"); -//$db->query("TRUNCATE {$db->getTable('fuel')}"); -$db->query("TRUNCATE {$db->getTable('price')}"); - -$comuni = $db->getResults("SELECT comune_ID, comune_uid FROM {$db->getTable('comune')}", 'Comune'); +//query("TRUNCATE {$GLOBALS[T]('rel_provincia_comune')}"); +//query("TRUNCATE {$GLOBALS[T]('provincia')}"); +//query("TRUNCATE {$GLOBALS[T]('comune')}"); +//query("TRUNCATE {$GLOBALS[T]('station')}"); +//query("TRUNCATE {$GLOBALS[T]('stationowner')}"); +//query("TRUNCATE {$GLOBALS[T]('fuelprovider')}"); +//query("TRUNCATE {$GLOBALS[T]('fuel')}"); +query("TRUNCATE {$GLOBALS[T]('price')}"); + +$comuni = query_results("SELECT comune_ID, comune_uid FROM {$GLOBALS[T]('comune')}", 'Comune'); $comuni = indexed_array($comuni, 'comune_uid', 'comune_ID'); -$provincie = $db->getResults("SELECT provincia_ID, provincia_uid FROM {$db->getTable('provincia')}", 'Provincia'); +$provincie = query_results("SELECT provincia_ID, provincia_uid FROM {$GLOBALS[T]('provincia')}", 'Provincia'); $provincie = indexed_array($provincie, 'provincia_uid', 'provincia_ID'); -$fuels = $db->getResults("SELECT fuel_ID, fuel_uid FROM {$db->getTable('fuel')}", 'Fuel'); +$fuels = query_results("SELECT fuel_ID, fuel_uid FROM {$GLOBALS[T]('fuel')}", 'Fuel'); $fuels = indexed_array($fuels, 'fuel_uid', 'fuel_ID'); -$stations = $db->getResults("SELECT station_ID, station_miseID FROM {$db->getTable('station')}", 'Station'); +$stations = query_results("SELECT station_ID, station_miseID FROM {$GLOBALS[T]('station')}", 'Station'); $stations = indexed_array($stations, 'station_miseID', 'station_ID'); -$stationowners = $db->getResults("SELECT stationowner_ID, stationowner_uid FROM {$db->getTable('stationowner')}", 'Stationowner'); +$stationowners = query_results("SELECT stationowner_ID, stationowner_uid FROM {$GLOBALS[T]('stationowner')}", 'Stationowner'); $stationowners = indexed_array($stationowners, 'stationowner_uid', 'stationowner_ID'); -$fuelproviders = $db->getResults("SELECT fuelprovider_ID, fuelprovider_uid FROM {$db->getTable('fuelprovider')}", 'Fuelprovider'); +$fuelproviders = query_results("SELECT fuelprovider_ID, fuelprovider_uid FROM {$GLOBALS[T]('fuelprovider')}", 'Fuelprovider'); $fuelproviders = indexed_array($fuelproviders, 'fuelprovider_uid', 'fuelprovider_ID'); try { if( ! isset( $argv[1], $argv[2] ) ) { throw new Exception( sprintf( _("Utilizzo: %s FILE_STAZIONI.csv PREZZI_ALLE_8.csv"), esc_html( $argv[0] ) ), 1 ); } define('FILENAME_STATIONS', $argv[1]); define('FILENAME_PRICES', $argv[2]); if( ! file_exists(FILENAME_STATIONS) ) { throw new Exception( _("File delle stazioni non trovato"), 2 ); } if( ! $handle = fopen(FILENAME_STATIONS, 'r') ) { throw new Exception( _("Impossibile aprire il file delle stazioni"), 3 ); } // Waste first 2 lines fgetcsv($handle); fgetcsv($handle); while( $data = fgetcsv($handle, 512, ';') ) { get_station_ID( (int) $data[0] /*$station_miseID*/, $data[4] /*$station_name*/, $data[3] /*$station_type*/, $data[5] /*$station_address*/, (float) $data[8] /*$station_lat*/, (float) $data[9] /*$station_lon*/, get_comune_ID( generate_slug( $data[6] ) /*$comune_uid*/, $data[6] /*$comune_name*/, $data[7] /*$provincia_name*/ ), get_stationowner_ID( generate_slug( $data[1] ) /*$stationowner_uid*/, $data[1] /*$stationowner_name*/ ), get_fuelprovider_ID( generate_slug( $data[2] ) /*$fuelprovider_uid*/, $data[2] /*$fuelprovider_name*/ ) ); } fclose($handle); // Prices if( ! file_exists(FILENAME_PRICES) ) { throw new Exception( _("File dei prezzi non trovato"), 4 ); } if( ! $handle = fopen(FILENAME_PRICES, 'r') ) { throw new Exception( _("Impossibile aprire il file dei prezzi"), 5 ); } // Waste first 2 lines fgetcsv($handle); fgetcsv($handle); while( $data = fgetcsv($handle, 255, ';') ) { - $db->insertRow('price', [ + insert_row('price', [ new DBCol('price_value', (float) $data[2], 'f'), new DBCol('price_self', (int) $data[3], 'd'), new DBCol('price_date', itdate2datetime($data[4]), 's'), new DBCol( 'fuel_ID', get_fuel_ID( generate_slug($data[1]) /*$fuel_uid*/, $data[1] /*$fuel_name*/ ), 'd' ), new DBCol('station_ID', get_station_ID( (int) $data[0] ), 'd') ] ); } fclose($handle); } catch(Exception $e) { printf( _("Errore: %s."), $e->getMessage() ); echo "\n"; exit( $e->getCode() ); } diff --git a/includes/footer.php b/includes/footer.php index bed31da..3410eec 100755 --- a/includes/footer.php +++ b/includes/footer.php @@ -1,27 +1,25 @@ . */ -function get_footer($page = null, $args = null) { - expect('menu'); - +function get_footer() { load_module('footer'); ?> . */ /** * It does what it's written. * * @param float $latitude1 * @param float $longitude1 * @param float $latitude2 * @param float $longitude2 * @return float Meters */ function get_distance_between_coordinates($latitude1, $longitude1, $latitude2, $longitude2) { $theta = $longitude1 - $longitude2; $distance = sin(deg2rad($latitude1)) * sin(deg2rad($latitude2)) + ( cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)) ); $distance = acos($distance); $distance = rad2deg($distance) * 60 * 1.1515; $distance = $distance * 1609.344; // To meters return $distance; } function get_in_touch($url, $who) { return HTML::a($url, mdi_icon('perm_contact_calendar') . _("Entra in contatto"), sprintf( _("Mettiti in contatto con %s"), $who ), 'waves-effect waves-teal btn'); } function legal_notes($url, $license, $project) { return HTML::a($url, $license, sprintf( _("Informazioni legali su %s"), $project ) ); } /** * Material Design Icon */ function mdi_icon($uid, $class = 'left') { return "$uid"; } /** * Run GNU Gettext */ function gettext_rocks($lang = 'it_IT', $domain = 'fuel.reyboz.it', $folder = 'l10n', $encoding = 'UTF-8') { putenv("LANG=$lang.$encoding"); setlocale(LC_MESSAGES, "$lang.$encoding"); bindtextdomain($domain, $folder); textdomain($domain); bind_textdomain_codeset($domain, $encoding); } /** * Get the last update price. * * Has a cache. * * @param string $format DateTime format. * @return string Formatted date. */ function last_price_date($format = 'd/m/Y H:i') { - expect('db'); - static $lastdate = null; if($lastdate === null) { - $lastdate = $GLOBALS['db']->getValue( - "SELECT MAX(price_date) AS lastdate FROM {$GLOBALS['db']->getTable('price')}", + $lastdate = query_value( + "SELECT MAX(price_date) AS lastdate FROM {$GLOBALS[T]('price')}", 'lastdate' ); } $d = DateTime::createFromFormat('Y-m-d H:i:s', $lastdate); return $d->format($format); } diff --git a/includes/header.php b/includes/header.php index 7d15951..4e1dd78 100755 --- a/includes/header.php +++ b/includes/header.php @@ -1,74 +1,72 @@ . */ function get_header($uid, $args = []) { - expect('menu'); - $args = merge_args_defaults($args, [ 'theme' => 'default' ] ); switch($args['theme']) { case 'default': enqueue_css('materialize'); enqueue_css('materialize.icons'); enqueue_js('jquery'); enqueue_js('materialize'); break; } header("Content-Type: text/html; charset=utf-8"); ?> <?php echo SITE_NAME ?> - <?php echo get_menu_entry($uid)->name ?> " . HTML::a( $menuEntry->url, $menuEntry->name, $menuEntry->get('description') ) . "\n"; } diff --git a/index.php b/index.php index f1c6a46..ccf6b61 100755 --- a/index.php +++ b/index.php @@ -1,100 +1,98 @@ . */ /* * The map */ require 'load.php'; -expect('db'); - enqueue_css('leaflet'); enqueue_css('my-fuel-map'); enqueue_js('jquery.ui'); enqueue_js('leaflet'); enqueue_js('leaflet.bouncemarker'); enqueue_js('my-fuel-map'); get_header('map'); $last_price_rfc3339 = last_price_date(DateTime::RFC3339); $last_price_text = last_price_date(); ?>

getValue( - "SELECT COUNT(*) as count FROM {$db->getTable('station')}", + query_value( + "SELECT COUNT(*) as count FROM {$GLOBALS[T]('station')}", 'count' ), HTML::property('class', 'station-counter') ) ) ?>

$last_price_text" ) ?>

">
navigation " />
navigation