CRM/Contact/Form/Search/Custom/ContributionAggregate.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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
  31  * @copyright CiviCRM LLC (c) 2004-2015
  32  */
  33 class CRM_Contact_Form_Search_Custom_ContributionAggregate extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
  34
  35   protected $_formValues;
  36   protected $_aclFrom = NULL;
  37   protected $_aclWhere = NULL;
  38   public $_permissionedComponent;
  39
  40   /**
  41    * Class constructor.
  42    *
  43    * @param array $formValues
  44    */
  45   public function __construct(&$formValues) {
  46     $this->_formValues = $formValues;
  47
  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',
  54     );
  55
  56     // define component access permission needed
  57     $this->_permissionedComponent = 'CiviContribute';
  58   }
  59
  60   /**
  61    * Build form.
  62    *
  63    * @param CRM_Core_Form $form
  64    */
  65   public function buildForm(&$form) {
  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');
  86
  87     $form->addDate('start_date', ts('Contribution Date From'), FALSE, array('formatType' => 'custom'));
  88     $form->addDate('end_date', ts('...through'), FALSE, array('formatType' => 'custom'));
  89
  90     $form->addSelect('financial_type_id',
  91       array('entity' => 'contribution', 'multiple' => 'multiple', 'context' => 'search')
  92     );
  93
  94     /**
  95      * If you are using the sample template, this array tells the template fields to render
  96      * for the search form.
  97      */
  98     $form->assign('elements', array('min_amount', 'max_amount', 'start_date', 'end_date'));
  99   }
 100
 101   /**
 102    * Define the smarty template used to layout the search form and results listings.
 103    *
 104    * @return string
 105    */
 106   public function templateFile() {
 107     return 'CRM/Contact/Form/Search/Custom/ContributionAggregate.tpl';
 108   }
 109
 110   /**
 111    * Construct the search query.
 112    *
 113    * @param int $offset
 114    * @param int $rowcount
 115    * @param string|object $sort
 116    * @param bool $includeContactIDs
 117    * @param bool $justIDs
 118    *
 119    * @return string
 120    */
 121   public function all(
 122     $offset = 0, $rowcount = 0, $sort = NULL,
 123     $includeContactIDs = FALSE, $justIDs = FALSE
 124   ) {
 125
 126     // SELECT clause must include contact_id as an alias for civicrm_contact.id
 127     if ($justIDs) {
 128       $select = "contact_a.id as contact_id";
 129     }
 130     else {
 131       $select = "
 132 DISTINCT contact_a.id as contact_id,
 133 contact_a.sort_name as sort_name,
 134 sum(contrib.total_amount) AS donation_amount,
 135 count(contrib.id) AS donation_count
 136 ";
 137     }
 138     $from = $this->from();
 139
 140     $where = $this->where($includeContactIDs);
 141
 142     $having = $this->having();
 143     if ($having) {
 144       $having = " HAVING $having ";
 145     }
 146
 147     $sql = "
 148 SELECT $select
 149 FROM   $from
 150 WHERE  $where
 151 GROUP BY contact_a.id
 152 $having
 153 ";
 154     //for only contact ids ignore order.
 155     if (!$justIDs) {
 156       // Define ORDER BY for query in $sort, with default value
 157       if (!empty($sort)) {
 158         if (is_string($sort)) {
 159           $sort = CRM_Utils_Type::escape($sort, 'String');
 160           $sql .= " ORDER BY $sort ";
 161         }
 162         else {
 163           $sql .= " ORDER BY " . trim($sort->orderBy());
 164         }
 165       }
 166       else {
 167         $sql .= "ORDER BY donation_amount desc";
 168       }
 169     }
 170
 171     if ($rowcount > 0 && $offset >= 0) {
 172       $offset = CRM_Utils_Type::escape($offset, 'Int');
 173       $rowcount = CRM_Utils_Type::escape($rowcount, 'Int');
 174       $sql .= " LIMIT $offset, $rowcount ";
 175     }
 176     return $sql;
 177   }
 178
 179   /**
 180    * @return string
 181    */
 182   public function from() {
 183     $this->buildACLClause('contact_a');
 184     $from = "
 185 civicrm_contribution AS contrib,
 186 civicrm_contact AS contact_a {$this->_aclFrom}
 187 ";
 188
 189     return $from;
 190   }
 191
 192   /**
 193    * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values.
 194    *
 195    * @param bool $includeContactIDs
 196    *
 197    * @return string
 198    */
 199   public function where($includeContactIDs = FALSE) {
 200     $clauses = array();
 201
 202     $clauses[] = "contrib.contact_id = contact_a.id";
 203     $clauses[] = "contrib.is_test = 0";
 204
 205     $startTime = !empty($this->_formValues['start_date_time']) ? $this->_formValues['start_date_time'] : '12:00:00';
 206     $endTime = !empty($this->_formValues['end_date_time']) ? $this->_formValues['end_date_time'] : '23:59:59';
 207
 208     $startDate = CRM_Utils_Date::processDate($this->_formValues['start_date'], $startTime);
 209     if ($startDate) {
 210       $clauses[] = "contrib.receive_date >= $startDate";
 211     }
 212
 213     $endDate = CRM_Utils_Date::processDate($this->_formValues['end_date'], $endTime);
 214     if ($endDate) {
 215       $clauses[] = "contrib.receive_date <= $endDate";
 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'])) {
 235       $financial_type_ids = implode(',', array_values($this->_formValues['financial_type_id']));
 236       $clauses[] = "contrib.financial_type_id IN ($financial_type_ids)";
 237     }
 238     if ($this->_aclWhere) {
 239       $clauses[] = " {$this->_aclWhere} ";
 240     }
 241
 242     return implode(' AND ', $clauses);
 243   }
 244
 245   /**
 246    * @param bool $includeContactIDs
 247    *
 248    * @return string
 249    */
 250   public function having($includeContactIDs = FALSE) {
 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
 267   /*
 268    * Functions below generally don't need to be modified
 269    */
 270
 271   /**
 272    * @inheritDoc
 273    */
 274   public function count() {
 275     $sql = $this->all();
 276
 277     $dao = CRM_Core_DAO::executeQuery($sql,
 278       CRM_Core_DAO::$_nullArray
 279     );
 280     return $dao->N;
 281   }
 282
 283   /**
 284    * @param int $offset
 285    * @param int $rowcount
 286    * @param null $sort
 287    * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned
 288    *
 289    * @return string
 290    */
 291   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) {
 292     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 293   }
 294
 295   /**
 296    * @return array
 297    */
 298   public function &columns() {
 299     return $this->_columns;
 300   }
 301
 302   /**
 303    * @param $title
 304    */
 305   public function setTitle($title) {
 306     if ($title) {
 307       CRM_Utils_System::setTitle($title);
 308     }
 309     else {
 310       CRM_Utils_System::setTitle(ts('Search'));
 311     }
 312   }
 313
 314   /**
 315    * @return null
 316    */
 317   public function summary() {
 318     return NULL;
 319   }
 320
 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
 328 }