equi_statistic_by_type.inc

物业管理和办公自动化系统

<%
Function ListTableRecords(sSQL, iCols)
'	response.write sSQL & "<br>"
'	response.end
	dim sTemp	: sTemp = ""	' 临时字符串变量
	dim j					' 临时循环变量

	'------------------------------------
	' 记录序号
	'------------------------------------
	dim iRecCounter		: iRecCounter = 0
	
	'------------------------------------
	' 获取数据库连接
	'------------------------------------
	dim iLastTypeId, iThisTypeId, sTypeDesc, iDiscarded, iCount
	
	dim iEquiInUse			: iEquiInUse = 0				'' 某种设备类别的在用设备数
	dim iEquiDiscarded	: iEquiDiscarded = 0			'' 某种设备类别的已报废设备数
	dim iEquiCount			: iEquiCount = 0				'' 某种设备类别所有（在用和已报废的）的设备数

	dim iEquiTotalInUse			: iEquiTotalInUse = 0			'' 总的在用设备数
	dim iEquiTotalDiscarded	: iEquiTotalDiscarded = 0		'' 总的已报废的设备数
	dim iEquiTotal					: iEquiTotal = 0						'' 总的设备数

	dim rs	: set rs = Openrs(conn,sSQL)

	'' 初始化设备标识，取第一条记录的设备类别标识，
	'' 则在while循环中取到第一条记录时总认为是老的设备类型，
	'' 要等取第种个设备类型的时候才会写第一种设备类型的设备数
	if not rs.EOF then iLastTypeId = GetValue(rs, "type_id")

	while not rs.EOF

		'' 先取设备类型标识，看是新的设备类型还是原来的设备类型
		'' 如果是新的设备类型标识，则先写上一条数据，然后再取本条记录；否则直接取本条数据
		iThisTypeId	= GetValue(rs, "type_id")
		if iThisTypeId <> iLastTypeId then
			iRecCounter	= iRecCounter + 1	'' 记录数（行数）加一
			sTemp = sTemp & _
					"<tr bgcolor=white>" & _
						"	<td align=center>" & iRecCounter & "</td>" & vbLF & _
						"	<td align=center><a href=""equi_list.asp?type_id=" & iLastTypeId & "&"">" & sTypeDesc & "</a>&nbsp;</td>" & vbLF & _
						"	<td align=center>" & iEquiInUse & "</td>" & vbLF & _
						"	<td align=center>" & iEquiDiscarded & "</td>" & vbLF & _
						"	<td align=center>" & iEquiCount & "</td>" & vbLF & _
					"</tr>" & vbLF			

			'' 重置在用设备数和已报废设备数为0，以及这两者之和
			iEquiInUse = 0
			iEquiDiscarded = 0
			iEquiCount = 0
			'' 本设备类型标识作为上一个设备类型标识
			iLastTypeId = iThisTypeId
		end if
		sTypeDesc	= GetValue(rs, "type_desc")
		iCount			= GetValue(rs, "equi_count")
		iDiscarded	= GetValue(rs, "discarded")
		
		if iDiscarded = 1 then				'' 表示已报废设备
			iEquiDiscarded = iEquiDiscarded + iCount						'' 本设备类型的报废设备数
			iEquiCount = iEquiCount + iCount									'' 本设备类型总的设备数
			iEquiTotalDiscarded = iEquiTotalDiscarded + iCount		'' 所有设备类型总的设备数
		else											'' 表示在用设备
			iEquiInUse = iEquiInUse + iCount									'' 本设备类型的在用设备数
			iEquiCount = iEquiCount + iCount									'' 本设备类型总的设备数
			iEquiTotalInUse = iEquiTotalInUse + iCount						'' 所有设备类型总的设备数
		end if
		rs.movenext
	wend
	iEquiTotal = iEquiTotalInUse + iEquiTotalDiscarded

	iRecCounter = iRecCounter + 1
	'' 写最后一种设备类型的设备数
	sTemp = sTemp & _
			"<tr bgcolor=white>" & vbLF & _
				"	<td align=center>" & iRecCounter & "</td>" & vbLF & _
				"	<td align=center><a href=""equi_list.asp?type_id=" & iThisTypeId & "&"">" & sTypeDesc & "&nbsp;</td>" & vbLF & _
				"	<td align=center>" & iEquiInUse & "</td>" & vbLF & _
				"	<td align=center>" & iEquiDiscarded & "</td>" & vbLF & _
				"	<td align=center>" & iEquiCount & "</td>" & vbLF & _
			"</tr>" & vbLF

	'' 再写“合计”统计数据
	sTemp = sTemp & "<tr bgcolor=white style=""font-weight:600"">" & vbLF & _
				"	<td align=center colspan=2>合　计</td>" & vbLF & _
				"	<td align=center>" & iEquiTotalInUse & "</td>" & vbLF & _
				"	<td align=center>" & iEquiTotalDiscarded & "</td>" & vbLF & _
				"	<td align=center>" & iEquiTotal & "</td>" & vbLF & _
				"</tr>" & vbLF

	ListTableRecords = _
		"<table cellspacing=1 cellpadding=3 width=""100%"" border=0 align=center  bgcolor=silver style=""table-layout:fixed"">" & vbLF & _
		"<tr style=""display:none"">" & vbLF & _
		"	<td width=60>序号</td>" & vbLF & _
		"	<td width=180>设备类别</td>" & vbLF & _
		"	<td width=130>在用设备数</td>" & vbLF & _
		"	<td width=130>已报废设备数</td>" & vbLF & _
		"	<td width=""*"">总的设备数</td></tr>" & vbLF & _
 
		"<tr><td colspan=" & iCols & " bgcolor=""#0040a0"" align=center style=""color:white;font-weight:600"" height=20 >按　类　型　统　计　设　备</td></tr>" & vbLF & _
		"<tr bgcolor=""#eeeeee"">" &  vbLF & _
		"	<td nowrap class=tdHead align=center>序号</td>" & vbLF & _
		"	<td nowrap class=tdHead align=center>&nbsp;<img src=""../images/bg/ar-y.gif"" width=8 height=8>&nbsp;" & _
						TableHeaderField("", "type_desc", "设备类别", "", sSorting, sSorted) & "</td>" & vbLF & _
		"	<td nowrap class=tdHead align=center>在用设备数</td>" & vbLF & _
		"	<td nowrap class=tdHead align=center>已报废设备数</td>" & vbLF & _
		"	<td nowrap class=tdHead align=center>" & _
						TableHeaderField("", "equi_count", "设备数", "", sSorting, sSorted) & "</td>" & vbLF & _
		"</tr>" & vbLF & _

		sTemp & _

		"</table>" & vbLF
End Function



Sub PrepareOrderbyStmt(sSortFields, sDelimiter)
	dim arrayFields
	dim bFlag, i

	'--------------------------------------
	' 排序列、排序方向、排序参数
	'--------------------------------------
	arrayFields = Split(sSortFields, sDelimiter)	'' 分隔排序字段
	bFlag = false
	for i = LBound(arrayFields) to UBound(arrayFields)
		if sSorting = arrayFields(i) then
			bFlag = true				'' 标志找到了匹配的排序字段
			exit for
		end if
	next

	if IsEmpty(sSorting) then
		sSorting = ""
	elseif bFlag = false then
		sSorting = ""						'' 如果没有找到匹配的排序字段，则不排序
	else
		if sSorting = sSorted then	' 这一列是上次排序的列，则将iSorted的值置空""(降序排列)，否则置iSort的值(升序排列)
			sDirection = " desc"
			sSortParams = "Sorting=" & sSorting & "&Sorted=" & sSorted & "&"
			sSorted = ""
		else
			sDirection = " asc"
			sSortParams = "Sorting=" & sSorting & "&Sorted=" & "&"
			sSorted = sSorting
		end if
	end if
	if sSorting <> "" then
		sOrderby = " order by " & sSorting & sDirection
	end if
End Sub
%>