better-payment / includes / Models / SubscriptionRelationModel.php

SubscriptionRelationModel.php in Better Payment – Instant Payments, Donations, Fundraising with Subscriptions & More trunk, at includes/Models/SubscriptionRelationModel.php

421 lines 16.5 KB

Raw Download Zip

1	<?php
2
3	namespace Better_Payment\Lite\Models;
4
5	/**
6	* Exit if accessed directly
7	*/
8	if ( ! defined( 'ABSPATH' ) ) {
9	exit;
10	}
11
12	/**
13	* E-commerce subscription <-> order relation model.
14	*
15	* Owns the `{prefix}better_payment_subscription_order` table (created in
16	* Installer::get_schema()). Every row links an e-commerce subscription to
17	* one of its orders and names the integration that owns it in `source`
18	* ('woo', 'fluentcart', 'surecart', ...), so several e-commerce integrations
19	* can share the table without their ids colliding.
20	*
21	* Contract: e-commerce subscription data only. Better Payment's own
22	* (Elementor/campaign) subscription payments live in the `better_payment`
23	* transactions table and must never be written here.
24	*
25	* What a row means:
26	* - `subscription_id` — the integration's subscription identifier. For the
27	* WooCommerce module that is the parent (subscription) order id.
28	* - `order_id` — the related order: the parent order itself for a
29	* `new` row, a renewal order for a `renew` row.
30	* - `order_item_id` — the subscription line item on that order (0 when the
31	* integration has no item-level ids).
32	*
33	* @since 2.4.0
34	*/
35	class SubscriptionRelationModel {
36
37	/**
38	* Relation types (row = the order that STARTED the subscription vs. a
39	* renewal of it).
40	*/
41	const TYPE_NEW = 'new';
42	const TYPE_RENEW = 'renew';
43
44	/**
45	* Known e-commerce sources. The column accepts any sanitized slug (a new
46	* integration should not need a core edit to record rows) — these consts
47	* exist so bundled integrations never typo their own name.
48	*/
49	const SOURCE_WOO = 'woo';
50	const SOURCE_FLUENTCART = 'fluentcart';
51	const SOURCE_SURECART = 'surecart';
52
53	/**
54	* The full table name.
55	*
56	* @return string
57	*/
58	public static function get_table_name() {
59	global $wpdb;
60
61	return $wpdb->prefix . 'better_payment_subscription_order';
62	}
63
64	/**
65	* Relation types as key => translated label.
66	*
67	* @return array
68	*/
69	public static function types() {
70	$types = array(
71	self::TYPE_NEW => __( 'New Subscription Order', 'better-payment' ),
72	self::TYPE_RENEW => __( 'Renewal Order', 'better-payment' ),
73	);
74
75	/**
76	* Filters the subscription order relation types.
77	*
78	* @since 2.4.0
79	*
80	* @param array $types Type key => translated label.
81	*/
82	return apply_filters( 'better_payment/subscription/order_relation_types', $types );
83	}
84
85	/**
86	* Known sources as key => label, for admin UI listings.
87	*
88	* @return array
89	*/
90	public static function known_sources() {
91	$sources = array(
92	self::SOURCE_WOO => __( 'WooCommerce', 'better-payment' ),
93	self::SOURCE_FLUENTCART => __( 'FluentCart', 'better-payment' ),
94	self::SOURCE_SURECART => __( 'SureCart', 'better-payment' ),
95	);
96
97	/**
98	* Filters the known e-commerce subscription sources.
99	*
100	* @since 2.4.0
101	*
102	* @param array $sources Source slug => label.
103	*/
104	return apply_filters( 'better_payment/subscription/order_relation_sources', $sources );
105	}
106
107	/**
108	* Pure: clamp a relation type to the registered set ('' when unknown —
109	* an unknown type must fail record(), not be silently rewritten).
110	*
111	* @param mixed $type Raw value.
112	* @return string
113	*/
114	public static function sanitize_type( $type ) {
115	$type = sanitize_key( (string) $type );
116
117	return array_key_exists( $type, self::types() ) ? $type : '';
118	}
119
120	/**
121	* Pure: sanitize a source slug. Any non-empty sanitized slug is accepted
122	* (a third-party integration must be able to record its own source
123	* without filtering a whitelist first); '' when unusable. Capped at the
124	* column width.
125	*
126	* @param mixed $source Raw value.
127	* @return string
128	*/
129	public static function sanitize_source( $source ) {
130	$source = sanitize_key( (string) $source );
131
132	return substr( $source, 0, 50 );
133	}
134
135	/**
136	* Record one subscription <-> order relation. Idempotent: an identical
137	* row (same subscription, order, type and source) is never duplicated.
138	*
139	* @param array $args {
140	* @type int $subscription_id Integration's subscription id (> 0).
141	* @type int $order_id Related order id (> 0).
142	* @type int $order_item_id Subscription line item id (optional, default 0).
143	* @type string $type self::TYPE_NEW \| self::TYPE_RENEW.
144	* @type string $source E-commerce source slug (e.g. self::SOURCE_WOO).
145	* }
146	* @return int Row id (the existing row's id when already recorded), 0 on invalid input or failure.
147	*/
148	public static function record( $args ) {
149	global $wpdb;
150
151	$subscription_id = isset( $args['subscription_id'] ) ? absint( $args['subscription_id'] ) : 0;
152	$order_id = isset( $args['order_id'] ) ? absint( $args['order_id'] ) : 0;
153	$order_item_id = isset( $args['order_item_id'] ) ? absint( $args['order_item_id'] ) : 0;
154	$type = self::sanitize_type( isset( $args['type'] ) ? $args['type'] : '' );
155	$source = self::sanitize_source( isset( $args['source'] ) ? $args['source'] : '' );
156
157	if ( $subscription_id <= 0 \|\| $order_id <= 0 \|\| '' === $type \|\| '' === $source ) {
158	return 0;
159	}
160
161	$existing = self::find_id( $subscription_id, $order_id, $type, $source );
162
163	if ( $existing > 0 ) {
164	return $existing;
165	}
166
167	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
168	$inserted = $wpdb->insert(
169	self::get_table_name(),
170	array(
171	'subscription_id' => $subscription_id,
172	'order_id' => $order_id,
173	'order_item_id' => $order_item_id,
174	'type' => $type,
175	'source' => $source,
176	),
177	array( '%d', '%d', '%d', '%s', '%s' )
178	);
179
180	return $inserted ? (int) $wpdb->insert_id : 0;
181	}
182
183	/**
184	* The id of an already-recorded identical relation row (0 = none).
185	*
186	* @param int $subscription_id Subscription id.
187	* @param int $order_id Order id.
188	* @param string $type Relation type.
189	* @param string $source Source slug.
190	* @return int
191	*/
192	public static function find_id( $subscription_id, $order_id, $type, $source ) {
193	global $wpdb;
194
195	$table = self::get_table_name();
196
197	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- custom table owned by this model; name is not user input.
198	return (int) $wpdb->get_var(
199	$wpdb->prepare(
200	"SELECT id FROM {$table} WHERE subscription_id = %d AND order_id = %d AND type = %s AND source = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
201	absint( $subscription_id ),
202	absint( $order_id ),
203	(string) $type,
204	(string) $source
205	)
206	);
207	}
208
209	/**
210	* All relation rows of one subscription, oldest first. Optionally
211	* restricted to one source.
212	*
213	* @param int $subscription_id Subscription id.
214	* @param string $source Optional source slug ('' = any).
215	* @return object[] Rows (id, subscription_id, order_id, order_item_id, type, source).
216	*/
217	public static function get_orders( $subscription_id, $source = '' ) {
218	global $wpdb;
219
220	$table = self::get_table_name();
221	$source = self::sanitize_source( $source );
222
223	if ( '' !== $source ) {
224	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
225	$rows = $wpdb->get_results(
226	$wpdb->prepare(
227	"SELECT * FROM {$table} WHERE subscription_id = %d AND source = %s ORDER BY id ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
228	absint( $subscription_id ),
229	$source
230	)
231	);
232	} else {
233	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
234	$rows = $wpdb->get_results(
235	$wpdb->prepare(
236	"SELECT * FROM {$table} WHERE subscription_id = %d ORDER BY id ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
237	absint( $subscription_id )
238	)
239	);
240	}
241
242	return is_array( $rows ) ? $rows : array();
243	}
244
245	/**
246	* The subscription an order belongs to (0 = none recorded). Optionally
247	* restricted to one source — pass it when the caller knows the
248	* integration, since order ids from different platforms may collide.
249	*
250	* @param int $order_id Order id.
251	* @param string $source Optional source slug ('' = any).
252	* @return int
253	*/
254	public static function get_subscription_id( $order_id, $source = '' ) {
255	global $wpdb;
256
257	$table = self::get_table_name();
258	$source = self::sanitize_source( $source );
259
260	if ( '' !== $source ) {
261	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
262	return (int) $wpdb->get_var(
263	$wpdb->prepare(
264	"SELECT subscription_id FROM {$table} WHERE order_id = %d AND source = %s ORDER BY id ASC LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
265	absint( $order_id ),
266	$source
267	)
268	);
269	}
270
271	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
272	return (int) $wpdb->get_var(
273	$wpdb->prepare(
274	"SELECT subscription_id FROM {$table} WHERE order_id = %d ORDER BY id ASC LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
275	absint( $order_id )
276	)
277	);
278	}
279
280	/**
281	* Paginated list of distinct e-commerce subscriptions, newest first.
282	*
283	* One entry per (subscription_id, source) pair — a subscription with ten
284	* renewal orders is still one row. `renewal_orders` counts its recorded
285	* renewals. Ordered by subscription_id DESC (for the bundled integrations
286	* the id is a post/order id, so descending ≈ newest subscription first).
287	*
288	* @param array $args paged, per_page
289	* @return array { subscriptions: object[], total: int, pages: int, page: int, per_page: int }
290	* @since 2.4.0
291	*/
292	public static function get_subscriptions_paginated( $args = array() ) {
293	global $wpdb;
294
295	$defaults = array(
296	'paged' => 1,
297	'per_page' => 20,
298	);
299	$args = wp_parse_args( $args, $defaults );
300	$table = self::get_table_name();
301	$paged = max( 1, intval( $args['paged'] ) );
302	$per_page = max( 1, intval( $args['per_page'] ) );
303	$offset = ( $paged - 1 ) * $per_page;
304
305	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- custom table owned by this model; name is not user input.
306	$total = (int) $wpdb->get_var(
307	"SELECT COUNT(*) FROM ( SELECT subscription_id FROM {$table} GROUP BY subscription_id, source ) AS grouped" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
308	);
309
310	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
311	$rows = $wpdb->get_results(
312	$wpdb->prepare(
313	"SELECT subscription_id, source,
314	SUM( CASE WHEN type = %s THEN 1 ELSE 0 END ) AS renewal_orders
315	FROM {$table}
316	GROUP BY subscription_id, source
317	ORDER BY subscription_id DESC, source ASC
318	LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
319	self::TYPE_RENEW,
320	$per_page,
321	$offset
322	)
323	);
324
325	return array(
326	'subscriptions' => is_array( $rows ) ? $rows : array(),
327	'total' => $total,
328	'pages' => $total > 0 ? (int) ceil( $total / $per_page ) : 1,
329	'page' => $paged,
330	'per_page' => $per_page,
331	);
332	}
333
334	/**
335	* Every distinct e-commerce subscription as a grouped row — the same
336	* shape and order get_subscriptions_paginated() returns, without the
337	* LIMIT.
338	*
339	* Exists for the admin list's status/search filters, which cannot become
340	* a WHERE clause: the fields they match on (status, customer, product)
341	* are not in this table, they arrive when each row is hydrated by its
342	* integration. Filtering therefore has to hydrate the whole set first,
343	* and this is the query that yields it. Callers that are NOT filtering
344	* must keep using get_subscriptions_paginated() — the fast path exists
345	* precisely so the default pageview never pays for this.
346	*
347	* The GROUP BY and ORDER BY must stay identical to
348	* get_subscriptions_paginated()'s, or a filtered list would come back in
349	* a different order from an unfiltered one.
350	*
351	* @return object[] Rows with subscription_id, source, renewal_orders.
352	* @since 2.4.0
353	*/
354	public static function get_subscription_groups() {
355	global $wpdb;
356
357	$table = self::get_table_name();
358
359	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
360	$rows = $wpdb->get_results(
361	$wpdb->prepare(
362	"SELECT subscription_id, source,
363	SUM( CASE WHEN type = %s THEN 1 ELSE 0 END ) AS renewal_orders
364	FROM {$table}
365	GROUP BY subscription_id, source
366	ORDER BY subscription_id DESC, source ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
367	self::TYPE_RENEW
368	)
369	);
370
371	return is_array( $rows ) ? $rows : array();
372	}
373
374	/**
375	* Every distinct e-commerce subscription as (subscription_id, source)
376	* pairs — the unpaginated id list behind get_subscriptions_paginated().
377	* Used for whole-table aggregates (the admin tab's summary counts).
378	*
379	* @return object[] Rows with subscription_id + source.
380	* @since 2.4.0
381	*/
382	public static function get_distinct_subscriptions() {
383	global $wpdb;
384
385	$table = self::get_table_name();
386
387	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
388	$rows = $wpdb->get_results(
389	"SELECT subscription_id, source FROM {$table} GROUP BY subscription_id, source" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
390	);
391
392	return is_array( $rows ) ? $rows : array();
393	}
394
395	/**
396	* Delete every relation row of one subscription (housekeeping — e.g. when
397	* an integration erases a subscription). Optionally one source only.
398	*
399	* @param int $subscription_id Subscription id.
400	* @param string $source Optional source slug ('' = any).
401	* @return int Rows deleted.
402	*/
403	public static function delete_for_subscription( $subscription_id, $source = '' ) {
404	global $wpdb;
405
406	$where = array( 'subscription_id' => absint( $subscription_id ) );
407	$format = array( '%d' );
408	$source = self::sanitize_source( $source );
409
410	if ( '' !== $source ) {
411	$where['source'] = $source;
412	$format[] = '%s';
413	}
414
415	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table owned by this model.
416	$deleted = $wpdb->delete( self::get_table_name(), $where, $format );
417
418	return is_numeric( $deleted ) ? (int) $deleted : 0;
419	}
420	}
421