CRM/Report/Form/Member/Detail.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  32  */
  33 class CRM_Report_Form_Member_Detail extends CRM_Report_Form {
  34
  35   protected $_summary = NULL;
  36
  37   protected $_customGroupExtends = array(
  38     'Membership',
  39     'Contribution',
  40     'Contact',
  41     'Individual',
  42     'Household',
  43     'Organization',
  44   );
  45
  46   protected $_customGroupGroupBy = FALSE;
  47
  48   /**
  49    * This report has not been optimised for group filtering.
  50    *
  51    * The functionality for group filtering has been improved but not
  52    * all reports have been adjusted to take care of it. This report has not
  53    * and will run an inefficient query until fixed.
  54    *
  55    * CRM-19170
  56    *
  57    * @var bool
  58    */
  59   protected $groupFilterNotOptimised = FALSE;
  60
  61   /**
  62    * Class constructor.
  63    */
  64   public function __construct() {
  65
  66     // Check if CiviCampaign is a) enabled and b) has active campaigns
  67     $config = CRM_Core_Config::singleton();
  68     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  69     if ($campaignEnabled) {
  70       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  71       $this->activeCampaigns = $getCampaigns['campaigns'];
  72       asort($this->activeCampaigns);
  73     }
  74
  75     $this->_columns = array(
  76       'civicrm_contact' => array(
  77         'dao' => 'CRM_Contact_DAO_Contact',
  78         'fields' => $this->getBasicContactFields(),
  79         'filters' => array(
  80           'sort_name' => array(
  81             'title' => ts('Contact Name'),
  82             'operator' => 'like',
  83           ),
  84           'is_deleted' => array(
  85             'title' => ts('Is Deleted'),
  86             'default' => 0,
  87             'type' => CRM_Utils_Type::T_BOOLEAN,
  88           ),
  89           'id' => array('no_display' => TRUE),
  90         ),
  91         'order_bys' => array(
  92           'sort_name' => array(
  93             'title' => ts('Last Name, First Name'),
  94             'default' => '1',
  95             'default_weight' => '0',
  96             'default_order' => 'ASC',
  97           ),
  98         ),
  99         'grouping' => 'contact-fields',
 100       ),
 101       'civicrm_membership' => array(
 102         'dao' => 'CRM_Member_DAO_Membership',
 103         'fields' => array(
 104           'membership_type_id' => array(
 105             'title' => ts('Membership Type'),
 106             'required' => TRUE,
 107             'no_repeat' => TRUE,
 108           ),
 109           'membership_start_date' => array(
 110             'title' => ts('Start Date'),
 111             'default' => TRUE,
 112           ),
 113           'membership_end_date' => array(
 114             'title' => ts('End Date'),
 115             'default' => TRUE,
 116           ),
 117           'join_date' => array(
 118             'title' => ts('Join Date'),
 119             'default' => TRUE,
 120           ),
 121           'source' => array('title' => ts('Source')),
 122         ),
 123         'filters' => array(
 124           'join_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 125           'membership_start_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 126           'membership_end_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 127           'owner_membership_id' => array(
 128             'title' => ts('Membership Owner ID'),
 129             'operatorType' => CRM_Report_Form::OP_INT,
 130           ),
 131           'tid' => array(
 132             'name' => 'membership_type_id',
 133             'title' => ts('Membership Types'),
 134             'type' => CRM_Utils_Type::T_INT,
 135             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 136             'options' => CRM_Member_PseudoConstant::membershipType(),
 137           ),
 138         ),
 139         'order_bys' => array(
 140           'membership_type_id' => array(
 141             'title' => ts('Membership Type'),
 142             'default' => '0',
 143             'default_weight' => '1',
 144             'default_order' => 'ASC',
 145           ),
 146         ),
 147         'grouping' => 'member-fields',
 148         'group_bys' => array(
 149           'id' => array(
 150             'title' => ts('Membership'),
 151             'default' => TRUE,
 152           ),
 153         ),
 154       ),
 155       'civicrm_membership_status' => array(
 156         'dao' => 'CRM_Member_DAO_MembershipStatus',
 157         'alias' => 'mem_status',
 158         'fields' => array(
 159           'name' => array(
 160             'title' => ts('Status'),
 161             'default' => TRUE,
 162           ),
 163         ),
 164         'filters' => array(
 165           'sid' => array(
 166             'name' => 'id',
 167             'title' => ts('Status'),
 168             'type' => CRM_Utils_Type::T_INT,
 169             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 170             'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
 171           ),
 172         ),
 173         'grouping' => 'member-fields',
 174       ),
 175       'civicrm_email' => array(
 176         'dao' => 'CRM_Core_DAO_Email',
 177         'fields' => array('email' => NULL),
 178         'grouping' => 'contact-fields',
 179       ),
 180       'civicrm_phone' => array(
 181         'dao' => 'CRM_Core_DAO_Phone',
 182         'fields' => array('phone' => NULL),
 183         'grouping' => 'contact-fields',
 184       ),
 185       'civicrm_contribution' => array(
 186         'dao' => 'CRM_Contribute_DAO_Contribution',
 187         'fields' => array(
 188           'contribution_id' => array(
 189             'name' => 'id',
 190             'no_display' => TRUE,
 191             'required' => TRUE,
 192           ),
 193           'financial_type_id' => array('title' => ts('Financial Type')),
 194           'contribution_status_id' => array('title' => ts('Contribution Status')),
 195           'payment_instrument_id' => array('title' => ts('Payment Type')),
 196           'currency' => array(
 197             'required' => TRUE,
 198             'no_display' => TRUE,
 199           ),
 200           'trxn_id' => NULL,
 201           'receive_date' => NULL,
 202           'receipt_date' => NULL,
 203           'fee_amount' => NULL,
 204           'net_amount' => NULL,
 205           'total_amount' => array(
 206             'title' => ts('Payment Amount (most recent)'),
 207             'statistics' => array('sum' => ts('Amount')),
 208           ),
 209         ),
 210         'filters' => array(
 211           'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 212           'financial_type_id' => array(
 213             'title' => ts('Financial Type'),
 214             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 215             'options' => CRM_Contribute_PseudoConstant::financialType(),
 216             'type' => CRM_Utils_Type::T_INT,
 217           ),
 218           'payment_instrument_id' => array(
 219             'title' => ts('Payment Type'),
 220             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 221             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 222             'type' => CRM_Utils_Type::T_INT,
 223           ),
 224           'currency' => array(
 225             'title' => ts('Currency'),
 226             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 227             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 228             'default' => NULL,
 229             'type' => CRM_Utils_Type::T_STRING,
 230           ),
 231           'contribution_status_id' => array(
 232             'title' => ts('Contribution Status'),
 233             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 234             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 235             'type' => CRM_Utils_Type::T_INT,
 236           ),
 237           'total_amount' => array('title' => ts('Contribution Amount')),
 238         ),
 239         'order_bys' => array(
 240           'receive_date' => array(
 241             'title' => ts('Date Received'),
 242             'default_weight' => '2',
 243             'default_order' => 'DESC',
 244           ),
 245         ),
 246         'grouping' => 'contri-fields',
 247       ),
 248     ) + $this->getAddressColumns(array(
 249       // These options are only excluded because they were not previously present.
 250       'order_by' => FALSE,
 251       'group_by' => FALSE,
 252     ));
 253     $this->_groupFilter = TRUE;
 254     $this->_tagFilter = TRUE;
 255
 256     // If we have active campaigns add those elements to both the fields and filters
 257     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 258       $this->_columns['civicrm_membership']['fields']['campaign_id'] = array(
 259         'title' => ts('Campaign'),
 260         'default' => 'false',
 261       );
 262       $this->_columns['civicrm_membership']['filters']['campaign_id'] = array(
 263         'title' => ts('Campaign'),
 264         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 265         'options' => $this->activeCampaigns,
 266         'type' => CRM_Utils_Type::T_INT,
 267       );
 268       $this->_columns['civicrm_membership']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
 269
 270     }
 271
 272     $this->_currencyColumn = 'civicrm_contribution_currency';
 273     parent::__construct();
 274   }
 275
 276   public function preProcess() {
 277     $this->assign('reportTitle', ts('Membership Detail Report'));
 278     parent::preProcess();
 279   }
 280
 281   public function from() {
 282     $this->setFromBase('civicrm_contact');
 283     $this->_from .= "
 284          {$this->_aclFrom}
 285                INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 286                           ON {$this->_aliases['civicrm_contact']}.id =
 287                              {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
 288                LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 289                           ON {$this->_aliases['civicrm_membership_status']}.id =
 290                              {$this->_aliases['civicrm_membership']}.status_id ";
 291
 292     if ($this->isTableSelected('civicrm_address')) {
 293       $this->_from .= "
 294              LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 295                        ON {$this->_aliases['civicrm_contact']}.id =
 296                           {$this->_aliases['civicrm_address']}.contact_id AND
 297                           {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 298     }
 299
 300     if ($this->isTableSelected('civicrm_email')) {
 301       $this->_from .= "
 302               LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 303                         ON {$this->_aliases['civicrm_contact']}.id =
 304                            {$this->_aliases['civicrm_email']}.contact_id AND
 305                            {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 306     }
 307     //used when phone field is selected
 308     if ($this->isTableSelected('civicrm_phone')) {
 309       $this->_from .= "
 310               LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 311                         ON {$this->_aliases['civicrm_contact']}.id =
 312                            {$this->_aliases['civicrm_phone']}.contact_id AND
 313                            {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
 314     }
 315     //used when contribution field is selected.
 316     if ($this->isTableSelected('civicrm_contribution')) {
 317       $this->_from .= "
 318              LEFT JOIN civicrm_membership_payment cmp
 319                  ON {$this->_aliases['civicrm_membership']}.id = cmp.membership_id
 320              LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 321                  ON cmp.contribution_id={$this->_aliases['civicrm_contribution']}.id\n";
 322     }
 323   }
 324
 325   public function postProcess() {
 326
 327     $this->beginPostProcess();
 328
 329     // get the acl clauses built before we assemble the query
 330     $this->buildACLClause($this->_aliases['civicrm_contact']);
 331     $sql = $this->buildQuery(TRUE);
 332
 333     $rows = array();
 334     $this->buildRows($sql, $rows);
 335
 336     $this->formatDisplay($rows);
 337     $this->doTemplateAssignment($rows);
 338     $this->endPostProcess($rows);
 339   }
 340
 341   /**
 342    * Alter display of rows.
 343    *
 344    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 345    * such as rendering contacts as links.
 346    *
 347    * @param array $rows
 348    *   Rows generated by SQL, with an array for each row.
 349    */
 350   public function alterDisplay(&$rows) {
 351     $entryFound = FALSE;
 352     $checkList = array();
 353
 354     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 355     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 356     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 357
 358     $repeatFound = FALSE;
 359     foreach ($rows as $rowNum => $row) {
 360       if ($repeatFound == FALSE ||
 361         $repeatFound < $rowNum - 1
 362       ) {
 363         unset($checkList);
 364         $checkList = array();
 365       }
 366       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 367         // not repeat contact display names if it matches with the one
 368         // in previous row
 369         foreach ($row as $colName => $colVal) {
 370           if (in_array($colName, $this->_noRepeats) &&
 371             $rowNum > 0
 372           ) {
 373             if ($rows[$rowNum][$colName] == $rows[$rowNum - 1][$colName] ||
 374               (!empty($checkList[$colName]) &&
 375               in_array($colVal, $checkList[$colName]))
 376               ) {
 377               $rows[$rowNum][$colName] = "";
 378               // CRM-15917: Don't blank the name if it's a different contact
 379               if ($colName == 'civicrm_contact_exposed_id') {
 380                 $rows[$rowNum]['civicrm_contact_sort_name'] = "";
 381               }
 382               $repeatFound = $rowNum;
 383             }
 384           }
 385           if (in_array($colName, $this->_noRepeats)) {
 386             $checkList[$colName][] = $colVal;
 387           }
 388         }
 389       }
 390
 391       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 392         if ($value = $row['civicrm_membership_membership_type_id']) {
 393           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 394         }
 395         $entryFound = TRUE;
 396       }
 397
 398       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 399         if ($value = $row['civicrm_address_state_province_id']) {
 400           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 401         }
 402         $entryFound = TRUE;
 403       }
 404
 405       if (array_key_exists('civicrm_address_country_id', $row)) {
 406         if ($value = $row['civicrm_address_country_id']) {
 407           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 408         }
 409         $entryFound = TRUE;
 410       }
 411
 412       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 413         $rows[$rowNum]['civicrm_contact_sort_name'] &&
 414         array_key_exists('civicrm_contact_id', $row)
 415       ) {
 416         $url = CRM_Utils_System::url("civicrm/contact/view",
 417           'reset=1&cid=' . $row['civicrm_contact_id'],
 418           $this->_absoluteUrl
 419         );
 420         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 421         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 422         $entryFound = TRUE;
 423       }
 424
 425       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 426         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 427         $entryFound = TRUE;
 428       }
 429       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 430         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 431         $entryFound = TRUE;
 432       }
 433       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 434         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 435         $entryFound = TRUE;
 436       }
 437
 438       // Convert campaign_id to campaign title
 439       if (array_key_exists('civicrm_membership_campaign_id', $row)) {
 440         if ($value = $row['civicrm_membership_campaign_id']) {
 441           $rows[$rowNum]['civicrm_membership_campaign_id'] = $this->activeCampaigns[$value];
 442           $entryFound = TRUE;
 443         }
 444       }
 445       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'member/detail', 'List all memberships(s) for this ') ? TRUE : $entryFound;
 446       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'member/detail', 'List all memberships(s) for this ') ? TRUE : $entryFound;
 447
 448       if (!$entryFound) {
 449         break;
 450       }
 451     }
 452   }
 453
 454 }