give / src / DonationForms / DonationQuery.php

DonationQuery.php in GiveWP – Donation Plugin and Fundraising Platform 4.16.8.1, at src/DonationForms/DonationQuery.php

161 lines 4.7 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 4.5.0 update to account for exchange rate
110	* @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()
111	* @since 3.12.0
112	* @return int\|float
113	*/
114	public function sumIntendedAmount($includeOnlyValidStatuses = true, $includeOnlyCurrentMode = true)
115	{
116	if ($includeOnlyValidStatuses) {
117	$this->includeOnlyValidStatuses();
118	}
119
120	if ($includeOnlyCurrentMode) {
121	$this->includeOnlyCurrentMode();
122	}
123
124	$this->joinMeta(DonationMetaKeys::AMOUNT, 'amount');
125	$this->joinMeta(DonationMetaKeys::FEE_AMOUNT_RECOVERED, 'feeAmountRecovered');
126	$this->joinMeta(DonationMetaKeys::EXCHANGE_RATE, 'exchangeRate');
127	return $this->sum(
128	'(IFNULL(amount.meta_value, 0) - IFNULL(feeAmountRecovered.meta_value, 0)) / IFNULL(exchangeRate.meta_value, 1)'
129	);
130	}
131
132	/**
133	* Returns a calculated sum of the amounts (with recovered fees) for the donations.
134	*
135	* @since 3.14.0
136	* @return int\|float
137	*/
138	public function sumAmount($includeOnlyValidStatuses = true, $includeOnlyCurrentMode = true)
139	{
140	if ($includeOnlyValidStatuses) {
141	$this->includeOnlyValidStatuses();
142	}
143
144	if ($includeOnlyCurrentMode) {
145	$this->includeOnlyCurrentMode();
146	}
147
148	$this->joinMeta('_give_payment_total', 'amount');
149
150	return $this->sum(
151	'amount.meta_value'
152	);
153	}
154
155	public function countDonors()
156	{
157	$this->joinMeta(DonationMetaKeys::DONOR_ID, 'donorId');
158	return $this->count('DISTINCT donorId.meta_value');
159	}
160	}
161