CRM/Member/BAO/Query.php

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