Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
06b69b18 | 4 | | CiviCRM version 4.5 | |
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 | +--------------------------------------------------------------------+ | |
26 | */ | |
27 | ||
28 | /** | |
29 | * | |
30 | * @package CRM | |
06b69b18 | 31 | * @copyright CiviCRM LLC (c) 2004-2014 |
6a488035 TO |
32 | * $Id$ |
33 | * | |
34 | */ | |
35 | class CRM_Contact_Form_Search_Custom_ContribSYBNT implements CRM_Contact_Form_Search_Interface { | |
36 | ||
4e54c348 PJ |
37 | protected $_formValues; |
38 | public $_permissionedComponent; | |
39 | ||
86538308 EM |
40 | /** |
41 | * @param $formValues | |
42 | */ | |
4e54c348 | 43 | 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 TO |
49 | ts('Name') => 'display_name', |
50 | ts('Donation Count') => 'donation_count', | |
51 | ts('Donation Amount') => 'donation_amount', | |
52 | ); | |
53 | ||
54 | $this->_amounts = array('min_amount_1' => ts('Min Amount One'), | |
55 | 'max_amount_1' => ts('Max Amount One'), | |
56 | 'min_amount_2' => ts('Min Amount Two'), | |
57 | 'max_amount_2' => ts('Max Amount Two'), | |
58 | 'exclude_min_amount' => ts('Exclusion Min Amount'), | |
59 | 'exclude_max_amount' => ts('Exclusion Max Amount'), | |
60 | ); | |
61 | ||
62 | $this->_dates = array('start_date_1' => ts('Start Date One'), | |
63 | 'end_date_1' => ts('End Date One'), | |
64 | 'start_date_2' => ts('Start Date Two'), | |
65 | 'end_date_2' => ts('End Date Two'), | |
66 | 'exclude_start_date' => ts('Exclusion Start Date'), | |
67 | 'exclude_end_date' => ts('Exclusion End Date'), | |
68 | ); | |
69 | ||
70 | $this->_checkboxes = array('is_first_amount' => ts('First Donation?')); | |
71 | ||
72 | foreach ($this->_amounts as $name => $title) { | |
73 | $this->{$name} = CRM_Utils_Array::value($name, $this->_formValues); | |
74 | } | |
75 | ||
76 | foreach ($this->_checkboxes as $name => $title) { | |
77 | $this->{$name} = CRM_Utils_Array::value($name, $this->_formValues, FALSE); | |
78 | } | |
79 | ||
80 | foreach ($this->_dates as $name => $title) { | |
a7488080 | 81 | if (!empty($this->_formValues[$name])) { |
6a488035 TO |
82 | $this->{$name} = CRM_Utils_Date::processDate($this->_formValues[$name]); |
83 | } | |
84 | } | |
85 | } | |
86 | ||
86538308 EM |
87 | /** |
88 | * @param $form | |
89 | */ | |
6a488035 TO |
90 | function buildForm(&$form) { |
91 | ||
92 | foreach ($this->_amounts as $name => $title) { | |
93 | $form->add('text', | |
94 | $name, | |
95 | $title | |
96 | ); | |
97 | } | |
98 | ||
99 | foreach ($this->_dates as $name => $title) { | |
100 | $form->addDate($name, $title, FALSE, array('formatType' => 'custom')); | |
101 | } | |
102 | ||
103 | foreach ($this->_checkboxes as $name => $title) { | |
104 | $form->add('checkbox', | |
105 | $name, | |
106 | $title | |
107 | ); | |
108 | } | |
109 | ||
110 | $this->setTitle('Contributions made in Year X and not Year Y'); | |
111 | // @TODO: Decide on better names for "Exclusion" | |
112 | // @TODO: Add rule to ensure that exclusion dates are not in the inclusion range | |
113 | } | |
114 | ||
4e87860d EM |
115 | /** |
116 | * @return mixed | |
117 | */ | |
6a488035 TO |
118 | function count() { |
119 | $sql = $this->all(); | |
120 | ||
121 | $dao = CRM_Core_DAO::executeQuery($sql); | |
122 | return $dao->N; | |
123 | } | |
124 | ||
86538308 EM |
125 | /** |
126 | * @param int $offset | |
127 | * @param int $rowcount | |
128 | * @param null $sort | |
129 | * | |
130 | * @return string | |
131 | */ | |
6a488035 TO |
132 | function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) { |
133 | return $this->all($offset, $rowcount, $sort, FALSE, TRUE); | |
134 | } | |
135 | ||
86538308 EM |
136 | /** |
137 | * @param int $offset | |
138 | * @param int $rowcount | |
139 | * @param null $sort | |
140 | * @param bool $includeContactIDs | |
141 | * @param bool $justIDs | |
142 | * | |
143 | * @return string | |
144 | */ | |
6a488035 TO |
145 | function all( |
146 | $offset = 0, | |
147 | $rowcount = 0, | |
148 | $sort = NULL, | |
149 | $includeContactIDs = FALSE, | |
150 | $justIDs = FALSE | |
151 | ) { | |
152 | ||
153 | $where = $this->where(); | |
154 | if (!empty($where)) { | |
155 | $where = " AND $where"; | |
156 | } | |
157 | ||
158 | $having = $this->having(); | |
159 | if ($having) { | |
160 | $having = " HAVING $having "; | |
161 | } | |
162 | ||
163 | $from = $this->from(); | |
164 | ||
165 | if ($justIDs) { | |
7c34ab11 | 166 | $select = $this->select(); |
b9f6b150 | 167 | $select .= ', contact_a.id, display_name'; |
6a488035 TO |
168 | } |
169 | else { | |
170 | $select = $this->select(); | |
171 | $select = " | |
b9f6b150 | 172 | DISTINCT contact_a.id as contact_id, |
173 | contact_a.display_name as display_name, | |
6a488035 TO |
174 | $select |
175 | "; | |
176 | ||
177 | } | |
178 | ||
179 | $sql = " | |
180 | SELECT $select | |
b9f6b150 | 181 | FROM civicrm_contact AS contact_a |
182 | LEFT JOIN civicrm_contribution contrib_1 ON contrib_1.contact_id = contact_a.id | |
6a488035 | 183 | $from |
b9f6b150 | 184 | WHERE contrib_1.contact_id = contact_a.id |
6a488035 TO |
185 | AND contrib_1.is_test = 0 |
186 | $where | |
b9f6b150 | 187 | GROUP BY contact_a.id |
6a488035 TO |
188 | $having |
189 | ORDER BY donation_amount desc | |
190 | "; | |
191 | ||
7c34ab11 | 192 | |
193 | if ($justIDs) { | |
194 | CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS CustomSearch_SYBNT_temp"); | |
195 | $query = "CREATE TEMPORARY TABLE CustomSearch_SYBNT_temp AS ({$sql})"; | |
196 | $dao = CRM_Core_DAO::executeQuery($query); | |
b9f6b150 | 197 | $sql = "SELECT contact_a.id as contact_id FROM CustomSearch_SYBNT_temp as contact_a"; |
7c34ab11 | 198 | } |
199 | ||
6a488035 TO |
200 | return $sql; |
201 | } | |
202 | ||
86538308 EM |
203 | /** |
204 | * @return string | |
205 | */ | |
6a488035 | 206 | 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 | */ | |
6a488035 TO |
224 | function from() { |
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 | */ | |
6a488035 TO |
245 | function where($includeContactIDs = FALSE) { |
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 | } | |
337 | ||
338 | return implode(' AND ', $clauses); | |
339 | } | |
340 | ||
86538308 EM |
341 | /** |
342 | * @param bool $includeContactIDs | |
343 | * | |
344 | * @return string | |
345 | */ | |
6a488035 TO |
346 | function having($includeContactIDs = FALSE) { |
347 | $clauses = array(); | |
348 | $min = CRM_Utils_Array::value('min_amount', $this->_formValues); | |
349 | if ($min) { | |
350 | $clauses[] = "sum(contrib_1.total_amount) >= $min"; | |
351 | } | |
352 | ||
353 | $max = CRM_Utils_Array::value('max_amount', $this->_formValues); | |
354 | if ($max) { | |
355 | $clauses[] = "sum(contrib_1.total_amount) <= $max"; | |
356 | } | |
357 | ||
358 | return implode(' AND ', $clauses); | |
359 | } | |
360 | ||
86538308 EM |
361 | /** |
362 | * @return array | |
363 | */ | |
6a488035 TO |
364 | function &columns() { |
365 | return $this->_columns; | |
366 | } | |
367 | ||
86538308 EM |
368 | /** |
369 | * @return string | |
370 | */ | |
6a488035 TO |
371 | function templateFile() { |
372 | return 'CRM/Contact/Form/Search/Custom/ContribSYBNT.tpl'; | |
373 | } | |
374 | ||
86538308 EM |
375 | /** |
376 | * @return null | |
377 | */ | |
6a488035 TO |
378 | function summary() { |
379 | return NULL; | |
380 | } | |
381 | ||
86538308 EM |
382 | /** |
383 | * @param $title | |
384 | */ | |
6a488035 TO |
385 | function setTitle($title) { |
386 | if ($title) { | |
387 | CRM_Utils_System::setTitle($title); | |
388 | } | |
389 | else { | |
390 | CRM_Utils_System::setTitle(ts('Search')); | |
391 | } | |
392 | } | |
393 | } | |
394 |