[civicrm-core.git] / CRM / Member / BAO / Query.php

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.5                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2014                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2014
 * $Id$
 *
 */
class CRM_Member_BAO_Query {

  /**
   * @return array
   */
  static function &getFields() {
    $fields = CRM_Member_BAO_Membership::exportableFields();
    return $fields;
  }

  /**
   * if membership are involved, add the specific membership fields
   *
   * @param $query
   *
   * @return void
   * @access public
   */
  static function select(&$query) {
    // if membership mode add membership id
    if ($query->_mode & CRM_Contact_BAO_Query::MODE_MEMBER ||
      CRM_Contact_BAO_Query::componentPresent($query->_returnProperties, 'membership_')
    ) {

      $query->_select['membership_id'] = "civicrm_membership.id as membership_id";
      $query->_element['membership_id'] = 1;
      $query->_tables['civicrm_membership'] = 1;
      $query->_whereTables['civicrm_membership'] = 1;

      //add membership type
      if (!empty($query->_returnProperties['membership_type'])) {
        $query->_select['membership_type'] = "civicrm_membership_type.name as membership_type";
        $query->_element['membership_type'] = 1;
        $query->_tables['civicrm_membership_type'] = 1;
        $query->_whereTables['civicrm_membership_type'] = 1;
      }

      //add join date
      if (!empty($query->_returnProperties['join_date'])) {
        $query->_select['join_date'] = "civicrm_membership.join_date as join_date";
        $query->_element['join_date'] = 1;
      }

      //add source
      if (!empty($query->_returnProperties['membership_source'])) {
        $query->_select['membership_source'] = "civicrm_membership.source as membership_source";
        $query->_element['membership_source'] = 1;
      }

      //add status
      if (!empty($query->_returnProperties['membership_status'])) {
        $query->_select['membership_status'] = "civicrm_membership_status.label as membership_status";
        $query->_element['membership_status'] = 1;
        $query->_tables['civicrm_membership_status'] = 1;
        $query->_whereTables['civicrm_membership_status'] = 1;
      }

      if (!empty($query->_returnProperties['status_id'])) {
        $query->_select['status_id'] = "civicrm_membership_status.id as status_id";
        $query->_element['status_id'] = 1;
        $query->_tables['civicrm_membership_status'] = 1;
        $query->_whereTables['civicrm_membership_status'] = 1;
      }

      //add start date / end date
      if (!empty($query->_returnProperties['membership_start_date'])) {
        $query->_select['membership_start_date'] = "civicrm_membership.start_date as membership_start_date";
        $query->_element['membership_start_date'] = 1;
      }

      if (!empty($query->_returnProperties['membership_end_date'])) {
        $query->_select['membership_end_date'] = "civicrm_membership.end_date as  membership_end_date";
        $query->_element['membership_end_date'] = 1;
      }

      //add owner_membership_id
      if (!empty($query->_returnProperties['owner_membership_id'])) {
        $query->_select['owner_membership_id'] = "civicrm_membership.owner_membership_id as owner_membership_id";
        $query->_element['owner_membership_id'] = 1;
      }
      //add max_related
      if (!empty($query->_returnProperties['max_related'])) {
        $query->_select['max_related'] = "civicrm_membership.max_related as max_related";
        $query->_element['max_related'] = 1;
      }
      //add recur id w/o taking contribution table in join.
      if (!empty($query->_returnProperties['membership_recur_id'])) {
        $query->_select['membership_recur_id'] = "civicrm_membership.contribution_recur_id as membership_recur_id";
        $query->_element['membership_recur_id'] = 1;
      }

      //add campaign id.
      if (!empty($query->_returnProperties['member_campaign_id'])) {
        $query->_select['member_campaign_id'] = 'civicrm_membership.campaign_id as member_campaign_id';
        $query->_element['member_campaign_id'] = 1;
      }
    }
  }

  /**
   * @param $query
   */
  static function where(&$query) {
    $grouping = NULL;
    foreach (array_keys($query->_params) as $id) {
      if (empty($query->_params[$id][0])) {
        continue;
      }
      if (substr($query->_params[$id][0], 0, 7) == 'member_' || substr($query->_params[$id][0], 0, 11) == 'membership_') {
        if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
          $query->_useDistinct = TRUE;
        }
        $grouping = $query->_params[$id][3];
        self::whereClauseSingle($query->_params[$id], $query);
      }
    }
  }

  /**
   * @param $values
   * @param $query
   */
  static function whereClauseSingle(&$values, &$query) {
    list($name, $op, $value, $grouping, $wildcard) = $values;
    switch ($name) {
      case 'member_join_date_low':
      case 'member_join_date_high':
        $query->dateQueryBuilder($values,
          'civicrm_membership', 'member_join_date', 'join_date',
          'Member Since'
        );
        return;

      case 'member_start_date_low':
      case 'member_start_date_high':
        $query->dateQueryBuilder($values,
          'civicrm_membership', 'member_start_date', 'start_date',
          'Start Date'
        );
        return;

      case 'member_end_date_low':
      case 'member_end_date_high':
        $query->dateQueryBuilder($values,
          'civicrm_membership', 'member_end_date', 'end_date',
          'End Date'
        );
        return;

      case 'member_join_date':
        $op = '>=';
        $date = CRM_Utils_Date::format($value);
        if ($date) {
          $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}";
          $date = CRM_Utils_Date::customFormat($value);
          $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-'));
          $query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op));
        }

        return;

      case 'member_source':
        $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
        $value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));

        $query->_where[$grouping][] = "civicrm_membership.source $op '{$value}'";
        $query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op));
        $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
        return;

      case 'membership_status':
      case 'member_status_id':
        if (!is_array($value)) {
          $status = $value;
          if (!empty($value)) {
            $value = array_flip(explode(",", str_replace(array( '(', ')' ), '', $value)));
          }
          else {
            $value = array();
          }
        }
        else {
          $status = implode(',', array_keys($value));
          if (count($value) > 0) {
            $op = 'IN';
            $status = "({$status})";
          }
        }

        $names = array();
        $statusTypes = CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label');
        foreach ($value as $id => $dontCare) {
          $names[] = $statusTypes[$id];
        }
        $query->_qill[$grouping][] = ts('Membership Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
        $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.status_id",
          $op,
          $status,
          "Integer"
        );
        $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
        return;

      case 'member_test':
        // We dont want to include all tests for sql OR CRM-7827
        if (!$value || $query->getOperator() != 'OR') {
          $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
          if ($value) {
            $query->_qill[$grouping][] = ts('Membership is a Test');
          }
        }
        $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
        return;

      case 'member_auto_renew':
        $op = "!=";
        if ($value) {
          $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
          $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
            "ccr.contribution_status_id",
            $op,
            array_search(
              'Cancelled', 
              CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
            ),
            "Integer"
          );
          $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
        }
        else {
          $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
          $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
        }
        $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
        return;

      case 'member_pay_later':
        $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later",
          $op,
          $value,
          "Integer"
        );
        if ($value) {
          $query->_qill[$grouping][] = ts("Membership is Pay Later");
        }
        else {
          $query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
        }
        $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
        return;

      case 'membership_type':
      case 'member_membership_type_id':
        if (!is_array($value)) {
          $mType = $value;
          if (!empty($value)) {
            $value = array_flip(explode(",", str_replace(array( '(', ')' ), '', $value)));
          }
          else {
            $value = array();
          }
        }
        else {
          $mType = implode(',', array_keys($value));
          if (count($value) > 0) {
            $op = 'IN';
            $mType = "({$mType})";
          }
        }

        $names = array();
        $membershipTypes = CRM_Member_PseudoConstant::membershipType();
        foreach ($value as $id => $dontCare) {
          if(!empty($membershipTypes[$id])) {
            $names[] = $membershipTypes[$id];
          }
        }
        $query->_qill[$grouping][] = ts('Membership Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
        $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.membership_type_id",
          $op,
          $mType,
          "Integer"
        );
        $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
        return;

      case 'member_id':
        $query->_where[$grouping][] = " civicrm_membership.id $op $value";
        $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
        return;

      case 'member_is_primary':
        if ($value) {
          $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
          $query->_qill[$grouping][] = ts("Primary Members Only");
        }
        else {
          $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
          $query->_qill[$grouping][] = ts("Related Members Only");
        }
        $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
        return;

      case 'member_campaign_id':
        $campParams = array(
          'op' => $op,
          'campaign' => $value,
          'grouping' => $grouping,
          'tableName' => 'civicrm_membership',
        );
        CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
        return;
    }
  }

  /**
   * @param $name
   * @param $mode
   * @param $side
   *
   * @return null|string
   */
  static function from($name, $mode, $side) {
    $from = NULL;
    switch ($name) {
      case 'civicrm_membership':
        $from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
        $from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
        break;

      case 'civicrm_membership_type':
        if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
          $from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
        }
        else {
          $from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
        }
        break;

      case 'civicrm_membership_status':
        if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
          $from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
        }
        else {
          $from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
        }
        break;

      case 'civicrm_membership_payment':
        $from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
        break;
    }
    return $from;
  }

  /**
   * @param $mode
   * @param bool $includeCustomFields
   *
   * @return array|null
   */
  static function defaultReturnProperties($mode,
    $includeCustomFields = TRUE
  ) {
    $properties = NULL;
    if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
      $properties = array(
        'contact_type' => 1,
        'contact_sub_type' => 1,
        'sort_name' => 1,
        'display_name' => 1,
        'membership_type' => 1,
        'member_is_test' => 1,
        'member_is_pay_later' => 1,
        'join_date' => 1,
        'membership_start_date' => 1,
        'membership_end_date' => 1,
        'membership_source' => 1,
        'membership_status' => 1,
        'membership_id' => 1,
        'owner_membership_id' => 1,
        'max_related' => 1,
        'membership_recur_id' => 1,
        'member_campaign_id' => 1,
      );

      if ($includeCustomFields) {
        // also get all the custom membership properties
        $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Membership');
        if (!empty($fields)) {
          foreach ($fields as $name => $dontCare) {
            $properties[$name] = 1;
          }
        }
      }
    }

    return $properties;
  }

  /**
   * @param CRM_Core_Form $form
   */
  static function buildSearchForm(&$form) {
    foreach (CRM_Member_PseudoConstant::membershipType() as $id => $Name) {
      $form->_membershipType = &$form->addElement('checkbox', "member_membership_type_id[$id]", NULL, $Name);
    }


    foreach (CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label') as $sId => $sName) {
      $form->_membershipStatus = &$form->addElement('checkbox', "member_status_id[$sId]", NULL, $sName);
    }

    $form->addElement('text', 'member_source', ts('Source'));

    CRM_Core_Form_Date::buildDateRange($form, 'member_join_date', 1, '_low', '_high', ts('From'), FALSE);

    CRM_Core_Form_Date::buildDateRange($form, 'member_start_date', 1, '_low', '_high', ts('From'), FALSE);

    CRM_Core_Form_Date::buildDateRange($form, 'member_end_date', 1, '_low', '_high', ts('From'), FALSE);

    $form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
    $form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
    $form->addYesNo('member_auto_renew', ts('Auto-Renew?'), TRUE);
    $form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);

    // add all the custom  searchable fields
    $extends = array('Membership');
    $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
    if ($groupDetails) {
      $form->assign('membershipGroupTree', $groupDetails);
      foreach ($groupDetails as $group) {
        foreach ($group['fields'] as $field) {
          $fieldId = $field['id'];
          $elementName = 'custom_' . $fieldId;
          CRM_Core_BAO_CustomField::addQuickFormElement($form,
            $elementName,
            $fieldId,
            FALSE, FALSE, TRUE
          );
        }
      }
    }

    CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');

    $form->assign('validCiviMember', TRUE);
    $form->setDefaults(array('member_test' => 0));
  }

  /**
   * @param $row
   * @param $id
   */
  static function searchAction(&$row, $id) {}

  /**
   * @param $tables
   */
  static function tableNames(&$tables) {
    //add membership table
    if (!empty($tables['civicrm_membership_log']) || !empty($tables['civicrm_membership_status']) || CRM_Utils_Array::value('civicrm_membership_type', $tables)) {
      $tables = array_merge(array('civicrm_membership' => 1), $tables);
    }
  }
}
Commit	Line	Data
6a488035 TO	1	<?php
	2	/*
	3	+--------------------------------------------------------------------+
06b69b18	4	\| CiviCRM version 4.5 \|
6a488035	5	+--------------------------------------------------------------------+
06b69b18	6	\| Copyright CiviCRM LLC (c) 2004-2014 \|
6a488035 TO	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
06b69b18	31	* @copyright CiviCRM LLC (c) 2004-2014
6a488035 TO	32	* $Id$
	33	*
	34	*/
	35	class CRM_Member_BAO_Query {
	36
bb3a214a EM	37	/**
	38	* @return array
	39	*/
6a488035 TO	40	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	*
6c8f6e67 EM	48	* @param $query
6c8f6e67 EM	49	*
6a488035 TO	50	* @return void
	51	* @access public
	52	*/
	53	static function select(&$query) {
	54	// if membership mode add membership id
	55	if ($query->_mode & CRM_Contact_BAO_Query::MODE_MEMBER \|\|
	56	CRM_Contact_BAO_Query::componentPresent($query->_returnProperties, 'membership_')
	57	) {
	58
	59	$query->_select['membership_id'] = "civicrm_membership.id as membership_id";
	60	$query->_element['membership_id'] = 1;
	61	$query->_tables['civicrm_membership'] = 1;
	62	$query->_whereTables['civicrm_membership'] = 1;
	63
	64	//add membership type
a7488080	65	if (!empty($query->_returnProperties['membership_type'])) {
6a488035 TO	66	$query->_select['membership_type'] = "civicrm_membership_type.name as membership_type";
	67	$query->_element['membership_type'] = 1;
	68	$query->_tables['civicrm_membership_type'] = 1;
	69	$query->_whereTables['civicrm_membership_type'] = 1;
	70	}
	71
	72	//add join date
a7488080	73	if (!empty($query->_returnProperties['join_date'])) {
6a488035 TO	74	$query->_select['join_date'] = "civicrm_membership.join_date as join_date";
	75	$query->_element['join_date'] = 1;
	76	}
	77
	78	//add source
a7488080	79	if (!empty($query->_returnProperties['membership_source'])) {
6a488035 TO	80	$query->_select['membership_source'] = "civicrm_membership.source as membership_source";
	81	$query->_element['membership_source'] = 1;
	82	}
	83
	84	//add status
a7488080	85	if (!empty($query->_returnProperties['membership_status'])) {
6a488035 TO	86	$query->_select['membership_status'] = "civicrm_membership_status.label as membership_status";
	87	$query->_element['membership_status'] = 1;
	88	$query->_tables['civicrm_membership_status'] = 1;
	89	$query->_whereTables['civicrm_membership_status'] = 1;
	90	}
	91
a7488080	92	if (!empty($query->_returnProperties['status_id'])) {
6a488035 TO	93	$query->_select['status_id'] = "civicrm_membership_status.id as status_id";
	94	$query->_element['status_id'] = 1;
	95	$query->_tables['civicrm_membership_status'] = 1;
	96	$query->_whereTables['civicrm_membership_status'] = 1;
	97	}
	98
	99	//add start date / end date
a7488080	100	if (!empty($query->_returnProperties['membership_start_date'])) {
6a488035 TO	101	$query->_select['membership_start_date'] = "civicrm_membership.start_date as membership_start_date";
	102	$query->_element['membership_start_date'] = 1;
	103	}
	104
a7488080	105	if (!empty($query->_returnProperties['membership_end_date'])) {
6a488035 TO	106	$query->_select['membership_end_date'] = "civicrm_membership.end_date as membership_end_date";
	107	$query->_element['membership_end_date'] = 1;
	108	}
	109
	110	//add owner_membership_id
a7488080	111	if (!empty($query->_returnProperties['owner_membership_id'])) {
6a488035 TO	112	$query->_select['owner_membership_id'] = "civicrm_membership.owner_membership_id as owner_membership_id";
	113	$query->_element['owner_membership_id'] = 1;
	114	}
	115	//add max_related
a7488080	116	if (!empty($query->_returnProperties['max_related'])) {
6a488035 TO	117	$query->_select['max_related'] = "civicrm_membership.max_related as max_related";
	118	$query->_element['max_related'] = 1;
	119	}
	120	//add recur id w/o taking contribution table in join.
a7488080	121	if (!empty($query->_returnProperties['membership_recur_id'])) {
6a488035 TO	122	$query->_select['membership_recur_id'] = "civicrm_membership.contribution_recur_id as membership_recur_id";
	123	$query->_element['membership_recur_id'] = 1;
	124	}
	125
	126	//add campaign id.
a7488080	127	if (!empty($query->_returnProperties['member_campaign_id'])) {
6a488035 TO	128	$query->_select['member_campaign_id'] = 'civicrm_membership.campaign_id as member_campaign_id';
	129	$query->_element['member_campaign_id'] = 1;
	130	}
	131	}
	132	}
	133
bb3a214a EM	134	/**
	135	* @param $query
	136	*/
6a488035	137	static function where(&$query) {
28c666be	138	$grouping = NULL;
6a488035	139	foreach (array_keys($query->_params) as $id) {
a7488080	140	if (empty($query->_params[$id][0])) {
6a488035 TO	141	continue;
6a488035 TO	142	}
3c53f77e	143	if (substr($query->_params[$id][0], 0, 7) == 'member_' \|\| substr($query->_params[$id][0], 0, 11) == 'membership_') {
6a488035 TO	144	if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
	145	$query->_useDistinct = TRUE;
	146	}
6a488035 TO	147	$grouping = $query->_params[$id][3];
	148	self::whereClauseSingle($query->_params[$id], $query);
	149	}
	150	}
6a488035 TO	151	}
6a488035 TO	152
bb3a214a EM	153	/**
	154	* @param $values
	155	* @param $query
	156	*/
6a488035 TO	157	static function whereClauseSingle(&$values, &$query) {
	158	list($name, $op, $value, $grouping, $wildcard) = $values;
	159	switch ($name) {
	160	case 'member_join_date_low':
	161	case 'member_join_date_high':
	162	$query->dateQueryBuilder($values,
	163	'civicrm_membership', 'member_join_date', 'join_date',
	164	'Member Since'
	165	);
	166	return;
	167
	168	case 'member_start_date_low':
	169	case 'member_start_date_high':
	170	$query->dateQueryBuilder($values,
	171	'civicrm_membership', 'member_start_date', 'start_date',
	172	'Start Date'
	173	);
	174	return;
	175
	176	case 'member_end_date_low':
	177	case 'member_end_date_high':
	178	$query->dateQueryBuilder($values,
	179	'civicrm_membership', 'member_end_date', 'end_date',
	180	'End Date'
	181	);
	182	return;
	183
	184	case 'member_join_date':
	185	$op = '>=';
	186	$date = CRM_Utils_Date::format($value);
	187	if ($date) {
	188	$query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}";
	189	$date = CRM_Utils_Date::customFormat($value);
	190	$format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-'));
	191	$query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op));
	192	}
	193
	194	return;
	195
	196	case 'member_source':
	197	$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
	198	$value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
	199
	200	$query->_where[$grouping][] = "civicrm_membership.source $op '{$value}'";
	201	$query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op));
	202	$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
	203	return;
	204
3c53f77e	205	case 'membership_status':
6a488035	206	case 'member_status_id':
3c53f77e DL	207	if (!is_array($value)) {
	208	$status = $value;
	209	if (!empty($value)) {
	210	$value = array_flip(explode(",", str_replace(array( '(', ')' ), '', $value)));
	211	}
	212	else {
	213	$value = array();
	214	}
	215	}
	216	else {
	217	$status = implode(',', array_keys($value));
dbc6f6d6	218	if (count($value) > 0) {
3c53f77e DL	219	$op = 'IN';
	220	$status = "({$status})";
	221	}
6a488035 TO	222	}
	223
	224	$names = array();
	225	$statusTypes = CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label');
	226	foreach ($value as $id => $dontCare) {
	227	$names[] = $statusTypes[$id];
	228	}
	229	$query->_qill[$grouping][] = ts('Membership Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
	230	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.status_id",
	231	$op,
	232	$status,
	233	"Integer"
	234	);
	235	$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
	236	return;
	237
	238	case 'member_test':
28c666be CW	239	// We dont want to include all tests for sql OR CRM-7827
	240	if (!$value \|\| $query->getOperator() != 'OR') {
	241	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean");
	242	if ($value) {
	243	$query->_qill[$grouping][] = ts('Membership is a Test');
	244	}
6a488035	245	}
227690c8	246	$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
6a488035 TO	247	return;
	248
	249	case 'member_auto_renew':
	250	$op = "!=";
	251	if ($value) {
	252	$query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
	253	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
	254	"ccr.contribution_status_id",
	255	$op,
3ac2136c PN	256	array_search(
	257	'Cancelled',
	258	CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
6a488035 TO	259	),
	260	"Integer"
	261	);
	262	$query->_qill[$grouping][] = ts("Membership is Auto-Renew");
	263	}
	264	else {
	265	$query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
	266	$query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
	267	}
	268	$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
	269	return;
	270
	271	case 'member_pay_later':
	272	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later",
	273	$op,
	274	$value,
	275	"Integer"
	276	);
	277	if ($value) {
	278	$query->_qill[$grouping][] = ts("Membership is Pay Later");
	279	}
	280	else {
	281	$query->_qill[$grouping][] = ts("Membership is NOT Pay Later");
	282	}
	283	$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
	284	return;
	285
3c53f77e	286	case 'membership_type':
6a488035	287	case 'member_membership_type_id':
3c53f77e DL	288	if (!is_array($value)) {
	289	$mType = $value;
	290	if (!empty($value)) {
	291	$value = array_flip(explode(",", str_replace(array( '(', ')' ), '', $value)));
	292	}
	293	else {
	294	$value = array();
	295	}
	296	}
	297	else {
	298	$mType = implode(',', array_keys($value));
dbc6f6d6	299	if (count($value) > 0) {
3c53f77e DL	300	$op = 'IN';
	301	$mType = "({$mType})";
	302	}
6a488035 TO	303	}
	304
	305	$names = array();
	306	$membershipTypes = CRM_Member_PseudoConstant::membershipType();
	307	foreach ($value as $id => $dontCare) {
0ba53d3d EM	308	if(!empty($membershipTypes[$id])) {
	309	$names[] = $membershipTypes[$id];
	310	}
6a488035 TO	311	}
	312	$query->_qill[$grouping][] = ts('Membership Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
	313	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.membership_type_id",
	314	$op,
	315	$mType,
	316	"Integer"
	317	);
	318	$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
	319	return;
	320
	321	case 'member_id':
	322	$query->_where[$grouping][] = " civicrm_membership.id $op $value";
	323	$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
	324	return;
	325
	326	case 'member_is_primary':
	327	if ($value) {
	328	$query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
	329	$query->_qill[$grouping][] = ts("Primary Members Only");
	330	}
	331	else {
	332	$query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
	333	$query->_qill[$grouping][] = ts("Related Members Only");
	334	}
	335	$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
	336	return;
	337
	338	case 'member_campaign_id':
	339	$campParams = array(
	340	'op' => $op,
	341	'campaign' => $value,
	342	'grouping' => $grouping,
	343	'tableName' => 'civicrm_membership',
	344	);
	345	CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
	346	return;
	347	}
	348	}
	349
bb3a214a EM	350	/**
	351	* @param $name
	352	* @param $mode
	353	* @param $side
	354	*
	355	* @return null\|string
	356	*/
6a488035 TO	357	static function from($name, $mode, $side) {
	358	$from = NULL;
	359	switch ($name) {
	360	case 'civicrm_membership':
	361	$from = " $side JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id ";
	362	$from .= " $side JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )";
	363	break;
	364
	365	case 'civicrm_membership_type':
	366	if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
	367	$from = " INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
	368	}
	369	else {
	370	$from = " $side JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id ";
	371	}
	372	break;
	373
	374	case 'civicrm_membership_status':
	375	if ($mode & CRM_Contact_BAO_Query::MODE_MEMBER) {
	376	$from = " INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
	377	}
	378	else {
	379	$from = " $side JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id ";
	380	}
	381	break;
	382
	383	case 'civicrm_membership_payment':
	384	$from = " $side JOIN civicrm_membership_payment ON civicrm_membership_payment.membership_id = civicrm_membership.id ";
	385	break;
	386	}
	387	return $from;
	388	}
	389
bb3a214a EM	390	/**
	391	* @param $mode
	392	* @param bool $includeCustomFields
	393	*
	394	* @return array\|null
	395	*/
6a488035 TO	396	static function defaultReturnProperties($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
bb3a214a	435	/**
c490a46a	436	* @param CRM_Core_Form $form
bb3a214a	437	*/
6a488035 TO	438	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
	444	foreach (CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label') as $sId => $sName) {
	445	$form->_membershipStatus = &$form->addElement('checkbox', "member_status_id[$sId]", NULL, $sName);
	446	}
	447
	448	$form->addElement('text', 'member_source', ts('Source'));
	449
bc3f7f04	450	CRM_Core_Form_Date::buildDateRange($form, 'member_join_date', 1, '_low', '_high', ts('From'), FALSE);
6a488035	451
bc3f7f04	452	CRM_Core_Form_Date::buildDateRange($form, 'member_start_date', 1, '_low', '_high', ts('From'), FALSE);
6a488035	453
bc3f7f04	454	CRM_Core_Form_Date::buildDateRange($form, 'member_end_date', 1, '_low', '_high', ts('From'), FALSE);
6a488035	455
8a4f27dc CW	456	$form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
	457	$form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
	458	$form->addYesNo('member_auto_renew', ts('Auto-Renew?'), TRUE);
	459	$form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);
6a488035 TO	460
	461	// add all the custom searchable fields
	462	$extends = array('Membership');
	463	$groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
	464	if ($groupDetails) {
	465	$form->assign('membershipGroupTree', $groupDetails);
	466	foreach ($groupDetails as $group) {
	467	foreach ($group['fields'] as $field) {
	468	$fieldId = $field['id'];
	469	$elementName = 'custom_' . $fieldId;
	470	CRM_Core_BAO_CustomField::addQuickFormElement($form,
	471	$elementName,
	472	$fieldId,
	473	FALSE, FALSE, TRUE
	474	);
	475	}
	476	}
	477	}
	478
	479	CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'member_campaign_id');
	480
	481	$form->assign('validCiviMember', TRUE);
	482	$form->setDefaults(array('member_test' => 0));
	483	}
	484
bb3a214a EM	485	/**
	486	* @param $row
	487	* @param $id
	488	*/
6a488035 TO	489	static function searchAction(&$row, $id) {}
6a488035 TO	490
bb3a214a EM	491	/**
	492	* @param $tables
	493	*/
6a488035 TO	494	static function tableNames(&$tables) {
6a488035 TO	495	//add membership table
8cc574cf	496	if (!empty($tables['civicrm_membership_log']) \|\| !empty($tables['civicrm_membership_status']) \|\| CRM_Utils_Array::value('civicrm_membership_type', $tables)) {
6a488035 TO	497	$tables = array_merge(array('civicrm_membership' => 1), $tables);
	498	}
	499	}
	500	}
	501