Perimeter Search

ALTER PROCEDURE

[dbo].[Esi_GetEventsByPerimiter3]

@radius
decimal,

@centerProfile
nvarchar(200),

@MaxRows
int,

@StartDate
datetime,

@EventCategoryId
uniqueidentifier,

@SearchString
nvarchar(50)

AS




DECLARE @latitude float, @longitude float;


DECLARE @toRadians float


SET @toRadians = PI() / 180




DECLARE @R float


SET @R = 6371--3958.7558657440545 -- mi


if ( @centerProfile IS NOT NULL)


begin


set @latitude = (SELECT TOP(1) GeoLatitude FROM City WHERE(City.Name = @centerProfile))


set @longitude = (SELECT TOP(1) GeoLongitude FROM City WHERE(City.Name = @centerProfile))


End

if

( @EventCategoryId = '00000000-0000-0000-0000-000000000000')

begin






Select EventId, TempTable.Name, ShortDescription, EventCategory, City, StartDateTime, EventCategoryName


from

(


SELECT TOP(@MaxRows) e.EventId, e.Name, e.VenueId, e.ShortDescription, e.EventCategory, v.City, ed.StartDateTime, ec.Name as EventCategoryName

,Row_Number()
Over ( PARTITION by e.EventId, e.VenueId, v.City Order By ed.StartDateTime) as RowNumber


FROM Event AS e


INNER JOIN Venue AS v ON e.VenueId = v.VenueId


INNER JOIN City AS c ON v.City = c.Name


INNER JOIN EventDateTime as ed ON e.Eventid = ed.EventId


INNER JOIN EventCategory as ec ON e.EventCategory = ec.EventCategoryId




WHERE ((acos(sin(@latitude*@toRadians)*sin(c.GeoLatitude*@toRadians)+


cos(@latitude*@toRadians)*cos(c.GeoLatitude*@toRadians)*cos((c.GeoLongitude-@longitude)*@toRadians))

* @R) <= @radius)


AND (e.IsPublic = 1)


AND (ed.StartDateTime >= @StartDate)


AND (

e.Name
LIKE @SearchString


OR e.Organizer LIKE @SearchString


OR e.ShortDescription LIKE @SearchString


OR e.Description LIKE @SearchString

)


ORDER BY ed.StartDateTime

)
as TempTable


where RowNumber = 1

End

ELSE




Select EventId, TempTable.Name, ShortDescription, EventCategory, City, StartDateTime, EventCategoryName


from

(


SELECT TOP(@MaxRows) e.EventId, e.Name, e.VenueId, e.ShortDescription, e.EventCategory, v.City, ed.StartDateTime, ec.Name as EventCategoryName

,Row_Number()
Over ( PARTITION by e.EventId, e.VenueId, v.City Order By ed.StartDateTime) as RowNumber


FROM Event AS e


INNER JOIN Venue AS v ON e.VenueId = v.VenueId


INNER JOIN City AS c ON v.City = c.Name


INNER JOIN EventDateTime as ed ON e.Eventid = ed.EventId


INNER JOIN EventCategory as ec ON e.EventCategory = ec.EventCategoryId




WHERE ((acos(sin(@latitude*@toRadians)*sin(c.GeoLatitude*@toRadians)+


cos(@latitude*@toRadians)*cos(c.GeoLatitude*@toRadians)*cos((c.GeoLongitude-@longitude)*@toRadians))

* @R) <= @radius)


AND (e.IsPublic = 1)


AND (ed.StartDateTime >= @StartDate)


AND (ec.EventCategoryId = @EventCategoryId)


AND (

e.Name
LIKE @SearchString


OR e.Organizer LIKE @SearchString


OR e.ShortDescription LIKE @SearchString


OR e.Description LIKE @SearchString

)


ORDER BY ed.StartDateTime

)
as TempTable


where RowNumber = 1