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

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