biscript: 14
This data as json
| id | active | data | dateLastModified | description | name | note | typeId | fbVersion | fbName | published |
|---|---|---|---|---|---|---|---|---|---|---|
| 14 | _binary | const dateRange = $RANGE{Date_Range|this month}; const currentSalesColor = '$COLOR{Current_Sales_Color|#33D566}'; let goal = "$MONEY{Goal}"; goal = Number(goal.replace(/[^0-9\.-]+/g,"")); let rep = "$USER{Rep|%}"; if (rep != "%"){ rep = parseInt(rep); } if (rep == -1) { rep = "%"; } const data = JSON.parse(runQuery("SELECT sysuser.firstName, sysuser.lastName, 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 " + "JOIN so ON so.id = soitem.soId " + "JOIN sysuser ON so.salesmanId = sysuser.id " + "WHERE postDate >= '" + dateRange.fromDate + "' " + "AND postDate <= '" + dateRange.toDate + "' " + "AND salesmanId LIKE '" + rep + "' " + "GROUP BY CAST(PostSo.postDate AS DATE), sysuser.firstName, sysuser.lastName")); const doughnutLabels = ["Current Sales","Remaining Goal"]; const doughnutDatasets = []; const allSales = [] let totalSales = 0; for (let i = 0; i < data.length; i++) { totalSales += data[i].totalprice; } const goalSales = totalSales; let remainingGoal = goal - goalSales; remainingGoal = remainingGoal < 0 ? 0 : remainingGoal; allSales.push(goalSales); allSales.push(remainingGoal); doughnutDatasets.push(createDoughnut(allSales, [UTIL.convertHex(currentSalesColor,100),'rgba(0,0,0,0)'],[UTIL.convertHex(currentSalesColor,50),UTIL.convertHex(currentSalesColor,50)])); 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'; } const doughnutData = { labels: doughnutLabels, datasets: doughnutDatasets }; const doughnutOptions = { tooltips: { callbacks: { label: function (tooltipItem, data) { return UTIL.dollarify(data.datasets[0].data[tooltipItem.index]); }, title: (tooltipItem, data) => { return data.labels[tooltipItem[0].index]; } } }, plugins: { datalabels: { color: '#828891', display: false, font: { weight: 'bold', family: 'Roboto' }, formatter: function(value, context) { return UTIL.dollarify(value); } } }, responsive: true, maintainAspectRatio: false, pieceLabel: { render: 'label', fontColor: '#828891', fontSize: 14, fontStyle: 'bold', position: 'outside', render: function (args) { let label = (args.value/goal * 100).toFixed(1) + '%'; return label; } }, legend: { position: 'bottom', display: false }, layout: { padding: { right: 50, left: 50 } }, circumference: Math.PI, rotation: Math.PI, }; // DOM Population ================================================================================= const ctxBar = document.getElementById('topSalesman'); new Chart(ctxBar, { type: 'doughnut', data: doughnutData, options: doughnutOptions }); const titleHtml = document.getElementById("title"); if (rep == "%") { titleHtml.innerHTML = "Sales Goal"; } else if (data[0]) { titleHtml.innerHTML = "Sales Goal - " + data[0].firstname + ' ' + data[0].lastname; } else { const repName = JSON.parse(runQuery("SELECT firstName, lastName FROM SysUser WHERE id='" + rep + "'")); titleHtml.innerHTML = "Sales Goal - " + repName[0].firstname + ' ' + repName[0].lastname; } document.getElementById("subTitle").innerHTML = UTIL.dollarify(totalSales, 0) + " of " + UTIL.dollarify(goal, 0) + " Goal"; document.getElementById("description").innerHTML = dateStr; // Function Declarations ========================================================================== function createDoughnut(data, colors, borderColor){ return { data: data, backgroundColor: colors, borderColor: borderColor, borderWidth: 2 }; } | 2023-01-05 17:28:06.990000 | Query and logic for the Sales Goal page | Sales Goal | 20 | 3 | sales-goal | 0 |