<%
DBNAME="pacificsailingschool_com_au"
DBUSER="pacificsailingschool"
DBPASS="79rkBz3v4qLh22"
DB_LOGON_STRING="Provider=SQLOLEDB.1;Data Source=sql2.ozhosting.com;Initial Catalog=pacificsailingschool_com_au;User ID=pacificsailingschool;pwd=79rkBz3v4qLh22"

BODY_OPTIONS="bgcolor=""#98D0FE"" text=""#0414A6"" "

'###########################################################################
'function to quote a string (just translate ' to '' for SQL)
Function SqlQuote(S)
Dim i			'looping var
Dim S2		'temp str
Dim C			'temp char
	i=1
	S2=""
	Do While (i<=Len(S))
		C=Mid(S,i,1)
		If (C="'") Then
			S2=S2 & "'"
		End If
		S2=S2 & C
		i=i+1
	Loop
	SqlQuote=S2
End Function

Function CrewRatingSQL(SearchType,CrewId)
If (0+CrewId)>0 Then 
	CrewIDClause=" AND CrewId="&CrewId&" "
Else 
	CrewIDClause=" AND Visible='Y'"
End If

If Request("Qual_C1")="on" Then
	QualNeeded = " AND Qual_C1='Y'"
End If
If Request("Qual_C2")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C2='Y'"
End If
If Request("Qual_C3")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C3='Y'"
End If
If Request("Qual_C4")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C4='Y'"
End If
If Request("Qual_C5")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C5='Y'"
End If
If Request("Qual_C6")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C6='Y'"
End If
If Request("Qual_C7")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C7='Y'"
End If
If Request("Qual_C8")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C8='Y'"
End If
If Request("Qual_C9")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C9='Y'"
End If
If Request("Qual_C10")="on" Then
	QualNeeded = QualNeeded & " AND Qual_C10='Y'"
End If
If Request("Qual_First_Aid")="on" Then
	QualNeeded = QualNeeded & " AND Qual_First_Aid='Y'"
End If
If Request("Qual_Radio_Licence")="on" Then
	QualNeeded = QualNeeded & " AND Qual_Radio_Licence='Y'"
End If
If Request("Qual_SSSC")="on" Then
	QualNeeded = QualNeeded & " AND Qual_SSSC='Y'"
End If
If Request("Avail_Hobart")="on" Then
	QualNeeded = QualNeeded & " AND Avail_Hobart='Y'"
End If

If RTrim(SearchType)="Offshore Racing" Then
	SQL="SELECT "& _
	"	*,"& _
	"	CASE WHEN Qual_C4='Y' THEN 10 ELSE 0 END + "& _
	"	CASE WHEN Qual_C1='Y' THEN 1 ELSE 0 END + "& _
	"	CASE WHEN Qual_C2='Y' AND Qual_C5='Y' AND Qual_C6='Y' AND Qual_C7='Y' THEN 5 ELSE 0 END + "& _
	"	Exp_Offshore "& _
	"	AS Score "& _
	"FROM Crewlist "& _
	"WHERE (Avail_Offshore='Y' OR Avail_Hobart='Y' OR Avail_Short_Haul='Y') "& CrewIDClause &_
	QualNeeded &_
	"ORDER BY Score DESC "


ElseIf RTrim(SearchType)="Harbour Racing" Then
	SQL="SELECT "& _
	"	*,"& _
	"	CASE WHEN Qual_C4='Y' THEN 10 ELSE 0 END + "& _
	"	CASE WHEN Qual_C1='Y' THEN 1 ELSE 0 END + "& _
	"	CASE WHEN Qual_C2='Y' AND Qual_C3='Y' THEN 5 ELSE 0 END + "& _
	"	Exp_Harbour_Racing "& _
	"	AS Score "& _
	"FROM Crewlist "& _
	"WHERE (Avail_Harbour='Y'  OR Avail_Twilight='Y') "& CrewIDClause & _
	"ORDER BY Score DESC "

ElseIf RTrim(SearchType)="Cruising" Then
	SQL="SELECT "& _
	"	*,"& _
	"	CASE WHEN Qual_C1='Y' THEN 1 ELSE 0 END + "& _
	"	CASE WHEN Qual_C10='Y' THEN 1 ELSE 0 END + "& _
	"	CASE WHEN Qual_C2='Y' AND Qual_C5='Y' AND Qual_C6='Y' AND Qual_C7='Y' THEN 5 ELSE 0 END + "& _
	"	Exp_Cruising "& _
	"	AS Score "& _
	"FROM Crewlist "& _
	"WHERE Avail_Cruising='Y' "& CrewIDClause & _
	"ORDER BY Score DESC "

ElseIf RTrim(SearchType)="Delivery" Then
	SQL="SELECT "& _
	"	*,"& _
	"	CASE WHEN Qual_C1='Y' THEN 1 ELSE 0 END + "& _
	"	CASE WHEN Qual_C10='Y' THEN 1 ELSE 0 END + "& _
	"	CASE WHEN Qual_C2='Y' AND Qual_C5='Y' AND Qual_C6='Y' AND Qual_C7='Y' THEN 5 ELSE 0 END + "& _
	"	Exp_Deliveries "& _
	"	AS Score "& _
	"FROM Crewlist " & _
	"WHERE Avail_Deliveries='Y' "& CrewIDClause & _
	"ORDER BY Score DESC " 
End If
	CrewRatingSQL=SQL
End Function

Sub SaveFormFields(SourcePage)
	If SourcePage=1 Then
		FieldNames=Array( _
			"Email", _
			"Gender", _
			"Crewmember_Name", _
			"Postcode", _
			"Primary_Phone", _
			"Secondary_Phone", _
			"Smoker", _
			"State", _
			"Street", _
			"Suburb", _
			"DOB", _
			"Weight" _
		)

	ElseIf SourcePage=2 Then
		FieldNames=Array( _
			"Exp_Cruising", _
			"Exp_Deliveries", _
			"Exp_Harbour_Racing", _
			"Exp_Offshore", _
			"Exp_Short_Haul", _
			"Qual_Auto_Helm", _
			"Qual_C1", _
			"Qual_C10", _
			"Qual_C2", _
			"Qual_C3", _
			"Qual_C4", _
			"Qual_C5", _
			"Qual_C6", _
			"Qual_C7", _
			"Qual_C8", _
			"Qual_C9", _
			"Qual_Celestial", _
			"Qual_Deisel_Maintenance", _
			"Qual_First_Aid", _
			"Qual_GPS", _
			"Qual_Radio_Licence", _
			"Qual_Sail_Repair", _
			"Qual_Yachtmaster", _
			"Qual_SSSC" _
		)
	ElseIf SourcePage=3 Then
		FieldNames=Array(_
			"Comments", _
			"Avail_Cruising", _
			"Avail_Deliveries", _
			"Avail_Friday_Twilight", _
			"Avail_Harbour", _
			"Avail_Hobart", _
			"Avail_Monday_Twilight", _
			"Avail_Offshore", _
			"Avail_Saturday", _
			"Avail_Short_Haul", _
			"Avail_Short_Notice", _
			"Avail_Sunday", _
			"Avail_Twilight", _
			"Avail_Wednesday_Afternoon", _
			"Avail_Wednesday_Twilight", _
			"Avail_Weekend", _
			"Pos_Foredeck", _
			"Pos_Grinder", _
			"Pos_Helm", _
			"Pos_Jib_Trimmer", _
			"Pos_Main_Trimmer", _
			"Pos_Mast", _
			"Pos_Sewer", _
			"Pos_Tactician" _
		)
	Else 
		Response.Write "ERROR: invalid source page "&SourcePage&"."
		Response.End

	End If

	For I=LBound(FieldNames) To UBound(FieldNames)
		ThisField=FieldNames(I)
		Session(ThisField)=Request(ThisField)
	Next


End Sub


	AllFieldNames=Array( _
		"Email", _
		"Gender", _
		"Postcode", _
		"Primary_Phone", _
		"Secondary_Phone", _
		"Smoker", _
		"State", _
		"Street", _
		"Suburb", _
		"DOB", _
		"Weight", _
		"Exp_Cruising", _
		"Exp_Deliveries", _
		"Exp_Harbour_Racing", _
		"Exp_Offshore", _
		"Exp_Short_Haul", _
		"Qual_Auto_Helm", _
		"Qual_C1", _
		"Qual_C10", _
		"Qual_C2", _
		"Qual_C3", _
		"Qual_C4", _
		"Qual_C5", _
		"Qual_C6", _
		"Qual_C7", _
		"Qual_C8", _
		"Qual_C9", _
		"Qual_Celestial", _
		"Qual_Deisel_Maintenance", _
		"Qual_First_Aid", _
		"Qual_GPS", _
		"Qual_Radio_Licence", _
		"Qual_Sail_Repair", _
		"Qual_Yachtmaster", _
		"Qual_SSSC", _
		"Comments", _
		"Avail_Cruising", _
		"Avail_Deliveries", _
		"Avail_Friday_Twilight", _
		"Avail_Harbour", _
		"Avail_Hobart", _
		"Avail_Monday_Twilight", _
		"Avail_Offshore", _
		"Avail_Saturday", _
		"Avail_Short_Haul", _
		"Avail_Short_Notice", _
		"Avail_Sunday", _
		"Avail_Twilight", _
		"Avail_Wednesday_Afternoon", _
		"Avail_Wednesday_Twilight", _
		"Avail_Weekend", _
		"Pos_Foredeck", _
		"Pos_Grinder", _
		"Pos_Helm", _
		"Pos_Jib_Trimmer", _
		"Pos_Main_Trimmer", _
		"Pos_Mast", _
		"Pos_Sewer", _
		"Pos_Tactician" _
	)


Sub LoadCrewMember(CrewId)
	
	Set DBConn=Server.CreateObject("ADODB.Connection")
	DBConn.Open DB_LOGON_STRING
	SQL="SELECT * From CrewList WHERE CrewId="&CrewId
	Set CrewMember=DBConn.Execute(SQL)
	if CrewMember.EOF Then
		Response.Write "ERROR: can't find record #"&CrewId
		Response.End
	End If
	Session("Crewmember_Name")=Trim(CrewMember("Name"))

	For I=LBound(AllFieldNames) To UBound(AllFieldNames)
		ThisField=AllFieldNames(I)
		Session(ThisField)=Trim(CrewMember(ThisField))
	Next

End Sub
Sub ClearFormFields

	For I=LBound(AllFieldNames) To UBound(AllFieldNames)
		ThisField=AllFieldNames(I)
		Session(ThisField)=""
	Next
	Session("Crewmember_Name")=""
End Sub
%>