sql1 = "SELECT member_name, email, search_name, dot_net_search_string, sql_search_string from automated_search"
adoRs.Open sql1 , adoCn


do while not adoRs.EOF
	dim member_name
	dim email 
	dim search_name
	dim dot_net_search_string
	dim sql_search_string
	member_name = adoRs(0)
	email = adoRs(1)
	search_name = adoRs(2) 
	dot_net_search_string = adoRs(3)
	sql_search_string = adoRs(4)
	
	sql2 =  "exec PROC_Search_Items @keywords='dog', @descriptions=0, @category=1, @poster='', @zip=75044, @distance=500, @sort=0, @post_dt='12/11/2005'"

	msgbox(sql2)

	adoRs2.Open sql2, adoCn2
	if not adoRs2.EOF then
		Dim objMail
		Set objMail = CreateObject("CDO.Message")
		objMail.From = "fromemail@yahoo.com"
		objMail.To = email
		objMail.Subject= search_name
		objMail.TextBody = "Your Search has new results: " & dot_net_search_string 
		objMail.Send
		Set objMail = nothing
	end if
	adoRs2.Close
	adoRs.MoveNext
loop
adoRs.close
End Function

CREATE Proc dbo.PROC_Search_Items
@keywords as varchar(100) = '',
@descriptions as int = 0,
@category as bigint = 0,
@poster as varchar(100) = '',
@zip as int = 0,
@distance as int = 0,
@sort as int = 0,
@open as int = 1,
@pt as int = 0,
@post_dt as varchar(30) = ''
as
declare @SQL varchar(2000)
declare @where_key varchar (1000)
declare @keyword varchar(100)
declare @idx int
declare @distance_join varchar(100)
declare @distance_select varchar(100)
set @where_key = ' '

if @open = 1
	begin
	set  @where_key = @where_key + ' where i.end_date > getdate() and i.start_date < getdate() ' 
	end
else
	begin
	set  @where_key = @where_key + ' where i.end_date < getdate() and i.start_date < getdate() ' 
	end

if @pt > 0
	begin
	set  @where_key = @where_key + ' and i.type_id = '+ convert(varchar(20),@pt)
	end

--parse list of keywords comma separated
set @idx = CHARINDEX(',', @keywords)
WHILE (@idx > 0)
BEGIN
	set @keyword = LEFT(@keywords, @idx - 1)
	set @keywords = RIGHT(@keywords, LEN(@keywords) - @idx)
	set @idx = CHARINDEX(',', @keywords)
	if @descriptions = 1
		begin
		set @where_key = @where_key + ' AND (i.title LIKE ''%'+@keyword+'%'' OR i.description LIKE ''%'+@keyword+'%'') '
		end
	else
		begin
		set @where_key = @where_key + ' and i.title LIKE ''%'+@keyword+'%'' '
		end
end

if @poster <> 'All' and @poster <> '' 
begin
	set @where_key = @where_key + ' AND i.member_name LIKE ''%' + @poster + '%'' '
end

set @distance_join = ''

if @zip > 0 and @distance > 0
	begin
	truncate table search_items_distance_table

	insert into search_items_distance_table
	select i.item_id, (Degrees(acos(l2.sinlat * l.sinlat + l2.coslat * l.coslat * cos(l2.lonrad - (l.lonrad)))) * 69.05) distance
	from items i join locations l on i.zip = l.zip
	join locations l2 on l2.zip = @zip
	
	set @distance_join = ' JOIN search_items_distance_table d on i.item_id = d.item_id '
	set @distance_select = ', d.distance ' 
	if @distance > 0 
		begin
		set @where_key = @where_key + ' AND d.distance < ' + convert(varchar(20),@distance)  + ' '
		end
	end
else
	begin
	set @distance_select = ', 0 '
	end

if @category > 0 
begin
	truncate table search_items_category_table
	INSERT INTO search_items_category_table 
	EXEC PROC_Get_Subcategories @category
	
	set @where_key = @where_key + ' and i.category_id in ( select category_id from search_items_category_table ) '
end

if @post_dt <> ''
	begin
	set @where_key = @where_key +' and start_date > ''' + @post_dt + ''''
	end

If @sort = 2
	begin
	set @where_key = @where_key +' ORDER BY i.start_date DESC '
	end
else if @sort = 1
	begin
	set @where_key = @where_key +'  ORDER BY i.end_date ASC '
	end
Else
	begin
	set @where_key = @where_key +' ORDER BY i.end_date ASC '
	End 

set @SQL = 'SELECT i.item_id,itr.name,c.category_name,i.title,i.end_date,p.thumbnail,i.zip'+@distance_select+' as distance
		 FROM items i join item_type_ref itr on i.type_id = itr.type_id 
		JOIN item_status_ref isr on i.status_id = isr.status_id 
		JOIN categories c on i.category_id = c.category_id 
		JOIN photos p on i.display_image_id = p.photo_id ' 
		+ @distance_join +
		+@where_key
print(@SQL)
exec (@SQL)
GO