renderTable($this->table); if (empty($str)) { return self::NO_DATA_AVAILABLE; } $this->renderHeader(); return $this->convertToUnicode($str); } /** * Enables / Disables unicode converting */ public function setConvertToUnicode(bool $convertToUnicode): void { $this->convertToUnicode = $convertToUnicode; } /** * Sets the column separator */ public function setSeparator(string $separator): void { $this->separator = $separator; } /** * Computes the output of the given data table * * @param DataTable|DataTable\Map|array $table * @param array $allColumns */ protected function renderTable($table, array &$allColumns = []): string { if (is_array($table)) { // convert array to DataTable $table = DataTable::makeFromSimpleArray($table); } if ($table instanceof DataTable\Map) { $str = $this->renderDataTableMap($table, $allColumns); } else { $str = $this->renderDataTable($table, $allColumns); } return $str; } /** * Computes the output of the given data table array * * @param array $allColumns */ protected function renderDataTableMap(DataTable\Map $table, array &$allColumns = []): string { $str = ''; foreach ($table->getDataTables() as $currentLinePrefix => $dataTable) { $returned = explode("\n", $this->renderTable($dataTable, $allColumns)); // get rid of the columns names $returned = array_slice($returned, 1); // case empty datatable we don't print anything in the CSV export // when in xml we would output if (!empty($returned)) { foreach ($returned as &$row) { $row = $this->formatValue($currentLinePrefix) . $this->separator . $row; } $str .= "\n" . implode("\n", $returned); } } // prepend table key to column list $allColumns = array_merge(array($table->getKeyName() => true), $allColumns); // add header to output string return $this->getHeaderLine(array_keys($allColumns)) . $str; } /** * Converts the output of the given simple data table * * @param DataTable|Simple $table * @param array $allColumns */ protected function renderDataTable($table, array &$allColumns = []): string { if ($table instanceof Simple) { $row = $table->getFirstRow(); if ($row !== false) { $columnNameToValue = $row->getColumns(); if (count($columnNameToValue) === 1) { // simple tables should only have one column, the value $allColumns['value'] = true; $value = array_values($columnNameToValue); return 'value' . $this->lineEnd . $this->formatValue($value[0]); } } } $csv = $this->makeArrayFromDataTable($table, $allColumns); return $this->buildCsvString($allColumns, $csv); } /** * Returns the CSV header line for a set of metrics. Will translate columns if desired. * * @param array $columnMetrics */ private function getHeaderLine(array $columnMetrics): string { foreach ($columnMetrics as $index => $value) { if (in_array($value, $this->unsupportedColumns)) { unset($columnMetrics[$index]); } } if ($this->translateColumnNames) { $columnMetrics = $this->translateColumnNames($columnMetrics); } foreach ($columnMetrics as &$value) { $value = $this->formatValue($value); } return implode($this->separator, $columnMetrics); } /** * Formats/Escapes the given value * * @param mixed $value * @return string */ public function formatValue($value) { if ( is_string($value) && !is_numeric($value) ) { $value = html_entity_decode($value, ENT_QUOTES, 'UTF-8'); } elseif ($value === false) { $value = 0; } $value = $this->formatFormulas($value); if (is_string($value)) { $value = str_replace(["\t", "\r"], ' ', $value); // surround value with double quotes if it contains a double quote or a commonly used separator if ( strpos($value, '"') !== false || strpos($value, $this->separator) !== false || strpos($value, $this->lineEnd) !== false || strpos($value, ',') !== false || strpos($value, ';') !== false ) { $value = '"' . str_replace('"', '""', $value) . '"'; } } // in some number formats (e.g. German), the decimal separator is a comma // we need to catch and replace this if (is_numeric($value)) { $value = (string)$value; $value = str_replace(',', '.', $value); } return $value; } /** * @param mixed $value * @return mixed */ protected function formatFormulas($value) { // Excel / Libreoffice formulas may start with one of these characters $formulaStartsWith = array('=', '+', '-', '@'); // remove first % sign and if string is still a number, return it as is $valueWithoutFirstPercentSign = $this->removeFirstPercentSign((string)$value); if ( empty($valueWithoutFirstPercentSign) || !is_string($value) || is_numeric($valueWithoutFirstPercentSign) ) { return $value; } $firstCharCellValue = $valueWithoutFirstPercentSign[0]; $isFormula = in_array($firstCharCellValue, $formulaStartsWith); if ($isFormula) { return "'" . $value; } return $value; } /** * Sends the http headers for csv file */ protected function renderHeader(): void { $fileName = Piwik::translate('General_Export'); $period = Request::fromRequest()->getStringParameter('period', ''); $date = Request::fromRequest()->getStringParameter('date', ''); if ($period || $date) { // in test cases, there are no request params set if ($period === 'range') { $period = new Range($period, $date); } elseif (strpos($date, ',') !== false) { $period = new Range('range', $date); } else { $period = Period\Factory::build($period, $date); } $prettyDate = $period->getLocalizedLongString(); $meta = $this->getApiMetaData(); $name = !empty($meta['name']) ? $meta['name'] : ''; $fileName .= ' _ ' . $name . ' _ ' . $prettyDate . '.csv'; } $fileName = Filesystem::sanitizeFilename($fileName); // silent fail otherwise unit tests fail Common::sendHeader("Content-Disposition: attachment; filename*=UTF-8''" . rawurlencode($fileName), true); ProxyHttp::overrideCacheControlHeaders(); } /** * Flattens an array of column values so they can be outputted as CSV (which does not support * nested structures). */ private function flattenColumnArray(array $columns, array &$csvRow = [], string $csvColumnNameTemplate = '%s'): array { foreach ($columns as $name => $value) { $csvName = sprintf($csvColumnNameTemplate, $this->getCsvColumnName($name)); if (is_array($value)) { // if we're translating column names and this is an array of arrays, the column name // format becomes a bit more complicated. also in this case, we assume $value is not // nested beyond 2 levels (ie, array(0 => array(0 => 1, 1 => 2)), but not array( // 0 => array(0 => array(), 1 => array())) ) if ( $this->translateColumnNames && is_array(reset($value)) ) { foreach ($value as $level1Key => $level1Value) { $inner = $name === 'goals' ? Piwik::translate('Goals_GoalX', $level1Key) : $name . ' ' . $level1Key; $columnNameTemplate = '%s (' . $inner . ')'; $this->flattenColumnArray($level1Value, $csvRow, $columnNameTemplate); } } else { $this->flattenColumnArray($value, $csvRow, $csvName . '_%s'); } } else { $csvRow[$csvName] = $value; } } return $csvRow; } private function getCsvColumnName(string $name): string { if ($this->translateColumnNames) { return $this->translateColumnName($name); } else { return $name; } } /** * @param array $allColumns * @param array $csv */ private function buildCsvString(array $allColumns, array $csv): string { $str = ''; // specific case, we have only one column and this column wasn't named properly (indexed by a number) // we don't print anything in the CSV file => an empty line if ( sizeof($allColumns) === 1 && reset($allColumns) && !is_string(key($allColumns)) ) { $str .= ''; } else { // render row names $str .= $this->getHeaderLine(array_keys($allColumns)) . $this->lineEnd; } // we render the CSV foreach ($csv as $theRow) { $rowStr = ''; foreach ($allColumns as $columnName => $true) { $rowStr .= $this->formatValue($theRow[$columnName] ?? '') . $this->separator; } // remove the last separator $rowStr = substr_replace($rowStr, "", -strlen($this->separator)); $str .= $rowStr . $this->lineEnd; } return substr($str, 0, -strlen($this->lineEnd)); } /** * @param DataTable $table * @param array $allColumns * @return array of csv data */ private function makeArrayFromDataTable($table, array &$allColumns): array { $csv = []; foreach ($table->getRows() as $row) { $csvRow = $this->flattenColumnArray($row->getColumns()); if (!$this->hideMetadata) { $metadata = $row->getMetadata(); foreach ($metadata as $name => $value) { if ($name === 'idsubdatatable_in_db') { continue; } //if a metadata and a column have the same name make sure they don't overwrite if ($this->translateColumnNames) { $name = Piwik::translate('General_Metadata') . ': ' . $name; } else { $name = 'metadata_' . $name; } if ( is_array($value) || is_object($value) ) { if (!in_array($name, $this->unsupportedColumns)) { $this->unsupportedColumns[] = $name; } } else { $csvRow[$name] = $value; } } } foreach ($csvRow as $name => $value) { if (in_array($name, $this->unsupportedColumns)) { unset($allColumns[$name]); } else { $allColumns[$name] = true; } } if (!$this->hideIdSubDatatable && !$this->hideMetadata) { $idsubdatatable = $row->getIdSubDataTable(); if ($idsubdatatable !== false) { $csvRow['idsubdatatable'] = $idsubdatatable; } } $csv[] = $csvRow; } if (!empty($this->unsupportedColumns)) { foreach ($this->unsupportedColumns as $unsupportedColumn) { foreach ($csv as $index => $row) { unset($row[$index][$unsupportedColumn]); } } } return $csv; } /** * @param string $str * @return string */ private function convertToUnicode($str) { if ( $this->convertToUnicode && function_exists('mb_convert_encoding') ) { $str = chr(255) . chr(254) . mb_convert_encoding($str, 'UTF-16LE', 'UTF-8'); } return $str; } protected function removeFirstPercentSign(string $value): string { // remove all null byte chars from the beginning $value = ltrim($value, "\0"); while (0 === strpos($value, '%00')) { $value = ltrim(substr($value, 3), "\0"); } $posPercent = strpos($value, '%'); if ($posPercent !== false) { return substr_replace($value, '', $posPercent, 1); } return $value; } }