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      */
  48
  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    * @param CRM_Core_Form $form
  62    */
  63   public 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   public function templateFile() {
 104     return 'CRM/Contact/Form/Search/Custom/ContributionAggregate.tpl';
 105   }
 106
 107   /**
 108    * Construct the search query
 109    */
 110   function all(
 111     $offset = 0, $rowcount = 0, $sort = NULL,
 112     $includeContactIDs = FALSE, $justIDs = FALSE
 113   ) {
 114
 115     // SELECT clause must include contact_id as an alias for civicrm_contact.id
 116     if ($justIDs) {
 117       $select = "contact_a.id as contact_id";
 118     }
 119     else {
 120       $select = "
 121 DISTINCT contact_a.id as contact_id,
 122 contact_a.sort_name as sort_name,
 123 sum(contrib.total_amount) AS donation_amount,
 124 count(contrib.id) AS donation_count
 125 ";
 126     }
 127     $from = $this->from();
 128
 129     $where = $this->where($includeContactIDs);
 130
 131     $having = $this->having();
 132     if ($having) {
 133       $having = " HAVING $having ";
 134     }
 135
 136     $sql = "
 137 SELECT $select
 138 FROM   $from
 139 WHERE  $where
 140 GROUP BY contact_a.id
 141 $having
 142 ";
 143     //for only contact ids ignore order.
 144     if (!$justIDs) {
 145       // Define ORDER BY for query in $sort, with default value
 146       if (!empty($sort)) {
 147         if (is_string($sort)) {
 148           $sort = CRM_Utils_Type::escape($sort, 'String');
 149           $sql .= " ORDER BY $sort ";
 150         }
 151         else {
 152           $sql .= " ORDER BY " . trim($sort->orderBy());
 153         }
 154       }
 155       else {
 156         $sql .= "ORDER BY donation_amount desc";
 157       }
 158     }
 159
 160     if ($rowcount > 0 && $offset >= 0) {
 161       $offset = CRM_Utils_Type::escape($offset, 'Int');
 162       $rowcount = CRM_Utils_Type::escape($rowcount, 'Int');
 163       $sql .= " LIMIT $offset, $rowcount ";
 164     }
 165     return $sql;
 166   }
 167
 168   /**
 169    * @return string
 170    */
 171   public function from() {
 172     return "
 173 civicrm_contribution AS contrib,
 174 civicrm_contact AS contact_a
 175 ";
 176   }
 177
 178   /*
 179       * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
 180       *
 181       */
 182   /**
 183    * @param bool $includeContactIDs
 184    *
 185    * @return string
 186    */
 187   public function where($includeContactIDs = FALSE) {
 188     $clauses = array();
 189
 190     $clauses[] = "contrib.contact_id = contact_a.id";
 191     $clauses[] = "contrib.is_test = 0";
 192
 193     $startDate = CRM_Utils_Date::processDate($this->_formValues['start_date']);
 194     if ($startDate) {
 195       $clauses[] = "contrib.receive_date >= $startDate";
 196     }
 197
 198     $endDate = CRM_Utils_Date::processDate($this->_formValues['end_date']);
 199     if ($endDate) {
 200       $clauses[] = "contrib.receive_date <= $endDate";
 201     }
 202
 203     if ($includeContactIDs) {
 204       $contactIDs = array();
 205       foreach ($this->_formValues as $id => $value) {
 206         if ($value &&
 207           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 208         ) {
 209           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 210         }
 211       }
 212
 213       if (!empty($contactIDs)) {
 214         $contactIDs = implode(', ', $contactIDs);
 215         $clauses[] = "contact_a.id IN ( $contactIDs )";
 216       }
 217     }
 218
 219     if (!empty($this->_formValues['financial_type_id'])) {
 220       $financial_type_ids = implode(',', array_keys($this->_formValues['financial_type_id']));
 221       $clauses[] = "contrib.financial_type_id IN ($financial_type_ids)";
 222     }
 223
 224     return implode(' AND ', $clauses);
 225   }
 226
 227   /**
 228    * @param bool $includeContactIDs
 229    *
 230    * @return string
 231    */
 232   public function having($includeContactIDs = FALSE) {
 233     $clauses = array();
 234     $min = CRM_Utils_Array::value('min_amount', $this->_formValues);
 235     if ($min) {
 236       $min = CRM_Utils_Rule::cleanMoney($min);
 237       $clauses[] = "sum(contrib.total_amount) >= $min";
 238     }
 239
 240     $max = CRM_Utils_Array::value('max_amount', $this->_formValues);
 241     if ($max) {
 242       $max = CRM_Utils_Rule::cleanMoney($max);
 243       $clauses[] = "sum(contrib.total_amount) <= $max";
 244     }
 245
 246     return implode(' AND ', $clauses);
 247   }
 248
 249   /*
 250    * Functions below generally don't need to be modified
 251    */
 252   public function count() {
 253     $sql = $this->all();
 254
 255     $dao = CRM_Core_DAO::executeQuery($sql,
 256       CRM_Core_DAO::$_nullArray
 257     );
 258     return $dao->N;
 259   }
 260
 261   /**
 262    * @param int $offset
 263    * @param int $rowcount
 264    * @param null $sort
 265    *
 266    * @return string
 267    */
 268   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
 269     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 270   }
 271
 272   /**
 273    * @return array
 274    */
 275   public function &columns() {
 276     return $this->_columns;
 277   }
 278
 279   /**
 280    * @param $title
 281    */
 282   public function setTitle($title) {
 283     if ($title) {
 284       CRM_Utils_System::setTitle($title);
 285     }
 286     else {
 287       CRM_Utils_System::setTitle(ts('Search'));
 288     }
 289   }
 290
 291   /**
 292    * @return null
 293    */
 294   public function summary() {
 295     return NULL;
 296   }
 297 }