CRM/Report/Form/Pledge/Pbnp.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_Pledge_Pbnp extends CRM_Report_Form {
  18   protected $_charts = [
  19     '' => 'Tabular',
  20     'barChart' => 'Bar Chart',
  21     'pieChart' => 'Pie Chart',
  22   ];
  23   public $_drilldownReport = ['pledge/summary' => 'Link to Detail Report'];
  24
  25   protected $_customGroupExtends = [
  26     'Pledge',
  27   ];
  28
  29   /**
  30    * Class constructor.
  31    */
  32   public function __construct() {
  33     $this->_columns = [
  34       'civicrm_contact' => [
  35         'dao' => 'CRM_Contact_DAO_Contact',
  36         'fields' => [
  37           'sort_name' => [
  38             'title' => ts('Constituent Name'),
  39             'required' => TRUE,
  40             'no_repeat' => TRUE,
  41           ],
  42           'id' => [
  43             'no_display' => TRUE,
  44             'required' => TRUE,
  45           ],
  46         ],
  47         'grouping' => 'contact-fields',
  48       ],
  49       'civicrm_pledge' => [
  50         'dao' => 'CRM_Pledge_DAO_Pledge',
  51         'fields' => [
  52           'pledge_create_date' => [
  53             'title' => ts('Pledge Made'),
  54             'required' => TRUE,
  55           ],
  56           'financial_type_id' => [
  57             'title' => ts('Financial Type'),
  58             'required' => TRUE,
  59           ],
  60           'frequency_unit' => [
  61             'title' => ts('Frequency Unit'),
  62           ],
  63           'installments' => [
  64             'title' => ts('Installments'),
  65           ],
  66           'amount' => [
  67             'title' => ts('Amount'),
  68             'required' => TRUE,
  69             'type' => CRM_Utils_Type::T_MONEY,
  70           ],
  71           'currency' => [
  72             'required' => TRUE,
  73             'no_display' => TRUE,
  74           ],
  75           'status_id' => [
  76             'title' => ts('Status'),
  77           ],
  78         ],
  79         'filters' => [
  80           'pledge_create_date' => [
  81             'title' => ts('Pledge Made'),
  82             'operatorType' => CRM_Report_Form::OP_DATE,
  83           ],
  84           'pledge_amount' => [
  85             'title' => ts('Pledged Amount'),
  86             'operatorType' => CRM_Report_Form::OP_INT,
  87           ],
  88           'currency' => [
  89             'title' => ts('Currency'),
  90             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  91             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
  92             'default' => NULL,
  93             'type' => CRM_Utils_Type::T_STRING,
  94           ],
  95           'financial_type_id' => [
  96             'title' => ts('Financial Type'),
  97             'type' => CRM_Utils_Type::T_INT,
  98             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  99             'options' => CRM_Contribute_PseudoConstant::financialType(),
 100           ],
 101           'pledge_status_id' => [
 102             'name' => 'status_id',
 103             'title' => ts('Pledge Status'),
 104             'type' => CRM_Utils_Type::T_INT,
 105             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 106             'options' => CRM_Core_OptionGroup::values('pledge_status'),
 107           ],
 108           'installments' => [
 109             'title' => ts('Installments'),
 110             'type' => CRM_Utils_Type::T_INT,
 111           ],
 112         ],
 113         'grouping' => 'pledge-fields',
 114       ],
 115       'civicrm_pledge_payment' => [
 116         'dao' => 'CRM_Pledge_DAO_PledgePayment',
 117         'fields' => [
 118           'scheduled_date' => [
 119             'title' => ts('Next Payment Due'),
 120             'type' => CRM_Utils_Type::T_DATE,
 121             'required' => TRUE,
 122           ],
 123           'scheduled_amount' => [
 124             'type' => CRM_Utils_Type::T_MONEY,
 125             'title' => ts('Next Payment Amount'),
 126           ],
 127         ],
 128         'filters' => [
 129           'scheduled_date' => [
 130             'title' => ts('Next Payment Due'),
 131             'operatorType' => CRM_Report_Form::OP_DATE,
 132             'type' => CRM_Utils_Type::T_DATE,
 133           ],
 134         ],
 135         'grouping' => 'pledge-fields',
 136       ],
 137       'civicrm_address' => [
 138         'dao' => 'CRM_Core_DAO_Address',
 139         'fields' => [
 140           'street_address' => NULL,
 141           'city' => NULL,
 142           'postal_code' => NULL,
 143           'state_province_id' => [
 144             'title' => ts('State/Province'),
 145           ],
 146           'country_id' => [
 147             'title' => ts('Country'),
 148             'default' => TRUE,
 149           ],
 150         ],
 151         'grouping' => 'contact-fields',
 152       ],
 153       'civicrm_email' => [
 154         'dao' => 'CRM_Core_DAO_Email',
 155         'fields' => ['email' => NULL],
 156         'grouping' => 'contact-fields',
 157       ],
 158     ];
 159
 160     // If we have a campaign, build out the relevant elements
 161     $this->addCampaignFields('civicrm_pledge');
 162
 163     $this->_groupFilter = TRUE;
 164     $this->_tagFilter = TRUE;
 165     $this->_currencyColumn = 'civicrm_pledge_currency';
 166     parent::__construct();
 167   }
 168
 169   public function preProcess() {
 170     $this->assign('reportTitle', ts('Pledged but not Paid Report'));
 171     parent::preProcess();
 172   }
 173
 174   public function select() {
 175     $select = [];
 176     $this->_columnHeaders = [];
 177     foreach ($this->_columns as $tableName => $table) {
 178       if (array_key_exists('fields', $table)) {
 179         foreach ($table['fields'] as $fieldName => $field) {
 180           if (!empty($field['required']) ||
 181             !empty($this->_params['fields'][$fieldName])
 182           ) {
 183             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 184             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 185             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL;
 186           }
 187         }
 188       }
 189     }
 190     $this->_selectClauses = $select;
 191     $this->_select = "SELECT " . implode(', ', $select) . " ";
 192   }
 193
 194   public function from() {
 195     $this->_from = NULL;
 196
 197     $allStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 198     $pendingStatus = array_search('Pending', $allStatus);
 199     foreach ([
 200       'Pending',
 201       'In Progress',
 202       'Overdue',
 203     ] as $statusKey) {
 204       if ($key = CRM_Utils_Array::key($statusKey, $allStatus)) {
 205         $unpaidStatus[] = $key;
 206       }
 207     }
 208
 209     $statusIds = implode(', ', $unpaidStatus);
 210
 211     $this->_from = "
 212         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 213              INNER JOIN civicrm_pledge  {$this->_aliases['civicrm_pledge']}
 214                         ON ({$this->_aliases['civicrm_pledge']}.contact_id =
 215                             {$this->_aliases['civicrm_contact']}.id)  AND
 216                             {$this->_aliases['civicrm_pledge']}.status_id IN ( {$statusIds} )\n";
 217
 218     // Note that the derived query protects us from providing inaccurate data in the edge case where pledge
 219     // payments have been edited such that they are not in id order. This might be better as a temp table.
 220     $this->_from .= "LEFT JOIN (SELECT * FROM civicrm_pledge_payment ORDER BY scheduled_date) as {$this->_aliases['civicrm_pledge_payment']}
 221                         ON ({$this->_aliases['civicrm_pledge']}.id =
 222                             {$this->_aliases['civicrm_pledge_payment']}.pledge_id AND  {$this->_aliases['civicrm_pledge_payment']}.status_id = {$pendingStatus} ) ";
 223
 224     $this->joinAddressFromContact();
 225     $this->joinEmailFromContact();
 226
 227   }
 228
 229   public function groupBy() {
 230     $groupBy = [
 231       "{$this->_aliases['civicrm_pledge']}.contact_id",
 232       "{$this->_aliases['civicrm_pledge']}.id",
 233       "{$this->_aliases['civicrm_pledge']}.currency",
 234     ];
 235     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 236   }
 237
 238   public function orderBy() {
 239     $this->_orderBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_pledge']}.contact_id, {$this->_aliases['civicrm_pledge']}.id";
 240   }
 241
 242   public function postProcess() {
 243     // get the acl clauses built before we assemble the query
 244     $this->buildACLClause($this->_aliases['civicrm_contact']);
 245     parent::PostProcess();
 246   }
 247
 248   /**
 249    * Alter display of rows.
 250    *
 251    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 252    * such as rendering contacts as links.
 253    *
 254    * @param array $rows
 255    */
 256   public function alterDisplay(&$rows) {
 257     $entryFound = FALSE;
 258     $checkList = [];
 259     $display_flag = $prev_cid = $cid = 0;
 260
 261     foreach ($rows as $rowNum => $row) {
 262       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 263         // don't repeat contact details if its same as the previous row
 264         if (array_key_exists('civicrm_contact_id', $row)) {
 265           if ($cid = $row['civicrm_contact_id']) {
 266             if ($rowNum == 0) {
 267               $prev_cid = $cid;
 268             }
 269             else {
 270               if ($prev_cid == $cid) {
 271                 $display_flag = 1;
 272                 $prev_cid = $cid;
 273               }
 274               else {
 275                 $display_flag = 0;
 276                 $prev_cid = $cid;
 277               }
 278             }
 279
 280             if ($display_flag) {
 281               foreach ($row as $colName => $colVal) {
 282                 if (in_array($colName, $this->_noRepeats)) {
 283                   unset($rows[$rowNum][$colName]);
 284                 }
 285               }
 286             }
 287             $entryFound = TRUE;
 288           }
 289         }
 290       }
 291
 292       //handle the Financial Type Ids
 293       if (array_key_exists('civicrm_pledge_financial_type_id', $row)) {
 294         if ($value = $row['civicrm_pledge_financial_type_id']) {
 295           $rows[$rowNum]['civicrm_pledge_financial_type_id'] = CRM_Contribute_PseudoConstant::financialType($value, FALSE);
 296         }
 297         $entryFound = TRUE;
 298       }
 299
 300       //handle the Status Ids
 301       if (array_key_exists('civicrm_pledge_status_id', $row)) {
 302         if ($value = $row['civicrm_pledge_status_id']) {
 303           $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Core_PseudoConstant::getLabel('CRM_Pledge_BAO_Pledge', 'status_id', $value);
 304         }
 305         $entryFound = TRUE;
 306       }
 307
 308       // convert display name to links
 309       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 310         array_key_exists('civicrm_contact_id', $row)
 311       ) {
 312         $url = CRM_Report_Utils_Report::getNextUrl('pledge/summary',
 313           'reset=1&force=1&id_op=eq&id_value=' .
 314           $row['civicrm_contact_id'],
 315           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 316         );
 317         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 318         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Pledge Details for this contact");
 319         $entryFound = TRUE;
 320       }
 321
 322       // If using campaigns, convert campaign_id to campaign title
 323       if (array_key_exists('civicrm_pledge_campaign_id', $row)) {
 324         if ($value = $row['civicrm_pledge_campaign_id']) {
 325           $rows[$rowNum]['civicrm_pledge_campaign_id'] = $this->campaigns[$value];
 326         }
 327         $entryFound = TRUE;
 328       }
 329
 330       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 331       // skip looking further in rows, if first row itself doesn't
 332       // have the column we need
 333       if (!$entryFound) {
 334         break;
 335       }
 336     }
 337   }
 338
 339 }