X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FReport%2FForm.php;h=350ab376f20008b52170a3c180b9d69fc5a56a89;hb=c160fde801c39de0dcc1f57f9612b9f39d98caf7;hp=9426e0f9ba947b6cbb4194b439cd69edb92a7e31;hpb=f45db615b80f44b71fdf06573f57eef44c8de493;p=civicrm-core.git diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index 9426e0f9ba..350ab376f2 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -296,12 +296,21 @@ class CRM_Report_Form extends CRM_Core_Form { protected $_aclWhere = NULL; /** - * Array of DAO tables having columns included in SELECT or ORDER BY clause + * Array of DAO tables having columns included in SELECT or ORDER BY clause. + * + * Where has also been added to this although perhaps the 'includes both' array should have a different name. * * @var array */ protected $_selectedTables; + /** + * Array of DAO tables having columns included in WHERE or HAVING clause + * + * @var array + */ + protected $filteredTables; + /** * Output mode e.g 'print', 'csv', 'pdf'. * @@ -414,6 +423,16 @@ class CRM_Report_Form extends CRM_Core_Form { */ protected $sql; + + /** + * SQL being run in this report as an array. + * + * The sql in the report is stored in this variable in order to be returned to api & test calls. + * + * @var string + */ + + protected $sqlArray; /** * Class constructor. */ @@ -1274,6 +1293,7 @@ class CRM_Report_Form extends CRM_Core_Form { ); $this->assignTabs(); + $this->sqlArray[] = $sql; foreach (array('LEFT JOIN') as $term) { $sql = str_replace($term, '
  ' . $term, $sql); } @@ -2129,9 +2149,13 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND * @return bool */ public function grandTotal(&$rows) { - if (!$this->_rollup || ($this->_rollup == '' || count($rows) == 1) || - ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT) - ) { + if (!$this->_rollup || count($rows) == 1) { + return FALSE; + } + + $this->moveSummaryColumnsToTheRightHandSide(); + + if ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT) { return FALSE; } @@ -2140,7 +2164,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND foreach ($this->_columnHeaders as $fld => $val) { if (!in_array($fld, $this->_statFields)) { if (!$this->_grandFlag) { - $this->rollupRow[$fld] = "Grand Total"; + $this->rollupRow[$fld] = ts('Grand Total'); $this->_grandFlag = TRUE; } else { @@ -2701,7 +2725,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND } if (!empty($orderByField)) { - $this->_orderByFields[] = $orderByField; + $this->_orderByFields[$orderByField['tplField']] = $orderByField; $orderBys[] = "{$orderByField['dbAlias']} {$orderBy['order']}"; // Record any section headers for assignment to the template @@ -2724,22 +2748,8 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND * @return array */ public function unselectedSectionColumns() { - $selectColumns = array(); - foreach ($this->_columns as $tableName => $table) { - if (array_key_exists('fields', $table)) { - foreach ($table['fields'] as $fieldName => $field) { - if (!empty($field['required']) || - !empty($this->_params['fields'][$fieldName]) - ) { - - $selectColumns["{$tableName}_{$fieldName}"] = 1; - } - } - } - } - if (is_array($this->_sections)) { - return array_diff_key($this->_sections, $selectColumns); + return array_diff_key($this->_sections, $this->getSelectColumns()); } else { return array(); @@ -2855,6 +2865,17 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND // use this method to modify $this->_columnHeaders } + /** + * Move totals columns to the right edge of the table. + * + * It seems like a more logical layout to have any totals columns on the far right regardless of + * the location of the rest of their table. + */ + public function moveSummaryColumnsToTheRightHandSide() { + $statHeaders = (array_intersect_key($this->_columnHeaders, array_flip($this->_statFields))); + $this->_columnHeaders = array_merge(array_diff_key($this->_columnHeaders, $statHeaders), $this->_columnHeaders, $statHeaders); + } + /** * Assign rows to the template. * @@ -2880,6 +2901,8 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND $count = count($rows); // Why do we increment the count for rollup seems to artificially inflate the count. + // It seems perhaps intentional to include the summary row in the count of results - although + // this just seems odd. if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) { $count++; } @@ -3178,6 +3201,15 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND return $this->_resultSet; } + /** + * Get the sql used to generate the report. + * + * @return string + */ + public function getReportSql() { + return $this->sqlArray; + } + /** * Use the form name to create the tpl file name. * @@ -3290,15 +3322,16 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND * @param int $rowCount */ public function setPager($rowCount = self::ROW_COUNT_LIMIT) { - // CRM-14115, over-ride row count if rowCount is specified in URL if ($this->_dashBoardRowCount) { $rowCount = $this->_dashBoardRowCount; } if ($this->_limit && ($this->_limit != '')) { - $sql = "SELECT FOUND_ROWS();"; - $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql); + if (!$this->_rowsFound) { + $sql = "SELECT FOUND_ROWS();"; + $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql); + } $params = array( 'total' => $this->_rowsFound, 'rowCount' => $rowCount, @@ -3556,6 +3589,7 @@ ORDER BY cg.weight, cf.weight"; case 'Money': $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT; $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY; + $curFields[$fieldName]['type'] = CRM_Utils_Type::T_MONEY; break; case 'Float': @@ -3608,21 +3642,22 @@ ORDER BY cg.weight, cf.weight"; /** * Build custom data from clause. + * + * @param bool $joinsForFiltersOnly + * Only include joins to support filters. This would be used if creating a table of contacts to include first. */ - public function customDataFrom() { + public function customDataFrom($joinsForFiltersOnly = FALSE) { if (empty($this->_customGroupExtends)) { return; } $mapper = CRM_Core_BAO_CustomQuery::$extendsMap; + $customTables = explode(',', CRM_Core_DAO::singleValueQuery("SELECT GROUP_CONCAT(table_name) FROM civicrm_custom_group")); foreach ($this->_columns as $table => $prop) { - if (substr($table, 0, 13) == 'civicrm_value' || - substr($table, 0, 12) == 'custom_value' - ) { + if (in_array($table, $customTables)) { $extendsTable = $mapper[$prop['extends']]; - - // check field is in params - if (!$this->isFieldSelected($prop)) { + // Check field is required for rendering the report. + if ((!$this->isFieldSelected($prop)) || ($joinsForFiltersOnly && !$this->isFieldFiltered($prop))) { continue; } $baseJoin = CRM_Utils_Array::value($prop['extends'], $this->_customGroupExtendsJoin, "{$this->_aliases[$extendsTable]}.id"); @@ -3697,7 +3732,18 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a } } } + return $this->isFieldFiltered($prop); + } + + /** + * Check if the field is used as a filter. + * + * @param string $prop + * + * @return bool + */ + protected function isFieldFiltered($prop) { if (!empty($prop['filters']) && $this->_customGroupFilters) { foreach ($prop['filters'] as $fieldAlias => $val) { foreach (array( @@ -3783,6 +3829,22 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a return in_array($tableName, $this->selectedTables()); } + /** + * Check if table name has columns in WHERE or HAVING clause. + * + * @param string $tableName + * Name of table (index of $this->_columns array). + * + * @return bool + */ + public function isTableFiltered($tableName) { + // Cause the array to be generated if not previously done. + if (!$this->_selectedTables && !$this->filteredTables) { + $this->selectedTables(); + } + return in_array($tableName, $this->filteredTables); + } + /** * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause. * @@ -3830,6 +3892,7 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a 'nnll' ) { $this->_selectedTables[] = $tableName; + $this->filteredTables[] = $tableName; break; } } @@ -4019,15 +4082,17 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a if (array_key_exists('civicrm_address_country_id', $row)) { if ($value = $row['civicrm_address_country_id']) { $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE); - $url = CRM_Report_Utils_Report::getNextUrl($baseUrl, - "reset=1&force=1&{$criteriaQueryParams}&" . - "country_id_op=in&country_id_value={$value}", - $this->_absoluteUrl, $this->_id - ); - $rows[$rowNum]['civicrm_address_country_id_link'] = $url; - $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.", - array(1 => $linkText) - ); + if ($baseUrl) { + $url = CRM_Report_Utils_Report::getNextUrl($baseUrl, + "reset=1&force=1&{$criteriaQueryParams}&" . + "country_id_op=in&country_id_value={$value}", + $this->_absoluteUrl, $this->_id + ); + $rows[$rowNum]['civicrm_address_country_id_link'] = $url; + $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.", + array(1 => $linkText) + ); + } } $entryFound = TRUE; @@ -4035,15 +4100,17 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a if (array_key_exists('civicrm_address_county_id', $row)) { if ($value = $row['civicrm_address_county_id']) { $rows[$rowNum]['civicrm_address_county_id'] = CRM_Core_PseudoConstant::county($value, FALSE); - $url = CRM_Report_Utils_Report::getNextUrl($baseUrl, - "reset=1&force=1&{$criteriaQueryParams}&" . - "county_id_op=in&county_id_value={$value}", - $this->_absoluteUrl, $this->_id - ); - $rows[$rowNum]['civicrm_address_county_id_link'] = $url; - $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.", - array(1 => $linkText) - ); + if ($baseUrl) { + $url = CRM_Report_Utils_Report::getNextUrl($baseUrl, + "reset=1&force=1&{$criteriaQueryParams}&" . + "county_id_op=in&county_id_value={$value}", + $this->_absoluteUrl, $this->_id + ); + $rows[$rowNum]['civicrm_address_county_id_link'] = $url; + $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.", + array(1 => $linkText) + ); + } } $entryFound = TRUE; } @@ -4051,15 +4118,16 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a if (array_key_exists('civicrm_address_state_province_id', $row)) { if ($value = $row['civicrm_address_state_province_id']) { $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE); - - $url = CRM_Report_Utils_Report::getNextUrl($baseUrl, - "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}", - $this->_absoluteUrl, $this->_id - ); - $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url; - $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.", - array(1 => $linkText) - ); + if ($baseUrl) { + $url = CRM_Report_Utils_Report::getNextUrl($baseUrl, + "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}", + $this->_absoluteUrl, $this->_id + ); + $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url; + $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.", + array(1 => $linkText) + ); + } } $entryFound = TRUE; } @@ -4087,13 +4155,24 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a if (array_key_exists('civicrm_contact_' . $fieldName, $row)) { if (($value = $row['civicrm_contact_' . $fieldName]) != FALSE) { $rows[$rowNum]['civicrm_contact_' . $fieldName] = CRM_Core_Pseudoconstant::getLabel('CRM_Contact_BAO_Contact', $fieldName, $value); - if (($title = CRM_Utils_Array::value($fieldName, $addLinks)) != FALSE) { + if ($baseUrl && ($title = CRM_Utils_Array::value($fieldName, $addLinks)) != FALSE) { $this->addLinkToRow($rows[$rowNum], $baseUrl, $linkText, $value, $fieldName, 'civicrm_contact', $title); } } $entryFound = TRUE; } } + $yesNoFields = array( + 'do_not_email', 'is_deceased', 'do_not_phone', 'do_not_sms', 'do_not_mail', 'is_opt_out', + ); + foreach ($yesNoFields as $fieldName) { + if (array_key_exists('civicrm_contact_' . $fieldName, $row)) { + // Since these are essentially 'negative fields' it feels like it + // makes sense to only highlight the exceptions hence no 'No'. + $rows[$rowNum]['civicrm_contact_' . $fieldName] = !empty($rows[$rowNum]['civicrm_contact_' . $fieldName]) ? ts('Yes') : ''; + $entryFound = TRUE; + } + } return $entryFound; } @@ -4270,6 +4349,12 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a 'external_identifier' => array( 'title' => ts('Contact identifier from external system'), ), + 'do_not_email' => array(), + 'do_not_phone' => array(), + 'do_not_mail' => array(), + 'do_not_sms' => array(), + 'is_opt_out' => array(), + 'is_deceased' => array(), ); } @@ -4511,4 +4596,52 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a $this->assign('sections', $this->_sections); } + /** + * Get an array of the columns that have been selected for display. + * + * @return array + */ + public function getSelectColumns() { + $selectColumns = array(); + foreach ($this->_columns as $tableName => $table) { + if (array_key_exists('fields', $table)) { + foreach ($table['fields'] as $fieldName => $field) { + if (!empty($field['required']) || + !empty($this->_params['fields'][$fieldName]) + ) { + + $selectColumns["{$tableName}_{$fieldName}"] = 1; + } + } + } + } + return $selectColumns; + } + + /** + * Add location tables to the query if they are used for filtering. + * + * This is for when we are running the query separately for filtering and retrieving display fields. + */ + public function selectivelyAddLocationTablesJoinsToFilterQuery() { + if ($this->isTableFiltered('civicrm_email')) { + $this->_from .= " + LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id + AND {$this->_aliases['civicrm_email']}.is_primary = 1"; + } + if ($this->isTableFiltered('civicrm_phone')) { + $this->_from .= " + LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id + AND {$this->_aliases['civicrm_phone']}.is_primary = 1"; + } + if ($this->isTableFiltered('civicrm_address')) { + $this->_from .= " + LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} + ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id) + AND {$this->_aliases['civicrm_address']}.is_primary = 1\n"; + } + } + }