<!--#include file="GenericLanguage.asp" -->
<% 
' Generic Database - Customisable List Records
' Notice: (c) 1998, 1999, 2000 Eli Robillard, All Rights Reserved. 
' E-Mail: erobillard@ofifc.org
' URL: http://www.ofifc.org/Eli/ASP/

' Using This File: Scroll down and place your HTML code between appropriate 
' <!-- GDB CUSTOM SECTION #--> and <!-- END SECTION --> tags. 
' There are 7 sections. Lines that begin with SAMPLE can be removed. 
' Further Instructions are provided in the sections.

' Revision History:
' 17 Mar 2000 - Changed default font string from "Verdana, Arial, Helvetica" to "Arial" to cut size
' 29 Feb 2000 - Minor fix to smart page numbering
' 26 Jan 2000 - "Smart" page numbering for large recordsets
' 27 Jul 1999 - First created 

On Error Resume Next

' Prevent caching
Response.Buffer = True
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "cache-control", "must-revalidate"
Response.AddHeader "cache-control", "private"
Response.AddHeader "pragma", "no-cache"

' Declare vars
DIM QUOTE, LT, GT
DIM x, y, z, bgcolor, curPage, curRec, curVal, IsPrev, IsSubTable, NextStart, PrevStart, xConn, xField, xrs
DIM aFields, arrFieldNames, arrSubTable
DIM strBorderColor, strConn, strContainsURL, strDisplay, strEditor, strFieldNames, strFields, strFont, strFormatDate, strGroupBy, strHaving, strLine, strLink, strMenuColor, strMenuTextColor, strOrderBy, strPassThru, strSearchFields, strSearchPos, strsql, strTable, strTotalFields, strType, strURL, strViewer, strWhere
DIM intActual, intAllowSort, intCount, intCSV, intDisplayRecs, intFieldCount, intFontSize, intHidePageNumbers, intOrderBy, intPage, intPageDisp, intPrimary, intStartRec, intStopRec, intTotalRecs

QUOTE = chr(34)
LT = chr(60)
GT = chr(62)
intAllowSort = 1
bgcolor="#FFFFCC"

' Check for parameters, if we jump in another direction we need to pass them on
strPassThru = "?"
if Request.QueryString("START").Count > 0 Then strPassThru = strPassThru & "START=" & Request.Querystring("START") & "&"
if Request.QueryString("ORDER").Count > 0 Then strPassThru = strPassThru & "ORDER=" & Request.Querystring("ORDER") & "&"
if strPassThru = "?" then 
	strPassThru = ""
else
	strPassThru = Left(strPassThru,Len(strPassThru)-1)
end if

' If a Template was identified, use the CustomList screen
if Not(Session("dbListTemplate") & "x" = "x") Then
	Response.Clear
	Response.Redirect "GenericCustomList.asp" & strPassthru
end If

' Quick security check, make sure we have an active session
If Session("dbConn") & "x" = "x" Then
	Response.Clear
	Response.Redirect "GenericError.asp"
End If

' Check which editor to use for Add and Edit links
If Session("dbEditTemplate") & "x" = "x" Then
	strEditor = "GenericEdit.asp"
Else 
	strEditor = "GenericCustomEdit.asp"
End if

' Check which viewer to use 
If Session("dbViewTemplate") & "x" = "x" Then
	strViewer = "GenericView.asp"
Else 
	strViewer = "GenericCustomView.asp"
End if

' If this is the first time through, blank the vars.
If Trim(Session("dbLastRs")) <> Trim(Session("dbRs")) Then 
	Session("dbLastRs") = Session("dbRs")
	Response.Clear
	Response.Redirect Session("dbGenericPath") & "GenericExit.asp?CMD='Reset'"
End If

' Get the settings from the Config File
strType = UCase(Session("dbType"))
strConn = Session("dbConn")
strDisplay = Session("dbDispList")
strSearchFields = Session("dbSearchFields")
strSearchPos = UCase(Trim(Session("dbSearchPos")))
strTotalFields = Session("dbTotalFields")
strFields = Session("dbFields")
strTable = Session("dbRs")
strWhere = Session("dbWhere")
strGroupBy = Session("dbGroupBy")
strHaving = Session("dbHaving")
strOrderBy = Session("dbOrderBy")
strFieldNames = Session("dbFieldNames")
intOrderBy = Session("dbOrder")
intPrimary = Session("dbKey")
strFont = Session("dbFont")
intFontSize = Session("dbFontSize")
intHidePageNumbers = Session("dbHidePageNumbers")
strBorderColor = Session("dbBorderColor")
strMenuColor = Session("dbMenuColor")
Session("ErrorNumber") = 0

' Check and set fonts and colours
If Trim(strFont) & "x" = "x" Then strFont = "Arial"
If NOT(intFontSize > 0) Then intFontSize = 2
If Trim(strBorderColor) & "x" = "x" Then strBorderColor = "#99CCCC"
If Trim(strMenuColor) & "x" = "x" Then strMenuColor = "#99CCCC"
If NOT(Trim(Session("dbExitPageText")) & "x" = "x") Then txtExit = Session("dbExitPageText")
If strSearchPos <> "TOP" Then strSearchPos = "BOTTOM"

' Is a field list provided
If Trim(strFields) & "x" = "x" Then
	strFields = "*"
	Session("dbFields") = "*"
End If	
' Is there a sub-table to display
If NOT(Trim(Session("dbSubTable")) & "x" = "x") Then
	arrSubTable = Split(Session("dbSubTable"),",")
	IsSubTable = True
End If
' Check for a limit on Records per Page
If Session("dbRecsPerPage") > 0 Then
	intDisplayRecs = Session("dbRecsPerPage")
Else
	intDisplayRecs = 10000
End If
' Check for a START parameter
If Request.QueryString("START").Count > 0 Then
	intStartRec = Request.QueryString("START")
	Session("dbStartRec") = intStartRec
Else
	' Check for a StartRec variable in the Config File
	If Session("dbStartRec") > 0 Then
		intStartRec = Session("dbStartRec")
	Else
		intStartRec = 1
	End If
End If
' Check for an Order parameter
If Request.QueryString("ORDER").Count > 0 Then
	' Check if an ASC/DESC toggle is required (- for desc, + for asc)
	if abs(intOrderBy) = abs(Request.QueryString("ORDER")) then
		intOrderBy = 0 - intOrderBy
	else
		intOrderBy = Request.QueryString("ORDER")
	end if
	Session("dbOrder") = intOrderBy
End If
'Set the last record to display
intStopRec = intStartRec + intDisplayRecs - 1

' Open Connection to the database
set xConn = Server.CreateObject("ADODB.Connection")
xConn.Open strConn

' Build Query
strsql = "SELECT " & strFields & " FROM [" & strTable & "]"
Select Case strType
	Case "UDF" 
		strsql = "SELECT " & strFields & " FROM " & strTable
	Case "SQL" 
		strsql = Replace(strsql,"[","")
		strsql = Replace(strsql,"]","")
End Select
If Not(Trim(strGroupBy) & "x" = "x") Then
	strsql = strsql & " GROUP BY " & strGroupBy
	intAllowSort = 0
End If	

' Open recordset
set xrs = Server.CreateObject("ADODB.Recordset")
xrs.Open strsql, xConn
' Call Error Handler if query bombs
If Err.Number <> 0 Then
	Session("ErrNumber") = Err.Number
	Session("ErrDesc") = Err.Description 
	Session("ErrSource") = Err.Source 
	Session("ErrLine") = Err.Line 
	Session("ErrMsg") = "Query: " & strsql
	Response.Clear
	Response.Redirect "GenericError.asp"
End If
intFieldCount = xrs.Fields.Count
Dim aFields()
ReDim aFields(intFieldCount,4)

' Get field info
If Trim(Session("dbFieldNames")) & "x" = "x" Then
	ReDim arrFieldNames(intFieldCount)
	For x = 1 to intFieldCount
		aFields(x, 1) = xrs.Fields(x-1).Name 
		aFields(x, 2) = xrs.Fields(x-1).Type 
		aFields(x, 3) = xrs.Fields(x-1).DefinedSize 
		aFields(x, 4) = 0 ' For running totals (per dbTotalFields)
		arrFieldNames(x-1) = xrs.Fields(x-1).Name 
	Next 
Else
	For x = 1 to intFieldCount
		aFields(x, 1) = xrs.Fields(x-1).Name 
		aFields(x, 2) = xrs.Fields(x-1).Type 
		aFields(x, 3) = xrs.Fields(x-1).DefinedSize 
		aFields(x, 4) = 0
	Next 
	arrFieldNames = Split(Session("dbFieldNames"), ",")
End If

xrs.Close
Set xrs = Nothing

' Reopen the Recordset, this time use the parameters passed
strsql = "SELECT " & strFields & " FROM [" & strTable & "]"
Select Case strType
	Case "UDF" 
		strsql = "SELECT " & strFields & " FROM " & strTable
	Case "SQL" 
		strsql = Replace(strsql,"[","")
		strsql = Replace(strsql,"]","")
End Select
If (strWhere & "x") <> "x" Then	strsql = strsql & " WHERE " & strWhere
If NOT Trim(strGroupBy) & "x" = "x" Then strsql = strsql & " GROUP BY " & strGroupBy
If NOT Trim(strHaving) & "x" = "x" Then strsql = strsql & " HAVING " & strHaving
If intOrderBy <> 0 Then 
	if intOrderBy > 0 then 
		strsql = strsql & " ORDER BY [" & aFields(intOrderBy, 1) & "]"
	else
		strsql = strsql & " ORDER BY [" & aFields(abs(intOrderBy), 1) & "] DESC"
	end if	
Else
	' See if a dbOrderBy string was passed.
	If Trim(strOrderBy) & "x" <> "x" Then strsql = strsql & " ORDER BY " & strOrderBy
End If
If strType = "SQL" Then
	' SQL databases do not allow spaces or brackets in table or field names
	strsql = Replace(strsql,"[","")
	strsql = Replace(strsql,"]","")
End If
set xrs = Server.CreateObject("ADODB.Recordset")
xrs.Open strsql, xConn, 1, 2 
' Call Error Handler if query bombs
If Err.Number <> 0 Then
	Session("ErrNumber") = Err.Number
	Session("ErrDesc") = Err.Description 
	Session("ErrSource") = Err.Source 
	Session("ErrLine") = Err.Line 
	Session("ErrMsg") = "Query: " & strsql
	Response.Clear
	Response.Redirect "GenericError.asp"
End If
intTotalRecs = xrs.RecordCount
%>
<HTML>
<HEAD>
	<TITLE><%=Session("dbTitle")%></TITLE>
</HEAD>

<!-- GDB CUSTOM SECTION 1: BODY Tag
Place any custom body tags in the following line (BACKGROUND, BGCOLOR, etc.) -->
<BODY >
<!-- END SECTION 1: BODY Tag -->


<!-- GDB CUSTOM SECTION 2: Links to cut and paste
The following links can be cut and pasted anywhere on your page. To change link text, change the
word(s) just before the </A> tag.

EXIT LINK:
<A HREF="<%=Session("dbGenericPath")%>GenericExit.asp">Exit</A>
RESET LINK:
<A HREF="<%=Session("dbGenericPath")%>GenericExit.asp?CMD='Reset'">Reset</A>
REFRESH LINK:
<A HREF="<%=Session("dbGenericPath")%>GenericList.asp">Refresh</A>
DBINFO (This page is designed for administrators, please use with care):
<A HREF="<%=Session("dbGenericPath")%>GenericInfo.asp">dbInfo</A>
ADD RECORD:
<A HREF="<%=Session("dbGenericPath")%><%=strEditor%>?CMD=NEW">Add Record</A>
SEARCH BOX (the following code must be enclosed in <% %> tags:
<% DispSearch() %>
END SECTION: Links to cut and paste -->


<!-- GDB CUSTOM SECTION 3: Page title, headings, introductory text, etc. -->
SAMPLE Title<p>
<!-- END SECTION: Page title, headings, introductory text, etc. -->


<!-- GDB CUSTOM SECTION 4: Formatting before field display
The following code will introduce your fields. Open TABLE tags here, display column headings, etc. -->
SAMPLE Record:<p>
<!-- END SECTION -->

<%
intCount = 0
intActual = 0
Do While (NOT xrs.EOF) AND (intCount < intStopRec)
	intCount = intCount + 1
	If Cint(intCount) >= Cint(intStartRec) Then 
		intActual = intActual + 1
		x = 0
		For Each xField in xrs.Fields
			x = x + 1
			' If on the Key field, build the link used to load the Viewer, Editor, or Deleter
			If x = CInt(intPrimary) Then
'				Session("zcurTable") = strTable
'				Session("zcurDisplay") = strDisplay
'				Session("zcurKeyField") = aFields(x,1)
				strLink = "KEY=" & xField.Value
			End If 
		Next 
	End If %>

<!-- GDB CUSTOM SECTION 5: Field display
Insert fields and formatting here. Refer to each field like this:
<%=xrs("FieldName")%>
At the end of each record, GDB can also display View, Edit, and Delete links. To place these, 
cut and paste the following:
VIEW:
<A HREF="<%=Session("dbGenericPath")&strViewer%>?<%=strLink%>">View</A>
EDIT:
<A HREF="<%=Session("dbGenericPath")%><%=strEditor%>?<%=strLink%>">Edit</A>
DELETE:
<A HREF="<%= Session("dbGenericPath") %>GenericDelete.asp?<%= strLink %>">Delete</A>
-->
SAMPLE Field <%=xrs("FieldName")%>
<!-- END SECTION: Field display -->


<%	' Do not change the following
xrs.MoveNext
Loop %>

<!-- GDB CUSTOM SECTION 6: After last record
Close any tables used to display data, etc. 
The Previous and Next Page links are still to come, this is not quite the page footer yet. -->
SAMPLE End of Records
<!-- END SECTION: After last record -->

<%' Find out if there should be Backward or Forward Buttons on the table.
intPageDisp = False
If 	intStartRec = 1 Then
	isPrev = False
Else
	isPrev = True
	PrevStart = intStartRec - intDisplayRecs
	If PrevStart < 1 Then PrevStart = 1 %>
<HR SIZE="1">
<STRONG><A HREF="GenericList.asp?START=<%=PrevStart%>">[&lt;&lt;&nbsp;<%=txtPreviousPage%>]</A></STRONG>
<%
End If

' Display Page numbers
If (intHidePageNumbers = 0) AND (isPrev OR (NOT xrs.EOF)) Then
	If (NOT isPrev) Then Response.Write "<HR SIZE=1>"
		if (intTotalRecs / intDisplayRecs > 30) Then 
			' If there are lots of pages, use intelligent page numbers
			intPage = (intStartRec+intDisplayRecs-1) / intDisplayRecs
			strLine = "<STRONG><FONT COLOR=Green>" & intPage & "</FONT></STRONG>"
			' Build to the left of the current page marker
			x = 1
			y = 1
			curPage = intPage - y
			curRec = intStartRec - (intDisplayRecs*x*y)
			while curPage > 1
				' For every 5 page numbers displayed we increase spacing by a factor of 5
				if (x mod 5) = 0 then y = y * 5
				strLine = "<STRONG><A HREF=" & QUOTE & "GenericList.asp?START=" & curRec & QUOTE & ">" & curPage & "</A></STRONG> " & strLine
				curPage = curPage - y
				curRec = curRec - (intDisplayRecs*y)
				x = x + 1
			wend
			' Link to page 1
			if intStartRec <> 1 then strLine = "<STRONG><A HREF=" & QUOTE & "GenericList.asp?START=1" & QUOTE & ">1</A></STRONG> " & strLine
			' Build to the right of the current page marker
			x = 1
			y = 1
			curPage = intPage + y
			curRec = intStartRec + (intDisplayRecs*x*y)
			while curRec < intTotalRecs
				if (x mod 5) = 0 then y = y * 5
				strLine = strLine & " <STRONG><A HREF=" & QUOTE & "GenericList.asp?START=" & curRec & QUOTE & ">" & curPage & "</A></STRONG>"
				curPage = curPage + y
				curRec = curRec + (intDisplayRecs*y)
				x = x + 1
			wend
			' Link to the last page
			if (curRec-(intDisplayRecs*y)) < (intTotalRecs-intDisplayRecs) then strLine = strLine & " <STRONG><A HREF=" & QUOTE & "GenericList.asp?START=" & ((clng(intTotalRecs / intDisplayRecs)-1)*intDisplayRecs)+1 & QUOTE & ">" & clng(intTotalRecs / intDisplayRecs) & "</A></STRONG>"
			response.write strLine
		else ' There aren't too many pages so display them in the regular style
			x = 1
			y = 1
		While x <= intTotalrecs
			If clng(intStartRec) = clng(x) Then %>
<STRONG><FONT COLOR="Green"><%=y%></FONT></STRONG>
<%	 		Else %>
<STRONG><A HREF="GenericList.asp?START=<%=x%>"><%=y%></A></STRONG>
<%			End If
			x = x + intDisplayRecs
			y = y + 1
		Wend
	end If
end if

' Next link
If NOT xrs.EOF Then
	NextStart = intStartRec + intDisplayRecs
	isMore = True %>
<STRONG><A HREF="GenericList.asp?START=<%=NextStart%>">[<%=txtNextPage%>&nbsp;&gt;&gt;]</A></STRONG>
<% Else
	isMore = False
End If %>
<HR SIZE="1">
<% If intStopRec > intCount Then intStopRec = intCount
Response.Write txtRecords & " " & intStartRec & " " & txtTo & " " & intStopRec & " " & txtOf & " " & intTotalRecs
' Close recordset and connection
xrs.Close
Set xrs = Nothing
xConn.Close
Set xConn = Nothing %>
</FONT>
<P>
<% 	If strSearchPos = "BOTTOM" Then 
		DispSearch()
	End If %>


<!-- GDB CUSTOM SECTION 7: Footer
Close any tables used to display data, etc. 
The Previous and Next Page links are still to come, this is not quite the page footer yet. -->
SAMPLE Footer
<!-- END SECTION: Footer -->
</BODY>
</HTML>
<%
Function DispSearch

If strSearchFields & "x" <> "x" Then
	' If in a search, ask for a reset before another search.
	If Session("dbState") = 3 Then %>
<TABLE CELLPADDING=1 CELLSPACING=0 BORDER=0 BGCOLOR=#99CCCC>
<TR><TD>
<TABLE CELLPADDING=2 CELLSPACING=2 BORDER=0 WIDTH=100% BGCOLOR=#99CCCC>
<FORM ACTION="GenericSearchResult.asp" METHOD="POST">
	<TR>
		<TD HEIGHT=0 BGCOLOR="WHITE" "ALIGN="LEFT">
			<FONT SIZE=2 FACE="<%=strFont%>">
			<A HREF="<%=Session("dbGenericPath")%>GenericExit.asp?CMD='Reset'"><%=txtReset%></A> <%=txtSearchFailMsgB%></TD>
	</TR>
</FORM>
</TABLE>
</TD></TR>
</TABLE>
<% 	Else %>
<TABLE CELLPADDING=1 CELLSPACING=0 BORDER=0 BGCOLOR=<%=strBorderColor%>>
<TR><TD>
<TABLE CELLPADDING=2 CELLSPACING=2 BORDER=0 WIDTH=100% BGCOLOR=<%=strBorderColor%>>
<FORM ACTION="GenericSearchResult.asp" METHOD="POST">
<input type="Hidden" name="SearchAction" value="SHORT">
	<TR>
		<TD HEIGHT=0 BGCOLOR="WHITE" "ALIGN="LEFT">
			<FONT SIZE=2 FACE="<%=strFont%>"><%=txtSearchFor%>
			<FONT SIZE=2 FACE="<%=strFont%>"><INPUT TYPE="Text" NAME="strSearch" SIZE=40>
<% If Session("dbSearchEnhanced") = 1 then%>
		</TD>
	</TR>
	<tr>
		<td HEIGHT=0 BGCOLOR="WHITE" ALIGN="right"><FONT SIZE=2 FACE="<%=strFont%>">
			<input type="Radio" name="SearchPhrase" value="OR" checked><%=txtAnyofthesewords%>
			<input type="Radio" name="SearchPhrase" value="EXACT"><%=txtExactPhrase%>
<% Else %>
<input type="Hidden" name="SearchPhrase" value="OR">
<% End If %>
			<FONT SIZE=2 FACE="<%=strFont%>"><INPUT TYPE="Submit" NAME="Submit" VALUE="<%=txtSearchSubmit%>">
		</TD>
	</TR>
</FORM>
</TABLE></TD></TR></TABLE>
<p>
<%	End If
End If
End Function
%>