CRM/Report/Form/Contribute/PCP.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2017                                |
   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-2017
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Contribute_PCP extends CRM_Report_Form {
  36
  37   /**
  38    * Class constructor.
  39    */
  40   public function __construct() {
  41     $this->_columns = array(
  42       'civicrm_contact' => array(
  43         'dao' => 'CRM_Contact_DAO_Contact',
  44         'fields' => array(
  45           'sort_name' => array(
  46             'title' => ts('Supporter'),
  47             'required' => TRUE,
  48             'default' => TRUE,
  49           ),
  50           'id' => array(
  51             'required' => TRUE,
  52             'no_display' => TRUE,
  53           ),
  54           'contact_type' => array(
  55             'title' => ts('Supporter Contact Type'),
  56           ),
  57           'contact_sub_type' => array(
  58             'title' => ts('Supporter Contact Subtype'),
  59           ),
  60         ),
  61         'filters' => array(
  62           'sort_name' => array(
  63             'title' => ts('Supporter Name'),
  64             'type' => CRM_Utils_Type::T_STRING,
  65             'operator' => 'like',
  66           ),
  67           'id' => array(
  68             'title' => ts('Contact ID'),
  69             'no_display' => TRUE,
  70           ),
  71         ),
  72         'grouping' => 'pcp-fields',
  73       ),
  74       'civicrm_contribution_page' => array(
  75         'dao' => 'CRM_Contribute_DAO_ContributionPage',
  76         'fields' => array(
  77           'page_title' => array(
  78             'title' => ts('Contribution Page Title'),
  79             'name' => 'title',
  80             'default' => TRUE,
  81           ),
  82         ),
  83         'filters' => array(
  84           'page_title' => array(
  85             'title' => ts('Contribution Page Title'),
  86             'name' => 'title',
  87             'type' => CRM_Utils_Type::T_STRING,
  88           ),
  89         ),
  90         'grouping' => 'pcp-fields',
  91       ),
  92       'civicrm_pcp' => array(
  93         'dao' => 'CRM_PCP_DAO_PCP',
  94         'fields' => array(
  95           'title' => array(
  96             'title' => ts('Personal Campaign Title'),
  97             'default' => TRUE,
  98           ),
  99           'goal_amount' => array(
 100             'title' => ts('Goal Amount'),
 101             'type' => CRM_Utils_Type::T_MONEY,
 102             'default' => TRUE,
 103           ),
 104         ),
 105         'filters' => array(
 106           'title' => array(
 107             'title' => ts('Personal Campaign Title'),
 108             'type' => CRM_Utils_Type::T_STRING,
 109           ),
 110         ),
 111         'grouping' => 'pcp-fields',
 112       ),
 113       'civicrm_contribution_soft' => array(
 114         'dao' => 'CRM_Contribute_DAO_ContributionSoft',
 115         'fields' => array(
 116           'amount_1' => array(
 117             'title' => ts('Committed Amount'),
 118             'name' => 'amount',
 119             'type' => CRM_Utils_Type::T_MONEY,
 120             'default' => TRUE,
 121             'statistics' => array(
 122               'sum' => ts('Committed Amount'),
 123             ),
 124           ),
 125           'amount_2' => array(
 126             'title' => ts('Amount Received'),
 127             'name' => 'amount',
 128             'type' => CRM_Utils_Type::T_MONEY,
 129             'default' => TRUE,
 130             // nice trick with dbAlias
 131             'dbAlias' => 'SUM(IF( contribution_civireport.contribution_status_id > 1, 0, contribution_soft_civireport.amount))',
 132           ),
 133           'soft_id' => array(
 134             'title' => ts('Number of Donors'),
 135             'name' => 'id',
 136             'default' => TRUE,
 137             'statistics' => array(
 138               'count' => ts('Number of Donors'),
 139             ),
 140           ),
 141         ),
 142         'filters' => array(
 143           'amount_2' => array(
 144             'title' => ts('Amount Received'),
 145             'type' => CRM_Utils_Type::T_MONEY,
 146             'dbAlias' => 'SUM(IF( contribution_civireport.contribution_status_id > 1, 0, contribution_soft_civireport.amount))',
 147           ),
 148         ),
 149         'grouping' => 'pcp-fields',
 150       ),
 151       'civicrm_contribution' => array(
 152         'dao' => 'CRM_Contribute_DAO_Contribution',
 153         'fields' => array(
 154           'contribution_id' => array(
 155             'name' => 'id',
 156             'no_display' => TRUE,
 157             'required' => TRUE,
 158           ),
 159           'receive_date' => array(
 160             'title' => ts('Most Recent Contribution'),
 161             'default' => TRUE,
 162             'statistics' => array(
 163               'max' => ts('Most Recent Contribution'),
 164             ),
 165           ),
 166         ),
 167         'grouping' => 'pcp-fields',
 168       ),
 169       'civicrm_financial_trxn' => array(
 170         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 171         'fields' => array(
 172           'card_type' => array(
 173             'title' => ts('Credit Card Type'),
 174             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type SEPARATOR ",")',
 175           ),
 176         ),
 177         'filters' => array(
 178           'card_type' => array(
 179             'title' => ts('Credit Card Type'),
 180             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 181             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type'),
 182             'default' => NULL,
 183             'type' => CRM_Utils_Type::T_STRING,
 184           ),
 185         ),
 186       ),
 187     );
 188
 189     parent::__construct();
 190   }
 191
 192   public function from() {
 193     $this->_from = "
 194 FROM civicrm_pcp {$this->_aliases['civicrm_pcp']}
 195
 196 LEFT JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
 197           ON {$this->_aliases['civicrm_pcp']}.id =
 198              {$this->_aliases['civicrm_contribution_soft']}.pcp_id
 199
 200 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 201           ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
 202              {$this->_aliases['civicrm_contribution']}.id
 203
 204 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 205           ON {$this->_aliases['civicrm_pcp']}.contact_id =
 206              {$this->_aliases['civicrm_contact']}.id
 207
 208 LEFT JOIN civicrm_contribution_page {$this->_aliases['civicrm_contribution_page']}
 209           ON {$this->_aliases['civicrm_pcp']}.page_id =
 210              {$this->_aliases['civicrm_contribution_page']}.id";
 211
 212     // for credit card type
 213     $this->addFinancialTrxnFromClause();
 214   }
 215
 216   public function groupBy() {
 217     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_pcp']}.id");
 218   }
 219
 220   public function orderBy() {
 221     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name ";
 222   }
 223
 224   public function where() {
 225     $whereClauses = $havingClauses = array();
 226
 227     foreach ($this->_columns as $tableName => $table) {
 228       if (array_key_exists('filters', $table)) {
 229         foreach ($table['filters'] as $fieldName => $field) {
 230           $clause = NULL;
 231
 232           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 233             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 234             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 235             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 236             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 237           }
 238           else {
 239             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 240
 241             if ($op) {
 242               $clause = $this->whereClause($field,
 243                 $op,
 244                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 245                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 246                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 247               );
 248             }
 249           }
 250
 251           if (!empty($clause)) {
 252             if ($tableName == 'civicrm_contribution_soft' &&
 253               $fieldName == 'amount_2'
 254             ) {
 255               $havingClauses[] = $clause;
 256             }
 257             else {
 258               $whereClauses[] = $clause;
 259             }
 260           }
 261         }
 262       }
 263     }
 264     if (empty($whereClauses)) {
 265       $this->_where = "WHERE ( 1 ) ";
 266     }
 267     else {
 268       $this->_where = "WHERE " . implode(' AND ', $whereClauses);
 269     }
 270     if ($this->_aclWhere) {
 271       $this->_where .= " AND {$this->_aclWhere} ";
 272     }
 273     $this->_having = "";
 274     if (!empty($havingClauses)) {
 275       // use this clause to construct group by clause.
 276       $this->_having = "HAVING " . implode(' AND ', $havingClauses);
 277     }
 278   }
 279
 280   /**
 281    * @param $rows
 282    *
 283    * @return array
 284    */
 285   public function statistics(&$rows) {
 286     $statistics = parent::statistics($rows);
 287
 288     // Calculate totals from the civicrm_contribution_soft table.
 289     $select = "SELECT SUM({$this->_aliases['civicrm_contribution_soft']}.amount) "
 290       . "as committed_total, COUNT({$this->_aliases['civicrm_contribution_soft']}.id) "
 291       . "as donors_total, SUM(IF( contribution_civireport.contribution_status_id > 1, 0, "
 292       . "contribution_soft_civireport.amount)) AS received_total ";
 293     $sql = "{$select} {$this->_from} {$this->_where}";
 294     $dao = CRM_Core_DAO::executeQuery($sql);
 295     $dao->fetch();
 296     $committed_total = $dao->committed_total;
 297     $received_total = $dao->received_total;
 298     $donors_total = $dao->donors_total;
 299
 300     // Calculate goal total goal from the PCP table (we only want one result per
 301     // PCP page - the query above produces one result per contribution made).
 302     $sql = "SELECT SUM(goal_amount) as goal_total FROM civicrm_pcp WHERE "
 303       . "goal_amount IS NOT NULL AND id IN ("
 304       . "SELECT DISTINCT {$this->_aliases['civicrm_pcp']}.id {$this->_from} "
 305       . "{$this->_where}"
 306       . ")";
 307     $dao = CRM_Core_DAO::executeQuery($sql);
 308     $dao->fetch();
 309     $goal_total = $dao->goal_total;
 310
 311     $statistics['counts']['goal_total'] = array(
 312       'title' => ts('Goal Total'),
 313       'value' => $goal_total,
 314       'type' => CRM_Utils_Type::T_MONEY,
 315     );
 316     $statistics['counts']['committed_total'] = array(
 317       'title' => ts('Total Committed'),
 318       'value' => $committed_total,
 319       'type' => CRM_Utils_Type::T_MONEY,
 320     );
 321     $statistics['counts']['received_total'] = array(
 322       'title' => ts('Total Received'),
 323       'value' => $received_total,
 324       'type' => CRM_Utils_Type::T_MONEY,
 325     );
 326     $statistics['counts']['donors_total'] = array(
 327       'title' => ts('Total Donors'),
 328       'value' => $donors_total,
 329       'type' => CRM_Utils_Type::T_INT,
 330     );
 331     return $statistics;
 332   }
 333
 334   /**
 335    * Alter display of rows.
 336    *
 337    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 338    * such as rendering contacts as links.
 339    *
 340    * @param array $rows
 341    *   Rows generated by SQL, with an array for each row.
 342    */
 343   public function alterDisplay(&$rows) {
 344     $entryFound = FALSE;
 345     $checkList = array();
 346     foreach ($rows as $rowNum => $row) {
 347       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 348         // not repeat contact sort names if it matches with the one
 349         // in previous row
 350         $repeatFound = FALSE;
 351
 352         foreach ($row as $colName => $colVal) {
 353           if (!empty($checkList[$colName]) &&
 354             is_array($checkList[$colName]) &&
 355             in_array($colVal, $checkList[$colName])
 356           ) {
 357             $rows[$rowNum][$colName] = "";
 358             $repeatFound = TRUE;
 359           }
 360           if (in_array($colName, $this->_noRepeats)) {
 361             $checkList[$colName][] = $colVal;
 362           }
 363         }
 364       }
 365
 366       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 367         $rows[$rowNum]['civicrm_contact_sort_name'] &&
 368         array_key_exists('civicrm_contact_id', $row)
 369       ) {
 370         $url = CRM_Utils_System::url("civicrm/contact/view",
 371           'reset=1&cid=' . $row['civicrm_contact_id'],
 372           $this->_absoluteUrl
 373         );
 374         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 375         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 376         $entryFound = TRUE;
 377       }
 378
 379       if (!empty($row['civicrm_financial_trxn_card_type'])) {
 380         $rows[$rowNum]['civicrm_financial_trxn_card_type'] = $this->getLabels($row['civicrm_financial_trxn_card_type'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type');
 381         $entryFound = TRUE;
 382       }
 383
 384       if (!$entryFound) {
 385         break;
 386       }
 387     }
 388   }
 389
 390 }