CRM/Member/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   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    * @return array
  37    */
  38   public static function &getFields() {
  39     $fields = CRM_Member_BAO_Membership::exportableFields();
  40     return $fields;
  41   }
  42
  43   /**
  44    * If membership are involved, add the specific membership fields
  45    *
  46    * @param $query
  47    */
  48   public static function select(&$query) {
  49     // if membership mode add membership id
  50     if ($query->_mode & CRM_Contact_BAO_Query::MODE_MEMBER ||
  51       CRM_Contact_BAO_Query::componentPresent($query->_returnProperties, 'membership_')
  52     ) {
  53
  54       $query->_select['membership_id'] = "civicrm_membership.id as membership_id";
  55       $query->_element['membership_id'] = 1;
  56       $query->_tables['civicrm_membership'] = 1;
  57       $query->_whereTables['civicrm_membership'] = 1;
  58
  59       //add membership type
  60       if (!empty($query->_returnProperties['membership_type'])) {
  61         $query->_select['membership_type'] = "civicrm_membership_type.name as membership_type";
  62         $query->_element['membership_type'] = 1;
  63         $query->_tables['civicrm_membership_type'] = 1;
  64         $query->_whereTables['civicrm_membership_type'] = 1;
  65       }
  66
  67       //add join date
  68       if (!empty($query->_returnProperties['join_date'])) {
  69         $query->_select['join_date'] = "civicrm_membership.join_date as join_date";
  70         $query->_element['join_date'] = 1;
  71       }
  72
  73       //add source
  74       if (!empty($query->_returnProperties['membership_source'])) {
  75         $query->_select['membership_source'] = "civicrm_membership.source as membership_source";
  76         $query->_element['membership_source'] = 1;
  77       }
  78
  79       //add status
  80       if (!empty($query->_returnProperties['membership_status'])) {
  81         $query->_select['membership_status'] = "civicrm_membership_status.label as membership_status";
  82         $query->_element['membership_status'] = 1;
  83         $query->_tables['civicrm_membership_status'] = 1;
  84         $query->_whereTables['civicrm_membership_status'] = 1;
  85       }
  86
  87       if (!empty($query->_returnProperties['membership_status_id'])) {
  88         $query->_select['status_id'] = "civicrm_membership_status.id as status_id";
  89         $query->_element['status_id'] = 1;
  90         $query->_tables['civicrm_membership_status'] = 1;
  91         $query->_whereTables['civicrm_membership_status'] = 1;
  92       }
  93
  94       //add start date / end date
  95       if (!empty($query->_returnProperties['membership_start_date'])) {
  96         $query->_select['membership_start_date'] = "civicrm_membership.start_date as membership_start_date";
  97         $query->_element['membership_start_date'] = 1;
  98       }
  99
 100       if (!empty($query->_returnProperties['membership_end_date'])) {
 101         $query->_select['membership_end_date'] = "civicrm_membership.end_date as  membership_end_date";
 102         $query->_element['membership_end_date'] = 1;
 103       }
 104
 105       //add owner_membership_id
 106       if (!empty($query->_returnProperties['owner_membership_id'])) {
 107         $query->_select['owner_membership_id'] = "civicrm_membership.owner_membership_id as owner_membership_id";
 108         $query->_element['owner_membership_id'] = 1;
 109       }
 110       //add max_related
 111       if (!empty($query->_returnProperties['max_related'])) {
 112         $query->_select['max_related'] = "civicrm_membership.max_related as max_related";
 113         $query->_element['max_related'] = 1;
 114       }
 115       //add recur id w/o taking contribution table in join.
 116       if (!empty($query->_returnProperties['membership_recur_id'])) {
 117         $query->_select['membership_recur_id'] = "civicrm_membership.contribution_recur_id as membership_recur_id";
 118         $query->_element['membership_recur_id'] = 1;
 119       }
 120
 121       //add campaign id.
 122       if (!empty($query->_returnProperties['member_campaign_id'])) {
 123         $query->_select['member_campaign_id'] = 'civicrm_membership.campaign_id as member_campaign_id';
 124         $query->_element['member_campaign_id'] = 1;
 125       }
 126     }
 127   }
 128
 129   /**
 130    * @param $query
 131    */
 132   public static function where(&$query) {
 133     $grouping = NULL;
 134     foreach (array_keys($query->_params) as $id) {
 135       if (empty($query->_params[$id][0])) {
 136         continue;
 137       }
 138       if (substr($query->_params[$id][0], 0, 7) == 'member_' || substr($query->_params[$id][0], 0, 11) == 'membership_') {
 139         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
 140           $query->_useDistinct = TRUE;
 141         }
 142         $grouping = $query->_params[$id][3];
 143         self::whereClauseSingle($query->_params[$id], $query);
 144       }
 145     }
 146   }
 147
 148   /**
 149    * @param $values
 150    * @param $query
 151    */
 152   public static function whereClauseSingle(&$values, &$query) {
 153     list($name, $op, $value, $grouping, $wildcard) = $values;
 154     switch ($name) {
 155       case 'member_join_date_low':
 156       case 'member_join_date_high':
 157         $query->dateQueryBuilder($values,
 158           'civicrm_membership', 'member_join_date', 'join_date',
 159           'Member Since'
 160         );
 161         return;
 162
 163       case 'member_start_date_low':
 164       case 'member_start_date_high':
 165         $query->dateQueryBuilder($values,
 166           'civicrm_membership', 'member_start_date', 'start_date',
 167           'Start Date'
 168         );
 169         return;
 170
 171       case 'member_end_date_low':
 172       case 'member_end_date_high':
 173         $query->dateQueryBuilder($values,
 174           'civicrm_membership', 'member_end_date', 'end_date',
 175           'End Date'
 176         );
 177         return;
 178
 179       case 'member_join_date':
 180         $op = '>=';
 181         $date = CRM_Utils_Date::format($value);
 182         if ($date) {
 183           $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}";
 184           $date = CRM_Utils_Date::customFormat($value);
 185           $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-'));
 186           $query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op));
 187         }
 188
 189         return;
 190
 191       case 'member_source':
 192         $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 193         $value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
 194
 195         $query->_where[$grouping][] = "civicrm_membership.source $op '{$value}'";
 196         $query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op));
 197         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 198         return;
 199
 200       // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
 201       case 'member_status_id':
 202       case 'member_membership_type_id':
 203         if (is_array($value)) {
 204           $op = 'IN';
 205           $value = array_keys($value);
 206         }
 207       case 'membership_status':
 208       case 'membership_status_id':
 209       case 'membership_type':
 210       case 'membership_type_id':
 211       case 'member_id':
 212         if (strpos($name, 'status') !== FALSE) {
 213           $name = 'status_id';
 214           $qillName = 'Membership Status(s)';
 215         }
 216         elseif ($name == 'member_id') {
 217           $name = 'id';
 218           $qillName = 'Membership ID';
 219         }
 220         else {
 221           $name = 'membership_type_id';
 222           $qillName = 'Membership Type(s)';
 223         }
 224         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.$name",
 225           $op,
 226           $value,
 227           "Integer"
 228         );
 229         list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Member_DAO_Membership', $name, $value, $op);
 230         $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $qillName, 2 => $op, 3 => $value));
 231         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 232         return;
 233
 234       case 'member_test':
 235         // We dont want to include all tests for sql OR CRM-7827
 236         if (!$value || $query->getOperator() != 'OR') {
 237           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
 238           if ($value) {
 239             $query->_qill[$grouping][] = ts('Membership is a Test');
 240           }
 241         }
 242         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 243         return;
 244
 245       case 'member_auto_renew':
 246         $op = "!=";
 247         if ($value) {
 248           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
 249           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
 250             "ccr.contribution_status_id",
 251             $op,
 252             array_search(
 253               'Cancelled',
 254               CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
 255             ),
 256             "Integer"
 257           );
 258           $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
 259         }
 260         else {
 261           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
 262           $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
 263         }
 264         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 265         return;
 266
 267       case 'member_pay_later':
 268         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later",
 269           $op,
 270           $value,
 271           "Integer"
 272         );
 273         if ($value) {
 274           $query->_qill[$grouping][] = ts("Membership is Pay Later");
 275         }
 276         else {
 277           $query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
 278         }
 279         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 280         return;
 281
 282       case 'member_is_primary':
 283         if ($value) {
 284           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
 285           $query->_qill[$grouping][] = ts("Primary Members Only");
 286         }
 287         else {
 288           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
 289           $query->_qill[$grouping][] = ts("Related Members Only");
 290         }
 291         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 292         return;
 293
 294       case 'member_campaign_id':
 295         $campParams = array(
 296           'op' => $op,
 297           'campaign' => $value,
 298           'grouping' => $grouping,
 299           'tableName' => 'civicrm_membership',
 300         );
 301         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 302         return;
 303     }
 304   }
 305
 306   /**
 307    * @param string $name
 308    * @param $mode
 309    * @param $side
 310    *
 311    * @return null|string
 312    */
 313   public static function from($name, $mode, $side) {
 314     $from = NULL;
 315     switch ($name) {
 316       case 'civicrm_membership':
 317         $from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
 318         $from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
 319         break;
 320
 321       case 'civicrm_membership_type':
 322         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 323           $from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 324         }
 325         else {
 326           $from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 327         }
 328         break;
 329
 330       case 'civicrm_membership_status':
 331         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 332           $from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 333         }
 334         else {
 335           $from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 336         }
 337         break;
 338
 339       case 'civicrm_membership_payment':
 340         $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 341         break;
 342     }
 343     return $from;
 344   }
 345
 346   /**
 347    * @param $mode
 348    * @param bool $includeCustomFields
 349    *
 350    * @return array|null
 351    */
 352   public static function defaultReturnProperties(
 353     $mode,
 354     $includeCustomFields = TRUE
 355   ) {
 356     $properties = NULL;
 357     if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 358       $properties = array(
 359         'contact_type' => 1,
 360         'contact_sub_type' => 1,
 361         'sort_name' => 1,
 362         'display_name' => 1,
 363         'membership_type' => 1,
 364         'member_is_test' => 1,
 365         'member_is_pay_later' => 1,
 366         'join_date' => 1,
 367         'membership_start_date' => 1,
 368         'membership_end_date' => 1,
 369         'membership_source' => 1,
 370         'membership_status' => 1,
 371         'membership_id' => 1,
 372         'owner_membership_id' => 1,
 373         'max_related' => 1,
 374         'membership_recur_id' => 1,
 375         'member_campaign_id' => 1,
 376       );
 377
 378       if ($includeCustomFields) {
 379         // also get all the custom membership properties
 380         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Membership');
 381         if (!empty($fields)) {
 382           foreach ($fields as $name => $dontCare) {
 383             $properties[$name] = 1;
 384           }
 385         }
 386       }
 387     }
 388
 389     return $properties;
 390   }
 391
 392   /**
 393    * @param CRM_Core_Form $form
 394    */
 395   public static function buildSearchForm(&$form) {
 396     $membershipStatus = CRM_Member_PseudoConstant::membershipStatus();
 397     $form->add('select', 'membership_status_id', ts('Membership Status(s)'), $membershipStatus, FALSE,
 398       array('id' => 'membership_status_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
 399     );
 400
 401     $form->addSelect('membership_type_id',
 402       array('entity' => 'membership', 'multiple' => 'multiple', 'label' => ts('Membership Type(s)'), 'option_url' => NULL, 'placeholder' => ts('- any -'))
 403     );
 404
 405     $form->addElement('text', 'member_source', ts('Source'));
 406
 407     CRM_Core_Form_Date::buildDateRange($form, 'member_join_date', 1, '_low', '_high', ts('From'), FALSE);
 408
 409     CRM_Core_Form_Date::buildDateRange($form, 'member_start_date', 1, '_low', '_high', ts('From'), FALSE);
 410
 411     CRM_Core_Form_Date::buildDateRange($form, 'member_end_date', 1, '_low', '_high', ts('From'), FALSE);
 412
 413     $form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
 414     $form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
 415     $form->addYesNo('member_auto_renew', ts('Auto-Renew?'), TRUE);
 416     $form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);
 417
 418     // add all the custom  searchable fields
 419     $extends = array('Membership');
 420     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
 421     if ($groupDetails) {
 422       $form->assign('membershipGroupTree', $groupDetails);
 423       foreach ($groupDetails as $group) {
 424         foreach ($group['fields'] as $field) {
 425           $fieldId = $field['id'];
 426           $elementName = 'custom_' . $fieldId;
 427           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 428             $elementName,
 429             $fieldId,
 430             FALSE, FALSE, TRUE
 431           );
 432         }
 433       }
 434     }
 435
 436     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');
 437
 438     $form->assign('validCiviMember', TRUE);
 439     $form->setDefaults(array('member_test' => 0));
 440   }
 441
 442   /**
 443    * @param $row
 444    * @param int $id
 445    */
 446   public static function searchAction(&$row, $id) {
 447   }
 448
 449   /**
 450    * @param $tables
 451    */
 452   public static function tableNames(&$tables) {
 453     //add membership table
 454     if (!empty($tables['civicrm_membership_log']) || !empty($tables['civicrm_membership_status']) || CRM_Utils_Array::value('civicrm_membership_type', $tables)) {
 455       $tables = array_merge(array('civicrm_membership' => 1), $tables);
 456     }
 457   }
 458
 459 }