give / src / Donors / DonorStatisticsQuery.php

DonorStatisticsQuery.php in GiveWP – Donation Plugin and Fundraising Platform 4.16.8.1, at src/Donors/DonorStatisticsQuery.php

226 lines 6.0 KB

Raw Download Zip

1	<?php
2
3	namespace Give\Donors;
4
5	use Give\Campaigns\Models\Campaign;
6	use Give\Donations\ValueObjects\DonationMetaKeys;
7	use Give\Donors\Models\Donor;
8	use Give\Donors\Repositories\DonorRepository;
9	use Give\Framework\QueryBuilder\JoinQueryBuilder;
10	use Give\Framework\QueryBuilder\QueryBuilder;
11	use Give\Donors\ValueObjects\DonorType;
12
13	/**
14	* @since 4.4.0
15	*/
16	class DonorStatisticsQuery extends QueryBuilder
17	{
18	/**
19	* @since 4.4.0
20	*/
21	private Donor $donor;
22
23	/**
24	* @since 4.4.0
25	*/
26	public function __construct(Donor $donor, $mode = '')
27	{
28	$this->donor = $donor;
29
30	$this->from('posts', 'donation');
31	$this->where('post_type', 'give_payment');
32
33	// Include only valid statuses
34	$this->whereIn('donation.post_status', ['publish', 'give_subscription']);
35
36	// Include only current payment "mode"
37	if (empty($mode)) {
38	$mode = give_is_test_mode() ? 'test' : 'live';
39	}
40	$this->joinDonationMeta(DonationMetaKeys::MODE, 'paymentMode');
41	$this->where('paymentMode.meta_value', $mode);
42
43	// Include only donations associated with the Donor.
44	$this->joinDonationMeta(DonationMetaKeys::DONOR_ID, 'donorId');
45	$this->where('donorId.meta_value', $donor->id);
46
47	$this->joinDonationMeta(DonationMetaKeys::AMOUNT, 'amount');
48	$this->joinDonationMeta(DonationMetaKeys::FEE_AMOUNT_RECOVERED, 'feeAmountRecovered');
49	$this->joinDonationMeta(DonationMetaKeys::EXCHANGE_RATE, 'exchangeRate');
50	}
51
52	/**
53	* @since 4.4.0
54	*/
55	public function filterByCampaign(Campaign $campaign): self
56	{
57	$query = clone $this;
58
59	$query->joinDonationMeta(DonationMetaKeys::CAMPAIGN_ID, 'campaignId');
60	$query->where('campaignId.meta_value', $campaign->id);
61
62	return $query;
63	}
64
65	/**
66	* @since 4.5.0 update to account for exchange rate
67	* @since 4.4.0
68	*
69	* @return int\|float
70	*/
71	public function getLifetimeDonationsAmount()
72	{
73	$query = clone $this;
74
75	return $query->sum(
76	'(IFNULL(amount.meta_value, 0) - IFNULL(feeAmountRecovered.meta_value, 0)) / IFNULL(exchangeRate.meta_value, 1)'
77	);
78	}
79
80	/**
81	* @since 4.5.0 update to account for exchange rate
82	* @since 4.4.0
83	*/
84	public function getHighestDonationAmount()
85	{
86	$query = clone $this;
87
88	$query->select('(IFNULL(amount.meta_value, 0) - IFNULL(feeAmountRecovered.meta_value, 0)) / IFNULL(exchangeRate.meta_value, 1) as highestDonationAmount');
89	$query->orderBy('CAST(amount.meta_value AS DECIMAL)', 'DESC');
90	$query->limit(1);
91	$result = $query->get();
92
93	if ( ! $result) {
94	return null;
95	}
96
97	return (float)$result->highestDonationAmount;
98	}
99
100	/**
101	* @since 4.5.0 update to account for exchange rate
102	* @since 4.4.0
103	*/
104	public function getAverageDonationAmount()
105	{
106	$query = clone $this;
107
108	$donationsCount = $this->getDonationsCount();
109	$lifetimeDonationsAmount = $query->getLifetimeDonationsAmount();
110
111	return $donationsCount > 0 ? $lifetimeDonationsAmount / $donationsCount : $lifetimeDonationsAmount;
112	}
113
114	/**
115	* @since 4.4.0
116	*/
117	public function getDonationsCount(): int
118	{
119	$query = clone $this;
120
121	return $query->count('donation.ID');
122	}
123
124
125	/**
126	* @since 4.5.0 update to account for exchange rate
127	* @since 4.4.0
128	*/
129	public function getFirstDonation()
130	{
131	$query = clone $this;
132	$query->select(
133	'donation.post_date',
134	'(IFNULL(amount.meta_value, 0) - IFNULL(feeAmountRecovered.meta_value, 0)) / IFNULL(exchangeRate.meta_value, 1) as amount'
135	);
136	$query->orderBy('post_date', 'ASC');
137	$query->limit(1);
138	$result = $query->get();
139
140	if (!$result) {
141	return null;
142	}
143
144	return [
145	'amount' => (float)$result->amount,
146	'date' => date('Y-m-d H:i:s', strtotime($result->post_date))
147	];
148	}
149
150	/**
151	* @since 4.5.0 update to account for exchange rate
152	* @since 4.4.0
153	*/
154	public function getLastDonation()
155	{
156	$query = clone $this;
157	$query->select(
158	'donation.post_date',
159	'(IFNULL(amount.meta_value, 0) - IFNULL(feeAmountRecovered.meta_value, 0)) / IFNULL(exchangeRate.meta_value, 1) as amount'
160	);
161	$query->orderBy('post_date', 'DESC');
162	$query->limit(1);
163	$result = $query->get();
164
165	if (!$result) {
166	return null;
167	}
168
169	return [
170	'amount' => (float)$result->amount,
171	'date' => date('Y-m-d H:i:s', strtotime($result->post_date)),
172	];
173	}
174
175	/**
176	* @since 4.10.0 Updated return value
177	* @since 4.4.0
178	*/
179	public function getDonorType()
180	{
181	$donorRepository = give(DonorRepository::class);
182	$donorType = $donorRepository->getDonorType($this->donor->id);
183
184	if (!$donorType) {
185	return null;
186	}
187
188	return $donorType->label();
189	}
190
191	/**
192	* @since 4.4.0
193	*/
194	public function preferredPaymentMethod(): string
195	{
196	$query = clone $this;
197	$query->joinDonationMeta(DonationMetaKeys::GATEWAY, 'gateway');
198	$query->select('gateway.meta_value as gateway');
199	$query->groupBy('gateway.meta_value');
200	$query->orderBy('COUNT(gateway.meta_value)', 'DESC');
201	$query->limit(1);
202	$result = $query->get();
203
204	if (!$result) {
205	return '';
206	}
207
208	return give_get_gateway_checkout_label($result->gateway) ?? $result->gateway;
209	}
210
211	/**
212	* @since 4.4.0
213	*/
214	public function joinDonationMeta($key, $alias): self
215	{
216	$this->join(function (JoinQueryBuilder $builder) use ($key, $alias) {
217	$builder
218	->leftJoin('give_donationmeta', $alias)
219	->on('donation.ID', $alias . '.donation_id')
220	->andOn($alias . '.meta_key', $key, true);
221	});
222
223	return $this;
224	}
225	}
226