suredonation / inc / import / charitable / source.php

source.php in SureDonation – Donation Forms, Fundraising Campaigns & Donor Management trunk, at inc/import/charitable/source.php

584 lines 19.4 KB

Raw Download Zip

1	<?php
2	/**
3	* Read-only access to Charitable data.
4	*
5	* Wraps all queries against Charitable's tables (`campaign` / `donation` /
6	* `recurring_donation` post types, `charitable_campaign_donations`,
7	* `charitable_donors`, `charitable_donormeta`) so the rest of the importer
8	* never touches raw $wpdb against Charitable-owned schemas. Keeps the surface
9	* area small if Charitable ever restructures its data model.
10	*
11	* @package SureDonation
12	* @since 1.5.1
13	*/
14
15	namespace SureDonation\Inc\Import\Charitable;
16
17	use SureDonation\Inc\Traits\Get_Instance;
18
19	// Exit if accessed directly.
20	defined( 'ABSPATH' ) \|\| exit;
21
22	/**
23	* Source class.
24	*
25	* @since 1.5.1
26	*/
27	class Source {
28	use Get_Instance;
29
30	/**
31	* The Charitable campaign↔donation join table (unprefixed name).
32	*
33	* @since 1.5.1
34	*/
35	private const CAMPAIGN_DONATIONS_TABLE = 'charitable_campaign_donations';
36
37	/**
38	* Memoised result of has_charitable_data() — null until first call.
39	*
40	* @var bool\|null
41	* @since 1.5.1
42	*/
43	private $has_data_cache = null;
44
45	/**
46	* Per-request cache of donation post meta, keyed by post ID.
47	*
48	* A multi-campaign donation yields several campaign_donations rows that
49	* share one donation post, so its meta is read once per request.
50	*
51	* @var array<int, array<string, string>>
52	* @since 1.5.1
53	*/
54	private $donation_meta_cache = [];
55
56	/**
57	* Whether this site holds Charitable data we can migrate.
58	*
59	* We deliberately don't gate on the Charitable plugin being active —
60	* every read in this class is raw $wpdb against Charitable's tables
61	* (wp_charitable_campaign_donations, wp_charitable_donors, wp_posts,
62	* wp_postmeta), so admins can migrate even after deactivating
63	* Charitable. The canonical signal is "does the
64	* charitable_campaign_donations table exist?" — it is created on
65	* activation and survives deactivation.
66	*
67	* Memoised for the request to keep the pre-flight count call cheap.
68	*
69	* @return bool
70	* @since 1.5.1
71	*/
72	public function has_charitable_data() {
73	if ( null !== $this->has_data_cache ) {
74	return $this->has_data_cache;
75	}
76
77	global $wpdb;
78	$table = $wpdb->prefix . self::CAMPAIGN_DONATIONS_TABLE;
79
80	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot existence check for migration pre-flight.
81	$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
82	$this->has_data_cache = ! empty( $exists );
83
84	return $this->has_data_cache;
85	}
86
87	/**
88	* Get the running Charitable version, or null if the plugin isn't loaded.
89	* Migration itself doesn't depend on this — it's surfaced in the
90	* pre-flight response for support/debugging context.
91	*
92	* @return string\|null
93	* @since 1.5.1
94	*/
95	public function get_charitable_version() {
96	return defined( 'CHARITABLE_VERSION' ) ? CHARITABLE_VERSION : null;
97	}
98
99	/**
100	* Count Charitable campaigns eligible for migration.
101	*
102	* @return int
103	* @since 1.5.1
104	*/
105	public function get_campaign_count() {
106	global $wpdb;
107
108	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot read for migration pre-flight.
109	$count = $wpdb->get_var(
110	$wpdb->prepare(
111	'SELECT COUNT(*) FROM %i WHERE post_type = %s AND post_status IN (%s, %s, %s)',
112	$wpdb->posts,
113	'campaign',
114	'publish',
115	'draft',
116	'private'
117	)
118	);
119
120	return absint( $count );
121	}
122
123	/**
124	* Count importable donation rows.
125	*
126	* The unit is one charitable_campaign_donations row (a donation may span
127	* multiple campaigns — one row each — and per-campaign amounts live only
128	* on those rows). The join on post_type = 'donation' both drops rows whose
129	* donation post was deleted AND excludes recurring_donation plan rows the
130	* Recurring add-on writes into the same table.
131	*
132	* @return int
133	* @since 1.5.1
134	*/
135	public function get_donation_count() {
136	global $wpdb;
137
138	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot read for migration pre-flight.
139	$count = $wpdb->get_var(
140	$wpdb->prepare(
141	'SELECT COUNT(*) FROM %i cd INNER JOIN %i p ON p.ID = cd.donation_id WHERE p.post_type = %s AND p.post_status NOT IN (%s, %s)',
142	$wpdb->prefix . self::CAMPAIGN_DONATIONS_TABLE,
143	$wpdb->posts,
144	'donation',
145	'trash',
146	'auto-draft'
147	)
148	);
149
150	return absint( $count );
151	}
152
153	/**
154	* Count Charitable Recurring plans (recurring_donation posts).
155	*
156	* Zero when the Recurring add-on was never used.
157	*
158	* @return int
159	* @since 1.5.1
160	*/
161	public function get_subscription_count() {
162	global $wpdb;
163
164	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot read for migration pre-flight.
165	$count = $wpdb->get_var(
166	$wpdb->prepare(
167	'SELECT COUNT(*) FROM %i WHERE post_type = %s AND post_status != %s',
168	$wpdb->posts,
169	'recurring_donation',
170	'trash'
171	)
172	);
173
174	return absint( $count );
175	}
176
177	/**
178	* Count donors with no linked donation (standalone donors).
179	*
180	* GDPR-erased donors (data_erased set) are excluded — their PII has been
181	* blanked and an email-less donor cannot be imported.
182	*
183	* @return int
184	* @since 1.5.1
185	*/
186	public function get_standalone_donor_count() {
187	global $wpdb;
188
189	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot read for migration pre-flight.
190	$count = $wpdb->get_var(
191	$wpdb->prepare(
192	'SELECT COUNT(*) FROM %i d WHERE NOT EXISTS ( SELECT 1 FROM %i cd WHERE cd.donor_id = d.donor_id ) AND ( d.data_erased IS NULL OR d.data_erased = %s )',
193	$wpdb->prefix . 'charitable_donors',
194	$wpdb->prefix . self::CAMPAIGN_DONATIONS_TABLE,
195	'0000-00-00 00:00:00'
196	)
197	);
198
199	return absint( $count );
200	}
201
202	/**
203	* Fetch a batch of Charitable campaign posts, scoped to a selection.
204	*
205	* @param int $offset Row offset.
206	* @param int $limit Batch size.
207	* @param array<int> $campaign_ids Charitable campaign post IDs to include (required).
208	* @return array<int, object> Raw post rows.
209	* @since 1.5.1
210	*/
211	public function get_campaigns_batch( $offset, $limit, $campaign_ids ) {
212	$campaign_ids = array_filter( array_map( 'absint', (array) $campaign_ids ) );
213	if ( empty( $campaign_ids ) ) {
214	return [];
215	}
216
217	global $wpdb;
218	$placeholders = implode( ',', array_fill( 0, count( $campaign_ids ), '%d' ) );
219
220	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders are generated %d markers; values are passed to prepare().
221	$rows = $wpdb->get_results(
222	$wpdb->prepare(
223	"SELECT * FROM %i WHERE post_type = %s AND post_status IN (%s, %s, %s) AND ID IN ( {$placeholders} ) ORDER BY ID ASC LIMIT %d OFFSET %d",
224	array_merge(
225	[ $wpdb->posts, 'campaign', 'publish', 'draft', 'private' ],
226	$campaign_ids,
227	[ absint( $limit ), absint( $offset ) ]
228	)
229	)
230	);
231
232	// phpcs:enable
233
234	return is_array( $rows ) ? $rows : [];
235	}
236
237	/**
238	* Fetch a batch of donation rows (campaign_donations ⋈ donation posts),
239	* scoped to the selected campaigns.
240	*
241	* @param int $offset Row offset.
242	* @param int $limit Batch size.
243	* @param array<int> $campaign_ids Charitable campaign post IDs to include (required).
244	* @return array<int, object> Rows with cd columns + post status/date fields.
245	* @since 1.5.1
246	*/
247	public function get_donation_rows_batch( $offset, $limit, $campaign_ids ) {
248	$campaign_ids = array_filter( array_map( 'absint', (array) $campaign_ids ) );
249	if ( empty( $campaign_ids ) ) {
250	return [];
251	}
252
253	global $wpdb;
254	$placeholders = implode( ',', array_fill( 0, count( $campaign_ids ), '%d' ) );
255
256	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders are generated %d markers; values are passed to prepare().
257	$rows = $wpdb->get_results(
258	$wpdb->prepare(
259	"SELECT cd.campaign_donation_id, cd.donation_id, cd.donor_id, cd.campaign_id, cd.campaign_name, cd.amount, p.post_status, p.post_date, p.post_date_gmt, p.post_parent FROM %i cd INNER JOIN %i p ON p.ID = cd.donation_id WHERE cd.campaign_id IN ( {$placeholders} ) AND p.post_type = %s AND p.post_status NOT IN (%s, %s) ORDER BY cd.campaign_donation_id ASC LIMIT %d OFFSET %d",
260	array_merge(
261	[ $wpdb->prefix . self::CAMPAIGN_DONATIONS_TABLE, $wpdb->posts ],
262	$campaign_ids,
263	[ 'donation', 'trash', 'auto-draft', absint( $limit ), absint( $offset ) ]
264	)
265	)
266	);
267
268	// phpcs:enable
269
270	return is_array( $rows ) ? $rows : [];
271	}
272
273	/**
274	* Fetch a batch of Charitable Recurring plan posts scoped to the selected
275	* campaigns.
276	*
277	* A plan is in scope when its own campaign_donations row targets a selected
278	* campaign OR any of its child (renewal) donations does — the Recurring
279	* add-on's schema is reconstructed from the free plugin's stubs, so we
280	* cannot rely on the plan itself always having a join row.
281	*
282	* @param int $offset Row offset.
283	* @param int $limit Batch size.
284	* @param array<int> $campaign_ids Charitable campaign post IDs to include (required).
285	* @return array<int, object> Raw recurring_donation post rows.
286	* @since 1.5.1
287	*/
288	public function get_recurring_plans_batch( $offset, $limit, $campaign_ids ) {
289	$campaign_ids = array_filter( array_map( 'absint', (array) $campaign_ids ) );
290	if ( empty( $campaign_ids ) ) {
291	return [];
292	}
293
294	global $wpdb;
295	$placeholders = implode( ',', array_fill( 0, count( $campaign_ids ), '%d' ) );
296	$cd_table = $wpdb->prefix . self::CAMPAIGN_DONATIONS_TABLE;
297
298	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders are generated %d markers; values are passed to prepare().
299	$rows = $wpdb->get_results(
300	$wpdb->prepare(
301	"SELECT DISTINCT pl.* FROM %i pl WHERE pl.post_type = %s AND pl.post_status != %s AND ( EXISTS ( SELECT 1 FROM %i cd WHERE cd.donation_id = pl.ID AND cd.campaign_id IN ( {$placeholders} ) ) OR EXISTS ( SELECT 1 FROM %i ch INNER JOIN %i cd2 ON cd2.donation_id = ch.ID WHERE ch.post_parent = pl.ID AND ch.post_type = %s AND cd2.campaign_id IN ( {$placeholders} ) ) ) ORDER BY pl.ID ASC LIMIT %d OFFSET %d",
302	array_merge(
303	[ $wpdb->posts, 'recurring_donation', 'trash', $cd_table ],
304	$campaign_ids,
305	[ $wpdb->posts, $cd_table, 'donation' ],
306	$campaign_ids,
307	[ absint( $limit ), absint( $offset ) ]
308	)
309	)
310	);
311
312	// phpcs:enable
313
314	return is_array( $rows ) ? $rows : [];
315	}
316
317	/**
318	* Flat meta map for a donation (or recurring plan) post.
319	*
320	* Cached per request — a multi-campaign donation is visited once per
321	* campaign_donations row but its meta only read once.
322	*
323	* @param int $post_id Donation/plan post ID.
324	* @return array<string, string>
325	* @since 1.5.1
326	*/
327	public function get_donation_meta( $post_id ) {
328	$post_id = absint( $post_id );
329	if ( isset( $this->donation_meta_cache[ $post_id ] ) ) {
330	return $this->donation_meta_cache[ $post_id ];
331	}
332
333	global $wpdb;
334
335	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only import source access.
336	$rows = $wpdb->get_results(
337	$wpdb->prepare( 'SELECT meta_key, meta_value FROM %i WHERE post_id = %d', $wpdb->postmeta, $post_id )
338	);
339
340	$meta = [];
341	foreach ( is_array( $rows ) ? $rows : [] as $row ) {
342	$meta[ (string) $row->meta_key ] = (string) $row->meta_value;
343	}
344
345	// Keep the cache bounded for very large imports.
346	if ( count( $this->donation_meta_cache ) > 200 ) {
347	$this->donation_meta_cache = [];
348	}
349	$this->donation_meta_cache[ $post_id ] = $meta;
350
351	return $meta;
352	}
353
354	/**
355	* Flat map of a Charitable campaign's migration-relevant meta.
356	*
357	* @param int $campaign_id Charitable campaign post ID.
358	* @return array<string, string>
359	* @since 1.5.1
360	*/
361	public function get_campaign_meta( $campaign_id ) {
362	global $wpdb;
363
364	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only import source access.
365	$rows = $wpdb->get_results(
366	$wpdb->prepare(
367	'SELECT meta_key, meta_value FROM %i WHERE post_id = %d AND ( meta_key LIKE %s OR meta_key = %s )',
368	$wpdb->postmeta,
369	absint( $campaign_id ),
370	$wpdb->esc_like( '_campaign_' ) . '%',
371	'campaign_settings_v2'
372	)
373	);
374
375	$meta = [];
376	foreach ( is_array( $rows ) ? $rows : [] as $row ) {
377	$meta[ (string) $row->meta_key ] = (string) $row->meta_value;
378	}
379
380	return $meta;
381	}
382
383	/**
384	* Fetch a charitable_donors row by donor_id.
385	*
386	* @param int $donor_id Charitable donor ID.
387	* @return array<string, mixed>\|null
388	* @since 1.5.1
389	*/
390	public function get_donor( $donor_id ) {
391	global $wpdb;
392
393	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only import source access.
394	$row = $wpdb->get_row(
395	$wpdb->prepare(
396	'SELECT * FROM %i WHERE donor_id = %d',
397	$wpdb->prefix . 'charitable_donors',
398	absint( $donor_id )
399	),
400	ARRAY_A
401	);
402
403	return is_array( $row ) ? $row : null;
404	}
405
406	/**
407	* Flat meta map from charitable_donormeta for a donor.
408	*
409	* The table may not exist on very old installs — guarded.
410	*
411	* @param int $donor_id Charitable donor ID.
412	* @return array<string, string>
413	* @since 1.5.1
414	*/
415	public function get_donor_meta( $donor_id ) {
416	global $wpdb;
417	$table = $wpdb->prefix . 'charitable_donormeta';
418
419	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot existence check.
420	$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
421	if ( empty( $exists ) ) {
422	return [];
423	}
424
425	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only import source access.
426	$rows = $wpdb->get_results(
427	$wpdb->prepare( 'SELECT meta_key, meta_value FROM %i WHERE donor_id = %d', $table, absint( $donor_id ) )
428	);
429
430	$meta = [];
431	foreach ( is_array( $rows ) ? $rows : [] as $row ) {
432	$meta[ (string) $row->meta_key ] = (string) $row->meta_value;
433	}
434
435	return $meta;
436	}
437
438	/**
439	* Group donations by their Charitable gateway slug for the pre-flight
440	* gateway breakdown panel.
441	*
442	* @return array<int, array{slug: string, count: int}>
443	* @since 1.5.1
444	*/
445	public function get_gateway_breakdown() {
446	global $wpdb;
447
448	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot read for migration pre-flight.
449	$rows = $wpdb->get_results(
450	$wpdb->prepare(
451	'SELECT pm.meta_value AS slug, COUNT(*) AS total FROM %i pm INNER JOIN %i p ON p.ID = pm.post_id WHERE pm.meta_key = %s AND p.post_type = %s AND p.post_status NOT IN (%s, %s) GROUP BY pm.meta_value ORDER BY total DESC',
452	$wpdb->postmeta,
453	$wpdb->posts,
454	'donation_gateway',
455	'donation',
456	'trash',
457	'auto-draft'
458	)
459	);
460
461	$breakdown = [];
462	foreach ( is_array( $rows ) ? $rows : [] as $row ) {
463	$breakdown[] = [
464	'slug' => (string) $row->slug,
465	'count' => (int) $row->total,
466	];
467	}
468
469	return $breakdown;
470	}
471
472	/**
473	* Per-campaign aggregate preview for the selection UI.
474	*
475	* The `form_id` key deliberately carries the Charitable campaign post ID —
476	* the shared migration UI's selection model, the `campaign_ids` option and
477	* estimate_total_items() all key on `form_id` (a GiveWP-era name kept so
478	* the React components work unchanged for every source).
479	*
480	* @return array{campaigns: array<int, array<string, mixed>>, standalone_donors: int}
481	* @since 1.5.1
482	*/
483	public function get_campaigns_preview() {
484	global $wpdb;
485	$cd_table = $wpdb->prefix . self::CAMPAIGN_DONATIONS_TABLE;
486
487	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot read for migration pre-flight.
488	$rows = $wpdb->get_results(
489	$wpdb->prepare(
490	'SELECT c.ID AS form_id, c.post_title AS title, COUNT(p.ID) AS donations, COUNT(DISTINCT CASE WHEN p.ID IS NOT NULL THEN cd.donor_id END) AS donors, COALESCE(SUM(CASE WHEN p.ID IS NOT NULL THEN cd.amount ELSE 0 END), 0) AS total_amount FROM %i c LEFT JOIN %i cd ON cd.campaign_id = c.ID LEFT JOIN %i p ON p.ID = cd.donation_id AND p.post_type = %s AND p.post_status NOT IN (%s, %s) WHERE c.post_type = %s AND c.post_status IN (%s, %s, %s) GROUP BY c.ID, c.post_title ORDER BY donations DESC, c.post_title ASC',
491	$wpdb->posts,
492	$cd_table,
493	$wpdb->posts,
494	'donation',
495	'trash',
496	'auto-draft',
497	'campaign',
498	'publish',
499	'draft',
500	'private'
501	)
502	);
503
504	$campaigns = [];
505	foreach ( is_array( $rows ) ? $rows : [] as $row ) {
506	$campaigns[ (int) $row->form_id ] = [
507	'form_id' => (int) $row->form_id,
508	'title' => (string) $row->title,
509	'donations' => (int) $row->donations,
510	'donors' => (int) $row->donors,
511	'subscriptions' => 0,
512	'total_amount' => (float) $row->total_amount,
513	];
514	}
515
516	// Merge per-campaign recurring-plan counts (plans join through their
517	// own campaign_donations row).
518	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-shot read for migration pre-flight.
519	$sub_rows = $wpdb->get_results(
520	$wpdb->prepare(
521	'SELECT cd.campaign_id, COUNT(DISTINCT pl.ID) AS subscriptions FROM %i pl INNER JOIN %i cd ON cd.donation_id = pl.ID WHERE pl.post_type = %s AND pl.post_status != %s GROUP BY cd.campaign_id',
522	$wpdb->posts,
523	$cd_table,
524	'recurring_donation',
525	'trash'
526	)
527	);
528
529	foreach ( is_array( $sub_rows ) ? $sub_rows : [] as $row ) {
530	$campaign_id = (int) $row->campaign_id;
531	if ( isset( $campaigns[ $campaign_id ] ) ) {
532	$campaigns[ $campaign_id ]['subscriptions'] = (int) $row->subscriptions;
533	}
534	}
535
536	return [
537	'campaigns' => array_values( $campaigns ),
538	'standalone_donors' => $this->get_standalone_donor_count(),
539	];
540	}
541
542	/**
543	* Extract the serialized donor snapshot Charitable stores on each
544	* donation ('donor' postmeta), hardened against object injection.
545	*
546	* @param array<string, string> $meta Donation meta map.
547	* @return array{first_name: string, last_name: string, email: string, address: string, address_2: string, city: string, state: string, postcode: string, country: string, phone: string}
548	* @since 1.5.1
549	*/
550	public function extract_donor_snapshot( $meta ) {
551	$defaults = [
552	'first_name' => '',
553	'last_name' => '',
554	'email' => '',
555	'address' => '',
556	'address_2' => '',
557	'city' => '',
558	'state' => '',
559	'postcode' => '',
560	'country' => '',
561	'phone' => '',
562	];
563
564	if ( empty( $meta['donor'] ) ) {
565	return $defaults;
566	}
567
568	// phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.serialize_unserialize, WordPress.PHP.NoSilencedErrors.Discouraged -- hardened native unserialize with class instantiation disabled.
569	$snapshot = @unserialize( $meta['donor'], [ 'allowed_classes' => false ] );
570	if ( ! is_array( $snapshot ) ) {
571	return $defaults;
572	}
573
574	$clean = $defaults;
575	foreach ( array_keys( $defaults ) as $key ) {
576	if ( isset( $snapshot[ $key ] ) && is_scalar( $snapshot[ $key ] ) ) {
577	$clean[ $key ] = sanitize_text_field( (string) $snapshot[ $key ] );
578	}
579	}
580
581	return $clean;
582	}
583	}
584