biscript: 16
This data as json
| id | active | data | dateLastModified | description | name | note | typeId | fbVersion | fbName | published |
|---|---|---|---|---|---|---|---|---|---|---|
| 16 | _binary | const numOfProd = $NUM{Number_of_products|5}; const dateRange = $RANGE{Date_Range|this year}; const chartOption = "$LIST{Chart_Type|^bar,pie}"; const displayLegend = $CHECK{Display_pie_legend|true}; const displayMiscSale = $CHECK{Display_Misc_Sales_Items|false}; let miscSale = "AND soItem.typeId <> 11 " if (displayMiscSale == true) { miscSale = " "; } else { miscSale = "AND soItem.typeId <> 11 " } const data = JSON.parse(runQuery("SELECT soItem.productNum, SUM(postSoItem.totalPrice) AS totalPrice from postSoItem " + "JOIN soItem ON postSoItem.soItemId = soItem.id AND soItem.typeId <> 40 " + "WHERE statusId <> 40 AND dateLastFulfillment >= '" + dateRange.fromDate + "' AND dateLastFulfillment <= '" + dateRange.toDate + "' " + miscSale + "GROUP BY productNum " + "ORDER BY totalPrice " + "DESC LIMIT " + numOfProd)); const other = JSON.parse(runQuery("SELECT SUM(totalPrice) AS totalPrice " + "FROM (SELECT SUM(postSoItem.totalPrice) AS totalPrice " + "FROM postSoItem " + "JOIN soItem ON postSoItem.soItemId = soItem.id AND soItem.typeId <> 40 AND soItem.statusId = 50 " + "JOIN postSo ON postSo.soId = soItem.soId " + "WHERE postSo.postDate >= '" + dateRange.fromDate + "' AND postSo.postDate <= '" + dateRange.toDate + "' " + "GROUP BY productNum " + "ORDER BY totalPrice DESC " + "LIMIT " + numOfProd + ", 18446744073709551615) AS subQuery")); //This offset is MySQL Infitity. See https://dev.mysql.com/doc/refman/5.7/en/select.html#id4651990 let dateStr = UTIL.convertDate(dateRange.fromDate, true) + " - " + UTIL.convertDate(dateRange.toDate, false); if (moment(dateRange.fromDate).isSame('1000-01-01', 'year') && moment(dateRange.toDate).isSame('3000-01-01', 'year')) { dateStr = 'All'; } function createBar(totalPrice, tableColors){ return { backgroundColor: tableColors.halfAlpha, borderColor: tableColors.fullAlpha, borderWidth: 3, data: totalPrice, }; } function createPie(data, tableColors) { //Insert grey (other) into pie chart tableColors.halfAlpha.push(UTIL.convertHex('#757575', 50)); tableColors.fullAlpha.push(UTIL.convertHex('#757575', 100)); return { data: data, backgroundColor: tableColors.halfAlpha, borderColor: tableColors.fullAlpha, borderWidth: 2, }; } const barLabels = []; const pieLabels = []; const barTotalPrices = []; const pieTotalPrices = []; let barTotalPriceOverall = 0; let pieTotalPriceOverall = 0; const chartData = []; const pieData = []; for (let product = 0; product < data.length; product++) { barLabels.push(data[product].productnum); pieLabels.push(data[product].productnum); barTotalPrices.push(data[product].totalprice); pieTotalPrices.push(data[product].totalprice); barTotalPriceOverall += barTotalPrices[product]; } pieTotalPriceOverall = barTotalPriceOverall + other[0].totalprice; let tableColors = COLORS.get(numOfProd); chartData.push(createBar(barTotalPrices, tableColors)); pieTotalPrices.push(other[0].totalprice); pieData.push(createPie(pieTotalPrices, tableColors)); pieLabels.push('Other'); const barChartData = { labels: barLabels, datasets: chartData } const pieChartData = { labels: pieLabels, datasets: pieData } const barOptions = { title: { display: true, padding: 3 }, mode: 'index', position: 'cursor', intersect: false, legend: { display: false, }, scales: { xAxes: [{ stacked: true, ticks: { autoSkip: false } }], yAxes: [{ ticks: { beginAtZero: true, callback: function(value, index, values) { return UTIL.dollarify(value, 0); } } }] }, tooltips: { callbacks: { label: (tooltipItem, data) => { return UTIL.dollarify(tooltipItem.yLabel); }, title: (tooltipItem, data) => { return tooltipItem[0].xLabel; } }, }, responsive: true, maintainAspectRatio: false, plugins: { datalabels: { align: 'end', anchor: 'end', color: '#828891', font: { weight: 'bold', family: 'Helvetica' }, formatter: function(value, context) { return UTIL.dollarify(value); } } } }; const pieOptions = { plugins: { datalabels: { display: false } }, legend: { display: displayLegend }, responsive: true, maintainAspectRatio: false, pieceLabel: { render: 'label', fontColor: '#828891', fontSize: 14, fontStyle: 'bold', position: 'outside', render: function (args) { let label = (args.value/pieTotalPriceOverall * 100).toFixed(1) + '%'; return label; } }, tooltips: { backgroundColor: '#ffffff', titleFontColor: '#4e5156', bodyFontColor: '#4e5156', callbacks: { label: (tooltipItem, data) => { return (data.datasets[0].data[tooltipItem.index] / pieTotalPriceOverall * 100).toFixed(1) + '%'; }, title: (tooltipItem, data) => { return data.labels[tooltipItem[0].index]; } } } } if(chartOption === 'bar') { const ctxBar = document.getElementById('productChart'); new Chart(ctxBar, { type: 'bar', data: barChartData, options: barOptions }); } else { const ctxPie = document.getElementById('productChart'); new Chart(ctxPie, { type: 'pie', data: pieChartData, options: pieOptions }); } document.getElementById("title").innerHTML = 'Top ' + numOfProd + ' Products'; document.getElementById("subTitle").innerHTML = dateStr; document.getElementById("description").innerHTML = '<Description>'; | 2023-01-05 17:28:07.005000 | Query and logic for the Top Products page | Top Products | 20 | 4 | top-products | 0 |