give / src / Subscriptions / Migrations / BackfillMissingCampaignIdForDonations.php

BackfillMissingCampaignIdForDonations.php in GiveWP – Donation Plugin and Fundraising Platform 4.16.9, at src/Subscriptions/Migrations/BackfillMissingCampaignIdForDonations.php

424 lines 13.2 KB

Raw Download Zip

1	<?php
2
3	namespace Give\Subscriptions\Migrations;
4
5	use Give\Framework\Database\DB;
6	use Give\Framework\Database\Exceptions\DatabaseQueryException;
7	use Give\Framework\Migrations\Contracts\BatchMigration;
8	use Give\Framework\Migrations\Exceptions\DatabaseMigrationException;
9	use Give\Framework\QueryBuilder\QueryBuilder;
10
11	/**
12	* Class BackfillMissingCampaignIdForDonations
13	*
14	* This migration backfills missing campaignId for existing donations.
15	* It ensures that donations have the campaignId from their parent payment (for renewals),
16	* from the revenue table, or discovers it through form-campaign associations.
17	*
18	* @since 4.3.2
19	*/
20	class BackfillMissingCampaignIdForDonations extends BatchMigration
21	{
22	/**
23	* @inheritDoc
24	*/
25	public static function id(): string
26	{
27	return 'subscriptions_backfill_missing_campaign_id_for_donations';
28	}
29
30	/**
31	* @inheritDoc
32	*/
33	public static function title(): string
34	{
35	return 'Backfill missing campaignId for donations';
36	}
37
38	/**
39	* @inheritDoc
40	*/
41	public static function timestamp(): string
42	{
43	return strtotime('2025-06-03 00:00:00');
44	}
45
46	/**
47	* Base query to find donations without campaignId
48	*
49	* @since 4.3.2
50	*/
51	protected function query(): QueryBuilder
52	{
53	return DB::table('posts', 'donations')
54	->where('donations.post_type', 'give_payment')
55	->whereNotExists(function (QueryBuilder $builder) {
56	$builder
57	->select('meta_id')
58	->from('give_donationmeta')
59	->where('meta_key', '_give_campaign_id')
60	->whereRaw('AND donation_id = donations.ID');
61	});
62	}
63
64	/**
65	* @inheritDoc
66	* @throws DatabaseMigrationException
67	*/
68	public function runBatch($firstId, $lastId)
69	{
70	try {
71	$query = $this->query()->select('donations.ID');
72
73	// Migration Runner will pass null for lastId in the last step
74	if (is_null($lastId)) {
75	$query->where('donations.ID', $firstId, '>');
76	} else {
77	$query->whereBetween('donations.ID', $firstId, $lastId);
78	}
79
80	$donations = $query->getAll();
81	$donationIds = array_column($donations, 'ID');
82
83	if (empty($donationIds)) {
84	return;
85	}
86
87	// Process donations in bulk
88	$this->processDonationsBulk($donationIds);
89
90	} catch (DatabaseQueryException $exception) {
91	throw new DatabaseMigrationException('An error occurred while backfilling missing campaignId for donations', 0, $exception);
92	}
93	}
94
95	/**
96	* Process multiple donations in bulk to assign missing campaignId
97	*
98	* @since 4.3.2
99	*/
100	private function processDonationsBulk(array $donationIds): void
101	{
102	// Step 1: Get campaign IDs from revenue table for all donations
103	$revenueCampaigns = $this->getBulkCampaignIdsFromRevenueTable($donationIds);
104
105	// Step 2: Get subscription and parent payment data for remaining donations
106	$subscriptionData = $this->getBulkSubscriptionData($donationIds);
107
108	// Step 3: Get form-to-campaign mappings for remaining donations
109	$formCampaigns = $this->getBulkFormCampaignMappings($donationIds, $revenueCampaigns, $subscriptionData);
110
111	// Step 4: Prepare bulk insert data
112	$metaInserts = [];
113
114	foreach ($donationIds as $donationId) {
115	$campaignId = null;
116
117	// Priority 1: Revenue table
118	if (isset($revenueCampaigns[$donationId])) {
119	$campaignId = $revenueCampaigns[$donationId];
120	}
121	// Priority 2: Parent payment campaign ID
122	elseif (isset($subscriptionData[$donationId]['parent_campaign_id'])) {
123	$campaignId = $subscriptionData[$donationId]['parent_campaign_id'];
124	}
125	// Priority 3: Form-based campaign mapping
126	elseif (isset($formCampaigns[$donationId])) {
127	$campaignId = $formCampaigns[$donationId];
128	}
129
130	if ($campaignId) {
131	$metaInserts[] = [
132	'donation_id' => $donationId,
133	'meta_key' => '_give_campaign_id',
134	'meta_value' => $campaignId
135	];
136	}
137	}
138
139	// Step 5: Bulk insert meta data
140	if (!empty($metaInserts)) {
141	$this->bulkInsertMeta($metaInserts);
142	}
143	}
144
145	/**
146	* Get campaign IDs from revenue table for multiple donations
147	*
148	* @since 4.3.2
149	*/
150	private function getBulkCampaignIdsFromRevenueTable(array $donationIds): array
151	{
152	$results = DB::table('give_revenue')
153	->select('donation_id', 'campaign_id')
154	->whereIn('donation_id', $donationIds)
155	->where('campaign_id', 0, '>')
156	->getAll();
157
158	$campaigns = [];
159	foreach ($results as $row) {
160	$campaigns[$row->donation_id] = (int)$row->campaign_id;
161	}
162
163	return $campaigns;
164	}
165
166	/**
167	* Get subscription and parent payment data for multiple donations
168	*
169	* @since 4.3.2
170	*/
171	private function getBulkSubscriptionData(array $donationIds): array
172	{
173	// Get subscription IDs for all donations
174	$subscriptionMeta = DB::table('give_donationmeta')
175	->select('donation_id', 'meta_value as subscription_id')
176	->whereIn('donation_id', $donationIds)
177	->where('meta_key', '_give_subscription_id')
178	->getAll();
179
180	if (empty($subscriptionMeta)) {
181	return [];
182	}
183
184	$subscriptionIds = array_column($subscriptionMeta, 'subscription_id');
185	$donationToSubscription = [];
186	foreach ($subscriptionMeta as $row) {
187	$donationToSubscription[$row->donation_id] = $row->subscription_id;
188	}
189
190	// Get parent payment IDs from subscriptions
191	$parentPayments = DB::table('give_subscriptions')
192	->select('id', 'parent_payment_id')
193	->whereIn('id', $subscriptionIds)
194	->where('parent_payment_id', 0, '>')
195	->getAll();
196
197	if (empty($parentPayments)) {
198	return [];
199	}
200
201	$subscriptionToParent = [];
202	$parentPaymentIds = [];
203	foreach ($parentPayments as $row) {
204	$subscriptionToParent[$row->id] = $row->parent_payment_id;
205	$parentPaymentIds[] = $row->parent_payment_id;
206	}
207
208	// Get campaign IDs from parent payments
209	$parentCampaigns = DB::table('give_donationmeta')
210	->select('donation_id', 'meta_value as campaign_id')
211	->whereIn('donation_id', $parentPaymentIds)
212	->where('meta_key', '_give_campaign_id')
213	->getAll();
214
215	$parentToCampaign = [];
216	foreach ($parentCampaigns as $row) {
217	$parentToCampaign[$row->donation_id] = (int)$row->campaign_id;
218	}
219
220	// Map back to original donations
221	$result = [];
222	foreach ($donationToSubscription as $donationId => $subscriptionId) {
223	if (isset($subscriptionToParent[$subscriptionId])) {
224	$parentPaymentId = $subscriptionToParent[$subscriptionId];
225	$data = [
226	'subscription_id' => $subscriptionId,
227	'parent_payment_id' => $parentPaymentId,
228	];
229
230	// Only add parent_campaign_id if it exists
231	if (isset($parentToCampaign[$parentPaymentId])) {
232	$data['parent_campaign_id'] = $parentToCampaign[$parentPaymentId];
233	}
234
235	$result[$donationId] = $data;
236	}
237	}
238
239	return $result;
240	}
241
242	/**
243	* Get form-to-campaign mappings for multiple donations
244	*
245	* @since 4.3.2
246	*/
247	private function getBulkFormCampaignMappings(array $donationIds, array $revenueCampaigns, array $subscriptionData): array
248	{
249	// Filter out donations that already have campaign IDs
250	$remainingDonationIds = array_filter($donationIds, function($id) use ($revenueCampaigns, $subscriptionData) {
251	return !isset($revenueCampaigns[$id]) && !isset($subscriptionData[$id]['parent_campaign_id']);
252	});
253
254	if (empty($remainingDonationIds)) {
255	return [];
256	}
257
258	// First try to get form IDs from parent payments for donations that have subscription data but no parent campaign
259	$parentFormIds = [];
260	foreach ($subscriptionData as $donationId => $data) {
261	if (!isset($revenueCampaigns[$donationId]) && !isset($data['parent_campaign_id']) && isset($data['parent_payment_id'])) {
262	$parentFormIds[$donationId] = $data['parent_payment_id'];
263	}
264	}
265
266	$formMappings = [];
267
268	// Get form IDs from parent payments
269	if (!empty($parentFormIds)) {
270	$parentForms = DB::table('give_donationmeta')
271	->select('donation_id', 'meta_value as form_id')
272	->whereIn('donation_id', array_values($parentFormIds))
273	->where('meta_key', '_give_payment_form_id')
274	->getAll();
275
276	foreach ($parentForms as $row) {
277	// Map back to original donation ID
278	$originalDonationId = array_search($row->donation_id, $parentFormIds);
279	if ($originalDonationId !== false) {
280	$formMappings[$originalDonationId] = (int)$row->form_id;
281	}
282	}
283	}
284
285	// Get form IDs directly from remaining donations
286	$directFormIds = DB::table('give_donationmeta')
287	->select('donation_id', 'meta_value as form_id')
288	->whereIn('donation_id', $remainingDonationIds)
289	->where('meta_key', '_give_payment_form_id')
290	->getAll();
291
292	foreach ($directFormIds as $row) {
293	if (!isset($formMappings[$row->donation_id])) {
294	$formMappings[$row->donation_id] = (int)$row->form_id;
295	}
296	}
297
298	if (empty($formMappings)) {
299	return [];
300	}
301
302	// Get campaign mappings for all form IDs
303	$formIds = array_unique(array_values($formMappings));
304	$formToCampaign = DB::table('give_campaign_forms')
305	->select('form_id', 'campaign_id')
306	->whereIn('form_id', $formIds)
307	->getAll();
308
309	$formCampaignMap = [];
310	foreach ($formToCampaign as $row) {
311	$formCampaignMap[$row->form_id] = (int)$row->campaign_id;
312	}
313
314	// Map back to donations
315	$result = [];
316	foreach ($formMappings as $donationId => $formId) {
317	if (isset($formCampaignMap[$formId])) {
318	$result[$donationId] = $formCampaignMap[$formId];
319	}
320	}
321
322	return $result;
323	}
324
325	/**
326	* Bulk insert meta data while avoiding duplicates
327	*
328	* @since 4.3.2
329	*/
330	private function bulkInsertMeta(array $metaInserts): void
331	{
332	if (empty($metaInserts)) {
333	return;
334	}
335
336	global $wpdb;
337
338	// Extract donation IDs to check for existing meta
339	$donationIds = array_column($metaInserts, 'donation_id');
340
341	// Check which donations already have the meta key
342	$existingMeta = DB::table('give_donationmeta')
343	->select('donation_id')
344	->whereIn('donation_id', $donationIds)
345	->where('meta_key', '_give_campaign_id')
346	->getAll();
347
348	$existingDonationIds = array_column($existingMeta, 'donation_id');
349
350	// Filter out donations that already have the meta key
351	$filteredMetaInserts = array_filter($metaInserts, function($meta) use ($existingDonationIds) {
352	return !in_array($meta['donation_id'], $existingDonationIds);
353	});
354
355	if (empty($filteredMetaInserts)) {
356	return;
357	}
358
359	// Perform bulk insert for remaining donations
360	$values = [];
361	$placeholders = [];
362
363	foreach ($filteredMetaInserts as $meta) {
364	$values[] = $meta['donation_id'];
365	$values[] = $meta['meta_key'];
366	$values[] = $meta['meta_value'];
367	$placeholders[] = '(%d, %s, %s)';
368	}
369
370	$sql = "INSERT INTO {$wpdb->prefix}give_donationmeta (donation_id, meta_key, meta_value) VALUES " .
371	implode(', ', $placeholders);
372
373	$wpdb->query($wpdb->prepare($sql, $values));
374	}
375
376	/**
377	* @inheritDoc
378	*/
379	public function getItemsCount(): int
380	{
381	return $this->query()->count();
382	}
383
384	/**
385	* @inheritDoc
386	*/
387	public function getBatchItemsAfter($lastId): ?array
388	{
389	$items = $this->query()
390	->select('donations.ID')
391	->where('donations.ID', $lastId, '>')
392	->orderBy('donations.ID')
393	->limit($this->getBatchSize())
394	->getAll();
395
396	if (!$items) {
397	return null;
398	}
399
400	return [
401	min($items)->ID,
402	max($items)->ID,
403	];
404	}
405
406	/**
407	* @inheritDoc
408	*/
409	public function getBatchSize(): int
410	{
411	return 100;
412	}
413
414	/**
415	* @inheritDoc
416	*/
417	public function hasMoreItemsToBatch($lastProcessedId): ?bool
418	{
419	return $this->query()
420	->where('donations.ID', $lastProcessedId, '>')
421	->count() > 0;
422	}
423	}
424