Show Work Orders on Dashboard? - VortecCPI - 12-19-2018
I added this at the bottom of Dashboard.php. Note it is for 4.14.1 and includes column for WC Code.
PHP Code: $Sql = "SELECT pagesecurity FROM scripts WHERE scripts.script = 'SelectWorkOrder.php'"; $ErrMsg = _('The security for Work Orders cannot be retrieved because'); $DbgMsg = _('The SQL that was used and failed was'); $Security1Result = DB_query($Sql, $ErrMsg, $DbgMsg); $MyUserRow = DB_fetch_array($Security1Result); $WorkOrderSecurity = $MyUserRow['pagesecurity'];
if(in_array($WorkOrderSecurity, $_SESSION['AllowedPageSecurityTokens']) OR !isset($WorkOrderSecurity)) { echo '<br /> <h2>', _('Outstanding Work Orders'), '</h2> <table class="selection"> <tr> <th>' . _('WO'), '</th> <th>' . _('LOC'), '</th> <th>' . _('WC'), '</th> <th>' . _('Item'), '</th> <th>' . _('Qty Reqd'), '</th> <th>' . _('Qty Recd'), '</th> <th>' . _('Qty Left'), '</th> <th>' . _('Start Date') , '</th> <th>' . _('Reqd Date'), '</th> </tr>'; $Sql = "SELECT workorders.wo, woitems.stockid, stockmaster.description, stockmaster.decimalplaces, woitems.qtyreqd, woitems.qtyrecd, workorders.requiredby, workorders.startdate, workorders.loccode, IF((SELECT COUNT(DISTINCT workcentreadded) FROM bom WHERE parent = woitems.stockid) > 1, 'Mixed', (SELECT DISTINCT workcentreadded FROM bom WHERE parent = woitems.stockid)) AS wccode FROM workorders INNER JOIN woitems ON workorders.wo=woitems.wo INNER JOIN locationusers ON locationusers.loccode=workorders.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1 INNER JOIN stockmaster ON woitems.stockid=stockmaster.stockid WHERE workorders.closed=0 ORDER BY workorders.requiredby"; $ErrMsg = _('No Work Orders were returned by the SQL because'); $WorkOrdersResult = DB_query($Sql,$ErrMsg);
/*show a table of the work orders returned by the SQL */ if(DB_num_rows($WorkOrdersResult)>0) { $k = 0; //row colour counter while ($MyRow=DB_fetch_array($WorkOrdersResult)) { if(DateDiff(Date($_SESSION['DefaultDateFormat']), ConvertSQLDate($MyRow['requiredby']), 'd') > 0) { $FontColor = ' style="color:red; font-weight:bold"'; } else { $FontColor = ''; }
if($k == 1) { echo '<tr class="EvenTableRows">'; $k = 0; } else { echo '<tr class="OddTableRows">'; $k = 1; }
echo '<td><a href="' . $RootPath . '/WorkOrderEntry.php?WO=' . $MyRow['wo'] . '">' . $MyRow['wo'] . '</a></td> <td>' . $MyRow['loccode'] . '</td> <td>' . $MyRow['wccode'] . '</td> <td>' . $MyRow['stockid'] . ' - ' . $MyRow['description'] . '</td> <td class="number">' . locale_number_format($MyRow['qtyreqd'],$MyRow['decimalplaces']) . '</td> <td class="number">' . locale_number_format($MyRow['qtyrecd'],$MyRow['decimalplaces']) . '</td> <td class="number">' . locale_number_format($MyRow['qtyreqd']-$MyRow['qtyrecd'],$MyRow['decimalplaces']) . '</td> <td class="centre">' . ConvertSQLDate($MyRow['startdate']) . '</td> <td class="centre"'.$FontColor.'>' . ConvertSQLDate($MyRow['requiredby']) . '</td> </tr>'; }// END while($MyRow=DB_fetch_array($WorkOrdersResult)) } //rows > 0 echo '</table>'; } //WorkOrderSecurity
Also note this uses the old row coloring and " . " instead of ", " for concatenation.
And... No thead nor tbody due to old sorting...
When I get time I will tune it up for GIT version...
RE: Show Work Orders on Dashboard? - VortecCPI - 12-19-2018
I believe this is suitable for GIT version:
PHP Code: $Sql = "SELECT pagesecurity FROM scripts WHERE scripts.script = 'SelectWorkOrder.php'"; $ErrMsg = _('The security for Work Orders cannot be retrieved because'); $DbgMsg = _('The SQL that was used and failed was'); $Security1Result = DB_query($Sql, $ErrMsg, $DbgMsg); $MyUserRow = DB_fetch_array($Security1Result); $WorkOrderSecurity = $MyUserRow['pagesecurity'];
if(in_array($WorkOrderSecurity, $_SESSION['AllowedPageSecurityTokens']) OR !isset($WorkOrderSecurity)) { echo '<br /> <h2>', _('Outstanding Work Orders'), '</h2> <table class="selection"> <thead> <tr> <th>', _('WO'), '</th> <th>', _('Locn'), '</th> <th>', _('Item'), '</th> <th>', _('Qty Required'), '</th> <th>', _('Qty Received'), '</th> <th>', _('Qty Left'), '</th> <th>', _('Start Date') , '</th> <th>', _('Required Date'), '</th> </tr> </thead><tbody>'; $Sql = "SELECT workorders.wo, woitems.stockid, stockmaster.description, stockmaster.decimalplaces, woitems.qtyreqd, woitems.qtyrecd, workorders.requiredby, workorders.startdate, workorders.loccode FROM workorders INNER JOIN woitems ON workorders.wo=woitems.wo INNER JOIN locationusers ON locationusers.loccode=workorders.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1 INNER JOIN stockmaster ON woitems.stockid=stockmaster.stockid WHERE workorders.closed=0 ORDER BY workorders.requiredby, workorders.wo"; $ErrMsg = _('No Work Orders were returned by the SQL because'); $WorkOrdersResult = DB_query($Sql,$ErrMsg);
/*show a table of the work orders returned by the SQL */ if(DB_num_rows($WorkOrdersResult)>0) { while ($MyRow=DB_fetch_array($WorkOrdersResult)) { if(DateDiff(Date($_SESSION['DefaultDateFormat']), ConvertSQLDate($MyRow['requiredby']), 'd') > 0) { $FontColor = ' style="color:red; font-weight:bold"'; } else { $FontColor = ''; } echo '<tr class="striped_row"> <td><a href="', $RootPath, '/WorkOrderEntry.php?WO=', $MyRow['wo'], '">', $MyRow['wo'], '</a></td> <td>', $MyRow['loccode'], '</td> <td>', $MyRow['stockid'], ' - ', $MyRow['description'], '</td> <td class="number">', locale_number_format($MyRow['qtyreqd'],$MyRow['decimalplaces']), '</td> <td class="number">', locale_number_format($MyRow['qtyrecd'],$MyRow['decimalplaces']), '</td> <td class="number">', locale_number_format($MyRow['qtyreqd']-$MyRow['qtyrecd'],$MyRow['decimalplaces']), '</td> <td class="centre">', ConvertSQLDate($MyRow['startdate']), '</td> <td class="centre"', $FontColor, '>', ConvertSQLDate($MyRow['requiredby']), '</td> </tr>'; }// END while($MyRow=DB_fetch_array($WorkOrdersResult)) } //rows > 0 echo '</tbody></table>'; } //WorkOrderSecurity
Thank you Tim for inspiration from your excellent dashboard.
RE: Show Work Orders on Dashboard? - TimSchofield - 12-19-2018
I cannot take credit for the dashboard you refer to. It was sent to me by a young Indian programmer called Mythri Hegde from http://netelity.com
I helped with the design but the coding is all hers.
Tim
|