
<%
searchfor = ucase(trim(request("searchfor")))

Set eventrs = Server.CreateObject( "ADODB.Recordset" )
eventrs.ActiveConnection = Con
sqlString = _
"SELECT	 	eventno, desc, catdesc, location, nicedates, fromdate, maxall, taken1, taken2, taken3, taken4 " &_
"FROM 			bkitevents " &_
"left join		bkitcategory on bkitevents.categoryno = bkitcategory.categoryno " &_
"WHERE 		status <> 'Cancel' and " &_
"				fromdate >= date() "

if not show 		= "All"   then
	sqlstring = sqlstring & " and fromdate <= date() + " & show
end if
if not cat 		= "All" 	then 
	sqlstring = sqlstring & " and catdesc='" & cat & "' " 
end if
if not searchfor = "" 		then 
	sqlstring = sqlstring & "and ( upper(desc) like '%" & searchfor & "%' " &_
	"or upper(location) like '%" & searchfor & "%') " 
end if

sqlstring = sqlstring & "ORDER BY fromdate "
eventrs.Open sqlString
%>

<table width="100%" border=1
 cellpadding=2 cellspacing=0>
<tr>
  <td bgcolor="#000080" width="54%">
  <font color="#FFFFFF"><b>Event</b></font></td>
  <td bgcolor="#000080" width="36%">
  <font color="#FFFFFF"><b>Date</b></font></td>
  <td bgcolor="#000080" width="5%">
  <font color="#FFFFFF"><b>Taken</b></font></td>
  <td bgcolor="#000080" width="5%">
  <font color="#FFFFFF"><b>Free</b></font></td>
</tr>

<%
WHILE NOT eventrs.EOF
	takenall = clng(eventrs("taken1")) + clng(eventrs("taken2")) + clng(eventrs("taken3")) + clng(eventrs("taken4"))

	Set bookingsrs = Server.CreateObject( "ADODB.Recordset" )
	bookingsrs.ActiveConnection = Con
	sqlString = _
	"SELECT 		eventno, sum(slots1) as slots1, sum(slots2) as slots2, " &_
	"				sum(slots3) as slots3, sum(slots4) as slots4 " &_
	"from 			bkitbookings "&_
	"where 		eventno = '" & eventrs("eventno") & "' " &_
	"group by 		eventno"
	bookingsrs.Open sqlString
	if not bookingsrs.eof then
		takenall = takenall + clng(bookingsrs("slots1")) + clng(bookingsrs("slots2")) + clng(bookingsrs("slots3")) +clng(bookingsrs("slots4")) 
	end if

	freeall  = clng(eventrs("maxall")) - takenall
	%>
	<tr>
	  <td width="54%" bgcolor="#FFF0E1">
	  <a href=<%= "events/" & eventrs("eventno") & ".asp?ref=" & ref & "&eventno=" & eventrs("eventno") %> >
	  <font size="2">
	  <%=trim(eventrs( "desc" ))%>
	  <% if not isempty(trim(eventrs( "location"))) then %>
	  		<% response.write( ", " & trim(eventrs( "location" )) ) %>
	  <% end if %>
	  
	  </font></a>
	  </td>
	  <td width="36%" bgcolor="#FFF0E1">
		  <font size="2"><%= eventrs("nicedates") %></font>
	  </td>
	  <td width="5%" bgcolor="#FFF0E1" align="right">
		  <font size="2">
		  <% if takenall > 0 then %>
		  		<%= takenall %>
		  <% else %>
		  		<font color="#FFF0E1">.</font>
		  <% end if %>
		  </font>
	  </td>
	  <td width="5%" bgcolor="#FFF0E1" align="right">
		  <font size="2">
		  <% if freeall > 0 then %>
			  <%= freeall %>
		  <% else %>
			  <font color="#FF0000">Full</font>
		  <% end if %>
		  </font>
	  </td>
	</tr>
	
	<%
	eventrs.MoveNext
WEND
eventrs.close
%>
</table>