<%@ LANGUAGE="VBScript" %> <% Set Connection = Server.CreateObject("ADODB.Connection") Connection.Mode = adModeRead Connection.Open phdb_rd If Not IsEmpty(Request("moye")) Then moye=CStr(Request("moye")) SQLStmt = " select to_char(to_date('" & moye & "','MMYYYY'),'Month') cmonth,to_char(to_date('" & moye & "','MMYYYY'),'YYYY') cyear from dual" ' Response.write(SQLStmt & "
") Set RS = Connection.Execute(SQLStmt) cmonth=RS("cmonth") cyear=RS("cyear") Else SQLStmt = " select to_char(sysdate,'Month') cmonth,to_char(sysdate,'YYYY') cyear,to_char(sysdate,'MMYYYY') moye from dual" ' Response.write(SQLStmt & "
") Set RS = Connection.Execute(SQLStmt) cmonth=RS("cmonth") cyear=RS("cyear") moye=CStr(RS("moye")) End If Set RS=nothing ' ' check last update time ' SQLStmt="select to_char(logdate,'MM/DD/YYYY HH24:MI:SS') udate from secretary.latest_shift_update" Set RS = Connection.Execute(SQLStmt) udate=RS("udate") Set RS=nothing SQLStmt = " select to_char(sn.shift_date,'MM/DD/YYYY HH24:MI:SS') sd,to_char(sn.shift_date,'MM/DD/YYYY') sday,to_char(sn.shift_date,'Day') nday,st.shift_type, " SQLStmt = SQLStmt & " lastname1 lc1,lastname2 lc2, " SQLStmt = SQLStmt & " substr(firstname1,1,1) || '.' || ' ' || lastname1 l1, " SQLStmt = SQLStmt & " substr(firstname2,1,1) || '.' || ' ' || lastname2 l2, " SQLStmt = SQLStmt & " 24*(sn.shift_date-sysdate) hdif " SQLStmt = SQLStmt & " from secretary.shift_names sn,secretary.shift_types st " SQLStmt = SQLStmt & " where sn.shift_date between to_date('" & moye & "','MMYYYY') " SQLStmt = SQLStmt & " and add_months(to_date('" & moye & "','MMYYYY'),1) " SQLStmt = SQLStmt & " and sn.shift_date=st.shift_date " SQLStmt = SQLStmt & " order by sn.shift_date " ' Response.write(SQLStmt & "
") Set RS = Connection.Execute(SQLStmt) %> Phobos Shift List, <%=Cstr(cmonth)%> <%=Cstr(cyear)%>

Phobos Shift List, <%=Cstr(cmonth)%> <%=Cstr(cyear)%>

Last updated on  <%=udate%>

The table shows always the same time interval but the requirement for presence in the counting house will be different, depending on the shift level. Shift Levels are color-coded. The first (top) name is always a shift leader

<% Day_Old="XXXXXXX" Do While Not RS.EOF If Day_Old <> CStr(RS("sday")) Then If Day_Old <> "XXXXXXX" Then Response.write("" & vbCrLf) End If Day_Now=CStr(RS("sday")) Day_Old=Day_Now Response.write("") Response.write("" & vbCrLf) End If ' ' color and maybe font code for different shifts ' ' shift type: ' If Not IsNull(RS("shift_type")) Then If CStr(RS("shift_type"))="D" Then rcolor="#FF0000" Elseif CStr(RS("shift_type"))="C" Then rcolor="#FF00FF" Elseif CStr(RS("shift_type"))="T" Then rcolor="#008000" Elseif CStr(RS("shift_type"))="A" Then rcolor="#D5D5D5" Else rcolor="#000000" End If Else rcolor="#000000" End If ' ' time dependent... ' ' response.write(CLng(RS("hdif")) & "
") If CDbl(RS("hdif"))<-8 Then lfmt="" rfmt="" Elseif CDbl(RS("hdif"))<0 Then lfmt="" rfmt="" Else lfmt="" rfmt="" End If ' ' print out names, lc1 is always a shift leader, lc2 is the second person ' If Not IsNull(RS("lc1")) Then Response.write("" & vbCrLf) Else Response.write("
" & vbCrLf) End If Else If Not IsNull(RS("lc2")) Then Response.write("" & vbCrLf) Else Response.write("" & vbCrLf) End If End If RS.Movenext Loop Response.write("" & vbCrLf) %>

Day

 4:00-12:00 noon 12 noon-20:00 20:00-4:00
" & CStr(RS("nday")) & "
"& CStr(RS("sday"))) If CDbl(RS("hdif"))>-20000 Then Response.write("") End If Response.write("		" & lfmt & CStr(RS("l1"))) If Not IsNull(RS("lc2")) Then Response.write("
" & lfmt & CStr(RS("l2")) & rfmt & "		" & lfmt & " Shift Leader Not Assigned " & rfmt & vbCrLf) Response.write("
" & lfmt & CStr(RS("l2")) & rfmt & "		" & lfmt & " Not assigned" & rfmt & "
<% Connection.Close %>

Shift Levels