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

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