biscript: 30
This data as json
| id | active | data | dateLastModified | description | name | note | typeId | fbVersion | fbName | published |
|---|---|---|---|---|---|---|---|---|---|---|
| 30 | _binary | let partNum = '$PART{Part|%|General}'; partNum == -1 ? (partNum = '%') : true; let locationGroupId = '$LG{Location_Group|%|General}'; locationGroupId == -1 ? (locationGroupId = '%') : true; let user = JSON.parse(getUser()); let currentUser = user.id; let dataset; function monitor() { function getData() { let query = `SELECT COALESCE((SELECT (sysvalue) * 1000 FROM sysproperties WHERE syskey = 'Refresh Rate'),300000) as refresh, watch.id as watchid, part.num as partnum, part.description as partdescription, COALESCE(locationgroup.name,'Company Wide') as locationgroup, coalesce(uom.code,'') as uom, @avail:= coalesce(IF(coalesce(qtyonhand.qty,0) - coalesce(qtyallocated.qty,0) - coalesce(qtynotavailable.qty,0) + coalesce(qtydropship.qty,0) < 0,0,coalesce(qtyonhand.qty,0) - coalesce(qtyallocated.qty,0) - coalesce(qtynotavailable.qty,0) + coalesce(qtydropship.qty,0))) as avail, @onhand:=(CASE WHEN watch.locationgroupid IS NOT NULL THEN coalesce(qtyonhand.qty,0) ELSE coalesce(totals.qtyonhand, 0) END) as onhand, @available:=(CASE WHEN watch.locationgroupid IS NOT NULL THEN coalesce(@avail,0) ELSE coalesce(totals.qtyavailable, 0) END) as available, @committed:=(CASE WHEN watch.locationgroupid IS NOT NULL THEN coalesce(qtycommitted.qty,0) ELSE coalesce((SELECT sum(qty) FROM qtycommitted qtyc WHERE qtyc.partid = part.id GROUP BY partid),0) END) as committed, @allocated:=(CASE WHEN watch.locationgroupid IS NOT NULL THEN coalesce(qtyallocated.qty,0) ELSE coalesce(totals.qtyallocated, 0) END) as allocated, @notavailable:=(CASE WHEN watch.locationgroupid IS NOT NULL THEN coalesce(qtynotavailable.qty,0) ELSE coalesce(totals.qtynotavailable, 0) END) as 'not available', @onorder:=(CASE WHEN watch.locationgroupid IS NOT NULL THEN coalesce(qtyonorder.qty,0) ELSE coalesce(totals.qtyonorder, 0) END) as 'on order', (CASE WHEN @onhand > watch.alertlevel THEN 'fb10' WHEN @onhand <= watch.alertlevel && @onhand > watch.criticallevel THEN 'fb20' WHEN @onhand < watch.alertlevel && @onhand <= watch.criticallevel THEN 'fb30' END) as state, (CASE WHEN @onhand > watch.alertlevel THEN '1-Good' WHEN @onhand <= watch.alertlevel && @onhand > watch.criticallevel THEN '2-Alert' WHEN @onhand < watch.alertlevel && @onhand <= watch.criticallevel THEN '3-Critical' END) as statetext FROM watch INNER JOIN part ON watch.itemid = part.id INNER JOIN uom ON part.uomid = uom.id LEFT OUTER JOIN locationgroup ON watch.locationgroupid = locationgroup.id left outer join qtyonhand on (part.id = qtyonhand.partid and locationgroup.id = qtyonhand.locationgroupid) left outer join qtycommitted on (part.id = qtycommitted.partid and locationgroup.id = qtycommitted.locationgroupid) left outer join qtyallocated on (part.id = qtyallocated.partid and locationgroup.id = qtyallocated.locationgroupid) left outer join qtyonorder on (part.id = qtyonorder.partid and locationgroup.id = qtyonorder.locationgroupid) left outer join qtynotavailable on (part.id = qtynotavailable.partid and locationgroup.id = qtynotavailable.locationgroupid) left outer join qtydropship on (part.id = qtydropship.partid and locationgroup.id = qtydropship.locationgroupid) left outer join (select coalesce(sum(qti.qtyonhand),0) as qtyonhand, coalesce(sum(qti.qtyallocated),0) as qtyallocated, coalesce(sum(qti.qtyonorder),0) as qtyonorder, if(coalesce((sum(qti.qtyonhand) - sum(qti.qtyallocated) - sum(qti.qtynotavailable) + sum(qti.qtydropship)),0) < 0,0,coalesce((sum(qti.qtyonhand) - sum(qti.qtyallocated) - sum(qti.qtynotavailable) + sum(qti.qtydropship)),0)) as qtyavailable, coalesce(sum(qti.qtynotavailable),0) as qtynotavailable, coalesce(sum(qti.qtydropship),0) as qtydropship, qti.partid from qtyinventorytotals qti group by qti.partid) as totals on part.id = totals.partid WHERE part.id LIKE '${partNum}' AND COALESCE(locationgroup.id,'') LIKE '${locationGroupId}' AND watch.sysuserid LIKE '${currentUser}'`; console.log(query); let dataset = JSON.parse(runQuery(query)); console.log(dataset); return dataset; } function drawTable() { dataset = getData(); let table = $('#report-table'); table.empty(); table.DataTable({ dom: 'B<"clear">lfrtip', data: dataset, columns: [ { title: 'State', data: 'statetext', defaultContent: '', className: 'stateText', createdCell: function(td, cellData, rowData, row, col) { let statecircle = document.createElement('div'); console.log(statecircle); statecircle.setAttribute('watchid', rowData.watchid); td.append(statecircle); statecircle.classList.add(rowData.state); statecircle.classList.add('dot'); } }, { title: 'Part Number', data: 'partnum' }, { title: 'Part Description', data: 'partdescription' }, { title: 'Location Group', data: 'locationgroup' }, { title: 'UOM', data: 'uom' }, { title: 'On Hand', data: 'onhand' }, { title: 'Available', data: 'available' }, { title: 'Committed', data: 'committed', defaultContent: '0' }, { title: 'Allocated', data: 'allocated' }, { title: 'Not Available', data: 'not available' }, { title: 'On Order', data: 'on order' } ], fixedHeader: true, colReorder: false, pagingType: 'simple', responsive: true, deferRender: true, buttons: ['csv'], language: { paginate: { previous: 'PREV', next: 'NEXT' } } }); } function refreshTable() { console.time('refreshRate'); let table = $('#report-table').DataTable(); dataset = getData(); table.clear(); table.rows.add(dataset); table.draw(false); console.timeEnd('refreshRate'); setTimeout(refreshTable, dataset[0].refresh); } drawTable(); $(".dt-buttons").addClass( "csv-button-margin" ); console.log(dataset[0].refresh); setTimeout(refreshTable, dataset[0].refresh); } $(document).ready(function() { monitor(); }); | 2023-01-05 17:28:07.068000 | Replicates the functionality of the Monitor Module, but adds the option to display it on your dashboard. Watches must be created in the Monitor Module. | Inventory Watch List | 20 | 2 | inventory-watch-list | 0 |