Function strWHERE(strSQL)
' determines whether there is already a "where" in the sql and if not prints it..
' alex@extramation.com (14/02/2007)
	dim x
	x = ""
	If LCase(Instr(Right(StrSQL,5), "and")) >0 Then
		x = " " 
	End If
	If LCase(InStr(strSQL, "where")) >0 Then 
		x = " "
	End If
	If LCase(InStr(strSQL, "where")) = 0 And LCase(Instr(Right(StrSQL,5), "and")) = 0 Then
		x = " where "
	Else
		x = " "
	End If
	strWHERE = x
End Function
		
Function strAND(strSQL)	
' determines whether there is an "and" in the last x chars of a sql string and prints it if not..
' alex@extramation.com (14/02/2007)
	Dim y
	y = ""
	If LCase(Instr(Right(StrSQL,8), "and")) >0 Then 
		y = " "
	End If
	If LCase(Instr(Right(StrSQL,9), "where")) >0 Then
		y = " " 
	End If
	If  LCase(Instr(Right(StrSQL,8), "and")) = 0 And LCase(Instr(Right(StrSQL,9), "where")) = 0 Then
		y = " and "
	Else 
		y = " "
	End If
	strAND = y
End Function 

SUB getExtranetReports()
' alex@extramation.com (14/02/2007)
' grabs all extranet report columns and uses strAND() and strWHERE() to build up the SQL query..

	Set oRs = server.createobject("ADODB.Recordset")
	oRs.activeconnection=oConn
	strSQL = "SELECT * FROM tblExtranetReport"
	
	'Lets ensure all our variables are string values within this function..
	filteryear = CStr(filteryear)
	filtermonth = CStr(filtermonth)
	visit_zone = CStr(visit_zone)
	customers = CStr(customers)

	'we must build the SQL in the format below so that the code can decide whether to add an "AND" or a "WHERE"
	If customers <> ""  And customers <> "0" Then
		strSQL = strSQL & strWHERE(strSQL) 
		strSQL = strSQL & strAND(strSQL) 
		strSQL = strSQL & "dir = '" & cleanForSQL(customers) & "'"
	End If
	If filteryear <> ""  And filteryear <> "0" Then
		strSQL = strSQL & strWHERE(strSQL)
		strSQL = strSQL & strAND(strSQL)
		strSQL = strSQL & "YEAR(visit_datetime) = '" & cleanForSQL(filteryear) & "'"
	End If
	If filtermonth <> ""  And filtermonth <> "0" Then
		strSQL = strSQL & strWHERE(strSQL)
		strSQL = strSQL & strAND(strSQL)
		strSQL = strSQL & "MONTH(visit_datetime) = '" & cleanForSQL(filtermonth) & "'"
	End If
	If visit_zone <> ""  And visit_zone <> "0" Then
		strSQL = strSQL & strWHERE(strSQL) 
		strSQL = strSQL & strAND(strSQL)
		strSQL = strSQL & "visit_zone = '" & cleanForSQL(visit_zone) & "'"
	End If

	strSQL = strSQL & " order by visit_datetime DESC"
	Response.write(strSQL)
	'Response.end()
	oRs.open strSQL
	If oRs.EOF Then
		EXIT SUB
	End If

END SUB

SUB getZones()'used in extranet/reporting/default.asp - also gets called from Function PulldownDB()
	Set objRs = server.createobject("ADODB.Recordset")
	objRs.activeconnection=oConn
	strSQL = "SELECT min(reporting_id) as reporting_id, visit_zone FROM tblExtranetReport group by visit_zone"

	objRs.open strSQL
	If objRs.EOF Then
		EXIT SUB
	End If
'Response.Write(StrSQL)
'Response.End()
END SUB
SUB getCustomers()'used in extranet/reporting/default.asp - also gets called from Function PulldownDB()
	Set objRs = server.createobject("ADODB.Recordset")
	objRs.activeconnection=oConn
	strSQL = "SELECT MIN(reporting_id) as reporting_id, dir FROM tblExtranetReport GROUP BY dir ORDER BY dir ASC"

	objRs.open strSQL
	If objRs.EOF Then
		EXIT SUB
	End If
'Response.write(strSQL)
'Response.end()
END SUB

Function PulldownDB(byRef funcName, ByRef strInputName, ByRef intSize, ByRef blnDisabled, _
	ByRef strFirstValue, ByRef strFirstLabel, ByRef varSelectedValue, ByRef strJavascript, value, Label )

		
'Usage :PulldownDB(funcName, InputName, Size, IsDisabled?, FirstValue, FirstLabel, SelectedValue(request.value), Javascript, value(db field), Label(db field) )
' strOutputSelect = PulldownDB("getZones()", "visit_zone", 1, "", 0, "Zone", visit_zone, "onchange=""this.form.submit();""", "visit_zone", "visit_zone" )
' response.write(strOutputSelect)
	
	Dim strDisabled
	Dim strPulldown
	Dim varValue, strLabel


	If blnDisabled = True Then strDisabled= " disabled=""disabled"""
	strPulldown = ""
	strPulldown = strPulldown & "<select name=""" & strInputName & """ id=""" & strInputName & """ size=""" & intSize & """" & strDisabled & " " & strJavascript & ">"

	' 1st option e.g. "Select..." is optional
	If strFirstLabel <> "" Then
							strPulldown = strPulldown & "<option value=""" & strFirstValue & """>" & strFirstLabel & "</option>" & vbcrlf
	End If
	
	Select Case funcName 'pass the function in and then call it 
		Case "getZones()"
			Call getZones()
		Case "getCustomers()"
			Call getCustomers()
		Case Else
			Response.Write("Nothing to do")
			EXIT Function
	End Select
	
                     
	With objRs
		Do While Not .EOF                                               
			varValue = .Fields(Value) '0
			strLabel = .Fields(Label) '1
			strPulldown = strPulldown & "<option value=""" & varValue & """"                                                
			If (CStr(varValue) = CStr(varSelectedValue)) Then
				strPulldown = strPulldown & " selected"
			End If                                      

			strPulldown = strPulldown & ">" & strLabel & "</option>" & vbcrlf                                                                  

			.MoveNext
		Loop
	End With

	' Clean up
	Set objRs = Nothing
	'objCon.Close
	'Set objCmd = Nothing
	'Set objCon = Nothing

	strPulldown = strPulldown & "</select>"
	PulldownDB = strPulldown

End Function

SUB GetExtranetUsers()'used in extranet/admin/default.asp.
	Set objRs = server.createobject("ADODB.Recordset")
	objRs.activeconnection=oConn
	strSQL = "SELECT * from tblusers"

	objRs.open strSQL
	If objRs.EOF Then
		EXIT SUB
	End If
Response.Write(StrSQL)
'Response.End()
END SUB