/**
* Check to see if we have cache entries for this group
* if not, regenerate, else return
*
* @param int $groupID groupID of group that we are checking against
*
* @return boolean true if we did not regenerate, false if we did
*/
static function check($groupID)
{
if (empty($groupID)) {
return true;
}
if (!is_array($groupID)) {
$groupID = array($groupID);
}
// note escapeString is a must here and we can't send the imploded value as second arguement to
// the executeQuery(), since that would put single quote around the string and such a string
// of comma separated integers would not work.
$groupID = CRM_Core_DAO::escapeString(implode(', ', $groupID));
$config = CRM_Core_Config::singleton();
$smartGroupCacheTimeout = isset($config->smartGroupCacheTimeout) && is_numeric($config->smartGroupCacheTimeout) ? $config->smartGroupCacheTimeout : 0;
//make sure to give original timezone settings again.
$originalTimezone = date_default_timezone_get();
date_default_timezone_set('UTC');
$now = date('YmdHis');
date_default_timezone_set($originalTimezone);
$query = "\nSELECT g.id\nFROM civicrm_group g\nWHERE g.id IN ( {$groupID} ) AND ( g.saved_search_id IS NOT NULL OR g.children IS NOT NULL ) AND \n (g.cache_date IS NULL OR (TIMESTAMPDIFF(MINUTE, g.cache_date, {$now}) >= {$smartGroupCacheTimeout}))\n";
$dao =& CRM_Core_DAO::executeQuery($query);
$groupIDs = array();
while ($dao->fetch()) {
$groupIDs[] = $dao->id;
}
if (empty($groupIDs)) {
return true;
} else {
self::add($groupIDs);
return false;
}
}
/**
* @param $rg
*
* @return array
*/
public static function record($rg)
{
$civicrm_contact = CRM_Utils_Array::value('civicrm_contact', $rg->params);
$civicrm_address = CRM_Utils_Array::value('civicrm_address', $rg->params);
// Since definitely have first and last name, escape them upfront.
$first_name = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('first_name', $civicrm_contact, ''));
$last_name = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('last_name', $civicrm_contact, ''));
$street_address = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('street_address', $civicrm_address, ''));
$query = "\n SELECT contact1.id id1, {$rg->threshold} as weight\n FROM civicrm_contact AS contact1\n JOIN civicrm_address AS address1 ON contact1.id=address1.contact_id\n WHERE contact1.contact_type = 'Couple'\n AND contact1.first_name = '{$first_name}'\n AND contact1.last_name = '{$last_name}'\n AND address1.street_address = '{$street_address}'\n ";
if ($spouse_first_name = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('spouse_first_name', $civicrm_contact, ''))) {
$query .= " AND (AND contact1.spouse_first_name IS NULL or contact1.spouse_first_name = '{$spouse_first_name}')\n";
}
if ($spouse_last_name = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('spouse_last_name', $civicrm_contact, ''))) {
$query .= " AND (AND contact1.spouse_last_name IS NULL or contact1.spouse_last_name = '{$spouse_last_name}')\n";
}
if ($birth_date = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('birth_date', $civicrm_contact, ''))) {
$query .= " AND (contact1.birth_date IS NULL or contact1.birth_date = '{$birth_date}')\n";
}
if ($spouse_birth_date = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('spouse_birth_date', $civicrm_contact, ''))) {
$query .= " AND (contact1.spouse_birth_date IS NULL or contact1.spouse_birth_date = '{$spouse_birth_date}')\n";
}
if ($suffix_id = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('suffix_id', $civicrm_contact, ''))) {
$query .= " AND (contact1.suffix_id IS NULL or contact1.suffix_id = {$suffix_id})\n";
}
if ($spouse_suffix_id = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('spouse_suffix_id', $civicrm_contact, ''))) {
$query .= " AND (contact1.spouse_suffix_id IS NULL or contact1.spouse_suffix_id = {$spouse_suffix_id})\n";
}
if ($middle_name = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('middle_name', $civicrm_contact, ''))) {
$query .= " AND (contact1.middle_name IS NULL or contact1.middle_name = '{$middle_name}')\n";
}
if ($spouse_middle_name = CRM_Core_DAO::escapeString(CRM_Utils_Array::value('spouse_middle_name', $civicrm_contact, ''))) {
$query .= " AND (contact1.spouse_middle_name IS NULL or contact1.spouse_middle_name = '{$spouse_middle_name}')\n";
}
return array("civicrm_contact.{$rg->name}.{$rg->threshold}" => $query);
}
/**
* @return array
*/
public function mailing_select()
{
$data = array();
$mailing = new CRM_Mailing_BAO_Mailing();
$query = "SELECT name FROM civicrm_mailing WHERE sms_provider_id IS NULL";
$mailing->query($query);
while ($mailing->fetch()) {
$data[CRM_Core_DAO::escapeString($mailing->name)] = $mailing->name;
}
return $data;
}
function __construct(&$formValues)
{
$this->_formValues =& $formValues;
$this->_text = CRM_Utils_Array::value('text', $formValues);
$this->_table = CRM_Utils_Array::value('table', $formValues);
if (!$this->_text) {
$this->_text = CRM_Utils_Request::retrieve('text', 'String', CRM_Core_DAO::$_nullObject);
if ($this->_text) {
$this->_text = trim($this->_text);
$formValues['text'] = $this->_text;
}
}
if (!$this->_table) {
$this->_table = CRM_Utils_Request::retrieve('table', 'String', CRM_Core_DAO::$_nullObject);
if ($this->_table) {
$formValues['table'] = $this->_table;
}
}
// fix text to include wild card characters at begining and end
if ($this->_text) {
if (is_numeric($this->_text)) {
$this->_textID = $this->_text;
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$this->_text = $strtolower(CRM_Core_DAO::escapeString($this->_text));
if (strpos($this->_text, '%') === false) {
$this->_text = "'%{$this->_text}%'";
} else {
$this->_text = "'{$this->_text}'";
}
} else {
$this->_text = "'%'";
}
if (!$this->_table) {
$this->_limitClause = " LIMIT {$this->_limitNumber}";
$this->_limitRowClause = $this->_limitClause;
} else {
// when there is table specified, we would like to use the pager. But since
// 1. this custom search has slightly different structure ,
// 2. we are in constructor right now,
// we 'll use a small hack -
$rowCount = CRM_Utils_Pager::ROWCOUNT;
$pageId = CRM_Utils_Array::value('crmPID', $_REQUEST);
$pageId = $pageId ? $pageId : 1;
$offset = ($pageId - 1) * $rowCount;
$this->_limitClause = " LIMIT {$offset}, {$rowCount}";
$this->_limitRowClause = " LIMIT {$rowCount}";
}
$this->buildTempTable();
$this->fillTable();
}
function __construct(&$formValues)
{
$this->_formValues =& $formValues;
$this->_text = CRM_Utils_Array::value('text', $formValues);
$this->_table = CRM_Utils_Array::value('table', $formValues);
if (!$this->_text) {
$this->_text = CRM_Utils_Request::retrieve('text', 'String', CRM_Core_DAO::$_nullObject);
if ($this->_text) {
$this->_text = trim($this->_text);
$formValues['text'] = $this->_text;
}
}
if (!$this->_table) {
$this->_table = CRM_Utils_Request::retrieve('table', 'String', CRM_Core_DAO::$_nullObject);
if ($this->_table) {
$formValues['table'] = $this->_table;
}
}
// fix text to include wild card characters at begining and end
if ($this->_text) {
if (is_numeric($this->_text)) {
$this->_textID = $this->_text;
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$this->_text = $strtolower(CRM_Core_DAO::escapeString($this->_text));
if (strpos($this->_text, '%') === false) {
$this->_text = "'%{$this->_text}%'";
} else {
$this->_text = "'{$this->_text}'";
}
} else {
$this->_text = "'%'";
}
if (!$this->_table) {
$this->_limitClause = " LIMIT {$this->_limitNumber}";
}
$this->buildTempTable();
$this->fillTable();
}
/**
* Create a SQL expression for matching against a list of
* text columns.
*
* @param string $table eg "civicrm_note" or "civicrm_note mynote"
* @param array|string $fullTextFields list of field names
* @param string $queryText
* @return string SQL, eg "MATCH (col1) AGAINST (queryText)" or "col1 LIKE '%queryText%'"
*/
public function matchText($table, $fullTextFields, $queryText)
{
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
if (strpos($table, ' ') === FALSE) {
$tableName = $tableAlias = $table;
} else {
list($tableName, $tableAlias) = explode(' ', $table);
}
if (is_scalar($fullTextFields)) {
$fullTextFields = array($fullTextFields);
}
$clauses = array();
if (CRM_Core_InnoDBIndexer::singleton()->hasDeclaredIndex($tableName, $fullTextFields)) {
$formattedQuery = CRM_Utils_QueryFormatter::singleton()->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL);
$prefixedFieldNames = array();
foreach ($fullTextFields as $fieldName) {
$prefixedFieldNames[] = "{$tableAlias}.{$fieldName}";
}
$clauses[] = sprintf("MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)", implode(',', $prefixedFieldNames), $strtolower(CRM_Core_DAO::escapeString($formattedQuery)));
} else {
//CRM_Core_Session::setStatus(ts('Cannot use FTS for %1 (%2)', array(
// 1 => $table,
// 2 => implode(', ', $fullTextFields),
//)));
$formattedQuery = CRM_Utils_QueryFormatter::singleton()->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_LIKE);
$escapedText = $strtolower(CRM_Core_DAO::escapeString($formattedQuery));
foreach ($fullTextFields as $fieldName) {
$clauses[] = "{$tableAlias}.{$fieldName} LIKE '{$escapedText}'";
}
}
return implode(' OR ', $clauses);
}
/**
* Returns array of group object(s) matching a set of one or Group properties.
*
*
* @param array $param Array of one or more valid property_name=>value pairs. Limits the set of groups returned.
* @param array $returnProperties Which properties should be included in the returned group objects. (member_count should be last element.)
*
* @return An array of group objects.
*
* @access public
*/
static function getGroups($params = null, $returnProperties = null)
{
$dao =& new CRM_Contact_DAO_Group();
$dao->is_active = 1;
if ($params) {
foreach ($params as $k => $v) {
if ($k == 'name' || $k == 'title') {
$dao->whereAdd($k . ' LIKE "' . CRM_Core_DAO::escapeString($v) . '"');
} else {
if (is_array($v)) {
$dao->whereAdd($k . ' IN (' . implode(',', $v) . ')');
} else {
$dao->{$k} = $v;
}
}
}
}
// return only specific fields if returnproperties are sent
if (!empty($returnProperties)) {
$dao->selectAdd();
$dao->selectAdd(implode(',', $returnProperties));
}
$dao->find();
$flag = $returnProperties && in_array('member_count', $returnProperties) ? 1 : 0;
$groups = array();
while ($dao->fetch()) {
$group =& new CRM_Contact_DAO_Group();
if ($flag) {
$dao->member_count = CRM_Contact_BAO_Group::memberCount($dao->id);
}
$groups[] = clone $dao;
}
return $groups;
}
/**
* @param string $tableName
* @param $details
* @param $sqlColumns
*/
public static function writeDetailsToTable($tableName, &$details, &$sqlColumns)
{
if (empty($details)) {
return;
}
$sql = "\nSELECT max(id)\nFROM {$tableName}\n";
$id = CRM_Core_DAO::singleValueQuery($sql);
if (!$id) {
$id = 0;
}
$sqlClause = array();
foreach ($details as $dontCare => $row) {
$id++;
$valueString = array($id);
foreach ($row as $dontCare => $value) {
if (empty($value)) {
$valueString[] = "''";
} else {
$valueString[] = "'" . CRM_Core_DAO::escapeString($value) . "'";
}
}
$sqlClause[] = '(' . implode(',', $valueString) . ')';
}
$sqlColumnString = '(id, ' . implode(',', array_keys($sqlColumns)) . ')';
$sqlValueString = implode(",\n", $sqlClause);
$sql = "\nINSERT INTO {$tableName} {$sqlColumnString}\nVALUES {$sqlValueString}\n";
CRM_Core_DAO::executeQuery($sql);
}
/**
* where clause for a single field
*
* @return void
* @access public
*/
static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
switch ($name) {
case 'case_subject':
$value = strtolower(CRM_Core_DAO::escapeString(trim($value)));
$query->_where[$grouping][] = "civicrm_case.subject {$op} '{$value}'";
$query->_qill[$grouping][] = ts('Case Subject %2 %1', array(1 => $value, 2 => $op));
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_status_id':
require_once 'CRM/Core/OptionGroup.php';
$caseStatus = CRM_Core_OptionGroup::values('case_status');
$query->_where[$grouping][] = "civicrm_case.status_id {$op} {$value} ";
$value = $caseStatus[$value];
$query->_qill[$grouping][] = ts('Case Status %2 %1', array(1 => $value, 2 => $op));
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_type_id':
require_once 'CRM/Core/OptionGroup.php';
$caseType = CRM_Core_OptionGroup::values('case_type');
$names = array();
foreach ($value as $id => $val) {
$names[] = $caseType[$val];
}
require_once 'CRM/Case/BAO/Case.php';
$value = CRM_Case_BAO_Case::VALUE_SEPERATOR . implode(CRM_Case_BAO_Case::VALUE_SEPERATOR . "%' OR civicrm_case.case_type_id LIKE '%" . CRM_Case_BAO_Case::VALUE_SEPERATOR, $value) . CRM_Case_BAO_Case::VALUE_SEPERATOR;
$query->_where[$grouping][] = "(civicrm_case.case_type_id LIKE '%{$value}%')";
$value = $caseType[$value];
$query->_qill[$grouping][] = ts('Case Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_casetag2_id':
require_once 'CRM/Core/OptionGroup.php';
$caseSubtype = CRM_Core_OptionGroup::values('f1_case_sub_type');
$names = array();
foreach ($value as $id => $val) {
$names[] = $caseSubtype[$val];
}
require_once 'CRM/Case/BAO/Case.php';
$value = CRM_Case_BAO_Case::VALUE_SEPERATOR . implode(CRM_Case_BAO_Case::VALUE_SEPERATOR . "%' OR civicrm_case.casetag2_id LIKE '%" . CRM_Case_BAO_Case::VALUE_SEPERATOR, $value) . CRM_Case_BAO_Case::VALUE_SEPERATOR;
$query->_where[$grouping][] = "(civicrm_case.casetag2_id LIKE '%{$value}%')";
$value = $caseSubtype[$value];
$query->_qill[$grouping][] = ts('Case SubType %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_casetag3_id':
require_once 'CRM/Core/OptionGroup.php';
$caseViolation = CRM_Core_OptionGroup::values('f1_case_violation');
$names = array();
foreach ($value as $id => $val) {
$names[] = $caseViolation[$val];
}
require_once 'CRM/Case/BAO/Case.php';
$value = CRM_Case_BAO_Case::VALUE_SEPERATOR . implode(CRM_Case_BAO_Case::VALUE_SEPERATOR . "%' OR civicrm_case.casetag3_id LIKE '%" . CRM_Case_BAO_Case::VALUE_SEPERATOR, $value) . CRM_Case_BAO_Case::VALUE_SEPERATOR;
$query->_where[$grouping][] = "(civicrm_case.casetag3_id LIKE '%{$value}%')";
$value = $caseViolation[$value];
$query->_qill[$grouping][] = ts('Case Voilation %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
return;
case 'case_start_date_low':
case 'case_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_case', 'case_start_date', 'start_date', 'Start Date');
return;
}
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$quoteValue = NULL;
$fields = array_merge(CRM_Contribute_BAO_Contribution::fields(), self::getFields());
if (!empty($value) && !is_array($value)) {
$quoteValue = "\"{$value}\"";
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
foreach (self::getRecurringFields() as $dateField => $dateFieldTitle) {
if (self::buildDateWhere($values, $query, $name, $dateField, $dateFieldTitle)) {
return;
}
}
switch ($name) {
case 'contribution_date':
case 'contribution_date_low':
case 'contribution_date_low_time':
case 'contribution_date_high':
case 'contribution_date_high_time':
// process to / from date
$query->dateQueryBuilder($values, 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date');
return;
case 'contribution_amount':
case 'contribution_amount_low':
case 'contribution_amount_high':
// process min/max amount
$query->numberRangeBuilder($values, 'civicrm_contribution', 'contribution_amount', 'total_amount', 'Contribution Amount', NULL);
return;
case 'contribution_thankyou_date_is_not_null':
if ($value) {
$op = "IS NOT NULL";
$query->_qill[$grouping][] = ts('Contribution Thank-you Sent');
} else {
$op = "IS NULL";
$query->_qill[$grouping][] = ts('Contribution Thank-you Not Sent');
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.thankyou_date", $op);
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_receipt_date_is_not_null':
if ($value) {
$op = "IS NOT NULL";
$query->_qill[$grouping][] = ts('Contribution Receipt Sent');
} else {
$op = "IS NULL";
$query->_qill[$grouping][] = ts('Contribution Receipt Not Sent');
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.receipt_date", $op);
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'financial_type':
case 'contribution_page':
case 'payment_instrument':
case 'contribution_payment_instrument':
case 'contribution_status':
$name .= '_id';
case 'financial_type_id':
case 'payment_instrument_id':
case 'contribution_payment_instrument_id':
case 'contribution_page_id':
case 'contribution_status_id':
case 'contribution_id':
case 'contribution_currency_type':
case 'contribution_currency':
case 'contribution_source':
case 'contribution_trxn_id':
case 'contribution_check_number':
case 'contribution_contact_id':
case strpos($name, '_amount') !== FALSE:
case strpos($name, '_date') !== FALSE:
$qillName = $name;
$pseudoExtraParam = NULL;
if (strpos($name, '_amount') !== FALSE || strpos($name, '_date') !== FALSE || in_array($name, array('contribution_id', 'contribution_currency', 'contribution_source', 'contribution_trxn_id', 'contribution_check_number', 'contribution_payment_instrument_id', 'contribution_contact_id'))) {
$name = str_replace('contribution_', '', $name);
if (!in_array($name, array('source', 'id', 'contact_id'))) {
$qillName = str_replace('contribution_', '', $qillName);
}
}
if (in_array($name, array('contribution_currency', 'contribution_currency_type'))) {
$qillName = $name = 'currency';
$pseudoExtraParam = array('labelColumn' => 'name');
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.{$name}", $op, $value, $dataType);
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', $name, $value, $op, $pseudoExtraParam);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_pcp_made_through_id':
case 'contribution_soft_credit_type_id':
$qillName = $name;
if ($name == 'contribution_pcp_made_through_id') {
$qillName = $name = 'pcp_id';
$fields[$name] = array('title' => ts('Personal Campaign Page'), 'type' => 2);
}
if ($name == 'contribution_soft_credit_type_id') {
$qillName = str_replace('_id', '', $qillName);
$fields[$qillName]['type'] = $fields[$qillName]['data_type'];
$name = str_replace('contribution_', '', $name);
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution_soft.{$name}", $op, $value, CRM_Utils_Type::typeToString($fields[$qillName]['type']));
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_ContributionSoft', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
case 'contribution_or_softcredits':
if ($value == 'only_scredits') {
$query->_where[$grouping][] = "contribution_search_scredit_combined.scredit_id IS NOT NULL";
$query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits Only');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
} elseif ($value == 'both_related') {
$query->_where[$grouping][] = "contribution_search_scredit_combined.filter_id IS NOT NULL";
$query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits with related Hard Credit');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
} elseif ($value == 'both') {
$query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Both');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
}
// default option: $value == 'only_contribs'
return;
case 'contribution_is_test':
// By default is Contribution Search form we choose is_test = 0 in otherwords always show active contribution
// so in case if any one choose any Yes/No avoid the default clause otherwise it will be conflict in whereClause
$key = array_search('civicrm_contribution.is_test = 0', $query->_where[$grouping]);
if (!empty($key)) {
unset($query->_where[$grouping][$key]);
}
case 'contribution_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_contribution.is_test", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = ts("Only Display Test Contributions");
}
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
}
return;
case 'contribution_is_pay_later':
case 'contribution_pay_later':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = ts("Find Pay Later Contributions");
} else {
$query->_qill[$grouping][] = ts("Exclude Pay Later Contributions");
}
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_recurring':
if ($value) {
$query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
$query->_qill[$grouping][] = ts("Find Recurring Contributions");
$query->_tables['civicrm_contribution_recur'] = $query->_whereTables['civicrm_contribution_recur'] = 1;
} else {
$query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
$query->_qill[$grouping][] = ts("Exclude Recurring Contributions");
}
return;
case 'contribution_recur_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_recur_id", $op, $value, "Integer");
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_note':
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER(civicrm_note.note)" : "civicrm_note.note";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = $query->_whereTables['contribution_note'] = 1;
return;
case 'contribution_membership_id':
$query->_where[$grouping][] = " civicrm_membership.id {$op} {$value}";
$query->_tables['contribution_membership'] = $query->_whereTables['contribution_membership'] = 1;
return;
case 'contribution_participant_id':
$query->_where[$grouping][] = " civicrm_participant.id {$op} {$value}";
$query->_tables['contribution_participant'] = $query->_whereTables['contribution_participant'] = 1;
return;
case 'contribution_pcp_display_in_roll':
$query->_where[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll {$op} '{$value}'";
if ($value) {
$query->_qill[$grouping][] = ts("Personal Campaign Page Honor Roll");
} else {
$query->_qill[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
}
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
case 'contribution_campaign_id':
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_contribution');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
return;
case 'contribution_batch_id':
$batches = CRM_Contribute_PseudoConstant::batch();
$query->_where[$grouping][] = " civicrm_entity_batch.batch_id {$op} {$value}";
$query->_qill[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
$query->_tables['contribution_batch'] = $query->_whereTables['contribution_batch'] = 1;
return;
default:
//all other elements are handle in this case
$fldName = substr($name, 13);
if (!isset($fields[$fldName])) {
// CRM-12597
CRM_Core_Session::setStatus(ts('We did not recognize the search field: %1. Please check and fix your contribution related smart groups.', array(1 => $fldName)));
return;
}
$whereTable = $fields[$fldName];
$value = trim($value);
$dataType = "String";
if (!empty($whereTable['type'])) {
$dataType = CRM_Utils_Type::typeToString($whereTable['type']);
}
$wc = $op != 'LIKE' && $dataType != 'Date' ? "LOWER({$whereTable['where']})" : "{$whereTable['where']}";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, $dataType);
$query->_qill[$grouping][] = "{$whereTable['title']} {$op} {$quoteValue}";
list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
$query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
if ($tableName == 'civicrm_contribution_product') {
$query->_tables['civicrm_product'] = $query->_whereTables['civicrm_product'] = 1;
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
} else {
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
}
}
}
/**
* @param array $params
*
* @throws Exception
*/
public static function updateGreeting($params)
{
$contactType = $params['ct'];
$greeting = $params['gt'];
$valueID = $id = CRM_Utils_Array::value('id', $params);
$force = CRM_Utils_Array::value('force', $params);
$limit = CRM_Utils_Array::value('limit', $params);
// if valueID is not passed use default value
if (!$valueID) {
$valueID = $id = self::defaultGreeting($contactType, $greeting);
}
$filter = array('contact_type' => $contactType, 'greeting_type' => $greeting);
$allGreetings = CRM_Core_PseudoConstant::greeting($filter);
$originalGreetingString = $greetingString = CRM_Utils_Array::value($valueID, $allGreetings);
if (!$greetingString) {
CRM_Core_Error::fatal(ts('Incorrect greeting value id %1, or no default greeting for this contact type and greeting type.', array(1 => $valueID)));
}
// build return properties based on tokens
$greetingTokens = CRM_Utils_Token::getTokens($greetingString);
$tokens = CRM_Utils_Array::value('contact', $greetingTokens);
$greetingsReturnProperties = array();
if (is_array($tokens)) {
$greetingsReturnProperties = array_fill_keys(array_values($tokens), 1);
}
// Process ALL contacts only when force=1 or force=2 is passed. Else only contacts with NULL greeting or addressee value are updated.
$processAll = $processOnlyIdSet = FALSE;
if ($force == 1) {
$processAll = TRUE;
} elseif ($force == 2) {
$processOnlyIdSet = TRUE;
}
//FIXME : apiQuery should handle these clause.
$filterContactFldIds = $filterIds = array();
$idFldName = $displayFldName = NULL;
if (in_array($greeting, CRM_Contact_BAO_Contact::$_greetingTypes)) {
$idFldName = $greeting . '_id';
$displayFldName = $greeting . '_display';
}
if ($idFldName) {
$queryParams = array(1 => array($contactType, 'String'));
// if $force == 1 then update all contacts else only
// those with NULL greeting or addressee value CRM-9476
if ($processAll) {
$sql = "SELECT DISTINCT id, {$idFldName} FROM civicrm_contact WHERE contact_type = %1 ";
} else {
$sql = "\n SELECT DISTINCT id, {$idFldName}\n FROM civicrm_contact\n WHERE contact_type = %1\n AND ({$idFldName} IS NULL\n OR ( {$idFldName} IS NOT NULL AND ({$displayFldName} IS NULL OR {$displayFldName} = '')) )";
}
if ($limit) {
$sql .= " LIMIT 0, %2";
$queryParams += array(2 => array($limit, 'Integer'));
}
$dao = CRM_Core_DAO::executeQuery($sql, $queryParams);
while ($dao->fetch()) {
$filterContactFldIds[$dao->id] = $dao->{$idFldName};
if (!CRM_Utils_System::isNull($dao->{$idFldName})) {
$filterIds[$dao->id] = $dao->{$idFldName};
}
}
}
if (empty($filterContactFldIds)) {
$filterContactFldIds[] = 0;
}
// retrieve only required contact information
$extraParams[] = array('contact_type', '=', $contactType, 0, 0);
// we do token replacement in the replaceGreetingTokens hook
list($greetingDetails) = CRM_Utils_Token::getTokenDetails(array_keys($filterContactFldIds), $greetingsReturnProperties, FALSE, FALSE, $extraParams);
// perform token replacement and build update SQL
$contactIds = array();
$cacheFieldQuery = "UPDATE civicrm_contact SET {$greeting}_display = CASE id ";
foreach ($greetingDetails as $contactID => $contactDetails) {
if (!$processAll && !array_key_exists($contactID, $filterContactFldIds)) {
continue;
}
if ($processOnlyIdSet && !array_key_exists($contactID, $filterIds)) {
continue;
}
if ($id) {
$greetingString = $originalGreetingString;
$contactIds[] = $contactID;
} else {
if ($greetingBuffer = CRM_Utils_Array::value($filterContactFldIds[$contactID], $allGreetings)) {
$greetingString = $greetingBuffer;
}
}
self::processGreetingTemplate($greetingString, $contactDetails, $contactID, 'CRM_UpdateGreeting');
$greetingString = CRM_Core_DAO::escapeString($greetingString);
$cacheFieldQuery .= " WHEN {$contactID} THEN '{$greetingString}' ";
$allContactIds[] = $contactID;
}
if (!empty($allContactIds)) {
$cacheFieldQuery .= " ELSE {$greeting}_display\n END;";
if (!empty($contactIds)) {
// need to update greeting _id field.
// reset greeting _custom
$resetCustomGreeting = '';
if ($valueID != 4) {
$resetCustomGreeting = ", {$greeting}_custom = NULL ";
}
$queryString = "\nUPDATE civicrm_contact\nSET {$greeting}_id = {$valueID}\n {$resetCustomGreeting}\nWHERE id IN (" . implode(',', $contactIds) . ")";
CRM_Core_DAO::executeQuery($queryString);
}
// now update cache field
CRM_Core_DAO::executeQuery($cacheFieldQuery);
}
}
/**
* Verify that a variable is of a given type, and apply a bit of processing.
*
* @param mixed $data
* The value to be verified/escaped.
* @param string $type
* The type to verify against.
* @param bool $abort
* If TRUE, the operation will CRM_Core_Error::fatal() on invalid data.
*
* @return mixed
* The data, escaped if necessary.
*/
public static function escape($data, $type, $abort = TRUE)
{
switch ($type) {
case 'Integer':
case 'Int':
if (CRM_Utils_Rule::integer($data)) {
return (int) $data;
}
break;
case 'Positive':
// CRM-8925 the 3 below are for custom fields of this type
// CRM-8925 the 3 below are for custom fields of this type
case 'Country':
case 'StateProvince':
// Checked for multi valued state/country value
if (is_array($data)) {
$returnData = TRUE;
// @todo Reuse of the $data variable = asking for trouble.
// @todo This code will always return the last item in the array. Intended?
foreach ($data as $data) {
if (CRM_Utils_Rule::positiveInteger($data) || CRM_Core_DAO::escapeString($data)) {
$returnData = TRUE;
} else {
$returnData = FALSE;
}
}
if ($returnData) {
return $data;
}
} elseif (!is_numeric($data) && CRM_Core_DAO::escapeString($data)) {
return $data;
} elseif (CRM_Utils_Rule::positiveInteger($data)) {
return $data;
}
break;
case 'File':
if (CRM_Utils_Rule::positiveInteger($data)) {
return $data;
}
break;
case 'Link':
if (CRM_Utils_Rule::url($data = trim($data))) {
return $data;
}
break;
case 'Boolean':
if (CRM_Utils_Rule::boolean($data)) {
return $data;
}
break;
case 'Float':
case 'Money':
if (CRM_Utils_Rule::numeric($data)) {
return $data;
}
break;
case 'String':
case 'Memo':
case 'Text':
return CRM_Core_DAO::escapeString($data);
case 'Date':
case 'Timestamp':
// a null date or timestamp is valid
if (strlen(trim($data)) == 0) {
return trim($data);
}
if ((preg_match('/^\\d{8}$/', $data) || preg_match('/^\\d{14}$/', $data)) && CRM_Utils_Rule::mysqlDate($data)) {
return $data;
}
break;
case 'ContactReference':
if (strlen(trim($data)) == 0) {
return trim($data);
}
if (CRM_Utils_Rule::validContact($data)) {
return $data;
}
break;
default:
CRM_Core_Error::fatal($type . " is not a recognised (camel cased) data type.");
break;
}
// @todo Use exceptions instead of CRM_Core_Error::fatal().
if ($abort) {
$data = htmlentities($data);
CRM_Core_Error::fatal("{$data} is not of the type {$type}");
}
return NULL;
}
/**
* Returns array of group object(s) matching a set of one or Group properties.
*
* @param array $params
* Limits the set of groups returned.
* @param array $returnProperties
* Which properties should be included in the returned group objects.
* (member_count should be last element.)
* @param string $sort
* @param int $offset
* @param int $rowCount
*
* @return array
* Array of group objects.
*
*
* @todo other BAO functions that use returnProperties (e.g. Query Objects) receive the array flipped & filled with 1s and
* add in essential fields (e.g. id). This should follow a regular pattern like the others
*/
public static function getGroups($params = NULL, $returnProperties = NULL, $sort = NULL, $offset = NULL, $rowCount = NULL)
{
$dao = new CRM_Contact_DAO_Group();
if (!isset($params['is_active'])) {
$dao->is_active = 1;
}
if ($params) {
foreach ($params as $k => $v) {
if ($k == 'name' || $k == 'title') {
$dao->whereAdd($k . ' LIKE "' . CRM_Core_DAO::escapeString($v) . '"');
} elseif ($k == 'group_type') {
foreach ((array) $v as $type) {
$dao->whereAdd($k . " LIKE '%" . CRM_Core_DAO::VALUE_SEPARATOR . (int) $type . CRM_Core_DAO::VALUE_SEPARATOR . "%'");
}
} elseif (is_array($v)) {
foreach ($v as &$num) {
$num = (int) $num;
}
$dao->whereAdd($k . ' IN (' . implode(',', $v) . ')');
} else {
$dao->{$k} = $v;
}
}
}
if ($offset || $rowCount) {
$offset = $offset > 0 ? $offset : 0;
$rowCount = $rowCount > 0 ? $rowCount : 25;
$dao->limit($offset, $rowCount);
}
if ($sort) {
$dao->orderBy($sort);
}
// return only specific fields if returnproperties are sent
if (!empty($returnProperties)) {
$dao->selectAdd();
$dao->selectAdd(implode(',', $returnProperties));
}
$dao->find();
$flag = $returnProperties && in_array('member_count', $returnProperties) ? 1 : 0;
$groups = array();
while ($dao->fetch()) {
$group = new CRM_Contact_DAO_Group();
if ($flag) {
$dao->member_count = CRM_Contact_BAO_Group::memberCount($dao->id);
}
$groups[] = clone $dao;
}
return $groups;
}
/**
* Determine if given entity (sub)type has any custom groups
*
* @param string $extends
* E.g. "Individual", "Activity".
* @param int $columnId
* E.g. custom-group matching mechanism (usu NULL for matching on sub type-id); see extends_entity_column_id.
* @param string $columnValue
* E.g. "Student" or "3" or "3\05"; see extends_entity_column_value.
*
* @return bool
*/
public static function hasCustomGroup($extends, $columnId, $columnValue)
{
$dao = new CRM_Core_DAO_CustomGroup();
$dao->extends = $extends;
$dao->extends_entity_column_id = $columnId;
$escapedValue = CRM_Core_DAO::VALUE_SEPARATOR . CRM_Core_DAO::escapeString($columnValue) . CRM_Core_DAO::VALUE_SEPARATOR;
$dao->whereAdd("extends_entity_column_value LIKE \"%{$escapedValue}%\"");
//$dao->extends_entity_column_value = $columnValue;
return $dao->find() ? TRUE : FALSE;
}
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', 'Join Date', false);
return;
case 'member_start_date_low':
case 'member_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_membership', 'member_start_date', 'start_date', 'Start Date', false);
return;
case 'member_end_date_low':
case 'member_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_membership', 'member_end_date', 'end_date', 'End Date', false);
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':
$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 'member_status_id':
require_once 'CRM/Member/PseudoConstant.php';
$status = implode(',', array_keys($value));
if (count($value) > 1) {
$op = 'IN';
$status = "({$status})";
}
$names = array();
$statusTypes = CRM_Member_PseudoConstant::membershipStatus();
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][] = "civicrm_membership.status_id {$op} {$status}";
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_test':
$query->_where[$grouping][] = " civicrm_membership.is_test {$op} {$value}";
if ($value) {
$query->_qill[$grouping][] = "Find Test Memberships";
}
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_pay_later':
$query->_where[$grouping][] = " civicrm_membership.is_pay_later {$op} {$value}";
if ($value) {
$query->_qill[$grouping][] = "Find Pay Later Memberships";
}
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_membership_type_id':
require_once 'CRM/Member/PseudoConstant.php';
$mType = implode(',', array_keys($value));
if (count($value) > 1) {
$op = 'IN';
$mType = "({$mType})";
}
$names = array();
$membershipTypes = CRM_Member_PseudoConstant::membershipType();
foreach ($value as $id => $dontCare) {
$names[] = $membershipTypes[$id];
}
$query->_qill[$grouping][] = ts('Membership Type %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = "civicrm_membership.membership_type_id {$op} {$mType}";
$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':
switch ($value) {
case 1:
$query->_qill[$grouping][] = "Primary AND Related Members";
break;
case 2:
$query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL";
$query->_qill[$grouping][] = "Primary Members Only";
break;
case 3:
$query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL";
$query->_qill[$grouping][] = "Related Members Only";
break;
}
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
}
}
/**
* Build the form object.
*
*
* @return void
*/
public function buildQuickForm()
{
parent::buildQuickForm();
$this->addSortNameField();
if (CRM_Core_Permission::check('access deleted contacts') and Civi::settings()->get('contact_undelete')) {
$this->addElement('checkbox', 'deleted_contacts', ts('Search in Trash') . '<br />' . ts('(deleted contacts)'));
}
CRM_Event_BAO_Query::buildSearchForm($this);
$rows = $this->get('rows');
if (is_array($rows)) {
$lineItems = $eventIds = array();
if (!$this->_single) {
$this->addRowSelectors($rows);
}
foreach ($rows as $row) {
$eventIds[$row['event_id']] = $row['event_id'];
if (CRM_Event_BAO_Event::usesPriceSet($row['event_id'])) {
// add line item details if applicable
$lineItems[$row['participant_id']] = CRM_Price_BAO_LineItem::getLineItems($row['participant_id']);
}
}
//get actual count only when we are dealing w/ single event.
$participantCount = 0;
if (count($eventIds) == 1) {
//convert form values to clause.
$seatClause = array();
if (CRM_Utils_Array::value('participant_test', $this->_formValues) == '1' || CRM_Utils_Array::value('participant_test', $this->_formValues) == '0') {
$seatClause[] = "( participant.is_test = {$this->_formValues['participant_test']} )";
}
if (!empty($this->_formValues['participant_status_id'])) {
$seatClause[] = CRM_Contact_BAO_Query::buildClause("participant.status_id", '=', $this->_formValues['participant_status_id'], 'Int');
if ($status = CRM_Utils_Array::value('IN', $this->_formValues['participant_status_id'])) {
$this->_formValues['participant_status_id'] = $status;
}
}
if (!empty($this->_formValues['participant_role_id'])) {
$escapedRoles = array();
foreach ((array) $this->_formValues['participant_role_id'] as $participantRole) {
$escapedRoles[] = CRM_Utils_Type::escape($participantRole, 'String');
}
$seatClause[] = "( participant.role_id IN ( '" . implode("' , '", $escapedRoles) . "' ) )";
}
// CRM-15379
if (!empty($this->_formValues['participant_fee_id'])) {
$participant_fee_id = $this->_formValues['participant_fee_id'];
foreach ($participant_fee_id as $k => &$val) {
$val = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $val, 'label');
$val = CRM_Core_DAO::escapeString(trim($val));
}
$feeLabel = implode('|', $participant_fee_id);
$seatClause[] = "( participant.fee_level REGEXP '{$feeLabel}' )";
}
$seatClause = implode(' AND ', $seatClause);
$participantCount = CRM_Event_BAO_Event::eventTotalSeats(array_pop($eventIds), $seatClause);
}
$this->assign('participantCount', $participantCount);
$this->assign('lineItems', $lineItems);
$permission = CRM_Core_Permission::getPermission();
$tasks = CRM_Event_Task::permissionedTaskTitles($permission);
if (isset($this->_ssID)) {
if ($permission == CRM_Core_Permission::EDIT) {
$tasks = $tasks + CRM_Event_Task::optionalTaskTitle();
}
$savedSearchValues = array('id' => $this->_ssID, 'name' => CRM_Contact_BAO_SavedSearch::getName($this->_ssID, 'title'));
$this->assign_by_ref('savedSearch', $savedSearchValues);
$this->assign('ssID', $this->_ssID);
}
$this->addTaskMenu($tasks);
}
}
static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array();
$fields = self::getFields();
if (!empty($value)) {
$quoteValue = "\"{$value}\"";
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
switch ($name) {
case 'contribution_date':
case 'contribution_date_low':
case 'contribution_date_high':
// process to / from date
$query->dateQueryBuilder($values, 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date', false);
return;
case 'contribution_amount':
case 'contribution_amount_low':
case 'contribution_amount_high':
// process min/max amount
$query->numberRangeBuilder($values, 'civicrm_contribution', 'contribution_amount', 'total_amount', 'Contribution Amount');
return;
case 'contribution_total_amount':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.total_amount", $op, $value, "Money");
$query->_qill[$grouping][] = ts('Contribution Total Amount %1 %2', array(1 => $op, 2 => $value));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_thankyou_date_isnull':
$query->_where[$grouping][] = "civicrm_contribution.thankyou_date is null";
$query->_qill[$grouping][] = ts('Contribution Thank-you date is null');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_receipt_date_isnull':
$query->_where[$grouping][] = "civicrm_contribution.receipt_date is null";
$query->_qill[$grouping][] = ts('Contribution Receipt date is null');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_type_id':
case 'contribution_type':
require_once 'CRM/Contribute/PseudoConstant.php';
$cType = $value;
$types = CRM_Contribute_PseudoConstant::contributionType();
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_type_id", $op, $value, "Integer");
$query->_qill[$grouping][] = ts('Contribution Type - %1', array(1 => $types[$cType]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_page_id':
require_once 'CRM/Contribute/PseudoConstant.php';
$cPage = $value;
$pages = CRM_Contribute_PseudoConstant::contributionPage();
$query->_where[$grouping][] = "civicrm_contribution.contribution_page_id = {$cPage}";
$query->_qill[$grouping][] = ts('Contribution Page - %1', array(1 => $pages[$cPage]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_pcp_made_through_id':
require_once 'CRM/Contribute/PseudoConstant.php';
$pcPage = $value;
$pcpages = CRM_Contribute_PseudoConstant::pcPage();
$query->_where[$grouping][] = "civicrm_contribution_soft.pcp_id = {$pcPage}";
$query->_qill[$grouping][] = ts('Personal Campaign Page - %1', array(1 => $pcpages[$pcPage]));
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
case 'contribution_payment_instrument_id':
case 'contribution_payment_instrument':
require_once 'CRM/Contribute/PseudoConstant.php';
$pi = $value;
$pis = CRM_Contribute_PseudoConstant::paymentInstrument();
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.payment_instrument_id", $op, $value, "Integer");
$query->_qill[$grouping][] = ts('Paid By - %1', array(1 => $pis[$pi]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_in_honor_of':
$name = trim($value);
$newName = str_replace(',', " ", $name);
$pieces = explode(' ', $newName);
foreach ($pieces as $piece) {
$value = $strtolower(CRM_Core_DAO::escapeString(trim($piece)));
$value = "'%{$value}%'";
$sub[] = " ( contact_b.sort_name LIKE {$value} )";
}
$query->_where[$grouping][] = ' ( ' . implode(' OR ', $sub) . ' ) ';
$query->_qill[$grouping][] = ts('Honor name like - \'%1\'', array(1 => $name));
$query->_tables['civicrm_contact_b'] = $query->_whereTables['civicrm_contact_b'] = 1;
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_status_id':
if (is_array($value)) {
foreach ($value as $k => $v) {
if ($v) {
$val[$k] = $k;
}
}
$status = implode(',', $val);
if (count($val) > 1) {
$op = 'IN';
$status = "({$status})";
}
} else {
$status = $value;
}
require_once "CRM/Core/OptionGroup.php";
$statusValues = CRM_Core_OptionGroup::values("contribution_status");
$names = array();
if (is_array($val)) {
foreach ($val as $id => $dontCare) {
$names[] = $statusValues[$id];
}
} else {
$names[] = $statusValues[$value];
}
$query->_qill[$grouping][] = ts('Contribution Status %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
$query->_where[$grouping][] = "civicrm_contribution.contribution_status_id {$op} {$status}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_source':
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.source)" : "civicrm_contribution.source";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = "Contribution Source {$op} {$quoteValue}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_trxn_id':
case 'contribution_transaction_id':
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.trxn_id)" : "civicrm_contribution.trxn_id";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = "Transaction ID {$op} {$quoteValue}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_check_number':
$wc = $op != 'LIKE' ? "LOWER(civicrm_contribution.check_number)" : "civicrm_contribution.check_number";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = "Check Number {$op} {$quoteValue}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_is_test':
case 'contribution_test':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = "Find Test Contributions";
}
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_is_pay_later':
case 'contribution_pay_later':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = "Find Pay Later Contributions";
}
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_recurring':
$query->_qill[$grouping][] = "Displaying Recurring Contributions";
$query->_tables['civicrm_contribution_recur'] = $query->_whereTables['civicrm_contribution_recur'] = 1;
return;
case 'contribution_recur_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_recur_id", $op, $value, "Integer");
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.id", $op, $value, "Integer");
$query->_qill[$grouping][] = "Contribution ID {$op} {$quoteValue}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_note':
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER(civicrm_note.note)" : "civicrm_note.note";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = "Contribution Note {$op} {$quoteValue}";
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = $query->_whereTables['contribution_note'] = 1;
return;
case 'contribution_membership_id':
$query->_where[$grouping][] = " civicrm_membership.id {$op} {$value}";
$query->_tables['contribution_membership'] = $query->_whereTables['contribution_membership'] = 1;
return;
case 'contribution_participant_id':
$query->_where[$grouping][] = " civicrm_participant.id {$op} {$value}";
$query->_tables['contribution_participant'] = $query->_whereTables['contribution_participant'] = 1;
return;
case 'contribution_pcp_display_in_roll':
$query->_where[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll {$op} '{$value}'";
if ($value) {
$query->_qill[$grouping][] = "Display in Roll";
}
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
default:
//all other elements are handle in this case
$fldName = substr($name, 13);
$whereTable = $fields[$fldName];
$value = trim($value);
//contribution fields (decimal fields) which don't require a quote in where clause.
$moneyFields = array('non_deductible_amount', 'fee_amount', 'net_amount');
//date fields
$dateFields = array('receive_date', 'cancel_date', 'receipt_date', 'thankyou_date', 'fulfilled_date');
if (in_array($fldName, $dateFields)) {
$dataType = "Date";
} elseif (in_array($fldName, $moneyFields)) {
$dataType = "Money";
} else {
$dataType = "String";
}
$wc = $op != 'LIKE' && $dataType != 'Date' ? "LOWER({$whereTable['where']})" : "{$whereTable['where']}";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, $dataType);
$query->_qill[$grouping][] = "{$whereTable['title']} {$op} {$quoteValue}";
list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
$query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
if ($tableName == 'civicrm_contribution_product') {
$query->_tables['civicrm_product'] = $query->_whereTables['civicrm_product'] = 1;
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
} else {
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
}
}
}
public function updateConstructedNames()
{
require_once 'CRM/Utils/Address.php';
require_once 'CRM/Core/BAO/Preferences.php';
require_once 'CRM/Core/DAO.php';
require_once 'CRM/Core/PseudoConstant.php';
require_once 'CRM/Contact/BAO/Contact.php';
//handle individuals using settings in the system
$query = "SELECT * FROM civicrm_contact WHERE contact_type = 'Individual';";
$dao = CRM_Core_DAO::executeQuery($query);
$prefixes = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'prefix_id');
$suffixes = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'suffix_id');
$tokens = array();
CRM_Utils_Hook::tokens($tokens);
$tokenFields = array();
foreach ($tokens as $category => $catTokens) {
foreach ($catTokens as $token) {
$tokenFields[] = $token;
}
}
//determine sort name construction
$sortFormat = CRM_Core_BAO_Preferences::value('sort_name_format');
$sortFormat = str_replace('contact.', '', $sortFormat);
//determine display name construction
$displayFormat = CRM_Core_BAO_Preferences::value('display_name_format');
$displayFormat = str_replace('contact.', '', $displayFormat);
while ($dao->fetch()) {
$contactID = $dao->id;
$params = array('first_name' => $dao->first_name, 'middle_name' => $dao->middle_name, 'last_name' => $dao->last_name, 'prefix_id' => $dao->prefix_id, 'suffix_id' => $dao->suffix_id);
$params['individual_prefix'] = $prefixes[$dao->prefix_id];
$params['individual_suffix'] = $suffixes[$dao->suffix_id];
$sortName = CRM_Utils_Address::format($params, $sortFormat, FALSE, FALSE, TRUE, $tokenFields);
$sortName = trim(CRM_Core_DAO::escapeString($sortName));
$displayName = CRM_Utils_Address::format($params, $displayFormat, FALSE, FALSE, TRUE, $tokenFields);
$displayName = trim(CRM_Core_DAO::escapeString($displayName));
//check for email
if (empty($sortName) || empty($displayName)) {
$email = NULL;
$email = CRM_Contact_BAO_Contact::getPrimaryEmail($contactID);
if (empty($email)) {
$email = $contactID;
}
if (empty($sortName)) {
$sortName = $email;
}
if (empty($displayName)) {
$displayName = $email;
}
}
//update record
$updateQuery = "UPDATE civicrm_contact SET display_name = '{$displayName}', sort_name = '{$sortName}' WHERE id = {$contactID};";
CRM_Core_DAO::executeQuery($updateQuery);
}
//end indiv
echo "\n Individuals recached... ";
//set organizations
$query = "UPDATE civicrm_contact\n\t\t SET display_name = organization_name,\n\t\t\t\t sort_name = organization_name\n\t\t\t WHERE contact_type = 'Organization';";
$dao = CRM_Core_DAO::executeQuery($query);
echo "\n Organizations recached... ";
//set households
$query = "UPDATE civicrm_contact\n\t\t SET display_name = household_name,\n\t\t\t\t sort_name = household_name\n\t\t\t WHERE contact_type = 'Household';";
$dao = CRM_Core_DAO::executeQuery($query);
echo "\n Households recached... ";
}
/**
* Form rule to send out a test mailing.
*
* @param aray $testParams
* @param array $files
* Any files posted to the form.
* @param array $self
* An current this object.
*
* @return bool
* true on successful SMTP handoff
*/
public static function testMail($testParams, $files, $self)
{
$error = NULL;
$urlString = 'civicrm/mailing/send';
$urlParams = "_qf_Test_display=true&qfKey={$testParams['qfKey']}";
$ssID = $self->get('ssID');
if ($ssID && $self->_searchBasedMailing) {
if ($self->_action == CRM_Core_Action::BASIC) {
$fragment = 'search';
} elseif ($self->_action == CRM_Core_Action::PROFILE) {
$fragment = 'search/builder';
} elseif ($self->_action == CRM_Core_Action::ADVANCED) {
$fragment = 'search/advanced';
} else {
$fragment = 'search/custom';
}
$urlString = 'civicrm/contact/' . $fragment;
}
$emails = NULL;
if (!empty($testParams['sendtest'])) {
if (!($testParams['test_group'] || $testParams['test_email'])) {
CRM_Core_Session::setStatus(ts('You did not provide an email address or select a group.'), ts('Test not sent.'), 'error');
$error = TRUE;
}
if ($testParams['test_email']) {
$emailAdd = explode(',', $testParams['test_email']);
foreach ($emailAdd as $key => $value) {
$email = trim($value);
$testParams['emails'][] = $email;
$emails .= ($emails ? ',' : '') . "'" . CRM_Core_DAO::escapeString($email) . "'";
if (!CRM_Utils_Rule::email($email)) {
CRM_Core_Session::setStatus(ts('Please enter a valid email address.'), ts('Test not sent.'), 'error');
$error = TRUE;
}
}
}
if ($error) {
$url = CRM_Utils_System::url($urlString, $urlParams);
CRM_Utils_System::redirect($url);
return $error;
}
}
if (!empty($testParams['_qf_Test_submit'])) {
//when user perform mailing from search context
//redirect it to search result CRM-3711.
if ($ssID && $self->_searchBasedMailing) {
$draftURL = CRM_Utils_System::url('civicrm/mailing/browse/unscheduled', 'scheduled=false&reset=1');
$status = ts("You can continue later by clicking the 'Continue' action to resume working on it.<br />From <a href='%1'>Draft and Unscheduled Mailings</a>.", array(1 => $draftURL));
//replace user context to search.
$context = $self->get('context');
if (!CRM_Contact_Form_Search::isSearchContext($context)) {
$context = 'search';
}
$urlParams = "force=1&reset=1&ssID={$ssID}&context={$context}&qfKey={$testParams['qfKey']}";
$url = CRM_Utils_System::url($urlString, $urlParams);
} else {
$status = ts("Click the 'Continue' action to resume working on it.");
$url = CRM_Utils_System::url('civicrm/mailing/browse/unscheduled', 'scheduled=false&reset=1');
}
CRM_Core_Session::setStatus($status, ts('Mailing Saved'), 'success');
CRM_Utils_System::redirect($url);
}
if (CRM_Mailing_Info::workflowEnabled()) {
if (!CRM_Core_Permission::check('schedule mailings') && CRM_Core_Permission::check('create mailings')) {
$url = CRM_Utils_System::url('civicrm/mailing/browse/unscheduled', 'scheduled=false&reset=1');
CRM_Utils_System::redirect($url);
}
}
if (!empty($testParams['_qf_Test_next']) && $self->get('count') <= 0) {
return array('_qf_default' => ts("You can not schedule or send this mailing because there are currently no recipients selected. Click 'Previous' to return to the Select Recipients step, OR click 'Save & Continue Later'."));
}
if (!empty($_POST['_qf_Import_refresh']) || !empty($testParams['_qf_Test_next']) || empty($testParams['sendtest'])) {
$error = TRUE;
return $error;
}
$job = new CRM_Mailing_BAO_MailingJob();
$job->mailing_id = $self->get('mailing_id');
$job->is_test = TRUE;
$job->save();
$newEmails = NULL;
$session = CRM_Core_Session::singleton();
if (!empty($testParams['emails'])) {
$query = "\nSELECT e.id, e.contact_id, e.email\nFROM civicrm_email e\nINNER JOIN civicrm_contact c ON e.contact_id = c.id\nWHERE e.email IN ({$emails})\nAND e.on_hold = 0\nAND c.is_opt_out = 0\nAND c.do_not_email = 0\nAND c.is_deleted = 0\nAND c.is_deceased = 0\nGROUP BY e.id\nORDER BY e.is_bulkmail DESC, e.is_primary DESC\n";
$dao = CRM_Core_DAO::executeQuery($query);
$emailDetail = array();
// fetch contact_id and email id for all existing emails
while ($dao->fetch()) {
$emailDetail[$dao->email] = array('contact_id' => $dao->contact_id, 'email_id' => $dao->id);
}
$dao->free();
foreach ($testParams['emails'] as $key => $email) {
// Email addresses are forced to lower case when saved, so ensure
// we have the same case when comparing.
$email = trim(strtolower($email));
$contactId = $emailId = NULL;
if (array_key_exists($email, $emailDetail)) {
$emailId = $emailDetail[$email]['email_id'];
$contactId = $emailDetail[$email]['contact_id'];
}
if (!$contactId) {
//create new contact.
$params = array('contact_type' => 'Individual', 'email' => array(1 => array('email' => $email, 'is_primary' => 1, 'location_type_id' => 1)));
$contact = CRM_Contact_BAO_Contact::create($params);
$emailId = $contact->email[0]->id;
$contactId = $contact->id;
$contact->free();
}
$params = array('job_id' => $job->id, 'email_id' => $emailId, 'contact_id' => $contactId);
CRM_Mailing_Event_BAO_Queue::create($params);
}
}
$testParams['job_id'] = $job->id;
$isComplete = FALSE;
while (!$isComplete) {
$isComplete = CRM_Mailing_BAO_MailingJob::runJobs($testParams);
}
if (!empty($testParams['sendtest'])) {
$status = NULL;
if (CRM_Mailing_Info::workflowEnabled()) {
if (CRM_Core_Permission::check('schedule mailings') && CRM_Core_Permission::check('create mailings') || CRM_Core_Permission::check('access CiviMail')) {
$status = ts("Click 'Next' when you are ready to Schedule or Send your live mailing (you will still have a chance to confirm or cancel sending this mailing on the next page).");
}
} else {
$status = ts("Click 'Next' when you are ready to Schedule or Send your live mailing (you will still have a chance to confirm or cancel sending this mailing on the next page).");
}
if ($status) {
CRM_Core_Session::setStatus($status, ts('Test message sent'), 'success');
}
$url = CRM_Utils_System::url($urlString, $urlParams);
CRM_Utils_System::redirect($url);
}
$error = TRUE;
return $error;
}
static function whereClauseSingle(&$values, &$query)
{
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
list($name, $op, $value, $grouping, $wildcard) = $values;
switch ($name) {
case 'grant_money_transfer_date_low':
case 'grant_money_transfer_date_high':
$query->dateQueryBuilder($values, 'civicrm_grant', 'grant_money_transfer_date', 'money_transfer_date', 'Money Transfer Date');
return;
case 'grant_money_transfer_date_notset':
$query->_where[$grouping][] = "civicrm_grant.money_transfer_date IS NULL";
$query->_qill[$grouping][] = ts("Grant Money Transfer Date is NULL");
$query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
return;
case 'grant_application_received_date_low':
case 'grant_application_received_date_high':
$query->dateQueryBuilder($values, 'civicrm_grant', 'grant_application_received_date', 'application_received_date', 'Application Received Date');
return;
case 'grant_application_received_notset':
$query->_where[$grouping][] = "civicrm_grant.application_received_date IS NULL";
$query->_qill[$grouping][] = ts("Grant Application Received Date is NULL");
$query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
return;
case 'grant_due_date_low':
case 'grant_due_date_high':
$query->dateQueryBuilder($values, 'civicrm_grant', 'grant_due_date', 'grant_due_date', 'Grant Due Date');
return;
case 'grant_due_date_notset':
$query->_where[$grouping][] = "civicrm_grant.grant_due_date IS NULL";
$query->_qill[$grouping][] = ts("Grant Due Date is NULL");
$query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
return;
case 'grant_decision_date_low':
case 'grant_decision_date_high':
$query->dateQueryBuilder($values, 'civicrm_grant', 'grant_decision_date', 'decision_date', 'Grant Decision Date');
return;
case 'grant_decision_date_notset':
$query->_where[$grouping][] = "civicrm_grant.decision_date IS NULL";
$query->_qill[$grouping][] = ts("Grant Decision Date is NULL");
$query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
return;
case 'grant_type_id':
$value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
$query->_where[$grouping][] = "civicrm_grant.grant_type_id {$op} '{$value}'";
$grantTypes = CRM_Core_OptionGroup::values('grant_type');
$value = $grantTypes[$value];
$query->_qill[$grouping][] = ts('Grant Type %2 %1', array(1 => $value, 2 => $op));
$query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
return;
case 'grant_status_id':
$value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
$query->_where[$grouping][] = "civicrm_grant.status_id {$op} '{$value}'";
$grantStatus = CRM_Core_OptionGroup::values('grant_status');
$value = $grantStatus[$value];
$query->_qill[$grouping][] = ts('Grant Status %2 %1', array(1 => $value, 2 => $op));
$query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
return;
case 'grant_report_received':
if ($value == 1) {
$yesNo = 'Yes';
$query->_where[$grouping][] = "civicrm_grant.grant_report_received {$op} {$value}";
} elseif ($value == 0) {
$yesNo = 'No';
$query->_where[$grouping][] = "civicrm_grant.grant_report_received IS NULL";
}
$query->_qill[$grouping][] = "Grant Report Received = {$yesNo} ";
$query->_tables['civicrm_grant'] = $query->_whereTables['civicrm_grant'] = 1;
return;
case 'grant_amount':
case 'grant_amount_low':
case 'grant_amount_high':
$query->numberRangeBuilder($values, 'civicrm_grant', 'grant_amount', 'amount_total', 'Total Amount');
}
}
/**
* where / qill clause for relationship
*
* @return void
* @access public
*/
function relationship(&$values)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
// also get values array for relation_target_name
// for relatinship search we always do wildcard
$targetName = $this->getWhereValues('relation_target_name', $grouping);
$relStatus = $this->getWhereValues('relation_status', $grouping);
$targetGroup = $this->getWhereValues('relation_target_group', $grouping);
$nameClause = $name = NULL;
if ($targetName) {
$name = trim($targetName[2]);
if (substr($name, 0, 1) == '"' && substr($name, -1, 1) == '"') {
$name = substr($name, 1, -1);
$name = strtolower(CRM_Core_DAO::escapeString($name));
$nameClause = "= '{$name}'";
} else {
$name = strtolower(CRM_Core_DAO::escapeString($name));
$nameClause = "LIKE '%{$name}%'";
}
}
$rel = explode('_', $value);
self::$_relType = $rel[1];
$params = array('id' => $rel[0]);
$rTypeValues = array();
$rType = CRM_Contact_BAO_RelationshipType::retrieve($params, $rTypeValues);
if (!$rType) {
return;
}
if ($rTypeValues['name_a_b'] == $rTypeValues['name_b_a']) {
self::$_relType = 'reciprocal';
}
if ($nameClause) {
$this->_where[$grouping][] = "( contact_b.sort_name {$nameClause} AND contact_b.id != contact_a.id )";
}
$relTypeInd = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Individual');
$relTypeOrg = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Organization');
$relTypeHou = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Household');
$allRelationshipType = array();
$allRelationshipType = array_merge($relTypeInd, $relTypeOrg);
$allRelationshipType = array_merge($allRelationshipType, $relTypeHou);
if ($nameClause || !$targetGroup) {
$this->_qill[$grouping][] = "{$allRelationshipType[$value]} {$name}";
}
//check to see if the target contact is in specified group
if ($targetGroup) {
//add contacts from static groups
$this->_tables['civicrm_relationship_group_contact'] = $this->_whereTables['civicrm_relationship_group_contact'] = " LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = contact_b.id";
$groupWhere[] = "( civicrm_relationship_group_contact.group_id IN (" . implode(",", $targetGroup[2]) . ") )";
//add contacts from saved searches
$ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", "contact_b");
//set the group where clause
if ($ssWhere) {
$groupWhere[] = "( " . $ssWhere . " )";
}
$this->_where[$grouping][] = "( " . implode(" OR ", $groupWhere) . " )";
//Get the names of the target groups for the qill
$groupNames =& CRM_Core_PseudoConstant::group();
$qillNames = array();
foreach ($targetGroup[2] as $groupId) {
if (array_key_exists($groupId, $groupNames)) {
$qillNames[] = $groupNames[$groupId];
}
}
$this->_qill[$grouping][] = "{$allRelationshipType[$value]} ( " . implode(", ", $qillNames) . " )";
}
//check for active, inactive and all relation status
$today = date('Ymd');
if ($relStatus[2] == 0) {
$this->_where[$grouping][] = "(\ncivicrm_relationship.is_active = 1 AND\n( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND\n( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )\n)";
$this->_qill[$grouping][] = ts('Relationship - Active');
} elseif ($relStatus[2] == 1) {
$this->_where[$grouping][] = "(\ncivicrm_relationship.is_active = 0 OR\ncivicrm_relationship.end_date < {$today} OR\ncivicrm_relationship.start_date > {$today}\n)";
$this->_qill[$grouping][] = ts('Relationship - Inactive');
}
$this->_where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rel[0];
$this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1;
$this->_useDistinct = TRUE;
}
/**
* Process the activities.
*
* @param array $params
* Associated array of the submitted values.
*
* @throws CRM_Core_Exception
*
* @return CRM_Activity_BAO_Activity|null|object
*/
public static function create(&$params)
{
// check required params
if (!self::dataExists($params)) {
throw new CRM_Core_Exception('Not enough data to create activity object');
}
$activity = new CRM_Activity_DAO_Activity();
if (isset($params['id']) && empty($params['id'])) {
unset($params['id']);
}
if (empty($params['status_id']) && empty($params['activity_status_id']) && empty($params['id'])) {
if (isset($params['activity_date_time']) && strcmp($params['activity_date_time'], CRM_Utils_Date::processDate(date('Ymd')) == -1)) {
$params['status_id'] = 2;
} else {
$params['status_id'] = 1;
}
}
// Set priority to Normal for Auto-populated activities (for Cases)
if (CRM_Utils_Array::value('priority_id', $params) === NULL && !CRM_Utils_Array::value('id', $params)) {
$priority = CRM_Core_PseudoConstant::get('CRM_Activity_DAO_Activity', 'priority_id');
$params['priority_id'] = array_search('Normal', $priority);
}
if (!empty($params['target_contact_id']) && is_array($params['target_contact_id'])) {
$params['target_contact_id'] = array_unique($params['target_contact_id']);
}
if (!empty($params['assignee_contact_id']) && is_array($params['assignee_contact_id'])) {
$params['assignee_contact_id'] = array_unique($params['assignee_contact_id']);
}
// CRM-9137
if (!empty($params['id'])) {
CRM_Utils_Hook::pre('edit', 'Activity', $activity->id, $params);
} else {
CRM_Utils_Hook::pre('create', 'Activity', NULL, $params);
}
$activity->copyValues($params);
if (isset($params['case_id'])) {
// CRM-8708, preserve case ID even though it's not part of the SQL model
$activity->case_id = $params['case_id'];
} elseif (is_numeric($activity->id)) {
// CRM-8708, preserve case ID even though it's not part of the SQL model
$activity->case_id = CRM_Case_BAO_Case::getCaseIdByActivityId($activity->id);
}
// start transaction
$transaction = new CRM_Core_Transaction();
$result = $activity->save();
if (is_a($result, 'CRM_Core_Error')) {
$transaction->rollback();
return $result;
}
$activityId = $activity->id;
$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
if (isset($params['source_contact_id'])) {
$acParams = array('activity_id' => $activityId, 'contact_id' => $params['source_contact_id'], 'record_type_id' => $sourceID);
self::deleteActivityContact($activityId, $sourceID);
CRM_Activity_BAO_ActivityContact::create($acParams);
}
// check and attach and files as needed
CRM_Core_BAO_File::processAttachment($params, 'civicrm_activity', $activityId);
// attempt to save activity assignment
$resultAssignment = NULL;
if (!empty($params['assignee_contact_id'])) {
$assignmentParams = array('activity_id' => $activityId);
if (is_array($params['assignee_contact_id'])) {
if (CRM_Utils_Array::value('deleteActivityAssignment', $params, TRUE)) {
// first delete existing assignments if any
self::deleteActivityContact($activityId, $assigneeID);
}
$values = array();
foreach ($params['assignee_contact_id'] as $acID) {
if ($acID) {
$values[] = "( {$activityId}, {$acID}, {$assigneeID} )";
}
}
while (!empty($values)) {
$input = array_splice($values, 0, CRM_Core_DAO::BULK_INSERT_COUNT);
$str = implode(',', $input);
$sql = "INSERT IGNORE INTO civicrm_activity_contact ( activity_id, contact_id, record_type_id ) VALUES {$str};";
CRM_Core_DAO::executeQuery($sql);
}
} else {
$assignmentParams['contact_id'] = $params['assignee_contact_id'];
$assignmentParams['record_type_id'] = $assigneeID;
if (!empty($params['id'])) {
$assignment = new CRM_Activity_BAO_ActivityContact();
$assignment->activity_id = $activityId;
$assignment->record_type_id = $assigneeID;
$assignment->find(TRUE);
if ($assignment->contact_id != $params['assignee_contact_id']) {
$assignmentParams['id'] = $assignment->id;
$resultAssignment = CRM_Activity_BAO_ActivityContact::create($assignmentParams);
}
} else {
$resultAssignment = CRM_Activity_BAO_ActivityContact::create($assignmentParams);
}
}
} else {
if (CRM_Utils_Array::value('deleteActivityAssignment', $params, TRUE)) {
self::deleteActivityContact($activityId, $assigneeID);
}
}
if (is_a($resultAssignment, 'CRM_Core_Error')) {
$transaction->rollback();
return $resultAssignment;
}
// attempt to save activity targets
$resultTarget = NULL;
if (!empty($params['target_contact_id'])) {
$targetParams = array('activity_id' => $activityId);
$resultTarget = array();
if (is_array($params['target_contact_id'])) {
if (CRM_Utils_Array::value('deleteActivityTarget', $params, TRUE)) {
// first delete existing targets if any
self::deleteActivityContact($activityId, $targetID);
}
$values = array();
foreach ($params['target_contact_id'] as $tid) {
if ($tid) {
$values[] = "( {$activityId}, {$tid}, {$targetID} )";
}
}
while (!empty($values)) {
$input = array_splice($values, 0, CRM_Core_DAO::BULK_INSERT_COUNT);
$str = implode(',', $input);
$sql = "INSERT IGNORE INTO civicrm_activity_contact ( activity_id, contact_id, record_type_id ) VALUES {$str};";
CRM_Core_DAO::executeQuery($sql);
}
} else {
$targetParams['contact_id'] = $params['target_contact_id'];
$targetParams['record_type_id'] = $targetID;
if (!empty($params['id'])) {
$target = new CRM_Activity_BAO_ActivityContact();
$target->activity_id = $activityId;
$target->record_type_id = $targetID;
$target->find(TRUE);
if ($target->contact_id != $params['target_contact_id']) {
$targetParams['id'] = $target->id;
$resultTarget = CRM_Activity_BAO_ActivityContact::create($targetParams);
}
} else {
$resultTarget = CRM_Activity_BAO_ActivityContact::create($targetParams);
}
}
} else {
if (CRM_Utils_Array::value('deleteActivityTarget', $params, TRUE)) {
self::deleteActivityContact($activityId, $targetID);
}
}
// write to changelog before transaction is committed/rolled
// back (and prepare status to display)
if (!empty($params['id'])) {
$logMsg = "Activity (id: {$result->id} ) updated with ";
} else {
$logMsg = "Activity created for ";
}
$msgs = array();
if (isset($params['source_contact_id'])) {
$msgs[] = "source={$params['source_contact_id']}";
}
if (!empty($params['target_contact_id'])) {
if (is_array($params['target_contact_id']) && !CRM_Utils_array::crmIsEmptyArray($params['target_contact_id'])) {
$msgs[] = "target=" . implode(',', $params['target_contact_id']);
// take only first target
// will be used for recently viewed display
$t = array_slice($params['target_contact_id'], 0, 1);
$recentContactId = $t[0];
} elseif (isset($params['target_contact_id']) && !is_array($params['target_contact_id'])) {
$msgs[] = "target={$params['target_contact_id']}";
// will be used for recently viewed display
$recentContactId = $params['target_contact_id'];
}
} else {
// at worst, take source for recently viewed display
$recentContactId = CRM_Utils_Array::value('source_contact_id', $params);
}
if (isset($params['assignee_contact_id'])) {
if (is_array($params['assignee_contact_id'])) {
$msgs[] = "assignee=" . implode(',', $params['assignee_contact_id']);
} else {
$msgs[] = "assignee={$params['assignee_contact_id']}";
}
}
$logMsg .= implode(', ', $msgs);
self::logActivityAction($result, $logMsg);
if (!empty($params['custom']) && is_array($params['custom'])) {
CRM_Core_BAO_CustomValueTable::store($params['custom'], 'civicrm_activity', $result->id);
}
$transaction->commit();
if (empty($params['skipRecentView'])) {
$recentOther = array();
if (!empty($params['case_id'])) {
$caseContactID = CRM_Core_DAO::getFieldValue('CRM_Case_DAO_CaseContact', $params['case_id'], 'contact_id', 'case_id');
$url = CRM_Utils_System::url('civicrm/case/activity/view', "reset=1&aid={$activity->id}&cid={$caseContactID}&caseID={$params['case_id']}&context=home");
} else {
$q = "action=view&reset=1&id={$activity->id}&atype={$activity->activity_type_id}&cid=" . CRM_Utils_Array::value('source_contact_id', $params) . "&context=home";
if ($activity->activity_type_id != CRM_Core_OptionGroup::getValue('activity_type', 'Email', 'name')) {
$url = CRM_Utils_System::url('civicrm/activity', $q);
if ($activity->activity_type_id == CRM_Core_OptionGroup::getValue('activity_type', 'Print PDF Letter', 'name')) {
$recentOther['editUrl'] = CRM_Utils_System::url('civicrm/activity/pdf/add', "action=update&reset=1&id={$activity->id}&atype={$activity->activity_type_id}&cid={$params['source_contact_id']}&context=home");
} else {
$recentOther['editUrl'] = CRM_Utils_System::url('civicrm/activity/add', "action=update&reset=1&id={$activity->id}&atype={$activity->activity_type_id}&cid=" . CRM_Utils_Array::value('source_contact_id', $params) . "&context=home");
}
if (CRM_Core_Permission::check("delete activities")) {
$recentOther['deleteUrl'] = CRM_Utils_System::url('civicrm/activity', "action=delete&reset=1&id={$activity->id}&atype={$activity->activity_type_id}&cid=" . CRM_Utils_Array::value('source_contact_id', $params) . "&context=home");
}
} else {
$url = CRM_Utils_System::url('civicrm/activity/view', $q);
if (CRM_Core_Permission::check('delete activities')) {
$recentOther['deleteUrl'] = CRM_Utils_System::url('civicrm/activity', "action=delete&reset=1&id={$activity->id}&atype={$activity->activity_type_id}&cid=" . CRM_Utils_Array::value('source_contact_id', $params) . "&context=home");
}
}
}
if (!isset($activity->parent_id)) {
$recentContactDisplay = CRM_Contact_BAO_Contact::displayName($recentContactId);
// add the recently created Activity
$activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, TRUE);
$activitySubject = CRM_Core_DAO::getFieldValue('CRM_Activity_DAO_Activity', $activity->id, 'subject');
$title = "";
if (isset($activitySubject)) {
$title = $activitySubject . ' - ';
}
$title = $title . $recentContactDisplay;
if (!empty($activityTypes[$activity->activity_type_id])) {
$title .= ' (' . $activityTypes[$activity->activity_type_id] . ')';
}
CRM_Utils_Recent::add($title, $url, $activity->id, 'Activity', $recentContactId, $recentContactDisplay, $recentOther);
}
}
// reset the group contact cache since smart groups might be affected due to this
CRM_Contact_BAO_GroupContactCache::remove();
if (!empty($params['id'])) {
CRM_Utils_Hook::post('edit', 'Activity', $activity->id, $activity);
} else {
CRM_Utils_Hook::post('create', 'Activity', $activity->id, $activity);
}
// if the subject contains a ‘[case #…]’ string, file that activity on the related case (CRM-5916)
$matches = array();
if (preg_match('/\\[case #([0-9a-h]{7})\\]/', CRM_Utils_Array::value('subject', $params), $matches)) {
$key = CRM_Core_DAO::escapeString(CIVICRM_SITE_KEY);
$hash = $matches[1];
$query = "SELECT id FROM civicrm_case WHERE SUBSTR(SHA1(CONCAT('{$key}', id)), 1, 7) = '{$hash}'";
$caseParams = array('activity_id' => $activity->id, 'case_id' => CRM_Core_DAO::singleValueQuery($query));
if ($caseParams['case_id']) {
CRM_Case_BAO_Case::processCaseActivity($caseParams);
} else {
self::logActivityAction($activity, "unknown case hash encountered: {$hash}");
}
}
return $result;
}
/**
* Get all the smart groups that this contact belongs to.
*
* Note that this could potentially be a super slow function since
* it ensure that all contact groups are loaded in the cache
*
* @param int $contactID
* @param bool $showHidden
* Hidden groups are shown only if this flag is set.
*
* @return array
* an array of groups that this contact belongs to
*/
public static function contactGroup($contactID, $showHidden = FALSE)
{
if (empty($contactID)) {
return NULL;
}
if (is_array($contactID)) {
$contactIDs = $contactID;
} else {
$contactIDs = array($contactID);
}
self::loadAll();
$hiddenClause = '';
if (!$showHidden) {
$hiddenClause = ' AND (g.is_hidden = 0 OR g.is_hidden IS NULL) ';
}
$contactIDString = CRM_Core_DAO::escapeString(implode(', ', $contactIDs));
$sql = "\nSELECT gc.group_id, gc.contact_id, g.title, g.children, g.description\nFROM civicrm_group_contact_cache gc\nINNER JOIN civicrm_group g ON g.id = gc.group_id\nWHERE gc.contact_id IN ({$contactIDString})\n {$hiddenClause}\nORDER BY gc.contact_id, g.children\n";
$dao = CRM_Core_DAO::executeQuery($sql);
$contactGroup = array();
$prevContactID = NULL;
while ($dao->fetch()) {
if ($prevContactID && $prevContactID != $dao->contact_id) {
$contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
}
$prevContactID = $dao->contact_id;
if (!array_key_exists($dao->contact_id, $contactGroup)) {
$contactGroup[$dao->contact_id] = array('group' => array(), 'groupTitle' => array());
}
$contactGroup[$dao->contact_id]['group'][] = array('id' => $dao->group_id, 'title' => $dao->title, 'description' => $dao->description, 'children' => $dao->children);
$contactGroup[$dao->contact_id]['groupTitle'][] = $dao->title;
}
if ($prevContactID) {
$contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
}
if (!empty($contactGroup[$contactID]) && is_numeric($contactID)) {
return $contactGroup[$contactID];
} else {
return $contactGroup;
}
}
/**
* Generate where for a single parameter.
*
* @param array $values
* @param CRM_Contact_BAO_Query $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping) = $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}";
$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;
// CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
// CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6.
case 'member_status_id':
case 'member_membership_type_id':
if (is_array($value)) {
$op = 'IN';
$value = array_keys($value);
}
case 'membership_type_id':
// CRM-17075 we are specifically handling the possibility we are dealing with the entity reference field
// for membership_type_id here (although status would be handled if converted). The unhandled pathway at the moment
// is from groupContactCache::load and this is a small fix to get the entity reference field to work.
// However, it would seem the larger fix would be to NOT invoke the form formValues for
// the load function. The where clause and the whereTables are saved so they should suffice to generate the query
// to get a contact list. But, better to deal with in 4.8 now...
if (is_string($value) && strpos($value, ',') && $op == '=') {
$value = array('IN' => explode(',', $value));
}
case 'membership_status':
case 'membership_status_id':
case 'membership_type':
case 'member_id':
if (strstr($name, 'status') && is_string($value) && !is_numeric($value)) {
$value = CRM_Core_PseudoConstant::getKey('CRM_Member_BAO_Membership', 'status_id', $value);
}
if (strpos($name, 'status') !== FALSE) {
$name = 'status_id';
$qillName = 'Membership Status(s)';
} elseif ($name == 'member_id') {
$name = 'id';
$qillName = 'Membership ID';
} else {
$name = 'membership_type_id';
$qillName = 'Membership Type(s)';
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.{$name}", $op, $value, "Integer");
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Member_DAO_Membership', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $qillName, 2 => $op, 3 => $value));
$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 '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_is_override':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_override", $op, $value, "Boolean");
$query->_qill[$grouping][] = $value ? ts("Is Membership Status overriden? Yes") : ts("Is Membership Status overriden? No");
$query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
return;
case 'member_campaign_id':
if (CRM_Utils_Array::value($op, $value)) {
$value = $value[$op];
}
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_membership');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
return;
}
}
/**
* @param string|NULL $value
* @return string
* SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
*/
public function escapeString($value)
{
return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
}
public function updateGreeting()
{
$config = CRM_Core_Config::singleton();
$contactType = CRM_Utils_Request::retrieve('ct', 'String', CRM_Core_DAO::$_nullArray, FALSE, NULL, 'REQUEST');
if (!in_array($contactType, array('Individual', 'Household', 'Organization'))) {
CRM_Core_Error::fatal(ts('Invalid Contact Type.'));
}
$greeting = CRM_Utils_Request::retrieve('gt', 'String', CRM_Core_DAO::$_nullArray, FALSE, NULL, 'REQUEST');
if (!in_array($greeting, array('email_greeting', 'postal_greeting', 'addressee'))) {
CRM_Core_Error::fatal(ts('Invalid Greeting Type.'));
}
if (in_array($greeting, array('email_greeting', 'postal_greeting')) && $contactType == 'Organization') {
CRM_Core_Error::fatal(ts('You cannot use %1 for contact type %2.', array(1 => $greeting, 2 => $contactType)));
}
$valueID = $id = CRM_Utils_Request::retrieve('id', 'Positive', CRM_Core_DAO::$_nullArray, FALSE, NULL, 'REQUEST');
// if valueID is not passed use default value
if (!$valueID) {
require_once 'CRM/Core/OptionGroup.php';
$contactTypeFilters = array(1 => 'Individual', 2 => 'Household', 3 => 'Organization');
$filter = CRM_Utils_Array::key($contactType, $contactTypeFilters);
$defaulValueID = CRM_Core_OptionGroup::values($greeting, NULL, NULL, NULL, " AND is_default = 1 AND ( filter = {$filter} OR filter = 0 )", "value");
$valueID = array_pop($defaulValueID);
}
$filter = array('contact_type' => $contactType, 'greeting_type' => $greeting);
$allGreetings = CRM_Core_PseudoConstant::greeting($filter);
$originalGreetingString = $greetingString = CRM_Utils_Array::value($valueID, $allGreetings);
if (!$greetingString) {
CRM_Core_Error::fatal(ts('Incorrect greeting value id %1.', array(1 => $valueID)));
}
// build return properties based on tokens
require_once 'CRM/Utils/Token.php';
$greetingTokens = CRM_Utils_Token::getTokens($greetingString);
$tokens = CRM_Utils_Array::value('contact', $greetingTokens);
$greetingsReturnProperties = array();
if (is_array($tokens)) {
$greetingsReturnProperties = array_fill_keys(array_values($tokens), 1);
}
//process all contacts only when force pass.
$force = CRM_Utils_Request::retrieve('force', 'String', CRM_Core_DAO::$_nullArray, FALSE, NULL, 'REQUEST');
$processAll = $processOnlyIdSet = FALSE;
if (in_array($force, array(1, 'true'))) {
$processAll = TRUE;
} elseif ($force == 2) {
$processOnlyIdSet = TRUE;
}
//FIXME : apiQuery should handle these clause.
$filterContactFldIds = $filterIds = array();
if (!$processAll) {
$idFldName = $displayFldName = NULL;
if ($greeting == 'email_greeting' || $greeting == 'postal_greeting' || $greeting == 'addressee') {
$idFldName = $greeting . '_id';
$displayFldName = $greeting . '_display';
}
if ($idFldName) {
$sql = "\nSELECT DISTINCT id, {$idFldName}\n FROM civicrm_contact\n WHERE contact_type = %1\n AND ( {$idFldName} IS NULL OR\n ( {$idFldName} IS NOT NULL AND {$displayFldName} IS NULL ) )\n ";
$dao = CRM_Core_DAO::executeQuery($sql, array(1 => array($contactType, 'String')));
while ($dao->fetch()) {
$filterContactFldIds[$dao->id] = $dao->{$idFldName};
if (!CRM_Utils_System::isNull($dao->{$idFldName})) {
$filterIds[$dao->id] = $dao->{$idFldName};
}
}
}
if (empty($filterContactFldIds)) {
$filterContactFldIds[] = 0;
}
}
if (empty($filterContactFldIds)) {
return;
}
// retrieve only required contact information
require_once 'CRM/Utils/Token.php';
$extraParams[] = array('contact_type', '=', $contactType, 0, 0);
// we do token replacement in the replaceGreetingTokens hook
list($greetingDetails) = CRM_Utils_Token::getTokenDetails(array_keys($filterContactFldIds), $greetingsReturnProperties, FALSE, FALSE, $extraParams);
// perform token replacement and build update SQL
$contactIds = array();
$cacheFieldQuery = "UPDATE civicrm_contact SET {$greeting}_display = CASE id ";
foreach ($greetingDetails as $contactID => $contactDetails) {
if (!$processAll && !array_key_exists($contactID, $filterContactFldIds)) {
continue;
}
if ($processOnlyIdSet) {
if (!array_key_exists($contactID, $filterIds)) {
continue;
}
if ($id) {
$greetingString = $originalGreetingString;
$contactIds[] = $contactID;
} else {
if ($greetingBuffer = CRM_Utils_Array::value($filterContactFldIds[$contactID], $allGreetings)) {
$greetingString = $greetingBuffer;
}
}
$allContactIds[] = $contactID;
} else {
$greetingString = $originalGreetingString;
if ($greetingBuffer = CRM_Utils_Array::value($filterContactFldIds[$contactID], $allGreetings)) {
$greetingString = $greetingBuffer;
} else {
$contactIds[] = $contactID;
}
}
CRM_Contact_BAO_Contact_Utils::processGreetingTemplate($greetingString, $contactDetails, $contactID, 'CRM_UpdateGreeting');
$greetingString = CRM_Core_DAO::escapeString($greetingString);
$cacheFieldQuery .= " WHEN {$contactID} THEN '{$greetingString}' ";
$allContactIds[] = $contactID;
}
if (!empty($allContactIds)) {
$cacheFieldQuery .= " ELSE {$greeting}_display\n END;";
if (!empty($contactIds)) {
// need to update greeting _id field.
$queryString = "\nUPDATE civicrm_contact\n SET {$greeting}_id = {$valueID}\n WHERE id IN (" . implode(',', $contactIds) . ")";
CRM_Core_DAO::executeQuery($queryString);
}
// now update cache field
CRM_Core_DAO::executeQuery($cacheFieldQuery);
}
}
/**
* This function is called to rebuild prev next cache using full sql in case of core search ( excluding custom search)
*
* @param int $start start for limit clause
* @param int $end end for limit clause
* @param $sort
* @param string $cacheKey cache key
*
* @internal param $object $sort sort object
* @return void
*/
function rebuildPreNextCache($start, $end, $sort, $cacheKey)
{
// generate full SQL
$sql = $this->_query->searchQuery($start, $end, $sort, FALSE, $this->_query->_includeContactIds, FALSE, FALSE, TRUE);
$dao = CRM_Core_DAO::executeQuery($sql);
// build insert query, note that currently we build cache for 500 contact records at a time, hence below approach
$insertValues = array();
while ($dao->fetch()) {
$insertValues[] = "('civicrm_contact', {$dao->contact_id}, {$dao->contact_id}, '{$cacheKey}', '" . CRM_Core_DAO::escapeString($dao->sort_name) . "')";
}
//update pre/next cache using single insert query
if (!empty($insertValues)) {
$sql = 'INSERT INTO civicrm_prevnext_cache ( entity_table, entity_id1, entity_id2, cacheKey, data ) VALUES
' . implode(',', $insertValues);
$result = CRM_Core_DAO::executeQuery($sql);
}
}
/**
* Generate triggers to update the timestamp.
*
* The corresponding civicrm_contact row is updated on insert/update/delete
* to a table that extends civicrm_contact.
* Don't regenerate triggers for all such tables if only asked for one table.
*
* @param array $info
* Reference to the array where generated trigger information is being stored
* @param string|null $reqTableName
* Name of the table for which triggers are being generated, or NULL if all tables
* @param array $relatedTableNames
* Array of all core or all custom table names extending civicrm_contact
* @param string $contactRefColumn
* 'contact_id' if processing core tables, 'entity_id' if processing custom tables
*
* @link https://issues.civicrm.org/jira/browse/CRM-15602
* @see triggerInfo
*/
public static function generateTimestampTriggers(&$info, $reqTableName, $relatedTableNames, $contactRefColumn)
{
// Safety
$contactRefColumn = CRM_Core_DAO::escapeString($contactRefColumn);
// If specific related table requested, just process that one
if (in_array($reqTableName, $relatedTableNames)) {
$relatedTableNames = array($reqTableName);
}
// If no specific table requested (include all related tables),
// or a specific related table requested (as matched above)
if (empty($reqTableName) || in_array($reqTableName, $relatedTableNames)) {
$info[] = array('table' => $relatedTableNames, 'when' => 'AFTER', 'event' => array('INSERT', 'UPDATE'), 'sql' => "\nUPDATE civicrm_contact SET modified_date = CURRENT_TIMESTAMP WHERE id = NEW.{$contactRefColumn};\n");
$info[] = array('table' => $relatedTableNames, 'when' => 'AFTER', 'event' => array('DELETE'), 'sql' => "\nUPDATE civicrm_contact SET modified_date = CURRENT_TIMESTAMP WHERE id = OLD.{$contactRefColumn};\n");
}
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array_merge(CRM_Event_BAO_Event::fields(), CRM_Event_BAO_Participant::exportableFields());
switch ($name) {
case 'event_start_date_low':
case 'event_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_start_date', 'start_date', 'Start Date');
return;
case 'event_end_date_low':
case 'event_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_end_date', 'end_date', 'End Date');
return;
case 'event_include_repeating_events':
/**
* Include Repeating Events
*/
//Get parent of this event
$exEventId = '';
if ($query->_where[$grouping]) {
foreach ($query->_where[$grouping] as $key => $val) {
if (strstr($val, 'civicrm_event.id =')) {
$exEventId = $val;
$extractEventId = explode(" ", $val);
$value = $extractEventId[2];
$where = $query->_where[$grouping][$key];
} else {
if (strstr($val, 'civicrm_event.id IN')) {
//extract the first event id if multiple events are selected
preg_match('/civicrm_event.id IN \\(\\"(\\d+)/', $val, $matches);
$value = $matches[1];
$where = $query->_where[$grouping][$key];
}
}
}
if ($exEventId) {
$extractEventId = explode(" ", $exEventId);
$value = $extractEventId[2];
} else {
if (!empty($matches[1])) {
$value = $matches[1];
}
}
$where = $query->_where[$grouping][$key];
}
$thisEventHasParent = CRM_Core_BAO_RecurringEntity::getParentFor($value, 'civicrm_event');
if ($thisEventHasParent) {
$getAllConnections = CRM_Core_BAO_RecurringEntity::getEntitiesForParent($thisEventHasParent, 'civicrm_event');
$allEventIds = array();
foreach ($getAllConnections as $key => $val) {
$allEventIds[] = $val['id'];
}
if (!empty($allEventIds)) {
$op = "IN";
$value = "(" . implode(",", $allEventIds) . ")";
}
}
$query->_where[$grouping][] = "{$where} OR civicrm_event.id {$op} {$value}";
$query->_qill[$grouping][] = ts('Include Repeating Events');
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
return;
case 'participant_is_test':
$key = array_search('civicrm_participant.is_test = 0', $query->_where[$grouping]);
if (!empty($key)) {
unset($query->_where[$grouping][$key]);
}
case 'participant_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_participant.is_test", $op, $value, "Boolean");
$isTest = $value ? 'a Test' : 'not a Test';
$query->_qill[$grouping][] = ts("Participant is %1", array(1 => $isTest));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
}
return;
case 'participant_fee_id':
foreach ($value as $k => &$val) {
$val = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $val, 'label');
$val = CRM_Core_DAO::escapeString(trim($val));
}
$feeLabel = implode('|', $value);
$query->_where[$grouping][] = "civicrm_participant.fee_level REGEXP '{$feeLabel}'";
$query->_qill[$grouping][] = ts("Fee level") . " IN " . implode(', ', $value);
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_fee_amount_high':
case 'participant_fee_amount_low':
$query->numberRangeBuilder($values, 'civicrm_participant', 'participant_fee_amount', 'fee_amount', 'Fee Amount');
return;
case 'participant_status_id':
if ($value && is_array($value) && strpos($op, 'IN') === FALSE) {
$op = 'IN';
}
case 'participant_status':
case 'participant_source':
case 'participant_id':
case 'participant_contact_id':
case 'participant_is_pay_later':
case 'participant_fee_amount':
case 'participant_fee_level':
case 'participant_campaign_id':
$qillName = $name;
if (in_array($name, array('participant_status_id', 'participant_source', 'participant_id', 'participant_contact_id', 'participant_fee_amount', 'participant_fee_level', 'participant_is_pay_later', 'participant_campaign_id'))) {
$name = str_replace('participant_', '', $name);
if ($name == 'is_pay_later') {
$qillName = $name;
}
} elseif ($name == 'participant_status') {
$name = 'status_id';
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$tableName = empty($tableName) ? 'civicrm_participant' : $tableName;
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$name}", $op, $value, $dataType);
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Participant', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_role':
case 'participant_role_id':
$qillName = $name;
$name = 'role_id';
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
if (!strstr($op, 'NULL') && !strstr($op, 'EMPTY') && !strstr($op, 'LIKE')) {
$regexOp = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT REGEXP' : 'REGEXP';
$regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', (array) $value) . "[[:cntrl:]]*";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.{$name}", $regexOp, $regexp, 'String');
} else {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$name}", $op, $value, $dataType);
}
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Participant', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_register_date':
case 'participant_register_date_high':
case 'participant_register_date_low':
$query->dateQueryBuilder($values, 'civicrm_participant', 'participant_register_date', 'register_date', 'Register Date');
return;
case 'event_id':
case 'participant_event_id':
$name = str_replace('participant_', '', $name);
case 'event_is_public':
case 'event_type_id':
case 'event_title':
$qillName = $name;
if (in_array($name, array('event_id', 'event_title', 'event_is_public'))) {
$name = str_replace('event_', '', $name);
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_event.{$name}", $op, $value, $dataType);
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
if (!array_key_exists($qillName, $fields)) {
break;
}
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Event', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
return;
}
}
/**
* Where / qill clause for relationship.
*
* @param array $values
*/
public function relationship(&$values)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
if ($this->_relationshipValuesAdded) {
return;
}
// also get values array for relation_target_name
// for relationship search we always do wildcard
$relationType = $this->getWhereValues('relation_type_id', $grouping);
$targetName = $this->getWhereValues('relation_target_name', $grouping);
$relStatus = $this->getWhereValues('relation_status', $grouping);
$relPermission = $this->getWhereValues('relation_permission', $grouping);
$targetGroup = $this->getWhereValues('relation_target_group', $grouping);
$nameClause = $name = NULL;
if ($targetName) {
$name = trim($targetName[2]);
if (substr($name, 0, 1) == '"' && substr($name, -1, 1) == '"') {
$name = substr($name, 1, -1);
$name = strtolower(CRM_Core_DAO::escapeString($name));
$nameClause = "= '{$name}'";
} else {
$name = strtolower(CRM_Core_DAO::escapeString($name));
$nameClause = "LIKE '%{$name}%'";
}
}
$rTypeValues = array();
if (!empty($relationType)) {
$rel = explode('_', $relationType[2]);
self::$_relType = $rel[1];
$params = array('id' => $rel[0]);
$rType = CRM_Contact_BAO_RelationshipType::retrieve($params, $rTypeValues);
}
if (!empty($rTypeValues) && $rTypeValues['name_a_b'] == $rTypeValues['name_b_a']) {
// if we don't know which end of the relationship we are dealing with we'll create a temp table
//@todo unless we are dealing with a target group
self::$_relType = 'reciprocal';
}
// if we are creating a temp table we build our own where for the relationship table
$relationshipTempTable = NULL;
if (self::$_relType == 'reciprocal' && empty($targetGroup)) {
$where = array();
self::$_relationshipTempTable = $relationshipTempTable = CRM_Core_DAO::createTempTableName('civicrm_rel');
if ($nameClause) {
$where[$grouping][] = " sort_name {$nameClause} ";
}
} else {
$where =& $this->_where;
if ($nameClause) {
$where[$grouping][] = "( contact_b.sort_name {$nameClause} AND contact_b.id != contact_a.id )";
}
}
$allRelationshipType = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, NULL, TRUE);
if ($nameClause || !$targetGroup) {
if (!empty($relationType)) {
$this->_qill[$grouping][] = $allRelationshipType[$relationType[2]] . " {$name}";
} else {
$this->_qill[$grouping][] = $name;
}
}
//check to see if the target contact is in specified group
if ($targetGroup) {
//add contacts from static groups
$this->_tables['civicrm_relationship_group_contact'] = $this->_whereTables['civicrm_relationship_group_contact'] = " LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = contact_b.id AND civicrm_relationship_group_contact.status = 'Added'";
$groupWhere[] = "( civicrm_relationship_group_contact.group_id IN (" . implode(",", $targetGroup[2]) . ") ) ";
//add contacts from saved searches
$ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", "contact_b", $op);
//set the group where clause
if ($ssWhere) {
$groupWhere[] = "( " . $ssWhere . " )";
}
$this->_where[$grouping][] = "( " . implode(" OR ", $groupWhere) . " )";
//Get the names of the target groups for the qill
$groupNames = CRM_Core_PseudoConstant::group();
$qillNames = array();
foreach ($targetGroup[2] as $groupId) {
if (array_key_exists($groupId, $groupNames)) {
$qillNames[] = $groupNames[$groupId];
}
}
if (!empty($relationType)) {
$this->_qill[$grouping][] = $allRelationshipType[$relationType[2]] . " ( " . implode(", ", $qillNames) . " )";
} else {
$this->_qill[$grouping][] = implode(", ", $qillNames);
}
}
// Note we do not currently set mySql to handle timezones, so doing this the old-fashioned way
$today = date('Ymd');
//check for active, inactive and all relation status
if ($relStatus[2] == 0) {
$where[$grouping][] = "(\ncivicrm_relationship.is_active = 1 AND\n( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND\n( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )\n)";
$this->_qill[$grouping][] = ts('Relationship - Active and Current');
} elseif ($relStatus[2] == 1) {
$where[$grouping][] = "(\ncivicrm_relationship.is_active = 0 OR\ncivicrm_relationship.end_date < {$today} OR\ncivicrm_relationship.start_date > {$today}\n)";
$this->_qill[$grouping][] = ts('Relationship - Inactive or not Current');
}
$onlyDeleted = 0;
if (in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params)) {
$onlyDeleted = 1;
}
$where[$grouping][] = "(contact_b.is_deleted = {$onlyDeleted})";
//check for permissioned, non-permissioned and all permissioned relations
if ($relPermission[2] == 1) {
$where[$grouping][] = "(\ncivicrm_relationship.is_permission_a_b = 1\n)";
$this->_qill[$grouping][] = ts('Relationship - Permissioned');
} elseif ($relPermission[2] == 2) {
//non-allowed permission relationship.
$where[$grouping][] = "(\ncivicrm_relationship.is_permission_a_b = 0\n)";
$this->_qill[$grouping][] = ts('Relationship - Non-permissioned');
}
$this->addRelationshipDateClauses($grouping, $where);
if (!empty($relationType) && !empty($rType) && isset($rType->id)) {
$where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rType->id;
}
$this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1;
$this->_useDistinct = TRUE;
$this->_relationshipValuesAdded = TRUE;
// it could be a or b, using an OR creates an unindexed join - better to create a temp table &
// join on that,
// @todo creating a temp table could be expanded to group filter
// as even creating a temp table of all relationships is much much more efficient than
// an OR in the join
if ($relationshipTempTable) {
$whereClause = '';
if (!empty($where[$grouping])) {
$whereClause = ' WHERE ' . implode(' AND ', $where[$grouping]);
$whereClause = str_replace('contact_b', 'c', $whereClause);
}
$sql = "\n CREATE TEMPORARY TABLE {$relationshipTempTable}\n (SELECT contact_id_b as contact_id, civicrm_relationship.id\n FROM civicrm_relationship\n INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_a = c.id\n {$whereClause} )\n UNION\n (SELECT contact_id_a as contact_id, civicrm_relationship.id\n FROM civicrm_relationship\n INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_b = c.id\n {$whereClause} )\n ";
CRM_Core_DAO::executeQuery($sql);
}
}