DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Because each row of my table input has sub items, similar to the table below, is there any example？

Because each row of my table input has sub items, similar to the table below, is there any example？

lancwplancwp Posts: 90Questions: 20Answers: 1
in Editor

Because each row of my table input has sub items, similar to the table below, is there any example related to this that I can refer to?

Link to test case:

Debugger code (debug.datatables.net):

Error messages shown:
Description of problem:

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 21,374Questions: 26Answers: 4,957

    Datatables doesn't support colspan or rowspan in the tbody. See the HTML requirements for details.

    See if the RowGroup extension will work for you. See this example.

    Kevin

  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421
    edited June 27

    You might also want to consider child rows:
    https://datatables.net/examples/api/row_details.html
    Or this blog for a "nicer" version:
    https://datatables.net/blog/2019/parent-child-editing-in-child-rows

    Based on the blog I built this for example with Excel exportable child tables for each parent row.

  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421
    Answer ✓

    This comes pretty close to what you've got.

    I have "order data" (Auftragsdaten) and "payments" (Zahlungsdaten).

    One order can have 0 - N payments. This is what it looks like in the data table. 2 orders, the first with 3 payments, the second with 0 payments.

    Order data and the corresponding payments can be edited, too:

    If you are interested in (parts of) this solution just let me know.

  • lancwplancwp Posts: 90Questions: 20Answers: 1

    Thank you everyone

  • lancwplancwp Posts: 90Questions: 20Answers: 1

    @rf1234 Can you share your code?

  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421

    Yes, I can share, but it's a lot of code ...

    I have a button in the parent table to open a modal with the order data table shown above.

    This is the button I use to open the bootstrap 3 modal and initialize the data table:

    //custom button to enter order data for subsidies
$.fn.dataTable.ext.buttons.subOrder = {
    //only enabled when one row is selected (like edit / delete)
    extend: 'selectedSingle', //alternative would be 'selected' (multiple rows)
    text: subOrderLabel,
    className: "subOrderButton",
    action: function ( e, dt, button, config ) {
        var selected = dt.row({ selected: true });
        if (selected.any()) {
            $('.subOrderButton').attr(
                {
                    "data-toggle": "modal",
                    "data-target": "#subOrderModal"
                }
            );
            showSubOrderTable();
        }
    }
};

    This is the HTML of the data table. I know it looks a bit overloaded because I support two languages using embedded PHP.

    <!--tblSubOrder: orders to implement the respective subsidy-->

<div class="container">
    <div class="modal fade" id="subOrderModal" tabindex="-1" role="dialog" aria-labelledby="myModalCategory">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                    <h4 class="modal-title" style="text-align:justify"><span id="subOrderHeader"><?php echo $en?("Orders"):('Aufträge');?></span></h4>
                </div>
                <div class="modal-body">    
                    <div class="row">  
                        <div class="col-lg-12" id="subTotalCosts"></div>
                    </div>
                    <br>
                    <table id="tblSubOrder" class="table table-striped table-bordered"
                           cellspacing="0" width="100%">
                        <thead>
                            <tr>
                                <th colspan="5" class="bg-primary">Auftragsdaten</th>
                                <th colspan="6" class="bg-danger">Zahlungsdaten</th>
                            </tr>
                            <tr>
                                <th><?php echo $en?('Order Name'):('Auftragsbezeichnung');?></th>
                                <th><?php echo $en?('Partner'):('Auftragnehmer');?></th>
                                <th><?php echo $en?('Order Date'):('Auftragsdatum');?></th>
                                <th class="dt-right"><?php echo $en?('Order Costs'):('Auftragskosten');?></th>
                                <th><?php echo $en?('Additional Information'):('Zusatzinformation');?></th>
                                <th class="orderPayments"><?php echo $en?('Billing Date'):('Rechnungsdatum');?></th>
                                <th class="orderPayments"><?php echo $en?('Billing Voucher Number'):('Rechnungs-Nr.');?></th>
                                <th class="orderPayments dt-right"><?php echo $en?('Amount'):('Betrag');?></th>
                                <th class="orderPayments"><?php echo $en?('Payment Date'):('Zahlungsdatum');?></th>
                                <th class="orderPayments"><?php echo $en?('Payment Voucher Number'):('Beleg-Nr. Zahlung');?></th>
                                <th class="orderPayments"><?php echo $en?('Additional Info'):('Zusatzinfo');?></th>
                            </tr>
                        </thead>      
                        <tfoot>
                            <tr class="bg-success subOrderFooter">
                                <th colspan="3"></th>
                                <th></th>
                                <th colspan="3"></th>
                                <th></th>
                                <th colspan="3"></th>
                            </tr>
                        </tfoot>
                    </table>     
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-primary" data-dismiss="modal"><?php echo $en?('Close'):('Schließen');?></button>
                </div>  
            </div>
        </div>
    </div>
</div>
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421

    The entire JS of the order data table inside the bootstrap 3 modal. The top 3 variables are global variables. The "parentTable" mentioned in the code is the table that has the button to open the Bootstrap 3 modal. It is also a global variable.
    Three things are important:
    - subOrderTable: The data table inside the modal
    - subOrderEditor: The editor to edit order data
    - subOrderScheduleEditor: The editor inside subOrderEditor (fieldtype: Datatable) to edit the billing and the payments for each order.

    You will also find a fairly complex Excel export button definition. I tried to make the Excel export as colorful as the data table. Almost got there. My users find it's good enough :smile:

    var subOrderScheduleEditor = {};
var subOrderEditor = {};
var subOrderTable = {};

var showSubOrderTable = function() {  
    
    $('#tblSubOrder').removeClass("table-striped");
    var selected = parentTable.row({selected: true});
    var subOrderId = 0;
    
    subOrderScheduleEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: 'actions.php?action=tblSubOrderSchedule',
            data: function ( d ) {
                d.sub_order_id = subOrderId;
                d.is_sub_provider = parentIsSubProvider;
            }
        },
        formOptions: { main: { focus: null } },
        fields: [
               {    
                label: lang === 'de' ? 'Rechnungsdatum:' : 'Billing Date:',
                name: "sub_order_schedule.billing_date",
                attr: {
                    class: dateMask
                },
                type: "datetime",
                format: 'L',
                opts: {
                    showWeekNumber: true,
                    momentLocale: momentLocale
                }
            }, {    
                label: lang === 'de' ? 'Rechnungs-Nr.:' : 'Billing Number:',
                name:  "sub_order_schedule.billing_number"
            }, {
                label: lang === 'de' ? 'Betrag:' : 'Amount:',
                name:  "sub_order_schedule.payment_amount",
                attr: {
                    class: amountMask,
                }
            }, {    
                label: lang === 'de' ? 'Zahlungsdatum:' : 'Payment Date:',
                name: "sub_order_schedule.payment_date",
                attr: {
                    class: dateMask
                },
                type: "datetime",
                format: 'L',
                opts: {
                    showWeekNumber: true,
                    momentLocale: momentLocale
                }
            }, {    
                label: lang === 'de' ? 'Beleg-Nr. Zahlung:' : 'Payment Voucher Number:',
                name:  "sub_order_schedule.voucher_number"
            }, {    
                label: lang === 'de' ? 'Zusatzinfo:' : 'Additional Info:',
                name:  "sub_order_schedule.add_info"
            }
        ]
    });

    subOrderScheduleEditor
        .on('open', function (e, mode, action) {
            maskAmount();
            maskDateTime();
        })
        .on( 'submitSuccess', function( e, json, data, action ) { 
            getOrderSummaryMessage(subOrderEditor);
            ajaxReloadTbls([subOrderTable]);
            generateSubSummaryPdf( 1 );
        });

    subOrderEditor = new $.fn.dataTable.Editor({
        ajax: {
                url: 'actions.php?action=tblSubOrder',
                data: function ( d ) {
                    d.ctr_id = parentId;
                    d.is_sub_provider = parentIsSubProvider;
                }
            },
        table: "#tblSubOrder",
        i18n: { edit: { title: lang === 'de' ? 'Auftragsdaten bearbeiten' : 'Edit Order Data' } },
        formOptions: {
            main: {
                focus: 1
            }
        },
        fields: [
            {
                label: lang === 'de' ? 'Auftragsbezeichnung:' : 'Order Name:',
                name:  "sub_order.order_name",
                attr: {
                    class: 'typeAheadOrderName',
                    autocomplete: 'off',
                    placeholder: lang === 'de' ? 'Auftragsbezeichnung eingeben und ggfs. einen Vorschlag auswählen' 
                                               : 'Enter order name and possibly select a suggestion',
                }
            }, {
                label: lang === 'de' ? 'Auftragnehmer:' : 'Partner:',
                name:  "sub_order.order_partner",
                attr: {
                    class: 'typeAheadOrderPartner',
                    autocomplete: 'off',
                    placeholder: lang === 'de' ? 'Auftragnehmer eingeben und ggfs. einen Vorschlag auswählen' 
                                               : 'Enter partner name and possibly select a suggestion',
                }
            }, {
                label: lang === 'de' ? 'Auftragsdatum:' : 'Order Date:',
                name: "sub_order.order_date",
                attr: {
                    class: dateMask
                },
                type: "datetime",
                format: 'L',
                opts: {
                    showWeekNumber: true,
                    momentLocale: momentLocale
                }
            }, {
                label: lang === 'de' ? 'Auftragskosten:' : 'Order Costs:',
                name:  "sub_order.order_total_costs",
                attr: {
                    class: amountMask,
                }
            }, {
                label: lang === 'de' ? 'Zusatzinformation:' : 'Additional Information:',
                name: "sub_order.order_short_desc",
                type: "textarea"
            },  {
                label: lang === 'de' ? 'Auftragszahlungen:' : 'Order Payments:',
                name: 'sub_order_schedule[].id',
                type: 'datatable',
                editor: subOrderScheduleEditor,
                submit: false,
                config: {
                    searching: false,
                    fixedHeader: false,
                    paging: false,
    //                scrollY: "150px",
    //                scrollCollapse: true,
                    ajax: {
                        url: 'actions.php?action=tblSubOrderSchedule',
                        type: 'POST',
                        data: function ( d ) {
                            d.sub_order_id = subOrderId;
                            d.is_sub_provider = parentIsSubProvider;
                        }
                    },
                    language: languageEditorDts,
                    buttons: [
                        { extend: 'create', editor: subOrderScheduleEditor },
                        { extend: 'edit',   editor: subOrderScheduleEditor },
                        { extend: 'remove', editor: subOrderScheduleEditor }
                    ],
                    order: [[0, 'asc']],
                    columns: [
                        {   title: lang === 'de' ? 'Rechnungsdatum' : 'Billing Date',
                            data: 'sub_order_schedule.billing_date'       },
                        {   title: lang === 'de' ? 'Rechnungs-Nr.' : 'Billing Number',
                            data: 'sub_order_schedule.billing_number'    },
                        {   title: lang === 'de' ? 'Betrag' : 'Amount',
                            data: 'sub_order_schedule.payment_amount'           },
                        {   title: lang === 'de' ? 'Zahlungsdatum' : 'Payment Date',
                            data: 'sub_order_schedule.payment_date'         },
                        {   title: lang === 'de' ? 'Beleg-Nr. Zahlung' : 'Payment Voucher Number',
                            data: 'sub_order_schedule.voucher_number'    },
                        {   title: lang === 'de' ? 'Zusatzinfo' : 'Additional Info',
                            data: 'sub_order_schedule.add_info'   }
                    ]
                }
            }, {
                type: "hidden",
                name: "sub_order.id"
            }
        ]
    });
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421 
    subOrderEditor
        .on('open', function (e, mode, action) {
            $('.DTED .modal-dialog').addClass("modal-xl-static");
            $('.DTED .modal-dialog').draggable();
            tooltipsPopups(this);
            maskAmount();
            maskDateTime();
            $('.col-lg-4').addClass('col-lg-2')
                          .removeClass('col-lg-4');
            $('.col-lg-8').addClass('col-lg-10')
                          .removeClass('col-lg-8');
            if ( action === "edit" ) {
                if ( this.val("sub_order.order_name") === "<Bitte ändern>" ||
                     this.val("sub_order.order_name") === "<Please edit>"     ) {
                    this.set({ "sub_order.order_name": "",
                               "sub_order.order_partner": "",
                               "sub_order.order_date": "" });
                }
            }       
            $('.typeAheadOrderPartner').typeahead( {
                source: function (query, process) {
                    $.ajax({
                        type: 'POST',
                        url: 'actions.php?action=typeAheadOrderPartner',
                        //only one parameter to post is allowed by typeahead!!
                        data: { query: $('.typeAheadOrderPartner').val() },
                        dataType: 'JSON',             
                        success:    function(data) {                      
                                    process(data);
                        }
                    });
                },
                items: 10,     //you would only retrieve up to 10 items
                minLength: 3   //you would only start querying after typing at least 3 letters
            });
            $('.typeAheadOrderName').typeahead( {
                source: function (query, process) {
                    $.ajax({
                        type: 'POST',
                        url: 'actions.php?action=typeAheadOrderName',
                        //only one parameter to post is allowed by typeahead!!
                        data: { query: $('.typeAheadOrderName').val() },
                        dataType: 'JSON',             
                        success:    function(data) {                      
                                    process(data);
                        }
                    });
                },
                items: 10,     //you would only retrieve up to 10 items
                minLength: 3   //you would only start querying after typing at least 3 letters
            })
        })
        .dependent('sub_order.order_total_costs', function ( val, data, callback ) {    
            //only negative numbers because these are costs!!
            if ( toFloat(val) != 0 && val.substr(0,1) != '-' ) {
                this.set({'sub_order.order_total_costs': '-' + val});
            }
            getOrderSummaryMessage(this);
            callback({});
        })
        .on('close', function() {
            $('.DTED .modal-dialog').removeClass("modal-xl-static");
            if ( $('.DTED .modal-dialog').hasClass('ui-draggable') ) {
                $('.DTED .modal-dialog').draggable("destroy");
            }
        })
        .on( 'submitSuccess', function( e, json, data, action ) { 
            subOrderTable.ajax.reload( function(){
                $.ajax({
                type: "POST",
                url: 'actions.php?action=getOrderSummationValues',
                data: { ctr_id: parentId },
                dataType: "json",
                success: function (data) {
                    $('#subTotalCosts').html( data.message );
                    setTimeout(function() {
                        subOrderTable.draw();
                    }, 200);
                    //avoid complete ajax reload of the parent table!!
                    //we want the data table view of the field, not the bold print like in the order Editor
                    var cell = data.message.replace('&emsp;&emsp;&emsp;', '; <br>')
                                           .replaceAll(' class="fontThick"', '')
                                           .replaceAll('fontThick ', '');
                    selected.data().sub_order.order_summary = cell;
                    parentTable.cell(selected, ".ordCol").invalidate().draw(false);
                    generateSubSummaryPdf( 1 );
                }
            });
            }, false)
            .columns.adjust()
            .responsive.recalc();
        });
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421 
    subOrderTable = $('#tblSubOrder').DataTable( {
        dom: "Bti",
        fixedHeader: false,
        paging: false,
        destroy: true,
        scrollY: "500px",
        scrollCollapse: true,
        scrollX: true,
        responsive: false,
        ajax: {
            url: 'actions.php?action=tblSubOrder',
            type: 'POST',
            data: function ( d ) {
                d.ctr_id = parentId;
                d.is_sub_provider = parentIsSubProvider;
            }
        },
        columns: [
            {   data: "sub_order.order_name", orderable: false },
            {   data: "sub_order.order_partner", orderable: false },
            {   data: "sub_order.order_date", orderable: false },
            {   data: "sub_order.order_total_costs", orderable: false,
                render: function (data, type, row) {
                    return renderAmountCurrency(data, row.ctr.currency);
                }
            },
            {   data: "sub_order.order_short_desc", orderable: false },
            {   data: 'sub_order_schedule.billing_date', orderable: false },
            {   data: 'sub_order_schedule.billing_number', orderable: false },
            {   data: 'sub_order_schedule.payment_amount', orderable: false,
                render: function (data, type, row) {
                    return renderAmountCurrency(data, row.ctr.currency);
                }
            },
            {   data: 'sub_order_schedule.payment_date', orderable: false },
            {   data: 'sub_order_schedule.voucher_number', orderable: false },
            {   data: 'sub_order_schedule.add_info', orderable: false }
        ],
        order: [], //no ordering by Data Tables
        select: {
            style: 'single',
            selector: 'tr.selectRow td:not(:first-child)'
        },    
        columnDefs: [
            {
                targets: 'dt-right', className: 'dt-right'
            }
        ],
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421 
            buttons: [
            {   extend: "create", editor: subOrderEditor,                  
                    action: function ( e, dt, node, config ) {
                        var self = subOrderEditor;
                        self.create( false )
                            .set( { 'sub_order.order_name': 
                                        lang === 'de' ? '<Bitte ändern>' : '<Please edit>',
                                    'sub_order.order_partner': 
                                        lang === 'de' ? '<Bitte ändern>' : '<Please edit>',
                                    'sub_order.order_date': inFiftyDays} )
                            .submit();       
                    }
            },
            {   extend: "edit", editor: subOrderEditor, className: "editRemove" },
            {   extend: "remove", editor: subOrderEditor, className: "editRemove" },
            {   extend: "excel",
                title:    function () { return lang === 'de' ? 'Aufträge und Auftragszahlungen' : 'Orders and Order Payments' },
                messageTop: function () { 
                    //replace multiple spaces and tabs etc with ";" plus two spaces
                    return $('#subTotalCosts').text().replace(/\s\s+/g, ';  ');  
                },                              
                filename: function () { return lang === 'de' ? 'Auftraege' : 'Orders' },
                footer: true,
                messageBottom: function() {
                    return tableFooter + subOrderTable.rows( {search: "applied"} ).count();
                },
                customize: function( xlsx ) {
                    var sSh = xlsx.xl['styles.xml'];
                    var lastXfIndex = $('cellXfs xf', sSh).length - 1;            
                    var lastFontIndex = $('fonts font', sSh).length - 1; 
                    var f1 = //bold and underlined font
                    '<font>'+
                            '<sz val="11" />'+
                            '<name val="Calibri" />'+
                            '<b />'+'<u />'+
                    '</font>'

                    var i; var y;
            //n1, n2 ... are number formats; s1, s2, ... are styles
                    var s1 = '<xf numFmtId="0" fontId="'+(lastFontIndex+1)+'" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                                '<alignment horizontal="center"/></xf>';         
            //s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
            //just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
                    var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                                '<alignment horizontal="center" wrapText="1"/></xf>';
                    var s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
                    var s4 = '<xf numFmtId="0" fontId="2" fillId="5" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                                '<alignment horizontal="center" wrapText="1"/></xf>';
                    var s5 = '<xf numFmtId="0" fontId="2" fillId="4" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                                '<alignment horizontal="center" wrapText="1"/></xf>';
                    var s6 = '<xf numFmtId="0" fontId="2" fillId="3" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                                '<alignment horizontal="center" wrapText="1"/></xf>';
                    var s7 = '<xf numFmtId="4" fontId="2" fillId="4" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
                    var s8 = '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="0" applyBorder="1" xfId="0" applyAlignment="1">'+
                                '<alignment vertical="top" wrapText="1"/></xf>';
                    var s9 = '<xf numFmtId="4" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1">'+
                                '<alignment vertical="top" wrapText="0"/></xf>';
                    sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
                    sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9; //new styles

                    var greyBoldCentered = lastXfIndex + 1;
                    var greyBoldWrapText = lastXfIndex + 2;
                    var twoDecPlacesBold = lastXfIndex + 3;
                    var blueBoldWrapText = lastXfIndex + 4;
                    var greenBoldWrapText = lastXfIndex + 5;
                    var redBoldWrapText = lastXfIndex + 6;
                    var greenTwoDecPlacesBold = lastXfIndex + 7;
                    var topAlignWrapText = lastXfIndex + 8;
                    var topAlignTwoDecPlaces = lastXfIndex + 9;

                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                //create array of all columns (0 - N)
                    var cols = $('col', sheet);
                //top align all columns and wrapt the text!! (can be overwritten later)
                    $('row c', sheet).attr( 's', topAlignWrapText );
        //two decimal places columns          
                    var twoDecPlacesCols = ['D', 'H'];       
                    for ( i=0; i < twoDecPlacesCols.length; i++ ) {
                        $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', topAlignTwoDecPlaces );
                    }
                    var rows = $('row', sheet).length;
                    $('row:eq(0) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold and centered, as added above
                    $('row:eq(1) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
                    $('row:eq(2) c', sheet).attr( 's', blueBoldWrapText );  //blue background bold, text wrapped
                    $('row:eq(-2) c', sheet).attr( 's', greenBoldWrapText );  //green background bold, text wrapped
                    var redHeaderCols = ['F', 'G', 'H', 'I', 'J', 'K'];            
                    for ( i=0; i < redHeaderCols.length; i++ ) {
                        $('row:eq(2) c[r^='+redHeaderCols[i]+']', sheet).attr( 's', redBoldWrapText );  //red background bold, text wrapped
                    }
                    for ( i=0; i < twoDecPlacesCols.length; i++ ) {
                        $('row:eq(-2) c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', greenTwoDecPlacesBold );
                    }
                    $('row:last c', sheet).attr( 's', '2' );  //bold
                },
                exportOptions: {
                    columns: function(column, data, node) {
//                            if ($.inArray(column, [16, 17, 18, 20]) >= 0) {
//                                return false;ords show up
                    format: {
                        body: function ( data, row, column, node ) {
                            return formatXLSBodyFoooter(data, column, [3, 7]); //'D', 'H'
                        },
                        header: function ( data, column, node ) {
                            //replace html tags with one space
                            return data.replace(/<[^>]*>/g, ' ');
                        },
                        footer: function ( data, column, node ) {
                            if ( subOrderTable.rows( {search: "applied"} ).count() > 0 ) {
                                //make sure empty footer cells are colored green, too!
                                //for that purpose they must not be empty!
                                if ( data <= "" ) {
                                    data = " ";
                                }
                                return formatXLSBodyFoooter(data, column, [3, 7]); //'D', 'H'
                            }
                            return "";
//                            }
                        return true;
                    },
                    modifier: { selected: null }, //make sure all rec
                        }
                    }
                }
            }
        ],
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421 
    rowCallback: function (row, data) {
            //if it is a heading row the row should be selectable
            if ( data.sub_order.order_name > "") {
                $(row).addClass('bg-info selectRow');
            } 
        },
        footerCallback: function (row, data, start, end, display) {                
            var api = this.api(); 
            if ( api.rows( {search: "applied"} ).count() < 1 ) {
                $(".subOrderFooter").addClass("hidden");
                return;
            } else {
                $(".subOrderFooter").removeClass("hidden");
            }
            if (lang == 'de') {
                var numberRenderer = $.fn.dataTable.render.number( '.', ',', 2 ).display;
            } else {
                var numberRenderer = $.fn.dataTable.render.number( ',', '.', 2 ).display;
            }
            
            // Remove the formatting to get integer data for summation
            var floatVal = function (i) {
                if (lang == 'de') {
                    return typeof i === 'string' ? i.replace(/[\.]/g, '').replace(/[\,]/g, '.') * 1 : typeof i === 'number' ? i : 0;
                } else {
                    return typeof i === 'string' ? i.replace(/[\,]/g, '') * 1 : typeof i === 'number' ? i : 0;
                }
            };
            
            // Total over all pages
            var twoDecPlacesCols = [3, 7];
            var total;
            var curr = data[0].ctr.currency;
            for ( i=0; i < twoDecPlacesCols.length; i++ ) {
                total = api
                    .column(twoDecPlacesCols[i])
                    .data()
                    .reduce(function (a, b) {
                        return floatVal(a) + floatVal(b);
                    }, 0);
                // Update footer
                $(api.column(twoDecPlacesCols[i]).footer()).html( numberRenderer(total) + " " + curr );
            }
        }
    } );
    
    subOrderTable
        .on ('init', function () {
            var interval = 
            setInterval( function () {
                subOrderTable
                    .columns.adjust()
                    .responsive.recalc();
            }, 100);
            setTimeout( function () {
                clearInterval(interval);
            }, 250);
            $.ajax({
                type: "POST",
                url: 'actions.php?action=getOrderSummationValues',
                data: { ctr_id: parentId },
                dataType: "json",
                success: function (data) {
                    $('#subTotalCosts').html( data.message );
                }
            });
        } )
        .on ( 'select', function (e, dt, type, indexes) {
            var selected = dt.row( {selected: true} );
            if (selected.any()) {
                subOrderId = selected.data().sub_order.id;
                subOrderEditor.field("sub_order_schedule[].id").dt().ajax.reload();
                if ( selected.data().DT_RowId.substr(0, 7) === "row_gen" ) {
                    dt.buttons(".editRemove").disable();
                } else {
                    dt.buttons(".editRemove").enable();
                }
            } 
        });
    
    $('#subOrderModal .modal-dialog').draggable();
    if ( currentUserId == 2394 ) { //Frau Kreder aus Würselen
        $('#subOrderModal .modal-dialog').addClass("modal-xl");
        $('#subOrderModal .modal-body').addClass("modal-body-xl");
    } else {
        $('#subOrderModal .modal-dialog').addClass("modal-xl");
    }
}
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421

    PHP on the server:

    use DataTables\Editor;
use DataTables\Editor\Field;
use DataTables\Editor\Mjoin;
use DataTables\Editor\Options;
use DataTables\Editor\Upload;

function tblSubOrder(&$db, &$lang, $ctrId=0) { 
    if ($lang === 'de') {     
        $msg[0] = 'Feld darf nicht leer sein.';
        $msg[1] = 'Max. Feldlänge: ';
        $signsLit = ' Zeichen.';
    } else {
        $msg[0] = 'Field may not be empty.';
        $msg[1] = 'Max. field length: ';
        $signsLit = ' characters.';
    }
    if ( $ctrId > 0 ) {
        $_POST['ctr_id'] = $ctrId;
    }
    if ( ! isset($_POST['ctr_id']) || ! is_numeric($_POST['ctr_id']) ) {
        echo json_encode( [ "data" => [] ] );
    } else {
        $editor = Editor::inst( $db, 'sub_order' )
        ->field(
            Field::inst( 'sub_order.id' )->set( false ),
            Field::inst( 'sub_order.id AS sub_order_id')->set( false ),
            Field::inst( 'sub_order.ctr_id' )->set(Field::SET_CREATE)
                                             ->setValue( filter_var($_POST['ctr_id']) ),
            Field::inst( 'sub_order.order_name' )
                ->validator( function ( $val, $data, $opts ) use ( $msg, $signsLit ) {
                    if ( isset($val) ) {
                        if ( $val <= '' ) {
                            return $msg[0];
                        }
                        if ( strlen($val) > 100 ) {
                            return $msg[1] . 100 . $signsLit;
                        }
                    }
                    return true;
                } ),
            Field::inst( 'sub_order.order_partner' )
                ->validator( function ( $val, $data, $opts ) use ( $msg, $signsLit ) {
                    if ( isset($val) ) {
                        if ( $val <= '' ) {
                            return $msg[0];
                        }
                        if ( strlen($val) > 255 ) {
                            return $msg[1] . 255 . $signsLit;
                        }
                    }
                    return true;
                } ),
            Field::inst( 'sub_order.order_date' )
                ->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
                ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                   
                } )
                ->setFormatter( function ( $val, $data, $opts ) {
                    return setFormatterDate($val);
                } ),
            Field::inst( 'sub_order.order_date AS order_date_unformatted')->set( false ),
            Field::inst( 'sub_order.order_total_costs' )
                ->getFormatter( function($val, $data, $opts) {
                    return getFormatterAmount($val);
                })
                ->setFormatter( function($val, $data, $opts) {
                    //an order might be free of charge
                    if ( is_null($val) || $val <= '' ) {
                        return 0;
                    }
                    return setFormatterSubAmount($val, 1); //we are paying for the order! (like sub_provider)
                }), 
            Field::inst( 'sub_order.order_short_desc' )
                ->validator( function ( $val, $data, $opts ) use ( $msg, $signsLit ) {
                    if ( isset($val) ) {
                        if ( strlen($val) > 1024 ) {
                            return $msg[1] . 1024 . $signsLit;
                        }
                    }
                    return true;
                } ),
            Field::inst( 'sub_order_schedule.billing_date' )->set( false )
                ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                   
                } ),
            Field::inst( 'sub_order_schedule.billing_date AS billing_date_unformatted')->set( false ),
            Field::inst( 'sub_order_schedule.billing_number' )->set( false ),
            Field::inst( 'sub_order_schedule.payment_amount' )->set( false )
                ->getFormatter( function($val, $data, $opts) {
                    return getFormatterAmount($val);
                }),
            Field::inst( 'sub_order_schedule.payment_date' )->set( false )
                ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                   
                } ),
//            Field::inst( 'sub_order_schedule.payment_date AS payment_date_unformatted')->set( false ),
            Field::inst( 'sub_order_schedule.voucher_number' )->set( false ),
            Field::inst( 'sub_order_schedule.add_info' )->set( false ),
            Field::inst( 'ctr.currency' )->set( false )            
        )
        ->leftJoin( 'sub_order_schedule', 'sub_order.id', '=', 'sub_order_schedule.sub_order_id')
        ->leftJoin( 'ctr', 'sub_order.ctr_id', '=', 'ctr.id')                
        ->where( function ( $q ) {        
            $q  ->where( 'sub_order.ctr_id', $_POST['ctr_id'] );
        } )
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421 
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
            logChange( $editor->db(), 'create', $id, $row, 'sub_order' );
        } )
        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
            logChange( $editor->db(), 'edit', $id, $row, 'sub_order' );
        } )
        ->on( 'postRemove', function ( $editor, $id, $values ) {
            logChange( $editor->db(), 'delete', $id, $values, 'sub_order' );
        } )
        ->on( 'postGet', function ( $editor, &$data, $id ) use ($ctrId) {     
            $noData = false;
            if ( count($data) <= 0 ) {
                $noData = true;
            }
            $subProjects = [];
            $result = $editor->db()->raw()
                ->bind( ':ctr_id', $_POST['ctr_id'] )  
                ->exec( 'SELECT main_project 
                           FROM sub  
                          WHERE ctr_id = :ctr_id' );
            $row = $result->fetch(PDO::FETCH_ASSOC);
            if ( (bool)$row["main_project"] ) {
                $lnk = getSubProjectIds( $_POST['ctr_id'], $editor->db() );
                $rowIdCounter = 0;
                foreach ($lnk as $val) {
                    $result = $editor->db()->raw()
                        ->bind( ':linked_ctr_id', $val["linked_ctr_id"] )  
                        ->exec( 'SELECT c.serial, b.sub_name, c.currency, 
                                        SUM(a.order_total_costs) AS sum_total_costs, 
                                        MAX(a.order_date) AS max_order_date 
                                   FROM sub_order a
                             INNER JOIN sub b ON a.ctr_id = b.ctr_id
                             INNER JOIN ctr c ON a.ctr_id = c.id
                                  WHERE a.ctr_id = :linked_ctr_id
                               GROUP BY 1, 2, 3
                                 HAVING sum_total_costs <> 0
                                  LIMIT 1' );      
                    $sub = $result->fetch(PDO::FETCH_ASSOC);
                    if ( ! empty($sub) ) {
                        $res = $editor->db()->raw()
                            ->bind( ':linked_ctr_id', $val["linked_ctr_id"] )  
                            ->exec( 'SELECT SUM(a.payment_amount) AS sum_total_payments, 
                                            MAX(a.payment_date) AS max_payment_date,
                                            MAX(a.billing_date) AS max_billing_date 
                                       FROM sub_order_schedule a
                                 INNER JOIN sub_order b ON a.sub_order_id = b.id
                                      WHERE b.ctr_id = :linked_ctr_id
                                     HAVING sum_total_payments <> 0
                                      LIMIT 1' );      
                        $pay = $res->fetch(PDO::FETCH_ASSOC);
                        if ( ! empty($pay) ) {
                            $max_payment_date = getFormatterDate($pay["max_payment_date"]);
                            $max_billing_date = getFormatterDate($pay["max_billing_date"]);
                            $sum_total_payments = getFormatterAmount($pay["sum_total_payments"]);
                        } else {
                            $max_payment_date = $max_billing_date = $sum_total_payments = "";
                        }
                        $rowIdCounter++;
                        $noData = false;
                        $order_name = renderCtrSerialNoZeroes($sub["serial"]) . 
                                      ' - ' . $sub["sub_name"];
                        $sum_total_costs = getFormatterAmount($sub["sum_total_costs"]);
                        $max_order_date  = getFormatterDate($sub["max_order_date"]);   
                        $subProjects[] = [ "DT_RowId"  => "row_gen_" . $rowIdCounter,  //generated row
                                           "sub_order" => 
                                            [ "id"                  => "0",
                                              "ctr_id"              => $_POST['ctr_id'],
                                              "order_name"          => $order_name, 
                                              "order_partner"       => "s. Untermaßnahme", 
                                              "order_date"          => $max_order_date, 
                                              "order_total_costs"   => $sum_total_costs, 
                                              "order_short_desc"    => ""                ],
                                           "sub_order_schedule" =>
                                            [ "billing_date"        => $max_billing_date,
                                              "billing_number"      => "",  
                                              "payment_amount"      => $sum_total_payments,
                                              "payment_date"        => $max_payment_date,
                                              "voucher_number"      => "",
                                              "add_info"            => "Details: s. Untermaßnahme"  ],
                                           "ctr" => 
                                            [ "currency"            => $sub["currency"] ]  ];
                    }
                }
            }
            if ( $noData ) {
                return;
            }
            $subOrderId = 0;
            array_multisort( array_column($data, "order_date_unformatted"),   SORT_ASC,
                             array_column($data, "sub_order_id"),             SORT_ASC,
                             array_column($data, "billing_date_unformatted"), SORT_ASC,
                             $data );
            $keys = array_keys($data);
            foreach ( $keys as $key ) {
                if ( $data[$key]["sub_order_id"] == $subOrderId ) {
                    $data[$key]["sub_order"]["order_name"]          = '';
                    $data[$key]["sub_order"]["order_partner"]       = '';
                    $data[$key]["sub_order"]["order_date"]          = '';
                    $data[$key]["sub_order"]["order_total_costs"]   = '';
                    $data[$key]["sub_order"]["order_short_desc"]    = '';
                }
                $subOrderId = $data[$key]["sub_order_id"];
                unset($data[$key]["order_date_unformatted"]);
                unset($data[$key]["sub_order_id"]);
                unset($data[$key]["billing_date_unformatted"]);
            }
            //prepend the sub projects to the data read
            $data = array_merge($subProjects, $data);
//            if ( $ctrId > 0 ) {
//                $_SESSION['sub_order_data'] = $data;
//            }
        })
        ->process($_POST);
        if ( $ctrId <= 0 ) {
            $editor->json();
        }
    }
    if ( $ctrId > 0 ) {
        return $editor->data();
    }
}
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421

    And the PHP for the editor in fieldType "Datatable":

    use DataTables\Editor;
use DataTables\Editor\Field;
use DataTables\Editor\Mjoin;
use DataTables\Editor\Options;
use DataTables\Editor\Upload;

function tblSubOrderSchedule(&$db, &$lang) { 
    if ($lang === 'de') {     
        $msg[0] = 'Feld darf nicht leer sein.';
        $msg[1] = 'Max. Feldlänge: ';
        $signsLit = ' Zeichen.';
    } else {
        $msg[0] = 'Field may not be empty.';
        $msg[1] = 'Max. field length: ';
        $signsLit = ' characters.';
    }
    if ( ! isset($_POST['sub_order_id']) || ! is_numeric($_POST['sub_order_id']) ) {
        echo json_encode( [ "data" => [] ] );
    } else {
        Editor::inst( $db, 'sub_order_schedule' )
        ->field(
            Field::inst( 'sub_order_schedule.sub_order_id' )->set(Field::SET_CREATE)
                                                            ->setValue( filter_var($_POST['sub_order_id']) ),
            Field::inst( 'sub_order_schedule.billing_date' )
                ->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
                ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                   
                } )
                ->setFormatter( function ( $val, $data, $opts ) {
                    return setFormatterDate($val);
                } ),
            Field::inst( 'sub_order_schedule.billing_number' )
                ->validator( function ( $val, $data, $opts ) use ( $msg, $signsLit ) {
                    if ( isset($val) ) {
                        if ( strlen($val) > 30 ) {
                            return $msg[1] . 30 . $signsLit;
                        }
                    }
                    return true;
                } ),
            Field::inst( 'sub_order_schedule.payment_amount' )
                ->getFormatter( function($val, $data, $opts) {
                    return getFormatterAmount($val);
                })
                ->setFormatter( function($val, $data, $opts) {
                    return setFormatterSubAmount($val, 1); //we are paying for the order! (like sub_provider)
                }), 
            Field::inst( 'sub_order_schedule.payment_date' )
                ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                   
                } )
                ->setFormatter( function ( $val, $data, $opts ) {
                    return setFormatterDate($val);
                } ),
            Field::inst( 'sub_order_schedule.voucher_number' )
                ->validator( function ( $val, $data, $opts ) use ( $msg, $signsLit ) {
                    if ( isset($val) ) {
                        if ( strlen($val) > 30 ) {
                            return $msg[1] . 30 . $signsLit;
                        }
                    }
                    return true;
                } ),
            Field::inst( 'sub_order_schedule.add_info' )
                ->validator( function ( $val, $data, $opts ) use ( $msg, $signsLit ) {
                    if ( isset($val) ) {
                        if ( strlen($val) > 100 ) {
                            return $msg[1] . 100 . $signsLit;
                        }
                    }
                    return true;
                } ),
            Field::inst( 'sub_order_schedule.payment_amount as unformattedAmount' )->set( false )
        )
        ->leftJoin( 'sub_order', 'sub_order_schedule.sub_order_id', '=', 'sub_order.id')
        ->where( function ( $q ) {        
            $q  ->where( 'sub_order_schedule.sub_order_id', $_POST['sub_order_id'] );
        } )
        ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
            logChange( $editor->db(), 'create', $id, $row, 'sub_order_schedule' );
        } )
        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
            logChange( $editor->db(), 'edit', $id, $row, 'sub_order_schedule' );
        } )
        ->on( 'postRemove', function ( $editor, $id, $values ) {
            logChange( $editor->db(), 'delete', $id, $values, 'sub_order_schedule' );
        } )
        ->on( 'postGet', function ( $e, &$data, $id ) use ( $lang ) { 
            if ( count($data) <= 0 ) {
                return;
            }
            $sum = array_sum(array_column($data, "unformattedAmount"));
            $keys = array_keys($data);
            foreach ( $keys as $key ) {
                unset($data[$key]["unformattedAmount"]);
            }   
            if ( $sum != 0 ) {
                $data[] = [ "DT_RowId" => "row_0", //in reality there is no row 0 because it is derived from the SQL id
                            "sub_order_schedule" => 
                                [ "sub_order_id"   => $_POST['sub_order_id'], 
                                  "billing_date"   => "", 
                                  "billing_number" => "", 
                                  "payment_amount" => getFormatterAmount($sum),
                                  "payment_date"   => $lang === "de" ? "Summe" : "Total",
                                  "voucher_number" => "", 
                                  "add_info"       => ""    ]   ];
            }
        })
        ->process($_POST)            
        ->json();
    }
}
  • rf1234rf1234 Posts: 3,004Questions: 87Answers: 421

    Something went wrong when copying the exportOptions above. Here is the correct code:

    exportOptions: {
    columns: function(column, data, node) {
//                            if ($.inArray(column, [16, 17, 18, 20]) >= 0) {
//                                return false;
//                            }
        return true;
    },
    modifier: { selected: null }, //make sure all records show up
    format: {
        body: function ( data, row, column, node ) {
            return formatXLSBodyFoooter(data, column, [3, 7]); //'D', 'H'
        },
        header: function ( data, column, node ) {
            //replace html tags with one space
            return data.replace(/<[^>]*>/g, ' ');
        },
        footer: function ( data, column, node ) {
            if ( subOrderTable.rows( {search: "applied"} ).count() > 0 ) {
                //make sure empty footer cells are colored green, too!
                //for that purpose they must not be empty!
                if ( data <= "" ) {
                    data = " ";
                }
                return formatXLSBodyFoooter(data, column, [3, 7]); //'D', 'H'
            }
            return "";
        }
    }
}
  • lancwplancwp Posts: 90Questions: 20Answers: 1

    Thank @rf1234

Sign In or Register to comment.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.