I have the following output from the DB:
[
{
"id": 6,
"lineItem": "PC COST",
"lineItemAmount": 1000,
"fiscalYear": 2022,
"routing_Tool_Id": 1
},
{
"id": 10,
"lineItem": "PC COST",
"lineItemAmount": 100,
"fiscalYear": 2023,
"routing_Tool_Id": 1
},
{
"id": 11,
"lineItem": "Travel",
"lineItemAmount": 10,
"fiscalYear": 2024,
"routing_Tool_Id": 1
},
{
"id": 12,
"lineItem": "Travel",
"lineItemAmount": 100,
"fiscalYear": 2026,
"routing_Tool_Id": 1
}
]
However, it needs to be converted it in the following form -each FY has the value for the lineAmount
[
{
"LineItem": "PC COST",
"currentFy": 1000,
"currentFy01": 100,
"currentFy02": null,
"currentFy03": null,
"currentFy04": null,
"currentFy05": null
},
{
"LineItem": "Travel",
"currentFy": null,
"currentFy01": null,
"currentFy02": null,
"currentFy03": 10,
"currentFy04": null,
"currentFy05": 100
}
]
this is what I have so far- I have spent days looking for a clean way to do it.
const handleMatrix = (obj) => {
let rowData = [];
let index = [];
// iterate obj and return unique line items
function groupBy(objectArray, property) {
return objectArray.reduce(function (acc, obj) {
let key = obj[property]
if (!acc[key]) {
acc[key] = []
}
acc[key].push(obj)
return acc
}, {})
}
// assign unique line items to be used as main index for new obj
index = groupBy(obj, 'lineItem');
//console.log(index);
Object.keys(index).map((lineItem) => (
rowData.push({
"LineItem": lineItem,
"currentFy": null,
"currentFy01": null,
"currentFy02": null,
"currentFy03": null,
"currentFy04": null,
"currentFy05": null,
})
)
)
// will update with current FY - function will be here updating array
rowData[0]["currentFy"] = 2000
return rowData
}
For each item:
lineItemAmount can be assigned to the proper locationFinally, turn the object of objects into an array of objects.
const input=[{id:6,lineItem:"PC COST",lineItemAmount:1e3,fiscalYear:2022,routing_Tool_Id:1},{id:10,lineItem:"PC COST",lineItemAmount:100,fiscalYear:2023,routing_Tool_Id:1},{id:11,lineItem:"Travel",lineItemAmount:10,fiscalYear:2024,routing_Tool_Id:1},{id:12,lineItem:"Travel",lineItemAmount:100,fiscalYear:2026,routing_Tool_Id:1}];
const initialFy = 2022;
const amountsByType = {};
const initialObj = {
"currentFy": null,
"currentFy01": null,
"currentFy02": null,
"currentFy03": null,
"currentFy04": null,
"currentFy05": null,
};
for (const { lineItem, lineItemAmount, fiscalYear } of input) {
amountsByType[lineItem] ??= { LineItem: lineItem, ...initialObj };
const fyDiff = fiscalYear - initialFy;
if (fyDiff > 5 || fyDiff < 0) continue;
const prop = 'currentFy' + (fyDiff === 0 ? '' : '0' + fyDiff);
amountsByType[lineItem][prop] = lineItemAmount;
}
console.log(Object.values(amountsByType));