Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
7e9e8871 | 4 | | CiviCRM version 4.7 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
0f03f337 | 6 | | Copyright CiviCRM LLC (c) 2004-2017 | |
6a488035 TO |
7 | +--------------------------------------------------------------------+ |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
10 | | CiviCRM is free software; you can copy, modify, and distribute it | | |
11 | | under the terms of the GNU Affero General Public License | | |
12 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | | |
13 | | | | |
14 | | CiviCRM is distributed in the hope that it will be useful, but | | |
15 | | WITHOUT ANY WARRANTY; without even the implied warranty of | | |
16 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
17 | | See the GNU Affero General Public License for more details. | | |
18 | | | | |
19 | | You should have received a copy of the GNU Affero General Public | | |
20 | | License and the CiviCRM Licensing Exception along | | |
21 | | with this program; if not, contact CiviCRM LLC | | |
22 | | at info[AT]civicrm[DOT]org. If you have questions about the | | |
23 | | GNU Affero General Public License or the licensing of CiviCRM, | | |
24 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
25 | +--------------------------------------------------------------------+ | |
d25dd0ee | 26 | */ |
6a488035 TO |
27 | |
28 | /** | |
29 | * | |
30 | * @package CRM | |
0f03f337 | 31 | * @copyright CiviCRM LLC (c) 2004-2017 |
6a488035 | 32 | */ |
17da84f5 | 33 | class CRM_Contact_Form_Search_Custom_ContributionAggregate extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface { |
6a488035 | 34 | |
4e54c348 | 35 | protected $_formValues; |
d14ccbdc SL |
36 | protected $_aclFrom = NULL; |
37 | protected $_aclWhere = NULL; | |
4e54c348 | 38 | public $_permissionedComponent; |
6a488035 | 39 | |
86538308 | 40 | /** |
5a409b50 | 41 | * Class constructor. |
42 | * | |
43 | * @param array $formValues | |
86538308 | 44 | */ |
00be9182 | 45 | public function __construct(&$formValues) { |
4e54c348 | 46 | $this->_formValues = $formValues; |
4e54c348 | 47 | |
1054415f | 48 | // Define the columns for search result rows |
6a488035 | 49 | $this->_columns = array( |
7b99ead3 | 50 | ts('Contact ID') => 'contact_id', |
6a488035 | 51 | ts('Name') => 'sort_name', |
be205937 CW |
52 | ts('Contribution Count') => 'donation_count', |
53 | ts('Contribution Amount') => 'donation_amount', | |
6a488035 | 54 | ); |
4e54c348 PJ |
55 | |
56 | // define component access permission needed | |
57 | $this->_permissionedComponent = 'CiviContribute'; | |
6a488035 TO |
58 | } |
59 | ||
86538308 | 60 | /** |
5a409b50 | 61 | * Build form. |
62 | * | |
c490a46a | 63 | * @param CRM_Core_Form $form |
86538308 | 64 | */ |
00be9182 | 65 | public function buildForm(&$form) { |
6a488035 TO |
66 | |
67 | /** | |
68 | * You can define a custom title for the search form | |
69 | */ | |
70 | $this->setTitle('Find Contributors by Aggregate Totals'); | |
71 | ||
72 | /** | |
73 | * Define the search form fields here | |
74 | */ | |
75 | $form->add('text', | |
76 | 'min_amount', | |
77 | ts('Aggregate Total Between $') | |
78 | ); | |
79 | $form->addRule('min_amount', ts('Please enter a valid amount (numbers and decimal point only).'), 'money'); | |
80 | ||
81 | $form->add('text', | |
82 | 'max_amount', | |
83 | ts('...and $') | |
84 | ); | |
85 | $form->addRule('max_amount', ts('Please enter a valid amount (numbers and decimal point only).'), 'money'); | |
b4f6780b | 86 | CRM_Core_Form_Date::buildDateRange($form, 'contribution_date', 1, '_low', '_high', ts('From:'), FALSE, FALSE); |
6a488035 | 87 | |
78065c52 | 88 | $form->addSelect('financial_type_id', |
89 | array('entity' => 'contribution', 'multiple' => 'multiple', 'context' => 'search') | |
90 | ); | |
6a488035 TO |
91 | |
92 | /** | |
93 | * If you are using the sample template, this array tells the template fields to render | |
94 | * for the search form. | |
95 | */ | |
b4f6780b | 96 | $form->assign('elements', array('min_amount', 'max_amount')); |
6a488035 TO |
97 | } |
98 | ||
99 | /** | |
100 | * Define the smarty template used to layout the search form and results listings. | |
1054415f CW |
101 | * |
102 | * @return string | |
6a488035 | 103 | */ |
00be9182 | 104 | public function templateFile() { |
6a488035 TO |
105 | return 'CRM/Contact/Form/Search/Custom/ContributionAggregate.tpl'; |
106 | } | |
107 | ||
108 | /** | |
fe482240 | 109 | * Construct the search query. |
1054415f CW |
110 | * |
111 | * @param int $offset | |
112 | * @param int $rowcount | |
113 | * @param string|object $sort | |
114 | * @param bool $includeContactIDs | |
115 | * @param bool $justIDs | |
116 | * | |
117 | * @return string | |
6a488035 | 118 | */ |
2da40d21 | 119 | public function all( |
51ccfbbe | 120 | $offset = 0, $rowcount = 0, $sort = NULL, |
6a488035 TO |
121 | $includeContactIDs = FALSE, $justIDs = FALSE |
122 | ) { | |
123 | ||
124 | // SELECT clause must include contact_id as an alias for civicrm_contact.id | |
125 | if ($justIDs) { | |
126 | $select = "contact_a.id as contact_id"; | |
127 | } | |
128 | else { | |
129 | $select = " | |
130 | DISTINCT contact_a.id as contact_id, | |
131 | contact_a.sort_name as sort_name, | |
132 | sum(contrib.total_amount) AS donation_amount, | |
133 | count(contrib.id) AS donation_count | |
134 | "; | |
135 | } | |
136 | $from = $this->from(); | |
137 | ||
138 | $where = $this->where($includeContactIDs); | |
139 | ||
140 | $having = $this->having(); | |
141 | if ($having) { | |
142 | $having = " HAVING $having "; | |
143 | } | |
144 | ||
145 | $sql = " | |
146 | SELECT $select | |
147 | FROM $from | |
148 | WHERE $where | |
149 | GROUP BY contact_a.id | |
150 | $having | |
151 | "; | |
152 | //for only contact ids ignore order. | |
153 | if (!$justIDs) { | |
154 | // Define ORDER BY for query in $sort, with default value | |
155 | if (!empty($sort)) { | |
156 | if (is_string($sort)) { | |
bf00d1b6 | 157 | $sort = CRM_Utils_Type::escape($sort, 'String'); |
6a488035 TO |
158 | $sql .= " ORDER BY $sort "; |
159 | } | |
160 | else { | |
161 | $sql .= " ORDER BY " . trim($sort->orderBy()); | |
162 | } | |
163 | } | |
164 | else { | |
165 | $sql .= "ORDER BY donation_amount desc"; | |
166 | } | |
167 | } | |
168 | ||
169 | if ($rowcount > 0 && $offset >= 0) { | |
bf00d1b6 | 170 | $offset = CRM_Utils_Type::escape($offset, 'Int'); |
dd3a4117 | 171 | $rowcount = CRM_Utils_Type::escape($rowcount, 'Int'); |
6a488035 TO |
172 | $sql .= " LIMIT $offset, $rowcount "; |
173 | } | |
174 | return $sql; | |
175 | } | |
176 | ||
86538308 EM |
177 | /** |
178 | * @return string | |
179 | */ | |
00be9182 | 180 | public function from() { |
d14ccbdc SL |
181 | $this->buildACLClause('contact_a'); |
182 | $from = " | |
6a488035 | 183 | civicrm_contribution AS contrib, |
5b7c52cf | 184 | civicrm_contact AS contact_a {$this->_aclFrom} |
6a488035 | 185 | "; |
47b8444f | 186 | |
d14ccbdc | 187 | return $from; |
6a488035 TO |
188 | } |
189 | ||
86538308 | 190 | /** |
fe482240 | 191 | * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values. |
1054415f | 192 | * |
86538308 EM |
193 | * @param bool $includeContactIDs |
194 | * | |
195 | * @return string | |
196 | */ | |
00be9182 | 197 | public function where($includeContactIDs = FALSE) { |
f36b9484 | 198 | $clauses = array( |
199 | "contrib.contact_id = contact_a.id", | |
200 | "contrib.is_test = 0", | |
201 | ); | |
6a488035 | 202 | |
f36b9484 | 203 | $dateParams = array( |
204 | 'contribution_date_relative' => $this->_formValues['contribution_date_relative'], | |
205 | 'contribution_date_low' => $this->_formValues['contribution_date_low'], | |
206 | 'contribution_date_high' => $this->_formValues['contribution_date_high'], | |
207 | ); | |
208 | foreach (CRM_Contact_BAO_Query::convertFormValues($dateParams) as $values) { | |
209 | list($name, $op, $value) = $values; | |
210 | if (strstr($name, '_low')) { | |
211 | $clauses[] = "contrib.receive_date >= " . CRM_Utils_Date::processDate($value); | |
212 | } | |
213 | else { | |
214 | $clauses[] = "contrib.receive_date <= " . CRM_Utils_Date::processDate($value); | |
215 | } | |
6a488035 TO |
216 | } |
217 | ||
218 | if ($includeContactIDs) { | |
219 | $contactIDs = array(); | |
220 | foreach ($this->_formValues as $id => $value) { | |
221 | if ($value && | |
222 | substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX | |
223 | ) { | |
224 | $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN); | |
225 | } | |
226 | } | |
227 | ||
228 | if (!empty($contactIDs)) { | |
229 | $contactIDs = implode(', ', $contactIDs); | |
230 | $clauses[] = "contact_a.id IN ( $contactIDs )"; | |
231 | } | |
232 | } | |
233 | ||
234 | if (!empty($this->_formValues['financial_type_id'])) { | |
78065c52 | 235 | $financial_type_ids = implode(',', array_values($this->_formValues['financial_type_id'])); |
6a488035 TO |
236 | $clauses[] = "contrib.financial_type_id IN ($financial_type_ids)"; |
237 | } | |
47b8444f | 238 | if ($this->_aclWhere) { |
2b8b60d6 | 239 | $clauses[] = " {$this->_aclWhere} "; |
47b8444f | 240 | } |
6a488035 TO |
241 | |
242 | return implode(' AND ', $clauses); | |
243 | } | |
244 | ||
86538308 EM |
245 | /** |
246 | * @param bool $includeContactIDs | |
247 | * | |
248 | * @return string | |
249 | */ | |
00be9182 | 250 | public function having($includeContactIDs = FALSE) { |
6a488035 TO |
251 | $clauses = array(); |
252 | $min = CRM_Utils_Array::value('min_amount', $this->_formValues); | |
253 | if ($min) { | |
254 | $min = CRM_Utils_Rule::cleanMoney($min); | |
255 | $clauses[] = "sum(contrib.total_amount) >= $min"; | |
256 | } | |
257 | ||
258 | $max = CRM_Utils_Array::value('max_amount', $this->_formValues); | |
259 | if ($max) { | |
260 | $max = CRM_Utils_Rule::cleanMoney($max); | |
261 | $clauses[] = "sum(contrib.total_amount) <= $max"; | |
262 | } | |
263 | ||
264 | return implode(' AND ', $clauses); | |
265 | } | |
266 | ||
546b78fa | 267 | /* |
c490a46a CW |
268 | * Functions below generally don't need to be modified |
269 | */ | |
546b78fa CW |
270 | |
271 | /** | |
272 | * @inheritDoc | |
273 | */ | |
00be9182 | 274 | public function count() { |
6a488035 TO |
275 | $sql = $this->all(); |
276 | ||
277 | $dao = CRM_Core_DAO::executeQuery($sql, | |
278 | CRM_Core_DAO::$_nullArray | |
279 | ); | |
280 | return $dao->N; | |
281 | } | |
282 | ||
86538308 EM |
283 | /** |
284 | * @param int $offset | |
285 | * @param int $rowcount | |
286 | * @param null $sort | |
822019c0 | 287 | * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned |
86538308 EM |
288 | * |
289 | * @return string | |
290 | */ | |
e98a9804 | 291 | public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) { |
6a488035 TO |
292 | return $this->all($offset, $rowcount, $sort, FALSE, TRUE); |
293 | } | |
294 | ||
86538308 EM |
295 | /** |
296 | * @return array | |
297 | */ | |
00be9182 | 298 | public function &columns() { |
6a488035 TO |
299 | return $this->_columns; |
300 | } | |
301 | ||
86538308 EM |
302 | /** |
303 | * @param $title | |
304 | */ | |
00be9182 | 305 | public function setTitle($title) { |
6a488035 TO |
306 | if ($title) { |
307 | CRM_Utils_System::setTitle($title); | |
308 | } | |
309 | else { | |
310 | CRM_Utils_System::setTitle(ts('Search')); | |
311 | } | |
312 | } | |
313 | ||
86538308 EM |
314 | /** |
315 | * @return null | |
316 | */ | |
00be9182 | 317 | public function summary() { |
6a488035 TO |
318 | return NULL; |
319 | } | |
96025800 | 320 | |
d14ccbdc SL |
321 | /** |
322 | * @param string $tableAlias | |
323 | */ | |
324 | public function buildACLClause($tableAlias = 'contact') { | |
325 | list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias); | |
326 | } | |
327 | ||
6a488035 | 328 | } |