charitable / includes / data / class-charitable-donors-db.php

class-charitable-donors-db.php in Charitable – Donation & Fundraising Platform (Donation Forms, Recurring Donations & Fundraising Campaigns) 1.8.11.3, at includes/data/class-charitable-donors-db.php

463 lines 11.3 KB

Raw Download Zip

1	<?php
2	/**
3	* Charitable Donors DB class.
4	*
5	* @package Charitable/Classes/Charitable_Donors_DB
6	* @author David Bisset
7	* @copyright Copyright (c) 2023, WP Charitable LLC
8	* @license http://opensource.org/licenses/gpl-2.0.php GNU Public License
9	* @since 1.0.0
10	* @version 1.8.9.1
11	*/
12
13	// Exit if accessed directly.
14	if ( ! defined( 'ABSPATH' ) ) {
15	exit;
16	}
17
18	if ( ! class_exists( 'Charitable_Donors_DB' ) ) :
19
20	/**
21	* Charitable_Donors_DB
22	*
23	* @since 1.0.0
24	*/
25	class Charitable_Donors_DB extends Charitable_DB {
26
27	/**
28	* The version of our database table
29	*
30	* @since 1.0.0
31	*
32	* @var string
33	*/
34	public $version = '1.6.5';
35
36	/**
37	* The name of the primary column
38	*
39	* @since 1.0.0
40	*
41	* @var string
42	*/
43	public $primary_key = 'donor_id';
44
45	/**
46	* Columns found in the database.
47	*
48	* @since 1.6.5
49	*
50	* @var array
51	*/
52	protected $db_columns;
53
54	/**
55	* Set up the database table name.
56	*
57	* @since 1.0.0
58	*
59	* @global WPDB $wpdb
60	*/
61	public function __construct() {
62	global $wpdb;
63
64	$this->table_name = $wpdb->prefix . 'charitable_donors';
65	}
66
67	/**
68	* Create the table.
69	*
70	* @since 1.0.0
71	*
72	* @global WPDB $wpdb
73	*/
74	public function create_table() {
75	global $wpdb;
76
77	$charset_collate = $wpdb->get_charset_collate();
78
79	$sql = "CREATE TABLE {$this->table_name} (
80	donor_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
81	user_id bigint(20) unsigned NOT NULL,
82	email varchar(100) NOT NULL,
83	first_name varchar(255) default '',
84	last_name varchar(255) default '',
85	date_joined datetime NOT NULL default '0000-00-00 00:00:00',
86	data_erased datetime default '0000-00-00 00:00:00',
87	contact_consent tinyint(1) unsigned default NULL,
88	PRIMARY KEY (donor_id),
89	KEY user_id (user_id),
90	KEY email (email),
91	KEY data_erased (data_erased),
92	KEY contact_consent (contact_consent)
93	) $charset_collate;";
94
95	$this->_create_table( $sql );
96	}
97
98	/**
99	* Return the list of tables that currently exist in the database.
100	*
101	* This allows us to handle backwards compatibility in cases where
102	* columns have been added to the table structure, but have not yet
103	* been added in the database.
104	*
105	* @since 1.6.5
106	*
107	* @global WPDB $wpdb
108	* @return array
109	*/
110	public function get_db_columns() {
111	if ( ! isset( $this->db_columns ) ) {
112	global $wpdb;
113
114	$columns = $wpdb->get_results( "SHOW COLUMNS FROM $this->table_name" ); // phpcs:ignore
115	$this->db_columns = $columns ? wp_list_pluck( $columns, 'Field' ) : array();
116	}
117
118	return $this->db_columns;
119	}
120
121	/**
122	* Whitelist of columns.
123	*
124	* @since 1.0.0
125	*
126	* @return array
127	*/
128	public function get_columns() {
129	$columns = array(
130	'donor_id' => '%d',
131	'user_id' => '%d',
132	'email' => '%s',
133	'first_name' => '%s',
134	'last_name' => '%s',
135	'date_joined' => '%s',
136	'data_erased' => '%s',
137	'contact_consent' => '%d',
138	);
139
140	return array_intersect_key( $columns, array_flip( $this->get_db_columns() ) );
141	}
142
143	/**
144	* Default column values.
145	*
146	* @since 1.0.0
147	* @version 1.8.9.1
148	*
149	* @return array
150	*/
151	public function get_column_defaults() {
152	$defaults = array(
153	'user_id' => 0,
154	'date_joined' => date( 'Y-m-d H:i:s' ), // phpcs:ignore WordPress.DateTime.RestrictedFunctions.date_date
155	);
156
157	return array_intersect_key( $defaults, array_flip( $this->get_db_columns() ) );
158	}
159
160	/**
161	* Add a new donor.
162	*
163	* @since 1.0.0
164	*
165	* @param array $data Donor data to insert.
166	* @param string $type Should always be 'donors'.
167	* @return int The ID of the inserted donor.
168	*/
169	public function insert( $data, $type = 'donors' ) {
170	if ( ! $this->validate_donor_email( $data ) ) {
171	charitable_get_notices()->add_error(
172	__( 'Unable to insert donor. This email address is already in use.', 'charitable' )
173	);
174	return 0;
175	}
176
177	$donor_id = parent::insert( $data, $type );
178
179	$this->maybe_log_consent( $data, $donor_id );
180
181	return $donor_id;
182	}
183
184	/**
185	* Update a donor record.
186	*
187	* @since 1.0.0
188	*
189	* @param int $row_id The record to update.
190	* @param array $data Donor data to update.
191	* @param string $where Column used in where argument.
192	* @return boolean Whether the donor record was updated.
193	*/
194	public function update( $row_id, $data = array(), $where = 'donor_id' ) {
195	if ( ! $this->validate_donor_email( $data, $row_id, $where ) ) {
196	charitable_get_notices()->add_error(
197	__( 'Unable to update donor. This email address is already in use.', 'charitable' )
198	);
199	return false;
200	}
201
202	$updated = parent::update( $row_id, $data, $where );
203
204	if ( $updated ) {
205	$this->maybe_log_consent( $data, $row_id, $where );
206	}
207
208	return $updated;
209	}
210
211	/**
212	* Validate a donor's email.
213	*
214	* @since 1.6.2
215	*
216	* @param array $data The donor data.
217	* @param mixed $row_id The id of the donor record to update.
218	* @param string $where Column used in where argument.
219	* @return boolean
220	*/
221	public function validate_donor_email( $data, $row_id = 0, $where = 'donor_id' ) {
222	if ( ! array_key_exists( 'email', $data ) ) {
223	return true;
224	}
225
226	/* We're anonymizing their email address. */
227	if ( $this->is_anonymized_email( $data['email'] ) ) {
228	return true;
229	}
230
231	$email_donor_id = $this->get_donor_id_by_email( $data['email'] );
232
233	/* No one else has this email address. */
234	if ( 0 == $email_donor_id ) {
235	return true;
236	}
237
238	/* This is a new donor, but the email address already belongs to another. */
239	if ( 0 == $row_id ) {
240	return false;
241	}
242
243	/* For an existing donor, the email address isn't being changed. */
244	return $this->get_column_by( 'email', $where, $row_id ) == $data['email'];
245	}
246
247	/**
248	* Log consent after a donor record has been added or updated, if contact_consent
249	* was explicitly set.
250	*
251	* @since 1.6.2
252	*
253	* @param array $data The donor data.
254	* @param mixed $row_id The id of the donor record to update.
255	* @param string $where Column used in where argument.
256	* @return boolean Whether consent was logged.
257	*/
258	public function maybe_log_consent( $data, $row_id, $where = 'donor_id' ) {
259	if ( ! array_key_exists( 'contact_consent', $data ) \|\| is_null( $data['contact_consent'] ) ) {
260	return false;
261	}
262
263	$donor_id = 'donor_id' == $where ? $row_id : $this->get_column_by( 'donor_id', $where, $row_id );
264	$log = new Charitable_Donor_Consent_Log( $donor_id );
265
266	return $log->add(
267	$data['contact_consent'],
268	charitable_get_option( 'contact_consent_label', __( 'Yes, I am happy for you to contact me via email or phone.', 'charitable' ) )
269	);
270	}
271
272	/**
273	* Return a user's ID, based on their donor ID.
274	*
275	* @since 1.0.0
276	*
277	* @param int $donor_id The Donor ID.
278	* @return int
279	*/
280	public function get_user_id( $donor_id ) {
281	$user_id = $this->get_column_by( 'user_id', 'donor_id', $donor_id );
282
283	return is_null( $user_id ) ? 0 : (int) $user_id;
284	}
285
286	/**
287	* Return a donor ID, based on their user ID.
288	*
289	* @since 1.0.0
290	*
291	* @param int $user_id Donor User ID.
292	* @return int
293	*/
294	public function get_donor_id( $user_id ) {
295	$donor_id = $this->get_column_by( 'donor_id', 'user_id', $user_id );
296
297	return is_null( $donor_id ) ? 0 : (int) $donor_id;
298	}
299
300	/**
301	* Return a donor ID, based on their email address.
302	*
303	* @since 1.0.0
304	*
305	* @param string $email Donor email address.
306	* @return int
307	*/
308	public function get_donor_id_by_email( $email ) {
309	$donor_id = $this->get_column_by( 'donor_id', 'email', $email );
310
311	return is_null( $donor_id ) ? 0 : (int) $donor_id;
312	}
313
314	/**
315	* Return a donor's personal data, given their email address.
316	*
317	* @since 1.6.0
318	*
319	* @global $wpdb WPDB
320	* @param string $email Donor email address.
321	* @return array\|object\|null Database query results
322	*/
323	public function get_personal_data( $email ) {
324	global $wpdb;
325
326	return $wpdb->get_results( // phpcs:ignore
327	$wpdb->prepare(
328	"SELECT donor_id, email, first_name, last_name FROM {$this->table_name} WHERE email = %s;", // phpcs:ignore
329	$email
330	)
331	);
332	}
333
334	/**
335	* Return a donor's personal data, given their donor id.
336	*
337	* @since 1.8.1
338	*
339	* @global $wpdb WPDB
340	* @param int $donor_id Donor ID.
341	* @return array\|object\|null Database query results
342	*/
343	public function get_personal_data_by_donor_id( $donor_id = 0 ) {
344	global $wpdb;
345
346	if ( 0 === $donor_id ) {
347	return array();
348	}
349
350	return $wpdb->get_results( // phpcs:ignore
351	$wpdb->prepare(
352	"SELECT donor_id, email, first_name, last_name, user_id FROM {$this->table_name} WHERE donor_id = %d;", // phpcs:ignore
353	$donor_id
354	)
355	);
356	}
357
358	/**
359	* Count the number of donors with donations.
360	*
361	* @since 1.3.4
362	*
363	* @global WPDB $wpdb
364	* @param string\|array $statuses One or more donation statuses.
365	* @return int
366	*/
367	public function count_donors_with_donations( $statuses = array( 'charitable-completed' ) ) {
368	global $wpdb;
369
370	if ( ! is_array( $statuses ) ) {
371	$statuses = array( $statuses );
372	}
373
374	if ( empty( $statuses ) ) {
375	$status_clause = '';
376	} else {
377	$statuses = array_filter( $statuses, 'charitable_is_valid_donation_status' );
378	$placeholders = array_fill( 0, count( $statuses ), '%s' );
379	$in = implode( ', ', $placeholders );
380	$status_clause = "AND p.post_status IN ( $in )";
381	}
382
383	$sql = "SELECT COUNT( DISTINCT(d.donor_id) )
384	FROM {$wpdb->prefix}charitable_donors d
385	INNER JOIN {$wpdb->prefix}charitable_campaign_donations cd ON cd.donor_id = d.donor_id
386	INNER JOIN $wpdb->posts p ON cd.donation_id = p.ID
387	WHERE 1 = 1
388	$status_clause;";
389
390	return $wpdb->get_var( $wpdb->prepare( $sql, $statuses ) ); // phpcs:ignore
391	}
392
393	/**
394	* Erase personal data for given donor IDs.
395	*
396	* @since 1.6.0
397	*
398	* @param int\|int[] $donor_id The donor IDs.
399	* @return int\|false Number of rows affected/selected or false on error.
400	*/
401	public function erase_donor_data( $donor_id ) {
402	global $wpdb;
403
404	if ( ! is_array( $donor_id ) ) {
405	$donor_id = array( $donor_id );
406	}
407
408	/* Filter out any non absolute integers. */
409	$donor_id = array_filter( $donor_id, 'absint' );
410
411	if ( empty( $donor_id ) ) {
412	return false;
413	}
414
415	$placeholders = charitable_get_query_placeholders( count( $donor_id ), '%d' );
416
417	if ( in_array( 'data_erased', $this->get_db_columns() ) ) {
418
419	$parameters = array_merge(
420	array(
421	wp_privacy_anonymize_data( 'email' ),
422	current_time( 'mysql', 0 ),
423	),
424	$donor_id
425	);
426
427	$sql = "UPDATE {$this->table_name}
428	SET email = %s, first_name = '', last_name = '', data_erased = %s
429	WHERE donor_id IN ( $placeholders )";
430
431	} else {
432
433	$parameters = array_merge(
434	array(
435	wp_privacy_anonymize_data( 'email' ),
436	),
437	$donor_id
438	);
439
440	$sql = "UPDATE {$this->table_name}
441	SET email = %s, first_name = '', last_name = ''
442	WHERE donor_id IN ( $placeholders )";
443
444	}
445
446	return $wpdb->query( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore
447	}
448
449	/**
450	* Checks whether the passed email is an anonymized email address.
451	*
452	* @since 1.6.4
453	*
454	* @param string $email The email address.
455	* @return boolean
456	*/
457	private function is_anonymized_email( $email ) {
458	return function_exists( 'wp_privacy_anonymize_data' ) && wp_privacy_anonymize_data( 'email' ) === $email;
459	}
460	}
461
462	endif;
463