· 10 years ago · Apr 09, 2016, 09:12 AM
1<?php
2 session_start();
3 $allowedExts = array("xls", "xlsx");
4
5 include_once 'include/Config.php';
6
7 $servername = "localhost";
8 $username = "root";
9 $password = "";
10 $dbname = "stats";
11
12 // Create connection
13 $conn = new mysqli($servername, $username, $password, $dbname);
14 // Check connection
15 if ($conn->connect_error) {
16 die("Connection failed: " . $conn->connect_error);
17 } else {
18 echo 'connected<br/>';
19 }
20
21 $nextGameID =$conn->query("SELECT MAX(game_id) `max` FROM game");
22 if (!$nextGameID) die($conn->error);
23 while($row=mysqli_fetch_array($nextGameID, MYSQLI_ASSOC))
24 {
25 //echo $row['max'];
26 $gameID = $row['max'] + 1;
27 echo 'Game ID: ' . $gameID . '<br/>';
28 }
29
30 $nextStatID =$conn->query("SELECT MAX(stat_id) `max` FROM game_stats");
31 if (!$nextStatID) die($conn->error);
32 while($row=mysqli_fetch_array($nextStatID, MYSQLI_ASSOC))
33 {
34 //echo $row['max'];
35 $statID = $row['max'] + 1;
36 echo 'Stat ID: ' . $statID. '<br/>';
37 }
38
39 //print_r($_FILES["upload"]);
40
41 //echo inputCompID
42
43 $compID = $_POST['inputCompID'];
44
45 // Count # of uploaded files in array
46 $total = count($_FILES['upload']['name']);
47
48 // Loop through each file
49 for($i=0; $i<$total; $i++) {
50
51 $extension = end(explode(".", $_FILES["upload"]["name"][$i]));
52
53
54 if (($_FILES["upload"]["size"][$i] < 200000)
55 && in_array($extension, $allowedExts)) {
56 if ($_FILES["upload"]["error"][$i] > 0)
57 {
58 echo "Return Code: " . $_FILES["upload"]["error"][$i] . "<br />";
59 }
60 else
61 {
62
63
64
65 $path = $_FILES["upload"]["name"][$i];
66 $pathInfo = pathinfo($path);
67
68 //print_r($pathInfo);
69
70 echo "Upload: " . $_FILES["upload"]["name"][$i] . "<br />";
71 echo "Type: " . $_FILES["upload"]["type"][$i] . "<br />";
72 echo "Size: " . ($_FILES["upload"]["size"][$i] / 1024) . " Kb<br />";
73 echo "Temp file: " . $_FILES["upload"]["tmp_name"][$i] . "<br />";
74
75 $loc = "competitions/$compID/". $pathInfo['filename'] ."/". $_FILES["upload"]["name"][$i];
76
77 if (file_exists("competitions/$compID/". $pathInfo['filename'] ."/" . $_FILES["upload"]["name"][$i]))
78 {
79 echo $_FILES["upload"]["name"][$i] . " already exists. ";
80 }
81 else
82 {
83
84 // Check if directory exists if not create it
85 if(!is_dir("competitions/$compID/". $pathInfo['filename'] ."/")) {
86 mkdir("competitions/$compID/". $pathInfo['filename'] ."/", 0777, true);
87 }
88
89 move_uploaded_file($_FILES["upload"]["tmp_name"][$i],
90 "competitions/$compID/". $pathInfo['filename'] ."/". $_FILES["upload"]["name"][$i]);
91 echo "Stored in: " . "competitions/$compID/". $pathInfo['filename'] ."/". $_FILES["upload"]["name"][$i];
92 }
93
94 $team1Players = '';
95 $team2Players = '';
96
97 require_once 'Classes/PHPExcel/IOFactory.php';
98
99 $objReader = PHPExcel_IOFactory::createReader('Excel2007');
100 $objPHPExcel = $objReader->load($loc);
101
102 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
103 $objWriter->setDelimiter(";");
104 $objWriter->setEnclosure("");
105
106 $index = 0;
107 foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
108
109 $objPHPExcel->setActiveSheetIndex($index);
110
111 $objWorksheet = $objPHPExcel->getActiveSheet();
112 //objWorksheet = $objPHPExcel->getActiveSheet();
113 /* echo '<table border=1>' . "\n";
114 foreach ($objWorksheet->getRowIterator() as $row) {
115 echo '<tr>' . "\n";
116 $cellIterator = $row->getCellIterator();
117 $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells,
118 // even if it is not set.
119 // By default, only cells
120 // that are set will be
121 // iterated.
122 foreach ($cellIterator as $cell) {
123 echo '<td>' . $cell->getValue() . '</td>' . "\n";
124 }
125 echo '</tr>' . "\n";
126 }
127 echo '</table>' . "\n";*/
128
129 // write out each worksheet to it's name with CSV extension
130 $title = str_replace(array("-"," "), "-", $worksheet->getTitle());
131 $outFile = "competitions/$compID/". $pathInfo['filename'] ."/". $title . '-' . $pathInfo['filename'] . ".csv";
132
133 $objWriter->setSheetIndex($index);
134 $objWriter->save($outFile);
135
136
137 //$handle = fopen("competitions/". $pathInfo['filename'] ."/". $title . '-' . $pathInfo['filename'] . ".csv", "r");
138
139 //while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
140 //$import="INSERT into tablename(item1,item2,item3,item4,item5) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
141 //print_r($data);
142
143
144 //mysql_query($import) or die(mysql_error());
145 //}
146
147 //fclose($handle);
148
149 $row = 1;
150 if (($handle = fopen("competitions/$compID/". $pathInfo['filename'] ."/". $title . '-' . $pathInfo['filename'] . ".csv", "r")) !== FALSE) {
151 while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
152 $num = count($data);
153
154 //print_r($data);
155
156 if($title == 'General') {
157 //echo "<p> $num fields in line $row: <br /></p>\n";
158
159 //print_r($data);
160
161 if($row != 1) {
162
163 $map = $data[3];
164 $team1 = $data[9];
165 $team2 = $data[10];
166 $team1Score = $data[11];
167 $team2Score = $data[12];
168 $score = $data[11] . '-' . $data[12];
169
170 echo '<br/><br/>';
171 echo 'General Sheet:<br/>';
172 echo 'Map: ' . $map . '<br/>';
173 echo 'Team 1: ' . $team1. '<br/>';
174 echo 'Team 2: ' . $team2. '<br/>';
175 echo 'Score: ' . $score. '<br/>';
176 echo '<br/><br/>';
177
178 if($team1Score > $team2Score) {
179 $winningteam = 'team1';
180 $losingTeam = 'team2';
181 } else {
182 $winningteam = 'team2';
183 $losingTeam = 'team1';
184 }
185
186 }
187
188 } elseif($title == 'Players') {
189
190 //print_r($data);
191 if($row != 1) {
192
193 if($data[3] == $team1) {
194
195 $team1Players .= $data[0] . ', ';
196
197 } elseif($data[3] == $team2) {
198
199 $team2Players .= $data[0] . ', ';
200
201 }
202
203 }
204
205
206 }
207
208
209 $row++;
210
211 }
212
213 if($winningteam == 'team1') {
214 $winningTeamLineup = $team1Players;
215 $losingTeamLineup = $team2Players;
216 } elseif($winningteam == 'team2') {
217 $winningTeamLineup = $team2Players;
218 $losingTeamLineup = $team1Players;
219 }
220
221 fclose($handle);
222 }
223
224 $index++;
225 }
226
227 }
228 } else {
229 echo "Invalid file";
230 }
231
232 }
233
234
235
236
237
238 $sql = "INSERT INTO game (game_id, comp_id, map_name, start_time, end_time, finished, reason_not_finished, game_score, game_format, winning_teammembers, losing_teammembers)
239 VALUES ($gameID, $compID, '$map', 0, 0, 1, null, '$score', null, '$winningTeamLineup', '$losingTeamLineup')";
240
241 if ($conn->query($sql) === TRUE) {
242 echo "New record created successfully";
243 } else {
244 echo "Error: " . $sql . "<br>" . $conn->error;
245 }
246
247
248
249 /*$sql = "INSERT INTO game_stas (stat_id, game_id, steam_id, alias, FHteam, SHteam, rating, kills, deaths, 1k, 2k, 3k, 4k, ace, damage, rounds, shots, hits, accuracy_percent, headshots, headshot_percentage, kdr, fpr, dpr, clutchwon, clutch_percent, lastalive_percent)
250 VALUES ($nextID, $gameID, steamid, alias, FHteam, SHteam, rating, kills, deaths, 1k, 2k, 3k, 4k, ace, damage, rounds, shots, hits, accuracy_percent, headshots, headshot_percentage, kdr, fpr, dpr, clutchwon, clutch_percent, lastalive_percent)";
251
252 if ($conn->query($sql) === TRUE) {
253 echo "New record created successfully";
254 } else {
255 echo "Error: " . $sql . "<br>" . $conn->error;
256 }*/
257
258 // Loop through each file
259
260 for($i=0; $i<$total; $i++) {
261
262 $extension = end(explode(".", $_FILES["upload"]["name"][$i]));
263
264
265 if (($_FILES["upload"]["size"][$i] < 200000)
266 && in_array($extension, $allowedExts)) {
267 if ($_FILES["upload"]["error"][$i] > 0)
268 {
269 echo "Return Code: " . $_FILES["upload"]["error"][$i] . "<br />";
270 }
271 else
272 {
273
274
275
276 $path = $_FILES["upload"]["name"][$i];
277 $pathInfo = pathinfo($path);
278
279 $loc = "competitions/$compID/". $pathInfo['filename'] ."/". $_FILES["upload"]["name"][$i];
280
281 require_once 'Classes/PHPExcel/IOFactory.php';
282
283 $objReader = PHPExcel_IOFactory::createReader('Excel2007');
284 $objPHPExcel = $objReader->load($loc);
285
286 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
287 $objWriter->setDelimiter(";");
288 $objWriter->setEnclosure("");
289
290 $index = 0;
291 foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
292
293 $objPHPExcel->setActiveSheetIndex($index);
294 $objWorksheet = $objPHPExcel->getActiveSheet();
295
296 // write out each worksheet to it's name with CSV extension
297 $title = str_replace(array("-"," "), "-", $worksheet->getTitle());
298 $outFile = "competitions/$compID/". $pathInfo['filename'] ."/". $title . '-' . $pathInfo['filename'] . ".csv";
299
300 $objWriter->setSheetIndex($index);
301 $objWriter->save($outFile);
302
303 $row = 1;
304 if (($handle = fopen("competitions/$compID/". $pathInfo['filename'] ."/". $title . '-' . $pathInfo['filename'] . ".csv", "r")) !== FALSE) {
305 while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
306 $num = count($data);
307
308 //print_r($data);
309
310 if($title == 'Players') {
311
312 //print_r($data);
313 if($row != 1) {
314
315 $alias = $data[0];
316 $steamid = $data[1];
317 //echo 'FH Team'. '?'.'<br/>';
318 //echo 'SH Team: '. '?'.'<br/>';
319 $rating = $data[16];
320 $kills = $data[4];
321 $deaths = $data[5];
322 $onekill = $data[27];
323 $twokill = $data[26];
324 $threekill = $data[25];
325 $fourkill = $data[24];
326 $ace = $data[23];
327 $damageTotal = $data[21] + $data[22];
328 //echo 'Rounds: '. '?'.'<br/>';
329 //echo 'Shots: '. '?'.'<br/>';
330 //echo 'Shots: '. '?'.'<br/>';
331 //echo 'Accuracy Percent: '. '?'.'<br/>';
332 $headshots = $data[8];
333 $headshotPercent = $data[9];
334 $kdr = $data[7];
335 $fpr = $data[17];
336 $dpr = $data[19];
337 $clutchwon = $data[28] + $data[29] + $data[30] + $data[31] + $data[32];
338 //echo 'Clutch Percent: '. '?'.'<br/>';
339 //echo 'Last Alive Percent '. '?'.'<br/>';
340
341 echo 'Alias: '. $alias .'<br/>';
342 echo 'Steam ID'. $steamid .'<br/>';
343 echo 'FH Team'. '?'.'<br/>';
344 echo 'SH Team: '. '?'.'<br/>';
345 echo 'Rating: '. $rating .'<br/>';
346 echo 'Kills: '. $kills .'<br/>';
347 echo 'Deaths: '. $deaths .'<br/>';
348 echo '1k: '. $onekill .'<br/>';
349 echo '2k: '. $twokill .'<br/>';
350 echo '3k: '. $threekill .'<br/>';
351 echo '4k: '. $fourkill .'<br/>';
352 echo 'Ace: '. $ace .'<br/>';
353 echo 'Damage: '. $damageTotal .'<br/>';
354 echo 'Rounds: '. '?'.'<br/>';
355 echo 'Shots: '. '?'.'<br/>';
356 echo 'Shots: '. '?'.'<br/>';
357 echo 'Accuracy Percent: '. '?'.'<br/>';
358 echo 'Headshots: '. $headshots .'<br/>';
359 echo 'Headshot Percent: '. $headshotPercent .'<br/>';
360 echo 'KDR: '. $kdr .'<br/>';
361 echo 'FPR: '. $fpr .'<br/>';
362 echo 'DPR: '. $dpr .'<br/>';
363 echo 'Clutchwon: '. $clutchwon .'<br/>';
364 echo 'Clutch Percent: '. '?'.'<br/>';
365 echo 'Last Alive Percent '. '?'.'<br/>';
366 echo $statID++;
367
368
369 //stat_id, game_id, steam_id, alias, FHteam, SHteam, rating, kills, deaths, 1k, 2k, 3k, 4k, ace, damage, rounds, shots, hits, accuracy_percent, headshots, headshot_percentage, kdr, fpr, dpr, clutchwon, clutch_percent, lastalive_percent
370
371 $sql = "INSERT INTO game_stats (stat_id, game_id, steam_id, alias, FHteam, SHteam, rating, kills, deaths, 1k, 2k, 3k, 4k, ace, damage, rounds, shots, hits, accuracy_percent, headshots, headshot_percent, kdr, fpr, dpr, clutchwon, clutch_percent, lastalive_percent)
372 VALUES ($statID, $gameID, '$steamid', '$alias', 'FHteam', 'SHteam', $rating, $kills, $deaths, $onekill, $twokill, $threekill, $fourkill, $ace, $damageTotal, 'rounds', 'shots', 'hits', 'accuracy_percent', $headshots, $headshotPercent, $kdr, $fpr, $dpr, $clutchwon, 'clutch_percent', 'lastalive_percent')";
373
374 if ($conn->query($sql) === TRUE) {
375 echo "New record created successfully";
376 } else {
377 echo "Error: " . $sql . "<br>" . $conn->error;
378 }
379
380 }
381
382
383 }
384
385
386 $row++;
387
388 }
389
390 fclose($handle);
391 }
392
393 $index++;
394 }
395
396 }
397 } else {
398 echo "Invalid file";
399 }
400
401 }
402
403 $conn->close();
404
405
406
407
408?>