wageorder1.asp

设计广州农信管理系统,具有一般的存取款功能等客户服务.

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/DatabaseConnection.asp" -->
<%
Dim strSheetName, strMemberID
strSheetName = Request("SheetName")
strOrderField=Request("OrderField")

set rsSum = Server.CreateObject("ADODB.Recordset")
rsSum.ActiveConnection = MM_DatabaseConnection_STRING
rsSum.Source = "SELECT *  FROM " & strSheetName & " WHERE MemberID like '" & left(strMemberID,4) & "%'  ORDER BY MemberID"
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_DatabaseConnection_STRING
rsOrder.Source = "SELECT *  FROM " & strSheetName & " WHERE MemberID like '" & left(strMemberID,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 = strClass + 1
Set rsClass = Server.CreateObject("ADODB.Recordset")
rsClass.ActiveConnection = MM_DatabaseConnection_STRING
rsClass.Source = "SELECT * FROM Departments WHERE Department = " + Replace(rsClass__MMColParam, "'", "''") + " ORDER BY DepartmentID 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_DatabaseConnection_STRING
  rsOrder.Source = "SELECT *  FROM " & strSheetName & " WHERE MemberID like '" & left(strMemberID,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("WageOrder2.asp?sheetName=" & strSheetName & "&Class=" & strClass & "&classselect=" & strClass &"&OrderField=" & strOrderField)

%>