CRM/Report/Form/Member/Detail.php

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