CRM/Report/Form/Member/Detail.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.4                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Member_Detail extends CRM_Report_Form {
  36
  37   protected $_addressField = FALSE;
  38
  39   protected $_emailField = FALSE;
  40
  41   protected $_phoneField = FALSE;
  42
  43   protected $_contribField = FALSE;
  44
  45   protected $_summary = NULL;
  46
  47   protected $_customGroupExtends = array('Membership', 'Contribution');
  48   protected $_customGroupGroupBy = FALSE;
  49
  50   function __construct() {
  51
  52     // Check if CiviCampaign is a) enabled and b) has active campaigns
  53   $config = CRM_Core_Config::singleton();
  54     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  55     if ($campaignEnabled) {
  56       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  57       $this->activeCampaigns = $getCampaigns['campaigns'];
  58       asort($this->activeCampaigns);
  59     }
  60
  61     $this->_columns = array(
  62       'civicrm_contact' =>
  63       array(
  64         'dao' => 'CRM_Contact_DAO_Contact',
  65         'fields' =>
  66         array(
  67           'sort_name' =>
  68           array('title' => ts('Contact Name'),
  69             'required' => TRUE,
  70             'default' => TRUE,
  71             'no_repeat' => TRUE,
  72           ),
  73           'id' =>
  74           array(
  75             'no_display' => TRUE,
  76             'required' => TRUE,
  77           ),
  78           'first_name' =>
  79           array('title' => ts('First Name'),
  80             'no_repeat' => TRUE,
  81           ),
  82           'id' =>
  83           array(
  84             'no_display' => TRUE,
  85             'required' => TRUE,
  86           ),
  87           'last_name' =>
  88           array('title' => ts('Last Name'),
  89             'no_repeat' => TRUE,
  90           ),
  91           'id' =>
  92           array(
  93             'no_display' => TRUE,
  94             'required' => TRUE,
  95           ),
  96           'contact_type' =>
  97           array(
  98             'title' => ts('Contact Type'),
  99           ),
 100           'contact_sub_type' =>
 101           array(
 102             'title' => ts('Contact SubType'),
 103           ),
 104         ),
 105         'filters' =>
 106         array(
 107           'sort_name' =>
 108           array('title' => ts('Contact Name'),
 109             'operator' => 'like',
 110           ),
 111           'id' =>
 112           array('no_display' => TRUE),
 113         ),
 114         'order_bys' =>
 115         array(
 116           'sort_name' => array(
 117             'title' => ts('Last Name, First Name'),
 118             'default' => '1',
 119             'default_weight' => '0',
 120             'default_order' => 'ASC'
 121           ),
 122         ),
 123         'grouping' => 'contact-fields',
 124       ),
 125       'civicrm_membership' =>
 126       array(
 127         'dao' => 'CRM_Member_DAO_Membership',
 128         'fields' =>
 129         array(
 130           'membership_type_id' => array(
 131             'title' => 'Membership Type',
 132             'required' => TRUE,
 133             'no_repeat' => TRUE,
 134           ),
 135           'membership_start_date' => array('title' => ts('Start Date'),
 136             'default' => TRUE,
 137           ),
 138           'membership_end_date' => array('title' => ts('End Date'),
 139             'default' => TRUE,
 140           ),
 141           'join_date' => array('title' => ts('Join Date'),
 142             'default' => TRUE,
 143           ),
 144           'source' => array('title' => 'Source'),
 145         ),
 146         'filters' => array(
 147           'join_date' =>
 148           array('operatorType' => CRM_Report_Form::OP_DATE),
 149           'membership_start_date' =>
 150           array('operatorType' => CRM_Report_Form::OP_DATE),
 151           'membership_end_date' =>
 152           array('operatorType' => CRM_Report_Form::OP_DATE),
 153           'owner_membership_id' =>
 154           array('title' => ts('Membership Owner ID'),
 155             'operatorType' => CRM_Report_Form::OP_INT,
 156           ),
 157           'tid' =>
 158           array(
 159             'name' => 'membership_type_id',
 160             'title' => ts('Membership Types'),
 161             'type' => CRM_Utils_Type::T_INT,
 162             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 163             'options' => CRM_Member_PseudoConstant::membershipType(),
 164           ),
 165         ),
 166         'grouping' => 'member-fields',
 167       ),
 168       'civicrm_membership_status' =>
 169       array(
 170         'dao' => 'CRM_Member_DAO_MembershipStatus',
 171         'alias' => 'mem_status',
 172         'fields' =>
 173         array('name' => array('title' => ts('Status'),
 174             'default' => TRUE,
 175           ),
 176         ),
 177         'filters' => array(
 178           'sid' =>
 179           array(
 180             'name' => 'id',
 181             'title' => ts('Status'),
 182             'type' => CRM_Utils_Type::T_INT,
 183             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 184             'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
 185           ),
 186         ),
 187         'grouping' => 'member-fields',
 188       ),
 189       'civicrm_address' =>
 190       array(
 191         'dao' => 'CRM_Core_DAO_Address',
 192         'fields' =>
 193         array(
 194           'street_address' => NULL,
 195           'city' => NULL,
 196           'postal_code' => NULL,
 197           'state_province_id' =>
 198           array('title' => ts('State/Province'),
 199           ),
 200           'country_id' =>
 201           array('title' => ts('Country'),
 202           ),
 203         ),
 204         'grouping' => 'contact-fields',
 205       ),
 206       'civicrm_email' =>
 207       array(
 208         'dao' => 'CRM_Core_DAO_Email',
 209         'fields' =>
 210         array('email' => NULL),
 211         'grouping' => 'contact-fields',
 212       ),
 213       'civicrm_phone' =>
 214       array(
 215         'dao' => 'CRM_Core_DAO_Phone',
 216         'fields' =>
 217         array('phone' => NULL),
 218         'grouping' => 'contact-fields',
 219       ),
 220       'civicrm_contribution' =>
 221       array(
 222         'dao' => 'CRM_Contribute_DAO_Contribution',
 223         'fields' =>
 224         array(
 225           'contribution_id' => array(
 226             'name' => 'id',
 227             'no_display' => TRUE,
 228             'required' => TRUE,
 229           ),
 230           'financial_type_id' => array('title' => ts('Financial Type')),
 231           'contribution_status_id' => array('title' => ts('Contribution Status')),
 232           'payment_instrument_id' => array('title' => ts('Payment Type')),
 233           'currency' => array(
 234             'required' => TRUE,
 235             'no_display' => TRUE,
 236           ),
 237           'trxn_id' => NULL,
 238           'receive_date' => NULL,
 239           'receipt_date' => NULL,
 240           'fee_amount' => NULL,
 241           'net_amount' => NULL,
 242           'total_amount' => array('title' => ts('Payment Amount (most recent)'),
 243             'statistics' =>
 244             array('sum' => ts('Amount')),
 245           ),
 246         ),
 247         'filters' =>
 248         array(
 249           'receive_date' =>
 250           array('operatorType' => CRM_Report_Form::OP_DATE),
 251           'financial_type_id' =>
 252           array('title' => ts('Financial Type'),
 253             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 254             'options' => CRM_Contribute_PseudoConstant::financialType(),
 255             'type' => CRM_Utils_Type::T_INT,
 256           ),
 257           'payment_instrument_id' =>
 258           array('title' => ts('Payment Type'),
 259             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 260             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 261             'type' => CRM_Utils_Type::T_INT,
 262           ),
 263           'currency' =>
 264           array('title' => 'Currency',
 265             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 266             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 267             'default' => NULL,
 268             'type' => CRM_Utils_Type::T_STRING,
 269           ),
 270           'contribution_status_id' =>
 271           array('title' => ts('Contribution Status'),
 272             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 273             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 274             'type' => CRM_Utils_Type::T_INT,
 275           ),
 276           'total_amount' =>
 277           array('title' => ts('Contribution Amount')),
 278         ),
 279         'grouping' => 'contri-fields',
 280       ),
 281     );
 282     $this->_groupFilter = TRUE;
 283     $this->_tagFilter = TRUE;
 284
 285   // If we have active campaigns add those elements to both the fields and filters
 286     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 287       $this->_columns['civicrm_membership']['fields']['campaign_id'] = array(
 288         'title' => ts('Campaign'),
 289         'default' => 'false',
 290       );
 291       $this->_columns['civicrm_membership']['filters']['campaign_id'] = array('title' => ts('Campaign'),
 292         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 293         'options' => $this->activeCampaigns,
 294       );
 295       $this->_columns['civicrm_membership']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
 296
 297     }
 298
 299     $this->_currencyColumn = 'civicrm_contribution_currency';
 300     parent::__construct();
 301   }
 302
 303   function preProcess() {
 304     $this->assign('reportTitle', ts('Membership Detail Report'));
 305     parent::preProcess();
 306   }
 307
 308   function select() {
 309     $select = $this->_columnHeaders = array();
 310
 311     foreach ($this->_columns as $tableName => $table) {
 312       if (array_key_exists('fields', $table)) {
 313         foreach ($table['fields'] as $fieldName => $field) {
 314           if (CRM_Utils_Array::value('required', $field) ||
 315             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 316           ) {
 317             if ($tableName == 'civicrm_address') {
 318               $this->_addressField = TRUE;
 319             }
 320             elseif ($tableName == 'civicrm_email') {
 321               $this->_emailField = TRUE;
 322             }
 323             elseif ($tableName == 'civicrm_phone') {
 324               $this->_phoneField = TRUE;
 325             }
 326             elseif ($tableName == 'civicrm_contribution') {
 327               $this->_contribField = TRUE;
 328             }
 329             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 330             if (array_key_exists('title', $field)) {
 331               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 332             }
 333             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 334           }
 335         }
 336       }
 337     }
 338
 339     $this->_select = "SELECT " . implode(', ', $select) . " ";
 340   }
 341
 342   function from() {
 343     $this->_from = NULL;
 344
 345     $this->_from = "
 346          FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 347                INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 348                           ON {$this->_aliases['civicrm_contact']}.id =
 349                              {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
 350                LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 351                           ON {$this->_aliases['civicrm_membership_status']}.id =
 352                              {$this->_aliases['civicrm_membership']}.status_id ";
 353
 354
 355     //used when address field is selected
 356     if ($this->_addressField) {
 357       $this->_from .= "
 358              LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 359                        ON {$this->_aliases['civicrm_contact']}.id =
 360                           {$this->_aliases['civicrm_address']}.contact_id AND
 361                           {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 362     }
 363     //used when email field is selected
 364     if ($this->_emailField) {
 365       $this->_from .= "
 366               LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 367                         ON {$this->_aliases['civicrm_contact']}.id =
 368                            {$this->_aliases['civicrm_email']}.contact_id AND
 369                            {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 370     }
 371     //used when phone field is selected
 372     if ($this->_phoneField) {
 373       $this->_from .= "
 374               LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 375                         ON {$this->_aliases['civicrm_contact']}.id =
 376                            {$this->_aliases['civicrm_phone']}.contact_id AND
 377                            {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
 378     }
 379     //used when contribution field is selected
 380     if ($this->_contribField) {
 381       $this->_from .= "
 382               LEFT JOIN (
 383                   SELECT cc.*, cmp.membership_id as membership_id
 384                   FROM civicrm_membership_payment cmp
 385                     JOIN civicrm_contribution cc
 386                       ON cc.id = cmp.contribution_id
 387                   ORDER BY cc.receive_date DESC
 388                   ) {$this->_aliases['civicrm_contribution']}
 389                 ON {$this->_aliases['civicrm_membership']}.id =
 390                   {$this->_aliases['civicrm_contribution']}.membership_id\n";
 391     }
 392   }
 393
 394   function groupBy() {
 395     $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
 396   }
 397
 398   function orderBy() {
 399     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
 400
 401     if ($this->_contribField) {
 402       $this->_orderBy .= ", {$this->_aliases['civicrm_contribution']}.receive_date DESC";
 403     }
 404   }
 405
 406   function postProcess() {
 407
 408     $this->beginPostProcess();
 409
 410     // get the acl clauses built before we assemble the query
 411     $this->buildACLClause($this->_aliases['civicrm_contact']);
 412     $sql = $this->buildQuery(TRUE);
 413
 414     $rows = array();
 415     $this->buildRows($sql, $rows);
 416
 417     $this->formatDisplay($rows);
 418     $this->doTemplateAssignment($rows);
 419     $this->endPostProcess($rows);
 420   }
 421
 422   function alterDisplay(&$rows) {
 423     // custom code to alter rows
 424     $entryFound = FALSE;
 425     $checkList = array();
 426
 427     $contributionTypes  = CRM_Contribute_PseudoConstant::financialType();
 428     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 429     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 430
 431     foreach ($rows as $rowNum => $row) {
 432
 433       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 434         // not repeat contact display names if it matches with the one
 435         // in previous row
 436         $repeatFound = FALSE;
 437         foreach ($row as $colName => $colVal) {
 438           if (CRM_Utils_Array::value($colName, $checkList) &&
 439             is_array($checkList[$colName]) &&
 440             in_array($colVal, $checkList[$colName])
 441           ) {
 442             $rows[$rowNum][$colName] = "";
 443             $repeatFound = TRUE;
 444           }
 445           if (in_array($colName, $this->_noRepeats)) {
 446             $checkList[$colName][] = $colVal;
 447           }
 448         }
 449       }
 450
 451       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 452         if ($value = $row['civicrm_membership_membership_type_id']) {
 453           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 454         }
 455         $entryFound = TRUE;
 456       }
 457
 458       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 459         if ($value = $row['civicrm_address_state_province_id']) {
 460           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 461         }
 462         $entryFound = TRUE;
 463       }
 464
 465       if (array_key_exists('civicrm_address_country_id', $row)) {
 466         if ($value = $row['civicrm_address_country_id']) {
 467           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 468         }
 469         $entryFound = TRUE;
 470       }
 471
 472       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 473         $rows[$rowNum]['civicrm_contact_sort_name'] &&
 474         array_key_exists('civicrm_contact_id', $row)
 475       ) {
 476         $url = CRM_Utils_System::url("civicrm/contact/view",
 477           'reset=1&cid=' . $row['civicrm_contact_id'],
 478           $this->_absoluteUrl
 479         );
 480         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 481         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 482         $entryFound = TRUE;
 483       }
 484
 485       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 486         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 487         $entryFound = TRUE;
 488       }
 489       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 490         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 491         $entryFound = TRUE;
 492       }
 493       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 494         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 495         $entryFound = TRUE;
 496       }
 497
 498       // Convert campaign_id to campaign title
 499       if (array_key_exists('civicrm_membership_campaign_id', $row)) {
 500         if ($value = $row['civicrm_membership_campaign_id']) {
 501           $rows[$rowNum]['civicrm_membership_campaign_id'] = $this->activeCampaigns[$value];
 502           $entryFound = TRUE;
 503         }
 504       }
 505
 506       if (!$entryFound) {
 507         break;
 508       }
 509     }
 510   }
 511 }
 512