CRM/Member/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   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   /**
  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['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 'member_status_id':
 206         if (!is_array($value)) {
 207           $status = $value;
 208           if (!empty($value)) {
 209             $value = array_flip(explode(",", str_replace(array('(', ')'), '', $value)));
 210           }
 211           else {
 212             $value = array();
 213           }
 214         }
 215         else {
 216           $status = implode(',', array_keys($value));
 217           if (count($value) > 0) {
 218             $op = 'IN';
 219             $status = "({$status})";
 220           }
 221         }
 222
 223         $names = array();
 224         $statusTypes = CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label');
 225         foreach ($value as $id => $dontCare) {
 226           $names[] = $statusTypes[$id];
 227         }
 228         $query->_qill[$grouping][] = ts('Membership Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 229         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.status_id",
 230           $op,
 231           $status,
 232           "Integer"
 233         );
 234         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 235         return;
 236
 237       case 'member_test':
 238         // We dont want to include all tests for sql OR CRM-7827
 239         if (!$value || $query->getOperator() != 'OR') {
 240           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
 241           if ($value) {
 242             $query->_qill[$grouping][] = ts('Membership is a Test');
 243           }
 244         }
 245         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 246         return;
 247
 248       case 'member_auto_renew':
 249         $op = "!=";
 250         if ($value) {
 251           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
 252           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
 253             "ccr.contribution_status_id",
 254             $op,
 255             array_search(
 256               'Cancelled',
 257               CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
 258             ),
 259             "Integer"
 260           );
 261           $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
 262         }
 263         else {
 264           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
 265           $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
 266         }
 267         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 268         return;
 269
 270       case 'member_pay_later':
 271         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later",
 272           $op,
 273           $value,
 274           "Integer"
 275         );
 276         if ($value) {
 277           $query->_qill[$grouping][] = ts("Membership is Pay Later");
 278         }
 279         else {
 280           $query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
 281         }
 282         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 283         return;
 284
 285       case 'membership_type':
 286       case 'member_membership_type_id':
 287         if (!is_array($value)) {
 288           $mType = $value;
 289           if (!empty($value)) {
 290             $value = array_flip(explode(",", str_replace(array('(', ')'), '', $value)));
 291           }
 292           else {
 293             $value = array();
 294           }
 295         }
 296         else {
 297           $mType = implode(',', array_keys($value));
 298           if (count($value) > 0) {
 299             $op = 'IN';
 300             $mType = "({$mType})";
 301           }
 302         }
 303
 304         $names = array();
 305         $membershipTypes = CRM_Member_PseudoConstant::membershipType();
 306         foreach ($value as $id => $dontCare) {
 307           if (!empty($membershipTypes[$id])) {
 308             $names[] = $membershipTypes[$id];
 309           }
 310         }
 311         $query->_qill[$grouping][] = ts('Membership Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 312         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.membership_type_id",
 313           $op,
 314           $mType,
 315           "Integer"
 316         );
 317         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 318         return;
 319
 320       case 'member_id':
 321         $query->_where[$grouping][] = " civicrm_membership.id $op $value";
 322         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 323         return;
 324
 325       case 'member_is_primary':
 326         if ($value) {
 327           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
 328           $query->_qill[$grouping][] = ts("Primary Members Only");
 329         }
 330         else {
 331           $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
 332           $query->_qill[$grouping][] = ts("Related Members Only");
 333         }
 334         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
 335         return;
 336
 337       case 'member_campaign_id':
 338         $campParams = array(
 339           'op' => $op,
 340           'campaign' => $value,
 341           'grouping' => $grouping,
 342           'tableName' => 'civicrm_membership',
 343         );
 344         CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
 345         return;
 346     }
 347   }
 348
 349   /**
 350    * @param string $name
 351    * @param $mode
 352    * @param $side
 353    *
 354    * @return null|string
 355    */
 356   public static function from($name, $mode, $side) {
 357     $from = NULL;
 358     switch ($name) {
 359       case 'civicrm_membership':
 360         $from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
 361         $from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
 362         break;
 363
 364       case 'civicrm_membership_type':
 365         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 366           $from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 367         }
 368         else {
 369           $from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
 370         }
 371         break;
 372
 373       case 'civicrm_membership_status':
 374         if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 375           $from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 376         }
 377         else {
 378           $from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
 379         }
 380         break;
 381
 382       case 'civicrm_membership_payment':
 383         $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
 384         break;
 385     }
 386     return $from;
 387   }
 388
 389   /**
 390    * @param $mode
 391    * @param bool $includeCustomFields
 392    *
 393    * @return array|null
 394    */
 395   public static function defaultReturnProperties(
 396     $mode,
 397     $includeCustomFields = TRUE
 398   ) {
 399     $properties = NULL;
 400     if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
 401       $properties = array(
 402         'contact_type' => 1,
 403         'contact_sub_type' => 1,
 404         'sort_name' => 1,
 405         'display_name' => 1,
 406         'membership_type' => 1,
 407         'member_is_test' => 1,
 408         'member_is_pay_later' => 1,
 409         'join_date' => 1,
 410         'membership_start_date' => 1,
 411         'membership_end_date' => 1,
 412         'membership_source' => 1,
 413         'membership_status' => 1,
 414         'membership_id' => 1,
 415         'owner_membership_id' => 1,
 416         'max_related' => 1,
 417         'membership_recur_id' => 1,
 418         'member_campaign_id' => 1,
 419       );
 420
 421       if ($includeCustomFields) {
 422         // also get all the custom membership properties
 423         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Membership');
 424         if (!empty($fields)) {
 425           foreach ($fields as $name => $dontCare) {
 426             $properties[$name] = 1;
 427           }
 428         }
 429       }
 430     }
 431
 432     return $properties;
 433   }
 434
 435   /**
 436    * @param CRM_Core_Form $form
 437    */
 438   public static function buildSearchForm(&$form) {
 439     foreach (CRM_Member_PseudoConstant::membershipType() as $id => $Name) {
 440       $form->_membershipType = &$form->addElement('checkbox', "member_membership_type_id[$id]", NULL, $Name);
 441     }
 442
 443     foreach (CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label') as $sId => $sName) {
 444       $form->_membershipStatus = &$form->addElement('checkbox', "member_status_id[$sId]", NULL, $sName);
 445     }
 446
 447     $form->addElement('text', 'member_source', ts('Source'));
 448
 449     CRM_Core_Form_Date::buildDateRange($form, 'member_join_date', 1, '_low', '_high', ts('From'), FALSE);
 450
 451     CRM_Core_Form_Date::buildDateRange($form, 'member_start_date', 1, '_low', '_high', ts('From'), FALSE);
 452
 453     CRM_Core_Form_Date::buildDateRange($form, 'member_end_date', 1, '_low', '_high', ts('From'), FALSE);
 454
 455     $form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
 456     $form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
 457     $form->addYesNo('member_auto_renew', ts('Auto-Renew?'), TRUE);
 458     $form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);
 459
 460     // add all the custom  searchable fields
 461     $extends = array('Membership');
 462     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
 463     if ($groupDetails) {
 464       $form->assign('membershipGroupTree', $groupDetails);
 465       foreach ($groupDetails as $group) {
 466         foreach ($group['fields'] as $field) {
 467           $fieldId = $field['id'];
 468           $elementName = 'custom_' . $fieldId;
 469           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 470             $elementName,
 471             $fieldId,
 472             FALSE, FALSE, TRUE
 473           );
 474         }
 475       }
 476     }
 477
 478     CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');
 479
 480     $form->assign('validCiviMember', TRUE);
 481     $form->setDefaults(array('member_test' => 0));
 482   }
 483
 484   /**
 485    * @param $row
 486    * @param int $id
 487    */
 488   public static function searchAction(&$row, $id) {
 489   }
 490
 491   /**
 492    * @param $tables
 493    */
 494   public static function tableNames(&$tables) {
 495     //add membership table
 496     if (!empty($tables['civicrm_membership_log']) || !empty($tables['civicrm_membership_status']) || CRM_Utils_Array::value('civicrm_membership_type', $tables)) {
 497       $tables = array_merge(array('civicrm_membership' => 1), $tables);
 498     }
 499   }
 500 }