I don´t have a small example at hand. But here is a full function that we use to prepare multiple charts on a form, after fetching and processing data:
// set defaults
if(app.get_variable({lsw_int_documents.reports.filter_year}) === null)
app.set_variable({lsw_int_documents.reports.filter_year}, new Date().getFullYear());
if(app.get_variable({lsw_int_documents.reports.filter_document_type}) === null)
app.set_variable({lsw_int_documents.reports.filter_document_type}, [
app.get_preset_record_id({lsw_int_documents.document_type.invoice}),
app.get_preset_record_id({lsw_int_documents.document_type.payroll})
]);
// prepare working values
const year = app.get_variable({lsw_int_documents.reports.filter_year});
const docTypeIds = app.get_variable({lsw_int_documents.reports.filter_document_type});
const showIncoming = app.get_variable({lsw_int_documents.reports.filter_show_incoming});
const showOutgoing = app.get_variable({lsw_int_documents.reports.filter_show_outgoing});
if(String(year).length !== 4)
return;
let optionsBar = {
legend:{
orient:'vertical',
left:'left',
type:'scroll'
},
series:[],
toolbox:{
feature: {
saveAsImage:{ show:true }
}
},
tooltip: {
trigger:'item'
},
xAxis:{
data:[],
type:'category'
},
yAxis: {
position:'left',
type:'value'
}
};
let optionsLine = JSON.parse(JSON.stringify(optionsBar));
const parse = function(rows) {
let docTypeMapData = {}; // map of docTypes, key = docTypeName, value = 12 values for all months of a year
let docTypeMapDataCum = {}; // map of docTypes, key = docTypeName, value = 12 values for all months of a year, cumulative
let docTypes = [];
// aggregate monthly data
for(const r of rows) {
if(!showIncoming && r.incoming) continue;
if(!showOutgoing && !r.incoming) continue;
r.docType = `${r.docType} ${r.incoming ? 'IN' : 'OUT'}`;
if(docTypeMapData[r.docType] === undefined) {
// new document type, fill empty values for all 12 months
for(let i = 0; i < 12; i++) {
docTypeMapData[r.docType] = [0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0];
docTypeMapDataCum[r.docType] = [0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0];
}
}
const date = new Date(r.docDate * 1000);
docTypeMapData[r.docType][date.getMonth()] += r.amountNet;
}
// calculate cumulative values
for(const docType in docTypeMapData) {
for(let i = 11; i >= 0; i--) {
for(let j = i; j >= 0; j--) {
docTypeMapDataCum[docType][i] += docTypeMapData[docType][j];
}
}
}
// set year+month as x axis data
for(let i = 0; i < 12; i++) {
optionsBar.xAxis.data.push(`${year}-${String((i+1)).padStart(2,'0')}`);
optionsLine.xAxis.data.push(`${year}-${String((i+1)).padStart(2,'0')}`);
}
// add 1 series per document type
for(const docType in docTypeMapData) {
optionsBar.series.push({
name:docType,
label:docType,
type:'bar',
data:docTypeMapData[docType]
});
optionsLine.series.push({
areaStyle:{},
name:docType,
label:docType,
type:'line',
data:docTypeMapDataCum[docType],
stack:'Total'
});
}
app.timer_set('chart_set', false, () => {
// bar chart
app.set_field_chart({F3::Chart}, optionsBar);
// cumulative line chart
app.set_field_chart({F5::Chart}, optionsLine);
}, 100);
};
const data = app.call_backend({lsw_int_documents.get_document_amounts_by_year}, year, docTypeIds).then(
res => parse(res !== null ? JSON.parse(res) : []),
console.warn
);
The function is executed on form load. It prepares 2 option objects for 2 chart fields from the same data set.
This is the corresponding backend function that is being called:
$BODY$
DECLARE
BEGIN
RETURN (
SELECT JSON_AGG(
JSON_BUILD_OBJECT(
'amountNet', (lsw_int_documents.document.amount_total_net),
'docDate', d.(lsw_int_documents.document.date_document),
'docType', dt.(lsw_int_documents.document_type.name),
'docTypeId', d.(lsw_int_documents.document.type),
'incoming', (
SELECT EXISTS(
SELECT (lsw_organizations.relation.id)
FROM {lsw_organizations}.[relation]
WHERE (lsw_organizations.relation.is_internal)
AND (lsw_organizations.relation.id) IN (
SELECT (lsw_organizations.organization_relation.relation)
FROM {lsw_organizations}.[organization_relation]
WHERE (lsw_organizations.organization_relation.organization) = (
SELECT (lsw_organizations.location.organization)
FROM {lsw_organizations}.[location]
WHERE (lsw_organizations.location.id) = (
SELECT (lsw_int_documents.customer.company_location)
FROM {lsw_int_documents}.[customer]
WHERE (lsw_int_documents.customer.id) = d.(lsw_int_documents.document.recipient)
)
)
)
LIMIT 1
)
)
)
)
FROM {lsw_int_documents}.[document] AS d
JOIN {lsw_int_documents}.[document_type] AS dt ON dt.(lsw_int_documents.document_type.id) = d.(lsw_int_documents.document.type)
WHERE EXTRACT(YEAR FROM TO_TIMESTAMP((lsw_int_documents.document.date_document))) = _year
AND (lsw_int_documents.document.amount_total_net) IS NOT NULL
AND d.(lsw_int_documents.document.type) = ANY(_document_type_ids)
AND d.(lsw_int_documents.document.cancelled) = FALSE
);
END;
$BODY$
Ultimately, as long as you can fetch the data and process it in such a way that fulfills the echarts options definition, you can do whatever you want.
