Sum of Time field column in the footer

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?

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>"
    ],
],

Chris Sideris commented on Jul 30, 2021

I tried the exact code but with no luck

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

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?

KoolReport commented on Jul 30, 2021

For DataTables, you use ClientRowGroup.

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,

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