give / src / Campaigns / CampaignDonationQuery.php

CampaignDonationQuery.php in GiveWP – Donation Plugin and Fundraising Platform 4.16.8.1, at src/Campaigns/CampaignDonationQuery.php

160 lines 5.0 KB

Raw Download Zip

1	<?php
2
3	namespace Give\Campaigns;
4
5	use DateTimeInterface;
6	use Give\Campaigns\Models\Campaign;
7	use Give\Donations\ValueObjects\DonationMetaKeys;
8	use Give\Framework\QueryBuilder\JoinQueryBuilder;
9	use Give\Framework\QueryBuilder\QueryBuilder;
10
11	/**
12	* @since 4.0.0
13	*/
14	class CampaignDonationQuery extends QueryBuilder
15	{
16	/**
17	* @since 4.0.0
18	*/
19	public function __construct(Campaign $campaign)
20	{
21	$this->from('posts', 'donation');
22	$this->where('post_type', 'give_payment');
23
24	// Include only valid statuses
25	$this->whereIn('donation.post_status', ['publish', 'give_subscription']);
26
27	// Include only current payment "mode"
28	$this->joinDonationMeta(DonationMetaKeys::MODE, 'paymentMode');
29	$this->where('paymentMode.meta_value', give_is_test_mode() ? 'test' : 'live');
30
31	// Include only forms associated with the Campaign.
32	$this->joinDonationMeta(DonationMetaKeys::CAMPAIGN_ID, 'campaignId');
33	$this->where('campaignId.meta_value', $campaign->id);
34	}
35
36	/**
37	* @since 4.0.0
38	*/
39	public function between(DateTimeInterface $startDate, DateTimeInterface $endDate): self
40	{
41	$query = clone $this;
42	$query->whereBetween(
43	'donation.post_date',
44	$startDate->format('Y-m-d H:i:s'),
45	$endDate->format('Y-m-d H:i:s')
46	);
47	return $query;
48	}
49
50	/**
51	* Returns a calculated sum of the intended amounts (without recovered fees) for the donations.
52	*
53	* @since 4.5.0 update to account for exchange rate
54	* @since 4.0.0
55	*
56	* @return int\|float
57	*/
58	public function sumIntendedAmount()
59	{
60	$query = clone $this;
61	$query->joinDonationMeta(DonationMetaKeys::AMOUNT, 'amount');
62	$query->joinDonationMeta(DonationMetaKeys::FEE_AMOUNT_RECOVERED, 'feeAmountRecovered');
63	$query->joinDonationMeta(DonationMetaKeys::EXCHANGE_RATE, 'exchangeRate');
64	return $query->sum(
65	/**
66	* The intended amount meta and the amount meta could either be 0 or NULL.
67	* So we need to use the NULLIF function to treat the 0 values as NULL.
68	* Then we coalesce the values to select the first non-NULL value.
69	* @link https://github.com/impress-org/givewp/pull/7411
70	*/
71	'(IFNULL(amount.meta_value, 0) - IFNULL(feeAmountRecovered.meta_value, 0)) / IFNULL(exchangeRate.meta_value, 1)'
72	);
73	}
74
75	/**
76	* @since 4.0.0
77	*/
78	public function countDonations(): int
79	{
80	$query = clone $this;
81	return $query->count('donation.ID');
82	}
83
84	/**
85	* @since 4.0.0
86	*/
87	public function countDonors(): int
88	{
89	$query = clone $this;
90	$query->joinDonationMeta(DonationMetaKeys::DONOR_ID, 'donorId');
91	return $query->count('DISTINCT donorId.meta_value');
92	}
93
94	/**
95	* @since 4.0.0
96	*/
97	public function getOldestDonationDate()
98	{
99	$query = clone $this;
100	$query->select('DATE(donation.post_date) as date_created');
101	$query->orderBy('donation.post_date', 'ASC');
102	$query->limit(1);
103	$result = $query->get();
104
105	if (!$result) {
106	return null;
107	}
108
109	return $result->date_created;
110	}
111
112	/**
113	* @since 4.5.0 update to account for exchange rate
114	* @since 4.0.0
115	*/
116	public function getDonationsByDate($groupBy = 'DATE'): array
117	{
118	$query = clone $this;
119
120	$query->joinDonationMeta(DonationMetaKeys::AMOUNT, 'amount');
121	$query->joinDonationMeta(DonationMetaKeys::FEE_AMOUNT_RECOVERED, 'feeAmountRecovered');
122	$query->joinDonationMeta(DonationMetaKeys::EXCHANGE_RATE, 'exchangeRate');
123	$query->select(
124	'SUM((IFNULL(amount.meta_value, 0) - IFNULL(feeAmountRecovered.meta_value, 0)) / IFNULL(exchangeRate.meta_value, 1)) as amount'
125	);
126
127	$query->select('YEAR(donation.post_date) as year');
128	$query->select('MONTH(donation.post_date) as month');
129	$query->select('DAY(donation.post_date) as day');
130	$query->select("DATE(donation.post_date) as date_created");
131
132	if ($groupBy === 'DAY') {
133	$query->groupBy('DATE(date_created)');
134	} else if ($groupBy === 'MONTH') {
135	$query->groupBy('YEAR(donation.post_date), MONTH(donation.post_date)');
136	} elseif ($groupBy === 'YEAR') {
137	$query->groupBy('YEAR(donation.post_date)');
138	} else {
139	$query->groupBy("$groupBy(donation.post_date)");
140	}
141
142	return $query->getAll();
143	}
144
145	/**
146	* An opinionated join method for the donation meta table.
147	* @since 4.0.0
148	*/
149	public function joinDonationMeta($key, $alias): self
150	{
151	$this->join(function (JoinQueryBuilder $builder) use ($key, $alias) {
152	$builder
153	->leftJoin('give_donationmeta', $alias)
154	->on('donation.ID', $alias . '.donation_id')
155	->andOn($alias . '.meta_key', $key, true);
156	});
157	return $this;
158	}
159	}
160