Charts

Examples:

1. 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)},

);