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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_ContribSYBNT implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_formValues;
  38   public $_permissionedComponent;
  39
  40   /**
  41    * @param $formValues
  42    */
  43   function __construct(&$formValues) {
  44     $this->_formValues = $formValues;
  45     $this->_permissionedComponent = 'CiviContribute';
  46
  47     $this->_columns = array(
  48       ts('Contact ID') => 'contact_id',
  49       ts('Name') => 'display_name',
  50       ts('Donation Count') => 'donation_count',
  51       ts('Donation Amount') => 'donation_amount',
  52     );
  53
  54     $this->_amounts = array('min_amount_1' => ts('Min Amount One'),
  55       'max_amount_1' => ts('Max Amount One'),
  56       'min_amount_2' => ts('Min Amount Two'),
  57       'max_amount_2' => ts('Max Amount Two'),
  58       'exclude_min_amount' => ts('Exclusion Min Amount'),
  59       'exclude_max_amount' => ts('Exclusion Max Amount'),
  60     );
  61
  62     $this->_dates = array('start_date_1' => ts('Start Date One'),
  63       'end_date_1' => ts('End Date One'),
  64       'start_date_2' => ts('Start Date Two'),
  65       'end_date_2' => ts('End Date Two'),
  66       'exclude_start_date' => ts('Exclusion Start Date'),
  67       'exclude_end_date' => ts('Exclusion End Date'),
  68     );
  69
  70     $this->_checkboxes = array('is_first_amount' => ts('First Donation?'));
  71
  72     foreach ($this->_amounts as $name => $title) {
  73       $this->{$name} = CRM_Utils_Array::value($name, $this->_formValues);
  74     }
  75
  76     foreach ($this->_checkboxes as $name => $title) {
  77       $this->{$name} = CRM_Utils_Array::value($name, $this->_formValues, FALSE);
  78     }
  79
  80     foreach ($this->_dates as $name => $title) {
  81       if (!empty($this->_formValues[$name])) {
  82         $this->{$name} = CRM_Utils_Date::processDate($this->_formValues[$name]);
  83       }
  84     }
  85   }
  86
  87   /**
  88    * @param $form
  89    */
  90   function buildForm(&$form) {
  91
  92     foreach ($this->_amounts as $name => $title) {
  93       $form->add('text',
  94         $name,
  95         $title
  96       );
  97     }
  98
  99     foreach ($this->_dates as $name => $title) {
 100       $form->addDate($name, $title, FALSE, array('formatType' => 'custom'));
 101     }
 102
 103     foreach ($this->_checkboxes as $name => $title) {
 104       $form->add('checkbox',
 105         $name,
 106         $title
 107       );
 108     }
 109
 110     $this->setTitle('Contributions made in Year X and not Year Y');
 111     // @TODO: Decide on better names for "Exclusion"
 112     // @TODO: Add rule to ensure that exclusion dates are not in the inclusion range
 113   }
 114
 115   /**
 116    * @return mixed
 117    */
 118   function count() {
 119     $sql = $this->all();
 120
 121     $dao = CRM_Core_DAO::executeQuery($sql);
 122     return $dao->N;
 123   }
 124
 125   /**
 126    * @param int $offset
 127    * @param int $rowcount
 128    * @param null $sort
 129    *
 130    * @return string
 131    */
 132   function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
 133     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 134   }
 135
 136   /**
 137    * @param int $offset
 138    * @param int $rowcount
 139    * @param null $sort
 140    * @param bool $includeContactIDs
 141    * @param bool $justIDs
 142    *
 143    * @return string
 144    */
 145   function all(
 146     $offset = 0,
 147     $rowcount = 0,
 148     $sort = NULL,
 149     $includeContactIDs = FALSE,
 150     $justIDs = FALSE
 151   ) {
 152
 153     $where = $this->where();
 154     if (!empty($where)) {
 155       $where = " AND $where";
 156     }
 157
 158     $having = $this->having();
 159     if ($having) {
 160       $having = " HAVING $having ";
 161     }
 162
 163     $from = $this->from();
 164
 165     if ($justIDs) {
 166       $select = $this->select();
 167       $select .= ', contact_a.id, display_name';
 168     }
 169     else {
 170       $select = $this->select();
 171       $select = "
 172            DISTINCT contact_a.id as contact_id,
 173            contact_a.display_name as display_name,
 174            $select
 175 ";
 176
 177     }
 178
 179     $sql = "
 180 SELECT     $select
 181 FROM       civicrm_contact AS contact_a
 182 LEFT JOIN  civicrm_contribution contrib_1 ON contrib_1.contact_id = contact_a.id
 183            $from
 184 WHERE      contrib_1.contact_id = contact_a.id
 185 AND        contrib_1.is_test = 0
 186            $where
 187 GROUP BY   contact_a.id
 188            $having
 189 ORDER BY   donation_amount desc
 190 ";
 191
 192
 193     if ($justIDs) {
 194       CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS CustomSearch_SYBNT_temp");
 195       $query = "CREATE TEMPORARY TABLE CustomSearch_SYBNT_temp AS ({$sql})";
 196       $dao = CRM_Core_DAO::executeQuery($query);
 197       $sql = "SELECT contact_a.id as contact_id FROM CustomSearch_SYBNT_temp as contact_a";
 198     }
 199
 200     return $sql;
 201   }
 202
 203   /**
 204    * @return string
 205    */
 206   function select() {
 207     if (!empty($this->start_date_2) || !empty($this->end_date_2)) {
 208       return "
 209 sum(contrib_1.total_amount) + sum(contrib_2.total_amount) AS donation_amount,
 210 count(contrib_1.id) + count(contrib_1.id) AS donation_count
 211 ";
 212     }
 213     else {
 214       return "
 215 sum(contrib_1.total_amount) AS donation_amount,
 216 count(contrib_1.id) AS donation_count
 217 ";
 218     }
 219   }
 220
 221   /**
 222    * @return null|string
 223    */
 224   function from() {
 225     $from = NULL;
 226     if (!empty($this->start_date_2) || !empty($this->end_date_2)) {
 227       $from .= " LEFT JOIN civicrm_contribution contrib_2 ON contrib_2.contact_id = contact_a.id ";
 228     }
 229
 230     if (!empty($this->exclude_start_date) ||
 231       !empty($this->exclude_end_date) ||
 232       !empty($this->is_first_amount)
 233     ) {
 234       $from .= " LEFT JOIN XG_CustomSearch_SYBNT xg ON xg.contact_id = contact_a.id ";
 235     }
 236
 237     return $from;
 238   }
 239
 240   /**
 241    * @param bool $includeContactIDs
 242    *
 243    * @return string
 244    */
 245   function where($includeContactIDs = FALSE) {
 246     $clauses = array();
 247
 248     if (!empty($this->start_date_1)) {
 249       $clauses[] = "contrib_1.receive_date >= {$this->start_date_1}";
 250     }
 251
 252     if (!empty($this->end_date_1)) {
 253       $clauses[] = "contrib_1.receive_date <= {$this->end_date_1}";
 254     }
 255
 256     if (!empty($this->start_date_2) || !empty($this->end_date_2)) {
 257       $clauses[] = "contrib_2.is_test = 0";
 258
 259       if (!empty($this->start_date_2)) {
 260         $clauses[] = "contrib_2.receive_date >= {$this->start_date_2}";
 261       }
 262
 263       if (!empty($this->end_date_2)) {
 264         $clauses[] = "contrib_2.receive_date <= {$this->end_date_2}";
 265       }
 266     }
 267
 268     if (!empty($this->exclude_start_date) ||
 269       !empty($this->exclude_end_date) ||
 270       !empty($this->is_first_amount)
 271     ) {
 272
 273       // first create temp table to store contact ids
 274       $sql = "DROP TEMPORARY TABLE IF EXISTS XG_CustomSearch_SYBNT";
 275       CRM_Core_DAO::executeQuery($sql);
 276
 277       $sql = "CREATE TEMPORARY TABLE XG_CustomSearch_SYBNT ( contact_id int primary key) ENGINE=HEAP";
 278       CRM_Core_DAO::executeQuery($sql);
 279
 280       $excludeClauses = array();
 281       if ($this->exclude_start_date) {
 282         $excludeClauses[] = "c.receive_date >= {$this->exclude_start_date}";
 283       }
 284
 285       if ($this->exclude_end_date) {
 286         $excludeClauses[] = "c.receive_date <= {$this->exclude_end_date}";
 287       }
 288
 289       $excludeClause = NULL;
 290       if ($excludeClauses) {
 291         $excludeClause = ' AND ' . implode(' AND ', $excludeClauses);
 292       }
 293
 294       $having = array();
 295       if ($this->exclude_min_amount) {
 296         $having[] = "sum(c.total_amount) >= {$this->exclude_min_amount}";
 297       }
 298
 299       if ($this->exclude_max_amount) {
 300         $having[] = "sum(c.total_amount) <= {$this->exclude_max_amount}";
 301       }
 302
 303       $havingClause = NULL;
 304       if (!empty($having)) {
 305         $havingClause = "HAVING " . implode(' AND ', $having);
 306       }
 307
 308       if ($excludeClause || $havingClause) {
 309         // Run subquery
 310         $query = "
 311 REPLACE   INTO XG_CustomSearch_SYBNT
 312 SELECT   DISTINCT contact_id AS contact_id
 313 FROM     civicrm_contribution c
 314 WHERE    c.is_test = 0
 315          $excludeClause
 316 GROUP BY c.contact_id
 317          $havingClause
 318 ";
 319
 320         $dao = CRM_Core_DAO::executeQuery($query);
 321       }
 322
 323       // now ensure we dont consider donors that are not first time
 324       if ($this->is_first_amount) {
 325         $query = "
 326 REPLACE  INTO XG_CustomSearch_SYBNT
 327 SELECT   DISTINCT contact_id AS contact_id
 328 FROM     civicrm_contribution c
 329 WHERE    c.is_test = 0
 330 AND      c.receive_date < {$this->start_date_1}
 331 ";
 332         $dao = CRM_Core_DAO::executeQuery($query);
 333       }
 334
 335       $clauses[] = " xg.contact_id IS NULL ";
 336     }
 337
 338     return implode(' AND ', $clauses);
 339   }
 340
 341   /**
 342    * @param bool $includeContactIDs
 343    *
 344    * @return string
 345    */
 346   function having($includeContactIDs = FALSE) {
 347     $clauses = array();
 348     $min = CRM_Utils_Array::value('min_amount', $this->_formValues);
 349     if ($min) {
 350       $clauses[] = "sum(contrib_1.total_amount) >= $min";
 351     }
 352
 353     $max = CRM_Utils_Array::value('max_amount', $this->_formValues);
 354     if ($max) {
 355       $clauses[] = "sum(contrib_1.total_amount) <= $max";
 356     }
 357
 358     return implode(' AND ', $clauses);
 359   }
 360
 361   /**
 362    * @return array
 363    */
 364   function &columns() {
 365     return $this->_columns;
 366   }
 367
 368   /**
 369    * @return string
 370    */
 371   function templateFile() {
 372     return 'CRM/Contact/Form/Search/Custom/ContribSYBNT.tpl';
 373   }
 374
 375   /**
 376    * @return null
 377    */
 378   function summary() {
 379     return NULL;
 380   }
 381
 382   /**
 383    * @param $title
 384    */
 385   function setTitle($title) {
 386     if ($title) {
 387       CRM_Utils_System::setTitle($title);
 388     }
 389     else {
 390       CRM_Utils_System::setTitle(ts('Search'));
 391     }
 392   }
 393 }
 394