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              'default' =>  NULL,
 105              'type' => CRM_Utils_Type::T_STRING,
 106           ),
 107                                  'financial_type_id' =>
 108                                  array( 'title'        =>  ts('Financial Type'),
 109             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 110                                         'options'      => CRM_Contribute_PseudoConstant::financialType(),
 111           ),
 112         ),
 113         'grouping' => 'pledge-fields',
 114       ),
 115       'civicrm_pledge_payment' =>
 116       array(
 117         'dao' => 'CRM_Pledge_DAO_PledgePayment',
 118         'fields' =>
 119         array(
 120           'scheduled_date' =>
 121           array('title' => ts('Next Payment Due'),
 122             'type' => CRM_Utils_Type::T_DATE,
 123             'required' => TRUE,
 124           ),
 125         ),
 126         'grouping' => 'pledge-fields',
 127       ),
 128       'civicrm_address' =>
 129       array(
 130         'dao' => 'CRM_Core_DAO_Address',
 131         'fields' =>
 132         array(
 133           'street_address' => NULL,
 134           'city' => NULL,
 135           'postal_code' => NULL,
 136           'state_province_id' =>
 137           array('title' => ts('State/Province'),
 138           ),
 139           'country_id' =>
 140           array('title' => ts('Country'),
 141             'default' => TRUE,
 142           ),
 143         ),
 144         'grouping' => 'contact-fields',
 145       ),
 146       'civicrm_email' =>
 147       array(
 148         'dao' => 'CRM_Core_DAO_Email',
 149         'fields' =>
 150         array('email' => NULL),
 151         'grouping' => 'contact-fields',
 152       ),
 153       'civicrm_group' =>
 154       array(
 155         'dao' => 'CRM_Contact_DAO_Group',
 156         'alias' => 'cgroup',
 157         'filters' =>
 158         array(
 159           'gid' =>
 160           array(
 161             'name' => 'group_id',
 162             'title' => ts('Group'),
 163             'group' => TRUE,
 164             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 165             'options' => CRM_Core_PseudoConstant::staticGroup(),
 166           ),
 167         ),
 168       ),
 169     );
 170
 171     $this->_tagFilter = TRUE;
 172     $this->_currencyColumn = 'civicrm_pledge_currency';
 173     parent::__construct();
 174   }
 175
 176   function preProcess() {
 177     $this->assign('reportTitle', ts('Pledge But Not Paid Report'));
 178     parent::preProcess();
 179   }
 180
 181   function select() {
 182     $select = array();
 183     $this->_columnHeaders = array();
 184     foreach ($this->_columns as $tableName => $table) {
 185       if (array_key_exists('fields', $table)) {
 186         foreach ($table['fields'] as $fieldName => $field) {
 187           if (CRM_Utils_Array::value('required', $field) ||
 188             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 189           ) {
 190             // to include optional columns address and email, only if checked
 191             if ($tableName == 'civicrm_address') {
 192               $this->_addressField = TRUE;
 193               $this->_emailField = TRUE;
 194             }
 195             elseif ($tableName == 'civicrm_email') {
 196               $this->_emailField = TRUE;
 197             }
 198             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 199             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 200             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 201           }
 202         }
 203       }
 204     }
 205     $this->_select = "SELECT " . implode(', ', $select) . " ";
 206   }
 207
 208   function from() {
 209     $this->_from = NULL;
 210
 211     $allStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 212     $pendingStatus = array_search('Pending', $allStatus);
 213     foreach (array(
 214       'Pending', 'In Progress', 'Overdue') as $statusKey) {
 215       if ($key = CRM_Utils_Array::key($statusKey, $allStatus)) {
 216         $unpaidStatus[] = $key;
 217       }
 218     }
 219
 220     $statusIds = implode(', ', $unpaidStatus);
 221
 222     $this->_from = "
 223         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 224              INNER JOIN civicrm_pledge  {$this->_aliases['civicrm_pledge']}
 225                         ON ({$this->_aliases['civicrm_pledge']}.contact_id =
 226                             {$this->_aliases['civicrm_contact']}.id)  AND
 227                             {$this->_aliases['civicrm_pledge']}.status_id IN ( {$statusIds} )
 228              LEFT  JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']}
 229                         ON ({$this->_aliases['civicrm_pledge']}.id =
 230                             {$this->_aliases['civicrm_pledge_payment']}.pledge_id AND  {$this->_aliases['civicrm_pledge_payment']}.status_id = {$pendingStatus} ) ";
 231
 232     // include address field if address column is to be included
 233     if ($this->_addressField) {
 234       $this->_from .= "
 235              LEFT  JOIN civicrm_address {$this->_aliases['civicrm_address']}
 236                         ON ({$this->_aliases['civicrm_contact']}.id =
 237                             {$this->_aliases['civicrm_address']}.contact_id) AND
 238                             {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 239     }
 240
 241     // include email field if email column is to be included
 242     if ($this->_emailField) {
 243       $this->_from .= "
 244             LEFT  JOIN civicrm_email {$this->_aliases['civicrm_email']}
 245                        ON ({$this->_aliases['civicrm_contact']}.id =
 246                            {$this->_aliases['civicrm_email']}.contact_id) AND
 247                            {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 248     }
 249   }
 250
 251   function groupBy() {
 252     $this->_groupBy = "
 253          GROUP BY {$this->_aliases['civicrm_pledge']}.contact_id,
 254                   {$this->_aliases['civicrm_pledge']}.id,
 255                   {$this->_aliases['civicrm_pledge']}.currency";
 256   }
 257
 258   function orderBy() {
 259     $this->_orderBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_pledge']}.contact_id, {$this->_aliases['civicrm_pledge']}.id";
 260   }
 261
 262   function postProcess() {
 263     // get the acl clauses built before we assemble the query
 264     $this->buildACLClause($this->_aliases['civicrm_contact']);
 265     parent::PostProcess();
 266   }
 267
 268   function alterDisplay(&$rows) {
 269     // custom code to alter rows
 270     $entryFound   = FALSE;
 271     $checkList    = array();
 272     $display_flag = $prev_cid = $cid = 0;
 273
 274     foreach ($rows as $rowNum => $row) {
 275       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 276         // don't repeat contact details if its same as the previous row
 277         if (array_key_exists('civicrm_contact_id', $row)) {
 278           if ($cid = $row['civicrm_contact_id']) {
 279             if ($rowNum == 0) {
 280               $prev_cid = $cid;
 281             }
 282             else {
 283               if ($prev_cid == $cid) {
 284                 $display_flag = 1;
 285                 $prev_cid = $cid;
 286               }
 287               else {
 288                 $display_flag = 0;
 289                 $prev_cid = $cid;
 290               }
 291             }
 292
 293             if ($display_flag) {
 294               foreach ($row as $colName => $colVal) {
 295                 if (in_array($colName, $this->_noRepeats)) {
 296                   unset($rows[$rowNum][$colName]);
 297                 }
 298               }
 299             }
 300             $entryFound = TRUE;
 301           }
 302         }
 303       }
 304
 305             //handle the Financial Type Ids
 306       if (array_key_exists('civicrm_pledge_contribution_type_id', $row)) {
 307         if ($value = $row['civicrm_pledge_contribution_type_id']) {
 308                     $rows[$rowNum]['civicrm_pledge_contribution_type_id'] =
 309                         CRM_Contribute_PseudoConstant::financialType( $value, false );
 310         }
 311         $entryFound = TRUE;
 312       }
 313
 314       //handle the Status Ids
 315       if (array_key_exists('civicrm_pledge_status_id', $row)) {
 316         if ($value = $row['civicrm_pledge_status_id']) {
 317           $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($value);
 318         }
 319         $entryFound = TRUE;
 320       }
 321
 322       // handle state province
 323       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 324         if ($value = $row['civicrm_address_state_province_id']) {
 325           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvinceAbbreviation($value, FALSE);
 326         }
 327         $entryFound = TRUE;
 328       }
 329
 330       // handle country
 331       if (array_key_exists('civicrm_address_country_id', $row)) {
 332         if ($value = $row['civicrm_address_country_id']) {
 333           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 334         }
 335         $entryFound = TRUE;
 336       }
 337
 338       // convert display name to links
 339       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 340         array_key_exists('civicrm_contact_id', $row)
 341       ) {
 342         $url = CRM_Report_Utils_Report::getNextUrl('pledge/summary',
 343           'reset=1&force=1&id_op=eq&id_value=' .
 344           $row['civicrm_contact_id'],
 345           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 346         );
 347         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 348         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Pledge Details for this contact");
 349         $entryFound = TRUE;
 350       }
 351
 352       // skip looking further in rows, if first row itself doesn't
 353       // have the column we need
 354       if (!$entryFound) {
 355         break;
 356       }
 357     }
 358   }
 359 }
 360