crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/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 = ts('Membership Status');
 215         }
 216         elseif ($name == 'member_id') {
 217           $name = 'id';
 218           $qillName = ts('Membership ID');
 219         }
 220         else {
 221           $name = 'membership_type_id';
 222           $qillName = ts('Membership Type');
 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][] = $qillName . ' ' . $op . ' ' . $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         if (CRM_Utils_Array::value($op, $value)) {
 296           $value = $value[$op];
 297         }
 298         $campParams = array(
 299           'op' => $op,
 300           'campaign' => $value,
 301           'grouping' => $grouping,
 302           'tableName' => 'civicrm_membership',
 303         );
 304         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 305         return;
 306     }
 307   }
 308
 309   /**
 310    * @param string $name
 311    * @param $mode
 312    * @param $side
 313    *
 314    * @return null|string
 315    */
 316   public static function from($name, $mode, $side) {
 317     $from = NULL;
 318     switch ($name) {
 319       case 'civicrm_membership':
 320         $from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
 321         $from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
 322         break;
 323
 324       case 'civicrm_membership_type':
 325         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 326           $from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 327         }
 328         else {
 329           $from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 330         }
 331         break;
 332
 333       case 'civicrm_membership_status':
 334         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 335           $from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 336         }
 337         else {
 338           $from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 339         }
 340         break;
 341
 342       case 'civicrm_membership_payment':
 343         $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 344         break;
 345     }
 346     return $from;
 347   }
 348
 349   /**
 350    * @param $mode
 351    * @param bool $includeCustomFields
 352    *
 353    * @return array|null
 354    */
 355   public static function defaultReturnProperties(
 356     $mode,
 357     $includeCustomFields = TRUE
 358   ) {
 359     $properties = NULL;
 360     if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 361       $properties = array(
 362         'contact_type' => 1,
 363         'contact_sub_type' => 1,
 364         'sort_name' => 1,
 365         'display_name' => 1,
 366         'membership_type' => 1,
 367         'member_is_test' => 1,
 368         'member_is_pay_later' => 1,
 369         'join_date' => 1,
 370         'membership_start_date' => 1,
 371         'membership_end_date' => 1,
 372         'membership_source' => 1,
 373         'membership_status' => 1,
 374         'membership_id' => 1,
 375         'owner_membership_id' => 1,
 376         'max_related' => 1,
 377         'membership_recur_id' => 1,
 378         'member_campaign_id' => 1,
 379       );
 380
 381       if ($includeCustomFields) {
 382         // also get all the custom membership properties
 383         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Membership');
 384         if (!empty($fields)) {
 385           foreach ($fields as $name => $dontCare) {
 386             $properties[$name] = 1;
 387           }
 388         }
 389       }
 390     }
 391
 392     return $properties;
 393   }
 394
 395   /**
 396    * @param CRM_Core_Form $form
 397    */
 398   public static function buildSearchForm(&$form) {
 399     $membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label', FALSE, FALSE);
 400     $form->add('select', 'membership_status_id', ts('Membership Status'), $membershipStatus, FALSE,
 401       array('id' => 'membership_status_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
 402     );
 403
 404     $form->addSelect('membership_type_id',
 405       array('entity' => 'membership', 'multiple' => 'multiple', 'label' => ts('Membership Type(s)'), 'option_url' => NULL, 'placeholder' => ts('- any -'))
 406     );
 407
 408     $form->addElement('text', 'member_source', ts('Source'));
 409
 410     CRM_Core_Form_Date::buildDateRange($form, 'member_join_date', 1, '_low', '_high', ts('From'), FALSE);
 411
 412     CRM_Core_Form_Date::buildDateRange($form, 'member_start_date', 1, '_low', '_high', ts('From'), FALSE);
 413
 414     CRM_Core_Form_Date::buildDateRange($form, 'member_end_date', 1, '_low', '_high', ts('From'), FALSE);
 415
 416     $form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
 417     $form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
 418     $form->addYesNo('member_auto_renew', ts('Auto-Renew?'), TRUE);
 419     $form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);
 420
 421     // add all the custom  searchable fields
 422     $extends = array('Membership');
 423     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
 424     if ($groupDetails) {
 425       $form->assign('membershipGroupTree', $groupDetails);
 426       foreach ($groupDetails as $group) {
 427         foreach ($group['fields'] as $field) {
 428           $fieldId = $field['id'];
 429           $elementName = 'custom_' . $fieldId;
 430           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 431             $elementName,
 432             $fieldId,
 433             FALSE, FALSE, TRUE
 434           );
 435         }
 436       }
 437     }
 438
 439     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');
 440
 441     $form->assign('validCiviMember', TRUE);
 442     $form->setDefaults(array('member_test' => 0));
 443   }
 444
 445   /**
 446    * @param $row
 447    * @param int $id
 448    */
 449   public static function searchAction(&$row, $id) {
 450   }
 451
 452   /**
 453    * @param $tables
 454    */
 455   public static function tableNames(&$tables) {
 456     //add membership table
 457     if (!empty($tables['civicrm_membership_log']) || !empty($tables['civicrm_membership_status']) || CRM_Utils_Array::value('civicrm_membership_type', $tables)) {
 458       $tables = array_merge(array('civicrm_membership' => 1), $tables);
 459     }
 460   }
 461
 462 }