· 6 years ago · Mar 25, 2019, 01:00 AM
1<?php
2
3class Database
4{
5
6 const TOURNAMENT_TABLE = 'tournaments';
7 const IMPORT_FOLDER = 'import';
8 const ARCHIVE_FOLDER = 'archive';
9 const CONFIG_FILE = 'config.php';
10
11 private $connection;
12 private $importPath;
13 private $archivePath;
14 private $config;
15
16 /**
17 * Database constructor.
18 */
19 public function __construct()
20 {
21 // load our custom settings from config file
22 $this->config = include(self::CONFIG_FILE);
23
24 // then use it to connect to the database
25 $this->connectToDb();
26 }
27
28 /**
29 * Connect to mysql database
30 *
31 * @throws Exception
32 */
33 private function connectToDb(){
34 // make sure all needed info is present
35 if (!isset(
36 $this->config['server'],
37 $this->config['user'],
38 $this->config['password'],
39 $this->config['database']
40 )) {
41 throw new Exception('Config file is missing param(s)');
42 }
43
44 // connect to the DB
45 $this->connection = new mysqli($this->config['server'], $this->config['user'], $this->config['password'],
46 $this->config['database']);
47 }
48
49 /**
50 * Returns an array of all the casino names in our tournament table
51 *
52 * @return array
53 * @throws Exception
54 */
55 public function getCasinoNames()
56 {
57 // make the sql query
58 $sql = 'SELECT DISTINCT casino FROM ' . self::TOURNAMENT_TABLE;
59
60 // run it
61 $result = $this->query($sql);
62
63 // get an empty array to hold the results
64 $casinos = [];
65
66 // loop through the results
67 foreach ($result as $row) {
68 // results come back one row at a time
69 // each row is an array with the column names as the array keys
70 $casinos[] = $row['casino']; // grabs the casino column value from each row and puts it in our array
71 }
72
73 // return the result
74 return $casinos;
75 }
76
77 public function getCasinoDates()
78 {
79 // make the sql query
80 $sql = 'SELECT DISTINCT schedule FROM ' . self::TOURNAMENT_TABLE;
81
82 // run it
83 $result = $this->query($sql);
84
85 // get an empty array to hold the results
86 $schedule = [];
87
88 // loop through the results
89 foreach ($result as $row) {
90 // results come back one row at a time
91 // each row is an array with the column names as the array keys
92 $schedule[] = $row['schedule']; // grabs the schedule column value from each row and puts it in our array
93 }
94
95 // return the result
96 return $schedule;
97 }
98
99
100
101
102
103
104
105 /**
106 * Create table and import all in one step
107 */
108 public function install()
109 {
110 $this->createTournamentTable();
111 $this->import();
112 }
113
114 /**
115 * Generate the table if it doesn't already exist in our DB
116 *
117 * @throws Exception
118 */
119 private function createTournamentTable()
120 {
121 $sql = 'CREATE TABLE IF NOT EXISTS ' . self::TOURNAMENT_TABLE . '
122 (
123 `id` INT(11) NOT NULL AUTO_INCREMENT,
124 `grouping` VARCHAR(64),
125 `casino` VARCHAR(64),
126 `game` VARCHAR(64),
127 `schedule` datetime,
128 `cost` VARCHAR(64),
129 `fee_percent` VARCHAR(64),
130 `s_points` VARCHAR(64),
131 `notes` VARCHAR(64),
132 PRIMARY KEY (`id`)
133 );';
134 $this->query($sql);
135 }
136
137 /**
138 * Runs a query on the DB
139 *
140 * @param string $sql
141 * @return bool|mysqli_result
142 * @throws Exception
143 */
144 public function query($sql)
145 {
146 $result = $this->connection->query($sql);
147 if ($result === false) {
148 // TODO: some kind of error handling?
149 throw new Exception('Database error: ' . $this->connection->error);
150 }
151 return $result;
152 }
153
154 /**
155 * Import the first file we find in the import folder, that way the name can be whatever we want it to be
156 *
157 * @throws Exception
158 */
159 public function import()
160 {
161 // load info about the import and archive directories
162 $this->loadDirectoryInfo();
163
164 // pull in all csv files in our import directory
165 $csvFiles = glob($this->importPath . DIRECTORY_SEPARATOR . '*.csv');
166 // we only want one file so grab the first we find
167 if (!isset($csvFiles[0])) {
168 throw new Exception('No import files found');
169 }
170 $importFile = $csvFiles[0];
171
172 // load data from the import file
173 $data = $this->getFileData($importFile);
174
175 // TODO: should probably separate some of the following into its own functions like we did for getFileData
176 // TODO: that way our import function here remains neat and tidy
177
178 // now clear the DB table
179 $sql = 'TRUNCATE ' . self::TOURNAMENT_TABLE;
180 $this->query($sql);
181
182
183 // now build our import query, first the data...
184 $insertValues = [];
185 foreach ($data as $row) {
186 // we need to convert the data to our formats as well (like combining Date and Time)
187 $dateTime = strtotime($row['Date'] . ' ' . $row['Time']);
188 $dateTime = date('Y-m-d H:i:s', $dateTime);
189 // here we take all the data, separate it with commas and quote it, then add it to an array
190 // so this $values here will look like: "grouping", "casino", "game" ...etc
191 // we use implode just so we don't have to type a ton of quotes, commas, and periods to get this
192 // into a usable string
193 $values = implode('", "', [
194 $row['Grouping'],
195 $row['Casino'],
196 $row['Game'],
197 $dateTime,
198 $row['Cost'],
199 $row['Vig%'],
200 $row['S-Points'],
201 $row['Notes'],
202 ]);
203 // now put it into parens
204 $values = '("' . $values . '")';
205 // and add it to this array
206 $insertValues[] = $values;
207 }
208
209 // now we add all the values we put into those nice parens, and commas separate them
210 $sql = 'INSERT INTO ' . self::TOURNAMENT_TABLE
211 . '(grouping, casino, game, schedule, cost, fee_percent, s_points, notes) VALUES '
212 . implode(', ', $insertValues);
213
214 // finally, execute this giant insert statement
215 // the final result of $sql should look like "INSERT INTO tournaments (column1, column2...)
216 // VALUES (val1a, val2a...), (val1b, val2b...), ...etc
217 $this->query($sql);
218
219 // ok cool, now let's archive it so we don't import it again
220 $this->archive($importFile);
221 }
222
223 /**
224 * Generate directory paths for import and archive directories
225 */
226 private function loadDirectoryInfo()
227 {
228 // do nothing if we've already loaded this info
229 if ($this->importPath) {
230 return;
231 }
232
233 // load the import directory information
234 $this->importPath = dirname(__FILE__) . DIRECTORY_SEPARATOR . self::IMPORT_FOLDER;
235
236 // if the import directory doesn't exist, we should create it
237 $this->initDirectory($this->importPath);
238
239 // same with our archive directory
240 $this->archivePath = $this->importPath . DIRECTORY_SEPARATOR . self::ARCHIVE_FOLDER;
241 $this->initDirectory($this->archivePath);
242 }
243
244 /**
245 * Creates a directory if it doesn't exist
246 *
247 * @param string $directory
248 * @return bool
249 */
250 private function initDirectory($directory)
251 {
252 return @mkdir($directory, 0777, true);
253 }
254
255 /**
256 * Loads a given file into an array
257 *
258 * @param string $file
259 * @return array
260 */
261 private function getFileData($file)
262 {
263 // read the file contents and put it into an array we can use
264 $headers = null; // this is where we store the column names (the first line in a csv)
265 $data = []; // this is where we will store the csv data
266
267 // first, open the file
268 if ($handle = fopen($file, 'r')) {
269
270 // now loop through each row
271 while (($row = fgetcsv($handle)) !== false) {
272 // we should trim each element because the export has weird spacing
273 $row = array_map('trim', $row);
274
275 if (!$headers) {
276 // if we haven't set the header, that means this is the first row (where the headers are)
277 $headers = $row;
278
279 // the data is kind of garbage in that the last column has no header, so let's just remove that
280 array_pop($headers);
281 } else {
282 // otherwise, we're looking at data
283 if (count($row) > count($headers)) {
284 // combine the notes2 field with the notes field if it's there
285 $notes2 = array_pop($row);
286 $row[9] .= " | $notes2";
287 }
288
289 // use $header array as the keys for each cell by using array_combine
290 $data[] = array_combine($headers, $row);
291 }
292 }
293 // close the file, we're done
294 fclose($handle);
295 }
296
297 return $data;
298 }
299
300 /**
301 * Appends the date and time to a filename, then moves the file into the archive directory
302 *
303 * @param string $file
304 */
305 private function archive($file)
306 {
307 // we will move it into an archive folder, adding a timestamp to make sure the filename is unique
308 $newFilename = $this->archivePath . DIRECTORY_SEPARATOR . basename($file, ".php")
309 . date('Ymd-His') . '.csv';
310 rename($file, $newFilename);
311 }
312
313
314
315
316
317
318}