1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
<?php
// SPDX-FileCopyrightText: 2021 Andrea Chirulescu <andrea.chirulescu@gmail.com>
// SPDX-FileCopyrightText: 2021 Harald Eilertsen <haraldei@anduin.net>
//
// 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 = '<br /><h3> UNPROCESSED ROWS</h3><table class="concertstable">';
$content .= '<tr class="concertsheaderrow"><th>Filerow</th><th>FILENAME</th><th>DATE</TH><th>UploadedContent</th>';
$query = 'SELECT rowid,filename,filedate,rowcontent from wpg_files where processed="N"';
$results = $wpdb->get_results($query);
foreach ($results AS $row) {
$content .= '<tr>';
$content .= '<td>' . $row->rowid . '</td>';
$content .= '<td>' . $row->filename . '</td>';
$content .= '<td>' . $row->filedate . '</td>';
$content .= '<td>' . $row->rowcontent . '</td>';
$content .= '</tr>';
}
$content .= '</table>';
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 = '<p>Inserted the following:</p>';
$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 .= ' <br />';
} 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 .= ' <br />';
}
//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 .= '<form method="POST" action=""><input type="submit" name="ProcessConcerts" value="ProcessConcerts"/></form>';
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;
}
|
