public function downloadAReportTask(Request $request)
{
// get parameters from url route
$parameters = $request->route()->parameters();
$reportTask = ReportTask::builder()->find(Crypt::decrypt($parameters['token']));
$filename = Crypt::decrypt($parameters['filename']);
if ($reportTask === null) {
abort(404);
}
return response()->download(storage_path('exports') . '/' . $filename . '.' . $reportTask->extension_file_023);
}
public function testing()
{
$reportTasks = ReportTask::builder()->get();
foreach ($reportTasks as $reportTask) {
// Execute query from report task
$response = DB::select(DB::raw($reportTask->sql_023));
// if has results from query
if (count($response) === 0) {
dd('no hay resultados');
}
// format response to manage with collections
$response = collect(array_map(function ($item) {
return $item;
}, $response));
dd($response);
}
}
public function updateCustomRecord($parameters)
{
// get data about frequency
$frequency = Cron::getFrequencyData((int) $this->request->input('frequency'));
ReportTask::where('id_023', $parameters['id'])->update(['email_023' => $this->request->input('email'), 'cc_023' => $this->request->input('jsonCcEmails'), 'subject_023' => $this->request->input('subject'), 'filename_023' => $this->request->input('filename'), 'extension_file_023' => $this->request->input('extensionFile'), 'frequency_023' => $this->request->input('frequency'), 'from_023' => $this->request->has('from') ? \DateTime::createFromFormat(config('pulsar.datePattern') . ' H:i', $this->request->input('from'))->getTimestamp() : null, 'until_023' => $this->request->has('until') ? \DateTime::createFromFormat(config('pulsar.datePattern') . ' H:i', $this->request->input('until'))->getTimestamp() : null, 'delivery_day_023' => $this->request->has('delivery_day') ? $this->request->input('delivery_day') : null, 'next_run_023' => $frequency['nextRun'], 'parameters_023' => null, 'sql_023' => $this->request->input('sql')]);
}
/**
* Function to return data from query, return false if has not any result
*
* @param ReportTask $reportTask
* @param string $action
* @return boolean
*/
public static function executeReportTask(ReportTask $reportTask, $action = 'store')
{
$parameters = [];
if ($reportTask->frequency_023 == 1) {
$parameters['from'] = $reportTask->from_023;
$parameters['until'] = $reportTask->until_023;
}
// get data about frequency
$frequency = self::getFrequencyData($reportTask->frequency_023, $parameters);
// replace wildcards
if (isset($frequency['from'])) {
$reportTask->sql_023 = str_replace("#FROM#", $frequency['from'], $reportTask->sql_023);
}
if (isset($frequency['until'])) {
$reportTask->sql_023 = str_replace("#UNTIL#", $frequency['until'], $reportTask->sql_023);
}
// Execute query from report task
$objects = DB::select(DB::raw($reportTask->sql_023));
// if has results from query
if (count($objects) == 0) {
return false;
}
// *************************************************************
// transform string data to number data, to operate with excel
// *************************************************************
foreach ($objects as &$object) {
$fields = get_object_vars($object);
foreach ($fields as $key => $value) {
if (is_numeric($value) && strpos($value, '.') === false) {
$object->{$key} = (int) $value;
} elseif (is_numeric($value) && strpos($value, '.') !== false) {
$object->{$key} = (double) $value;
}
}
}
// format response to manage with collections
$objects = collect(array_map(function ($item) {
return collect($item);
}, $objects));
// ***************************
// get sum columns from numerics fields
// ***************************
$object = $objects->first();
$operationsRow = [];
$object->map(function ($item, $key) use(&$operationsRow, $objects) {
if (is_numeric($item)) {
$operationsRow[$key] = $objects->sum($key);
} else {
$operationsRow[$key] = null;
}
});
$filename = $reportTask->filename_023 . '-' . uniqid();
// create spreadsheet to export data
$excel = Excel::create($filename, function ($excel) use($objects, $operationsRow) {
// set the title
$excel->setTitle('Report')->setCreator('Pulsar')->setCompany('SYSCOVER');
// set sheet
$excel->sheet('Data', function ($sheet) use($objects, $operationsRow) {
// get keys from first element
$headers = $objects->first()->keys()->toArray();
// set data and headers
$sheet->prependRow($headers);
$sheet->fromArray($objects->toArray(), null, 'A2', true, false);
$sheet->cells('A1:' . $sheet->row(0)->getHighestDataColumn() . '1', function ($cells) {
$cells->setBackground('#CCCCCC');
$cells->setFontWeight('bold');
});
// append row with sum numeric columns
if (count($operationsRow) > 0) {
$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');
});
}
});
});
// get excel option
if ($action == 'store') {
$excel->store($reportTask->extension_file_023);
// transform json to array and get ccEmails object
$ccEmailsJson = json_decode($reportTask->cc_023);
$ccEmails = [];
if (is_array($ccEmailsJson) && count($ccEmailsJson) > 0) {
foreach ($ccEmailsJson as $ccEmail) {
$ccEmails[] = $ccEmail->label;
}
}
// delivery reports
$dataMessage = ['emailTo' => $reportTask->email_023, 'cc' => $ccEmails, 'subject' => $reportTask->subject_023, 'token' => Crypt::encrypt($reportTask->id_023), 'filename' => Crypt::encrypt($filename), 'reportTask' => $reportTask];
Mail::send('pulsar::emails.report_task_notification', $dataMessage, function ($m) use($dataMessage) {
$m->to($dataMessage['emailTo'])->subject($dataMessage['subject']);
// send copy to alternative emails
foreach ($dataMessage['cc'] as $cc) {
$m->cc($cc);
}
});
} else {
$excel->download($reportTask->extension_file_023);
}
// updates time stamp of executions
$reportTask->last_run_023 = $frequency['lastRun'];
$reportTask->next_run_023 = $frequency['nextRun'];
$reportTask->save();
return true;
}