CRM/Report/Form/Member/Detail.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Report_Form_Member_Detail extends CRM_Report_Form {
  18
  19   protected $_summary = NULL;
  20
  21   protected $_customGroupExtends = [
  22     'Membership',
  23     'Contribution',
  24     'Contact',
  25     'Individual',
  26     'Household',
  27     'Organization',
  28   ];
  29
  30   protected $_customGroupGroupBy = FALSE;
  31
  32   /**
  33    * This report has not been optimised for group filtering.
  34    *
  35    * The functionality for group filtering has been improved but not
  36    * all reports have been adjusted to take care of it. This report has not
  37    * and will run an inefficient query until fixed.
  38    * @var bool
  39    * @see https://issues.civicrm.org/jira/browse/CRM-19170
  40    */
  41   protected $groupFilterNotOptimised = FALSE;
  42
  43   /**
  44    * Class constructor.
  45    */
  46   public function __construct() {
  47     $this->_columns = [
  48       'civicrm_contact' => [
  49         'dao' => 'CRM_Contact_DAO_Contact',
  50         'fields' => $this->getBasicContactFields(),
  51         'filters' => [
  52           'sort_name' => [
  53             'title' => ts('Contact Name'),
  54             'operator' => 'like',
  55           ],
  56           'is_deleted' => [
  57             'title' => ts('Is Deleted'),
  58             'default' => 0,
  59             'type' => CRM_Utils_Type::T_BOOLEAN,
  60           ],
  61           'id' => ['no_display' => TRUE],
  62         ],
  63         'order_bys' => [
  64           'sort_name' => [
  65             'title' => ts('Last Name, First Name'),
  66             'default' => '1',
  67             'default_weight' => '0',
  68             'default_order' => 'ASC',
  69           ],
  70         ],
  71         'grouping' => 'contact-fields',
  72       ],
  73       'civicrm_membership' => [
  74         'dao' => 'CRM_Member_DAO_Membership',
  75         'fields' => [
  76           'membership_type_id' => [
  77             'title' => ts('Membership Type'),
  78             'required' => TRUE,
  79             'no_repeat' => TRUE,
  80           ],
  81           'membership_start_date' => [
  82             'title' => ts('Start Date'),
  83             'default' => TRUE,
  84           ],
  85           'membership_end_date' => [
  86             'title' => ts('End Date'),
  87             'default' => TRUE,
  88           ],
  89           'owner_membership_id' => [
  90             'title' => ts('Primary/Inherited?'),
  91             'default' => TRUE,
  92           ],
  93           'join_date' => [
  94             'title' => ts('Join Date'),
  95             'default' => TRUE,
  96           ],
  97           'source' => ['title' => ts('Source')],
  98         ],
  99         'filters' => [
 100           'membership_join_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
 101           'membership_start_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
 102           'membership_end_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
 103           'owner_membership_id' => [
 104             'title' => ts('Primary Membership'),
 105             'operatorType' => CRM_Report_Form::OP_INT,
 106           ],
 107           'tid' => [
 108             'name' => 'membership_type_id',
 109             'title' => ts('Membership Types'),
 110             'type' => CRM_Utils_Type::T_INT,
 111             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 112             'options' => CRM_Member_PseudoConstant::membershipType(),
 113           ],
 114         ],
 115         'order_bys' => [
 116           'membership_type_id' => [
 117             'title' => ts('Membership Type'),
 118             'default' => '0',
 119             'default_weight' => '1',
 120             'default_order' => 'ASC',
 121           ],
 122         ],
 123         'grouping' => 'member-fields',
 124         'group_bys' => [
 125           'id' => [
 126             'title' => ts('Membership'),
 127             'default' => TRUE,
 128           ],
 129         ],
 130       ],
 131       'civicrm_membership_status' => [
 132         'dao' => 'CRM_Member_DAO_MembershipStatus',
 133         'alias' => 'mem_status',
 134         'fields' => [
 135           'name' => [
 136             'title' => ts('Status'),
 137             'default' => TRUE,
 138           ],
 139         ],
 140         'filters' => [
 141           'sid' => [
 142             'name' => 'id',
 143             'title' => ts('Status'),
 144             'type' => CRM_Utils_Type::T_INT,
 145             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 146             'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
 147           ],
 148         ],
 149         'grouping' => 'member-fields',
 150       ],
 151       'civicrm_email' => [
 152         'dao' => 'CRM_Core_DAO_Email',
 153         'fields' => ['email' => NULL],
 154         'grouping' => 'contact-fields',
 155       ],
 156       'civicrm_phone' => [
 157         'dao' => 'CRM_Core_DAO_Phone',
 158         'fields' => ['phone' => NULL],
 159         'grouping' => 'contact-fields',
 160       ],
 161       'civicrm_contribution' => [
 162         'dao' => 'CRM_Contribute_DAO_Contribution',
 163         'fields' => [
 164           'contribution_id' => [
 165             'name' => 'id',
 166             'no_display' => TRUE,
 167             'required' => TRUE,
 168           ],
 169           'financial_type_id' => ['title' => ts('Financial Type')],
 170           'contribution_status_id' => ['title' => ts('Contribution Status')],
 171           'payment_instrument_id' => ['title' => ts('Payment Type')],
 172           'currency' => [
 173             'required' => TRUE,
 174             'no_display' => TRUE,
 175           ],
 176           'trxn_id' => NULL,
 177           'receive_date' => NULL,
 178           'receipt_date' => NULL,
 179           'fee_amount' => NULL,
 180           'net_amount' => NULL,
 181           'total_amount' => [
 182             'title' => ts('Payment Amount (most recent)'),
 183             'statistics' => ['sum' => ts('Amount')],
 184           ],
 185         ],
 186         'filters' => [
 187           'receive_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
 188           'financial_type_id' => [
 189             'title' => ts('Financial Type'),
 190             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 191             'options' => CRM_Contribute_PseudoConstant::financialType(),
 192             'type' => CRM_Utils_Type::T_INT,
 193           ],
 194           'payment_instrument_id' => [
 195             'title' => ts('Payment Type'),
 196             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 197             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 198             'type' => CRM_Utils_Type::T_INT,
 199           ],
 200           'currency' => [
 201             'title' => ts('Currency'),
 202             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 203             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 204             'default' => NULL,
 205             'type' => CRM_Utils_Type::T_STRING,
 206           ],
 207           'contribution_status_id' => [
 208             'title' => ts('Contribution Status'),
 209             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 210             'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'),
 211             'type' => CRM_Utils_Type::T_INT,
 212           ],
 213           'total_amount' => ['title' => ts('Contribution Amount')],
 214         ],
 215         'order_bys' => [
 216           'receive_date' => [
 217             'title' => ts('Date Received'),
 218             'default_weight' => '2',
 219             'default_order' => 'DESC',
 220           ],
 221         ],
 222         'grouping' => 'contri-fields',
 223       ],
 224       'civicrm_contribution_recur' => [
 225         'dao' => 'CRM_Contribute_DAO_ContributionRecur',
 226         'fields' => [
 227           'autorenew_status_id' => [
 228             'name' => 'contribution_status_id',
 229             'title' => ts('Auto-Renew Subscription Status'),
 230           ],
 231         ],
 232         'filters' => [
 233           'autorenew_status_id' => [
 234             'name' => 'contribution_status_id',
 235             'title' => ts('Auto-Renew Subscription Status?'),
 236             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 237             'options' => [0 => ts('None'), -1 => ts('Ended')] + CRM_Contribute_BAO_ContributionRecur::buildOptions('contribution_status_id', 'search'),
 238             'type' => CRM_Utils_Type::T_INT,
 239           ],
 240         ],
 241         'grouping' => 'member-fields',
 242       ],
 243     ] + $this->getAddressColumns([
 244       // These options are only excluded because they were not previously present.
 245       'order_by' => FALSE,
 246       'group_by' => FALSE,
 247     ]);
 248     $this->_groupFilter = TRUE;
 249     $this->_tagFilter = TRUE;
 250
 251     // If we have campaigns enabled, add those elements to both the fields, filters and sorting
 252     $this->addCampaignFields('civicrm_membership', FALSE, TRUE);
 253
 254     $this->_currencyColumn = 'civicrm_contribution_currency';
 255     parent::__construct();
 256   }
 257
 258   public function preProcess() {
 259     $this->assign('reportTitle', ts('Membership Detail Report'));
 260     parent::preProcess();
 261   }
 262
 263   public function select() {
 264     parent::select();
 265     if (in_array('civicrm_contribution_recur_autorenew_status_id', $this->_selectAliases)) {
 266       // If we're getting auto-renew status we'll want to know if auto-renew has
 267       // ended.
 268       $this->_selectClauses[] = "{$this->_aliases['civicrm_contribution_recur']}.end_date as civicrm_contribution_recur_end_date";
 269       $this->_selectAliases[] = 'civicrm_contribution_recur_end_date';
 270       // Regenerate SELECT part of query
 271       $this->_select = "SELECT " . implode(', ', $this->_selectClauses) . " ";
 272       $this->_columnHeaders["civicrm_contribution_recur_end_date"] = [
 273         'title' => NULL,
 274         'type' => NULL,
 275         'no_display' => TRUE,
 276       ];
 277     }
 278   }
 279
 280   public function from() {
 281     $this->setFromBase('civicrm_contact');
 282     $this->_from .= "
 283          {$this->_aclFrom}
 284                INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 285                           ON {$this->_aliases['civicrm_contact']}.id =
 286                              {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
 287                LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 288                           ON {$this->_aliases['civicrm_membership_status']}.id =
 289                              {$this->_aliases['civicrm_membership']}.status_id ";
 290
 291     $this->joinAddressFromContact();
 292     $this->joinPhoneFromContact();
 293     $this->joinEmailFromContact();
 294
 295     //used when contribution field is selected.
 296     if ($this->isTableSelected('civicrm_contribution')) {
 297       $this->_from .= "
 298              LEFT JOIN civicrm_membership_payment cmp
 299                  ON {$this->_aliases['civicrm_membership']}.id = cmp.membership_id
 300              LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 301                  ON cmp.contribution_id={$this->_aliases['civicrm_contribution']}.id\n";
 302     }
 303     if ($this->isTableSelected('civicrm_contribution_recur')) {
 304       $this->_from .= <<<HERESQL
 305             LEFT JOIN civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']}
 306                 ON {$this->_aliases['civicrm_membership']}.contribution_recur_id = {$this->_aliases['civicrm_contribution_recur']}.id
 307 HERESQL;
 308     }
 309   }
 310
 311   /**
 312    * Override to add handling for autorenew status.
 313    */
 314   public function whereClause(&$field, $op, $value, $min, $max) {
 315     if ($field['dbAlias'] == "{$this->_aliases['civicrm_contribution_recur']}.contribution_status_id") {
 316       $clauseParts = [];
 317       switch ($op) {
 318         case 'in':
 319           if ($value !== NULL && is_array($value) && count($value) > 0) {
 320             $regularOptions = implode(', ', array_diff($value, [0, -1]));
 321             // None: is null
 322             if (in_array(0, $value)) {
 323               $clauseParts[] = "{$this->_aliases['civicrm_membership']}.contribution_recur_id IS NULL";
 324             }
 325             // Ended: not null, end_date in past
 326             if (in_array(-1, $value)) {
 327               $clauseParts[] = <<<HERESQL
 328                 {$this->_aliases['civicrm_membership']}.contribution_recur_id IS NOT NULL
 329                   AND {$this->_aliases['civicrm_contribution_recur']}.end_date < NOW()
 330 HERESQL;
 331             }
 332             // Normal statuses: IN()
 333             if (!empty($regularOptions)) {
 334               $clauseParts[] = "{$this->_aliases['civicrm_contribution_recur']}.contribution_status_id IN ($regularOptions)";
 335             }
 336             // Double parentheses b/c ORs should be treated as a group
 337             return '((' . implode(') OR (', $clauseParts) . '))';
 338           }
 339           return;
 340
 341         case 'notin':
 342           if ($value !== NULL && is_array($value) && count($value) > 0) {
 343             $regularOptions = implode(', ', array_diff($value, [0, -1]));
 344             // None: is not null
 345             if (in_array(0, $value)) {
 346               $clauseParts[] = "{$this->_aliases['civicrm_membership']}.contribution_recur_id IS NOT NULL";
 347             }
 348             // Ended: null or end_date in future
 349             if (in_array(-1, $value)) {
 350               $clauseParts[] = <<<HERESQL
 351                 {$this->_aliases['civicrm_membership']}.contribution_recur_id IS NULL
 352                   OR {$this->_aliases['civicrm_contribution_recur']}.end_date >= NOW()
 353                   OR {$this->_aliases['civicrm_contribution_recur']}.end_date IS NULL
 354 HERESQL;
 355             }
 356             // Normal statuses: null or NOT IN()
 357             if (!empty($regularOptions)) {
 358               $clauseParts[] = <<<HERESQL
 359                 {$this->_aliases['civicrm_membership']}.contribution_recur_id IS NULL
 360                   OR {$this->_aliases['civicrm_contribution_recur']}.contribution_status_id NOT IN ($regularOptions)
 361 HERESQL;
 362             }
 363             return '(' . implode(') AND (', $clauseParts) . ')';
 364           }
 365           return;
 366
 367         case 'nll':
 368           return "{$this->_aliases['civicrm_membership']}.contribution_recur_id IS NULL";
 369
 370         case 'nnll':
 371           return "{$this->_aliases['civicrm_membership']}.contribution_recur_id IS NOT NULL";
 372       }
 373     }
 374     else {
 375       return parent::whereClause($field, $op, $value, $min, $max);
 376     }
 377   }
 378
 379   public function getOperationPair($type = "string", $fieldName = NULL) {
 380     //re-name IS NULL/IS NOT NULL for clarity
 381     if ($fieldName == 'owner_membership_id') {
 382       $result = [];
 383       $result['nll'] = ts('Primary members only');
 384       $result['nnll'] = ts('Non-primary members only');
 385       $options = parent::getOperationPair($type, $fieldName);
 386       foreach ($options as $key => $label) {
 387         if (!array_key_exists($key, $result)) {
 388           $result[$key] = $label;
 389         }
 390       }
 391     }
 392     else {
 393       $result = parent::getOperationPair($type, $fieldName);
 394     }
 395     return $result;
 396   }
 397
 398   /**
 399    * Alter display of rows.
 400    *
 401    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 402    * such as rendering contacts as links.
 403    *
 404    * @param array $rows
 405    *   Rows generated by SQL, with an array for each row.
 406    */
 407   public function alterDisplay(&$rows) {
 408     $entryFound = FALSE;
 409     $checkList = [];
 410
 411     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 412     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'label');
 413     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 414
 415     $repeatFound = FALSE;
 416     foreach ($rows as $rowNum => $row) {
 417       if ($repeatFound == FALSE ||
 418         $repeatFound < $rowNum - 1
 419       ) {
 420         unset($checkList);
 421         $checkList = [];
 422       }
 423       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 424         // not repeat contact display names if it matches with the one
 425         // in previous row
 426         foreach ($row as $colName => $colVal) {
 427           if (in_array($colName, $this->_noRepeats) &&
 428             $rowNum > 0
 429           ) {
 430             if ($rows[$rowNum][$colName] == $rows[$rowNum - 1][$colName] ||
 431               (!empty($checkList[$colName]) &&
 432               in_array($colVal, $checkList[$colName]))
 433               ) {
 434               $rows[$rowNum][$colName] = "";
 435               // CRM-15917: Don't blank the name if it's a different contact
 436               if ($colName == 'civicrm_contact_exposed_id') {
 437                 $rows[$rowNum]['civicrm_contact_sort_name'] = "";
 438               }
 439               $repeatFound = $rowNum;
 440             }
 441           }
 442           if (in_array($colName, $this->_noRepeats)) {
 443             $checkList[$colName][] = $colVal;
 444           }
 445         }
 446       }
 447
 448       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 449         if ($value = $row['civicrm_membership_membership_type_id']) {
 450           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 451         }
 452         $entryFound = TRUE;
 453       }
 454
 455       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 456         $rows[$rowNum]['civicrm_contact_sort_name'] &&
 457         array_key_exists('civicrm_contact_id', $row)
 458       ) {
 459         $url = CRM_Utils_System::url("civicrm/contact/view",
 460           'reset=1&cid=' . $row['civicrm_contact_id'],
 461           $this->_absoluteUrl
 462         );
 463         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 464         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 465         $entryFound = TRUE;
 466       }
 467
 468       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 469         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 470         $entryFound = TRUE;
 471       }
 472       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 473         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 474         $entryFound = TRUE;
 475       }
 476       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 477         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 478         $entryFound = TRUE;
 479       }
 480       if ($value = $row['civicrm_contribution_recur_autorenew_status_id'] ?? NULL) {
 481         $rows[$rowNum]['civicrm_contribution_recur_autorenew_status_id'] = $contributionStatus[$value];
 482         if (!empty($row['civicrm_contribution_recur_end_date'])
 483           && strtotime($row['civicrm_contribution_recur_end_date']) < time()) {
 484           $ended = ts('ended');
 485           $rows[$rowNum]['civicrm_contribution_recur_autorenew_status_id'] .= " ($ended)";
 486         }
 487         $entryFound = TRUE;
 488       }
 489
 490       if (array_key_exists('civicrm_membership_owner_membership_id', $row)) {
 491         $value = $row['civicrm_membership_owner_membership_id'];
 492         $rows[$rowNum]['civicrm_membership_owner_membership_id'] = ($value != '') ? 'Inherited' : 'Primary';
 493         $entryFound = TRUE;
 494       }
 495
 496       // Convert campaign_id to campaign title
 497       if (array_key_exists('civicrm_membership_campaign_id', $row)) {
 498         if ($value = $row['civicrm_membership_campaign_id']) {
 499           $rows[$rowNum]['civicrm_membership_campaign_id'] = $this->campaigns[$value];
 500           $entryFound = TRUE;
 501         }
 502       }
 503       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'member/detail', 'List all memberships(s) for this ') ? TRUE : $entryFound;
 504       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'member/detail', 'List all memberships(s) for this ') ? TRUE : $entryFound;
 505
 506       if (!$entryFound) {
 507         break;
 508       }
 509     }
 510   }
 511
 512 }