CRM/Member/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  */
  33 class CRM_Member_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_status_id'])) {
  90         $query->_select['status_id'] = "civicrm_membership_status.id as status_id";
  91         $query->_element['status_id'] = 1;
  92         $query->_tables['civicrm_membership_status'] = 1;
  93         $query->_whereTables['civicrm_membership_status'] = 1;
  94       }
  95
  96       //add start date / end date
  97       if (!empty($query->_returnProperties['membership_start_date'])) {
  98         $query->_select['membership_start_date'] = "civicrm_membership.start_date as membership_start_date";
  99         $query->_element['membership_start_date'] = 1;
 100       }
 101
 102       if (!empty($query->_returnProperties['membership_end_date'])) {
 103         $query->_select['membership_end_date'] = "civicrm_membership.end_date as  membership_end_date";
 104         $query->_element['membership_end_date'] = 1;
 105       }
 106
 107       //add owner_membership_id
 108       if (!empty($query->_returnProperties['owner_membership_id'])) {
 109         $query->_select['owner_membership_id'] = "civicrm_membership.owner_membership_id as owner_membership_id";
 110         $query->_element['owner_membership_id'] = 1;
 111       }
 112       //add max_related
 113       if (!empty($query->_returnProperties['max_related'])) {
 114         $query->_select['max_related'] = "civicrm_membership.max_related as max_related";
 115         $query->_element['max_related'] = 1;
 116       }
 117       //add recur id w/o taking contribution table in join.
 118       if (!empty($query->_returnProperties['membership_recur_id'])) {
 119         $query->_select['membership_recur_id'] = "civicrm_membership.contribution_recur_id as membership_recur_id";
 120         $query->_element['membership_recur_id'] = 1;
 121       }
 122
 123       //add campaign id.
 124       if (!empty($query->_returnProperties['member_campaign_id'])) {
 125         $query->_select['member_campaign_id'] = 'civicrm_membership.campaign_id as member_campaign_id';
 126         $query->_element['member_campaign_id'] = 1;
 127       }
 128     }
 129   }
 130
 131   /**
 132    * Generate where clause.
 133    *
 134    * @param CRM_Contact_BAO_Query $query
 135    */
 136   public static function where(&$query) {
 137     foreach (array_keys($query->_params) as $id) {
 138       if (empty($query->_params[$id][0])) {
 139         continue;
 140       }
 141       if (substr($query->_params[$id][0], 0, 7) == 'member_' || substr($query->_params[$id][0], 0, 11) == 'membership_') {
 142         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
 143           $query->_useDistinct = TRUE;
 144         }
 145         self::whereClauseSingle($query->_params[$id], $query);
 146       }
 147     }
 148   }
 149
 150   /**
 151    * Generate where for a single parameter.
 152    *
 153    * @param array $values
 154    * @param CRM_Contact_BAO_Query $query
 155    */
 156   public static function whereClauseSingle(&$values, &$query) {
 157     list($name, $op, $value, $grouping) = $values;
 158     switch ($name) {
 159       case 'member_join_date_low':
 160       case 'member_join_date_high':
 161         $query->dateQueryBuilder($values,
 162           'civicrm_membership', 'member_join_date', 'join_date',
 163           'Member Since'
 164         );
 165         return;
 166
 167       case 'member_start_date_low':
 168       case 'member_start_date_high':
 169         $query->dateQueryBuilder($values,
 170           'civicrm_membership', 'member_start_date', 'start_date',
 171           'Start Date'
 172         );
 173         return;
 174
 175       case 'member_end_date_low':
 176       case 'member_end_date_high':
 177         $query->dateQueryBuilder($values,
 178           'civicrm_membership', 'member_end_date', 'end_date',
 179           'End Date'
 180         );
 181         return;
 182
 183       case 'member_join_date':
 184         $op = '>=';
 185         $date = CRM_Utils_Date::format($value);
 186         if ($date) {
 187           $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}";
 188           $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-'));
 189           $query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op));
 190         }
 191
 192         return;
 193
 194       case 'member_source':
 195         $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 196         $value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
 197
 198         $query->_where[$grouping][] = "civicrm_membership.source $op '{$value}'";
 199         $query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op));
 200         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 201         return;
 202
 203       // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
 204       case 'member_status_id':
 205       case 'member_membership_type_id':
 206         if (is_array($value)) {
 207           $op = 'IN';
 208           $value = array_keys($value);
 209         }
 210       case 'membership_status':
 211       case 'membership_status_id':
 212       case 'membership_type':
 213       case 'membership_type_id':
 214         // CRM-17075 we are specifically handling the possibility we are dealing with the entity reference field
 215         // for membership_type_id here (although status would be handled if converted). The unhandled pathway at the moment
 216         // is from groupContactCache::load and this is a small fix to get the entity reference field to work.
 217         // However, it would seem the larger fix would be to NOT invoke the form formValues for
 218         // the load function. The where clause and the whereTables are saved so they should suffice to generate the query
 219         // to get a contact list. But, better to deal with in 4.8 now...
 220         if (is_string($value) && strpos($value, ',') && $op == '=') {
 221           $value = array('IN' => explode(',', $value));
 222         }
 223       case 'member_id':
 224       case 'member_campaign_id':
 225
 226         if (strpos($name, 'status') !== FALSE) {
 227           $name = 'status_id';
 228           $qillName = 'Membership Status(s)';
 229         }
 230         elseif ($name == 'member_id') {
 231           $name = 'id';
 232           $qillName = 'Membership ID';
 233         }
 234         elseif ($name == 'member_campaign_id') {
 235           $name = 'campaign_id';
 236           $qillName = 'Campaign';
 237         }
 238         else {
 239           $name = 'membership_type_id';
 240           $qillName = 'Membership Type(s)';
 241         }
 242         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.$name",
 243           $op,
 244           $value,
 245           "Integer"
 246         );
 247         list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Member_DAO_Membership', $name, $value, $op);
 248         $query->_qill[$grouping][] = ts('%1 %2 %3', array(
 249           1 => $qillName,
 250           2 => $op,
 251           3 => $value,
 252         ));
 253         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 254         return;
 255
 256       case 'member_test':
 257         // We dont want to include all tests for sql OR CRM-7827
 258         if (!$value || $query->getOperator() != 'OR') {
 259           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
 260           if ($value) {
 261             $query->_qill[$grouping][] = ts('Membership is a Test');
 262           }
 263         }
 264         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 265         return;
 266
 267       case 'member_auto_renew':
 268         $op = "!=";
 269         if ($value) {
 270           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
 271           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
 272             "ccr.contribution_status_id",
 273             $op,
 274             array_search(
 275               'Cancelled',
 276               CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
 277             ),
 278             "Integer"
 279           );
 280           $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
 281         }
 282         else {
 283           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
 284           $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
 285         }
 286         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 287         return;
 288
 289       case 'member_pay_later':
 290         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later",
 291           $op,
 292           $value,
 293           "Integer"
 294         );
 295         if ($value) {
 296           $query->_qill[$grouping][] = ts("Membership is Pay Later");
 297         }
 298         else {
 299           $query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
 300         }
 301         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 302         return;
 303
 304       case 'member_is_primary':
 305         if ($value) {
 306           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
 307           $query->_qill[$grouping][] = ts("Primary Members Only");
 308         }
 309         else {
 310           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
 311           $query->_qill[$grouping][] = ts("Related Members Only");
 312         }
 313         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 314         return;
 315
 316       case 'member_is_override':
 317         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_override", $op, $value, "Boolean");
 318         $query->_qill[$grouping][] = $value ? ts("Is Membership Status overriden? Yes") : ts("Is Membership Status overriden? No");
 319         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 320         return;
 321     }
 322   }
 323
 324   /**
 325    * Generate from clause.
 326    *
 327    * @param string $name
 328    * @param int $mode
 329    * @param string $side
 330    *
 331    * @return string
 332    */
 333   public static function from($name, $mode, $side) {
 334     $from = NULL;
 335     switch ($name) {
 336       case 'civicrm_membership':
 337         $from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
 338         $from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
 339         break;
 340
 341       case 'civicrm_membership_type':
 342         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 343           $from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 344         }
 345         else {
 346           $from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 347         }
 348         break;
 349
 350       case 'civicrm_membership_status':
 351         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 352           $from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 353         }
 354         else {
 355           $from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 356         }
 357         break;
 358
 359       case 'civicrm_membership_payment':
 360         $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 361         break;
 362     }
 363     return $from;
 364   }
 365
 366   /**
 367    * Get default return properties.
 368    *
 369    * @param string $mode
 370    * @param bool $includeCustomFields
 371    *
 372    * @return array|null
 373    */
 374   public static function defaultReturnProperties(
 375     $mode,
 376     $includeCustomFields = TRUE
 377   ) {
 378     $properties = NULL;
 379     if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 380       $properties = array(
 381         'contact_type' => 1,
 382         'contact_sub_type' => 1,
 383         'sort_name' => 1,
 384         'display_name' => 1,
 385         'membership_type' => 1,
 386         'member_is_test' => 1,
 387         'member_is_pay_later' => 1,
 388         'join_date' => 1,
 389         'membership_start_date' => 1,
 390         'membership_end_date' => 1,
 391         'membership_source' => 1,
 392         'membership_status' => 1,
 393         'membership_id' => 1,
 394         'owner_membership_id' => 1,
 395         'max_related' => 1,
 396         'membership_recur_id' => 1,
 397         'member_campaign_id' => 1,
 398         'member_is_override' => 1,
 399       );
 400
 401       if ($includeCustomFields) {
 402         // also get all the custom membership properties
 403         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Membership');
 404         if (!empty($fields)) {
 405           foreach ($fields as $name => $dontCare) {
 406             $properties[$name] = 1;
 407           }
 408         }
 409       }
 410     }
 411
 412     return $properties;
 413   }
 414
 415   /**
 416    * Build the search form.
 417    *
 418    * @param CRM_Core_Form $form
 419    */
 420   public static function buildSearchForm(&$form) {
 421     $membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label', FALSE, FALSE);
 422     $form->add('select', 'membership_status_id', ts('Membership Status(s)'), $membershipStatus, FALSE, array(
 423       'id' => 'membership_status_id',
 424       'multiple' => 'multiple',
 425       'class' => 'crm-select2',
 426     ));
 427
 428     $form->addEntityRef('membership_type_id', ts('Membership Type(s)'), array(
 429       'entity' => 'MembershipType',
 430       'multiple' => TRUE,
 431       'placeholder' => ts('- any -'),
 432       'select' => array('minimumInputLength' => 0),
 433     ));
 434
 435     $form->addElement('text', 'member_source', ts('Source'));
 436
 437     CRM_Core_Form_Date::buildDateRange($form, 'member_join_date', 1, '_low', '_high', ts('From'), FALSE);
 438
 439     CRM_Core_Form_Date::buildDateRange($form, 'member_start_date', 1, '_low', '_high', ts('From'), FALSE);
 440
 441     CRM_Core_Form_Date::buildDateRange($form, 'member_end_date', 1, '_low', '_high', ts('From'), FALSE);
 442
 443     $form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
 444     $form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
 445     $form->addYesNo('member_auto_renew', ts('Auto-Renew?'), TRUE);
 446     $form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);
 447     $form->addYesNo('member_is_override', ts('Membership Status Is Override?'), TRUE);
 448
 449     // add all the custom  searchable fields
 450     $extends = array('Membership');
 451     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
 452     if ($groupDetails) {
 453       $form->assign('membershipGroupTree', $groupDetails);
 454       foreach ($groupDetails as $group) {
 455         foreach ($group['fields'] as $field) {
 456           $fieldId = $field['id'];
 457           $elementName = 'custom_' . $fieldId;
 458           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 459             $elementName,
 460             $fieldId,
 461             FALSE, FALSE, TRUE
 462           );
 463         }
 464       }
 465     }
 466
 467     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');
 468
 469     $form->assign('validCiviMember', TRUE);
 470     $form->setDefaults(array('member_test' => 0));
 471   }
 472
 473   /**
 474    * Possibly un-required function.
 475    *
 476    * @param array $row
 477    * @param int $id
 478    */
 479   public static function searchAction(&$row, $id) {
 480   }
 481
 482   /**
 483    * Add membership table.
 484    *
 485    * @param array $tables
 486    */
 487   public static function tableNames(&$tables) {
 488     if (!empty($tables['civicrm_membership_log']) || !empty($tables['civicrm_membership_status']) || CRM_Utils_Array::value('civicrm_membership_type', $tables)) {
 489       $tables = array_merge(array('civicrm_membership' => 1), $tables);
 490     }
 491   }
 492
 493 }