Create report from query with export CSV

This is generic Yii code to generate a simple report from an SQL query. The query has no parameters.

QueryController.php

Th elast method/funciton needs to be changed. The methid can also be copied as much as needed.

Change the code that is labeled with < and > in the last function.

Open the controller the usueal way: query/<name of action>

<?php

namespace app\controllers;

use Yii;
use app\models\Resultaat;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;

use yii\filters\AccessControl;

use yii\helpers\ArrayHelper;

/**
 * BeoordelingController implements the CRUD actions for Beoordeling model.
 */
class QueryController extends Controller
{
    /**
     * {@inheritdoc}
     */
    public function behaviors()
    {
        return [
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'delete' => ['POST'],
                ],
            ],
            'access' => [
                'class' => AccessControl::className(),
                'rules' => [
                    // when logged in, any user
                    [ 'actions' => [],
                        'allow' => true,
                        'roles' => ['@'],
                    ],
                ],
            ],

        ];
    }


    private function executeQuery($sql, $title="no title", $export=false) {

        $result = Yii::$app->db->createCommand($sql)->queryAll();

        if ($result) { // column names are derived from query results
            $data['col']=array_keys($result[0]);
        }
        $data['row']=$result;
        
        if ($export) {
            $this->exportExcel($data);
        } else {
            $data['title']=$title;
            return $data;
        }

    }

    public function exportExcel($data) {
        header('Content-type: text/csv');
        header('Content-Disposition: attachment; filename="canvas-export' . date('YmdHi') .'.csv"');

        foreach ($data['col'] as $key => $value) {
            echo $value.", ";
        }
        echo "\n";
        foreach ($data['row'] as $line) {
            foreach ( $line as $key => $value) {
                echo $value.", ";
            }
            echo "\n";
        }
        
    }

    public function action<Naam>($sort='desc', $export=false) {

      	// sort parameter is optional, export parameter determines if export to CSV needs to be given
      	// specify query
        $sql="select column1 Header1, column2 Header2,.... from .... order by 3 $sort";

      	// specify report title
        $data=$this->executeQuery($sql, <rapport title>, $export);

      	// specify sub title (descr, can also be omitted).
        return $this->render('output', [
            'data' => $data,
            'action' => Yii::$app->controller->action->id,
          	'descr' => <optionele sub tekst onder title>,
        ]);
    }
}

View

The view does not need any adaptations.

<?php
use yii\helpers\Url;
use yii\helpers\Html;
$nr=0;
?>

<div class="card"  style="width: 900px">

    <div class="container">
        <div class="row  align-items-center">
            <div class="col">
                <h1><?= Html::encode($data['title']) ?></h1>
                    <?php
                        if (isset($descr)) {
                            echo "<small>".$descr."</small>";
                        }
                    ?>
                </div>
            <div class="col-md-auto">
                <?= Html::a('Export', [$action.'?export=1'], ['class'=>'btn btn-primary', 'title'=> 'Export to CSV',]) ?>
            </div>
        </div>
    </div>

    <p></p>

    <div class="card-body">
        <table class="table">
            <thead>
                <tr>
                    <?php
                        if ( ! isset($nocount) ) echo "<td>#</td>";
                        if ( $data['row'] ) {
                            for($i=0;$i<count($data['col']);$i++) {
                                echo "<th>".$data['col'][$i]."</th>";
                            }
                        } else {
                            echo "<td>Empty result set</td>";
                        }
                    ?>
            </thead>
            
            <?php
                if ( $data['row'] ) {
                    foreach($data['row'] as $item) {
                        if ( ! isset($nocount) ) {
                            $nr++;
                            echo "<tr>";
                            echo "<td>".$nr."</td>";
                        }
                        for($i=0;$i<count($data['col']);$i++) {
                            echo "<td>".$item[$data['col'][$i]]."</td>";
                        }
                        echo "</tr>";
                    }
                }
            ?>
        </table>
    </div>
</div>