Charts js php implementation group by multiCurrency timestamp

Question

I have two tables named income and expenses, each of these tables also have a currency type.

Im doing a query to find out how much income and expenses have ocurred per month per currency.

The query is fine and it returns this:

 [
{"type":"income","monto":"1000","currency":"1","month":"6","year":"2016","idcurrency":"1","name":"Pesos argentinos","simbolo":"$"},
{"type":"expenditure","monto":"-500","currency":"1","month":"6","year":"2016","idcurrency":"1","name":"Pesos argentinos","simbolo":"$"},
{"type":"income","monto":"5000","currency":"2","month":"6","year":"2016","idcurrency":"2","name":"Dolares estadounidenses","simbolo":"u$d"},
{"type":"expenditure","monto":"-5000","currency":"2","month":"6","year":"2016","idcurrency":"2","name":"Dolares estadounidenses","simbolo":"u$d"},
{"type":"expenditure","monto":"-5000","currency":"3","month":"6","year":"2016","idcurrency":"3","name":"Pesos uruguayos","simbolo":"$"}
]   

I need to plot it using charts js, but the problem is that according to the documentation of charts js, i need to set the labels and the values for each of these labels. It may happend that on any month some currency didnt have any movement but another did, so the data will be showing wrong. How can i solve this?

Thanks

Edit: Im trying to use a bar graph, for the labels im actually using this to generate them:

        $desde = new DateTime(); 
        $desde->modify('first day of this month');
        $desde = $desde->sub(new DateInterval('P1Y'));

        $hasta = new DateTime();
        $hasta->modify('first day of next month');

        $interval = DateInterval::createFromDateString('1 month');
        $period   = new DatePeriod($desde, $interval, $hasta);


        $info = array();


        foreach ($period as $dt) {
            array_push($info,$dt->format("Y-m"));
        }

Patricks solution but i cannot get it to work:

// Bar chart
 var ctx = document.getElementById("graficoIngresosCostos"); 

let dataSource = 
<?=$dataSource?>;

//build the labels (you already did it in your OP. This is your info array
// that holds the first day of each month)
let labels = <?=$labels?>;

let currencies = <?=$monedas?>;
let datasets = [];

currencies.forEach(c => {
//filter out the incomes and expenditures for just the currency
// you're interested in
let incomeSource = dataSource.filter(
source=>source.nombre===c && source.concepto==='ingresos');
let expenseSource = dataSource.filter(
source=>source.nombre===c && source.concepto==='gastos');

let incomeDataset = { //dataset that holds this currency's income
label:c + ': ingresos',
data:[],
backgroundColor:'#03586A' //set color of bars
};
let expenseDataset = {//dataset that holds this currency's expense
label:c + ': gastos',
data:[],
backgroundColor:'#03586A' //set color of bars
};
//add datapoints to income and expense datasets for this currency
incomeSource.forEach(source=>{incomeDataset.data.push(source.monto)});
expenseSource.forEach(source=>{expenseDataset.data.push(source.monto)});

//todo: set backgroundColor for the bars of this dataset

//add the datasets to the chart
datasets.push(incomeDataset,expenseDataset);
});

//at this point you have all the datasets organized. Build chart
//ctx refers to the 2dContext of the canvas
let chart = new Chart(ctx,{
type:'bar',
data:{labels:labels, datasets:datasets}
});

Answer 1

You probably should have one dataset for each currency. All datasets share the same labels array, but each dataset has its own datapoints. So the basic strategy is, to loop through each of your currencies, build up all the datapoints for that currency into a dataset, and add it to the datasets array.

let dataSource = [
    {"type":"income","monto":"1000","currency":"1","month":"6","year":"2016","idcurrency":"1","name":"Pesos argentinos","simbolo":"$"},
    {"type":"expenditure","monto":"-500","currency":"1","month":"6","year":"2016","idcurrency":"1","name":"Pesos argentinos","simbolo":"$"},
    {"type":"income","monto":"5000","currency":"2","month":"6","year":"2016","idcurrency":"2","name":"Dolares estadounidenses","simbolo":"u$d"},
    {"type":"expenditure","monto":"-5000","currency":"2","month":"6","year":"2016","idcurrency":"2","name":"Dolares estadounidenses","simbolo":"u$d"},
    {"type":"expenditure","monto":"-5000","currency":"3","month":"6","year":"2016","idcurrency":"3","name":"Pesos uruguayos","simbolo":"$"}
];

//build the labels (you already did it in your OP. This is your info array
// that holds the first day of each month)
let labels = [/*info*/];

//if you want to build this dynamically, loop through dataSource and
//push the currency (be sure to check for duplicates) to the array
let currencies = ['Dolares estadounidenses','Pesos uruguayos','Pesos argentinos'];
let datasets = [];
currencies.forEach(c => {
    //filter out the incomes and expenditures for just the currency
    // you're interested in
    let incomeSource = dataSource.filter(
        source=>source.name===c && source.type==='income');
    let expenseSource = dataSource.filter(
        source=>source.name===c && source.type==='expenditure');

    let incomeDataset = { //dataset that holds this currency's income
        label:c + ': income',
        data:[],
        backgroundColor:'' //set color of bars
    };
    let expenseDataset = {//dataset that holds this currency's expense
        label:c + ': expenditures',
        data:[],
        backgroundColor:'' //set color of bars
    };
    //add datapoints to income and expense datasets for this currency
    incomeSource.forEach(source=>{incomeDataset.data.push(source.monto)});
    expenseSource.forEach(source=>{expenseDataset.data.push(source.monto)});

    //todo: set backgroundColor for the bars of this dataset

    //add the datasets to the chart
    datasets.push(incomeDataset,expenseDataset);
});

//at this point you have all the datasets organized. Build chart
//ctx refers to the 2dContext of the canvas
let chart = new Chart(ctx,{
    type:'bar',
    data:{labels:labels, datasets:datasets}
});

Your chart will be quite crowded to be honest, because if you have just 4 currencies, you will have 8 datasets. This means up to 8 bars possible at every x-axis value. You may want to consider having a couple of charts (one for income, another for expenditures).

You can find good samples of bar charts with multiple datasets here.

Addendum 1

You will have more work to do if some of your datasets have holes (months without data). Chart.js has no way to know where the holes are since it sees only an array of numbers. So before building your datasets, you will need to pad dataSource so that both income and expenditures for all currencies have the same number of data values as you have labels. The logic would be something like:

foreach(labels)
    //extract month and year that this label is responsible for
    month=...; year=...;

    foreach(currencies as c)

        //capture the data for the current month and currency
        currencyData = dataSource.filter(
            source => source.name===c && source.month===month 
                                      && source.year===year);

        //separate into income/expense data for this month      
        incomeDatapoints = currencyData.filter(
            source=>source.type==='income');
        exprenseDatapoints = dataSource.filter(
            source=>source.type==='expenditure');

        //if income data is missing, add a null datapoint the Chart.js
        //will keep data and labels in alignment
        if(!incomeDatapoints.length)
            dataSource.push({
                type:'income',
                monto: 0, //or maybe null
                currency:c,
                month:month,
                year:year
            });
        end if

        //repeat the if block above with expanseDatapoints

    endforeach(currencies)
end foreach(labels)

At this point, every dataset should have a value for every item in labels last thing to do is to sort dataSource by year, then by month to guarantee that when you add values to the chart they will be in the same order as the labels. I'll let you figure out how to order an array of object based on object properties but this is where I learned it myself.