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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   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-2019
  32  */
  33 class CRM_Contact_Form_Search_Custom_TagContributions 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 = self::formatSavedSearchFields($formValues);
  47     $this->_permissionedComponent = 'CiviContribute';
  48
  49     /**
  50      * Define the columns for search result rows
  51      */
  52     $this->_columns = [
  53       ts('Contact ID') => 'contact_id',
  54       ts('Full Name') => 'sort_name',
  55       ts('First Name') => 'first_name',
  56       ts('Last Name') => 'last_name',
  57       ts('Tag') => 'tag_name',
  58       ts('Totals') => 'amount',
  59     ];
  60   }
  61
  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 Contribution Amounts by Tag');
  71
  72     /**
  73      * Define the search form fields here
  74      */
  75
  76     $form->add('datepicker', 'start_date', ts('Contribution Date From'), [], FALSE, ['time' => FALSE]);
  77     $form->add('datepicker', 'end_date', ts('...through'), [], FALSE, ['time' => FALSE]);
  78     $tag = ['' => ts('- any tag -')] + CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', ['onlyActive' => FALSE]);
  79     $form->addElement('select', 'tag', ts('Tagged'), $tag);
  80
  81     /**
  82      * If you are using the sample template, this array tells the template fields to render
  83      * for the search form.
  84      */
  85     $form->assign('elements', ['start_date', 'end_date', 'tag']);
  86   }
  87
  88   /**
  89    * Define the smarty template used to layout the search form and results listings.
  90    */
  91   public function templateFile() {
  92     return 'CRM/Contact/Form/Search/Custom.tpl';
  93   }
  94
  95   /**
  96    * Construct the search query.
  97    *
  98    * @param int $offset
  99    * @param int $rowcount
 100    * @param string $sort
 101    * @param bool $includeContactIDs
 102    * @param bool $onlyIDs
 103    *
 104    * @return string
 105    */
 106   public function all(
 107     $offset = 0, $rowcount = 0, $sort = NULL,
 108     $includeContactIDs = FALSE, $onlyIDs = FALSE
 109   ) {
 110
 111     // SELECT clause must include contact_id as an alias for civicrm_contact.id
 112     if ($onlyIDs) {
 113       $select = "contact_a.id as contact_id";
 114     }
 115     else {
 116       $select = "
 117 DISTINCT
 118 contact_a.id as contact_id,
 119 contact_a.sort_name as sort_name,
 120 contact_a.first_name as first_name,
 121 contact_a.last_name as last_name,
 122 GROUP_CONCAT(DISTINCT civicrm_tag.name ORDER BY  civicrm_tag.name ASC ) as tag_name,
 123 sum(civicrm_contribution.total_amount) as amount
 124 ";
 125     }
 126     $from = $this->from();
 127
 128     $where = $this->where($includeContactIDs);
 129
 130     $sql = "
 131 SELECT $select
 132 FROM   $from
 133 WHERE  $where
 134 ";
 135
 136     $sql .= " GROUP BY contact_a.id";
 137     // Define ORDER BY for query in $sort, with default value
 138     if (!empty($sort)) {
 139       if (is_string($sort)) {
 140         $sort = CRM_Utils_Type::escape($sort, 'String');
 141         $sql .= " ORDER BY $sort ";
 142       }
 143       else {
 144         $sql .= " ORDER BY " . trim($sort->orderBy());
 145       }
 146     }
 147     else {
 148       $sql .= "";
 149     }
 150     return $sql;
 151   }
 152
 153   /**
 154    * @return string
 155    */
 156   public function from() {
 157     $this->buildACLClause('contact_a');
 158     $from = "
 159       civicrm_contribution,
 160       civicrm_contact contact_a
 161       LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
 162                                         civicrm_entity_tag.entity_id = contact_a.id )
 163       LEFT JOIN civicrm_tag ON civicrm_tag.id = civicrm_entity_tag.tag_id {$this->_aclFrom}
 164      ";
 165     return $from;
 166   }
 167
 168   /*
 169    * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
 170    *
 171    */
 172
 173   /**
 174    * @param bool $includeContactIDs
 175    *
 176    * @return string
 177    */
 178   public function where($includeContactIDs = FALSE) {
 179     $clauses = [];
 180
 181     $clauses[] = "contact_a.contact_type = 'Individual'";
 182     $clauses[] = "civicrm_contribution.contact_id = contact_a.id";
 183
 184     if ($this->_formValues['start_date']) {
 185       $clauses[] = "civicrm_contribution.receive_date >= '{$this->_formValues['start_date']} 00:00:00'";
 186     }
 187
 188     if ($this->_formValues['end_date']) {
 189       $clauses[] = "civicrm_contribution.receive_date <= '{$this->_formValues['end_date']} 23:59:59'";
 190     }
 191
 192     $tag = CRM_Utils_Array::value('tag', $this->_formValues);
 193     if ($tag) {
 194       $clauses[] = "civicrm_entity_tag.tag_id = $tag";
 195       $clauses[] = "civicrm_tag.id = civicrm_entity_tag.tag_id";
 196     }
 197     else {
 198       $clauses[] = "civicrm_entity_tag.tag_id IS NOT NULL";
 199     }
 200
 201     if ($includeContactIDs) {
 202       $contactIDs = [];
 203       foreach ($this->_formValues as $id => $value) {
 204         if ($value &&
 205           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 206         ) {
 207           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 208         }
 209       }
 210
 211       if (!empty($contactIDs)) {
 212         $contactIDs = implode(', ', $contactIDs);
 213         $clauses[] = "contact_a.id IN ( $contactIDs )";
 214       }
 215     }
 216     if ($this->_aclWhere) {
 217       $clauses[] = " {$this->_aclWhere} ";
 218     }
 219     return implode(' AND ', $clauses);
 220   }
 221
 222   /*
 223    * Functions below generally don't need to be modified
 224    */
 225
 226   /**
 227    * @inheritDoc
 228    */
 229   public function count() {
 230     $sql = $this->all();
 231
 232     $dao = CRM_Core_DAO::executeQuery($sql,
 233       CRM_Core_DAO::$_nullArray
 234     );
 235     return $dao->N;
 236   }
 237
 238   /**
 239    * @param int $offset
 240    * @param int $rowcount
 241    * @param null $sort
 242    * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned
 243    *
 244    * @return string
 245    */
 246   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) {
 247     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 248   }
 249
 250   /**
 251    * @return array
 252    */
 253   public function &columns() {
 254     return $this->_columns;
 255   }
 256
 257   /**
 258    * @param $title
 259    */
 260   public function setTitle($title) {
 261     if ($title) {
 262       CRM_Utils_System::setTitle($title);
 263     }
 264     else {
 265       CRM_Utils_System::setTitle(ts('Search'));
 266     }
 267   }
 268
 269   /**
 270    * @return null
 271    */
 272   public function summary() {
 273     return NULL;
 274   }
 275
 276   /**
 277    * @param string $tableAlias
 278    */
 279   public function buildACLClause($tableAlias = 'contact') {
 280     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
 281   }
 282
 283   /**
 284    * Format saved search fields for this custom group.
 285    *
 286    * Note this is a function to facilitate the transition to jcalendar for
 287    * saved search groups. In time it can be stripped out again.
 288    *
 289    * @param array $formValues
 290    *
 291    * @return array
 292    */
 293   public static function formatSavedSearchFields($formValues) {
 294     $dateFields = [
 295       'start_date',
 296       'end_date',
 297     ];
 298     foreach ($formValues as $element => $value) {
 299       if (in_array($element, $dateFields) && !empty($value)) {
 300         $formValues[$element] = date('Y-m-d', strtotime($value));
 301       }
 302     }
 303     return $formValues;
 304   }
 305
 306 }