Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
7e9e8871 | 4 | | CiviCRM version 4.7 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
e7112fa7 | 6 | | Copyright CiviCRM LLC (c) 2004-2015 | |
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 | |
e7112fa7 | 31 | * @copyright CiviCRM LLC (c) 2004-2015 |
6a488035 TO |
32 | * $Id$ |
33 | * | |
34 | */ | |
17da84f5 | 35 | class CRM_Contact_Form_Search_Custom_ContribSYBNT extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface { |
6a488035 | 36 | |
4e54c348 | 37 | protected $_formValues; |
d14ccbdc SL |
38 | protected $_aclFrom = NULL; |
39 | protected $_aclWhere = NULL; | |
4e54c348 PJ |
40 | public $_permissionedComponent; |
41 | ||
86538308 EM |
42 | /** |
43 | * @param $formValues | |
44 | */ | |
00be9182 | 45 | public function __construct(&$formValues) { |
6a488035 | 46 | $this->_formValues = $formValues; |
4e54c348 | 47 | $this->_permissionedComponent = 'CiviContribute'; |
6a488035 TO |
48 | |
49 | $this->_columns = array( | |
7b99ead3 | 50 | ts('Contact ID') => 'contact_id', |
6a488035 | 51 | ts('Name') => 'display_name', |
be205937 CW |
52 | ts('Contribution Count') => 'donation_count', |
53 | ts('Contribution Amount') => 'donation_amount', | |
6a488035 TO |
54 | ); |
55 | ||
6ea503d4 TO |
56 | $this->_amounts = array( |
57 | 'min_amount_1' => ts('Min Amount One'), | |
6a488035 TO |
58 | 'max_amount_1' => ts('Max Amount One'), |
59 | 'min_amount_2' => ts('Min Amount Two'), | |
60 | 'max_amount_2' => ts('Max Amount Two'), | |
61 | 'exclude_min_amount' => ts('Exclusion Min Amount'), | |
62 | 'exclude_max_amount' => ts('Exclusion Max Amount'), | |
63 | ); | |
64 | ||
6ea503d4 TO |
65 | $this->_dates = array( |
66 | 'start_date_1' => ts('Start Date One'), | |
6a488035 TO |
67 | 'end_date_1' => ts('End Date One'), |
68 | 'start_date_2' => ts('Start Date Two'), | |
69 | 'end_date_2' => ts('End Date Two'), | |
70 | 'exclude_start_date' => ts('Exclusion Start Date'), | |
71 | 'exclude_end_date' => ts('Exclusion End Date'), | |
72 | ); | |
73 | ||
74 | $this->_checkboxes = array('is_first_amount' => ts('First Donation?')); | |
75 | ||
76 | foreach ($this->_amounts as $name => $title) { | |
77 | $this->{$name} = CRM_Utils_Array::value($name, $this->_formValues); | |
78 | } | |
79 | ||
80 | foreach ($this->_checkboxes as $name => $title) { | |
81 | $this->{$name} = CRM_Utils_Array::value($name, $this->_formValues, FALSE); | |
82 | } | |
83 | ||
84 | foreach ($this->_dates as $name => $title) { | |
a7488080 | 85 | if (!empty($this->_formValues[$name])) { |
6a488035 TO |
86 | $this->{$name} = CRM_Utils_Date::processDate($this->_formValues[$name]); |
87 | } | |
88 | } | |
89 | } | |
90 | ||
86538308 | 91 | /** |
c490a46a | 92 | * @param CRM_Core_Form $form |
86538308 | 93 | */ |
00be9182 | 94 | public function buildForm(&$form) { |
6a488035 TO |
95 | |
96 | foreach ($this->_amounts as $name => $title) { | |
97 | $form->add('text', | |
98 | $name, | |
99 | $title | |
100 | ); | |
101 | } | |
102 | ||
103 | foreach ($this->_dates as $name => $title) { | |
104 | $form->addDate($name, $title, FALSE, array('formatType' => 'custom')); | |
105 | } | |
106 | ||
107 | foreach ($this->_checkboxes as $name => $title) { | |
108 | $form->add('checkbox', | |
109 | $name, | |
110 | $title | |
111 | ); | |
112 | } | |
113 | ||
114 | $this->setTitle('Contributions made in Year X and not Year Y'); | |
115 | // @TODO: Decide on better names for "Exclusion" | |
116 | // @TODO: Add rule to ensure that exclusion dates are not in the inclusion range | |
117 | } | |
118 | ||
4e87860d EM |
119 | /** |
120 | * @return mixed | |
121 | */ | |
00be9182 | 122 | public function count() { |
6a488035 TO |
123 | $sql = $this->all(); |
124 | ||
125 | $dao = CRM_Core_DAO::executeQuery($sql); | |
126 | return $dao->N; | |
127 | } | |
128 | ||
86538308 EM |
129 | /** |
130 | * @param int $offset | |
131 | * @param int $rowcount | |
132 | * @param null $sort | |
d14ccbdc | 133 | * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned |
86538308 EM |
134 | * |
135 | * @return string | |
136 | */ | |
e98a9804 | 137 | public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) { |
6a488035 TO |
138 | return $this->all($offset, $rowcount, $sort, FALSE, TRUE); |
139 | } | |
140 | ||
86538308 EM |
141 | /** |
142 | * @param int $offset | |
143 | * @param int $rowcount | |
144 | * @param null $sort | |
145 | * @param bool $includeContactIDs | |
146 | * @param bool $justIDs | |
147 | * | |
148 | * @return string | |
149 | */ | |
8d7a9d07 | 150 | public function all( |
6a488035 TO |
151 | $offset = 0, |
152 | $rowcount = 0, | |
153 | $sort = NULL, | |
154 | $includeContactIDs = FALSE, | |
155 | $justIDs = FALSE | |
156 | ) { | |
157 | ||
158 | $where = $this->where(); | |
159 | if (!empty($where)) { | |
160 | $where = " AND $where"; | |
161 | } | |
162 | ||
163 | $having = $this->having(); | |
164 | if ($having) { | |
165 | $having = " HAVING $having "; | |
166 | } | |
167 | ||
168 | $from = $this->from(); | |
169 | ||
d02c030f | 170 | $select = $this->select(); |
6a488035 | 171 | if ($justIDs) { |
b9f6b150 | 172 | $select .= ', contact_a.id, display_name'; |
6a488035 TO |
173 | } |
174 | else { | |
6a488035 | 175 | $select = " |
b9f6b150 | 176 | DISTINCT contact_a.id as contact_id, |
177 | contact_a.display_name as display_name, | |
d02c030f | 178 | $select "; |
6a488035 | 179 | } |
d14ccbdc | 180 | $this->buildACLClause('contact_a'); |
6a488035 TO |
181 | $sql = " |
182 | SELECT $select | |
d14ccbdc | 183 | FROM civicrm_contact AS contact_a {$this->_aclFrom} |
b9f6b150 | 184 | LEFT JOIN civicrm_contribution contrib_1 ON contrib_1.contact_id = contact_a.id |
6a488035 | 185 | $from |
b9f6b150 | 186 | WHERE contrib_1.contact_id = contact_a.id |
6a488035 TO |
187 | AND contrib_1.is_test = 0 |
188 | $where | |
b9f6b150 | 189 | GROUP BY contact_a.id |
6a488035 TO |
190 | $having |
191 | ORDER BY donation_amount desc | |
192 | "; | |
193 | ||
7c34ab11 | 194 | if ($justIDs) { |
195 | CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS CustomSearch_SYBNT_temp"); | |
196 | $query = "CREATE TEMPORARY TABLE CustomSearch_SYBNT_temp AS ({$sql})"; | |
197 | $dao = CRM_Core_DAO::executeQuery($query); | |
b9f6b150 | 198 | $sql = "SELECT contact_a.id as contact_id FROM CustomSearch_SYBNT_temp as contact_a"; |
7c34ab11 | 199 | } |
6a488035 TO |
200 | return $sql; |
201 | } | |
202 | ||
86538308 EM |
203 | /** |
204 | * @return string | |
205 | */ | |
00be9182 | 206 | public function select() { |
7c34ab11 | 207 | if (!empty($this->start_date_2) || !empty($this->end_date_2)) { |
6a488035 TO |
208 | return " |
209 | sum(contrib_1.total_amount) + sum(contrib_2.total_amount) AS donation_amount, | |
210 | count(contrib_1.id) + count(contrib_1.id) AS donation_count | |
211 | "; | |
212 | } | |
213 | else { | |
214 | return " | |
215 | sum(contrib_1.total_amount) AS donation_amount, | |
216 | count(contrib_1.id) AS donation_count | |
217 | "; | |
218 | } | |
219 | } | |
220 | ||
86538308 EM |
221 | /** |
222 | * @return null|string | |
223 | */ | |
00be9182 | 224 | public function from() { |
6a488035 | 225 | $from = NULL; |
7c34ab11 | 226 | if (!empty($this->start_date_2) || !empty($this->end_date_2)) { |
b9f6b150 | 227 | $from .= " LEFT JOIN civicrm_contribution contrib_2 ON contrib_2.contact_id = contact_a.id "; |
6a488035 TO |
228 | } |
229 | ||
7c34ab11 | 230 | if (!empty($this->exclude_start_date) || |
231 | !empty($this->exclude_end_date) || | |
232 | !empty($this->is_first_amount) | |
6a488035 | 233 | ) { |
b9f6b150 | 234 | $from .= " LEFT JOIN XG_CustomSearch_SYBNT xg ON xg.contact_id = contact_a.id "; |
6a488035 TO |
235 | } |
236 | ||
237 | return $from; | |
238 | } | |
239 | ||
86538308 EM |
240 | /** |
241 | * @param bool $includeContactIDs | |
242 | * | |
243 | * @return string | |
244 | */ | |
00be9182 | 245 | public function where($includeContactIDs = FALSE) { |
6a488035 TO |
246 | $clauses = array(); |
247 | ||
7c34ab11 | 248 | if (!empty($this->start_date_1)) { |
6a488035 TO |
249 | $clauses[] = "contrib_1.receive_date >= {$this->start_date_1}"; |
250 | } | |
251 | ||
7c34ab11 | 252 | if (!empty($this->end_date_1)) { |
6a488035 TO |
253 | $clauses[] = "contrib_1.receive_date <= {$this->end_date_1}"; |
254 | } | |
255 | ||
7c34ab11 | 256 | if (!empty($this->start_date_2) || !empty($this->end_date_2)) { |
6a488035 TO |
257 | $clauses[] = "contrib_2.is_test = 0"; |
258 | ||
7c34ab11 | 259 | if (!empty($this->start_date_2)) { |
6a488035 TO |
260 | $clauses[] = "contrib_2.receive_date >= {$this->start_date_2}"; |
261 | } | |
262 | ||
7c34ab11 | 263 | if (!empty($this->end_date_2)) { |
6a488035 TO |
264 | $clauses[] = "contrib_2.receive_date <= {$this->end_date_2}"; |
265 | } | |
266 | } | |
267 | ||
7c34ab11 | 268 | if (!empty($this->exclude_start_date) || |
269 | !empty($this->exclude_end_date) || | |
270 | !empty($this->is_first_amount) | |
6a488035 TO |
271 | ) { |
272 | ||
273 | // first create temp table to store contact ids | |
274 | $sql = "DROP TEMPORARY TABLE IF EXISTS XG_CustomSearch_SYBNT"; | |
275 | CRM_Core_DAO::executeQuery($sql); | |
276 | ||
277 | $sql = "CREATE TEMPORARY TABLE XG_CustomSearch_SYBNT ( contact_id int primary key) ENGINE=HEAP"; | |
278 | CRM_Core_DAO::executeQuery($sql); | |
279 | ||
280 | $excludeClauses = array(); | |
281 | if ($this->exclude_start_date) { | |
282 | $excludeClauses[] = "c.receive_date >= {$this->exclude_start_date}"; | |
283 | } | |
284 | ||
285 | if ($this->exclude_end_date) { | |
286 | $excludeClauses[] = "c.receive_date <= {$this->exclude_end_date}"; | |
287 | } | |
288 | ||
289 | $excludeClause = NULL; | |
290 | if ($excludeClauses) { | |
291 | $excludeClause = ' AND ' . implode(' AND ', $excludeClauses); | |
292 | } | |
293 | ||
294 | $having = array(); | |
295 | if ($this->exclude_min_amount) { | |
296 | $having[] = "sum(c.total_amount) >= {$this->exclude_min_amount}"; | |
297 | } | |
298 | ||
299 | if ($this->exclude_max_amount) { | |
300 | $having[] = "sum(c.total_amount) <= {$this->exclude_max_amount}"; | |
301 | } | |
302 | ||
303 | $havingClause = NULL; | |
304 | if (!empty($having)) { | |
305 | $havingClause = "HAVING " . implode(' AND ', $having); | |
306 | } | |
307 | ||
308 | if ($excludeClause || $havingClause) { | |
309 | // Run subquery | |
310 | $query = " | |
311 | REPLACE INTO XG_CustomSearch_SYBNT | |
312 | SELECT DISTINCT contact_id AS contact_id | |
313 | FROM civicrm_contribution c | |
314 | WHERE c.is_test = 0 | |
315 | $excludeClause | |
316 | GROUP BY c.contact_id | |
317 | $havingClause | |
318 | "; | |
319 | ||
320 | $dao = CRM_Core_DAO::executeQuery($query); | |
321 | } | |
322 | ||
323 | // now ensure we dont consider donors that are not first time | |
324 | if ($this->is_first_amount) { | |
325 | $query = " | |
326 | REPLACE INTO XG_CustomSearch_SYBNT | |
327 | SELECT DISTINCT contact_id AS contact_id | |
328 | FROM civicrm_contribution c | |
329 | WHERE c.is_test = 0 | |
330 | AND c.receive_date < {$this->start_date_1} | |
331 | "; | |
332 | $dao = CRM_Core_DAO::executeQuery($query); | |
333 | } | |
334 | ||
335 | $clauses[] = " xg.contact_id IS NULL "; | |
336 | } | |
47b8444f SL |
337 | if ($this->_aclWhere) { |
338 | $clauses[] .= " {$this->_aclWhere} "; | |
339 | } | |
5b7c52cf | 340 | return implode(' AND ', $clauses); |
6a488035 TO |
341 | } |
342 | ||
86538308 EM |
343 | /** |
344 | * @param bool $includeContactIDs | |
345 | * | |
346 | * @return string | |
347 | */ | |
00be9182 | 348 | public function having($includeContactIDs = FALSE) { |
6a488035 TO |
349 | $clauses = array(); |
350 | $min = CRM_Utils_Array::value('min_amount', $this->_formValues); | |
351 | if ($min) { | |
352 | $clauses[] = "sum(contrib_1.total_amount) >= $min"; | |
353 | } | |
354 | ||
355 | $max = CRM_Utils_Array::value('max_amount', $this->_formValues); | |
356 | if ($max) { | |
357 | $clauses[] = "sum(contrib_1.total_amount) <= $max"; | |
358 | } | |
359 | ||
360 | return implode(' AND ', $clauses); | |
361 | } | |
362 | ||
86538308 EM |
363 | /** |
364 | * @return array | |
365 | */ | |
00be9182 | 366 | public function &columns() { |
6a488035 TO |
367 | return $this->_columns; |
368 | } | |
369 | ||
86538308 EM |
370 | /** |
371 | * @return string | |
372 | */ | |
00be9182 | 373 | public function templateFile() { |
6a488035 TO |
374 | return 'CRM/Contact/Form/Search/Custom/ContribSYBNT.tpl'; |
375 | } | |
376 | ||
86538308 EM |
377 | /** |
378 | * @return null | |
379 | */ | |
00be9182 | 380 | public function summary() { |
6a488035 TO |
381 | return NULL; |
382 | } | |
383 | ||
86538308 EM |
384 | /** |
385 | * @param $title | |
386 | */ | |
00be9182 | 387 | public function setTitle($title) { |
6a488035 TO |
388 | if ($title) { |
389 | CRM_Utils_System::setTitle($title); | |
390 | } | |
391 | else { | |
392 | CRM_Utils_System::setTitle(ts('Search')); | |
393 | } | |
394 | } | |
96025800 | 395 | |
d14ccbdc SL |
396 | /** |
397 | * @param string $tableAlias | |
398 | */ | |
399 | public function buildACLClause($tableAlias = 'contact') { | |
400 | list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias); | |
401 | } | |
402 | ||
6a488035 | 403 | } |