From 3fdbe3e2bcdfb2fa94db65fdd302ae4a143702bd Mon Sep 17 00:00:00 2001 From: Harald Eilertsen Date: Mon, 8 Mar 2021 21:32:37 +0100 Subject: Refactor processing uploads. Moves all database operations to separate classes for each type of object, bands, venues and concerts. This makes the logic cleaner, and concentrates the db related code to logically distinct parts of the code. This means we no longer need the global access to the $wpdb object from the processing code. --- includes/admin/views/giglog_import_gigs.php | 99 ++++++++++++----------------- includes/band.php | 44 +++++++++++++ includes/concert.php | 49 ++++++++++++++ includes/venue.php | 52 +++++++++++++++ 4 files changed, 186 insertions(+), 58 deletions(-) create mode 100644 includes/band.php create mode 100644 includes/concert.php create mode 100644 includes/venue.php diff --git a/includes/admin/views/giglog_import_gigs.php b/includes/admin/views/giglog_import_gigs.php index 2fe9776..48d9a6a 100644 --- a/includes/admin/views/giglog_import_gigs.php +++ b/includes/admin/views/giglog_import_gigs.php @@ -1,22 +1,26 @@ . + * Copyright (C) 2021 Harald Eilertsen, Andrea Chirulescu + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see . */ if ( !class_exists( 'GiglogAdmin_ImportGigsPage' ) ) { + require_once __DIR__ . '/../../band.php'; + require_once __DIR__ . '/../../concert.php'; + require_once __DIR__ . '/../../venue.php'; + class GiglogAdmin_ImportGigsPage { static function render_html() { ?> @@ -41,9 +45,21 @@ if ( !class_exists( 'GiglogAdmin_ImportGigsPage' ) ) { } } + /** + * Imports concert data from a file with tab separated values. + * + * The file must contain the following columns each separated by _one_ + * tab character: + * + * 1. Bandname + * 2. Venuename + * 3. Concert date + * 4. Ticket link + * 5. Event info link + * + * Empty lines are ignored. + */ static function process_upload($file) { - global $wpdb; - $concertlist = '

Inserted the following:

'; $newconcert= []; $fo = new SplFileObject($file['tmp_name']); @@ -63,62 +79,29 @@ if ( !class_exists( 'GiglogAdmin_ImportGigsPage' ) ) { //first item in the row should be band $resultArray[0]; second should be venue $resultArray[1]; third should be concert date $resultArray[2]; //fourth item is ticketlink $resultArray[3]; fifth item is eventlink $resultArray[4]; - //processing band - $bandsql = 'SELECT id FROM wpg_bands WHERE upper(wpgband_name)="' . $band . '"'; - $results = $wpdb->get_results($bandsql); - if ($results) - $newconcert[0] = $results[0]->id; - else { - $wpdb->insert('wpg_bands', array( - 'id' => '', - 'wpgband_name' => $band - )); - echo ($wpdb->last_error); - $newconcert[0] = $wpdb->insert_id; - } - //done processing band + $newconcert[0] = GiglogAdmin_Band::find_or_create($band); - //processing venue if (is_numeric($venue)) $newconcert[1] = $venue; else { - $venuesql = 'SELECT id FROM wpg_venues WHERE upper(wpgvenue_name)="' . $venue . '"'; - $results = $wpdb->get_results($venuesql); - if ($results) - $newconcert[1] = $results[0]->id; - else { - $wpdb->insert('wpg_venues', array( - 'id' => '', - 'wpgvenue_name' => $venue - )); - echo ($wpdb->last_error); - $newconcert[1] = $wpdb->insert_id; - } + $newconcert[1] = GiglogAdmin_Venue::find_or_create($venue); } - //done processing venue //not sure how to check dates, hopefully manual verification of files will take care of it - //check if concert already exists and return ID if it does. Not checking by date, to be considered - $csql = 'SELECT id from wpg_concerts where band = ' . $newconcert[0] . ' and venue = ' . $newconcert[1] . ' and wpgconcert_date ="' . $condate . '"'; - - $cresults = $wpdb->get_results($csql); + $cresults = GiglogAdmin_Concert::get($newconcert[0], $newconcert[1], $condate); if ($cresults) { $concertlist .= 'DUPLICATE ROW detected BAND ' . $band . ' with band ID ' . $newconcert[0]; $concertlist .= ', VENUE ' . $venue . ' with venue ID ' . $newconcert[1]; $concertlist .= ', CONCERTDATE ' . $condate; $concertlist .= '
'; } else { - $wpdb->insert('wpg_concerts', array( - 'id' => '', - 'band' => $newconcert[0], - 'venue' => $newconcert[1], - 'wpgconcert_date' => $condate, - 'wpgconcert_tickets' => $ticketlink, - 'wpgconcert_event' => $eventlink - )); - echo ($wpdb->last_error); - $newconcertid = $wpdb->insert_id; + $newconcertid = GiglogAdmin_Band::create( + $newconcerts[0], + $newconcerts[1], + $condate, + $ticketlink, + $eventlink); $concertlist .= 'BAND ' . $band . ' with band ID ' . $newconcert[0]; $concertlist .= ', VENUE ' . $venue . ' with venue ID ' . $newconcert[1]; diff --git a/includes/band.php b/includes/band.php new file mode 100644 index 0000000..ce3c689 --- /dev/null +++ b/includes/band.php @@ -0,0 +1,44 @@ +. + */ + +if ( !class_exists('GiglogAdmin_Band') ) { + class GiglogAdmin_Band + { + static function create($name) + { + global $wpdb; + + $wpdb->insert('wpg_bands', array( + 'id' => '', + 'wpgband_name' => $name + )); + + return $wpdb->insert_id; + } + + static function find_or_create($name) + { + global $wpdb; + + $bandsql = 'SELECT id FROM wpg_bands WHERE upper(wpgband_name)="' . $name . '"'; + $results = $wpdb->get_results($bandsql); + + return $results ? $results[0]->id : GiglogAdmin_Band::create($name); + } + } +} diff --git a/includes/concert.php b/includes/concert.php new file mode 100644 index 0000000..59f78c2 --- /dev/null +++ b/includes/concert.php @@ -0,0 +1,49 @@ +. + */ + +if ( !class_exists('GiglogAdmin_Concert') ) { + class GiglogAdmin_Concert + { + public static function create($band, $venue, $date, $ticketlink, $eventlink) + { + global $wpdb; + + $wpdb->insert('wpg_concerts', array( + 'band' => $band, + 'venue' => $venue, + 'wpgconcert_date' => $date, + 'wpgconcert_tickets' => $ticketlink, + 'wpgconcert_event' => $eventlink + )); + + return $wpdb->insert_id; + } + + public static function get($band, $venue, $date) + { + global $wpdb; + + $sql = 'SELECT id from wpg_concerts' + . ' where band = ' . $band + . ' and venue = ' . $venue + . ' and wpgconcert_date ="' . $date . '"'; + + return $wpdb->get_results($sql); + } + } +} diff --git a/includes/venue.php b/includes/venue.php new file mode 100644 index 0000000..8edb136 --- /dev/null +++ b/includes/venue.php @@ -0,0 +1,52 @@ +. + */ + +if ( !class_exists('GiglogAdmin_Venue') ) { + class GiglogAdmin_Venue + { + static function create($name) + { + global $wpdb; + + $wpdb->insert('wpg_venues', array( + 'id' => '', + 'wpgvenue_name' => $name + )); + + return $wpdb->insert_id; + } + + static function find_or_create($name) + { + global $wpdb; + + $venuesql = 'SELECT id FROM wpg_venues WHERE upper(wpgvenue_name)="' . $name . '"'; + $results = $wpdb->get_results($venuesql); + + return $results ? $results[0]->id : GiglogAdmin_Venue::create($name); + } + + static function all_cities() + { + global $wpdb; + $results = $wpdb->get_results('select distinct wpgvenue_city from wpg_venues'); + + return array_map(function ($r) { return $r->wpgvenue_city; }, $results); + } + } +} -- cgit v1.2.3