CRM/Report/Form/Pledge/Pbnp.php

   1 <?php
   2 // $Id$
   3
   4 /*
   5  +--------------------------------------------------------------------+
   6  | CiviCRM version 4.3                                                |
   7  +--------------------------------------------------------------------+
   8  | Copyright CiviCRM LLC (c) 2004-2013                                |
   9  +--------------------------------------------------------------------+
  10  | This file is a part of CiviCRM.                                    |
  11  |                                                                    |
  12  | CiviCRM is free software; you can copy, modify, and distribute it  |
  13  | under the terms of the GNU Affero General Public License           |
  14  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  15  |                                                                    |
  16  | CiviCRM is distributed in the hope that it will be useful, but     |
  17  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  18  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  19  | See the GNU Affero General Public License for more details.        |
  20  |                                                                    |
  21  | You should have received a copy of the GNU Affero General Public   |
  22  | License and the CiviCRM Licensing Exception along                  |
  23  | with this program; if not, contact CiviCRM LLC                     |
  24  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  25  | GNU Affero General Public License or the licensing of CiviCRM,     |
  26  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  27  +--------------------------------------------------------------------+
  28 */
  29
  30 /**
  31  *
  32  * @package CRM
  33  * @copyright CiviCRM LLC (c) 2004-2013
  34  * $Id$
  35  *
  36  */
  37 class CRM_Report_Form_Pledge_Pbnp extends CRM_Report_Form {
  38   protected $_charts = array(
  39     '' => 'Tabular',
  40     'barChart' => 'Bar Chart',
  41     'pieChart' => 'Pie Chart',
  42   );
  43   public $_drilldownReport = array('pledge/summary' => 'Link to Detail Report');
  44
  45   protected $_customGroupExtends = array(
  46     'Pledge');
  47
  48   function __construct() {
  49     $this->_columns = array(
  50       'civicrm_contact' =>
  51       array(
  52         'dao' => 'CRM_Contact_DAO_Contact',
  53         'fields' =>
  54         array(
  55           'sort_name' =>
  56           array('title' => ts('Constituent Name'),
  57             'required' => TRUE,
  58             'no_repeat' => TRUE,
  59           ),
  60           'id' =>
  61           array(
  62             'no_display' => TRUE,
  63             'required' => TRUE,
  64           ),
  65         ),
  66         'grouping' => 'contact-fields',
  67       ),
  68       'civicrm_pledge' =>
  69       array(
  70         'dao' => 'CRM_Pledge_DAO_Pledge',
  71         'fields' =>
  72         array(
  73           'pledge_create_date' =>
  74           array('title' => ts('Pledge Made'),
  75             'required' => TRUE,
  76           ),
  77           'financial_type_id' =>
  78           array('title' => ts('Financial Type'),
  79             'requried' => TRUE,
  80           ),
  81           'amount' =>
  82           array('title' => ts('Amount'),
  83             'required' => TRUE,
  84             'type' => CRM_Utils_Type::T_MONEY,
  85           ),
  86           'currency' =>
  87           array(
  88             'required' => TRUE,
  89             'no_display' => TRUE,
  90           ),
  91           'status_id' =>
  92           array('title' => ts('Status'),
  93           ),
  94         ),
  95         'filters' =>
  96         array(
  97           'pledge_create_date' =>
  98           array(
  99             'title' => 'Pledge Made',
 100             'operatorType' => CRM_Report_Form::OP_DATE,
 101           ),
 102           'currency' =>
 103           array('title' => 'Currency',
 104             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 105             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 106             'default' => NULL,
 107             'type' => CRM_Utils_Type::T_STRING,
 108           ),
 109           'financial_type_id' =>
 110           array( 'title'   => ts('Financial Type'),
 111             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 112             'options'      => CRM_Contribute_PseudoConstant::financialType(),
 113           ),
 114         ),
 115         'grouping' => 'pledge-fields',
 116       ),
 117       'civicrm_pledge_payment' =>
 118       array(
 119         'dao' => 'CRM_Pledge_DAO_PledgePayment',
 120         'fields' =>
 121         array(
 122           'scheduled_date' =>
 123           array('title' => ts('Next Payment Due'),
 124             'type' => CRM_Utils_Type::T_DATE,
 125             'required' => TRUE,
 126           ),
 127         ),
 128         'grouping' => 'pledge-fields',
 129       ),
 130       'civicrm_address' =>
 131       array(
 132         'dao' => 'CRM_Core_DAO_Address',
 133         'fields' =>
 134         array(
 135           'street_address' => NULL,
 136           'city' => NULL,
 137           'postal_code' => NULL,
 138           'state_province_id' =>
 139           array('title' => ts('State/Province'),
 140           ),
 141           'country_id' =>
 142           array('title' => ts('Country'),
 143             'default' => TRUE,
 144           ),
 145         ),
 146         'grouping' => 'contact-fields',
 147       ),
 148       'civicrm_email' =>
 149       array(
 150         'dao' => 'CRM_Core_DAO_Email',
 151         'fields' =>
 152         array('email' => NULL),
 153         'grouping' => 'contact-fields',
 154       ),
 155       'civicrm_group' =>
 156       array(
 157         'dao' => 'CRM_Contact_DAO_Group',
 158         'alias' => 'cgroup',
 159         'filters' =>
 160         array(
 161           'gid' =>
 162           array(
 163             'name' => 'group_id',
 164             'title' => ts('Group'),
 165             'group' => TRUE,
 166             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 167             'options' => CRM_Core_PseudoConstant::staticGroup(),
 168           ),
 169         ),
 170       ),
 171     );
 172
 173     $this->_tagFilter = TRUE;
 174     $this->_currencyColumn = 'civicrm_pledge_currency';
 175     parent::__construct();
 176   }
 177
 178   function preProcess() {
 179     $this->assign('reportTitle', ts('Pledge But Not Paid Report'));
 180     parent::preProcess();
 181   }
 182
 183   function select() {
 184     $select = array();
 185     $this->_columnHeaders = array();
 186     foreach ($this->_columns as $tableName => $table) {
 187       if (array_key_exists('fields', $table)) {
 188         foreach ($table['fields'] as $fieldName => $field) {
 189           if (CRM_Utils_Array::value('required', $field) ||
 190             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 191           ) {
 192             // to include optional columns address and email, only if checked
 193             if ($tableName == 'civicrm_address') {
 194               $this->_addressField = TRUE;
 195               $this->_emailField = TRUE;
 196             }
 197             elseif ($tableName == 'civicrm_email') {
 198               $this->_emailField = TRUE;
 199             }
 200             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 201             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 202             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 203           }
 204         }
 205       }
 206     }
 207     $this->_select = "SELECT " . implode(', ', $select) . " ";
 208   }
 209
 210   function from() {
 211     $this->_from = NULL;
 212
 213     $allStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 214     $pendingStatus = array_search('Pending', $allStatus);
 215     foreach (array(
 216       'Pending', 'In Progress', 'Overdue') as $statusKey) {
 217       if ($key = CRM_Utils_Array::key($statusKey, $allStatus)) {
 218         $unpaidStatus[] = $key;
 219       }
 220     }
 221
 222     $statusIds = implode(', ', $unpaidStatus);
 223
 224     $this->_from = "
 225         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 226              INNER JOIN civicrm_pledge  {$this->_aliases['civicrm_pledge']}
 227                         ON ({$this->_aliases['civicrm_pledge']}.contact_id =
 228                             {$this->_aliases['civicrm_contact']}.id)  AND
 229                             {$this->_aliases['civicrm_pledge']}.status_id IN ( {$statusIds} )
 230              LEFT  JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']}
 231                         ON ({$this->_aliases['civicrm_pledge']}.id =
 232                             {$this->_aliases['civicrm_pledge_payment']}.pledge_id AND  {$this->_aliases['civicrm_pledge_payment']}.status_id = {$pendingStatus} ) ";
 233
 234     // include address field if address column is to be included
 235     if ($this->_addressField) {
 236       $this->_from .= "
 237              LEFT  JOIN civicrm_address {$this->_aliases['civicrm_address']}
 238                         ON ({$this->_aliases['civicrm_contact']}.id =
 239                             {$this->_aliases['civicrm_address']}.contact_id) AND
 240                             {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 241     }
 242
 243     // include email field if email column is to be included
 244     if ($this->_emailField) {
 245       $this->_from .= "
 246             LEFT  JOIN civicrm_email {$this->_aliases['civicrm_email']}
 247                        ON ({$this->_aliases['civicrm_contact']}.id =
 248                            {$this->_aliases['civicrm_email']}.contact_id) AND
 249                            {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 250     }
 251   }
 252
 253   function groupBy() {
 254     $this->_groupBy = "
 255          GROUP BY {$this->_aliases['civicrm_pledge']}.contact_id,
 256                   {$this->_aliases['civicrm_pledge']}.id,
 257                   {$this->_aliases['civicrm_pledge']}.currency";
 258   }
 259
 260   function orderBy() {
 261     $this->_orderBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_pledge']}.contact_id, {$this->_aliases['civicrm_pledge']}.id";
 262   }
 263
 264   function postProcess() {
 265     // get the acl clauses built before we assemble the query
 266     $this->buildACLClause($this->_aliases['civicrm_contact']);
 267     parent::PostProcess();
 268   }
 269
 270   function alterDisplay(&$rows) {
 271     // custom code to alter rows
 272     $entryFound   = FALSE;
 273     $checkList    = array();
 274     $display_flag = $prev_cid = $cid = 0;
 275
 276     foreach ($rows as $rowNum => $row) {
 277       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 278         // don't repeat contact details if its same as the previous row
 279         if (array_key_exists('civicrm_contact_id', $row)) {
 280           if ($cid = $row['civicrm_contact_id']) {
 281             if ($rowNum == 0) {
 282               $prev_cid = $cid;
 283             }
 284             else {
 285               if ($prev_cid == $cid) {
 286                 $display_flag = 1;
 287                 $prev_cid = $cid;
 288               }
 289               else {
 290                 $display_flag = 0;
 291                 $prev_cid = $cid;
 292               }
 293             }
 294
 295             if ($display_flag) {
 296               foreach ($row as $colName => $colVal) {
 297                 if (in_array($colName, $this->_noRepeats)) {
 298                   unset($rows[$rowNum][$colName]);
 299                 }
 300               }
 301             }
 302             $entryFound = TRUE;
 303           }
 304         }
 305       }
 306
 307       //handle the Financial Type Ids
 308       if (array_key_exists('civicrm_pledge_financial_type_id', $row)) {
 309         if ($value = $row['civicrm_pledge_financial_type_id']) {
 310           $rows[$rowNum]['civicrm_pledge_financial_type_id'] =
 311             CRM_Contribute_PseudoConstant::financialType($value, false);
 312         }
 313         $entryFound = TRUE;
 314       }
 315
 316       //handle the Status Ids
 317       if (array_key_exists('civicrm_pledge_status_id', $row)) {
 318         if ($value = $row['civicrm_pledge_status_id']) {
 319           $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($value);
 320         }
 321         $entryFound = TRUE;
 322       }
 323
 324       // handle state province
 325       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 326         if ($value = $row['civicrm_address_state_province_id']) {
 327           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvinceAbbreviation($value, FALSE);
 328         }
 329         $entryFound = TRUE;
 330       }
 331
 332       // handle country
 333       if (array_key_exists('civicrm_address_country_id', $row)) {
 334         if ($value = $row['civicrm_address_country_id']) {
 335           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 336         }
 337         $entryFound = TRUE;
 338       }
 339
 340       // convert display name to links
 341       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 342         array_key_exists('civicrm_contact_id', $row)
 343       ) {
 344         $url = CRM_Report_Utils_Report::getNextUrl('pledge/summary',
 345           'reset=1&force=1&id_op=eq&id_value=' .
 346           $row['civicrm_contact_id'],
 347           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 348         );
 349         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 350         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Pledge Details for this contact");
 351         $entryFound = TRUE;
 352       }
 353
 354       // skip looking further in rows, if first row itself doesn't
 355       // have the column we need
 356       if (!$entryFound) {
 357         break;
 358       }
 359     }
 360   }
 361 }
 362