X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FContact%2FBAO%2FQuery.php;h=2a65e69528668118eee81a39578cc8bd729d8cc3;hb=fe3416bf598349a6e6c6a64d9c66bfc450198581;hp=e60884b6f1dcbcde8712d86edf89f28eb1f2eb6a;hpb=7cb808dc35fd26dc142b06f3882eec65b22a2b94;p=civicrm-core.git diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index e60884b6f1..2a65e69528 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -413,6 +413,7 @@ class CRM_Contact_BAO_Query { public $_pseudoConstantsSelect = array(); public $_groupUniqueKey = NULL; + public $_groupKeys = []; /** * Class constructor which also does all the work. @@ -538,6 +539,7 @@ class CRM_Contact_BAO_Query { $component = 'membership'; } if (isset($component)) { + // @todo should be if (isset($component && !$this->_skipPermission) CRM_Financial_BAO_FinancialType::buildPermissionedClause($this->_whereClause, $component); } @@ -727,7 +729,7 @@ class CRM_Contact_BAO_Query { if ( !empty($this->_paramLookup[$name]) || !empty($this->_returnProperties[$name]) - || $this->pseudoConstantNameIsInReturnProperties($field) + || $this->pseudoConstantNameIsInReturnProperties($field, $name) || $makeException ) { if ($cfID) { @@ -868,22 +870,10 @@ class CRM_Contact_BAO_Query { $this->_select[$name] = "{$field['where']} as `$name`"; } } + elseif ($this->pseudoConstantNameIsInReturnProperties($field, $name)) { + $this->addPseudoconstantFieldToSelect($name); + } else { - // If we have an option group defined then rather than joining the option value table in - // (which is an unindexed join) we render the option value on output. - // @todo - extend this to other pseudoconstants. - if ($this->pseudoConstantNameIsInReturnProperties($field, $name)) { - $pseudoFieldName = $field['pseudoconstant']['optionGroupName']; - $this->_pseudoConstantsSelect[$pseudoFieldName] = array( - 'pseudoField' => $field['name'], - 'idCol' => $name, - 'field_name' => $field['name'], - 'bao' => $field['bao'], - 'pseudoconstant' => $field['pseudoconstant'], - ); - $this->_tables[$tableName] = 1; - $this->_element[$pseudoFieldName] = 1; - } $this->_select[$name] = str_replace('civicrm_contact.', 'contact_a.', "{$field['where']} as `$name`"); } if (!in_array($tName, array('state_province', 'country', 'county'))) { @@ -1394,7 +1384,7 @@ class CRM_Contact_BAO_Query { } } elseif ($sortByChar) { - $select = 'SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name'; + $select = 'SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name'; $from = $this->_simpleFromClause; } elseif ($groupContacts) { @@ -2041,6 +2031,18 @@ class CRM_Contact_BAO_Query { // check for both id and contact_id if ($this->_params[$id][0] == 'id' || $this->_params[$id][0] == 'contact_id') { $this->_where[0][] = self::buildClause("contact_a.id", $this->_params[$id][1], $this->_params[$id][2]); + $field = CRM_Utils_Array::value('id', $this->_fields); + list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue( + 'CRM_Contact_BAO_Contact', + "contact_a.id", + $this->_params[$id][2], + $this->_params[$id][1] + ); + $this->_qill[0][] = ts("%1 %2 %3", [ + 1 => $field['title'], + 2 => $qillop, + 3 => $qillVal + ]); } else { $this->whereClauseSingle($this->_params[$id], $apiEntity); @@ -3044,6 +3046,7 @@ class CRM_Contact_BAO_Query { $groupContactCacheClause = ''; if (count($smartGroupIDs) || empty($value)) { $this->_groupUniqueKey = uniqid(); + $this->_groupKeys[] = $this->_groupUniqueKey; $gccTableAlias = "civicrm_group_contact_cache_{$this->_groupUniqueKey}"; $groupContactCacheClause = $this->addGroupContactCache($smartGroupIDs, $gccTableAlias, "contact_a", $op); if (!empty($groupContactCacheClause)) { @@ -3075,8 +3078,8 @@ class CRM_Contact_BAO_Query { } } - public function getGroupCacheTableKey() { - return $this->_groupUniqueKey; + public function getGroupCacheTableKeys() { + return $this->_groupKeys; } /** @@ -3909,7 +3912,21 @@ WHERE $smartGroupClause $name = $targetName[4] ? "%$name%" : $name; $this->_where[$grouping][] = "contact_b_log.sort_name LIKE '%$name%'"; $this->_tables['civicrm_log'] = $this->_whereTables['civicrm_log'] = 1; - $this->_qill[$grouping][] = ts('Modified By') . " $name"; + $fieldTitle = ts('Added By'); + foreach ($this->_params as $params) { + if ($params[0] == 'log_date') { + if ($params[2] == 2) { + $fieldTitle = ts('Modified By'); + } + break; + } + } + list($qillop, $qillVal) = self::buildQillForFieldValue(NULL, 'changed_by', $name, 'LIKE'); + $this->_qill[$grouping][] = ts("%1 %2 '%3'", [ + 1 => $fieldTitle, + 2 => $qillop, + 3 => $qillVal, + ]); } /** @@ -4874,97 +4891,7 @@ civicrm_relationship.start_date > {$today} $additionalFromClause = NULL, $skipOrderAndLimit = FALSE ) { - if ($includeContactIds) { - $this->_includeContactIds = TRUE; - $this->_whereClause = $this->whereClause(); - } - - $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params); - - // if we’re explicitly looking for a certain contact’s contribs, events, etc. - // and that contact happens to be deleted, set $onlyDeleted to true - foreach ($this->_params as $values) { - $name = CRM_Utils_Array::value(0, $values); - $op = CRM_Utils_Array::value(1, $values); - $value = CRM_Utils_Array::value(2, $values); - if ($name == 'contact_id' and $op == '=') { - if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) { - $onlyDeleted = TRUE; - } - break; - } - } - - // building the query string - $groupBy = $groupByCols = NULL; - if (!$count) { - if (isset($this->_groupByComponentClause)) { - $groupBy = $this->_groupByComponentClause; - $groupByCols = preg_replace('/^GROUP BY /', '', trim($this->_groupByComponentClause)); - $groupByCols = explode(', ', $groupByCols); - } - elseif ($this->_useGroupBy) { - $groupByCols = array('contact_a.id'); - } - } - if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) { - $groupByCols = array('civicrm_activity.id'); - } - - $order = $orderBy = $limit = ''; - if (!$count) { - list($order, $additionalFromClause) = $this->prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause); - - if ($rowCount > 0 && $offset >= 0) { - $offset = CRM_Utils_Type::escape($offset, 'Int'); - $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); - $limit = " LIMIT $offset, $rowCount "; - } - } - // Two cases where we are disabling FGB (FULL_GROUP_BY_MODE): - // 1. Expecting the search query to return all the first single letter characters of contacts ONLY, but when FGB is enabled - // MySQL expect the columns present in GROUP BY, must be present in SELECT clause and that results into error, needless to have other columns. - // 2. When GROUP BY columns are present then disable FGB otherwise it demands to add ORDER BY columns in GROUP BY and eventually in SELECT - // clause. This will impact the search query output. - $disableFullGroupByMode = ($sortByChar || !empty($groupByCols) || $groupContacts); - - if ($disableFullGroupByMode) { - CRM_Core_DAO::disableFullGroupByMode(); - } - - // CRM-15231 - $this->_sort = $sort; - - //CRM-15967 - $this->includePseudoFieldsJoin($sort); - - list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted); - - if (!empty($groupByCols)) { - $groupBy = " GROUP BY " . implode(', ', $groupByCols); - } - - if ($additionalWhereClause) { - $where = $where . ' AND ' . $additionalWhereClause; - } - - //additional from clause should be w/ proper joins. - if ($additionalFromClause) { - $from .= "\n" . $additionalFromClause; - } - - // if we are doing a transform, do it here - // use the $from, $where and $having to get the contact ID - if ($this->_displayRelationshipType) { - $this->filterRelatedContacts($from, $where, $having); - } - - if ($skipOrderAndLimit) { - $query = "$select $from $where $having $groupBy"; - } - else { - $query = "$select $from $where $having $groupBy $order $limit"; - } + $query = $this->getSearchSQL($offset, $rowCount, $sort, $count, $includeContactIds, $sortByChar, $groupContacts, $additionalWhereClause, $sortOrder, $additionalFromClause, $skipOrderAndLimit); if ($returnQuery) { return $query; @@ -4975,9 +4902,8 @@ civicrm_relationship.start_date > {$today} $dao = CRM_Core_DAO::executeQuery($query); - if ($disableFullGroupByMode) { - CRM_Core_DAO::reenableFullGroupByMode(); - } + // We can always call this - it will only re-enable if it was originally enabled. + CRM_Core_DAO::reenableFullGroupByMode(); if ($groupContacts) { $ids = array(); @@ -4990,6 +4916,22 @@ civicrm_relationship.start_date > {$today} return $dao; } + /** + * Create and query the db for a contact search. + * + * @return CRM_Core_DAO + */ + public function alphabetQuery() { + $query = $this->getSearchSQL(NULL, NULL, NULL, FALSE, FALSE, TRUE); + + $dao = CRM_Core_DAO::executeQuery($query); + + // We can always call this - it will only re-enable if it was originally enabled. + CRM_Core_DAO::reenableFullGroupByMode(); + + return $dao; + } + /** * Fetch a list of contacts for displaying a search results page * @@ -5097,15 +5039,8 @@ civicrm_relationship.start_date > {$today} * * @return array */ - public function &summaryContribution($context = NULL) { + public function summaryContribution($context = NULL) { list($innerselect, $from, $where, $having) = $this->query(TRUE); - - // hack $select - $select = " -SELECT COUNT( conts.total_amount ) as total_count, - SUM( conts.total_amount ) as total_amount, - AVG( conts.total_amount ) as total_avg, - conts.currency as currency"; if ($this->_permissionWhereClause) { $where .= " AND " . $this->_permissionWhereClause; } @@ -5113,133 +5048,16 @@ SELECT COUNT( conts.total_amount ) as total_count, $where .= " AND contact_a.is_deleted = 0 "; } - $query = $this->appendFinancialTypeWhereAndFromToQueryStrings($where, $from); - - // make sure contribution is completed - CRM-4989 - $completedWhere = $where . " AND civicrm_contribution.contribution_status_id = 1 "; - - $summary = array(); - $summary['total'] = array(); - $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a"; - $innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count, - civicrm_contribution.currency $from $completedWhere"; - $query = "$select FROM ( - $innerQuery GROUP BY civicrm_contribution.id - ) as conts - GROUP BY currency"; - - $dao = CRM_Core_DAO::executeQuery($query); - - $summary['total']['count'] = 0; - $summary['total']['amount'] = $summary['total']['avg'] = array(); - while ($dao->fetch()) { - $summary['total']['count'] += $dao->total_count; - $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency); - $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency); - } - - $orderBy = 'ORDER BY civicrm_contribution_total_amount_count DESC'; - $groupBy = 'GROUP BY currency, civicrm_contribution.total_amount'; - $modeSQL = "$select, SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount - ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount, - MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count - FROM ($innerQuery - $groupBy $orderBy) as conts - GROUP BY currency"; - - $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL); + $this->appendFinancialTypeWhereAndFromToQueryStrings($where, $from); - $medianSQL = "{$from} {$completedWhere}"; - $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution'); - $summary['total']['currencyCount'] = count($summary['total']['median']); + $summary = ['total' => []]; + $this->addBasicStatsToSummary($summary, $where, $from); - if (!empty($summary['total']['amount'])) { - $summary['total']['amount'] = implode(', ', $summary['total']['amount']); - $summary['total']['avg'] = implode(', ', $summary['total']['avg']); - $summary['total']['mode'] = implode(', ', $summary['total']['mode']); - $summary['total']['median'] = implode(', ', $summary['total']['median']); - } - else { - $summary['total']['amount'] = $summary['total']['avg'] = $summary['total']['median'] = 0; - } - - // soft credit summary if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) { - $softCreditWhere = "{$completedWhere} AND civicrm_contribution_soft.id IS NOT NULL"; - $query = " - $select FROM ( - SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere - GROUP BY civicrm_contribution_soft.id - ) as conts - GROUP BY currency"; - $dao = CRM_Core_DAO::executeQuery($query); - $summary['soft_credit']['count'] = 0; - $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = array(); - while ($dao->fetch()) { - $summary['soft_credit']['count'] += $dao->total_count; - $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency); - $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency); - } - if (!empty($summary['soft_credit']['amount'])) { - $summary['soft_credit']['amount'] = implode(', ', $summary['soft_credit']['amount']); - $summary['soft_credit']['avg'] = implode(', ', $summary['soft_credit']['avg']); - } - else { - $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0; - } - } - - // hack $select - //@todo - this could be one query using the IF in mysql - eg - // SELECT sum(total_completed), sum(count_completed), sum(count_cancelled), sum(total_cancelled) FROM ( - // SELECT civicrm_contribution.total_amount, civicrm_contribution.currency , - // IF(civicrm_contribution.contribution_status_id = 1, 1, 0 ) as count_completed, - // IF(civicrm_contribution.contribution_status_id = 1, total_amount, 0 ) as total_completed, - // IF(civicrm_contribution.cancel_date IS NOT NULL = 1, 1, 0 ) as count_cancelled, - // IF(civicrm_contribution.cancel_date IS NOT NULL = 1, total_amount, 0 ) as total_cancelled - // FROM civicrm_contact contact_a - // LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id - // WHERE ( ... where clause.... - // AND (civicrm_contribution.cancel_date IS NOT NULL OR civicrm_contribution.contribution_status_id = 1) - // ) as conts - - $select = " -SELECT COUNT( conts.total_amount ) as cancel_count, - SUM( conts.total_amount ) as cancel_amount, - AVG( conts.total_amount ) as cancel_avg, - conts.currency as currency"; - - $where .= " AND civicrm_contribution.cancel_date IS NOT NULL "; - if ($context == 'search') { - $where .= " AND contact_a.is_deleted = 0 "; + $this->addBasicSoftCreditStatsToStats($summary, $where, $from); } - $query = "$select FROM ( - SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where - GROUP BY civicrm_contribution.id - ) as conts - GROUP BY currency"; - - $dao = CRM_Core_DAO::executeQuery($query); - - if ($dao->N <= 1) { - if ($dao->fetch()) { - $summary['cancel']['count'] = $dao->cancel_count; - $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency); - $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency); - } - } - else { - $summary['cancel']['count'] = 0; - $summary['cancel']['amount'] = $summary['cancel']['avg'] = array(); - while ($dao->fetch()) { - $summary['cancel']['count'] += $dao->cancel_count; - $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency); - $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency); - } - $summary['cancel']['amount'] = implode(', ', $summary['cancel']['amount']); - $summary['cancel']['avg'] = implode(', ', $summary['cancel']['avg']); - } + $this->addBasicCancelStatsToSummary($summary, $where, $from); return $summary; } @@ -6431,10 +6249,10 @@ AND displayRelType.is_active = 1 * list(string $orderByClause, string $additionalFromClause). */ protected function prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause) { - $order = NULL; - $orderByArray = array(); - $config = CRM_Core_Config::singleton(); - if ($config->includeOrderByClause || + $orderByArray = []; + $orderBy = ''; + + if (CRM_Core_Config::singleton()->includeOrderByClause || isset($this->_distinctComponentClause) ) { if ($sort) { @@ -6455,39 +6273,31 @@ AND displayRelType.is_active = 1 $orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy); } - $order = " ORDER BY $orderBy"; - if ($sortOrder) { - $order .= " $sortOrder"; + $orderBy .= " $sortOrder"; } // always add contact_a.id to the ORDER clause // so the order is deterministic - if (strpos('contact_a.id', $order) === FALSE) { - $order .= ", contact_a.id"; + if (strpos('contact_a.id', $orderBy) === FALSE) { + $orderBy .= ", contact_a.id"; } } } elseif ($sortByChar) { - $orderByArray = array("UPPER(LEFT(contact_a.sort_name, 1)) asc"); + $orderBy = " sort_name asc"; } else { - $order = " ORDER BY contact_a.sort_name ASC, contact_a.id"; + $orderBy = " contact_a.sort_name ASC, contact_a.id"; } } - if (!$order && empty($orderByArray)) { - return array($order, $additionalFromClause); + if (!$orderBy) { + return [NULL, $additionalFromClause]; } // Remove this here & add it at the end for simplicity. - $order = trim(str_replace('ORDER BY', '', $order)); + $order = trim($orderBy); + $orderByArray = explode(',', $order); - // hack for order clause - if (!empty($orderByArray)) { - $order = implode(', ', $orderByArray); - } - else { - $orderByArray = explode(',', $order); - } foreach ($orderByArray as $orderByClause) { $orderByClauseParts = explode(' ', trim($orderByClause)); $field = $orderByClauseParts[0]; @@ -6524,24 +6334,32 @@ AND displayRelType.is_active = 1 $this->_select = array_merge($this->_select, $this->_customQuery->_select); $this->_tables = array_merge($this->_tables, $this->_customQuery->_tables); } - foreach ($this->_pseudoConstantsSelect as $key => $pseudoConstantMetadata) { - // By replacing the join to the option value table with the mysql construct - // ORDER BY field('contribution_status_id', 2,1,4) - // we can remove a join. In the case of the option value join it is - /// a join known to cause slow queries. - // @todo cover other pseudoconstant types. Limited to option group ones in the - // first instance for scope reasons. They require slightly different handling as the column (label) - // is not declared for them. - // @todo so far only integer fields are being handled. If we add string fields we need to look at - // escaping. - if (isset($pseudoConstantMetadata['pseudoconstant']) - && isset($pseudoConstantMetadata['pseudoconstant']['optionGroupName']) - && $field === CRM_Utils_Array::value('optionGroupName', $pseudoConstantMetadata['pseudoconstant']) + + // By replacing the join to the option value table with the mysql construct + // ORDER BY field('contribution_status_id', 2,1,4) + // we can remove a join. In the case of the option value join it is + /// a join known to cause slow queries. + // @todo cover other pseudoconstant types. Limited to option group ones & Foreign keys + // matching an id+name parrern in the + // first instance for scope reasons. They require slightly different handling as the column (label) + // is not declared for them. + // @todo so far only integer fields are being handled. If we add string fields we need to look at + // escaping. + $fieldSpec = $this->getMetadataForRealField($field); + $pseudoConstantMetadata = CRM_Utils_Array::value('pseudoconstant', $fieldSpec, FALSE); + if (!empty($pseudoConstantMetadata) + ) { + if (!empty($pseudoConstantMetadata['optionGroupName']) + || $this->isPseudoFieldAnFK($fieldSpec) ) { - $sortedOptions = $pseudoConstantMetadata['bao']::buildOptions($pseudoConstantMetadata['pseudoField'], NULL, array( - 'orderColumn' => 'label', - )); - $order = str_replace("$field $direction", "field({$pseudoConstantMetadata['pseudoField']}," . implode(',', array_keys($sortedOptions)) . ") $direction", $order); + $sortedOptions = $fieldSpec['bao']::buildOptions($fieldSpec['name'], NULL, [ + 'orderColumn' => CRM_Utils_Array::value('labelColumn', $pseudoConstantMetadata, 'label'), + ]); + $fieldIDsInOrder = implode(',', array_keys($sortedOptions)); + // Pretty sure this validation ALSO happens in the order clause & this can't be reached but... + // this might give some early warning. + CRM_Utils_Type::validate($fieldIDsInOrder, 'CommaSeparatedIntegers'); + $order = str_replace("$field", "field({$fieldSpec['name']},$fieldIDsInOrder)", $order); } //CRM-12565 add "`" around $field if it is a pseudo constant // This appears to be for 'special' fields like locations with appended numbers or hyphens .. maybe. @@ -6622,18 +6440,26 @@ AND displayRelType.is_active = 1 * @return bool */ private function pseudoConstantNameIsInReturnProperties($field, $fieldName = NULL) { - if (!isset($field['pseudoconstant']['optionGroupName'])) { + $realField = $this->getMetadataForRealField($fieldName); + if (!isset($realField['pseudoconstant'])) { + return FALSE; + } + $pseudoConstant = $realField['pseudoconstant']; + if (empty($pseudoConstant['optionGroupName']) && + CRM_Utils_Array::value('labelColumn', $pseudoConstant) !== 'name') { + // We are increasing our pseudoconstant handling - but still very cautiously, + // hence the check for labelColumn === name return FALSE; } - if (CRM_Utils_Array::value($field['pseudoconstant']['optionGroupName'], $this->_returnProperties)) { + if (!empty($pseudoConstant['optionGroupName']) && CRM_Utils_Array::value($pseudoConstant['optionGroupName'], $this->_returnProperties)) { return TRUE; } if (CRM_Utils_Array::value($fieldName, $this->_returnProperties)) { return TRUE; } // Is this still required - the above goes off the unique name. Test with things like - // communication_prefferences & prefix_id. + // communication_preferences & prefix_id. if (CRM_Utils_Array::value($field['name'], $this->_returnProperties)) { return TRUE; } @@ -6654,4 +6480,401 @@ AND displayRelType.is_active = 1 return $select; } + /** + * Add basic statistics to the summary. + * + * @param array $summary + * @param string $where + * @param string $from + * + * @return array + */ + protected function addBasicStatsToSummary(&$summary, $where, $from) { + $summary['total']['count'] = 0; + $summary['total']['amount'] = $summary['total']['avg'] = []; + + $query = " + SELECT COUNT( conts.total_amount ) as total_count, + SUM( conts.total_amount ) as total_amount, + AVG( conts.total_amount ) as total_avg, + conts.currency as currency + FROM ( + SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count, + civicrm_contribution.currency + $from + $where AND civicrm_contribution.contribution_status_id = 1 + GROUP BY civicrm_contribution.id + ) as conts + GROUP BY currency"; + + $dao = CRM_Core_DAO::executeQuery($query); + + while ($dao->fetch()) { + $summary['total']['count'] += $dao->total_count; + $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency); + $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency); + } + + if (!empty($summary['total']['amount'])) { + $summary['total']['amount'] = implode(', ', $summary['total']['amount']); + $summary['total']['avg'] = implode(', ', $summary['total']['avg']); + } + else { + $summary['total']['amount'] = $summary['total']['avg'] = 0; + } + return $summary; + } + + /** + * Add basic soft credit statistics to summary array. + * + * @param array $summary + * @param string $where + * @param string $from + */ + protected function addBasicSoftCreditStatsToStats(&$summary, $where, $from) { + $query = " + SELECT COUNT( conts.total_amount ) as total_count, + SUM( conts.total_amount ) as total_amount, + AVG( conts.total_amount ) as total_avg, + conts.currency as currency + FROM ( + SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency + $from + $where AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution_soft.id IS NOT NULL + GROUP BY civicrm_contribution_soft.id + ) as conts + GROUP BY currency"; + + $dao = CRM_Core_DAO::executeQuery($query); + $summary['soft_credit']['count'] = 0; + $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = []; + while ($dao->fetch()) { + $summary['soft_credit']['count'] += $dao->total_count; + $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency); + $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency); + } + if (!empty($summary['soft_credit']['amount'])) { + $summary['soft_credit']['amount'] = implode(', ', $summary['soft_credit']['amount']); + $summary['soft_credit']['avg'] = implode(', ', $summary['soft_credit']['avg']); + } + else { + $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0; + } + } + + /** + * Add basic stats about cancelled contributions to the summary. + * + * @param array $summary + * @param string $where + * @param string $from + */ + protected function addBasicCancelStatsToSummary(&$summary, $where, $from) { + $query = " + SELECT COUNT( conts.total_amount ) as cancel_count, + SUM( conts.total_amount ) as cancel_amount, + AVG( conts.total_amount ) as cancel_avg, + conts.currency as currency + FROM ( + SELECT civicrm_contribution.total_amount, civicrm_contribution.currency + $from + $where AND civicrm_contribution.cancel_date IS NOT NULL + GROUP BY civicrm_contribution.id + ) as conts + GROUP BY currency"; + + $dao = CRM_Core_DAO::executeQuery($query); + + if ($dao->N <= 1) { + if ($dao->fetch()) { + $summary['cancel']['count'] = $dao->cancel_count; + $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency); + $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency); + } + } + else { + $summary['cancel']['count'] = 0; + $summary['cancel']['amount'] = $summary['cancel']['avg'] = []; + while ($dao->fetch()) { + $summary['cancel']['count'] += $dao->cancel_count; + $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency); + $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency); + } + $summary['cancel']['amount'] = implode(', ', $summary['cancel']['amount']); + $summary['cancel']['avg'] = implode(', ', $summary['cancel']['avg']); + } + } + + /** + * Create the sql query for an contact search. + * + * @param int $offset + * The offset for the query. + * @param int $rowCount + * The number of rows to return. + * @param string|CRM_Utils_Sort $sort + * The order by string. + * @param bool $count + * Is this a count only query ?. + * @param bool $includeContactIds + * Should we include contact ids?. + * @param bool $sortByChar + * If true returns the distinct array of first characters for search results. + * @param bool $groupContacts + * If true, return only the contact ids. + * @param string $additionalWhereClause + * If the caller wants to further restrict the search (used for components). + * @param null $sortOrder + * @param string $additionalFromClause + * Should be clause with proper joins, effective to reduce where clause load. + * + * @param bool $skipOrderAndLimit + * @return string + */ + public function getSearchSQL( + $offset = 0, $rowCount = 0, $sort = NULL, + $count = FALSE, $includeContactIds = FALSE, + $sortByChar = FALSE, $groupContacts = FALSE, + $additionalWhereClause = NULL, $sortOrder = NULL, + $additionalFromClause = NULL, $skipOrderAndLimit = FALSE) { + + $sqlParts = $this->getSearchSQLParts($offset, $rowCount, $sort, $count, $includeContactIds, $sortByChar, $groupContacts, $additionalWhereClause, $sortOrder, $additionalFromClause); + + if ($skipOrderAndLimit) { + CRM_Core_Error::deprecatedFunctionWarning('skipOrderAndLimit is deprected - call getSearchSQLParts & construct it in the calling function'); + $query = "{$sqlParts['select']} {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['having']} {$sqlParts['group_by']}"; + } + else { + $query = "{$sqlParts['select']} {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['having']} {$sqlParts['group_by']} {$sqlParts['order_by']} {$sqlParts['limit']}"; + } + return $query; + } + + /** + * Get the component parts of the search query as an array. + * + * @param int $offset + * The offset for the query. + * @param int $rowCount + * The number of rows to return. + * @param string|CRM_Utils_Sort $sort + * The order by string. + * @param bool $count + * Is this a count only query ?. + * @param bool $includeContactIds + * Should we include contact ids?. + * @param bool $sortByChar + * If true returns the distinct array of first characters for search results. + * @param bool $groupContacts + * If true, return only the contact ids. + * @param string $additionalWhereClause + * If the caller wants to further restrict the search (used for components). + * @param null $sortOrder + * @param string $additionalFromClause + * Should be clause with proper joins, effective to reduce where clause load. + * + * @return array + */ + public function getSearchSQLParts($offset = 0, $rowCount = 0, $sort = NULL, + $count = FALSE, $includeContactIds = FALSE, + $sortByChar = FALSE, $groupContacts = FALSE, + $additionalWhereClause = NULL, $sortOrder = NULL, + $additionalFromClause = NULL) { + if ($includeContactIds) { + $this->_includeContactIds = TRUE; + $this->_whereClause = $this->whereClause(); + } + $onlyDeleted = in_array([ + 'deleted_contacts', + '=', + '1', + '0', + '0' + ], $this->_params); + + // if we’re explicitly looking for a certain contact’s contribs, events, etc. + // and that contact happens to be deleted, set $onlyDeleted to true + foreach ($this->_params as $values) { + $name = CRM_Utils_Array::value(0, $values); + $op = CRM_Utils_Array::value(1, $values); + $value = CRM_Utils_Array::value(2, $values); + if ($name == 'contact_id' and $op == '=') { + if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) { + $onlyDeleted = TRUE; + } + break; + } + } + + // building the query string + $groupBy = $groupByCols = NULL; + if (!$count) { + if (isset($this->_groupByComponentClause)) { + $groupByCols = preg_replace('/^GROUP BY /', '', trim($this->_groupByComponentClause)); + $groupByCols = explode(', ', $groupByCols); + } + elseif ($this->_useGroupBy) { + $groupByCols = ['contact_a.id']; + } + } + if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) { + $groupByCols = ['civicrm_activity.id']; + } + if (!empty($groupByCols)) { + $groupBy = " GROUP BY " . implode(', ', $groupByCols); + } + + $order = $orderBy = ''; + if (!$count) { + list($order, $additionalFromClause) = $this->prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause); + } + // Two cases where we are disabling FGB (FULL_GROUP_BY_MODE): + // 1. Expecting the search query to return all the first single letter characters of contacts ONLY, but when FGB is enabled + // MySQL expect the columns present in GROUP BY, must be present in SELECT clause and that results into error, needless to have other columns. + // 2. When GROUP BY columns are present then disable FGB otherwise it demands to add ORDER BY columns in GROUP BY and eventually in SELECT + // clause. This will impact the search query output. + $disableFullGroupByMode = ($sortByChar || !empty($groupBy) || $groupContacts); + + if ($disableFullGroupByMode) { + CRM_Core_DAO::disableFullGroupByMode(); + } + + // CRM-15231 + $this->_sort = $sort; + + //CRM-15967 + $this->includePseudoFieldsJoin($sort); + + list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted); + + if ($additionalWhereClause) { + $where = $where . ' AND ' . $additionalWhereClause; + } + + //additional from clause should be w/ proper joins. + if ($additionalFromClause) { + $from .= "\n" . $additionalFromClause; + } + + // if we are doing a transform, do it here + // use the $from, $where and $having to get the contact ID + if ($this->_displayRelationshipType) { + $this->filterRelatedContacts($from, $where, $having); + } + $limit = (!$count && $rowCount) ? " LIMIT " . CRM_Utils_Type::escape($offset, 'Int') . ", " . CRM_Utils_Type::escape($rowCount, 'Int') : ''; + + return [ + 'select' => $select, + 'from' => $from, + 'where' => $where, + 'order_by' => $order, + 'group_by' => $groupBy, + 'having' => $having, + 'limit' => $limit, + ]; + } + + /** + * Get the metadata for a given field. + * + * @param string $fieldName + * + * @return array + */ + protected function getMetadataForField($fieldName) { + if ($fieldName === 'contact_a.id') { + // This seems to be the only anomaly. + $fieldName = 'id'; + } + $pseudoField = isset($this->_pseudoConstantsSelect[$fieldName]) ? $this->_pseudoConstantsSelect[$fieldName] : []; + $field = isset($this->_fields[$fieldName]) ? $this->_fields[$fieldName] : $pseudoField; + $field = array_merge($field, $pseudoField); + if (!empty($field) && empty($field['name'])) { + // standardising field formatting here - over time we can phase out variants. + // all paths using this currently unit tested + $field['name'] = CRM_Utils_Array::value('field_name', $field, CRM_Utils_Array::value('idCol', $field, $fieldName)); + } + return $field; + } + + /** + * Get the metadata for a given field, returning the 'real field' if it is a pseudofield. + * + * @param string $fieldName + * + * @return array + */ + protected function getMetadataForRealField($fieldName) { + $field = $this->getMetadataForField($fieldName); + if (!empty($field['is_pseudofield_for'])) { + $field = $this->getMetadataForField($field['is_pseudofield_for']); + $field['pseudofield_name'] = $fieldName; + } + elseif (!empty($field['pseudoconstant'])) { + if (!empty($field['pseudoconstant']['optionGroupName'])) { + $field['pseudofield_name'] = $field['pseudoconstant']['optionGroupName']; + if (empty($field['table_name'])) { + if (!empty($field['where'])) { + $field['table_name'] = explode('.', $field['where'])[0]; + } + else { + $field['table_name'] = 'civicrm_contact'; + } + } + } + } + return $field; + } + + /** + * If we have a field that is better rendered via the pseudoconstant handled them here. + * + * Rather than joining in the additional table we render the option value on output. + * + * @todo - so far this applies to a narrow range of pseudocontants. We are adding them + * carefully with test coverage but aim to extend. + * + * @param string $name + */ + protected function addPseudoconstantFieldToSelect($name) { + $field = $this->getMetadataForRealField($name); + $realFieldName = $field['name']; + $pseudoFieldName = CRM_Utils_Array::value('pseudofield_name', $field); + if ($pseudoFieldName) { + // @todo - we don't really need to build this array now we have metadata more available with getMetadataForField fn. + $this->_pseudoConstantsSelect[$pseudoFieldName] = [ + 'pseudoField' => $pseudoFieldName, + 'idCol' => $realFieldName, + 'field_name' => $field['name'], + 'bao' => $field['bao'], + 'pseudoconstant' => $field['pseudoconstant'], + ]; + } + + $this->_tables[$field['table_name']] = 1; + $this->_element[$realFieldName] = 1; + $this->_select[$field['name']] = str_replace('civicrm_contact.', 'contact_a.', "{$field['where']} as `$realFieldName`"); + } + + /** + * Is this pseudofield a foreign key constraint. + * + * We are trying to cautiously expand our pseudoconstant handling. This check allows us + * to extend to a narrowly defined type (and then only if the pseudofield is in the fields + * array which is done for contributions which are mostly handled as pseudoconstants. + * + * @param $fieldSpec + * + * @return bool + */ + protected function isPseudoFieldAnFK($fieldSpec) { + if (empty($fieldSpec['FKClassName']) + || CRM_Utils_Array::value('keyColumn', $fieldSpec['pseudoconstant']) !== 'id' + || CRM_Utils_Array::value('labelColumn', $fieldSpec['pseudoconstant']) !== 'name') { + return FALSE; + } + return TRUE; + } + }