X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FReport%2FForm.php;h=350ab376f20008b52170a3c180b9d69fc5a56a89;hb=c160fde801c39de0dcc1f57f9612b9f39d98caf7;hp=d8adc66607b541c0a50792864246f5274e2abd85;hpb=f4fd60824eaffdef96b7ddea93ce8b48d340b9ef;p=civicrm-core.git diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index d8adc66607..350ab376f2 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -176,6 +176,13 @@ class CRM_Report_Form extends CRM_Core_Form { */ protected $tabs = array(); + /** + * Should we add paging. + * + * @var bool + */ + protected $addPaging = TRUE; + /** * An attribute for checkbox/radio form field layout * @@ -237,7 +244,8 @@ class CRM_Report_Form extends CRM_Core_Form { * This can be set to specify a limit to the number of rows * Since it is currently envisaged as part of the api usage it is only being applied * when $_output mode is not 'html' or 'group' so as not to have to interpret / mess with that part - * of the code (see limit() fn + * of the code (see limit() fn. + * * @var integer */ protected $_limitValue = NULL; @@ -288,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'. * @@ -390,6 +407,13 @@ class CRM_Report_Form extends CRM_Core_Form { */ protected $_createNew; + /** + * When a grand total row has calculated the status we pop it off to here. + * + * This allows us to access it from the stats function and avoid recalculating. + */ + protected $rollupRow = array(); + /** * SQL being run in this report. * @@ -399,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. */ @@ -522,11 +556,22 @@ class CRM_Report_Form extends CRM_Core_Form { $this->_formValues = NULL; } + $this->setOutputMode(); + + if ($this->_outputMode == 'copy') { + $this->_createNew = TRUE; + $this->_params = $this->_formValues; + $this->_params['view_mode'] = 'criteria'; + $this->_params['title'] = ts('(copy)') . $this->getTitle(); + // Do not pass go. Do not collect another chance to re-run the same query. + CRM_Report_Form_Instance::postProcess($this); + } + // lets always do a force if reset is found in the url. // Hey why not? see CRM-17225 for more about this. The use of reset to be force is historical for reasons stated // in the comment line above these 2. - if (!empty($_REQUEST['reset']) && CRM_Utils_Request::retrieve('output', 'String') != 'criteria' - && CRM_Utils_Request::retrieve('output', 'String') != 'save') { + if (!empty($_REQUEST['reset']) + && !in_array(CRM_Utils_Request::retrieve('output', 'String'), array('save', 'criteria'))) { $this->_force = 1; } @@ -991,6 +1036,15 @@ class CRM_Report_Form extends CRM_Core_Form { $this->_offsetValue = $_offsetValue; } + /** + * Setter for $addPaging. + * + * @param bool $value + */ + public function setAddPaging($value) { + $this->addPaging = $value; + } + /** * Getter for $_defaultValues. * @@ -1239,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); } @@ -2003,21 +2058,11 @@ class CRM_Report_Form extends CRM_Core_Form { return; } - $customFields = $fieldValueMap = array(); - $customFieldCols = array( - 'column_name', - 'data_type', - 'html_type', - 'option_group_id', - 'id', - ); - // skip for type date and ContactReference since date format is already handled $query = " -SELECT cg.table_name, cf." . implode(", cf.", $customFieldCols) . ", ov.value, ov.label +SELECT cg.table_name, cf.id FROM civicrm_custom_field cf INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id -LEFT JOIN civicrm_option_value ov ON cf.option_group_id = ov.option_group_id WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND cg.is_active = 1 AND cf.is_active = 1 AND @@ -2027,13 +2072,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { - foreach ($customFieldCols as $key) { - $customFields[$dao->table_name . '_custom_' . - $dao->id][$key] = $dao->$key; - } - if ($dao->option_group_id) { - $fieldValueMap[$dao->option_group_id][$dao->value] = $dao->label; - } + $customFields[$dao->table_name . '_custom_' . $dao->id] = $dao->id; } $dao->free(); @@ -2041,7 +2080,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND foreach ($rows as $rowNum => $row) { foreach ($row as $tableCol => $val) { if (array_key_exists($tableCol, $customFields)) { - $rows[$rowNum][$tableCol] = $this->formatCustomValues($val, $customFields[$tableCol], $fieldValueMap); + $rows[$rowNum][$tableCol] = CRM_Core_BAO_CustomField::displayValue($val, $customFields[$tableCol]); $entryFound = TRUE; } } @@ -2054,128 +2093,6 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND } } - /** - * Format custom values. - * - * @param mixed $value - * @param array $customField - * @param array $fieldValueMap - * - * @return float|string|void - */ - public function formatCustomValues($value, $customField, $fieldValueMap) { - if (CRM_Utils_System::isNull($value)) { - return NULL; - } - - $htmlType = $customField['html_type']; - - switch ($customField['data_type']) { - case 'Boolean': - if ($value == '1') { - $retValue = ts('Yes'); - } - else { - $retValue = ts('No'); - } - break; - - case 'Link': - $retValue = CRM_Utils_System::formatWikiURL($value); - break; - - case 'File': - $retValue = $value; - break; - - case 'Memo': - $retValue = $value; - break; - - case 'Float': - if ($htmlType == 'Text') { - $retValue = (float) $value; - break; - } - case 'Money': - if ($htmlType == 'Text') { - $retValue = CRM_Utils_Money::format($value, NULL, '%a'); - break; - } - case 'String': - case 'Int': - if (in_array($htmlType, array( - 'Text', - 'TextArea', - ))) { - $retValue = $value; - break; - } - case 'StateProvince': - case 'Country': - - switch ($htmlType) { - case 'Multi-Select Country': - $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value); - $customData = array(); - foreach ($value as $val) { - if ($val) { - $customData[] = CRM_Core_PseudoConstant::country($val, FALSE); - } - } - $retValue = implode(', ', $customData); - break; - - case 'Select Country': - $retValue = CRM_Core_PseudoConstant::country($value, FALSE); - break; - - case 'Select State/Province': - $retValue = CRM_Core_PseudoConstant::stateProvince($value, FALSE); - break; - - case 'Multi-Select State/Province': - $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value); - $customData = array(); - foreach ($value as $val) { - if ($val) { - $customData[] = CRM_Core_PseudoConstant::stateProvince($val, FALSE); - } - } - $retValue = implode(', ', $customData); - break; - - case 'Select': - case 'Radio': - case 'Autocomplete-Select': - $retValue = $fieldValueMap[$customField['option_group_id']][$value]; - break; - - case 'CheckBox': - case 'AdvMulti-Select': - case 'Multi-Select': - $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value); - $customData = array(); - foreach ($value as $val) { - if ($val) { - $customData[] = $fieldValueMap[$customField['option_group_id']][$val]; - } - } - $retValue = implode(', ', $customData); - break; - - default: - $retValue = $value; - } - break; - - default: - $retValue = $value; - } - - return $retValue; - } - /** * Remove duplicate rows. * @@ -2232,26 +2149,31 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND * @return bool */ public function grandTotal(&$rows) { - if (!$this->_rollup || ($this->_rollup == '') || - ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT) - ) { + if (!$this->_rollup || count($rows) == 1) { return FALSE; } - $lastRow = array_pop($rows); + + $this->moveSummaryColumnsToTheRightHandSide(); + + if ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT) { + return FALSE; + } + + $this->rollupRow = array_pop($rows); foreach ($this->_columnHeaders as $fld => $val) { if (!in_array($fld, $this->_statFields)) { if (!$this->_grandFlag) { - $lastRow[$fld] = "Grand Total"; + $this->rollupRow[$fld] = ts('Grand Total'); $this->_grandFlag = TRUE; } else { - $lastRow[$fld] = ""; + $this->rollupRow[$fld] = ""; } } } - $this->assign('grandStat', $lastRow); + $this->assign('grandStat', $this->rollupRow); return TRUE; } @@ -2589,13 +2511,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND * Set output mode. */ public function processReportMode() { - $buttonName = $this->controller->getButtonName(); - - $output = CRM_Utils_Request::retrieve( - 'output', - 'String', - CRM_Core_DAO::$_nullObject - ); + $this->setOutputMode(); $this->_sendmail = CRM_Utils_Request::retrieve( @@ -2608,44 +2524,40 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND $printOnly = FALSE; $this->assign('printOnly', FALSE); - if ($this->_printButtonName == $buttonName || $output == 'print' || - ($this->_sendmail && !$output) + if ($this->_outputMode == 'print' || + ($this->_sendmail && !$this->_outputMode) ) { $this->assign('printOnly', TRUE); $printOnly = TRUE; + $this->addPaging = FALSE; $this->assign('outputMode', 'print'); $this->_outputMode = 'print'; if ($this->_sendmail) { $this->_absoluteUrl = TRUE; } } - elseif ($this->_pdfButtonName == $buttonName || $output == 'pdf') { - $this->assign('printOnly', TRUE); + elseif ($this->_outputMode == 'pdf') { $printOnly = TRUE; - $this->assign('outputMode', 'pdf'); - $this->_outputMode = 'pdf'; + $this->addPaging = FALSE; $this->_absoluteUrl = TRUE; } - elseif ($this->_csvButtonName == $buttonName || $output == 'csv') { - $this->assign('printOnly', TRUE); + elseif ($this->_outputMode == 'csv') { $printOnly = TRUE; - $this->assign('outputMode', 'csv'); - $this->_outputMode = 'csv'; $this->_absoluteUrl = TRUE; + $this->addPaging = FALSE; } - elseif ($this->_groupButtonName == $buttonName || $output == 'group') { + elseif ($this->_outputMode == 'group') { $this->assign('outputMode', 'group'); - $this->_outputMode = 'group'; } - elseif ($output == 'create_report' && $this->_criteriaForm) { + elseif ($this->_outputMode == 'create_report' && $this->_criteriaForm) { $this->assign('outputMode', 'create_report'); - $this->_outputMode = 'create_report'; } - else { - $this->assign('outputMode', 'html'); - $this->_outputMode = 'html'; + elseif ($this->_outputMode == 'copy' && $this->_criteriaForm) { + $this->_createNew = TRUE; } + $this->assign('outputMode', $this->_outputMode); + $this->assign('printOnly', $printOnly); // Get today's date to include in printed reports if ($printOnly) { $reportDate = CRM_Utils_Date::customFormat(date('Y-m-d H:i')); @@ -2657,10 +2569,10 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND * Post Processing function for Form. * * postProcessCommon should be used to set other variables from input as the api accesses that function. + * This function is not accessed when the api calls the report. */ public function beginPostProcess() { $this->setParams($this->controller->exportValues($this->_name)); - if (empty($this->_params) && $this->_force ) { @@ -2685,7 +2597,16 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND ) { $this->assign('updateReportButton', TRUE); } + $this->processReportMode(); + + if ($this->_outputMode == 'save' || $this->_outputMode == 'copy') { + $this->_createNew = ($this->_outputMode == 'copy'); + // Do not pass go. Do not collect another chance to re-run the same query. + // This will be called from the button - there is an earlier response to the url + // perhaps they can still be consolidated more. + CRM_Report_Form_Instance::postProcess($this); + } $this->beginPostProcessCommon(); } @@ -2804,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 @@ -2827,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(); @@ -2958,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. * @@ -2982,7 +2900,9 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND $statistics = array(); $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++; } @@ -3051,7 +2971,8 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND foreach ($this->_columns as $tableName => $table) { if (array_key_exists('filters', $table)) { foreach ($table['filters'] as $fieldName => $field) { - if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE && + if ((CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE || + CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_TIME) && CRM_Utils_Array::value('operatorType', $field) != CRM_Report_Form::OP_MONTH ) { @@ -3260,15 +3181,6 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND $group = $this->_params['groups']; $this->add2group($group); } - elseif ($this->_instanceButtonName == $this->controller->getButtonName()) { - CRM_Report_Form_Instance::postProcess($this); - } - elseif ($this->_createNewButtonName == $this->controller->getButtonName() || - $this->_outputMode == 'create_report' - ) { - $this->_createNew = TRUE; - CRM_Report_Form_Instance::postProcess($this); - } } /** @@ -3289,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. * @@ -3358,7 +3279,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND if ($this->_dashBoardRowCount) { $rowCount = $this->_dashBoardRowCount; } - if ($this->_outputMode == 'html' || $this->_outputMode == 'group') { + if ($this->addPaging) { $this->_select = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select); $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject); @@ -3401,23 +3322,28 @@ 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, 'status' => ts('Records') . ' %%StatusMessage%%', 'buttonBottom' => 'PagerBottomButton', 'buttonTop' => 'PagerTopButton', - 'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID), ); + if (!empty($this->controller)) { + // This happens when being called from the api Really we want the api to be able to + // pass paging parameters, but at this stage just preventing test crashes. + $params['pageID'] = $this->get(CRM_Utils_Pager::PAGE_ID); + } $pager = new CRM_Utils_Pager($params); $this->assign_by_ref('pager', $pager); @@ -3610,11 +3536,7 @@ ORDER BY cg.weight, cf.weight"; $this->_columns[$curTable]['grouping'] = $customDAO->table_name; $this->_columns[$curTable]['group_title'] = $customDAO->title; - foreach (array( - 'fields', - 'filters', - 'group_bys', - ) as $colKey) { + foreach (array('fields', 'filters', 'group_bys') as $colKey) { if (!array_key_exists($colKey, $this->_columns[$curTable])) { $this->_columns[$curTable][$colKey] = array(); } @@ -3654,11 +3576,8 @@ ORDER BY cg.weight, cf.weight"; case 'Boolean': $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT; - $curFilters[$fieldName]['options'] = array( - '' => ts('- select -'), - 1 => ts('Yes'), - 0 => ts('No'), - ); + $curFilters[$fieldName]['options'] = array('' => ts('- select -')) + + CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search'); $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT; break; @@ -3670,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': @@ -3678,57 +3598,15 @@ ORDER BY cg.weight, cf.weight"; break; case 'String': - $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING; - - if (!empty($customDAO->option_group_id)) { - if (in_array($customDAO->html_type, array( - 'Multi-Select', - 'AdvMulti-Select', - 'CheckBox', - ))) { - $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR; - } - else { - $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT; - } - if ($this->_customGroupFilters) { - $curFilters[$fieldName]['options'] = array(); - $ogDAO = CRM_Core_DAO::executeQuery("SELECT ov.value, ov.label FROM civicrm_option_value ov WHERE ov.option_group_id = %1 ORDER BY ov.weight", array( - 1 => array( - $customDAO->option_group_id, - 'Integer', - ), - )); - while ($ogDAO->fetch()) { - $curFilters[$fieldName]['options'][$ogDAO->value] = $ogDAO->label; - } - CRM_Utils_Hook::customFieldOptions($customDAO->cf_id, $curFilters[$fieldName]['options'], FALSE); - } - } - break; - case 'StateProvince': - if (in_array($customDAO->html_type, array( - 'Multi-Select State/Province', - ))) { - $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR; - } - else { - $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT; - } - $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::stateProvince(); - break; - case 'Country': - if (in_array($customDAO->html_type, array( - 'Multi-Select Country', - ))) { - $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR; - } - else { - $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT; + $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING; + + $options = CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search'); + if ($options !== FALSE) { + $curFilters[$fieldName]['operatorType'] = CRM_Core_BAO_CustomField::isSerialized($customDAO) ? CRM_Report_Form::OP_MULTISELECT_SEPARATOR : CRM_Report_Form::OP_MULTISELECT; + $curFilters[$fieldName]['options'] = $options; } - $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::country(); break; case 'ContactReference': @@ -3764,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"); @@ -3853,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( @@ -3939,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. * @@ -3986,6 +3892,7 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a 'nnll' ) { $this->_selectedTables[] = $tableName; + $this->filteredTables[] = $tableName; break; } } @@ -4175,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; @@ -4191,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; } @@ -4207,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; } @@ -4243,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; } @@ -4426,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(), ); } @@ -4594,4 +4523,125 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a $query->_from .= $from; } + /** + * Get label for show results buttons. + * + * @return string + */ + public function getResultsLabel() { + $showResultsLabel = $this->resultsDisplayed() ? ts('Refresh results') : ts('View results'); + return $showResultsLabel; + } + + /** + * Determine the output mode from the url or input. + * + * Output could be + * - pdf : Render as pdf + * - csv : Render as csv + * - print : Render in print format + * - save : save the report and display the new report + * - copy : save the report as a new instance and display that. + * - group : go to the add to group screen. + * + * Potentially chart variations could also be included but the complexity + * is that we might print a bar chart as a pdf. + */ + protected function setOutputMode() { + $buttonName = $this->controller->getButtonName(); + $this->_outputMode = CRM_Utils_Request::retrieve( + 'output', + 'String', + CRM_Core_DAO::$_nullObject, + FALSE, + CRM_Utils_Array::value('task', $this->_params) + ); + + if ($buttonName) { + if ($buttonName == $this->_instanceButtonName) { + $this->_outputMode = 'save'; + } + if ($buttonName == $this->_printButtonName) { + $this->_outputMode = 'print'; + } + if ($buttonName == $this->_pdfButtonName) { + $this->_outputMode = 'pdf'; + } + if ($this->_csvButtonName == $buttonName) { + $this->_outputMode = 'csv'; + } + if ($this->_groupButtonName == $buttonName) { + $this->_outputMode = 'group'; + } + if ($buttonName == $this->_createNewButtonName) { + $this->_outputMode = 'copy'; + } + } + } + + /** + * CRM-17793 - Alter DateTime section header to group by date from the datetime field. + * @param $tempTable + * @param $columnName + */ + public function alterSectionHeaderForDateTime($tempTable, $columnName) { + // add new column with date value for the datetime field + $tempQuery = "ALTER TABLE {$tempTable} ADD COLUMN {$columnName}_date VARCHAR(128)"; + CRM_Core_DAO::executeQuery($tempQuery); + $updateQuery = "UPDATE {$tempTable} SET {$columnName}_date = date({$columnName})"; + CRM_Core_DAO::executeQuery($updateQuery); + $this->_select .= ", {$columnName}_date"; + $this->_sections["{$columnName}_date"] = $this->_sections["{$columnName}"]; + unset($this->_sections["{$columnName}"]); + $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"; + } + } + }