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
Very nice! I’ve been toying around with the idea of using gadgets for monitoring business statistics.
Russell,
Thank you for the compliment. Take a look in the VBS category in this blog for some more ideas of what you can do to extend the Database Inspector Gadget – you will see examples that produce HTML charts that show Oracle performance data and a lot of other interesting things – much of that same logic should work for this Windows gadget’s coding.
I originally built this gadget mostly as a proof of concept for a presentation that I gave in the middle of 2009, and I have done little with it since. If you create something interesting with the framework that I provided, please take a screen capture and link it back into this article in a comment.