<%@ Language=VBScript %>
<% 
	Dim sTitle
	sTitle = "Search Results"
%>
<!--#include file="_ScriptLibrary/adovbs.inc"-->
<!--#include file="datastore.asp"-->

<%
Dim sUserID
sUserID = trim(Request("userid"))

'check if userid is empty. if empty redirect to login screen
if sUserID = "" then Response.Redirect "login.asp?menu=school"

%>

<!--#include file=header.asp-->
<!--#include file=top.asp-->


<%
	'Create variables
	Dim objConn
	Dim rsMetroArea
	Dim rsRegion
	Dim rsRes
	Dim sSQLMA, sSQLRG, sSQL, sSQLSR, sSQLEX, sSQLED ,sSalaryRange, sExpertise, sMetroArea, sRegion, sEducation
	Dim i
	Dim index
	Dim bRegion
	
	'Assign variables
	sSalaryRange = Request.Form("cboSalaryRange")
	sMetroArea = Request.Form("cboMetro")
	sRegion = Request.Form("cboRegion")
	sExpertise = Request.Form("cboExpertise")
	sEducation = Request.Form("cboEducation")

	'Build sql statements
	
	'sSQL = "SELECT TEACHER.id, TEACHER.datesubmitted, [city] & chr(44) & chr(32) & [code] AS Location, TEACHER.jobtitle, TEACHER.salary FROM STATE INNER JOIN TEACHER ON STATE.id = TEACHER.state WHERE "
	sSQL = "SELECT TEACHER.id, TEACHER.datesubmitted, [city] & chr(44) & chr(32) & [code] AS Residence, EXPERTISE.areaofexpertise, [REGION].[name] & chr(32) & chr(45) & chr(32) & [METROAREA].[name] AS Location, TEACHER.salary FROM METROAREA RIGHT JOIN (REGION RIGHT JOIN ((STATE INNER JOIN TEACHER ON STATE.id = TEACHER.state) INNER JOIN EXPERTISE ON TEACHER.expertise = EXPERTISE.id) ON REGION.id = TEACHER.region) ON METROAREA.id = TEACHER.metroarea WHERE "
	
	'salary criteria
	Select Case sSalaryRange
		
		Case 0
			sSQLSR = ""
			sSQL = sSQL & " salary = True"
		Case 1
			sSQLSR = " salary <= 10000 "
			sSQL = sSQL & sSQLSR
		Case 2
			sSQLSR = " salary <= 20000 "
			sSQL = sSQL & sSQLSR
		Case 3
			sSQLSR = " salary <= 30000 "
			sSQL = sSQL & sSQLSR
		Case 4
			sSQLSR = " salary <= 40000 "
			sSQL = sSQL & sSQLSR
		Case 5
			sSQLSR = " salary <= 50000 "
			sSQL = sSQL & sSQLSR
		Case Else
			sSQLSR = " salary <= 10000 "
			sSQL = sSQL & sSQLSR
	End Select
	
	'Expertise criteria
	Select Case sExpertise
		Case 0
			sSQLEX = ""
		Case Else
			sSQLEX = " expertise = " & sExpertise
			sSQL = sSQL & " AND " & sSQLEX
	End Select
	
	'Education Criteria
	Select Case sEducation
		Case 0
			sSQLED = ""
		Case 4
			sSQLED = " education = " & sEducation
			sSQL = sSQL & " AND " & sSQLED
		Case Else
			sSQLED = " education >= " & sEducation
			sSQL = sSQL & " AND " & sSQLED
	End Select
	
	'Metro Area Criteria
	Select Case sMetroArea
		Case 0
			sSQLMA = ""
		Case Else
			if sRegion <> 0 then sSQLMA = " (metroarea = " & sMetroArea else sSQLMA = " metroarea = " & sMetroArea
			sSQL = sSQL & " AND " & sSQLMA
	End Select
	
	'Region Criteria
	Select Case sRegion
		Case 0
			sSQLRG = ""
		Case Else
			sSQLRG = " region = " & sRegion
			if sMetroArea <> 0 then sSQL = sSQL & " OR " & sSQLRG & ")" else sSQL = sSQL & " AND " & sSQLRG
	End Select
	
	
	'Connection object
	Set objConn = Server.CreateObject("ADODB.Connection")
	
	'Recordset objects
	'Set rsMetroArea = Server.CreateObject("ADODB.Recordset")
	'Set rsRegion = Server.CreateObject("ADODB.Recordset")
	Set rsRes = Server.CreateObject("ADODB.Recordset")
	
	'open connection
	objConn.Open sConnect	
	
		sSQL = sSQL & " AND active=True ORDER BY datesubmitted;"
	
	'response.write sSQL
			' Determine what PageNumber the scrolling currently is on
			
			Mv = Request("Mv")

			If Request("PageNo") = "" Then
				PageNo = 1
			Else
				PageNo = Request("PageNo")
			End If
			
			
			' Setup Query Recordset (10 records per page)
					
			rsRes.Open sSQL, objConn, adOpenStatic
			rsRes.PageSize = 10
			Response.Write "<p align=center><b>" & rsRes.RecordCount & " resume(s) were found.</b><p>"
			
			' Adjust PageNumber as Appropriate
			if not rsRes.EOF then
			
			If Mv = "Page Up" or Mv = "Page Down" Then
				Select Case Mv
					Case "Page Up"
						If PageNo > 1 Then
							PageNo = PageNo - 1
						Else
							PageNo = 1
						End If
					Case "Page Down"
						If rsRes.AbsolutePage < rsRes.PageCount Then
							PageNo = PageNo + 1
						Else
							PageNo = rsRes.PageCount
						End If
					Case Else
						PageNo = 1
				End Select
			End If

			rsRes.AbsolutePage = PageNo
%>

		
		<!-- Draw Table of Contacts in DB -->
<div align=center>
		<TABLE BORDER=1>
			<tr>
				<td><b>Date</b></td><td><b>Residence</b></td><td><b>Area of Expertise</b></td><td><b>Desired Location</b></td><td><b>Salary</b></td>
			</tr>
			<%  For j = 1 to rsRes.PageSize %>
				<TR>
					<%  For i = 1 to rsRes.Fields.Count - 1 %>
						<% if i=3 then %>
							<TD VALIGN=TOP><a href="rdetails.asp?userid=<%=sUserID%>&id=<%= rsRes(0) %>"><%= rsRes(i) %></a></TD>
						<% else %>
							<%if i = 5 then %>
								<TD VALIGN=TOP><%= FormatCurrency(rsRes(i)) %></TD>
							<%else%>
								<TD VALIGN=TOP><%= rsRes(i) %></TD>
							<%end if%>
						<% end if %>
					<%  Next %>
				</TR>

				<%
					rsRes.MoveNext
					
					' Don't try to print the EOF record.
					If rsRes.EOF Then
						Exit For
					End If
				Next %>
		</TABLE>


		<!-- Scrolling Navigation Control for Sample -->

		<Form Action="findres.asp?userid=<%=sUserID%>" Method="POST" id=form1 name=form1>
			<Input Type="Hidden" Name="PageNo" Value="<%= PageNo %>">
			<!-- Only show appropriate buttons -->
			<%  If PageNo <  rsRes.PageCount Then %>
				<INPUT TYPE="Submit" Name="Mv" Value="Page Down">
			<%  End If %>

			<%  If PageNo > 1 Then %>
				<INPUT TYPE="Submit" Name="Mv" Value="Page Up">
			<%  End If %>
			
		</Form>
	</div>

	<p align=center><form action="research.asp?userid=<%=sUserID%>" method=post><input type="submit" Value="Search Again" name="SearchAgain"></form>
<%
	rsRes.Close
	objConn.Close
else %>
	<p align=center><form action="research.asp?userid=<%=sUserID%>" method=post><input type="submit" Value="Search Again" name="SearchAgain"></form>
<%	
	rsRes.Close
	objConn.Close
end if
%>
<!--#include file=bottom.asp-->
<!--#include file=footer.asp-->