Detail.php 4.46 KB
Newer Older
bgm's avatar
bgm committed
1 2 3 4 5 6 7 8 9
<?php

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2010
 *            $Id$
 *
 */
Mathieu Lutfy's avatar
Mathieu Lutfy committed
10
use CRM_Extendedreport_ExtensionUtil as E;
bgm's avatar
bgm committed
11 12 13 14 15 16 17 18

class CRM_Extendedreport_Form_Report_Pledge_Detail extends CRM_Extendedreport_Form_Report_ExtendedReport {
  protected $_summary = NULL;
  protected $_totalPaid = FALSE;
  protected $_customGroupExtends = array(
    'Pledge',
  );
  protected $_customGroupGroupBy = TRUE;
19
  protected $_baseTable = 'civicrm_pledge';
bgm's avatar
bgm committed
20 21 22 23 24 25 26 27 28

  function __construct() {
    $this->_columns = $this->getColumns('Contact', array(
          'fields' => TRUE,
          'order_by' => TRUE,
        )
      ) + $this->getColumns('Contact')
      + $this->getColumns('Email')
      + $this->getColumns('Pledge', array('group_bys' => FALSE))
29
      + $this->getColumns('PledgePayment', array('fields_defaults' => array('actual_amount')))
bgm's avatar
bgm committed
30
      + $this->getColumns('FinancialType');
Mathieu Lutfy's avatar
Mathieu Lutfy committed
31 32
    unset($this->_columns['civicrm_pledge_payment']['fields']['status_id']);
    $this->_columns['civicrm_pledge_payment']['metadata']['balance_amount'] = [
bgm's avatar
bgm committed
33 34 35
      'title' => ts('Balance to Pay'),
      'statistics' => array('sum' => ts('Balance')),
      'type' => CRM_Utils_Type::T_MONEY,
Mathieu Lutfy's avatar
Mathieu Lutfy committed
36 37 38 39 40
      'is_fields' => TRUE,
      'is_filters' => FALSE,
      'is_order_bys' => FALSE,
      'is_group_bys' => FALSE,
      'is_join_filters' => FALSE,
Mathieu Lutfy's avatar
Mathieu Lutfy committed
41 42
      'is_aggregate_columns' => FALSE,
      'is_aggregate_rows' => FALSE,
Mathieu Lutfy's avatar
Mathieu Lutfy committed
43 44 45 46 47 48
      'alias' => 'pledge_balance_amount'
    ];
    $this->_columns['civicrm_contribution']['group_title'] = E::ts('Report Date');
    $this->_columns['civicrm_contribution']['metadata']['effective_date'] = array(
      'type' => CRM_Utils_Type::T_DATE,
      'title' => ts('Do not consider payments or pledges after...'),
bgm's avatar
bgm committed
49 50
      'operatorType' => self::OP_SINGLEDATE,
      'pseudofield' => TRUE,
Mathieu Lutfy's avatar
Mathieu Lutfy committed
51 52 53 54 55
      'is_fields' => FALSE,
      'is_filters' => TRUE,
      'is_group_bys' => FALSE,
      'is_order_bys' => FALSE,
      'is_join_filters' => FALSE,
Mathieu Lutfy's avatar
Mathieu Lutfy committed
56 57
      'is_aggregate_columns' => FALSE,
      'is_aggregate_rows' => FALSE,
Mathieu Lutfy's avatar
Mathieu Lutfy committed
58 59
      'operations' => ['to' => E::ts('Date')],
      'alias' => 'contribution_effective_date',
bgm's avatar
bgm committed
60
    );
Mathieu Lutfy's avatar
Mathieu Lutfy committed
61

bgm's avatar
bgm committed
62 63 64 65 66
    $this->_groupFilter = TRUE;
    $this->_tagFilter = TRUE;
    $defaults = array(
      'civicrm_contact' => array('civicrm_contact_display_name', 'civicrm_contact_contact_id'),
      'civicrm_pledge' => array('pledge_amount'),
67
      'civicrm_pledge_payment' => array('balance_amount'),
bgm's avatar
bgm committed
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
    );
    foreach ($defaults as $entity => $fields) {
      foreach ($fields as $field) {
        $this->_columns[$entity]['fields'][$field]['default'] = 1;
      }
    }
    parent::__construct();
  }

  function from() {
    $this->_from = "
            FROM civicrm_pledge {$this->_aliases['civicrm_pledge']}";
    $this->joinPledgePaymentFromPledge();
    $this->_from .= " LEFT JOIN civicrm_financial_type {$this->_aliases['civicrm_financial_type']}
                      ON  ({$this->_aliases['civicrm_pledge']}.financial_type_id =
                          {$this->_aliases['civicrm_financial_type']}.id)
                 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
                      ON ({$this->_aliases['civicrm_contact']}.id =
                          {$this->_aliases['civicrm_pledge']}.contact_id )
                 {$this->_aclFrom} ";

89
    $this->joinEmailFromContact();
bgm's avatar
bgm committed
90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
  }

  /**
   * Add balance amount calculation.
   *
   * @param string $tableName
   * @param string $tableKey
   * @param string $fieldName
   * @param array $field
   *
   * @return string
   */
  function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
    if ($fieldName == 'balance_amount') {
      $alias = $this->selectStatSum($tableName, $fieldName, $field);
105 106
      return " SUM(COALESCE(IF((pledge.status_id =3), {$this->_aliases['civicrm_pledge_payment']}.actual_amount, pledge.amount), 0))
       - COALESCE(sum({$this->_aliases['civicrm_pledge_payment']}.actual_amount), 0) as $alias ";
bgm's avatar
bgm committed
107 108 109
    }
    if ($fieldName == 'pledge_amount') {
      $alias = $this->selectStatSum($tableName, $fieldName, $field);
110 111 112 113 114 115
      return " SUM(COALESCE(IF((pledge.status_id =3), {$this->_aliases['civicrm_pledge_payment']}.actual_amount, pledge.amount), 0)) as $alias ";
    }

    if ($fieldName == 'next_scheduled_amount') {
      $alias = $this->selectStatSum($tableName, $fieldName, $field);
      return " SUM(COALESCE(IF((pledge.status_id =3), {$this->_aliases['civicrm_pledge_payment']}.actual_amount, pledge.amount), 0)) as $alias ";
bgm's avatar
bgm committed
116 117 118 119 120 121
    }
    return parent::selectClause($tableName, $tableKey, $fieldName, $field);

  }

}