3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
33 class CRM_Contact_Form_Search_Custom_ContributionAggregate
extends CRM_Contact_Form_Search_Custom_Base
implements CRM_Contact_Form_Search_Interface
{
35 protected $_formValues;
36 protected $_aclFrom = NULL;
37 protected $_aclWhere = NULL;
38 public $_permissionedComponent;
43 * @param array $formValues
45 public function __construct(&$formValues) {
46 $this->_formValues
= $formValues;
48 // Define the columns for search result rows
49 $this->_columns
= array(
50 ts('Contact ID') => 'contact_id',
51 ts('Name') => 'sort_name',
52 ts('Contribution Count') => 'donation_count',
53 ts('Contribution Amount') => 'donation_amount',
56 // define component access permission needed
57 $this->_permissionedComponent
= 'CiviContribute';
63 * @param CRM_Core_Form $form
65 public function buildForm(&$form) {
68 * You can define a custom title for the search form
70 $this->setTitle('Find Contributors by Aggregate Totals');
73 * Define the search form fields here
77 ts('Aggregate Total Between $')
79 $form->addRule('min_amount', ts('Please enter a valid amount (numbers and decimal point only).'), 'money');
85 $form->addRule('max_amount', ts('Please enter a valid amount (numbers and decimal point only).'), 'money');
86 CRM_Core_Form_Date
::buildDateRange($form, 'contribution_date', 1, '_low', '_high', ts('From:'), FALSE, FALSE);
88 $form->addSelect('financial_type_id',
89 array('entity' => 'contribution', 'multiple' => 'multiple', 'context' => 'search')
93 * If you are using the sample template, this array tells the template fields to render
94 * for the search form.
96 $form->assign('elements', array('min_amount', 'max_amount'));
100 * Define the smarty template used to layout the search form and results listings.
104 public function templateFile() {
105 return 'CRM/Contact/Form/Search/Custom/ContributionAggregate.tpl';
109 * Construct the search query.
112 * @param int $rowcount
113 * @param string|object $sort
114 * @param bool $includeContactIDs
115 * @param bool $justIDs
120 $offset = 0, $rowcount = 0, $sort = NULL,
121 $includeContactIDs = FALSE, $justIDs = FALSE
124 // SELECT clause must include contact_id as an alias for civicrm_contact.id
126 $select = "contact_a.id as contact_id";
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
136 $from = $this->from();
138 $where = $this->where($includeContactIDs);
140 $having = $this->having();
142 $having = " HAVING $having ";
149 GROUP BY contact_a.id
152 //for only contact ids ignore order.
154 // Define ORDER BY for query in $sort, with default value
156 if (is_string($sort)) {
157 $sort = CRM_Utils_Type
::escape($sort, 'String');
158 $sql .= " ORDER BY $sort ";
161 $sql .= " ORDER BY " . trim($sort->orderBy());
165 $sql .= "ORDER BY donation_amount desc";
169 if ($rowcount > 0 && $offset >= 0) {
170 $offset = CRM_Utils_Type
::escape($offset, 'Int');
171 $rowcount = CRM_Utils_Type
::escape($rowcount, 'Int');
172 $sql .= " LIMIT $offset, $rowcount ";
180 public function from() {
181 $this->buildACLClause('contact_a');
183 civicrm_contribution AS contrib,
184 civicrm_contact AS contact_a {$this->_aclFrom}
191 * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values.
193 * @param bool $includeContactIDs
197 public function where($includeContactIDs = FALSE) {
199 "contrib.contact_id = contact_a.id",
200 "contrib.is_test = 0",
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'],
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);
214 $clauses[] = "contrib.receive_date <= " . CRM_Utils_Date
::processDate($value);
218 if ($includeContactIDs) {
219 $contactIDs = array();
220 foreach ($this->_formValues
as $id => $value) {
222 substr($id, 0, CRM_Core_Form
::CB_PREFIX_LEN
) == CRM_Core_Form
::CB_PREFIX
224 $contactIDs[] = substr($id, CRM_Core_Form
::CB_PREFIX_LEN
);
228 if (!empty($contactIDs)) {
229 $contactIDs = implode(', ', $contactIDs);
230 $clauses[] = "contact_a.id IN ( $contactIDs )";
234 if (!empty($this->_formValues
['financial_type_id'])) {
235 $financial_type_ids = implode(',', array_values($this->_formValues
['financial_type_id']));
236 $clauses[] = "contrib.financial_type_id IN ($financial_type_ids)";
238 if ($this->_aclWhere
) {
239 $clauses[] = " {$this->_aclWhere} ";
242 return implode(' AND ', $clauses);
246 * @param bool $includeContactIDs
250 public function having($includeContactIDs = FALSE) {
252 $min = CRM_Utils_Array
::value('min_amount', $this->_formValues
);
254 $min = CRM_Utils_Rule
::cleanMoney($min);
255 $clauses[] = "sum(contrib.total_amount) >= $min";
258 $max = CRM_Utils_Array
::value('max_amount', $this->_formValues
);
260 $max = CRM_Utils_Rule
::cleanMoney($max);
261 $clauses[] = "sum(contrib.total_amount) <= $max";
264 return implode(' AND ', $clauses);
268 * Functions below generally don't need to be modified
274 public function count() {
277 $dao = CRM_Core_DAO
::executeQuery($sql,
278 CRM_Core_DAO
::$_nullArray
285 * @param int $rowcount
287 * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned
291 public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) {
292 return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
298 public function &columns() {
299 return $this->_columns
;
305 public function setTitle($title) {
307 CRM_Utils_System
::setTitle($title);
310 CRM_Utils_System
::setTitle(ts('Search'));
317 public function summary() {
322 * @param string $tableAlias
324 public function buildACLClause($tableAlias = 'contact') {
325 list($this->_aclFrom
, $this->_aclWhere
) = CRM_Contact_BAO_Contact_Permission
::cacheClause($tableAlias);