bookit / includes / classes / vendor / DatabaseModel.php

DatabaseModel.php in Bookit — Booking & Appointment Calendar trunk, at includes/classes/vendor/DatabaseModel.php

228 lines 5.1 KB

Raw Download Zip

1	<?php
2
3	namespace Bookit\Classes\Vendor;
4
5	abstract class DatabaseModel {
6
7	public static $primary_key = 'id';
8	protected static $table_prefix = 'bookit_';
9
10	/**
11	* Generate Table Name from Called Class
12	* @return string
13	*/
14	public static function _table() {
15	global $wpdb;
16	$classname = explode( '\\', strtolower( get_called_class() ) );
17	$tablename = self::$table_prefix . end( $classname );
18	return $wpdb->prefix . $tablename;
19	}
20
21	/**
22	* SQL Fetch from Table
23	* @param $key
24	* @param $value
25	* @return mixed
26	*/
27	private static function _fetch_sql( $key, $value ) {
28	global $wpdb;
29	$sql = sprintf( 'SELECT * FROM `%s` WHERE `%s` = %%s', esc_sql( self::_table() ), esc_sql( $key ) );
30	return $wpdb->prepare( $sql, $value );
31	}
32
33	/**
34	* Get Rows with Pagination
35	*
36	* @param $limit
37	* @param $offset
38	* @param string $search
39	* @param string $sort
40	* @param string $order
41	*
42	* @return mixed
43	*/
44	public static function get_paged( $limit, $offset, $search = '', $sort = '', $order = '' ) {
45	global $wpdb;
46	$sql = sprintf(
47	'SELECT * FROM `%s` %s ORDER BY `%s` %s LIMIT %%d OFFSET %%d',
48	esc_sql( self::_table() ),
49	$search,
50	esc_sql( ( empty( $sort ) ) ? static::$primary_key : $sort ),
51	esc_sql( ( empty( $order ) ) ? 'DESC' : $order )
52	);
53	return $wpdb->get_results(
54	$wpdb->prepare(
55	$sql,
56	intval( $limit ),
57	intval( $offset )
58	),
59	ARRAY_A
60	);
61	}
62
63	/**
64	* Get All Rows
65	*/
66	public static function get_all() {
67	global $wpdb;
68	return $wpdb->get_results(
69	sprintf( 'SELECT * FROM `%s` ORDER BY `%s` DESC', esc_sql( self::_table() ), esc_sql( static::$primary_key ) ),
70	ARRAY_A
71	);
72	}
73
74	/**
75	* Get Total Count of Rows
76	* @return mixed
77	*/
78	public static function get_count() {
79	global $wpdb;
80	return $wpdb->get_var( sprintf( 'SELECT COUNT(*) FROM `%s`', esc_sql( self::_table() ) ) );
81	}
82
83	/**
84	* Get Row by ID
85	* @param $key
86	* @param $value
87	* @return mixed
88	*/
89	public static function get( $key, $value ) {
90	global $wpdb;
91	return $wpdb->get_row( self::_fetch_sql( $key, $value ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
92	}
93
94	/**
95	* Insert data to Table
96	* @param $data
97	*/
98	public static function insert( $data ) {
99	global $wpdb;
100
101	add_filter( 'query', array( self::class, 'wp_db_null_value' ) );
102
103	if ( isset( $data['nonce'] ) ) {
104	unset( $data['nonce'] );
105	}
106
107	$data = array_map(
108	function( $item ) {
109	$item = (string) $item; // Cast to string to prevent null values.
110	if ( trim( $item, ' \'"' ) ) {
111	return trim( $item );
112	}
113	return null;
114	},
115	$data
116	);
117
118	$wpdb->insert( self::_table(), $data );
119
120	remove_filter( 'query', array( self::class, 'wp_db_null_value' ) );
121	}
122
123	/**
124	* Update data in Table with $where clause
125	* @param $data
126	* @param $where
127	*/
128	public static function update( $data, $where ) {
129	global $wpdb;
130
131	add_filter( 'query', array( self::class, 'wp_db_null_value' ) );
132
133	if ( isset( $data['nonce'] ) ) {
134	unset( $data['nonce'] );
135	}
136
137	$data = array_map(
138	function( $item ) {
139	$item = (string) $item; // Cast to string to prevent null values.
140	if ( trim( $item, ' \'"' ) ) {
141	return trim( $item );
142	}
143	return null;
144	},
145	$data
146	);
147
148	$wpdb->update( self::_table(), $data, $where );
149
150	remove_filter( 'query', array( self::class, 'wp_db_null_value' ) );
151	}
152
153	/**
154	* Delete data from Table by ID
155	* @param $value
156	* @return mixed
157	*/
158	public static function delete( $value ) {
159	global $wpdb;
160	$sql = sprintf( 'DELETE FROM `%s` WHERE `%s` = %%s', esc_sql( self::_table() ), esc_sql( static::$primary_key ) );
161	return $wpdb->query( $wpdb->prepare( $sql, $value ) );
162	}
163
164	/**
165	* Delete data from Table Where
166	* @param $key
167	* @param $value
168	* @return mixed
169	*/
170	public static function delete_where( $key, $value ) {
171	global $wpdb;
172	$sql = sprintf( 'DELETE FROM `%s` WHERE `%s` = %%s', esc_sql( self::_table() ), esc_sql( $key ) );
173	return $wpdb->query( $wpdb->prepare( $sql, $value ) );
174	}
175
176	/**
177	* Get Inserted data ID
178	* @return mixed
179	*/
180	public static function insert_id() {
181	global $wpdb;
182	return $wpdb->insert_id;
183	}
184
185	/**
186	* Replace the 'NULL' string with NULL
187	*
188	* @param string $query
189	* @return string $query
190	*/
191	public static function wp_db_null_value( $query ) {
192	return str_ireplace( "'NULL'", 'NULL', $query );
193	}
194
195	/**
196	* Show Last Query
197	* @return mixed
198	*/
199	public static function show_last_query() {
200	global $wpdb;
201	echo $wpdb->last_query; // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
202	}
203
204	/**
205	* Drop all bookit tables on uninstall
206	*/
207	public static function drop_tables() {
208	global $wpdb;
209
210	$sql = self::generate_drop_table_statement();
211	$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
212	}
213
214	/**
215	* Generate sql statement to remove all bookit tables by prefix
216	*/
217	private static function generate_drop_table_statement() {
218	global $wpdb;
219
220	$prefix = $wpdb->prefix . self::$table_prefix;
221	return $wpdb->get_var(
222	"SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(DISTINCT( table_name) ) , ';' ) AS statement
223	FROM information_schema.tables
224	WHERE table_name LIKE '%{$prefix}%'" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
225	);
226	}
227	}
228