biscript: 12
This data as json
| id | active | data | dateLastModified | description | name | note | typeId | fbVersion | fbName | published |
|---|---|---|---|---|---|---|---|---|---|---|
| 12 | _binary | const dateRange = $RANGE{Date_Range|this month}; const includeReturns = $CHECK{IncludeReturns|false}; const includeDiscounts = $CHECK{IncludeDiscounts|false}; const query = `SELECT so.salesman, SUM(postSoItem.totalPrice) AS totalPrice FROM postSoItem JOIN soItem ON postSoItem.soItemId = soitem.id AND soItem.typeId <> 40 AND soItem.qtyfulfilled > 0 JOIN so ON soItem.soId = so.id JOIN postSo ON postSo.Id = postsoitem.postsoid WHERE postSo.postDate between '${dateRange.fromDate}' AND '${dateRange.toDate}' AND IF(${includeReturns} = 1,1=1,soitem.typeid NOT IN (20,21)) AND IF(${includeDiscounts} = 1,1=1,soitem.typeid NOT IN (30,31)) GROUP BY so.salesmanId ORDER BY totalPrice DESC`; const data = JSON.parse(runQuery(query)); console.log(query); console.log(data); 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, }; } const barLabels = []; const barTotalPrices = []; let barTotalPriceOverall = 0; const chartData = []; for (let salesman = 0; salesman < data.length; salesman++) { barLabels.push(data[salesman].salesman); barTotalPrices.push(data[salesman].totalprice); barTotalPriceOverall += barTotalPrices[salesman]; } let tableColors = COLORS.get(data.length); chartData.push(createBar(barTotalPrices, tableColors)); const barChartData = { labels: barLabels, datasets: chartData } const barOptions = { title: { display: true, padding: 3 }, mode: 'index', position: 'cursor', intersect: false, legend: { display: false, }, scales: { xAxes: [{ 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 ctxBar = document.getElementById('topSalesman'); new Chart(ctxBar, { type: 'bar', data: barChartData, options: barOptions }); document.getElementById("title").innerHTML = 'Sales By Rep'; document.getElementById("subTitle").innerHTML = dateStr; document.getElementById("description").innerHTML = '<Description>'; | 2023-01-05 17:28:06.974000 | Query and logic for the Sales By Rep page | Sales By Rep | 20 | 5 | sales-by-rep | 0 |