Sum of Time field column in the footer

C

Chris Sideris commented on Jul 29, 2021

Hi,

I am creating a datatable using SQL query datastore

$sql_query = "SELECT TIMEDIFF(check_out, check_in) AS time_diff FROM.........";

$this->src("db")
    ->query($sql_query)
    ->pipe($this->dataStore("dataStore"))
    ->requestDataSending();

and in my view I am grouping the time_diff column to get the total of time which is in H:i:s format (ex: 16:30:23)

"grouping"   => [
    "time_diff" => [
        "calculate" => [
            "{sumTime}" => function ($store) {
                $sum = 0;
                foreach ($store as $row) {
                    list($h, $i, $s) = explode(":", $row["worktime"]);
                    if (is_numeric($h) && is_numeric($i) && is_numeric($s)) {
                        $sum += $h * 3600 + $i * 60 + $s;
                    }
                }
                $h = floor($sum / 3600);
                $i = floor(($sum - $h * 3600) / 60);
                $s = $sum - $h * 3600 - $i * 60;
                return (($h<10) ? "0$h" : $h) .":". (($i<10) ? "0$i" : $i).":".(($s<10) ? "0$s" : $s);
            },
        ],
        "bottom"    => "<td><b>Total Time {sumTime}</b></td>"
    ],
],

but this seems to not generate the sum at the bottom of the datatable

Can you please advise?

S

Sebastian Morales commented on Jul 30, 2021

Chris, pls print out the variables in your grouping function to check their values at each state:

"grouping"   => [
    "time_diff" => [
        "calculate" => [
            "{sumTime}" => function ($store) {
                $sum = 0;
                echo "sum="; echo $sum; echo "<br>";
                foreach ($store as $row) {
                    echo "row="; print_r($row); echo "<br>";
                    list($h, $i, $s) = explode(":", $row["worktime"]);
                    echo "h="; echo $h; echo "<br>";
                    echo "i="; echo $i; echo "<br>";
                    echo "s="; echo $s; echo "<br>";
                    if (is_numeric($h) && is_numeric($i) && is_numeric($s)) {
                        $sum += $h * 3600 + $i * 60 + $s;
                        echo "sum="; echo $sum; echo "<br>";
                    }
                }
                $h = floor($sum / 3600);
                $i = floor(($sum - $h * 3600) / 60);
                $s = $sum - $h * 3600 - $i * 60;
                echo "h="; echo $h; echo "<br>";
                echo "i="; echo $i; echo "<br>";
                echo "s="; echo $s; echo "<br>";
                $sumTime = (($h<10) ? "0$h" : $h) .":". (($i<10) ? "0$i" : $i).":".(($s<10) ? "0$s" : $s);
                echo "s="; echo $h; echo "<br>";
                return $sumTime;
            },
        ],
        "bottom"    => "<td><b>Total Time {sumTime}</b></td>"
    ],
],

C

Chris Sideris commented on Jul 30, 2021

I tried the exact code but with no luck

K

KoolReport commented on Jul 30, 2021

Are you using koolreport\datagrid\Datatables or koolreport\widgets\koolphp\Table? This "grouping" property is used inside koolphp\Table only

C

Chris Sideris commented on Jul 30, 2021

I am using using koolreport\datagrid\DataTables is there any way I can do a sum of this time field?

K

KoolReport commented on Jul 30, 2021

For DataTables, you use ClientRowGroup.

S

Sebastian Morales commented on Aug 2, 2021

Chris, if you want to use "grouping" property like this, the core Table widget should be used. "grouping" uses server-rendering groups. With datagrid\DataTables we have a client rendering grouping called "ClientRowGroup". You would have to split and sum the time with javascript. Rgds,

C

Chris Sideris commented on Aug 5, 2021

Thanks for the help Koolreport and Sebastian. I also worked another solution for this listed below.

I was able to do it by getting the time in seconds instead of HH:MM:SS from SQL query and once I generated the Datagrid's Datatable I formatted the value to look like HH:MM:SS and then sum of all the seconds in format HH:MM:SS was taken care by Koolreport's

"footer" => "sum"

$sql_query = "SELECT TIME_TO_SEC(TIMEDIFF(check_out, check_in)) AS time_diff FROM.........";

$this->src("db")
    ->query($sql_query)
    ->pipe($this->dataStore("dataStore"))
    ->requestDataSending();

"time_diff"       => [
    "label"       => "Time",
    "formatValue" => function ($val) {
        if ($val != "-") {
            $sec  = $val % 60;
            $min  = floor(($val % 3600) / 60);
            $hour = floor(($val % 86400) / 3600);

            // Ensure all values are 2 digits, prepending zero if necessary.
            $sec_str  = $sec == 0 ? 0 : $sec;
            $min_str  = $min == 0 ? 0 : $min . ':';
            $hour_str = $hour == 0 ? 0 : $hour . ':';

            return $hour_str . $min_str . $sec_str;
        } else {
            return "";
        }
    },
    "footer"      => "sum"
]

KoolReport's Forum

Sum of Time field column in the footer #2239

Build Your Excellent Data Report