<!--##session info##-->
<!--##
' common up variables
sTblVar = TABLE.TblVar
##-->
<%
' ASPMaker 5 configuration

' Table Level Constants
Const ewTblVar = "<!--##=sTblVar##-->"
Const ewTblRecPerPage = "RecPerPage"
Const ewSessionTblRecPerPage = "<!--##=sTblVar##-->_RecPerPage"
Const ewTblStartRec = "start"
Const ewSessionTblStartRec = "<!--##=sTblVar##-->_start"
Const ewTblShowMaster = "<!--##=ShowMaster##-->"
Const ewSessionTblMasterKey = "<!--##=sTblVar##-->_MasterKey"
Const ewSessionTblMasterWhere = "<!--##=sTblVar##-->_MasterWhere"
Const ewSessionTblDetailWhere = "<!--##=sTblVar##-->_DetailWhere"
Const ewSessionTblAdvSrch = "<!--##=sTblVar##-->_AdvSrch"
Const ewTblBasicSrch = "psearch"
Const ewSessionTblBasicSrch = "<!--##=sTblVar##-->_psearch"
Const ewTblBasicSrchType = "psearchtype"
Const ewSessionTblBasicSrchType = "<!--##=sTblVar##-->_psearchtype"
Const ewSessionTblSearchWhere = "<!--##=sTblVar##-->_SearchWhere"
Const ewSessionTblSort = "<!--##=sTblVar##-->_Sort"
Const ewSessionTblOrderBy = "<!--##=sTblVar##-->_OrderBy"
Const ewSessionTblKey = "<!--##=sTblVar##-->_Key"

' Table Level SQL
<!--##
nOrders = OrderByFieldCount ' Number of Order By Fields
sOrderFlds = OrderByFieldNames ' List of Order By Field Names
sDefaultOrderBy = OrderByFieldSources ' List of Order By Field Sources

If TABLE.TblType = "CUSTOMVIEW" Then
	sSelectPart = ewSQLPart(TABLE.TblCustomSQL, "SELECT")
	sFromPart = ewSQLPart(TABLE.TblCustomSQL, "FROM")
	sWherePart = ewSQLPart(TABLE.TblCustomSQL, "WHERE")
	sGroupByPart = ewSQLPart(TABLE.TblCustomSQL, "GROUP BY")
	sHavingPart = ewSQLPart(TABLE.TblCustomSQL, "HAVING")
	sOrderByPart = ewSQLPart(TABLE.TblCustomSQL, "ORDER BY")
	If sOrderByPart <> "" Then
		If sDefaultOrderBy <> "" Then sDefaultOrderBy = sDefaultOrderBy & ", "
		sDefaultOrderBy = sDefaultOrderBy & sOrderByPart
		' Set up order by session variables for custom view
		sDefaultOrderBySessions = ""
		arOrderFlds = Split(sOrderByPart, ",")
		For i = 0 to UBound(arOrderFlds)
			sOrderFld = Trim(arOrderFlds(i))
			If UCase(Right(sOrderFld, 4)) = " ASC" Then
				sSort = "ASC"
				sOrderFld = Mid(sOrderFld, 1, Len(sOrderFld)-3)
			ElseIf UCase(Right(sOrderFld, 5)) = " DESC" Then
				sSort = "DESC"
				sOrderFld = Mid(sOrderFld, 1, Len(sOrderFld)-4)
			Else
				sSort = "ASC"
			End If
			sOrderFld = Trim(sOrderFld)
			If sSort <> "" Then
				For j = 1 to TABLE.Fields.Count
					Set FIELD = TABLE.Fields(j)
					If ewFieldName = sOrderFld Then
						If sDefaultOrderBySessions <> "" Then sDefaultOrderBySessions = sDefaultOrderBySessions & ","
						sDefaultOrderBySessions = sDefaultOrderBySessions & FIELD.FldVar & "," & sSort
						Exit For
					End If
				Next
			End If
		Next
	End If
Else
	sSelectPart = "*"
	sFromPart = DB.DBQuoteS & TABLE.TblName & DB.DBQuoteE
	sWherePart = ""
	sGroupByPart = ""
	sHavingPart = ""
	' Set up order by session variables
	sDefaultOrderBySessions = ""
	arOrderFlds = Split(sOrderFlds, ",")
	For i = 0 to UBound(arOrderFlds)
		Set FIELD = TABLE.Fields(arOrderFlds(i))
		If sDefaultOrderBySessions <> "" Then sDefaultOrderBySessions = sDefaultOrderBySessions & ","
		sDefaultOrderBySessions = sDefaultOrderBySessions & FIELD.FldVar & "," & FIELD.FldOrder
	Next
End If
##-->
Const ewSqlSelect = "SELECT <!--##=Quote(sSelectPart)##--> FROM <!--##=Quote(sFromPart)##-->"
Const ewSqlWhere = "<!--##=Quote(sWherePart)##-->"
Const ewSqlGroupBy = "<!--##=Quote(sGroupByPart)##-->"
Const ewSqlHaving = "<!--##=Quote(sHavingPart)##-->"
Const ewSqlOrderBy = "<!--##=Quote(sDefaultOrderBy)##-->"
Const ewSqlOrderBySessions = "<!--##=Quote(sDefaultOrderBySessions)##-->"
<!--##
sFilter = ""
For i = 1 to TABLE.Fields.Count
	Set FIELD = TABLE.Fields.Seq(i)
	If FIELD.FldIsPrimaryKey Then
		sFldName = ewFieldName
		sFldQuoteS = FIELD.FldQuoteS
		sFldQuoteE = FIELD.FldQuoteE
		sFldVar = FIELD.FldVar
		sFldVar2 = Mid(sFldVar, 3)
		If sFilter <> "" Then sFilter = sFilter & " AND "
		sFilter = sFilter & Quote(sFldName) & " = " & sFldQuoteS & "@"  & sFldVar2 & sFldQuoteE
	End If
Next
##-->
Const ewSqlKeyWhere = "<!--##=sFilter##-->"
<!--##
If bMasterTableExist Then
	If MASTERTABLE.TblType = "CUSTOMVIEW" Then
		sSelectPart = ewSQLPart(MASTERTABLE.TblCustomSQL, "SELECT")
		sFromPart = ewSQLPart(MASTERTABLE.TblCustomSQL, "FROM")
		sWherePart = ewSQLPart(MASTERTABLE.TblCustomSQL, "WHERE")
		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 = ""
		sGroupByPart = ""
		sHavingPart = ""
		sOrderByPart = ""
	End If
	sMasterTblName = sFromPart
##-->
Const ewSqlMasterSelect = "SELECT <!--##=Quote(sSelectPart)##--> FROM <!--##=Quote(sFromPart)##-->"
Const ewSqlMasterWhere = "<!--##=Quote(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##-->"
Const ewSqlUserIDFilter	= "#Key = <!--##=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 & ")"
		sWrk = "#Key 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 " & Replace(sSqlParent, "#Key", "<!--##=Quote(sTblUserIDFldName)##-->")
	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

	If sWrk <> "" Then sWrk = "(" & sWrk & ")"

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

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

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

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

	GetMasterUserIDQuery = sWrk

End Function
%>
<!--## End If ##-->
<!--##/session##-->