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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_ContributionAggregate implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_formValues;
  38   public $_permissionedComponent;
  39
  40   /**
  41    * @param $formValues
  42    */
  43   public function __construct(&$formValues) {
  44     $this->_formValues = $formValues;
  45
  46     // Define the columns for search result rows
  47     $this->_columns = array(
  48       ts('Contact ID') => 'contact_id',
  49       ts('Name') => 'sort_name',
  50       ts('Contribution Count') => 'donation_count',
  51       ts('Contribution Amount') => 'donation_amount',
  52     );
  53
  54     // define component access permission needed
  55     $this->_permissionedComponent = 'CiviContribute';
  56   }
  57
  58   /**
  59    * @param CRM_Core_Form $form
  60    */
  61   public function buildForm(&$form) {
  62
  63     /**
  64      * You can define a custom title for the search form
  65      */
  66     $this->setTitle('Find Contributors by Aggregate Totals');
  67
  68     /**
  69      * Define the search form fields here
  70      */
  71     $form->add('text',
  72       'min_amount',
  73       ts('Aggregate Total Between $')
  74     );
  75     $form->addRule('min_amount', ts('Please enter a valid amount (numbers and decimal point only).'), 'money');
  76
  77     $form->add('text',
  78       'max_amount',
  79       ts('...and $')
  80     );
  81     $form->addRule('max_amount', ts('Please enter a valid amount (numbers and decimal point only).'), 'money');
  82
  83     $form->addDate('start_date', ts('Contribution Date From'), FALSE, array('formatType' => 'custom'));
  84     $form->addDate('end_date', ts('...through'), FALSE, array('formatType' => 'custom'));
  85
  86     $financial_types = CRM_Contribute_PseudoConstant::financialType();
  87     foreach ($financial_types as $financial_type_id => $financial_type) {
  88       $form->addElement('checkbox', "financial_type_id[{$financial_type_id}]", 'Financial Type', $financial_type);
  89     }
  90
  91     /**
  92      * If you are using the sample template, this array tells the template fields to render
  93      * for the search form.
  94      */
  95     $form->assign('elements', array('min_amount', 'max_amount', 'start_date', 'end_date', 'financial_type_id'));
  96   }
  97
  98   /**
  99    * Define the smarty template used to layout the search form and results listings.
 100    *
 101    * @return string
 102    */
 103   public function templateFile() {
 104     return 'CRM/Contact/Form/Search/Custom/ContributionAggregate.tpl';
 105   }
 106
 107   /**
 108    * Construct the search query
 109    *
 110    * @param int $offset
 111    * @param int $rowcount
 112    * @param string|object $sort
 113    * @param bool $includeContactIDs
 114    * @param bool $justIDs
 115    *
 116    * @return string
 117    */
 118   function all(
 119     $offset = 0, $rowcount = 0, $sort = NULL,
 120     $includeContactIDs = FALSE, $justIDs = FALSE
 121   ) {
 122
 123     // SELECT clause must include contact_id as an alias for civicrm_contact.id
 124     if ($justIDs) {
 125       $select = "contact_a.id as contact_id";
 126     }
 127     else {
 128       $select = "
 129 DISTINCT contact_a.id as contact_id,
 130 contact_a.sort_name as sort_name,
 131 sum(contrib.total_amount) AS donation_amount,
 132 count(contrib.id) AS donation_count
 133 ";
 134     }
 135     $from = $this->from();
 136
 137     $where = $this->where($includeContactIDs);
 138
 139     $having = $this->having();
 140     if ($having) {
 141       $having = " HAVING $having ";
 142     }
 143
 144     $sql = "
 145 SELECT $select
 146 FROM   $from
 147 WHERE  $where
 148 GROUP BY contact_a.id
 149 $having
 150 ";
 151     //for only contact ids ignore order.
 152     if (!$justIDs) {
 153       // Define ORDER BY for query in $sort, with default value
 154       if (!empty($sort)) {
 155         if (is_string($sort)) {
 156           $sort = CRM_Utils_Type::escape($sort, 'String');
 157           $sql .= " ORDER BY $sort ";
 158         }
 159         else {
 160           $sql .= " ORDER BY " . trim($sort->orderBy());
 161         }
 162       }
 163       else {
 164         $sql .= "ORDER BY donation_amount desc";
 165       }
 166     }
 167
 168     if ($rowcount > 0 && $offset >= 0) {
 169       $offset = CRM_Utils_Type::escape($offset, 'Int');
 170       $rowcount = CRM_Utils_Type::escape($rowcount, 'Int');
 171       $sql .= " LIMIT $offset, $rowcount ";
 172     }
 173     return $sql;
 174   }
 175
 176   /**
 177    * @return string
 178    */
 179   public function from() {
 180     return "
 181 civicrm_contribution AS contrib,
 182 civicrm_contact AS contact_a
 183 ";
 184   }
 185
 186   /**
 187    * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
 188    *
 189    * @param bool $includeContactIDs
 190    *
 191    * @return string
 192    */
 193   public function where($includeContactIDs = FALSE) {
 194     $clauses = array();
 195
 196     $clauses[] = "contrib.contact_id = contact_a.id";
 197     $clauses[] = "contrib.is_test = 0";
 198
 199     $startDate = CRM_Utils_Date::processDate($this->_formValues['start_date']);
 200     if ($startDate) {
 201       $clauses[] = "contrib.receive_date >= $startDate";
 202     }
 203
 204     $endDate = CRM_Utils_Date::processDate($this->_formValues['end_date']);
 205     if ($endDate) {
 206       $clauses[] = "contrib.receive_date <= $endDate";
 207     }
 208
 209     if ($includeContactIDs) {
 210       $contactIDs = array();
 211       foreach ($this->_formValues as $id => $value) {
 212         if ($value &&
 213           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 214         ) {
 215           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 216         }
 217       }
 218
 219       if (!empty($contactIDs)) {
 220         $contactIDs = implode(', ', $contactIDs);
 221         $clauses[] = "contact_a.id IN ( $contactIDs )";
 222       }
 223     }
 224
 225     if (!empty($this->_formValues['financial_type_id'])) {
 226       $financial_type_ids = implode(',', array_keys($this->_formValues['financial_type_id']));
 227       $clauses[] = "contrib.financial_type_id IN ($financial_type_ids)";
 228     }
 229
 230     return implode(' AND ', $clauses);
 231   }
 232
 233   /**
 234    * @param bool $includeContactIDs
 235    *
 236    * @return string
 237    */
 238   public function having($includeContactIDs = FALSE) {
 239     $clauses = array();
 240     $min = CRM_Utils_Array::value('min_amount', $this->_formValues);
 241     if ($min) {
 242       $min = CRM_Utils_Rule::cleanMoney($min);
 243       $clauses[] = "sum(contrib.total_amount) >= $min";
 244     }
 245
 246     $max = CRM_Utils_Array::value('max_amount', $this->_formValues);
 247     if ($max) {
 248       $max = CRM_Utils_Rule::cleanMoney($max);
 249       $clauses[] = "sum(contrib.total_amount) <= $max";
 250     }
 251
 252     return implode(' AND ', $clauses);
 253   }
 254
 255   /*
 256    * Functions below generally don't need to be modified
 257    */
 258
 259   /**
 260    * @inheritDoc
 261    */
 262   public function count() {
 263     $sql = $this->all();
 264
 265     $dao = CRM_Core_DAO::executeQuery($sql,
 266       CRM_Core_DAO::$_nullArray
 267     );
 268     return $dao->N;
 269   }
 270
 271   /**
 272    * @param int $offset
 273    * @param int $rowcount
 274    * @param null $sort
 275    *
 276    * @return string
 277    */
 278   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
 279     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 280   }
 281
 282   /**
 283    * @return array
 284    */
 285   public function &columns() {
 286     return $this->_columns;
 287   }
 288
 289   /**
 290    * @param $title
 291    */
 292   public function setTitle($title) {
 293     if ($title) {
 294       CRM_Utils_System::setTitle($title);
 295     }
 296     else {
 297       CRM_Utils_System::setTitle(ts('Search'));
 298     }
 299   }
 300
 301   /**
 302    * @return null
 303    */
 304   public function summary() {
 305     return NULL;
 306   }
 307 }