sellkit / includes / db.php

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

251 lines 5.6 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	updated_at VARCHAR(11),
59	PRIMARY KEY ( id )
60	',
61	],
62	'applied_funnel' => [
63	'name' => $wpdb->prefix . self::DATABASE_PREFIX . 'applied_funnel',
64	'query' => '
65	id bigint(20) NOT NULL AUTO_INCREMENT,
66	funnel_id bigint(20) NOT NULL,
67	visit int(11) DEFAULT 0,
68	unique_visit bigint(20),
69	is_started_number bigint(20),
70	is_finished_number bigint(20),
71	revenue VARCHAR(20),
72	upsell_revenue VARCHAR(20),
73	orders bigint(20),
74	applied_at VARCHAR(20),
75	PRIMARY KEY ( id )
76	',
77	],
78	];
79
80	return apply_filters( 'sellkit_tables', $tables );
81	}
82
83	/**
84	* Create all tables on activation.
85	*
86	* @since 1.1.0
87	*/
88	public static function create_all_tables() {
89	global $wpdb;
90
91	$charset_collate = $wpdb->get_charset_collate();
92
93	foreach ( self::tables() as $table ) {
94	$table_name = $table['name'];
95	$table_query = $table['query'];
96
97	if ( $table_name === $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) ) { // phpcs:ignore
98	continue;
99	}
100
101	$sql = "CREATE TABLE $table_name (
102	$table_query
103	) $charset_collate;";
104
105	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
106
107	dbDelta( $sql );
108	}
109	}
110
111	/**
112	* Create a new table.
113	*
114	* @since 1.1.0
115	* @param string $table_main_name Table main name.
116	*/
117	public static function create_new_table( $table_main_name ) {
118	global $wpdb;
119
120	$charset_collate = $wpdb->get_charset_collate();
121
122	$table = self::tables()[ $table_main_name ];
123	$table_name = $table['name'];
124	$table_query = $table['query'];
125
126	if ( $table_name === $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) ) { // phpcs:ignore
127	return;
128	}
129
130	$sql = "CREATE TABLE $table_name (
131	$table_query
132	) $charset_collate;";
133
134	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
135	dbDelta( $sql );
136	}
137
138	/**
139	* Drop all tables on deactivation.
140	*
141	* @since 1.1.0
142	*/
143	public static function drop_all_tables() {
144	foreach ( self::tables() as $table ) {
145	global $wpdb;
146
147	$table_name = $table['name'];
148
149	$wpdb->query( "DROP TABLE IF EXISTS $table_name" ); // phpcs:ignore
150	}
151	}
152
153	/**
154	* Update row into table.
155	*
156	* @param string $table_name Table name.
157	* @param array $data Data.
158	* @param array $where Which row.
159	* @param array $format Data.
160	* @since 1.1.0
161	*/
162	public function update( $table_name, $data, $where, $format = null ) {
163	$prepared_data = [];
164
165	foreach ( $data as $key => $value ) {
166	$prepared_data[ $key ] = maybe_serialize( $value );
167	}
168
169	global $wpdb;
170
171	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
172	$result = $wpdb->update( $wpdb->prefix . self::DATABASE_PREFIX . $table_name, $prepared_data, $where, $format );
173
174	if ( ! $result ) {
175	return false;
176	}
177
178	return $result;
179	}
180
181	/**
182	* Insert row into table.
183	*
184	* @param string $table_name Table name.
185	* @param array $data Data.
186	* @param array $format Data.
187	* @since 1.1.0
188	*/
189	public function insert( $table_name, $data, $format = null ) {
190	$prepared_data = [];
191
192	foreach ( $data as $key => $value ) {
193	$prepared_data[ $key ] = maybe_serialize( $value );
194	}
195
196	global $wpdb;
197
198	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
199	$result = $wpdb->insert( $wpdb->prefix . self::DATABASE_PREFIX . $table_name, $prepared_data, $format );
200
201	return $result;
202	}
203
204	/**
205	* Gets database data.
206	*
207	* @since 1.1.0
208	* @param string $table_name Table name.
209	* @param array $args Where args.
210	* @param null $callback Callback function.
211	* @return array\|false\|object
212	*/
213	public function get( $table_name = null, $args = [], $callback = null ) {
214	global $wpdb;
215
216	$query = "SELECT * FROM {$wpdb->prefix}" . self::DATABASE_PREFIX . "$table_name"; // phpcs:ignore
217
218	if ( ! empty( $args ) ) {
219	$query .= ' WHERE ';
220	$connector = ' AND ';
221
222	foreach ( $args as $key => $value ) {
223	if ( ! is_array( $value ) ) {
224	$query .= sprintf( '`%1$s` = \'%2$s\'', esc_sql( $key ), esc_sql( $value ) );
225	} else {
226	$value = array_map( 'esc_sql', $value );
227	$query .= sprintf( '`%1$s` IN (%2$s)', esc_sql( $key ), implode( ',', $value ) );
228	}
229
230	if ( end( $args ) !== $value ) {
231	$query .= $connector;
232	}
233	}
234	}
235
236	$query .= ' ORDER BY id DESC';
237
238	$result = $wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore
239
240	if ( ! $result ) {
241	return false;
242	}
243
244	if ( ! $callback ) {
245	return $result;
246	}
247
248	return array_map( $callback, $result );
249	}
250	}
251