CRM/Member/BAO/Query.php

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