0

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'
mbenjemaa
  • 79
  • 11
  • You want the data to be filtered when you export to Excel? You need to make that a second Submit button inside the form, with a different name. (See [this question](https://stackoverflow.com/questions/32377221/two-submit-buttons-with-different-values-in-cakephp) for an example.) – Greg Schmidt Jun 25 '18 at 21:15
  • thanks @GregSchmidt I updated my code above. at the end of the Excel button code, I put "return $this->redirect(['action' => 'test.xlsx']);" The excel file is generated but I got this error: Excel cannot open the file ... because the file format is not valid. Verify that the file has not been corrupted... – mbenjemaa Jun 26 '18 at 13:13
  • If you've left the `debug` statements in there, then that output may be included in the Excel output, which would definitely make it invalid. – Greg Schmidt Jun 26 '18 at 15:01
  • thanks. I deleted the debug lines. Now I got the excel. it atill includes all the rows, not the filtered rows with the dropdown – mbenjemaa Jun 26 '18 at 15:05
  • It looks wrong to me for you to be redirecting to `test.xlsx`. You have a separate action for handling that? You're not passing the posted data to it in the URL. But that seems like the wrong solution anyway; you should handle it in this action (though you might have a separate, private function for generating that output). There's also no need to replicate 100% of your code inside the main `if`. All the setup of the query is the same whether it's Excel output or not, so do that outside the `if`, and only change up the actual output generation inside the `if`. – Greg Schmidt Jun 26 '18 at 15:09
  • is a duplication of my initial code. I created a view test.ctp, another test.ctp under AssetsAssetignations/xlsx folder, and a test function in my controller. I think it correct like that. – mbenjemaa Jun 26 '18 at 15:13
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/173828/discussion-between-mbenjemaa-and-greg-schmidt). – mbenjemaa Jun 26 '18 at 15:13

1 Answers1

1

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;
mbenjemaa
  • 79
  • 11
Greg Schmidt
  • 5,010
  • 2
  • 14
  • 35