suredonation / inc / database / tables / donors.php

donors.php in SureDonation – Donation Forms, Fundraising Campaigns & Donor Management 0.0.1, at inc/database/tables/donors.php

656 lines 15.6 KB

Raw Download Zip

1	<?php
2	/**
3	* SureDonation Database Donors Table Class.
4	*
5	* @package SureDonation
6	*/
7
8	namespace SureDonation\Inc\Database\Tables;
9
10	use SureDonation\Inc\Database\Base;
11	use SureDonation\Inc\Traits\Get_Instance;
12
13	// Exit if accessed directly.
14	defined( 'ABSPATH' ) \|\| exit;
15
16	/**
17	* SureDonation Database Donors Table Class.
18	*
19	* @since 0.0.1
20	*/
21	class Donors extends Base {
22	use Get_Instance;
23
24	/**
25	* Table suffix.
26	*
27	* @var string
28	* @since 0.0.1
29	*/
30	protected $table_suffix = 'donors';
31
32	/**
33	* Table version.
34	*
35	* @var int
36	* @since 0.0.1
37	*/
38	protected $table_version = 2;
39
40	/**
41	* Valid donor statuses.
42	*
43	* @var array<string>
44	* @since 0.0.1
45	*/
46	private static $valid_statuses = [
47	'active',
48	'inactive',
49	'blocked',
50	];
51
52	/**
53	* Valid order columns.
54	*
55	* @var array<string>
56	* @since 0.0.1
57	*/
58	private static $valid_order_columns = [
59	'id',
60	'email',
61	'name',
62	'total_donated',
63	'donation_count',
64	'created_at',
65	'updated_at',
66	'last_donation_date',
67	];
68
69	/**
70	* {@inheritDoc}
71	*/
72	public function get_schema() {
73	return [
74	'id' => [
75	'type' => 'number',
76	],
77	'email' => [
78	'type' => 'string',
79	],
80	'name' => [
81	'type' => 'string',
82	'default' => '',
83	],
84	'phone' => [
85	'type' => 'string',
86	'default' => '',
87	],
88	'user_id' => [
89	'type' => 'number',
90	'default' => 0,
91	],
92	'total_donated' => [
93	'type' => 'decimal',
94	'default' => 0,
95	],
96	'donation_count' => [
97	'type' => 'number',
98	'default' => 0,
99	],
100	'largest_donation' => [
101	'type' => 'decimal',
102	'default' => 0,
103	],
104	'first_donation_date' => [
105	'type' => 'datetime',
106	],
107	'last_donation_date' => [
108	'type' => 'datetime',
109	],
110	'donor_tags' => [
111	'type' => 'array',
112	'default' => [],
113	],
114	'donor_status' => [
115	'type' => 'string',
116	'default' => 'active',
117	],
118	'donor_data' => [
119	'type' => 'array',
120	'default' => [],
121	],
122	'stripe_customer_id' => [
123	'type' => 'string',
124	'default' => '',
125	],
126	'created_at' => [
127	'type' => 'datetime',
128	],
129	'updated_at' => [
130	'type' => 'datetime',
131	],
132	];
133	}
134
135	/**
136	* {@inheritDoc}
137	*/
138	public function get_columns_definition() {
139	return [
140	'id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
141	'email VARCHAR(255) NOT NULL UNIQUE',
142	'name VARCHAR(255) NOT NULL',
143	'phone VARCHAR(50) NOT NULL',
144	'user_id BIGINT(20) UNSIGNED NULL',
145	'total_donated DECIMAL(26,8) NOT NULL DEFAULT 0',
146	'donation_count INT(11) NOT NULL DEFAULT 0',
147	'largest_donation DECIMAL(26,8) NOT NULL DEFAULT 0',
148	'first_donation_date TIMESTAMP NULL',
149	'last_donation_date TIMESTAMP NULL',
150	'donor_tags LONGTEXT',
151	'donor_status VARCHAR(20) NOT NULL',
152	'donor_data LONGTEXT',
153	'stripe_customer_id VARCHAR(255) DEFAULT NULL',
154	'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
155	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
156	'INDEX idx_email (email)',
157	'INDEX idx_user (user_id)',
158	'INDEX idx_total (total_donated)',
159	'INDEX idx_status (donor_status)',
160	];
161	}
162
163	/**
164	* Add a new donor record.
165	*
166	* @param array<mixed> $data Donor data to insert.
167	* @return int\|false The donor ID on success, false on error.
168	* @since 0.0.1
169	*/
170	public static function add( $data ) {
171	if ( empty( $data['email'] ) ) {
172	return false;
173	}
174
175	$instance = self::get_instance();
176
177	// Set created_at if not provided.
178	if ( ! isset( $data['created_at'] ) ) {
179	$data['created_at'] = current_time( 'mysql' );
180	}
181
182	return $instance->use_insert( $data );
183	}
184
185	/**
186	* Update a donor record.
187	*
188	* @param int $donor_id Donor ID to update.
189	* @param array<string,mixed> $data Data to update.
190	* @return int\|false Number of rows updated or false on error.
191	* @since 0.0.1
192	*/
193	public static function update( $donor_id, $data = [] ) {
194	if ( empty( $donor_id ) ) {
195	return false;
196	}
197
198	$data['updated_at'] = current_time( 'mysql' );
199
200	return self::get_instance()->use_update( $data, [ 'id' => absint( $donor_id ) ] );
201	}
202
203	/**
204	* Get a single donor by ID.
205	*
206	* @param int $donor_id Donor ID.
207	* @return array<mixed>\|null Donor data or null if not found.
208	* @since 0.0.1
209	*/
210	public static function get( $donor_id ) {
211	if ( empty( $donor_id ) ) {
212	return null;
213	}
214
215	$instance = self::get_instance();
216	global $wpdb;
217
218	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
219	$result = $wpdb->get_row(
220	$wpdb->prepare(
221	'SELECT * FROM %i WHERE id = %d',
222	$instance->get_tablename(),
223	absint( $donor_id )
224	),
225	ARRAY_A
226	);
227
228	if ( ! $result ) {
229	return null;
230	}
231
232	return $instance->decode_by_datatype( $result );
233	}
234
235	/**
236	* Get donor by email.
237	*
238	* @param string $email Donor email.
239	* @return array<mixed>\|null Donor data or null if not found.
240	* @since 0.0.1
241	*/
242	public static function get_by_email( $email ) {
243	if ( empty( $email ) ) {
244	return null;
245	}
246
247	$instance = self::get_instance();
248	global $wpdb;
249
250	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
251	$result = $wpdb->get_row(
252	$wpdb->prepare(
253	'SELECT * FROM %i WHERE email = %s',
254	$instance->get_tablename(),
255	sanitize_email( $email )
256	),
257	ARRAY_A
258	);
259
260	if ( ! $result ) {
261	return null;
262	}
263
264	return $instance->decode_by_datatype( $result );
265	}
266
267	/**
268	* Get all donors with pagination.
269	*
270	* @param int $limit Number of records to return.
271	* @param int $offset Offset for pagination.
272	* @param string $orderby Column to order by.
273	* @param string $order Order direction (ASC or DESC).
274	* @return array<mixed> Array of donors.
275	* @since 0.0.1
276	*/
277	public static function get_all( $limit = 10, $offset = 0, $orderby = 'created_at', $order = 'DESC' ) {
278	$instance = self::get_instance();
279	global $wpdb;
280	$table = $instance->get_tablename();
281
282	// Validate orderby column.
283	if ( ! in_array( $orderby, self::$valid_order_columns, true ) ) {
284	$orderby = 'created_at';
285	}
286
287	// Validate order direction.
288	$order = strtoupper( $order );
289	if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
290	$order = 'DESC';
291	}
292
293	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Data changes frequently, caching would show stale results.
294	$results = 'ASC' === $order
295	? $wpdb->get_results(
296	$wpdb->prepare(
297	'SELECT * FROM %i ORDER BY %i ASC LIMIT %d, %d',
298	$table,
299	$orderby,
300	absint( $offset ),
301	absint( $limit )
302	),
303	ARRAY_A
304	)
305	: $wpdb->get_results(
306	$wpdb->prepare(
307	'SELECT * FROM %i ORDER BY %i DESC LIMIT %d, %d',
308	$table,
309	$orderby,
310	absint( $offset ),
311	absint( $limit )
312	),
313	ARRAY_A
314	);
315	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
316
317	if ( ! $results \|\| ! is_array( $results ) ) {
318	return [];
319	}
320
321	return array_map( [ $instance, 'decode_by_datatype' ], $results );
322	}
323
324	/**
325	* Get donors by status with pagination.
326	*
327	* @param string $status Donor status.
328	* @param int $limit Number of records to return.
329	* @param int $offset Offset for pagination.
330	* @param string $orderby Column to order by.
331	* @param string $order Order direction (ASC or DESC).
332	* @return array<mixed> Array of donors.
333	* @since 0.0.1
334	*/
335	public static function get_by_status( $status, $limit = 10, $offset = 0, $orderby = 'created_at', $order = 'DESC' ) {
336	$instance = self::get_instance();
337	global $wpdb;
338	$table = $instance->get_tablename();
339
340	// Validate orderby column.
341	if ( ! in_array( $orderby, self::$valid_order_columns, true ) ) {
342	$orderby = 'created_at';
343	}
344
345	// Validate order direction.
346	$order = strtoupper( $order );
347	if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
348	$order = 'DESC';
349	}
350
351	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Data changes frequently, caching would show stale results.
352	$results = 'ASC' === $order
353	? $wpdb->get_results(
354	$wpdb->prepare(
355	'SELECT * FROM %i WHERE donor_status = %s ORDER BY %i ASC LIMIT %d, %d',
356	$table,
357	sanitize_text_field( $status ),
358	$orderby,
359	absint( $offset ),
360	absint( $limit )
361	),
362	ARRAY_A
363	)
364	: $wpdb->get_results(
365	$wpdb->prepare(
366	'SELECT * FROM %i WHERE donor_status = %s ORDER BY %i DESC LIMIT %d, %d',
367	$table,
368	sanitize_text_field( $status ),
369	$orderby,
370	absint( $offset ),
371	absint( $limit )
372	),
373	ARRAY_A
374	);
375	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
376
377	if ( ! $results \|\| ! is_array( $results ) ) {
378	return [];
379	}
380
381	return array_map( [ $instance, 'decode_by_datatype' ], $results );
382	}
383
384	/**
385	* Delete a donor.
386	*
387	* @param int $donor_id Donor ID.
388	* @return int\|false Number of rows deleted or false on error.
389	* @since 0.0.1
390	*/
391	public static function delete( $donor_id ) {
392	if ( empty( $donor_id ) ) {
393	return false;
394	}
395
396	return self::get_instance()->use_delete( [ 'id' => absint( $donor_id ) ] );
397	}
398
399	/**
400	* Get or create donor by email.
401	*
402	* @param string $email Donor email.
403	* @param string $name Donor name.
404	* @param string $phone Donor phone.
405	* @return int\|false Donor ID or false on error.
406	* @since 0.0.1
407	*/
408	public static function get_or_create( $email, $name = '', $phone = '' ) {
409	if ( empty( $email ) ) {
410	return false;
411	}
412
413	$existing = self::get_by_email( $email );
414
415	if ( $existing ) {
416	// Update name/phone if provided and different.
417	$updates = [];
418
419	if ( ! empty( $name ) && $name !== $existing['name'] ) {
420	$updates['name'] = $name;
421	}
422
423	if ( ! empty( $phone ) && $phone !== $existing['phone'] ) {
424	$updates['phone'] = $phone;
425	}
426
427	if ( ! empty( $updates ) && isset( $existing['id'] ) ) {
428	$existing_id = is_numeric( $existing['id'] ) ? (int) $existing['id'] : 0;
429	if ( $existing_id > 0 ) {
430	self::update( $existing_id, $updates );
431	}
432	}
433
434	$existing_id = isset( $existing['id'] ) && is_numeric( $existing['id'] ) ? (int) $existing['id'] : 0;
435	return $existing_id > 0 ? $existing_id : false;
436	}
437
438	// Create new donor.
439	return self::add(
440	[
441	'email' => sanitize_email( $email ),
442	'name' => sanitize_text_field( $name ),
443	'phone' => sanitize_text_field( $phone ),
444	'first_donation_date' => current_time( 'mysql' ),
445	]
446	);
447	}
448
449	/**
450	* Update donor statistics after a donation.
451	*
452	* @param int $donor_id Donor ID.
453	* @param float $amount Donation amount.
454	* @return int\|false Number of rows updated or false on error.
455	* @since 0.0.1
456	*/
457	public static function record_donation( $donor_id, $amount ) {
458	if ( empty( $donor_id ) \|\| $amount <= 0 ) {
459	return false;
460	}
461
462	$donor = self::get( $donor_id );
463
464	if ( ! $donor ) {
465	return false;
466	}
467
468	$total_value = $donor['total_donated'] ?? 0;
469	$current_total = is_numeric( $total_value ) ? (float) $total_value : 0.0;
470	$count_value = $donor['donation_count'] ?? 0;
471	$current_count = is_numeric( $count_value ) ? (int) $count_value : 0;
472	$largest_value = $donor['largest_donation'] ?? 0;
473	$current_largest = is_numeric( $largest_value ) ? (float) $largest_value : 0.0;
474
475	$updates = [
476	'total_donated' => $current_total + $amount,
477	'donation_count' => $current_count + 1,
478	'last_donation_date' => current_time( 'mysql' ),
479	];
480
481	if ( $amount > $current_largest ) {
482	$updates['largest_donation'] = $amount;
483	}
484
485	return self::update( $donor_id, $updates );
486	}
487
488	/**
489	* Get top donors by total donated.
490	*
491	* @param int $limit Number of donors to retrieve.
492	* @return array<int, array<string, mixed>> Array of top donors.
493	* @since 0.0.1
494	*/
495	public static function get_top_donors( $limit = 10 ) {
496	$instance = self::get_instance();
497	global $wpdb;
498
499	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
500	$results = $wpdb->get_results(
501	$wpdb->prepare(
502	'SELECT * FROM %i WHERE donor_status = %s ORDER BY total_donated DESC LIMIT %d',
503	$instance->get_tablename(),
504	'active',
505	absint( $limit )
506	),
507	ARRAY_A
508	);
509
510	if ( ! $results \|\| ! is_array( $results ) ) {
511	return [];
512	}
513
514	return array_map( [ $instance, 'decode_by_datatype' ], $results );
515	}
516
517	/**
518	* Get total donors count.
519	*
520	* @return int Total count.
521	* @since 0.0.1
522	*/
523	public static function count_all() {
524	$instance = self::get_instance();
525	global $wpdb;
526
527	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
528	$count = $wpdb->get_var(
529	$wpdb->prepare(
530	'SELECT COUNT(*) FROM %i',
531	$instance->get_tablename()
532	)
533	);
534
535	return is_numeric( $count ) ? (int) $count : 0;
536	}
537
538	/**
539	* Get total donors count by status.
540	*
541	* @param string $status Donor status ('all' for no filter).
542	* @return int Total count.
543	* @since 0.0.1
544	*/
545	public static function get_total_donors( $status = 'all' ) {
546	$instance = self::get_instance();
547	global $wpdb;
548
549	if ( 'all' === $status ) {
550	return self::count_all();
551	}
552
553	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
554	$count = $wpdb->get_var(
555	$wpdb->prepare(
556	'SELECT COUNT(*) FROM %i WHERE donor_status = %s',
557	$instance->get_tablename(),
558	sanitize_text_field( $status )
559	)
560	);
561
562	return is_numeric( $count ) ? (int) $count : 0;
563	}
564
565	/**
566	* Get valid donor statuses.
567	*
568	* @return array<string> Valid statuses.
569	* @since 0.0.1
570	*/
571	public static function get_valid_statuses() {
572	return self::$valid_statuses;
573	}
574
575	/**
576	* Get Stripe customer ID for a donor by email.
577	*
578	* @param string $email Donor email.
579	* @return string\|null Stripe customer ID or null if not found.
580	* @since 0.0.1
581	*/
582	public static function get_stripe_customer_id_by_email( $email ) {
583	if ( empty( $email ) ) {
584	return null;
585	}
586
587	$donor = self::get_by_email( $email );
588
589	if ( $donor && ! empty( $donor['stripe_customer_id'] ) && is_string( $donor['stripe_customer_id'] ) ) {
590	return $donor['stripe_customer_id'];
591	}
592
593	return null;
594	}
595
596	/**
597	* Update Stripe customer ID for a donor by email.
598	*
599	* @param string $email Donor email.
600	* @param string $stripe_customer_id Stripe customer ID.
601	* @return bool True on success, false on failure.
602	* @since 0.0.1
603	*/
604	public static function set_stripe_customer_id_by_email( $email, $stripe_customer_id ) {
605	if ( empty( $email ) \|\| empty( $stripe_customer_id ) ) {
606	return false;
607	}
608
609	$donor = self::get_by_email( $email );
610
611	if ( ! $donor \|\| empty( $donor['id'] ) ) {
612	return false;
613	}
614
615	$donor_id = is_numeric( $donor['id'] ) ? (int) $donor['id'] : 0;
616	if ( $donor_id <= 0 ) {
617	return false;
618	}
619
620	$result = self::update( $donor_id, [ 'stripe_customer_id' => sanitize_text_field( $stripe_customer_id ) ] );
621
622	return false !== $result;
623	}
624
625	/**
626	* Clear Stripe customer ID for a donor by email.
627	*
628	* This is used when a cached customer ID is no longer valid
629	* (e.g., customer was deleted from Stripe or mode switched).
630	*
631	* @param string $email Donor email.
632	* @return bool True on success, false on failure.
633	* @since 0.0.1
634	*/
635	public static function clear_stripe_customer_id_by_email( $email ) {
636	if ( empty( $email ) ) {
637	return false;
638	}
639
640	$donor = self::get_by_email( $email );
641
642	if ( ! $donor \|\| empty( $donor['id'] ) ) {
643	return false;
644	}
645
646	$donor_id = is_numeric( $donor['id'] ) ? (int) $donor['id'] : 0;
647	if ( $donor_id <= 0 ) {
648	return false;
649	}
650
651	$result = self::update( $donor_id, [ 'stripe_customer_id' => '' ] );
652
653	return false !== $result;
654	}
655	}
656