Charts
Examples:
- Northwind Orders table
SQL
select EXTRACT(month from TO_DATE("OrderDate",'MM')) AS "MON", TO_CHAR(TO_DATE("OrderDate",'MM/DD/YYYY'), 'Month') AS "Month", SUM(1)
from orders
group by EXTRACT(month from TO_DATE("OrderDate",'MM')) , TO_CHAR(TO_DATE("OrderDate",'MM/DD/YYYY'), 'Month')
order by EXTRACT(month from TO_DATE("OrderDate",'MM'))
this.draw('<style>canvas {border: 1px dotted red;} .chart-container {position: relative;margin: auto; height:40vh;width:40vw;}</style><div class="chart-container"><canvas id="chart"></canvas></div>');
//LUIS this is actually how
//var _sampledata=this.querydb("Northwind","Orders","select EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) AS \"MON\", TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') AS \"Month\", SUM(1) from orders group by EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) , TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') order by EXTRACT(month from TO_DATE(\"OrderDate\",'MM'))")
//hard coded sample data
var _sampledata={"returnValue":[{"MON":1.0,"Month":"January ","sum":88},{"MON":2.0,"Month":"February ","sum":83},{"MON":3.0,"Month":"March ","sum":103},{"MON":4.0,"Month":"April ","sum":105},{"MON":5.0,"Month":"May ","sum":46},{"MON":6.0,"Month":"June ","sum":30},{"MON":7.0,"Month":"July ","sum":55},{"MON":8.0,"Month":"August ","sum":58},{"MON":9.0,"Month":"September","sum":60},{"MON":10.0,"Month":"October ","sum":64}],"total":12,"message":null,"requestUrl":"https://apidev.crmtogether.com/158f70ae-e272-437b-822f-87797f2dd37e/islands/query?page=1","region":"northwind","regionId":"158f70ae-e272-437b-822f-87797f2dd37e","userName":"crmtogether","page":1,"query":"select EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) AS \"MON\", TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') AS \"Month\", SUM(1)\nfrom orders\ngroup by EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) , TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') \norder by EXTRACT(month from TO_DATE(\"OrderDate\",'MM'))","island":"TO_DATE","islandDescription":"","createDate":"","updatedDate":""}
var _labels=[];
var _data=[];
for(var x=0;x<_sampledata.returnValue.length;x++)
{
_labels.push(_sampledata.returnValue[x]["Month"]);
_data.push(_sampledata.returnValue[x]["sum"]);
}
var data = {
labels: _labels,
datasets: [{
label: "Order Summary",
backgroundColor: "rgba(255,99,132,0.2)",
borderColor: "rgba(255,99,132,1)",
borderWidth: 2,
hoverBackgroundColor: "rgba(255,99,132,0.4)",
hoverBorderColor: "rgba(255,99,132,1)",
data: _data,
}]
};
var options = {
maintainAspectRatio: false,
scales: {
y: {
stacked: true,
grid: {
display: true,
color: "rgba(255,99,132,0.2)"
}
},
x: {
grid: {
display: false
}
}
}
};
setTimeout(() => {
new Chart('chart', {
type: 'bar',
options: options,
data: data
});
}, 500);
/////////////////////////
example 2
this.draw('<div class="chart-container"><canvas id="chart3"></canvas></div>');
setTimeout(() => {
new Chart('chart3', {
type: "line",
data: {
labels: ["Red", "Blue", "Yellow", "Green", "Purple", "Orange"],
datasets: [
{
label: "# of Votes",
data: [12, 19, 3, 5, 2, 3]
}
]
},
options: {
scales: {
y: {
ticks: {
beginAtZero: true
}
}
}
}
});
}, 500);
---
querydb - we need to use a promise object to call this
Example:
var _sampledatax=this.querydb("northwind","Orders","select EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) AS \"MON\", TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') AS \"Month\", SUM(1) from orders group by EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) , TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') order by EXTRACT(month from TO_DATE(\"OrderDate\",'MM'))")
let myPromise = new Promise(function(myResolve, myReject) {
myResolve(_sampledatax); // when successful
myReject(); // when error
});
myPromise.then(
function(value) { console.log("result-", value)},
);
