Actions
7 months ago
CustomFields
1 year ago
DataTransferObjects
1 year ago
Endpoints
5 months ago
Exceptions
2 years ago
Factories
1 year ago
ListTable
7 months ago
Migrations
1 year ago
Models
1 year ago
Repositories
8 months ago
ValueObjects
9 months ago
resources
5 months ago
DonorStatisticsQuery.php
9 months ago
DonorsAdminPage.php
5 months ago
DonorsQuery.php
5 months ago
ServiceProvider.php
5 months ago
DonorStatisticsQuery.php
226 lines
| 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 |