/** * Generate a query to locate recipients who match the given * schedule. * * @param \CRM_Core_DAO_ActionSchedule $schedule * The schedule as configured by the administrator. * @param string $phase * See, e.g., RecipientBuilder::PHASE_RELATION_FIRST. * @return \CRM_Utils_SQL_Select * @see RecipientBuilder * @throws \CRM_Core_Exception */ public function createQuery($schedule, $phase, $defaultParams) { $selectedValues = (array) \CRM_Utils_Array::explodePadded($schedule->entity_value); $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); $query = \CRM_Utils_SQL_Select::from("{$this->entity} e")->param($defaultParams); $query['casAddlCheckFrom'] = 'civicrm_activity e'; $query['casContactIdField'] = 'r.contact_id'; $query['casEntityIdField'] = 'e.id'; $query['casContactTableAlias'] = NULL; $query['casDateField'] = 'e.activity_date_time'; if (!is_null($schedule->limit_to)) { $activityContacts = \CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name'); if ($schedule->limit_to == 0 || !isset($activityContacts[$schedule->recipient])) { $recipientTypeId = \CRM_Utils_Array::key('Activity Targets', $activityContacts); } else { $recipientTypeId = $schedule->recipient; } $query->join('r', "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$recipientTypeId}"); } // build where clause if (!empty($selectedValues)) { $query->where("e.activity_type_id IN (#selectedValues)")->param('selectedValues', $selectedValues); } else { $query->where("e.activity_type_id IS NULL"); } if (!empty($selectedStatuses)) { $query->where("e.status_id IN (#selectedStatuss)")->param('selectedStatuss', $selectedStatuses); } $query->where('e.is_current_revision = 1 AND e.is_deleted = 0'); return $query; }
/** * Generate a query to locate recipients who match the given * schedule. * * @param \CRM_Core_DAO_ActionSchedule $schedule * The schedule as configured by the administrator. * @param string $phase * See, e.g., RecipientBuilder::PHASE_RELATION_FIRST. * @param array $defaultParams * * @return \CRM_Utils_SQL_Select * @see RecipientBuilder */ public function createQuery($schedule, $phase, $defaultParams) { $selectedValues = (array) \CRM_Utils_Array::explodePadded($schedule->entity_value); $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); $query = \CRM_Utils_SQL_Select::from("{$this->entity} e")->param($defaultParams); $query['casAddlCheckFrom'] = 'civicrm_membership e'; $query['casContactIdField'] = 'e.contact_id'; $query['casEntityIdField'] = 'e.id'; $query['casContactTableAlias'] = NULL; $query['casDateField'] = str_replace('membership_', 'e.', $schedule->start_action_date); // FIXME: Numbers should be constants. if (in_array(2, $selectedStatuses)) { //auto-renew memberships $query->where("e.contribution_recur_id IS NOT NULL"); } elseif (in_array(1, $selectedStatuses)) { $query->where("e.contribution_recur_id IS NULL"); } if (!empty($selectedValues)) { $query->where("e.membership_type_id IN (@memberTypeValues)")->param('memberTypeValues', $selectedValues); } else { $query->where("e.membership_type_id IS NULL"); } $query->where("( e.is_override IS NULL OR e.is_override = 0 )"); $query->merge($this->prepareMembershipPermissionsFilter()); $query->where("e.status_id IN (#memberStatus)")->param('memberStatus', \CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id')); // Why is this only for civicrm_membership? if ($schedule->start_action_date && $schedule->is_repeat == FALSE) { $query['casUseReferenceDate'] = TRUE; } return $query; }
/** * Check for a UUID by entity type and ID. */ function _civicrm_api3_entity_id_find_by_uuid($entity_type, $uuid) { $query = CRM_Utils_SQL_Select::from('civicrm_managed m')->select(array('m.id', 'm.uuid', 'm.entity_type', 'm.entity_id'))->where('m.uuid = @uuid', array('@uuid' => $uuid))->where('m.entity_type = @entity_type', array('@entity_type' => $entity_type))->where('m.module = @module', array('@module' => 'civicrm_configexport'))->toSQL(); $dao = CRM_Core_DAO::executeQuery($query); if ($dao->fetch()) { return $dao->entity_id; } }
/** * @param string $bao_name * Name of BAO * @param array $params * As passed into api get function. * @param bool $isFillUniqueFields * Do we need to ensure unique fields continue to be populated for this api? (backward compatibility). */ public function __construct($bao_name, $params, $isFillUniqueFields) { $this->bao = new $bao_name(); $this->entity = _civicrm_api_get_entity_name_from_dao($this->bao); $this->params = $params; $this->isFillUniqueFields = $isFillUniqueFields; $this->checkPermissions = \CRM_Utils_Array::value('check_permissions', $this->params, FALSE); $this->options = _civicrm_api3_get_options_from_params($this->params); $this->entityFieldNames = _civicrm_api3_field_names(_civicrm_api3_build_fields_array($this->bao)); // Call this function directly instead of using the api wrapper to force unique field names off require_once 'api/v3/Generic.php'; $apiSpec = \civicrm_api3_generic_getfields(array('entity' => $this->entity, 'version' => 3, 'params' => array('action' => 'get')), FALSE); $this->apiFieldSpec = $apiSpec['values']; $this->query = \CRM_Utils_SQL_Select::from($this->bao->tableName() . " a"); }
/** * @param string $baoName * Name of BAO * @param array $params * As passed into api get function. * @param bool $isFillUniqueFields * Do we need to ensure unique fields continue to be populated for this api? (backward compatibility). */ public function __construct($baoName, $params, $isFillUniqueFields) { $bao = new $baoName(); $this->entity = _civicrm_api_get_entity_name_from_dao($bao); $this->params = $params; $this->isFillUniqueFields = $isFillUniqueFields; $this->checkPermissions = \CRM_Utils_Array::value('check_permissions', $this->params, FALSE); $this->options = _civicrm_api3_get_options_from_params($this->params); $this->entityFieldNames = _civicrm_api3_field_names(_civicrm_api3_build_fields_array($bao)); // Call this function directly instead of using the api wrapper to force unique field names off require_once 'api/v3/Generic.php'; $apiSpec = \civicrm_api3_generic_getfields(array('entity' => $this->entity, 'version' => 3, 'params' => array('action' => 'get')), FALSE); $this->apiFieldSpec = $apiSpec['values']; $this->query = \CRM_Utils_SQL_Select::from($bao->tableName() . ' ' . self::MAIN_TABLE_ALIAS); $bao->free(); // Add ACLs first to avoid redundant subclauses $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName)); }
/** * Generate action_log's for repeated, follow-up alerts to additional contacts. * * @throws \CRM_Core_Exception * @throws \Exception */ protected function buildAddlRepeatPass() { $query = $this->prepareQuery(self::PHASE_ADDITION_REPEAT); $addlCheck = \CRM_Utils_SQL_Select::from($query['casAddlCheckFrom'])->select('*')->merge($query, array('wheres'))->merge($this->prepareRepetitionEndFilter($query['casDateField']))->limit(1)->strict()->toSQL(); $daoCheck = \CRM_Core_DAO::executeQuery($addlCheck); if ($daoCheck->fetch()) { $repeatInsertAddl = \CRM_Utils_SQL_Select::from('civicrm_contact c')->merge($this->selectActionLogFields(self::PHASE_ADDITION_REPEAT, $query))->merge($this->joinReminder('INNER JOIN', 'addl', $query))->select("MAX(reminder.action_date_time) as latest_log_time")->merge($this->prepareAddlFilter('c.id'))->where("c.is_deleted = 0 AND c.is_deceased = 0")->groupBy("reminder.contact_id")->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval)")->param(array('casRepetitionInterval' => $this->parseRepetitionInterval()))->strict()->toSQL(); // For unknown reasons, we manually insert each row. Why not change // selectActionLogFields() to selectIntoActionLog() above? $addValues = \CRM_Core_DAO::executeQuery($repeatInsertAddl)->fetchAll(); if ($addValues) { \CRM_Core_DAO::executeQuery(\CRM_Utils_SQL_Insert::into('civicrm_action_log')->columns(array('contact_id', 'entity_id', 'entity_table', 'action_schedule_id'))->rows($addValues)->toSQL()); } } }
function testInterpolateBadKey() { try { $result = CRM_Utils_SQL_Select::from('ignore')->interpolate('this is a {var}', array('{var}' => 'not a well-formed variable name')); $this->fail('Expected exception; got: ' . var_export($result, TRUE)); } catch (CRM_Core_Exception $e) { $this->assertTrue(TRUE, "Caught expected exception"); } }
/** * Generate a query to locate contacts who match the given * schedule. * * @param \CRM_Core_DAO_ActionSchedule $schedule * @param string $phase * See, e.g., RecipientBuilder::PHASE_RELATION_FIRST. * @param array $defaultParams * Default parameters that should be included with query. * @return \CRM_Utils_SQL_Select * @see RecipientBuilder * @throws CRM_Core_Exception */ public function createQuery($schedule, $phase, $defaultParams) { $selectedValues = (array) \CRM_Utils_Array::explodePadded($schedule->entity_value); $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); $query = \CRM_Utils_SQL_Select::from("civicrm_contribution e")->param($defaultParams); $query['casAddlCheckFrom'] = 'civicrm_contribution e'; $query['casContactIdField'] = 'e.contact_id'; $query['casEntityIdField'] = 'e.id'; $query['casContactTableAlias'] = NULL; // $schedule->start_action_date is user-supplied data. validate. if (!array_key_exists($schedule->start_action_date, $this->getDateFields())) { throw new CRM_Core_Exception("Invalid date field"); } $query['casDateField'] = $schedule->start_action_date; // build where clause if (!empty($selectedValues)) { $query->where("e.contribution_page_id IN (@selectedValues)")->param('selectedValues', $selectedValues); } if (!empty($selectedStatuses)) { $query->where("e.contribution_status_id IN (#selectedStatuses)")->param('selectedStatuses', $selectedStatuses); } return $query; }
/** * Generate a query to locate contacts who match the given * schedule. * * @param \CRM_Core_DAO_ActionSchedule $schedule * @param string $phase * See, e.g., RecipientBuilder::PHASE_RELATION_FIRST. * @param array $defaultParams * Default parameters that should be included with query. * @return \CRM_Utils_SQL_Select * @see RecipientBuilder * @throws CRM_Core_Exception */ public function createQuery($schedule, $phase, $defaultParams) { $selectedValues = (array) \CRM_Utils_Array::explodePadded($schedule->entity_value); $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); $query = \CRM_Utils_SQL_Select::from("civicrm_contribution e")->param($defaultParams); $query['casAddlCheckFrom'] = 'civicrm_contribution e'; $query['casContactIdField'] = 'e.contact_id'; $query['casEntityIdField'] = 'e.id'; $query['casContactTableAlias'] = NULL; // $schedule->start_action_date is user-supplied data. validate. if (!array_key_exists($schedule->start_action_date, $this->getDateFields())) { throw new CRM_Core_Exception("Invalid date field"); } $query['casDateField'] = $schedule->start_action_date; // build where clause if (!empty($selectedValues)) { $query->where("e.financial_type_id IN (@selectedValues)")->param('selectedValues', $selectedValues); } if (!empty($selectedStatuses)) { $query->where("e.contribution_status_id IN (#selectedStatuses)")->param('selectedStatuses', $selectedStatuses); } if ($schedule->recipient_listing && $schedule->limit_to) { switch ($schedule->recipient) { case 'soft_credit_type': $query['casContactIdField'] = 'soft.contact_id'; $query->join('soft', 'INNER JOIN civicrm_contribution_soft soft ON soft.contribution_id = e.id')->where("soft.soft_credit_type_id IN (#recipList)")->param('recipList', \CRM_Utils_Array::explodePadded($schedule->recipient_listing)); break; } } return $query; }
/** * @param \Civi\ActionSchedule\MappingInterface $mapping * @param \CRM_Core_DAO_ActionSchedule $actionSchedule * @return string */ protected static function prepareMailingQuery($mapping, $actionSchedule) { $select = CRM_Utils_SQL_Select::from('civicrm_action_log reminder')->select("reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id AS entityID")->join('e', "!casMailingJoinType !casMappingEntity e ON !casEntityJoinExpr")->select("e.id as entityID, e.*")->where("reminder.action_schedule_id = #casActionScheduleId")->where("reminder.action_date_time IS NULL")->param(array('casActionScheduleId' => $actionSchedule->id, 'casMailingJoinType' => $actionSchedule->limit_to == 0 ? 'LEFT JOIN' : 'INNER JOIN', 'casMappingId' => $mapping->getId(), 'casMappingEntity' => $mapping->getEntity(), 'casEntityJoinExpr' => 'e.id = reminder.entity_id')); if ($actionSchedule->limit_to == 0) { $select->where("e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact'"); } \Civi\Core\Container::singleton()->get('dispatcher')->dispatch(\Civi\ActionSchedule\Events::MAILING_QUERY, new \Civi\ActionSchedule\Event\MailingQueryEvent($actionSchedule, $mapping, $select)); return $select->toSQL(); }
/** * Get a list of Projects matching the params. * * This function is invoked from within the web form layer and also from the * API layer. Special params include: * <ol> * <li>project_contacts (@see CRM_Volunteer_BAO_Project::create() and * CRM_Volunteer_BAO_Project::buildContactWhere)</li> * <li>proximity (@see CRM_Volunteer_BAO_Project::buildProximityWhere)</li> * </ol> * * NOTE: This method does not return data related to the special params * outlined above; however, these parameters can be used to filter the list * of Projects that is returned. * * @param array $params * @return array of CRM_Volunteer_BAO_Project objects */ public static function retrieve(array $params) { $result = array(); $query = CRM_Utils_SQL_Select::from('`civicrm_volunteer_project` vp')->select('DISTINCT vp.*'); if (!empty($params['project_contacts'])) { $contactJoin = self::buildContactJoin($params['project_contacts']); if ($contactJoin) { $query->join('vpc', $contactJoin); } } if (!empty($params['proximity'])) { $query->join('loc', 'INNER JOIN `civicrm_loc_block` loc ON loc.id = vp.loc_block_id')->join('civicrm_address', 'INNER JOIN `civicrm_address` ON civicrm_address.id = loc.address_id')->where(self::buildProximityWhere($params['proximity'])); } // This step is here to support both naming conventions for specifying params // (e.g., volunteer_project_id and id) while normalizing how we access them // (e.g., $project->id) $project = new CRM_Volunteer_BAO_Project(); $project->copyValues($params); foreach ($project->fields() as $field) { $fieldName = $field['name']; if (!empty($project->{$fieldName})) { $query->where('!column = @value', array('column' => $fieldName, 'value' => $project->{$fieldName})); } } $dao = self::executeQuery($query->toSQL()); while ($dao->fetch()) { $fetchedProject = new CRM_Volunteer_BAO_Project(); $fetchedProject->copyValues(clone $dao); $result[(int) $dao->id] = $fetchedProject; } $dao->free(); return $result; }
/** * Attachment find helper. * * @param array $params * @param int|null $id the user-supplied ID of the attachment record * @param array $file * The user-supplied vales for the file (mime_type, description, upload_date). * @param array $entityFile * The user-supplied values of the entity-file (entity_table, entity_id). * @param bool $isTrusted * * @return CRM_Core_DAO * @throws API_Exception */ function __civicrm_api3_attachment_find($params, $id, $file, $entityFile, $isTrusted) { foreach (array('name', 'content', 'path', 'url') as $unsupportedFilter) { if (!empty($params[$unsupportedFilter])) { throw new API_Exception("Get by {$unsupportedFilter} is not currently supported"); } } $select = CRM_Utils_SQL_Select::from('civicrm_file cf')->join('cef', 'INNER JOIN civicrm_entity_file cef ON cf.id = cef.file_id')->select(array('cf.id', 'cf.uri', 'cf.mime_type', 'cf.description', 'cf.upload_date', 'cef.entity_table', 'cef.entity_id')); if ($id) { $select->where('cf.id = #id', array('#id' => $id)); } // Recall: $file is filtered by parse_params. foreach ($file as $key => $value) { $select->where('cf.!field = @value', array('!field' => $key, '@value' => $value)); } // Recall: $entityFile is filtered by parse_params. foreach ($entityFile as $key => $value) { $select->where('cef.!field = @value', array('!field' => $key, '@value' => $value)); } if (!$isTrusted) { // FIXME ACLs: Add any JOIN or WHERE clauses needed to enforce access-controls for the target entity. // // The target entity is identified by "cef.entity_table" (aka $entityFile['entity_table']) and "cef.entity_id". // // As a simplification, we *require* the "get" actions to filter on a single "entity_table" which should // avoid the complexity of matching ACL's against multiple entity types. } $dao = CRM_Core_DAO::executeQuery($select->toSQL()); return $dao; }
public function testArrayGet() { $select = CRM_Utils_SQL_Select::from("foo")->param('hello', 'world'); $this->assertEquals('world', $select['hello']); }
public function testInsertInto_WithDupes() { $select = CRM_Utils_SQL_Select::from('foo')->insertInto('bar', array('first', 'second', 'third', 'fourth'))->select('fid')->select('1')->select('fid')->select('1')->where('!field = #value', array('field' => 'zoo', 'value' => 3))->where('!field = #value', array('field' => 'aviary', 'value' => 3))->where('!field = #value', array('field' => 'zoo', 'value' => 3))->groupBy('!colName', array('colName' => 'noodle'))->groupBy('!colName', array('colName' => 'sauce'))->groupBy('!colName', array('colName' => 'noodle')); $this->assertLike('INSERT INTO bar (first, second, third, fourth) SELECT fid, 1, fid, 1 FROM foo WHERE (zoo = 3) AND (aviary = 3) GROUP BY noodle, sauce', $select->toSQL()); }
/** * Generate a query to locate recipients who match the given * schedule. * * @param \CRM_Core_DAO_ActionSchedule $schedule * The schedule as configured by the administrator. * @param string $phase * See, e.g., RecipientBuilder::PHASE_RELATION_FIRST. * @param array $defaultParams * * @return \CRM_Utils_SQL_Select * @see RecipientBuilder */ public function createQuery($schedule, $phase, $defaultParams) { $selectedValues = (array) \CRM_Utils_Array::explodePadded($schedule->entity_value); $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); $query = \CRM_Utils_SQL_Select::from("{$this->entity} e")->param($defaultParams); $query['casAddlCheckFrom'] = 'civicrm_event r'; $query['casContactIdField'] = 'e.contact_id'; $query['casEntityIdField'] = 'e.id'; $query['casContactTableAlias'] = NULL; $query['casDateField'] = str_replace('event_', 'r.', $schedule->start_action_date); $query->join('r', 'INNER JOIN civicrm_event r ON e.event_id = r.id'); if ($schedule->recipient_listing && $schedule->limit_to) { switch ($schedule->recipient) { case 'participant_role': $query->where("e.role_id IN (#recipList)")->param('recipList', \CRM_Utils_Array::explodePadded($schedule->recipient_listing)); break; default: break; } } // build where clause if (!empty($selectedValues)) { $valueField = $this->id == \CRM_Event_ActionMapping::EVENT_TYPE_MAPPING_ID ? 'event_type_id' : 'id'; $query->where("r.{$valueField} IN (@selectedValues)")->param('selectedValues', $selectedValues); } else { $query->where($this->id == \CRM_Event_ActionMapping::EVENT_TYPE_MAPPING_ID ? "r.event_type_id IS NULL" : "r.id IS NULL"); } $query->where('r.is_active = 1'); $query->where('r.is_template = 0'); // participant status criteria not to be implemented for additional recipients // ... why not? if (!empty($selectedStatuses)) { switch ($phase) { case RecipientBuilder::PHASE_RELATION_FIRST: case RecipientBuilder::PHASE_RELATION_REPEAT: $query->where("e.status_id IN (#selectedStatuses)")->param('selectedStatuses', $selectedStatuses); break; } } return $query; }
/** * Send test mailing. * * @param array $params * * @return array * @throws \API_Exception * @throws \CiviCRM_API3_Exception */ function civicrm_api3_mailing_send_test($params) { if (!array_key_exists('test_group', $params) && !array_key_exists('test_email', $params)) { throw new API_Exception("Mandatory key(s) missing from params array: test_group and/or test_email field are required"); } civicrm_api3_verify_mandatory($params, 'CRM_Mailing_DAO_MailingJob', array('mailing_id'), FALSE); $testEmailParams = _civicrm_api3_generic_replace_base_params($params); $testEmailParams['is_test'] = 1; $job = civicrm_api3('MailingJob', 'create', $testEmailParams); $testEmailParams['job_id'] = $job['id']; $testEmailParams['emails'] = explode(',', $testEmailParams['test_email']); if (!empty($params['test_email'])) { $query = CRM_Utils_SQL_Select::from('civicrm_email e')->select(array('e.id', 'e.contact_id', 'e.email'))->join('c', 'INNER JOIN civicrm_contact c ON e.contact_id = c.id')->where('e.email IN (@emails)', array('@emails' => $testEmailParams['emails']))->where('e.on_hold = 0')->where('c.is_opt_out = 0')->where('c.do_not_email = 0')->where('c.is_deceased = 0')->where('c.is_deleted = 0')->groupBy('e.id')->orderBy(array('e.is_bulkmail DESC', 'e.is_primary DESC'))->toSQL(); $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 ($testEmailParams['emails'] as $key => $email) { $email = trim($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. $contact = civicrm_api3('Contact', 'create', array('contact_type' => 'Individual', 'email' => $email, 'api.Email.get' => array('return' => 'id'))); $contactId = $contact['id']; $emailId = $contact['values'][$contactId]['api.Email.get']['id']; } civicrm_api3('MailingEventQueue', 'create', array('job_id' => $job['id'], 'email_id' => $emailId, 'contact_id' => $contactId)); } } $isComplete = FALSE; $config = CRM_Core_Config::singleton(); $mailerJobSize = Civi::settings()->get('mailerJobSize'); while (!$isComplete) { // Q: In CRM_Mailing_BAO_Mailing::processQueue(), the three runJobs*() // functions are all called. Why does Mailing.send_test only call one? // CRM_Mailing_BAO_MailingJob::runJobs_pre($mailerJobSize, NULL); $isComplete = CRM_Mailing_BAO_MailingJob::runJobs($testEmailParams); // CRM_Mailing_BAO_MailingJob::runJobs_post(NULL); } //return delivered mail info $mailDelivered = CRM_Mailing_Event_BAO_Delivered::getRows($params['mailing_id'], $job['id'], TRUE, NULL, NULL, NULL, TRUE); return civicrm_api3_create_success($mailDelivered); }
/** * Get function for query object api. * * This is a simple get function, but it should be usable for any kind of * entity. I created it to work around CRM-16036. * * @param string $dao_name * Name of DAO * @param array $params * As passed into api get function. * @param bool $isFillUniqueFields * Do we need to ensure unique fields continue to be populated for this api? (backward compatibility). * @param CRM_Utils_SQL_Select|NULL $sqlFragment * * @return array */ function _civicrm_api3_get_using_utils_sql($dao_name, $params, $isFillUniqueFields, $sqlFragment) { $dao = new $dao_name(); $entity = _civicrm_api_get_entity_name_from_dao($dao); $custom_fields = _civicrm_api3_custom_fields_for_entity($entity); $options = _civicrm_api3_get_options_from_params($params); // Unset $params['options'] if they are api parameters (not options as a fieldname). if (!empty($params['options']) && is_array($params['options']) && array_intersect(array_keys($params['options']), array_keys($options))) { unset($params['options']); } $entity_field_names = _civicrm_api3_field_names(_civicrm_api3_build_fields_array($dao)); $custom_field_names = array(); $uniqueAliases = array(); $getFieldsResult = civicrm_api3($entity, 'getfields', array('action' => 'get')); $getFieldsResult = $getFieldsResult['values']; foreach ($getFieldsResult as $getFieldKey => $getFieldSpec) { $uniqueAliases[$getFieldKey] = $getFieldSpec['name']; $uniqueAliases[$getFieldSpec['name']] = $getFieldSpec['name']; } // $select_fields maps column names to the field names of the result // values. $select_fields = array(); // array with elements array('column', 'operator', 'value'); $where_clauses = array(); // Tables we need to join with to retrieve the custom values. $custom_value_tables = array(); // ID's of custom fields that refer to a contact. $contact_reference_field_ids = array(); // populate $select_fields $return_all_fields = empty($options['return']) || !is_array($options['return']); $return = $return_all_fields ? array_fill_keys($entity_field_names, 1) : $options['return']; // default fields foreach (array_keys($return) as $field_name) { if (!empty($uniqueAliases[$field_name]) && CRM_Core_BAO_CustomField::getKeyID($field_name) == FALSE) { // 'a.' is an alias for the entity table. $select_fields["a.{$uniqueAliases[$field_name]}"] = $uniqueAliases[$field_name]; } } // custom fields foreach ($custom_fields as $cf_id => $custom_field) { $field_name = "custom_{$cf_id}"; $custom_field_names[] = $field_name; if ($return_all_fields || !empty($options['return'][$field_name]) || !empty($options['return']['custom'])) { $table_name = $custom_field["table_name"]; $column_name = $custom_field["column_name"]; // remember that we will need to join the correct table. if (!in_array($table_name, $custom_value_tables)) { $custom_value_tables[] = $table_name; } if ($custom_field["data_type"] != "ContactReference") { // 'ordinary' custom field. We will select the value as custom_XX. $select_fields["{$table_name}.{$column_name}"] = $field_name; } else { // contact reference custom field. The ID will be stored in // custom_XX_id. custom_XX will contain the sort name of the // contact. $contact_reference_field_ids[] = $cf_id; $select_fields["{$table_name}.{$column_name}"] = $field_name . "_id"; // We will call the contact table for the join c_XX. $select_fields["c_{$cf_id}.sort_name"] = $field_name; } } } if (!in_array("a.id", $select_fields)) { // Always select the ID. $select_fields["a.id"] = "id"; } // build query $query = CRM_Utils_SQL_Select::from($dao->tableName() . " a"); // populate $where_clauses foreach ($params as $key => $value) { $type = 'String'; $table_name = NULL; $column_name = NULL; if (substr($key, 0, 7) == 'filter.') { // Legacy support for old filter syntax per the test contract. // (Convert the style to the later one & then deal with them). $filterArray = explode('.', $key); $value = array($filterArray[1] => $value); $key = 'filters'; } // Legacy support for 'filter's construct. if ($key == 'filters') { foreach ($value as $filterKey => $filterValue) { if (substr($filterKey, -4, 4) == 'high') { $key = substr($filterKey, 0, -5); $value = array('<=' => $filterValue); } if (substr($filterKey, -3, 3) == 'low') { $key = substr($filterKey, 0, -4); $value = array('>=' => $filterValue); } if ($filterKey == 'is_current' || $filterKey == 'isCurrent') { // Is current is almost worth creating as a 'sql filter' in the DAO function since several entities have the // concept. $todayStart = date('Ymd000000', strtotime('now')); $todayEnd = date('Ymd235959', strtotime('now')); $query->where(array("(a.start_date <= '{$todayStart}' OR a.start_date IS NULL) AND (a.end_date >= '{$todayEnd}' OR\n a.end_date IS NULL)\n AND a.is_active = 1\n ")); } } } if (array_key_exists($key, $getFieldsResult)) { $type = $getFieldsResult[$key]['type']; $key = $getFieldsResult[$key]['name']; } if ($key == _civicrm_api_get_entity_name_from_camel($entity) . '_id') { // The test contract enforces support of (eg) mailing_group_id if the entity is MailingGroup. $type = 'int'; $key = 'id'; } if (in_array($key, $entity_field_names)) { $table_name = 'a'; $column_name = $key; } elseif (($cf_id = CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) { $table_name = $custom_fields[$cf_id]["table_name"]; $column_name = $custom_fields[$cf_id]["column_name"]; if (!in_array($table_name, $custom_value_tables)) { $custom_value_tables[] = $table_name; } } // I don't know why I had to specifically exclude 0 as a key - wouldn't the others have caught it? // We normally silently ignore null values passed in - if people want IS_NULL they can use acceptedSqlOperator syntax. if (!$table_name || empty($key) || is_null($value)) { // No valid filter field. This might be a chained call or something. // Just ignore this for the $where_clause. continue; } if (!is_array($value)) { $query->where(array("{$table_name}.{$column_name} = @value"), array("@value" => $value)); } else { // We expect only one element in the array, of the form // "operator" => "rhs". $operator = CRM_Utils_Array::first(array_keys($value)); if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) { $query->where(array("{$table_name}.{$column_name} = @value"), array("@value" => $value)); } else { $query->where(CRM_Core_DAO::createSQLFilter("{$table_name}.{$column_name}", $value, $type)); } } } $i = 0; if (!$options['is_count']) { foreach ($select_fields as $column => $alias) { ++$i; $query = $query->select("!column_{$i} as !alias_{$i}", array("!column_{$i}" => $column, "!alias_{$i}" => $alias)); } } else { $query->select("count(*) as c"); } // join with custom value tables foreach ($custom_value_tables as $table_name) { ++$i; $query = $query->join("!table_name_{$i}", "LEFT OUTER JOIN !table_name_{$i} ON !table_name_{$i}.entity_id = a.id", array("!table_name_{$i}" => $table_name)); } // join with contact for contact reference fields foreach ($contact_reference_field_ids as $field_id) { ++$i; $query = $query->join("!contact_table_name{$i}", "LEFT OUTER JOIN civicrm_contact !contact_table_name_{$i} ON !contact_table_name_{$i}.id = !values_table_name_{$i}.!column_name_{$i}", array("!contact_table_name_{$i}" => "c_{$field_id}", "!values_table_name_{$i}" => $custom_fields[$field_id]["table_name"], "!column_name_{$i}" => $custom_fields[$field_id]["column_name"])); } foreach ($where_clauses as $clause) { ++$i; if (substr($clause[1], -4) == "NULL") { $query->where("!columnName_{$i} !nullThing_{$i}", array("!columnName_{$i}" => $clause[0], "!nullThing_{$i}" => $clause[1])); } else { $query->where("!columnName_{$i} !operator_{$i} @value_{$i}", array("!columnName_{$i}" => $clause[0], "!operator_{$i}" => $clause[1], "@value_{$i}" => $clause[2])); } } if (!empty($sqlFragment)) { $query->merge($sqlFragment); } // order by if (!empty($options['sort'])) { $sort_fields = array(); foreach (explode(',', $options['sort']) as $sort_option) { $words = preg_split("/[\\s]+/", $sort_option); if (count($words) > 0 && in_array($words[0], array_values($select_fields))) { $tmp = $words[0]; if (!empty($words[1]) && strtoupper($words[1]) == 'DESC') { $tmp .= " DESC"; } $sort_fields[] = $tmp; } } if (count($sort_fields) > 0) { $query->orderBy(implode(",", $sort_fields)); } } // limit if (!empty($options['limit']) || !empty($options['offset'])) { $query->limit($options['limit'], $options['offset']); } $result_entities = array(); $result_dao = CRM_Core_DAO::executeQuery($query->toSQL()); while ($result_dao->fetch()) { if ($options['is_count']) { $result_dao->free(); return (int) $result_dao->c; } $result_entities[$result_dao->id] = array(); foreach ($select_fields as $column => $alias) { if (property_exists($result_dao, $alias) && $result_dao->{$alias} != NULL) { $result_entities[$result_dao->id][$alias] = $result_dao->{$alias}; } // Backward compatibility on fields names. if ($isFillUniqueFields && !empty($getFieldsResult['values'][$column]['uniqueName'])) { $result_entities[$result_dao->id][$getFieldsResult['values'][$column]['uniqueName']] = $result_dao->{$alias}; } foreach ($getFieldsResult as $returnName => $spec) { if (empty($result_entities[$result_dao->id][$returnName]) && !empty($result_entities[$result_dao->id][$spec['name']])) { $result_entities[$result_dao->id][$returnName] = $result_entities[$result_dao->id][$spec['name']]; } } } } $result_dao->free(); return $result_entities; }
/** * Generate a query to locate recipients who match the given * schedule. * * @param \CRM_Core_DAO_ActionSchedule $schedule * The schedule as configured by the administrator. * @param string $phase * See, e.g., RecipientBuilder::PHASE_RELATION_FIRST. * @param array $defaultParams * * @return \CRM_Utils_SQL_Select * @throws \CRM_Core_Exception * @see RecipientBuilder */ public function createQuery($schedule, $phase, $defaultParams) { $selectedValues = (array) \CRM_Utils_Array::explodePadded($schedule->entity_value); $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); // FIXME: This assumes that $values only has one field, but UI shows multiselect. // Properly supporting multiselect would require total rewrite of this function. if (count($selectedValues) != 1 || !isset($selectedValues[0])) { throw new \CRM_Core_Exception("Error: Scheduled reminders may only have one contact field."); } elseif (in_array($selectedValues[0], $this->contactDateFields)) { $dateDBField = $selectedValues[0]; $query = \CRM_Utils_SQL_Select::from("{$this->entity} e")->param($defaultParams); $query->param(array('casAddlCheckFrom' => 'civicrm_contact e', 'casContactIdField' => 'e.id', 'casEntityIdField' => 'e.id', 'casContactTableAlias' => 'e')); $query->where('e.is_deleted = 0 AND e.is_deceased = 0'); } else { //custom field $customFieldParams = array('id' => substr($selectedValues[0], 7)); $customGroup = $customField = array(); \CRM_Core_BAO_CustomField::retrieve($customFieldParams, $customField); $dateDBField = $customField['column_name']; $customGroupParams = array('id' => $customField['custom_group_id'], $customGroup); \CRM_Core_BAO_CustomGroup::retrieve($customGroupParams, $customGroup); $query = \CRM_Utils_SQL_Select::from("{$customGroup['table_name']} e")->param($defaultParams); $query->param(array('casAddlCheckFrom' => "{$customGroup['table_name']} e", 'casContactIdField' => 'e.entity_id', 'casEntityIdField' => 'e.id', 'casContactTableAlias' => NULL)); $query->where('1'); // possible to have no "where" in this case } $query['casDateField'] = 'e.' . $dateDBField; if (in_array(2, $selectedStatuses)) { $query['casAnniversaryMode'] = 1; $query['casDateField'] = 'DATE_ADD(' . $query['casDateField'] . ', INTERVAL ROUND(DATEDIFF(DATE(' . $query['casNow'] . '), ' . $query['casDateField'] . ') / 365) YEAR)'; } return $query; }
/** * @return \CRM_Utils_SQL_Select */ protected function createQuery() { $select = \CRM_Utils_SQL_Select::from('civicrm_setting')->select('id, name, value, domain_id, contact_id, is_domain, component_id, created_date, created_id')->where('domain_id = #id', array('id' => $this->domainId)); if ($this->contactId === NULL) { $select->where('is_domain = 1'); } else { $select->where('contact_id = #id', array('id' => $this->contactId)); $select->where('is_domain = 0'); } return $select; }
/** * @param string $entity * @param bool $checkPermissions */ public function __construct($entity, $checkPermissions) { $this->entity = $entity; require_once 'api/v3/utils.php'; $baoName = _civicrm_api3_get_BAO($entity); $bao = new $baoName(); $this->entityFieldNames = _civicrm_api3_field_names(_civicrm_api3_build_fields_array($bao)); $this->apiFieldSpec = $this->getFields(); $this->query = \CRM_Utils_SQL_Select::from($bao->tableName() . ' ' . self::MAIN_TABLE_ALIAS); $bao->free(); // Add ACLs first to avoid redundant subclauses $this->checkPermissions = $checkPermissions; $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName)); }