db.php — Event Tickets with Ticket Scanner 3.0.5

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

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

db.php

449 lines

1	<?php
2	include_once(plugin_dir_path(__FILE__)."init_file.php");
3	class sasoEventticketsDB extends sasoEventtickets_DB {
4	public $dbversion = '1.13';
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', 'options', 'options_history'];
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	// Options table - v1.11: replaces individual wp_options rows with single table
182	$tabellen[] = [
183	"sql"=>
184	"CREATE TABLE ".$this->getTabelle('options')." (
185	option_key varchar(191) NOT NULL DEFAULT '',
186	option_value longtext NOT NULL DEFAULT '',
187	updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
188	updated_by int(32) unsigned NOT NULL DEFAULT 0,
189	PRIMARY KEY (option_key)) ".$this->getCharsetCollate().";",
190	"additional"=>[]
191	];
192	// Options history table - v1.12: tracks changes to plugin options
193	$tabellen[] = [
194	"sql"=>
195	"CREATE TABLE ".$this->getTabelle('options_history')." (
196	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
197	option_key varchar(191) NOT NULL DEFAULT '',
198	old_value text NOT NULL,
199	new_value text NOT NULL,
200	changed_by int(32) unsigned NOT NULL DEFAULT 0,
201	changed_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
202	PRIMARY KEY (id),
203	KEY option_key_changed_at (option_key, changed_at),
204	KEY changed_at (changed_at)) ".$this->getCharsetCollate().";",
205	"additional"=>[]
206	];
207	$tabellen = apply_filters( $this->MAIN->_add_filter_prefix.'db_system_installiereTabellen', $tabellen );
208	do_action( $this->MAIN->_do_action_prefix.'db_system_installiereTabellen', $tabellen );
209	return $tabellen;
210	}
211	}
212
213	class sasoEventtickets_DB {
214	// irgendwann nach dem https://codex.wordpress.org/Creating_Tables_with_Plugins
215	//https://tobier.de/wordpress-plugin-erstellen-datenbank/
216	public $dbversion;
217	protected static $dbprefix;
218	protected $_tabellen = [];
219	private $tabellen;
220	protected $callerValue = "basic";
221
222	protected $MAIN;
223
224	public function __construct($MAIN) {
225	$this->MAIN = $MAIN;
226	$this->init();
227	}
228	protected function init() {
229	$this->tabellen = [];
230	foreach($this->_tabellen as $t) {
231	$this->tabellen[$t] = $this->getPrefix().$t;
232	}
233	}
234
235	public function getTabelle($tabelle) {
236	return $this->tabellen[$tabelle];
237	}
238
239	public function getTables() {
240	return $this->_tabellen;
241	}
242
243	public function reinigen_in($text, $len=0, $addsl=1, $utf=0, $html=0) {
244	$text = trim($text);
245	if ($len > 0)
246	$text = substr($text, 0, $len);
247	if ($utf == 1)
248	$text = mb_convert_encoding($text, 'ISO-8859-1', 'UTF-8');
249	//$text = utf8_decode($text); // die zeichen sind utf kodiert
250	if ($html == 1)
251	$text = htmlentities($text); // zerstört HTML zeug im text
252	if ($addsl == 1)
253	$text = addslashes($text);
254	return $text;
255	}
256
257	private function getPrefix() {
258	global $wpdb;
259	return $wpdb->prefix . self::$dbprefix;
260	}
261
262	protected function getCharsetCollate() {
263	global $wpdb;
264	return $wpdb->get_charset_collate();
265	}
266
267	public function _db_datenholen_prepared($sql, $felder) {
268	global $wpdb;
269	for ($a=0;$a<count($felder);$a++) {
270	//$felder[$a] = $wpdb->esc_like( $felder[$a] );
271	$felder[$a] = $this->reinigen_in( $felder[$a] );
272	}
273	$sql = $wpdb->prepare( $sql, $felder );
274	return $this->_db_datenholen($sql);
275	}
276
277	public function _db_datenholen($sql, $again=true) {
278	global $wpdb;
279	//update_option( self::$dbprefix."db_version", "1.4" );
280	//$installed_ver = get_option( self::$dbprefix."db_version" );
281	//if ($installed_ver != $this->dbversion) $this->installiereTabellen();
282	//echo $installed_ver;
283	try {
284	$ret = $wpdb->get_results($sql, ARRAY_A);
285	} catch(Exception $e) {
286	if ($again == true) {
287	$this->installiereTabellen(true);
288	return $this->_db_datenholen($sql, false);
289	} else {
290	throw $e;
291	}
292	}
293	if ( $wpdb->last_error ) {
294	$this->MAIN->getAdmin()->logErrorToDB(new Exception("Database error - Again: ".$again), null, $wpdb->last_error);
295	if ($again == true) {
296	$this->installiereTabellen(true);
297	$ret = $this->_db_datenholen($sql, false);
298	}
299	}
300	return $ret;
301	}
302
303	public function _db_getRecordCountOfTable($tabelle, $where="") {
304	$sql = "select count(*) as anzahl from ".$this->getTabelle($tabelle);
305	if ($where != "") $sql .= " where ".$where;
306	list($d) = $this->_db_datenholen($sql);
307	return $d['anzahl'];
308	}
309
310	public function getCodesSize() {
311	return $this->_db_getRecordCountOfTable('codes');
312	}
313
314	private function addMissingFelder($felder) {
315	if (($this->callerValue == "basic" && version_compare( $this->dbversion, '1.6', '>' )) \|\|
316	($this->callerValue == "prem" && version_compare( $this->dbversion, '1.3', '>' ))) {
317	if (array_key_exists("time", $felder)) {
318	if (!array_key_exists("timezone", $felder)) {
319	$felder["timezone"] = wp_timezone_string();
320	}
321	}
322	if (array_key_exists("changed", $felder)) {
323	if (!array_key_exists("changed_timezone", $felder)) {
324	$felder["changed_timezone"] = wp_timezone_string();
325	}
326	}
327	}
328	$felder = apply_filters( $this->MAIN->_add_filter_prefix.'db_addMissingFelder', $felder );
329	return $felder;
330	}
331
332	public function insert($tabelle, $felder=[]) {
333	global $wpdb;
334	if (count($felder) == 0) throw new Exception("no fields provided");
335	$felder = $this->addMissingFelder($felder);
336	$wpdb->insert( $this->getTabelle($tabelle), $felder );
337	return $wpdb->insert_id;
338	}
339
340	public function update($tabelle, $felder, $where) {
341	global $wpdb;
342	if (count($felder) == 0) throw new Exception("no fields provided");
343	$felder = $this->addMissingFelder($felder);
344	if (count($where) == 0) throw new Exception("no where fields provided");
345	return $wpdb->update( $this->getTabelle($tabelle), $felder, $where);
346	}
347
348	public function _db_query($sql) {
349	global $wpdb;
350	$erg = $wpdb->query($sql);
351	if ($erg):
352	if (strtolower(substr($sql, 0, 6)) == "insert") {
353	return $wpdb->insert_id;
354	}
355	return $erg;
356	else:
357	if (!empty($wpdb->last_error)) {
358	$this->installiereTabellen(true);
359	$this->MAIN->getAdmin()->logErrorToDB(new Exception("Database error"), null, $wpdb->last_error);
360	echo $wpdb->last_error;
361	wp_die($wpdb->last_error);
362	}
363	endif;
364	return $erg;
365	}
366
367	public function installiereTabellen($force=false) {
368	global $wpdb;
369	if (empty($this->dbversion)) throw new Exception("dbversion is not set");
370	if (empty(self::$dbprefix)) throw new Exception("dbprefix is not set");
371
372	$installed_ver = get_option( self::$dbprefix."db_version" );
373
374	if ($force \|\| $installed_ver != $this->dbversion ) {
375	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
376
377	$tabellen = $this->_system_installiereTabellen(); // array
378	foreach($tabellen as $tabelle) {
379	dbDelta( $tabelle['sql'] ); // tabelle erstellen
380	if (isset($tabelle['additional'])) {
381	$wpdb->suppress_errors = true;
382	foreach($tabelle['additional'] as $sql) {
383	//echo $sql;
384	$wpdb->query($sql); // zusätzlich sql wie index
385	}
386	$wpdb->suppress_errors = false;
387	}
388	}
389
390	// Run upgrade jobs BEFORE saving the new db_version.
391	// This ensures that if any job (e.g. options migration) crashes,
392	// the next request re-runs the entire upgrade — all jobs are idempotent.
393	// Note: premium/hook calls inside performJobsAfterDBUpgraded are wrapped
394	// in try/catch to prevent an infinite crash loop if they throw.
395	if ($this->callerValue == "basic") {
396	$this->MAIN->getAdmin()->performJobsAfterDBUpgraded($this->dbversion, $installed_ver);
397	} else { // wenn für die prem DB dann direkt aufruf
398	try {
399	if ($this->MAIN->isPremium() && method_exists($this->MAIN->getPremiumFunctions(), 'performJobsAfterPremDBUpgraded')) {
400	$this->MAIN->getPremiumFunctions()->performJobsAfterPremDBUpgraded($this->dbversion, $installed_ver);
401	}
402	} catch (\Throwable $e) {
403	$this->MAIN->getAdmin()->logErrorToDB($e, null, 'Premium DB upgrade job failed: ' . $e->getMessage());
404	}
405	}
406	update_option( self::$dbprefix."db_version", $this->dbversion );
407	}
408	}
409	public static function plugin_deactivated() {
410	//delete_option(self::$dbprefix."db_version");
411	}
412	public static function plugin_uninstall(){
413	self::plugin_deactivated();
414	//delete tabellen
415	/*
416	global $wpdb;
417	foreach($this->tabellen as $key => $value) {
418	$wpdb->query("DROP TABLE IF EXISTS ".$value);
419	}
420	*/
421	}
422	/**
423	* Log an error message to the errorlogs table.
424	*
425	* Convenience method used by seating subsystem and other components
426	* that need simple string-based error logging without an Exception object.
427	*
428	* @param string $message Error message
429	* @param string $callerName Optional caller identification
430	*/
431	public function logError(string $message, string $callerName = ''): void {
432	try {
433	$this->insert('errorlogs', [
434	'time' => wp_date('Y-m-d H:i:s'),
435	'exception_msg' => mb_substr($message, 0, 250),
436	'msg' => $message,
437	'caller_name' => mb_substr($callerName, 0, 250),
438	]);
439	} catch (\Exception $e) {
440	// Silently fail — logging should never break the application
441	}
442	}
443
444	protected function _system_installiereTabellen()
445	{
446	throw new Exception("overwrite this function");
447	}
448	}
449	?>