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.7.0.14, at includes/data/class-charitable-donors-db.php

438 lines 10.6 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) 2022, WP Charitable LLC
8	* @license http://opensource.org/licenses/gpl-2.0.php GNU Public License
9	* @since 1.0.0
10	* @version 1.6.5
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" );
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	*
148	* @return array
149	*/
150	public function get_column_defaults() {
151	$defaults = array(
152	'user_id' => 0,
153	'date_joined' => date( 'Y-m-d H:i:s' ),
154	);
155
156	return array_intersect_key( $defaults, array_flip( $this->get_db_columns() ) );
157	}
158
159	/**
160	* Add a new donor.
161	*
162	* @since 1.0.0
163	*
164	* @param array $data Donor data to insert.
165	* @param string $type Should always be 'donors'.
166	* @return int The ID of the inserted donor.
167	*/
168	public function insert( $data, $type = 'donors' ) {
169	if ( ! $this->validate_donor_email( $data ) ) {
170	charitable_get_notices()->add_error(
171	__( 'Unable to insert donor. This email address is already in use.', 'charitable' )
172	);
173	return 0;
174	}
175
176	$donor_id = parent::insert( $data, $type );
177
178	$this->maybe_log_consent( $data, $donor_id );
179
180	return $donor_id;
181	}
182
183	/**
184	* Update a donor record.
185	*
186	* @since 1.0.0
187	*
188	* @param int $row_id The record to update.
189	* @param array $data Donor data to update.
190	* @param string $where Column used in where argument.
191	* @return boolean Whether the donor record was updated.
192	*/
193	public function update( $row_id, $data = array(), $where = 'donor_id' ) {
194	if ( ! $this->validate_donor_email( $data, $row_id, $where ) ) {
195	charitable_get_notices()->add_error(
196	__( 'Unable to update donor. This email address is already in use.', 'charitable' )
197	);
198	return false;
199	}
200
201	$updated = parent::update( $row_id, $data, $where );
202
203	if ( $updated ) {
204	$this->maybe_log_consent( $data, $row_id, $where );
205	}
206
207	return $updated;
208	}
209
210	/**
211	* Validate a donor's email.
212	*
213	* @since 1.6.2
214	*
215	* @param array $data The donor data.
216	* @param mixed $row_id The id of the donor record to update.
217	* @param string $where Column used in where argument.
218	* @return boolean
219	*/
220	public function validate_donor_email( $data, $row_id = 0, $where = 'donor_id' ) {
221	if ( ! array_key_exists( 'email', $data ) ) {
222	return true;
223	}
224
225	/* We're anonymizing their email address. */
226	if ( $this->is_anonymized_email( $data['email'] ) ) {
227	return true;
228	}
229
230	$email_donor_id = $this->get_donor_id_by_email( $data['email'] );
231
232	/* No one else has this email address. */
233	if ( 0 == $email_donor_id ) {
234	return true;
235	}
236
237	/* This is a new donor, but the email address already belongs to another. */
238	if ( 0 == $row_id ) {
239	return false;
240	}
241
242	/* For an existing donor, the email address isn't being changed. */
243	return $this->get_column_by( 'email', $where, $row_id ) == $data['email'];
244	}
245
246	/**
247	* Log consent after a donor record has been added or updated, if contact_consent
248	* was explicitly set.
249	*
250	* @since 1.6.2
251	*
252	* @param array $data The donor data.
253	* @param mixed $row_id The id of the donor record to update.
254	* @param string $where Column used in where argument.
255	* @return boolean Whether consent was logged.
256	*/
257	public function maybe_log_consent( $data, $row_id, $where = 'donor_id' ) {
258	if ( ! array_key_exists( 'contact_consent', $data ) \|\| is_null( $data['contact_consent'] ) ) {
259	return false;
260	}
261
262	$donor_id = 'donor_id' == $where ? $row_id : $this->get_column_by( 'donor_id', $where, $row_id );
263	$log = new Charitable_Donor_Consent_Log( $donor_id );
264
265	return $log->add(
266	$data['contact_consent'],
267	charitable_get_option( 'contact_consent_label', __( 'Yes, I am happy for you to contact me via email or phone.', 'charitable' ) )
268	);
269	}
270
271	/**
272	* Return a user's ID, based on their donor ID.
273	*
274	* @since 1.0.0
275	*
276	* @param int $donor_id The Donor ID.
277	* @return int
278	*/
279	public function get_user_id( $donor_id ) {
280	$user_id = $this->get_column_by( 'user_id', 'donor_id', $donor_id );
281
282	return is_null( $user_id ) ? 0 : (int) $user_id;
283	}
284
285	/**
286	* Return a donor ID, based on their user ID.
287	*
288	* @since 1.0.0
289	*
290	* @param int $user_id Donor User ID.
291	* @return int
292	*/
293	public function get_donor_id( $user_id ) {
294	$donor_id = $this->get_column_by( 'donor_id', 'user_id', $user_id );
295
296	return is_null( $donor_id ) ? 0 : (int) $donor_id;
297	}
298
299	/**
300	* Return a donor ID, based on their email address.
301	*
302	* @since 1.0.0
303	*
304	* @param string $email Donor email address.
305	* @return int
306	*/
307	public function get_donor_id_by_email( $email ) {
308	$donor_id = $this->get_column_by( 'donor_id', 'email', $email );
309
310	return is_null( $donor_id ) ? 0 : (int) $donor_id;
311	}
312
313	/**
314	* Return a donor's personal data, given their email address.
315	*
316	* @since 1.6.0
317	*
318	* @global $wpdb WPDB
319	* @param string $email Donor email address.
320	* @return array\|object\|null Database query results
321	*/
322	public function get_personal_data( $email ) {
323	global $wpdb;
324
325	return $wpdb->get_results(
326	$wpdb->prepare(
327	"SELECT donor_id, email, first_name, last_name FROM {$this->table_name} WHERE email = %s;",
328	$email
329	)
330	);
331	}
332
333	/**
334	* Count the number of donors with donations.
335	*
336	* @since 1.3.4
337	*
338	* @global WPDB $wpdb
339	* @param string\|array $statuses One or more donation statuses.
340	* @return int
341	*/
342	public function count_donors_with_donations( $statuses = array( 'charitable-completed' ) ) {
343	global $wpdb;
344
345	if ( ! is_array( $statuses ) ) {
346	$statuses = array( $statuses );
347	}
348
349	if ( empty( $statuses ) ) {
350	$status_clause = '';
351	} else {
352	$statuses = array_filter( $statuses, 'charitable_is_valid_donation_status' );
353	$placeholders = array_fill( 0, count( $statuses ), '%s' );
354	$in = implode( ', ', $placeholders );
355	$status_clause = "AND p.post_status IN ( $in )";
356	}
357
358	$sql = "SELECT COUNT( DISTINCT(d.donor_id) )
359	FROM {$wpdb->prefix}charitable_donors d
360	INNER JOIN {$wpdb->prefix}charitable_campaign_donations cd ON cd.donor_id = d.donor_id
361	INNER JOIN $wpdb->posts p ON cd.donation_id = p.ID
362	WHERE 1 = 1
363	$status_clause;";
364
365	return $wpdb->get_var( $wpdb->prepare( $sql, $statuses ) );
366	}
367
368	/**
369	* Erase personal data for given donor IDs.
370	*
371	* @since 1.6.0
372	*
373	* @param int\|int[] $donor_id The donor IDs.
374	* @return int\|false Number of rows affected/selected or false on error.
375	*/
376	public function erase_donor_data( $donor_id ) {
377	global $wpdb;
378
379	if ( ! is_array( $donor_id ) ) {
380	$donor_id = array( $donor_id );
381	}
382
383	/* Filter out any non absolute integers. */
384	$donor_id = array_filter( $donor_id, 'absint' );
385
386	if ( empty( $donor_id ) ) {
387	return false;
388	}
389
390	$placeholders = charitable_get_query_placeholders( count( $donor_id ), '%d' );
391
392	if ( in_array( 'data_erased', $this->get_db_columns() ) ) {
393
394	$parameters = array_merge(
395	array(
396	wp_privacy_anonymize_data( 'email' ),
397	current_time( 'mysql', 0 ),
398	),
399	$donor_id
400	);
401
402	$sql = "UPDATE {$this->table_name}
403	SET email = %s, first_name = '', last_name = '', data_erased = %s
404	WHERE donor_id IN ( $placeholders )";
405
406	} else {
407
408	$parameters = array_merge(
409	array(
410	wp_privacy_anonymize_data( 'email' ),
411	),
412	$donor_id
413	);
414
415	$sql = "UPDATE {$this->table_name}
416	SET email = %s, first_name = '', last_name = ''
417	WHERE donor_id IN ( $placeholders )";
418
419	}
420
421	return $wpdb->query( $wpdb->prepare( $sql, $parameters ) );
422	}
423
424	/**
425	* Checks whether the passed email is an anonymized email address.
426	*
427	* @since 1.6.4
428	*
429	* @param string $email The email address.
430	* @return boolean
431	*/
432	private function is_anonymized_email( $email ) {
433	return function_exists( 'wp_privacy_anonymize_data' ) && wp_privacy_anonymize_data( 'email' ) == $email;
434	}
435	}
436
437	endif;
438