biscript: 10
This data as json
| id | active | data | dateLastModified | description | name | note | typeId | fbVersion | fbName | published |
|---|---|---|---|---|---|---|---|---|---|---|
| 10 | _binary | google.charts.load('current', { 'packages': ['geochart'], 'mapsApiKey': 'AIzaSyBZsbr4RQBre8LIOeEN9P-qlmiKGSi95aU' }); const dateRange = $RANGE{Date_Range|This year-to-date}; let defaultDrillDown = { region: "$TEXT{Default_Location|Global}" }; 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 VIEW = { world: "world", country: "country", province: "province" }; let currentView = VIEW.world; let previousCountry; google.charts.setOnLoadCallback(drawWorldMap); const btnBack = document.getElementById('button'); const subTitleHTML = document.getElementById('subTitle'); const titleHTML = document.getElementById('title'); const descriptionHTML = document.getElementById('description'); btnBack.addEventListener("click", btnBackEvent); let chart; let currentData = {}; let subTitle = 'Global'; let worldData; let countryData; let countryOptions = { legend: { textStyle: { fontSize: 16 }, numberFormat: '¤#,##0.00' }, resolution: "provinces", keepAspectRatio: true, width: '100%', height: '100%' }; function getWorldData() { let worldFbData = JSON.parse(runQuery("SELECT SUM(postSoItem.totalPrice) AS totalPrice, abbreviation FROM postSoItem " + "JOIN soItem ON postSoItem.soItemId = soItem.id " + "JOIN so ON so.id = soItem.soId AND soItem.statusID = 50 AND soItem.typeId <> 40 " + "JOIN CountryConst ON so.billToCountryId = CountryConst.id " + "JOIN postso ON postso.id = postsoitem.postsoid " + "WHERE postSoItem.totalPrice > 0 AND postSo.postDate >= '" + dateRange.fromDate + "' AND postSo.postDate <= '" + dateRange.toDate + "' " + "GROUP BY CountryConst.id")); let worldData = [ ['Country', 'Revenue', { type: 'string', role: 'tooltip' }] ]; for (let i = 0; i < worldFbData.length; i++) { worldData.push([worldFbData[i].abbreviation, worldFbData[i].totalprice, 'Revenue: ' + UTIL.dollarify(worldFbData[i].totalprice)]); } return worldData; } // Solid color fill for US, CA, and AU function getSolidProvincesData(country) { let USCAdata = JSON.parse(runQuery("SELECT SUM(postSoItem.totalPrice) AS totalPrice, code AS state, billToCity AS city FROM postSoItem " + "JOIN soItem ON postSoItem.soItemId = soItem.id " + "JOIN so ON so.Id = soItem.soId AND soItem.statusId = 50 AND soItem.typeId <> 40 " + "LEFT JOIN StateConst ON so.billToStateId = StateConst.id " + "JOIN CountryConst ON so.billToCountryId = CountryConst.id AND CountryConst.abbreviation = '" + country + "' " + "JOIN postso ON postso.soId = so.id " + "WHERE postSoItem.totalPrice > 0 AND postSo.postDate >= '" + dateRange.fromDate + "' AND postSo.postDate <= '" + dateRange.toDate + "' " + "GROUP BY state")); let tableData = [ ["State", "Revenue", { type: 'string', role: 'tooltip' }] ]; for (let i = 0; i < USCAdata.length; i++) { let region = country + "-" + USCAdata[i].state; tableData.push([region, USCAdata[i].totalprice, 'Revenue: ' + UTIL.dollarify(USCAdata[i].totalprice)]); } return tableData; } function getCountryData(country) { let countryFbData = JSON.parse(runQuery("SELECT SUM(postSoItem.totalPrice) AS totalPrice, code AS state, billToCity AS city FROM postSoItem " + "JOIN soItem ON postSoItem.soItemId = soItem.id " + "JOIN so ON so.Id = soItem.soId AND soItem.statusId = 50 AND soItem.typeId <> 40 " + "LEFT JOIN StateConst ON so.billToStateId = StateConst.id " + "JOIN CountryConst ON so.billToCountryId = CountryConst.id AND CountryConst.abbreviation = '" + country + "' " + "JOIN postso ON postso.soId = so.id " + "WHERE postSoItem.totalPrice > 0 AND postSo.postDate >= '" + dateRange.fromDate + "' AND postSo.postDate <= '" + dateRange.toDate + "' " + "GROUP BY city")); let tableData = [ ["City", "Revenue", { type: 'string', role: 'tooltip' }] ]; for (let i = 0; i < countryFbData.length; i++) { tableData.push([countryFbData[i].city, countryFbData[i].totalprice, 'Revenue: ' + UTIL.dollarify(countryFbData[i].totalprice)]); } return tableData; } function getProvinceData(country, province) { let provinceData = JSON.parse(runQuery("SELECT SUM(postSoItem.totalPrice) AS totalPrice, code AS state, billToCity AS city FROM postSoItem " + "JOIN soItem ON postSoItem.soItemId = soItem.id " + "JOIN so ON so.Id = soItem.soId AND soItem.statusId = 50 AND soItem.typeId <> 40 " + "LEFT JOIN StateConst ON so.billToStateId = StateConst.id " + "JOIN CountryConst ON so.billToCountryId = CountryConst.id AND CountryConst.abbreviation = '" + country + "' " + "JOIN postso ON postso.soId = so.id " + "WHERE postSoItem.totalPrice > 0 AND postSo.postDate >= '" + dateRange.fromDate + "' AND postSo.postDate <= '" + dateRange.toDate + "' " + "AND CODE = '" + province + "' " + "GROUP BY city")); let tableData = [ ["City", "Revenue", { type: 'string', role: 'tooltip' }] ]; for (let i = 0; i < provinceData.length; i++) { tableData.push([provinceData[i].city, provinceData[i].totalprice, 'Revenue: ' + UTIL.dollarify(provinceData[i].totalprice)]); } return tableData; } function drawWorldMap() { btnBack.style.visibility = 'hidden'; subTitle = 'Global'; currentView = VIEW.world; worldData = google.visualization.arrayToDataTable(getWorldData()); chart = new google.visualization.GeoChart(document.getElementById('regions_div')); google.visualization.events.addListener(chart, 'regionClick', drawCountryMap); const worldOptions = { legend: { textStyle: { fontSize: 16 }, numberFormat: '¤#,##0.00' }, displayMode: 'auto', keepAspectRatio: true, width: '100%', height: '100%' }; currentData.data = worldData; currentData.options = worldOptions; subTitleHTML.innerHTML = subTitle; chart.draw(currentData.data, currentData.options); if (defaultDrillDown && defaultDrillDown.region != "Global") { google.visualization.events.trigger(chart, 'regionClick', defaultDrillDown); if (defaultDrillDown.region.length > 2) { currentView = VIEW.province; previousCountry = { region: defaultDrillDown.region.substring(0, 2) }; } defaultDrillDown = null; } } function drawCountryMap(country) { if (currentView == VIEW.world) { previousCountry = country; } currentView = VIEW.country; btnBack.style.visibility = 'visible'; let countryStr = country.region.substring(0, 2); let hasProvinces = countryStr == 'US' || countryStr == 'CA' || countryStr == 'AU'; countryData = hasProvinces ? google.visualization.arrayToDataTable(getSolidProvincesData(countryStr)) : google.visualization.arrayToDataTable(getCountryData(countryStr)); subTitle = country.region; if (country.region.includes('-')) { currentView = VIEW.province; let provinceStr = country.region.substring(3); countryOptions.displayMode = 'markers'; countryOptions.region = country.region; if (countryStr == 'CA' || countryStr == 'AU') { countryOptions.region = countryStr; } let provinceData = google.visualization.arrayToDataTable(getProvinceData(countryStr, provinceStr)); currentData.data = provinceData; currentData.options = countryOptions; } else { countryOptions.region = countryStr; countryOptions.displayMode = hasProvinces ? 'auto' : 'markers'; currentData.data = countryData; currentData.options = countryOptions; } chart.draw(currentData.data, currentData.options); subTitleHTML.innerHTML = subTitle; } function btnBackEvent() { if (currentView == VIEW.province) { drawCountryMap(previousCountry); } else { drawWorldMap(); } } window.onresize = function (event) { chart.draw(currentData.data, currentData.options); } titleHTML.innerHTML = "Sales By Region"; subTitleHTML.innerHTML = subTitle; descriptionHTML.innerHTML = dateStr; | 2023-01-05 17:28:06.959000 | Query and logic for the Sales By Region page | Sales By Region | 20 | 3 | sales-by-region | 0 |