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

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