sellkit / includes / db.php

db.php in SellKit – Funnel builder and checkout optimizer for WooCommerce to sell more, faster 2.6.0, at includes/db.php

273 lines 6.3 KB

Raw Download Zip

1	<?php
2
3	namespace Sellkit;
4
5	defined( 'ABSPATH' ) \|\| die();
6
7	/**
8	* Class Database.
9	*
10	* @package Sellkit\Contact_Segmentation\Base
11	* @since 1.1.0
12	*/
13	class Database {
14
15	const DATABASE_PREFIX = 'sellkit_';
16
17	/**
18	* Return table name by key.
19	*
20	* @since 1.1.0
21	* @return array
22	*/
23	public static function tables() {
24	global $wpdb;
25
26	$tables = [
27	'contact_segmentation' => [
28	'name' => $wpdb->prefix . self::DATABASE_PREFIX . 'contact_segmentation',
29	'query' => '
30	id bigint(20) NOT NULL AUTO_INCREMENT,
31	email VARCHAR(50) UNIQUE NOT NULL,
32	utm_source text,
33	utm_medium text,
34	utm_campaign text,
35	utm_term text,
36	utm_content text,
37	visitor_country longtext,
38	visitor_city longtext,
39	user_device VARCHAR(11),
40	browser_language VARCHAR(11),
41	user_type VARCHAR(11),
42	total_order_count VARCHAR(11),
43	total_spent VARCHAR(11),
44	first_order_date VARCHAR(11),
45	last_order_date VARCHAR(11),
46	purchased_product longtext,
47	purchased_category longtext,
48	billing_country text,
49	billing_city text,
50	shipping_country text,
51	shipping_city text,
52	viewed_product longtext,
53	viewed_category longtext,
54	rfm_r VARCHAR(3),
55	rfm_f VARCHAR(3),
56	rfm_m VARCHAR(3),
57	ip VARCHAR(255),
58	url_query_string longtext,
59	updated_at VARCHAR(11),
60	PRIMARY KEY ( id )
61	',
62	],
63	'applied_funnel' => [
64	'name' => $wpdb->prefix . self::DATABASE_PREFIX . 'applied_funnel',
65	'query' => '
66	id bigint(20) NOT NULL AUTO_INCREMENT,
67	funnel_id bigint(20) NOT NULL,
68	visit int(11) DEFAULT 0,
69	unique_visit bigint(20),
70	is_started_number bigint(20),
71	is_finished_number bigint(20),
72	revenue VARCHAR(20),
73	upsell_revenue VARCHAR(20),
74	orders bigint(20),
75	applied_at VARCHAR(20),
76	PRIMARY KEY ( id )
77	',
78	],
79	'funnel_contact' => [
80	'name' => $wpdb->prefix . self::DATABASE_PREFIX . 'funnel_contact',
81	'query' => '
82	id bigint(20) NOT NULL AUTO_INCREMENT,
83	funnel_id bigint(20) NOT NULL,
84	user_id bigint(20) NOT NULL,
85	order_bump longtext DEFAULT NULL,
86	upsell longtext DEFAULT NULL,
87	downsell longtext DEFAULT NULL,
88	optin longtext DEFAULT NULL,
89	checkout longtext DEFAULT NULL,
90	total_spent longtext DEFAULT NULL,
91	updated_at VARCHAR(11),
92	created_at VARCHAR(11),
93	PRIMARY KEY ( id )
94	',
95	],
96	];
97
98	return apply_filters( 'sellkit_tables', $tables );
99	}
100
101	/**
102	* Create all tables on activation.
103	*
104	* @since 1.1.0
105	*/
106	public static function create_all_tables() {
107	global $wpdb;
108
109	$charset_collate = $wpdb->get_charset_collate();
110
111	foreach ( self::tables() as $table ) {
112	$table_name = $table['name'];
113	$table_query = $table['query'];
114
115	if ( $table_name === $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) ) { // phpcs:ignore
116	continue;
117	}
118
119	$sql = "CREATE TABLE $table_name (
120	$table_query
121	) $charset_collate;";
122
123	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
124
125	dbDelta( $sql );
126	}
127	}
128
129	/**
130	* Create a new table.
131	*
132	* @since 1.1.0
133	* @param string $table_main_name Table main name.
134	*/
135	public static function create_new_table( $table_main_name ) {
136	global $wpdb;
137
138	$charset_collate = $wpdb->get_charset_collate();
139
140	$table = self::tables()[ $table_main_name ];
141	$table_name = $table['name'];
142	$table_query = $table['query'];
143
144	if ( $table_name === $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) ) { // phpcs:ignore
145	return;
146	}
147
148	$sql = "CREATE TABLE $table_name (
149	$table_query
150	) $charset_collate;";
151
152	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
153	dbDelta( $sql );
154	}
155
156	/**
157	* Drop all tables on deactivation.
158	*
159	* @since 1.1.0
160	*/
161	public static function drop_all_tables() {
162	foreach ( self::tables() as $table ) {
163	global $wpdb;
164
165	$table_name = $table['name'];
166
167	$wpdb->query( "DROP TABLE IF EXISTS $table_name" ); // phpcs:ignore
168	}
169	}
170
171	/**
172	* Update row into table.
173	*
174	* @param string $table_name Table name.
175	* @param array $data Data.
176	* @param array $where Which row.
177	* @param array $format Data.
178	* @since 1.1.0
179	*/
180	public function update( $table_name, $data, $where, $format = null ) {
181	$prepared_data = [];
182
183	foreach ( $data as $key => $value ) {
184	$prepared_data[ $key ] = maybe_serialize( $value );
185	}
186
187	global $wpdb;
188
189	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
190	$result = $wpdb->update( $wpdb->prefix . self::DATABASE_PREFIX . $table_name, $prepared_data, $where, $format );
191
192	if ( ! $result ) {
193	return false;
194	}
195
196	return $result;
197	}
198
199	/**
200	* Insert row into table.
201	*
202	* @param string $table_name Table name.
203	* @param array $data Data.
204	* @param array $format Data.
205	* @since 1.1.0
206	*/
207	public function insert( $table_name, $data, $format = null ) {
208	$prepared_data = [];
209
210	foreach ( $data as $key => $value ) {
211	$prepared_data[ $key ] = maybe_serialize( $value );
212	}
213
214	global $wpdb;
215
216	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
217	$result = $wpdb->insert( $wpdb->prefix . self::DATABASE_PREFIX . $table_name, $prepared_data, $format );
218
219	if ( empty( $result ) \|\| is_wp_error( $result ) ) {
220	return false;
221	}
222
223	return $wpdb->insert_id;
224	}
225
226	/**
227	* Gets database data.
228	*
229	* @since 1.1.0
230	* @param string $table_name Table name.
231	* @param array $args Where args.
232	* @param null $callback Callback function.
233	* @return array\|false\|object
234	*/
235	public function get( $table_name = null, $args = [], $callback = null ) {
236	global $wpdb;
237
238	$query = "SELECT * FROM {$wpdb->prefix}" . self::DATABASE_PREFIX . "$table_name"; // phpcs:ignore
239
240	if ( ! empty( $args ) ) {
241	$query .= ' WHERE ';
242	$connector = ' AND ';
243
244	foreach ( $args as $key => $value ) {
245	if ( ! is_array( $value ) ) {
246	$query .= sprintf( '`%1$s` = \'%2$s\'', esc_sql( $key ), esc_sql( $value ) );
247	} else {
248	$value = array_map( 'esc_sql', $value );
249	$query .= sprintf( '`%1$s` IN (%2$s)', esc_sql( $key ), implode( ',', $value ) );
250	}
251
252	if ( end( $args ) !== $value ) {
253	$query .= $connector;
254	}
255	}
256	}
257
258	$query .= ' ORDER BY id DESC';
259
260	$result = $wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore
261
262	if ( ! $result ) {
263	return false;
264	}
265
266	if ( ! $callback ) {
267	return $result;
268	}
269
270	return array_map( $callback, $result );
271	}
272	}
273