Actions
1 year ago
AsyncData
1 year ago
Blocks
1 year ago
Controllers
2 years ago
DataTransferObjects
1 year ago
Exceptions
1 year ago
Factories
2 years ago
FormDesigns
1 year ago
FormPage
2 years ago
Listeners
1 year ago
Migrations
2 years ago
Models
2 years ago
Properties
1 year ago
Repositories
2 years ago
Routes
1 year ago
Rules
1 year ago
Shortcodes
2 years ago
V2
1 year ago
ValueObjects
2 years ago
ViewModels
2 years ago
resources
1 year ago
DonationFormsAdminPage.php
1 year ago
DonationQuery.php
1 year ago
ServiceProvider.php
1 year ago
SubscriptionQuery.php
2 years ago
DonationQuery.php
160 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->joinMeta('_give_completed_date', 'completed'); |
| 82 | $this->whereBetween('completed.meta_value', $startDate, $endDate); |
| 83 | return $this; |
| 84 | } |
| 85 | |
| 86 | /** |
| 87 | * @since 3.14.0 |
| 88 | */ |
| 89 | public function includeOnlyValidStatuses(): DonationQuery |
| 90 | { |
| 91 | $this->whereIn('donation.post_status', ['publish', 'give_subscription']); |
| 92 | |
| 93 | return $this; |
| 94 | } |
| 95 | |
| 96 | /** |
| 97 | * @since 3.14.0 |
| 98 | */ |
| 99 | public function includeOnlyCurrentMode(): DonationQuery |
| 100 | { |
| 101 | $this->joinMeta('_give_payment_mode', 'paymentMode'); |
| 102 | $this->where('paymentMode.meta_value', give_is_test_mode() ? 'test' : 'live'); |
| 103 | |
| 104 | return $this; |
| 105 | } |
| 106 | |
| 107 | /** |
| 108 | * Returns a calculated sum of the intended amounts (without recovered fees) for the donations. |
| 109 | * |
| 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('_give_payment_total', 'amount'); |
| 125 | $this->joinMeta('_give_fee_donation_amount', 'intendedAmount'); |
| 126 | return $this->sum( |
| 127 | 'COALESCE(NULLIF(intendedAmount.meta_value,0), NULLIF(amount.meta_value,0), 0)' |
| 128 | ); |
| 129 | } |
| 130 | |
| 131 | /** |
| 132 | * Returns a calculated sum of the amounts (with recovered fees) for the donations. |
| 133 | * |
| 134 | * @since 3.14.0 |
| 135 | * @return int|float |
| 136 | */ |
| 137 | public function sumAmount($includeOnlyValidStatuses = true, $includeOnlyCurrentMode = true) |
| 138 | { |
| 139 | if ($includeOnlyValidStatuses) { |
| 140 | $this->includeOnlyValidStatuses(); |
| 141 | } |
| 142 | |
| 143 | if ($includeOnlyCurrentMode) { |
| 144 | $this->includeOnlyCurrentMode(); |
| 145 | } |
| 146 | |
| 147 | $this->joinMeta('_give_payment_total', 'amount'); |
| 148 | |
| 149 | return $this->sum( |
| 150 | 'amount.meta_value' |
| 151 | ); |
| 152 | } |
| 153 | |
| 154 | public function countDonors() |
| 155 | { |
| 156 | $this->joinMeta(DonationMetaKeys::DONOR_ID, 'donorId'); |
| 157 | return $this->count('DISTINCT donorId.meta_value'); |
| 158 | } |
| 159 | } |
| 160 |