db.php — Event Tickets with Ticket Scanner 2.6.0

event-tickets-with-ticket-scanner / db.php

event-tickets-with-ticket-scanner Last commit date

db.php

318 lines

1	<?php
2	include_once(plugin_dir_path(__FILE__)."init_file.php");
3	class sasoEventticketsDB extends sasoEventtickets_DB {
4	public $dbversion = '1.7';
5	public function __construct($MAIN) {
6	$this->MAIN = $MAIN;
7	parent::$dbprefix = "saso_eventtickets_";
8	$this->_tabellen = ['lists', 'codes', 'ips', 'authtokens', 'errorlogs'];
9	$this->init();
10	}
11
12	protected function _system_installiereTabellen() {
13	$tabellen = [];
14	$tabellen[] = [
15	"sql"=>
16	"CREATE TABLE ".$this->getTabelle('lists')." (
17	id int(32) unsigned NOT NULL auto_increment,
18	time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
19	timezone varchar(255) NOT NULL DEFAULT '',
20	name varchar(255) NOT NULL DEFAULT '',
21	aktiv int(1) unsigned NOT NULL DEFAULT 0,
22	meta longtext NOT NULL DEFAULT '',
23	PRIMARY KEY (id)) ".$this->getCharsetCollate().";",
24	"additional"=>[
25	"CREATE UNIQUE INDEX idx1 ON ".$this->getTabelle('lists')." (name)"
26	]
27	];
28	$tabellen[] = [
29	"sql"=>
30	"CREATE TABLE ".$this->getTabelle('codes')." (
31	id int(32) unsigned NOT NULL auto_increment,
32	time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
33	timezone varchar(255) NOT NULL DEFAULT '',
34	code varchar(150) NOT NULL DEFAULT '',
35	code_display varchar(250) NOT NULL DEFAULT '',
36	cvv varchar(50) NOT NULL DEFAULT '',
37	meta longtext NOT NULL DEFAULT '',
38	aktiv int(1) unsigned NOT NULL DEFAULT 0,
39	redeemed int(1) unsigned NOT NULL DEFAULT 0,
40	list_id int(32) unsigned NOT NULL DEFAULT 0,
41	user_id int(32) unsigned NOT NULL DEFAULT 0,
42	order_id int(32) unsigned NOT NULL DEFAULT 0,
43	semaphorecode varchar(50) NOT NULL DEFAULT '',
44	PRIMARY KEY (id)) ".$this->getCharsetCollate().";",
45	"additional"=>[
46	"CREATE UNIQUE INDEX idx1 ON ".$this->getTabelle('codes')." (code)",
47	"CREATE INDEX idx2 ON ".$this->getTabelle('codes')." (time)",
48	"CREATE INDEX idx3 ON ".$this->getTabelle('codes')." (order_id)",
49	"CREATE INDEX idx4 ON ".$this->getTabelle('codes')." (user_id)",
50	"CREATE INDEX idx5 ON ".$this->getTabelle('codes')." (redeemed)"
51	]
52	];
53	$tabellen[] = [
54	"sql"=>
55	"CREATE TABLE ".$this->getTabelle('ips')." (
56	id int(32) unsigned NOT NULL auto_increment,
57	time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
58	timezone varchar(255) NOT NULL DEFAULT '',
59	code varchar(150) NOT NULL DEFAULT '',
60	valid int(1) NOT NULL DEFAULT 0,
61	ip varchar(40) NOT NULL DEFAULT '',
62	action varchar(150) NOT NULL DEFAULT 'Validation',
63	PRIMARY KEY (id)) ".$this->getCharsetCollate().";",
64	"additional"=>[
65	"CREATE INDEX idx1 ON ".$this->getTabelle('ips')." (code,time)",
66	"CREATE INDEX idx2 ON ".$this->getTabelle('ips')." (ip,time)"
67	]
68	];
69	$tabellen[] = [
70	"sql"=>
71	"CREATE TABLE ".$this->getTabelle('authtokens')." (
72	id int(32) unsigned NOT NULL auto_increment,
73	time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
74	timezone varchar(255) NOT NULL DEFAULT '',
75	changed datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
76	changed_timezone varchar(255) NOT NULL DEFAULT '',
77	name varchar(255) NOT NULL DEFAULT '',
78	aktiv int(1) unsigned NOT NULL DEFAULT 0,
79	code varchar(200) NOT NULL DEFAULT '',
80	areacode varchar(25) NOT NULL DEFAULT '',
81	user_id int(32) unsigned NOT NULL DEFAULT 0,
82	meta longtext NOT NULL DEFAULT '',
83	PRIMARY KEY (id)) ".$this->getCharsetCollate().";",
84	"additional"=>[
85	"CREATE UNIQUE INDEX idx1 ON ".$this->getTabelle('authtokens')." (code, areacode)"
86	]
87	];
88	$tabellen[] = [
89	"sql"=>
90	"CREATE TABLE ".$this->getTabelle('errorlogs')." (
91	id int(32) unsigned NOT NULL auto_increment,
92	time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
93	timezone varchar(255) NOT NULL DEFAULT '',
94	exception_msg varchar(250) NOT NULL DEFAULT '',
95	msg longtext NOT NULL DEFAULT '',
96	caller_name varchar(250) NOT NULL DEFAULT '',
97	PRIMARY KEY (id)) ".$this->getCharsetCollate().";",
98	"additional"=>[
99	"CREATE INDEX idx1 ON ".$this->getTabelle('ips')." (time)",
100	"CREATE INDEX idx2 ON ".$this->getTabelle('ips')." (caller_name)"
101	]
102	];
103	$tabellen = apply_filters( $this->MAIN->_add_filter_prefix.'db_system_installiereTabellen', $tabellen );
104	do_action( $this->MAIN->_do_action_prefix.'db_system_installiereTabellen', $tabellen );
105	return $tabellen;
106	}
107	}
108
109	class sasoEventtickets_DB {
110	// irgendwann nach dem https://codex.wordpress.org/Creating_Tables_with_Plugins
111	//https://tobier.de/wordpress-plugin-erstellen-datenbank/
112	public $dbversion;
113	protected static $dbprefix;
114	protected $_tabellen = [];
115	private $tabellen;
116	protected $callerValue = "basic";
117
118	protected $MAIN;
119
120	public function __construct($MAIN) {
121	$this->MAIN = $MAIN;
122	$this->init();
123	}
124	protected function init() {
125	$this->tabellen = [];
126	foreach($this->_tabellen as $t) {
127	$this->tabellen[$t] = $this->getPrefix().$t;
128	}
129	}
130
131	public function getTabelle($tabelle) {
132	return $this->tabellen[$tabelle];
133	}
134
135	private function getAdminSettings() {
136	return $this->MAIN->getAdmin();
137	}
138
139	public function getTables() {
140	return $this->_tabellen;
141	}
142
143	public function reinigen_in($text, $len=0, $addsl=1, $utf=0, $html=0) {
144	$text = trim($text);
145	if ($len > 0)
146	$text = substr($text, 0, $len);
147	if ($utf == 1)
148	$text = mb_convert_encoding($text, 'ISO-8859-1', 'UTF-8');
149	//$text = utf8_decode($text); // die zeichen sind utf kodiert
150	if ($html == 1)
151	$text = htmlentities($text); // zerstört HTML zeug im text
152	if ($addsl == 1)
153	$text = addslashes($text);
154	return $text;
155	}
156
157	private function getPrefix() {
158	global $wpdb;
159	return $wpdb->prefix . self::$dbprefix;
160	}
161
162	protected function getCharsetCollate() {
163	global $wpdb;
164	return $wpdb->get_charset_collate();
165	}
166
167	public function _db_datenholen_prepared($sql, $felder) {
168	global $wpdb;
169	for ($a=0;$a<count($felder);$a++) {
170	//$felder[$a] = $wpdb->esc_like( $felder[$a] );
171	$felder[$a] = $this->reinigen_in( $felder[$a] );
172	}
173	$sql = $wpdb->prepare( $sql, $felder );
174	return $this->_db_datenholen($sql);
175	}
176
177	public function _db_datenholen($sql, $again=true) {
178	global $wpdb;
179	//update_option( self::$dbprefix."db_version", "1.4" );
180	//$installed_ver = get_option( self::$dbprefix."db_version" );
181	//if ($installed_ver != $this->dbversion) $this->installiereTabellen();
182	//echo $installed_ver;
183	try {
184	$ret = $wpdb->get_results($sql, ARRAY_A);
185	} catch(Exception $e) {
186	if ($again == true) {
187	$this->installiereTabellen(true);
188	return $this->_db_datenholen($sql, false);
189	} else {
190	throw $e;
191	}
192	}
193	if ( $wpdb->last_error ) {
194	$this->MAIN->getAdmin()->logErrorToDB(new Exception("Database error - Again: ".$again), null, $wpdb->last_error);
195	if ($again == true) {
196	$this->installiereTabellen(true);
197	$ret = $this->_db_datenholen($sql, false);
198	}
199	}
200	return $ret;
201	}
202
203	public function _db_getRecordCountOfTable($tabelle, $where="") {
204	$sql = "select count(*) as anzahl from ".$this->getTabelle($tabelle);
205	if ($where != "") $sql .= " where ".$where;
206	list($d) = $this->_db_datenholen($sql);
207	return $d['anzahl'];
208	}
209
210	public function getCodesSize() {
211	return $this->_db_getRecordCountOfTable('codes');
212	}
213
214	private function addMissingFelder($felder) {
215	if (($this->callerValue == "basic" && version_compare( $this->dbversion, '1.6', '>' )) \|\|
216	($this->callerValue == "prem" && version_compare( $this->dbversion, '1.3', '>' ))) {
217	if (array_key_exists("time", $felder)) {
218	if (!array_key_exists("timezone", $felder)) {
219	$felder["timezone"] = wp_timezone_string();
220	}
221	}
222	if (array_key_exists("changed", $felder)) {
223	if (!array_key_exists("changed_timezone", $felder)) {
224	$felder["changed_timezone"] = wp_timezone_string();
225	}
226	}
227	}
228	$felder = apply_filters( $this->MAIN->_add_filter_prefix.'db_addMissingFelder', $felder );
229	return $felder;
230	}
231
232	public function insert($tabelle, $felder=[]) {
233	global $wpdb;
234	if (count($felder) == 0) throw new Exception("no fields provided");
235	$felder = $this->addMissingFelder($felder);
236	$wpdb->insert( $this->getTabelle($tabelle), $felder );
237	return $wpdb->insert_id;
238	}
239
240	public function update($tabelle, $felder, $where) {
241	global $wpdb;
242	if (count($felder) == 0) throw new Exception("no fields provided");
243	$felder = $this->addMissingFelder($felder);
244	if (count($where) == 0) throw new Exception("no where fields provided");
245	return $wpdb->update( $this->getTabelle($tabelle), $felder, $where);
246	}
247
248	public function _db_query($sql) {
249	global $wpdb;
250	$erg = $wpdb->query($sql);
251	if ($erg):
252	if (strtolower(substr($sql, 0, 6)) == "insert") {
253	return $wpdb->insert_id;
254	}
255	return $erg;
256	else:
257	if (!empty($wpdb->last_error)) {
258	$this->installiereTabellen(true);
259	$this->MAIN->getAdmin()->logErrorToDB(new Exception("Database error"), null, $wpdb->last_error);
260	echo $wpdb->last_error;
261	wp_die($wpdb->last_error);
262	}
263	endif;
264	return $erg;
265	}
266
267	public function installiereTabellen($force=false) {
268	global $wpdb;
269	if (empty($this->dbversion)) throw new Exception("dbversion is not set");
270	if (empty(self::$dbprefix)) throw new Exception("dbprefix is not set");
271
272	$installed_ver = get_option( self::$dbprefix."db_version" );
273
274	if ($force \|\| $installed_ver != $this->dbversion ) {
275	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
276
277	$tabellen = $this->_system_installiereTabellen(); // array
278	foreach($tabellen as $tabelle) {
279	dbDelta( $tabelle['sql'] ); // tabelle erstellen
280	if (isset($tabelle['additional'])) {
281	$wpdb->suppress_errors = true;
282	foreach($tabelle['additional'] as $sql) {
283	//echo $sql;
284	$wpdb->query($sql); // zusätzlich sql wie index
285	}
286	$wpdb->suppress_errors = false;
287	}
288	}
289
290	update_option( self::$dbprefix."db_version", $this->dbversion );
291	if ($this->callerValue == "basic") {
292	$this->getAdminSettings()->performJobsAfterDBUpgraded($this->dbversion, $installed_ver);
293	} else { // wenn für die prem DB dann direkt aufruf
294	if ($this->MAIN->isPremium() && method_exists($this->MAIN->getPremiumFunctions(), 'performJobsAfterPremDBUpgraded')) {
295	$this->MAIN->getPremiumFunctions()->performJobsAfterPremDBUpgraded($this->dbversion, $installed_ver);
296	}
297	}
298	}
299	}
300	public static function plugin_deactivated() {
301	//delete_option(self::$dbprefix."db_version");
302	}
303	public static function plugin_uninstall(){
304	self::plugin_deactivated();
305	//delete tabellen
306	/*
307	global $wpdb;
308	foreach($this->tabellen as $key => $value) {
309	$wpdb->query("DROP TABLE IF EXISTS ".$value);
310	}
311	*/
312	}
313	protected function _system_installiereTabellen()
314	{
315	throw new Exception("overwrite this function");
316	}
317	}
318	?>