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?





Network Monitoring Experimentations 2

16 12 2009

December 16, 2009

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

The first post in this series introduced Wireshark, demonstrated some of the problems that may be detected with the tool, demonstrated what happens with various fetch array sizes in the presence of configuration problems, and compared a low latency 100Mb connection with a higher latency wireless 802.11G (54Mb) connection from the same client computer during a transfer of database table data.

The connection attempt shown in the screen capture from the previous post was of the computer connecting through the wireless connection to a server during the launch of an ERP package. That connection attempt was slowed a bit by the network latency. That screen shot is shown below.

Why introduce the same screen shot again?  Let’s look at what happens sometimes when application versions are upgraded and in the process (sometimes undocumented) features are added to applications.  Take, for instance, this ERP program’s connection attempt that required at least 10 seconds to complete on a gigabit connection, when it is able to complete in less than a second on the much slower wireless connection.  What changed?  Taking a look at a couple of the screen shots of the slow connection attempt will help (the client computer is .185, the database server is .45, and the file server is .40):

In the above we see the client computer searching the file server for a file named DNSAPI.dll that did not exist.  Then we see a DNS lookup attempt for a device named TNS.k-mm.com (for those following along, this was caused by the TNS: keyword on the remotedbname= entry in the SQL.INI file).  Notice that the timestamp of the request is at 17:25:14.683144.  When the client is told by the DNS server for the second time that the name does not exist, the timestamp has advanced to 17:25:14.686069.  Next, the client tried to resolve the name TNS first by a direct WINS (NBNS) lookup and then by a WINS broadcast.  The timestamp of the next client activity after the WINS lookup is 17:25:16.967553, so roughly 2.3 seconds were wasted.  But, that is not the most significant lost time interval.

The above shows the actual connection starting at 17:25:16.988701 and completing at 17:25:17.001425, which is very quick, but are we done yet?

The above screen shot shows the client looking for a file named nethasp.ini on the file server, but is not able to find that file.  Important?

The above shows that the client finally gave up looking for the nethasp.ini file at 17:25:17:209969, and at 17:25:17:232671 started sending a series of four full broadcast packets, spaced roughly one second apart, to a destination port number that Wireshark has aliased as tcpnethaspsrv.  The client finally started communicating again with the database server at 17:25:21.344038.  So, between 17:25:14.686069 and 17:25:21.344038 the client mostly just sat idle waiting for a response that never came.  Yes, Wireshark ultimately identified the cause of the problem and helped find a course of action that was fully in place at 05:30:00 the next morning, with the new version of the ERP program starting in less than a second just like the old version.

Wireshark exposes a lot of detail from the packets that it captures.  Here are two screen shots of a network packet containing a SQL statement that was transmitted in clear text:

Notice that the raw packet data appears at the bottom and is expanded into human readable text in the upper pane.  Clicking a line in the upper pane (for instance one that shows the destination MAC address) shows that information in the raw packet data at the bottom of the window.








Follow

Get every new post delivered to your Inbox.

Join 142 other followers