Actions
4 months ago
AsyncData
1 year ago
Blocks
2 months ago
Controllers
3 months ago
DataTransferObjects
1 week ago
Exceptions
1 year ago
Factories
9 months ago
FormDesigns
11 months ago
FormPage
1 year ago
Listeners
1 year ago
Migrations
1 year ago
Models
10 months ago
OrphanedForms
1 year ago
Properties
2 months ago
Repositories
10 months ago
Routes
5 months ago
Rules
9 months ago
Shortcodes
2 years ago
V2
4 months ago
ValueObjects
1 year ago
ViewModels
2 months ago
resources
1 month ago
DonationFormDataQuery.php
11 months ago
DonationFormsAdminPage.php
1 year ago
DonationQuery.php
11 months ago
ServiceProvider.php
7 months ago
SubscriptionQuery.php
2 years ago
DonationQuery.php
161 lines
| 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 |