Display values in a table Javascript

Question

I am creating a table with the data I get from a query, the query reaches me in an array like this:

var data = [
      {id:1, land: 'FINCA1',  product: "ROSA", week:25,  quantity: 2000}, 
      {id:1, land: 'FINCA2',  product: "ROSA", week:25, quantity: 1900}, 
      {id:1, land: 'FINCA3',  product: "ROSA", week:25, quantity: 350},
      {id:1, land: 'FINCA3',  product: "ROSA1",week:25, quantity: 410},
      {id:1, land: 'FINCA1',  product: "ROSA", week:26, quantity: 1254},
      {id:1, land: 'FINCA2' ,  product: "ROSA",week:26, quantity: 123},
      {id:1, land: 'FINCA3' ,  product: "ROSA",week:26, quantity: 200}
    ];

The following code shows the values ​​of each product per week and their total as follows:

//I get the keys of the object to create the thead of the table and keep it in a variable

var tabla = document.createElement('table');
var tableHead = document.createElement('tr');

Object.keys(data[0]).forEach(prop=>{
  if(prop != 'week' && prop != 'quantity')
  tableHead.innerHTML += '<th>'+prop+'</th>';
});

var semanas = data.reduce( (semanas_, elemento)=>{
  if(semanas_.indexOf(elemento.week) == -1)
    semanas_.push(elemento.week)
  return semanas_;
}, []);

semanas.forEach( semana=>{
  tableHead.innerHTML += '<th>'+semana+'</th>';
});


var total_semanas = [];


tableHead.innerHTML += '<th>Total</th>';
tabla.appendChild(tableHead);

var tbody = document.createElement('tbody');

tabla.appendChild(tbody);

document.querySelector('body').appendChild(tabla);

var arrayOrganizado = data.reduce((arr, item)=>{


  var t = total_semanas.find(e=> e.semana === item.week);
  if (t) t.total += item.quantity;
  else total_semanas.push({ semana : item.week, total : item.quantity });


  item.week = {
    numero : item.week,
    cantidad : item.quantity
  }
  var ele = arr.find(it=>it.land === item.land && it.product === item.product);
  if(ele){
    ele.week.push(item.week);
    ele.total = ele.week.reduce((a, b)=> a + b.cantidad, 0);
  } else {
    item.week = [item.week];
    item.total = item.quantity;
    arr.push(item);
  }
  return arr;
}, []);

arrayOrganizado.forEach(fila=>{
  var f = Object.keys(fila).reduce( (a, b)=>{
    //console.log(b);
    if(b != 'week' && b != 'quantity' && b != 'total')
      return a +  '<td>' + fila[b] + '</td>';
    return a;
  }, '');
  tbody.innerHTML += f + semanas.reduce( (a, _, i)=>a + '<td>' + (fila.week[i] ? fila.week[i].cantidad : 0) + '</td>', '') + '<td>' + fila.total + '</td>';   
});

var f = Object.keys(arrayOrganizado[0]).reduce( (a, b)=>{
  //console.log(b);
  if(b != 'week' && b != 'quantity' && b != 'total')
    return a +  '<td> ---- </td>';
  return a;
}, '');

tbody.innerHTML += f + total_semanas.reduce( (a, _, i)=>a + '<td>' + _.total + '</td>', '') + '<td>' + total_semanas.reduce( (a, b) => a.total + b.total) + '</td>'; 

And he shows me a table like this:

-----------------------------------------------------
Land     |     Product   |    25   |    26   | Total |
-----------------------------------------------------
FINCA1   |     ROSA      |   2000  |   1254  |  3254 |
-----------------------------------------------------
FINCA2   |     ROSA      |   1900  |   123   |  2023 |
-----------------------------------------------------
FINCA3   |     ROSA      |   350   |   200   |  550  |
-----------------------------------------------------
FINCA3   |     ROSA1     |   410   |    0    |  410  |
-----------------------------------------------------
TOTAL    |               |   4660  |   1577  |  6237 |
------------------------------------------------------

Until everything is fine here , the problem is that when the weeks are increased , there may be weeks that have no values ​​and the result is 0, but instead put that value in the week corresponding puts it in the last , let me explain:

The array can be as follows

var data = [
      {id:1, land: 'FINCA1',  product: "ROSA", week:25,  quantity: 2000}, 
      {id:1, land: 'FINCA2',  product: "ROSA", week:25, quantity: 1900}, 
      {id:1, land: 'FINCA3',  product: "ROSA", week:25, quantity: 350},
      {id:1, land: 'FINCA3',  product: "ROSA1",week:25, quantity: 410},
      {id:1, land: 'FINCA1',  product: "ROSA", week:26, quantity: 1254},
      {id:1, land: 'FINCA2' , product: "ROSA", week:26, quantity: 123},
      {id:1, land: 'FINCA3' , product: "ROSA", week:26, quantity: 200},
      {id:1, land: 'FINCA3' , product: "ROSA", week:24, quantity: 200}
      {id:1, land: 'FINCA3' , product: "ROSA", week:23, quantity: 1200}
    ];

The table should look like:

-----------------------------------------------------------------------
Land     |     Product   |    23   |    24   |   25  |  26  |   Total |
-----------------------------------------------------------------------
FINCA1   |     ROSA      |   0     |   0     |  2000 | 1254 |  3254   |
-----------------------------------------------------------------------
FINCA2   |     ROSA      |   0     |   0     |  1900 | 123  |  2023   | 
-----------------------------------------------------------------------
FINCA3   |     ROSA      |   1200  |   200   |  350  | 200  |  1950   |
-----------------------------------------------------------------------
FINCA3   |     ROSA1     |   0     |    0    |  410  |   0  |  410    |
-----------------------------------------------------------------------
TOTAL    |               |   1200  |   200   |  4660 | 1577 |  7637   |
-----------------------------------------------------------------------

However, the table is as follows ;

-----------------------------------------------------------------------
Land     |     Product   |    23   |    24   |   25  |  26  |   Total |
-----------------------------------------------------------------------
FINCA1   |     ROSA      |   2000  |  1254   |  0    | 0    |  3254   |
-----------------------------------------------------------------------
FINCA2   |     ROSA      |   1900  |   123   |  0    | 0    |  2023   | 
-----------------------------------------------------------------------
FINCA3   |     ROSA      |   1200  |   200   |  350  | 200  |  1950   |
-----------------------------------------------------------------------
FINCA3   |     ROSA1     |   410   |    0    |    0  |   0  |  410    |
-----------------------------------------------------------------------
TOTAL    |               |   1200  |   200   |  4660 | 1577 |  7637   |
-----------------------------------------------------------------------

I think it is the function reduce() generates the problem in this part:

tbody.innerHTML += f + semanas.reduce( (a, _, i)=>a + '<td>' + (fila.week[i] ? fila.week[i].cantidad : 0) + '</td>', '') + '<td>' + fila.total + '</td>';

I don´t know if there is another similiar function to do the same, or if this function.

Answer 1

It's more code, but easier to follow for those less familiar with the language.

var data = {};
// Step 1: Prepare the data
prepareData(data);
let fields      = Object.keys(data.source[0]);
let skip_fields = ['id','quantity','week'];
// Step 2: Generate table
generateTable(data, fields, skip_fields);


function prepareData(data){
  data.source = [
    {id:1, land: 'FINCA1',  product: "ROSA", week:25,  quantity: 2000}, 
    {id:1, land: 'FINCA2',  product: "ROSA", week:25, quantity: 1900}, 
    {id:1, land: 'FINCA3',  product: "ROSA", week:25, quantity: 350},
    {id:1, land: 'FINCA3',  product: "ROSA1",week:25, quantity: 410},
    {id:1, land: 'FINCA1',  product: "ROSA", week:26, quantity: 1254},
    {id:1, land: 'FINCA2' , product: "ROSA", week:26, quantity: 123},
    {id:1, land: 'FINCA3' , product: "ROSA", week:26, quantity: 200},
    {id:1, land: 'FINCA3' , product: "ROSA", week:24, quantity: 200},
    {id:1, land: 'FINCA3' , product: "ROSA", week:23, quantity: 1200}
  ];
  // organize by composite keys
  data.nested = {};
  
  // build data.nested
  data.source.forEach((entry,index)=>{
    let land = entry.land,
        prod = entry.product,
        week = entry.week;
    data.nested[land]             = data.nested[land]             || {};
    data.nested[land][prod]       = data.nested[land][prod]       || {};
    data.nested[land][prod][week] = data.nested[land][prod][week] || [];
    
    let week_arr = data.nested[land][prod][week];
    week_arr[ week_arr.length ] = index; // hold index of data array
  });
}


function generateTable(data,fields,skip_fields){
  // Declare and Instantiate DOM elements
  let table = document.createElement('table');
  let thead = document.createElement('thead');
  let tbody = document.createElement('tbody');
  let weeks = {};
  data.source.forEach(entry=>weeks[entry.week]=0); // set weeks to 0 to be used for subtotals
  let _weeks = Object.keys(weeks).sort();
    
 
  let thead_row = generateTableHeaderRow(fields,skip_fields);
  thead.appendChild(thead_row);
  table.appendChild(thead);
  table.appendChild(tbody);
  generateTableDataRows(data,weeks,tbody,fields);
  document.querySelector('body').appendChild(table); // add to DOM
  
  function generateTableHeaderRow(fields,skip_fields){
    let tr = document.createElement('tr');
    // Generate TH for fields
    fields.forEach(field => {
      if(skip_fields.indexOf(field) == -1)
         tr.innerHTML += `<th>${field}</th>`;
    });
    _weeks.forEach(week=>tr.innerHTML+=`<th>${week}</th>`); // generate TH for weeks
    tr.innerHTML+=`<th>total</th>`; // add TH for total
    return tr;
  }

  function generateTableDataRows(data,weeks,tbody,fields){
    // Create Data Rows
    weeks['total']=0;
    Object.keys(data.nested).sort().forEach(week=>{
      Object.keys(data.nested[week]).sort().forEach(prod=>{
        let tr = document.createElement('tr');
        tr.innerHTML+=`<td>${week}</td><td>${prod}</td>`;

        let total = 0;
        _weeks.forEach(wk=>{
          let subtotal = 0;
          if(data.nested[week][prod][wk])
            data.nested[week][prod][wk].forEach(index=>{
              let quantity = +data.source[index].quantity;
              subtotal += quantity;
              weeks[wk] += quantity; 
            });

          tr.innerHTML += `<td>${subtotal}</td>`;
          total += subtotal;
        });
        tr.innerHTML +=`<td>${total}</td>`;
        weeks['total'] += total;
        tbody.appendChild(tr);
      });
    });

    // Create Final Data Row for Totals
    let tbody_row       = document.createElement('tr');
    tbody_row.innerHTML = `<td>Total</td><td></td>`;
    _weeks.forEach(week=>{tbody_row.innerHTML+=`<td>${weeks[week]}</td>`}); // week cells
    tbody_row.innerHTML+=`<td>${weeks.total}</td>`;                       // total cell
    tbody.appendChild(tbody_row);
  }
}

th { text-transform: capitalize; }

Answer 2

I suggets to calculate the sums first and store the result in two objects, one for the week parts and one for grouped items.

Later you can use the values for building the array and access the values direcly.

var data = [{ id: 1, land: 'FINCA1', product: "ROSA", week: 25, quantity: 2000 }, { id: 1, land: 'FINCA2', product: "ROSA", week: 25, quantity: 1900 }, { id: 1, land: 'FINCA3', product: "ROSA", week: 25, quantity: 350 }, { id: 1, land: 'FINCA3', product: "ROSA1", week: 25, quantity: 410 }, { id: 1, land: 'FINCA1', product: "ROSA", week: 26, quantity: 1254 }, { id: 1, land: 'FINCA2', product: "ROSA", week: 26, quantity: 123 }, { id: 1, land: 'FINCA3', product: "ROSA", week: 26, quantity: 200 }, { id: 1, land: 'FINCA3', product: "ROSA", week: 24, quantity: 200 }, { id: 1, land: 'FINCA3', product: "ROSA", week: 23, quantity: 1200 }],
    weeks = {},
    weekKeys,
    groups = {},
    cols = ['Land', 'Product'],
    table = document.createElement('table'),
    tr, th, td;

data.forEach(function (a) {
    groups[a.land] = groups[a.land] || {};
    groups[a.land][a.product] = groups[a.land][a.product] || {};
    groups[a.land][a.product][a.week] = (groups[a.land][a.product][a.week] || 0) + a.quantity;
    groups[a.land][a.product].total = (groups[a.land][a.product].total || 0) + a.quantity;
    weeks[a.week] = (weeks[a.week] || 0) + a.quantity;
});

weekKeys = Object.keys(weeks).map(Number).sort(function (a,b) { return a - b; });
cols = cols.concat(weekKeys, 'Total');

tr = document.createElement('tr');
cols.forEach(function (a) {
    var th = document.createElement('th');
    th.innerText = a;
    tr.appendChild(th);
});
table.appendChild(tr);
Object.keys(groups).forEach(function (land) {
    Object.keys(groups[land]).forEach(function (product) {
        var tr = document.createElement('tr');
        cols.forEach(function (a, i) {
            var td = document.createElement('td');
            if (i > 1) { td.style.textAlign = 'right'; }
            td.innerText = [land, product][i] ||
                ((groups[land] || {})[product] || {})[(a).toString().toLowerCase()] ||
                0;            
            tr.appendChild(td);
        });
        table.appendChild(tr);
    });
});

weeks.total = 0;
tr = document.createElement('tr');
cols.forEach(function (a, i) {
    var td = document.createElement('td'),
        value = weeks[(a).toString().toLowerCase()] || 0;
    
    if (i > 1) { td.style.textAlign = 'right'; }
    td.innerText = ['Total', ' '][i] || value;
    tr.appendChild(td);
    if (a !== 'Total') { weeks.total += value; }
});
table.appendChild(tr);
document.body.appendChild(table);