biscript: 2

This data as json

id active data dateLastModified description name note typeId fbVersion fbName published
2 _binary  const today = new Date(); var canvas = document.querySelector('canvas'); function createYearQuery(subtractYear) { let mm = (today.getMonth() + 1).toString().padStart(2, '0'); // January is 0! let timestamp = (today.getFullYear() - subtractYear) + '-' + mm + '-01 00:00:00'; return "SELECT DAY(PostSo.postDate) AS PostedDate, SUM(PostSoItem.totalPrice) AS TotalPrice " + "FROM PostSoItem " + "JOIN PostSo ON PostSo.id = PostSoItem.postSoId " + "JOIN SoItem ON PostSoItem.soItemId = SoItem.id AND SoItem.typeId <> 40 AND SoItem.statusID = 50 " + "WHERE postDate >= '" + timestamp + "' " + "AND postDate < DATE_ADD('" + timestamp + "', INTERVAL 1 MONTH) " + "GROUP BY CAST(PostSo.postDate AS DATE)"; } function createData(query) { const result = runQuery(query); return JSON.parse(result); } function createDataset(label, data, backgroundColor, borderColor) { return { label: label, fill: true, data: data, backgroundColor: backgroundColor, borderColor: borderColor, borderWidth: 2, pointRadius: 3, pointHoverRadius: 5, pointHitRadius: 7, lineTension: 0 }; } const data = []; data.push(createData(createYearQuery(1))); data.push(createData(createYearQuery(0))); console.log(data); let runningTotal = 0; const monthData = []; const labels = []; for (let year = 0; year < data.length; year++) { if(data[year].length != 0){ data[year] = data[year].reduce( (obj, day) => { obj[day.posteddate] = day.totalprice; return obj; }, {}); runningTotal = 0; let mData = []; let yearOffset = 1 if (year == 1) {yearOffset = 0} for (let day = 1; day <= new Date((today.getFullYear() - yearOffset), today.getMonth() + 1, 0).getDate(); day++) { if (labels.length < moment().daysInMonth()) { labels.push(day); } if (year === data.length - 1 && day > today.getDate()) { continue; } runningTotal += data[year][day] || 0; mData.push(runningTotal); } monthData.push(mData); } } const chartData = []; const currentYearColor = '$COLOR{Current_Year|#33D566}'; const previousYearColor = '$COLOR{Previous_Year|#4682b4}'; if (monthData.length == 1) { chartData.push(createDataset(today.getFullYear(), monthData[0], UTIL.convertHex(currentYearColor, 50), currentYearColor)); chartData.push(createDataset(today.getFullYear() - 1, monthData[1], UTIL.convertHex(previousYearColor, 50), previousYearColor)); } else { chartData.push(createDataset(today.getFullYear(), monthData[1], UTIL.convertHex(currentYearColor, 50), currentYearColor)); chartData.push(createDataset(today.getFullYear() - 1, monthData[0], UTIL.convertHex(previousYearColor, 50), previousYearColor)); } const options = { legend: { position: 'right' }, scales: { yAxes: [{ ticks: { beginAtZero: true, callback: function (value, values) { return UTIL.dollarify(value, 0); } } }], }, tooltips: { callbacks: { label: function (tooltipItem, data) { return UTIL.dollarify(tooltipItem.yLabel); }, title: (tooltipItem) => { return moment().format('MMMM') + ' ' + tooltipItem[0].xLabel; } } }, responsive: true, maintainAspectRatio: false, plugins: { datalabels: { display: false } } }; const ctx = document.getElementById('myChart'); new Chart(ctx, { type: 'line', data: { labels: labels, datasets: chartData }, options: options }); document.getElementById("title").innerHTML = 'Revenue ' + moment().format('MMMM') + ' ' + today.getFullYear(); document.getElementById("subTitle").innerHTML = 'MTD - ' + UTIL.dollarify(runningTotal); document.getElementById("description").innerHTML = '<Description>'; 2024-05-15 08:55:40.430000 Query and logic for the MTD Chart page MTD Chart   20 3 mtd-chart 0