CRM/Report/Form/Contribute/PCP.php

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