CRM/Report/Form/Pledge/Detail.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28
  29 /*
  30  *   !!!!!!!!!!!!!!!!!!!!
  31  *     NB: this is named detail but behaves like a summary report.
  32  *   It is also accessed through the Pledge Summary link in the UI
  33  *   This should presumably be changed.
  34  *   ~ Doten
  35  *   !!!!!!!!!!!!!!!!!!!!
  36  *
  37  */
  38
  39 /**
  40  *
  41  * @package CRM
  42  * @copyright CiviCRM LLC (c) 2004-2015
  43  * $Id$
  44  *
  45  */
  46 class CRM_Report_Form_Pledge_Detail extends CRM_Report_Form {
  47
  48   protected $_summary = NULL;
  49   protected $_totalPaid = FALSE;
  50   protected $_pledgeStatuses = array();
  51   protected $_customGroupExtends = array(
  52     'Pledge',
  53     'Individual',
  54   );
  55
  56   /**
  57    */
  58   /**
  59    */
  60   public function __construct() {
  61     $this->_pledgeStatuses = CRM_Contribute_PseudoConstant::contributionStatus();
  62     // Check if CiviCampaign is a) enabled and b) has active campaigns
  63     $config = CRM_Core_Config::singleton();
  64     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  65     if ($campaignEnabled) {
  66       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  67       $this->activeCampaigns = $getCampaigns['campaigns'];
  68       asort($this->activeCampaigns);
  69     }
  70
  71     $this->_columns = array(
  72       'civicrm_contact' => array(
  73         'dao' => 'CRM_Contact_DAO_Contact',
  74         'fields' => array(
  75           'sort_name' => array(
  76             'title' => ts('Contact Name'),
  77             'required' => TRUE,
  78             'no_repeat' => TRUE,
  79           ),
  80         ),
  81         'filters' => array(
  82           'sort_name' => array('title' => ts('Contact Name')),
  83           'id' => array('no_display' => TRUE),
  84         ),
  85         'grouping' => 'contact-fields',
  86       ),
  87       'civicrm_email' => array(
  88         'dao' => 'CRM_Core_DAO_Email',
  89         'fields' => array(
  90           'email' => array('no_repeat' => TRUE),
  91         ),
  92         'grouping' => 'contact-fields',
  93       ),
  94       'civicrm_pledge' => array(
  95         'dao' => 'CRM_Pledge_DAO_Pledge',
  96         'fields' => array(
  97           'id' => array(
  98             'no_display' => TRUE,
  99             'required' => TRUE,
 100           ),
 101           'contact_id' => array(
 102             'no_display' => TRUE,
 103             'required' => TRUE,
 104           ),
 105           'amount' => array(
 106             'title' => ts('Pledge Amount'),
 107             'required' => TRUE,
 108             'type' => CRM_Utils_Type::T_MONEY,
 109           ),
 110           'currency' => array(
 111             'required' => TRUE,
 112             'no_display' => TRUE,
 113           ),
 114           'frequency_unit' => array(
 115             'title' => ts('Frequency Unit'),
 116           ),
 117           'installments' => array(
 118             'title' => ts('Installments'),
 119           ),
 120           'pledge_create_date' => array(
 121             'title' => ts('Pledge Made Date'),
 122           ),
 123           'start_date' => array(
 124             'title' => ts('Pledge Start Date'),
 125             'type' => CRM_Utils_Type::T_DATE,
 126           ),
 127           'end_date' => array(
 128             'title' => ts('Pledge End Date'),
 129             'type' => CRM_Utils_Type::T_DATE,
 130           ),
 131           'status_id' => array(
 132             'title' => ts('Pledge Status'),
 133             'required' => TRUE,
 134           ),
 135         ),
 136         'filters' => array(
 137           'pledge_create_date' => array(
 138             'title' => 'Pledge Made Date',
 139             'operatorType' => CRM_Report_Form::OP_DATE,
 140           ),
 141           'pledge_amount' => array(
 142             'title' => ts('Pledged Amount'),
 143             'operatorType' => CRM_Report_Form::OP_INT,
 144           ),
 145           'currency' => array(
 146             'title' => 'Currency',
 147             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 148             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 149             'default' => NULL,
 150             'type' => CRM_Utils_Type::T_STRING,
 151           ),
 152           'sid' => array(
 153             'name' => 'status_id',
 154             'title' => ts('Pledge Status'),
 155             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 156             'options' => CRM_Core_OptionGroup::values('contribution_status'),
 157           ),
 158
 159         ),
 160       ),
 161       'civicrm_pledge_payment' => array(
 162         'dao' => 'CRM_Pledge_DAO_PledgePayment',
 163         'fields' => array(
 164           'total_paid' => array(
 165             'title' => ts('Total Amount Paid'),
 166             'type' => CRM_Utils_Type::T_MONEY,
 167           ),
 168           'balance_due' => array(
 169             'title' => ts('Balance Due'),
 170             'default' => TRUE,
 171             'type' => CRM_Utils_Type::T_MONEY,
 172           ),
 173         ),
 174       ),
 175     );
 176
 177     $this->_columns += $this->getAddressColumns(array('group_by' => FALSE)) + $this->getPhoneColumns();
 178
 179     // If we have a campaign, build out the relevant elements
 180     $this->_tagFilter = TRUE;
 181     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 182       $this->_columns['civicrm_pledge']['fields']['campaign_id'] = array(
 183         'title' => 'Campaign',
 184         'default' => 'false',
 185       );
 186       $this->_columns['civicrm_pledge']['filters']['campaign_id'] = array(
 187         'title' => ts('Campaign'),
 188         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 189         'options' => $this->activeCampaigns,
 190       );
 191       $this->_columns['civicrm_pledge']['group_bys']['campaign_id'] = array('title' => ts('Campaign'));
 192
 193     }
 194
 195     $this->_groupFilter = TRUE;
 196     $this->_tagFilter = TRUE;
 197     $this->_currencyColumn = 'civicrm_pledge_currency';
 198     parent::__construct();
 199   }
 200
 201   public function preProcess() {
 202     parent::preProcess();
 203   }
 204
 205   public function select() {
 206     parent::select();
 207   }
 208
 209   /**
 210    * If we are retrieving total paid we need to define the inclusion of pledge_payment.
 211    *
 212    * @param string $tableName
 213    * @param $tableKey
 214    * @param string $fieldName
 215    * @param $field
 216    *
 217    * @return bool|string
 218    */
 219   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
 220     if ($fieldName == 'total_paid') {
 221       $this->_totalPaid = TRUE; // add pledge_payment join
 222       $this->_columnHeaders["{$tableName}_{$fieldName}"] = array(
 223         'title' => $field['title'],
 224         'type' => $field['type'],
 225       );
 226       return "COALESCE(sum({$this->_aliases[$tableName]}.actual_amount), 0) as {$tableName}_{$fieldName}";
 227     }
 228     if ($fieldName == 'balance_due') {
 229       $cancelledStatus = array_search('Cancelled', $this->_pledgeStatuses);
 230       $completedStatus = array_search('Completed', $this->_pledgeStatuses);
 231       $this->_totalPaid = TRUE; // add pledge_payment join
 232       $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field['title'];
 233       $this->_columnHeaders["{$tableName}_{$fieldName}"] = array(
 234         'title' => $field['title'],
 235         'type' => $field['type'],
 236       );
 237       return "IF({$this->_aliases['civicrm_pledge']}.status_id IN({$cancelledStatus}, $completedStatus), 0, COALESCE({$this->_aliases['civicrm_pledge']}.amount, 0) - COALESCE(sum({$this->_aliases[$tableName]}.actual_amount),0)) as {$tableName}_{$fieldName}";
 238     }
 239     return FALSE;
 240   }
 241
 242   public function groupBy() {
 243     parent::groupBy();
 244     if (empty($this->_groupBy) && $this->_totalPaid) {
 245       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_pledge']}.id, {$this->_aliases['civicrm_pledge']}.currency";
 246     }
 247   }
 248
 249   public function from() {
 250     $this->_from = "
 251             FROM civicrm_pledge {$this->_aliases['civicrm_pledge']}
 252                  LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 253                       ON ({$this->_aliases['civicrm_contact']}.id =
 254                           {$this->_aliases['civicrm_pledge']}.contact_id )
 255                  {$this->_aclFrom} ";
 256
 257     if ($this->_totalPaid) {
 258       $this->_from .= "
 259         LEFT JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']} ON
 260           {$this->_aliases['civicrm_pledge']}.id = {$this->_aliases['civicrm_pledge_payment']}.pledge_id
 261           AND {$this->_aliases['civicrm_pledge_payment']}.status_id = 1
 262       ";
 263     }
 264
 265     $this->addPhoneFromClause();
 266     $this->addAddressFromClause();
 267     // include email field if email column is to be included
 268     if ($this->_emailField) {
 269       $this->_from .= "
 270                  LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 271                            ON ({$this->_aliases['civicrm_contact']}.id =
 272                                {$this->_aliases['civicrm_email']}.contact_id) AND
 273                                {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 274     }
 275   }
 276
 277   /**
 278    * @param $rows
 279    *
 280    * @return array
 281    */
 282   public function statistics(&$rows) {
 283     $statistics = parent::statistics($rows);
 284     //regenerate the from field without extra left join on pledge payments
 285     $this->_totalPaid = FALSE;
 286     $this->from();
 287     $this->customDataFrom();
 288     if (!$this->_having) {
 289       $totalAmount = $average = array();
 290       $count = 0;
 291       $select = "
 292         SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount )       as count,
 293           SUM({$this->_aliases['civicrm_pledge']}.amount )         as amount,
 294           ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg,
 295           {$this->_aliases['civicrm_pledge']}.currency as currency
 296         ";
 297
 298       $group = "GROUP BY {$this->_aliases['civicrm_pledge']}.currency";
 299       $sql = "{$select} {$this->_from} {$this->_where} {$group}";
 300       $dao = CRM_Core_DAO::executeQuery($sql);
 301       $count = $index = $totalCount = 0;
 302       // this will run once per currency
 303       while ($dao->fetch()) {
 304         $totalAmount = CRM_Utils_Money::format($dao->amount, $dao->currency);
 305         $average = CRM_Utils_Money::format($dao->avg, $dao->currency);
 306         $count = $dao->count;
 307         $totalCount .= $count;
 308         $statistics['counts']['amount' . $index] = array(
 309           'title' => ts('Total Pledged') . ' (' . $dao->currency . ')',
 310           'value' => $totalAmount,
 311           'type' => CRM_Utils_Type::T_STRING,
 312         );
 313         $statistics['counts']['avg' . $index] = array(
 314           'title' => ts('Average') . ' (' . $dao->currency . ')',
 315           'value' => $average,
 316           'type' => CRM_Utils_Type::T_STRING,
 317         );
 318         $statistics['counts']['count' . $index] = array(
 319           'title' => ts('Total No Pledges') . ' (' . $dao->currency . ')',
 320           'value' => $count,
 321           'type' => CRM_Utils_Type::T_INT,
 322         );
 323         $index++;
 324       }
 325       if ($totalCount > $count) {
 326         $statistics['counts']['count' . $index] = array(
 327           'title' => ts('Total No Pledges'),
 328           'value' => $totalCount,
 329           'type' => CRM_Utils_Type::T_INT,
 330         );
 331       }
 332     }
 333     return $statistics;
 334   }
 335
 336   public function orderBy() {
 337     $this->_orderBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id";
 338   }
 339
 340   public function where() {
 341     $clauses = array();
 342     foreach ($this->_columns as $tableName => $table) {
 343       if (array_key_exists('filters', $table)) {
 344         foreach ($table['filters'] as $fieldName => $field) {
 345           $clause = NULL;
 346           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 347             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 348             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 349             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 350
 351             if ($relative || $from || $to) {
 352               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 353             }
 354           }
 355           else {
 356             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 357             if ($op) {
 358               $clause = $this->whereClause($field,
 359                 $op,
 360                 CRM_Utils_Array::value("{$fieldName}_value",
 361                   $this->_params
 362                 ),
 363                 CRM_Utils_Array::value("{$fieldName}_min",
 364                   $this->_params
 365                 ),
 366                 CRM_Utils_Array::value("{$fieldName}_max",
 367                   $this->_params
 368                 )
 369               );
 370             }
 371           }
 372
 373           if (!empty($clause)) {
 374             $clauses[] = $clause;
 375           }
 376         }
 377       }
 378     }
 379     if (empty($clauses)) {
 380       $this->_where = "WHERE ({$this->_aliases['civicrm_pledge']}.is_test=0 ) ";
 381     }
 382     else {
 383       $this->_where = "WHERE  ({$this->_aliases['civicrm_pledge']}.is_test=0 )  AND
 384       " . implode(' AND ', $clauses);
 385     }
 386
 387     if ($this->_aclWhere) {
 388       $this->_where .= " AND {$this->_aclWhere} ";
 389     }
 390   }
 391
 392   public function postProcess() {
 393
 394     $this->beginPostProcess();
 395
 396     // get the acl clauses built before we assemble the query
 397     $this->buildACLClause($this->_aliases['civicrm_contact']);
 398     $sql = $this->buildQuery();
 399     $rows = $payment = array();
 400
 401     $dao = CRM_Core_DAO::executeQuery($sql);
 402
 403     // Set pager for the Main Query only which displays basic information
 404     $this->setPager();
 405     $this->assign('columnHeaders', $this->_columnHeaders);
 406
 407     while ($dao->fetch()) {
 408       $pledgeID = $dao->civicrm_pledge_id;
 409       foreach ($this->_columnHeaders as $columnHeadersKey => $columnHeadersValue) {
 410         $row = array();
 411         if (property_exists($dao, $columnHeadersKey)) {
 412           $display[$pledgeID][$columnHeadersKey] = $dao->$columnHeadersKey;
 413         }
 414       }
 415       $pledgeIDArray[] = $pledgeID;
 416     }
 417
 418     // Add Special headers
 419     $this->_columnHeaders['scheduled_date'] = array(
 420       'type' => CRM_Utils_Type::T_DATE,
 421       'title' => 'Next Payment Due',
 422     );
 423     $this->_columnHeaders['scheduled_amount'] = array(
 424       'type' => CRM_Utils_Type::T_MONEY,
 425       'title' => 'Next Payment Amount',
 426     );
 427     $this->_columnHeaders['status_id'] = NULL;
 428
 429     /*
 430      * this is purely about ordering the total paid & balance due fields off to the end
 431      * of the table in case custom or address fields cause them to fall in the middle
 432      * (arguably the pledge amount should be moved to after these fields too)
 433      *
 434      */
 435     $tableHeaders = array(
 436       'civicrm_pledge_payment_total_paid',
 437       'civicrm_pledge_payment_balance_due',
 438     );
 439
 440     foreach ($tableHeaders as $header) {
 441       //per above, unset & reset them so they move to the end
 442       if (isset($this->_columnHeaders[$header])) {
 443         $headervalue = $this->_columnHeaders[$header];
 444         unset($this->_columnHeaders[$header]);
 445         $this->_columnHeaders[$header] = $headervalue;
 446       }
 447     }
 448
 449     // To Display Payment Details of pledged amount
 450     // for pledge payments In Progress
 451     if (!empty($display)) {
 452       $statusId = array_keys(CRM_Core_PseudoConstant::accountOptionValues("contribution_status", NULL, " AND v.name IN  ('Pending', 'Overdue')"));
 453       $statusId = implode(',', $statusId);
 454       $sqlPayment = "
 455                  SELECT min(payment.scheduled_date) as scheduled_date,
 456                         payment.pledge_id,
 457                         payment.scheduled_amount,
 458                         pledge.contact_id
 459
 460                   FROM civicrm_pledge_payment payment
 461                        LEFT JOIN civicrm_pledge pledge
 462                                  ON pledge.id = payment.pledge_id
 463
 464                   WHERE payment.status_id IN ({$statusId})
 465
 466                   GROUP BY payment.pledge_id";
 467
 468       $daoPayment = CRM_Core_DAO::executeQuery($sqlPayment);
 469
 470       while ($daoPayment->fetch()) {
 471         foreach ($pledgeIDArray as $key => $val) {
 472           if ($val == $daoPayment->pledge_id) {
 473
 474             $display[$daoPayment->pledge_id]['scheduled_date'] = $daoPayment->scheduled_date;
 475
 476             $display[$daoPayment->pledge_id]['scheduled_amount'] = $daoPayment->scheduled_amount;
 477           }
 478         }
 479       }
 480     }
 481
 482     // Displaying entire data on the form
 483     if (!empty($display)) {
 484       foreach ($display as $key => $value) {
 485         $row = array();
 486         foreach ($this->_columnHeaders as $columnKey => $columnValue) {
 487           if (array_key_exists($columnKey, $value)) {
 488             $row[$columnKey] = !empty($value[$columnKey]) ? $value[$columnKey] : '';
 489           }
 490         }
 491         $rows[] = $row;
 492       }
 493     }
 494
 495     unset($this->_columnHeaders['status_id']);
 496     unset($this->_columnHeaders['civicrm_pledge_id']);
 497     unset($this->_columnHeaders['civicrm_pledge_contact_id']);
 498
 499     $this->formatDisplay($rows, FALSE);
 500     $this->doTemplateAssignment($rows);
 501     $this->endPostProcess($rows);
 502   }
 503
 504   /**
 505    * Alter display of rows.
 506    *
 507    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 508    * such as rendering contacts as links.
 509    *
 510    * @param array $rows
 511    *   Rows generated by SQL, with an array for each row.
 512    */
 513   public function alterDisplay(&$rows) {
 514     $entryFound = FALSE;
 515     $checkList = array();
 516     $display_flag = $prev_cid = $cid = 0;
 517
 518     foreach ($rows as $rowNum => $row) {
 519       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 520         // don't repeat contact details if its same as the previous row
 521         if (array_key_exists('civicrm_pledge_contact_id', $row)) {
 522           if ($cid = $row['civicrm_pledge_contact_id']) {
 523             if ($rowNum == 0) {
 524               $prev_cid = $cid;
 525             }
 526             else {
 527               if ($prev_cid == $cid) {
 528                 $display_flag = 1;
 529                 $prev_cid = $cid;
 530               }
 531               else {
 532                 $display_flag = 0;
 533                 $prev_cid = $cid;
 534               }
 535             }
 536
 537             if ($display_flag) {
 538               foreach ($row as $colName => $colVal) {
 539                 if (in_array($colName, $this->_noRepeats)) {
 540                   unset($rows[$rowNum][$colName]);
 541                 }
 542               }
 543             }
 544             $entryFound = TRUE;
 545           }
 546         }
 547       }
 548
 549       // convert display name to links
 550       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 551         array_key_exists('civicrm_pledge_contact_id', $row)
 552       ) {
 553         $url = CRM_Utils_System::url("civicrm/contact/view",
 554           'reset=1&cid=' . $row['civicrm_pledge_contact_id'],
 555           $this->_absoluteUrl
 556         );
 557         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 558         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 559         $entryFound = TRUE;
 560       }
 561
 562       //handle status id
 563       if (array_key_exists('civicrm_pledge_status_id', $row)) {
 564         if ($value = $row['civicrm_pledge_status_id']) {
 565           $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($value);
 566         }
 567         $entryFound = TRUE;
 568       }
 569
 570       // If using campaigns, convert campaign_id to campaign title
 571       if (array_key_exists('civicrm_pledge_campaign_id', $row)) {
 572         if ($value = $row['civicrm_pledge_campaign_id']) {
 573           $rows[$rowNum]['civicrm_pledge_campaign_id'] = $this->activeCampaigns[$value];
 574         }
 575         $entryFound = TRUE;
 576       }
 577
 578       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'pledge/detail', 'List all pledge(s) for this ') ? TRUE : $entryFound;
 579
 580       // skip looking further in rows, if first row itself doesn't
 581       // have the column we need
 582       if (!$entryFound) {
 583         break;
 584       }
 585     }
 586   }
 587
 588 }