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 (CRM_Utils_Array::value('membership_type', $query->_returnProperties)) {
  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 (CRM_Utils_Array::value('join_date', $query->_returnProperties)) {
  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 (CRM_Utils_Array::value('membership_source', $query->_returnProperties)) {
  75         $query->_select['membership_source'] = "civicrm_membership.source as membership_source";
  76         $query->_element['membership_source'] = 1;
  77       }
  78
  79       //add status
  80       if (CRM_Utils_Array::value('membership_status', $query->_returnProperties)) {
  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 (CRM_Utils_Array::value('status_id', $query->_returnProperties)) {
  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 (CRM_Utils_Array::value('membership_start_date', $query->_returnProperties)) {
  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 (CRM_Utils_Array::value('membership_end_date', $query->_returnProperties)) {
 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 (CRM_Utils_Array::value('owner_membership_id', $query->_returnProperties)) {
 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 (CRM_Utils_Array::value('max_related', $query->_returnProperties)) {
 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 (CRM_Utils_Array::value('membership_recur_id', $query->_returnProperties)) {
 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 (CRM_Utils_Array::value('member_campaign_id', $query->_returnProperties)) {
 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 (!CRM_Utils_Array::value(0, $query->_params[$id])) {
 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         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 235         return;
 236
 237       case 'member_auto_renew':
 238         $op = "!=";
 239         if ($value) {
 240           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
 241           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
 242             "ccr.contribution_status_id",
 243             $op,
 244             CRM_Core_DAO::getFieldValue(
 245               'CRM_Core_DAO_OptionValue',
 246               'Cancelled',
 247               'value',
 248               'name'
 249             ),
 250             "Integer"
 251           );
 252           $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
 253         }
 254         else {
 255           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
 256           $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
 257         }
 258         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 259         return;
 260
 261       case 'member_pay_later':
 262         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later",
 263           $op,
 264           $value,
 265           "Integer"
 266         );
 267         if ($value) {
 268           $query->_qill[$grouping][] = ts("Membership is Pay Later");
 269         }
 270         else {
 271           $query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
 272         }
 273         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 274         return;
 275
 276       case 'membership_type':
 277       case 'member_membership_type_id':
 278         if (!is_array($value)) {
 279           $mType = $value;
 280           if (!empty($value)) {
 281             $value = array_flip(explode(",", str_replace(array( '(', ')' ), '', $value)));
 282           }
 283           else {
 284             $value = array();
 285           }
 286         }
 287         else {
 288           $mType = implode(',', array_keys($value));
 289           if (count($value) > 1) {
 290             $op = 'IN';
 291             $mType = "({$mType})";
 292           }
 293         }
 294
 295         $names = array();
 296         $membershipTypes = CRM_Member_PseudoConstant::membershipType();
 297         foreach ($value as $id => $dontCare) {
 298           if(!empty($membershipTypes[$id])) {
 299             $names[] = $membershipTypes[$id];
 300           }
 301         }
 302         $query->_qill[$grouping][] = ts('Membership Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 303         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.membership_type_id",
 304           $op,
 305           $mType,
 306           "Integer"
 307         );
 308         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 309         return;
 310
 311       case 'member_id':
 312         $query->_where[$grouping][] = " civicrm_membership.id $op $value";
 313         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 314         return;
 315
 316       case 'member_is_primary':
 317         if ($value) {
 318           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
 319           $query->_qill[$grouping][] = ts("Primary Members Only");
 320         }
 321         else {
 322           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
 323           $query->_qill[$grouping][] = ts("Related Members Only");
 324         }
 325         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 326         return;
 327
 328       case 'member_campaign_id':
 329         $campParams = array(
 330           'op' => $op,
 331           'campaign' => $value,
 332           'grouping' => $grouping,
 333           'tableName' => 'civicrm_membership',
 334         );
 335         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 336         return;
 337     }
 338   }
 339
 340   static function from($name, $mode, $side) {
 341     $from = NULL;
 342     switch ($name) {
 343       case 'civicrm_membership':
 344         $from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
 345         $from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
 346         break;
 347
 348       case 'civicrm_membership_type':
 349         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 350           $from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 351         }
 352         else {
 353           $from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 354         }
 355         break;
 356
 357       case 'civicrm_membership_status':
 358         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 359           $from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 360         }
 361         else {
 362           $from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 363         }
 364         break;
 365
 366       case 'civicrm_membership_payment':
 367         $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 368         break;
 369     }
 370     return $from;
 371   }
 372
 373   static function defaultReturnProperties($mode,
 374     $includeCustomFields = TRUE
 375   ) {
 376     $properties = NULL;
 377     if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 378       $properties = array(
 379         'contact_type' => 1,
 380         'contact_sub_type' => 1,
 381         'sort_name' => 1,
 382         'display_name' => 1,
 383         'membership_type' => 1,
 384         'member_is_test' => 1,
 385         'member_is_pay_later' => 1,
 386         'join_date' => 1,
 387         'membership_start_date' => 1,
 388         'membership_end_date' => 1,
 389         'membership_source' => 1,
 390         'membership_status' => 1,
 391         'membership_id' => 1,
 392         'owner_membership_id' => 1,
 393         'max_related' => 1,
 394         'membership_recur_id' => 1,
 395         'member_campaign_id' => 1,
 396       );
 397
 398       if ($includeCustomFields) {
 399         // also get all the custom membership properties
 400         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Membership');
 401         if (!empty($fields)) {
 402           foreach ($fields as $name => $dontCare) {
 403             $properties[$name] = 1;
 404           }
 405         }
 406       }
 407     }
 408
 409     return $properties;
 410   }
 411
 412   static function buildSearchForm(&$form) {
 413     foreach (CRM_Member_PseudoConstant::membershipType() as $id => $Name) {
 414       $form->_membershipType = &$form->addElement('checkbox', "member_membership_type_id[$id]", NULL, $Name);
 415     }
 416
 417
 418     foreach (CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label') as $sId => $sName) {
 419       $form->_membershipStatus = &$form->addElement('checkbox', "member_status_id[$sId]", NULL, $sName);
 420     }
 421
 422     $form->addElement('text', 'member_source', ts('Source'));
 423
 424     CRM_Core_Form_Date::buildDateRange($form, 'member_join_date', 1, '_low', '_high', ts('From'), FALSE);
 425
 426     CRM_Core_Form_Date::buildDateRange($form, 'member_start_date', 1, '_low', '_high', ts('From'), FALSE);
 427
 428     CRM_Core_Form_Date::buildDateRange($form, 'member_end_date', 1, '_low', '_high', ts('From'), FALSE);
 429
 430     $form->addYesNo('member_is_primary', ts('Primary Member?'));
 431     $form->addYesNo('member_pay_later', ts('Pay Later?'));
 432     $form->addYesNo('member_auto_renew', ts('Auto-Renew?'));
 433     $form->addYesNo('member_test', ts('Membership is a Test?'));
 434
 435     // add all the custom  searchable fields
 436     $extends = array('Membership');
 437     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
 438     if ($groupDetails) {
 439       $form->assign('membershipGroupTree', $groupDetails);
 440       foreach ($groupDetails as $group) {
 441         foreach ($group['fields'] as $field) {
 442           $fieldId = $field['id'];
 443           $elementName = 'custom_' . $fieldId;
 444           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 445             $elementName,
 446             $fieldId,
 447             FALSE, FALSE, TRUE
 448           );
 449         }
 450       }
 451     }
 452
 453     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');
 454
 455     $form->assign('validCiviMember', TRUE);
 456     $form->setDefaults(array('member_test' => 0));
 457   }
 458
 459   static function searchAction(&$row, $id) {}
 460
 461   static function addShowHide(&$showHide) {
 462     $showHide->addHide('memberForm');
 463     $showHide->addShow('memberForm_show');
 464   }
 465
 466   static function tableNames(&$tables) {
 467     //add membership table
 468     if (CRM_Utils_Array::value('civicrm_membership_log', $tables) || CRM_Utils_Array::value('civicrm_membership_status', $tables) || CRM_Utils_Array::value('civicrm_membership_type', $tables)) {
 469       $tables = array_merge(array('civicrm_membership' => 1), $tables);
 470     }
 471   }
 472 }
 473