CRM/Member/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Member_BAO_Query {
  36
  37   static function &getFields() {
  38     $fields = CRM_Member_BAO_Membership::exportableFields();
  39     return $fields;
  40   }
  41
  42   /**
  43    * if membership are involved, add the specific membership fields
  44    *
  45    * @param $query
  46    *
  47    * @return void
  48    * @access public
  49    */
  50   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['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   static function where(&$query) {
 132     $grouping = NULL;
 133     foreach (array_keys($query->_params) as $id) {
 134       if (empty($query->_params[$id][0])) {
 135         continue;
 136       }
 137       if (substr($query->_params[$id][0], 0, 7) == 'member_' || substr($query->_params[$id][0], 0, 11) == 'membership_') {
 138         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
 139           $query->_useDistinct = TRUE;
 140         }
 141         $grouping = $query->_params[$id][3];
 142         self::whereClauseSingle($query->_params[$id], $query);
 143       }
 144     }
 145   }
 146
 147   static function whereClauseSingle(&$values, &$query) {
 148     list($name, $op, $value, $grouping, $wildcard) = $values;
 149     switch ($name) {
 150       case 'member_join_date_low':
 151       case 'member_join_date_high':
 152         $query->dateQueryBuilder($values,
 153           'civicrm_membership', 'member_join_date', 'join_date',
 154           'Member Since'
 155         );
 156         return;
 157
 158       case 'member_start_date_low':
 159       case 'member_start_date_high':
 160         $query->dateQueryBuilder($values,
 161           'civicrm_membership', 'member_start_date', 'start_date',
 162           'Start Date'
 163         );
 164         return;
 165
 166       case 'member_end_date_low':
 167       case 'member_end_date_high':
 168         $query->dateQueryBuilder($values,
 169           'civicrm_membership', 'member_end_date', 'end_date',
 170           'End Date'
 171         );
 172         return;
 173
 174       case 'member_join_date':
 175         $op = '>=';
 176         $date = CRM_Utils_Date::format($value);
 177         if ($date) {
 178           $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}";
 179           $date = CRM_Utils_Date::customFormat($value);
 180           $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-'));
 181           $query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op));
 182         }
 183
 184         return;
 185
 186       case 'member_source':
 187         $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 188         $value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
 189
 190         $query->_where[$grouping][] = "civicrm_membership.source $op '{$value}'";
 191         $query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op));
 192         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 193         return;
 194
 195       case 'membership_status':
 196       case 'member_status_id':
 197         if (!is_array($value)) {
 198           $status = $value;
 199           if (!empty($value)) {
 200             $value = array_flip(explode(",", str_replace(array( '(', ')' ), '', $value)));
 201           }
 202           else {
 203             $value = array();
 204           }
 205         }
 206         else {
 207           $status = implode(',', array_keys($value));
 208           if (count($value) > 1) {
 209             $op = 'IN';
 210             $status = "({$status})";
 211           }
 212         }
 213
 214         $names = array();
 215         $statusTypes = CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label');
 216         foreach ($value as $id => $dontCare) {
 217           $names[] = $statusTypes[$id];
 218         }
 219         $query->_qill[$grouping][] = ts('Membership Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 220         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.status_id",
 221           $op,
 222           $status,
 223           "Integer"
 224         );
 225         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 226         return;
 227
 228       case 'member_test':
 229         // We dont want to include all tests for sql OR CRM-7827
 230         if (!$value || $query->getOperator() != 'OR') {
 231           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
 232           if ($value) {
 233             $query->_qill[$grouping][] = ts('Membership is a Test');
 234           }
 235         }
 236         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 237         return;
 238
 239       case 'member_auto_renew':
 240         $op = "!=";
 241         if ($value) {
 242           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
 243           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
 244             "ccr.contribution_status_id",
 245             $op,
 246             CRM_Core_DAO::getFieldValue(
 247               'CRM_Core_DAO_OptionValue',
 248               'Cancelled',
 249               'value',
 250               'name'
 251             ),
 252             "Integer"
 253           );
 254           $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
 255         }
 256         else {
 257           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
 258           $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
 259         }
 260         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 261         return;
 262
 263       case 'member_pay_later':
 264         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later",
 265           $op,
 266           $value,
 267           "Integer"
 268         );
 269         if ($value) {
 270           $query->_qill[$grouping][] = ts("Membership is Pay Later");
 271         }
 272         else {
 273           $query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
 274         }
 275         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 276         return;
 277
 278       case 'membership_type':
 279       case 'member_membership_type_id':
 280         if (!is_array($value)) {
 281           $mType = $value;
 282           if (!empty($value)) {
 283             $value = array_flip(explode(",", str_replace(array( '(', ')' ), '', $value)));
 284           }
 285           else {
 286             $value = array();
 287           }
 288         }
 289         else {
 290           $mType = implode(',', array_keys($value));
 291           if (count($value) > 1) {
 292             $op = 'IN';
 293             $mType = "({$mType})";
 294           }
 295         }
 296
 297         $names = array();
 298         $membershipTypes = CRM_Member_PseudoConstant::membershipType();
 299         foreach ($value as $id => $dontCare) {
 300           if(!empty($membershipTypes[$id])) {
 301             $names[] = $membershipTypes[$id];
 302           }
 303         }
 304         $query->_qill[$grouping][] = ts('Membership Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 305         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.membership_type_id",
 306           $op,
 307           $mType,
 308           "Integer"
 309         );
 310         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 311         return;
 312
 313       case 'member_id':
 314         $query->_where[$grouping][] = " civicrm_membership.id $op $value";
 315         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 316         return;
 317
 318       case 'member_is_primary':
 319         if ($value) {
 320           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
 321           $query->_qill[$grouping][] = ts("Primary Members Only");
 322         }
 323         else {
 324           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
 325           $query->_qill[$grouping][] = ts("Related Members Only");
 326         }
 327         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 328         return;
 329
 330       case 'member_campaign_id':
 331         $campParams = array(
 332           'op' => $op,
 333           'campaign' => $value,
 334           'grouping' => $grouping,
 335           'tableName' => 'civicrm_membership',
 336         );
 337         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 338         return;
 339     }
 340   }
 341
 342   static function from($name, $mode, $side) {
 343     $from = NULL;
 344     switch ($name) {
 345       case 'civicrm_membership':
 346         $from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
 347         $from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
 348         break;
 349
 350       case 'civicrm_membership_type':
 351         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 352           $from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 353         }
 354         else {
 355           $from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 356         }
 357         break;
 358
 359       case 'civicrm_membership_status':
 360         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 361           $from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 362         }
 363         else {
 364           $from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 365         }
 366         break;
 367
 368       case 'civicrm_membership_payment':
 369         $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 370         break;
 371     }
 372     return $from;
 373   }
 374
 375   static function defaultReturnProperties($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       );
 399
 400       if ($includeCustomFields) {
 401         // also get all the custom membership properties
 402         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Membership');
 403         if (!empty($fields)) {
 404           foreach ($fields as $name => $dontCare) {
 405             $properties[$name] = 1;
 406           }
 407         }
 408       }
 409     }
 410
 411     return $properties;
 412   }
 413
 414   static function buildSearchForm(&$form) {
 415     foreach (CRM_Member_PseudoConstant::membershipType() as $id => $Name) {
 416       $form->_membershipType = &$form->addElement('checkbox', "member_membership_type_id[$id]", NULL, $Name);
 417     }
 418
 419
 420     foreach (CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label') as $sId => $sName) {
 421       $form->_membershipStatus = &$form->addElement('checkbox', "member_status_id[$sId]", NULL, $sName);
 422     }
 423
 424     $form->addElement('text', 'member_source', ts('Source'));
 425
 426     CRM_Core_Form_Date::buildDateRange($form, 'member_join_date', 1, '_low', '_high', ts('From'), FALSE);
 427
 428     CRM_Core_Form_Date::buildDateRange($form, 'member_start_date', 1, '_low', '_high', ts('From'), FALSE);
 429
 430     CRM_Core_Form_Date::buildDateRange($form, 'member_end_date', 1, '_low', '_high', ts('From'), FALSE);
 431
 432     $form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
 433     $form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
 434     $form->addYesNo('member_auto_renew', ts('Auto-Renew?'), TRUE);
 435     $form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);
 436
 437     // add all the custom  searchable fields
 438     $extends = array('Membership');
 439     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
 440     if ($groupDetails) {
 441       $form->assign('membershipGroupTree', $groupDetails);
 442       foreach ($groupDetails as $group) {
 443         foreach ($group['fields'] as $field) {
 444           $fieldId = $field['id'];
 445           $elementName = 'custom_' . $fieldId;
 446           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 447             $elementName,
 448             $fieldId,
 449             FALSE, FALSE, TRUE
 450           );
 451         }
 452       }
 453     }
 454
 455     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');
 456
 457     $form->assign('validCiviMember', TRUE);
 458     $form->setDefaults(array('member_test' => 0));
 459   }
 460
 461   static function searchAction(&$row, $id) {}
 462
 463   static function tableNames(&$tables) {
 464     //add membership table
 465     if (!empty($tables['civicrm_membership_log']) || !empty($tables['civicrm_membership_status']) || CRM_Utils_Array::value('civicrm_membership_type', $tables)) {
 466       $tables = array_merge(array('civicrm_membership' => 1), $tables);
 467     }
 468   }
 469 }
 470