Fabian Sierra Fabian Sierra - 4 months ago 8
Javascript Question

Display values ​in a table Javascript

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, appreciate your help

Answer

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);