DonorsExport.php
208 lines
| 1 | <?php |
| 2 | |
| 3 | namespace Give\Exports; |
| 4 | |
| 5 | use Give\Framework\Database\DB; |
| 6 | use Give\Framework\QueryBuilder\JoinQueryBuilder; |
| 7 | use Give_Batch_Export; |
| 8 | |
| 9 | /** |
| 10 | * @since 2.21.2 |
| 11 | */ |
| 12 | class DonorsExport extends Give_Batch_Export |
| 13 | { |
| 14 | /** @inheritdoc */ |
| 15 | public $export_type = 'donors'; |
| 16 | |
| 17 | /** @var array */ |
| 18 | protected $postedData; |
| 19 | |
| 20 | /** @var String */ |
| 21 | protected $startDate; |
| 22 | |
| 23 | /** @var String */ |
| 24 | protected $endDate; |
| 25 | |
| 26 | /** @var String */ |
| 27 | protected $searchBy; |
| 28 | |
| 29 | /** |
| 30 | * @var int |
| 31 | */ |
| 32 | protected $donationFormId; |
| 33 | |
| 34 | /** |
| 35 | * @inheritdoc |
| 36 | */ |
| 37 | public function set_properties($request) |
| 38 | { |
| 39 | $this->postedData = $request; |
| 40 | |
| 41 | if ($this->postedData['giveDonorExport-startDate']) { |
| 42 | $this->startDate = date('Y-m-d', strtotime($this->postedData['giveDonorExport-startDate'])); |
| 43 | } |
| 44 | |
| 45 | if ($this->postedData['giveDonorExport-endDate']) { |
| 46 | $this->endDate = date('Y-m-d', strtotime($this->postedData['giveDonorExport-endDate'])); |
| 47 | } |
| 48 | |
| 49 | if ($this->postedData['searchBy']) { |
| 50 | $this->searchBy = $this->postedData['searchBy']; |
| 51 | } |
| 52 | |
| 53 | $this->donationFormId = (int)$this->postedData['forms']; |
| 54 | } |
| 55 | |
| 56 | /** |
| 57 | * @since 3.12.1 Include donor phone. |
| 58 | * @since 2.29.0 Include donor created date |
| 59 | * @since 2.21.2 |
| 60 | * @since 3.3.0 Filter donors by form ID |
| 61 | */ |
| 62 | public function get_data(): array |
| 63 | { |
| 64 | $donorQuery = DB::table('give_donors', 'donors') |
| 65 | ->distinct() |
| 66 | ->select( |
| 67 | ['donors.name', 'full_name'], |
| 68 | ['donors.email', 'email'], |
| 69 | ['donors.user_id', 'userid'], |
| 70 | ['donors.date_created', 'donor_created_date'], |
| 71 | ['donors.phone', 'donor_phone_number'], |
| 72 | ['donors.purchase_count', 'donations'], |
| 73 | ['donors.purchase_value', 'donation_sum'] |
| 74 | ); |
| 75 | |
| 76 | $donationQuery = DB::table('posts', 'donations') |
| 77 | ->select('donations.ID', ['meta.meta_value', 'donorId']) |
| 78 | ->join(function (JoinQueryBuilder $builder) { |
| 79 | $builder |
| 80 | ->leftJoin('give_donationmeta', 'meta') |
| 81 | ->on('donations.ID', 'meta.donation_id') |
| 82 | ->andOn('meta.meta_key', '_give_payment_donor_id', true); |
| 83 | }) |
| 84 | ->where('donations.post_type', 'give_payment'); |
| 85 | |
| 86 | if ($this->searchBy === 'donor') { |
| 87 | if ($this->startDate && $this->endDate) { |
| 88 | $donorQuery->whereBetween('DATE(donors.date_created)', $this->startDate, $this->endDate); |
| 89 | } elseif ($this->startDate) { |
| 90 | $donorQuery->where('DATE(donors.date_created)', $this->startDate, '>='); |
| 91 | } elseif ($this->endDate) { |
| 92 | $donorQuery->where('DATE(donors.date_created)', $this->endDate, '<='); |
| 93 | } |
| 94 | } else { |
| 95 | if ($this->startDate && $this->endDate) { |
| 96 | $donationQuery->whereBetween('DATE(donations.post_date)', $this->startDate, $this->endDate); |
| 97 | } elseif ($this->startDate) { |
| 98 | $donationQuery->where('DATE(donations.post_date)', $this->startDate, '>='); |
| 99 | } elseif ($this->endDate) { |
| 100 | $donationQuery->where('DATE(donations.post_date)', $this->endDate, '<='); |
| 101 | } |
| 102 | } |
| 103 | |
| 104 | if ($this->donationFormId) { |
| 105 | $donationQuery |
| 106 | ->join(function (JoinQueryBuilder $builder) { |
| 107 | $builder |
| 108 | ->leftJoin('give_donationmeta', 'form') |
| 109 | ->on('donations.ID', 'form.donation_id') |
| 110 | ->andOn('form.meta_key', '_give_payment_form_id', true); |
| 111 | }) |
| 112 | ->where('form.meta_value', $this->donationFormId); |
| 113 | } |
| 114 | |
| 115 | $donorQuery->joinRaw("JOIN ({$donationQuery->getSQL()}) AS sub ON donors.id = sub.donorId"); |
| 116 | |
| 117 | if ($this->shouldIncludeAddress()) { |
| 118 | $donorQuery->attachMeta( |
| 119 | 'give_donormeta', |
| 120 | 'donors.ID', |
| 121 | 'donor_id', |
| 122 | ['_give_donor_address_billing_line1_0', 'address_line1'], |
| 123 | ['_give_donor_address_billing_line2_0', 'address_line2'], |
| 124 | ['_give_donor_address_billing_city_0', 'address_city'], |
| 125 | ['_give_donor_address_billing_state_0', 'address_state'], |
| 126 | ['_give_donor_address_billing_zip_0', 'address_zip'], |
| 127 | ['_give_donor_address_billing_country_0', 'address_country'] |
| 128 | ); |
| 129 | } |
| 130 | |
| 131 | return $this->filterExportData( |
| 132 | array_map(function ($row) { |
| 133 | return array_intersect_key($row, $this->csv_cols()); |
| 134 | }, $donorQuery->getAll(ARRAY_A)) |
| 135 | ); |
| 136 | } |
| 137 | |
| 138 | /** |
| 139 | * @since 2.21.2 |
| 140 | */ |
| 141 | protected function shouldIncludeAddress(): bool |
| 142 | { |
| 143 | return isset($this->postedData['give_export_columns']['address']); |
| 144 | } |
| 145 | |
| 146 | /** |
| 147 | * @since 2.21.2 |
| 148 | */ |
| 149 | protected function filterExportData(array $exportData): array |
| 150 | { |
| 151 | /** |
| 152 | * @since 2.21.2 |
| 153 | * |
| 154 | * @param array $exportData |
| 155 | */ |
| 156 | return apply_filters("give_export_get_data_{$this->export_type}", $exportData); |
| 157 | } |
| 158 | |
| 159 | /** |
| 160 | * @since 3.12.1 Include donor_phone_number col. |
| 161 | * @since 2.29.0 Include donor created col |
| 162 | * @since 2.21.2 |
| 163 | */ |
| 164 | public function csv_cols(): array |
| 165 | { |
| 166 | return $this->flattenAddressColumn( |
| 167 | array_intersect_key([ |
| 168 | 'full_name' => __('Name', 'give'), |
| 169 | 'email' => __('Email', 'give'), |
| 170 | 'address' => [ |
| 171 | 'address_line1' => __('Address', 'give'), |
| 172 | 'address_line2' => __('Address 2', 'give'), |
| 173 | 'address_city' => __('City', 'give'), |
| 174 | 'address_state' => __('State', 'give'), |
| 175 | 'address_zip' => __('Zip', 'give'), |
| 176 | 'address_country' => __('Country', 'give'), |
| 177 | ], |
| 178 | 'userid' => __('User ID', 'give'), |
| 179 | 'donor_created_date' => __('Donor Created', 'give'), |
| 180 | 'donor_phone_number' => __('Donor Phone Number', 'give'), |
| 181 | 'donations' => __('Number of donations', 'give'), |
| 182 | 'donation_sum' => __('Total Donated', 'give'), |
| 183 | ], $this->postedData['give_export_columns']) |
| 184 | ); |
| 185 | } |
| 186 | |
| 187 | /** |
| 188 | * @since 2.21.2 |
| 189 | */ |
| 190 | protected function flattenAddressColumn(array $columnarData): array |
| 191 | { |
| 192 | return $this->flattenColumn($columnarData, 'address'); |
| 193 | } |
| 194 | |
| 195 | /** |
| 196 | * @since 2.21.2 |
| 197 | */ |
| 198 | protected function flattenColumn(array $columnarData, string $columnName): array |
| 199 | { |
| 200 | if (isset($columnarData[$columnName])) { |
| 201 | $columnarData = array_merge($columnarData, $columnarData[$columnName]); |
| 202 | unset($columnarData[$columnName]); |
| 203 | } |
| 204 | |
| 205 | return $columnarData; |
| 206 | } |
| 207 | } |
| 208 |