Try to think about this in a more generic way, you basically have:
- Select data from DB
- Put that data in a CSV (will you in the future want to use other formats like XLSX, etc? If so, we should abstract this step too, but I wont do this right now)
- Return that CSV file (doesn't matter to who you're returning)
So we can create a few classes from those steps
- Any class that can query the DB, your app probably has a bunch of them
- AbstractWriter - this class is an abstraction/interface to write anywhere, you'll need to implement it for specific formats, like Writer/Csv, Writer/Xlsx, Writer/JSON, etc.
- AbstractFileReturner - this class is an abstraction/interface to return it to anyone, you'll need to implement it for specific cases, AbstractFileReturner/like Sftp, AbstractFileReturner/HttpResponse, etc.
Any time you need more File formats or Returner, you just implement the AbstractWriter or FileReturner respectively one more time.
Doing all these, you'll be using Single Responsability Principle (each class do only one thing) and Open/Closed (the abstractions/interfaces are closed for modification, but open for extension).
Real code
Ok so I came up with a little bit of code having SOLID in mind and to make it easy to add more file formats and and way outs
(Sftp, Response, etc).
This is a photo of the layers I tried to create, it might make the code more understandable.

Now embrace yourself for there are coming a few classes.
I didn't test this code and doens't know much of Laravel, so there might be a few bugs, but you'll get the big picture.
First, a sample controller and model which I thought was your Line
.
class SomeModel
{
// the chunk does not go here, it will go inside who's putting the data into the csv
public static function someQuery()
{
return Line::with(['invoice', 'invoice.customer', 'item'])
->whereHas('invoice', function ($query) {
$query->where('is_exportable', 1);
});
}
public static function lineMapping(Line $line)
{
return [
'Invoice number' => $line->invoice->id,
'Document type' => self::DOCUMENT_TYPE,
'Date' => $line->invoice->date,
];
}
}
class SomeController
{
public function exportCsvSftpAction()
{
$dataCollection = SomeModel::someQuery();
$filename = 'output.csv';
$exporter = new SftpCsvExporter($dataCollection, $filename);
$exporter->export();
}
public function exportCsvResponseAction()
{
$dataCollection = SomeModel::someQuery();
$filename = 'output2.csv';
$exporter = new ResponseCsvExporter($dataCollection, $filename);
$exporter->export();
}
}
Than there's the first layer: Export, I created an interface to say what
it should do (don't mind about how
now) and a abstract class that implements some parts of it.
interface Exportable
{
protected function createFile();
protected function write($line);
protected function deliver($filename);
}
abstract class Export implements Exportable
{
protected $fileHandle = null;
private $dataCollection;
private $filename;
public function __construct($dataCollection, $filename)
{
$this->createFile();
// @TODO You should add validation for $dataCollection like
// checking if it has data, if it really is a Collection so we can call chunk, etc
// And check $filename for invalid characters
$this->dataCollection = $dataCollection;
$this->filename = $filename;
}
protected abstract function createFile();
protected abstract function write($line);
protected abstract function deliver($filename);
// this method is who makes it all work together
// and let the controller be so simple
public function export()
{
$this->dataCollection->chunk(200, function ($lines) {
foreach ($lines as $line) {
$this->write($line);
}
});
$this->deliver($this->filename);
}
}
Now there's the second layer: Csv, in this layer you will be able to add more file formats in the future if needed.
This layer knows how to write, but doesn't know how to open nor deliver the file.
abstract class CsvExporter extends Export
{
const DELIMITER = ';';
private $headerWritten = false;
protected function write($line)
{
if (!$this->headerWritten) {
fputcsv(
$this->fileHandle,
array_keys(SomeModel::lineMapping(SomeModel::first())),
self::DELIMITER
);
$this->headerWritten = true;
}
fputcsv(
$this->fileHandle,
SomeModel::lineMapping($line),
self::DELIMITER
);
}
}
And finally the third and last layer: this is responsible for opening and delivering the file. So we have Sftp and Response in this layer.
class SftpCsvExporter extends CsvExporter
{
protected function createFile()
{
if ($this->fileHandle !== null) {
throw new LogicException('Handle already initilized');
}
$this->fileHandle = fopen('php://temp', 'w');
}
protected function deliver($filename)
{
return Storage::disk('sftp')->put($filename, $this->fileHandle);
}
}
class ResponseCsvExporter extends CsvExporter
{
protected function createFile()
{
if ($this->fileHandle !== null) {
throw new LogicException('Handle already initilized');
}
$this->fileHandle = fopen('php://output', 'w');
}
protected function deliver($filename)
{
$handle = $this->fileHandle;
return new StreamedResponse(function () use ($handle, $filename) {
// I don't know this StreamedResponse so you might want to correct this
// the only thing left is to close the handle before send the file
fclose($handle);
}, 200, [
'Content-Type' => 'text/csv',
'Content-Disposition' => 'attachment; filename="' . $filename . '"',
]);
}
}
Line
? I see it query the DB but the columns names doesn't seem to have anything to do withLine
\$\endgroup\$ – Edson Horacio Junior Nov 20 '18 at 13:01lineMapping()
for the question since it's not relevant. theLine
model is a line in an invoice.with(['invoice', 'invoice.customer', 'item'])
is an eager load to load theInvoice
, theCustomer
and theItem
. In the CSV, every row will have information about the the line, the item, the invoice and the customer. The CSV is templated to be imported in an external accounting software. \$\endgroup\$ – Clément Baconnier Nov 20 '18 at 13:13