static function getContactList(&$config) { require_once 'CRM/Core/BAO/Preferences.php'; $name = CRM_Utils_Type::escape($_GET['s'], 'String'); $limit = '10'; $list = array_keys(CRM_Core_BAO_Preferences::valueOptions('contact_autocomplete_options'), '1'); $select = array('sort_name'); $where = ''; $from = array(); foreach ($list as $value) { $suffix = substr($value, 0, 2) . substr($value, -1); switch ($value) { case 'street_address': case 'city': $selectText = $value; $value = "address"; $suffix = 'sts'; case 'phone': case 'email': $select[] = $value == 'address' ? $selectText : $value; $from[$value] = "LEFT JOIN civicrm_{$value} {$suffix} ON ( cc.id = {$suffix}.contact_id AND {$suffix}.is_primary = 1 ) "; break; case 'country': case 'state_province': $select[] = "{$suffix}.name"; if (!in_array('address', $from)) { $from['address'] = 'LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1) '; } $from[$value] = " LEFT JOIN civicrm_{$value} {$suffix} ON ( sts.{$value}_id = {$suffix}.id ) "; break; } } $select = implode(', ', $select); $from = implode(' ', $from); if (CRM_Utils_Array::value('limit', $_GET)) { $limit = CRM_Utils_Type::escape($_GET['limit'], 'Positive'); } // add acl clause here require_once 'CRM/Contact/BAO/Contact/Permission.php'; list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc'); if ($aclWhere) { $where .= " AND {$aclWhere} "; } $query = "\nSELECT DISTINCT(cc.id) as id, CONCAT_WS( ' :: ', {$select} ) as data\nFROM civicrm_contact cc {$from}\n{$aclFrom}\nWHERE sort_name LIKE '%{$name}%' {$where} \nORDER BY sort_name\nLIMIT 0, {$limit}\n"; // send query to hook to be modified if needed require_once 'CRM/Utils/Hook.php'; CRM_Utils_Hook::contactListQuery($query, $name, CRM_Utils_Array::value('context', $_GET), CRM_Utils_Array::value('id', $_GET)); $dao = CRM_Core_DAO::executeQuery($query); $contactList = null; while ($dao->fetch()) { echo $contactList = "{$dao->data}|{$dao->id}\n"; } exit; }
function buildACLClause($tableAlias = 'contact') { list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias); }
public static function getContactPhone() { $queryString = NULL; //check for mobile type $phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name'); $mobileType = CRM_Utils_Array::value('Mobile', $phoneTypes); $name = CRM_Utils_Array::value('name', $_GET); if ($name) { $name = CRM_Utils_Type::escape($name, 'String'); $queryString = " ( cc.sort_name LIKE '%{$name}%' OR cp.phone LIKE '%{$name}%' ) "; } else { $cid = CRM_Utils_Array::value('cid', $_GET); if ($cid) { //check cid for integer $contIDS = explode(',', $cid); foreach ($contIDS as $contID) { CRM_Utils_Type::escape($contID, 'Integer'); } $queryString = " cc.id IN ( {$cid} )"; } } if ($queryString) { $offset = CRM_Utils_Array::value('offset', $_GET, 0); $rowCount = CRM_Utils_Array::value('rowcount', $_GET, 20); $offset = CRM_Utils_Type::escape($offset, 'Int'); $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); // add acl clause here list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc'); if ($aclWhere) { $aclWhere = " AND {$aclWhere}"; } $query = "\nSELECT sort_name name, cp.phone, cc.id\nFROM civicrm_phone cp INNER JOIN civicrm_contact cc ON cc.id = cp.contact_id\n {$aclFrom}\nWHERE cc.is_deceased = 0 AND cc.do_not_sms = 0 AND cp.phone_type_id = {$mobileType} AND {$queryString}\n {$aclWhere}\nLIMIT {$offset}, {$rowCount}\n"; // send query to hook to be modified if needed CRM_Utils_Hook::contactListQuery($query, $name, CRM_Utils_Request::retrieve('context', 'String', CRM_Core_DAO::$_nullObject), CRM_Utils_Request::retrieve('cid', 'Positive', CRM_Core_DAO::$_nullObject)); $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $result[] = array('text' => '"' . $dao->name . '" (' . $dao->phone . ')', 'id' => CRM_Utils_Array::value('id', $_GET) ? "{$dao->id}::{$dao->phone}" : '"' . $dao->name . '" <' . $dao->phone . '>'); } } if ($result) { CRM_Utils_JSON::output($result); } CRM_Utils_System::civiExit(); }
/** * note that $job_id is used only as a variable in the temp table construction * and does not play a role in the queries generated * @param int $job_id * (misnomer) a nonce value used to name temporary tables. * @param int $mailing_id * @param bool $storeRecipients * @param bool $dedupeEmail * @param null $mode * * @return CRM_Mailing_Event_BAO_Queue|string */ public static function getRecipients($job_id, $mailing_id = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL) { $mailingGroup = new CRM_Mailing_DAO_MailingGroup(); $mailing = CRM_Mailing_BAO_Mailing::getTableName(); $job = CRM_Mailing_BAO_MailingJob::getTableName(); $mg = CRM_Mailing_DAO_MailingGroup::getTableName(); $eq = CRM_Mailing_Event_DAO_Queue::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); if ($mode == 'sms') { $phone = CRM_Core_DAO_Phone::getTableName(); } $contact = CRM_Contact_DAO_Contact::getTableName(); $group = CRM_Contact_DAO_Group::getTableName(); $g2contact = CRM_Contact_DAO_GroupContact::getTableName(); $m = new CRM_Mailing_DAO_Mailing(); $m->id = $mailing_id; $m->find(TRUE); $email_selection_method = $m->email_selection_method; $location_type_id = $m->location_type_id; // Note: When determining the ORDER that results are returned, it's // the record that comes last that counts. That's because we are // INSERT'ing INTO a table with a primary id so that last record // over writes any previous record. switch ($email_selection_method) { case 'location-exclude': $location_filter = "({$email}.location_type_id != {$location_type_id})"; // If there is more than one email that doesn't match the location, // prefer the one marked is_bulkmail, followed by is_primary. $order_by = "ORDER BY {$email}.is_bulkmail, {$email}.is_primary"; break; case 'location-only': $location_filter = "({$email}.location_type_id = {$location_type_id})"; // If there is more than one email of the desired location, prefer // the one marked is_bulkmail, followed by is_primary. $order_by = "ORDER BY {$email}.is_bulkmail, {$email}.is_primary"; break; case 'location-prefer': $location_filter = "({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1 OR {$email}.location_type_id = {$location_type_id})"; // ORDER BY is more complicated because we have to set an arbitrary // order that prefers the location that we want. We do that using // the FIELD function. For more info, see: // https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field // We assign the location type we want the value "1" by putting it // in the first position after we name the field. All other location // types are left out, so they will be assigned the value 0. That // means, they will all be equally tied for first place, with our // location being last. $order_by = "ORDER BY FIELD({$email}.location_type_id, {$location_type_id}), {$email}.is_bulkmail, {$email}.is_primary"; break; case 'automatic': // fall through to default // fall through to default default: $location_filter = "({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)"; $order_by = "ORDER BY {$email}.is_bulkmail"; } /* Create a temp table for contact exclusion */ $mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id}\n (contact_id int primary key)\n ENGINE=HEAP"); /* Add all the members of groups excluded from this mailing to the temp * table */ $excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubGroup); /* Add all unsubscribe members of base group from this mailing to the temp * table */ $unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'"; $mailingGroup->query($unSubscribeBaseGroup); /* Add all the (intended) recipients of an excluded prior mailing to * the temp table */ $excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubMailing); // get all the saved searches AND hierarchical groups // and load them in the cache $sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n"; $groupDAO = CRM_Core_DAO::executeQuery($sql); while ($groupDAO->fetch()) { if ($groupDAO->cache_date == NULL) { CRM_Contact_BAO_GroupContactCache::load($groupDAO); } $smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM civicrm_group_contact_cache c\nWHERE c.group_id = {$groupDAO->id}\n"; $mailingGroup->query($smartGroupExclude); } $tempColumn = 'email_id'; if ($mode == 'sms') { $tempColumn = 'phone_id'; } /* Get all the group contacts we want to include */ $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n ({$tempColumn} int, contact_id int primary key)\n ENGINE=HEAP"); /* Get the group contacts, but only those which are not in the * exclusion temp table */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.email IS NOT NULL\n AND {$email}.email != ''\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}"; if ($mode == 'sms') { $phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name'); $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$phone}.phone IS NOT NULL\n AND {$phone}.phone != ''\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); /* Query prior mailings */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}"; if ($mode == 'sms') { $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); $sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n"; $groupDAO = CRM_Core_DAO::executeQuery($sql); while ($groupDAO->fetch()) { if ($groupDAO->cache_date == NULL) { CRM_Contact_BAO_GroupContactCache::load($groupDAO); } $smartGroupInclude = "\nREPLACE INTO I_{$job_id} (email_id, contact_id)\nSELECT civicrm_email.id as email_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_email ON civicrm_email.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_email = 0\n AND c.is_opt_out = 0\n AND c.is_deceased <> 1\n AND {$location_filter}\n AND civicrm_email.on_hold = 0\n AND X_{$job_id}.contact_id IS null\n{$order_by}\n"; if ($mode == 'sms') { $smartGroupInclude = "\nREPLACE INTO I_{$job_id} (phone_id, contact_id)\nSELECT p.id as phone_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_phone p ON p.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_sms = 0\n AND c.is_opt_out = 0\n AND c.is_deceased <> 1\n AND p.phone_type_id = {$phoneTypes['Mobile']}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($smartGroupInclude); } /** * Construct the filtered search queries */ $query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id); $query = "REPLACE INTO I_{$job_id} ({$tempColumn}, contact_id)\n {$customSQL}"; $mailingGroup->query($query); } /* Get the emails with only location override */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}"; if ($mode == "sms") { $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); $eq = new CRM_Mailing_Event_BAO_Queue(); list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(); $aclWhere = $aclWhere ? "WHERE {$aclWhere}" : ''; if ($storeRecipients && $mailing_id) { $sql = "\nDELETE\nFROM civicrm_mailing_recipients\nWHERE mailing_id = %1\n"; $params = array(1 => array($mailing_id, 'Integer')); CRM_Core_DAO::executeQuery($sql, $params); // CRM-3975 $groupBy = $groupJoin = ''; if ($dedupeEmail) { $groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id"; $groupBy = " GROUP BY e.email "; } $sql = "\nINSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} )\nSELECT %1, i.contact_id, i.{$tempColumn}\nFROM civicrm_contact contact_a\nINNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$groupJoin}\n {$aclFrom}\n {$aclWhere}\n {$groupBy}\nORDER BY i.contact_id, i.{$tempColumn}\n"; CRM_Core_DAO::executeQuery($sql, $params); // if we need to add all emails marked bulk, do it as a post filter // on the mailing recipients table if (CRM_Core_BAO_Email::isMultipleBulkMail()) { self::addMultipleEmails($mailing_id); } } /* Delete the temp table */ $mailingGroup->reset(); $mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}"); $mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}"); return $eq; }
/** * Old Contact quick search api. * * @deprecated * * @param array $params * * @return array * @throws \API_Exception */ function civicrm_api3_contact_getquick($params) { $name = CRM_Utils_Type::escape(CRM_Utils_Array::value('name', $params), 'String'); $table_name = CRM_Utils_String::munge($params['table_name']); // get the autocomplete options from settings $acpref = explode(CRM_Core_DAO::VALUE_SEPARATOR, CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, 'contact_autocomplete_options')); // get the option values for contact autocomplete $acOptions = CRM_Core_OptionGroup::values('contact_autocomplete_options', FALSE, FALSE, FALSE, NULL, 'name'); $list = array(); foreach ($acpref as $value) { if ($value && !empty($acOptions[$value])) { $list[$value] = $acOptions[$value]; } } // If we are doing quicksearch by a field other than name, make sure that field is added to results if (!empty($params['field_name'])) { $field_name = CRM_Utils_String::munge($params['field_name']); // Unique name contact_id = id if ($field_name == 'contact_id') { $field_name = 'id'; } // phone_numeric should be phone $searchField = str_replace('_numeric', '', $field_name); if (!in_array($searchField, $list)) { $list[] = $searchField; } } else { // Set field name to first name for exact match checking. $field_name = 'sort_name'; } $select = $actualSelectElements = array('sort_name'); $where = ''; $from = array(); foreach ($list as $value) { $suffix = substr($value, 0, 2) . substr($value, -1); switch ($value) { case 'street_address': case 'city': case 'postal_code': $selectText = $value; $value = "address"; $suffix = 'sts'; case 'phone': case 'email': $actualSelectElements[] = $select[] = $value == 'address' ? $selectText : $value; if ($value == 'phone') { $actualSelectElements[] = $select[] = 'phone_ext'; } $from[$value] = "LEFT JOIN civicrm_{$value} {$suffix} ON ( cc.id = {$suffix}.contact_id AND {$suffix}.is_primary = 1 ) "; break; case 'country': case 'state_province': $select[] = "{$suffix}.name as {$value}"; $actualSelectElements[] = "{$suffix}.name"; if (!in_array('address', $from)) { $from['address'] = 'LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1) '; } $from[$value] = " LEFT JOIN civicrm_{$value} {$suffix} ON ( sts.{$value}_id = {$suffix}.id ) "; break; default: if ($value != 'id') { $suffix = 'cc'; if (!empty($params['field_name']) && $params['field_name'] == 'value') { $suffix = CRM_Utils_String::munge(CRM_Utils_Array::value('table_name', $params, 'cc')); } $actualSelectElements[] = $select[] = $suffix . '.' . $value; } break; } } $config = CRM_Core_Config::singleton(); $as = $select; $select = implode(', ', $select); if (!empty($select)) { $select = ", {$select}"; } $actualSelectElements = implode(', ', $actualSelectElements); $selectAliases = $from; unset($selectAliases['address']); $selectAliases = implode(', ', array_keys($selectAliases)); if (!empty($selectAliases)) { $selectAliases = ", {$selectAliases}"; } $from = implode(' ', $from); $limit = (int) CRM_Utils_Array::value('limit', $params); $limit = $limit > 0 ? $limit : Civi::settings()->get('search_autocomplete_count'); // add acl clause here list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc'); if ($aclWhere) { $where .= " AND {$aclWhere} "; } if (!empty($params['org'])) { $where .= " AND contact_type = \"Organization\""; // CRM-7157, hack: get current employer details when // employee_id is present. $currEmpDetails = array(); if (!empty($params['employee_id'])) { if ($currentEmployer = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', (int) $params['employee_id'], 'employer_id')) { if ($config->includeWildCardInName) { $strSearch = "%{$name}%"; } else { $strSearch = "{$name}%"; } // get current employer details $dao = CRM_Core_DAO::executeQuery("SELECT cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data, sort_name\n FROM civicrm_contact cc {$from} WHERE cc.contact_type = \"Organization\" AND cc.id = {$currentEmployer} AND cc.sort_name LIKE '{$strSearch}'"); if ($dao->fetch()) { $currEmpDetails = array('id' => $dao->id, 'data' => $dao->data); } } } } if (!empty($params['contact_sub_type'])) { $contactSubType = CRM_Utils_Type::escape($params['contact_sub_type'], 'String'); $where .= " AND cc.contact_sub_type = '{$contactSubType}'"; } if (!empty($params['contact_type'])) { $contactType = CRM_Utils_Type::escape($params['contact_type'], 'String'); $where .= " AND cc.contact_type LIKE '{$contactType}'"; } // Set default for current_employer or return contact with particular id if (!empty($params['id'])) { $where .= " AND cc.id = " . (int) $params['id']; } if (!empty($params['cid'])) { $where .= " AND cc.id <> " . (int) $params['cid']; } // Contact's based of relationhip type $relType = NULL; if (!empty($params['rel'])) { $relation = explode('_', CRM_Utils_Array::value('rel', $params)); $relType = CRM_Utils_Type::escape($relation[0], 'Integer'); $rel = CRM_Utils_Type::escape($relation[2], 'String'); } if ($config->includeWildCardInName) { $strSearch = "%{$name}%"; } else { $strSearch = "{$name}%"; } $includeEmailFrom = $includeNickName = $exactIncludeNickName = ''; if ($config->includeNickNameInName) { $includeNickName = " OR nick_name LIKE '{$strSearch}'"; $exactIncludeNickName = " OR nick_name LIKE '{$name}'"; } //CRM-10687 if (!empty($params['field_name']) && !empty($params['table_name'])) { $whereClause = " WHERE ( {$table_name}.{$field_name} LIKE '{$strSearch}') {$where}"; $exactWhereClause = " WHERE ( {$table_name}.{$field_name} = '{$name}') {$where}"; // Search by id should be exact if ($field_name == 'id' || $field_name == 'external_identifier') { $whereClause = $exactWhereClause; } } else { $whereClause = " WHERE ( sort_name LIKE '{$strSearch}' {$includeNickName} ) {$where} "; $exactWhereClause = " WHERE ( sort_name LIKE '{$name}' {$exactIncludeNickName} ) {$where} "; if ($config->includeEmailInName) { if (!in_array('email', $list)) { $includeEmailFrom = "LEFT JOIN civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 )"; } $emailWhere = " WHERE email LIKE '{$strSearch}'"; } } $additionalFrom = ''; if ($relType) { $additionalFrom = "\n INNER JOIN civicrm_relationship_type r ON (\n r.id = {$relType}\n AND ( cc.contact_type = r.contact_type_{$rel} OR r.contact_type_{$rel} IS NULL )\n AND ( cc.contact_sub_type = r.contact_sub_type_{$rel} OR r.contact_sub_type_{$rel} IS NULL )\n )"; } // check if only CMS users are requested if (!empty($params['cmsuser'])) { $additionalFrom = "\n INNER JOIN civicrm_uf_match um ON (um.contact_id=cc.id)\n "; } $orderByInner = $orderByOuter = "ORDER BY exactFirst"; if ($config->includeOrderByClause) { $orderByInner = "ORDER BY exactFirst, sort_name"; $orderByOuter .= ", sort_name"; } //CRM-5954 $query = "\n SELECT DISTINCT(id), data, sort_name {$selectAliases}, exactFirst\n FROM (\n ( SELECT IF({$table_name}.{$field_name} = '{$name}', 0, 1) as exactFirst, cc.id as id, CONCAT_WS( ' :: ',\n {$actualSelectElements} )\n as data\n {$select}\n FROM civicrm_contact cc {$from}\n {$aclFrom}\n {$additionalFrom}\n {$whereClause}\n {$orderByInner}\n LIMIT 0, {$limit} )\n "; if (!empty($emailWhere)) { $query .= "\n UNION (\n SELECT IF({$table_name}.{$field_name} = '{$name}', 0, 1) as exactFirst, cc.id as id, CONCAT_WS( ' :: ',\n {$actualSelectElements} )\n as data\n {$select}\n FROM civicrm_contact cc {$from}\n {$aclFrom}\n {$additionalFrom} {$includeEmailFrom}\n {$emailWhere} AND cc.is_deleted = 0 " . ($aclWhere ? " AND {$aclWhere} " : '') . "\n {$orderByInner}\n LIMIT 0, {$limit}\n )\n "; } $query .= ") t\n {$orderByOuter}\n LIMIT 0, {$limit}\n "; // send query to hook to be modified if needed CRM_Utils_Hook::contactListQuery($query, $name, empty($params['context']) ? NULL : CRM_Utils_Type::escape($params['context'], 'String'), empty($params['id']) ? NULL : $params['id']); $dao = CRM_Core_DAO::executeQuery($query); $contactList = array(); $listCurrentEmployer = TRUE; while ($dao->fetch()) { $t = array('id' => $dao->id); foreach ($as as $k) { $t[$k] = isset($dao->{$k}) ? $dao->{$k} : ''; } $t['data'] = $dao->data; $contactList[] = $t; if (!empty($params['org']) && !empty($currEmpDetails) && $dao->id == $currEmpDetails['id']) { $listCurrentEmployer = FALSE; } } //return organization name if doesn't exist in db if (empty($contactList)) { if (!empty($params['org'])) { if ($listCurrentEmployer && !empty($currEmpDetails)) { $contactList = array(array('data' => $currEmpDetails['data'], 'id' => $currEmpDetails['id'])); } else { $contactList = array(array('data' => $name, 'id' => $name)); } } } return civicrm_api3_create_success($contactList, $params, 'Contact', 'getquick'); }
/** * Return the SQL query for getting only the interesting results out of the dedupe table. * * @$checkPermission boolean $params a flag to indicate if permission should be considered. * default is to always check permissioning but public pages for example might not want * permission to be checked for anonymous users. Refer CRM-6211. We might be beaking * Multi-Site dedupe for public pages. * * @param bool $checkPermission * * @return string */ public function thresholdQuery($checkPermission = TRUE) { $this->_aclFrom = ''; // CRM-6603: anonymous dupechecks side-step ACLs $this->_aclWhere = ' AND is_deleted = 0 '; if ($this->params && !$this->noRules) { if ($checkPermission) { list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('civicrm_contact'); $this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : ''; } $query = "SELECT dedupe.id1 as id\n FROM dedupe JOIN civicrm_contact ON dedupe.id1 = civicrm_contact.id {$this->_aclFrom}\n WHERE contact_type = '{$this->contact_type}' {$this->_aclWhere}\n AND weight >= {$this->threshold}"; } else { $this->_aclWhere = ' AND c1.is_deleted = 0 AND c2.is_deleted = 0'; if ($checkPermission) { list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(array('c1', 'c2')); $this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : ''; } $query = "SELECT dedupe.id1, dedupe.id2, dedupe.weight\n FROM dedupe JOIN civicrm_contact c1 ON dedupe.id1 = c1.id\n JOIN civicrm_contact c2 ON dedupe.id2 = c2.id {$this->_aclFrom}\n LEFT JOIN civicrm_dedupe_exception exc ON dedupe.id1 = exc.contact_id1 AND dedupe.id2 = exc.contact_id2\n WHERE c1.contact_type = '{$this->contact_type}' AND\n c2.contact_type = '{$this->contact_type}' {$this->_aclWhere}\n AND weight >= {$this->threshold} AND exc.contact_id1 IS NULL"; } CRM_Utils_Hook::dupeQuery($this, 'threshold', $query); return $query; }
/** * We are over-riding this because the current choice is NO acls or automatically adding contact.is_deleted * which is a pain when left joining form another table * @see CRM_Report_Form::buildACLClause($tableAlias) * * @param string $tableAlias * */ function buildACLClause($tableAlias = 'contact_a') { list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias); if ($this->_skipACLContactDeletedClause && CRM_Core_Permission::check('access deleted contacts')) { if (trim($this->_aclWhere) == "{$tableAlias}.is_deleted = 0") { $this->_aclWhere = NULL; } else { $this->_aclWhere = str_replace("AND {$tableAlias}.is_deleted = 0", '', $this->_aclWhere); } } }
static function &getRecipients($job_id, $mailing_id = NULL, $offset = NULL, $limit = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL) { $mailingGroup = new CRM_Mailing_DAO_MailingGroup(); $mailing = CRM_Mailing_BAO_Mailing::getTableName(); $job = CRM_Mailing_BAO_MailingJob::getTableName(); $mg = CRM_Mailing_DAO_MailingGroup::getTableName(); $eq = CRM_Mailing_Event_DAO_Queue::getTableName(); $ed = CRM_Mailing_Event_DAO_Delivered::getTableName(); $eb = CRM_Mailing_Event_DAO_Bounce::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); if ($mode == 'sms') { $phone = CRM_Core_DAO_Phone::getTableName(); } $contact = CRM_Contact_DAO_Contact::getTableName(); $group = CRM_Contact_DAO_Group::getTableName(); $g2contact = CRM_Contact_DAO_GroupContact::getTableName(); /* Create a temp table for contact exclusion */ $mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id}\n (contact_id int primary key)\n ENGINE=HEAP"); /* Add all the members of groups excluded from this mailing to the temp * table */ $excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubGroup); /* Add all unsubscribe members of base group from this mailing to the temp * table */ $unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'"; $mailingGroup->query($unSubscribeBaseGroup); /* Add all the (intended) recipients of an excluded prior mailing to * the temp table */ $excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubMailing); // get all the saved searches AND hierarchical groups // and load them in the cache $sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n"; $groupDAO = CRM_Core_DAO::executeQuery($sql); while ($groupDAO->fetch()) { if ($groupDAO->cache_date == NULL) { CRM_Contact_BAO_GroupContactCache::load($groupDAO); } $smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM civicrm_group_contact_cache c\nWHERE c.group_id = {$groupDAO->id}\n"; $mailingGroup->query($smartGroupExclude); } $tempColumn = 'email_id'; if ($mode == 'sms') { $tempColumn = 'phone_id'; } /* Get all the group contacts we want to include */ $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n ({$tempColumn} int, contact_id int primary key)\n ENGINE=HEAP"); /* Get the group contacts, but only those which are not in the * exclusion temp table */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.email IS NOT NULL\n AND {$email}.email != ''\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; if ($mode == 'sms') { $phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name'); $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$phone}.phone IS NOT NULL\n AND {$phone}.phone != ''\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); /* Query prior mailings */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; if ($mode == 'sms') { $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); $sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n"; $groupDAO = CRM_Core_DAO::executeQuery($sql); while ($groupDAO->fetch()) { if ($groupDAO->cache_date == NULL) { CRM_Contact_BAO_GroupContactCache::load($groupDAO); } $smartGroupInclude = "\nINSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\nSELECT e.id as email_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_email e ON e.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_email = 0\n AND c.is_opt_out = 0\n AND c.is_deceased = 0\n AND (e.is_bulkmail = 1 OR e.is_primary = 1)\n AND e.on_hold = 0\n AND X_{$job_id}.contact_id IS null\nORDER BY e.is_bulkmail\n"; if ($mode == 'sms') { $smartGroupInclude = "\nINSERT IGNORE INTO I_{$job_id} (phone_id, contact_id)\nSELECT p.id as phone_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_phone p ON p.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_sms = 0\n AND c.is_opt_out = 0\n AND c.is_deceased = 0\n AND p.phone_type_id = {$phoneTypes['Mobile']}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($smartGroupInclude); } /** * Construct the filtered search queries */ $query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id); $query = "REPLACE INTO I_{$job_id} ({$tempColumn}, contact_id)\n {$customSQL}"; $mailingGroup->query($query); } /* Get the emails with only location override */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; if ($mode == "sms") { $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); $results = array(); $eq = new CRM_Mailing_Event_BAO_Queue(); list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(); $aclWhere = $aclWhere ? "WHERE {$aclWhere}" : ''; $limitString = NULL; if ($limit && $offset !== NULL) { $offset = CRM_Utils_Type::escape($offset, 'Int'); $limit = CRM_Utils_Type::escape($limit, 'Int'); $limitString = "LIMIT {$offset}, {$limit}"; } if ($storeRecipients && $mailing_id) { $sql = "\nDELETE\nFROM civicrm_mailing_recipients\nWHERE mailing_id = %1\n"; $params = array(1 => array($mailing_id, 'Integer')); CRM_Core_DAO::executeQuery($sql, $params); // CRM-3975 $groupBy = $groupJoin = ''; if ($dedupeEmail) { $groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id"; $groupBy = " GROUP BY e.email "; } $sql = "\nINSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} )\nSELECT %1, i.contact_id, i.{$tempColumn}\nFROM civicrm_contact contact_a\nINNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$groupJoin}\n {$aclFrom}\n {$aclWhere}\n {$groupBy}\nORDER BY i.contact_id, i.{$tempColumn}\n"; CRM_Core_DAO::executeQuery($sql, $params); // if we need to add all emails marked bulk, do it as a post filter // on the mailing recipients table if (CRM_Core_BAO_Email::isMultipleBulkMail()) { self::addMultipleEmails($mailing_id); } } /* Delete the temp table */ $mailingGroup->reset(); $mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}"); $mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}"); return $eq; }
/** * Function to get email address of a contact */ static function getContactEmail() { if (CRM_Utils_Array::value('contact_id', $_POST)) { $contactID = CRM_Utils_Type::escape($_POST['contact_id'], 'Positive'); require_once 'CRM/Contact/BAO/Contact/Location.php'; list($displayName, $userEmail) = CRM_Contact_BAO_Contact_Location::getEmailDetails($contactID); if ($userEmail) { echo $userEmail; } } else { $noemail = CRM_Utils_Array::value('noemail', $_GET); if ($name = CRM_Utils_Array::value('name', $_GET)) { $name = CRM_Utils_Type::escape($name, 'String'); if ($noemail) { $queryString = " cc.sort_name LIKE '%{$name}%'"; } else { $queryString = " ( cc.sort_name LIKE '%{$name}%' OR ce.email LIKE '%{$name}%' ) "; } } else { $cid = CRM_Utils_Array::value('cid', $_GET); $queryString = " cc.id IN ( {$cid} )"; } // add acl clause here require_once 'CRM/Contact/BAO/Contact/Permission.php'; list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc'); if ($aclWhere) { $aclWhere = " AND {$aclWhere}"; } if ($noemail) { $query = "\nSELECT sort_name name, cc.id\nFROM civicrm_contact cc \n {$aclFrom}\nWHERE {$queryString}\n {$aclWhere}\n"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $result[] = array('name' => $dao->name, 'id' => $dao->id); } } else { $query = "\nSELECT sort_name name, ce.email, cc.id\nFROM civicrm_email ce INNER JOIN civicrm_contact cc ON cc.id = ce.contact_id\n {$aclFrom}\nWHERE ce.on_hold = 0 AND cc.is_deceased = 0 AND cc.do_not_email = 0 AND {$queryString}\n {$aclWhere}\n"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $result[] = array('name' => '"' . $dao->name . '" <' . $dao->email . '>', 'id' => CRM_Utils_Array::value('id', $_GET) ? "{$dao->id}::{$dao->email}" : '"' . $dao->name . '" <' . $dao->email . '>'); } } if ($result) { echo json_encode($result); } } exit; }
function buildACLClause($tableAlias = 'contact_a') { require_once 'CRM/Contact/BAO/Contact/Permission.php'; list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias); }
function &getRecipients($job_id, $includeDelivered = false, $mailing_id = null, $offset = NULL, $limit = NULL) { $mailingGroup = new CRM_Mailing_DAO_Group(); $mailing = CRM_Mailing_BAO_Mailing::getTableName(); $job = CRM_Mailing_BAO_Job::getTableName(); $mg = CRM_Mailing_DAO_Group::getTableName(); $eq = CRM_Mailing_Event_DAO_Queue::getTableName(); $ed = CRM_Mailing_Event_DAO_Delivered::getTableName(); $eb = CRM_Mailing_Event_DAO_Bounce::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); $contact = CRM_Contact_DAO_Contact::getTableName(); require_once 'CRM/Contact/DAO/Group.php'; $group = CRM_Contact_DAO_Group::getTableName(); $g2contact = CRM_Contact_DAO_GroupContact::getTableName(); /* Create a temp table for contact exclusion */ $mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id} \n (contact_id int primary key) \n ENGINE=HEAP"); /* Add all the members of groups excluded from this mailing to the temp * table */ $excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubGroup); /* Add all unsubscribe members of base group from this mailing to the temp * table */ $unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'"; $mailingGroup->query($unSubscribeBaseGroup); /* Add all the (intended) recipients of an excluded prior mailing to * the temp table */ $excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubMailing); $ss = new CRM_Core_DAO(); $ss->query("SELECT {$group}.saved_search_id as saved_search_id,\n {$group}.id as id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n WHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND {$group}.saved_search_id IS NOT null"); $whereTables = array(); while ($ss->fetch()) { /* run the saved search query and dump result contacts into the temp * table */ $tables = array($contact => 1); $sql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ss->saved_search_id); $sql = $sql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM {$g2contact} \n WHERE {$g2contact}.group_id = {$ss->id} AND {$g2contact}.status = 'Removed')"; $mailingGroup->query("INSERT IGNORE INTO X_{$job_id} (contact_id) {$sql}"); } /* Get all the group contacts we want to include */ $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id} \n (email_id int, contact_id int primary key)\n ENGINE=HEAP"); /* Get the group contacts, but only those which are not in the * exclusion temp table */ /* Get the emails with no override */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n ({$mg}.group_type = 'Include' OR {$mg}.group_type = 'Base')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.email_id IS null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; $mailingGroup->query($query); /* Query prior mailings */ $mailingGroup->query("REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include' OR {$mg}.group_type = 'Base')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"); /* Construct the saved-search queries */ $ss->query("SELECT {$group}.saved_search_id as saved_search_id,\n {$group}.id as id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n AND {$mg}.entity_table = '{$group}'\n WHERE \n {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND {$group}.saved_search_id IS NOT null"); $whereTables = array(); while ($ss->fetch()) { $tables = array($contact => 1, $location => 1, $email => 1); list($from, $where) = CRM_Contact_BAO_SavedSearch::fromWhereEmail($ss->saved_search_id); $where = trim($where); if ($where) { $where = " AND {$where} "; } $ssq = "INSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n contact_a.id as contact_id \n {$from}\n LEFT JOIN X_{$job_id}\n ON contact_a.id = X_{$job_id}.contact_id\n WHERE \n contact_a.do_not_email = 0\n AND contact_a.is_opt_out = 0\n AND contact_a.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n {$where}\n AND contact_a.id NOT IN ( \n SELECT contact_id FROM {$g2contact} \n WHERE {$g2contact}.group_id = {$ss->id} AND {$g2contact}.status = 'Removed') \n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; $mailingGroup->query($ssq); } /** * Construct the filtered search queries */ $query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n"; $dao = CRM_Core_DAO::executeQuery($query); require_once 'CRM/Contact/BAO/SearchCustom.php'; while ($dao->fetch()) { $customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id); $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n {$customSQL}"; $mailingGroup->query($query); } /* Get the emails with only location override */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.email_id is null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; $mailingGroup->query($query); /* Get the emails with full override */ $mailingGroup->query("REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$g2contact}\n ON {$email}.id = {$g2contact}.email_id\n INNER JOIN {$contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.email_id IS NOT null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"); $results = array(); $eq = new CRM_Mailing_Event_BAO_Queue(); require_once 'CRM/Contact/BAO/Contact/Permission.php'; list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(); $aclWhere = $aclWhere ? "WHERE {$aclWhere}" : ''; $limitString = null; if ($limit && $offset !== null) { $limitString = "LIMIT {$offset}, {$limit}"; } $eq->query("SELECT i.contact_id, i.email_id \n FROM civicrm_contact contact_a\n INNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$aclFrom}\n {$aclWhere}\n ORDER BY i.contact_id, i.email_id\n {$limitString}"); /* Delete the temp table */ $mailingGroup->reset(); $mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}"); $mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}"); return $eq; }
/** * Return the SQL query for getting only the interesting results out of the dedupe table. */ function thresholdQuery() { require_once 'CRM/Contact/BAO/Contact/Permission.php'; if ($this->params && !$this->noRules) { list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('civicrm_contact'); $this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : ''; $query = "SELECT dedupe.id\n FROM dedupe JOIN civicrm_contact USING (id) {$this->_aclFrom}\n WHERE contact_type = '{$this->contact_type}' {$this->_aclWhere}\n GROUP BY dedupe.id HAVING SUM(weight) >= {$this->threshold}\n ORDER BY SUM(weight) desc"; } else { list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(array('c1', 'c2')); $this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : ''; $query = "SELECT dedupe.id1, dedupe.id2, SUM(weight) as weight\n FROM dedupe JOIN civicrm_contact c1 ON dedupe.id1 = c1.id \n JOIN civicrm_contact c2 ON dedupe.id2 = c2.id {$this->_aclFrom}\n WHERE c1.contact_type = '{$this->contact_type}' AND \n c2.contact_type = '{$this->contact_type}' {$this->_aclWhere}\n GROUP BY dedupe.id1, dedupe.id2 HAVING SUM(weight) >= {$this->threshold}\n ORDER BY SUM(weight) desc"; } return $query; }
function civicrm_api3_contact_getquick($params) { civicrm_api3_verify_mandatory($params, NULL, array('name')); $name = CRM_Utils_Array::value('name', $params); // get the autocomplete options from settings require_once 'CRM/Core/BAO/Setting.php'; $acpref = explode(CRM_Core_DAO::VALUE_SEPARATOR, CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, 'contact_autocomplete_options')); // get the option values for contact autocomplete $acOptions = CRM_Core_OptionGroup::values('contact_autocomplete_options', FALSE, FALSE, FALSE, NULL, 'name'); $list = array(); foreach ($acpref as $value) { if ($value && CRM_Utils_Array::value($value, $acOptions)) { $list[$value] = $acOptions[$value]; } } $select = $actualSelectElements = array('sort_name'); $where = ''; $from = array(); foreach ($list as $value) { $suffix = substr($value, 0, 2) . substr($value, -1); switch ($value) { case 'street_address': case 'city': $selectText = $value; $value = "address"; $suffix = 'sts'; case 'phone': case 'email': $actualSelectElements[] = $select[] = $value == 'address' ? $selectText : $value; $from[$value] = "LEFT JOIN civicrm_{$value} {$suffix} ON ( cc.id = {$suffix}.contact_id AND {$suffix}.is_primary = 1 ) "; break; case 'country': case 'state_province': $select[] = "{$suffix}.name as {$value}"; $actualSelectElements[] = "{$suffix}.name"; if (!in_array('address', $from)) { $from['address'] = 'LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1) '; } $from[$value] = " LEFT JOIN civicrm_{$value} {$suffix} ON ( sts.{$value}_id = {$suffix}.id ) "; break; } } $config = CRM_Core_Config::singleton(); $as = $select; $select = implode(', ', $select); if (!empty($select)) { $select = ", {$select}"; } $actualSelectElements = implode(', ', $actualSelectElements); $selectAliases = $from; unset($selectAliases['address']); $selectAliases = implode(', ', array_keys($selectAliases)); if (!empty($selectAliases)) { $selectAliases = ", {$selectAliases}"; } $from = implode(' ', $from); $limit = CRM_Utils_Array::value('limit', $params, 10); // add acl clause here require_once 'CRM/Contact/BAO/Contact/Permission.php'; list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc'); if ($aclWhere) { $where .= " AND {$aclWhere} "; } if (CRM_Utils_Array::value('org', $params)) { $where .= " AND contact_type = \"Organization\""; //set default for current_employer if ($orgId = CRM_Utils_Array::value('id', $params)) { $where .= " AND cc.id = {$orgId}"; } // CRM-7157, hack: get current employer details when // employee_id is present. $currEmpDetails = array(); if (CRM_Utils_Array::value('employee_id', $params)) { if ($currentEmployer = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', CRM_Utils_Array::value('employee_id', $params), 'employer_id')) { if ($config->includeWildCardInName) { $strSearch = "%{$name}%"; } else { $strSearch = "{$name}%"; } // get current employer details $dao = CRM_Core_DAO::executeQuery("SELECT cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data, sort_name\n FROM civicrm_contact cc {$from} WHERE cc.contact_type = \"Organization\" AND cc.id = {$currentEmployer} AND cc.sort_name LIKE '{$strSearch}'"); if ($dao->fetch()) { $currEmpDetails = array('id' => $dao->id, 'data' => $dao->data); } } } } if (CRM_Utils_Array::value('cid', $params)) { $where .= " AND cc.id <> {$params['cid']}"; } //contact's based of relationhip type $relType = NULL; if (CRM_Utils_Array::value('rel', $params)) { $relation = explode('_', CRM_Utils_Array::value('rel', $params)); $relType = CRM_Utils_Type::escape($relation[0], 'Integer'); $rel = CRM_Utils_Type::escape($relation[2], 'String'); } if ($config->includeWildCardInName) { $strSearch = "%{$name}%"; } else { $strSearch = "{$name}%"; } $includeEmailFrom = $includeNickName = $exactIncludeNickName = ''; if ($config->includeNickNameInName) { $includeNickName = " OR nick_name LIKE '{$strSearch}'"; $exactIncludeNickName = " OR nick_name LIKE '{$name}'"; } if ($config->includeEmailInName) { if (!in_array('email', $list)) { $includeEmailFrom = "LEFT JOIN civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 )"; } $whereClause = " WHERE ( email LIKE '{$strSearch}' OR sort_name LIKE '{$strSearch}' {$includeNickName} ) {$where} "; $exactWhereClause = " WHERE ( email LIKE '{$name}' OR sort_name LIKE '{$name}' {$exactIncludeNickName} ) {$where} "; } else { $whereClause = " WHERE ( sort_name LIKE '{$strSearch}' {$includeNickName} ) {$where} "; $exactWhereClause = " WHERE ( sort_name LIKE '{$name}' {$exactIncludeNickName} ) {$where} "; } $additionalFrom = ''; if ($relType) { $additionalFrom = "\n INNER JOIN civicrm_relationship_type r ON (\n r.id = {$relType}\n AND ( cc.contact_type = r.contact_type_{$rel} OR r.contact_type_{$rel} IS NULL )\n AND ( cc.contact_sub_type = r.contact_sub_type_{$rel} OR r.contact_sub_type_{$rel} IS NULL )\n )"; } //CRM-5954 $query = "\n SELECT DISTINCT(id), data, sort_name {$selectAliases}\n FROM (\n ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data {$select}\n FROM civicrm_contact cc {$from}\n {$aclFrom}\n {$additionalFrom} {$includeEmailFrom}\n {$exactWhereClause}\n LIMIT 0, {$limit} )\n UNION\n ( SELECT 1 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data {$select}\n FROM civicrm_contact cc {$from}\n {$aclFrom}\n {$additionalFrom} {$includeEmailFrom}\n {$whereClause}\n ORDER BY sort_name\n LIMIT 0, {$limit} )\n) t\nORDER BY exactFirst, sort_name\nLIMIT 0, {$limit}\n "; // send query to hook to be modified if needed require_once 'CRM/Utils/Hook.php'; CRM_Utils_Hook::contactListQuery($query, $name, CRM_Utils_Array::value('context', $params), CRM_Utils_Array::value('id', $params)); $dao = CRM_Core_DAO::executeQuery($query); $contactList = array(); $listCurrentEmployer = TRUE; while ($dao->fetch()) { $t = array('id' => $dao->id); foreach ($as as $k) { $t[$k] = $dao->{$k}; } $t['data'] = $dao->data; $contactList[] = $t; if (CRM_Utils_Array::value('org', $params) && !empty($currEmpDetails) && $dao->id == $currEmpDetails['id']) { $listCurrentEmployer = FALSE; } } //return organization name if doesn't exist in db if (empty($contactList)) { if (CRM_Utils_Array::value('org', $params)) { if ($listCurrentEmployer && !empty($currEmpDetails)) { $contactList = array(array('data' => $currEmpDetails['data'], 'id' => $currEmpDetails['id'])); } else { $contactList = array(array('data' => $name, 'id' => $name)); } } } return civicrm_api3_create_success($contactList, $params); }