sellkit / includes / db.php

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

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