sellkit / includes / db.php

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

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