/** * Test the function that extracts the arrays used to structure the output. * * The keys in the output fields array should by matched by field aliases in the sql query (with * exceptions of course - currently country is one - although maybe a future refactor can change that!). * * We are trying to move towards simpler processing in the per row iteration as that may be * repeated 100,000 times and in general we should simply be able to match the query fields to * our expected rows & do a little pseudoconstant mapping. */ public function testGetExportStructureArrays() { // This is how return properties are formatted internally within the function for passing to the BAO query. $returnProperties = array('first_name' => 1, 'last_name' => 1, 'receive_date' => 1, 'contribution_source' => 1, 'location' => array('Home' => array('street_address' => 1, 'city' => 1, 'country' => 1, 'email' => 1, 'im-1' => 1, 'im_provider' => 1, 'phone-1' => 1)), 'phone' => 1, 'trxn_id' => 1, 'contribution_id' => 1); $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id'); $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id'); $contactRelationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, NULL, NULL, NULL, TRUE, 'name', FALSE); $query = new CRM_Contact_BAO_Query(array(), $returnProperties, NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_CONTRIBUTE, FALSE, TRUE, TRUE, NULL, 'AND'); list($select) = $query->query(); $pattern = '/as `?([^`,]*)/'; $queryFieldAliases = array(); preg_match_all($pattern, $select, $queryFieldAliases, PREG_PATTERN_ORDER); list($outputFields) = CRM_Export_BAO_Export::getExportStructureArrays($returnProperties, $query, $phoneTypes, $imProviders, $contactRelationshipTypes, '', array()); foreach (array_keys($outputFields) as $fieldAlias) { if ($fieldAlias == 'Home-country') { $this->assertTrue(in_array($fieldAlias . '_id', $queryFieldAliases[1]), 'Country is subject to some funky translate so we make sure country id is present'); } else { $this->assertTrue(in_array($fieldAlias, $queryFieldAliases[1]), 'looking for field ' . $fieldAlias . ' in generaly the alias fields need to match the outputfields'); } } }
/** * process the form after the input has been submitted and validated * * @access public * @return void */ public function postProcess() { $params = $this->controller->exportValues($this->_name); $config = CRM_Core_Config::singleton(); require_once 'CRM/Event/BAO/Query.php'; require_once 'CRM/Contact/BAO/Query.php'; $returnProperties =& CRM_Event_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_EVENT); $additionalFields = array('first_name', 'last_name', 'middle_name', 'current_employer'); foreach ($additionalFields as $field) { $returnProperties[$field] = 1; } if ($this->_single) { $queryParams = null; } else { $queryParams = $this->get('queryParams'); } $query = new CRM_Contact_BAO_Query($queryParams, $returnProperties, null, false, false, CRM_Contact_BAO_Query::MODE_EVENT); list($select, $from, $where) = $query->query(); if (empty($where)) { $where = "WHERE {$this->_componentClause}"; } else { $where .= " AND {$this->_componentClause}"; } $queryString = "{$select} {$from} {$where}"; $dao = CRM_Core_DAO::executeQuery($queryString); $rows = array(); while ($dao->fetch()) { $rows[$dao->participant_id] = array(); foreach ($returnProperties as $key => $dontCare) { $rows[$dao->participant_id][$key] = isset($dao->{$key}) ? $dao->{$key} : null; } } // get the class name from the participantListingID require_once 'CRM/Core/OptionGroup.php'; $className = CRM_Core_OptionGroup::getValue('event_badge', $params['badge_id'], 'value', 'Integer', 'name'); $classFile = str_replace('_', DIRECTORY_SEPARATOR, $className) . '.php'; $error = (include_once $classFile); if ($error == false) { CRM_Core_Error::fatal('Event Badge code file: ' . $classFile . ' does not exist. Please verify your custom event badge settings in CiviCRM administrative panel.'); } eval("\$eventBadgeClass = new {$className}( );"); $eventBadgeClass->run($rows); }
/** * These are stub comments as this function needs more explanation - particularly in terms of how it * relates to $this->searchQuery and why it replicates rather than calles $this->searchQuery. * * This function was originally written as a wrapper for the api query but is called from multiple places * in the core code directly so the name is misleading. This function does not use the searchQuery function * but it is unclear as to whehter that is historical or there is a reason * CRM-11290 led to the permissioning action being extracted from searchQuery & shared with this function * * @param array $params * @param array $returnProperties * @param null $fields * @param string $sort * @param int $offset * @param int $row_count * @param bool $smartGroupCache * ?? update smart group cache?. * @param bool $count * Return count obnly. * @param bool $skipPermissions * Should permissions be ignored or should the logged in user's permissions be applied. * * * @return array */ public static function apiQuery($params = NULL, $returnProperties = NULL, $fields = NULL, $sort = NULL, $offset = 0, $row_count = 25, $smartGroupCache = TRUE, $count = FALSE, $skipPermissions = TRUE) { $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL, TRUE, FALSE, 1, $skipPermissions, TRUE, $smartGroupCache); //this should add a check for view deleted if permissions are enabled if ($skipPermissions) { $query->_skipDeleteClause = TRUE; } $query->generatePermissionClause(FALSE, $count); // note : this modifies _fromClause and _simpleFromClause $query->includePseudoFieldsJoin($sort); list($select, $from, $where, $having) = $query->query($count); $options = $query->_options; if (!empty($query->_permissionWhereClause)) { if (empty($where)) { $where = "WHERE {$query->_permissionWhereClause}"; } else { $where = "{$where} AND {$query->_permissionWhereClause}"; } } $sql = "{$select} {$from} {$where} {$having}"; // add group by if ($query->_useGroupBy) { $sql .= ' GROUP BY contact_a.id'; } if (!empty($sort)) { $sort = CRM_Utils_Type::escape($sort, 'String'); $sql .= " ORDER BY {$sort} "; } if ($row_count > 0 && $offset >= 0) { $offset = CRM_Utils_Type::escape($offset, 'Int'); $rowCount = CRM_Utils_Type::escape($row_count, 'Int'); $sql .= " LIMIT {$offset}, {$row_count} "; } $dao = CRM_Core_DAO::executeQuery($sql); $values = array(); while ($dao->fetch()) { if ($count) { $noRows = $dao->rowCount; $dao->free(); return array($noRows, NULL); } $val = $query->store($dao); $convertedVals = $query->convertToPseudoNames($dao, TRUE); if (!empty($convertedVals)) { $val = array_replace_recursive($val, $convertedVals); } $values[$dao->contact_id] = $val; } $dao->free(); return array($values, $options); }
/** * wrapper for a api search query * * @param array $params * @param array $returnProperties * @param string $sort * @param int $offset * @param int $row_count * * @return void * @access public */ function apiQuery($params = null, $returnProperties = null, $options = null, $sort = null, $offset = 0, $row_count = 25) { $query = new CRM_Contact_BAO_Query($params, $returnProperties, null); list($select, $from, $where) = $query->query(); $options = $query->_options; $sql = "{$select} {$from} {$where}"; if (!empty($sort)) { $sql .= " ORDER BY {$sort} "; } if ($row_count > 0 && $offset >= 0) { $sql .= " LIMIT {$offset}, {$row_count} "; } $dao =& CRM_Core_DAO::executeQuery($sql); $values = array(); while ($dao->fetch()) { $values[$dao->contact_id] = $query->store($dao); } return array($values, $options); }
/** * Build badges parameters before actually creating badges. * * @param array $params * Associated array of submitted values. * @param CRM_Core_Form $form * * @return void */ public static function buildBadges(&$params, &$form) { // get name badge layout info $layoutInfo = CRM_Badge_BAO_Layout::buildLayout($params); // split/get actual field names from token and individual contact image URLs $returnProperties = array(); if (!empty($layoutInfo['data']['token'])) { foreach ($layoutInfo['data']['token'] as $index => $value) { $element = ''; if ($value) { $token = CRM_Utils_Token::getTokens($value); if (key($token) == 'contact') { $element = $token['contact'][0]; } elseif (key($token) == 'event') { $element = $token['event'][0]; //FIX ME - we need to standardize event token names if (substr($element, 0, 6) != 'event_') { $element = 'event_' . $element; } } elseif (key($token) == 'participant') { $element = $token['participant'][0]; } // build returnproperties for query $returnProperties[$element] = 1; } // add actual field name to row element $layoutInfo['data']['rowElements'][$index] = $element; } } // add additional required fields for query execution $additionalFields = array('participant_register_date', 'participant_id', 'event_id', 'contact_id', 'image_URL'); foreach ($additionalFields as $field) { $returnProperties[$field] = 1; } if ($form->_single) { $queryParams = NULL; } else { $queryParams = $form->get('queryParams'); } $query = new CRM_Contact_BAO_Query($queryParams, $returnProperties, NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_EVENT); list($select, $from, $where, $having) = $query->query(); if (empty($where)) { $where = "WHERE {$form->_componentClause}"; } else { $where .= " AND {$form->_componentClause}"; } $sortOrder = NULL; if ($form->get(CRM_Utils_Sort::SORT_ORDER)) { $sortOrder = $form->get(CRM_Utils_Sort::SORT_ORDER); if (!empty($sortOrder)) { $sortOrder = " ORDER BY {$sortOrder}"; } } $queryString = "{$select} {$from} {$where} {$having} {$sortOrder}"; $dao = CRM_Core_DAO::executeQuery($queryString); $rows = array(); while ($dao->fetch()) { $query->convertToPseudoNames($dao); $rows[$dao->participant_id] = array(); foreach ($returnProperties as $key => $dontCare) { $value = isset($dao->{$key}) ? $dao->{$key} : NULL; // Format custom fields if (strstr($key, 'custom_') && isset($value)) { $value = CRM_Core_BAO_CustomField::getDisplayValue($value, substr($key, 7), $query->_options, $dao->contact_id); } $rows[$dao->participant_id][$key] = $value; } } $eventBadgeClass = new CRM_Badge_BAO_Badge(); $eventBadgeClass->createLabels($rows, $layoutInfo); }
function civicrm_api3_contact_getstat ($params) { // mostly copy pasted from contact_get and the functions called by it $options = array(); _civicrm_api3_contact_get_supportanomalies($params, $options); $contacts = _civicrm_api3_get_using_query_object('contact', $params, $options); $options = _civicrm_api3_get_options_from_params($params, TRUE); $inputParams = CRM_Utils_Array::value('input_params', $options, array()); $returnProperties = CRM_Utils_Array::value('return', $options, array()); if(!empty($params['check_permissions'])){ // we will filter query object against getfields $fields = civicrm_api("contact", 'getfields', array('version' => 3, 'action' => 'get')); // we need to add this in as earlier in this function 'id' was unset in favour of $entity_id $fields['values'][$entity . '_id'] = array(); $varsToFilter = array('returnProperties', 'inputParams'); foreach ($varsToFilter as $varToFilter){ if(!is_array($$varToFilter)){ continue; } $$varToFilter = array_intersect_key($$varToFilter, $fields['values']); } } // $options = array_merge($options,$additional_options); $sort = CRM_Utils_Array::value('sort', $options, NULL); $returnSQL = CRM_Utils_Array::value('sql', $options, CRM_Utils_Array::value('options_sql', $options['input_params'])); $smartGroupCache = CRM_Utils_Array::value('smartGroupCache', $params); $newParams = CRM_Contact_BAO_Query::convertFormValues($inputParams); $skipPermissions = CRM_Utils_Array::value('check_permissions', $params)? 0 :1; $query = new CRM_Contact_BAO_Query( $params, $returnProperties, NULL, TRUE, FALSE, 1, $skipPermissions, TRUE, $smartGroupCache ); //this should add a check for view deleted if permissions are enabled if ($skipPermissions){ $query->_skipDeleteClause = TRUE; } $query->generatePermissionClause(FALSE, $count); list($select, $from, $where, $having) = $query->query($count); $options = $query->_options; if(!empty($query->_permissionWhereClause)){ if (empty($where)) { $where = "WHERE $query->_permissionWhereClause"; } else { $where = "$where AND $query->_permissionWhereClause"; } } $sql = "$select $from $where $having"; if (!empty($returnProperties)) { $extra = array(); $sql = "SELECT count(*) AS total,". substr ($sql, 34,10000); //replace select contact_id, by select count(*) $sql .= " GROUP BY ". implode (",",array_keys($returnProperties)) ; } else { $sql = "SELECT count(*) AS total $from $where $having"; $extra = array ("tip"=>"if you need to group by a field, use the return param, eg return=contact_type,gender", "warning"=> "use getcount, getstat without param might be blocked in the future"); if (!empty($sort)) { $sql .= " ORDER BY $sort "; } else { $sql .= " ORDER BY total DESC "; } } if ($returnSQL) { return array("is_error"=>1,"sql"=>$sql,"from"=>$from,"where"=>$where,"having"=>$having); } $dao = CRM_Core_DAO::executeQuery($sql); $values = array(); while ($dao->fetch()) { $values[] = $dao->toArray(); } return civicrm_api3_create_success($values, $params, "contact", "getstat", $dao,$extra); }
/** * Retrieve a set of pledges, given a set of input params * * @param array $params (reference ) input parameters. Use interogate for possible fields * * @return array (reference ) array of pledges, if error an array with an error id and error message * {@getfields pledge_get} * @example PledgeGet.php * @access public */ function civicrm_api3_pledge_get($params) { $options = _civicrm_api3_get_options_from_params($params, TRUE, 'pledge', 'get'); if (empty($options['return'])) { $options['return'] = CRM_Pledge_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_PLEDGE); } else { $options['return']['pledge_id'] = 1; } $newParams = CRM_Contact_BAO_Query::convertFormValues($options['input_params']); $query = new CRM_Contact_BAO_Query($newParams, $options['return'], NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_PLEDGE); list($select, $from, $where) = $query->query(); $sql = "{$select} {$from} {$where}"; if (!empty($options['sort'])) { $sql .= " ORDER BY " . $options['sort']; } $sql .= " LIMIT " . $options['offset'] . " , " . $options['limit']; $dao = CRM_Core_DAO::executeQuery($sql); $pledge = array(); while ($dao->fetch()) { $pledge[$dao->pledge_id] = $query->store($dao); } return civicrm_api3_create_success($pledge, $params, 'pledge', 'get', $dao); }
/** * Test set up to test calling the query object per GroupContactCache BAO usage. * * CRM-17254 ensure that if only the contact_id is required other fields should * not be appended. */ public function testGroupContactCacheAddSearch() { $returnProperties = array('contact_id'); $params = array(array('group', 'IN', array(1), 0, 0)); $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL, TRUE, FALSE, 1, TRUE, TRUE, FALSE); list($select) = $query->query(FALSE); $this->assertEquals('SELECT contact_a.id as contact_id', $select); }
/** * Retrieve a set of pledges, given a set of input params * * @param array $params (reference ) input parameters. Use interogate for possible fields * * @return array (reference ) array of pledges, if error an array with an error id and error message * @static void * @access public */ function &civicrm_pledge_get(&$params) { _civicrm_initialize(); if (!is_array($params)) { return civicrm_create_error('Input parameters is not an array'); } $inputParams = array(); $returnProperties = array(); $otherVars = array('sort', 'offset', 'rowCount'); $sort = NULL; $offset = 0; $rowCount = 25; foreach ($params as $n => $v) { if (substr($n, 0, 7) == 'return.') { $returnProperties[substr($n, 7)] = $v; } elseif (in_array($n, $otherVars)) { ${$n} = $v; } else { $inputParams[$n] = $v; } } // add is_test to the clause if not present if (!array_key_exists('pledge_test', $inputParams)) { $inputParams['pledge_test'] = 0; } require_once 'CRM/Pledge/BAO/Query.php'; require_once 'CRM/Contact/BAO/Query.php'; if (empty($returnProperties)) { $returnProperties = CRM_Pledge_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_PLEDGE); } else { $returnProperties['pledge_id'] = 1; } $newParams = CRM_Contact_BAO_Query::convertFormValues($inputParams); $query = new CRM_Contact_BAO_Query($newParams, $returnProperties, NULL); list($select, $from, $where) = $query->query(); $sql = "{$select} {$from} {$where}"; if (!empty($sort)) { $sql .= " ORDER BY {$sort} "; } $sql .= " LIMIT {$offset}, {$rowCount} "; $dao = CRM_Core_DAO::executeQuery($sql); $pledge = array(); while ($dao->fetch()) { if ($params['sequential']) { $pledge[] = $query->store($dao); } else { $pledge[$dao->pledge_id] = $query->store($dao); } } $dao->free(); return $pledge; }
/** * Test smart groups with non-numeric don't fail on equal queries. * * CRM-14720 */ public function testNonNumericEqualsPostal() { $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main'))); $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main'))); $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main'))); $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main'))); $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0)); CRM_Contact_BAO_Query::convertFormValues($params); $query = new CRM_Contact_BAO_Query($params, array('contact_id'), NULL, TRUE, FALSE, 1, TRUE, TRUE, FALSE); $sql = $query->query(FALSE); $this->assertEquals("WHERE ( civicrm_address.postal_code = 'eh10 4rb-889' ) AND (contact_a.is_deleted = 0)", $sql[2]); $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); $this->assertEquals(1, $result->N); }
/** * Retrieve a set of contributions, given a set of input params * * @param array $params (reference ) input parameters * @param array $returnProperties Which properties should be included in the * returned Contribution object. If NULL, the default * set of properties will be included. * * @return array (reference ) array of contributions, if error an array with an error id and error message * @static void * @access public * {@getfields Contribution_get} * @example ContributionGet.php */ function civicrm_api3_contribution_get($params) { $options = _civicrm_api3_get_options_from_params($params, TRUE, 'contribution', 'get'); $sort = CRM_Utils_Array::value('sort', $options, NULL); $offset = CRM_Utils_Array::value('offset', $options); $rowCount = CRM_Utils_Array::value('limit', $options); $smartGroupCache = CRM_Utils_Array::value('smartGroupCache', $params); $inputParams = CRM_Utils_Array::value('input_params', $options, array()); $returnProperties = CRM_Utils_Array::value('return', $options, NULL); if (empty($returnProperties)) { $returnProperties = CRM_Contribute_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_CONTRIBUTE); } $newParams = CRM_Contact_BAO_Query::convertFormValues($inputParams); $query = new CRM_Contact_BAO_Query($newParams, $returnProperties, NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_CONTRIBUTE); list($select, $from, $where, $having) = $query->query(); $sql = "{$select} {$from} {$where} {$having}"; if (!empty($sort)) { $sql .= " ORDER BY {$sort} "; } $sql .= " LIMIT {$offset}, {$rowCount} "; $dao = CRM_Core_DAO::executeQuery($sql); $contribution = array(); while ($dao->fetch()) { //CRM-8662 $contribution_details = $query->store($dao); $softContribution = CRM_Contribute_BAO_ContributionSoft::getSoftContribution($dao->contribution_id, TRUE); $contribution[$dao->contribution_id] = array_merge($contribution_details, $softContribution); if (isset($contribution[$dao->contribution_id]['financial_type_id'])) { $contribution[$dao->contribution_id]['financial_type_id'] = $contribution[$dao->contribution_id]['financial_type_id']; } // format soft credit for backward compatibility _civicrm_api3_format_soft_credit($contribution[$dao->contribution_id]); } return civicrm_api3_create_success($contribution, $params, 'contribution', 'get', $dao); }
/** * Retrieve a specific participant, given a set of input params * If more than one matching participant exists, return an error, unless * the client has requested to return the first found contact * * @param array $params (reference ) input parameters * * @return array (reference ) array of properties, if error an array with an error id and error message * {@getfields participant_get} * @access public */ function civicrm_api3_participant_get($params) { $options = _civicrm_api3_get_options_from_params($params, TRUE, 'participant', 'get'); $sort = CRM_Utils_Array::value('sort', $options, NULL); $offset = CRM_Utils_Array::value('offset', $options); $rowCount = CRM_Utils_Array::value('limit', $options); $smartGroupCache = CRM_Utils_Array::value('smartGroupCache', $params); $inputParams = CRM_Utils_Array::value('input_params', $options, array()); $returnProperties = CRM_Utils_Array::value('return', $options, NULL); if (empty($returnProperties)) { $returnProperties = CRM_Event_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_EVENT); } $newParams = CRM_Contact_BAO_Query::convertFormValues($inputParams); $query = new CRM_Contact_BAO_Query($newParams, $returnProperties, NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_EVENT); list($select, $from, $where, $having) = $query->query(); $sql = "{$select} {$from} {$where} {$having}"; if (!empty($sort)) { $sql .= " ORDER BY {$sort} "; } $sql .= " LIMIT {$offset}, {$rowCount} "; $dao = CRM_Core_DAO::executeQuery($sql); $participant = array(); while ($dao->fetch()) { $participant[$dao->participant_id] = $query->store($dao); _civicrm_api3_custom_data_get($participant[$dao->participant_id], 'Participant', $dao->participant_id, NULL); } return civicrm_api3_create_success($participant, $params, 'participant', 'get', $dao); }
/** * Function to get the list the export fields * * @param int $selectAll user preference while export * @param array $ids contact ids * @param array $params associated array of fields * @param string $order order by clause * @param array $fields associated array of fields * @param array $moreReturnProperties additional return fields * @param int $exportMode export mode * @param string $componentClause component clause * @param string $componentTable component table * @param bool $mergeSameAddress merge records if they have same address * @param bool $mergeSameHousehold merge records if they belong to the same household * * @static * @access public */ static function exportComponents($selectAll, $ids, $params, $order = null, $fields = null, $moreReturnProperties = null, $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT, $componentClause = null, $componentTable = null, $mergeSameAddress = false, $mergeSameHousehold = false) { $headerRows = $returnProperties = array(); $primary = $paymentFields = false; $origFields = $fields; $queryMode = null; $phoneTypes = CRM_Core_PseudoConstant::phoneType(); $imProviders = CRM_Core_PseudoConstant::IMProvider(); $contactRelationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(null, null, null, null, true, 'label', false); $queryMode = CRM_Contact_BAO_Query::MODE_CONTACTS; switch ($exportMode) { case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: $queryMode = CRM_Contact_BAO_Query::MODE_CONTRIBUTE; break; case CRM_Export_Form_Select::EVENT_EXPORT: $queryMode = CRM_Contact_BAO_Query::MODE_EVENT; break; case CRM_Export_Form_Select::MEMBER_EXPORT: $queryMode = CRM_Contact_BAO_Query::MODE_MEMBER; break; case CRM_Export_Form_Select::PLEDGE_EXPORT: $queryMode = CRM_Contact_BAO_Query::MODE_PLEDGE; break; case CRM_Export_Form_Select::CASE_EXPORT: $queryMode = CRM_Contact_BAO_Query::MODE_CASE; break; case CRM_Export_Form_Select::GRANT_EXPORT: $queryMode = CRM_Contact_BAO_Query::MODE_GRANT; break; case CRM_Export_Form_Select::ACTIVITY_EXPORT: $queryMode = CRM_Contact_BAO_Query::MODE_ACTIVITY; break; } require_once 'CRM/Core/BAO/CustomField.php'; if ($fields) { //construct return properties $locationTypes = CRM_Core_PseudoConstant::locationType(); $locationTypeFields = array('street_address', 'supplemental_address_1', 'supplemental_address_2', 'city', 'postal_code', 'postal_code_suffix', 'geo_code_1', 'geo_code_2', 'state_province', 'country', 'phone', 'email', 'im'); foreach ($fields as $key => $value) { $phoneTypeId = $imProviderId = null; $relationshipTypes = $fieldName = CRM_Utils_Array::value(1, $value); if (!$fieldName) { continue; } // get phoneType id and IM service provider id seperately if ($fieldName == 'phone') { $phoneTypeId = CRM_Utils_Array::value(3, $value); } else { if ($fieldName == 'im') { $imProviderId = CRM_Utils_Array::value(3, $value); } } if (array_key_exists($relationshipTypes, $contactRelationshipTypes)) { if (CRM_Utils_Array::value(2, $value)) { $relationField = CRM_Utils_Array::value(2, $value); if (trim(CRM_Utils_Array::value(3, $value))) { $relLocTypeId = CRM_Utils_Array::value(3, $value); } else { $relLocTypeId = 'Primary'; } if ($relationField == 'phone') { $relPhoneTypeId = CRM_Utils_Array::value(4, $value); } else { if ($relationField == 'im') { $relIMProviderId = CRM_Utils_Array::value(4, $value); } } } else { if (CRM_Utils_Array::value(4, $value)) { $relationField = CRM_Utils_Array::value(4, $value); $relLocTypeId = CRM_Utils_Array::value(5, $value); if ($relationField == 'phone') { $relPhoneTypeId = CRM_Utils_Array::value(6, $value); } else { if ($relationField == 'im') { $relIMProviderId = CRM_Utils_Array::value(6, $value); } } } } } $contactType = CRM_Utils_Array::value(0, $value); $locTypeId = CRM_Utils_Array::value(2, $value); $phoneTypeId = CRM_Utils_Array::value(3, $value); if ($relationField) { if (in_array($relationField, $locationTypeFields) && is_numeric($relLocTypeId)) { if ($relPhoneTypeId) { $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['phone-' . $relPhoneTypeId] = 1; } else { if ($relIMProviderId) { $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['im-' . $relIMProviderId] = 1; } else { $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]][$relationField] = 1; } } $relPhoneTypeId = $relIMProviderId = null; } else { $returnProperties[$relationshipTypes][$relationField] = 1; } } else { if (is_numeric($locTypeId)) { if ($phoneTypeId) { $returnProperties['location'][$locationTypes[$locTypeId]]['phone-' . $phoneTypeId] = 1; } else { if (isset($imProviderId)) { //build returnProperties for IM service provider $returnProperties['location'][$locationTypes[$locTypeId]]['im-' . $imProviderId] = 1; } else { $returnProperties['location'][$locationTypes[$locTypeId]][$fieldName] = 1; } } } else { //hack to fix component fields if ($fieldName == 'event_id') { $returnProperties['event_title'] = 1; } else { $returnProperties[$fieldName] = 1; } } } } // hack to add default returnproperty based on export mode if ($exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) { $returnProperties['contribution_id'] = 1; } else { if ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) { $returnProperties['participant_id'] = 1; if ($returnProperties['participant_role']) { unset($returnProperties['participant_role']); $returnProperties['participant_role_id'] = 1; } } else { if ($exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) { $returnProperties['membership_id'] = 1; } else { if ($exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) { $returnProperties['pledge_id'] = 1; } else { if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) { $returnProperties['case_id'] = 1; } else { if ($exportMode == CRM_Export_Form_Select::GRANT_EXPORT) { $returnProperties['grant_id'] = 1; } else { if ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { $returnProperties['activity_id'] = 1; } } } } } } } } else { $primary = true; $fields = CRM_Contact_BAO_Contact::exportableFields('All', true, true); foreach ($fields as $key => $var) { if ($key && substr($key, 0, 6) != 'custom') { //for CRM=952 $returnProperties[$key] = 1; } } if ($primary) { $returnProperties['location_type'] = 1; $returnProperties['im_provider'] = 1; $returnProperties['phone_type_id'] = 1; $returnProperties['provider_id'] = 1; $returnProperties['current_employer'] = 1; } $extraReturnProperties = array(); $paymentFields = false; switch ($queryMode) { case CRM_Contact_BAO_Query::MODE_EVENT: $paymentFields = true; $paymentTableId = "participant_id"; break; case CRM_Contact_BAO_Query::MODE_MEMBER: $paymentFields = true; $paymentTableId = "membership_id"; break; case CRM_Contact_BAO_Query::MODE_PLEDGE: require_once 'CRM/Pledge/BAO/Query.php'; $extraReturnProperties = CRM_Pledge_BAO_Query::extraReturnProperties($queryMode); $paymentFields = true; $paymentTableId = "pledge_payment_id"; break; case CRM_Contact_BAO_Query::MODE_CASE: require_once 'CRM/Case/BAO/Query.php'; $extraReturnProperties = CRM_Case_BAO_Query::extraReturnProperties($queryMode); break; } if ($queryMode != CRM_Contact_BAO_Query::MODE_CONTACTS) { $componentReturnProperties = CRM_Contact_BAO_Query::defaultReturnProperties($queryMode); $returnProperties = array_merge($returnProperties, $componentReturnProperties); if (!empty($extraReturnProperties)) { $returnProperties = array_merge($returnProperties, $extraReturnProperties); } // unset non exportable fields for components $nonExpoFields = array('groups', 'tags', 'notes', 'contribution_status_id', 'pledge_status_id', 'pledge_payment_status_id'); foreach ($nonExpoFields as $value) { unset($returnProperties[$value]); } } } if ($mergeSameAddress) { $drop = false; //make sure the addressee fields are selected //while using merge same address feature $returnProperties['addressee'] = 1; $returnProperties['street_name'] = 1; if (!CRM_Utils_Array::value('last_name', $returnProperties)) { $returnProperties['last_name'] = 1; $drop = 'last_name'; } $returnProperties['household_name'] = 1; $returnProperties['street_address'] = 1; } if ($moreReturnProperties) { // fix for CRM-7066 if (CRM_Utils_Array::value('group', $moreReturnProperties)) { unset($moreReturnProperties['group']); $moreReturnProperties['groups'] = 1; } $returnProperties = array_merge($returnProperties, $moreReturnProperties); } $query = new CRM_Contact_BAO_Query(0, $returnProperties, null, false, false, $queryMode); list($select, $from, $where) = $query->query(); if ($mergeSameHousehold == 1) { if (!$returnProperties['id']) { $returnProperties['id'] = 1; $setId = true; } else { $setId = false; } $relationKey = CRM_Utils_Array::key('Household Member of', $contactRelationshipTypes); foreach ($returnProperties as $key => $value) { if (!array_key_exists($key, $contactRelationshipTypes)) { $returnProperties[$relationKey][$key] = $value; } } unset($returnProperties[$relationKey]['location_type']); unset($returnProperties[$relationKey]['im_provider']); } $allRelContactArray = $relationQuery = array(); foreach ($contactRelationshipTypes as $rel => $dnt) { if ($relationReturnProperties = CRM_Utils_Array::value($rel, $returnProperties)) { $allRelContactArray[$rel] = array(); // build Query for each relationship $relationQuery[$rel] = new CRM_Contact_BAO_Query(0, $relationReturnProperties, null, false, false, $queryMode); list($relationSelect, $relationFrom, $relationWhere) = $relationQuery[$rel]->query(); list($id, $direction) = explode('_', $rel, 2); // identify the relationship direction $contactA = 'contact_id_a'; $contactB = 'contact_id_b'; if ($direction == 'b_a') { $contactA = 'contact_id_b'; $contactB = 'contact_id_a'; } if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) { $relIDs = $ids; } else { if ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { $query = "SELECT source_contact_id FROM civicrm_activity\n WHERE id IN ( " . implode(',', $ids) . ")"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $relIDs[] = $dao->source_contact_id; } } else { switch ($exportMode) { case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: $component = 'civicrm_contribution'; break; case CRM_Export_Form_Select::EVENT_EXPORT: $component = 'civicrm_participant'; break; case CRM_Export_Form_Select::MEMBER_EXPORT: $component = 'civicrm_membership'; break; case CRM_Export_Form_Select::PLEDGE_EXPORT: $component = 'civicrm_pledge'; break; case CRM_Export_Form_Select::CASE_EXPORT: $component = 'civicrm_case'; break; case CRM_Export_Form_Select::GRANT_EXPORT: $component = 'civicrm_grant'; break; } $relIDs = CRM_Core_DAO::getContactIDsFromComponent($ids, $component); } } $relationshipJoin = $relationshipClause = ''; if ($componentTable) { $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = {$contactA}"; } else { $relID = implode(',', $relIDs); $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )"; } $relationFrom = " {$relationFrom}\n INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id} \n {$relationshipJoin} "; $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause}"; $relationGroupBy = " GROUP BY crel.{$contactA}"; $relationSelect = "{$relationSelect}, {$contactA} as refContact "; $relationQueryString = "{$relationSelect} {$relationFrom} {$relationWhere} {$relationGroupBy}"; $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString); while ($allRelContactDAO->fetch()) { //FIX Me: Migrate this to table rather than array // build the array of all related contacts $allRelContactArray[$rel][$allRelContactDAO->refContact] = clone $allRelContactDAO; } $allRelContactDAO->free(); } } // make sure the groups stuff is included only if specifically specified // by the fields param (CRM-1969), else we limit the contacts outputted to only // ones that are part of a group if (CRM_Utils_Array::value('groups', $returnProperties)) { $oldClause = "contact_a.id = civicrm_group_contact.contact_id"; $newClause = " ( {$oldClause} AND civicrm_group_contact.status = 'Added' OR civicrm_group_contact.status IS NULL ) "; // total hack for export, CRM-3618 $from = str_replace($oldClause, $newClause, $from); } if ($componentTable) { $from .= " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = contact_a.id "; } else { if ($componentClause) { if (empty($where)) { $where = "WHERE {$componentClause}"; } else { $where .= " AND {$componentClause}"; } } } $queryString = "{$select} {$from} {$where}"; $groupBy = ""; if (CRM_Utils_Array::value('tags', $returnProperties) || CRM_Utils_Array::value('groups', $returnProperties) || CRM_Utils_Array::value('notes', $returnProperties) || $query->_useGroupBy) { $groupBy = " GROUP BY contact_a.id"; } if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) { $groupBy = " GROUP BY civicrm_activity.id "; } $queryString .= $groupBy; if ($order) { list($field, $dir) = explode(' ', $order, 2); $field = trim($field); if (CRM_Utils_Array::value($field, $returnProperties)) { // $queryString .= " ORDER BY $order"; } } //hack for student data require_once 'CRM/Core/OptionGroup.php'; $multipleSelectFields = array('preferred_communication_method' => 1); if (CRM_Core_Permission::access('Quest')) { require_once 'CRM/Quest/BAO/Student.php'; $studentFields = array(); $studentFields = CRM_Quest_BAO_Student::$multipleSelectFields; $multipleSelectFields = array_merge($multipleSelectFields, $studentFields); } $header = $addPaymentHeader = false; if ($paymentFields) { //special return properties for event and members $paymentHeaders = array('total_amount' => ts('Total Amount'), 'contribution_status' => ts('Contribution Status'), 'received_date' => ts('Received Date'), 'payment_instrument' => ts('Payment Instrument'), 'transaction_id' => ts('Transaction ID')); // get payment related in for event and members require_once 'CRM/Contribute/BAO/Contribution.php'; $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids); if (!empty($paymentDetails)) { $addPaymentHeader = true; } $nullContributionDetails = array_fill_keys($paymentHeaders, null); } $componentDetails = $headerRows = $sqlColumns = array(); $setHeader = true; $rowCount = self::EXPORT_ROW_COUNT; $offset = 0; $count = -1; // for CRM-3157 purposes require_once 'CRM/Core/I18n.php'; $i18n =& CRM_Core_I18n::singleton(); while (1) { $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}"; $dao = CRM_Core_DAO::executeQuery($limitQuery); if ($dao->N <= 0) { break; } while ($dao->fetch()) { $count++; $row = array(); //first loop through returnproperties so that we return what is required, and in same order. $relationshipField = 0; foreach ($returnProperties as $field => $value) { //we should set header only once if ($setHeader) { $sqlDone = false; if (isset($query->_fields[$field]['title'])) { $headerRows[] = $query->_fields[$field]['title']; } else { if ($field == 'phone_type_id') { $headerRows[] = 'Phone Type'; } else { if ($field == 'provider_id') { $headerRows[] = 'Im Service Provider'; } else { if (is_array($value) && $field == 'location') { // fix header for location type case foreach ($value as $ltype => $val) { foreach (array_keys($val) as $fld) { $type = explode('-', $fld); $hdr = "{$ltype}-" . $query->_fields[$type[0]]['title']; if (CRM_Utils_Array::value(1, $type)) { if (CRM_Utils_Array::value(0, $type) == 'phone') { $hdr .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes); } else { if (CRM_Utils_Array::value(0, $type) == 'im') { $hdr .= "-" . CRM_Utils_Array::value($type[1], $imProviders); } } } $headerRows[] = $hdr; self::sqlColumnDefn($query, $sqlColumns, $hdr); } $sqlDone = true; } } else { if (substr($field, 0, 5) == 'case_') { if ($query->_fields['case'][$field]['title']) { $headerRows[] = $query->_fields['case'][$field]['title']; } else { if ($query->_fields['activity'][$field]['title']) { $headerRows[] = $query->_fields['activity'][$field]['title']; } } } else { if (array_key_exists($field, $contactRelationshipTypes)) { $relName = $field; foreach ($value as $relationField => $relationValue) { // below block is same as primary block (duplicate) if (isset($relationQuery[$field]->_fields[$relationField]['title'])) { $headerName = $field . '-' . $relationQuery[$field]->_fields[$relationField]['title']; $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } else { if ($relationField == 'phone_type_id') { $headerName = $field . '-' . 'Phone Type'; $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } else { if ($relationField == 'provider_id') { $headerName = $field . '-' . 'Im Service Provider'; $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } else { if (is_array($relationValue) && $relationField == 'location') { // fix header for location type case foreach ($relationValue as $ltype => $val) { foreach (array_keys($val) as $fld) { $type = explode('-', $fld); $hdr = "{$ltype}-" . $relationQuery[$field]->_fields[$type[0]]['title']; if (CRM_Utils_Array::value(1, $type)) { if (CRM_Utils_Array::value(0, $type) == 'phone') { $hdr .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes); } else { if (CRM_Utils_Array::value(0, $type) == 'im') { $hdr .= "-" . CRM_Utils_Array::value($type[1], $imProviders); } } } $headerName = $field . '-' . $hdr; $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } } } } } } } } else { $headerRows[] = $field; } } } } } } if (!$sqlDone) { self::sqlColumnDefn($query, $sqlColumns, $field); } } //build row values (data) if (property_exists($dao, $field)) { $fieldValue = $dao->{$field}; // to get phone type from phone type id if ($field == 'phone_type_id') { $fieldValue = $phoneTypes[$fieldValue]; } else { if ($field == 'provider_id') { $fieldValue = CRM_Utils_Array::value($fieldValue, $imProviders); } else { if ($field == 'participant_role_id') { require_once 'CRM/Event/PseudoConstant.php'; $participantRoles = CRM_Event_PseudoConstant::participantRole(); $sep = CRM_Core_DAO::VALUE_SEPARATOR; $viewRoles = array(); foreach (explode($sep, $dao->{$field}) as $k => $v) { $viewRoles[] = $participantRoles[$v]; } $fieldValue = implode(',', $viewRoles); } } } } else { if ($field == 'master_address_belongs_to') { $masterAddressId = null; if (isset($dao->master_id)) { $masterAddressId = $dao->master_id; } // get display name of contact that address is shared. $fieldValue = CRM_Contact_BAO_Contact::getMasterDisplayName($masterAddressId, $dao->contact_id); } else { $fieldValue = ''; } } if ($field == 'id') { $row[$field] = $dao->contact_id; } else { if ($field == 'pledge_balance_amount') { //special case for calculated field $row[$field] = $dao->pledge_amount - $dao->pledge_total_paid; } else { if ($field == 'pledge_next_pay_amount') { //special case for calculated field $row[$field] = $dao->pledge_next_pay_amount + $dao->pledge_outstanding_amount; } else { if (is_array($value) && $field == 'location') { // fix header for location type case foreach ($value as $ltype => $val) { foreach (array_keys($val) as $fld) { $type = explode('-', $fld); $fldValue = "{$ltype}-" . $type[0]; if (CRM_Utils_Array::value(1, $type)) { $fldValue .= "-" . $type[1]; } // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) switch ($fld) { case 'country': case 'world_region': $row[$fldValue] = $i18n->crm_translate($dao->{$fldValue}, array('context' => 'country')); break; case 'state_province': $row[$fldValue] = $i18n->crm_translate($dao->{$fldValue}, array('context' => 'province')); break; default: $row[$fldValue] = $dao->{$fldValue}; break; } } } } else { if (array_key_exists($field, $contactRelationshipTypes)) { $relDAO = $allRelContactArray[$field][$dao->contact_id]; foreach ($value as $relationField => $relationValue) { if (is_object($relDAO) && property_exists($relDAO, $relationField)) { $fieldValue = $relDAO->{$relationField}; if ($relationField == 'phone_type_id') { $fieldValue = $phoneTypes[$relationValue]; } else { if ($relationField == 'provider_id') { $fieldValue = CRM_Utils_Array::value($relationValue, $imProviders); } } } else { $fieldValue = ''; } if ($relationField == 'id') { $row[$field . $relationField] = $relDAO->contact_id; } else { if (is_array($relationValue) && $relationField == 'location') { foreach ($relationValue as $ltype => $val) { foreach (array_keys($val) as $fld) { $type = explode('-', $fld); $fldValue = "{$ltype}-" . $type[0]; if (CRM_Utils_Array::value(1, $type)) { $fldValue .= "-" . $type[1]; } // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) switch (true) { case in_array('country', $type): case in_array('world_region', $type): $row[$field . $fldValue] = $i18n->crm_translate($relDAO->{$fldValue}, array('context' => 'country')); break; case in_array('state_province', $type): $row[$field . $fldValue] = $i18n->crm_translate($relDAO->{$fldValue}, array('context' => 'province')); break; default: $row[$field . $fldValue] = $relDAO->{$fldValue}; break; } } } } else { if (isset($fieldValue) && $fieldValue != '') { //check for custom data if ($cfID = CRM_Core_BAO_CustomField::getKeyID($relationField)) { $row[$field . $relationField] = CRM_Core_BAO_CustomField::getDisplayValue($fieldValue, $cfID, $relationQuery[$field]->_options); } else { if (in_array($relationField, array('email_greeting', 'postal_greeting', 'addressee'))) { //special case for greeting replacement $fldValue = "{$relationField}_display"; $row[$field . $relationField] = $relDAO->{$fldValue}; } else { //normal relationship fields // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) switch ($relationField) { case 'country': case 'world_region': $row[$field . $relationField] = $i18n->crm_translate($fieldValue, array('context' => 'country')); break; case 'state_province': $row[$field . $relationField] = $i18n->crm_translate($fieldValue, array('context' => 'province')); break; default: $row[$field . $relationField] = $fieldValue; break; } } } } else { // if relation field is empty or null $row[$field . $relationField] = ''; } } } } } else { if (isset($fieldValue) && $fieldValue != '') { //check for custom data if ($cfID = CRM_Core_BAO_CustomField::getKeyID($field)) { $row[$field] = CRM_Core_BAO_CustomField::getDisplayValue($fieldValue, $cfID, $query->_options); } else { if (array_key_exists($field, $multipleSelectFields)) { //option group fixes $paramsNew = array($field => $fieldValue); if ($field == 'test_tutoring') { $name = array($field => array('newName' => $field, 'groupName' => 'test')); } else { if (substr($field, 0, 4) == 'cmr_') { //for readers group $name = array($field => array('newName' => $field, 'groupName' => substr($field, 0, -3))); } else { $name = array($field => array('newName' => $field, 'groupName' => $field)); } } CRM_Core_OptionGroup::lookupValues($paramsNew, $name, false); $row[$field] = $paramsNew[$field]; } else { if (in_array($field, array('email_greeting', 'postal_greeting', 'addressee'))) { //special case for greeting replacement $fldValue = "{$field}_display"; $row[$field] = $dao->{$fldValue}; } else { //normal fields with a touch of CRM-3157 switch ($field) { case 'country': case 'world_region': $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'country')); break; case 'state_province': $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'province')); break; case 'gender': case 'preferred_communication_method': case 'preferred_mail_format': $row[$field] = $i18n->crm_translate($fieldValue); break; default: $row[$field] = $fieldValue; break; } } } } } else { // if field is empty or null $row[$field] = ''; } } } } } } } // add payment headers if required if ($addPaymentHeader && $paymentFields) { $headerRows = array_merge($headerRows, $paymentHeaders); foreach ($paymentHeaders as $paymentHdr) { self::sqlColumnDefn($query, $sqlColumns, $paymentHdr); } $addPaymentHeader = false; } if ($setHeader) { $exportTempTable = self::createTempTable($sqlColumns); } //build header only once $setHeader = false; // add payment related information if ($paymentFields && isset($paymentDetails[$row[$paymentTableId]])) { $row = array_merge($row, $paymentDetails[$row[$paymentTableId]]); } else { if ($paymentDetails) { $row = array_merge($row, $nullContributionDetails); } } //remove organization name for individuals if it is set for current employer if (CRM_Utils_Array::value('contact_type', $row) && $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row)) { $row['organization_name'] = ''; } // add component info // write the row to a file $componentDetails[] = $row; // output every $rowCount rows if ($count % $rowCount == 0) { self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); $componentDetails = array(); } } $dao->free(); $offset += $rowCount; } self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); // do merge same address and merge same household processing if ($mergeSameAddress) { self::mergeSameAddress($exportTempTable, $headerRows, $sqlColumns, $drop); } // merge the records if they have corresponding households if ($mergeSameHousehold) { self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, $relationKey); } // fix the headers for rows with relationship type if ($relName) { self::manipulateHeaderRows($headerRows, $contactRelationshipTypes); } // call export hook require_once 'CRM/Utils/Hook.php'; CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode); // now write the CSV file self::writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $exportMode); CRM_Utils_System::civiExit(); }
/** * Get contact participant record. * * This api is used for finding an existing participant record. * * @param array $params an associative array of name/value property values of civicrm_participant * * @return participant property values. * @access public */ function &civicrm_participant_search(&$params) { if (!is_array($params)) { return civicrm_create_error('Params need to be of type array!'); } $inputParams = array(); $returnProperties = array(); $otherVars = array('sort', 'offset', 'rowCount'); $sort = NULL; $offset = 0; $rowCount = 25; foreach ($params as $n => $v) { if (substr($n, 0, 7) == 'return.') { $returnProperties[substr($n, 7)] = $v; } elseif (in_array($n, $otherVars)) { ${$n} = $v; } else { $inputParams[$n] = $v; } } // add is_test to the clause if not present if (!array_key_exists('participant_test', $inputParams)) { $inputParams['participant_test'] = 0; } require_once 'CRM/Contact/BAO/Query.php'; require_once 'CRM/Event/BAO/Query.php'; if (empty($returnProperties)) { $returnProperties = CRM_Event_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_EVENT); } $newParams = CRM_Contact_BAO_Query::convertFormValues($params); $query = new CRM_Contact_BAO_Query($newParams, $returnProperties, NULL); list($select, $from, $where, $having) = $query->query(); $sql = "{$select} {$from} {$where} {$having}"; if (!empty($sort)) { $sql .= " ORDER BY {$sort} "; } $sql .= " LIMIT {$offset}, {$rowCount} "; $dao = CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray); $participant = array(); while ($dao->fetch()) { $participant[$dao->participant_id] = $query->store($dao); } $dao->free(); return $participant; }
/** * wrapper for a api search query * * @param array $params * @param array $returnProperties * @param string $sort * @param int $offset * @param int $row_count * * @return void * @access public */ static function apiQuery($params = NULL, $returnProperties = NULL, $fields = NULL, $sort = NULL, $offset = 0, $row_count = 25, $smartGroupCache = TRUE) { $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL, TRUE, FALSE, 1, FALSE, TRUE, $smartGroupCache); list($select, $from, $where, $having) = $query->query(); $options = $query->_options; $sql = "{$select} {$from} {$where} {$having}"; // add group by if ($query->_useGroupBy) { $sql .= ' GROUP BY contact_a.id'; } if (!empty($sort)) { $sql .= " ORDER BY {$sort} "; } if ($row_count > 0 && $offset >= 0) { $sql .= " LIMIT {$offset}, {$row_count} "; } $dao = CRM_Core_DAO::executeQuery($sql); $values = array(); while ($dao->fetch()) { $values[$dao->contact_id] = $query->store($dao); } $dao->free(); return array($values, $options); }
/** * Get dao query object based on input params. * * Ideally this would be merged with _civicrm_get_using_query_object but we need to resolve differences in what the * 2 variants call * * @param array $params * @param string $mode * @param string $entity * * @return array * [CRM_Core_DAO|CRM_Contact_BAO_Query] */ function _civicrm_api3_get_query_object($params, $mode, $entity) { $options = _civicrm_api3_get_options_from_params($params, TRUE, $entity, 'get'); $sort = CRM_Utils_Array::value('sort', $options, NULL); $offset = CRM_Utils_Array::value('offset', $options); $rowCount = CRM_Utils_Array::value('limit', $options); $inputParams = CRM_Utils_Array::value('input_params', $options, array()); $returnProperties = CRM_Utils_Array::value('return', $options, NULL); if (empty($returnProperties)) { $returnProperties = CRM_Contribute_BAO_Query::defaultReturnProperties($mode); } $newParams = CRM_Contact_BAO_Query::convertFormValues($inputParams, 0, FALSE, $entity); $query = new CRM_Contact_BAO_Query($newParams, $returnProperties, NULL, FALSE, FALSE, $mode, empty($params['check_permissions'])); list($select, $from, $where, $having) = $query->query(); $sql = "{$select} {$from} {$where} {$having}"; if (!empty($sort)) { $sql .= " ORDER BY {$sort} "; } if (!empty($rowCount)) { $sql .= " LIMIT {$offset}, {$rowCount} "; } $dao = CRM_Core_DAO::executeQuery($sql); return array($dao, $query); }
/** * Get the list the export fields. * * @param int $selectAll * User preference while export. * @param array $ids * Contact ids. * @param array $params * Associated array of fields. * @param string $order * Order by clause. * @param array $fields * Associated array of fields. * @param array $moreReturnProperties * Additional return fields. * @param int $exportMode * Export mode. * @param string $componentClause * Component clause. * @param string $componentTable * Component table. * @param bool $mergeSameAddress * Merge records if they have same address. * @param bool $mergeSameHousehold * Merge records if they belong to the same household. * * @param array $exportParams * @param string $queryOperator * */ public static function exportComponents($selectAll, $ids, $params, $order = NULL, $fields = NULL, $moreReturnProperties = NULL, $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT, $componentClause = NULL, $componentTable = NULL, $mergeSameAddress = FALSE, $mergeSameHousehold = FALSE, $exportParams = array(), $queryOperator = 'AND') { $headerRows = $returnProperties = array(); $primary = $paymentFields = $selectedPaymentFields = FALSE; $origFields = $fields; $relationField = NULL; $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id'); $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id'); $contactRelationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, NULL, NULL, NULL, TRUE, 'name', FALSE); $queryMode = self::getQueryMode($exportMode); if ($fields) { //construct return properties $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id'); $locationTypeFields = array('street_address', 'supplemental_address_1', 'supplemental_address_2', 'city', 'postal_code', 'postal_code_suffix', 'geo_code_1', 'geo_code_2', 'state_province', 'country', 'phone', 'email', 'im'); foreach ($fields as $key => $value) { $phoneTypeId = $imProviderId = NULL; $relationshipTypes = $fieldName = CRM_Utils_Array::value(1, $value); if (!$fieldName) { continue; } // get phoneType id and IM service provider id separately if ($fieldName == 'phone') { $phoneTypeId = CRM_Utils_Array::value(3, $value); } elseif ($fieldName == 'im') { $imProviderId = CRM_Utils_Array::value(3, $value); } if (array_key_exists($relationshipTypes, $contactRelationshipTypes)) { if (!empty($value[2])) { $relationField = CRM_Utils_Array::value(2, $value); if (trim(CRM_Utils_Array::value(3, $value))) { $relLocTypeId = CRM_Utils_Array::value(3, $value); } else { $relLocTypeId = 'Primary'; } if ($relationField == 'phone') { $relPhoneTypeId = CRM_Utils_Array::value(4, $value); } elseif ($relationField == 'im') { $relIMProviderId = CRM_Utils_Array::value(4, $value); } } elseif (!empty($value[4])) { $relationField = CRM_Utils_Array::value(4, $value); $relLocTypeId = CRM_Utils_Array::value(5, $value); if ($relationField == 'phone') { $relPhoneTypeId = CRM_Utils_Array::value(6, $value); } elseif ($relationField == 'im') { $relIMProviderId = CRM_Utils_Array::value(6, $value); } } } $contactType = CRM_Utils_Array::value(0, $value); $locTypeId = CRM_Utils_Array::value(2, $value); if ($relationField) { if (in_array($relationField, $locationTypeFields) && is_numeric($relLocTypeId)) { if ($relPhoneTypeId) { $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['phone-' . $relPhoneTypeId] = 1; } elseif ($relIMProviderId) { $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['im-' . $relIMProviderId] = 1; } else { $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]][$relationField] = 1; } $relPhoneTypeId = $relIMProviderId = NULL; } else { $returnProperties[$relationshipTypes][$relationField] = 1; } } elseif (is_numeric($locTypeId)) { if ($phoneTypeId) { $returnProperties['location'][$locationTypes[$locTypeId]]['phone-' . $phoneTypeId] = 1; } elseif ($imProviderId) { $returnProperties['location'][$locationTypes[$locTypeId]]['im-' . $imProviderId] = 1; } else { $returnProperties['location'][$locationTypes[$locTypeId]][$fieldName] = 1; } } else { //hack to fix component fields //revert mix of event_id and title if ($fieldName == 'event_id') { $returnProperties['event_id'] = 1; } elseif ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT && array_key_exists($fieldName, self::componentPaymentFields())) { $selectedPaymentFields = TRUE; $paymentTableId = 'participant_id'; $returnProperties[$fieldName] = 1; } else { $returnProperties[$fieldName] = 1; } } } $returnProperties[self::defaultReturnProperty($exportMode)] = 1; } else { $primary = TRUE; $fields = CRM_Contact_BAO_Contact::exportableFields('All', TRUE, TRUE); foreach ($fields as $key => $var) { if ($key && substr($key, 0, 6) != 'custom') { //for CRM=952 $returnProperties[$key] = 1; } } if ($primary) { $returnProperties['location_type'] = 1; $returnProperties['im_provider'] = 1; $returnProperties['phone_type_id'] = 1; $returnProperties['provider_id'] = 1; $returnProperties['current_employer'] = 1; } $extraReturnProperties = array(); $paymentFields = FALSE; switch ($queryMode) { case CRM_Contact_BAO_Query::MODE_EVENT: $paymentFields = TRUE; $paymentTableId = 'participant_id'; break; case CRM_Contact_BAO_Query::MODE_MEMBER: $paymentFields = TRUE; $paymentTableId = 'membership_id'; break; case CRM_Contact_BAO_Query::MODE_PLEDGE: $extraReturnProperties = CRM_Pledge_BAO_Query::extraReturnProperties($queryMode); $paymentFields = TRUE; $paymentTableId = 'pledge_payment_id'; break; case CRM_Contact_BAO_Query::MODE_CASE: $extraReturnProperties = CRM_Case_BAO_Query::extraReturnProperties($queryMode); break; } if ($queryMode != CRM_Contact_BAO_Query::MODE_CONTACTS) { $componentReturnProperties = CRM_Contact_BAO_Query::defaultReturnProperties($queryMode); if ($queryMode == CRM_Contact_BAO_Query::MODE_CONTRIBUTE) { // soft credit columns are not automatically populated, because contribution search doesn't require them by default $componentReturnProperties = array_merge($componentReturnProperties, CRM_Contribute_BAO_Query::softCreditReturnProperties(TRUE)); } $returnProperties = array_merge($returnProperties, $componentReturnProperties); if (!empty($extraReturnProperties)) { $returnProperties = array_merge($returnProperties, $extraReturnProperties); } // unset non exportable fields for components $nonExpoFields = array('groups', 'tags', 'notes', 'contribution_status_id', 'pledge_status_id', 'pledge_payment_status_id'); foreach ($nonExpoFields as $value) { unset($returnProperties[$value]); } } } if ($mergeSameAddress) { //make sure the addressee fields are selected //while using merge same address feature $returnProperties['addressee'] = 1; $returnProperties['postal_greeting'] = 1; $returnProperties['email_greeting'] = 1; $returnProperties['street_name'] = 1; $returnProperties['household_name'] = 1; $returnProperties['street_address'] = 1; $returnProperties['city'] = 1; $returnProperties['state_province'] = 1; // some columns are required for assistance incase they are not already present $exportParams['merge_same_address']['temp_columns'] = array(); $tempColumns = array('id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id'); foreach ($tempColumns as $column) { if (!array_key_exists($column, $returnProperties)) { $returnProperties[$column] = 1; $column = $column == 'id' ? 'civicrm_primary_id' : $column; $exportParams['merge_same_address']['temp_columns'][$column] = 1; } } } if (!$selectAll && $componentTable && !empty($exportParams['additional_group'])) { // If an Additional Group is selected, then all contacts in that group are // added to the export set (filtering out duplicates). $query = "\nINSERT INTO {$componentTable} SELECT distinct gc.contact_id FROM civicrm_group_contact gc WHERE gc.group_id = {$exportParams['additional_group']} ON DUPLICATE KEY UPDATE {$componentTable}.contact_id = gc.contact_id"; CRM_Core_DAO::executeQuery($query); } if ($moreReturnProperties) { // fix for CRM-7066 if (!empty($moreReturnProperties['group'])) { unset($moreReturnProperties['group']); $moreReturnProperties['groups'] = 1; } $returnProperties = array_merge($returnProperties, $moreReturnProperties); } $exportParams['postal_mailing_export']['temp_columns'] = array(); if ($exportParams['exportOption'] == 2 && isset($exportParams['postal_mailing_export']) && CRM_Utils_Array::value('postal_mailing_export', $exportParams['postal_mailing_export']) == 1) { $postalColumns = array('is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1'); foreach ($postalColumns as $column) { if (!array_key_exists($column, $returnProperties)) { $returnProperties[$column] = 1; $exportParams['postal_mailing_export']['temp_columns'][$column] = 1; } } } // rectify params to what proximity search expects if there is a value for prox_distance // CRM-7021 if (!empty($params)) { CRM_Contact_BAO_ProximityQuery::fixInputParams($params); } $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL, FALSE, FALSE, $queryMode, FALSE, TRUE, TRUE, NULL, $queryOperator); //sort by state //CRM-15301 $query->_sort = $order; list($select, $from, $where, $having) = $query->query(); if ($mergeSameHousehold == 1) { if (!$returnProperties['id']) { $returnProperties['id'] = 1; } //also merge Head of Household $relationKeyMOH = CRM_Utils_Array::key('Household Member of', $contactRelationshipTypes); $relationKeyHOH = CRM_Utils_Array::key('Head of Household for', $contactRelationshipTypes); foreach ($returnProperties as $key => $value) { if (!array_key_exists($key, $contactRelationshipTypes)) { $returnProperties[$relationKeyMOH][$key] = $value; $returnProperties[$relationKeyHOH][$key] = $value; } } unset($returnProperties[$relationKeyMOH]['location_type']); unset($returnProperties[$relationKeyMOH]['im_provider']); unset($returnProperties[$relationKeyHOH]['location_type']); unset($returnProperties[$relationKeyHOH]['im_provider']); } $allRelContactArray = $relationQuery = array(); foreach ($contactRelationshipTypes as $rel => $dnt) { if ($relationReturnProperties = CRM_Utils_Array::value($rel, $returnProperties)) { $allRelContactArray[$rel] = array(); // build Query for each relationship $relationQuery[$rel] = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties, NULL, FALSE, FALSE, $queryMode); list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery[$rel]->query(); list($id, $direction) = explode('_', $rel, 2); // identify the relationship direction $contactA = 'contact_id_a'; $contactB = 'contact_id_b'; if ($direction == 'b_a') { $contactA = 'contact_id_b'; $contactB = 'contact_id_a'; } if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) { $relIDs = $ids; } elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { $sourceID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Source'); $query = "SELECT contact_id FROM civicrm_activity_contact\n WHERE activity_id IN ( " . implode(',', $ids) . ") AND\n record_type_id = {$sourceID}"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $relIDs[] = $dao->contact_id; } } else { $component = self::exportComponent($exportMode); if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) { $relIDs = CRM_Case_BAO_Case::retrieveContactIdsByCaseId($ids); } else { $relIDs = CRM_Core_DAO::getContactIDsFromComponent($ids, $component); } } $relationshipJoin = $relationshipClause = ''; if (!$selectAll && $componentTable) { $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = {$contactA}"; } elseif (!empty($relIDs)) { $relID = implode(',', $relIDs); $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )"; } $relationFrom = " {$relationFrom}\n INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id}\n {$relationshipJoin} "; //check for active relationship status only $today = date('Ymd'); $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )"; $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}"; $relationGroupBy = " GROUP BY crel.{$contactA}"; $relationSelect = "{$relationSelect}, {$contactA} as refContact "; $relationQueryString = "{$relationSelect} {$relationFrom} {$relationWhere} {$relationHaving} {$relationGroupBy}"; $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString); while ($allRelContactDAO->fetch()) { //FIX Me: Migrate this to table rather than array // build the array of all related contacts $allRelContactArray[$rel][$allRelContactDAO->refContact] = clone $allRelContactDAO; } $allRelContactDAO->free(); } } // make sure the groups stuff is included only if specifically specified // by the fields param (CRM-1969), else we limit the contacts outputted to only // ones that are part of a group if (!empty($returnProperties['groups'])) { $oldClause = "( contact_a.id = civicrm_group_contact.contact_id )"; $newClause = " ( {$oldClause} AND ( civicrm_group_contact.status = 'Added' OR civicrm_group_contact.status IS NULL ) )"; // total hack for export, CRM-3618 $from = str_replace($oldClause, $newClause, $from); } if (!$selectAll && $componentTable) { $from .= " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = contact_a.id "; } elseif ($componentClause) { if (empty($where)) { $where = "WHERE {$componentClause}"; } else { $where .= " AND {$componentClause}"; } } // CRM-13982 - check if is deleted $excludeTrashed = TRUE; foreach ($params as $value) { if ($value[0] == 'contact_is_deleted') { $excludeTrashed = FALSE; } } $trashClause = $excludeTrashed ? "contact_a.is_deleted != 1" : "( 1 )"; if (empty($where)) { $where = "WHERE {$trashClause}"; } else { $where .= " AND {$trashClause}"; } $queryString = "{$select} {$from} {$where} {$having}"; $groupBy = ""; if (!empty($returnProperties['tags']) || !empty($returnProperties['groups']) || CRM_Utils_Array::value('notes', $returnProperties) || $queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy) { $groupBy = " GROUP BY contact_a.id"; } switch ($exportMode) { case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: $groupBy = 'GROUP BY civicrm_contribution.id'; if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) { // especial group by when soft credit columns are included $groupBy = 'GROUP BY contribution_search_scredit_combined.id, contribution_search_scredit_combined.scredit_id'; } break; case CRM_Export_Form_Select::EVENT_EXPORT: $groupBy = 'GROUP BY civicrm_participant.id'; break; case CRM_Export_Form_Select::MEMBER_EXPORT: $groupBy = " GROUP BY civicrm_membership.id"; break; } if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) { $groupBy = " GROUP BY civicrm_activity.id "; } $queryString .= $groupBy; // always add contact_a.id to the ORDER clause // so the order is deterministic //CRM-15301 if (strpos('contact_a.id', $order) === FALSE) { $order .= ", contact_a.id"; } if ($order) { list($field, $dir) = explode(' ', $order, 2); $field = trim($field); if (!empty($returnProperties[$field])) { //CRM-15301 $queryString .= " ORDER BY {$order}"; } } $multipleSelectFields = array('preferred_communication_method' => 1); $addPaymentHeader = FALSE; $paymentDetails = array(); if ($paymentFields || $selectedPaymentFields) { // get payment related in for event and members $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids); //get all payment headers. // If we haven't selected specific payment fields, load in all the // payment headers. if (!$selectedPaymentFields) { $paymentHeaders = self::componentPaymentFields(); if (!empty($paymentDetails)) { $addPaymentHeader = TRUE; } } else { $paymentHeaders = array(); } $nullContributionDetails = array_fill_keys(array_keys($paymentHeaders), NULL); } $componentDetails = $headerRows = $sqlColumns = array(); $setHeader = TRUE; $rowCount = self::EXPORT_ROW_COUNT; $offset = 0; // we write to temp table often to avoid using too much memory $tempRowCount = 100; $count = -1; // for CRM-3157 purposes $i18n = CRM_Core_I18n::singleton(); $outputColumns = array(); //@todo - it would be clearer to start defining output columns earlier in this function rather than stick with return properties until this point // as the array is not actually 'returnProperties' after the sql query is formed - making the alterations to it confusing foreach ($returnProperties as $key => $value) { $outputColumns[$key] = $value; } while (1) { $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}"; $dao = CRM_Core_DAO::executeQuery($limitQuery); if ($dao->N <= 0) { break; } while ($dao->fetch()) { $count++; $row = array(); //convert the pseudo constants // CRM-14398 there is problem in this architecture that is not easily solved. For now we are using the cloned // temporary iterationDAO object to get around it. // the issue is that the convertToPseudoNames function is adding additional properties (e.g for campaign) to the DAO object // these additional properties are NOT reset when the $dao cycles through the while loop // nor are they overwritten as they are not in the loop // the convertToPseudoNames will not adequately over-write them either as it doesn't 'kick-in' unless the // relevant property is set. // It may be that a long-term fix could be introduced there - however, it's probably necessary to figure out how to test the // export class before tackling a better architectural fix $iterationDAO = clone $dao; $query->convertToPseudoNames($iterationDAO); //first loop through output columns so that we return what is required, and in same order. $relationshipField = 0; foreach ($outputColumns as $field => $value) { //we should set header only once if ($setHeader) { $sqlDone = FALSE; // Split campaign into 2 fields for id and title if (substr($field, -14) == 'campaign_title') { $headerRows[] = ts('Campaign Title'); } elseif (substr($field, -11) == 'campaign_id') { $headerRows[] = ts('Campaign ID'); } elseif (isset($query->_fields[$field]['title'])) { $headerRows[] = $query->_fields[$field]['title']; } elseif ($field == 'phone_type_id') { $headerRows[] = ts('Phone Type'); } elseif ($field == 'provider_id') { $headerRows[] = ts('IM Service Provider'); } elseif (is_array($value) && $field == 'location') { // fix header for location type case foreach ($value as $ltype => $val) { foreach (array_keys($val) as $fld) { $type = explode('-', $fld); $hdr = "{$ltype}-" . $query->_fields[$type[0]]['title']; if (!empty($type[1])) { if (CRM_Utils_Array::value(0, $type) == 'phone') { $hdr .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes); } elseif (CRM_Utils_Array::value(0, $type) == 'im') { $hdr .= "-" . CRM_Utils_Array::value($type[1], $imProviders); } } $headerRows[] = $hdr; self::sqlColumnDefn($query, $sqlColumns, $hdr); } $sqlDone = TRUE; } } elseif (substr($field, 0, 5) == 'case_') { if ($query->_fields['case'][$field]['title']) { $headerRows[] = $query->_fields['case'][$field]['title']; } elseif ($query->_fields['activity'][$field]['title']) { $headerRows[] = $query->_fields['activity'][$field]['title']; } } elseif (array_key_exists($field, $contactRelationshipTypes)) { $relName = $field; foreach ($value as $relationField => $relationValue) { // below block is same as primary block (duplicate) if (isset($relationQuery[$field]->_fields[$relationField]['title'])) { if ($relationQuery[$field]->_fields[$relationField]['name'] == 'name') { $headerName = $field . '-' . $relationField; } else { if ($relationField == 'current_employer') { $headerName = $field . '-' . 'current_employer'; } else { $headerName = $field . '-' . $relationQuery[$field]->_fields[$relationField]['name']; } } $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } elseif ($relationField == 'phone_type_id') { $headerName = $field . '-' . 'Phone Type'; $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } elseif ($relationField == 'provider_id') { $headerName = $field . '-' . 'Im Service Provider'; $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } elseif ($relationField == 'state_province_id') { $headerName = $field . '-' . 'state_province_id'; $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } elseif (is_array($relationValue) && $relationField == 'location') { // fix header for location type case foreach ($relationValue as $ltype => $val) { foreach (array_keys($val) as $fld) { $type = explode('-', $fld); $hdr = "{$ltype}-" . $relationQuery[$field]->_fields[$type[0]]['title']; if (!empty($type[1])) { if (CRM_Utils_Array::value(0, $type) == 'phone') { $hdr .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes); } elseif (CRM_Utils_Array::value(0, $type) == 'im') { $hdr .= "-" . CRM_Utils_Array::value($type[1], $imProviders); } } $headerName = $field . '-' . $hdr; $headerRows[] = $headerName; self::sqlColumnDefn($query, $sqlColumns, $headerName); } } } } } elseif ($selectedPaymentFields && array_key_exists($field, self::componentPaymentFields())) { $headerRows[] = CRM_Utils_Array::value($field, self::componentPaymentFields()); } else { $headerRows[] = $field; } if (!$sqlDone) { self::sqlColumnDefn($query, $sqlColumns, $field); } } // add im_provider to $dao object if ($field == 'im_provider' && property_exists($iterationDAO, 'provider_id')) { $iterationDAO->im_provider = $iterationDAO->provider_id; } //build row values (data) $fieldValue = NULL; if (property_exists($iterationDAO, $field)) { $fieldValue = $iterationDAO->{$field}; // to get phone type from phone type id if ($field == 'phone_type_id' && isset($phoneTypes[$fieldValue])) { $fieldValue = $phoneTypes[$fieldValue]; } elseif ($field == 'provider_id' || $field == 'im_provider') { $fieldValue = CRM_Utils_Array::value($fieldValue, $imProviders); } elseif ($field == 'master_id') { $masterAddressId = NULL; if (isset($iterationDAO->master_id)) { $masterAddressId = $iterationDAO->master_id; } // get display name of contact that address is shared. $fieldValue = CRM_Contact_BAO_Contact::getMasterDisplayName($masterAddressId, $iterationDAO->contact_id); } } if ($field == 'id') { $row[$field] = $iterationDAO->contact_id; // special case for calculated field } elseif ($field == 'source_contact_id') { $row[$field] = $iterationDAO->contact_id; } elseif ($field == 'pledge_balance_amount') { $row[$field] = $iterationDAO->pledge_amount - $iterationDAO->pledge_total_paid; // special case for calculated field } elseif ($field == 'pledge_next_pay_amount') { $row[$field] = $iterationDAO->pledge_next_pay_amount + $iterationDAO->pledge_outstanding_amount; } elseif (is_array($value) && $field == 'location') { // fix header for location type case foreach ($value as $ltype => $val) { foreach (array_keys($val) as $fld) { $type = explode('-', $fld); $fldValue = "{$ltype}-" . $type[0]; // CRM-14076 - fix label to work as the query object expects // FIXME: We should not be using labels as keys! $daoField = CRM_Utils_String::munge($ltype) . '-' . $type[0]; if (!empty($type[1])) { $fldValue .= "-" . $type[1]; $daoField .= "-" . $type[1]; } // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) switch ($fld) { case 'country': case 'world_region': $row[$fldValue] = $i18n->crm_translate($iterationDAO->{$daoField}, array('context' => 'country')); break; case 'state_province': $row[$fldValue] = $i18n->crm_translate($iterationDAO->{$daoField}, array('context' => 'province')); break; case 'im_provider': $imFieldvalue = $daoField . "-provider_id"; $row[$fldValue] = CRM_Utils_Array::value($iterationDAO->{$imFieldvalue}, $imProviders); break; default: $row[$fldValue] = $iterationDAO->{$daoField}; break; } } } } elseif (array_key_exists($field, $contactRelationshipTypes)) { $relDAO = CRM_Utils_Array::value($iterationDAO->contact_id, $allRelContactArray[$field]); $relationQuery[$field]->convertToPseudoNames($relDAO); foreach ($value as $relationField => $relationValue) { if (is_object($relDAO) && property_exists($relDAO, $relationField)) { $fieldValue = $relDAO->{$relationField}; if ($relationField == 'phone_type_id') { $fieldValue = $phoneTypes[$relationValue]; } elseif ($relationField == 'provider_id') { $fieldValue = CRM_Utils_Array::value($relationValue, $imProviders); } elseif (is_object($relDAO) && in_array($relationField, array('email_greeting', 'postal_greeting', 'addressee'))) { //special case for greeting replacement $fldValue = "{$relationField}_display"; $fieldValue = $relDAO->{$fldValue}; } } elseif (is_object($relDAO) && $relationField == 'state_province') { $fieldValue = CRM_Core_PseudoConstant::stateProvince($relDAO->state_province_id); } elseif (is_object($relDAO) && $relationField == 'country') { $fieldValue = CRM_Core_PseudoConstant::country($relDAO->country_id); } else { $fieldValue = ''; } $field = $field . '_'; if (array_key_exists($relationField, $multipleSelectFields)) { $param = array($relationField => $fieldValue); $names = array($relationField => array('newName' => $relationField, 'groupName' => $relationField)); CRM_Core_OptionGroup::lookupValues($param, $names, FALSE); $fieldValue = $param[$relationField]; } if (is_object($relDAO) && $relationField == 'id') { $row[$field . $relationField] = $relDAO->contact_id; } elseif (is_array($relationValue) && $relationField == 'location') { foreach ($relationValue as $ltype => $val) { foreach (array_keys($val) as $fld) { $type = explode('-', $fld); $fldValue = "{$ltype}-" . $type[0]; if (!empty($type[1])) { $fldValue .= "-" . $type[1]; } // CRM-3157: localise country, region (both have ‘country’ context) // and state_province (‘province’ context) switch (TRUE) { case !is_object($relDAO): $row[$field . '_' . $fldValue] = ''; break; case in_array('country', $type): case in_array('world_region', $type): $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->{$fldValue}, array('context' => 'country')); break; case in_array('state_province', $type): $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->{$fldValue}, array('context' => 'province')); break; default: $row[$field . '_' . $fldValue] = $relDAO->{$fldValue}; break; } } } } elseif (isset($fieldValue) && $fieldValue != '') { //check for custom data if ($cfID = CRM_Core_BAO_CustomField::getKeyID($relationField)) { $row[$field . $relationField] = CRM_Core_BAO_CustomField::getDisplayValue($fieldValue, $cfID, $relationQuery[$field]->_options); } else { //normal relationship fields // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) switch ($relationField) { case 'country': case 'world_region': $row[$field . $relationField] = $i18n->crm_translate($fieldValue, array('context' => 'country')); break; case 'state_province': $row[$field . $relationField] = $i18n->crm_translate($fieldValue, array('context' => 'province')); break; default: $row[$field . $relationField] = $fieldValue; break; } } } else { // if relation field is empty or null $row[$field . $relationField] = ''; } } } elseif (isset($fieldValue) && $fieldValue != '') { //check for custom data if ($cfID = CRM_Core_BAO_CustomField::getKeyID($field)) { $row[$field] = CRM_Core_BAO_CustomField::getDisplayValue($fieldValue, $cfID, $query->_options); } elseif (array_key_exists($field, $multipleSelectFields)) { //option group fixes $paramsNew = array($field => $fieldValue); if ($field == 'test_tutoring') { $name = array($field => array('newName' => $field, 'groupName' => 'test')); // for readers group } elseif (substr($field, 0, 4) == 'cmr_') { $name = array($field => array('newName' => $field, 'groupName' => substr($field, 0, -3))); } else { $name = array($field => array('newName' => $field, 'groupName' => $field)); } CRM_Core_OptionGroup::lookupValues($paramsNew, $name, FALSE); $row[$field] = $paramsNew[$field]; } elseif (in_array($field, array('email_greeting', 'postal_greeting', 'addressee'))) { //special case for greeting replacement $fldValue = "{$field}_display"; $row[$field] = $iterationDAO->{$fldValue}; } else { //normal fields with a touch of CRM-3157 switch ($field) { case 'country': case 'world_region': $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'country')); break; case 'state_province': $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'province')); break; case 'gender': case 'preferred_communication_method': case 'preferred_mail_format': case 'communication_style': $row[$field] = $i18n->crm_translate($fieldValue); break; default: $row[$field] = $fieldValue; break; } } } elseif ($selectedPaymentFields && array_key_exists($field, self::componentPaymentFields())) { $paymentData = CRM_Utils_Array::value($iterationDAO->{$paymentTableId}, $paymentDetails); $payFieldMapper = array('componentPaymentField_total_amount' => 'total_amount', 'componentPaymentField_contribution_status' => 'contribution_status', 'componentPaymentField_payment_instrument' => 'pay_instru', 'componentPaymentField_transaction_id' => 'trxn_id', 'componentPaymentField_received_date' => 'receive_date'); $row[$field] = CRM_Utils_Array::value($payFieldMapper[$field], $paymentData, ''); } else { // if field is empty or null $row[$field] = ''; } } // add payment headers if required if ($addPaymentHeader && $paymentFields) { $headerRows = array_merge($headerRows, $paymentHeaders); foreach (array_keys($paymentHeaders) as $paymentHdr) { self::sqlColumnDefn($query, $sqlColumns, $paymentHdr); } } if ($setHeader) { $exportTempTable = self::createTempTable($sqlColumns); } //build header only once $setHeader = FALSE; // If specific payment fields have been selected for export, payment // data will already be in $row. Otherwise, add payment related // information, if appropriate. if ($addPaymentHeader) { if (!$selectedPaymentFields) { if ($paymentFields) { $paymentData = CRM_Utils_Array::value($row[$paymentTableId], $paymentDetails); if (!is_array($paymentData) || empty($paymentData)) { $paymentData = $nullContributionDetails; } $row = array_merge($row, $paymentData); } elseif (!empty($paymentDetails)) { $row = array_merge($row, $nullContributionDetails); } } } //remove organization name for individuals if it is set for current employer if (!empty($row['contact_type']) && $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row)) { $row['organization_name'] = ''; } // add component info // write the row to a file $componentDetails[] = $row; // output every $tempRowCount rows if ($count % $tempRowCount == 0) { self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); $componentDetails = array(); } } $dao->free(); $offset += $rowCount; } if ($exportTempTable) { self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); // do merge same address and merge same household processing if ($mergeSameAddress) { self::mergeSameAddress($exportTempTable, $headerRows, $sqlColumns, $exportParams); } // merge the records if they have corresponding households if ($mergeSameHousehold) { self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, $relationKeyMOH); self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, $relationKeyHOH); } // fix the headers for rows with relationship type if (!empty($relName)) { self::manipulateHeaderRows($headerRows, $contactRelationshipTypes); } // if postalMailing option is checked, exclude contacts who are deceased, have // "Do not mail" privacy setting, or have no street address if (isset($exportParams['postal_mailing_export']['postal_mailing_export']) && $exportParams['postal_mailing_export']['postal_mailing_export'] == 1) { self::postalMailingFormat($exportTempTable, $headerRows, $sqlColumns, $exportMode); } // call export hook CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode); // now write the CSV file self::writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $exportMode); // delete the export temp table and component table $sql = "DROP TABLE IF EXISTS {$exportTempTable}"; CRM_Core_DAO::executeQuery($sql); CRM_Utils_System::civiExit(); } else { CRM_Core_Error::fatal(ts('No records to export')); } }
/** * Retrieve a set of contributions, given a set of input params * * @param array $params (reference ) input parameters * @param array $returnProperties Which properties should be included in the * returned Contribution object. If NULL, the default * set of properties will be included. * * @return array (reference ) array of contributions, if error an array with an error id and error message * @static void * @access public */ function &civicrm_contribution_search( &$params ) { _civicrm_initialize( ); if ( ! is_array( $params ) ) { return civicrm_create_error( ts( 'Input parameters is not an array' ) ); } $inputParams = array( ); $returnProperties = array( ); $otherVars = array( 'sort', 'offset', 'rowCount' ); $sort = null; $offset = 0; $rowCount = 25; foreach ( $params as $n => $v ) { if ( substr( $n, 0, 7 ) == 'return.' ) { $returnProperties[ substr( $n, 7 ) ] = $v; } elseif ( in_array( $n, $otherVars ) ) { $$n = $v; } else { $inputParams[$n] = $v; } } // add is_test to the clause if not present if ( ! array_key_exists( 'contribution_test', $inputParams ) ) { $inputParams['contribution_test'] = 0; } require_once 'CRM/Contribute/BAO/Query.php'; require_once 'CRM/Contact/BAO/Query.php'; if ( empty( $returnProperties ) ) { $returnProperties = CRM_Contribute_BAO_Query::defaultReturnProperties( CRM_Contact_BAO_Query::MODE_CONTRIBUTE ); } $newParams =& CRM_Contact_BAO_Query::convertFormValues( $inputParams ); $query = new CRM_Contact_BAO_Query( $newParams, $returnProperties, null ); list( $select, $from, $where ) = $query->query( ); $sql = "$select $from $where"; if ( ! empty( $sort ) ) { $sql .= " ORDER BY $sort "; } $sql .= " LIMIT $offset, $rowCount "; $dao =& CRM_Core_DAO::executeQuery( $sql ); $contribution = array( ); while ( $dao->fetch( ) ) { $contribution[$dao->contribution_id] = $query->store( $dao ); } $dao->free( ); return $contribution; }