Laravel Dashboard Koolreport - Excel Issue & Filters not showing

Valerio Valentini commented on Sep 7, 2023

Good evening,

I'm using koolreport dashboard and I'm trying to implement the Excel download of a table and filters with a select2 as in the demos but I have the following problems.

Excel downloads a corrupted file to me.

The filters I wanted to integrate instead are those in this link ( HighCreditFilter ) but they don't even appear above the table.

I'm following the documentation step by step but I'm not leaving it, I'll attach the code.

User.php

<?php

use koolreport\dashboard\Client;
use \koolreport\dashboard\Dashboard;
use koolreport\dashboard\inputs\Button;
use koolreport\dashboard\admin\Resource;
use \koolreport\dashboard\containers\Row;

class User extends Dashboard
{
    protected function content()
    {
        return [
            Row::create()->sub([
                Row::create(),
                UserDateRange::create()->width(1/3),
            ]),
            Row::create()->sub([
                TotalCustomers::create()->width(1/3),
                InternalUsersSessions::create()->width(1/3),
                ExternalUsersSessions::create()->width(1/3),
            ]),
            Button::create()->text("Export to Excel")->onClick(
                Client::widget("UserTable")->exportToXLSX()
            ),
            UserTable::create()->xlsxExportable([
                "rawData" => true, // use raw data instead of formatted data
                "useTable"=>false, // export to chart format instead of table one
            ]) ,
        ];
    }

    protected function filters()
    {
        return [
            CompletatoFilter::create()->title("Completato")
        ];
    }


}

UserTable.php

<?php

use \koolreport\dashboard\widgets\Table;
use \koolreport\dashboard\fields\Number;
use \koolreport\dashboard\fields\Text;
use \koolreport\dashboard\fields\Date;
use \koolreport\dashboard\fields\Badge;

use \koolreport\dashboard\containers\Html;

class UserTable extends Table
{

    protected function onCreated()
    {
        $this->pageSize(10)
        ->tableHover(true)
        ->tableStriped(true)
        ->searchable(true)
        ->searchAlign("right")
        ->searchWidth("300px")
        ->tableResponsive(true)
        ->pageSizeOptions([5,10,50,100]);
    }

    protected function dataSource()
    {
        $range = $this->sibling("UserDateRange")->value();

        $array = AutoMaker::table('users')
        ->join('session_user', 'users.id', '=', 'session_user.user_id')
        ->join('sessions', 'session_user.session_id', '=', 'sessions.id')
        ->join('courses', 'sessions.course_id', '=', 'courses.id')
        ->join('clients', 'users.client_id', '=', 'clients.id')
        ->selectRaw("CONCAT(users.name, ' ', users.familyname) as utente")
        ->selectRaw("users.email as email")
        ->selectRaw("clients.name as azienda")
        ->selectRaw("sessions.title as sessione")
        ->selectRaw("courses.code as codice")
        ->selectRaw("sessions.duration as durata")
        ->selectRaw("CASE WHEN session_user.completed = 0 THEN 'Non Completato' WHEN session_user.completed = 1 THEN 'Completato' END as status")
        ->selectRaw("CASE WHEN users.email LIKE '%se.com' THEN 'Si' ELSE 'No' END as interno")
        ->selectRaw("CASE WHEN session_user.completed = 0 THEN '' WHEN session_user.completed = 1 THEN DATE_FORMAT(session_user.updated_at, '%d/%m/%Y') END as datacompletamento")
        ->where('users.email', 'not like', '%mindthehead%')
        ->where('users.email', 'not like', '%testmth16%')
        ->where('users.email', 'not like', '%consulenti.nposistemi.it%')
        ->whereBetween("session_user.created_at",$range)
        ->orderBy('utente')
        ->run();

        return $array;
    }

    protected function onExporting($params)
    {

        //Base on parameters received, you can do custom settings for your chart
        //before it getting exported.

        return true; //Return true to allow exporting, return false will cancel exporting
    }

    protected function fields()
    {
        return [
            // Campo utente
            Text::create("utente")
                ->label("Utente")
                ->searchable(true)
                ->sortable(true),

            // Campo email
            Text::create("email")
                ->label("Email")
                ->searchable(true)
                ->sortable(true),

            // Campo azienda
            Text::create("azienda")
                ->label("Azienda")
                ->searchable(true)
                ->sortable(true),

            // Campo sessione
            Text::create("sessione")
                ->label("Sessione")
                ->searchable(true)
                ->sortable(true),

            // Campo codice
            Text::create("codice")
                ->label("Codice")
                ->searchable(true)
                ->sortable(true),

            // Campo durata
            Text::create("durata")
                ->label("Durata (h)")
                ->searchable(true)
                ->sortable(true),

            // Campo status
            Badge::create("status")->type(function($status){
                switch($status){
                    case "Completato":
                        return "success";
                    case "Non Completato":
                        return "danger";
                    default:
                        return "default";
                }
            })
            ->sortable(true),

            // Campo interno
            Text::create("interno")
                ->label("Interno")
                ->searchable(true)
                ->sortable(true),

            // Campo datacompletamento
            Text::create("datacompletamento")
                ->label("Data Completamento")
                ->searchable(true)
                ->sortable(true),
        ];
    }

}

CompletatoFilter.php

<?php

use koolreport\dashboard\admin\filters\ToggleFilter;

class CompletatoFilter extends ToggleFilter
{
    protected function apply($query, $bool)
    {
        $query->where("completed",1);
        return $query;
    }
}

App.php

<?php
//App.php


///Menus
use \koolreport\dashboard\menu\Group;
use \koolreport\dashboard\menu\Section;
use \koolreport\dashboard\Application;

///Themes
use koolreport\dashboard\languages\DE;
use koolreport\dashboard\languages\EN;

///Pages
use koolreport\dashboard\languages\ES;

///Languages
use koolreport\dashboard\languages\FR;
use koolreport\dashboard\languages\IT;
use \koolreport\dashboard\menu\MegaMenu;
use \koolreport\dashboard\menu\MenuItem;
use \koolreport\amazing\dashboard\Amazing;
use \koolreport\appstack\dashboard\AppStack;

///Imports
use \koolreport\dashboard\ExportHandler;
use \koolreport\dashboard\export\XLSXEngine;


class App extends Application
{

    protected function sidebar()
    {
        return [
            "Sessioni"=> Section::create()->sub([
                "Sessioni utenti"=> User::create()->icon("fas fa-users"),
            ])
        ];
    }

    protected function languages()
    {
        return [
            EN::create(),
            ES::create(),
            FR::create(),
            DE::create(),
            IT::create()
        ];
    }

    // protected function topMenu()
    // {
    //     return [
    //         "Simple link"=>MenuItem::create()
    //             ->href("https://www.anywebsite.com")
    //             ->target("_blank"),

    //         "With Icon and Badge"=>MenuItem::create()
    //             ->href("https://www.example.com")
    //             ->icon("fa fa-book")
    //             ->badge(["NEW","danger"]),

    //         "Execute javascript"=>MenuItem::create()
    //             ->onClick("alert('hola')"),

    //         "Mega Menu"=>MegaMenu::create()->sub([
    //             "Group 1"=>Group::create()->sub([
    //                 "Item 11"=>MenuItem::create(),
    //                 "Item 12"=>MenuItem::create(),
    //             ]),
    //             "Group 2"=>Group::create()->sub([
    //                 "Item 21"=>MenuItem::create(),
    //                 "Item 22"=>MenuItem::create(),
    //             ]),
    //         ]),

    //         "Disabled Item"=>MenuItem::create()->disabled(true),
    //     ];
    // }


    protected function onCreated()
    {
         $this->debugMode(true)
          ->appKey(env('APP_KEY'))
         ->title(env('APP_NAME'))
         ->favicon("favicon-geco.ico")
         ->footerLeft("")  //Lasciati vuoti altrimenti di default prendono quelli della libreria
         ->footerRight("") //Lasciati vuoti altrimenti di default prendono quelli della libreria
         ->csrf(
            \koolreport\dashboard\security\CSRF::create()
            ->token(function(){
                return csrf_token();
            })
          )  //Token importante per il funzionamento

         ->theme(Amazing::create());

    }

    protected function export()
    {
        return ExportHandler::create()
                ->storage(dirname(__DIR__)."/storage/report")
                ->engine( // equivalent to xlsxEngine()
                    XLSXEngine::create()
                        ->defaultConfig([
                        ])
                        ->rawData(true)
                        ->useTable(true)
                );
    }

}

This is what I see and everything works, except the export and the "CompletedFilter" filter which does not appear.

What am I forgetting? I've been stuck for days

Thanks in advance

Sebastian Morales commented on Sep 8, 2023

Regarding the filter, pls add the filter creating in method content() to show it:

    protected function content()
    {
        return [
            Row::create()->sub([
                Row::create(),
                UserDateRange::create()->width(1/3),
            ]),
            Row::create()->sub([
                TotalCustomers::create()->width(1/3),
                InternalUsersSessions::create()->width(1/3),
                ExternalUsersSessions::create()->width(1/3),
            ]),
            Button::create()->text("Export to Excel")->onClick(
                Client::widget("UserTable")->exportToXLSX()
            ),
            CompletatoFilter::create()->title("Completato"),
            UserTable::create()->xlsxExportable([
                "rawData" => true, // use raw data instead of formatted data
                "useTable"=>false, // export to chart format instead of table one
            ]) ,
        ];
    }

As for excel export, would you pls email us the corrupted xlsx file to support@koolreport.com for us to check it for you.

Valerio Valentini commented on Sep 8, 2023

Thanks for the reply, unfortunately I get an error if I insert that into content(). I also tried putting it only in content by removing the filters() function

<?php

use koolreport\dashboard\Client;
use \koolreport\dashboard\Dashboard;
use koolreport\dashboard\inputs\Button;
use koolreport\dashboard\admin\Resource;
use \koolreport\dashboard\containers\Row;

class User extends Dashboard
{
    protected function content()
    {
        return [
            Row::create()->sub([
                Row::create(),
                UserDateRange::create()->width(1/3),
            ]),
            Row::create()->sub([
                TotalCustomers::create()->width(1/3),
                InternalUsersSessions::create()->width(1/3),
                ExternalUsersSessions::create()->width(1/3),
            ]),
            Button::create()->text("Export to Excel")->onClick(
                Client::widget("UserTable")->exportToXLSX()
            ),
            CompletatoFilter::create()->title("Completato"),
            UserTable::create()->xlsxExportable([
                "rawData" => true, // use raw data instead of formatted data
                "useTable"=>false, // export to chart format instead of table one
            ]) ,
        ];
    }

    protected function filters()
    {
        return [
            CompletatoFilter::create()->title("Completato")
        ];
    }


}

For Excel files, it saves them with the data inside the storage/report with an "encrypted" name, but when I try to download it from the browser it gives me a network error and doesn't download anything

SCREEN 1 Excel

SCREEN 2 Excel

Sebastian Morales commented on Sep 11, 2023

Ah, probably what you want to show should be the input widget \koolreport\dashboard\inputs\Toggle instead of AdminPanel's filter koolreport\dashboard\admin\filters\ToggleFilter:

Dashboard's Toggle

As for excel export, pls try this and let us know the result:

            UserTable::create()->xlsxExportable(true) ,

Valerio Valentini commented on Sep 11, 2023

Thanks for the reply, unfortunately Excel doesn't work, I attach a screen shot, it says "Check your network connection" but in reality it works correctly.

As I was saying, the file is generated and saved with the correct data within Laravel storage but with an encrypted name as per the previous screens.

As for the filters, I was trying to implement the ones in this screen "Countries" and "Credit Limit" but they don't work..

Sebastian Morales commented on Sep 12, 2023

Can you pls try our dashboard-demo source code on your server and see if its excel export works ok?

https://github.com/koolreport/dashboard-demo

Regarding the toggle filter, pls copy the toggle board of dashboard-demo:

//InProcessToggle.php
use \koolreport\dashboard\inputs\Toggle;

class InProcessToggle extends Toggle
{
    protected function onCreated()
    {
        $this
        ->showText(true)
        ->type("info")
        ->defaultValue(1);
    }

    protected function actionChange() // when this toggle changed, update Orders table
    {
        $this
        ->sibling("OrderTable")
        ->pageIndex(0)
        ->updateEffect("none")
        ->update();
    }
} 

//OrderTable.php
class OrderTable extends Table
{
    protected function onCreated()
    {
        $this->pageSize(10);
    }


    protected function dataSource()
    {
        $query = AutoMaker::table("orders")
                ->select("orderNumber","orderDate","status");

        if($this->sibling("InProcessToggle")->value()==1) { // use toggle's value when select Order table data
            return $query->where("status","In Process");
        }
        
        return $query;
    }

Valerio Valentini commented on Sep 12, 2023

Thanks for the reply, it works with the toggle, although I still wanted to use other tools and not the toggle.

For export, however, it only works with CSV instead of Excel.

Sebastian Morales commented on Sep 19, 2023

With our Dashboard's Excel export example, what happened when you click Export to Excel? Pls provide the error message with screenshots if possible. If there's an excel file with a hashed name generated in Laravel storage pls email it to support@koolreport.com for us to check it for you. Tks,

KoolReport's Forum

Laravel Dashboard Koolreport - Excel Issue & Filters not showing #3141

Build Your Excellent Data Report