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