And again about Sort for Dates

Eugene commented on Jan 31, 2019

Hi,

it was discussed many times already and I thought it is clear for me. But .... I have some data with dates. The dates look like this: ["purchDate"]=> string(19) "2019-01-17 14:01:30"

my setup:

$this->src('quinos')
            ->query($query)
            ->params($query_params)
            ->pipe(new Sort(array(
                "purchDate"=>"desc",
                )))
            ->pipe(new Limit(array(20)))
            ->pipe($this->dataStore('purchase'));

In the Table it looks like this:

"dataStore" => $this->dataStore('purchase'),
            "columns" => array(
                "purchDate" => array(
                    "label" => "Date of Purchase",
                ),

But this i get in my report:

KoolReport commented on Jan 31, 2019

We are unable to replicate the issue, could you please export your date column data to csv, we really want to look details on what went wrong.

Eugene commented on Feb 1, 2019

sent to email

KoolReport commented on Feb 1, 2019

My test is very simple:

From your data, here is data.csv:

purchDate
2019-01-17 14:01:30
2018-12-31 22:43:23
2018-12-15 17:27:01
2018-11-15 15:45:30
2018-10-15 21:01:59
2018-09-28 20:55:58
2018-09-17 15:56:04
2018-09-02 16:40:21
2018-08-17 16:30:21
2018-07-24 21:51:07
2018-07-02 16:02:57
2018-06-15 14:21:43
2018-05-20 16:51:14
2018-04-07 17:35:16
2018-02-26 16:44:44
2018-01-28 21:47:45
2017-12-24 17:29:57
2017-11-02 11:20:28
2017-09-25 13:37:52
2017-08-02 08:00:38

Report.php

<?php
use \koolreport\processes\Sort;

class Report extends \koolreport\KoolReport
{
    use \koolreport\clients\Bootstrap;

    public function settings()
    {
        return array(
            "dataSources"=>array(
                "data"=>array(
                    "class"=>'\koolreport\datasources\CSVDataSource',
                    'filePath'=>dirname(__FILE__)."/data.csv",
                )
            )
        );
    }   
    protected function setup()
    {
        $this->src("data")
        ->pipe(Sort::process([
            "purchDate"=>"desc"
        ]))
        ->pipe($this->dataStore("data"));
    }
}

Report.view.php

<html>
    <head>
        <title>Test sorting data</title>
    </head>
    <body>
        <h1>Test Sorting data</h1>
        <?php
        \koolreport\widgets\koolphp\Table::create([
            "dataSource"=>$this->dataStore("data")
        ]);
        ?>
    </body>
</html>

The result is:

purchDate
2019-01-17 14:01:30
2018-12-31 22:43:23
2018-12-15 17:27:01
2018-11-15 15:45:30
2018-10-15 21:01:59
2018-09-28 20:55:58
2018-09-17 15:56:04
2018-09-02 16:40:21
2018-08-17 16:30:21
2018-07-24 21:51:07
2018-07-02 16:02:57
2018-06-15 14:21:43
2018-05-20 16:51:14
2018-04-07 17:35:16
2018-02-26 16:44:44
2018-01-28 21:47:45
2017-12-24 17:29:57
2017-11-02 11:20:28
2017-09-25 13:37:52
2017-08-02 08:00:38

Eugene commented on Feb 1, 2019

Yes i see it works. But what is wrong in my script?

KoolReport commented on Feb 1, 2019

Not sure yet! Looking at your code, all seems right. You please try to query only purchDate, then adding more and more columns. It sound crazy but sometime it is the only way to find out what went wrong, we start with what works first.

Eugene commented on Feb 1, 2019

I've found the reason, but i don't know how to resolve the issue:

in my view file I have a grouping code

            "grouping" => array(
                "ItemName" => array(
                    "calculate" => array(
                            "{min}"=>array("min","unitCost"),
                            "{max}"=>array("max","unitCost"),
                            "{avg}"=>function($store){
                                return $value1=number_format($store->sum("total")/$store->sum("quantity1"), 0, '.', ',');
                            },
                    ),
                    "top" => "<td colspan='2'style=\"background-color: #b6001d; color: white;\"><b>{ItemName} </b></td><td colspan='5'style=\"background-color: #b6001d; color: white;\"><b>For periode: Average Price \"{avg}\" </b>Min Price \"{min}\"; Max Price \"{max}\"</td>",
                ),
            ),

A bit long but i dont know what is important. If I comment this code the sorting works correct. With this code the result as in my first message. What else ...

ItemName is a field from sql query, I don't have a column ItemName

unitCost and total are the columns

quantity1 is also from sql query.

so the report looks like this

without grouping sorting is ok

KoolReport commented on Feb 1, 2019

You do this:

$this->src('quinos')
            ->query($query)
            ->params($query_params)
            ->pipe(new Sort(array(
                "itemName"=>"asc",
                "purchDate"=>"desc",
                )))
            ->pipe($this->dataStore('purchase'));

Adding the "itemName" sorting in front of the date. Let me know if it works.

Eugene commented on Feb 1, 2019

No :-( it did not help.

KoolReport commented on Feb 1, 2019

Hi, please add this as well:

DataTables::create(array(
    ...
    "options"=>array(
                "columnDefs"=>array(
                    array("type"=>"string","targets"=>1),
                ),
            )
));

Please change the "targets" to the position of your column. Remember it is the zero base position, the first column is 0.

Please let me know if it works.

Eugene commented on Feb 1, 2019

No. Does not work. But just to be sure

I use table - not DataTables
The position of the column in the table? So I used 0, because I have


 Table::create(array(
            "dataStore" => $this->dataStore('purchase'),
            "columns" => array(
                "purchDate" => array(
                    "label" => "Date of Purchase",
                ),

KoolReport commented on Feb 1, 2019

Really sorry, It was for another topic :(

Eugene commented on Feb 1, 2019

:-) it is ok :-) I am sure you will find a solution for me also :-)

KoolReport commented on Feb 1, 2019

Let do this:

$this->src('quinos')
            ->query($query)
            ->params($query_params)
            ->pipe(new Sort(array(
                "purchDate"=>"desc",
                )))
            ->pipe(new Sort(array(
                "itemName"=>"asc",
               )))
            ->pipe($this->dataStore('purchase'));

Let me know the result. Please try to change the order of the sorting as well, above I sort by purchDate first then itemName. If it does not work, try to reverse.

Eugene commented on Feb 1, 2019

You know it changes something because the outputs are different but the order is still wrong

Without item sort (old)

With item sort after the date sort

Eugene commented on Feb 1, 2019

Sorting by item first gives the old version of output

KoolReport commented on Feb 1, 2019

Let me consult the dev.team if they have any solution.

Eugene commented on Feb 1, 2019

ok... thanks

Eugene commented on Feb 3, 2019

Hi, do you have any news for me?

KoolReport commented on Feb 4, 2019

I have handled your case to devteam and today they will work on that. I will update you!

KoolReport commented on Feb 4, 2019

Updated from dev.team: The new sorting feature of Table in next version will help to solve this issue.

Eugene commented on Feb 5, 2019

:-) thanks when do you plan to release it?

KoolReport commented on Feb 12, 2019

With the new version of KoolReport 3.25.0, you will add extra sorting in Table:

Table::create(array(
    ...
    "sorting"=>array(
        "purchDate"=>"desc"
    )
))

Eugene commented on Feb 12, 2019

it works! thanks

KoolReport's Forum

And again about Sort for Dates #642

Build Your Excellent Data Report