Csv.php
| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * Matomo - free/libre analytics platform |
| 5 | * |
| 6 | * @link https://matomo.org |
| 7 | * @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later |
| 8 | */ |
| 9 | namespace Piwik\DataTable\Renderer; |
| 10 | |
| 11 | use Piwik\Common; |
| 12 | use Piwik\DataTable\Renderer; |
| 13 | use Piwik\DataTable\Simple; |
| 14 | use Piwik\DataTable; |
| 15 | use Piwik\Period; |
| 16 | use Piwik\Period\Range; |
| 17 | use Piwik\Piwik; |
| 18 | use Piwik\ProxyHttp; |
| 19 | /** |
| 20 | * CSV export |
| 21 | * |
| 22 | * When rendered using the default settings, a CSV report has the following characteristics: |
| 23 | * The first record contains headers for all the columns in the report. |
| 24 | * All rows have the same number of columns. |
| 25 | * The default field delimiter string is a comma (,). |
| 26 | * Formatting and layout are ignored. |
| 27 | * |
| 28 | */ |
| 29 | class Csv extends Renderer |
| 30 | { |
| 31 | /** |
| 32 | * Column separator |
| 33 | * |
| 34 | * @var string |
| 35 | */ |
| 36 | public $separator = ","; |
| 37 | /** |
| 38 | * Line end |
| 39 | * |
| 40 | * @var string |
| 41 | */ |
| 42 | public $lineEnd = "\n"; |
| 43 | /** |
| 44 | * 'metadata' columns will be exported, prefixed by 'metadata_' |
| 45 | * |
| 46 | * @var bool |
| 47 | */ |
| 48 | public $exportMetadata = true; |
| 49 | /** |
| 50 | * Converts the content to unicode so that UTF8 characters (eg. chinese) can be imported in Excel |
| 51 | * |
| 52 | * @var bool |
| 53 | */ |
| 54 | public $convertToUnicode = true; |
| 55 | /** |
| 56 | * idSubtable will be exported in a column called 'idsubdatatable' |
| 57 | * |
| 58 | * @var bool |
| 59 | */ |
| 60 | public $exportIdSubtable = true; |
| 61 | /** |
| 62 | * This string is also hardcoded in archive,sh |
| 63 | */ |
| 64 | public const NO_DATA_AVAILABLE = 'No data available'; |
| 65 | private $unsupportedColumns = array(); |
| 66 | /** |
| 67 | * Computes the dataTable output and returns the string/binary |
| 68 | * |
| 69 | * @return string |
| 70 | */ |
| 71 | public function render() |
| 72 | { |
| 73 | $str = $this->renderTable($this->table); |
| 74 | if (empty($str)) { |
| 75 | return self::NO_DATA_AVAILABLE; |
| 76 | } |
| 77 | $this->renderHeader(); |
| 78 | $str = $this->convertToUnicode($str); |
| 79 | return $str; |
| 80 | } |
| 81 | /** |
| 82 | * Enables / Disables unicode converting |
| 83 | * |
| 84 | * @param $bool |
| 85 | */ |
| 86 | public function setConvertToUnicode($bool) |
| 87 | { |
| 88 | $this->convertToUnicode = $bool; |
| 89 | } |
| 90 | /** |
| 91 | * Sets the column separator |
| 92 | * |
| 93 | * @param $separator |
| 94 | */ |
| 95 | public function setSeparator($separator) |
| 96 | { |
| 97 | $this->separator = $separator; |
| 98 | } |
| 99 | /** |
| 100 | * Computes the output of the given data table |
| 101 | * |
| 102 | * @param DataTable|array $table |
| 103 | * @param array $allColumns |
| 104 | * @return string |
| 105 | */ |
| 106 | protected function renderTable($table, &$allColumns = array()) |
| 107 | { |
| 108 | if (is_array($table)) { |
| 109 | // convert array to DataTable |
| 110 | $table = DataTable::makeFromSimpleArray($table); |
| 111 | } |
| 112 | if ($table instanceof DataTable\Map) { |
| 113 | $str = $this->renderDataTableMap($table, $allColumns); |
| 114 | } else { |
| 115 | $str = $this->renderDataTable($table, $allColumns); |
| 116 | } |
| 117 | return $str; |
| 118 | } |
| 119 | /** |
| 120 | * Computes the output of the given data table array |
| 121 | * |
| 122 | * @param DataTable\Map $table |
| 123 | * @param array $allColumns |
| 124 | * @return string |
| 125 | */ |
| 126 | protected function renderDataTableMap($table, &$allColumns = array()) |
| 127 | { |
| 128 | $str = ''; |
| 129 | foreach ($table->getDataTables() as $currentLinePrefix => $dataTable) { |
| 130 | $returned = explode("\n", $this->renderTable($dataTable, $allColumns)); |
| 131 | // get rid of the columns names |
| 132 | $returned = array_slice($returned, 1); |
| 133 | // case empty datatable we don't print anything in the CSV export |
| 134 | // when in xml we would output <result date="2008-01-15" /> |
| 135 | if (!empty($returned)) { |
| 136 | foreach ($returned as &$row) { |
| 137 | $row = $this->formatValue($currentLinePrefix) . $this->separator . $row; |
| 138 | } |
| 139 | $str .= "\n" . implode("\n", $returned); |
| 140 | } |
| 141 | } |
| 142 | // prepend table key to column list |
| 143 | $allColumns = array_merge(array($table->getKeyName() => true), $allColumns); |
| 144 | // add header to output string |
| 145 | $str = $this->getHeaderLine(array_keys($allColumns)) . $str; |
| 146 | return $str; |
| 147 | } |
| 148 | /** |
| 149 | * Converts the output of the given simple data table |
| 150 | * |
| 151 | * @param DataTable|Simple $table |
| 152 | * @param array $allColumns |
| 153 | * @return string |
| 154 | */ |
| 155 | protected function renderDataTable($table, &$allColumns = array()) |
| 156 | { |
| 157 | if ($table instanceof Simple) { |
| 158 | $row = $table->getFirstRow(); |
| 159 | if ($row !== false) { |
| 160 | $columnNameToValue = $row->getColumns(); |
| 161 | if (count($columnNameToValue) === 1) { |
| 162 | // simple tables should only have one column, the value |
| 163 | $allColumns['value'] = true; |
| 164 | $value = array_values($columnNameToValue); |
| 165 | $str = 'value' . $this->lineEnd . $this->formatValue($value[0]); |
| 166 | return $str; |
| 167 | } |
| 168 | } |
| 169 | } |
| 170 | $csv = $this->makeArrayFromDataTable($table, $allColumns); |
| 171 | $str = $this->buildCsvString($allColumns, $csv); |
| 172 | return $str; |
| 173 | } |
| 174 | /** |
| 175 | * Returns the CSV header line for a set of metrics. Will translate columns if desired. |
| 176 | * |
| 177 | * @param array $columnMetrics |
| 178 | * @return array |
| 179 | */ |
| 180 | private function getHeaderLine($columnMetrics) |
| 181 | { |
| 182 | foreach ($columnMetrics as $index => $value) { |
| 183 | if (in_array($value, $this->unsupportedColumns)) { |
| 184 | unset($columnMetrics[$index]); |
| 185 | } |
| 186 | } |
| 187 | if ($this->translateColumnNames) { |
| 188 | $columnMetrics = $this->translateColumnNames($columnMetrics); |
| 189 | } |
| 190 | foreach ($columnMetrics as &$value) { |
| 191 | $value = $this->formatValue($value); |
| 192 | } |
| 193 | return implode($this->separator, $columnMetrics); |
| 194 | } |
| 195 | /** |
| 196 | * Formats/Escapes the given value |
| 197 | * |
| 198 | * @param mixed $value |
| 199 | * @return string |
| 200 | */ |
| 201 | public function formatValue($value) |
| 202 | { |
| 203 | if (is_string($value) && !is_numeric($value)) { |
| 204 | $value = html_entity_decode($value, ENT_QUOTES, 'UTF-8'); |
| 205 | } elseif ($value === false) { |
| 206 | $value = 0; |
| 207 | } |
| 208 | $value = $this->formatFormulas($value); |
| 209 | if (is_string($value)) { |
| 210 | $value = str_replace(["\t"], ' ', $value); |
| 211 | // surround value with double quotes if it contains a double quote or a commonly used separator |
| 212 | if (strpos($value, '"') !== false || strpos($value, $this->separator) !== false || strpos($value, ',') !== false || strpos($value, ';') !== false) { |
| 213 | $value = '"' . str_replace('"', '""', $value) . '"'; |
| 214 | } |
| 215 | } |
| 216 | // in some number formats (e.g. German), the decimal separator is a comma |
| 217 | // we need to catch and replace this |
| 218 | if (is_numeric($value)) { |
| 219 | $value = (string) $value; |
| 220 | $value = str_replace(',', '.', $value); |
| 221 | } |
| 222 | return $value; |
| 223 | } |
| 224 | protected function formatFormulas($value) |
| 225 | { |
| 226 | // Excel / Libreoffice formulas may start with one of these characters |
| 227 | $formulaStartsWith = array('=', '+', '-', '@'); |
| 228 | // remove first % sign and if string is still a number, return it as is |
| 229 | $valueWithoutFirstPercentSign = $this->removeFirstPercentSign($value); |
| 230 | if (empty($valueWithoutFirstPercentSign) || !is_string($value) || is_numeric($valueWithoutFirstPercentSign)) { |
| 231 | return $value; |
| 232 | } |
| 233 | $firstCharCellValue = $valueWithoutFirstPercentSign[0]; |
| 234 | $isFormula = in_array($firstCharCellValue, $formulaStartsWith); |
| 235 | if ($isFormula) { |
| 236 | return "'" . $value; |
| 237 | } |
| 238 | return $value; |
| 239 | } |
| 240 | /** |
| 241 | * Sends the http headers for csv file |
| 242 | */ |
| 243 | protected function renderHeader() |
| 244 | { |
| 245 | $fileName = Piwik::translate('General_Export'); |
| 246 | $period = Common::getRequestVar('period', false); |
| 247 | $date = Common::getRequestVar('date', false); |
| 248 | if ($period || $date) { |
| 249 | // in test cases, there are no request params set |
| 250 | if ($period === 'range') { |
| 251 | $period = new Range($period, $date); |
| 252 | } elseif (strpos($date, ',') !== false) { |
| 253 | $period = new Range('range', $date); |
| 254 | } else { |
| 255 | $period = Period\Factory::build($period, $date); |
| 256 | } |
| 257 | $prettyDate = $period->getLocalizedLongString(); |
| 258 | $meta = $this->getApiMetaData(); |
| 259 | $name = !empty($meta['name']) ? $meta['name'] : ''; |
| 260 | $fileName .= ' _ ' . $name . ' _ ' . $prettyDate . '.csv'; |
| 261 | } |
| 262 | // silent fail otherwise unit tests fail |
| 263 | Common::sendHeader("Content-Disposition: attachment; filename*=UTF-8''" . rawurlencode($fileName), true); |
| 264 | ProxyHttp::overrideCacheControlHeaders(); |
| 265 | } |
| 266 | /** |
| 267 | * Flattens an array of column values so they can be outputted as CSV (which does not support |
| 268 | * nested structures). |
| 269 | */ |
| 270 | private function flattenColumnArray($columns, &$csvRow = array(), $csvColumnNameTemplate = '%s') |
| 271 | { |
| 272 | foreach ($columns as $name => $value) { |
| 273 | $csvName = sprintf($csvColumnNameTemplate, $this->getCsvColumnName($name)); |
| 274 | if (is_array($value)) { |
| 275 | // if we're translating column names and this is an array of arrays, the column name |
| 276 | // format becomes a bit more complicated. also in this case, we assume $value is not |
| 277 | // nested beyond 2 levels (ie, array(0 => array(0 => 1, 1 => 2)), but not array( |
| 278 | // 0 => array(0 => array(), 1 => array())) ) |
| 279 | if ($this->translateColumnNames && is_array(reset($value))) { |
| 280 | foreach ($value as $level1Key => $level1Value) { |
| 281 | $inner = $name === 'goals' ? Piwik::translate('Goals_GoalX', $level1Key) : $name . ' ' . $level1Key; |
| 282 | $columnNameTemplate = '%s (' . $inner . ')'; |
| 283 | $this->flattenColumnArray($level1Value, $csvRow, $columnNameTemplate); |
| 284 | } |
| 285 | } else { |
| 286 | $this->flattenColumnArray($value, $csvRow, $csvName . '_%s'); |
| 287 | } |
| 288 | } else { |
| 289 | $csvRow[$csvName] = $value; |
| 290 | } |
| 291 | } |
| 292 | return $csvRow; |
| 293 | } |
| 294 | private function getCsvColumnName($name) |
| 295 | { |
| 296 | if ($this->translateColumnNames) { |
| 297 | return $this->translateColumnName($name); |
| 298 | } else { |
| 299 | return $name; |
| 300 | } |
| 301 | } |
| 302 | /** |
| 303 | * @param $allColumns |
| 304 | * @param $csv |
| 305 | * @return array |
| 306 | */ |
| 307 | private function buildCsvString($allColumns, $csv) |
| 308 | { |
| 309 | $str = ''; |
| 310 | // specific case, we have only one column and this column wasn't named properly (indexed by a number) |
| 311 | // we don't print anything in the CSV file => an empty line |
| 312 | if (sizeof($allColumns) === 1 && reset($allColumns) && !is_string(key($allColumns))) { |
| 313 | $str .= ''; |
| 314 | } else { |
| 315 | // render row names |
| 316 | $str .= $this->getHeaderLine(array_keys($allColumns)) . $this->lineEnd; |
| 317 | } |
| 318 | // we render the CSV |
| 319 | foreach ($csv as $theRow) { |
| 320 | $rowStr = ''; |
| 321 | foreach ($allColumns as $columnName => $true) { |
| 322 | $rowStr .= $this->formatValue($theRow[$columnName] ?? '') . $this->separator; |
| 323 | } |
| 324 | // remove the last separator |
| 325 | $rowStr = substr_replace($rowStr, "", -strlen($this->separator)); |
| 326 | $str .= $rowStr . $this->lineEnd; |
| 327 | } |
| 328 | $str = substr($str, 0, -strlen($this->lineEnd)); |
| 329 | return $str; |
| 330 | } |
| 331 | /** |
| 332 | * @param $table |
| 333 | * @param $allColumns |
| 334 | * @return array of csv data |
| 335 | */ |
| 336 | private function makeArrayFromDataTable($table, &$allColumns) |
| 337 | { |
| 338 | $csv = array(); |
| 339 | foreach ($table->getRows() as $row) { |
| 340 | $csvRow = $this->flattenColumnArray($row->getColumns()); |
| 341 | if ($this->exportMetadata) { |
| 342 | $metadata = $row->getMetadata(); |
| 343 | foreach ($metadata as $name => $value) { |
| 344 | if ($name === 'idsubdatatable_in_db') { |
| 345 | continue; |
| 346 | } |
| 347 | //if a metadata and a column have the same name make sure they don't overwrite |
| 348 | if ($this->translateColumnNames) { |
| 349 | $name = Piwik::translate('General_Metadata') . ': ' . $name; |
| 350 | } else { |
| 351 | $name = 'metadata_' . $name; |
| 352 | } |
| 353 | if (is_array($value) || is_object($value)) { |
| 354 | if (!in_array($name, $this->unsupportedColumns)) { |
| 355 | $this->unsupportedColumns[] = $name; |
| 356 | } |
| 357 | } else { |
| 358 | $csvRow[$name] = $value; |
| 359 | } |
| 360 | } |
| 361 | } |
| 362 | foreach ($csvRow as $name => $value) { |
| 363 | if (in_array($name, $this->unsupportedColumns)) { |
| 364 | unset($allColumns[$name]); |
| 365 | } else { |
| 366 | $allColumns[$name] = true; |
| 367 | } |
| 368 | } |
| 369 | if ($this->exportIdSubtable) { |
| 370 | $idsubdatatable = $row->getIdSubDataTable(); |
| 371 | if ($idsubdatatable !== false && $this->hideIdSubDatatable === false) { |
| 372 | $csvRow['idsubdatatable'] = $idsubdatatable; |
| 373 | } |
| 374 | } |
| 375 | $csv[] = $csvRow; |
| 376 | } |
| 377 | if (!empty($this->unsupportedColumns)) { |
| 378 | foreach ($this->unsupportedColumns as $unsupportedColumn) { |
| 379 | foreach ($csv as $index => $row) { |
| 380 | unset($row[$index][$unsupportedColumn]); |
| 381 | } |
| 382 | } |
| 383 | } |
| 384 | return $csv; |
| 385 | } |
| 386 | /** |
| 387 | * @param $str |
| 388 | * @return string |
| 389 | */ |
| 390 | private function convertToUnicode($str) |
| 391 | { |
| 392 | if ($this->convertToUnicode && function_exists('mb_convert_encoding')) { |
| 393 | $str = chr(255) . chr(254) . mb_convert_encoding($str, 'UTF-16LE', 'UTF-8'); |
| 394 | } |
| 395 | return $str; |
| 396 | } |
| 397 | /** |
| 398 | * @param $value |
| 399 | * @return mixed |
| 400 | */ |
| 401 | protected function removeFirstPercentSign($value) |
| 402 | { |
| 403 | $needle = '%'; |
| 404 | $posPercent = strpos($value ?? '', $needle); |
| 405 | if ($posPercent !== false) { |
| 406 | return substr_replace($value, '', $posPercent, strlen($needle)); |
| 407 | } |
| 408 | return $value; |
| 409 | } |
| 410 | } |
| 411 |