notificationx / includes / Core / Database.php

Database.php in NotificationX – FOMO, Live Sales Notification, WooCommerce Sales Popup, GDPR, Social Proof, Announcement Banner & Floating Notification Bar 3.2.10, at includes/Core/Database.php

338 lines 12.1 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Extension Factory
5	*
6	* @package NotificationX\Extensions
7	*/
8
9	namespace NotificationX\Core;
10
11	use NotificationX\GetInstance;
12	use WPDeveloper\QueryBuilder\Query as QueryBuilder;
13
14	/**
15	* Database Class
16	* @method static Database get_instance($args = null)
17	*/
18	class Database {
19	/**
20	* Instance of Database
21	*
22	* @var Database
23	*/
24	use GetInstance;
25
26	/**
27	* WordPress database abstraction object.
28	*
29	* @var \wpdb
30	*/
31	protected $wpdb;
32	public static $version = '2.1';
33	public static $table_entries;
34	public static $table_posts;
35	public static $table_stats;
36	protected static $query;
37
38	/**
39	* Initially Invoked when initialized.
40	*/
41	public function __construct() {
42	global $wpdb;
43	$this->wpdb = $wpdb;
44	self::$table_entries = $wpdb->prefix . 'nx_entries';
45	self::$table_posts = $wpdb->prefix . 'nx_posts';
46	self::$table_stats = $wpdb->prefix . 'nx_stats';
47	}
48
49	public static function query() {
50	if (!isset(self::$query)) {
51	self::$query = QueryBuilder::init();
52	}
53	return self::$query;
54	}
55
56	public function Create_DB() {
57	$charset_collate = $this->wpdb->get_charset_collate();
58	$table_posts = self::$table_posts;
59	$table_entries = self::$table_entries;
60	$table_stats = self::$table_stats;
61
62	$sql = "CREATE TABLE {$table_entries} (
63	entry_id bigint(20) unsigned NOT NULL auto_increment,
64	nx_id bigint(20) unsigned NULL,
65	source varchar(55) default NULL,
66	entry_key varchar(255) default NULL,
67	data longtext,
68	created_at TIMESTAMP NOT NULL,
69	updated_at TIMESTAMP NOT NULL,
70	PRIMARY KEY (entry_id),
71	KEY source (source),
72	KEY nx_id (nx_id)
73	) $charset_collate ;";
74
75	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
76	$entries_db = dbDelta( $sql );
77
78	$sql = "CREATE TABLE {$table_posts} (
79	nx_id bigint(20) unsigned NOT NULL auto_increment,
80	title text default NULL,
81	type varchar(55) default NULL,
82	source varchar(55) default NULL,
83	theme varchar(55) default NULL,
84	is_inline varchar(255) default NULL,
85	global_queue BOOLEAN default false,
86	enabled BOOLEAN default false,
87	data longtext,
88	created_at TIMESTAMP NOT NULL,
89	updated_at TIMESTAMP NOT NULL,
90	PRIMARY KEY (nx_id),
91	KEY type (type),
92	KEY source (source),
93	KEY theme (theme)
94	) $charset_collate ;";
95	$post_db = dbDelta( $sql );
96
97	$sql = "CREATE TABLE {$table_stats} (
98	stat_id bigint(20) unsigned NOT NULL auto_increment,
99	nx_id bigint(20) unsigned default NULL,
100	views varchar(55) default 0,
101	clicks varchar(55) default 0,
102	created_at DATE NOT NULL,
103	PRIMARY KEY (stat_id),
104	KEY nx_id (nx_id)
105	) $charset_collate ;";
106	$stats_db = dbDelta( $sql );
107
108	}
109
110	public function update_analytics( $col, $id, $date, $data = null ) {
111	$table_name = self::$table_stats;
112	$_data = is_null( $data ) ? 1 : intval( $data );
113
114	return $this->wpdb->query( $this->wpdb->prepare( '
115	UPDATE %1$s
116	SET `%2$s` = `%3$s` + %4$s
117	WHERE nx_id = "%5$s"
118	AND created_at = "%6$s"',
119	$table_name, esc_sql( $col ), esc_sql( $col ), $_data, intval( $id ), $date
120	)
121	);
122	}
123
124	public function insert_post( $table_name, $post, $format = null ) {
125	$post = $this->serialize_data( $post );
126	$this->wpdb->insert( $table_name, $post, $this->get_format($format, $post) );
127	return $this->wpdb->insert_id;
128	}
129
130	public function insert_posts( $table_name, $posts, $format = null ) {
131	if ( ! empty( $posts[0] ) ) {
132	$values = array();
133	$place_holders = array();
134	$_column = array_keys( $posts[0] );
135	$columns = implode( ', ', $_column );
136	$query = "INSERT INTO $table_name ($columns) VALUES ";
137	foreach ( $posts as $key => $entry ) {
138	$entry = $this->serialize_data( $entry );
139	reset( $_column );
140	$_place_holders = [];
141	foreach ( $_column as $col ) {
142	$values[] = isset( $entry[ $col ] ) ? $entry[ $col ] : '';
143	if(!empty($format) && isset($format[$col])){
144	$_place_holders[] = $format[$col];
145	} else {
146	$_place_holders[] = '%s';
147	}
148	}
149	// $values = array_merge($values, array_values($entry));
150	$place_holders[] = '(' . implode( ', ', $_place_holders ) . ')'; /* In my case, i know they will always be integers */
151	}
152	$query .= implode( ', ', $place_holders );
153	$query = $this->wpdb->prepare( "$query", $values );
154	$this->wpdb->query( $query );
155	}
156	}
157
158	public function update_post( $table_name, $post, $where__or_pid, $format = null ) {
159	$post = $this->serialize_data( $post );
160	if ( ! is_array( $where__or_pid ) ) {
161	$id = $this->get_primary_col( $table_name );
162	$where__or_pid = [ $id => $where__or_pid ];
163	}
164	return $this->wpdb->update( $table_name, $post, $where__or_pid, $this->get_format($format, $post) );
165	}
166
167	public function get_post( $table_name, $where__or_pid, $select = '*' ) {
168	if ( ! is_array( $where__or_pid ) ) {
169	$id = $this->get_primary_col( $table_name );
170	$where__or_pid = [ $id => absint( $where__or_pid ) ];
171	}
172	$posts = $this->get_posts( $table_name, $select, $where__or_pid );
173	return ! empty( $posts[0] ) ? $posts[0] : null;
174	}
175
176	public function get_posts( $table_name, $select = '*', $wheres = [], $join_table = '', $group_by_col = '', $join_type = 'LEFT JOIN', $extra_query = '' ) {
177	$query = "SELECT $select FROM $table_name";
178	if ( ! empty( $join_table ) ) {
179	$query .= " AS a $join_type `$join_table` AS b ON a.nx_id = b.nx_id";
180	}
181	$query .= $this->get_where_query( $wheres );
182	if ( ! empty( $group_by_col ) ) {
183	$query .= " GROUP BY $group_by_col";
184	}
185	$posts = $this->wpdb->get_results( "$query $extra_query", ARRAY_A );
186	$posts = array_map( [ $this, 'unserialize_data' ], $posts );
187	return $posts;
188	}
189
190	public function get_col( $table_name, $col, $wheres, $distinct = 'DISTINCT' ) {
191	$col = esc_sql( $col );
192	$posts = $this->get_posts( $table_name, "$distinct `$col`", $wheres );
193	return array_column( $posts, $col );
194	}
195
196	// public function get_count($table_name, $col, $wheres) {
197	// $posts = $this->get_posts($table_name, "$col", $wheres);
198	// return array_column($posts, $col, 0);
199	// }
200
201	public function get_source_count( $table_name, $col, $wheres = [] ) {
202	$results = [];
203	$col = esc_sql( $col );
204	$posts = $this->get_posts( $table_name, "`$col`, count(*)", $wheres, '', "`$col`" );
205	foreach ( $posts as $key => $value ) {
206	$results[ $value[ $col ] ] = $value['count(*)'];
207	}
208	return $results;
209	}
210
211	public function delete_post( $table_name, $post_id ) {
212	$id = $this->get_primary_col( $table_name );
213	return $this->delete_posts( $table_name, [ $id => $post_id ] );
214	}
215
216	public function delete_posts( $table_name, $wheres, $limit = 0 ) {
217	if ( $limit ) {
218	return $this->delete_posts_limit( $table_name, $wheres, $limit );
219	}
220	return $this->wpdb->delete( $table_name, $wheres );
221	}
222
223	public function delete_posts_limit( $table_name, $wheres, $limit ) {
224	$limit = absint( $limit );
225	$query = "DELETE FROM `$table_name` ";
226	$query .= $this->get_where_query( $wheres );
227	$query .= " LIMIT $limit";
228	return $this->wpdb->query( $query );
229	}
230
231	public function serialize_data( $post ) {
232	if ( ! empty( $post['data'] ) ) {
233	$post['data'] = maybe_serialize( $post['data'] );
234	}
235	// created_at and updated_at if not empty convert to mysql date
236	if ( ! empty( $post['created_at'] ) ) {
237	$post['created_at'] = date( 'Y-m-d H:i:s', strtotime( $post['created_at'] ) );
238	}
239	if ( ! empty( $post['updated_at'] ) ) {
240	$post['updated_at'] = date( 'Y-m-d H:i:s', strtotime( $post['updated_at'] ) );
241	}
242	return $post;
243	}
244
245	public function unserialize_data( $post ) {
246	if ( ! empty( $post['data'] ) ) {
247	$post['data'] = maybe_unserialize( $post['data'] );
248	}
249	return $post;
250	}
251
252	public function get_primary_col( $table_name ) {
253	if ( $table_name == self::$table_posts ) {
254	return 'nx_id';
255	} elseif ( $table_name == self::$table_entries ) {
256	return 'entry_id';
257	} elseif ( $table_name == self::$table_stats ) {
258	return 'stat_id';
259	}
260	}
261
262	public function get_where_query( $wheres ) {
263	$query = '';
264	if ( ! empty( $wheres ) && is_string( $wheres ) ) {
265	return " $wheres ";
266	} elseif ( ! empty( $wheres ) ) {
267	$query .= ' WHERE true=true';
268	foreach ( $wheres as $key => $value ) {
269	$compare = '=';
270	if ( is_array( $value ) ) {
271	$compare = $value[0];
272	// $value = $value[1];
273	if ( 'IN' === strtoupper( $compare ) && is_array( $value[1] ) ) {
274	$value = array_map( 'esc_sql', $value[1] );
275	$value = "('" . implode( "', '", $value ) . "')";
276	} elseif ( 'BETWEEN' === strtoupper( $compare ) && isset( $value[1], $value[2] ) ) {
277	$value = $this->wpdb->prepare( '%s AND %s', $value[1], $value[2] );
278	} elseif ( in_array( $compare, [ '<', '<=', '>', '>=' ], true ) ) {
279	$value = "'" . esc_sql( $value[1] ) . "'";
280	} else {
281	throw new \Exception( "Unknown parameter $compare.", 1 );
282	}
283	} else {
284	$value = "'" . esc_sql( $value ) . "'"; // is_bool($value) ? $value :.
285	}
286	$query .= " AND $key $compare $value";
287	}
288	}
289	return $query;
290	}
291
292	public function update_option( $key, $value, $autoload = 'no' ) {
293	$is_exists = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->wpdb->prefix}options WHERE option_name=%s LIMIT 1", $key ) );
294	if ( $is_exists ) {
295	if ( $is_exists->option_value == $value ) {
296	return;
297	}
298	$this->wpdb->update( "{$this->wpdb->options}", [ 'option_value' => $value ], [ 'option_name' => $key ] );
299	} else {
300	$this->wpdb->insert( "{$this->wpdb->options}", [
301	'option_name' => $key,
302	'option_value' => $value,
303	]
304	);
305	}
306	}
307	public function get_option( $key, $default = false ) {
308	$results = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->wpdb->options} WHERE option_name=%s LIMIT 1", $key ) );
309	if ( $results ) {
310	return ! empty( $results->option_value ) ? $results->option_value : $default;
311	}
312	return $default;
313	}
314
315	/**
316	* Undocumented function
317	*
318	* @param array $format
319	* @param array $data
320	* @return array
321	*/
322	public function get_format($format, $data) {
323	if(empty($format)){
324	return null;
325	}
326
327	$result = array();
328	foreach ($data as $key => $value) {
329	if (isset($format[$key])) {
330	$result[] = $format[$key];
331	} else {
332	$result[] = '%s';
333	}
334	}
335	return $result;
336	}
337	}
338