The SQL to the Orbiting Ball

12 09 2010

September 12, 2010

Several years ago I developed a somewhat simple program in 16 bit Borland C (last compiled in 1994, so it pre-dates graphical web pages) that was optimized for 386 and 486 computers which sported VGA graphics but was also able to work with EGA graphics cards.  The simple program, after creating 18 images on the fly that simulated a rotating basketball, generated X and Y coordinates using a specially crafted formula that was capable of producing X and Y cordinates in a circular pattern.  With slightly different inputs the same formula produced a slightly out of round circular pattern that eventually might have generated straight line patterns.  With a slight adjustment to the inputs the same formula produced W patterns within a bounded box.  Keyboard input allowed the user to specify adjusted inputs as the program executed.  The C program looked like this:

#include <graphics.h>               // for graphics functions
#include <conio.h>                  // for kbhit()
#include <math.h>
#include <dos.h>
#define LEFT      100               // boundary of rectangle
#define TOP       100
#define RADIUS    20                // radius of ball
#define pi        3.14159276
#define L_ARROW   75
#define R_ARROW   77
#define U_ARROW   72
#define D_ARROW   80
#define ESC       27

void main(void)
   {
   int driver, mode;                // for initgraph()
   float x, y, dx, dy, i, sped, temp, x1, x2, y1, y2, sta1, sta2, ena1, ena2, rad;    // ball coordinates
   int imgnum, tim, del;
   char key;
   unsigned char ballbuff[10][5000];    // buffer for ball image

   driver = DETECT;                 // auto detect
        // set graphics mode
   initgraph(&driver, &mode, "c:\\bc4\\bgi");

   x = LEFT + RADIUS;          // designate center where ball is created
   y = TOP  + RADIUS;
   for (i =0; i <180; i = i + 18) // create basketball rotation images
       {
 setcolor(RED);
 setfillstyle(SOLID_FILL, RED);
 circle(x, y, RADIUS);
 floodfill(x, y, RED);
 setcolor(BLACK);
 rad = i / (2 * pi);
 x1 = x + 30 * cos(rad);
 x2 = x - 30 * cos(rad);
 y1 = y + 30 * sin(rad);
 y2 = y - 30 * sin(rad);
 sta1 = (180 + i -62);
 ena1 = (180 + i + 42);
 sta2 = (i -62);
 ena2 = (i + 42);
 if ((i/36) != int(i/36))    // must be included to swap arcs
    {
     temp = sta1;
     sta1 = sta2;
     sta2 = temp;
     temp = ena1;
     ena1 = ena2;
     ena2 = temp;
    }
 ellipse (x1, y1, sta1, ena1, RADIUS , RADIUS);
 ellipse (x2, y2, sta2, ena2, RADIUS , RADIUS);
 line (x - cos(rad + pi/2) * RADIUS, y - sin(rad + pi/2) * RADIUS, x + cos(rad + pi/2) * RADIUS, y + sin(rad + pi/2) * RADIUS);
        // pickup image
 getimage(x-RADIUS-20, y-RADIUS-20, x+RADIUS+20, y+RADIUS+20, ballbuff[i/18]);
        // clear screen
 setcolor(WHITE);
 rectangle(-1,-1, 640, 480);
 setfillstyle(SOLID_FILL, BLACK);
 floodfill(100,100, WHITE);
       }

   imgnum = 10;                         // load first position + 1
   tim = 0;                             // set delay to zero
   dx = .1;                             // set constant in x equation
   dy = .1;
   sped = 1;
   del = 1;
   while ( key != ESC )             
      {
      if (kbhit())
      {
       key = getch();
       if (key == 61) del++;            // = key pressed
       if (key == 43) del++;            // + key pressed
       if (key == 45) del--;            // - key pressed
       if (key == 47) sped = sped +.1;  // / key pressed
       if (key == 92) sped = sped -.1;  // \ key pressed
       if (key == 0)                    // place image on screen
 switch(getch())
 {
  case L_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dx = dx -.01;
     break;
  case R_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dx = dx + .01;
     break;
  case U_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dy = dy + .01;
     break;
  case D_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dy = dy -.01;
     break;
  case ESC:
     key = 27;
     break;
        }
      }
      tim = tim + sped;
      x = (sin(dx * tim)*100) + 300;
      y = (cos(dy * tim)* 100) + 300;
      imgnum--;                   // cycle through images
      if (imgnum == -1) imgnum = 9;
      putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], COPY_PUT);

      // move ball across screen
      // to make ball move rapidly increase +
      // set height on screen
      delay(del);                 // make delay smaller for slow computers
      }
   getch();

   closegraph();
   }

If you still have a 16 bit Borland C compiler (my copy is in a huge box on the bottom self of my bookcase), feel free to compile the above program to see it in action.  The original compiled C program, last compiled in 1994, may be downloaded here: OrbitBall2.zip (save as OrbitBall.zip and extract the files – download might not work).  The original program is confirmed to work on 32 bit Windows 95 through Windows XP (in DOS full screen mode), but definitely will not work on 64 bit Windows, even in a virtual machine (it also failed to run on a 32 bit Windows 7 netbook).

You are probably thinking, “Neat, but what does that have to do with Oracle?”.  It might be interesting to produce a modernized version of the above program.  We could use a simple SQL statement like the following to generate 3,600 X and Y coordinates, much like the X and Y coordinates that were generated by the above C program (note that the COS function is typically used to derive X values (using mathematical cosine) and the SIN function is typically used to derive Y values – the functions were swapped so that the X and Y cordinates start at the bottom-center of the screen).  The SQL follows:

SELECT
  ROUND((SIN(DX * (SPEED * COUNTER)) * WIDTH/2) + WIDTH/2) X,
  ROUND((COS(DY * (SPEED * COUNTER)) * HEIGHT/2) + HEIGHT/2) Y
FROM
  (SELECT
    0.1 DX,
    0.1 DY,
    1 SPEED,
    1 DELAY,
    300 WIDTH,
    300 HEIGHT,
    LEVEL COUNTER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=3600);

Now we have a slight problem, how do we present the X and Y coordinate points produced by the above SQL statement?  We need some sort of object to track the X and Y coordinate pairs.  Drawn basketballs might work, but instead I will use these pictures (created with Microsoft Power Point 2010):


To display the round oak pictures, we will put together a VBS script to build a web page on the fly, cycling through the above eight pictures (every two X,Y coordinate pairs cause the displayed picture to change).  Much like the original program, we will allow the user to control the input parameters as the program runs.  Each time the parameters are adjusted, 3,600 new X,Y coordinate points are retrieved from the database into an array – this allows the VBS script to continue at the next X,Y coordinate pair, rather than starting at the beginning every time the parameters are adjusted. 

Option Explicit

Const adCmdText = 1
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adVarNumeric = 139
Const adBigInt = 20
Const adDecimal = 14
Const adDouble = 5
Const adInteger = 3
Const adLongVarBinary = 205
Const adNumeric = 131
Const adSingle = 4
Const adSmallInt = 2
Const adTinyInt = 16
Const adUnsignedBigInt = 21
Const adUnsignedInt = 19
Const adUnsignedSmallInt = 18
Const adUnsignedTinyInt = 17
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adUseClient = 3

Dim dbDatabase
Dim snpData
Dim comData
Dim varData
Dim objIE

Dim strUsername             'Username
Dim strPassword             'Password
Dim strDatabase             'SID name from tnsnames.ora

startup

Sub startup()
    Dim strSQL
    Dim strHTML
    Dim objOrbitBall
    Dim objOrbitBallPic
    Dim objCommand
    Dim objSettings
    Dim i
    Dim intQuit

    'Fire up Internet Explorer
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Left = 0
    objIE.Top = 0
    objIE.Width = 930
    objIE.Height = 820
    objIE.StatusBar = True
    objIE.MenuBar = False
    objIE.Toolbar = False
    objIE.Navigate "about:blank"
    objIE.Document.Title = "The SQL to the Orbiting Ball"
    objIE.Visible = True

    'The data entry area
    strHTML = "<div style=""position: absolute;width: 180px; height: 200px;top: 10px;left: 710px;"">" & vbCrLf
    strHTML = strHTML & "<input type=text id=txtCommand value="""" size=""1""><br>" & vbCrLf
    strHTML = strHTML & "<font size=1><b>+&nbsp;&nbsp;&nbsp;Increase Delay (Not Used)<br>" & vbCrLf
    strHTML = strHTML & "-&nbsp;&nbsp;&nbsp;Decrease Delay (Not Used)<br>" & vbCrLf
    strHTML = strHTML & "/&nbsp;&nbsp;&nbsp;Increase Rotation Speed<br>" & vbCrLf
    strHTML = strHTML & "\&nbsp;&nbsp;&nbsp;Decrease Rotation Speed<br>" & vbCrLf
    strHTML = strHTML & "D&nbsp;&nbsp;&nbsp;Increase Rotation Speed X Axis<br>" & vbCrLf
    strHTML = strHTML & "A&nbsp;&nbsp;&nbsp;Decrease Rotation Speed X Axis<br>" & vbCrLf
    strHTML = strHTML & "W&nbsp;&nbsp;&nbsp;Increase Rotation Speed Y Axis<br>" & vbCrLf
    strHTML = strHTML & "S&nbsp;&nbsp;&nbsp;Decrease Rotation Speed Y Axis<br>" & vbCrLf
    strHTML = strHTML & "L&nbsp;&nbsp;&nbsp;Increase Width X Axis<br>" & vbCrLf
    strHTML = strHTML & "J&nbsp;&nbsp;&nbsp;Decrease Width X Axis<br>" & vbCrLf
    strHTML = strHTML & "I&nbsp;&nbsp;&nbsp;Increase Height Y Axis<br>" & vbCrLf
    strHTML = strHTML & "K&nbsp;&nbsp;&nbsp;Decrease Height Y Axis<br>" & vbCrLf
    strHTML = strHTML & "(space)&nbsp;&nbsp;&nbsp;Restart at 0<br>" & vbCrLf
    strHTML = strHTML & "X&nbsp;&nbsp;&nbsp;Exit</b></font>" & vbCrLf
    strHTML = strHTML & "</div>" & vbCrLf

    'The current orbit information
    strHTML = strHTML & "<div id=""Settings"" style=""position: absolute;width: 180px; height: 100px;top: 600px;left: 710px;""> </div>"
    strHTML = strHTML & "<IMG ID=""picOrbitBall"" style=""position: absolute;"" src=""http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball0.png"">" & vbCrLf
    objIE.Document.Body.InnerHTML = strHTML

    'The sleep here is only necessary if the database connections happen very quickly
    'Wscript.Sleep 500

    Set dbDatabase = CreateObject("ADODB.Connection")
    Set snpData = CreateObject("ADODB.Recordset")
    Set comData = CreateObject("ADODB.Command")

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDB"

    On Error Resume Next

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbDatabase.open

    'Should verify that the connection attempt was successful, but I will leave that for someone else to code

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  ROUND((SIN(DX * (SPEED * COUNTER)) * WIDTH/2) + WIDTH/2) X," & vbCrLf
    strSQL = strSQL & "  ROUND((COS(DY * (SPEED * COUNTER)) * HEIGHT/2) + HEIGHT/2) Y" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  (SELECT" & vbCrLf
    strSQL = strSQL & "    ? DX," & vbCrLf
    strSQL = strSQL & "    ? DY," & vbCrLf
    strSQL = strSQL & "    ? SPEED," & vbCrLf
    strSQL = strSQL & "    ? DELAY," & vbCrLf
    strSQL = strSQL & "    ? WIDTH," & vbCrLf
    strSQL = strSQL & "    ? HEIGHT," & vbCrLf
    strSQL = strSQL & "    LEVEL COUNTER" & vbCrLf
    strSQL = strSQL & "  FROM" & vbCrLf
    strSQL = strSQL & "    DUAL" & vbCrLf
    strSQL = strSQL & "  CONNECT BY" & vbCrLf
    strSQL = strSQL & "    LEVEL<=3600)"

    With comData
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase

        'Add the bind variables
        .Parameters.Append .CreateParameter("dx", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("dy", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("speed", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("delay", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("width", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("height", adDouble, adParamInput, 30)
    End With

    comData("dx") = 0.1
    comData("dy") = 0.1
    comData("speed") = 1
    comData("delay") = 1
    comData("width") = 600
    comData("height") = 600

    Set snpData = comData.Execute

    'Retrieve up to 10,000 data points from Oracle
    varData = snpData.GetRows(10000)
    snpData.Close
    Set snpData = Nothing

    'Allow faster access to these objects when executing in the loop
    Set objOrbitBall = objIE.Document.getElementById("picOrbitBall").Style
    Set objOrbitBallPic = objIE.Document.getElementById("picOrbitBall")
    Set objCommand = objIE.Document.All.txtCommand
    Set objSettings = objIE.Document.getElementById("Settings")

    'Write out the current settings for the orbit
    objSettings.InnerText = "DX: " & comData("dx") & Chr(10) & "DY: " & comData("dy") & Chr(10) & _
                            "Speed: " & comData("speed") &Chr(10) & _
                            "Width: " & comData("width") & Chr(10) & "Height: " & comData("height")

    intQuit = False

    Do While intQuit = False
        For i = 0 To UBound(varData, 2)
            objOrbitBall.Left = CInt(varData(0, i))
            objOrbitBall.Top = CInt(varData(1, i))
            objOrbitBallPic.Src = "http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball" & cStr(i/2 Mod 8 ) & ".png"

            Wscript.Sleep 50
            Select Case Left(objCommand.Value, 1)
                Case "=", "+"
                    comData("delay") = comData("delay") + 1
                Case "-"
                    comData("delay") = comData("delay") - 1
                Case "/"
                    comData("speed") = comData("speed") + 0.1
                Case "\"
                    comData("speed") = comData("speed") - 0.1
                Case "W", "w"
                    comData("dy") = comData("dy") + 0.0005
                Case "S", "s"
                    comData("dy") = comData("dy") - 0.0005
                Case "D", "d"
                    comData("dx") = comData("dx") + 0.0005
                Case "A", "a"
                    comData("dx") = comData("dx") - 0.0005
                Case "I", "i"
                    comData("height") = comData("height") + 5
                Case "K", "k"
                    comData("height") = comData("height") - 5
                Case "L", "l"
                    comData("width") = comData("width") + 5
                Case "J", "j"
                    comData("width") = comData("width") - 5
                Case "X", "x"
                    intQuit = True
                    Exit For
                Case " "
                     'Reset the loop from the beginning
                     objCommand.Value = ""
                     Exit For
            End Select

            If objCommand.Value <> "" Then
                objCommand.Value = ""

                Set snpData = comData.Execute

                'Retrieve up to 10,000 data points from Oracle
                varData = snpData.GetRows(10000)

                snpData.Close
                Set snpData = Nothing

                'Write out the current settings for the orbit
                objSettings.InnerText = "DX: " & comData("dx") & Chr(10) & "DY: " & comData("dy") & Chr(10) & _
                                        "Speed: " & comData("speed") &Chr(10) & _
                                        "Width: " & comData("width") & Chr(10) & "Height: " & comData("height")
            End If
        Next
    Loop

    objIE.quit
    dbDatabase.Close
    Set dbDatabase = Nothing
    Set objIE = Nothing
End Sub

You may download the above script here: SQLOrbitingBall.vbs (save as SQLOrbitingBall.vbs).

A Circular Orbit:

————-

An Orbit that Changes from a Circular Orbit to a Straight Line:

————-

An Orbit where the Ball Bounces Between the Top and Bottom of the Window:

—————————

As written, the script assumes a minimum of 930 x 820 resolution (1080p resolution or greater should work without any problems).  Adjust the script as necessary for lower resolution screens.  The program written in C certainly is shorter than the moderized version of the program, and had a bit more wow factor prior to the widespread use of Windows and other graphical user interfaces.





Graphical Work Center Utilization – Creating the Demo Data and Active Server Page

1 09 2010

September 1, 2010

Today’s blog article provides a graphical view of production work areas on a factory floor, providing feedback to indicate when the production area is in use.  The blog article includes a classic ASP web page which uses VBScript to write the web page on the fly to the browser on the client computer.  The web page written to the client browser automatically refreshes itself every 30 seconds, automatically advancing the view time by 15 minutes, displaying the production work areas that were in use during the new view time (the time displayed at the top of the page).

The ASP web page is the simple part of today’s blog article (although enabling classic ASP support may be a little challenging), while creating the demo data is actually the challenging portion of the article.  First, we need a table that will define the production work areas and the locations of those areas on the factory floor:

CREATE TABLE RESOURCE_LOCATION_DEMO (
  RESOURCE_ID VARCHAR2(15),
  DESCRIPTION VARCHAR2(30),
  LOCATION_LEFT NUMBER(12,4),
  LOCATION_TOP NUMBER(12,4),
  LOCATION_WIDTH NUMBER(12,4),
  LOCATION_HEIGHT NUMBER(12,4),
  PRIMARY KEY (RESOURCE_ID));

To keep things interesting, I do not want to just place the first production work area next to the second, the second next to the third, etc.  Instead, I want to randomly locate the production work areas on the factory floor, making certain that no two work areas overlap.  We can accomplish this by creating a list of numbers and ordering the numbers in a random sequence, like this:

SELECT
  ROWNUM RN
FROM
  DUAL
CONNECT BY
  LEVEL<=200
ORDER BY
  DBMS_RANDOM.VALUE;

  RN
----
 191
 165
 122
  12
  48
  27
 104
...
 198
 168
 150

200 rows selected.

Now, to locate the production work areas, imagine that we permitted 10 work areas horizontally (along the X axis) across the factory floor.  We can use the above number sequence along with the MOD function to determine the horizontal location of the work areas, and the FLOOR function to determine the vertical location of the work areas (note that each time we run this SQL statement will we receive different results):

SELECT
  'MA'||TO_CHAR(ROWNUM) RESOURCE_ID,
  'Shop Floor Machine '||TO_CHAR(ROWNUM) DESCRIPTION,
  MOD(RN,10) BOX_LEFT,
  FLOOR(RN/10) BOX_TOP
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=200
  ORDER BY
    DBMS_RANDOM.VALUE)
WHERE
  ROWNUM<=50;

RESOURCE_ID DESCRIPTION                 BOX_LEFT    BOX_TOP
----------- ------------------------- ---------- ----------
MA1         Shop Floor Machine 1               4         14
MA2         Shop Floor Machine 2               9          6
MA3         Shop Floor Machine 3               5          2
MA4         Shop Floor Machine 4               5          9
MA5         Shop Floor Machine 5               7         18
MA6         Shop Floor Machine 6               9          4
MA7         Shop Floor Machine 7               0          8
MA8         Shop Floor Machine 8               6          6
MA9         Shop Floor Machine 9               5          5
MA10        Shop Floor Machine 10              7         15
...
MA49        Shop Floor Machine 49              2         11
MA50        Shop Floor Machine 50              8          7

It would be too boring to assume that each of the production work areas is exactly the same width and height, so we will add a little more randomization.  Additionally, I want each production area to be up to 1.5 units wide and up to 1.0 units tall, both offset 0.25 units from the top-left (we are dealing with screen coordinates here, where positive Y is the same as mathematical -Y).  While there are up to 200 locations on the factory floor for work areas, we will only define 50 work areas (controlled by the ROWNUM<=50 predicate at the end of the SQL statement):

SELECT
  'MA'||TO_CHAR(ROWNUM) RESOURCE_ID,
  'Shop Floor Machine '||TO_CHAR(ROWNUM) DESCRIPTION,
  (MOD(RN,10))*1.5 + 0.25 LOCATION_LEFT,
  (FLOOR(RN/10))*1.0 + 0.25 LOCATION_TOP,
  ROUND(1.5*DBMS_RANDOM.VALUE,4) LOCATION_WIDTH,
  ROUND(1.0*DBMS_RANDOM.VALUE,4) LOCATION_HEIGHT
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=200
  ORDER BY
    DBMS_RANDOM.VALUE)
WHERE
  ROWNUM<=50;

RESOURCE_ID DESCRIPTION               LOCATION_LEFT LOCATION_TOP LOCATION_WIDTH LOCATION_HEIGHT
----------- ------------------------- ------------- ------------ -------------- ---------------
MA1         Shop Floor Machine 1               3.25        18.25         1.2386           .7948
MA2         Shop Floor Machine 2               4.75        11.25          .6078           .9578
MA3         Shop Floor Machine 3               1.75        12.25          .5318            .457
MA4         Shop Floor Machine 4               3.25        13.25         1.2908           .9813
MA5         Shop Floor Machine 5                .25        16.25          .3245           .4644
MA6         Shop Floor Machine 6              12.25        15.25           .239           .3822
MA7         Shop Floor Machine 7               1.75        18.25          .0159           .8868
MA8         Shop Floor Machine 8               1.75        16.25          .3948           .8511
MA9         Shop Floor Machine 9              12.25         6.25          .4856           .3356
MA10        Shop Floor Machine 10             13.75        11.25         1.2619           .6124
...
MA49        Shop Floor Machine 49              7.75        16.25          .6664           .6938
MA50        Shop Floor Machine 50              9.25        15.25         1.3449           .6606

Now that we have tested the results, let’s insert a new set of similar random values into the RESOURCE_LOCATION_DEMO table, and display some of the inserted rows:

INSERT INTO
  RESOURCE_LOCATION_DEMO 
SELECT
  'MA'||TO_CHAR(ROWNUM) RESOURCE_ID,
  'Shop Floor Machine '||TO_CHAR(ROWNUM) DESCRIPTION,
  (MOD(RN,10))*1.5 + 0.25 LOCATION_LEFT,
  (FLOOR(RN/10))*1.0 + 0.25 LOCATION_TOP,
  ROUND(1.5*DBMS_RANDOM.VALUE,4) LOCATION_WIDTH,
  ROUND(1.0*DBMS_RANDOM.VALUE,4) LOCATION_HEIGHT
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=200
  ORDER BY
    DBMS_RANDOM.VALUE)
WHERE
  ROWNUM<=50;

COMMIT;

SELECT
  *
FROM
  RESOURCE_LOCATION_DEMO;

RESOURCE_ID DESCRIPTION               LOCATION_LEFT LOCATION_TOP LOCATION_WIDTH LOCATION_HEIGHT
----------- ------------------------- ------------- ------------ -------------- ---------------
MA1         Shop Floor Machine 1              10.75        13.25           .104           .2165
MA2         Shop Floor Machine 2               7.75        18.25         1.2291            .478
MA3         Shop Floor Machine 3               9.25        16.25          .3431           .4364
MA4         Shop Floor Machine 4               1.75        15.25          .3665           .7278
MA5         Shop Floor Machine 5               4.75        15.25          .6842           .4507
MA6         Shop Floor Machine 6               4.75        18.25          .1384           .6434
MA7         Shop Floor Machine 7               4.75         7.25          .7448           .2178
MA8         Shop Floor Machine 8               7.75          .25          .3756            .499
MA9         Shop Floor Machine 9               1.75        18.25         1.0155           .0769
MA10        Shop Floor Machine 10              7.75        13.25         1.1892           .7518
...
MA49        Shop Floor Machine 49              6.25         3.25           .278           .6513
MA50        Shop Floor Machine 50               .25        15.25          .5216           .9607

To translate the above storage units (maybe in inch scale) into screen units we will multiply the storage units by 96 (96 dots per inch) and then multiply by the zoom percent (75% = 0.75).

SELECT
  RESOURCE_ID,
  ROUND(LOCATION_LEFT*96 *0.75) LOCATION_LEFT,
  ROUND(LOCATION_TOP*96 *0.75) LOCATION_TOP,
  ROUND(LOCATION_WIDTH*96 *0.75) LOCATION_WIDTH,
  ROUND(LOCATION_HEIGHT*96 *0.75) LOCATION_HEIGHT
FROM
  RESOURCE_LOCATION_DEMO;

RESOURCE_ID LOCATION_LEFT LOCATION_TOP LOCATION_WIDTH LOCATION_HEIGHT
----------- ------------- ------------ -------------- ---------------
MA1                   774          954              7              16
MA2                   558         1314             88              34
MA3                   666         1170             25              31
MA4                   126         1098             26              52
MA5                   342         1098             49              32
MA6                   342         1314             10              46
MA7                   342          522             54              16
MA8                   558           18             27              36
MA9                   126         1314             73               6
MA10                  558          954             86              54
...

Next, we need a table to maintain the time periods in which each of the production work areas was in use, and by whom the work areas were used:

CREATE TABLE LABOR_TICKET_DEMO (
  TRANSACTION_ID NUMBER,
  RESOURCE_ID VARCHAR2(15),
  EMPLOYEE_ID VARCHAR2(15),
  CLOCK_IN DATE,
  CLOCK_OUT DATE,
  PRIMARY KEY (TRANSACTION_ID));

Let’s see if we are able to generate some random data for the table:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI';

SELECT
  ROWNUM TRANSACTION_ID,
  'MA'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,50))) RESOURCE_ID,
  'EMP'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,300))) EMPLOYEE_ID,
  TRUNC(SYSDATE)+DBMS_RANDOM.VALUE CLOCK_IN
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

TRANSACTION_ID RESOURCE_ID EMPLOYEE_ID CLOCK_IN
-------------- ----------- ----------- -----------------
             1 MA29        EMP50       01-SEP-2010 01:56
             2 MA35        EMP181      01-SEP-2010 10:06
             3 MA13        EMP172      01-SEP-2010 17:40
             4 MA21        EMP182      01-SEP-2010 09:00
             5 MA14        EMP80       01-SEP-2010 09:53
             6 MA4         EMP80       01-SEP-2010 19:04
             7 MA7         EMP110      01-SEP-2010 14:34
             8 MA45        EMP19       01-SEP-2010 22:05
             9 MA10        EMP207      01-SEP-2010 21:51
            10 MA46        EMP127      01-SEP-2010 16:49

That worked, but note that we did not generate a CLOCK_OUT time – we want to make certain that the CLOCK_OUT time is after the CLOCK_IN time, but we simply cannot do that with the above SQL statement as written.  We slide the above into an inline view and then set the CLOCK_OUT time to be up to 12 hours after the CLOCK_IN time (DBMS_RANDOM.VALUE by default returns a value between 0 and 1, so if we divide that value by 2, we can add up to 1/2 of a day to the CLOCK_IN date and time):

INSERT INTO
  LABOR_TICKET_DEMO
SELECT
  TRANSACTION_ID,
  RESOURCE_ID,
  EMPLOYEE_ID,
  CLOCK_IN,
  CLOCK_IN + (DBMS_RANDOM.VALUE/2) CLOCK_OUT
FROM
  (SELECT
    ROWNUM TRANSACTION_ID,
    'MA'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,50))) RESOURCE_ID,
    'EMP'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,300))) EMPLOYEE_ID,
    TRUNC(SYSDATE)+DBMS_RANDOM.VALUE CLOCK_IN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000);

COMMIT;

Let’s take a look at what made it into the table (your results will be different):

SELECT
  *
FROM
  LABOR_TICKET_DEMO
ORDER BY
  TRANSACTION_ID; 

TRANSACTION_ID RESOURCE_ID     EMPLOYEE_ID     CLOCK_IN          CLOCK_OUT
-------------- --------------- --------------- ----------------- -----------------
             1 MA34            EMP49           01-SEP-2010 20:32 02-SEP-2010 08:18
             2 MA47            EMP230          01-SEP-2010 20:08 02-SEP-2010 03:06
             3 MA20            EMP257          01-SEP-2010 02:17 01-SEP-2010 05:44
             4 MA21            EMP129          01-SEP-2010 09:37 01-SEP-2010 15:41
             5 MA18            EMP214          01-SEP-2010 18:57 01-SEP-2010 20:57
             6 MA46            EMP173          01-SEP-2010 05:51 01-SEP-2010 15:32
             7 MA34            EMP224          01-SEP-2010 20:23 02-SEP-2010 08:17
             8 MA31            EMP8            01-SEP-2010 02:56 01-SEP-2010 14:02
             9 MA37            EMP178          01-SEP-2010 09:28 01-SEP-2010 16:03
            10 MA8             EMP31           01-SEP-2010 20:17 02-SEP-2010 05:51
...
           999 MA43            EMP138          01-SEP-2010 05:07 01-SEP-2010 05:23
          1000 MA2             EMP235          01-SEP-2010 05:29 01-SEP-2010 13:28

We now have 1000 transactions scattered across the 50 work areas (RESOURCE_ID column).  What we next want to determine is which of the work areas was in use at a specific time of the day.  Because we eventually will want only one row per unique RESOURCE_ID value, we will use the ROW_NUMBER analytic function to number each of the rows within each unique RESOURCE_ID value:

SELECT
  RESOURCE_ID,
  EMPLOYEE_ID,
  TRUNC(SYSDATE) + (1.25)/24 CHECK_TIME,
  CLOCK_IN,
  CLOCK_OUT,
  ROW_NUMBER() OVER (PARTITION BY RESOURCE_ID ORDER BY CLOCK_IN) RN
FROM
  LABOR_TICKET_DEMO
WHERE
  CLOCK_IN<=TRUNC(SYSDATE) + (1.25)/24
  AND CLOCK_OUT>TRUNC(SYSDATE) + (1.25)/24
ORDER BY
  RESOURCE_ID,
  CLOCK_IN;

RESOURCE_ID  EMPLOYEE_ID  CHECK_TIME        CLOCK_IN          CLOCK_OUT         RN
------------ ------------ ----------------- ----------------- ----------------- --
MA10         EMP192       01-SEP-2010 01:15 01-SEP-2010 00:21 01-SEP-2010 05:44  1
MA10         EMP233       01-SEP-2010 01:15 01-SEP-2010 00:23 01-SEP-2010 02:42  2
MA16         EMP114       01-SEP-2010 01:15 01-SEP-2010 00:21 01-SEP-2010 04:48  1
MA18         EMP261       01-SEP-2010 01:15 01-SEP-2010 00:18 01-SEP-2010 07:02  1
MA18         EMP133       01-SEP-2010 01:15 01-SEP-2010 00:32 01-SEP-2010 04:35  2
MA2          EMP62        01-SEP-2010 01:15 01-SEP-2010 01:14 01-SEP-2010 12:03  1
MA21         EMP235       01-SEP-2010 01:15 01-SEP-2010 00:05 01-SEP-2010 10:42  1
MA22         EMP4         01-SEP-2010 01:15 01-SEP-2010 00:01 01-SEP-2010 06:27  1
MA22         EMP300       01-SEP-2010 01:15 01-SEP-2010 01:12 01-SEP-2010 11:50  2
MA23         EMP135       01-SEP-2010 01:15 01-SEP-2010 00:35 01-SEP-2010 05:19  1
MA25         EMP35        01-SEP-2010 01:15 01-SEP-2010 00:20 01-SEP-2010 06:58  1
MA28         EMP298       01-SEP-2010 01:15 01-SEP-2010 00:52 01-SEP-2010 06:27  1
MA30         EMP72        01-SEP-2010 01:15 01-SEP-2010 00:56 01-SEP-2010 07:28  1
MA32         EMP84        01-SEP-2010 01:15 01-SEP-2010 01:00 01-SEP-2010 05:25  1
MA34         EMP299       01-SEP-2010 01:15 01-SEP-2010 00:31 01-SEP-2010 12:04  1
MA38         EMP268       01-SEP-2010 01:15 01-SEP-2010 00:31 01-SEP-2010 04:15  1
MA38         EMP278       01-SEP-2010 01:15 01-SEP-2010 00:32 01-SEP-2010 04:50  2
MA38         EMP176       01-SEP-2010 01:15 01-SEP-2010 01:01 01-SEP-2010 04:01  3
MA4          EMP257       01-SEP-2010 01:15 01-SEP-2010 00:10 01-SEP-2010 10:45  1
MA40         EMP231       01-SEP-2010 01:15 01-SEP-2010 00:58 01-SEP-2010 11:01  1
MA43         EMP65        01-SEP-2010 01:15 01-SEP-2010 00:54 01-SEP-2010 09:29  1
MA44         EMP18        01-SEP-2010 01:15 01-SEP-2010 00:07 01-SEP-2010 03:30  1
MA46         EMP36        01-SEP-2010 01:15 01-SEP-2010 00:40 01-SEP-2010 04:57  1
MA48         EMP61        01-SEP-2010 01:15 01-SEP-2010 00:27 01-SEP-2010 10:20  1
MA48         EMP169       01-SEP-2010 01:15 01-SEP-2010 00:44 01-SEP-2010 01:27  2
MA5          EMP147       01-SEP-2010 01:15 01-SEP-2010 00:02 01-SEP-2010 04:48  1
MA6          EMP132       01-SEP-2010 01:15 01-SEP-2010 00:34 01-SEP-2010 09:42  1

27 rows selected.

In some cases we have up to three employees working in a work area at 1:15AM (the time of day is indicated by the 1.25 value in the SQL statement).  Now, lets eliminate the duplicate rows, leaving just the rows where the calculated RN column is equal to 1.  We will join the above SQL statement in an inline view to the RESOURCE_LOCATION_DEMO table and convert the production work area coordinates to screen coordinates, in this case 96 pixels per unit (inches) at a 75% zoom percent (we made this same screen coordinate conversion in a previous SQL statement above):

SELECT
  RL.RESOURCE_ID,
  RL.DESCRIPTION,
  ROUND(RL.LOCATION_LEFT*96 *0.75) LOCATION_LEFT,
  ROUND(RL.LOCATION_TOP*96 *0.75) LOCATION_TOP,
  ROUND(RL.LOCATION_WIDTH*96 *0.75) LOCATION_WIDTH,
  ROUND(RL.LOCATION_HEIGHT*96 *0.75) LOCATION_HEIGHT,
  LT.EMPLOYEE_ID,
  LT.CLOCK_IN,
  LT.CLOCK_OUT
FROM
  RESOURCE_LOCATION_DEMO RL,
  (SELECT
    RESOURCE_ID,
    EMPLOYEE_ID,
    CLOCK_IN,
    CLOCK_OUT,
    ROW_NUMBER() OVER (PARTITION BY RESOURCE_ID ORDER BY CLOCK_IN) RN
  FROM
    LABOR_TICKET_DEMO
  WHERE
    CLOCK_IN<=TRUNC(SYSDATE) + (1.25)/24
    AND CLOCK_OUT>TRUNC(SYSDATE) + (1.25)/24) LT
WHERE
  RL.RESOURCE_ID=LT.RESOURCE_ID(+)
  AND NVL(LT.RN,1)=1
ORDER BY
  RL.RESOURCE_ID;

RESOURCE_ID  DESCRIPTION                    LOCATION_LEFT LOCATION_TOP LOCATION_WIDTH LOCATION_HEIGHT EMPLOYEE_ID  CLOCK_IN          CLOCK_OUT
------------ ------------------------------ ------------- ------------ -------------- --------------- ------------ ----------------- -----------------
MA1          Shop Floor Machine 1                     774          954              7              16
MA10         Shop Floor Machine 10                    558          954             86              54 EMP192       01-SEP-2010 00:21 01-SEP-2010 05:44
MA11         Shop Floor Machine 11                    882         1098             29               1
MA12         Shop Floor Machine 12                    234          378             51              51
MA13         Shop Floor Machine 13                    882         1314             83              62
MA14         Shop Floor Machine 14                    558          378             38              61
MA15         Shop Floor Machine 15                    774          522             63              64
MA16         Shop Floor Machine 16                    126          666            103              55 EMP114       01-SEP-2010 00:21 01-SEP-2010 04:48
MA17         Shop Floor Machine 17                    558          234             94              30
MA18         Shop Floor Machine 18                    342          450             85              21 EMP261       01-SEP-2010 00:18 01-SEP-2010 07:02
MA19         Shop Floor Machine 19                    342          666             94              20
MA2          Shop Floor Machine 2                     558         1314             88              34 EMP62        01-SEP-2010 01:14 01-SEP-2010 12:03
MA20         Shop Floor Machine 20                    666          162             33              33
MA21         Shop Floor Machine 21                    774          306             66              22 EMP235       01-SEP-2010 00:05 01-SEP-2010 10:42
MA22         Shop Floor Machine 22                    990          378             78              71 EMP4         01-SEP-2010 00:01 01-SEP-2010 06:27
MA23         Shop Floor Machine 23                    666          666             50              37 EMP135       01-SEP-2010 00:35 01-SEP-2010 05:19
MA24         Shop Floor Machine 24                    990          810            107              45
...

From the above output, we know the screen coordinates of each production work area (RESOURCE_ID) and the first employee to use the production work area (and the employee was still using it) at 1:15AM.

For the next portion of this blog article, the portion that requires an ASP enabled web server, we need a couple of pictures (these were created using Microsoft Power Point):

Representing a production work center that is in use:

Representing a production work center that is idle:

The factory floor – the background area:

——

Now we need the classic ASP page code – note that the code syntax is very similar to that of the previous VBScript examples.  The script uses Response.Write to write information to the client computer’s web browser, and an embedded Java script to call the post event of the embedded HTML form to update the display as of time every 30 seconds (yes, I should have used bind variables in the SQL statement, but that would have required an extra 120 seconds to code and would have left you with nothing to improve):

<html>

<head>
<meta http-equiv="refresh" content="600">
<title>Graphical Work Center Utilization Animated</title>
</head>

<body>
    <%
    Dim strSQL

    Dim sglOriginLeft
    Dim sglOriginTop

    Dim sglZoom
    Dim strZoom

    Dim i
    Dim intWidth
    Dim intHeight
    Dim strOffset
    Dim sglOffset
    Dim varDateTime

    Dim snpData
    Dim dbDatabase

    Set dbDatabase = Server.CreateObject("ADODB.Connection")

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDB"

    On Error Resume Next

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbDatabase.Open

    'Should verify that the connection attempt was successful, but I will leave that for someone else to code

    Set snpData = Server.CreateObject("ADODB.Recordset")

    'Retrieve the last value for the time offset and the selected zoom percent
    strOffset = Request.Form("txtOffset")
    strZoom = Request.Form("cboZoom")

    'Convert back to a number
    sglOffset = CSng(strOffset)
    sglZoom = CSng(strZoom) / 100

    'Advance to the next 0.25 hour
    if (sglOffset = 0) or (sglOffset = 24) then
        sglOffset = 0.25
    else
        sglOffset = sglOffset + 0.25
    end if

    'Set the zoom percent, if not already set
    If sglZoom = 0 Then
        sglZoom = 0.5 '50% zoom
    End If

    varDateTime = DateAdd("n", sglOffset*60, Date) 'Create a printable version of the view date and time

    Response.Write "<form name=" & chr(34) & "reportform" & chr(34) & " method=" & chr(34) & "POST" & chr(34) & " action=" & chr(34) & "GraphicalWorkCenterUtilization.asp" & chr(34) & ">" & vbcrlf
    Response.Write varDateTime & "  " & vbCrLf
    Response.Write "&nbsp;&nbsp;&nbsp;Zoom Percent <select size=""1"" id=""cboZoom"" name=""cboZoom"" style=""width:50"">" & vbCrLf
    For i = 10 to 200 Step 10
        If sglZoom = i / 100 Then
            Response.Write "<option selected value=""" & cStr(i) & """>" & cStr(i) & "</option>"
        Else
            Response.Write "<option value=""" & cStr(i) & """>" & cStr(i) & "</option>"
        End If
    Next
    Response.Write "</select><br>"
    Response.Write "  <input type=" & chr(34) & "submit" & chr(34) & " value=" & chr(34) & "Update View" & chr(34) & " name=" & chr(34) & "cmdViewReport" & chr(34) & ">" & vbcrlf
    Response.Write "  <input type=" & chr(34) & "hidden" & chr(34) & " name=" & chr(34) & "txtOffset" & chr(34) & " size=" & chr(34) & "5" & chr(34) & " value=" & chr(34) & cStr(sglOffset) & chr(34) & ">" & vbcrlf
    Response.Write "</form>" & vbcrlf

    'The background image
    intWidth = Round(16 * 96 * sglZoom)
    intHeight = Round(20 * 96 * sglZoom)
    Response.Write "<img src=" & chr(34) & "http://hoopercharles.files.wordpress.com/2010/09/graphicalworkcenterutilizationbackground.jpg" & chr(34) & " width=" & chr(34) & cstr(intWidth) & chr(34) & " height=" & chr(34) & cstr(intheight) & chr(34) & " style=" & chr(34) & "position:absolute;top:50px;left:0px;z-index:-1" & chr(34) & "/>" & vbcrlf

    'The SQL statement developed earlier
    strSQL = "SELECT" & VBCrLf
    strSQL = strSQL & "  RL.RESOURCE_ID," & VBCrLf
    strSQL = strSQL & "  RL.DESCRIPTION," & VBCrLf
    strSQL = strSQL & "  ROUND(RL.LOCATION_LEFT*96 *" & cStr(sglZoom) & ") LOCATION_LEFT," & VBCrLf
    strSQL = strSQL & "  ROUND(RL.LOCATION_TOP*96 *" & cStr(sglZoom) & ") LOCATION_TOP," & VBCrLf
    strSQL = strSQL & "  ROUND(RL.LOCATION_WIDTH*96 *" & cStr(sglZoom) & ") LOCATION_WIDTH," & VBCrLf
    strSQL = strSQL & "  ROUND(RL.LOCATION_HEIGHT*96 *" & cStr(sglZoom) & ") LOCATION_HEIGHT," & VBCrLf
    strSQL = strSQL & "  LT.EMPLOYEE_ID," & VBCrLf
    strSQL = strSQL & "  LT.CLOCK_IN," & VBCrLf
    strSQL = strSQL & "  LT.CLOCK_OUT" & VBCrLf
    strSQL = strSQL & "FROM" & VBCrLf
    strSQL = strSQL & "  RESOURCE_LOCATION_DEMO RL," & VBCrLf
    strSQL = strSQL & "  (SELECT" & VBCrLf
    strSQL = strSQL & "    RESOURCE_ID," & VBCrLf
    strSQL = strSQL & "    EMPLOYEE_ID," & VBCrLf
    strSQL = strSQL & "    CLOCK_IN," & VBCrLf
    strSQL = strSQL & "    CLOCK_OUT," & VBCrLf
    strSQL = strSQL & "    ROW_NUMBER() OVER (PARTITION BY RESOURCE_ID ORDER BY CLOCK_IN) RN" & VBCrLf
    strSQL = strSQL & "  FROM" & VBCrLf
    strSQL = strSQL & "    LABOR_TICKET_DEMO" & VBCrLf
    strSQL = strSQL & "  WHERE" & VBCrLf
    strSQL = strSQL & "    CLOCK_IN<=TRUNC(SYSDATE) + (" & cStr(sglOffset) & ")/24" & VBCrLf
    strSQL = strSQL & "    AND CLOCK_OUT>TRUNC(SYSDATE) + (" & cStr(sglOffset) & ")/24) LT" & VBCrLf
    strSQL = strSQL & "WHERE" & VBCrLf
    strSQL = strSQL & "  RL.RESOURCE_ID=LT.RESOURCE_ID(+)" & VBCrLf
    strSQL = strSQL & "  AND NVL(LT.RN,1)=1" & VBCrLf
    strSQL = strSQL & "ORDER BY" & VBCrLf
    strSQL = strSQL & "  RL.RESOURCE_ID"

    snpData.open strSQL, dbDatabase

    If snpData.State = 1 then
        Response.Write "<B><font color=" & chr(34) & "#0000FF" & chr(34) & "><p style=" & chr(34) & "position:absolute;top:15px;left:500px" & chr(34) & ">" & FormatDateTime(cdate(snpData("START_TIME")),2) & " " & FormatDateTime(cdate(snpData("START_TIME")),4) & " - " & FormatDateTime(cdate(snpData("END_TIME")),4) & "</p></font></b>" & vbcrlf

        Do While Not snpData.EOF
            If Not(IsNull(snpData("employee_id"))) Then
                'A labor ticket was in process during this time period
                Response.Write "<img alt=" & Chr(34) & snpData("resource_id") & "  " & snpData("description") & vbCrlf & snpData("employee_id") & "(" & snpData("clock_in") & " - " & snpData("clock_out") & ")" & Chr(34) & " src=" & chr(34) & "http://hoopercharles.files.wordpress.com/2010/09/graphicalworkcenterutilizationrunning.jpg" & chr(34) & " width=" & chr(34) & cStr(snpData("location_width")) & chr(34) & " height=" & chr(34) & cStr(snpData("location_height")) & chr(34) & " style=" & chr(34) & "position:absolute;top:" & cStr(cLng(snpData("location_top")) + 40) & "px;left:" & cStr(snpData("location_left")) & "px" & chr(34) & "/>" & vbcrlf
                'Write the title down 1 pixel
                Response.Write "<B><font color=" & chr(34) & "#00FFFF" & chr(34) & "><p style=" & chr(34) & "position:absolute;top:" & cStr(Round(41 + CSng(snpData("location_top")))) & "px;left:" & cStr(Round(CSng(snpData("location_left")))) & "px" & chr(34) & ">" & snpData("resource_id") & "</p></font></b>" & vbcrlf
            Else
                'No labor ticket was in process during this time period
                Response.Write "<img alt=" & Chr(34) & snpData("resource_id") & "  " & snpData("description") & Chr(34) & " src=" & chr(34) & "http://hoopercharles.files.wordpress.com/2010/09/graphicalworkcenterutilizationstopped.jpg" & chr(34) & " width=" & chr(34) & cStr(snpData("location_width")) & chr(34) & " height=" & chr(34) & cStr(snpData("location_height")) & chr(34) & " style=" & chr(34) & "position:absolute;top:" & cStr(cLng(snpData("location_top")) + 40) & "px;left:" & cStr(snpData("location_left")) & "px" & chr(34) & "/>" & vbcrlf
                'Write the title down 1 pixel
                Response.Write "<B><font color=" & chr(34) & "#FF0000" & chr(34) & "><p style=" & chr(34) & "position:absolute;top:" & cStr(Round(41 + CSng(snpData("location_top")))) & "px;left:" & cStr(Round(CSng(snpData("location_left")))) & "px" & chr(34) & ">" & snpData("resource_id") & "</p></font></b>" & vbcrlf
            End If
            snpData.movenext
        Loop
    End if

    snpData.Close
    dbDatabase.Close

    Set snpData = Nothing
    Set dbDatabase = Nothing
    %>

    <script language="JavaScript1.2">
    function NextInterval(){
      reportform.submit();
    }

      setTimeout("NextInterval()",30000)
    </script>
</body>

</html>

GraphicalWorkCenterUtilization.asp (save as GraphicalWorkCenterUtilization.asp on a web server that supports classic ASP pages)

Below are samples of the output as the display as of time advanced – the zoom percent was set to 50.  Notice that the work centers go online and offline as the time progresses (click a picture to see a larger version of that picture):

The final example demonstrates how the display changed when the zoom percent was changed from 50% to 130%:

-

As the mouse pointer is moved over the boxes representing the work centers, a pop-up tooltip appears that describes the work center, as well as employee ID, clock in time, and clock out time for the first labor ticket at that work center in the time period.

——-

Hopefully, you have found this example to be interesting.  Your assignment is to now connect proximity switches to the devices in your office and surrounding areas, recording their location in the RESOURCE_LOCATION_DEMO table.  Then log the proximity switch status to the LABOR_TICKET_DEMO table so that you are able to determine when the water cooler, coffee machine, chairs, keyboards, and computer mice are in use.  Use the data to determine which employees are the hardest working, and which employees have determined how to think smarter rather than working harder.  :-)





Oracle Logging Trigger Creator

25 08 2010

August 25, 2010

In a multi-user database environment it seems that sometimes changes just magically appear in ERP (or other) data.  The questions swirl, “Who changed that record?”; “I know that employee ABC arrived on time yesterday, why does the computer show he arrived late?”; “That new record is missing vital information, who created it?”, etc.  There are auditing features built into Oracle Database, and it is possible to review the redo log contents with LogMiner, but what if you just need a simple way to quietly record changes made to a table’s data?  Sure, triggers could be manually crafted to log changes, but it is a time consuming task, and consistency from one logging table/trigger to the next is difficult to maintain.  Well, this seems to be enough justification to develop a solution in search of a suitable problem, for which the solution will solve (we always invent the solution first, and then search for suitable problems that can be fixed, right?).

Today’s article will offer a VBS script that connects to an Oracle Database, and displays table definitions for all tables in the database using Internet Explorer as the script’s user interface.  For example, the following screen capture shows the tables in the TESTUSER schema with the definition of the LOCATIONS table displayed.  As shown, this table contains two VARCHAR2(15) columns in the primary key, a typical VARCHAR2(80) column, and a CHAR(1) column with a NOT NULL constraint.

—-

Switching to the PARTS table, we see that there is much more to the VBS script that automatically creates logging triggers and the table to maintain the captured data.  For each column in the table we are able to specify whether or not a change in a column’s value will cause a row to be inserted into the logging table – the “Trigger On Change” column in the VBS script’s user interface allows the user to specify the columns that cause the trigger to fire.  The “Log Values” column determines what data will be captured when the trigger fires:

  • Both New and Old (capture the prior values before the change and the values after the change)
  • New Values (capture only the values after the change)
  • Old Values (capture only the values before the change)
  • New Values and Delete (capture only the values after the change, unless the row is being deleted – then capture the values from before the change)
  • Old Values and Insert (capture only the values before the change, unless the row is being inserted – then capture the values from the insert statement)
  • Do Not Log (no data is captured for this column)

Clicking on the Create Data Log Script button builds the script which may be used to create the logging table and the logging trigger.  By default the logging table will be owned by the DATALOG user, although that can be changed within the VBS script.  The generated logging script displayed on the screen should look like this when the above selections are made:

(Added January 6, 2013: Sample trigger code shown above in a text file, save as TRIGGER_DATALOG_or112p_HPM_LOG_PARTS.SQL - without the .DOC extension.)

Notice that the generated script provides details about the purpose of the script, how to execute the script, what passwords need to be specified, sample commands to create a DATALOG tablespace and DATALOG user, the SQL to create the logging table, and the SQL to create the logging trigger.  By design, the default behavior of the script is easy to modify.

You can download the VBS script here: LoggingTrigger.vbs (save as LoggingTrigger.vbs). 

The script logic starts in the StartUp sub:

Sub StartUp()
    Dim strSQL
    Dim intResult
    Dim intFlag

    'Fire up Internet Explorer
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Left = 0
    objIE.Top = 0
    objIE.Width = 1100
    objIE.Height = 800
    objIE.StatusBar = True
    objIE.MenuBar = False
    objIE.Toolbar = False

    objIE.Navigate "about:blank"
    objIE.Document.Title = "Charles Hoopers Oracle Logging Trigger Creator"
    objIE.Visible = True

    Set dbDatabase = CreateObject("ADODB.Connection")
    Set snpData = CreateObject("ADODB.Recordset")
    Set comRetrieveTableNames = CreateObject("ADODB.Command")
    Set comRetrieveTableColumns = CreateObject("ADODB.Command")

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDB"

    On Error Resume Next

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbDatabase.Open

    'Should verify that the connection attempt was successful, but I will leave that for someone else to code

    strTableOwner = "TESTUSER"  'Set to the default owner name to be selected, possibly same as strUsername
    strDataLogSchema = "DATALOG"

As was the case for VBS scripts appearing in earlier articles that interact with the database, you must modify the database connection details, specifying username, password, and database.  Additionally, this script permits setting the default table schema (strTableOwner) and the default logging schema (strDataLogSchema) that display in the script’s user interface.  The script runs in a tight loop (sleeping for 0.5 seconds) in the StartUp sub until the script ends.  Within that loop the script reacts to changes in the Internet Explorer window, such as a selection of a different table name, or clicking the Create Data Log Script button.

When the user selects a different Table Owner the script executes the TableOwnerChange procedure which retrieves a list of the tables that reside in the selected user’s schema (note that this means the username used to run the script must have access to the various DBA_ views).  When a Table Name is selected, the TableNameChange procedure is executed which retrieves the definition for that table, and displays that definition in the Internet Explorer supplied user interface.  When the Create Data Log Script button is clicked the CreateLoggingScript procedure is executed, which writes the SQL*Plus compatible script to the root of the C:\ drive, and then displays the script on the screen.

If we run the script that was generated (after setting the correct passwords in the script), we should see something like this:

SQL> @C:\TRIGGER_DATALOG_or112p_HPM_LOG_PARTS.SQL
Connected.
DROP TABLE DATALOG.HPM_LOG_PARTS
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

Note that the script attempted to drop an old logging table named DATALOG.HPM_LOG_PARTS at the start of the script, and that is what triggered the ORA-00942 error – just ignore this error message.

The PARTS table in this database has a definition that looks like this (it was a table created in one of my scripts mentioned in this article Faulty Quotes 5 – Block Sizes):

CREATE TABLE PARTS (
  PART_ID VARCHAR2(30) NOT NULL ENABLE,
  DESCRIPTION VARCHAR2(40),
  STOCK_UM VARCHAR2(15) NOT NULL ENABLE,
  PLANNING_LEADTIME NUMBER DEFAULT 0 NOT NULL ENABLE,
  ORDER_POLICY CHAR(1) DEFAULT 'M' NOT NULL ENABLE,
  ORDER_POINT NUMBER(14,4),
  SAFETY_STOCK_QTY NUMBER(14,4),
  FIXED_ORDER_QTY NUMBER(14,4),
  DAYS_OF_SUPPLY NUMBER,
  MINIMUM_ORDER_QTY NUMBER(14,4),
  MAXIMUM_ORDER_QTY NUMBER(14,4),
  ENGINEERING_MSTR VARCHAR2(3),
  PRODUCT_CODE VARCHAR2(15),
  COMMODITY_CODE VARCHAR2(15),
  MFG_NAME VARCHAR2(30),
  MFG_PART_ID VARCHAR2(30),
  FABRICATED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  PURCHASED CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,
  STOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  DETAIL_ONLY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  DEMAND_HISTORY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  TOOL_OR_FIXTURE CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  INSPECTION_REQD CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  WEIGHT NUMBER(14,4),
  WEIGHT_UM VARCHAR2(15),
  DRAWING_ID VARCHAR2(15),
  DRAWING_REV_NO VARCHAR2(8),
  PREF_VENDOR_ID VARCHAR2(15),
  PRIMARY_WHS_ID VARCHAR2(15),
  PRIMARY_LOC_ID VARCHAR2(15),
  BACKFLUSH_WHS_ID VARCHAR2(15),
  BACKFLUSH_LOC_ID VARCHAR2(15),
  INSPECT_WHS_ID VARCHAR2(15),
  INSPECT_LOC_ID VARCHAR2(15),
  MRP_REQUIRED CHAR(1) DEFAULT 'N',
  MRP_EXCEPTIONS CHAR(1) DEFAULT 'N',
  PRIVATE_UM_CONV CHAR(1) DEFAULT 'N',
  AUTO_BACKFLUSH CHAR(1) DEFAULT 'Y',
  PLANNER_USER_ID VARCHAR2(20),
  BUYER_USER_ID VARCHAR2(20),
  ABC_CODE CHAR(1),
  ANNUAL_USAGE_QTY NUMBER(15,4),
  INVENTORY_LOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  UNIT_MATERIAL_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_LABOR_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_BURDEN_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_SERVICE_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
  BURDEN_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  PURC_BUR_PERCENT NUMBER(6,3) DEFAULT 0 NOT NULL ENABLE,
  PURC_BUR_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  UNIT_PRICE NUMBER(20,6),
  NEW_MATERIAL_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_LABOR_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_SERVICE_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_PERUNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  NEW_FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  MAT_GL_ACCT_ID VARCHAR2(30),
  LAB_GL_ACCT_ID VARCHAR2(30),
  BUR_GL_ACCT_ID VARCHAR2(30),
  SER_GL_ACCT_ID VARCHAR2(30),
  QTY_ON_HAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_AVAILABLE_ISS NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_AVAILABLE_MRP NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_ON_ORDER NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_IN_DEMAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  USER_1 VARCHAR2(80),
  USER_2 VARCHAR2(80),
  USER_3 VARCHAR2(80),
  USER_4 VARCHAR2(80),
  USER_5 VARCHAR2(80),
  USER_6 VARCHAR2(80),
  USER_7 VARCHAR2(80),
  USER_8 VARCHAR2(80),
  USER_9 VARCHAR2(80),
  USER_10 VARCHAR2(80),
  LT_PLUS_DAYS NUMBER,
  LT_MINUS_DAYS NUMBER,
  STATUS CHAR(1),
  USE_SUPPLY_BEF_LT CHAR(1),
  QTY_COMMITTED NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  PRT_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,
  PRT_CREATE_DATE DATE DEFAULT SYSDATE,
  CONSTRAINT "CHK_PART1" CHECK (
    (PLANNING_LEADTIME >= 0)
    AND (ORDER_POLICY = 'N'
      Or ORDER_POLICY = 'M'
      Or ORDER_POLICY = 'F'
      Or ORDER_POLICY = 'E'
      Or ORDER_POLICY = 'D'
      Or ORDER_POLICY = 'P')
    AND (ORDER_POINT >= 0)
    AND (SAFETY_STOCK_QTY >= 0)
    AND (FIXED_ORDER_QTY >= 0)
    AND (DAYS_OF_SUPPLY >= 0)
    AND (MINIMUM_ORDER_QTY >= 0)
    AND (MAXIMUM_ORDER_QTY >= 0)
    AND (FABRICATED = 'Y' Or FABRICATED = 'N')
    AND (PURCHASED = 'Y' Or PURCHASED = 'N')
    AND (STOCKED = 'Y' Or STOCKED = 'N')
    AND (DETAIL_ONLY = 'Y' Or DETAIL_ONLY = 'N')
    AND (DEMAND_HISTORY = 'Y' Or DEMAND_HISTORY = 'N')
    AND (TOOL_OR_FIXTURE = 'Y' Or TOOL_OR_FIXTURE = 'N')
    AND (MRP_REQUIRED = 'Y' Or MRP_REQUIRED = 'N')
    AND (MRP_EXCEPTIONS = 'Y' Or MRP_EXCEPTIONS = 'N')
    AND (PRIVATE_UM_CONV = 'Y' Or PRIVATE_UM_CONV = 'N')
    AND (INVENTORY_LOCKED = 'Y' Or INVENTORY_LOCKED = 'N')
    AND (INSPECTION_REQD = 'Y' Or INSPECTION_REQD = 'N')) ENABLE,
  PRIMARY KEY (PART_ID),
  CONSTRAINT "FKEY_INSP" FOREIGN KEY (INSPECT_WHS_ID, INSPECT_LOC_ID)
    REFERENCES LOCATIONS (WAREHOUSE_ID, LOCATION_ID) ENABLE,
  CONSTRAINT "FKEY_PREF_VENDOR" FOREIGN KEY (PREF_VENDOR_ID)
    REFERENCES VENDORS (VENDOR_ID) ENABLE,
  CONSTRAINT "FKEY_UM" FOREIGN KEY (WEIGHT_UM)
    REFERENCES UMS (UNIT_OF_MEASURE) ENABLE,
  CONSTRAINT "FKEY_STOCK_UM" FOREIGN KEY (STOCK_UM)
    REFERENCES UMS (UNIT_OF_MEASURE) ENABLE);

Yes, it is an ugly, long table definition that references a couple of additional tables.  Now, let’s make some changes to the above table:

INSERT INTO PARTS (
  PART_ID,
  DESCRIPTION,
  STOCK_UM,
  PLANNING_LEADTIME,
  ORDER_POLICY,
  FABRICATED,
  PURCHASED,
  STOCKED,
  DETAIL_ONLY,
  DEMAND_HISTORY,
  TOOL_OR_FIXTURE,
  INSPECTION_REQD,
  INVENTORY_LOCKED,
  UNIT_MATERIAL_COST,
  UNIT_LABOR_COST,
  UNIT_BURDEN_COST,
  UNIT_SERVICE_COST,
  BURDEN_PERCENT,
  BURDEN_PER_UNIT,
  PURC_BUR_PERCENT,
  PURC_BUR_PER_UNIT,
  FIXED_COST,
  UNIT_PRICE,
  NEW_MATERIAL_COST,
  NEW_LABOR_COST,
  NEW_BURDEN_COST,
  NEW_SERVICE_COST,
  NEW_BURDEN_PERCENT,
  NEW_BURDEN_PERUNIT,
  NEW_FIXED_COST,
  QTY_ON_HAND,
  QTY_AVAILABLE_ISS,
  QTY_AVAILABLE_MRP,
  QTY_ON_ORDER,
  QTY_IN_DEMAND,
  QTY_COMMITTED)
SELECT
  'AA'||ROWNUM,
  'WATCH ME',
  'EA',
  0,
  'N',
  'Y',
  'N',
  'N',
  'N',
  'N',
  'N',
  'N',
  'N',
  100,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  109,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0
FROM
  DUAL
CONNECT BY
  LEVEL<=5;

5 rows created.

The above added 5 rows to the PARTS table.  Now let’s modify those rows:

UPDATE
  PARTS
SET
  DESCRIPTION='CHANGED'
WHERE
  PART_ID IN ('AA1','AA2');

2 rows updated.

UPDATE
  PARTS
SET
  SAFETY_STOCK_QTY=10,
  STOCK_UM='PC',
  FIXED_ORDER_QTY=10
WHERE
  PART_ID IN ('AA1','AA4','A5');

2 rows updated.

UPDATE
  PARTS
SET
  SAFETY_STOCK_QTY=10,
  STOCK_UM='PC',
  FIXED_ORDER_QTY=10
WHERE
  PART_ID IN ('AA1','AA4','AA5');

3 rows updated.

DELETE FROM
  PARTS
WHERE
  PART_ID IN ('AA2','AA3','AA4','AA5');

4 rows deleted.

COMMIT;

SELECT
  COUNT(*)
FROM
  DATALOG.HPM_LOG_PARTS;

  COUNT(*)
----------
        14

As indicated by the above output, our logging table now has 14 rows of data.  Let’s view that data using a spreadsheet program (in this case Excel 2010):

Obviously, it is important to be smart when deciding which columns to capture, whether the old and/or new values should be captured, and changes to which columns should cause the trigger to fire.  So, what was the inspiration for this article?  One of the features in my Toy Project for Performance Tuning:

The VBS script should work on client computers running Windows 2000 or above.  Have fun with the script, and let me know about any improvements that you make to the script (there were a couple of neat improvements to the Automated DBMS XPLAN Viewer script).





Oracle Statistics Chart Viewer

11 07 2010

July 11, 2010

Last week I provided a blog article that created a horizonal orientation auto-scaling HTML-based bar chart using nothing more than a SQL statement.  At first glace the final SQL statement would likely appear to be very complicated, however if you follow the blog article from the start, the solution really is quite straightforward.  Today’s blog article will build on the final result of last week’s blog article, however the path to the final solution will be very different, and multiple vertical orientation bar charts will appear on a single web page.

The inspiration for today’s blog article comes from the Real Time Monitor in my Toy Project for Performance Tuning:

Since this project is a bit more advanced than last week’s project, we will use a programming language to perform the calculations and write out the HTML directly to a web browser.  We will use VBScript, which is available on most computers running Windows, so that will make it easy for you to modify the script for your needs:

  • Changing the statistics to be displayed
  • Changing the sampling frequency 
  • Changing the number of charts displayed per row and the number of rows
  • Changing the spacing between the charts
  • Changing the bar colors
  • Changing the background color
  • Adding grid lines

The actual script may be a little difficult to understand.  Basically, the script queries several views, displays the historical delta values of the specified statistics in chart form, and then waits for the sampling frequency counter to tick down before collecting the next set of statistics.  The statistics collection process ends when the user clicks the Close button.  The following views are queried every time the statistics are gathered:

  • V$OSSTAT
  • V$SYS_TIME_MODEL
  • V$WAITSTAT
  • V$SYSSTAT
  • V$SYSTEM_EVENT

The statistic names from some of the views are prefixed to reduce the chances of name collisions between statistics from the different views.  For example, the statistics from V$SYS_TIME_MODEL are prefixed with “SYS TIME MODEL ” and the statistics from V$WAITSTAT are prefixed with “WAITSTAT TIME “.  The current value is displayed in the left-most bar, with up to the previous 29 values displayed in the remaining bars.

Sample Charts:

Flat appearance with three horizontal and two vertical bar charts with the refresh interval set at 10 seconds:

-

Flat appearance with six horizontal and four vertical bar charts with the refresh interval set at 10 seconds:

-

3D appearance with three horizontal and two vertical bar charts with the refresh interval set at 10 seconds:

—–

So, what changes do you need to make to use the script?  First, if you are running a release of Oracle Database prior to 10.1 you will need to remove the queries of V$SYS_TIME_MODEL and V$OSSTAT.  Second, you need to have an Oracle user other than a SYSDBA user (SYS) that is able to query the various performance views.  Third, you need to specify the username and password of that user in the script:

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

Next, decide which statistics you would like to display in the charts and in what order (sequenced across the page and then down the page).  The statistic names are all recorded in uppercase, with the following as defaults in the script:

'The statistics to be displayed in the charts
strStatToChart(1) = "BUSY_TIME"
strStatToChart(2) = "CPU USED BY THIS SESSION"
strStatToChart(3) = "USER CALLS"
strStatToChart(4) = "SQL*NET ROUNDTRIPS TO/FROM CLIENT"
strStatToChart(5) = "USER COMMITS"
strStatToChart(6) = "USER ROLLBACKS"
strStatToChart(7) = "TRANSACTION ROLLBACKS"
strStatToChart(8) = "DB FILE SEQUENTIAL READ"
strStatToChart(9) = "DB FILE SCATTERED READ"
strStatToChart(10) = "READ BY OTHER SESSION"
strStatToChart(11) = "SYS TIME MODEL DB TIME"
strStatToChart(12) = "SYS TIME MODEL DB CPU"
strStatToChart(13) = "SYS TIME MODEL BACKGROUND CPU TIME"
strStatToChart(14) = "PARSE TIME CPU"
strStatToChart(15) = "RECURSIVE CPU USAGE"
strStatToChart(16) = "CONSISTENT GETS"
strStatToChart(17) = "DB BLOCK GETS"
strStatToChart(18) = "PHYSICAL READS CACHE"
strStatToChart(19) = "DATA BLOCKS CONSISTENT READS - UNDO RECORDS APPLIED"
strStatToChart(20) = "REDO SIZE"
strStatToChart(21) = "REDO WRITE TIME"
strStatToChart(30) = "WAITSTAT TIME DATA BLOCK"

Next, decide how the charts should appear on the page.  For example, for six charts, you might decide to use the following settings (the defaults)

'Large Charts
intChartsHorizontal = 3
intChartsVertical = 2
lngLeftMargin = 50
lngTopMargin = 50
lngChartSpacingX = 50
lngChartSpacingY = 50
lngChartWidth = 300
lngChartHeight = 300
strBar3DEffect = "border-style:outset;border-width:thin;"

For 24 charts, you might use the following settings (the defaults):

'Small Charts
intChartsHorizontal = 6
intChartsVertical = 4
lngLeftMargin = 50
lngTopMargin = 50
lngChartSpacingX = 10
lngChartSpacingY = 20
lngChartWidth = 150
lngChartHeight = 150
strBar3DEffect = "border-style:none;border-width:inherit;"

Finally, decide the color scheme of the charts, the update frequency, and whether delta values or absolute values should be displayed.  The following are the defaults:

strBarColorStandard = "#AAAAAA"
strBarColorCurrent = "#1010FF"
strBarTextColor = "#000000"
strChartBackgroundColor = "#EEEEEE"
strChart3DEffect = "border-style:inset;"
intShowDelta = True
intRefreshSeconds = 60 'Refresh interval of the chart data

I have found the book “CSS: The Definitive Guide” to be very helpful when adjusting the various appearance options.

Please post any improvements that you make to the script.  It might be interesting to note that this code can be incorporated into the Database Inspector Gadget that I created a while ago if the computer is running Windows Vista or Windows 7.

Script download: OracleStatisticChartViewer.vbs (save as OracleStatisticChartViewer.vbs)

—————————————————————–

Partially related side note: There are still a couple of seats remaining for the Michigan OakTable Symposium, which will certainly have the most entertaining and in-depth training sessions for DBAs and developers in 2010.  There are 20+ OakTable Network members from around the world converging on Ann Arbor, MI. in mid-September 2010.  I will be in attendance, but not presenting (Randolf Geist, who co-wrote two chapters in the Expert Oracle Practices book with me is flying in from Germany to present two sessions).  More information may be found here: Michigan OakTable Symposium.





Create an Auto-Scaling HTML Chart using Only SQL

8 07 2010

July 8, 2010 (Modified July 9, 2010)

I thought that I would try something a little different today – build an auto-scaling HTML bar chart using nothing more than a SQL statement.  I mentioned in this book review that I was impressed with the HTML chart that was included in the book, but I felt that it might be more interesting if the example used absolute positioning, rather than an HTML table.  So, I built an example using dynamic positioning that is not based on what appears in that book.

We will use the sample table from this blog article (side note: this is an interesting article that shows how a VBS script can generate a 10046 trace file, and then transform that trace file back into a VBS script), just with the table renamed to T1.

CREATE TABLE T1 AS
SELECT
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,5)),
          0,'MIKE',
          1,'ROB',
          2,'SAM',
          3,'JOE',
          4,'ERIC') EMPLOYEE_ID,
  TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE,
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)),
          0,'VAC',
          1,'HOL',
          2,'BEREAVE',
          3,'JURY',
          4,'ABS',
          5,'EXCUSE',
          6,'MIL',
          'OTHER') INDIRECT_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

COMMIT;

Now that we have 1000 rows in the sample table, let’s see how many entries fall into each week in the table (the week starts on a Monday) for those indirect entries that are either VAC, ABS, or EXCUSE:

SELECT
  NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
  COUNT(*) IND
FROM
  T1
WHERE
  INDIRECT_ID IN ('VAC','ABS','EXCUSE')
GROUP BY
  NEXT_DAY(SHIFT_DATE,'MONDAY')-7
ORDER BY
  1;

Your results of course will be different from what follows due to the randomization of the data, but this is what was returned from my database:

WEEK_OF   IND
--------- ---
08-OCT-07   1
15-OCT-07   2
22-OCT-07   4
29-OCT-07   3
05-NOV-07   3
03-DEC-07   2
10-DEC-07   3
24-DEC-07   2
...
05-JAN-09   1
12-JAN-09   3
19-JAN-09   7
02-FEB-09   1
...
21-JUN-10   3
28-JUN-10   2
05-JUL-10   2

The above SQL statement should work for the base query, now we need to start manipulating the data so that we are able to calculate the size and location of the bars in the chart.  We will slide the above SQL statement into an inline view:

SELECT
  WEEK_OF,
  IND,
  MAX(IND) OVER () MAX_IND,
  COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
  ROWNUM RN
FROM
  (SELECT
    NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
    COUNT(*) IND
  FROM
    T1
  WHERE
    INDIRECT_ID IN ('VAC','ABS','EXCUSE')
  GROUP BY
    NEXT_DAY(SHIFT_DATE,'MONDAY')-7
  ORDER BY
    1);

In addition to returning the original data from the SQL statement, we are now also returning the maximum data value, the total number of weeks with at least one entry, and a row counter:

WEEK_OF   IND MAX_IND COUNT_WEEK_OF    RN
--------- --- ------- ------------- -----
08-OCT-07   1       7           126     1
15-OCT-07   2       7           126     2
22-OCT-07   4       7           126     3
29-OCT-07   3       7           126     4
05-NOV-07   3       7           126     5
03-DEC-07   2       7           126     6
10-DEC-07   3       7           126     7
...
05-JAN-09   1       7           126    57
12-JAN-09   3       7           126    58
19-JAN-09   7       7           126    59
02-FEB-09   1       7           126    60
...
14-JUN-10   2       7           126   123
21-JUN-10   3       7           126   124
28-JUN-10   2       7           126   125
05-JUL-10   2       7           126   126

Next, we need to calculate the position and size of each of the bars in the chart, so we will again slide the above into an inline view:

SELECT
  WEEK_OF,
  IND,
  MAX_IND,
  COUNT_WEEK_OF,
  RN,
  TRUNC(300 * IND/MAX_IND) BAR_WIDTH,
  TRUNC(800 * 1/COUNT_WEEK_OF) BAR_HEIGHT,
  TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1)) BAR_TOP,
  100 BAR_LEFT
FROM
  (SELECT
    WEEK_OF,
    IND,
    MAX(IND) OVER () MAX_IND,
    COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
    ROWNUM RN
  FROM
    (SELECT
      NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
      COUNT(*) IND
    FROM
      T1
    WHERE
      INDIRECT_ID IN ('VAC','ABS','EXCUSE')
    GROUP BY
      NEXT_DAY(SHIFT_DATE,'MONDAY')-7
    ORDER BY
      1));

You might notice in the above that I specified that the maximum width of the chart will be 300 (pixels) and the maximum height will be 800 (pixels).  Here is the output:

WEEK_OF   IND MAX_IND COUNT_WEEK_OF    RN  BAR_WIDTH BAR_HEIGHT BAR_TOP BAR_LEFT
--------- --- ------- ------------- ----- ---------- ---------- ------- --------
08-OCT-07   1       7           126     1         42          6       0      100
15-OCT-07   2       7           126     2         85          6       6      100
22-OCT-07   4       7           126     3        171          6      12      100
29-OCT-07   3       7           126     4        128          6      19      100
05-NOV-07   3       7           126     5        128          6      25      100
03-DEC-07   2       7           126     6         85          6      31      100
10-DEC-07   3       7           126     7        128          6      38      100
24-DEC-07   2       7           126     8         85          6      44      100
...
05-JAN-09   1       7           126    57         42          6     355      100
12-JAN-09   3       7           126    58        128          6     361      100
19-JAN-09   7       7           126    59        300          6     368      100
02-FEB-09   1       7           126    60         42          6     374      100
...
14-JUN-10   2       7           126   123         85          6     774      100
21-JUN-10   3       7           126   124        128          6     780      100
28-JUN-10   2       7           126   125         85          6     787      100
05-JUL-10   2       7           126   126         85          6     793      100

Now what?  We need to convert the above into HTML using DIV tags to position the bars as calculated.  Prior to the first row we need to write a couple of HTML tags to set the page title, and after the last row we need to write a couple more HTML tags to close the BODY and HTML section of the document.  The transformed SQL statement looks like this:

SET TRIMSPOOL ON
SET LINESIZE 400
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET SQLPROMPT ''

SPOOL C:\CUSTOM_CHART.HTM

SELECT
  DECODE(RN,1,'<html><head><title>Custom Chart</title></head><body>' || CHR(13) || CHR(10),' ') ||
  '<div style="position:absolute;' ||
    'top:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1))) || 'px;' ||
    'left:' || TO_CHAR(5) || 'px;' ||
    'width:' || TO_CHAR(100) || 'px;' ||
    'height:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF)) || 'px;' ||
    '"><font size="1px" color="#0000FF">' || TO_CHAR(WEEK_OF,'MM/DD/YY') ||
      REPLACE('     ',' ',CHR(38) || 'nbsp;') || TO_CHAR(IND) || '</font></div>' ||
  '<div style="background:#444466;position:absolute;' ||
    'top:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1))) || 'px;' ||
    'left:' || TO_CHAR(100) || 'px;' ||
    'width:' || TO_CHAR(TRUNC(300 * IND/MAX_IND)) || 'px;' ||
    'height:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF)) || 'px;' ||
    '"><font size="1px" color="#FFFFFF"></font></div>' ||
  DECODE(RN,COUNT_WEEK_OF, CHR(13) || CHR(10) || '</body></html>',' ') HTML_LINE
FROM
  (SELECT
    WEEK_OF,
    IND,
    MAX(IND) OVER () MAX_IND,
    COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
    ROWNUM RN
  FROM
    (SELECT
      NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
      COUNT(*) IND
    FROM
      T1
    WHERE
      INDIRECT_ID IN ('VAC','ABS','EXCUSE')
    GROUP BY
      NEXT_DAY(SHIFT_DATE,'MONDAY')-7
    ORDER BY
      1));

SPOOL OFF

There is a slight problem with the above, the SQL statement and SPOOL OFF are printed in the resulting HTML file – if someone knows how to avoid that behavior (without placing the above into another script file), I would like to see how it is done (Oracle’s documentation did not help).

This is what the resulting HTML file looks like:

The number of result rows from the query was a bit high (126) so the bars are significantly compressed in height.  Just to see what happens, let’s add the following to the WHERE clause in the inner-most inline view:

AND SHIFT_DATE >= TO_DATE('01-JAN-2010','DD-MON-YYYY')

The resulting chart now looks like this:

Of course it is possible to adjust the colors of the font (#0000FF) and the bars (#444466), which are specified in hex in the format of RRGGBB (red green blue).  It is also possible to adjust the color of the bars to reflect the value represented by the bar, but that is an exercise for the reader.  For those who need to feel creative, it is also possible to display pictures in the bars, but that is also an exercise left for the reader.

—-

Edit: The sample output from the SQL statement displays correctly on Red Hat Enterprise Linux 3 using Firefox 0.8:





Working with Oracle’s Time Model Data 3

9 02 2010

February 9, 2010

(Back to the Previous Post in the Series)

 In the previous article in this series, we created a structured view of the V$SYS_TIME_MODEL view:
 

Then on demand brought in session level detail for the same statistics so that it was possible to investigate why a statistic value continued to grow.  The previous article also displayed delta values from V$OSSTAT and the wait events from V$SYSTEM_EVENT.  It appears that a couple people liked the previous effort, and I gave serious thought to having the computer provide audio feedback when a session started consuming a lot of resources – but then I started to wonder if maybe we should instead have an audio warning when a session is consuming less than the average resource consumption so that we could then determine who is reading this article rather than working. :-)  In the end, I decided to leave out the audio feedback.  So, what else can we do with the framework that was provided in the second article of this series?

It might be nice to be able to drill into session-level wait events to determine why a wait event’s time delta keeps growing, maybe by adding a couple of extra buttons to the web page that is created on the fly by the VBS script:

It might also be nice to be able to view the DBMS_XPLAN for a query that was identified as a SQL statement that was recently executed by a session.  One of the problems with the original script is that it only retrieved the SQL_ID and SQL_CHILD_NUMBER from V$SESSION, and it seemed that those columns contained NULL values just as frequently as they contained data.  The updated script will attempt to return the PREV_SQL_ID and PREV_CHILD_NUMBER columns if the other two columns contain NULL values.  In addition to retrieving the DBMS_XPLAN for the CHILD_NUMBER that is executed by the session, it might also be helpful to retrieve the DBMS_XPLAN for all child cursors for a given SQL_ID so that we are able to determine if the execution plan changed, and why it changed.  While we are at it, let’s make it possible to enable a 10046 extended SQL trace for a session just by clicking a button on the web page.  With the changes to the script, the generated web page might look something like this (note that this is a touched up image – the computer name and username were erased):

Scrolling down to the bottom of the page, note that the Session Waits button for the db file scattered read wait event was clicked:

 So, what happens if one of the XPlan buttons is clicked?  We could just write the execution plan to a text file, but where is the fun in doing that?  How about popping up another web browser page that lists all execution plans for a query that are currently in the library cache, the reasons for those generated execution plans from V$SQL_SHARED_CURSOR, and the bind variable definitions from V$SQL_BIND_METADATA.  The generated web page might look something like this (with duplicate sections from the different child cursors removed to save space):

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
LOAD_OPTIMIZER_STATS
**********
Bind Variable Definitions
  Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 1
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM    
WORK_ORDER WO,    REQUIREMENT R,    PART P  WHERE  
  WO.TYPE='W'    AND WO.SUB_ID='0'    AND WO.STATUS IN ('F','R','U')    
AND WO.DESIRED_QTY>WO.RECEIVED_QTY    AND WO.PART_ID= :1    AND 
R.WORKORDER_TYPE='W'    AND WO.TYPE=R.WORKORDER_TYPE    AND 
WO.BASE_ID=R.WORKORDER_BASE_ID    AND WO.LOT_ID=R.WORKORDER_LOT_ID    
AND WO.SPLIT_ID=R.WORKORDER_SPLIT_ID    AND R.SUBORD_WO_SUB_ID IS NULL  
  AND R.CALC_QTY>R.ISSUED_QTY    AND R.PART_ID=P.ID  ORDER BY    
R.PART_ID

Plan hash value: 3990487722

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |       |       |    27 (100)|          |        |
|   1 |  SORT UNIQUE                    |                 |     9 |   747 |    26   (4)| 00:00:01 |        |
|   2 |   NESTED LOOPS                  |                 |       |       |            |          |        |
|   3 |    NESTED LOOPS                 |                 |     9 |   747 |    25   (0)| 00:00:01 |        |
|   4 |     NESTED LOOPS                |                 |     9 |   621 |    16   (0)| 00:00:01 |        |
|*  5 |      TABLE ACCESS BY INDEX ROWID| WORK_ORDER      |     1 |    37 |    13   (0)| 00:00:01 |   OR11 |
|*  6 |       INDEX RANGE SCAN          | X_WORK_ORDER_1  |    21 |       |     3   (0)| 00:00:01 |   OR11 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| REQUIREMENT     |    25 |   800 |     3   (0)| 00:00:01 |   OR11 |
|*  8 |       INDEX RANGE SCAN          | X_REQUIREMENT_5 |     1 |       |     2   (0)| 00:00:01 |   OR11 |
|*  9 |     INDEX UNIQUE SCAN           | SYS_C0011459    |     1 |       |     0   (0)|          |   OR11 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | PART            |     1 |    14 |     1   (0)| 00:00:01 |   OR11 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("WO"."DESIRED_QTY">"WO"."RECEIVED_QTY" AND "WO"."SUB_ID"='0' AND 
              INTERNAL_FUNCTION("WO"."STATUS") AND "WO"."TYPE"='W'))
   6 - access("WO"."PART_ID"=:1)
   7 - filter("R"."CALC_QTY">"R"."ISSUED_QTY")
   8 - access("WO"."TYPE"="R"."WORKORDER_TYPE" AND "WO"."BASE_ID"="R"."WORKORDER_BASE_ID" AND 
              "WO"."LOT_ID"="R"."WORKORDER_LOT_ID" AND "WO"."SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND 
              "R"."SUBORD_WO_SUB_ID" IS NULL)
       filter("R"."WORKORDER_TYPE"='W')
   9 - access("R"."PART_ID"="P"."ID")

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
ROW_LEVEL_SEC_MISMATCH
**********
Bind Variable Definitions
  Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 2
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM    
...
Plan hash value: 3990487722

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |       |       |    27 (100)|          |        |
|   1 |  SORT UNIQUE                    |                 |     9 |   747 |    26   (4)| 00:00:01 |        |
|   2 |   NESTED LOOPS                  |                 |       |       |            |          |        |
|   3 |    NESTED LOOPS                 |                 |     9 |   747 |    25   (0)| 00:00:01 |        |
|   4 |     NESTED LOOPS                |                 |     9 |   621 |    16   (0)| 00:00:01 |        |
|*  5 |      TABLE ACCESS BY INDEX ROWID| WORK_ORDER      |     1 |    37 |    13   (0)| 00:00:01 |   OR11 |
|*  6 |       INDEX RANGE SCAN          | X_WORK_ORDER_1  |    21 |       |     3   (0)| 00:00:01 |   OR11 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| REQUIREMENT     |    25 |   800 |     3   (0)| 00:00:01 |   OR11 |
|*  8 |       INDEX RANGE SCAN          | X_REQUIREMENT_5 |     1 |       |     2   (0)| 00:00:01 |   OR11 |
|*  9 |     INDEX UNIQUE SCAN           | SYS_C0011459    |     1 |       |     0   (0)|          |   OR11 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | PART            |     1 |    14 |     1   (0)| 00:00:01 |   OR11 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("WO"."DESIRED_QTY">"WO"."RECEIVED_QTY" AND "WO"."SUB_ID"='0' AND 
              INTERNAL_FUNCTION("WO"."STATUS") AND "WO"."TYPE"='W'))
   6 - access("WO"."PART_ID"=:1)
   7 - filter("R"."CALC_QTY">"R"."ISSUED_QTY")
   8 - access("WO"."TYPE"="R"."WORKORDER_TYPE" AND "WO"."BASE_ID"="R"."WORKORDER_BASE_ID" AND 
              "WO"."LOT_ID"="R"."WORKORDER_LOT_ID" AND "WO"."SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND 
              "R"."SUBORD_WO_SUB_ID" IS NULL)
       filter("R"."WORKORDER_TYPE"='W')
   9 - access("R"."PART_ID"="P"."ID")

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
ROW_LEVEL_SEC_MISMATCH
**********
Bind Variable Definitions
  Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 3
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM    
...

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
LOAD_OPTIMIZER_STATS
**********
Bind Variable Definitions
  Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 4
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM    
...

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
ROW_LEVEL_SEC_MISMATCH
**********
Bind Variable Definitions
  Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 5
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM    
...
 
**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
ROW_LEVEL_SEC_MISMATCH
**********
Bind Variable Definitions
  Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 7
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM    
...
Plan hash value: 1336181825

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |   577 (100)|          |        |
|   1 |  SORT UNIQUE                          |                 |   838 | 69554 |   576   (1)| 00:00:07 |        |
|*  2 |   HASH JOIN                           |                 |   838 | 69554 |   575   (1)| 00:00:07 |        |
|   3 |    NESTED LOOPS                       |                 |       |       |            |          |        |
|   4 |     NESTED LOOPS                      |                 |   840 | 57960 |   164   (0)| 00:00:02 |        |
|*  5 |      TABLE ACCESS BY INDEX ROWID      | WORK_ORDER      |    35 |  1295 |    60   (0)| 00:00:01 |   OR11 |
|   6 |       BITMAP CONVERSION TO ROWIDS     |                 |       |       |            |          |        |
|   7 |        BITMAP AND                     |                 |       |       |            |          |        |
|   8 |         BITMAP CONVERSION FROM ROWIDS |                 |       |       |            |          |        |
|*  9 |          INDEX RANGE SCAN             | X_WORK_ORDER_1  | 13721 |       |     8   (0)| 00:00:01 |   OR11 |
|  10 |         BITMAP OR                     |                 |       |       |            |          |        |
|  11 |          BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |        |
|* 12 |           INDEX RANGE SCAN            | X_WORK_ORDER_2  | 13721 |       |     6   (0)| 00:00:01 |   OR11 |
|  13 |          BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |        |
|* 14 |           INDEX RANGE SCAN            | X_WORK_ORDER_2  | 13721 |       |     1   (0)| 00:00:01 |   OR11 |
|  15 |          BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |        |
|* 16 |           INDEX RANGE SCAN            | X_WORK_ORDER_2  | 13721 |       |    19   (0)| 00:00:01 |   OR11 |
|* 17 |      INDEX RANGE SCAN                 | X_REQUIREMENT_5 |     1 |       |     2   (0)| 00:00:01 |   OR11 |
|* 18 |     TABLE ACCESS BY INDEX ROWID       | REQUIREMENT     |    24 |   768 |     3   (0)| 00:00:01 |   OR11 |
|  19 |    TABLE ACCESS FULL                  | PART            | 37032 |   506K|   410   (1)| 00:00:05 |   OR11 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("R"."PART_ID"="P"."ID")
   5 - filter(("WO"."DESIRED_QTY">"WO"."RECEIVED_QTY" AND "WO"."SUB_ID"='0' AND "WO"."TYPE"='W'))
   9 - access("WO"."PART_ID"=:1)
  12 - access("WO"."STATUS"='F')
  14 - access("WO"."STATUS"='R')
  16 - access("WO"."STATUS"='U')
  17 - access("WO"."TYPE"="R"."WORKORDER_TYPE" AND "WO"."BASE_ID"="R"."WORKORDER_BASE_ID" AND 
              "WO"."LOT_ID"="R"."WORKORDER_LOT_ID" AND "WO"."SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND 
              "R"."SUBORD_WO_SUB_ID" IS NULL)
       filter("R"."WORKORDER_TYPE"='W')
  18 - filter("R"."CALC_QTY">"R"."ISSUED_QTY")

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
**********
Bind Variable Definitions
  Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 8
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM    
SYSADM.WORK_ORDER WO,    SYSADM.REQUIREMENT R,    SYSADM.PART P  WHERE  
  WO.TYPE='W'    AND WO.SUB_ID='0'    AND WO.STATUS IN ('F','R','U')    
AND WO.DESIRED_QTY>WO.RECEIVED_QTY    AND WO.PART_ID= :1    AND 
R.WORKORDER_TYPE='W'    AND WO.TYPE=R.WORKORDER_TYPE    AND 
WO.BASE_ID=R.WORKORDER_BASE_ID    AND WO.LOT_ID=R.WORKORDER_LOT_ID    
AND WO.SPLIT_ID=R.WORKORDER_SPLIT_ID    AND R.SUBORD_WO_SUB_ID IS NULL  
  AND R.CALC_QTY>R.ISSUED_QTY    AND R.PART_ID=P.ID  ORDER BY    
R.PART_ID

Plan hash value: 3990487722

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |       |       |    27 (100)|          |        |
|   1 |  SORT UNIQUE                    |                 |     9 |   747 |    26   (4)| 00:00:01 |        |
|   2 |   NESTED LOOPS                  |                 |       |       |            |          |        |
|   3 |    NESTED LOOPS                 |                 |     9 |   747 |    25   (0)| 00:00:01 |        |
|   4 |     NESTED LOOPS                |                 |     9 |   621 |    16   (0)| 00:00:01 |        |
|*  5 |      TABLE ACCESS BY INDEX ROWID| WORK_ORDER      |     1 |    37 |    13   (0)| 00:00:01 |   OR11 |
|*  6 |       INDEX RANGE SCAN          | X_WORK_ORDER_1  |    21 |       |     3   (0)| 00:00:01 |   OR11 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| REQUIREMENT     |    24 |   768 |     3   (0)| 00:00:01 |   OR11 |
|*  8 |       INDEX RANGE SCAN          | X_REQUIREMENT_5 |     1 |       |     2   (0)| 00:00:01 |   OR11 |
|*  9 |     INDEX UNIQUE SCAN           | SYS_C0011459    |     1 |       |     0   (0)|          |   OR11 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | PART            |     1 |    14 |     1   (0)| 00:00:01 |   OR11 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("WO"."DESIRED_QTY">"WO"."RECEIVED_QTY" AND "WO"."SUB_ID"='0' AND 
              INTERNAL_FUNCTION("WO"."STATUS") AND "WO"."TYPE"='W'))
   6 - access("WO"."PART_ID"=:1)
   7 - filter("R"."CALC_QTY">"R"."ISSUED_QTY")
   8 - access("WO"."TYPE"="R"."WORKORDER_TYPE" AND "WO"."BASE_ID"="R"."WORKORDER_BASE_ID" AND 
              "WO"."LOT_ID"="R"."WORKORDER_LOT_ID" AND "WO"."SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND 
              "R"."SUBORD_WO_SUB_ID" IS NULL)
       filter("R"."WORKORDER_TYPE"='W')
   9 - access("R"."PART_ID"="P"."ID")

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
LOAD_OPTIMIZER_STATS
**********
Bind Variable Definitions
  Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 9
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM    
...
**********************************************************************************************

In the above, the selected session was actually using the execution plan for child number 3.  Notice that one of the execution plans converted normal B*Tree indexes on the fly to permit comparisons much like what are possible with bitmap indexes.  A large portion of the execution plans were created, at least in part, because of a bind mismatch – see the documentation V$SQL_SHARED_CURSOR for more information.

This example requires at a minimum Oracle Database 10.1.0.1 running on Windows, Unix, or Linux.  The supplied script must be run from a Windows client computer.  You will need to specify a suitable username, password, and database name (from tnsnames.ora) for the strUsername, strPassword, and strDatabase variables in the VBS script.  This script ties into several of the chapters in the Expert Oracle Practices book.

Download the script from here: MonitorDatabaseTimeModel.vbs (save as MonitorDatabaseTimeModel.vbs, without the .doc extension – note that this is version 2.0.1)





Submit Input to an ASP Web Page and Retrieve the Result using VBS

17 01 2010

January 17, 2010

While it is helpful that SQL statements may be submitted directly from VBS scripts, in most cases the username and password for the database user will be exposed in the VBS script.  So, we need another way.  How about having the VBS script pass a value of interest to an ASP web page (note that this is not ASP.Net, this is old style programming without a Net).  First, we need to create the ASP web page (after, of course, enabling ASP in Microsoft’s IIS web server configuration):

<html>
<head>
<title>I am a Hidden Web Page - You will Not See Me</title>
</head>
<body>
   <%
    Dim adVarChar
    Dim adParamInput
    Dim adCmdText
    Dim strSQL
    Dim snpData
    Dim comData
    Dim strPartID

    Dim dbDatabase

    adVarChar = 200
    adParamInput = 1
    adCmdText = 1
    Set dbDatabase = Server.CreateObject("ADODB.Connection")
    Set comData = Server.CreateObject("ADODB.Command")
    Set snpData = Server.CreateObject("ADODB.Recordset")

    On Error Resume Next

    strPartID = cStr(Request("strPartID"))

    dbDatabase.Open "Provider=MSDAORA.1;User ID=MyUser;Password=MyPassword;Data Source=MyDB;Persist Security Info=True"

    With ComData
        strSQL = "SELECT /*+ LEADING(IT) INDEX(IT X_INV_TRANS_1) */" & vbCrLf
        strSQL = strSQL & "  IT.PART_ID," & vbCrLf
        strSQL = strSQL & "  TRUNC(SUM(DECODE(IT.CLASS,'I',DECODE(IT.TYPE,'O',IT.QTY,0),0))-SUM(DECODE(IT.CLASS,'I',DECODE(IT.TYPE,'I',IT.QTY,0),0))+.9999) AS NEW_ANNUAL_USAGE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  INVENTORY_TRANS IT," & vbCrLf
        strSQL = strSQL & "  PART P" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  IT.TRANSACTION_DATE>TRUNC(SYSDATE-365)" & vbCrLf
        strSQL = strSQL & "  AND P.ID=IT.PART_ID" & vbCrLf
        strSQL = strSQL & "  AND P.ID= ?" & vbCrLf
        strSQL = strSQL & "GROUP BY" & vbCrLf
        strSQL = strSQL & "  IT.PART_ID" & vbCrLf
        '
        .Parameters.Append .CreateParameter("part_id", adVarChar, adParamInput, 30, strPartID)
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase
    End With
    Set snpData = ComData.Execute

    Response.Write "<input type=""text"" name=""txtPartID"" size=30 value=""" & strPartID & """ disabled=true>"
    If Not (snpData Is Nothing) Then
        If Not(snpData.EOF) Then
            Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""" & cstr(snpData("new_annual_usage")) & """ disabled=true>"
            Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""RETRIEVED"" disabled=true>"
        Else
            Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""0"" disabled=true>"
            Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""NO TRANSACTIONS"" disabled=true>"
        End If
    Else
        Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""0"" disabled=true>"
        Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""ERROR"" disabled=true>"
    End If

    snpData.Close
    dbDatabase.Close

    Set snpData = Nothing
    Set comData = Nothing
    Set dbDatabase = Nothing
    %>
</body>
</html>

OK, reading the ASP web page code, we create an ADO database connection object, an ADO recordset object, and an ADO command object.  Next, we set the strPartID variable to the value of the passed in strPartID variable from the web session, build a SQL statement with a bind variable set to the value of the strPartID variable, and then execute the SQL statement.  If the SQL statement successfully executed, we build two HTML text box, the first with the value of NEW_ANNUAL_USAGE, and the second with a status of either RETRIEVED or NO TRANSACTIONS.  If the SQL statement failed to execute, the two HTML text boxes will contain 0 and ERROR.

Now for the VBS script that will call the ASP web page:

Dim intResult
Dim objIE
Dim strHTML
Dim strID
Dim ANNUAL_USAGE_QTY

On Error Resume Next

Set objIE = CreateObject("InternetExplorer.Application")

strID = "ABCDEF123456"
ANNUAL_USAGE_QTY = 100

objIE.Navigate "http://localhost/Update_Annual_Usage_Qty.asp?strPartID=" & strID

objIE.Width=100
objIE.Height=100
objIE.Statusbar=False
objIE.Menubar=False
objIE.Toolbar=False
objIE.Visible = False

Do While objIE.Busy <> False
    WScript.Sleep 200
Loop

'loop until the button is clicked
Do While intFlag = 0
    If Err <> 0 Then
        IntFlag = -1
    End If   
    If objIE is Nothing Then
        'User closed ID
        intFlag = -1
    Else
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    WScript.Sleep 200
Loop

If intFlag = 1 Then
    If objIE.Document.Body.All.txtOK.Value = "ERROR" Then
        MsgBox "Error sending the query to the database"
    Else
        If objIE.Document.Body.All.txtOK.Value = "NO TRANSACTIONS" Then
            intResult = MsgBox ("No transactions for this part in the last year, OK to set the annual usage qty to 0?  The old value is " & cStr(ANNUAL_USAGE_QTY), vbQuestion + vbYesNo, "Annual Usage")
            If intResult = vbYes Then
                ANNUAL_USAGE_QTY = 0
            End If
        Else
            'Copy in the values from the web page
            intResult = MsgBox ("The old annual usage quantity value is " & cStr(ANNUAL_USAGE_QTY) & " - the database indicates that the updated quantity should be " & cstr(objIE.Document.Body.All.txtAnnualUsage.Value) & ".  Would you like to update the annual usage quantity?", vbQuestion + vbYesNo,"Annual Usage")
            If intResult = vbYes Then
                 ANNUAL_USAGE_QTY = objIE.Document.Body.All.txtAnnualUsage.Value
            End If
        End If
    End If
    objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing

The VBS script launches the ASP page in a hidden Internet Explorer window, passing in the value of strID on the address line (this is picked up in the ASP script as the strPartID session variable).  The VBS script then waits until the ASP page finishes loading.  Once the ASP page finishes, the VBS script reads the values of the two HTML text boxes and acts appropriately based on the values of those text boxes.

The neat thing about straight ASP programming code is that it looks a lot like the VBS programming code, and that looks a lot like the Excel macro programming code, and that looks a lot like the classic Visual Basic programming code, and that kind of looks like the classic BASIC programming code that I started working with in 1981/1982.  I have been sitting in on the technology training advisory committee for one of the local colleges.  The committee helps determine what computer classes will be taught to earn a degree at the college.  The question was asked what languages to teach – I heard C++ and Java being suggested… I wonder if I should have suggested Visual Basic?  VBS like languages are also used as macro languages in some ERP products and other packages (I believe that AutoCAD uses a similar macro syntax, as does PC-DMIS).





Working with Oracle’s Time Model Data 2

14 01 2010

January 14, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

So, how is it possible to transform this:

Into something like this:

A fairly long VBS script connects to the Oracle database, performs a lot of calculations on the data returned from the database, and then outputs the formatted result to a web page in Internet Explorer.  The VBS script continues to control the web page once the page is built, automatically refreshing the web page after a specified number of seconds, and responding to button clicks on the web page.  Easy, right?  Because this is done using a VBS script, the client computer must be running on Windows, while the server may run Unix, Linux, or Windows (I suggest not running this script directly on the server, instead run it from another computer).  The Show Detail button acts like a toggle to either show or hide the session details (on the yellow lines) that contributed to the system-wide statistic values (by default, the sessions must contribute to at least 10% of the total to be included in the session-level output).  Clicking the Re-Query button causes the script to update the page with the latest statistic delta values prior to the automatic refresh timer expiring.

So, where is the code?  Note that there may be bugs in the code – don’t step on them.  Also, the code is mostly written in a very verbose syntax so that it is easy to follow along with the script logic.

Const adCmdText = 1

Dim i
Dim j
Dim k
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase
Dim intCheckIterations              'Number of times to check the instances
Dim intDelayIterations              'Number of seconds to delay between iterations
Dim sglSessionMinimumPercent        'Minimum percent of the total required for the session to be included in the report detail
Dim dteLastLoopStart                'Time of the last loop start
Dim intDataChanged                  'Indicates whether or not the data to be displayed on the web page has changed
Dim intDisplaySessionDetail         'Indicates whether or not to display the session level detail
Dim snpDataWait                     'ADO recordset used to query V$SYSTEM_EVENT
Dim comDataWait                     'ADO command object used to retrieve data from V$SYSTEM_EVENT
Dim snpDataOSStat                   'ADO recordset used to query V$OSSTAT
Dim comDataOSStat                   'ADO command object used to retrieve data from V$OSSTAT
Dim snpDataSysTime                  'ADO recordset used to query V$SYS_TIME_MODEL
Dim comDataSysTime                  'ADO command object used to retrieve from V$SYS_TIME_MODEL
Dim snpDataSessTime                 'ADO recordset used to query V$SESS_TIME_MODEL
Dim comDataSessTime                 'ADO command object used to retrieve from V$SESS_TIME_MODEL
Dim dbDatabase                      'ADO database connection object

Dim strHTML                         'The raw HTML for the web page
Dim objIE                           'The Internet Explorer object
Dim strInd                          'Indent characters for the table
Dim intFlag                         'Loop control variable, allow to jump out of the loop early

Dim intNumCPUs                      'Number of CPUs
Dim dblIdleTime                     'Current value of idle time from V$OSSTAT
Dim dblBusyTime                     'Current value of busy time from V$OSSTAT
Dim dblUserTime                     'Current value of user time from V$OSSTAT
Dim dblSysTime                      'Current value of system/kernel mode time from V$OSSTAT
Dim dblIdleTimeLast                 'Previous value of idle time from V$OSSTAT
Dim dblBusyTimeLast                 'Previous value of busy time from V$OSSTAT
Dim dblUserTimeLast                 'Previous value of user time from V$OSSTAT
Dim dblSysTimeLast                  'Previous value of system/kernel mode time from V$OSSTAT

Dim dblDBCPU                        'Current value of DB CPU from V$SYS_TIME_MODEL
Dim dblDBTime                       'Current value of DB time from V$SYS_TIME_MODEL
Dim dblJavaTime                     'Current value of Java execution elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLCompile                 'Current value of PL/SQL compilation elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLExecution               'Current value of PL/SQL execution elapsed time from V$SYS_TIME_MODEL
Dim dblRMANCPU                      'Current value of RMAN cpu time (backup/restore) from V$SYS_TIME_MODEL
Dim dblBackgroundCPU                'Current value of background cpu time from V$SYS_TIME_MODEL
Dim dblBackgroundElapsed            'Current value of background elapsed time from V$SYS_TIME_MODEL
Dim dblConnectMgmt                  'Current value of connection management call elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseMemory            'Current value of failed parse (out of shared memory) elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseElapsed           'Current value of failed parse elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseBind                'Current value of hard parse (bind mismatch) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseSharing             'Current value of hard parse (sharing criteria) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseElapsed             'Current value of hard parse elapsed time from V$SYS_TIME_MODEL
Dim dblInboundPLSQL                 'Current value of inbound PL/SQL rpc elapsed time from V$SYS_TIME_MODEL
Dim dblParseTimeElapsed             'Current value of parse time elapsed from V$SYS_TIME_MODEL
Dim dblRepeatedBind                 'Current value of repeated bind elapsed time from V$SYS_TIME_MODEL
Dim dblSequenceLoad                 'Current value of sequence load elapsed time from V$SYS_TIME_MODEL
Dim dblSQLExecuteTime               'Current value of sql execute elapsed time from V$SYS_TIME_MODEL

Dim dblDBCPULast                    'Last value of DB CPU from V$SYS_TIME_MODEL
Dim dblDBTimeLast                   'Last value of DB time from V$SYS_TIME_MODEL
Dim dblJavaTimeLast                 'Last value of Java execution elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLCompileLast             'Last value of PL/SQL compilation elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLExecutionLast           'Last value of PL/SQL execution elapsed time from V$SYS_TIME_MODEL
Dim dblRMANCPULast                  'Last value of RMAN cpu time (backup/restore) from V$SYS_TIME_MODEL
Dim dblBackgroundCPULast            'Last value of background cpu time from V$SYS_TIME_MODEL
Dim dblBackgroundElapsedLast        'Last value of background elapsed time from V$SYS_TIME_MODEL
Dim dblConnectMgmtLast              'Last value of connection management call elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseMemoryLast        'Last value of failed parse (out of shared memory) elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseElapsedLast       'Last value of failed parse elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseBindLast            'Last value of hard parse (bind mismatch) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseSharingLast         'Last value of hard parse (sharing criteria) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseElapsedLast         'Last value of hard parse elapsed time from V$SYS_TIME_MODEL
Dim dblInboundPLSQLLast             'Last value of inbound PL/SQL rpc elapsed time from V$SYS_TIME_MODEL
Dim dblParseTimeElapsedLast         'Last value of parse time elapsed from V$SYS_TIME_MODEL
Dim dblRepeatedBindLast             'Last value of repeated bind elapsed time from V$SYS_TIME_MODEL
Dim dblSequenceLoadLast             'Last value of sequence load elapsed time from V$SYS_TIME_MODEL
Dim dblSQLExecuteTimeLast           'Last value of sql execute elapsed time from V$SYS_TIME_MODEL

Dim intSessionCount                 'Number of sessions logged
Dim intSessionCurrent               'Index of the current session
Dim lngSIDLast                      'SID for the previous row from the database
Dim lngSerialLast                   'SERIAL# for the previous row
Dim intSessionExists(999)           'Used to determine if the session is still found in the system
Dim lngSID(999)                     'SID for session
Dim lngSerial(999)                  'SERIAL# for the session
Dim strSessionOther(999)            'USERNAME, MACHINE, PROGRAM
Dim dblDBCPUS(999)                  'Current value of DB CPU from V$SESS_TIME_MODEL
Dim dblDBTimeS(999)                 'Current value of DB time from V$SESS_TIME_MODEL
Dim dblJavaTimeS(999)               'Current value of Java execution elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLCompileS(999)           'Current value of PL/SQL compilation elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLExecutionS(999)         'Current value of PL/SQL execution elapsed time from V$SESS_TIME_MODEL
Dim dblRMANCPUS(999)                'Current value of RMAN cpu time (backup/restore) from V$SESS_TIME_MODEL
Dim dblBackgroundCPUS(999)          'Current value of background cpu time from V$SESS_TIME_MODEL
Dim dblBackgroundElapsedS(999)      'Current value of background elapsed time from V$SESS_TIME_MODEL
Dim dblConnectMgmtS(999)            'Current value of connection management call elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseMemoryS(999)      'Current value of failed parse (out of shared memory) elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseElapsedS(999)     'Current value of failed parse elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseBindS(999)          'Current value of hard parse (bind mismatch) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseSharingS(999)       'Current value of hard parse (sharing criteria) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseElapsedS(999)       'Current value of hard parse elapsed time from V$SESS_TIME_MODEL
Dim dblInboundPLSQLS(999)           'Current value of inbound PL/SQL rpc elapsed time from V$SESS_TIME_MODEL
Dim dblParseTimeElapsedS(999)       'Current value of parse time elapsed from V$SESS_TIME_MODEL
Dim dblRepeatedBindS(999)           'Current value of repeated bind elapsed time from V$SESS_TIME_MODEL
Dim dblSequenceLoadS(999)           'Current value of sequence load elapsed time from V$SESS_TIME_MODEL
Dim dblSQLExecuteTimeS(999)         'Current value of sql execute elapsed time from V$SESS_TIME_MODEL

Dim dblDBCPUSLast(999)              'Last value of DB CPU from V$SESS_TIME_MODEL
Dim dblDBTimeSLast(999)             'Last value of DB time from V$SESS_TIME_MODEL
Dim dblJavaTimeSLast(999)           'Last value of Java execution elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLCompileSLast(999)       'Last value of PL/SQL compilation elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLExecutionSLast(999)     'Last value of PL/SQL execution elapsed time from V$SESS_TIME_MODEL
Dim dblRMANCPUSLast(999)            'Last value of RMAN cpu time (backup/restore) from V$SESS_TIME_MODEL
Dim dblBackgroundCPUSLast(999)      'Last value of background cpu time from V$SESS_TIME_MODEL
Dim dblBackgroundElapsedSLast(999)  'Last value of background elapsed time from V$SESS_TIME_MODEL
Dim dblConnectMgmtSLast(999)        'Last value of connection management call elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseMemorySLast(999)  'Last value of failed parse (out of shared memory) elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseElapsedSLast(999) 'Last value of failed parse elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseBindSLast(999)      'Last value of hard parse (bind mismatch) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseSharingSLast(999)   'Last value of hard parse (sharing criteria) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseElapsedSLast(999)   'Last value of hard parse elapsed time from V$SESS_TIME_MODEL
Dim dblInboundPLSQLSLast(999)       'Last value of inbound PL/SQL rpc elapsed time from V$SESS_TIME_MODEL
Dim dblParseTimeElapsedSLast(999)   'Last value of parse time elapsed from V$SESS_TIME_MODEL
Dim dblRepeatedBindSLast(999)       'Last value of repeated bind elapsed time from V$SESS_TIME_MODEL
Dim dblSequenceLoadSLast(999)       'Last value of sequence load elapsed time from V$SESS_TIME_MODEL
Dim dblSQLExecuteTimeSLast(999)     'Last value of sql execute elapsed time from V$SESS_TIME_MODEL

Dim intWaitCount                    'Number of wait events read from the database
Dim intWaitCurrent                  'Current index of the wait event
Dim strWaitEventName(1300)          'Name of the wait event
Dim dblWaitValue(1300)              'Current wait event total time
Dim dblWaitValueLast(1300)          'Previous wait event total time
Dim dblWaitWaitsValue(1300)         'Current wait event number of waits
Dim dblWaitWaitsValueLast(1300)     'Previous wait event number of waits
Dim dblWaitTOValue(1300)            'Current wait event number of timeouts
Dim dblWaitTOValueLast(1300)        'Previous wait event number of timeouts

Set snpDataWait = CreateObject("ADODB.Recordset")
Set comDataWait = CreateObject("ADODB.Command")
Set snpDataOSStat = CreateObject("ADODB.Recordset")
Set comDataOSStat = CreateObject("ADODB.Command")
Set snpDataSysTime = CreateObject("ADODB.Recordset")
Set comDataSysTime = CreateObject("ADODB.Command")
Set snpDataSessTime = CreateObject("ADODB.Recordset")
Set comDataSessTime = CreateObject("ADODB.Command")

Set dbDatabase = CreateObject("ADODB.Connection")

strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

intCheckIterations = 20
intDelayIterations = 60
sglSessionMinimumPercent = 0.1  '10% of the total for the time period needed to be inccluded in the detail
strInd = "&nbsp;&nbsp;"

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open

'Should verify that the connection attempt was successful, but I will leave that for someone else to code
On Error Resume Next  'Allow continuing the script if an error happens

With comDataWait
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  EVENT," & vbCrLf
  strSQL = strSQL & "  TOTAL_WAITS," & vbCrLf
  strSQL = strSQL & "  TOTAL_TIMEOUTS," & vbCrLf
  strSQL = strSQL & "  TIME_WAITED" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$SYSTEM_EVENT" & vbCrLf
  strSQL = strSQL & "WHERE" & vbCrLf
  strSQL = strSQL & "  WAIT_CLASS<>'Idle'" & vbCrLf
  strSQL = strSQL & "ORDER BY" & vbCrLf
  strSQL = strSQL & "  EVENT"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

With comDataOSStat
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  STAT_NAME," & vbCrLf
  strSQL = strSQL & "  VALUE" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$OSSTAT" & vbCrLf
  strSQL = strSQL & "WHERE" & vbCrLf
  strSQL = strSQL & "  STAT_NAME IN ('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME')"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

With comDataSysTime
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  VALUE," & vbCrLf
  strSQL = strSQL & "  STAT_NAME" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$SYS_TIME_MODEL"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

With comDataSessTime
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  S.SID," & vbCrLf
  strSQL = strSQL & "  S.SERIAL#," & vbCrLf
  strSQL = strSQL & "  NVL(S.USERNAME,' ') USERNAME," & vbCrLf
  strSQL = strSQL & "  NVL(S.MACHINE,' ') MACHINE," & vbCrLf
  strSQL = strSQL & "  NVL(S.PROGRAM,' ') PROGRAM," & vbCrLf
  strSQL = strSQL & "  NVL(S.SQL_ID,' ') SQL_ID," & vbCrLf
  strSQL = strSQL & "  NVL(S.SQL_CHILD_NUMBER,0) SQL_CHILD_NUMBER," & vbCrLf
  strSQL = strSQL & "  STM.VALUE," & vbCrLf
  strSQL = strSQL & "  STM.STAT_NAME" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$SESS_TIME_MODEL STM," & vbCrLf
  strSQL = strSQL & "  V$SESSION S" & vbCrLf
  strSQL = strSQL & "WHERE" & vbCrLf
  strSQL = strSQL & "  S.SID=STM.SID" & vbCrLf
  strSQL = strSQL & "ORDER BY" & vbCrLf
  strSQL = strSQL & "  S.USERNAME," & vbCrLf
  strSQL = strSQL & "  S.PROGRAM," & vbCrLf
  strSQL = strSQL & "  S.SID"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

'Fire up Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Left = 0
objIE.Top = 0
objIE.Width = 950
objIE.Height = 800
objIE.StatusBar = False
objIE.MenuBar = False
objIE.Toolbar = False

objIE.Navigate "about:blank"
objIE.Document.Title = "Charles Hooper's Time Model Data Viewer"
objIE.Visible = True

For i = 1 To intCheckIterations
  Set snpDataOSStat = comDataOSStat.Execute
  If Not (snpDataOSStat Is Nothing) Then
    Do While Not (snpDataOSStat.EOF)
      Select Case CStr(snpDataOSStat("stat_name"))
        Case "NUM_CPUS"
          intNumCPUs = CInt(snpDataOSStat("value"))
        Case "IDLE_TIME"
          dblIdleTimeLast = dblIdleTime
          dblIdleTime = CDbl(snpDataOSStat("value"))
        Case "BUSY_TIME"
          dblBusyTimeLast = dblBusyTime
          dblBusyTime = CDbl(snpDataOSStat("value"))
        Case "USER_TIME"
          dblUserTimeLast = dblUserTime
          dblUserTime = CDbl(snpDataOSStat("value"))
        Case "SYS_TIME"
          dblSysTimeLast = dblSysTime
          dblSysTime = CDbl(snpDataOSStat("value"))
      End Select

      snpDataOSStat.movenext
    Loop
  End If

  Set snpDataWait = comDataWait.Execute
  If Not (snpDataWait Is Nothing) Then
    Do While Not (snpDataWait.EOF)
      intWaitCurrent = intWaitCount + 1
      'Find the previous entry for this wait event
      For j = 1 To intWaitCount
        If strWaitEventName(j) = CStr(snpDataWait("event")) Then
          intWaitCurrent = j
          Exit For
        End If
      Next
      If intWaitCurrent = intWaitCount + 1 Then
        'New entry
        intWaitCount = intWaitCount + 1
        strWaitEventName(intWaitCurrent) = CStr(snpDataWait("event"))
      End If
      dblWaitValueLast(intWaitCurrent) = dblWaitValue(intWaitCurrent)
      dblWaitValue(intWaitCurrent) = CDbl(snpDataWait("time_waited"))
      dblWaitWaitsValueLast(intWaitCurrent) = dblWaitWaitsValue(intWaitCurrent)
      dblWaitWaitsValue(intWaitCurrent) = CDbl(snpDataWait("total_waits"))
      dblWaitTOValueLast(intWaitCurrent) = dblWaitTOValue(intWaitCurrent)
      dblWaitTOValue(intWaitCurrent) = CDbl(snpDataWait("total_timeouts"))

      snpDataWait.movenext
    Loop
  End If

  Set snpDataSysTime = comDataSysTime.Execute
  If Not (snpDataSysTime Is Nothing) Then
    Do While Not (snpDataSysTime.EOF)
      Select Case CStr(snpDataSysTime("stat_name"))
        Case "DB CPU"
          dblDBCPULast = dblDBCPU
          dblDBCPU = CDbl(snpDataSysTime("value"))
        Case "DB time"
          dblDBTimeLast = dblDBTime
          dblDBTime = CDbl(snpDataSysTime("value"))
        Case "Java execution elapsed time"
          dblJavaTimeLast = dblJavaTime
          dblJavaTime = CDbl(snpDataSysTime("value"))
        Case "PL/SQL compilation elapsed time"
          dblPLSQLCompileLast = dblPLSQLCompile
          dblPLSQLCompile = CDbl(snpDataSysTime("value"))
        Case "PL/SQL execution elapsed time"
          dblPLSQLExecutionLast = dblPLSQLExecution
          dblPLSQLExecution = CDbl(snpDataSysTime("value"))
        Case "RMAN cpu time (backup/restore)"
          dblRMANCPULast = dblRMANCPU
          dblRMANCPU = CDbl(snpDataSysTime("value"))
        Case "background cpu time"
          dblBackgroundCPULast = dblBackgroundCPU
          dblBackgroundCPU = CDbl(snpDataSysTime("value"))
        Case "background elapsed time"
          dblBackgroundElapsedLast = dblBackgroundElapsed
          dblBackgroundElapsed = CDbl(snpDataSysTime("value"))
        Case "connection management call elapsed time"
          dblConnectMgmtLast = dblConnectMgmt
          dblConnectMgmt = CDbl(snpDataSysTime("value"))
        Case "failed parse (out of shared memory) elapsed time"
          dblFailedParseMemoryLast = dblFailedParseMemory
          dblFailedParseMemory = CDbl(snpDataSysTime("value"))
        Case "failed parse elapsed time"
          dblFailedParseElapsedLast = dblFailedParseElapsed
          dblFailedParseElapsed = CDbl(snpDataSysTime("value"))
        Case "hard parse (bind mismatch) elapsed time"
          dblHardParseBindLast = dblHardParseBind
          dblHardParseBind = CDbl(snpDataSysTime("value"))
        Case "hard parse (sharing criteria) elapsed time"
          dblHardParseSharingLast = dblHardParseSharing
          dblHardParseSharing = CDbl(snpDataSysTime("value"))
        Case "hard parse elapsed time"
          dblHardParseElapsedLast = dblHardParseElapsed
          dblHardParseElapsed = CDbl(snpDataSysTime("value"))
        Case "inbound PL/SQL rpc elapsed time"
          dblInboundPLSQLLast = dblInboundPLSQL
          dblInboundPLSQL = CDbl(snpDataSysTime("value"))
        Case "parse time elapsed"
          dblParseTimeElapsedLast = dblParseTimeElapsed
          dblParseTimeElapsed = CDbl(snpDataSysTime("value"))
        Case "repeated bind elapsed time"
          dblRepeatedBindLast = dblRepeatedBind
          dblRepeatedBind = CDbl(snpDataSysTime("value"))
        Case "sequence load elapsed time"
          dblSequenceLoadLast = dblSequenceLoad
          dblSequenceLoad = CDbl(snpDataSysTime("value"))
        Case "sql execute elapsed time"
          dblSQLExecuteTimeLast = dblSQLExecuteTime
          dblSQLExecuteTime = CDbl(snpDataSysTime("value"))
      End Select

      snpDataSysTime.MoveNext
    Loop
  End If

  For j = 1 To intSessionCount
    intSessionExists(j) = False
  Next
  Set snpDataSessTime = comDataSessTime.Execute
  If Not (snpDataSessTime Is Nothing) Then
    Do While Not (snpDataSessTime.EOF)
      'Find the matching session's previous statistics
      If (lngSIDLast <> CLng(snpDataSessTime("sid"))) Or (lngSerialLast <> CLng(snpDataSessTime("serial#"))) Then
        'This is a different session, see if the session was previously captured
        lngSIDLast = CLng(snpDataSessTime("sid"))
        lngSerialLast = CLng(snpDataSessTime("serial#"))

        intSessionCurrent = intSessionCount + 1
        For j = 1 To intSessionCount
          If (lngSID(j) = CLng(snpDataSessTime("sid"))) And (lngSerial(j) = CLng(snpDataSessTime("serial#"))) Then
            intSessionCurrent = j
            Exit For
          End If
        Next
        If intSessionCurrent = intSessionCount + 1 Then
          intSessionCount = intSessionCount + 1
          lngSID(intSessionCurrent) = CLng(snpDataSessTime("sid"))
          lngSerial(intSessionCurrent) = CLng(snpDataSessTime("serial#"))
          strSessionOther(intSessionCurrent) = CStr(snpDataSessTime("machine")) & " ~ " & _
             CStr(snpDataSessTime("username")) & " ~ " & _
             CStr(snpDataSessTime("program")) & " ~ "
          If snpDataSessTime("sql_id") <> " " Then
            strSessionOther(intSessionCurrent) = strSessionOther(intSessionCurrent) & "SQL_ID/Child: " & _
              CStr(snpDataSessTime("sql_id")) & "/" & CStr(snpDataSessTime("sql_child_number"))
          End If
        End If
      End If

      intSessionExists(intSessionCurrent) = True
      Select Case CStr(snpDataSessTime("stat_name"))
        Case "DB CPU"
          dblDBCPUSLast(intSessionCurrent) = dblDBCPUS(intSessionCurrent)
          dblDBCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "DB time"
          dblDBTimeSLast(intSessionCurrent) = dblDBTimeS(intSessionCurrent)
          dblDBTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "Java execution elapsed time"
          dblJavaTimeSLast(intSessionCurrent) = dblJavaTimeS(intSessionCurrent)
          dblJavaTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "PL/SQL compilation elapsed time"
          dblPLSQLCompileSLast(intSessionCurrent) = dblPLSQLCompileS(intSessionCurrent)
          dblPLSQLCompileS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "PL/SQL execution elapsed time"
          dblPLSQLExecutionSLast(intSessionCurrent) = dblPLSQLExecutionS(intSessionCurrent)
          dblPLSQLExecutionS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "RMAN cpu time (backup/restore)"
          dblRMANCPUSLast(intSessionCurrent) = dblRMANCPUS(intSessionCurrent)
          dblRMANCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "background cpu time"
          dblBackgroundCPUSLast(intSessionCurrent) = dblBackgroundCPUS(intSessionCurrent)
          dblBackgroundCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "background elapsed time"
          dblBackgroundElapsedSLast(intSessionCurrent) = dblBackgroundElapsedS(intSessionCurrent)
          dblBackgroundElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "connection management call elapsed time"
          dblConnectMgmtSLast(intSessionCurrent) = dblConnectMgmtS(intSessionCurrent)
          dblConnectMgmtS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "failed parse (out of shared memory) elapsed time"
          dblFailedParseMemorySLast(intSessionCurrent) = dblFailedParseMemoryS(intSessionCurrent)
          dblFailedParseMemoryS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "failed parse elapsed time"
          dblFailedParseElapsedSLast(intSessionCurrent) = dblFailedParseElapsedS(intSessionCurrent)
          dblFailedParseElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "hard parse (bind mismatch) elapsed time"
          dblHardParseBindSLast(intSessionCurrent) = dblHardParseBindS(intSessionCurrent)
          dblHardParseBindS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "hard parse (sharing criteria) elapsed time"
          dblHardParseSharingSLast(intSessionCurrent) = dblHardParseSharingS(intSessionCurrent)
          dblHardParseSharingS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "hard parse elapsed time"
          dblHardParseElapsedSLast(intSessionCurrent) = dblHardParseElapsedS(intSessionCurrent)
          dblHardParseElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "inbound PL/SQL rpc elapsed time"
          dblInboundPLSQLSLast(intSessionCurrent) = dblInboundPLSQLS(intSessionCurrent)
          dblInboundPLSQLS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "parse time elapsed"
          dblParseTimeElapsedSLast(intSessionCurrent) = dblParseTimeElapsedS(intSessionCurrent)
          dblParseTimeElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "repeated bind elapsed time"
          dblRepeatedBindSLast(intSessionCurrent) = dblRepeatedBindS(intSessionCurrent)
          dblRepeatedBindS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "sequence load elapsed time"
          dblSequenceLoadSLast(intSessionCurrent) = dblSequenceLoadS(intSessionCurrent)
          dblSequenceLoadS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "sql execute elapsed time"
          dblSQLExecuteTimeSLast(intSessionCurrent) = dblSQLExecuteTimeS(intSessionCurrent)
          dblSQLExecuteTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
      End Select

      snpDataSessTime.MoveNext
    Loop
  End If

  dteLastLoopStart = Now
  intDataChanged = True

  'Uncomment the following line if you would like for the session detail to be collapsed on each refresh
  'intDisplaySessionDetail = False

  Do While DateDiff("s", dteLastLoopStart, Now) < intDelayIterations
    'Remain in this loop until intDelayIterations seconds have elapsed
    intFlag = 0
    If intDataChanged = True Then
      'Update the web page
      strHTML = ""

      strHTML = strHTML & "<form name=""OracleTimeModel"">" & vbCrLf
      'strHTML = strHTML & "<input type=text id=divStatus name=divStatus value="" "" size=50 disabled=true><br />" & vbCrLf
      strHTML = strHTML & "<input type=hidden id=txtOK value="" "">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Re-Query"" id=cmdQuery onclick=""document.getElementById('txtOK').value='QUERY';"">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Show Detail"" id=cmdShowDetail onclick=""document.getElementById('txtOK').value='DETAIL';"">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Close"" id=cmdClose onclick=""document.getElementById('txtOK').value='CLOSE';"">" & vbCrLf
      strHTML = strHTML & "</form>" & vbCrLf

      strHTML = strHTML & "<table border=""1"" width=""500"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">CPUs</td><td  bgcolor=""#11AAFF"">Busy Time</td><td bgcolor=""#11AAFF"">Idle Time</td>" & _
        "<td bgcolor=""#11AAFF"">User Mode</td><td bgcolor=""#11AAFF"">Kernel Mode</td></tr>" & vbCrLf
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber(intNumCPUs, 0) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblBusyTime - dblBusyTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblIdleTime - dblIdleTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblUserTime - dblUserTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblSysTime - dblSysTimeLast) / 100, 2) & "</td></tr>"
      strHTML = strHTML & "</table><p>" & vbCrLf

      strHTML = strHTML & "<table border=""1"" width=""900"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">Value</td><td bgcolor=""#11AAFF"" colspan=""5"">Statistic Name</td></tr>" & vbCrLf
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblBackgroundElapsed - dblBackgroundElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">Background Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblBackgroundElapsed - dblBackgroundElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / (dblBackgroundElapsed - dblBackgroundElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblBackgroundCPU - dblBackgroundCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Background CPU Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblBackgroundCPU - dblBackgroundCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / (dblBackgroundCPU - dblBackgroundCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblRMANCPU - dblRMANCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "RMAN CPU Time (Backup Restore)</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblRMANCPU - dblRMANCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblRMANCPUS(j) - dblRMANCPUSLast(j)) / (dblRMANCPU - dblRMANCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><<td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblRMANCPUS(j) - dblRMANCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblDBTime - dblDBTimeLast) / 1000000, 2) & "</td><td colspan=""5"">DB Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblDBTime - dblDBTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblDBTimeS(j) - dblDBTimeSLast(j)) / (dblDBTime - dblDBTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblDBTimeS(j) - dblDBTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblDBCPU - dblDBCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "DB CPU</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblDBCPU - dblDBCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblDBCPUS(j) - dblDBCPUSLast(j)) / (dblDBCPU - dblDBCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblDBCPUS(j) - dblDBCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblConnectMgmt - dblConnectMgmtLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Connection Management Call Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblConnectMgmt - dblConnectMgmtLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / (dblConnectMgmt - dblConnectMgmtLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblSequenceLoad - dblSequenceLoadLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Sequence Load Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblSequenceLoad - dblSequenceLoadLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / (dblSequenceLoad - dblSequenceLoadLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblSQLExecuteTime - dblSQLExecuteTimeLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "SQL Execute Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblSQLExecuteTime - dblSQLExecuteTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / (dblSQLExecuteTime - dblSQLExecuteTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblParseTimeElapsed - dblParseTimeElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Parse Time Elapsed</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / (dblParseTimeElapsed - dblParseTimeElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseElapsed - dblHardParseElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "Hard Parse Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseElapsed - dblHardParseElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / (dblHardParseElapsed - dblHardParseElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseSharing - dblHardParseSharingLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & "Hard Parse (Sharing Criteria) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseSharing - dblHardParseSharingLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / (dblHardParseSharing - dblHardParseSharingLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseBind - dblHardParseBindLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & strInd & "Hard Parse (Bind Mismatch) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseBind - dblHardParseBindLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseBindS(j) - dblHardParseBindSLast(j)) / (dblHardParseBind - dblHardParseBindLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseBindS(j) - dblHardParseBindSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblFailedParseElapsed - dblFailedParseElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "Failed Parse Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblFailedParseElapsed - dblFailedParseElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / (dblFailedParseElapsed - dblFailedParseElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblFailedParseMemory - dblFailedParseMemoryLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & "Failed Parse (Out of Shared Memory) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblFailedParseMemory - dblFailedParseMemoryLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / (dblFailedParseMemory - dblFailedParseMemoryLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblPLSQLExecution - dblPLSQLExecutionLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "PL/SQL Execution Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblPLSQLExecution - dblPLSQLExecutionLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / (dblPLSQLExecution - dblPLSQLExecutionLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblInboundPLSQL - dblInboundPLSQLLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Inbound PL/SQL RPC Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblInboundPLSQL - dblInboundPLSQLLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / (dblInboundPLSQL - dblInboundPLSQLLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblPLSQLCompile - dblPLSQLCompileLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "PL/SQL Compilation Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblPLSQLCompile - dblPLSQLCompileLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / (dblPLSQLCompile - dblPLSQLCompileLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblJavaTime - dblJavaTimeLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Java Execution Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblJavaTime - dblJavaTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblJavaTimeS(j) - dblJavaTimeSLast(j)) / (dblJavaTime - dblJavaTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblJavaTimeS(j) - dblJavaTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblRepeatedBind - dblRepeatedBindLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Repeated Bind Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblRepeatedBind - dblRepeatedBindLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / (dblRepeatedBind - dblRepeatedBindLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "</table><p>" & vbCrLf
      strHTML = strHTML & "<table border=""1"" width=""500"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">Wait Event Name</td><td  bgcolor=""#11AAFF"">Wait Time</td><td bgcolor=""#11AAFF"">Waits</td><td bgcolor=""#11AAFF"">Timeouts</td></tr>" & vbCrLf
      For j = 1 To intWaitCount
        If (dblWaitValue(j) - dblWaitValueLast(j)) <> 0 Then
          strHTML = strHTML & "<tr><td>" & strWaitEventName(j) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitValue(j) - dblWaitValueLast(j)) / 100, 2) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitWaitsValue(j) - dblWaitWaitsValueLast(j)), 0) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitTOValue(j) - dblWaitTOValueLast(j)), 0) & "</td></tr>"
        End If
      Next
      strHTML = strHTML & "</table>" & vbCrLf

      objIE.Document.Body.InnerHTML = strHTML
      intDataChanged = False
    End If

    'Put the VBS script into suspend mode for 1/2 second to prevent hammering the CPUs
    Wscript.Sleep 500

    If objIE Is Nothing Then
      'User closed the Window
      intFlag = -1
    Else
      If objIE.Document.All.txtOK.Value <> " " Then
        Select Case objIE.Document.All.txtOK.Value
          Case "QUERY"
            intFlag = 1
            objIE.Document.All.txtOK.Value = " "
          Case "DETAIL"
            intFlag = 2
            If intDisplaySessionDetail = True Then
              intDisplaySessionDetail = False
            Else
              intDisplaySessionDetail = True
            End If
            intDataChanged = True
            objIE.Document.All.txtOK.Value = " "
          Case "CLOSE"
            intFlag = -1
            objIE.Document.All.txtOK.Value = " "
            objIE.Quit
        End Select
      End If
    End If

    If Abs(intFlag) = 1 Then
      Exit Do
    End If
  Loop

  If intFlag = -1 Then
    Exit For
  End If
Next

dbDatabase.Close

Set snpDataWait = Nothing
Set comDataWait = Nothing
Set snpDataOSStat = Nothing
Set comDataOSStat = Nothing
Set snpDataSysTime = Nothing
Set comDataSysTime = Nothing
Set snpDataSessTime = Nothing
Set comDataSessTime = Nothing
Set dbDatabase = Nothing
Set objIE = Nothing

Easy, right? 

There is no need to stop at this level – dig into the session-level wait events, enable a 10046 trace for sessions, set up the script to send an email to a user if the user consumes more than 10% of the server’s capacity, … and most important, have fun.





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





Output Employee Attendance Calendar to Web with VBS

16 12 2009

December 16, 2009

This post is adapted from a small part of a presentation I gave a couple months ago.  The original code sample integrated into an ERP system to display an employee’s running attendance record for the last 6 or 12 months in graphical form, output to an Internet Explorer window.

First, we need sample “attendance” data in a table:

CREATE TABLE EMPLOYEE_RECORD_TEST AS
SELECT
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,5)),
          0,'MIKE',
          1,'ROB',
          2,'SAM',
          3,'JOE',
          4,'ERIC') EMPLOYEE_ID,
  TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE,
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)),
          0,'VAC',
          1,'HOL',
          2,'BEREAVE',
          3,'JURY',
          4,'ABS',
          5,'EXCUSE',
          6,'MIL',
          'OTHER') INDIRECT_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

The above created a table with 1,000 rows that picked one of five employees at random for each row, specifying a random date between today and 999 days ago, with one of eight random identifiers for the date.  The data in the table will look something like this:

SELECT
  *
FROM
  EMPLOYEE_RECORD_TEST
WHERE
  ROWNUM<=10;

EMPLOYEE_ID SHIFT_DAT INDIRECT_ID
----------- --------- -----------
MIKE        03-SEP-08 OTHER
MIKE        26-JUL-09 HOL
MIKE        27-MAY-09 EXCUSE
ERIC        27-JUL-08 OTHER
ERIC        02-NOV-07 VAC
ROB         02-OCT-07 OTHER
JOE         26-MAY-08 HOL
ERIC        29-JUL-08 ABS
ERIC        23-JUL-09 MIL
ERIC        14-JUN-07 HOL

Now that we have sample data, let’s see what we are trying to achieve (reduced in size):

As the color-coded output shows, Eric took a vacation day on January 4 and March 8.  Eric also was on bereavement leave on February 23 and 24, as well as March 22 and 25.  So, how was this output created?  A VBS script connected to the Oracle database (using a custom DLL to hide the username and password, and to simplify the process of submitting the SQL statement with bind variables), submitted a query, and then built the web page on the fly.

Dim varDateStart
Dim varDateEnd
Dim varDateMonthStart
Dim varDateMonthEnd
Dim intWeekdayStart
Dim intShiftDay
Dim i
Dim intRow
Dim intCol

'The color constants
Dim lngVacationBackColor
Dim lngHolidayBackColor
Dim lngBereavementBackColor
Dim lngJuryDutyBackColor
Dim lngAbsentBackColor
Dim lngExcusedBackColor
Dim lngMilitaryBackColor
Dim lngOtherMonthBackColor

Dim lngDateBackColor(31)
Dim lngDateForeColor(31)
Dim strSQL
Dim snpData
Dim OracleSQL
Dim objIE
Dim objShell

Dim intLastMonth
Dim intOutputMonth

Dim strHTML
Dim strEmployeeID
Dim dteTransactionDate

strEmployeeID = "MIKE"
'strEmployeeID = "ROB"
'strEmployeeID = "SAM"
'strEmployeeID = "JOE"
'strEmployeeID = "ERIC"

'dteTransactionDate = CDate("January 1, 2009") 'Can specify a specific date
dteTransactionDate = Date 'Can specify the current date

'Define the colors to be used to indicate the indirect in the date
'Note that the RGB components must be specified as BGR to be compatible with HTML
lngVacationBackColor = RGB(255, 0, 0)
lngHolidayBackColor = RGB(0, 255, 0)
lngBereavementBackColor = RGB(255, 175, 0)
lngJuryDutyBackColor = RGB(33, 153, 255)
lngAbsentBackColor = RGB(0, 0, 255)
lngExcusedBackColor = RGB(0, 255, 255)
lngMilitaryBackColor = RGB(255, 0, 150)
lngOtherMonthBackColor = RGB(75, 75, 100)

Set OracleSQL = CreateObject("VMDBOracle.SQLProcessor")
Set snpData = CreateObject("ADODB.Recordset")

'Specify the start of the month based on the current transaction date - set it back to the first day of the month
varDateStart = DateAdd("m", -11, DateSerial(DatePart("yyyy", dteTransactionDate), DatePart("m", dteTransactionDate), 1))

'Finding the end of the month is a little more difficult - we add 1 month to the transaction date, find the start of that month, and subtract one day
varDateEnd = DateAdd("d", -1, DateSerial(DatePart("yyyy", DateAdd("m", 1, dteTransactionDate)), DatePart("m", DateAdd("m", 1, dteTransactionDate)), 1))

'Set the starting colors
For i = 1 To 31
    lngDateBackColor(i) = RGB(230, 230, 230) 'Off White
    lngDateForeColor(i) = RGB(0, 0, 0) 'Black
Next

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & "  INDIRECT_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE BETWEEN ? AND ?" & vbCrLf
strSQL = strSQL & "  AND INDIRECT_ID IS NOT NULL" & vbCrLf

'Specify the start of the month based on the current transaction date - set it back to the first day of the month
OracleSQL.SetParameter varDateStart, "DATE"

'Finding the end of the month is a little more difficult - we add 1 month to the transaction date, find the start of that month, and subtract one day
OracleSQL.SetParameter varDateEnd, "DATE"

If strEmployeeID <> "" Then
    strSQL = strSQL & "  AND EMPLOYEE_ID= ?" & vbCrLf
    OracleSQL.SetParameter strEmployeeID, "VARCHAR"
End If

strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & "  INDIRECT_ID DESC"

OracleSQL.Sql = strSQL
Set snpData = OracleSQL.Execute

intOutputMonth = False
strHTML = ""
intRow = 0

'Shadow
strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 7px;left: 7;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #000000;"
strHTML = strHTML & "background-color: #FFFFFF;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 5px;left: 5;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #FFFF00;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 6px;left: 6;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #0000FF;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

If Not (snpData Is Nothing) Then
    Do While Not (snpData.EOF)
        intShiftDay = DatePart("d", CDate(snpData("shift_date")))
        Select Case CStr(snpData("indirect_id"))
            Case "VAC", "VACB", "VACC", "VACF", "VACM"
                lngDateBackColor(intShiftDay) = lngVacationBackColor
            Case "HOL", "HOLC", "HOLF", "HOLB", "HOLM"
                lngDateBackColor(intShiftDay) = lngHolidayBackColor
            Case "BEREAVE", "BEREAVEC", "BEREAVEF", "BEREAVEB", "BEREAVEM"
                lngDateBackColor(intShiftDay) = lngBereavementBackColor
            Case "JURY", "JURYC", "JURYF", "JURYB", "JURYM"
                lngDateBackColor(intShiftDay) = lngJuryDutyBackColor
            Case "ABS", "ABSC", "ABSF", "ABSB", "ABSM"
                lngDateBackColor(intShiftDay) = lngAbsentBackColor
            Case "EXCUSE", "EXCUSEC", "EXCUSEF", "EXCUSEB", "EXCUSEM"
                lngDateBackColor(intShiftDay) = lngExcusedBackColor
            Case "MIL", "MILC", "MILF", "MILB", "MILM"
                lngDateBackColor(intShiftDay) = lngMilitaryBackColor
        End Select

        'See if the month will change
        intLastMonth = DatePart("m", CDate(snpData("shift_date")))
        varDateMonthStart = DateSerial(DatePart("yyyy", CDate(snpData("shift_date"))), DatePart("m", CDate(snpData("shift_date"))), 1)
        varDateMonthEnd = DateAdd("d", -1, DateAdd("m", 1, DateSerial(DatePart("yyyy", CDate(snpData("shift_date"))), DatePart("m", CDate(snpData("shift_date"))), 1)))

        snpData.MoveNext

        intOutputMonth = False

        If snpData.EOF Then
            intOutputMonth = True
        Else
            If DatePart("m", CDate(snpData("shift_date"))) <> intLastMonth Then
                intOutputMonth = True
            End If
        End If

        If intOutputMonth = True Then
            intWeekdayStart = Weekday(varDateMonthStart)

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 14pt; color: #110011;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "<b>" & MonthName(DatePart("m", varDateMonthStart)) & " " & CStr(DatePart("yyyy", varDateMonthStart)) & "</b></div>" & vbCrLf

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Sun</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(2 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Mon</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(3 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Tue</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(4 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Wed</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(5 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Thu</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(6 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Fri</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(7 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Sat</div>" & vbCrLf

            intRow = intRow + 1
            'Fill in the days from the previous month
            For i = 1 To intWeekdayStart - 1
                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(i * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #FFFFFF;"
                'Pad with leading 0s
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngOtherMonthBackColor)), "0") & Hex(lngOtherMonthBackColor) & ";"">"
                strHTML = strHTML & DatePart("d", DateAdd("d", -(intWeekdayStart - i), varDateMonthStart)) & "</div>" & vbCrLf
            Next

            For i = 1 To DatePart("d", varDateMonthEnd)
                'See if we need to jump to the next row
                If i > 1 Then
                    'See if the week day is less than the previous week day - if so, jump to the next row in the calendar since the week changed
                    If Weekday(DateAdd("d", i - 1, varDateMonthStart)) < Weekday(DateAdd("d", i - 2, varDateMonthStart)) Then
                        intRow = intRow + 1
                    End If
                End If
                intCol = Weekday(DateAdd("d", i - 1, varDateMonthStart))

                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(Weekday(DateAdd("d", i - 1, varDateMonthStart)) * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #" & String(6 - Len(Hex(lngDateForeColor(i))), "0") & Hex(lngDateForeColor(i)) & ";"
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngDateBackColor(i))), "0") & Hex(lngDateBackColor(i)) & ";"">"
                strHTML = strHTML & CStr(i) & "</div>" & vbCrLf
            Next

            'Finish out the final week
            For i = Weekday(varDateMonthEnd) + 1 To 7
                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(i * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #FFFFFF;"
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngOtherMonthBackColor)), "0") & Hex(lngOtherMonthBackColor) & ";"">"
                strHTML = strHTML & CStr(i - Weekday(varDateMonthEnd)) & "</div>" & vbCrLf
            Next

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngVacationBackColor)), "0") & Hex(lngVacationBackColor) & ";"">"
            strHTML = strHTML & "VAC</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(2 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngHolidayBackColor)), "0") & Hex(lngHolidayBackColor) & ";"">"
            strHTML = strHTML & "HOL</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(3 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngBereavementBackColor)), "0") & Hex(lngBereavementBackColor) & ";"">"
            strHTML = strHTML & "BER</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(4 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngJuryDutyBackColor)), "0") & Hex(lngJuryDutyBackColor) & ";"">"
            strHTML = strHTML & "JUR</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(5 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngAbsentBackColor)), "0") & Hex(lngAbsentBackColor) & ";"">"
            strHTML = strHTML & "ABS</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(6 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngExcusedBackColor)), "0") & Hex(lngExcusedBackColor) & ";"">"
            strHTML = strHTML & "EXC</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(7 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngMilitaryBackColor)), "0") & Hex(lngMilitaryBackColor) & ";"">"
            strHTML = strHTML & "MIL</div>" & vbCrLf

            'Reset the starting colors
            For i = 1 To 31
                lngDateBackColor(i) = RGB(230, 230, 230) 'Off White
                lngDateForeColor(i) = RGB(0, 0, 0) 'Black
            Next
        End If
    Loop

    snpData.Close
End If

'Fire up Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Left = 0
objIE.Top = 0
objIE.Width = 260
objIE.Height = 700
objIE.StatusBar = False
objIE.MenuBar = False
objIE.Toolbar = False

objIE.Navigate "about:blank"
objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title = "Attendance " & strEmployeeID
objIE.Visible = True

'with the help of custom program, set a 1 second delay, then force the Attendance web page to the top
Set objShell = CreateObject("WScript.Shell")
objShell.Run("C:\BringToTop.exe " & Chr(34) & "Attendance " & strEmployeeID & Chr(34) & " 1")

Set objShell = Nothing
Set snpData = Nothing
Set objIE = Nothing
Set OracleSQL = Nothing

While the above uses a custom DLL for the database connection, a standard ADO connection will work just as well.  The script also uses a custom program that I created called BringToTop that simply addresses the “pop under” behavior on Vista and Windows 7.

So, which employee has the best attendance record?





Use VBS to Search for Oracle Books using Google’s Book Library

13 12 2009

December 12, 2009

Below is a somewhat complicated VBS script that interacts with Internet Explorer to submit a query to books.google.com.  Once Google prepares the web page, the VBS script parses the raw HTML code in the web page to generate a new web page containing the list of matching books supplied by Google.  Selecting a book and then clicking the View button opens that book on the Google books site.

The search keyword is specified on the objIESource.Navigate line at the end of the website address.  A plus sign should appear between each search keyword.  To exclude a particular word from the search, prefix the word with a minus sign, for example to search for the keywords Oracle and SQL, but exclude the word dummy:

objIESource.Navigate "http://books.google.com/books?um=1&q=oracle+sql+-dummy"

Note that there are various ways to extend this example.

Dim objIE            'For the data entry form where we present the harvested book list
Dim objShell         'To add a delay, may throw an error when executed in a Visual macro
Dim strHTML          'Holds what we are displaying on our data entry form
Dim intFlag          'Indicates if the user clicked OK or closed the browser window
Dim i                'For our counter
Dim objIESource      'Holds the data source web page
Dim strHTMLSource    'Holds the HTML contents of the source web page
Dim intStart         'The starting position of title="  in the source web page
Dim intEnd           'The position of the next " after title="  in the source web page
Dim intApproxStart   'The book titles appear after the first entry of coverthumb, so we will start there
Dim strBook(1000)    'Holds the book titles found on this page
Dim intBookCount     'Counter for the number of books found
Dim strFind          'What to find in the HTML code which indicates that the data of interest will follow
Dim intFindLen       'The length of the what to find string
Dim intLinkStart     'The starting position of title="  in the source web page
Dim intLinkEnd       'The position of the next " after title="  in the source web page
Dim strBookLink(1000)'Holds the link to the book found on this page
Dim strLinkFind      'What to find in the HTML code which indicates that the page link will follow
Dim intLinkFindLen   'The length of the what to find link string
Dim strSelectedBook  'The name of the selected book
Dim strBookAddress   'The web address of the book
Dim adsFile          'Used if we want to write the downloaded web page to the hard drive

On Error Resume Next
'Set objShell = CreateObject("WScript.Shell")
Set objIESource = CreateObject("InternetExplorer.Application")
objIESource.Navigate "http://books.google.com/books?um=1&q=oracle+tuning"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"

For i = 1 to 10000
    'Give it some time to prepare the objIESource
Next
Do While objIESource.Busy <> False
    'objShell.Sleep 500 'Edit: This line was supposed to be replaced with the following line Dec 13, 2009
    Wscript.Sleep 200
Loop

intBookCount = 0
strHTMLSource = cStr(objIESource.Document.Body.InnerHTML)    'Retrieve the raw HTML code from the web page

'Uncomment to save the web page to the hard drive
'Set adsFile = CreateObject("ADODB.Stream")
'adsFile.Type = 2
'adsFile.Charset = "iso-8859-1"
'adsFile.Open
'adsFile.WriteText(strHTMLSource)
'adsFile.SaveToFile "c:\InnerHTML.txt", 2
'adsFile.close
'Set adsFile = Nothing

'In the same HTML page, the first book title starts after the first entry of: 
intApproxStart = InStr(strHTMLSource, "coverthumb") + 1
strFind = "title=" & Chr(34)
intFindLen = Len(strFind)
strLinkFind = "<A href=" & Chr(34)
intLinkFindLen = Len(strLinkFind)

'Find the start of the first book title
'Might be listed like this in the HTML code:  title="Excel 2007 VBA Programming For Dummies"
intStart = InStr(intApproxStart, strHTMLSource, strFind)

Do While intStart > 0
    'Find the end of the book title
    intEnd = InStr(intStart + intFindLen, strHTMLSource, Chr(34))
    If intEnd > 0 Then
        intBookCount = intBookCount + 1
        strBook(intBookCount) = Mid(strHTMLSource, intStart + intFindLen, intEnd - (intStart + intFindLen))

        'Find the link to the book title
        intLinkStart = InStr(intEnd, strHTMLSource, strLinkFind)
        If intLinkStart > 0 Then
            intLinkEnd = InStr(intLinkStart + intLinkFindLen, strHTMLSource, Chr(34))
            If intLinkEnd > 0 Then
                strBookLink(intBookCount) = Mid(strHTMLSource, intLinkStart + intLinkFindLen, intLinkEnd - (intLinkStart + intLinkFindLen))
            Else
                strBookLink(intBookCount) = ""
            End If
        Else
            strBookLink(intBookCount) = ""
        End If

        'Find the start of the next book title
        intStart = InStr(intEnd, strHTMLSource, strFind)
    Else
        Exit Do
    End If
Loop

'Edit: place a single quote in front of the following two lines to prevent the list of books from disappearing, Dec 13, 2009
objIESource.Quit
Set objIESource = Nothing

strHTML = strHTML & "<form name=""BookFind"">" & vbCrLf
strHTML = strHTML & "<input type=hidden id=""txtOK"" value="" "">" & vbCrLf
strHTML = strHTML & "Book:<br /> <select size=""23"" id=""lstBooks"" style=""width:450"">" & vbCrLf

For i = 1 To intBookCount
    strHTML = strHTML & "<option value=" & Chr(34) & strBook(i) & Chr(34) & ">" & strBook(i) & "</option>" & vbCrLf
Next
strHTML = strHTML & "</select>" & vbCrLf
strHTML = strHTML & "<p><center><input type=button value=""View"" id=""cmdOK"" onclick=""document.getElementById('txtOK').value='OK';""></center>" & vbCrLf
strHTML = strHTML & "</form>" & vbCrLf

objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title = "Select Book Title from Google Books"
objIE.Left = 0
objIE.Top = 0
objIE.Width = 500
objIE.Height = 520
objIE.Statusbar = False
objIE.Menubar = False
objIE.Toolbar = False
objIE.Visible = True

For i = 1 to 10000
    'Give it some time to prepare the objIE
Next
Do While objIE.Busy <> False
    Wscript.Sleep 200
Loop

intFlag = 0
'loop until the button is clicked
Do While intFlag = 0
    If Err <> 0 Then
        intFlag = -1
    End If
    If objIE Is Nothing Then
        'User closed ID
        intFlag = -1
    Else
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    Wscript.Sleep 250
Loop

If intFlag = 1 Then
    'Copy in the values from the web page
    strSelectedBook = objIE.Document.Body.All.lstBooks.Value

    'Try to find the associated link to the book
    For i = 1 to intBookCount
        If strBook(i) = strSelectedBook Then
            'Found the book
            strBookAddress = strBookLink(i)
            'Extra credit - display the link associated with the selected book
            'Comment out the following objIE lines and uncomment the objIE.Quit
            '  if the link associated with the book should not be displayed
            objIE.Navigate "about:blank"
            objIE.Width = 800
            objIE.Height = 600
            objIE.Statusbar = True
            objIE.Menubar = True
            objIE.Toolbar = True
            objIE.Navigate strBookLink(i)
            Exit For
        End If
    Next
    'objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing

The list of results:

The selected book:








Follow

Get every new post delivered to your Inbox.

Join 137 other followers