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