Hi everyone!

I'm new to KoolReport and i'm using the KoolReport Dashboard.

I'm following the example of Payments (in the Dashboard demo)

I'm building a simple widget of sales amount by date.

I want the data to be grouped by day. But, since since the date is stored in MySQL as DATETIME (0000-00-00 00:00:00) it doesn't add up the sales amount in a day, everything is separated...

How can i convert the date so the sales are grouped by day?

Here's my code (based on the PAYMENTS example in the dashboard demo):

<?php

use \koolreport\dashboard\widgets\google\ColumnChart;
use \koolreport\dashboard\fields\Text;
use \koolreport\dashboard\fields\DateTime;
use \koolreport\dashboard\fields\Currency;
use \koolreport\processes\Limit;
use \koolreport\processes\DateTimeFormat;

class TransactionsByDate extends ColumnChart
{
    protected function onInit()
    {
        $this
            ->title("Payment By Date")
            //->updateEffect("none")
            ->height("240px");
    }
    protected function dataSource()
    {
        //Get value from the date range picker
        $range = $this->sibling("TransactionsDateRange")->value();
        //Apply to query

        return Incognito::table("wp_wc_order_stats")
                ->whereBetween("date_created",$range)
                ->groupBy("date_created")
                ->sum("net_total")->alias("dayAmount")
                ->select("date_created");

    }

    protected function fields()
    {
        return [

            DateTime::create("date_created")->displayFormat("Y-m-d")->sort("asc"),
            Currency::create("dayAmount")->CAD()->symbol()->decimals(2)

        ];
    }
}

Thanks for your help!

Wow thank you very much! Works fine!

If i have other questions concerning dates, should i continue writing in this thread of start a new topic? Thanks!

Hi again,

the Chart widget works fine...but when i create the Table (with the same query) I get :

Query Error >> [Unknown column 'myDate' in 'group statement'] >> SELECT COUNT(1) FROM wp_wc_order_stats WHERE (date_created >= '2021-08-01 00:00:00' AND date_created <= '2021-08-31 23:59:59') GROUP BY myDate Line: 433 File: /home/ardecom/public_html/reports/koolreport/core/src/datasources/PdoDataSource.php

My code:

<?php

use \koolreport\dashboard\widgets\Table;
use \koolreport\dashboard\fields\Text;
use \koolreport\dashboard\fields\Date;
use \koolreport\dashboard\fields\Currency;

class TransactionsTable extends Table
{


    protected function dataSource()
    {
        //Get value from the date range picker
        $range = $this->sibling("TransactionsDateRange")->value();
        //Apply to query

        return Incognito::table("wp_wc_order_stats")
                ->selectRaw("DATE(date_created) AS myDate")
                ->whereBetween("date_created",$range)
                ->groupBy("myDate")
                ->sum("net_total")->alias("dayAmount");

    }

    protected function fields()
    {
        return [

            Date::create("myDate")->displayFormat("M-d")->sort("asc"),
            Currency::create("dayAmount")->CAD()->symbol()->decimals(2)

        ];
    }
}

Thanks for helping!

Thank you again for the fast reply!

Me again! I started another report type, and anmopther problem with dates!

The date i'm getting now is in UNIX TIMESTAMP, i'm converting it, but now i get on the column chart :

Data column(s) for axis #0 cannot be of type string

Here's my code (the SQL seems correct because it works as a TABLE view) :

protected function dataSource()
    {
        //Get value from the date range picker
        $range = $this->sibling("TransactionsDateRange")->value();
        //Apply to query

                
        return Incognito::rawSQL("
		select
		COUNT(*) AS countTrans, DATE(FROM_UNIXTIME(meta_value)) AS dateOnly
        FROM wp_postmeta
        WHERE meta_key = '_wcpdf_invoice_date'
        	AND FROM_UNIXTIME(meta_value) >'".$range[0]."' and FROM_UNIXTIME(meta_value)<'".$range[1]."'
		group by dateOnly
		");

    }

    protected function fields()
    {
        return [

            Date::create("dateOnly")->displayFormat("Y-m-d")->sort("asc"),
            Text::create("countTrans")
        ];
    }
}

Board :

<?php

use \koolreport\dashboard\Dashboard;
use \koolreport\dashboard\containers\Row;
use \koolreport\dashboard\containers\Panel;
use \koolreport\dashboard\widgets\Text;

class TransactionsBoard extends Dashboard
{
    protected function widgets()
    {
        return [
            Row::create()->sub([
                Row::create(),
                TransactionsDateRange::create()->width(1/3),
            ]),
            Row::create()->sub([
                Panel::create()->sub([
                    TransactionsByDate::create(),
                ])->width(1/1),
            ]),
            TransactionsTable::create(),
        ];
    }
}

Wow OK...it now works...quite the learning curve for me!

Please tell me, is there any more efficient way for me to debug such things?

Right now, in my index.php i'm using App::create()->debugMode(true)->run();

Thank you!!!!! :)