PC@LLの出席データ
attendance.aspを開いて検索して表示、ダウンロードはattend_down.aspって面倒くさい仕様でやってきたので魔改造することにした。
改造するファイルリスト
改造しないけど、参考
- d:\attendance\room\attendance.asp
- Microsoft SQL Server Manegement Studioで「ファイル＞オブジェクト エクスプローラーを接続」、データベースエンジン、SQLEXPRESS、Windows認証、パスワードとかはdb2table
- 接続したら「データベース＞room＞テーブル＞dbo.ROLLCALL＞列 右クリック「上位１０００件の選択」で確認する
- 保持しているデータは少ないdata,login,name,seat,room_nameぐらい
top.asp
「d:\attendance\room\attendance.asp」を参考にしてページ遷移することなく、１ページで完了させるために徹底的に魔改造を施す。jQueryも使っちゃう。
top.aspファイルをテキストエディターで作成する
オリジナルよりかは綺麗になっているはず、、、
<%@Language="VBScript" %>
<html>
<head>
<meta http-equiv="Content-Language" content="ja">
<meta http-equiv="Content-Type" content="text/html; charset=shift_jis">
<link rel="stylesheet" href="../common/style.css" type="text/css">
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">google.load("jquery", "1.7.1");</script>
<script type="text/javascript">
$(function(){
$("select#jigen").change(function(){
switch($("#jigen").val()){
case "0":
$("#SHour").val("12");
$("#SMinute").val("10");
$("#FHour").val("12");
$("#FMinute").val("59");
break;
case "1":
$("#SHour").val("8");
$("#SMinute").val("50");
$("#FHour").val("10");
$("#FMinute").val("30");
break;
case "2":
$("#SHour").val("10");
$("#SMinute").val("30");
$("#FHour").val("12");
$("#FMinute").val("10");
break;
case "3":
$("#SHour").val("12");
$("#SMinute").val("50");
$("#FHour").val("14");
$("#FMinute").val("30");
break;
case "4":
$("#SHour").val("14");
$("#SMinute").val("30");
$("#FHour").val("16");
$("#FMinute").val("10");
break;
case "5":
$("#SHour").val("16");
$("#SMinute").val("10");
$("#FHour").val("17");
$("#FMinute").val("50");
break;
case "6":
$("#SHour").val("17");
$("#SMinute").val("50");
$("#FHour").val("19");
$("#FMinute").val("30");
break;
case "7":
$("#SHour").val("19");
$("#SMinute").val("30");
$("#FHour").val("21");
$("#FMinute").val("30");
break;
case "9":
$("#SHour").val("0");
$("#SMinute").val("0");
$("#FHour").val("23");
$("#FMinute").val("59");
break;
}
})
})
</script>
<title>PC@LL 出席データ表示</title>
</head>
<body background="st2_1.gif">
<div id="header"><img src="pcll_attendance.gif" alt="PC@LL出席データ">
</div>
<div id="datetime">
<h1>教室</h1>
<FORM ACTION ="attendance.asp" METHOD="POST">
<!-- #INCLUDE File="db2table.asp" -->
<div class="date">
<select name="Year" size="1">
<%
For i = -1 to 0
IF Request.Form ="" And i=0 then
Response.Write("<option value='" & Year(Now) + i & "' selected='selected'> " & Year(Now) + i & "</option>")
ElseIf Request.Form <>"" And CInt(Year(Now))+i = CInt(Request.Form("Year")) then
Response.Write("<option value='" & Year(Now) + i & "' selected='selected'> " & Year(Now) + i & "</option>")
Else
Response.Write("<option value='" & Year(Now) + i & "'> " & Year(Now) + i & "</option>")
End If
Next
%>
</select>年
<select name="Month" size="1">
<%
For i = 1 to 12
If Request.Form ="" And i = CInt(Month(Now)) then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
ElseIf Request.Form <>"" And i = CInt(Request.Form("Month")) then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
Else
Response.Write("<option value='" & i & "'> " & i & "</option>")
End If
Next
%>
</select>月
<select name="Day" size="1">
<%
For i = 1 to 31
If Request.Form ="" And i = CInt(Day(Now)) then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
ElseIf Request.Form <>"" And i = CInt(Request.Form("Day")) then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
Else
Response.Write("<option value='" & i & "'> " & i & "</option>")
End If
Next
%>
</select>日
</div>
<div class="time">
<select id="jigen" name="jigen">
<option value="9">時限を選択してください</option>
<option value="1">1限</option>
<option value="2">2限</option>
<option value="0">昼</option>
<option value="3">3限</option>
<option value="4">4限</option>
<option value="5">5限</option>
<option value="6">6限</option>
<option value="7">7限</option>
</select>
<select id="SHour" name="SHour" size="1">
<%
For i = 0 to 23
If Request.Form ="" And i = 0 then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
ElseIf Request.Form <>"" And i = CInt(Request.Form("SHour")) then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
Else
Response.Write("<option value='" & i & "'> " & i & "</option>")
End If
Next
%>
</select>時
<select id="SMinute" name="SMinute" size="1">
<%
For i = 0 to 59
If Request.Form ="" And i = 0 then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
ElseIf Request.Form <>"" And i = CInt(Request.Form("SMinute")) then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
Else
Response.Write("<option value='" & i & "'> " & i & "</option>")
End If
Next
%>
</select>分 〜
<select id="FHour" name="FHour" size="1">
<%
For i = 0 to 23
If Request.Form ="" And i = 23 then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
ElseIf Request.Form <>"" And i = CInt(Request.Form("FHour")) then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
Else
Response.Write("<option value='" & i & "'> " & i & "</option>")
End If
Next
%>
</select>時
<select id="FMinute" name="FMinute" size="1">
<%
For i = 0 to 59
If Request.Form ="" And i = 59 then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
ElseIf Request.Form <>"" And i = CInt(Request.Form("FMinute")) then
Response.Write("<option value='" & i & "' selected='selected'> " & i & "</option>")
Else
Response.Write("<option value='" & i & "'> " & i & "</option>")
End If
Next
%>
</select>分
</div>
<div class="button">
<INPUT id="submit" TYPE ="submit" VALUE = "表示/更新">
</div>
</form>
</div>
<%
IF Request.Form <>"" then
%>
<!-- 日付指定後 -->
<div id="result">
<h2>検索結果</h2>
<%
FFMinutei = Cint(Request.Form("FMinute")) + 1 '終了時間調整用
'繰り上げ処理
if FFMinutei>=60 then FFMinutei=59
FFMinute=CStr(FFMinutei) '文字列化
'SQL文
mySQL = "SELECT DISTINCT date as 日付,login as ﾛｸﾞｲﾝ名,name as 氏名,seat as 座席名 FROM ROLLCALL " & _
"WHERE date BETWEEN " & _
"CAST('" & Request.Form("Year") & "/" & Request.Form("Month") & "/" & Request.Form("Day") & " " & _
Request.Form("SHour") & ":" & Request.Form("SMinute") & "' AS DATETIME) AND " & _
"CAST('" & Request.Form("Year") & "/" & Request.Form("Month") & "/" & Request.Form("Day") & " " & _
Request.Form("FHour") & ":" & Request.Form("FMinute") & "' AS DATETIME) ORDER BY date DESC;"
'データベースファイル
db2Table mySQL
%>
<!-- #INCLUDE File="download.asp" -->
<form method="POST" action="attend_down.asp">
<%
Response.Write("<input type='hidden' name='Year' value='" & Request.Form("Year") & "'>")
Response.Write("<input type='hidden' name='Month' value='" & Request.Form("Month") & "'>")
Response.Write("<input type='hidden' name='Day' value='" & Request.Form("Day") & "'>")
Response.Write("<input type='hidden' name='SHour' value='" & Request.Form("SHour") & "'>")
Response.Write("<input type='hidden' name='SMinute' value='" & Request.Form("SMinute") & "'>")
Response.Write("<input type='hidden' name='FHour' value='" & Request.Form("FHour") & "'>")
Response.Write("<input type='hidden' name='FMinute' value='" & Request.Form("FMinute") & "'>")
%>
<div class="button">
<input type="submit" value="エクセルCSVファイル保存" name="B1">
</div>
</form>
</div>
<%
End If
%>
</body>
</html>
db2table.asp
HTMLタグを修正
<%
Sub db2table(sql)
' 接続先情報を指定します。
Const strServer = "localhost\SQLEXPRESS" ' サーバ名
Const strDatabase = "room" ' データベース名
Const strUserID = "hoge" ' ユーザID
Const strPassword = "hoge_password" ' パスワード
Set cn = Server.CreateObject("ADODB.Connection")
'cn.Open "Provider=SQLOLEDB;" & _
' "Data Source=" & strServer & ";" & _
' "Initial Catalog=" & strDatabase & "", strUserID, strPassword
cn.Open "Provider=SQLOLEDB.1;Password=" & strPassword & ";Persist Security Info=True;User ID=" & strUserID & ";Initial Catalog=" & strDatabase & ";Data Source=" & strServer
Set rs = cn.Execute(sql)
'Set db=Server.CreateObject("ADODB.Connection")
'db.Provider="Microsoft.Jet.OLEDB.4.0"
'db.Mode=1
'db.ConnectionString=Server.MapPath(dbName)
'db.Open
'Set rs=db.Execute(sql)
Response.Write "<table><thead><tr>"
For i=0 To rs.Fields.Count-1
Response.Write "<th>" & rs.Fields.Item(i).Name & "</th>"
Next
Response.Write "</tr><thead><tbody>"
Do Until rs.EOF
Response.Write "<tr>"
For i=0 To rs.Fields.Count-1
Response.Write "<td>" & rs.Fields.Item(i).Value & "</td>"
Next
Response.Write "</tr>"
rs.MoveNext
Loop
Response.Write "</tbody></table>"
rs.Close
'db.Close
'Set db=Nothing
End Sub
%>
style.css
色は勝手に考える
h1,h2{}
#datetime{border:1px solid gray;}
#datetime select{font-size: 18px;}
#datetime .date,#datetime .time,#datetime .button{padding:10px;}
#result{margin:30px auto 0px auto;paddig:10px;text-align:center;}
#result table{width: 90%;line-height: 1.2em;border-collapse: collapse;}
#result thead th{font-weigth: bold;}
