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 " 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) & "https://hoopercharles.wordpress.com/wp-content/uploads/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) & "https://hoopercharles.wordpress.com/wp-content/uploads/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) & "https://hoopercharles.wordpress.com/wp-content/uploads/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. 🙂
Charles Hooper's Oracle Notes 
Leave a comment