report.asp

AspMaker调用的自定义包

<!--##session reportheader##-->
<!--##
Set SRCTABLE = DB.Tables(TABLE.TblRptSrc)
sTblVar = TABLE.TblVar
##-->
<%
' ASPMaker 5 configuration

' Table Level Constants
Const ewTblVar = "<!--##=sTblVar##-->"
Const ewTblShowMaster = "<!--##=ShowMaster##-->"
Const ewSessionTblMasterKey = "<!--##=sTblVar##-->_MasterKey"
Const ewSessionTblMasterWhere = "<!--##=sTblVar##-->_MasterWhere"
Const ewSessionTblDetailWhere = "<!--##=sTblVar##-->_DetailWhere"

' Report Level SQL
<!--##
nGroups = GroupByFieldCount ' Number of Group By Fields
sGroupFldNames = GroupByFieldNames ' List of Group By Field Names
sGroupFldSrcs = GroupByFieldSources ' List of Group By Field Sources
sGroupFlds = GroupByFields ' List of Group By Fields
nOrders = OrderByFieldCount ' Number of Order By Fields
sDefaultOrderBy = OrderByFieldSources ' List of Order By Field Sources

If SRCTABLE.TblType = "CUSTOMVIEW" Then
	sSelectPart = ewSQLPart(SRCTABLE.TblCustomSQL, "SELECT")
	sFromPart =  ewSQLPart(SRCTABLE.TblCustomSQL, "FROM")
	sWherePart = DoubleQuote(ewSQLPart(SRCTABLE.TblCustomSQL, "WHERE"), 1)
	sGroupByPart = ewSQLPart(SRCTABLE.TblCustomSQL, "GROUP BY")
	sHavingPart = ewSQLPart(SRCTABLE.TblCustomSQL, "HAVING")
	sOrderByPart = ewSQLPart(SRCTABLE.TblCustomSQL, "ORDER BY")
	If sOrderByPart <> "" Then
		If sDefaultOrderBy <> "" Then sDefaultOrderBy = sDefaultOrderBy & ", "
		sDefaultOrderBy = sDefaultOrderBy & sOrderByPart
	End If
Else
	sSelectPart = "*"
	sFromPart = DB.DBQuoteS & SRCTABLE.TblName & DB.DBQuoteE
	sWherePart = SRCTABLE.TblFilter
	sGroupByPart = ""
	sHavingPart = ""
End If

If sWherePart = "" Then sWherePart = """""" ' Empty String

If nGroups > 0 Then

	' Report Group Level SQL
	'     SELECT DISTINCT [Group-By FIELDS] FROM [TABLE/VIEW]
	'     ORDER BY [Group-By FIELDS]

	sGrpSelectPart = sGroupFldSrcs ' Use Group Fields
	sGrpOrderByPart = sGroupFlds
##-->
' Report Group Level SQL
Const ewSqlGrpSelect = "SELECT DISTINCT <!--##=Quote(sGrpSelectPart)##--> FROM <!--##=Quote(sFromPart)##-->"
Dim ewSqlGrpWhere
ewSqlGrpWhere = <!--##=sWherePart##-->
Const ewSqlGrpGroupBy = "<!--##=Quote(sGroupByPart)##-->"
Const ewSqlGrpHaving = "<!--##=Quote(sHavingPart)##-->"
Const ewSqlGrpOrderBy = "<!--##=Quote(sGrpOrderByPart)##-->"
<!--##
End If

' Report Detail Level SQL
'     SELECT * FROM [TABLE/VIEW] WHERE [Group-By FIELDS] = ...
'     ORDER BY [Order-By FIELDS]

sDtlOrderByPart = sDefaultOrderBy
##-->
' Report Detail Level SQL
Const ewSqlDtlSelect = "SELECT <!--##=Quote(sSelectPart)##--> FROM <!--##=Quote(sFromPart)##-->"
Dim ewSqlDtlWhere
ewSqlDtlWhere = <!--##=sWherePart##-->
Const ewSqlDtlGroupBy = "<!--##=Quote(sGroupByPart)##-->"
Const ewSqlDtlHaving = "<!--##=Quote(sHavingPart)##-->"
Const ewSqlDtlOrderBy = "<!--##=Quote(sDtlOrderByPart)##-->"
<!--##
If bMasterTableExist Then
	If MASTERTABLE.TblType = "CUSTOMVIEW" Then
		sSelectPart = ewSQLPart(MASTERTABLE.TblCustomSQL, "SELECT")
		sFromPart = ewSQLPart(MASTERTABLE.TblCustomSQL, "FROM")
		sWherePart = DoubleQuote(ewSQLPart(MASTERTABLE.TblCustomSQL, "WHERE"), 1)
		sGroupByPart = ewSQLPart(MASTERTABLE.TblCustomSQL, "GROUP BY")
		sHavingPart = ewSQLPart(MASTERTABLE.TblCustomSQL, "HAVING")
		sOrderByPart = ewSQLPart(MASTERTABLE.TblCustomSQL, "ORDER BY")
	Else
		sSelectPart = "*"
		sFromPart = DB.DBQuoteS & MASTERTABLE.TblName & DB.DBQuoteE
		sWherePart = MASTERTABLE.TblFilter
		sGroupByPart = ""
		sHavingPart = ""
		sOrderByPart = ""
	End If
	sMasterTblName = sFromPart
	If sWherePart = "" Then sWherePart = """""" ' Empty String
##-->
Const ewSqlMasterSelect = "SELECT <!--##=Quote(sSelectPart)##--> FROM <!--##=Quote(sFromPart)##-->"
Dim ewSqlMasterWhere
ewSqlMasterWhere = <!--##=sWherePart##-->
Const ewSqlMasterGroupBy = "<!--##=Quote(sGroupByPart)##-->"
Const ewSqlMasterHaving = "<!--##=Quote(sHavingPart)##-->"
Const ewSqlMasterOrderBy = "<!--##=Quote(sOrderByPart)##-->"
<!--##
	sMasterFilter = ""
	sDetailFilter = ""
	For i = 0 to UBound(arDetailFlds)
		Set FIELD = MASTERTABLE.Fields(arMasterFlds(i))
		sMasterFldName = ewFieldName()
		Set FIELD = TABLE.Fields(arDetailFlds(i))
		sDetailFldName = ewFieldName()
		sDetailFldVar = FIELD.FldVar
		sDetailFldVar2 = Mid(sDetailFldVar, 3)
		If sMasterFilter <> "" Then sMasterFilter = sMasterFilter & " AND "
		sMasterFilter = sMasterFilter & Quote(sMasterFldName) & " = " & FIELD.FldQuoteS & "@" & sDetailFldVar2 & FIELD.FldQuoteE
		If sDetailFilter <> "" Then sDetailFilter = sDetailFilter & " AND "
		sDetailFilter = sDetailFilter & Quote(sDetailFldName) & " = " & FIELD.FldQuoteS & "@" & sDetailFldVar2 & FIELD.FldQuoteE
	Next
##-->
Const ewSqlMasterFilter = "<!--##=sMasterFilter##-->"
Const ewSqlDetailFilter = "<!--##=sDetailFilter##-->"
<!--##
End If

If bTableHasUserIDFld Then
		Set FIELD = TABLE.Fields(TABLE.TblUserIDFld)
		sFldName = ewFieldName()
		sFldQuoteS = FIELD.FldQuoteS
		sFldQuoteE = FIELD.FldQuoteE
##-->
Const ewSqlUserIDFilter	= "<!--##=Quote(sFldName)##--> = <!--##=sFldQuoteS##-->#UserID<!--##=sFldQuoteE##-->"
<!--##
Else
##-->
Const ewSqlUserIDFilter = ""
<!--##
End If
##-->
%>
<!--## If (bTableHasUserIDFld Or bMasterTableHasUserIDFld) And bParentUserID Then ##-->
<!--##
	If bTableHasUserIDFld Then
		Set FIELD = TABLE.Fields(TABLE.TblUserIDFld)
	Else
		Set FIELD = MASTERTABLE.Fields(MASTERTABLE.TblUserIDFld)
	End If
	sTblUserIDFldName = ewFieldName
	Set SECTBL = DB.Tables(PROJ.SecTbl)
	If SECTBL.TblType = "CUSTOMVIEW" Then
		sFromPart = ewSQLPart(SECTBL.TblCustomSQL, "FROM")
	Else
		sFromPart = DB.DBQuoteS & SECTBL.TblName & DB.DBQuoteE
	End If
	Set FIELD = SECTBL.Fields(DB.SecuUserIDFld)
	sUserIDFldName = ewFieldName
	Set FIELD = SECTBL.Fields(DB.SecuParentUserIDFld)
	sParentUserIDFldName = ewFieldName
	sQuoteS = FIELD.FldQuoteS
	sQuoteE = FIELD.FldQuoteE
##-->
<%
Dim sParentUserIDQuery
' Function to get the user id query for parent user
Function GetParentUserIDQuery(sUserID)

	Dim sWrk, sSql, rs

	<!--## If Not bUseSubQuery Then ##-->

	' List all values
	sSql = "SELECT <!--##=Quote(sUserIDFldName)##--> FROM <!--##=sFromPart##--> WHERE <!--##=sParentUserIDFldName##--> = <!--##=sQuoteS##-->" & AdjustSql(sUserID) & "<!--##=sQuoteE##-->"
	Set rs = conn.Execute(sSql)
	sWrk = ""
	Do Until rs.Eof
		If sWrk <> "" Then sWrk = sWrk & ","
		sWrk = sWrk & "<!--##=sQuoteS##-->" & AdjustSql(rs("<!--##=DB.SecuUserIDFld##-->")) & "<!--##=sQuoteE##-->"
		rs.MoveNext
	Loop
	rs.Close
	Set rs = Nothing

	<!--## Else ' Use subquery ##-->

	sWrk = "SELECT <!--##=Quote(sUserIDFldName)##--> FROM <!--##=sFromPart##--> WHERE <!--##=sParentUserIDFldName##--> = <!--##=sQuoteS##-->" & AdjustSql(sUserID) & "<!--##=sQuoteE##-->"

	<!--## End If ##-->

	If sWrk <> "" Then
		sWrk = "<!--##=Quote(sTblUserIDFldName)##--> IN (" & sWrk & ")"
	End If

	GetParentUserIDQuery = sWrk

End Function
%>
<!--## End If ##-->
<!--## If bMasterTableHasUserIDFld Then ##-->
<!--##
	Set FIELD = MASTERTABLE.Fields(MASTERTABLE.TblUserIDFld)
	sQuoteS = FIELD.FldQuoteS
	sQuoteE = FIELD.FldQuoteE
	sTblUserIDFldName = ewFieldName
	If MASTERTABLE.TblType = "CUSTOMVIEW" Then
		sFromPart = ewSQLPart(MASTERTABLE.TblCustomSQL, "FROM")
	Else
		sFromPart = DB.DBQuoteS & MASTERTABLE.TblName & DB.DBQuoteE
	End If
##-->
<%
Dim sMasterUserIDQuery, sTmpQuery

' Function to get the user id query for parent user of the master table
Function GetMasterUserIDQuery(sUserID)

	Dim sWrk, sSql, rs
	sWrk = ""

	<!--## If Not bUseSubQuery Then ##-->

	' List all values
	sSql = "SELECT <!--##=sMasterFldList##--> FROM <!--##=sFromPart##--> WHERE <!--##=sTblUserIDFldName##--> = <!--##=sQuoteS##-->" & AdjustSql(sUserID) & "<!--##=sQuoteE##-->"
	<!--## If bParentUserID Then ##-->
	Dim sSqlParent
	sSqlParent = GetParentUserIDQuery(sUserID)
	If sSqlParent <> "" Then
		sSql = sSql & " OR " & sSqlParent
	End If
	<!--## End If ##-->
	Set rs = conn.Execute(sSql)
	Do Until rs.Eof
		If sWrk <> "" Then sWrk = sWrk & " OR "
		sWrk = sWrk & "("
	<!--##
	 For i = 0 to UBound(arMasterFlds)
	 	Set MASTERFLD = MASTERTABLE.Fields(arMasterFlds(i))
		sMasterFldQuoteS = MASTERFLD.FldQuoteS
		sMasterFldQuoteE = MASTERFLD.FldQuoteE
	##-->
		sWrk = sWrk & "#Key<!--##=i##-->=<!--##=sMasterFldQuoteS##-->" & AdjustSql(rs(<!--##=i##-->)) & "<!--##=sMasterFldQuoteE##--> AND "
	<!--## Next ##-->
		If Right(sWrk, 5) = " AND " Then sWrk = Mid(sWrk, 1, Len(sWrk)-5)
		sWrk = sWrk & ")"
		rs.MoveNext
	Loop
	rs.Close
	Set rs = Nothing

	<!--## Else ' Use subquery ##-->

	sWrk = "SELECT #MasterKey FROM <!--##=sFromPart##--> WHERE <!--##=sTblUserIDFldName##--> = <!--##=sQuoteS##-->" & AdjustSql(sUserID) & "<!--##=sQuoteE##-->"
	<!--## If bParentUserID Then ##-->
	sWrk = sWrk & " OR " & GetParentUserIDQuery(sUserID)
	<!--## End If ##-->

	If sWrk <> "" Then
		sWrk = "#Key IN (" & sWrk & ")"
	End If

	<!--## End If ##-->

	GetMasterUserIDQuery = sWrk

End Function
%>
<!--## End If ##-->
<!--##
bShowSummary = False ' Show Summary
bAggSum = False ' Aggregate Sum
bAggAvg = False ' Aggregate Average
bAggMin = False ' Aggregate Minimum
bAggMax = False ' Aggregate Maximum
For i = 1 to TABLE.Fields.Count
	Set FIELD = TABLE.Fields.Seq(i)
	If FIELD.FldGroupByShowSummary <> 0 Then
		bShowSummary = True
		Exit For
	End If 
Next
If bShowSummary Then
For j = 1 to TABLE.Fields.Count
	Set FIELD = TABLE.Fields.Seq(j)
	If FIELD.FldList And FIELD.FldGroupBy <= 0 And FIELD.FldGenerate And FIELD.FldHtmlTag <> "FILE" Then
		If FIELD.FldRptAggSum Then
			bAggSum = True
		End If
		If FIELD.FldRptAggAvg Then
			bAggAvg = True
		End If
		If FIELD.FldRptAggMin Then
			bAggMin = True
		End If
		If FIELD.FldRptAggMax Then
			bAggMax = True
		End If
	End If
Next
End If
bAggregate = bAggSum Or bAggAvg Or bAggMin Or bAggMax

If TABLE.TblRptIndent = 0 Then
	sIndent = ""
Else
	sIndent = "<td" & ewCSSGroupIndent & "></td>"
End If
##-->
<%
<!--## If PROJ.OptionExplicit Then ##-->
Dim nRecCount, nGrpRecs, nDtlnRecCount, nDtlRecs, sFilter
Dim sDbWhereMaster, sDbWhereDetail, sCmd
Dim rsdtl
<!--## End If ##-->

<!--##
If bMasterTableExist And PROJ.OptionExplicit Then
##-->
Dim sSqlMaster, bMasterRecordExist
<!--##
End If
##-->

<!--##
nFlds = 0 ' No. Of Selected Fields
For i = 1 to TABLE.Fields.Count
	Set FIELD = TABLE.Fields.Seq(i)
	If FIELD.FldList And FIELD.FldGroupBy <= 0 And FIELD.FldGenerate And FIELD.FldHtmlTag <> "FILE" Then
		nFlds = nFlds + 1
	End If
	If PROJ.OptionExplicit Then
##-->
Dim grp_<!--##=FIELD.FldVar##-->, tmp_<!--##=FIELD.FldVar##-->
<!--##
	End If
	If FIELD.FldRptAggSum Or FIELD.FldRptAggAvg Then
##-->
Dim s<!--##=FIELD.FldVar##-->(<!--##=nGroups##-->) ' Array for SUM/AVERAGE aggregation
<!--##
	End If
	If FIELD.FldRptAggMin Then
##-->
Dim n<!--##=FIELD.FldVar##-->(<!--##=nGroups##-->) ' Array for MINIMUM aggregation
<!--##
	End If
	If FIELD.FldRptAggMax Then
##-->
Dim m<!--##=FIELD.FldVar##-->(<!--##=nGroups##-->) ' Array for MAXIMUM aggregation
<!--##
	End If
Next
##-->
Dim nCntRecs(<!--##=nGroups##-->)
Dim bLvlBreak(<!--##=nGroups##-->)

<!--##
If bMasterTableExist Then
##-->
' Set up Master Detail criteria
If Request.QueryString(ewTblShowMaster).Count > 0 Then
	sDbWhereMaster = ewSqlMasterFilter
	sDbWhereDetail = ewSqlDetailFilter
<!--##
	For i = 0 to UBound(arDetailFlds)
		Set FIELD = TABLE.Fields(arDetailFlds(i))
		Set DETAILFIELD = FIELD
		sDetailFldVar = FIELD.FldVar
		sDetailFldVar2 = Mid(sDetailFldVar, 3)
		sDetailFldName = ewFieldName()
##-->
	<!--##=sDetailFldVar##--> = Request.QueryString("<!--##=sDetailFldVar2##-->") ' Load Parameter from QueryString
	sDbWhereMaster = Replace(sDbWhereMaster, "@<!--##=sDetailFldVar2##-->", AdjustSql(<!--##=sDetailFldVar##-->)) ' Replace key value
	sDbWhereDetail = Replace(sDbWhereDetail, "@<!--##=sDetailFldVar2##-->", AdjustSql(<!--##=sDetailFldVar##-->)) ' Replace key value
	Session(ewSessionTblMasterKey & "_<!--##=sDetailFldVar2##-->") = <!--##=sDetailFldVar##--> ' Save Master Key Value
<!--##
	Next
##-->
	Session(ewSessionTblMasterWhere) = sDbWhereMaster
	Session(ewSessionTblDetailWhere) = sDbWhereDetail
ElseIf Request.QueryString("cmd").Count > 0 Then
	sCmd = Request.QueryString("cmd")
	If LCase(sCmd) = "resetall" Then
		Session(ewSessionTblMasterWhere) = "" ' Clear master criteria
		sDbWhereMaster = ""
		Session(ewSessionTblDetailWhere) = "" ' Clear detail criteria
		sDbWhereDetail = ""
<!--##
	For i = 0 to UBound(arDetailFlds)
		Set DETAILFIELD = TABLE.Fields(arDetailFlds(i))
		sDetailFldVar = DETAILFIELD.FldVar
		sDetailFldVar2 = Mid(sDetailFldVar, 3)
##-->
		Session(ewSessionTblMasterKey & "_<!--##=sDetailFldVar2##-->") = "" ' Clear Master Key Value
<!--##
	Next
##-->
	End If
Else
	sDbWhereMaster = Session(ewSessionTblMasterWhere)
	sDbWhereDetail = Session(ewSessionTblDetailWhere)
End If
<!--##
End If
##-->

' Open connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
%>
<!--##
 If bMasterTableExist Then
	sTblVar = TABLE.TblVar ' Get detail table var
	Set DETAILTABLE = TABLE ' Save current table
	Set TABLE = MASTERTABLE
##-->
<%
' Build Master Record SQL
If sDbWhereMaster <> "" Then

<!--##
If bMasterTableHasUserIDFld Then
##-->
	If CurrentUserID <> "-1" And CurrentUserID <> "" Then ' Non system admin
		sMasterUserIDQuery = GetMasterUserIDQuery(CurrentUserID)
		If sMasterUserIDQuery <> "" Then
	<!--##
	If Not bUseSubQuery Then
	##-->
			sTmpQuery = sMasterUserIDQuery
	<!--##
		For i = 0 to UBound(arMasterFldNames)
	##-->
			sTmpQuery = Replace(sTmpQuery, "#Key<!--##=i##-->", "<!--##=arMasterFldNames(i)##-->")
	<!--##
		Next
	##-->
			sDbWhereMaster = sDbWhereMaster & " AND " & sTmpQuery
	<!--##
	Else
		For i = 0 to UBound(arMasterFldNames)
	##-->
			sTmpQuery = sMasterUserIDQuery
			sTmpQuery = Replace(sTmpQuery, "#MasterKey", "<!--##=arMasterFldNames(i)##-->")
			sTmpQuery = Replace(sTmpQuery, "#Key", "<!--##=arMasterFldNames(i)##-->")
			sDbWhereMaster = sDbWhereMaster & " AND " & sTmpQuery
	<!--##
		Next
	End If
	##-->