CRM/Member/BAO/Query.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_Member_BAO_Query extends CRM_Core_BAO_Query {
  18
  19   /**
  20    * Get available fields.
  21    *
  22    * @return array
  23    */
  24   public static function &getFields() {
  25     $fields = CRM_Member_BAO_Membership::exportableFields();
  26     return $fields;
  27   }
  28
  29   /**
  30    * If membership are involved, add the specific membership fields.
  31    *
  32    * @param CRM_Contact_BAO_Query $query
  33    */
  34   public static function select(&$query) {
  35     // if membership mode add membership id
  36     if ($query->_mode & CRM_Contact_BAO_Query::MODE_MEMBER ||
  37       CRM_Contact_BAO_Query::componentPresent($query->_returnProperties, 'membership_')
  38     ) {
  39
  40       $query->_select['membership_id'] = "civicrm_membership.id as membership_id";
  41       $query->_element['membership_id'] = 1;
  42       $query->_tables['civicrm_membership'] = 1;
  43       $query->_whereTables['civicrm_membership'] = 1;
  44
  45       //add membership type
  46       if (!empty($query->_returnProperties['membership_type'])) {
  47         $query->_select['membership_type'] = "civicrm_membership_type.name as membership_type";
  48         $query->_element['membership_type'] = 1;
  49         $query->_tables['civicrm_membership_type'] = 1;
  50         $query->_whereTables['civicrm_membership_type'] = 1;
  51       }
  52
  53       //add join date
  54       if (!empty($query->_returnProperties['membership_join_date'])) {
  55         $query->_select['membership_join_date'] = "civicrm_membership.join_date as membership_join_date";
  56         $query->_element['membership_join_date'] = 1;
  57       }
  58
  59       //add source
  60       if (!empty($query->_returnProperties['membership_source'])) {
  61         $query->_select['membership_source'] = "civicrm_membership.source as membership_source";
  62         $query->_element['membership_source'] = 1;
  63       }
  64
  65       //add status
  66       if (!empty($query->_returnProperties['membership_status'])) {
  67         $query->_select['membership_status'] = "civicrm_membership_status.label as membership_status";
  68         $query->_element['membership_status'] = 1;
  69         $query->_tables['civicrm_membership_status'] = 1;
  70         $query->_whereTables['civicrm_membership_status'] = 1;
  71       }
  72
  73       if (!empty($query->_returnProperties['membership_is_current_member'])) {
  74         $query->_select['is_current_member'] = "civicrm_membership_status.is_current_member as is_current_member";
  75         $query->_element['is_current_member'] = 1;
  76         $query->_tables['civicrm_membership_status'] = 1;
  77         $query->_whereTables['civicrm_membership_status'] = 1;
  78       }
  79
  80       if (!empty($query->_returnProperties['membership_status_id'])) {
  81         $query->_select['status_id'] = "civicrm_membership_status.id as status_id";
  82         $query->_element['status_id'] = 1;
  83         $query->_tables['civicrm_membership_status'] = 1;
  84         $query->_whereTables['civicrm_membership_status'] = 1;
  85       }
  86
  87       //add start date / end date
  88       if (!empty($query->_returnProperties['membership_start_date'])) {
  89         $query->_select['membership_start_date'] = "civicrm_membership.start_date as membership_start_date";
  90         $query->_element['membership_start_date'] = 1;
  91       }
  92
  93       if (!empty($query->_returnProperties['membership_end_date'])) {
  94         $query->_select['membership_end_date'] = "civicrm_membership.end_date as  membership_end_date";
  95         $query->_element['membership_end_date'] = 1;
  96       }
  97
  98       //add owner_membership_id
  99       if (!empty($query->_returnProperties['owner_membership_id'])) {
 100         $query->_select['owner_membership_id'] = "civicrm_membership.owner_membership_id as owner_membership_id";
 101         $query->_element['owner_membership_id'] = 1;
 102       }
 103       //add max_related
 104       if (!empty($query->_returnProperties['max_related'])) {
 105         $query->_select['max_related'] = "civicrm_membership.max_related as max_related";
 106         $query->_element['max_related'] = 1;
 107       }
 108       //add recur id w/o taking contribution table in join.
 109       if (!empty($query->_returnProperties['membership_recur_id'])) {
 110         $query->_select['membership_recur_id'] = "civicrm_membership.contribution_recur_id as membership_recur_id";
 111         $query->_element['membership_recur_id'] = 1;
 112       }
 113
 114       //add campaign id.
 115       if (!empty($query->_returnProperties['member_campaign_id'])) {
 116         $query->_select['member_campaign_id'] = 'civicrm_membership.campaign_id as member_campaign_id';
 117         $query->_element['member_campaign_id'] = 1;
 118       }
 119     }
 120   }
 121
 122   /**
 123    * Generate where clause.
 124    *
 125    * @param CRM_Contact_BAO_Query $query
 126    */
 127   public static function where(&$query) {
 128     foreach (array_keys($query->_params) as $id) {
 129       if (empty($query->_params[$id][0])) {
 130         continue;
 131       }
 132       if (substr($query->_params[$id][0], 0, 7) == 'member_' || substr($query->_params[$id][0], 0, 11) == 'membership_') {
 133         if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
 134           $query->_useDistinct = TRUE;
 135         }
 136         self::whereClauseSingle($query->_params[$id], $query);
 137       }
 138     }
 139   }
 140
 141   /**
 142    * Generate where for a single parameter.
 143    *
 144    * @param array $values
 145    * @param CRM_Contact_BAO_Query $query
 146    */
 147   public static function whereClauseSingle(&$values, &$query) {
 148     if ($query->buildDateRangeQuery($values)) {
 149       // @todo - move this to Contact_Query in or near the call to
 150       // $this->buildRelativeDateQuery($values);
 151       return;
 152     }
 153     list($name, $op, $value, $grouping) = $values;
 154     $fields = self::getFields();
 155
 156     $quoteValue = NULL;
 157
 158     if (!empty($value) && !is_array($value)) {
 159       $quoteValue = "\"$value\"";
 160     }
 161
 162     $fieldAliases = self::getLegacySupportedFields();
 163
 164     $fieldName = $name = self::getFieldName($values);
 165     $qillName = $name;
 166     if (in_array($name, $fieldAliases)) {
 167       $qillName = array_search($name, $fieldAliases);
 168     }
 169     $pseudoExtraParam = [];
 170     $fieldSpec = CRM_Utils_Array::value($fieldName, $fields, []);
 171     $tableName = CRM_Utils_Array::value('table_name', $fieldSpec, 'civicrm_membership');
 172     $dataType = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $fieldSpec));
 173     if ($dataType === 'Timestamp' || $dataType === 'Date') {
 174       $title = empty($fieldSpec['unique_title']) ? $fieldSpec['title'] : $fieldSpec['unique_title'];
 175       $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 176       $query->dateQueryBuilder($values,
 177         $tableName, $fieldName, $fieldSpec['name'], $title
 178       );
 179       return;
 180     }
 181
 182     switch ($name) {
 183       case 'member_join_date_low':
 184       case 'member_join_date_high':
 185         Civi::log()->warning(
 186           'member_join_date field is deprecated please use membership_join_date field instead',
 187           ['civi.tag' => 'deprecated']
 188         );
 189         $fldName = str_replace(['_low', '_high'], '', $name);
 190         $query->dateQueryBuilder($values,
 191           'civicrm_membership', $fldName, 'join_date',
 192           'Member Since'
 193         );
 194         return;
 195
 196       case 'member_start_date_low':
 197       case 'member_start_date_high':
 198         Civi::log()->warning(
 199           'member_start_date field is deprecated please use membership_start_date field instead',
 200           ['civi.tag' => 'deprecated']
 201         );
 202         $fldName = str_replace(['_low', '_high'], '', $name);
 203         $query->dateQueryBuilder($values,
 204           'civicrm_membership', $fldName, 'start_date',
 205           'Start Date'
 206         );
 207         return;
 208
 209       case 'member_end_date_low':
 210       case 'member_end_date_high':
 211         Civi::log()->warning(
 212           'member_end_date field is deprecated please use membership_end_date field instead',
 213           ['civi.tag' => 'deprecated']
 214         );
 215         $fldName = str_replace(['_low', '_high'], '', $name);
 216         $query->dateQueryBuilder($values,
 217           'civicrm_membership', $fldName, 'end_date',
 218           'End Date'
 219         );
 220         return;
 221
 222       case 'member_join_date':
 223         $op = '>=';
 224         $date = CRM_Utils_Date::format($value);
 225         if ($date) {
 226           $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}";
 227           $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-'));
 228           $query->_qill[$grouping][] = ts('Member Since %2 %1', [1 => $format, 2 => $op]);
 229         }
 230
 231         return;
 232
 233       case 'member_source':
 234       case 'membership_source':
 235         $fieldSpec = $fields['membership_source'] ?? [];
 236         $query->handleWhereFromMetadata($fieldSpec, $name, $value, $op);
 237         return;
 238
 239       // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
 240       case 'member_status_id':
 241       case 'member_membership_type_id':
 242         if (is_array($value)) {
 243           $op = 'IN';
 244           $value = array_keys($value);
 245         }
 246       case 'membership_status':
 247       case 'membership_status_id':
 248       case 'membership_type':
 249       case 'membership_type_id':
 250         // CRM-17075 we are specifically handling the possibility we are dealing with the entity reference field
 251         // for membership_type_id here (although status would be handled if converted). The unhandled pathway at the moment
 252         // is from groupContactCache::load and this is a small fix to get the entity reference field to work.
 253         // However, it would seem the larger fix would be to NOT invoke the form formValues for
 254         // the load function. The where clause and the whereTables are saved so they should suffice to generate the query
 255         // to get a contact list. But, better to deal with in 4.8 now...
 256         if (is_string($value) && strpos($value, ',') && $op == '=') {
 257           $value = ['IN' => explode(',', $value)];
 258         }
 259       case 'membership_id':
 260         // CRM-18523 Updated to membership_id but kept member_id case for backwards compatibility
 261       case 'member_id':
 262       case 'member_campaign_id':
 263
 264         if (strpos($name, 'status') !== FALSE) {
 265           $name = 'status_id';
 266           $qillName = ts('Membership Status');
 267         }
 268         elseif ($name == 'membership_id' || $name == 'member_id') {
 269           $name = 'id';
 270           $qillName = ts('Membership ID');
 271         }
 272         elseif ($name == 'member_campaign_id') {
 273           $name = 'campaign_id';
 274           $qillName = ts('Campaign');
 275         }
 276         else {
 277           $name = 'membership_type_id';
 278           $qillName = ts('Membership Type');
 279         }
 280         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.$name",
 281           $op,
 282           $value,
 283           "Integer"
 284         );
 285         list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Member_DAO_Membership', $name, $value, $op);
 286         $query->_qill[$grouping][] = $qillName . ' ' . $op . ' ' . $value;
 287         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 288         return;
 289
 290       case 'membership_is_current_member':
 291         // We don't want to include all tests for sql OR CRM-7827
 292         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership_status.is_current_member", $op, $value, "Boolean");
 293         $query->_qill[$grouping][] = $value ? ts('Is a current member') : ts('Is a non-current member');
 294         $query->_tables['civicrm_membership_status'] = $query->_whereTables['civicrm_membership_status'] = 1;
 295         return;
 296
 297       case 'member_test':
 298         // We don't want to include all tests for sql OR CRM-7827
 299         if (!$value || $query->getOperator() != 'OR') {
 300           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
 301           if ($value) {
 302             $query->_qill[$grouping][] = ts('Membership is a Test');
 303           }
 304         }
 305         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 306         return;
 307
 308       case 'member_auto_renew':
 309         $op = '=';
 310         $where = $qill = [];
 311         foreach ($value as $val) {
 312           if ($val == 1) {
 313             $where[] = ' civicrm_membership.contribution_recur_id IS NULL';
 314             $qill[] = ts('Membership is NOT Auto-Renew');
 315           }
 316           elseif ($val == 2) {
 317             $where[] = ' civicrm_membership.contribution_recur_id IS NOT NULL AND ' . CRM_Contact_BAO_Query::buildClause(
 318                 'ccr.contribution_status_id',
 319                 $op,
 320                 array_search(
 321                   'In Progress',
 322                   CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
 323                 ),
 324                 'Integer'
 325               );
 326             $qill[] = ts('Membership is Auto-Renew and In Progress');
 327           }
 328           elseif ($val == 3) {
 329             $where[] = ' civicrm_membership.contribution_recur_id IS NOT NULL AND ' .
 330               CRM_Contact_BAO_Query::buildClause(
 331                 'ccr.contribution_status_id',
 332                 $op,
 333                 array_search(
 334                   'Failed',
 335                   CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
 336                 ),
 337                 'Integer'
 338               );
 339             $qill[] = ts('Membership is Auto-Renew and Failed');
 340           }
 341           elseif ($val == 4) {
 342             $where[] = ' civicrm_membership.contribution_recur_id IS NOT NULL AND ' .
 343               CRM_Contact_BAO_Query::buildClause(
 344                 'ccr.contribution_status_id',
 345                 $op,
 346                 array_search(
 347                   'Cancelled',
 348                   CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
 349                 ),
 350                 'Integer'
 351               );
 352             $qill[] = ts('Membership is Auto-Renew and Cancelled');
 353           }
 354           elseif ($val == 5) {
 355             $where[] = ' civicrm_membership.contribution_recur_id IS NOT NULL AND ccr.end_date IS NOT NULL AND ccr.end_date < NOW()';
 356             $qill[] = ts('Membership is Auto-Renew and Ended');
 357           }
 358           elseif ($val == 6) {
 359             $where[] = ' civicrm_membership.contribution_recur_id IS NOT NULL';
 360             $qill[] = ts('Membership is Auto-Renew');
 361           }
 362         }
 363         if (!empty($where)) {
 364           $query->_where[$grouping][] = implode(' OR ', $where);
 365           $query->_qill[$grouping][] = implode(' OR ', $qill);
 366         }
 367
 368         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 369         return;
 370
 371       case 'member_pay_later':
 372         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later",
 373           $op,
 374           $value,
 375           "Integer"
 376         );
 377         if ($value) {
 378           $query->_qill[$grouping][] = ts("Membership is Pay Later");
 379         }
 380         else {
 381           $query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
 382         }
 383         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 384         return;
 385
 386       case 'member_is_primary':
 387         if ($value) {
 388           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
 389           $query->_qill[$grouping][] = ts("Primary Members Only");
 390         }
 391         else {
 392           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
 393           $query->_qill[$grouping][] = ts("Related Members Only");
 394         }
 395         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 396         return;
 397
 398       case 'member_is_override':
 399         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_override", $op, $value, "Boolean");
 400         $query->_qill[$grouping][] = $value ? ts("Membership Status Is Overriden") : ts("Membership Status Is NOT Overriden");
 401         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 402         return;
 403
 404     }
 405   }
 406
 407   /**
 408    * Generate from clause.
 409    *
 410    * @param string $name
 411    * @param int $mode
 412    * @param string $side
 413    *
 414    * @return string
 415    */
 416   public static function from($name, $mode, $side) {
 417     $from = NULL;
 418     switch ($name) {
 419       case 'civicrm_membership':
 420         $from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
 421         $from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
 422         break;
 423
 424       case 'civicrm_membership_type':
 425         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 426           $from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 427         }
 428         else {
 429           $from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 430         }
 431         break;
 432
 433       case 'civicrm_membership_status':
 434         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 435           $from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 436         }
 437         else {
 438           $from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 439         }
 440         break;
 441
 442       case 'civicrm_membership_payment':
 443         $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 444         break;
 445     }
 446     return $from;
 447   }
 448
 449   /**
 450    * Get default return properties.
 451    *
 452    * @param string $mode
 453    * @param bool $includeCustomFields
 454    *
 455    * @return array|null
 456    */
 457   public static function defaultReturnProperties(
 458     $mode,
 459     $includeCustomFields = TRUE
 460   ) {
 461     $properties = NULL;
 462     if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 463       $properties = [
 464         'contact_type' => 1,
 465         'contact_sub_type' => 1,
 466         'sort_name' => 1,
 467         'display_name' => 1,
 468         'membership_type' => 1,
 469         'member_is_test' => 1,
 470         'member_is_pay_later' => 1,
 471         'membership_join_date' => 1,
 472         'membership_start_date' => 1,
 473         'membership_end_date' => 1,
 474         'membership_source' => 1,
 475         'membership_status' => 1,
 476         'membership_id' => 1,
 477         'owner_membership_id' => 1,
 478         'max_related' => 1,
 479         'membership_recur_id' => 1,
 480         'member_campaign_id' => 1,
 481         'member_is_override' => 1,
 482       ];
 483
 484       if ($includeCustomFields) {
 485         // also get all the custom membership properties
 486         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Membership');
 487         if (!empty($fields)) {
 488           foreach ($fields as $name => $dontCare) {
 489             $properties[$name] = 1;
 490           }
 491         }
 492       }
 493     }
 494
 495     return $properties;
 496   }
 497
 498   /**
 499    * Get the metadata for fields to be included on the grant search form.
 500    *
 501    * @throws \CiviCRM_API3_Exception
 502    */
 503   public static function getSearchFieldMetadata() {
 504     $fields = [
 505       'membership_join_date',
 506       'membership_start_date',
 507       'membership_end_date',
 508       'membership_type_id',
 509       'membership_status_id',
 510     ];
 511     $metadata = civicrm_api3('Membership', 'getfields', [])['values'];
 512     // We should really have a unique name in the url but to reduce risk of regression just hacking
 513     // here for now, since this is being done as an rc fix & the other is moderately risky.
 514     // https://lab.civicrm.org/dev/user-interface/-/issues/14
 515     $metadata['membership_status_id'] = $metadata['status_id'];
 516     // It can't be autoadded due to ^^.
 517     $metadata['membership_status_id']['is_pseudofield'] = TRUE;
 518     unset($metadata['status_id']);
 519     return array_intersect_key($metadata, array_flip($fields));
 520   }
 521
 522   /**
 523    * Build the search form.
 524    *
 525    * @param CRM_Core_Form $form
 526    *
 527    * @throws \CiviCRM_API3_Exception
 528    */
 529   public static function buildSearchForm(&$form) {
 530     $form->addSearchFieldMetadata(['Membership' => self::getSearchFieldMetadata()]);
 531     $form->addFormFieldsFromMetadata();
 532     $membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label', FALSE, FALSE);
 533     $form->add('select', 'membership_status_id', ts('Membership Status'), $membershipStatus, FALSE, [
 534       'id' => 'membership_status_id',
 535       'multiple' => 'multiple',
 536       'class' => 'crm-select2',
 537     ]);
 538
 539     $form->addElement('text', 'member_source', ts('Source'));
 540     $form->add('number', 'membership_id', ts('Membership ID'), ['class' => 'four', 'min' => 1]);
 541
 542     $form->addYesNo('membership_is_current_member', ts('Current Member?'), TRUE);
 543     $form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
 544     $form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
 545
 546     $form->add('select', 'member_auto_renew',
 547       ts('Auto-Renew Subscription Status?'),
 548       [
 549         '1' => ts('- None -'),
 550         '2' => ts('In Progress'),
 551         '3' => ts('Failed'),
 552         '4' => ts('Cancelled'),
 553         '5' => ts('Ended'),
 554         '6' => ts('Any'),
 555       ],
 556       FALSE, ['class' => 'crm-select2', 'multiple' => 'multiple', 'placeholder' => ts('- any -')]
 557     );
 558
 559     $form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);
 560     $form->addYesNo('member_is_override', ts('Membership Status Is Overriden?'), TRUE);
 561
 562     self::addCustomFormFields($form, ['Membership']);
 563
 564     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');
 565
 566     $form->assign('validCiviMember', TRUE);
 567     $form->setDefaults(['member_test' => 0]);
 568   }
 569
 570   /**
 571    * Add membership table.
 572    *
 573    * @param array $tables
 574    */
 575   public static function tableNames(&$tables) {
 576     if (!empty($tables['civicrm_membership_log']) || !empty($tables['civicrm_membership_status']) || !empty($tables['civicrm_membership_type'])) {
 577       $tables = array_merge(['civicrm_membership' => 1], $tables);
 578     }
 579   }
 580
 581 }