// function called to dynamically create child table HTML

function formatChildTable(rowData, prefix, tableId) {

  let childTable =

    '<table id="' +

    prefix +

    "-" +

    tableId +

    '" class="display compact" width="100%">' +

    '<thead style="display:none">' +

    "</thead> " +

    "</table>";

  return $(childTable).toArray();

}

​

// footerCallback function for parent table which computes sums in the footer row.

//  - sums for filtered data, reflecting sum of visible rows wiht class of

//  - remove child toggle control from leftmost column

//  - put full value decimal value in data-value attribute

function computeSums(row, data, start, end, display) {

  var api = this.api();

  var numFormat = $.fn.dataTable.render.number(",", ".", 0, "$").display;

  api.columns(".col-fees-sum", { search: "applied" }).every(function () {

    var sum = this.data().reduce(function (a, b) {

      var x = parseFloat(a) || 0;

      var y = parseFloat(b) || 0;

      return x + y;

    }, 0);

    //$(this.footer()).attr("data-value", sum); //retain precision for excel export

    $(this.footer()).html(numFormat(sum.toFixed()));

  });

  $(api.table().column(0).footer()).removeClass(); // remove child-toggle-control from footer row

}

​

var debug = true;

var invChildTable = {};

var matterData = [

  {

    MatterID: "123456.0001",

    MatterName: "Project X-Ray",

    WIP: 1000.49,

    AR: 30000,

    AR_000_060: 6000,

    AR_061_120: 6000,

    AR_121_180: 3000,

    AR_181: 1000

  },

  {

    MatterID: "123456.0002",

    MatterName: "Project CT Scan",

    WIP: 1500.49,

    AR: 6500,

    AR_000_060: 5000,

    AR_061_120: 1000,

    AR_121_180: 500,

    AR_181: 0

  },

  {

    MatterID: "123456.0003",

    MatterName: "Project Gurney Lift",

    WIP: 4000.49,

    AR: 13000,

    AR_000_060: 10000,

    AR_061_120: 2000,

    AR_121_180: 1000,

    AR_181: 0

  },

  {

    MatterID: "123456.0004",

    MatterName: "Project Nurse Call",

    WIP: 6000.49,

    AR: 3000,

    AR_000_060: 1000,

    AR_061_120: 1000,

    AR_121_180: 1000,

    AR_181: 0

  }

];

var invoiceData = [

  [

    {

      InvoiceID: 1000001,

      InvoiceDate: "2024-01-31T00:00:00",

      WIP: 0,

      AR: 8000,

      AR_000_060: 4000,

      AR_061_120: 3000,

      AR_121_180: 1000,

      AR_181: 0

    },

    {

      InvoiceID: 1000002,

      InvoiceDate: "2024-01-31T00:00:00",

      WIP: 0,

      AR: 6000,

      AR_000_060: 0,

      AR_061_120: 3000,

      AR_121_180: 2000,

      AR_181: 1000

    },

    {

      InvoiceID: 1000003,

      InvoiceDate: "2024-01-31T00:00:00",

      WIP: 0,

      AR: 9000,

      AR_000_060: 1000,

      AR_061_120: 0,

      AR_121_180: 0,

      AR_181: 0

    },

    {

      InvoiceID: 1000004,

      InvoiceDate: "2024-01-31T00:00:00",

      WIP: 0,

      AR: 7000,

      AR_000_060: 1000,

      AR_061_120: 0,

      AR_121_180: 0,

      AR_181: 0

    }

  ],

  [

    {

      InvoiceID: 1000002,

      InvoiceDate: "2024-01-31T00:00:00",

      WIP: 0,

      AR: 92488.79,

      AR_000_060: 0,

      AR_061_120: 0,

      AR_121_180: 0,

      AR_181: 0

    }

  ],

  [

    {

      InvoiceID: 1000003,

      InvoiceDate: "2024-01-31T00:00:00",

      WIP: 0,

      AR: 92488.79,

      AR_000_060: 0,

      AR_061_120: 0,

      AR_121_180: 0,

      AR_181: 0

    }

  ],

  [

    {

      InvoiceID: 1000004,

      InvoiceDate: "2024-01-31T00:00:00",

      WIP: 0,

      AR: 92488.79,

      AR_000_060: 0,

      AR_061_120: 0,

      AR_121_180: 0,

      AR_181: 0

    }

  ]

];

var columnsInventoryByMatter = [

  { data: null },

  { data: "MatterName", title: "Matter Name" },

  { data: "WIP", title: "WIP<br>Total", name: "WIP" },

  { data: "AR", title: "AR<br>Total" },

  { data: "AR_000_060", title: "AR<br>0-60" },

  { data: "AR_061_120", title: "AR<br>61-120" },

  { data: "AR_121_180", title: "AR<br>121-180" },

  { data: "AR_181", title: "AR<br>+181" },

  {

    // ADDED META ROW

    data: null,

    title: "row.meta",

    visible: true,

    render: function (data, type, row, meta) {

      return meta.row;

    }

  }

];

var columnDefsInventoryByMatter = [

  {

    targets: [0],

    className: "inv-details-control",

    orderable: false,

    defaultContent: ""

  },

  {

    targets: [1],

    className: "col-label",

    orderSequence: ["asc", "desc"]

  },

  {

    targets: [2, 3, 4, 5, 6, 7],

    className: "col-metric100 col-fees-sum",

    orderSequence: ["desc", "asc"],

    render: $.fn.dataTable.render.number(",", ".", 0, "$")

  },

  {

    targets: [8],

    className: "col-metric100"

  }

];

var columnsInventoryByInvoice = [

  {

    data: null

  },

  {

    data: "InvoiceID"

  },

  { data: null, defaultContent: "", name: "WIP" },

  { data: "AR" },

  { data: "AR_000_060" },

  { data: "AR_061_120" },

  { data: "AR_121_180" },

  { data: "AR_181" },

  {

    // ADDED META ROW

    data: null,

    visible: true,

    render: function (data, type, row, meta) {

      return meta.row;

    }

  }

];

var columnDefsInventoryByInvoice = [

  {

    targets: [0],

    className: "inv-details-control-blank",

    orderable: false,

    defaultContent: ""

  },

  {

    targets: [1],

    className: "col-label0"

  },

  {

    targets: [2, 3, 4, 5, 6, 7],

    className: "col-metric100",

    render: $.fn.dataTable.render.number(",", ".", 0, "$")

  },

  {

    targets: [8],

    className: "col-metric100"

  }

];

​

// format currency values by removing currency and commas from numbers

let exportFormatter = {

  format: {

    body: function (data, row, column, node) {

      if ($(node).hasClass("col-fees-sum")) {

        data = data.replace(/[$,]/g, "");

      }

​

      return data;

    }

  }

};

​

var buttonsCustom = [

  {

    extend: "excelHtml5",

    exportOptions: exportFormatter,

    customize: function (xlsx) {

      var table = $("#datatable-inventory").DataTable();

​

      // Get number of columns to remove last hidden index column.

      var numColumns = table.columns().header().count() - 1;

​

      if (debug) {

        console.log("There are " + numColumns + " columns in the main table.");

      }

​

      // Get sheet.

      var sheet = xlsx.xl.worksheets["sheet1.xml"];

​

      var col = $("col", sheet);

      // Set the column width.

      $(col[1]).attr("width", 20);

​

      // Get a clone of the sheet data.

      var sheetData = $("sheetData", sheet).clone();

      //console.log($("sheetData", sheet));

      // Clear the current sheet data for appending rows.

      $("sheetData", sheet).empty();

​

      // Row index from last column.

      var DT_row; // Row count in Excel sheet.

​

      var rowCount = 1;

​

      // Itereate each row in the sheet data.

      $(sheetData)

        .children()

        .each(function (index) {

          // Used for DT row() API to get child data.

          var rowIndex = index - 1;

​

          if (debug) {

            console.log("Processing Parent Table, rowCount " + rowCount);

          }

​

          // Don't process row if its the header row.

          if (index > 1) {

            // Get row

            var row = $(this.outerHTML);

​

            // Set the Excel row attr to the current Excel row count.

            row.attr("r", rowCount);

​

            var colCount = 1;

​

            // Iterate each cell in the row to change the row number.

            row.children().each(function (index) {

              var cell = $(this);

​

              // Set each cell's row value.

              var rc = cell.attr("r");

              rc = rc.replace(/\d+$/, "") + rowCount;

              cell.attr("r", rc);

​

              if (colCount === numColumns) {

                if (debug) {

                  console.log("    meta.row = " + cell.text());

                }

​

                DT_row = cell.text();

                cell.html("");

              }

​

              colCount++;

            });

​

            // Get the row HTML and append to sheetData.

            row = row[0].outerHTML;

            $("sheetData", sheet).append(row);

​

            if (debug) {

              console.log(

                "    Parent Table Row Exported : " +

                  table.row(DT_row).data().MatterName

              );

              //console.log(row);

            }

            rowCount++;

​

            var childTableId = $(table.row(DT_row).node()).attr("id");

​

            var childTable = $(

              "#datatable-inventory-" + childTableId

            ).DataTable();

​

            var childData = childTable

              .row(DT_row, { search: "none", order: "index" })

              .data();

​

            if ($(table.row(DT_row).node()).hasClass("shown")) {

              if (debug) {

                console.log(

                  "Processing Child Table with " +

                    childTable.rows().count() +

                    " row(s)"

                );

              }

​

              // The child data is an array of rows

              for (c = 0; c < childTable.rows().count(); c++) {

                // Get row data.

                rowData = childTable.row(c, { order: "applied" }).data(); // order is specifci as applied, yet inital order is used instead.

​

                // Prepare Excel formated row

                childRow =

                  '<row r="' +

                  rowCount +

                  '"><c t="inlineStr" r="A' +

                  rowCount +

                  '"><is><t>' +

                  '</t></is></c><c t="inlineStr" r="B' +

                  rowCount +

                  '"><is><t>---' +

                  rowData.InvoiceID +

                  '</t></is></c><c r="C' +

                  rowCount +

                  '" s="65"><v>' +

                  rowData.WIP +

                  '</v></c><c r="D' +

                  rowCount +

                  '" s="65"><v>' +

                  rowData.AR +

                  '</v></c><c r="E' +

                  rowCount +

                  '" s="65"><v>' +

                  rowData.AR_000_060 +

                  '</v></c><c r="F' +

                  rowCount +

                  '" s="65"><v>' +

                  rowData.AR_061_120 +

                  '</v></c><c r="G' +

                  rowCount +

                  '" s="65"><v>' +

                  rowData.AR_121_180 +

                  '</v></c><c r="H' +

                  rowCount +

                  '" s="65"><v>' +

                  rowData.AR_181 +

                  "</v></c></row>";

​

                // Append row to sheetData.

                $("sheetData", sheet).append(childRow);

                if (debug) {

                  console.log(

                    "    Child Table Row Exported : " + rowData.InvoiceID

                  );

                  //console.log("       " + childRow);

                }

                rowCount++; // Inc excelt row counter.

              }

            }

            // Just append the header row and increment the excel row counter.

          } else {

            var row = $(this.outerHTML);

​

            var colCount = 1;

​

            // Remove the last header cell.

            row.children().each(function (index) {

              var cell = $(this);

​

              if (colCount === numColumns) {

                cell.html("");

              }

​

              colCount++;

            });

            row = row[0].outerHTML;

            $("sheetData", sheet).append(row);

            if (debug) {

              console.log("Header Exported (rowCount:" + rowCount + ")");

              //console.log(row);

            }

            rowCount++;

          }

        });

​

      var footer =

        '<row r="' +

        rowCount +

        '"><c t="inlineStr" r="B' +

        rowCount +

        '"><is><t xml:space="preserve">' +

        "TOTAL" +

        '</t></is></c><c r="C' +

        rowCount +

        '" s="65"><v>' +

        $(table.column(2).footer()).html().replace(/[$,]/g, "") + //WIP

        '</v></c><c r="D' +

        rowCount +

        '" s="65"><v>' +

        $(table.column(3).footer()).html().replace(/[$,]/g, "") + //AR

        '</v></c><c r="E' +

        rowCount +

        '" s="65"><v>' +

        $(table.column(4).footer()).html().replace(/[$,]/g, "") + //AR 0-60

        '</v></c><c r="F' +

        rowCount +

        '" s="65"><v>' +

        $(table.column(5).footer()).html().replace(/[$,]/g, "") + //AR 61-120

        '</v></c><c r="G' +

        rowCount +

        '" s="65"><v>' +

        $(table.column(6).footer()).html().replace(/[$,]/g, "") + //AR 121-180

        '</v></c><c r="H' +

        rowCount +

        '" s="65"><v>0' +

        $(table.column(7).footer()).html().replace(/[$,]/g, "") + //AR 181 +

        "</v></c></row>";

      $("sheetData", sheet).append(footer);

      if (debug) {

        console.log("Footer Exported (rowCount:" + rowCount + ")");

        //console.log(footer);

      }

    }

  }

];

​

$(document).ready(function () {

  var dtInventory = $("#datatable-inventory").DataTable({

    dom: "Bftip",

    buttons: buttonsCustom,

    data: matterData,

    order: [[3, "desc"]],

    columns: columnsInventoryByMatter,

    columnDefs: columnDefsInventoryByMatter,

    footerCallback: computeSums,

    createdRow: function (row, data, index) {

      row.id = "matter_" + index;

    }

  });

​

  $("#datatable-inventory tbody").on(

    "click",

    "td.inv-details-control",

    function () {

      var tr = $(this).closest("tr");

      let parentRowId = tr[0].id;

      var row = dtInventory.row(tr);

      var rowData = row.data();

​

      if (row.child.isShown()) {

        row.child.hide();

        tr.removeClass("shown");

      } else {

        row

          .child(formatChildTable(rowData, "datatable-inventory", parentRowId))

          .show();

        var id = rowData.MatterID;

        var arrayIndex = Number(id.substr(id.length - 4)) - 1;

        invChildTable[parentRowId] = $(

          "#datatable-inventory-" + parentRowId

        ).DataTable({

          dom: "t",

          pageLength: -1,

          order: dtInventory.order(),

          data: invoiceData[arrayIndex],

          columns: columnsInventoryByInvoice,

          columnDefs: columnDefsInventoryByInvoice

        });

        tr.addClass("shown");

      }

    }

  );

});

​