CRM/Report/Form/Contribute/PCP.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.6                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2014                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2014
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_Contribute_PCP extends CRM_Report_Form {
  37   /**
  38    *
  39    */
  40   /**
  41    *
  42    */
  43   public function __construct() {
  44     $this->_columns = array(
  45       'civicrm_contact' => array(
  46         'dao' => 'CRM_Contact_DAO_Contact',
  47         'fields' => array(
  48           'sort_name' => array(
  49             'title' => ts('Supporter'),
  50             'required' => TRUE,
  51             'default' => TRUE,
  52           ),
  53           'id' => array(
  54             'required' => TRUE,
  55             'no_display' => TRUE,
  56           ),
  57           'contact_type' => array(
  58             'title' => ts('Supporter Contact Type'),
  59           ),
  60           'contact_sub_type' => array(
  61             'title' => ts('Supporter Contact Subtype'),
  62           ),
  63         ),
  64         'filters' => array(
  65           'sort_name' => array(
  66             'title' => ts('Supporter Name'),
  67             'type' => CRM_Utils_Type::T_STRING,
  68             'operator' => 'like',
  69           ),
  70           'id' => array(
  71             'title' => ts('Contact ID'),
  72             'no_display' => TRUE,
  73           ),
  74         ),
  75         'grouping' => 'pcp-fields',
  76       ),
  77       'civicrm_contribution_page' => array(
  78         'dao' => 'CRM_Contribute_DAO_ContributionPage',
  79         'fields' => array(
  80           'page_title' => array(
  81             'title' => ts('Contribution Page Title'),
  82             'name' => 'title',
  83             'default' => TRUE,
  84           ),
  85         ),
  86         'filters' => array(
  87           'page_title' => array(
  88             'title' => ts('Contribution Page Title'),
  89             'name' => 'title',
  90             'type' => CRM_Utils_Type::T_STRING,
  91           ),
  92         ),
  93         'grouping' => 'pcp-fields',
  94       ),
  95       'civicrm_pcp' => array(
  96         'dao' => 'CRM_PCP_DAO_PCP',
  97         'fields' => array(
  98           'title' => array(
  99             'title' => ts('Personal Campaign Title'),
 100             'default' => TRUE,
 101           ),
 102           'goal_amount' => array(
 103             'title' => ts('Goal Amount'),
 104             'type' => CRM_Utils_Type::T_MONEY,
 105             'default' => TRUE,
 106           ),
 107         ),
 108         'filters' => array(
 109           'title' => array(
 110             'title' => ts('Personal Campaign Title'),
 111             'type' => CRM_Utils_Type::T_STRING,
 112           ),
 113         ),
 114         'grouping' => 'pcp-fields',
 115       ),
 116       'civicrm_contribution_soft' => array(
 117         'dao' => 'CRM_Contribute_DAO_ContributionSoft',
 118         'fields' => array(
 119           'amount_1' => array(
 120             'title' => ts('Committed Amount'),
 121             'name' => 'amount',
 122             'type' => CRM_Utils_Type::T_MONEY,
 123             'default' => TRUE,
 124             'statistics' => array(
 125               'sum' => ts('Committed Amount'),
 126             ),
 127           ),
 128           'amount_2' => array(
 129             'title' => ts('Amount Received'),
 130             'name' => 'amount',
 131             'type' => CRM_Utils_Type::T_MONEY,
 132             'default' => TRUE,
 133             // nice trick with dbAlias
 134             'dbAlias' => 'SUM(IF( contribution_civireport.contribution_status_id > 1, 0, contribution_soft_civireport.amount))',
 135           ),
 136           'soft_id' => array(
 137             'title' => ts('Number of Donors'),
 138             'name' => 'id',
 139             'default' => TRUE,
 140             'statistics' => array(
 141               'count' => ts('Number of Donors'),
 142             ),
 143           ),
 144         ),
 145         'filters' => array(
 146           'amount_2' => array(
 147             'title' => ts('Amount Received'),
 148             'type' => CRM_Utils_Type::T_MONEY,
 149             'dbAlias' => 'SUM(IF( contribution_civireport.contribution_status_id > 1, 0, contribution_soft_civireport.amount))',
 150           ),
 151         ),
 152         'grouping' => 'pcp-fields',
 153       ),
 154       'civicrm_contribution' => array(
 155         'dao' => 'CRM_Contribute_DAO_Contribution',
 156         'fields' => array(
 157           'contribution_id' => array(
 158             'name' => 'id',
 159             'no_display' => TRUE,
 160             'required' => TRUE,
 161           ),
 162           'receive_date' => array(
 163             'title' => ts('Most Recent Contribution'),
 164             'default' => TRUE,
 165             'statistics' => array(
 166               'max' => ts('Most Recent Contribution'),
 167             ),
 168           ),
 169         ),
 170         'grouping' => 'pcp-fields',
 171       ),
 172     );
 173
 174     parent::__construct();
 175   }
 176
 177   public function from() {
 178     $this->_from = "
 179 FROM civicrm_pcp {$this->_aliases['civicrm_pcp']}
 180
 181 LEFT JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
 182           ON {$this->_aliases['civicrm_pcp']}.id =
 183              {$this->_aliases['civicrm_contribution_soft']}.pcp_id
 184
 185 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 186           ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
 187              {$this->_aliases['civicrm_contribution']}.id
 188
 189 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 190           ON {$this->_aliases['civicrm_pcp']}.contact_id =
 191              {$this->_aliases['civicrm_contact']}.id
 192
 193 LEFT JOIN civicrm_contribution_page {$this->_aliases['civicrm_contribution_page']}
 194           ON {$this->_aliases['civicrm_pcp']}.page_id =
 195              {$this->_aliases['civicrm_contribution_page']}.id";
 196   }
 197
 198   public function groupBy() {
 199     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_pcp']}.id";
 200   }
 201
 202   public function orderBy() {
 203     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name ";
 204   }
 205
 206   public function where() {
 207     $whereClauses = $havingClauses = array();
 208
 209     foreach ($this->_columns as $tableName => $table) {
 210       if (array_key_exists('filters', $table)) {
 211         foreach ($table['filters'] as $fieldName => $field) {
 212           $clause = NULL;
 213
 214           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 215             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 216             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 217             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 218             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 219           }
 220           else {
 221             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 222
 223             if ($op) {
 224               $clause = $this->whereClause($field,
 225                 $op,
 226                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 227                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 228                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 229               );
 230             }
 231           }
 232
 233           if (!empty($clause)) {
 234             if ($tableName == 'civicrm_contribution_soft' &&
 235               $fieldName == 'amount_2'
 236             ) {
 237               $havingClauses[] = $clause;
 238             }
 239             else {
 240               $whereClauses[] = $clause;
 241             }
 242           }
 243         }
 244       }
 245     }
 246     if (empty($whereClauses)) {
 247       $this->_where = "WHERE ( 1 ) ";
 248     }
 249     else {
 250       $this->_where = "WHERE " . implode(' AND ', $whereClauses);
 251     }
 252     if ($this->_aclWhere) {
 253       $this->_where .= " AND {$this->_aclWhere} ";
 254     }
 255     $this->_having = "";
 256     if (!empty($havingClauses)) {
 257       // use this clause to construct group by clause.
 258       $this->_having = "HAVING " . implode(' AND ', $havingClauses);
 259     }
 260   }
 261
 262   /**
 263    * @param $rows
 264    *
 265    * @return array
 266    */
 267   public function statistics(&$rows) {
 268     $statistics = parent::statistics($rows);
 269
 270     $select =
 271       "SELECT SUM({$this->_aliases['civicrm_pcp']}.goal_amount) as goal_total, " .
 272       "SUM({$this->_aliases['civicrm_contribution_soft']}.amount) as committed_total, " .
 273       "COUNT({$this->_aliases['civicrm_contribution_soft']}.id) as donors_total, " .
 274       "SUM(IF( contribution_civireport.contribution_status_id > 1, 0, contribution_soft_civireport.amount)) AS received_total ";
 275     $sql = "{$select} {$this->_from} {$this->_where}";
 276     $dao = CRM_Core_DAO::executeQuery($sql);
 277     $dao->fetch();
 278     $statistics['counts']['goal_total'] = array(
 279       'title' => ts('Goal Total'),
 280       'value' => $dao->goal_total,
 281       'type' => CRM_Utils_Type::T_MONEY
 282     );
 283     $statistics['counts']['committed_total'] = array(
 284       'title' => ts('Total Committed'),
 285       'value' => $dao->committed_total,
 286       'type' => CRM_Utils_Type::T_MONEY
 287     );
 288     $statistics['counts']['received_total'] = array(
 289       'title' => ts('Total Received'),
 290       'value' => $dao->received_total,
 291       'type' => CRM_Utils_Type::T_MONEY
 292     );
 293     $statistics['counts']['donors_total'] = array(
 294       'title' => ts('Total Donors'),
 295       'value' => $dao->donors_total,
 296       'type' => CRM_Utils_Type::T_INT
 297     );
 298     return $statistics;
 299   }
 300
 301   /**
 302    * @param $rows
 303    */
 304   public function alterDisplay(&$rows) {
 305     // custom code to alter rows
 306     $entryFound = FALSE;
 307     $checkList = array();
 308     foreach ($rows as $rowNum => $row) {
 309       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 310         // not repeat contact sort names if it matches with the one
 311         // in previous row
 312         $repeatFound = FALSE;
 313
 314         foreach ($row as $colName => $colVal) {
 315           if (!empty($checkList[$colName]) &&
 316             is_array($checkList[$colName]) &&
 317             in_array($colVal, $checkList[$colName])
 318           ) {
 319             $rows[$rowNum][$colName] = "";
 320             $repeatFound = TRUE;
 321           }
 322           if (in_array($colName, $this->_noRepeats)) {
 323             $checkList[$colName][] = $colVal;
 324           }
 325         }
 326       }
 327
 328       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 329         $rows[$rowNum]['civicrm_contact_sort_name'] &&
 330         array_key_exists('civicrm_contact_id', $row)
 331       ) {
 332         $url = CRM_Utils_System::url("civicrm/contact/view",
 333           'reset=1&cid=' . $row['civicrm_contact_id'],
 334           $this->_absoluteUrl
 335         );
 336         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 337         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 338         $entryFound = TRUE;
 339       }
 340
 341       if (!$entryFound) {
 342         break;
 343       }
 344     }
 345   }
 346 }