KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

New column sum from multi coumns #1810

Open Ron opened this topic on on Dec 30, 2020 - 11 comments

Ron commented on Dec 30, 2020

Hi, I have a report that is generating dynamically 29-31 columns as days of month depending on the month type. each day has a different number value. I want to add another column that will sum all the numbers in the generated days column.

here is the report code:

function setup()
    {
        $this->month = date('m', strtotime($this->params["date"]));
        $this->year = date('Y', strtotime($this->params["date"]));
        $this->monthDays = cal_days_in_month(CAL_GREGORIAN, $this->month, $this->year);
        $this->src('db')
        ->query('CALL getTeacherAttendancePerMonth(:year, :date)')
        ->params(array(
            ":year"=>$this->params["year"],
            ":date"=>$this->params["date"]
        ))
        ->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row, $metaData, $index, $mapState) {
                $days = array('א','ב','ג','ד','ה','ו','ש');
                //$month = date('m', strtotime($this->params["date"]));
                //$year = date('Y', strtotime($this->params["date"]));
                $newRow = [];
                foreach ($row as $k => $v) {
                    if (is_numeric($k))
                        $k = 1*$k;
                        if ($k >= 1 && $k <= 31) { //i.e $k is month day
                            $weekday = date('w', strtotime($this->year.'-'.$this->month.'-'.$k));
            		        $newRow[$k. " - " . $days[$weekday]] = $row[$k];
                        } else {
                            //echo $k."<br>";
                            $originalK = $k;
                            if ( in_array($k, array('teacher_id','id_number','hour_group_id', 'total_hours', 'reason_id') ) ) {
                                $k = lang('tts.'.$k);
                            }
                            $newRow[$k] = $row[$originalK];
                            //$row[$originalK] = number_format($substituteDays * $row['cost'], 2);
                        }
                    }
                    return $newRow;
                }
        )))
        ->pipe($this->dataStore("st"));
    }
<?php
        DataTables::create(array(
            "dataSource"=>$this->dataStore("st"),
            "removeDuplicate"=>array("id_number","teacher_id","total_hours"),
            "options"=>array(
                "searching"=>true,
                "paging"=>true,
                'columnDefs' => array(
        			array(
        				'visible' => false,
        				//'targets' => [$this->monthDays+6], //hide the first column
        			)
        		),
            ),
            'complexHeaders' => true,
            'headerSeparator' => ' - ',
            'cssClass'=>array(
                'table'=>'table table-bordered',
                'tr'=>'cssItem',
                'td'=>function($row,$colName)
                {
                    return in_array($colName, array('teacher_id','id_number', 'reason')) ? 'text-right' : 'text-center';
                },
                'th'=>function($colName)
                {
                    return in_array($colName, array('teacher_id','id_number','reason')) ? 'table-dark text-right' : 'table-dark text-center';
                },
            ),
        ));
        echo '<div class="footer print-only">Footer</div>';
        ?>
Ron commented on Dec 30, 2020

Please see attached a screen capture of the report.

Ron commented on Dec 30, 2020

I want to group the first, second and tenth column to be one in case its have the same value. how do I do that?

David Winterburn commented on Dec 31, 2020

Use the map process and create new columns according to your requirement.

1 . Sum column:

    ->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row, $metaData, $index, $mapState) {
                $row["sumColumn"] = 0;
                foreach ($dayColumns as $dayColumn) $row["sumColumn"] += 1 * $row[$dayColumn];
                return $row;
            }
    )))    

2 . Group column:

    ->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row, $metaData, $index, $mapState) {
                $row["groupColumn"] = "";
                foreach ($firstSecondTenthColumns as $column) $row["groupColumn"] .= $row[$dayColumn];
                return $row;
            }
    )))    
Ron commented on Dec 31, 2020

Hi David, Thanks, i used the first solution to calculate the Sum in the same MAP process I already have in the code and it works. 1. I did not understand the second solution for the group. what actually I am trying to achieve is the following: I have a row with teacher name for example David Winterburn id number 123456789. this teacher have two rows or maybe three or more depending on the grouped data in the database. I want that the column teacher name and id number will join to be one.
2. columns that have NULL are displayed in the report with a dash. can I remove it. I want it to be empty. I tried to use DropNull() but does not help!

David Winterburn commented on Jan 4

Hi Ron,

We can only group on one column so if you want to group on the 1st, 2nd and 10th column we must concatenate them into one such as the 'groupColumn' then do grouping on that concatenated column. As for the null value of one of the columns, just check its null when concatenating:

    foreach ($firstSecondTenthColumns as $column)
        if (!isset($row[$dayColumn])) $row["groupColumn"] .= "";
        else $row["groupColumn"] .= " - " . $row[$dayColumn];
Ron commented on Jan 4

Lets ask it like this. assuming I want to group a single column, teacher_id what do I need to do?

David Winterburn commented on Jan 5

Search DataTables' 'clientRowGroup' property.

Ron commented on Jan 5

clientRowGroup can not help me. it is a formula column. I have a column that dome of its rows are identical and I just want to rowspan them to be single. do you have a code sample for that?

David Winterburn commented on Jan 7

Ok, then it's something like the "removeDuplicate" from the core Table widget. It's a pity that we haven't implemented that for DataTables yet. We will add this to our road map for the future releases of Datagrid. Thanks!

Ron commented on Jan 11

But "removeDuplicate" is also not something that can help in my case. because in case I have a teacher name David and he has two rows in table. implementing removeDuplicate to the teacher name will work fine because the name is unique but lets assume I want to removeDuplicate on a column which is not unique in the table, for example working_hours. in this case if two different teachers will have the same working_hours it will group them also despite the fact the it is a different teacher name

Sebastian Morales commented on Jan 12

Then you concatenate both teacher_id and working_hours into one column and format its value to hide the teacher_id part using CSS (display: none) if you want. ""removeDuplicate" groups by value, not CSS display so it should be good.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

None