← All changes | inc/database/tables/donations.php +303 -37 0.0.1 → 1.1.0 View file →

		@@ -6,9 +6,11 @@
6	6	*/
7	7
8	8	namespace SureDonation\Inc\Database\Tables;
9	9
	10	+use SureDonation\Inc\Campaigns\Campaign_Stats;
10	11	use SureDonation\Inc\Database\Base;
	12	+use SureDonation\Inc\Helper;
11	13	use SureDonation\Inc\Traits\Get_Instance;
12	14
13	15	// Exit if accessed directly.
14	16	defined( 'ABSPATH' ) || exit;
		@@ -34,9 +36,9 @@
34	36	*
35	37	* @var int
36	38	* @since 0.0.1
37	39	*/
38		- protected $table_version = 1;
	40	+ protected $table_version = 4;
39	41
40	42	/**
41	43	* Valid payment statuses.
42	44	*
		@@ -68,8 +70,10 @@
68	70	'updated_at',
69	71	'payment_status',
70	72	'donor_name',
71	73	'donor_email',
	74	+ 'subscription_status',
	75	+ 'subscription_id',
72	76	];
73	77
74	78	/**
75	79	* {@inheritDoc}
		@@ -75,114 +79,134 @@
75	79	* {@inheritDoc}
76	80	*/
77	81	public function get_schema() {
78	82	return [
79		- 'id' => [
	83	+ 'id' => [
80	84	'type' => 'number',
81	85	],
82		- 'campaign_id' => [
	86	+ 'campaign_id' => [
83	87	'type' => 'number',
84	88	],
85		- 'donor_id' => [
	89	+ 'donor_id' => [
86	90	'type' => 'number',
87	91	'default' => 0,
88	92	],
89		- 'form_id' => [
	93	+ 'form_id' => [
90	94	'type' => 'number',
91	95	'default' => 0,
92	96	],
93		- 'amount' => [
	97	+ 'amount' => [
94	98	'type' => 'string',
95	99	'default' => '0.00000000',
96	100	],
97		- 'fees_covered' => [
	101	+ 'fees_covered' => [
98	102	'type' => 'string',
99	103	'default' => '0.00000000',
100	104	],
101		- 'refunded_amount' => [
	105	+ 'refunded_amount' => [
102	106	'type' => 'string',
103	107	'default' => '0.00000000',
104	108	],
105		- 'currency' => [
	109	+ 'currency' => [
106	110	'type' => 'string',
107	111	'default' => 'USD',
108	112	],
109		- 'transaction_id' => [
	113	+ 'transaction_id' => [
110	114	'type' => 'string',
111	115	'default' => '',
112	116	],
113		- 'customer_id' => [
	117	+ 'customer_id' => [
114	118	'type' => 'string',
115	119	'default' => '',
116	120	],
117		- 'gateway' => [
	121	+ 'gateway' => [
118	122	'type' => 'string',
119	123	'default' => 'stripe',
120	124	],
121		- 'payment_status' => [
	125	+ 'payment_status' => [
122	126	'type' => 'string',
123	127	'default' => 'pending',
124	128	],
125		- 'payment_mode' => [
	129	+ 'payment_mode' => [
126	130	'type' => 'string',
127	131	'default' => 'test',
128	132	],
129		- 'donor_name' => [
	133	+ 'donor_name' => [
130	134	'type' => 'string',
131	135	'default' => '',
132	136	],
133		- 'donor_email' => [
	137	+ 'donor_email' => [
134	138	'type' => 'string',
135	139	'default' => '',
136	140	],
137		- 'donor_phone' => [
	141	+ 'donor_phone' => [
138	142	'type' => 'string',
139	143	'default' => '',
140	144	],
141		- 'is_anonymous' => [
	145	+ 'is_anonymous' => [
142	146	'type' => 'boolean',
143	147	'default' => false,
144	148	],
145		- 'donation_type' => [
	149	+ 'donation_type' => [
146	150	'type' => 'string',
147	151	'default' => 'one-time',
148	152	],
149		- 'donor_comment' => [
	153	+ 'subscription_id' => [
150	154	'type' => 'string',
151	155	'default' => '',
152	156	],
153		- 'receipt_sent' => [
	157	+ 'subscription_status' => [
	158	+ 'type' => 'string',
	159	+ 'default' => '',
	160	+ ],
	161	+ 'parent_subscription_id' => [
	162	+ 'type' => 'number',
	163	+ 'default' => 0,
	164	+ ],
	165	+ 'donor_comment' => [
	166	+ 'type' => 'string',
	167	+ 'default' => '',
	168	+ ],
	169	+ 'receipt_sent' => [
154	170	'type' => 'boolean',
155	171	'default' => false,
156	172	],
157		- 'receipt_pdf_url' => [
	173	+ 'receipt_pdf_url' => [
158	174	'type' => 'string',
159	175	'default' => '',
160	176	],
161		- 'donation_data' => [
	177	+ 'donation_data' => [
162	178	'type' => 'array',
163	179	'default' => [],
164	180	],
165		- 'log' => [
	181	+ 'log' => [
166	182	'type' => 'array',
167	183	'default' => [],
168	184	],
169		- 'ip_address' => [
	185	+ 'ip_address' => [
170	186	'type' => 'string',
171	187	'default' => '',
172	188	],
173		- 'user_agent' => [
	189	+ 'user_agent' => [
174	190	'type' => 'string',
175	191	'default' => '',
176	192	],
177		- 'referer_url' => [
	193	+ 'referer_url' => [
178	194	'type' => 'string',
179	195	'default' => '',
180	196	],
181		- 'created_at' => [
	197	+ 'import_source_id' => [
	198	+ 'type' => 'number',
	199	+ 'default' => 0,
	200	+ ],
	201	+ 'import_source' => [
	202	+ 'type' => 'string',
	203	+ 'default' => '',
	204	+ ],
	205	+ 'created_at' => [
182	206	'type' => 'datetime',
183	207	],
184		- 'updated_at' => [
	208	+ 'updated_at' => [
185	209	'type' => 'datetime',
186	210	],
187	211	];
188	212	}
		@@ -209,8 +233,11 @@
209	233	'donor_email VARCHAR(255) NOT NULL',
210	234	'donor_phone VARCHAR(50) NOT NULL',
211	235	'is_anonymous TINYINT(1) NOT NULL DEFAULT 0',
212	236	'donation_type VARCHAR(30) NOT NULL',
	237	+ 'subscription_id VARCHAR(255) NOT NULL',
	238	+ 'subscription_status VARCHAR(30) NOT NULL',
	239	+ 'parent_subscription_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
213	240	'donor_comment TEXT',
214	241	'receipt_sent TINYINT(1) NOT NULL DEFAULT 0',
215	242	'receipt_pdf_url VARCHAR(255) NOT NULL',
216	243	'donation_data LONGTEXT',
		@@ -217,8 +244,10 @@
217	244	'log LONGTEXT',
218	245	'ip_address VARCHAR(45) NOT NULL',
219	246	'user_agent TEXT',
220	247	'referer_url TEXT',
	248	+ 'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
	249	+ 'import_source VARCHAR(20) NOT NULL DEFAULT ""',
221	250	'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
222	251	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
223	252	'INDEX idx_campaign (campaign_id)',
224	253	'INDEX idx_donor (donor_id)',
		@@ -225,12 +254,41 @@
225	254	'INDEX idx_status (payment_status)',
226	255	'INDEX idx_email (donor_email)',
227	256	'INDEX idx_created (created_at)',
228	257	'INDEX idx_form (form_id)',
	258	+ 'INDEX idx_subscription (subscription_id)',
	259	+ 'INDEX idx_subscription_status (subscription_status)',
	260	+ 'INDEX idx_parent_subscription (parent_subscription_id)',
	261	+ 'INDEX idx_import_source (import_source_id, import_source)',
229	262	];
230	263	}
231	264
232	265	/**
	266	+ * New columns added across versions.
	267	+ *
	268	+ * Version 2 added subscription support; version 4 added the
	269	+ * source-agnostic pair `import_source_id` + `import_source` used by
	270	+ * the migration tool for duplicate detection and rollback.
	271	+ *
	272	+ * {@inheritDoc}
	273	+ *
	274	+ * @since 1.0.0
	275	+ */
	276	+ public function get_new_columns_definition() {
	277	+ return [
	278	+ 'subscription_id VARCHAR(255) NOT NULL AFTER donation_type',
	279	+ 'subscription_status VARCHAR(30) NOT NULL AFTER subscription_id',
	280	+ 'parent_subscription_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER subscription_status',
	281	+ 'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER referer_url',
	282	+ 'import_source VARCHAR(20) NOT NULL DEFAULT "" AFTER import_source_id',
	283	+ 'INDEX idx_subscription (subscription_id)',
	284	+ 'INDEX idx_subscription_status (subscription_status)',
	285	+ 'INDEX idx_parent_subscription (parent_subscription_id)',
	286	+ 'INDEX idx_import_source (import_source_id, import_source)',
	287	+ ];
	288	+ }
	289	+
	290	+ /**
233	291	* Add a new donation record.
234	292	*
235	293	* @param array<mixed> $data Donation data to insert.
236	294	* @return int|false The donation ID on success, false on error.
		@@ -236,20 +294,45 @@
236	294	* @return int|false The donation ID on success, false on error.
237	295	* @since 0.0.1
238	296	*/
239	297	public static function add( $data ) {
240		- if ( empty( $data['campaign_id'] ) ) {
	298	+ // Use isset check — empty() would reject campaign_id=0 which is valid for standalone forms.
	299	+ if ( ! isset( $data['campaign_id'] ) ) {
241	300	return false;
242	301	}
243	302
244	303	$instance = self::get_instance();
245	304
246		- // Set created_at if not provided.
	305	+ // Set created_at if not provided (use GMT for consistency with TIMESTAMP column default).
247	306	if ( ! isset( $data['created_at'] ) ) {
248		- $data['created_at'] = current_time( 'mysql' );
	307	+ $data['created_at'] = current_time( 'mysql', true );
249	308	}
250	309
251		- return $instance->use_insert( $data );
	310	+ $result = $instance->use_insert( $data );
	311	+
	312	+ if ( $result ) {
	313	+ Campaign_Stats::clear_cache( absint( Helper::get_string_value( $data['campaign_id'] ) ) );
	314	+
	315	+ // Notify integration hooks (e.g. OttoKit) about the new donation.
	316	+ // Imported rows carry an import_source and are skipped: migrating
	317	+ // historical donations must not replay automations.
	318	+ if ( empty( $data['import_source'] ) ) {
	319	+ $donation_id = absint( $result );
	320	+ $donation = self::get( $donation_id );
	321	+ $donation = is_array( $donation ) ? $donation : [];
	322	+
	323	+ /**
	324	+ * Fires when a new donation record is created.
	325	+ *
	326	+ * @param int $donation_id Newly created donation ID.
	327	+ * @param array<mixed> $donation Complete donation record.
	328	+ * @since 1.1.0
	329	+ */
	330	+ do_action( 'suredonation_donation_created', $donation_id, $donation );
	331	+ }
	332	+ }
	333	+
	334	+ return $result;
252	335	}
253	336
254	337	/**
255	338	* Update a donation record.
		@@ -263,12 +346,55 @@
263	346	if ( empty( $donation_id ) ) {
264	347	return false;
265	348	}
266	349
	350	+ // Capture the current status before the write so integration hooks
	351	+ // (e.g. OttoKit) can react to the actual status transition, not just
	352	+ // the resulting value.
	353	+ $old_status = '';
	354	+ if ( isset( $data['payment_status'] ) ) {
	355	+ $existing = self::get( absint( $donation_id ) );
	356	+ $old_status = is_array( $existing ) ? Helper::get_string_value( $existing['payment_status'] ?? '' ) : '';
	357	+ }
	358	+
267	359	// Set updated_at.
268	360	$data['updated_at'] = current_time( 'mysql' );
269	361
270		- return self::get_instance()->use_update( $data, [ 'id' => absint( $donation_id ) ] );
	362	+ $updated = self::get_instance()->use_update( $data, [ 'id' => absint( $donation_id ) ] );
	363	+
	364	+ // Status/amount changes (e.g. a webhook completing a pending donation)
	365	+ // affect the cached stats and donor lists.
	366	+ if ( $updated ) {
	367	+ $donation = self::get( absint( $donation_id ) );
	368	+ if ( ! empty( $donation['campaign_id'] ) ) {
	369	+ Campaign_Stats::clear_cache( absint( Helper::get_string_value( $donation['campaign_id'] ) ) );
	370	+ }
	371	+
	372	+ // Notify integration hooks about a genuine status transition.
	373	+ // Fired from update() — the single choke point every status write
	374	+ // passes through (update_status() delegates here, as do the payment
	375	+ // frontends and webhooks) — so all transitions are caught.
	376	+ if ( isset( $data['payment_status'] ) ) {
	377	+ $new_status = Helper::get_string_value( $data['payment_status'] );
	378	+
	379	+ if ( $new_status !== $old_status ) {
	380	+ $donation = is_array( $donation ) ? $donation : [];
	381	+
	382	+ /**
	383	+ * Fires when a donation's payment status changes.
	384	+ *
	385	+ * @param int $donation_id Donation ID.
	386	+ * @param string $new_status New payment status.
	387	+ * @param string $old_status Previous payment status (empty string if unknown).
	388	+ * @param array<mixed> $donation Complete donation record after the update.
	389	+ * @since 1.1.0
	390	+ */
	391	+ do_action( 'suredonation_donation_status_changed', absint( $donation_id ), $new_status, $old_status, $donation );
	392	+ }
	393	+ }
	394	+ }
	395	+
	396	+ return $updated;
271	397	}
272	398
273	399	/**
274	400	* Get a single donation by ID.
		@@ -388,8 +514,11 @@
388	514	$order = 'DESC';
389	515	}
390	516
391	517	// Build query based on filters.
	518	+ // Note: Renewal records (donation_type = 'renewal') are intentionally included in the listing.
	519	+ // They are shown alongside parent subscriptions so admins can see all transaction activity.
	520	+ // Renewals are also accessible from the parent donation's subscription detail billing history.
392	521	$has_status = 'all' !== $status;
393	522	$has_campaign = $campaign_id > 0;
394	523	$has_search = ! empty( $search );
395	524	$is_asc = 'ASC' === $order;
		@@ -752,9 +881,9 @@
752	881	return array_map( [ $instance, 'decode_by_datatype' ], $results );
753	882	}
754	883
755	884	/**
756		- * Delete a donation.
	885	+ * Delete a donation record.
757	886	*
758	887	* @param int $donation_id Donation ID.
759	888	* @return int|false Number of rows deleted or false on error.
760	889	* @since 0.0.1
		@@ -831,9 +960,9 @@
831	960	return $instance->decode_by_datatype( $result );
832	961	}
833	962
834	963	/**
835		- * Get total donations count.
	964	+ * Get total donations count (no filters).
836	965	*
837	966	* @return int Total count.
838	967	* @since 0.0.1
839	968	*/
		@@ -852,9 +981,9 @@
852	981	return is_numeric( $count ) ? (int) $count : 0;
853	982	}
854	983
855	984	/**
856		- * Get total donations count by status.
	985	+ * Get total donations count by payment status.
857	986	*
858	987	* @param string $status Payment status.
859	988	* @return int Total count.
860	989	* @since 0.0.1
		@@ -1157,8 +1286,145 @@
1157	1286	return [];
1158	1287	}
1159	1288
1160	1289	return array_map( [ $instance, 'decode_by_datatype' ], $results );
	1290	+ }
	1291	+
	1292	+ /**
	1293	+ * Get paginated donations for a specific donor.
	1294	+ *
	1295	+ * @param int $donor_id Donor ID.
	1296	+ * @param int $limit Number of records to return.
	1297	+ * @param int $offset Offset for pagination.
	1298	+ * @return array{donations: array<int, array<string, mixed>>, total: int} Paginated donations and total count.
	1299	+ * @since 1.0.0
	1300	+ */
	1301	+ public static function get_by_donor_id( $donor_id, $limit = 10, $offset = 0 ) {
	1302	+ if ( empty( $donor_id ) ) {
	1303	+ return [
	1304	+ 'donations' => [],
	1305	+ 'total' => 0,
	1306	+ ];
	1307	+ }
	1308	+
	1309	+ $instance = self::get_instance();
	1310	+ global $wpdb;
	1311	+ $table = $instance->get_tablename();
	1312	+
	1313	+ // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	1314	+
	1315	+ $total = $wpdb->get_var(
	1316	+ $wpdb->prepare(
	1317	+ 'SELECT COUNT(*) FROM %i WHERE donor_id = %d',
	1318	+ $table,
	1319	+ absint( $donor_id )
	1320	+ )
	1321	+ );
	1322	+
	1323	+ $results = $wpdb->get_results(
	1324	+ $wpdb->prepare(
	1325	+ 'SELECT * FROM %i WHERE donor_id = %d ORDER BY created_at DESC LIMIT %d, %d',
	1326	+ $table,
	1327	+ absint( $donor_id ),
	1328	+ absint( $offset ),
	1329	+ absint( $limit )
	1330	+ ),
	1331	+ ARRAY_A
	1332	+ );
	1333	+
	1334	+ // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	1335	+
	1336	+ if ( ! $results || ! is_array( $results ) ) {
	1337	+ $results = [];
	1338	+ }
	1339	+
	1340	+ return [
	1341	+ 'donations' => array_map( [ $instance, 'decode_by_datatype' ], $results ),
	1342	+ 'total' => is_numeric( $total ) ? (int) $total : 0,
	1343	+ ];
	1344	+ }
	1345	+
	1346	+ /**
	1347	+ * Get donation activity data for a specific donor (for chart).
	1348	+ *
	1349	+ * @param int $donor_id Donor ID.
	1350	+ * @param string $after Start date (Y-m-d).
	1351	+ * @param string $before End date (Y-m-d).
	1352	+ * @return array{chart_data: array<int, array{date: string, amount: float}>, stats: array{lifetime: float, highest: float, average: float}} Activity data.
	1353	+ * @since 1.0.0
	1354	+ */
	1355	+ public static function get_donor_activity( $donor_id, $after = '', $before = '' ) {
	1356	+ if ( empty( $donor_id ) ) {
	1357	+ return [
	1358	+ 'chart_data' => [],
	1359	+ 'stats' => [
	1360	+ 'lifetime' => 0,
	1361	+ 'highest' => 0,
	1362	+ 'average' => 0,
	1363	+ ],
	1364	+ ];
	1365	+ }
	1366	+
	1367	+ $instance = self::get_instance();
	1368	+ global $wpdb;
	1369	+ $table = $instance->get_tablename();
	1370	+
	1371	+ // Default date range: last 30 days.
	1372	+ if ( empty( $after ) ) {
	1373	+ $after = gmdate( 'Y-m-d', strtotime( '-30 days' ) );
	1374	+ }
	1375	+ if ( empty( $before ) ) {
	1376	+ $before = gmdate( 'Y-m-d' );
	1377	+ }
	1378	+
	1379	+ // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	1380	+
	1381	+ // Chart data: donations grouped by date.
	1382	+ $chart_data = $wpdb->get_results(
	1383	+ $wpdb->prepare(
	1384	+ "SELECT DATE(created_at) as date, COALESCE(SUM(amount), 0) as amount
	1385	+ FROM %i
	1386	+ WHERE donor_id = %d
	1387	+ AND payment_status IN ('completed', 'partially_refunded')
	1388	+ AND DATE(created_at) >= %s
	1389	+ AND DATE(created_at) <= %s
	1390	+ GROUP BY DATE(created_at)
	1391	+ ORDER BY date ASC",
	1392	+ $table,
	1393	+ absint( $donor_id ),
	1394	+ $after,
	1395	+ $before
	1396	+ ),
	1397	+ ARRAY_A
	1398	+ );
	1399	+
	1400	+ // Lifetime stats for this donor.
	1401	+ $stats = $wpdb->get_row(
	1402	+ $wpdb->prepare(
	1403	+ "SELECT
	1404	+ COALESCE(SUM(amount - refunded_amount), 0) as lifetime,
	1405	+ COALESCE(MAX(amount), 0) as highest,
	1406	+ COALESCE(AVG(amount), 0) as average
	1407	+ FROM %i
	1408	+ WHERE donor_id = %d AND payment_status IN ('completed', 'partially_refunded')",
	1409	+ $table,
	1410	+ absint( $donor_id )
	1411	+ ),
	1412	+ ARRAY_A
	1413	+ );
	1414	+
	1415	+ // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	1416	+
	1417	+ $stats = is_array( $stats ) ? $stats : [];
	1418	+
	1419	+ return [
	1420	+ 'chart_data' => is_array( $chart_data ) ? $chart_data : [],
	1421	+ 'stats' => [
	1422	+ 'lifetime' => is_numeric( $stats['lifetime'] ?? 0 ) ? round( (float) ( $stats['lifetime'] ?? 0 ), 2 ) : 0,
	1423	+ 'highest' => is_numeric( $stats['highest'] ?? 0 ) ? round( (float) ( $stats['highest'] ?? 0 ), 2 ) : 0,
	1424	+ 'average' => is_numeric( $stats['average'] ?? 0 ) ? round( (float) ( $stats['average'] ?? 0 ), 2 ) : 0,
	1425	+ ],
	1426	+ ];
1161	1427	}
1162	1428
1163	1429	/**
1164	1430	* Update donation status.