<%
On Error Resume Next
Response.Expires = 0
Dim objConn, objRS, strQuery
Dim strConnection
Dim tID, tFirstName, tLastName, tType, tLot, tSAddress1, tSStreetType, tSSuburb, tSState, tSPostcode, tSupervisor, tAdministrator, tSalesRep, tStatus
Dim intLoop

    Set objConn = Server.CreateObject("ADODB.Connection")
    strConnection = "DSN=personahomes;Database=WebDB.mdb;"
  
    objConn.Open strConnection
  
	tID = Request.QueryString("ID")
	tFirstName = Request.QueryString("firstname")
	tLastName = Request.QueryString("Lastname")
	tType = Request.QueryString("type")
	tLot = Request.QueryString("lot")
	tSAddress1 = Request.QueryString("SAddress1")
	tSStreetType = Request.QueryString("SStreetType")
	tSSuburb = Request.QueryString("SSuburb")
	tSState = Request.QueryString("SState")
	tSPostcode = Request.QueryString("SPostcode")
	tSupervisor = Request.QueryString("supervisor")
	tAdministrator = Request.QueryString("administrator")
	tSalesRep = Request.QueryString("salesRep")
	tStatus = Request.QueryString("status")

	if InStr(tFirstName,"'") > 0 then
		tRFirstName = Replace(tFirstName,"'","''")
	else
		tRFirstName = tFirstName
	end if

	if InStr(tLastName,"'") > 0 then
		tRLastName = Replace(tLastName,"'","''")
	else
		tRLastName = tLastName
	end if

	if InStr(tSAddress1,"'") > 0 then
		tRSAddress1 = Replace(tSAddress1,"'","''")
	else
		tRSAddress1 = tSAddress1
	end if

	if InStr(tSStreetType,"'") > 0 then
		tRSStreetType = Replace(tSStreetType,"'","''")
	else
		tRSStreetType = tSStreetType
	end if

	if InStr(tSSuburb,"'") > 0 then
		tRSSuburb = Replace(tSSuburb,"'","''")
	else
		tRSSuburb = tSSuburb
	end if

	if InStr(tSupervisor,"'") > 0 then
		tRSupervisor = Replace(tSupervisor,"'","''")
	else
		tRSupervisor = tSupervisor
	end if

	if InStr(tAdministrator,"'") > 0 then
		tRAdministrator = Replace(tAdministrator,"'","''")
	else
		tRAdministrator = tAdministrator
	end if

	if InStr(tSalesRep,"'") > 0 then
		tRSalesRep = Replace(tSalesRep,"'","''")
	else
		tRSalesRep = tSalesRep
	end if

	strQuery = "SELECT * FROM Project WHERE ID > 0"

	if not tFirstName = "" then
		strQuery = strQuery & " AND (FirstName = '" & tRFirstName & "' OR FirstName2 = '" & tRFirstName & "')"
	end if

	if not tLastName = "" then
		strQuery = strQuery & " AND (LastName = '" & tRLastName & "' OR LastName2 = '" & tRLastName & "')"
	end if
	
	if not tID = "" then
		strQuery = strQuery & " AND ID = " & tID
	end if
	
	if not tType = "" then
		strQuery = strQuery & " AND TypeID = " & tType
	end if

	if not tLot = "" then
		strQuery = strQuery & " AND Lot = '" & tLot & "'"
	end if

	if not tSAddress1 = "" then
		strQuery = strQuery & " AND SAddress1 = '" & tRSAddress1 & "'"
	end if

	if not tSStreetType = "" then
		strQuery = strQuery & " AND SStreetType = '" & tRSStreetType & "'"
	end if

	if not tSSuburb = "" then
		strQuery = strQuery & " AND SSuburb = '" & tRSSuburb & "'"
	end if

	if not tSState = "" then
		strQuery = strQuery & " AND SState = '" & tSState & "'"
	end if

	if not tSPostcode = "" then
		strQuery = strQuery & " AND SPostcode = '" & tSPostcode & "'"
	end if

	if not tSupervisor = "" then
		strQuery = strQuery & " AND Supervisor = '" & tRSupervisor & "'"
	end if

	if not tAdministrator = "" then
		strQuery = strQuery & " AND Administrator = '" & tRAdministrator & "'"
	end if

	if not tSalesRep = "" then
		strQuery = strQuery & " AND SalesRep = '" & tRSalesRep & "'"
	end if

	if not tStatus = "" then
		strQuery = strQuery & " AND Status = '" & tStatus & "'"
	end if
	
	strQuery = strQuery & " ORDER BY ID"

    Set objRS = objConn.Execute(strQuery)
    
	Response.ContentType = "application/vnd.ms-excel"
	Response.AddHeader "Content-Disposition", "attachment; filename=projectEmail.xls" 
  
    if not objRS.EOF then
%>
					<tr>
						<td align="center"><table border="2" bordercolor="CCCCCC" cellpadding="0" cellspacing="0">
				    		<tr>
        						<td width="80" height="25" class="h2">Reference No.</td>
        						<td width="150" height="25" class="h2">Customer</td>
        						<td width="60" height="25" class="h2">Type</td>
        						<td width="200" height="25" class="h2">Address</td>
        						<td width="200" height="25" class="h2">Email</td>
        						<td width="200" height="25" class="h2">Email 2</td>
        						<td width="120" height="25" class="h2">Mobile</td>
        						<td width="120" height="25" class="h2">Mobile 2</td>
        						<td width="120" height="25" class="h2">Phone</td>
        						<td width="100" height="25" class="h2">Status</td>
      						</tr>
<%
        Do While not objRS.EOF

			TstrQuery = "SELECT * FROM Type WHERE ID = " & objRS("TypeID")
			Set TobjRS = objConn.Execute(TstrQuery)

          Response.Write "<tr height=""25""><td width=""80"" class=""p"">" & objRS("ID") & "</td>"
		  
		  if objRS("FirstName2") <> "" or objRS ("LastName2") <> "" then
								
									response.write "<td width=""150"" height=""15"" class=""p"">" & objRS("FirstName") & " " & objRS("LastName") & " & " & objRS("FirstName2") & " " & objRS("LastName2") & "</td>"
								
							else
								 	response.Write "<td width=""150"" height=""15"" class=""p"">" & objRS("FirstName") & " " & objRS("LastName") & "</td>"								
							
							end if 

          Response.Write "<td width=""60"" height=""15"" class=""p"">" & TobjRS("Name") & "</td>"
          Response.Write "<td width=""200"" height=""15"" class=""p"">" & objRS("Lot") & " " & objRS("SAddress1") & " " & objRS("SStreetType") & " " & objRS("SSuburb") & " " & objRS("SState") & " " & objRS("SPostcode") & "</td>"
          Response.Write "<td width=""200"" class=""p"">" & objRS("Email") & "</td>"
          Response.Write "<td width=""200"" class=""p"">" & objRS("Email2") & "</td>"
          Response.Write "<td width=""120"" class=""p"">&nbsp;" & objRS("Mobile") & "</td>"
          Response.Write "<td width=""120"" class=""p"">&nbsp;" & objRS("Mobile2") & "</td>"
          Response.Write "<td width=""120"" class=""p"">&nbsp;" & objRS("Phone") & "</td>"
          Response.Write "<td width=""100"" class=""p"">" & objRS("Status") & "</td>"
		  Response.Write "</tr>"
    
          objRS.MoveNext
        Loop

    end if
   
    objRS.close
    objConn.close
    Set objRS = Nothing
    Set objConn = Nothing
%>
