// SPDX-FileCopyrightText: 2021 Harald Eilertsen // // SPDX-License-Identifier: AGPL-3.0-or-later /* * I kinda overloaded this snippet. Added comments for each function. But this * is used in the giglog admin page, which should only be available to admin * users. After the file is being uploaded into the concertlists folder, its * content is written into wpg_files. Then the content is split into lines and * each line is transformed intoa concert */ /* this checks th wpg_files table to see if any file is uploaded but hasn't * gone through the processing process - aka fetching each line and * transforming it into a concert line */ function giglogadmin_getunprocessed() { global $wpdb; $content = '

UNPROCESSED ROWS

'; $content .= ''; $query = 'SELECT rowid,filename,filedate,rowcontent from wpg_files where processed="N"'; $results = $wpdb->get_results($query); foreach ($results AS $row) { $content .= ''; $content .= ''; $content .= ''; $content .= ''; $content .= ''; $content .= ''; } $content .= '
FilerowFILENAMEDATEUploadedContent
' . $row->rowid . '' . $row->filename . '' . $row->filedate . '' . $row->rowcontent . '
'; return $content; } /* function that goes through each line of the unprocessed file. Each line is * checked against the concerts table. if it exists - concert and date and * venue - it does nothing with it. If it doesn't exist, it checks if band or * venue exists. If they don't, they get created, if they do, their ID from * their table is fetchd and used in concerts table */ function giglogadmin_insertconcerts() { global $wpdb; $concertlist = '

Inserted the following:

'; $newconcert= []; $query1 = 'SELECT id,rowid,filename,filedate,rowcontent from wpg_files where processed="N"'; $cresults = $wpdb->get_results($query1); foreach ($cresults AS $row) { $rowfileid = $row->id; $resultArray = explode("\t", $row->rowcontent); $band = $resultArray[0]; $venue = $resultArray[1]; $condate = date('Y-m-d', strtotime($resultArray[2])); $ticketlink = $resultArray[3]; $eventlink = $resultArray[4]; //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 //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; } } //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); if ($cresults) { $usql = 'UPDATE wpg_files SET processed="D", wpgc_id = ' . $cresults[0]->id . ' WHERE id = ' . $rowfileid; $uresults = $wpdb->get_results($usql); $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; $usql = 'UPDATE wpg_files SET processed="Y", wpgc_id = ' . $newconcertid . ' WHERE id = ' . $rowfileid; $uresults = $wpdb->get_results($usql); $concertlist .= 'BAND ' . $band . ' with band ID ' . $newconcert[0]; $concertlist .= ', VENUE ' . $venue . ' with venue ID ' . $newconcert[1]; $concertlist .= ', CONCERTDATE ' . $condate . ', Ticket LINK ' . $ticketlink . ', event LINK' . $eventlink; $concertlist .= '
'; } //end check if concert exists //remember to add the concert ID when displaying } //end looping through unprocessed rows return $concertlist; } function giglogadmin_display_unprocessed() { $output = giglogadmin_getunprocessed(); $output .= '
'; if (isset($_POST['ProcessConcerts'])) { $output .= giglogadmin_insertconcerts(); //$url2 = $_SERVER['REQUEST_URI']; //header("Refresh: 5; URL=$url2"); //reload page } //end if button for process concerts is pressed return $output; }