in my view, I have 2 buttons, one in a form to filter data based on a dropdown list, and another to export the filtered data to Excel.
<?php
$options = array();
$options[0] = 'All';
$options[1] = 'Due last 6 months';
$options[2] = 'Due current month';
$options[3] = 'Due next 3 months';
$options[4] = 'Due next 6 months';
$options[5] = 'Due next 12 months';
?>
<div class="row">
<?= $this->Form->create() ?>
<fieldset>
<div class="row">
<div class="col-xs-3"><?= $this->Form->input('select_period_id', ['options' => $options, 'empty' => false, 'label' => __('Select Period')]) ?></div>
<div class="col-sm-2" style="padding-top:25px;"><?= $this->Form->button(__('Search'), ['class' => 'btn btn-primary']) ?></div>
</div>
</fieldset>
<?= $this->Form->end() ?>
</div>
<div class="timetables index large-9 medium-8 columns content">
<?= $this->Html->link(__('Export to xlsx'), array_merge($this->request->query, ['_ext' => 'xlsx']), ['class' => 'btn btn-primary']) ?>
</div>
I assumed that the first button is a POST, and the second is a GET. I was able to have the filtered data with the first button, but not with the second:
$data = $this->request->data;
$select_period = $this->request->data('select_period_id');
$today = Time::now()->format('Y-m-d');
$second_date = Time::now();
$assetsAssignations = $this->AssetsAssignations->find()
->contain(['Assets']);
if($this->request->is(['patch', 'post', 'put']))
{
if ($select_period == 0) {
$second_date = $second_date->modify('-6 months');
$second_date = $second_date->format('Y-m-d');
} elseif ($select_period == 1) {
$second_date = $second_date->modify('-6 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'end_date >=' => $second_date,
'end_date <=' => $today
]);
// etc..
} elseif($this->request->is(['get']))
{
???
}
$_filename = "xls_report_replacement_" . date('Ymd');
$this->set(compact('assetsAssignations', '_filename'));
}
I did not find what should I put when it the the export to Excel button (GET).
Any help please ?
_ update: I tried this, and I got an error when opening the excel file
{
$data = $this->request->data;
$select_period = $this->request->data('select_period_id');
$today = Time::now()->format('Y-m-d');
$second_date = Time::now();
$assetsAssignations = $this->AssetsAssignations->find()
->contain('Assets');
debug($this->request->data);
if (isset($this->request->data['btn1']))
{
if ($select_period == 0) {
$second_date = $second_date->modify('-6 months');
$second_date = $second_date->format('Y-m-d');
} elseif ($select_period == 1) {
$second_date = $second_date->modify('-6 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $second_date,
'Assets.life_end_date <=' => $today
]);
} elseif ($select_period == 2) {
$second_date = $second_date->modify('1 month');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 3) {
$second_date = $second_date->modify('3 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 4) {
$second_date = $second_date->modify('6 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 5) {
$second_date = $second_date->modify('12 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
}
} elseif (isset($this->request->data['btn2'])) {
if ($select_period == 0) {
$second_date = $second_date->modify('-6 months');
$second_date = $second_date->format('Y-m-d');
} elseif ($select_period == 1) {
$second_date = $second_date->modify('-6 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $second_date,
'Assets.life_end_date <=' => $today
]);
} elseif ($select_period == 2) {
$second_date = $second_date->modify('1 month');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 3) {
$second_date = $second_date->modify('3 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 4) {
$second_date = $second_date->modify('6 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 5) {
$second_date = $second_date->modify('12 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
}
debug($select_period);
die();
return $this->redirect(['action' => 'test.xlsx']);
}
$_filename = "xls_report_replacement_" . date('Ymd');
$this->set(compact('assetsAssignations', '_filename'));
}
In my view, I included the second button in the form:
<div class="row">
<?= $this->Form->create('form_name') ?>
<fieldset>
<div class="row">
<div class="col-xs-3"><?= $this->Form->input('select_period_id', ['options' => $options, 'empty' => false, 'label' => __('Select Period')]) ?></div>
<div class="col-sm-2" style="padding-top:25px;"><?php echo $this->Form->submit('Search', array('name'=>'btn1'))?></div>
<div class="col-sm-2" style="padding-top:25px;"><?php echo $this->Form->submit('Excel', array('name'=>'btn2'))?></div>
</div>
</fieldset>
<?= $this->Form->end() ?>
</div>
When I debug with the first button:
[
'select_period_id' => '1',
'btn2' => 'Excel'
]
select_period_id = '1'
Debug with the Excel button:
[
'select_period_id' => '2',
'btn2' => 'Excel'
]
select_period_id = '2'
By including debug statements when you're generating an xlsx file, you are corrupting the output. And by redirecting to test.xlsx without any parameters, you are losing your posted data. You could mess around with something like this:
$this->redirect(['action' => 'test.xlsx', 'select_period_id' => $select_period])
But that's ugly. Instead of redirecting, just set the response type like so:
$this->RequestHandler->renderAs($this, 'xlsx');
Also, you have a lot of duplicated code; much of it can be eliminated.
And you shouldn't need to format dates before passing them to the query builder, by switching from the Time class to Date.
Here's my suggested version:
{
$data = $this->request->data;
$select_period = $this->request->data('select_period_id');
$today = FrozenDate::now();
$assetsAssignations = $this->AssetsAssignations->find()
->contain('Assets');
// This block could be done with a switch instead of if-elseif
if ($select_period == 0) {
$start_date = $end_date = null;
} elseif ($select_period == 1) {
// Alternately, $start_date = $today->subMonths(6);
$start_date = $today->modify('-6 months');
$end_date = $today;
} elseif ($select_period == 2) {
$start_date = $today;
// Alternately, $start_date = $today->addMonth();
$end_date = $today->modify('1 month');
} elseif ($select_period == 3) {
$start_date = $today;
// Alternately, $start_date = $today->addMonths(3);
$end_date = $today->modify('3 months');
} elseif ($select_period == 4) {
$start_date = $today;
$end_date = $today->modify('6 months');
} elseif ($select_period == 5) {
$start_date = $today;
$end_date = $today->modify('12 months');
}
if ($start_date) {
$assetsAssignations->where([
'Assets.life_end_date >=' => $start_date,
'Assets.life_end_date <=' => $end_date
]);
}
// Personally, I'd rename this from btn2 to something like xlsx, here and in the form template
if (isset($this->request->data['btn2'])) {
$this->RequestHandler->renderAs($this, 'xlsx');
}
$_filename = "xls_report_replacement_" . date('Ymd');
$this->set(compact('assetsAssignations', '_filename'));
}
As you can see, much shorter code, and it should be functionally identical.
Update: In the controller, add on top:
use Cake\I18n\FrozenDate;