/**
* @return string Full filesystem path to resulting temporary file.
*/
public function export()
{
$columns = array();
$columnHeaders = array();
$join_clause = '';
foreach ($this->columns as $col_name => $col) {
if ($col->isForeignKey()) {
$colJoin = $this->joinOn($col);
$columnName = $colJoin['column_alias'];
$join_clause .= $colJoin['join_clause'];
} elseif ($col->getType() === 'point') {
$columns[] = "IF(`{$this->name}`.`{$col_name}` IS NOT NULL, AsText(`{$this->name}`.`{$col_name}`), '') AS `{$col_name}`";
} else {
$columnName = "`{$this->name}`.`{$col_name}`";
}
if ($col->getType() !== 'point' && isset($columnName)) {
$columns[] = "REPLACE(IFNULL({$columnName}, ''),CONCAT(CHAR(13),CHAR(10)),CHAR(10))";
// 13 = \r and 10 = \n
}
$columnHeaders[] = $col->getTitle();
}
// Build basic SELECT statement
$sql = 'SELECT ' . join(',', $columns) . ' FROM `' . $this->getName() . '` ' . $join_clause;
$params = $this->applyFilters($sql);
$tmpDir = Config::storageDirExport();
$filename = $tmpDir . '/' . uniqid() . '.csv';
if (DIRECTORY_SEPARATOR == '\\') {
// Clean Windows slashes, for MySQL's benefit.
$filename = str_replace('\\', '/', $filename);
}
// Clear out any old copy.
if (file_exists($filename)) {
unlink($filename);
}
// Build the final SQL, appending the column headers in a UNION.
$sql = 'SELECT "' . join('", "', $columnHeaders) . '"' . ' UNION ' . $sql . ' INTO OUTFILE "' . $filename . '" ' . ' FIELDS TERMINATED BY ","' . ' ENCLOSED BY \'"\'' . ' ESCAPED BY \'"\'' . ' LINES TERMINATED BY "\\r\\n"';
// Execute the SQL.
$this->database->query($sql, $params);
// Make sure it exported.
if (!file_exists($filename)) {
$msg = "Unable to create temporary export file:<br /><code>{$filename}</code>";
throw new \Exception($msg);
}
// Give the filename back to the controller, to send to the client.
return $filename;
}
