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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_TagContributions extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_formValues;
  38   protected $_aclFrom = NULL;
  39   protected $_aclWhere = NULL;
  40   public $_permissionedComponent;
  41
  42   /**
  43    * @param $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   public function all(
  99     $offset = 0, $rowcount = 0, $sort = NULL,
 100     $includeContactIDs = FALSE, $onlyIDs = FALSE
 101   ) {
 102
 103     // SELECT clause must include contact_id as an alias for civicrm_contact.id
 104     if ($onlyIDs) {
 105       $select = "contact_a.id as contact_id";
 106     }
 107     else {
 108       $select = "
 109 DISTINCT
 110 contact_a.id as contact_id,
 111 contact_a.sort_name as sort_name,
 112 contact_a.first_name as first_name,
 113 contact_a.last_name as last_name,
 114 GROUP_CONCAT(DISTINCT civicrm_tag.name ORDER BY  civicrm_tag.name ASC ) as tag_name,
 115 sum(civicrm_contribution.total_amount) as amount
 116 ";
 117     }
 118     $from = $this->from();
 119
 120     $where = $this->where($includeContactIDs);
 121
 122     $sql = "
 123 SELECT $select
 124 FROM   $from
 125 WHERE  $where
 126 ";
 127
 128     $sql .= " GROUP BY contact_a.id";
 129     // Define ORDER BY for query in $sort, with default value
 130     if (!empty($sort)) {
 131       if (is_string($sort)) {
 132         $sort = CRM_Utils_Type::escape($sort, 'String');
 133         $sql .= " ORDER BY $sort ";
 134       }
 135       else {
 136         $sql .= " ORDER BY " . trim($sort->orderBy());
 137       }
 138     }
 139     else {
 140       $sql .= "";
 141     }
 142     return $sql;
 143   }
 144
 145   /**
 146    * @return string
 147    */
 148   public function from() {
 149     $this->buildACLClause('contact_a');
 150     $from = "
 151       civicrm_contribution,
 152       civicrm_contact contact_a
 153       LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
 154                                         civicrm_entity_tag.entity_id = contact_a.id )
 155       LEFT JOIN civicrm_tag ON civicrm_tag.id = civicrm_entity_tag.tag_id {$this->_aclFrom}
 156      ";
 157     return $from;
 158   }
 159
 160   /*
 161    * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
 162    *
 163    */
 164   /**
 165    * @param bool $includeContactIDs
 166    *
 167    * @return string
 168    */
 169   public function where($includeContactIDs = FALSE) {
 170     $clauses = array();
 171
 172     $clauses[] = "contact_a.contact_type = 'Individual'";
 173     $clauses[] = "civicrm_contribution.contact_id = contact_a.id";
 174
 175     $startDate = CRM_Utils_Date::processDate($this->_formValues['start_date']);
 176     if ($startDate) {
 177       $clauses[] = "civicrm_contribution.receive_date >= $startDate";
 178     }
 179
 180     $endDate = CRM_Utils_Date::processDate($this->_formValues['end_date']);
 181     if ($endDate) {
 182       $clauses[] = "civicrm_contribution.receive_date <= $endDate";
 183     }
 184
 185     $tag = CRM_Utils_Array::value('tag', $this->_formValues);
 186     if ($tag) {
 187       $clauses[] = "civicrm_entity_tag.tag_id = $tag";
 188       $clauses[] = "civicrm_tag.id = civicrm_entity_tag.tag_id";
 189     }
 190     else {
 191       $clauses[] = "civicrm_entity_tag.tag_id IS NOT NULL";
 192     }
 193
 194     if ($includeContactIDs) {
 195       $contactIDs = array();
 196       foreach ($this->_formValues as $id => $value) {
 197         if ($value &&
 198           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 199         ) {
 200           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 201         }
 202       }
 203
 204       if (!empty($contactIDs)) {
 205         $contactIDs = implode(', ', $contactIDs);
 206         $clauses[] = "contact_a.id IN ( $contactIDs )";
 207       }
 208     }
 209     if ($this->_aclWhere) {
 210       $clauses[] = " {$this->_aclWhere} ";
 211     }
 212     return implode(' AND ', $clauses);
 213   }
 214
 215
 216   /*
 217    * Functions below generally don't need to be modified
 218    */
 219
 220   /**
 221    * @inheritDoc
 222    */
 223   public function count() {
 224     $sql = $this->all();
 225
 226     $dao = CRM_Core_DAO::executeQuery($sql,
 227       CRM_Core_DAO::$_nullArray
 228     );
 229     return $dao->N;
 230   }
 231
 232   /**
 233    * @param int $offset
 234    * @param int $rowcount
 235    * @param null $sort
 236    * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned
 237    *
 238    * @return string
 239    */
 240   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) {
 241     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 242   }
 243
 244   /**
 245    * @return array
 246    */
 247   public function &columns() {
 248     return $this->_columns;
 249   }
 250
 251   /**
 252    * @param $title
 253    */
 254   public function setTitle($title) {
 255     if ($title) {
 256       CRM_Utils_System::setTitle($title);
 257     }
 258     else {
 259       CRM_Utils_System::setTitle(ts('Search'));
 260     }
 261   }
 262
 263   /**
 264    * @return null
 265    */
 266   public function summary() {
 267     return NULL;
 268   }
 269
 270   /**
 271    * @param string $tableAlias
 272    */
 273   public function buildACLClause($tableAlias = 'contact') {
 274     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
 275   }
 276
 277 }