vbscript - Attendance register using asp classic -
vbscript - Attendance register using asp classic -
i looking vb script in asp classic below:
i have 1 table named "attendance" in mssql2005 database has records. table includes 2 columns (sname, sdate).
my question how can show records attendance register, meaning dates should in horizontal column header on top (based on month in selected year if month has 31 date 31 columns).
in next row: below date column, if header date match date field sdate should show "p" or should show "a". it's calendar without daynames.
unfortunately thorough search didn't find me built-in solutions yet
here show how show attendance register passing single month , year value using vbscript , asp classic
this article may helpfull other people looking same solution.
i have 2 tables below written
staff: contain info staff user in column (staff_id, aname,...) staff_attendance: attendance of staff user save in table date wise unique row. table has 3 columns(staff_aid, staff_attn_dt, staff_att_takenby)now show how retrive records "staff_attendance" table retriving records "staff" table attendance register (see below given pic)
<!--#include file="connection.asp"--> <% ' above include file bring info connection "mm_conndudirectory_string" dim mymonth 'month of calendar dim myyear 'year of calendar dim firstday 'first day of month. 0 = sunday dim currentday 'used print dates in calendar dim col 'calendar column dim row 'calendar row mymonth = request.form("imonth") myyear = request.form("iyear") if mymonth = 0 mymonth = month(date) if myyear = 0 myyear = year(date) response.write "<b>" & monthname(mymonth) & " " & myyear & "</b>" %> <table width="100%" style="border: #6f6f6f 1px solid; text-align: center;"> <tr> <td nowrap style="border-bottom: #6f6f6f 1px solid; text-align: center;"> faculty name </td> <% firstday = dateserial(myyear, mymonth, 1) currentday = 1 col = 0 30 if currentday > lastday(mymonth, myyear) %> <td style="border: #6f6f6f 1px solid; text-align: center;"> </td> <% else %> <td style="border: #6f6f6f 1px solid; text-align: center;"><font face='arial, helvetica, sans-serif' size='2'><%= currentday %></font></td> <% currentday = currentday + 1 end if next 'below have added additional column header number of persent (nop) %> <td style="border-bottom: #6f6f6f 1px solid; background-color: #505050; color: #ffffff;"><b>nop</b></td> </tr> <% dim objrs1, str1, objcmd1 set objcmd1 = server.createobject("adodb.command") set objrs1 = server.createobject("adodb.recordset") str1 = "select * staff order aname asc" objcmd1 .activeconnection = mm_conndudirectory_string .commandtext = str1 .commandtype = adcmdtext end objrs1.open objcmd1, , 1, 2 if objrs1.eof %> <div class="information-box round">sorry! record(s) not found</div> <% else %> <% while not objrs1.eof %> <tr> <td nowrap style="padding: 5px 0 5px 0;border-bottom: #6f6f6f 1px solid;background-color: #f8f9fa; font-size: 13px;"> <b><%= objrs1("aname") %></b> </td> <% firstday = dateserial(myyear, mymonth, 1) currentday = 1 col = 1 31 if currentday > lastday(mymonth, myyear) %> <td style="border: #6f6f6f 1px solid; text-align: center;"> </td> <% else %> <td style="border: #6f6f6f 1px solid; text-align: center;background-color: #fffccc;"><font face='arial, helvetica, sans-serif' size='2'> <% 'below code match above retrieved staff record in "staff_attendance" table dim objrs1x, str1x, objcmd1x set objcmd1x = server.createobject("adodb.command") set objrs1x = server.createobject("adodb.recordset") str1x = "select staff_aid, staff_attn_dt, staff_att_takenby staff_attendance staff_aid=? , day(staff_attn_dt)=? , month(staff_attn_dt)=? , year(staff_attn_dt)=?" objcmd1x .activeconnection = mm_conndudirectory_string .commandtext = str1x .commandtype = adcmdtext .parameters.append(.createparameter("@param4gk1", adinteger, adparaminput, ,objrs1("aid"))) .parameters.append(.createparameter("@param4gk22", advarchar, adparaminput, len(myyear))) .parameters.append(.createparameter("@param4gk2", advarchar, adparaminput, len(mymonth))) .parameters.append(.createparameter("@param4gk21", advarchar, adparaminput, len(myyear))) .parameters("@param4gk1").value = objrs1("aid") .parameters("@param4gk22").value = col .parameters("@param4gk2").value = mymonth .parameters("@param4gk21").value = myyear end objrs1x.open objcmd1x, , 1, 2 if not objrs1x.eof %> <span style="color: #0000ff;" title="taken user: <%=objrs1x("staff_att_takenby")%>"><b>p</b></span></font> <% else %> <span style="color: #ff0000;"><b>a</b></span></font> <% end if objrs1x.close set objrs1x = nil set objcmd1x = nil %> </td> <% currentday = currentday + 1 end if %> <% next 'below code show number of persent (nop) in column %> <td style="border-bottom: #6f6f6f 1px solid; background-color: #505050; color: #ffffff;"> <% dim objrs1xq, str1xq, objcmd1xq set objcmd1xq = server.createobject("adodb.command") set objrs1xq = server.createobject("adodb.recordset") str1xq = "select count(staff_aid) nop staff_attendance staff_aid=? , month(staff_attn_dt)=? , year(staff_attn_dt)=?" objcmd1xq .activeconnection = mm_conndudirectory_string .commandtext = str1xq .commandtype = adcmdtext .parameters.append(.createparameter("@param4gk1x", adinteger, adparaminput, ,objrs1("aid"))) .parameters.append(.createparameter("@param4gk2x", advarchar, adparaminput, len(mymonth))) .parameters.append(.createparameter("@param4gk21x", advarchar, adparaminput, len(myyear))) .parameters("@param4gk1x").value = objrs1("aid") .parameters("@param4gk2x").value = mymonth .parameters("@param4gk21x").value = myyear end objrs1xq.open objcmd1xq, , 1, 2 if not objrs1xq.eof %> <b><%=objrs1xq("nop")%></b> <% end if objrs1xq.close set objrs1xq = nil set objcmd1xq = nil %> </td> </tr> <% objrs1.movenext() wend %> <% end if objrs1.close set objrs1 = nil %> </table> <% function monthname(mymonth) select case mymonth case 1 monthname = "january" case 2 monthname = "febuary" case 3 monthname = "march" case 4 monthname = "april" case 5 monthname = "may" case 6 monthname = "june" case 7 monthname = "july" case 8 monthname = "august" case 9 monthname = "september" case 10 monthname = "october" case 11 monthname = "november" case 12 monthname = "december" case else monthname = "error!" end select end function function lastday(mymonth, myyear) ' returns lastly day of month. takes business relationship jump years select case mymonth case 1, 3, 5, 7, 8, 10, 12 lastday = 31 case 4, 6, 9, 11 lastday = 30 case 2 if isdate(myyear & "-" & mymonth & "-" & "29") lastday = 29 else lastday = 28 case else lastday = 0 end select end function %> vbscript asp-classic time-and-attendance
Comments
Post a Comment