DonationQuery.php at 4.1.0 · GiveWP – Donation Plugin and Fundraising Platform

give / src / DonationForms / DonationQuery.php

DonationQuery.php

159 lines 4.5 KB

Raw Download Zip

1	<?php
2
3	namespace Give\DonationForms;
4
5	use Give\Donations\ValueObjects\DonationMetaKeys;
6	use Give\Framework\QueryBuilder\JoinQueryBuilder;
7	use Give\Framework\QueryBuilder\QueryBuilder;
8
9	/**
10	* An opinionated Query Builder for GiveWP donations and meta fields.
11	*
12	* @since 3.12.0
13	*
14	* Example usage:
15	* (new DonationQuery)
16	* ->form(1816)
17	* ->between('2024-02-00', '2024-02-23')
18	* ->sumIntendedAmount();
19	*/
20	class DonationQuery extends QueryBuilder
21	{
22	/**
23	* @since 3.12.0
24	*/
25	public function __construct()
26	{
27	$this->from('posts', 'donation');
28	}
29
30	/**
31	* An opinionated join method for the donation meta table.
32	* @since 3.12.0
33	*/
34	public function joinMeta($key, $alias): DonationQuery
35	{
36	$this->join(function (JoinQueryBuilder $builder) use ($key, $alias) {
37	$builder
38	->leftJoin('give_donationmeta', $alias)
39	->on('donation.ID', $alias . '.donation_id')
40	->andOn($alias . '.meta_key', $key, true);
41	});
42	return $this;
43	}
44
45	/**
46	* An opinionated where method for the donation form ID meta field.
47	* @since 3.12.0
48	*/
49	public function form($formId)
50	{
51	$this->joinMeta('_give_payment_form_id', 'formId');
52	$this->where('formId.meta_value', $formId);
53	return $this;
54	}
55
56
57	/**
58	* An opinionated where method for the multiple donation form IDs meta field.
59	* @since 3.12.0
60	*/
61	public function forms(array $formIds): DonationQuery
62	{
63	$this->joinMeta('_give_payment_form_id', 'formId');
64	$this->whereIn('formId.meta_value', $formIds);
65	return $this;
66	}
67
68	/**
69	* An opinionated whereBetween method for the completed date meta field.
70	* @since 3.12.0
71	*/
72	public function between($startDate, $endDate): DonationQuery
73	{
74	// If the dates are empty or invalid, they will fallback to January 1st, 1970.
75	// For the start date, this is exactly what we need, but for the end date, we should set it as the current date so that we have a correct date range.
76	$startDate = date('Y-m-d H:i:s', strtotime($startDate));
77	$endDate = empty($endDate)
78	? date('Y-m-d H:i:s')
79	: date('Y-m-d H:i:s', strtotime($endDate));
80
81	$this->whereBetween('donation.post_date', $startDate, $endDate);
82	return $this;
83	}
84
85	/**
86	* @since 3.14.0
87	*/
88	public function includeOnlyValidStatuses(): DonationQuery
89	{
90	$this->whereIn('donation.post_status', ['publish', 'give_subscription']);
91
92	return $this;
93	}
94
95	/**
96	* @since 3.14.0
97	*/
98	public function includeOnlyCurrentMode(): DonationQuery
99	{
100	$this->joinMeta('_give_payment_mode', 'paymentMode');
101	$this->where('paymentMode.meta_value', give_is_test_mode() ? 'test' : 'live');
102
103	return $this;
104	}
105
106	/**
107	* Returns a calculated sum of the intended amounts (without recovered fees) for the donations.
108	*
109	* @since 3.14.0 Use the NULLIF function to prevent zero values that can generate a wrong final result and use $this->includeOnlyValidStatuses() and $this->includeOnlyCurrentMode()
110	* @since 3.12.0
111	* @return int\|float
112	*/
113	public function sumIntendedAmount($includeOnlyValidStatuses = true, $includeOnlyCurrentMode = true)
114	{
115	if ($includeOnlyValidStatuses) {
116	$this->includeOnlyValidStatuses();
117	}
118
119	if ($includeOnlyCurrentMode) {
120	$this->includeOnlyCurrentMode();
121	}
122
123	$this->joinMeta('_give_payment_total', 'amount');
124	$this->joinMeta('_give_fee_donation_amount', 'intendedAmount');
125	return $this->sum(
126	'COALESCE(NULLIF(intendedAmount.meta_value,0), NULLIF(amount.meta_value,0), 0)'
127	);
128	}
129
130	/**
131	* Returns a calculated sum of the amounts (with recovered fees) for the donations.
132	*
133	* @since 3.14.0
134	* @return int\|float
135	*/
136	public function sumAmount($includeOnlyValidStatuses = true, $includeOnlyCurrentMode = true)
137	{
138	if ($includeOnlyValidStatuses) {
139	$this->includeOnlyValidStatuses();
140	}
141
142	if ($includeOnlyCurrentMode) {
143	$this->includeOnlyCurrentMode();
144	}
145
146	$this->joinMeta('_give_payment_total', 'amount');
147
148	return $this->sum(
149	'amount.meta_value'
150	);
151	}
152
153	public function countDonors()
154	{
155	$this->joinMeta(DonationMetaKeys::DONOR_ID, 'donorId');
156	return $this->count('DISTINCT donorId.meta_value');
157	}
158	}
159