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