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

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