Database Inpector Gadget

19 12 2009

December 19, 2009

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>&nbsp;</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>&nbsp;</td>"
                    End If
                Else
                    strHTML = strHTML & "<td>Indirect: " & cStr(snpData("indirect_id")) & "</td>"
                    strHTML = strHTML & "<td>&nbsp;</td>"
                    strHTML = strHTML & "<td>&nbsp;</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>&nbsp;</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;">&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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;">&nbsp;</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>&nbsp;</td>"
                End If
                If Not IsNull(snpData("product_code")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("product_code")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("commodity_code")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("commodity_code")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</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>&nbsp;</td>"
                End If
                If Not IsNull(snpData("part_type")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_type")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</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;">&nbsp;</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>&nbsp;</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>&nbsp;</td>"
                End If
                If Not IsNull(snpData("shipped_date")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("shipped_date")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("customer_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("customer_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</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>&nbsp;</td>"
                End If
                If Not IsNull(snpData("part_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</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>&nbsp;</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;">&nbsp;</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>&nbsp;</td>"
                    End If
                End If
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</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>&nbsp;</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;">&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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;">&nbsp;</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








Follow

Get every new post delivered to your Inbox.

Join 139 other followers