scoreorder1.asp

学籍管理系统

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/StudentConn.asp" -->
<!--#include file="CheckLogin.asp"-->
<%
Dim strSheetName, strStudentNum, strGrade, strClass
strSheetName = Request("SheetName")
strGrade=Request("Grade")
strClass=Request("ClassSelect")
strOrderField=Request("OrderField")

If len(Year(Now()))<=2 Then
    NewYear="20" & Year(Now())
Else
    NewYear=Year(Now())
End If
If len(Month(Now()))<=1 Then
    NewMonth="0" & Month(Now())
Else
    NewMonth=Month(Now())
End If
if NewMonth<9 then
    strStudentNum=NewYear-strGrade-1
else
    strStudentNum=NewYear-strGrade
end if

set rsSum = Server.CreateObject("ADODB.Recordset")
rsSum.ActiveConnection = MM_StudentConn_STRING
rsSum.Source = "SELECT *  FROM " & strSheetName & " WHERE StudentNum like '" & left(strStudentNum,4) & "%'  ORDER BY StudentNum"
rsSum.CursorType = 0
rsSum.CursorLocation = 2
rsSum.LockType = 3
rsSum.Open()
rsSum_numRows = 0

'计算总分
If (NOT rsSum.BOF or Not rsSum.EOF) then
  rsSum.MoveFirst()
end if
While NOT rsSum.EOF
	intTotal=0
    for i= 5 to rsSum.Fields.Count-1
		if NOT IsNull(rsSum.Fields.Item(i).Value) then
		  intTotal= intTotal + rsSum.Fields.Item(i).Value
		end if
	next
	rsSum.Fields.Item("总分").Value = intTotal
	rsSum.Fields.Item("平均分").Value = Round(intTotal/(rsSum.Fields.Count-5),2)
	rsSum.Update
	rsSum.MoveNext()
Wend
rsSum.Close

'进行级排名
set rsOrder = Server.CreateObject("ADODB.Recordset")
rsOrder.ActiveConnection = MM_StudentConn_STRING
rsOrder.Source = "SELECT *  FROM " & strSheetName & " WHERE StudentNum like '" & left(strStudentNum,4) & "%'  ORDER BY 总分 DESC"
rsOrder.CursorType = 0
rsOrder.CursorLocation = 2
rsOrder.LockType = 3
rsOrder.Open()
rsOrder_numRows = 0
if (NOT rsOrder.BOF or NOT rsOrder.EOF) Then
OrderNum = 1
i = 1
rsOrder.MoveFirst()
tmpTotal=rsOrder.Fields.Item("总分")
rsOrder.Fields.Item("全级排名")=OrderNum
rsOrder.Update
rsOrder.MoveNext()
While NOT rsOrder.EOF
  i = i + 1
  if rsOrder.Fields.Item("总分") = tmpTotal then
	rsOrder.Fields.Item("全级排名")= OrderNum
  else
	rsOrder.Fields.Item("全级排名")= i
  end if
  tmpTotal=rsOrder.Fields.Item("总分")
  rsOrder.Update
  OrderNum = rsOrder.Fields.Item("全级排名")

  rsOrder.MoveNext()
  
Wend
End If
rsOrder.Close()

'对每个班的学生进行班排名
Dim rsClass__MMColParam
Dim rsClass
Dim rsClass_numRows
'先查询出班级
rsClass__MMColParam = strGrade + 1
Set rsClass = Server.CreateObject("ADODB.Recordset")
rsClass.ActiveConnection = MM_StudentConn_STRING
rsClass.Source = "SELECT * FROM Class WHERE Grade = " + Replace(rsClass__MMColParam, "'", "''") + " ORDER BY ClassID ASC"
rsClass.CursorType = 0
rsClass.CursorLocation = 2
rsClass.LockType = 1
rsClass.Open()
rsClass_numRows = 0

While (NOT rsClass.EOF)
  tmpClass=rsClass.Fields.Item("ClassID").Value
  if Len(CStr(tmpClass))=1 then
    tmpClass="0" & CStr(tmpClass)
  end if
  set rsOrder = Server.CreateObject("ADODB.Recordset")
  rsOrder.ActiveConnection = MM_StudentConn_STRING
  rsOrder.Source = "SELECT *  FROM " & strSheetName & " WHERE StudentNum like '" & left(strStudentNum,4) & tmpClass & "%'  ORDER BY 总分 DESC"
  rsOrder.CursorType = 0
  rsOrder.CursorLocation = 2
  rsOrder.LockType = 3
  rsOrder.Open()
  rsOrder_numRows = 0
  
 if Not rsOrder.EOF Or Not rsOrder.BOF Then
  OrderNum = 1
  i = 1
  rsOrder.MoveFirst()
  tmpTotal=rsOrder.Fields.Item("总分")
  rsOrder.Fields.Item("班级排名")=OrderNum
  rsOrder.Update
  rsOrder.MoveNext()
  While NOT rsOrder.EOF
    i = i + 1
    if rsOrder.Fields.Item("总分") = tmpTotal then
	  rsOrder.Fields.Item("班级排名")= OrderNum
    else
      rsOrder.Fields.Item("班级排名")= i
    end if
    tmpTotal=rsOrder.Fields.Item("总分")
    rsOrder.Update
    OrderNum = rsOrder.Fields.Item("班级排名")
  
    rsOrder.MoveNext()
  
  Wend
 end if
 rsOrder.Close()
 rsClass.MoveNext 
Wend

Response.Redirect("ScoreQuery3.asp?sheetName=" & strSheetName & "&grade=" & strGrade & "&classselect=" & strClass &"&OrderField=" & strOrderField)

%>