· 10 years ago · Feb 06, 2016, 11:27 PM
1<?php
2//----------------------------------------------------------------------------------
3// class DBWrapper
4//----------------------------------------------------------------------------------
5class Wrapper
6{
7 private $db;
8 private $username;
9 private $password;
10 private $host;
11 private $port;
12 private $dbname;
13
14 private $in_transaction;
15
16 public function __construct($a_username, $a_password, $a_host, $a_dbname, $a_port=null)
17 {
18 $this->username = $a_username;
19 $this->password = $a_password;
20 $this->host = $a_host;
21 $this->dbname = $a_dbname;
22 $this->port = $a_port;
23 $this->in_transaction = false;
24 }
25
26 public function close()
27 {
28 $this->db = null;
29 }
30
31 public function beginTransaction()
32 {
33 if(!$this->in_transaction)
34 {
35 $this->in_transaction= true;
36 $this->db = $this->getDatabase();
37 $this->db->beginTransaction();
38 }
39 }
40
41 public function commit()
42 {
43 if($this->in_transaction)
44 {
45 $this->in_transaction = false;
46 $this->db = $this->getDatabase();
47 $this->db->commit();
48 }
49 }
50
51 public function rollBack()
52 {
53 if($this->in_transaction)
54 {
55 $this->in_transaction = false;
56 $this->db = $this->getDatabase();
57 $this->db->rollBack();
58 }
59 }
60
61 public function inTransaction()
62 {
63 return $this->in_transaction;
64 }
65
66 public function getLastInsertId()
67 {
68 $this->db = $this->GetDatabase();
69 return $this->db->lastInsertId();
70 }
71
72 private function getDatabase()
73 {
74 if ($this->db === null) {
75 try {
76 $port_string="";
77 if(isset($this->port) && !empty($this->port))
78 $port_string=";port=".$this->port;
79 $this->db = new PDO("mysql:host=".$this->host.$port_string.";dbname=".$this->dbname.";charset=utf8", $this->username, $this->password);
80 $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
81 $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
82 $this->db->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
83 $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
84 }
85 catch(PDOException $e) {
86 die ('Unable to connect to Database!');
87 }
88 }
89 return $this->db;
90 }
91
92 public function newOptimization($session, $optimize_run, $created, $monster_id,& $data)
93 {
94 $step = 10000;
95 $total = count($data);
96 $i = 0;
97 $rowCount = 0;
98 while($i<$total) {
99 try {
100 $values = "";
101 $i1 = 0;
102 while( $i + $i1 < $total && $i1 < $step) {
103 $index = $i + $i1;
104 if(!empty($values)) {
105 $values .= ",";
106 }
107 $values .= "('".$session."','".$optimize_run."',NOW(),'".$monster_id."','".$data[$index]["rune_ids"]."','".$data[$index]["sets"]."',".$data[$index]["a_hp"].",".$data[$index]["a_atk"].",".$data[$index]["a_def"].",".$data[$index]["a_spd"].",".$data[$index]["a_crate"].",".$data[$index]["a_cdmg"].",".$data[$index]["a_res"].",".$data[$index]["a_acc"].",".$data[$index]["m_hp"].",".$data[$index]["m_atk"].",".$data[$index]["m_def"].",".$data[$index]["m_spd"].",".$data[$index]["m_crate"].",".$data[$index]["m_cdmg"].",".$data[$index]["m_res"].",".$data[$index]["m_acc"].",'".$data[$index]["slots246"]."','".$data[$index]["substat_skillups"]."','".$data[$index]["a_dps"]."','".$data[$index]["m_dps"]."','".$data[$index]["a_effhp"]."','".$data[$index]["a_effhp_d"]."','".$data[$index]["m_effhp"]."','".$data[$index]["m_effhp_d"]."')";
108 $i1 += 1;
109 }
110 $this->db = $this->getDatabase();
111 $count = $this->db->exec(
112 'INSERT INTO sw_optimizer
113 (`session`, `optimize_run`, `created`, `monster_id`, `rune_ids`, `sets`, `a_hp`, `a_atk`, `a_def`, `a_spd`, `a_crate`, `a_cdmg`, `a_res`, `a_acc`, `m_hp`, `m_atk`, `m_def`, `m_spd`, `m_crate`, `m_cdmg`, `m_res`, `m_acc`, `slots246`, `substat_skillups`, `a_dps`, `m_dps`,`a_effhp`,`a_effhp_d`,`m_effhp`,`m_effhp_d`)
114 VALUES '.$values);
115 $rowCount = $rowCount + $count;
116 }
117 catch(PDOException $e) {
118 //$e->getMessage();
119 return $rowCount;
120 }
121 $i = $i + $i1;
122 }
123 return $rowCount;
124 }
125
126 public function Maintenance()
127 {
128 try {
129
130 $this->db = $this->getDatabase();
131 $statm = $this->db->prepare(
132 'OPTIMIZE TABLE `sw_optimizer` ');
133 $statm->execute();
134 return $statm->fetchAll();
135 }
136 catch(PDOException $e) {
137 //$e->getMessage();
138 }
139 return array();
140 }
141
142 public function deleteOld($minutes)
143 {
144 try {
145 $this->db = $this->getDatabase();
146 $statm = $this->db->prepare(
147 "DELETE FROM sw_optimizer
148 WHERE created < DATE_SUB(NOW(), INTERVAL ".$minutes." MINUTE)
149 LIMIT 10000 ");
150 $cnt = 1;
151 while($cnt > 0) {
152 $statm->execute();
153 $cnt = $statm->rowCount();
154 }
155 return $statm->rowCount();
156 }
157 catch(PDOException $e) {
158 //$e->getMessage();
159 }
160 return 0;
161 }
162
163
164 public function deleteBySession($session, $optimize_run)
165 {
166 try {
167 $this->db = $this->getDatabase();
168 $statm = $this->db->prepare(
169 "DELETE FROM sw_optimizer
170 WHERE session = ? AND optimize_run <> ?
171 LIMIT 10000 ");
172 $cnt = 1;
173 while($cnt > 0) {
174 $statm->execute(array($session, $optimize_run));
175 $cnt = $statm->rowCount();
176 }
177 return $statm->rowCount();
178 }
179 catch(PDOException $e) {
180 //$e->getMessage();
181 }
182 return 0;
183 }
184
185 public function getOptimization($session, $optimize_run, $post, $search, $orderBy, $orderWay, $start, $count, $count_only=false)
186 {
187 if( $orderWay != "asc" && $orderWay != "desc" && $orderWay != "ASC" && $orderWay != "DESC") {
188 if($count_only){
189 return array( array("cnt"=>0));
190 }
191 else {
192 return array();
193 }
194 }
195
196 $cond = "";
197 $limit = "";
198 $select = "";
199 $orderby = "";
200 $params[] = $session;
201 $params[] = $optimize_run;
202 $filter_type = "a";
203 if( !empty($post["filter_type_div"]) && $post["filter_type_div"] == "m") {
204 $filter_type = "m";
205 }
206
207 if( !empty($post["filter_min_hp"]) )
208 {
209 $cond .= " AND ".$filter_type."_hp >= ? ";
210 $params[] = $post["filter_min_hp"];
211 }
212 if( !empty($post["filter_max_hp"]) )
213 {
214 $cond .= " AND ".$filter_type."_hp <= ? ";
215 $params[] = $post["filter_max_hp"];
216 }
217 if( !empty($post["filter_min_atk"]) )
218 {
219 $cond .= " AND ".$filter_type."_atk >= ? ";
220 $params[] = $post["filter_min_atk"];
221 }
222 if( !empty($post["filter_max_atk"]) )
223 {
224 $cond .= " AND ".$filter_type."_atk <= ? ";
225 $params[] = $post["filter_max_atk"];
226 }
227 if( !empty($post["filter_min_def"]) )
228 {
229 $cond .= " AND ".$filter_type."_def >= ? ";
230 $params[] = $post["filter_min_def"];
231 }
232 if( !empty($post["filter_max_def"]) )
233 {
234 $cond .= " AND ".$filter_type."_def <= ? ";
235 $params[] = $post["filter_max_def"];
236 }
237 if( !empty($post["filter_min_spd"]) )
238 {
239 $cond .= " AND ".$filter_type."_spd >= ? ";
240 $params[] = $post["filter_min_spd"];
241 }
242 if( !empty($post["filter_max_spd"]) )
243 {
244 $cond .= " AND ".$filter_type."_spd <= ? ";
245 $params[] = $post["filter_max_spd"];
246 }
247 if( !empty($post["filter_min_crate"]) )
248 {
249 $cond .= " AND ".$filter_type."_crate >= ? ";
250 $params[] = $post["filter_min_crate"];
251 }
252 if( !empty($post["filter_max_crate"]) )
253 {
254 $cond .= " AND ".$filter_type."_crate <= ? ";
255 $params[] = $post["filter_max_crate"];
256 }
257 if( !empty($post["filter_min_cdmg"]) )
258 {
259 $cond .= " AND ".$filter_type."_cdmg >= ? ";
260 $params[] = $post["filter_min_cdmg"];
261 }
262 if( !empty($post["filter_max_cdmg"]) )
263 {
264 $cond .= " AND ".$filter_type."_cdmg <= ? ";
265 $params[] = $post["filter_max_cdmg"];
266 }
267 if( !empty($post["filter_min_res"]) )
268 {
269 $cond .= " AND ".$filter_type."_res >= ? ";
270 $params[] = $post["filter_min_res"];
271 }
272 if( !empty($post["filter_max_res"]) )
273 {
274 $cond .= " AND ".$filter_type."_res <= ? ";
275 $params[] = $post["filter_max_res"];
276 }
277 if( !empty($post["filter_min_acc"]) )
278 {
279 $cond .= " AND ".$filter_type."_acc >= ? ";
280 $params[] = $post["filter_min_acc"];
281 }
282 if( !empty($post["filter_max_acc"]) )
283 {
284 $cond .= " AND ".$filter_type."_acc <= ? ";
285 $params[] = $post["filter_max_acc"];
286 }
287 if( !empty($search) )
288 {
289 $cond .= " AND ( rune_ids like CONCAT('%',?,'%') OR UPPER(sets) like CONCAT('%',?,'%') OR UPPER(slots246) like CONCAT('%',?,'%') ) ";
290 $escaped_upper = strtoupper($search);
291 $params[] = $escaped_upper;
292 $params[] = $escaped_upper;
293 $params[] = $escaped_upper;
294 }
295
296
297 if($count_only)
298 {
299 $select = "COUNT(*) AS cnt";
300 }
301 else
302 {
303 $select = "rune_ids, sets, `a_hp`, `a_atk`, `a_def`, `a_spd`, `a_crate`, `a_cdmg`, `a_res`, `a_acc`,`a_dps`,`a_effhp`,`a_effhp_d`, `m_hp`, `m_atk`, `m_def`, `m_spd`, `m_crate`, `m_cdmg`, `m_res`, `m_acc`, `m_dps`,`m_effhp`,`m_effhp_d`, `slots246`, `substat_skillups`, `monster_id` ";
304
305 $orderby = "ORDER BY ".$orderBy." ".$orderWay;
306 if( !empty($start) && !empty($count))
307 {
308 $limit = " LIMIT ".$start.",".$count;
309 }
310 else if( empty($start) && !empty($count))
311 {
312 $limit = " LIMIT ".$count;
313 }
314 }
315
316 try {
317 $this->db = $this->getDatabase();
318 $statm = $this->db->prepare(
319 "SELECT ".$select."
320 FROM sw_optimizer
321 WHERE session = ? AND optimize_run = ? ".$cond." ".$orderby." ".$limit);
322 $statm->execute($params);
323
324 return $statm->fetchAll();
325 }
326 catch(PDOException $e) {
327 //echo $e->getMessage();
328 }
329 return array();
330 }
331
332 public function getTotalRecords()
333 {
334 try {
335 $this->db = $this->getDatabase();
336 $statm = $this->db->prepare(
337 "SELECT COUNT(*) AS cnt
338 FROM sw_optimizer ");
339 $statm->execute();
340
341 return $statm->fetchAll();
342 }
343 catch(PDOException $e) {
344 //echo $e->getMessage();
345 }
346 return array();
347 }
348
349 public function getUniqueSessions()
350 {
351 try {
352 $this->db = $this->getDatabase();
353 $statm = $this->db->prepare(
354 "SELECT COUNT(*) as cnt from
355 (SELECT DISTINCT session
356 FROM sw_optimizer
357 )
358 ");
359 $statm->execute();
360
361 return $statm->fetchAll();
362 }
363 catch(PDOException $e) {
364 //echo $e->getMessage();
365 }
366 return array();
367 }
368
369 public function getSessionsAndRecords()
370 {
371 try {
372 $this->db = $this->getDatabase();
373 $statm = $this->db->prepare(
374 "SELECT session, count(*) as cnt
375 FROM sw_optimizer
376 GROUP BY session
377 ORDER BY cnt desc");
378 $statm->execute();
379
380 return $statm->fetchAll();
381 }
382 catch(PDOException $e) {
383 //echo $e->getMessage();
384 }
385 return array();
386 }
387
388 public function createNewOptimizerTableCopyData($minutes, $table)
389 {
390 try {
391 $this->db = $this->getDatabase();
392 $statm = $this->db->prepare(
393 "CREATE TABLE IF NOT EXISTS ".$table." (
394 `session` varchar(20) COLLATE utf8_bin NOT NULL,
395 `optimize_run` varchar(3) COLLATE utf8_bin NOT NULL,
396 `created` datetime NOT NULL,
397 `rune_ids` varchar(50) COLLATE utf8_bin NOT NULL,
398 `sets` varchar(50) COLLATE utf8_bin DEFAULT NULL,
399 `a_hp` int(11) NOT NULL,
400 `a_atk` int(11) NOT NULL,
401 `a_def` int(11) NOT NULL,
402 `a_spd` int(11) NOT NULL,
403 `a_crate` int(11) NOT NULL,
404 `a_cdmg` int(11) NOT NULL,
405 `a_res` int(11) NOT NULL,
406 `a_acc` int(11) NOT NULL,
407 `a_dps` int(11) NOT NULL,
408 `m_hp` int(11) NOT NULL,
409 `m_atk` int(11) NOT NULL,
410 `m_def` int(11) NOT NULL,
411 `m_spd` int(11) NOT NULL,
412 `m_crate` int(11) NOT NULL,
413 `m_cdmg` int(11) NOT NULL,
414 `m_res` int(11) NOT NULL,
415 `m_acc` int(11) NOT NULL,
416 `m_dps` int(11) NOT NULL,
417 `slots246` varchar(50) COLLATE utf8_bin NOT NULL,
418 `substat_skillups` int(11) DEFAULT NULL,
419 `monster_id` int(11) NOT NULL,
420 KEY `session_run` (`session`,`optimize_run`),
421 KEY `sets` (`sets`),
422 KEY `slots246` (`slots246`)
423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
424 AS SELECT * from sw_optimizer WHERE created >= DATE_SUB(NOW(), INTERVAL ".$minutes." MINUTE) ");
425 $statm->execute();
426
427 return $statm->fetchAll();
428 }
429 catch(PDOException $e) {
430 //echo $e->getMessage();
431 }
432 return array();
433 }
434
435 public function createNewOptimizerTable($table)
436 {
437 try {
438 $this->db = $this->getDatabase();
439 $statm = $this->db->prepare(
440 "CREATE TABLE IF NOT EXISTS ".$table." (
441 `id` int(11) NOT NULL AUTO_INCREMENT,
442 `session` varchar(20) COLLATE utf8_bin NOT NULL,
443 `optimize_run` varchar(3) COLLATE utf8_bin NOT NULL,
444 `created` datetime NOT NULL,
445 `rune_ids` varchar(50) COLLATE utf8_bin NOT NULL,
446 `sets` varchar(50) COLLATE utf8_bin DEFAULT NULL,
447 `a_hp` int(11) NOT NULL,
448 `a_atk` int(11) NOT NULL,
449 `a_def` int(11) NOT NULL,
450 `a_spd` int(11) NOT NULL,
451 `a_crate` int(11) NOT NULL,
452 `a_cdmg` int(11) NOT NULL,
453 `a_res` int(11) NOT NULL,
454 `a_acc` int(11) NOT NULL,
455 `a_dps` int(11) NOT NULL,
456 `a_effhp` int(11) NOT NULL,
457 `a_effhp_d` int(11) NOT NULL,
458 `m_hp` int(11) NOT NULL,
459 `m_atk` int(11) NOT NULL,
460 `m_def` int(11) NOT NULL,
461 `m_spd` int(11) NOT NULL,
462 `m_crate` int(11) NOT NULL,
463 `m_cdmg` int(11) NOT NULL,
464 `m_res` int(11) NOT NULL,
465 `m_acc` int(11) NOT NULL,
466 `m_dps` int(11) NOT NULL,
467 `m_effhp` int(11) NOT NULL,
468 `m_effhp_d` int(11) NOT NULL,
469 `slots246` varchar(50) COLLATE utf8_bin NOT NULL,
470 `substat_skillups` int(11) DEFAULT NULL,
471 `monster_id` int(11) NOT NULL,
472 PRIMARY KEY (`id`),
473 KEY `session_run` (`session`,`optimize_run`),
474 KEY `sets` (`sets`),
475 KEY `slots246` (`slots246`)
476 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ");
477 $statm->execute();
478
479 return $statm->fetchAll();
480 }
481 catch(PDOException $e) {
482 //echo $e->getMessage();
483 }
484 return array();
485 }
486
487 public function dropTable($table)
488 {
489 try {
490 $this->db = $this->getDatabase();
491 $statm = $this->db->prepare(
492 "DROP TABLE ".$table);
493 $statm->execute();
494
495 return $statm->fetchAll();
496 }
497 catch(PDOException $e) {
498 //echo $e->getMessage();
499 }
500 return array();
501 }
502
503 public function renameTable($old_name, $new_name)
504 {
505 try {
506 $this->db = $this->getDatabase();
507 $statm = $this->db->prepare(
508 "RENAME TABLE ".$old_name." TO ".$new_name);
509 $statm->execute();
510
511 return $statm->fetchAll();
512 }
513 catch(PDOException $e) {
514 //echo $e->getMessage();
515 }
516 return array();
517 }
518
519}
520?>