Creating an attendance register report

Creating an attendance register report

GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

I want to create an attendance register report that looks like this:

The information above the dates is the lesson for that day.

The JSON being returned to the jQuery is:

[{"attId":"120","cdId":"MTQ5","attMeeting":"A","attDate":"2019-01-11","cdSurname":"Achilles","cdFirstName":"Tendon","attDescription":"Cycling","attNewChum":"N"},{"cdId":"ODA=","cdSurname":"Alistair","cdFirstName":"Tricia","attDescription":"Running","attNewChum":"N"},{"cdId":"MTQ5","cdSurname":"Achilles","cdFirstName":"Tendon","attDescription":"Cycling","attNewChum":"N"},{"attId":"121","cdId":"ODA=","attMeeting":"P","attDate":"2019-01-12","cdSurname":"Alistair","cdFirstName":"Tricia","attNewChum":"N"},{"attId":"122","cdId":"MTQ5","attMeeting":"P","attDate":"2019-01-13","cdSurname":"Achilles","cdFirstName":"Tendon","attDescription":"Rowing","attNewChum":"N"},{"attId":"123","cdId":"ODA=","attMeeting":"P","attDate":"2019-01-13","cdSurname":"Alistair","cdFirstName":"Tricia","attDescription":"Rowing","attNewChum":"N"}]

The mySQL that extracts the data has a Left Join so all the names are extracted if there is a date (i.e., you can have a name followed by all columns having blanks).

Is it possible to do this?

Kind regards,

Glyn

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    Yes it is certainly possible to display a DataTable like that, but you'd need to transform the JSON into a row based structure. DataTables requires that each item (array or object) in the array of data fed to the table represent a whole row.

    Allan

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Hi Allan,

    I have been able to transform the JSON into a row based structure:

    jsonString: [{"attId":"MTIw","cdId":"MTQ5","cdSurname":"Achilles","cdFirstName":"Tendon","cdArchived":"null","attNewChum":"N","2016-08-24":"null","2016-08-25":"null","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"A","2019-01-11":"null"},{"attId":"NzQ=","cdId":"ODA=","cdSurname":"Alistair","cdFirstName":"Tricia","cdArchived":"null","attNewChum":"N","2016-08-23":"a","2016-08-24":"null","2016-08-25":"null","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-24":"f","2016-08-25":"null","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-25":"a","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-26":"Y","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-27":"a","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-28":"null","2016-09-30":"Y","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2017-03-04":"y","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2017-03-05":"Y","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2017-07-05":"Y","2017-07-12":"null","2019-01-11":"null","2017-07-12":"Y","2019-01-11":"null","2017-07-12":"null","2019-01-11":"null"}]

    I have then tried to use this example:
    https://datatables.net/forums/discussion/comment/117303/#Comment_117303

    However, I get the console error:

    Uncaught SyntaxError: Unexpected token o in JSON at position 1
    at JSON.parse (<anonymous>)
    at Object.<anonymous> (attendanceReport.js:172)
    at u (jquery.min.js:2)
    at Object.fireWith [as resolveWith] (jquery.min.js:2)
    at k (jquery.min.js:2)
    at XMLHttpRequest.<anonymous> (jquery.min.js:2)
    

    My code is:

    HTML:

    <table class="table table-hover table-bordered" id="attendanceDetailsTable">
        <thead>
            <tr>
                <th>Att. ID</th>
                <th>CD ID</th>
                <th>Surname</th>
                <th>First Name</th>
                <th>Archived Date</th>
                <th>New Chum</th>
            </tr>
        </thead>
        <tbody id="attendanceDetailsTablebody">
        </tbody>
    </table>
    

    JSON:

    $(document).ready(function(){
    
        $("#includedContent").load("Menu.html");
    
        $('[data-toggle="tooltip"]').tooltip();
    
        $('#ajaxGetUserServletResponse').text('');
        sessionStorage.setItem('ssArchived', "Hidden");
    
        //Validate Dates
        $("#datepicker1").datepicker({
          showOn: "both",
          todayHighlight: true,
          format: 'dd/mm/yyyy',
        });
    
        $("#datepicker2").datepicker({
          showOn: "both",
          todayHighlight: true,
          format: 'dd/mm/yyyy',
        });
    
        $("#attendanceForm").validate({
            //debug: true,
    
            rules: {
                startDate: {
                    required: true
                },
                endDate: {
                    required: true
                },
            },
            submitHandler: function(attendanceForm) {
    
                showDataTable();
            }
        });
    
    
    }); // end document.ready
    
    function showDataTable() {
        //Show DataTable
        moment.updateLocale(moment.locale(), { invalidDate: "" })
        if ( $.fn.dataTable.isDataTable( '#attendanceDetailsTable' ) ) {
            var attendanceDetailsTable = $('#attendanceDetailsTable').DataTable();
        }
        else {
            var attendanceDetailsTable = $('#attendanceDetailsTable').DataTable( {
                "info":     false,
                "fixedHeader" : true,
                "scrollY":        "500px",
                "scrollX":        true,
                "scrollCollapse": true,
                "paging":         false,
                "pageLength": 30,
                dom: 'Bfrtip',
                buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
                columns: [                  
                      {data: 'attId',
                          visible: false,
                          defaultContent: ""},
                      {data: 'cdId',
                              visible: false,
                          defaultContent: ""},
                      {data: 'cdSurname',
                          defaultContent: ""},
                  {data: 'cdFirstName',
                          defaultContent: ""},
                      {data: 'cdArchived',
                          visible: false,
                          defaultContent: "",
                      },
                      {data: 'attNewChum',
                          defaultContent: ""},
                 ],
                 columnDefs: [
                      {targets: [4], render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )}, 
                ],
            } );
        }
    
        //Toggle the "Archived" column
        $('#archivedHidden').on( 'click', function (e) {
            $('#ajaxGetUserServletResponse').text('');
            $('#ajaxGetUserServletResponse2').text('');
    
            e.preventDefault();
    
            // Get the column API object
            var column = attendanceDetailsTable.column(4);
    
            // Toggle the visibility
            column.visible( ! column.visible() );
    
            if($(this).text().trim() == 'Archived Hidden' ){
                $(this).text('Archived Shown');
                sessionStorage.setItem('ssArchived', "Shown");
    
            }else{
                 $(this).text('Archived Hidden');
                 sessionStorage.setItem('ssArchived', "Hidden");
            }
    
            //Get Attendance Details List
            $.ajax({
                url : 'AttendanceReportView', // Your Servlet mapping or JSP(not suggested)
                data : {
                    ssAccountLevel : sessionStorage.getItem('ssAccountLevel'),
                    ssAccountID : sessionStorage.getItem('ssAccountID'),
                    ssArchived : sessionStorage.getItem('ssArchived'),
                    startDate : $("#startDate").val(),
                    endDate : $("#endDate").val(),
                }, 
                type : 'POST',
            })
            .fail (function(jqXHR, textStatus, errorThrown) {
    //          alert(jqXHR.responseText);
                if(jqXHR.responseText.includes('No members')){
                    $('#ajaxGetUserServletResponse').text('No members.');
                }else{
                    $('#ajaxGetUserServletResponse').text('Error getting report details.');
                }
                attendanceDetailsTable.clear().draw();
            })
            .done(function(responseJson1a){
                // JSON response to populate the activities table
                dataType: "json";
    
                var columns = [];
                responseJson1a = JSON.parse(responseJson1a);
                columnNames = Object.keys(responseJson1a.data[0]);
                for (var i in columnNames) {
                  columns.push({responseJson1a: columnNames[i], title: columnNames[i]});
                }
    
                attendanceDetailsTable.clear();
                attendanceDetailsTable.rows.add(responseJson1a).draw();
            })
        } );
    
        //Get Attendance Details List
        $.ajax({
            url : 'AttendanceReportView', // Your Servlet mapping or JSP(not suggested)
            data : {
                ssAccountLevel : sessionStorage.getItem('ssAccountLevel'),
                ssAccountID : sessionStorage.getItem('ssAccountID'),
                ssArchived : sessionStorage.getItem('ssArchived'),
                startDate : $("#startDate").val(),
                endDate : $("#endDate").val(),
            }, 
            type : 'POST',
        })
        .fail (function(jqXHR, textStatus, errorThrown) {
    //      alert(jqXHR.responseText);
            if(jqXHR.responseText.includes('No members')){
                $('#ajaxGetUserServletResponse').text('No members.');
            }else{
                $('#ajaxGetUserServletResponse').text('Error getting report details.');
            }
            attendanceDetailsTable.clear().draw();
        })
        .done(function(responseJson1a){
            // JSON response to populate the activities table
            dataType: "json";
    
            var columns = [];
            responseJson1a = JSON.parse(responseJson1a);
            columnNames = Object.keys(responseJson1a.data[0]);
            for (var i in columnNames) {
              columns.push({responseJson1a: columnNames[i], title: columnNames[i]});
            }
    
            attendanceDetailsTable.clear();
            attendanceDetailsTable.rows.add(responseJson1a).draw();
        })
    }
    
  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Please note that the code I added from the example is:

    var columns = [];
     responseJson1a = JSON.parse(responseJson1a);
     columnNames = Object.keys(responseJson1a.data[0]);
     for (var i in columnNames) {
     columns.push({responseJson1a: columnNames[i], title: columnNames[i]});
    }
    

    Without this code the columns defined in the HTML are displayed. So all I need to do now is add the dynamic date columns (there can be 0 to n date columns).

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Hi @Glyndwr ,

    That's a lot of coding going on there. 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

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Hi Colin,

    I am not sure how to do this as I am using a json string not html embedded data. Never the less, I have tried to do it here: http://live.datatables.net/fegacevu/1/edit

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    dataType: "json";

    Not sure what your intention is there, but I don't think that's even valid Javascript. Drop those two lines.

    responseJson1a = JSON.parse(responseJson1a);

    If you are getting a JSON parsing error from that line, then responseJson1a is not a valid JSON string. Use JSONLint to validate the string (i.e. add console.log( responseJson1a ); just before that line and then put the string into the linter.

    Allan

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Yes, that works to complete the columns:
    <th>Att. ID</th>
    <th>CD ID</th>
    <th>Surname</th>
    <th>First Name</th>
    <th>Archived Date</th>
    <th>New Chum</th>

    All I need is how to dynamically create the column headers for the date columns:
    "2016-08-24":"null","2016-08-25":"null","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"A","2019-01-11":"null"},

    I tried:

    var columns = [];
     responseJson1a = JSON.parse(responseJson1a);
     columnNames = Object.keys(responseJson1a.data[0]);
     for (var i in columnNames) {
     columns.push({responseJson1a: columnNames[i], title: columnNames[i]});
    }
    

    from the example: https://datatables.net/forums/discussion/comment/117303/#Comment_117303

    However, that gave me the console error:

    Uncaught SyntaxError: Unexpected token o in JSON at position 1
    at JSON.parse (<anonymous>)
    at Object.<anonymous> (attendanceReport.js:172)
    at u (jquery.min.js:2)
    at Object.fireWith [as resolveWith] (jquery.min.js:2)
    at k (jquery.min.js:2)
    at XMLHttpRequest.<anonymous> (jquery.min.js:2)
    

    Without that code the first six columns are correctly populated. So I need the correct code to dynamically create the column headers for the date columns.

    Kind regards,

    Glyn

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    Did you use JSONLint as Allan recommended?
    Have you updated your example?

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    We'd really need a test case showing the issue in this case. I'm afraid we can't really help without one.

    Allan

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Hi,

    I very much appreciate your help. I have created another link http://live.datatables.net/kuhobodu/1/edit

    I hope this is acceptable. The trouble is I do not know how to provide an input file in place of the HTML in order to better demonstrate the problem.

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    Your example Javascript is showing errors. They include "dataType: "json"; which Allan already suggested should be removed..

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Hi Tangerine,

    Thank you for your help. I have been working on this in my spare time and found a few things. I have removed the "dataType: "json"; and this makes no difference. I have also found that:

    var varColumns = [];
        $.each(responseJson1a, function(key1, value){
            $.each(value, function(key, value){
                alert(key);
                alert(value);
                varColumns.push({data: key, title: key});
            });
     });
    

    placed in the ".done" function displays the key and value and so replaces:

    var columns = [];
         responseJson1a = JSON.parse(responseJson1a);
         columnNames = Object.keys(responseJson1a.data[0]);
         for (var i in columnNames) {
            columns.push({responseJson1a: columnNames[i], title: columnNames[i]});
        }
    

    So now I need to find out how to use this code to generate the dynamic headings. I will continue to plug away with trial and error.

    Kind regards,

    Glyn

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin
    Answer ✓

    That looks like all you should need for dynamic headings. Just pass your columns array into the columns option.

    Allan

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Thanks for everyone's help. I have found a solution so I thought I would post it here in the hope that it helps others.

    This code creates the header row:

               var varColumns = [];
            $.each(responseJson1a[0], function(key, value){
                //console.log(key);
                //console.log(value);
                if (key === "Archived"){
                    varColumns.push({data: key, title: key, defaultContent: "", visible: false,render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )});
                }else{
                    varColumns.push({data: key, title: key, defaultContent: ""});
                }
    
            });
    

    Then the following:

    var attendanceDetailsTable = $('#attendanceDetailsTable').DataTable( {
                info:           false,
                fixedHeader:    true,
                scrollY:        "500px",
                scrollX:        "100%",
                scrollCollapse: true,
                paging:         false,
                fixedColumns:   {leftColumns: 2},
                pageLength:     30,
                dom:            'Bfrtip',
                buttons:        ['copy', 'csv', 'excel', 'pdf', 'print'],
                data:           responseJson1a,
                columns:        varColumns,
            } );
    

    Note:
    data: will load the table data from your input independent of your column headings (I did not understand this, some one may be able to explain this more eloquently)
    columns: loads the headings you have created above. VERY IMPORTANT do not use [varColumns] it is without the []. The joy I had when I removed the [] and it suddenly worked!!

This discussion has been closed.