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

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