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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  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 = $formValues;
  47     $this->_permissionedComponent = 'CiviContribute';
  48
  49     /**
  50      * Define the columns for search result rows
  51      */
  52     $this->_columns = array(
  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->addDate('start_date', ts('Contribution Date From'), FALSE, array('formatType' => 'custom'));
  77     $form->addDate('end_date', ts('...through'), FALSE, array('formatType' => 'custom'));
  78     $tag = array('' => ts('- any tag -')) + CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('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', array('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    * @param bool $includeContactIDs
 174    *
 175    * @return string
 176    */
 177   public function where($includeContactIDs = FALSE) {
 178     $clauses = array();
 179
 180     $clauses[] = "contact_a.contact_type = 'Individual'";
 181     $clauses[] = "civicrm_contribution.contact_id = contact_a.id";
 182
 183     $startDate = CRM_Utils_Date::processDate($this->_formValues['start_date']);
 184     if ($startDate) {
 185       $clauses[] = "civicrm_contribution.receive_date >= $startDate";
 186     }
 187
 188     $endDate = CRM_Utils_Date::processDate($this->_formValues['end_date']);
 189     if ($endDate) {
 190       $clauses[] = "civicrm_contribution.receive_date <= $endDate";
 191     }
 192
 193     $tag = CRM_Utils_Array::value('tag', $this->_formValues);
 194     if ($tag) {
 195       $clauses[] = "civicrm_entity_tag.tag_id = $tag";
 196       $clauses[] = "civicrm_tag.id = civicrm_entity_tag.tag_id";
 197     }
 198     else {
 199       $clauses[] = "civicrm_entity_tag.tag_id IS NOT NULL";
 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     if ($this->_aclWhere) {
 218       $clauses[] = " {$this->_aclWhere} ";
 219     }
 220     return implode(' AND ', $clauses);
 221   }
 222
 223
 224   /*
 225    * Functions below generally don't need to be modified
 226    */
 227
 228   /**
 229    * @inheritDoc
 230    */
 231   public function count() {
 232     $sql = $this->all();
 233
 234     $dao = CRM_Core_DAO::executeQuery($sql,
 235       CRM_Core_DAO::$_nullArray
 236     );
 237     return $dao->N;
 238   }
 239
 240   /**
 241    * @param int $offset
 242    * @param int $rowcount
 243    * @param null $sort
 244    * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned
 245    *
 246    * @return string
 247    */
 248   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) {
 249     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 250   }
 251
 252   /**
 253    * @return array
 254    */
 255   public function &columns() {
 256     return $this->_columns;
 257   }
 258
 259   /**
 260    * @param $title
 261    */
 262   public function setTitle($title) {
 263     if ($title) {
 264       CRM_Utils_System::setTitle($title);
 265     }
 266     else {
 267       CRM_Utils_System::setTitle(ts('Search'));
 268     }
 269   }
 270
 271   /**
 272    * @return null
 273    */
 274   public function summary() {
 275     return NULL;
 276   }
 277
 278   /**
 279    * @param string $tableAlias
 280    */
 281   public function buildACLClause($tableAlias = 'contact') {
 282     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
 283   }
 284
 285 }