disco / app / Analytics / Queries / IntentQuery.php

IntentQuery.php in Discount Rules for WooCommerce – Disco | Dynamic Pricing, Conditions, Bulk, Bundle, BOGO 1.4.14, at app/Analytics/Queries/IntentQuery.php

112 lines 3.7 KB

Raw Download Zip

1	<?php
2
3	/**
4	* IntentQuery — intent-wise sales aggregation for the /analytics/intents endpoint.
5	*
6	* @package Disco
7	* @subpackage Disco\App\Analytics\Queries
8	* @since 1.3.23
9	*/
10
11	namespace Disco\App\Analytics\Queries;
12
13	/**
14	* Groups completed disco orders by campaign intent and returns sales + order counts.
15	*
16	* Campaigns with no matching record in disco_campaigns (deleted) are folded
17	* into the "Others" bucket. Multiple campaigns sharing the same intent are
18	* combined automatically by the GROUP BY.
19	*/
20	class IntentQuery extends BaseQuery {
21
22	/**
23	* WooCommerce status value for completed orders.
24	*/
25	private const STATUS_COMPLETED = 'wc-completed';
26
27	/**
28	* Returns per-intent sales and order counts for completed disco orders in a period.
29	*
30	* Rows are ordered by sales DESC so the controller can assign percentages
31	* without re-sorting.
32	*
33	* @param array $period { from: string Y-m-d, to: string Y-m-d }.
34	* @return array Array of { intent: string, orders: int, sales: float }
35	*/
36	public function get_intent_sales( array $period ): array {
37	$rows = $this->fetch_intent_rows( $period );
38
39	return array_map(
40	function ( $row ) {
41	return array(
42	'intent' => $row->intent,
43	'orders' => (int) $row->orders,
44	'revenue' => round( (float) $row->revenue, 2 ),
45	);
46	},
47	$rows
48	);
49	}
50
51	/**
52	* Fetches per-intent aggregate rows, deleted campaigns folded into 'Others'.
53	*
54	* @param array $period { from: string Y-m-d, to: string Y-m-d } — empty values skip date filter.
55	* @return array Row objects: intent, orders, revenue.
56	*/
57	private function fetch_intent_rows( array $period ): array {
58	global $wpdb;
59
60	$tables = $this->get_tables();
61	$clauses = $this->get_order_clauses( $tables );
62	$order_id_col = $tables['order_id_col'];
63	$date_filter = $this->build_date_filter( $period, $clauses );
64	$dedup = $this->get_campaign_dedup_condition( $tables );
65
66	$rows_sql = "SELECT
67	CASE
68	WHEN campaign.id IS NULL THEN 'Others'
69	ELSE COALESCE(campaign.intent, 'Others')
70	END AS intent,
71	COUNT(DISTINCT order_meta.{$order_id_col}) AS orders,
72	SUM({$clauses['total_expr']} - COALESCE(order_stats.shipping_total, 0)) AS revenue
73	FROM {$tables['order_meta']} order_meta
74	JOIN {$tables['orders']} o ON o.ID = order_meta.{$order_id_col} AND {$clauses['status_where']}
75	{$clauses['total_join']}
76	LEFT JOIN {$wpdb->prefix}wc_order_stats order_stats ON order_stats.order_id = order_meta.{$order_id_col}
77	LEFT JOIN {$tables['campaigns']} campaign ON campaign.id = CAST(order_meta.meta_value AS UNSIGNED)
78	WHERE order_meta.meta_key = 'disco_campaign'
79	AND o.status = %s
80	AND {$dedup}
81	{$date_filter['clause']}
82	GROUP BY intent
83	ORDER BY revenue DESC";
84
85	return $this->run_rows( $rows_sql, array_merge( array( self::STATUS_COMPLETED ), $date_filter['params'] ) );
86	}
87
88	/**
89	* Builds the optional BETWEEN date clause and its params.
90	*
91	* Both period bounds must be present; otherwise the filter is skipped.
92	*
93	* @param array $period { from: string Y-m-d, to: string Y-m-d }.
94	* @param array $clauses From get_order_clauses().
95	* @return array { clause: string, params: array }
96	*/
97	private function build_date_filter( array $period, array $clauses ): array {
98	if ( empty( $period['from'] ) \|\| empty( $period['to'] ) ) {
99	return array(
100	'clause' => '',
101	'params' => array(),
102	);
103	}
104
105	return array(
106	'clause' => "AND {$clauses['date_col']} BETWEEN %s AND %s",
107	'params' => array( $period['from'] . ' 00:00:00', $period['to'] . ' 23:59:59' ),
108	);
109	}
110
111	}
112