db.php — Event Tickets with Ticket Scanner 2.9.3

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

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

db.php

414 lines

1	<?php
2	include_once(plugin_dir_path(__FILE__)."init_file.php");
3	class sasoEventticketsDB extends sasoEventtickets_DB {
4	public $dbversion = '1.10';
5	public function __construct($MAIN) {
6	$this->MAIN = $MAIN;
7	parent::$dbprefix = "saso_eventtickets_";
8	$this->_tabellen = ['lists', 'codes', 'ips', 'authtokens', 'errorlogs', 'seatingplans', 'seats', 'seat_blocks'];
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('errorlogs')." (time)",
100	"CREATE INDEX idx2 ON ".$this->getTabelle('errorlogs')." (caller_name)"
101	]
102	];
103	// Seating Plans - v1.8, extended v1.9 for Visual Designer
104	$tabellen[] = [
105	"sql"=>
106	"CREATE TABLE ".$this->getTabelle('seatingplans')." (
107	id int(32) unsigned NOT NULL auto_increment,
108	time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
109	timezone varchar(255) NOT NULL DEFAULT '',
110	name varchar(255) NOT NULL DEFAULT '',
111	aktiv int(1) unsigned NOT NULL DEFAULT 0,
112	meta longtext NOT NULL DEFAULT '',
113	layout_type varchar(20) NOT NULL DEFAULT 'simple',
114	meta_draft longtext NOT NULL DEFAULT '',
115	meta_published longtext NOT NULL DEFAULT '',
116	published_at datetime DEFAULT NULL,
117	published_by int(32) unsigned DEFAULT NULL,
118	created_by int(32) unsigned DEFAULT NULL,
119	updated_by int(32) unsigned DEFAULT NULL,
120	created_at datetime DEFAULT NULL,
121	updated_at datetime DEFAULT NULL,
122	PRIMARY KEY (id)) ".$this->getCharsetCollate().";",
123	"additional"=>[
124	"CREATE UNIQUE INDEX idx1 ON ".$this->getTabelle('seatingplans')." (name)"
125	]
126	];
127	// Seats - v1.8, extended v1.9 for Visual Designer
128	$tabellen[] = [
129	"sql"=>
130	"CREATE TABLE ".$this->getTabelle('seats')." (
131	id int(32) unsigned NOT NULL auto_increment,
132	time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
133	timezone varchar(255) NOT NULL DEFAULT '',
134	seatingplan_id int(32) unsigned NOT NULL DEFAULT 0,
135	seat_identifier varchar(100) NOT NULL DEFAULT '',
136	aktiv int(1) unsigned NOT NULL DEFAULT 1,
137	sort_order int(32) unsigned NOT NULL DEFAULT 0,
138	meta longtext NOT NULL DEFAULT '',
139	is_deleted tinyint(1) unsigned NOT NULL DEFAULT 0,
140	deleted_at datetime DEFAULT NULL,
141	deleted_by int(32) unsigned DEFAULT NULL,
142	created_by int(32) unsigned DEFAULT NULL,
143	updated_by int(32) unsigned DEFAULT NULL,
144	created_at datetime DEFAULT NULL,
145	updated_at datetime DEFAULT NULL,
146	PRIMARY KEY (id)) ".$this->getCharsetCollate().";",
147	"additional"=>[
148	"CREATE INDEX idx1 ON ".$this->getTabelle('seats')." (seatingplan_id, aktiv, sort_order)",
149	"CREATE UNIQUE INDEX idx2 ON ".$this->getTabelle('seats')." (seatingplan_id, seat_identifier)",
150	"CREATE INDEX idx3 ON ".$this->getTabelle('seats')." (seatingplan_id, is_deleted)"
151	]
152	];
153	// Seat Blocks (Semaphore) - v1.8
154	$tabellen[] = [
155	"sql"=>
156	"CREATE TABLE ".$this->getTabelle('seat_blocks')." (
157	id int(32) unsigned NOT NULL auto_increment,
158	time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
159	timezone varchar(255) NOT NULL DEFAULT '',
160	seat_id int(32) unsigned NOT NULL DEFAULT 0,
161	seatingplan_id int(32) unsigned NOT NULL DEFAULT 0,
162	product_id int(32) unsigned NOT NULL DEFAULT 0,
163	event_date date DEFAULT NULL,
164	session_id varchar(100) NOT NULL DEFAULT '',
165	order_id int(32) unsigned DEFAULT NULL,
166	code_id int(32) unsigned DEFAULT NULL,
167	expires_at datetime DEFAULT NULL,
168	last_seen datetime DEFAULT NULL,
169	status varchar(20) NOT NULL DEFAULT 'blocked',
170	meta longtext NOT NULL DEFAULT '',
171	PRIMARY KEY (id)) ".$this->getCharsetCollate().";",
172	"additional"=>[
173	"CREATE INDEX idx1 ON ".$this->getTabelle('seat_blocks')." (seatingplan_id, event_date, status)",
174	"CREATE INDEX idx2 ON ".$this->getTabelle('seat_blocks')." (seat_id, product_id, event_date, status)",
175	"CREATE INDEX idx3 ON ".$this->getTabelle('seat_blocks')." (status, expires_at)",
176	"CREATE INDEX idx4 ON ".$this->getTabelle('seat_blocks')." (session_id)",
177	"CREATE INDEX idx5 ON ".$this->getTabelle('seat_blocks')." (order_id)",
178	"CREATE INDEX idx6 ON ".$this->getTabelle('seat_blocks')." (code_id)"
179	]
180	];
181	$tabellen = apply_filters( $this->MAIN->_add_filter_prefix.'db_system_installiereTabellen', $tabellen );
182	do_action( $this->MAIN->_do_action_prefix.'db_system_installiereTabellen', $tabellen );
183	return $tabellen;
184	}
185	}
186
187	class sasoEventtickets_DB {
188	// irgendwann nach dem https://codex.wordpress.org/Creating_Tables_with_Plugins
189	//https://tobier.de/wordpress-plugin-erstellen-datenbank/
190	public $dbversion;
191	protected static $dbprefix;
192	protected $_tabellen = [];
193	private $tabellen;
194	protected $callerValue = "basic";
195
196	protected $MAIN;
197
198	public function __construct($MAIN) {
199	$this->MAIN = $MAIN;
200	$this->init();
201	}
202	protected function init() {
203	$this->tabellen = [];
204	foreach($this->_tabellen as $t) {
205	$this->tabellen[$t] = $this->getPrefix().$t;
206	}
207	}
208
209	public function getTabelle($tabelle) {
210	return $this->tabellen[$tabelle];
211	}
212
213	public function getTables() {
214	return $this->_tabellen;
215	}
216
217	public function reinigen_in($text, $len=0, $addsl=1, $utf=0, $html=0) {
218	$text = trim($text);
219	if ($len > 0)
220	$text = substr($text, 0, $len);
221	if ($utf == 1)
222	$text = mb_convert_encoding($text, 'ISO-8859-1', 'UTF-8');
223	//$text = utf8_decode($text); // die zeichen sind utf kodiert
224	if ($html == 1)
225	$text = htmlentities($text); // zerstört HTML zeug im text
226	if ($addsl == 1)
227	$text = addslashes($text);
228	return $text;
229	}
230
231	private function getPrefix() {
232	global $wpdb;
233	return $wpdb->prefix . self::$dbprefix;
234	}
235
236	protected function getCharsetCollate() {
237	global $wpdb;
238	return $wpdb->get_charset_collate();
239	}
240
241	public function _db_datenholen_prepared($sql, $felder) {
242	global $wpdb;
243	for ($a=0;$a<count($felder);$a++) {
244	//$felder[$a] = $wpdb->esc_like( $felder[$a] );
245	$felder[$a] = $this->reinigen_in( $felder[$a] );
246	}
247	$sql = $wpdb->prepare( $sql, $felder );
248	return $this->_db_datenholen($sql);
249	}
250
251	public function _db_datenholen($sql, $again=true) {
252	global $wpdb;
253	//update_option( self::$dbprefix."db_version", "1.4" );
254	//$installed_ver = get_option( self::$dbprefix."db_version" );
255	//if ($installed_ver != $this->dbversion) $this->installiereTabellen();
256	//echo $installed_ver;
257	try {
258	$ret = $wpdb->get_results($sql, ARRAY_A);
259	} catch(Exception $e) {
260	if ($again == true) {
261	$this->installiereTabellen(true);
262	return $this->_db_datenholen($sql, false);
263	} else {
264	throw $e;
265	}
266	}
267	if ( $wpdb->last_error ) {
268	$this->MAIN->getAdmin()->logErrorToDB(new Exception("Database error - Again: ".$again), null, $wpdb->last_error);
269	if ($again == true) {
270	$this->installiereTabellen(true);
271	$ret = $this->_db_datenholen($sql, false);
272	}
273	}
274	return $ret;
275	}
276
277	public function _db_getRecordCountOfTable($tabelle, $where="") {
278	$sql = "select count(*) as anzahl from ".$this->getTabelle($tabelle);
279	if ($where != "") $sql .= " where ".$where;
280	list($d) = $this->_db_datenholen($sql);
281	return $d['anzahl'];
282	}
283
284	public function getCodesSize() {
285	return $this->_db_getRecordCountOfTable('codes');
286	}
287
288	private function addMissingFelder($felder) {
289	if (($this->callerValue == "basic" && version_compare( $this->dbversion, '1.6', '>' )) \|\|
290	($this->callerValue == "prem" && version_compare( $this->dbversion, '1.3', '>' ))) {
291	if (array_key_exists("time", $felder)) {
292	if (!array_key_exists("timezone", $felder)) {
293	$felder["timezone"] = wp_timezone_string();
294	}
295	}
296	if (array_key_exists("changed", $felder)) {
297	if (!array_key_exists("changed_timezone", $felder)) {
298	$felder["changed_timezone"] = wp_timezone_string();
299	}
300	}
301	}
302	$felder = apply_filters( $this->MAIN->_add_filter_prefix.'db_addMissingFelder', $felder );
303	return $felder;
304	}
305
306	public function insert($tabelle, $felder=[]) {
307	global $wpdb;
308	if (count($felder) == 0) throw new Exception("no fields provided");
309	$felder = $this->addMissingFelder($felder);
310	$wpdb->insert( $this->getTabelle($tabelle), $felder );
311	return $wpdb->insert_id;
312	}
313
314	public function update($tabelle, $felder, $where) {
315	global $wpdb;
316	if (count($felder) == 0) throw new Exception("no fields provided");
317	$felder = $this->addMissingFelder($felder);
318	if (count($where) == 0) throw new Exception("no where fields provided");
319	return $wpdb->update( $this->getTabelle($tabelle), $felder, $where);
320	}
321
322	public function _db_query($sql) {
323	global $wpdb;
324	$erg = $wpdb->query($sql);
325	if ($erg):
326	if (strtolower(substr($sql, 0, 6)) == "insert") {
327	return $wpdb->insert_id;
328	}
329	return $erg;
330	else:
331	if (!empty($wpdb->last_error)) {
332	$this->installiereTabellen(true);
333	$this->MAIN->getAdmin()->logErrorToDB(new Exception("Database error"), null, $wpdb->last_error);
334	echo $wpdb->last_error;
335	wp_die($wpdb->last_error);
336	}
337	endif;
338	return $erg;
339	}
340
341	public function installiereTabellen($force=false) {
342	global $wpdb;
343	if (empty($this->dbversion)) throw new Exception("dbversion is not set");
344	if (empty(self::$dbprefix)) throw new Exception("dbprefix is not set");
345
346	$installed_ver = get_option( self::$dbprefix."db_version" );
347
348	if ($force \|\| $installed_ver != $this->dbversion ) {
349	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
350
351	$tabellen = $this->_system_installiereTabellen(); // array
352	foreach($tabellen as $tabelle) {
353	dbDelta( $tabelle['sql'] ); // tabelle erstellen
354	if (isset($tabelle['additional'])) {
355	$wpdb->suppress_errors = true;
356	foreach($tabelle['additional'] as $sql) {
357	//echo $sql;
358	$wpdb->query($sql); // zusätzlich sql wie index
359	}
360	$wpdb->suppress_errors = false;
361	}
362	}
363
364	update_option( self::$dbprefix."db_version", $this->dbversion );
365	if ($this->callerValue == "basic") {
366	$this->MAIN->getAdmin()->performJobsAfterDBUpgraded($this->dbversion, $installed_ver);
367	} else { // wenn für die prem DB dann direkt aufruf
368	if ($this->MAIN->isPremium() && method_exists($this->MAIN->getPremiumFunctions(), 'performJobsAfterPremDBUpgraded')) {
369	$this->MAIN->getPremiumFunctions()->performJobsAfterPremDBUpgraded($this->dbversion, $installed_ver);
370	}
371	}
372	}
373	}
374	public static function plugin_deactivated() {
375	//delete_option(self::$dbprefix."db_version");
376	}
377	public static function plugin_uninstall(){
378	self::plugin_deactivated();
379	//delete tabellen
380	/*
381	global $wpdb;
382	foreach($this->tabellen as $key => $value) {
383	$wpdb->query("DROP TABLE IF EXISTS ".$value);
384	}
385	*/
386	}
387	/**
388	* Log an error message to the errorlogs table.
389	*
390	* Convenience method used by seating subsystem and other components
391	* that need simple string-based error logging without an Exception object.
392	*
393	* @param string $message Error message
394	* @param string $callerName Optional caller identification
395	*/
396	public function logError(string $message, string $callerName = ''): void {
397	try {
398	$this->insert('errorlogs', [
399	'time' => wp_date('Y-m-d H:i:s'),
400	'exception_msg' => mb_substr($message, 0, 250),
401	'msg' => $message,
402	'caller_name' => mb_substr($callerName, 0, 250),
403	]);
404	} catch (\Exception $e) {
405	// Silently fail — logging should never break the application
406	}
407	}
408
409	protected function _system_installiereTabellen()
410	{
411	throw new Exception("overwrite this function");
412	}
413	}
414	?>