Row Grouping assistance

bbrindza

Hello Gurus,

I am looking for a little help and another set of eyes.

My row grouping was fine in 2020 but when we went to 2021, adding a new value for group level 0, my group level 0 and level 1 is not grouping correctly.

Looking at my code I noticed that my order clause was missing the year column in the sort.

order: [[0, 'desc'],[1, 'asc'],[2, 'asc'],[4, 'asc']],

I added the year column it and it caused my 2nd level grouping not to group properly.

What am I not seeing here?

Full Script

 var table = $('#timeLogTable_Vacation').DataTable( {

  rowCallback: function(row, data, index){  
            if(data['time_log_date_yyyymmdd'] <= date){
                $(row).find('td:eq(0)').css('color', 'red');
                $(row).find('td:eq(1)').css('color', 'red');
                $(row).find('td:eq(2)').css('color', 'red');
            }
    },
    displayLength:100,
    scrollY: '495px',
    paging: false,
    dom: 'Bfrtip',
    language: {  sSearch: 'Table Search: '},
    buttons: {
        buttons: [ 
               { text: 'Create Vacation Day',
                 attr: { id: 'displayVacationDayModalButton'},
                 action: function ( e, dt, node, config ) {displayVacationDayModal(); }
              },
              { text: 'Remove Vacation Entry',
                className: 'btn-danger',
                attr: { id: 'removeVacationDayModalButton'},
                action: function ( e, dt, node, config ) {removeVacationDayModal(); },
                enabled: false
               }
            ],
            dom: {
                  button: { className: 'btn btn-primary'},
                  buttonLiner: { tag: null }
                 }
    },
    processing: true,
    serverSide: true,
    ajax: {
            url: "ssp_TimeLogTable_Vacation.php",
            dataType: 'json',
            data: {employeeNumber: employeeNumber,
            employeeDepartmentCode: employeeDepartmentCode },
    },
       columns: [
                 { data: 'time_log_year', visible: false},
                 { data: 'employee_last_name', visible: false},
                 { data: 'employee_full_name', visible: false},
                 { data: 'employee_manager_number', visible: false},
                 { data: 'sort_date', visible: false},
                 { data: 'created_by', visible: false},
                 { data: 'created_on', visible: false},
                 { data: 'time_log_date_yyyymmdd', visible: false},
                 {
                     sortable: false,
                       "render": function ( data, type, full, meta ) {
                        if(full.comments == null)   {
                            return '';
                        }else{
                         return '<img class="ui-corner-all" src="../../../images/document-16.ico" alt="'+full.comments+'">';
                        }   
                     }
                   },
                 { data: 'out_of_office', orderable: false},
                 { data: 'time_log_date', orderable: false },
                 { data: 'total_hours', orderable: false}
         ],
    select: {style: 'single'},  

    order: [[0, 'desc'],[1, 'asc'],[2, 'asc'],[4, 'asc']],

    rowGroup: {
           dataSrc: [ 'time_log_year', 'employee_full_name'],
           startRender: function (rows, group, level) {

                var totalVacationDays = rows
                .data()
                .pluck('total_hours')
                .reduce( function (a, b) {
                return a + b ;
           });

            totalVacationDays = totalVacationDays / 8;
            totalVacationDays = Math.round(totalVacationDays);  
            
            var all;

                if (level === 0) {
                    level_1 = group;
                    all = group;
                    level_2 ='';
                } else {
                    // if parent collapsed, nothing to do
                    if (!!collapsedGroups[level-1]) {
                        return;
                    }
    
                 if (level === 1) {
                       level_2 = group
                      }
                    
                 all = level_1 + level_2 + group;
                }
                
               var collapsed = !!collapsedGroups[all];

               rows.nodes().each(function (r) {
                   r.style.display = 'none';
                   if (collapsed) {
                     r.style.display = '';
                   }});

              //fontawsome + and -
              var toggleClass = collapsed ? 'fa fa-minus-circle' : 'fa fa-plus-circle';
              var groupTD = '';

              if(level == 0){
                    groupTD = '<td colspan="' + rows.columns()[0].length +'">' + group + '</td>'
              }else{
                    groupTD = '<td colspan="' + rows.columns()[0].length +'"><span style="color:#007bff"  class="fa fa-fw' + toggleClass + ' toggler"></span>  ' + group + ' (' + totalVacationDays + ')</td>'
             }

            // Add category name to the <tr>
            return $('<tr/>')
             
              .append(groupTD)
              .attr('data-name', all)
              .toggleClass('collapsed', collapsed);

           }
   }//End rowGroup
    
});//END .dataTable

kthorngren

Your rowGrpup.dataSrc option is this:

dataSrc: [ 'time_log_year', 'employee_full_name'],

Those are the columns to sort by. Looks like you should he this fo rsorting:

order: [[0, 'desc'],[2, 'asc'],[1, 'asc'],[4, 'asc']],

Kevin

colin

At a glance it all looks OK. We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

bbrindza

Kevin,
I gave that a try and still not change to the row grouping.

Colin,
I would be more then happy to set up a test case, however, my back-end environment is using DB2 with a DataTable ssp script on a IBM Power System behind a corporate firewall. It would take quite sometime for my to put something like this together

In the past I worked with Allan Jardine in allowing him to test the DB2 ssp scripts in our environment back in the day. We did this via WebEx so he could work with me on developing the DB2 functionality for DataTables. Because he is a principle at DataTables we had no problem doing this.

I am open to any assistance you my suggest.

kthorngren

Can you take a sample of your data, collected from the browser's network inspector, and use is as Javascript data like this example. Just use data to add the data instead of using the ajax request. All we need is a sample of the data, the data source doesn't matter.

Kevin

bbrindza

Kevin,
Will do... give me a day to put that together.

bbrindza

Gentlemen,

I created a DataTable -JS Bin using my script code and data collected from the browser's network inspector, as Kevin suggestion.

http://live.datatables.net/naduhota/1/edit

The odd thing is that it work fine here.

The only wildcard is the ssp script that I am using to gather the data from my backend.

processing: true,
serverSide: true,
ajax: {
          url: "ssp_TimeLogTable_Vacation.php",
          dataType: 'json',
          data: {employeeNumber: employeeNumber,
      employeeDepartmentCode: employeeDepartmentCode },
    },

Thoughts?

kthorngren

I wasn't expecting to see 422 rows fo data You are using server side processing but I just realized you have paging disabled. This negates the purpose of server side processing. If you don't need server side processing I would suggest disabling it so all your table processes, searching, sorting, paging (even though its disabled) is performed in the client.

I would guess the SSP script is not sorting the data properly. Are you using a Datatables provided server script or your own?

You can look at the order of the data returned to see if its correct. One way is to remove the rowgroup config and make the columns you are ordering visible. Or you can just look at the JSON response. The order in the JSON response is the order Datatables will display the data with SSP enabled.

EDIT: Another option is to simply turn off server side processing and let the client sort the data. If it works then there is something with the sorting in your SSP script.

Kevin

bbrindza

Kevin,

I disabled processing: true and serverSide: true and it works.

As far as the paging: false, I want to load all data for a department so the user just scrolls down the front-end table.

And yes, I am using the Datatables provided server script. By disabling processing: true, and serverSide: true, what impact does this have on my DataTable functionality?

<?php
/*
 * DataTables server-side processing custom script 
 *
 * Used by timeLogTable_Vacation.php
 */

$today = date('Y-m-d'); //15.04.2013
$yesterday= date('Y-m-d',strtotime("-1 days"));

include('i5db2connect.php');

// DB table to use
$table = 'LIBNWF.TIME_LOG';

$extraWhere = "OUT_OF_OFFICE = 'Vacation Day' and EMPLOYEE_DEPARTMENT_CODE = '" . $_GET['employeeDepartmentCode']. "'";

// Table's primary key
$primaryKey = 'EMPLOYEE_NUMBER';

$columns = array(
             array( 'db' => 'TIME_LOG_DATE', 'dt' => 'time_log_year',
                    'formatter' => function( $d, $row ) {
                    $date = new DateTime($row[TIME_LOG_DATE]);
                    return trim($date->format('Y'));
                }
        ),

        array( 'db' => 'EMPLOYEE_FIRST_NAME', 'dt' => 'employee_first_name',
              'formatter' => function($d, $row){
               return trim($row[EMPLOYEE_FIRST_NAME]);
               }
        ),
        array( 'db' => 'EMPLOYEE_LAST_NAME', 'dt' => 'employee_last_name',
                'formatter' => function($d, $row){
                return trim($row[EMPLOYEE_LAST_NAME]);
                }
        ),
        array( 'db' => 'EMPLOYEE_FULL_NAME','dt' => 'employee_full_name',
               'formatter' => function($d, $row){
                 $employeeNameLastFirst = trim($row[EMPLOYEE_LAST_NAME]) . ', ' . trim($row[EMPLOYEE_FIRST_NAME]);
                 return trim($employeeNameLastFirst);
              }
            ),
        
        array( 'db' => 'EMPLOYEE_MANAGER_NUMBER', 'dt' => 'employee_manager_number'),
        
        array( 'db' => 'TIME_LOG_DATE', 'dt' => 'sort_date'),
      
        array( 'db' => 'CREATED_BY','dt' => 'created_by',
                'formatter' => function($d, $row){
                return trim($row[CREATED_BY]);
         }),
            
        array( 'db' => 'CREATED_ON','dt' => 'created_on' ),
        array( 'db' => 'TIME_LOG_DATE', 'dt' => 'time_log_date_yyyymmdd',
            'formatter' => function( $d, $row ) {
            $date = new DateTime($row[TIME_LOG_DATE]);
            return $date->format('Ymd');
            }
        ),
        array( 'db' => 'COMMENTS','dt' => 'comments' ),
        
        array( 'db' => 'OUT_OF_OFFICE', 'dt' => 'out_of_office',
              'formatter' => function($d, $row){
              return trim($row[OUT_OF_OFFICE]);
            }
        ),
        array( 'db' => 'TIME_LOG_DATE', 'dt' => 'time_log_date',
                        'formatter' => function( $d, $row ) {
                                          $date = new DateTime($row[TIME_LOG_DATE]);
                                          return $date->format('m/d/Y');
                         }
        ),
        array( 'db' => 'TOTAL_HOURS_SECONDS', 'dt' => 'total_hours_formatted',
                                   'formatter' => function($d, $row){
                                         $hours   = floor($row[TOTAL_HOURS_SECONDS] / 3600);
                                         return   $hours . ' h ' . '0 m';
                              }
          ),
        array( 'db' => 'TOTAL_HOURS_SECONDS', 'dt' => 'total_hours',
                            'formatter' => function($d, $row){
                                  $hours   = floor($row[TOTAL_HOURS_SECONDS] / 3600);
                                  return   $hours;
                             }
         )
       );

require( 'ssp.class.php' );

echo json_encode(
    SSP::simple( $_GET, $connection, $select, $table, $primaryKey, $columns, $extraWhere)
    );

kthorngren

By disabling processing: true, and serverSide: true, what impact does this have on my DataTable functionality?

All of the sorting, searching and paging functions will happen at the client. The paging affect of server side processing won't be used so your server script will need to return all the data for the table - which its doing now with paging false.

Other than that if all your Datatable functionality works then there is no impact

Kevin

bbrindza

Thank you Kevin and Colin for all your help.