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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.3                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_TagContributions implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_formValues;
  38
  39   function __construct(&$formValues) {
  40     $this->_formValues = $formValues;
  41
  42     /**
  43      * Define the columns for search result rows
  44      */
  45     $this->_columns = array(
  46       ts('Contact Id') => 'contact_id',
  47       ts('Full Name') => 'sort_name',
  48       ts('First Name') => 'first_name',
  49       ts('Last Name') => 'last_name',
  50       ts('Tag') => 'tag_name',
  51       ts('Totals') => 'amount',
  52     );
  53   }
  54
  55   function buildForm(&$form) {
  56
  57     /**
  58      * You can define a custom title for the search form
  59      */
  60     $this->setTitle('Find Contribution Amounts by Tag');
  61
  62     /**
  63      * Define the search form fields here
  64      */
  65
  66
  67     $form->addDate('start_date', ts('Contribution Date From'), FALSE, array('formatType' => 'custom'));
  68     $form->addDate('end_date', ts('...through'), FALSE, array('formatType' => 'custom'));
  69     $tag = array('' => ts('- any tag -')) + CRM_Core_PseudoConstant::tag();
  70     $form->addElement('select', 'tag', ts('Tagged'), $tag);
  71
  72     /**
  73      * If you are using the sample template, this array tells the template fields to render
  74      * for the search form.
  75      */
  76     $form->assign('elements', array('start_date', 'end_date', 'tag'));
  77   }
  78
  79   /**
  80    * Define the smarty template used to layout the search form and results listings.
  81    */
  82   function templateFile() {
  83     return 'CRM/Contact/Form/Search/Custom.tpl';
  84   }
  85
  86   /**
  87    * Construct the search query
  88    */
  89   function all($offset = 0, $rowcount = 0, $sort = NULL,
  90     $includeContactIDs = FALSE, $onlyIDs = FALSE
  91   ) {
  92
  93     // SELECT clause must include contact_id as an alias for civicrm_contact.id
  94     if ($onlyIDs) {
  95       $select = "contact_a.id as contact_id";
  96     }
  97     else {
  98       $select = "
  99 DISTINCT
 100 contact_a.id as contact_id,
 101 contact_a.sort_name as sort_name,
 102 contact_a.first_name as first_name,
 103 contact_a.last_name as last_name,
 104 GROUP_CONCAT(DISTINCT civicrm_tag.name ORDER BY  civicrm_tag.name ASC ) as tag_name,
 105 sum(civicrm_contribution.total_amount) as amount
 106 ";
 107     }
 108     $from = $this->from();
 109
 110     $where = $this->where($includeContactIDs);
 111
 112     $sql = "
 113 SELECT $select
 114 FROM   $from
 115 WHERE  $where
 116 ";
 117     //for only contact ids ignore order and group by.
 118     if (!$onlyIDs) {
 119       $sql .= " GROUP BY contact_a.id";
 120       // Define ORDER BY for query in $sort, with default value
 121       if (!empty($sort)) {
 122         if (is_string($sort)) {
 123           $sort = CRM_Utils_Type::escape($sort, 'String');
 124           $sql .= " ORDER BY $sort ";
 125         }
 126         else {
 127           $sql .= " ORDER BY " . trim($sort->orderBy());
 128         }
 129       }
 130       else {
 131         $sql .= "";
 132       }
 133     }
 134     return $sql;
 135   }
 136
 137   function from() {
 138     return "
 139       civicrm_contribution,
 140       civicrm_contact contact_a
 141       LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
 142                                         civicrm_entity_tag.entity_id = contact_a.id )
 143       LEFT JOIN civicrm_tag ON civicrm_tag.id = civicrm_entity_tag.tag_id
 144 ";
 145   }
 146
 147   /*
 148   * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
 149   *
 150   */
 151   function where($includeContactIDs = FALSE) {
 152     $clauses = array();
 153
 154     $clauses[] = "contact_a.contact_type = 'Individual'";
 155     $clauses[] = "civicrm_contribution.contact_id = contact_a.id";
 156
 157     $startDate = CRM_Utils_Date::processDate($this->_formValues['start_date']);
 158     if ($startDate) {
 159       $clauses[] = "civicrm_contribution.receive_date >= $startDate";
 160     }
 161
 162     $endDate = CRM_Utils_Date::processDate($this->_formValues['end_date']);
 163     if ($endDate) {
 164       $clauses[] = "civicrm_contribution.receive_date <= $endDate";
 165     }
 166
 167     $tag = CRM_Utils_Array::value('tag', $this->_formValues);
 168     if ($tag) {
 169       $clauses[] = "civicrm_entity_tag.tag_id = $tag";
 170       $clauses[] = "civicrm_tag.id = civicrm_entity_tag.tag_id";
 171     }
 172     else {
 173       $clauses[] = "civicrm_entity_tag.tag_id IS NOT NULL";
 174     }
 175
 176     if ($includeContactIDs) {
 177       $contactIDs = array();
 178       foreach ($this->_formValues as $id => $value) {
 179         if ($value &&
 180           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 181         ) {
 182           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 183         }
 184       }
 185
 186       if (!empty($contactIDs)) {
 187         $contactIDs = implode(', ', $contactIDs);
 188         $clauses[] = "contact_a.id IN ( $contactIDs )";
 189       }
 190     }
 191     return implode(' AND ', $clauses);
 192   }
 193
 194
 195   /*
 196      * Functions below generally don't need to be modified
 197      */
 198   function count() {
 199     $sql = $this->all();
 200
 201     $dao = CRM_Core_DAO::executeQuery($sql,
 202       CRM_Core_DAO::$_nullArray
 203     );
 204     return $dao->N;
 205   }
 206
 207   function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
 208     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 209   }
 210
 211   function &columns() {
 212     return $this->_columns;
 213   }
 214
 215   function setTitle($title) {
 216     if ($title) {
 217       CRM_Utils_System::setTitle($title);
 218     }
 219     else {
 220       CRM_Utils_System::setTitle(ts('Search'));
 221     }
 222   }
 223
 224   function summary() {
 225     return NULL;
 226   }
 227 }
 228