public function downloadAdvancedSearch(Request $request)
{
// get parameters from url route
$parameters = $request->route()->parameters();
$advancedSearch = AdvancedSearchTask::builder()->find(Crypt::decrypt($parameters['token']));
if ($advancedSearch === null) {
abort(404);
}
return response()->download(storage_path('exports') . '/' . $advancedSearch->filename_022 . '.' . $advancedSearch->extension_file_022);
}
public function exportData()
{
$parameters = [];
// Maximum rows from request to direct download
$maximumRowsToDownload = 100;
// set advanced search, with this function get only inputs to do advanced search
$parameters = Miscellaneous::dataTableColumnFiltering($this->request, $parameters, 'array');
// set order table
$parameters['order'] = json_decode($this->request->input('order'), true);
// set columns to display in export file
$parameters['displayColumns'] = json_decode($this->request->input('displayColumns'), true);
// config variables to count n records
$parametersCount = $parameters;
$parametersCount['count'] = true;
$nFilteredTotal = call_user_func($this->model . '::getIndexRecords', $this->request, $parametersCount);
// check than there are any data to export
if ($nFilteredTotal < 1) {
return null;
}
// if there are more than $maximumRowsToDownload rows, set start and length element to storage in file
if ($nFilteredTotal > $maximumRowsToDownload) {
// set paginate parameters
$parameters['start'] = 0;
// take first row
$parameters['length'] = config('pulsar.advancedSearchLimitLength');
// get limit to rows to take
}
// get data from model
$objects = call_user_func($this->model . '::getIndexRecords', $this->request, $parameters);
// *************************************************************
// transform string data to number data, to operate with excel
// *************************************************************
$objects->transform(function ($item, $key) {
$attributes = $item->getAttributes();
foreach ($attributes as $key => $value) {
if (is_numeric($value) && strpos($value, '.') === false) {
$item->{$key} = (int) $value;
} elseif (is_numeric($value) && strpos($value, '.') !== false) {
$item->{$key} = (double) $value;
}
}
return $item;
});
// get de first object, to konow properties from model, table name, columns, etc.
$object = $objects->first();
// get filename to download
$filename = ($nFilteredTotal > $maximumRowsToDownload ? uniqid() . '_' : null) . (empty($object->tableTranslation) ? $object->table : trans($object->tableTranslation));
// create spreadsheet to export data
$excel = Excel::create($filename, function ($excel) use(&$parameters, $objects, $object) {
// set the title
$excel->setTitle('Export table :table')->setCreator('Pulsar')->setCompany('SYSCOVER');
// set sheet
$excel->sheet('Data', function ($sheet) use(&$parameters, $objects, $object) {
// get all attributes from model and transform
// to array only the keys, to know columns names
$headers = collect($object->getAttributes())->keys()->toArray();
/***********************************************************************
* Get operation columns, this value is sent from advanced search form,
* with this structure on json format
*
* [
* ['column' => 'column_database_000', 'operation' => 'sum'],
* ['column' => 'column_database_001', 'operation' => 'sum'],
* ...
* ]
***********************************************************************/
if ($this->request->has('operationColumns')) {
// set operations columns in parameters to be saved in AdvancedSearchTask model
$parameters['operationColumns'] = json_decode($this->request->input('operationColumns'), true);
// transform array to collection to manage elements
$operationColumns = collect($parameters['operationColumns']);
// create empty operations row to be filled after
$operationsRow = array_fill(0, count($headers) - 1, '');
// set operations columns row
foreach ($headers as $key => &$columnName) {
// first, check if column has any operation on itself
$operationColumn = $operationColumns->where('column', $columnName);
if ($operationColumn->count() > 0) {
// set operation
$operationColumn = $operationColumn->first();
switch ($operationColumn['operation']) {
case 'sum':
$operationsRow[$key] = $objects->sum($columnName);
break;
}
}
}
}
foreach ($headers as $key => &$columnName) {
// get translation column if exist
if (isset($object->columnTranslation[$columnName])) {
$columnName = trans($object->columnTranslation[$columnName]);
}
}
// set data and headers
$sheet->prependRow($headers);
$sheet->fromArray($objects->toArray(), null, 'A2', false, false);
$sheet->cells('A1:' . $sheet->row(0)->getHighestDataColumn() . '1', function ($cells) {
$cells->setBackground('#CCCCCC');
$cells->setFontWeight('bold');
});
// set operations row and styles
if ($this->request->has('operationColumns')) {
$sheet->appendRow($operationsRow);
$sheet->cells('A' . $sheet->row(0)->getHighestRow() . ':' . $sheet->row(0)->getHighestDataColumn() . $sheet->row(0)->getHighestRow(), function ($cells) {
$cells->setBackground('#F8F8F8');
$cells->setFontWeight('bold');
});
}
});
});
// if there are more than $maximumRowsToDownload rows, create AdvancedSearchTask to execute with cron after
if ($nFilteredTotal > $maximumRowsToDownload) {
$excel->store($this->request->input('extensionFile'));
// set start to next call
$parameters['start'] = config('pulsar.advancedSearchLimitLength');
// crete task to download after
AdvancedSearchTask::create(['date_022' => date('U'), 'user_id_022' => auth('pulsar')->user()->id_010, 'model_022' => $this->model, 'parameters_022' => json_encode($parameters), 'extension_file_022' => $this->request->input('extensionFile'), 'filename_022' => $filename, 'created_022' => false]);
return redirect()->route($this->routeSuffix)->with(['msg' => 1, 'txtMsg' => trans('pulsar::pulsar.message_advanced_search_exports_03')]);
} else {
$excel->download($this->request->input('extensionFile'));
}
}
/**
* Function to manage queue from advanced search
*/
public static function checkAdvancedSearchExports()
{
// get advanced search without create yet
$advancedSearches = AdvancedSearchTask::builder()->where('created_022', false)->get();
foreach ($advancedSearches as $advancedSearch) {
// get parametes from advnaced search
$parameters = json_decode($advancedSearch->parameters_022, true);
// config variables to count n records
$parametersCount = $parameters;
$parametersCount['count'] = true;
$nFilteredTotal = call_user_func($advancedSearch->model_022 . '::getIndexRecords', null, $parametersCount);
$lastInteraction = false;
// flag to know if this is the las call to do
// if is the last interaction, set length to get just last record
if ($parameters['start'] + $parameters['length'] >= $nFilteredTotal) {
$parameters['length'] = $nFilteredTotal;
$lastInteraction = true;
}
// get data from model
$objects = call_user_func($advancedSearch->model_022 . '::getIndexRecords', null, $parameters);
// *************************************************************
// transform string data to number data, to operate with excel
// *************************************************************
$objects->transform(function ($item, $key) {
$attributes = $item->getAttributes();
foreach ($attributes as $key => $value) {
if (is_numeric($value) && strpos($value, '.') === false) {
$item->{$key} = (int) $value;
} elseif (is_numeric($value) && strpos($value, '.') !== false) {
$item->{$key} = (double) $value;
}
}
return $item;
});
// get de first object, to konow properties from model, table name, columns, etc.
$object = $objects->first();
// get filename to load
$filename = storage_path('exports') . '/' . $advancedSearch->filename_022 . '.' . $advancedSearch->extension_file_022;
// create spreadsheet to export data
Excel::load($filename, function ($excel) use($parameters, $objects, $object) {
// get sheet
$excel->sheet('Data', function ($sheet) use($parameters, $objects, $object) {
// get all attributes from model and transform
// to array only the keys, to know columns names
$headers = collect($object->getAttributes())->keys()->toArray();
// if has operations columns, read las row (operations row), and delete
if (isset($parameters['operationColumns'])) {
$highestRow = $sheet->getHighestRow();
// get highest row
$highestColumn = $sheet->getHighestColumn();
// get highest column
// get last row
$oldOperationsRow = $sheet->rangeToArray('A' . $highestRow . ':' . $highestColumn . $highestRow, null, true, false)[0];
// transform array to collection to manage elements
$operationColumns = collect($parameters['operationColumns']);
// create empty operations row to be filled after
$operationsRow = array_fill(0, count($headers) - 1, '');
// set operations columns row
// key from headers correspond with the key from oldOperationColumns
foreach ($headers as $key => &$columnName) {
// first, check if column has any operation on itself
$operationColumn = $operationColumns->where('column', $columnName);
if ($operationColumn->count() > 0) {
// set operation
$operationColumn = $operationColumn->first();
switch ($operationColumn['operation']) {
case 'sum':
$operationsRow[$key] = $objects->sum($columnName) + $oldOperationsRow[$key];
// sum old operations row
break;
}
}
}
// remove operations columns row
$sheet->removeRow($highestRow);
}
// set operations row and styles
if (isset($parameters['operationColumns'])) {
// set new data in spreadsheet, but overwrite old operations row
$sheet->fromArray($objects->toArray(), null, 'A' . $sheet->getHighestRow(), false, false);
$sheet->appendRow($operationsRow);
$sheet->cells('A' . $sheet->row(0)->getHighestRow() . ':' . $sheet->row(0)->getHighestDataColumn() . $sheet->row(0)->getHighestRow(), function ($cells) {
$cells->setBackground('#F8F8F8');
$cells->setFontWeight('bold');
});
} else {
// set new data in spreadsheet
$sheet->fromArray($objects->toArray(), null, 'A' . ($sheet->getHighestRow() + 1), false, false);
}
});
}, null, true)->store($advancedSearch->extension_file_022);
if ($lastInteraction) {
// get user to send email
$user = $advancedSearch->getUser;
// send email to user
$dataMessage = ['emailTo' => $user->email_010, 'nameTo' => $user->name_010 . ' ' . $user->surname_010, 'subject' => trans('pulsar::pulsar.message_advanced_search_exports'), 'token' => Crypt::encrypt($advancedSearch->id_022), 'advancedSearch' => $advancedSearch];
Mail::send('pulsar::emails.advanced_search_exports_notification', $dataMessage, function ($m) use($dataMessage) {
$m->to($dataMessage['emailTo'], $dataMessage['nameTo'])->subject($dataMessage['subject']);
});
// send mail
$advancedSearch->created_022 = true;
// todo, delete advanced search from database?
} else {
// set next paginate parameters
$parameters['start'] = $parameters['start'] + $parameters['length'];
$advancedSearch->parameters_022 = json_encode($parameters);
}
// save model with new data interaction
$advancedSearch->save();
}
}
