CRM/Contact/Form/Search/Custom/ContribSYBNT.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  */
  33 class CRM_Contact_Form_Search_Custom_ContribSYBNT 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 $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     $select = $this->select();
 171     if ($justIDs) {
 172       $select .= ', contact_a.id, display_name';
 173     }
 174     else {
 175       $select = "
 176            DISTINCT contact_a.id as contact_id,
 177            contact_a.display_name as display_name,
 178            $select ";
 179     }
 180     $this->buildACLClause('contact_a');
 181     $sql = "
 182 SELECT     $select
 183 FROM       civicrm_contact AS contact_a {$this->_aclFrom}
 184 LEFT JOIN  civicrm_contribution contrib_1 ON contrib_1.contact_id = contact_a.id
 185            $from
 186 WHERE      contrib_1.contact_id = contact_a.id
 187 AND        contrib_1.is_test = 0
 188            $where
 189 GROUP BY   contact_a.id
 190            $having
 191 ORDER BY   donation_amount desc
 192 ";
 193
 194     if ($justIDs) {
 195       CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS CustomSearch_SYBNT_temp");
 196       $query = "CREATE TEMPORARY TABLE CustomSearch_SYBNT_temp AS ({$sql})";
 197       $dao = CRM_Core_DAO::executeQuery($query);
 198       $sql = "SELECT contact_a.id as contact_id FROM CustomSearch_SYBNT_temp as contact_a";
 199     }
 200     return $sql;
 201   }
 202
 203   /**
 204    * @return string
 205    */
 206   public 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   public 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   public 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     if ($this->_aclWhere) {
 338       $clauses[] .= " {$this->_aclWhere} ";
 339     }
 340     return implode(' AND ', $clauses);
 341   }
 342
 343   /**
 344    * @param bool $includeContactIDs
 345    *
 346    * @return string
 347    */
 348   public function having($includeContactIDs = FALSE) {
 349     $clauses = array();
 350     $min = CRM_Utils_Array::value('min_amount', $this->_formValues);
 351     if ($min) {
 352       $clauses[] = "sum(contrib_1.total_amount) >= $min";
 353     }
 354
 355     $max = CRM_Utils_Array::value('max_amount', $this->_formValues);
 356     if ($max) {
 357       $clauses[] = "sum(contrib_1.total_amount) <= $max";
 358     }
 359
 360     return implode(' AND ', $clauses);
 361   }
 362
 363   /**
 364    * @return array
 365    */
 366   public function &columns() {
 367     return $this->_columns;
 368   }
 369
 370   /**
 371    * @return string
 372    */
 373   public function templateFile() {
 374     return 'CRM/Contact/Form/Search/Custom/ContribSYBNT.tpl';
 375   }
 376
 377   /**
 378    * @return null
 379    */
 380   public function summary() {
 381     return NULL;
 382   }
 383
 384   /**
 385    * @param $title
 386    */
 387   public function setTitle($title) {
 388     if ($title) {
 389       CRM_Utils_System::setTitle($title);
 390     }
 391     else {
 392       CRM_Utils_System::setTitle(ts('Search'));
 393     }
 394   }
 395
 396   /**
 397    * @param string $tableAlias
 398    */
 399   public function buildACLClause($tableAlias = 'contact') {
 400     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
 401   }
 402
 403 }