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