This example is adapted from an example I created for a presentation a couple months ago. While the example as displayed is specific to displaying ERP data, the example may be easily adapted for use in monitoring Oracle database performance.
—
Vista and Windows 7 offer a set of built in gadgets that perform a variety of tasks, such as displaying calendars, clocks, resource meters, stock quotes, and so on. It might be nice to show a quick overview of various statistics in an ERP system (or in the database itself) so that a determination may be made whether or not a potential problem exists. Such a gadget would need to be able to automatically update its statistics. This example is complicated as it combines a large number of technologies each with their own unique syntax which looks vaguely like English. This example uses VBScript, Oracle database access, cascading style sheets, XML, HTML, DHTML, and coding that is specific to Vista/Windows 7 gadgets.
When the gadget is opened, it determines the statistics that should be displayed for the user, and the order in which the statistics should be displayed. The selected statistics are then retrieved from the database and displayed on the gadget surface in a DIV tag. Every ten minutes the gadget reconnects to the database and displays updated statistics. As the mouse pointer is moved over the gadget, the gadget’s background lights up slightly, and as the mouse pointer passes over the statistics, those also light up (more specifically, the background image for the statistic is changed). When one of the statistics is clicked, a fly-out child window appears on the screen that shows the detailed statistics behind the one line numeric statistic.
The gadget code files should be placed into the C:\Program Files\Windows Sidebar\Gadgets\KMInspector.gadget folder on the computer (note that KMInspector may be specified with a different name). The gadget.xml file in that folder describes the gadget to Vista/Windows 7 and tells Windows where to find the main gadget HTML file, the name of the gadget, and the name of the various icons – this file MUST be saved in a UTF-8 characterset (this is an option when saving with Notepad). The KMInspector.html file is the main gadget page, and the various pages with names beginning with FlyOut are the pages containing the detail information. The security requirement that all Windows gadgets must be signed needs to be disabled.
Below are a couple of screen shots of what we are trying to achieve (a standard Windows sidebar calendar gadget appears above, with the custom developed gadget below):
The top left screen shot shows the custom gadget directly below a built-in Windows calendar gadget. The top right screen shot shows what happens when the mouse pointer passes over the gadget (note that the background becomes lighter in color, and the button under the mouse pointer changes color). The bottom picture shows what happens when one of the buttons in the gedget is clicked.
gadget.xml file (save as in the UTF-8 character set):
<?xml version="1.0" encoding="utf-8" ?> <gadget> <name>KM Inspector</name> <version>1.0.0.0</version> <icons> <icon height="48" width="36" src="InspectorIcon.png" /> </icons> <hosts> <host name="sidebar"> <base type="HTML" apiVersion="1.0.0" src="KMInspector.html" /> <permissions>Full</permissions> <platform minPlatformVersion="1.0" /> <defaultImage src="Inspector.png" /> </host> </hosts> </gadget>
Inspector.png is the picture that is displayed when the gadget is dragged from the list of available Windows gadgets to the sidebar. InspectorIcon.png is the picture of the gadget that is displayed in the list of available Windows gadgets. KMInspector.html is the web page that contains the code for the gadget.
Inspector.png:
InspectorIcon.png:
KMInspector.html file:
<html> <head> <title>Visual Inspector</title> <style type="text/css"> body { margin: 0px; width: 140px; height: 200px; } #gadgetContent { margin-top: 0px; width: 140px; vertical-align: middle; text-align: center; overflow: hidden; } .Item { background-position: center center; padding: 0px; font-family: Arial; font-size: 8pt; color: #FFFFFF; border-style: none; clip: width: 130px; height: 14px; position: absolute; left: 10px; width: 128px; background-image: url('InspectorButtonUp.png'); background-repeat: no-repeat; } </style> </head> <script language="VBScript"> Option Explicit Dim intMinuteCount 'Number of minutes that have elapsed since the last refresh Dim intRefreshMinutes 'Frequency of the data refresh, in minutes Dim strInpector(10) 'Keeps track of which data is displayed in each Inspector position Dim userEntry Sub Window_Onload Dim lngTimerID 'Specify the Inspector items to be output, in the order that they should appear 'Currently Available options ' "Negative On Hand" ' "Past Due PO Line Count" ' "In Use Shop Resources" ' "Employees Clocked In" ' "Employees Clocked Into Indirect" ' "Non Invoiced Shipper" strInpector(1) = "Negative On Hand" strInpector(2) = "Past Due PO Line Count" strInpector(3) = "In Use Shop Resources" strInpector(4) = "Employees Clocked In" strInpector(5) = "Employees Clocked Into Indirect" strInpector(6) = "Non Invoiced Shipper" strInpector(7) = "" strInpector(8) = "" strInpector(9) = "" strInpector(10) = "" 'Hide those Inspector items which are not specified (set = "") If strInpector(1) = "" Then divInspector1.style.visibility = "hidden" End If If strInpector(2) = "" Then divInspector2.style.visibility = "hidden" End If If strInpector(3) = "" Then divInspector3.style.visibility = "hidden" End If If strInpector(4) = "" Then divInspector4.style.visibility = "hidden" End If If strInpector(5) = "" Then divInspector5.style.visibility = "hidden" End If If strInpector(6) = "" Then divInspector6.style.visibility = "hidden" End If If strInpector(7) = "" Then divInspector7.style.visibility = "hidden" End If If strInpector(8) = "" Then divInspector8.style.visibility = "hidden" End If If strInpector(9) = "" Then divInspector9.style.visibility = "hidden" End If If strInpector(10) = "" Then divInspector10.style.visibility = "hidden" End If 'System.Gadget.onSettingsClosing = "SaveSettings" 'System.Gadget.onSettingsClosed = "SaveSettingsClosed" System.Gadget.settingsUI = "settings.html" 'Set the refresh frequency in minutes intRefreshMinutes = 10 'Force a refresh on the first execution intMinuteCount = intRefreshMinutes 'Refresh the Inspector items RefreshInspector 'Set a times that fires every 60 seconds that determines if it is time to refresh the data lngTimerID = window.SetInterval("RefreshInspector", 60000) End Sub Sub RefreshInspector Dim i Dim lngCount Dim dbMyConnection Dim comData Dim snpData Dim strUsername Dim strPassword Dim strDatabase Dim strSQL Dim strOut On Error Resume Next 'See if the specified number of minutes have elapsed since the last refresh, if not, exit the subroutine intMinuteCount = intMinuteCount + 1 If intMinuteCount < intRefreshMinutes Then Exit Sub End If intMinuteCount = 0 Set snpData = CreateObject("ADODB.Recordset") Set dbMyConnection = CreateObject("ADODB.Connection") strUsername = "MyUser" 'Note that Switch is a function that implments a simple light-weight reversible encryption so that the password does not appear in clear text strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25)) strDatabase = "MyDB" dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";" dbMyConnection.Open If dbMyConnection.State = 1 Then For i = 1 to 10 strOut = "" Select Case strInpector(i) Case "Negative On Hand" strSQL = "SELECT" & VBCrLf strSQL = strSQL & " COUNT(*) AS NUM" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " PART" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " QTY_ON_HAND<0" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then If Not(snpData.EOF) Then strOut = "Negative On Hand: " & cStr(snpData("num")) End If snpData.Close End If Case "Past Due PO Line Count" strSQL = "SELECT" & VBCrLf strSQL = strSQL & " COUNT(*) AS NUM" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " PURCHASE_ORDER PO," & VBCrLf strSQL = strSQL & " PURC_ORDER_LINE POL," & VBCrLf strSQL = strSQL & " PURC_LINE_DEL PLD" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " PO.STATUS IN ('F','R','U')" & VBCrLf strSQL = strSQL & " AND PO.ID=POL.PURC_ORDER_ID" & VBCrLf strSQL = strSQL & " AND POL.LINE_STATUS='A'" & VBCrLf strSQL = strSQL & " AND POL.SERVICE_ID IS NULL" & VBCrLf strSQL = strSQL & " AND POL.ORDER_QTY>POL.TOTAL_RECEIVED_QTY" & VBCrLf strSQL = strSQL & " AND POL.PURC_ORDER_ID=PLD.PURC_ORDER_ID(+)" & VBCrLf strSQL = strSQL & " AND POL.LINE_NO=PLD.PURC_ORDER_LINE_NO(+)" & VBCrLf strSQL = strSQL & " AND DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) > " & VBCrLf strSQL = strSQL & " DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0))" & VBCrLf strSQL = strSQL & " AND COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) < SYSDATE" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then If Not(snpData.EOF) Then strOut = "PO Past Due: " & cStr(snpData("num")) End If snpData.Close End If Case "In Use Shop Resources" strSQL = "SELECT" & VBCrLf strSQL = strSQL & " COUNT(DISTINCT RESOURCE_ID) AS NUM" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " LABOR_TICKET" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf strSQL = strSQL & " AND HOURS_WORKED IS NULL" & VBCrLf strSQL = strSQL & " AND RESOURCE_ID IS NOT NULL" & VBCrLf strSQL = strSQL & "ORDER BY" & VBCrLf strSQL = strSQL & " RESOURCE_ID" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then If Not(snpData.EOF) Then strOut = "Resources in Use: " & cStr(snpData("num")) End If snpData.Close End If Case "Employees Clocked In" strSQL = "SELECT" & VBCrLf strSQL = strSQL & " COUNT(DISTINCT EMPLOYEE_ID) AS NUM" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " LABOR_TICKET" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf strSQL = strSQL & " AND HOURS_WORKED IS NULL" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then If Not(snpData.EOF) Then strOut = "Emp Clocked In: " & cStr(snpData("num")) End If snpData.Close End If Case "Employees Clocked Into Indirect" strSQL = "SELECT" & VBCrLf strSQL = strSQL & " COUNT(DISTINCT EMPLOYEE_ID) AS NUM" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " LABOR_TICKET" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf strSQL = strSQL & " AND HOURS_WORKED IS NULL" & VBCrLf strSQL = strSQL & " AND RESOURCE_ID IS NULL" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then If Not(snpData.EOF) Then strOut = "Emp on Indirect: " & cStr(snpData("num")) End If snpData.Close End If Case "Non Invoiced Shipper" STRSQL = "SELECT" & VBCrLf strSQL = strSQL & " S.PACKLIST_ID" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " SHIPPER S," & VBCrLf strSQL = strSQL & " SHIPPER_LINE SL" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " S.INVOICE_ID IS NULL" & VBCrLf strSQL = strSQL & " AND S.STATUS='A'" & VBCrLf strSQL = strSQL & " AND S.PACKLIST_ID=SL.PACKLIST_ID" & VBCrLf strSQL = strSQL & " AND SL.SHIPPED_QTY>0" & VBCrLf strSQL = strSQL & "GROUP BY" & VBCrLf strSQL = strSQL & " S.PACKLIST_ID" snpData.Open strSQL, dbMyConnection lngCount = 0 If snpData.State = 1 Then Do While Not(snpData.EOF) lngCount = lngCount +1 snpData.MoveNext Loop strOut = "PLs to Invoice: " & cStr(lngCount) snpData.Close End If End Select 'Output the selections in the correct divInspector box Select Case i Case 1 divInspector1.InnerText = strOut Case 2 divInspector2.InnerText = strOut Case 3 divInspector3.InnerText = strOut Case 4 divInspector4.InnerText = strOut Case 5 divInspector5.InnerText = strOut Case 6 divInspector6.InnerText = strOut case 7 divInspector7.InnerText = strOut Case 8 divInspector8.InnerText = strOut Case 9 divInspector9.InnerText = strOut case 10 divInspector10.InnerText = strOut End Select Next divLastUpdate.InnerText = "Last Update: " & Time dbMyConnection.Close Else divLastUpdate.InnerText = "Could Not Connect" End If Set snpData = Nothing Set dbMyConnection = Nothing End Sub 'Function SaveSettings(Event) ' If Event.closeAction = event.Action.commit Then ' System.Gadget.Settings.write("settingsSelectionIndex", selUserEntry.selectedIndex) ' End If ' 'Allow the Settings dialog to close. ' event.cancel = false 'End Function 'Function SaveSettingsClosed(event) ' 'User hits OK on the settings page. ' If event.closeAction = event.Action.commit Then ' userEntry = System.Gadget.Settings.readString("settingsUserEntry") ' SetContentText userEntry ' Else ' If event.closeAction = event.Action.cancel Then ' 'User hits Cancel on the settings page. ' SetContentText "Cancelled" ' End If ' End If 'End Function Sub ShowBackgroundHighlight imgBackground.brightness = 0.0 End Sub Sub ShowFlyOut(intInspector) Dim strPage On Error Resume Next 'Determine which of the flyout web pages to display based on the Inspector loaded into the position Select Case strInpector(intInspector) Case "Negative On Hand" strPage = "FlyOutNOH.html" Case "Past Due PO Line Count" strPage = "FlyOutPOPD.html" Case "In Use Shop Resources" strPage = "FlyOutRIU.html" Case "Employees Clocked In" strPage = "FlyOutECI.html" Case "Employees Clocked Into Indirect" strPage = "FlyOutEOI.html" Case "Non Invoiced Shipper" strPage = "FlyOutPLI.html" End Select If strPage <> "" Then System.Gadget.Flyout.file = cStr(strPage) System.Gadget.Flyout.Show = true End If End Sub Private Function Switch(strValue) Dim i Dim intOffset Dim intTemp Dim strTemp Dim strKey strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand" intOffset = 50 strTemp = "" For i = 1 To Len(strValue) intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset If intTemp > 255 Then intTemp = intTemp - 255 End If strTemp = strTemp & Chr(intTemp) Next Switch = strTemp End Function </script> <body id="Background" onmouseover="imgBackground.brightness = 0.5" onmouseout="imgBackground.brightness = 0.0"> <g:background id="imgBackground" src="url(Inspectorbackground.png)" brightness="0.0" style="padding: 0px"</g:background> <div id="divInspector1" style="top: 10px;" onmouseover="divInspector1.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector1.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(1)">Inspector 1</div> <div id="divInspector2" style="top: 25px;" onmouseover="divInspector2.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector2.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(2)">Inspector 2</div> <div id="divInspector3" style="top: 40px;" onmouseover="divInspector3.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector3.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(3)">Inspector 3</div> <div id="divInspector4" style="top: 55px;" onmouseover="divInspector4.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector4.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(4)">Inspector 4</div> <div id="divInspector5" style="top: 70px;" onmouseover="divInspector5.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector5.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(5)">Inspector 5</div> <div id="divInspector6" style="top: 85px;" onmouseover="divInspector6.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector6.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(6)">Inspector 6</div> <div id="divInspector7" style="top: 100px;" onmouseover="divInspector7.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector7.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(7)">Inspector 7</div> <div id="divInspector8" style="top: 115px;" onmouseover="divInspector8.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector8.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(8)">Inspector 8</div> <div id="divInspector9" style="top: 130px;" onmouseover="divInspector9.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector9.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(9)">Inspector 9</div> <div id="divInspector10" style="top: 145px;" onmouseover="divInspector10.style.backgroundImage='url(InspectorButtonMouseOver.png)'" onmouseout="divInspector10.style.backgroundImage='url(InspectorButtonUp.png)'" onclick="ShowFlyOut(10)">Inspector 10</div> <div id="divLastUpdate" style="background-position: center center; padding: 0px; font-family: Perpetua; font-size: 10px; font-weight: bold; position: absolute; top: 169px; left: 12px; color: #000000;">Time</div> <div id="divTitle" style="background-position: left center; border-style: none; border-width: 0px; padding: 0px; margin: 0px; position: absolute; top: 178px; left: 3px; height: 25px; width: 135px; background-image: url(Inspector.png); background-repeat: no-repeat;"> </div> <div id="div1" style="background-position: left center; border-style: none; border-width: 0px; padding: 0px; margin: 0px; position: absolute; top: 200px; left: 3px; height: 25px; width: 135px; background-image: url(Inspector.png); background-repeat: no-repeat; visibility: hidden;"> </div> </body> </html>
—
FlyOutECI.html:
<html> <head> <title>K&M Inspector</title> <style type="text/css"> body { margin: 0px; width: 655px; height: 300px; } <!--table { font-size: 8pt; font-family: Times New Roman } --> </style> </head> <script language="VBScript"> Option Explicit Sub Window_Onload Dim dbMyConnection Dim comData Dim snpData Dim strUsername Dim strPassword Dim strDatabase Dim strSQL Dim strHTML On Error Resume Next Set snpData = CreateObject("ADODB.Recordset") Set dbMyConnection = CreateObject("ADODB.Connection") strUsername = "MyUser" strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25)) strDatabase = "MyDB" dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";" dbMyConnection.Open If dbMyConnection.State = 1 Then strSQL = "SELECT" & VBCrLf strSQL = strSQL & " LT.EMPLOYEE_ID," & VBCrLf strSQL = strSQL & " E.LAST_NAME || ', ' || E.FIRST_NAME AS EMPLOYEE_NAME," & VBCrLf strSQL = strSQL & " LT.RESOURCE_ID," & VBCrLf strSQL = strSQL & " LT.WORKORDER_BASE_ID," & VBCrLf strSQL = strSQL & " LT.WORKORDER_LOT_ID," & VBCrLf strSQL = strSQL & " LT.WORKORDER_SPLIT_ID," & VBCrLf strSQL = strSQL & " LT.WORKORDER_SUB_ID," & VBCrLf strSQL = strSQL & " LT.OPERATION_SEQ_NO," & VBCrLf strSQL = strSQL & " WO.PART_ID," & VBCrLf strSQL = strSQL & " LT.INDIRECT_ID," & VBCrLf strSQL = strSQL & " NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " LABOR_TICKET LT," & VBCrLf strSQL = strSQL & " EMPLOYEE E," & VBCrLf strSQL = strSQL & " WORK_ORDER WO" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf strSQL = strSQL & " AND LT.HOURS_WORKED IS NULL" & VBCrLf strSQL = strSQL & " AND LT.EMPLOYEE_ID=E.ID" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_TYPE=WO.TYPE(+)" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_BASE_ID=WO.BASE_ID(+)" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_LOT_ID=WO.LOT_ID(+)" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_SPLIT_ID=WO.SPLIT_ID(+)" & VBCrLf strSQL = strSQL & " AND WO.SUB_ID(+)='0'" & VBCrLf strSQL = strSQL & "ORDER BY" & VBCrLf strSQL = strSQL & " E.LAST_NAME || ', ' || E.FIRST_NAME," & VBCrLf strSQL = strSQL & " NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then strHTML = strHTML & "<b>Employees Clocked In</b>" & vbCrLf strHTML = strHTML & "<table width=640 border=1>" & vbCrLf strHTML = strHTML & "<tr bgcolor=#5555FF>" strHTML = strHTML & "<td><b>Emp ID</b></td>" strHTML = strHTML & "<td><b>Name</b></td>" strHTML = strHTML & "<td><b>Resource ID</b></td>" strHTML = strHTML & "<td><b>Work Order Op</b></td>" strHTML = strHTML & "<td><b>Part ID</b></td>" strHTML = strHTML & "<td><b>Clock In</b></td>" strHTML = strHTML & "</tr>" & vbCrLf Do While Not snpData.EOF strHTML = strHTML & "<tr>" strHTML = strHTML & "<td>" & cStr(snpData("employee_id")) & "</td>" If Not IsNull(snpData("employee_name")) Then strHTML = strHTML & "<td>" & cStr(snpData("employee_name")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("resource_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("resource_id")) & "</td>" If cStr(snpData("workorder_sub_id")) = "0" Then strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>" Else strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "-" & cStr(snpData("workorder_sub_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>" End If If Not IsNull(snpData("part_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If Else strHTML = strHTML & "<td>Indirect: " & cStr(snpData("indirect_id")) & "</td>" strHTML = strHTML & "<td> </td>" strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("clock_in")) Then strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If strHTML = strHTML & "</tr>" & vbCrLf snpData.MoveNext Loop strHTML = strHTML & "</table>" & vbCrLf snpData.Close divOutput.InnerHTML = strHTML Else divOutput.InnerText = "No Rows Returned" End If dbMyConnection.Close Else 'Could Not Connect divOutput.InnerText = "Could Not Connect" End If Set snpData = Nothing Set dbMyConnection = Nothing End Sub Private Function Switch(strValue) Dim i Dim intOffset Dim intTemp Dim strTemp Dim strKey strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand" intOffset = 50 strTemp = "" For i = 1 To Len(strValue) intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset If intTemp > 255 Then intTemp = intTemp - 255 End If strTemp = strTemp & Chr(intTemp) Next Switch = strTemp End Function </script> <body id="Background"> <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;"> </div> </body> </html>
—
FlyOutEOI.html:
<html> <head> <title>K&M Inspector</title> <style type="text/css"> body { margin: 0px; width: 505px; height: 300px; } <!--table { font-size: 8pt; font-family: Times New Roman } --> </style> </head> <script language="VBScript"> Option Explicit Sub Window_Onload Dim dbMyConnection Dim comData Dim snpData Dim strUsername Dim strPassword Dim strDatabase Dim strSQL Dim strHTML On Error Resume Next Set snpData = CreateObject("ADODB.Recordset") Set dbMyConnection = CreateObject("ADODB.Connection") strUsername = "MyUser" strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25)) strDatabase = "MyDB" dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";" dbMyConnection.Open If dbMyConnection.State = 1 Then strSQL = "SELECT" & VBCrLf strSQL = strSQL & " LT.EMPLOYEE_ID," & VBCrLf strSQL = strSQL & " E.LAST_NAME || ', ' || E.FIRST_NAME AS EMPLOYEE_NAME," & VBCrLf strSQL = strSQL & " LT.INDIRECT_ID," & VBCrLf strSQL = strSQL & " I.DESCRIPTION," & VBCrLf strSQL = strSQL & " NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " LABOR_TICKET LT," & VBCrLf strSQL = strSQL & " EMPLOYEE E," & VBCrLf strSQL = strSQL & " INDIRECT I" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf strSQL = strSQL & " AND LT.HOURS_WORKED IS NULL" & VBCrLf strSQL = strSQL & " AND LT.EMPLOYEE_ID=E.ID" & VBCrLf strSQL = strSQL & " AND LT.INDIRECT_ID IS NOT NULL" & VBCrLf strSQL = strSQL & " AND LT.INDIRECT_ID=I.ID" & VBCrLf strSQL = strSQL & "ORDER BY" & VBCrLf strSQL = strSQL & " E.LAST_NAME || ', ' || E.FIRST_NAME," & VBCrLf strSQL = strSQL & " NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then strHTML = strHTML & "<b>Employees Clocked Into Indirect</b>" & vbCrLf strHTML = strHTML & "<table width=490 border=1>" & vbCrLf strHTML = strHTML & "<tr bgcolor=#5555FF>" strHTML = strHTML & "<td><b>Emp ID</b></td>" strHTML = strHTML & "<td><b>Name</b></td>" strHTML = strHTML & "<td><b>Indirect</b></td>" strHTML = strHTML & "<td><b>Description</b></td>" strHTML = strHTML & "<td><b>Clock In</b></td>" strHTML = strHTML & "</tr>" & vbCrLf Do While Not snpData.EOF strHTML = strHTML & "<tr>" strHTML = strHTML & "<td>" & cStr(snpData("employee_id")) & "</td>" If Not IsNull(snpData("employee_name")) Then strHTML = strHTML & "<td>" & cStr(snpData("employee_name")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If strHTML = strHTML & "<td>" & cStr(snpData("indirect_id")) & "</td>" If Not IsNull(snpData("description")) Then strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("clock_in")) Then strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If strHTML = strHTML & "</tr>" & vbCrLf snpData.MoveNext Loop strHTML = strHTML & "</table>" & vbCrLf snpData.Close divOutput.InnerHTML = strHTML Else divOutput.InnerText = "No Rows Returned" End If dbMyConnection.Close Else 'Could Not Connect divOutput.InnerText = "Could Not Connect" End If Set snpData = Nothing Set dbMyConnection = Nothing End Sub Private Function Switch(strValue) Dim i Dim intOffset Dim intTemp Dim strTemp Dim strKey strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand" intOffset = 50 strTemp = "" For i = 1 To Len(strValue) intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset If intTemp > 255 Then intTemp = intTemp - 255 End If strTemp = strTemp & Chr(intTemp) Next Switch = strTemp End Function </script> <body id="Background"> <div id="divOutput" style="position: absolute; top: 10px; width: 495px; height: 280px; overflow-y:auto;"> </div> </body> </html>
—
FlyOutNOH.html:
<html> <head> <title>K&M Inspector</title> <style type="text/css"> body { margin: 0px; width: 505px; height: 300px; } <!--table { font-size: 8pt; font-family: Times New Roman } --> </style> </head> <script language="VBScript"> Option Explicit Sub Window_Onload Dim dbMyConnection Dim comData Dim snpData Dim strUsername Dim strPassword Dim strDatabase Dim strSQL Dim strHTML On Error Resume Next Set snpData = CreateObject("ADODB.Recordset") Set dbMyConnection = CreateObject("ADODB.Connection") strUsername = "MyUser" strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25)) strDatabase = "MyDB" dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";" dbMyConnection.Open If dbMyConnection.State = 1 Then strSQL = "SELECT" & VBCrLf strSQL = strSQL & " ID," & VBCrLf strSQL = strSQL & " DESCRIPTION," & VBCrLf strSQL = strSQL & " PRODUCT_CODE," & VBCrLf strSQL = strSQL & " COMMODITY_CODE," & VBCrLf strSQL = strSQL & " QTY_ON_HAND," & VBCrLf strSQL = strSQL & " DECODE(PURCHASED,'Y','Purchased','Fabricated') PART_TYPE" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " PART" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " QTY_ON_HAND<0" & VBCrLf strSQL = strSQL & "ORDER BY" & VBCrLf strSQL = strSQL & " ID" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then strHTML = strHTML & "<b>Parts with a Negative QTY On Hand</b>" & vbCrLf strHTML = strHTML & "<table width=490 border=1>" & vbCrLf strHTML = strHTML & "<tr bgcolor=#5555FF>" strHTML = strHTML & "<td><b>Part ID</b></td>" strHTML = strHTML & "<td><b>Description</b></td>" strHTML = strHTML & "<td><b>P.C.</b></td>" strHTML = strHTML & "<td><b>C.C.</b></td>" strHTML = strHTML & "<td><b>Qty</b></td>" strHTML = strHTML & "<td><b>Type</b></td>" strHTML = strHTML & "</tr>" & vbCrLf Do While Not snpData.EOF strHTML = strHTML & "<tr>" strHTML = strHTML & "<td>" & cStr(snpData("id")) & "</td>" If Not IsNull(snpData("description")) Then strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("product_code")) Then strHTML = strHTML & "<td>" & cStr(snpData("product_code")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("commodity_code")) Then strHTML = strHTML & "<td>" & cStr(snpData("commodity_code")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("qty_on_hand")) Then strHTML = strHTML & "<td><p align=""right"">" & FormatNumber(snpData("qty_on_hand"),4) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("part_type")) Then strHTML = strHTML & "<td>" & cStr(snpData("part_type")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If strHTML = strHTML & "</tr>" & vbCrLf snpData.MoveNext Loop strHTML = strHTML & "</table>" & vbCrLf snpData.Close divOutput.InnerHTML = strHTML Else divOutput.InnerText = "No Rows Returned" End If dbMyConnection.Close Else 'Could Not Connect divOutput.InnerText = "Could Not Connect" End If Set snpData = Nothing Set dbMyConnection = Nothing End Sub Private Function Switch(strValue) Dim i Dim intOffset Dim intTemp Dim strTemp Dim strKey strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand" intOffset = 50 strTemp = "" For i = 1 To Len(strValue) intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset If intTemp > 255 Then intTemp = intTemp - 255 End If strTemp = strTemp & Chr(intTemp) Next Switch = strTemp End Function </script> <body id="Background"> <div id="divOutput" style="position: absolute; top: 10px; width: 495px; height: 280px; overflow-y:auto;"> </div> </body> </html>
—
FlyOutPLI.html:
<html> <head> <title>K&M Inspector</title> <style type="text/css"> body { margin: 0px; width: 655px; height: 300px; } <!--table { font-size: 8pt; font-family: Times New Roman } --> </style> </head> <script language="VBScript"> Option Explicit Sub Window_Onload Dim dbMyConnection Dim comData Dim snpData Dim strUsername Dim strPassword Dim strDatabase Dim strLastPacklist Dim strSQL Dim strHTML On Error Resume Next Set snpData = CreateObject("ADODB.Recordset") Set dbMyConnection = CreateObject("ADODB.Connection") strUsername = "MyUser" strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25)) strDatabase = "MyDB" dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";" dbMyConnection.Open If dbMyConnection.State = 1 Then strSQL = "SELECT" & VBCrLf strSQL = strSQL & " S.PACKLIST_ID," & VBCrLf strSQL = strSQL & " SL.LINE_NO," & VBCrLf strSQL = strSQL & " S.BOL_ID," & VBCrLf strSQL = strSQL & " S.SHIPPED_DATE," & VBCrLf strSQL = strSQL & " CO.CUSTOMER_ID," & VBCrLf strSQL = strSQL & " SL.CUST_ORDER_ID," & VBCrLf strSQL = strSQL & " SL.CUST_ORDER_LINE_NO," & VBCrLf strSQL = strSQL & " COL.PART_ID," & VBCrLf strSQL = strSQL & " SL.SHIPPED_QTY" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " SHIPPER S," & VBCrLf strSQL = strSQL & " SHIPPER_LINE SL," & VBCrLf strSQL = strSQL & " CUST_ORDER_LINE COL," & VBCrLf strSQL = strSQL & " CUSTOMER_ORDER CO" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " S.INVOICE_ID IS NULL" & VBCrLf strSQL = strSQL & " AND S.STATUS='A'" & VBCrLf strSQL = strSQL & " AND S.PACKLIST_ID=SL.PACKLIST_ID" & VBCrLf strSQL = strSQL & " AND SL.CUST_ORDER_ID=COL.CUST_ORDER_ID(+)" & VBCrLf strSQL = strSQL & " AND SL.CUST_ORDER_LINE_NO=COL.LINE_NO(+)" & VBCrLf strSQL = strSQL & " AND COL.CUST_ORDER_ID=CO.ID(+)" & VBCrLf strSQL = strSQL & "ORDER BY" & VBCrLf strSQL = strSQL & " S.PACKLIST_ID," & VBCrLf strSQL = strSQL & " SL.LINE_NO" snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then strHTML = strHTML & "<b>Pack Lists Not Yet Invoiced</b>" & vbCrLf strHTML = strHTML & "<table width=640 border=1>" & vbCrLf strHTML = strHTML & "<tr bgcolor=#5555FF>" strHTML = strHTML & "<td><b>PL</b></td>" strHTML = strHTML & "<td><b>Line</b></td>" strHTML = strHTML & "<td><b>BOL</b></td>" strHTML = strHTML & "<td><b>Ship Date</b></td>" strHTML = strHTML & "<td><b>Customer</b></td>" strHTML = strHTML & "<td><b>Cust Order</b></td>" strHTML = strHTML & "<td><b>Part ID</b></td>" strHTML = strHTML & "<td><b>Qty</b></td>" strHTML = strHTML & "</tr>" & vbCrLf Do While Not snpData.EOF strHTML = strHTML & "<tr>" If strLastPacklist <> snpData("packlist_id") Then 'Need to output the pack list ID this time, since it is not the same as the last row returned by the database strHTML = strHTML & "<td>" & cStr(snpData("packlist_id")) & "</td>" strLastPacklist = snpData("packlist_id") Else strHTML = strHTML & "<td> </td>" End If strHTML = strHTML & "<td>" & cStr(snpData("line_no")) & "</td>" If Not IsNull(snpData("bol_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("bol_id")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("shipped_date")) Then strHTML = strHTML & "<td>" & cStr(snpData("shipped_date")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("customer_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("customer_id")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("cust_order_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("cust_order_id")) & "/" & cStr(snpData("cust_order_line_no")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("part_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("shipped_qty")) Then strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("shipped_qty")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If strHTML = strHTML & "</tr>" & vbCrLf snpData.MoveNext Loop strHTML = strHTML & "</table>" & vbCrLf snpData.Close divOutput.InnerHTML = strHTML Else divOutput.InnerText = "No Rows Returned" End If dbMyConnection.Close Else 'Could Not Connect divOutput.InnerText = "Could Not Connect" End If Set snpData = Nothing Set dbMyConnection = Nothing End Sub Private Function Switch(strValue) Dim i Dim intOffset Dim intTemp Dim strTemp Dim strKey strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand" intOffset = 50 strTemp = "" For i = 1 To Len(strValue) intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset If intTemp > 255 Then intTemp = intTemp - 255 End If strTemp = strTemp & Chr(intTemp) Next Switch = strTemp End Function </script> <body id="Background"> <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;"> </div> </body> </html>
—
FlyOutPOPD.html:
<html> <head> <title>K&M Inspector</title> <style type="text/css"> body { margin: 0px; width: 610px; height: 500px; } <!--table { font-size: 8pt; font-family: Times New Roman } --> </style> </head> <script language="VBScript"> Option Explicit Sub Window_Onload Dim dbMyConnection Dim comData Dim snpData Dim strUsername Dim strPassword Dim strDatabase Dim strSQL Dim strHTML 'On Error Resume Next Set snpData = CreateObject("ADODB.Recordset") Set dbMyConnection = CreateObject("ADODB.Connection") strUsername = "MyUser" strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25)) strDatabase = "MyDB" dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";" dbMyConnection.Open If dbMyConnection.State = 1 Then strSQL = "SELECT" & VBCrLf strSQL = strSQL & " POL.PART_ID," & VBCrLf strSQL = strSQL & " POL.VENDOR_PART_ID," & VBCrLf strSQL = strSQL & " P.DESCRIPTION," & VBCrLf strSQL = strSQL & " POL.PURC_ORDER_ID," & VBCrLf strSQL = strSQL & " POL.LINE_NO AS PURC_ORDER_LINE_NO," & VBCrLf strSQL = strSQL & " PLD.DEL_SCHED_LINE_NO," & VBCrLf strSQL = strSQL & " COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) AS DESIRED_RECV_DATE," & VBCrLf strSQL = strSQL & " DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) AS ORDER_QTY," & VBCrLf strSQL = strSQL & " DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0)) AS RECEIVED_QTY" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " PURCHASE_ORDER PO," & VBCrLf strSQL = strSQL & " PURC_ORDER_LINE POL," & VBCrLf strSQL = strSQL & " PART P," & VBCrLf strSQL = strSQL & " PURC_LINE_DEL PLD" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " PO.STATUS IN ('F','R','U')" & VBCrLf strSQL = strSQL & " AND PO.ID=POL.PURC_ORDER_ID" & VBCrLf strSQL = strSQL & " AND POL.LINE_STATUS='A'" & VBCrLf strSQL = strSQL & " AND POL.SERVICE_ID IS NULL" & VBCrLf strSQL = strSQL & " AND POL.ORDER_QTY>POL.TOTAL_RECEIVED_QTY" & VBCrLf strSQL = strSQL & " AND POL.PART_ID=P.ID(+)" & VBCrLf strSQL = strSQL & " AND POL.PURC_ORDER_ID=PLD.PURC_ORDER_ID(+)" & VBCrLf strSQL = strSQL & " AND POL.LINE_NO=PLD.PURC_ORDER_LINE_NO(+)" & VBCrLf strSQL = strSQL & " AND DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) > DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0))" & VBCrLf strSQL = strSQL & " AND COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) < SYSDATE" & VBCrLf strSQL = strSQL & "ORDER BY" & VBCrLf strSQL = strSQL & " POL.PART_ID," & vbCrLf strSQL = strSQL & " POL.PURC_ORDER_ID," & VBCrLf strSQL = strSQL & " POL.LINE_NO," & VBCrLf strSQL = strSQL & " PLD.DEL_SCHED_LINE_NO" & VBCrLf snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then strHTML = strHTML & "<b>Past Due Purchase Orders</b>" & vbCrLf strHTML = strHTML & "<table width=590 border=1>" & vbCrLf strHTML = strHTML & "<tr bgcolor=#5555FF>" strHTML = strHTML & "<td><b>Part ID</b></td>" strHTML = strHTML & "<td><b>Description</b></td>" strHTML = strHTML & "<td><b>PO</b></td>" strHTML = strHTML & "<td><b>Received</b></td>" strHTML = strHTML & "<td><b>Wanted</b></td>" strHTML = strHTML & "</tr>" & vbCrLf Do While Not snpData.EOF strHTML = strHTML & "<tr>" If Not IsNull(snpData("part_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>" Else If Not IsNull(snpData("vendor_part_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("vendor_part_id")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If End If If Not IsNull(snpData("description")) Then strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("del_sched_line_no")) Then strHTML = strHTML & "<td>" & cStr(snpData("purc_order_id")) & "/" & cStr(snpData("purc_order_line_no")) & "/DL " & cStr(snpData("del_sched_line_no")) & "</td>" Else strHTML = strHTML & "<td>" & cStr(snpData("purc_order_id")) & "/" & cStr(snpData("purc_order_line_no")) & "</td>" End If strHTML = strHTML & "<td>" & cStr(snpData("received_qty")) & " of " & cStr(snpData("order_qty")) & "</td>" If Not IsNull(snpData("desired_recv_date")) Then strHTML = strHTML & "<td>" & cStr(snpData("desired_recv_date")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If strHTML = strHTML & "</tr>" & vbCrLf snpData.MoveNext Loop strHTML = strHTML & "</table>" & vbCrLf snpData.Close divOutput.InnerHTML = strHTML Else divOutput.InnerText = "No Rows Returned" End If dbMyConnection.Close Else 'Could Not Connect divOutput.InnerText = "Could Not Connect" End If Set snpData = Nothing Set dbMyConnection = Nothing End Sub Private Function Switch(strValue) Dim i Dim intOffset Dim intTemp Dim strTemp Dim strKey strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand" intOffset = 50 strTemp = "" For i = 1 To Len(strValue) intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset If intTemp > 255 Then intTemp = intTemp - 255 End If strTemp = strTemp & Chr(intTemp) Next Switch = strTemp End Function </script> <body id="Background"> <div id="divOutput" style="position: absolute; top: 10px; width: 590px; height: 480px; overflow-y:auto;"> </div> </body> </html>
—
FlyOutRIU.html:
<html> <head> <title>K&M Inspector</title> <style type="text/css"> body { margin: 0px; width: 655px; height: 300px; } <!--table { font-size: 8pt; font-family: Times New Roman } --> </style> </head> <script language="VBScript"> Option Explicit Sub Window_Onload Dim dbMyConnection Dim comData Dim snpData Dim strUsername Dim strPassword Dim strDatabase Dim strSQL Dim strHTML On Error Resume Next Set snpData = CreateObject("ADODB.Recordset") Set dbMyConnection = CreateObject("ADODB.Connection") strUsername = "MyUser" strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25)) strDatabase = "MyDB" dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";" dbMyConnection.Open If dbMyConnection.State = 1 Then strSQL = "SELECT" & VBCrLf strSQL = strSQL & " LT.RESOURCE_ID," & VBCrLf strSQL = strSQL & " SR.DESCRIPTION," & VBCrLf strSQL = strSQL & " LT.WORKORDER_BASE_ID," & VBCrLf strSQL = strSQL & " LT.WORKORDER_LOT_ID," & VBCrLf strSQL = strSQL & " LT.WORKORDER_SPLIT_ID," & VBCrLf strSQL = strSQL & " LT.WORKORDER_SUB_ID," & VBCrLf strSQL = strSQL & " LT.OPERATION_SEQ_NO," & VBCrLf strSQL = strSQL & " WO.PART_ID," & VBCrLf strSQL = strSQL & " NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " LABOR_TICKET LT," & VBCrLf strSQL = strSQL & " SHOP_RESOURCE SR," & VBCrLf strSQL = strSQL & " WORK_ORDER WO" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf strSQL = strSQL & " AND LT.HOURS_WORKED IS NULL" & VBCrLf strSQL = strSQL & " AND LT.RESOURCE_ID IS NOT NULL" & VBCrLf strSQL = strSQL & " AND LT.RESOURCE_ID=SR.ID" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_TYPE='W'" & VBCrLf strSQL = strSQL & " AND WO.TYPE='W'" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_TYPE=WO.TYPE" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_BASE_ID=WO.BASE_ID" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_LOT_ID=WO.LOT_ID" & VBCrLf strSQL = strSQL & " AND LT.WORKORDER_SPLIT_ID=WO.SPLIT_ID" & VBCrLf strSQL = strSQL & " AND WO.SUB_ID='0'" & VBCrLf strSQL = strSQL & "ORDER BY" & VBCrLf strSQL = strSQL & " LT.RESOURCE_ID," & VBCrLf strSQL = strSQL & " NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)" & VBCrLf snpData.Open strSQL, dbMyConnection If snpData.State = 1 Then strHTML = strHTML & "<b>Shop Resources Currently in Use</b>" & vbCrLf strHTML = strHTML & "<table width=640 border=1>" & vbCrLf strHTML = strHTML & "<tr bgcolor=#5555FF>" strHTML = strHTML & "<td><b>Resource ID</b></td>" strHTML = strHTML & "<td><b>Description</b></td>" strHTML = strHTML & "<td><b>Work Order Op</b></td>" strHTML = strHTML & "<td><b>Part ID</b></td>" strHTML = strHTML & "<td><b>Clock In</b></td>" strHTML = strHTML & "</tr>" & vbCrLf Do While Not snpData.EOF strHTML = strHTML & "<tr>" strHTML = strHTML & "<td>" & cStr(snpData("resource_id")) & "</td>" If Not IsNull(snpData("description")) Then strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If cStr(snpData("workorder_sub_id")) = "0" Then strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>" Else strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "-" & cStr(snpData("workorder_sub_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>" End If If Not IsNull(snpData("part_id")) Then strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If If Not IsNull(snpData("clock_in")) Then strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>" Else strHTML = strHTML & "<td> </td>" End If strHTML = strHTML & "</tr>" & vbCrLf snpData.MoveNext Loop strHTML = strHTML & "</table>" & vbCrLf snpData.Close divOutput.InnerHTML = strHTML Else divOutput.InnerText = "No Rows Returned" End If dbMyConnection.Close Else 'Could Not Connect divOutput.InnerText = "Could Not Connect" End If Set snpData = Nothing Set dbMyConnection = Nothing End Sub Private Function Switch(strValue) Dim i Dim intOffset Dim intTemp Dim strTemp Dim strKey strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand" intOffset = 50 strTemp = "" For i = 1 To Len(strValue) intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset If intTemp > 255 Then intTemp = intTemp - 255 End If strTemp = strTemp & Chr(intTemp) Next Switch = strTemp End Function </script> <body id="Background"> <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;"> </div> </body> </html>
—
Settings.html (this is just a placeholder for configuration settings):
<html > <head> <meta http-equiv="Content-Type" content="text/html; charset=Unicode" /> <title>Hello World</title> <style type="text/css"> body { margin: 0; width: 130px; height: 75px; font-family: verdana; font-weight: bold; font-size: small; } #gadgetContent { margin-top: 20px; width: 130px; vertical-align: middle; text-align: center; overflow: hidden; } </style> <script type="text/jscript" language="jscript"> // Initialize the gadget. function init() { var oBackground = document.getElementById("imgBackground"); oBackground.src = "url(images/background.png)"; } </script> </head> <body onload="init()"> <g:background id="imgBackground"> <span id="gadgetContent">Hello World!</span> </g:background> </body> </html>
—
InspectorButtonUp.png:
—
InspectorButtonMouseOver.png:
—
InspectorBackground.png (Background Image for Main Web Page):
Other Backgrounds that May be Used (Note that Power Point is a great tool for creating backgrounds):
—
Resources for Developing Windows Sidebar Applications:
http://msdn.microsoft.com/en-us/library/bb456468(VS.85).aspx
http://www.microsoft.com/uk/msdn/screencasts/screencast/262/building-a-vista-sidebar-gadget-part-1-getting-started.aspx
Resources for VBScript Programming:
http://msdn.microsoft.com/en-us/library/d1wf56tt.aspx
http://www.w3schools.com/vbscript/vbscript_ref_functions.asp
Suggested Book:
“CSS The Definitive Guide” by Eric A. Meyer:
http://www.amazon.com/CSS-Definitive-Guide-Eric-Meyer/dp/0596527330
Recent Comments