CRM/Report/Form/Contribute/PCP.php

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