class-merchant-analytics-db-orm.php in Product Labels, Quick View, Buy Now, Pre-Orders, Frequently Bought Together & More for WooCommerce – Merchant 2.3.1, at inc/analytics/class-merchant-analytics-db-orm.php

636 lines 15.5 KB Raw Download Zip

1	<?php
2
3	// Exit if accessed directly.
4	if ( ! defined( 'ABSPATH' ) ) {
5	exit;
6	}
7
8	class Merchant_Analytics_DB_ORM {
9	private $table_name;
10	private $wpdb;
11	private $fillable
12	= array(
13	'source_product_id',
14	'event_type',
15	'customer_id',
16	'related_event_id',
17	'module_id',
18	'campaign_id',
19	'campaign_cost',
20	'order_id',
21	'order_subtotal',
22	'order_total', // Added
23	'meta_data',
24	'meta_data_2', // Added
25	);
26
27	// Query building properties
28	private $query_where = '';
29	private $query_params = array();
30	private $query_results = null;
31	private $query_executed = false;
32	private $select = '*';
33	private $aggregates = array();
34	private $order_by = '';
35	private $sql_statement = '';
36	private $limit = '';
37	private $joins = '';
38	private $eager_load = array();
39
40	public function __construct() {
41	global $wpdb;
42	$this->wpdb = $wpdb;
43	$this->table_name = $this->wpdb->prefix . 'merchant_modules_analytics';
44	}
45
46	/**
47	* Reset query builder state.
48	*
49	* @return $this
50	*/
51	public function reset_query() {
52	$this->query_where = '';
53	$this->sql_statement = '';
54	$this->query_params = array();
55	$this->query_results = null;
56	$this->query_executed = false;
57	$this->select = '*';
58	$this->aggregates = array();
59	$this->order_by = '';
60	$this->limit = '';
61	$this->joins = '';
62	$this->eager_load = array();
63
64	return $this;
65	}
66
67	/**
68	* Creates a new record in the analytics table.
69	*
70	* @param array $attributes An associative array of attributes for the new record.
71	*
72	* @return int\|false The ID of the inserted row on success, false on failure.
73	*/
74	public function create( array $attributes ) {
75	$data = array();
76	foreach ( $this->fillable as $field ) {
77	if ( isset( $attributes[ $field ] ) ) {
78	$data[ $field ] = $attributes[ $field ];
79	}
80	}
81	$format = $this->get_data_formats( $data );
82	$result = $this->wpdb->insert( $this->table_name, $data, $format );
83
84	if ( $result === false ) {
85	return false;
86	}
87
88	return $this->wpdb->insert_id;
89	}
90
91	/**
92	* Finds a record by its primary key (id).
93	*
94	* @param int $id The ID of the record to find.
95	*
96	* @return array\|null The record object if found, null otherwise.
97	*/
98	public function find( $id, $first_or_last = 'first' ) {
99	if ( $first_or_last === 'last' ) {
100	$results = $this->reset_query()
101	->where( 'id = %d', $id )
102	->last();
103	} else {
104	$results = $this->reset_query()
105	->where( 'id = %d', $id )
106	->first();
107	}
108
109	return $results;
110	}
111
112	/**
113	* Retrieves all records from the analytics table.
114	*
115	* @return array\|null An array of objects representing the matching records, or null if there are no results or an error occurs.
116	*/
117	public function get_all() {
118	return $this->reset_query()->get();
119	}
120
121	/**
122	* Add a where clause with multiple conditions.
123	*
124	* @param array\|string $conditions Conditions to filter by.
125	* @param mixed $value Value for simple key-value condition.
126	*
127	* @return $this
128	*/
129	/**
130	* Add a where clause with multiple conditions.
131	*
132	* @param array\|string $conditions Conditions to filter by.
133	* @param mixed $value Value for simple key-value condition.
134	*
135	* @return $this
136	*/
137	public function where( $conditions, $value = null ) {
138	if ( is_string( $conditions ) && $value !== null ) {
139	$this->query_where .= empty( $this->query_where )
140	? ' WHERE ' . $conditions
141	: ' AND ' . $conditions;
142	$this->query_params[] = $value;
143	} elseif ( is_array( $conditions ) ) {
144	$and_conditions = array();
145	foreach ( $conditions as $key => $val ) {
146	if ( is_array( $val ) ) {
147	if ( count( $val ) === 3 ) {
148	list( $column, $operator, $comparison ) = $val;
149	$and_conditions[] = "$column $operator %s";
150	$this->query_params[] = $comparison;
151	} elseif ( isset( $val['in'] ) ) {
152	$in_values = $val['in'];
153	$placeholders = implode( ',', array_fill( 0, count( $in_values ), '%s' ) );
154	$and_conditions[] = "$key IN ($placeholders)";
155	foreach ( $in_values as $in_value ) {
156	$this->query_params[] = $in_value;
157	}
158	} elseif ( isset( $val['not_in'] ) ) {
159	$not_in_values = $val['not_in'];
160	$placeholders = implode( ',', array_fill( 0, count( $not_in_values ), '%s' ) );
161	$and_conditions[] = "$key NOT IN ($placeholders)";
162	foreach ( $not_in_values as $not_in_value ) {
163	$this->query_params[] = $not_in_value;
164	}
165	}
166	} else {
167	$and_conditions[] = "$key = %s";
168	$this->query_params[] = $val;
169	}
170	}
171	$conditions_sql = implode( " AND ", $and_conditions );
172	$this->query_where .= empty( $this->query_where )
173	? ' WHERE ' . $conditions_sql
174	: ' AND ' . $conditions_sql;
175	}
176
177	return $this;
178	}
179
180	/**
181	* Add OR conditions to the query.
182	*
183	* @param array $conditions Conditions to filter by.
184	*
185	* @return $this
186	*/
187	public function or_where( $conditions ) {
188	$or_conditions = array();
189	foreach ( $conditions as $key => $val ) {
190	$or_conditions[] = "$key = %s";
191	$this->query_params[] = $val;
192	}
193	$conditions_sql = implode( " OR ", $or_conditions );
194	$this->query_where .= empty( $this->query_where )
195	? ' WHERE ' . $conditions_sql
196	: ' OR ' . $conditions_sql;
197
198	return $this;
199	}
200
201	/**
202	* Add date range filter for chaining.
203	*
204	* @param string $start_date The start date for the range (YYYY-MM-DD HH:MM:SS).
205	* @param string $end_date The end date for the range (YYYY-MM-DD HH:MM:SS).
206	*
207	* @return $this
208	*/
209	public function where_between_dates( $start_date, $end_date ) {
210	$this->query_where .= empty( $this->query_where )
211	? ' WHERE timestamp BETWEEN %s AND %s'
212	: ' AND timestamp BETWEEN %s AND %s';
213	$this->query_params[] = $start_date;
214	$this->query_params[] = $end_date;
215
216	return $this;
217	}
218
219	/**
220	* Sets columns to select.
221	*
222	* @param string\|array $columns Columns to select.
223	*
224	* @return $this
225	*/
226	public function select( $columns = '*' ) {
227	$this->select = is_array( $columns ) ? implode( ', ', $columns ) : $columns;
228
229	return $this;
230	}
231
232	/**
233	* Add SUM to query.
234	*
235	* @param string $column Column to sum.
236	* @param string $alias Optional alias for the sum.
237	*
238	* @return $this
239	*/
240	public function sum( $column, $alias = '' ) {
241	$this->aggregates[] = array(
242	'function' => 'SUM',
243	'column' => $column,
244	'alias' => $alias ? $alias : "sum_{$column}",
245	);
246
247	return $this;
248	}
249
250	/**
251	* Add COUNT to query.
252	*
253	* @param string $column Column to count.
254	* @param boolean $distinct Count distinct values.
255	* @param string $alias Optional alias.
256	*
257	* @return $this
258	*/
259	public function count( $column = '*', $distinct = false, $alias = '' ) {
260	$this->aggregates[] = array(
261	'function' => 'COUNT',
262	'column' => $distinct ? "DISTINCT {$column}" : $column,
263	'alias' => $alias ? $alias : "count_{$column}",
264	);
265
266	return $this;
267	}
268
269	/**
270	* Prepares DISTINCT query.
271	*
272	* @param string\|array $columns Columns to get distinct values.
273	*
274	* @return $this
275	*/
276	public function distinct( $columns ) {
277	if ( is_array( $columns ) ) {
278	$columns = implode( ', ', $columns );
279	}
280	$this->select = "DISTINCT {$columns}";
281
282	return $this;
283	}
284
285	/**
286	* Prepares MAX query.
287	*
288	* @param string $column Column to get maximum value.
289	*
290	* @return $this
291	*/
292	public function max( $column, $alias = '' ) {
293	$this->aggregates[] = array(
294	'function' => 'MAX',
295	'column' => $column,
296	'alias' => $alias ? $alias : "max_{$column}",
297	);
298
299	return $this;
300	}
301
302	/**
303	* Prepares MIN query.
304	*
305	* @param string $column Column to get minimum value.
306	*
307	* @return $this
308	*/
309	public function min( $column, $alias = '' ) {
310	$this->aggregates[] = array(
311	'function' => 'MIN',
312	'column' => $column,
313	'alias' => $alias ? $alias : "min_{$column}",
314	);
315
316	return $this;
317	}
318
319	/**
320	* Add AVG to query.
321	*
322	* @param string $column Column to average.
323	* @param string $alias Optional alias.
324	*
325	* @return $this
326	*/
327	public function avg( $column, $alias = '' ) {
328	$this->aggregates[] = array(
329	'function' => 'AVG',
330	'column' => $column,
331	'alias' => $alias ? $alias : "avg_{$column}",
332	);
333
334	return $this;
335	}
336
337	/**
338	* Add a group by clause to the query.
339	*
340	* @param string $column The column to group by.
341	*
342	* @return $this
343	*/
344	public function group_by( $column ) {
345	$this->query_where .= " GROUP BY " . esc_sql( $column );
346
347	return $this;
348	}
349
350	/**
351	* Add where not in clause to the query.
352	*
353	* @param string $column The column to check.
354	* @param array $values Array of values to exclude.
355	*
356	* @return $this
357	*/
358	public function where_not_in( string $column, array $values ) {
359	if ( empty( $values ) ) {
360	return $this;
361	}
362	$column = esc_sql( $column );
363	$placeholders = implode( ',', array_fill( 0, count( $values ), '%s' ) );
364	$this->query_where .= empty( $this->query_where )
365	? ' WHERE ' . $column . ' NOT IN (' . $placeholders . ')'
366	: ' AND ' . $column . ' NOT IN (' . $placeholders . ')';
367	$this->query_params = array_merge( $this->query_params, $values );
368
369	return $this;
370	}
371
372	/**
373	* Add ORDER BY clause to query.
374	*
375	* @param string $column Column name.
376	* @param string $direction Sort direction (ASC or DESC).
377	*
378	* @return $this
379	*/
380	public function order_by( $column, $direction = 'ASC' ) {
381	$column = esc_sql( $column );
382	$direction = in_array( strtoupper( $direction ), array( 'ASC', 'DESC' ), true ) ? strtoupper( $direction ) : 'ASC';
383	$this->order_by = " ORDER BY {$column} {$direction}";
384
385	return $this;
386	}
387
388	/**
389	* Add LIMIT clause to query.
390	*
391	* @param int $limit Number of records to return.
392	* @param int $offset Offset to start from.
393	*
394	* @return $this
395	*/
396	public function limit( $limit, $offset = 0 ) {
397	if ( $limit >= 0 ) {
398	$this->limit = " LIMIT {$limit} OFFSET {$offset}";
399	}
400
401	return $this;
402	}
403
404	/**
405	* Add pagination to the query.
406	*
407	* @param int $page The page number.
408	* @param int $per_page The number of records per page.
409	*
410	* @return $this
411	*/
412	public function paginate( $page, $per_page = 10 ) {
413	$offset = ( $page - 1 ) * $per_page;
414	$this->limit( $per_page, $offset );
415
416	return $this;
417	}
418
419	/**
420	* Add a JOIN clause to the query.
421	*
422	* @param string $table The table to join.
423	* @param string $first The first column to join on.
424	* @param string $operator The join operator.
425	* @param string $second The second column to join on.
426	* @param string $type The type of join (INNER, LEFT, RIGHT, etc.).
427	*
428	* @return $this
429	*/
430	public function join( $table, $first, $operator, $second, $type = 'INNER' ) {
431	$this->joins .= " $type JOIN $table ON $first $operator $second";
432
433	return $this;
434	}
435
436	/**
437	* Eager load related data.
438	*
439	* @param string $relation The relation to load.
440	* @param callable $callback A callback to build the related query.
441	*
442	* @return $this
443	*/
444	public function with( $relation, callable $callback ) {
445	$this->eager_load[ $relation ] = $callback;
446
447	return $this;
448	}
449
450	/**
451	* Execute a raw SQL query.
452	*
453	* @param string $sql The SQL query.
454	* @param array $params The query parameters.
455	*
456	* @return array\|null
457	*/
458	public function raw( $sql, $params = array() ) {
459	if ( ! empty( $params ) ) {
460	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
461	$sql = $this->wpdb->prepare( $sql, $params );
462	}
463
464	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
465	return $this->wpdb->get_results( $sql, ARRAY_A );
466	}
467
468	/**
469	* Start a database transaction.
470	*
471	* @return $this
472	*/
473	public function begin_transaction() {
474	$this->wpdb->query( 'START TRANSACTION' );
475
476	return $this;
477	}
478
479	/**
480	* Commit the current transaction.
481	*
482	* @return $this
483	*/
484	public function commit() {
485	$this->wpdb->query( 'COMMIT' );
486
487	return $this;
488	}
489
490	/**
491	* Rollback the current transaction.
492	*
493	* @return $this
494	*/
495	public function rollback() {
496	$this->wpdb->query( 'ROLLBACK' );
497
498	return $this;
499	}
500
501	/**
502	* Execute the query and get results.
503	*
504	* @return array\|null An array of objects representing the matching records, or null if there are no results or an error occurs.
505	*/
506	public function get() {
507	if ( $this->query_executed && $this->query_results !== null ) {
508	return $this->query_results;
509	}
510
511	$select = $this->select;
512
513	if ( ! empty( $this->aggregates ) ) {
514	$aggregate_parts = array();
515	foreach ( $this->aggregates as $agg ) {
516	$aggregate_parts[] = sprintf(
517	'%s(%s) as %s',
518	esc_sql( $agg['function'] ),
519	esc_sql( $agg['column'] ),
520	esc_sql( $agg['alias'] )
521	);
522	}
523	$select = implode( ', ', $aggregate_parts );
524	}
525
526	$sql = "SELECT {$select} FROM {$this->table_name}" . $this->joins . $this->query_where . $this->order_by . $this->limit;
527
528	if ( ! empty( $this->query_params ) ) {
529	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
530	$sql = $this->wpdb->prepare( $sql, $this->query_params );
531	}
532
533	$this->sql_statement = $sql;
534	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
535	$this->query_results = $this->wpdb->get_results( $sql, ARRAY_A );
536	$this->query_executed = true;
537
538	return $this->query_results;
539	}
540
541	/**
542	* Get the SQL statement for the last query
543	*
544	* @return string The SQL statement
545	*/
546	public function get_sql_statement() {
547	return $this->sql_statement;
548	}
549
550	/**
551	* Get the first result from the query.
552	*
553	* @return array\|null The first record object if found, null otherwise.
554	*/
555	public function first() {
556	$results = $this->get();
557
558	return ! empty( $results ) ? $results[0] : null;
559	}
560
561	/**
562	* Get the last result from the query.
563	*
564	* @return array\|null The last record object if found, null otherwise.
565	*/
566	public function last() {
567	$results = $this->get();
568
569	return ! empty( $results ) ? $results[ count( $results ) - 1 ] : null;
570	}
571
572	/**
573	* Updates a record in the analytics table.
574	*
575	* @param int $id The ID of the record to update.
576	* @param array $attributes An associative array of attributes to update. Keys should match column names.
577	*
578	* @return int\|false The number of rows affected, or false on error.
579	*/
580	public function update( $id, array $attributes ) {
581	$data = array();
582	foreach ( $this->fillable as $field ) {
583	if ( isset( $attributes[ $field ] ) ) {
584	$data[ $field ] = $attributes[ $field ];
585	}
586	}
587	if ( empty( $data ) ) {
588	return 0;
589	}
590
591	return $this->wpdb->update( $this->table_name, $data, array( 'id' => $id ), $this->get_data_formats( $data ) );
592	}
593
594	/**
595	* Deletes a record from the analytics table by its ID.
596	*
597	* @param int $id The ID of the record to delete.
598	*
599	* @return int\|false The number of rows affected, or false on error.
600	*/
601	public function delete( $id ) {
602	return $this->wpdb->delete( $this->table_name, array( 'id' => $id ), array( '%d' ) );
603	}
604
605	/**
606	* Checks if the analytics table exists.
607	*
608	* @return bool True if the table exists, false otherwise.
609	*/
610	public function table_exists() {
611	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
612	return $this->wpdb->get_var( $this->wpdb->prepare( "SHOW TABLES LIKE %s", $this->table_name ) ) === $this->table_name;
613	}
614
615	/**
616	* Gets the data formats for the wpdb update/insert function based on data.
617	*
618	* @param array $data The data array to check.
619	*
620	* @return array The formats array.
621	*/
622	private function get_data_formats( $data ) {
623	$format = array();
624	foreach ( $data as $key => $value ) {
625	if ( in_array( $key, array( 'source_product_id', 'related_event_id', 'order_id' ), true ) ) {
626	$format[] = '%d';
627	} elseif ( $key === 'campaign_cost' \|\| $key === 'order_subtotal' \|\| $key === 'order_total' ) {
628	$format[] = '%f';
629	} else {
630	$format[] = '%s';
631	}
632	}
633
634	return $format;
635	}
636	}