
-- SQLAzureMWParseTSQL TSQL Output File:
-- Generated at: 3/1/2014 3:07:23 PM

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsLocation' and column_name = 'address1')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] add address1 nvarchar(255) null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsLocation' and column_name = 'address2')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] add address2 nvarchar(255) null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsLocation' and column_name = 'city')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] add city nvarchar(255) null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsLocation' and column_name = 'postcode')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] add postcode nvarchar(255) null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsLocation' and column_name = 'country')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] add country nvarchar(255) null
 end
GO

alter procedure {databaseOwner}{objectQualifier}usp_nadev_Event_Location_Save_Location
(
 @locationId int,
 @moduleId int,
 @locationName nvarchar(max),
 @isArchived bit,
 @parentId int,
 @longitude nvarchar(255),
 @latitude nvarchar (255),
 @eventFontColor nvarchar(50),
 @eventColor nvarchar(50),
 @address1 nvarchar(255),
 @address2 nvarchar(255),
 @city nvarchar(255),
 @postcode nvarchar(255),
 @country nvarchar(255)
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] where locationId = @locationId)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] set moduleId = @moduleId,
																			 locationName = @locationName,
																			 isArchived = @isArchived,
																			 parentId = @parentId,
																			 longitude = @longitude,
																			 latitude = @latitude,
																			 eventFontColor =@eventFontColor,
																			 eventColor =@eventColor,
																			 address1 = @address1,
																			 address2 = @address2,
																			 city = @city,
																			 postcode = @postcode,
																			 country = @country
																		  										 
																			where locationId = @locationId
																			return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] (moduleId,locationName,isArchived,parentId,longitude,latitude,eventFontColor,eventColor,address1,address2,city,postcode,country)

	values (@moduleId,@locationName,@isArchived,@parentId,@longitude,@latitude,@eventFontColor,@eventColor,@address1,@address2,@city,@postcode,@country)
	
	SELECT SCOPE_IDENTITY()

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}tblNaDevEventsRecentlyViewed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 begin
	DROP TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsRecentlyViewed]
 end
GO


if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}tblNaDevEventsRecentlyViewed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 begin

	CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsRecentlyViewed](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[eventId] [int] NOT NULL,
		[StartDate] [datetime] not null,
		[EndDate] [datetime] not null,
		[CookieId] [nvarchar] (200),
		[DateAdded] [datetime] default getdate()
	 CONSTRAINT [PK_{objectQualifier}tblNaDevEventsRecentlyViewed] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
	)
 end
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}tblNaDevEventsAttraction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 begin

	CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction](
		[AttractionId] [int] IDENTITY(1,1) NOT NULL,
		[ModuleId] [int] not null,
		[AttractionName] [nvarchar] (255),
		[OpeningTime] [datetime] null,
		[ClosingTime] [datetime] null,
		[Address1] [nvarchar] (50) null,
		[Address2] [nvarchar] (50) null,
		[City] [nvarchar] (50) null,
		[Region] [nvarchar] (50) null,
		[Country] [nvarchar] (50) null,
		[Zipcode] [nvarchar] (50) null,
		[Telephone] [nvarchar] (50) null,
		[Summary] [nvarchar] (max),
		[Description] [nvarchar] (max),
		[OwnerId] [int] null,
		[Longitude] [nvarchar] (50) null,
		[Latitude] [nvarchar] (50) null,
		[SocialMediaCode] [nvarchar] (max) null,
		[DnnImageUrl] [nvarchar] (50) null,
		[ImageUrl] [nvarchar](max) null
	 CONSTRAINT [PK_{objectQualifier}tblNaDevEventsAttraction] PRIMARY KEY CLUSTERED 
	(
		[AttractionId] ASC
	)
	)
 end
GO



if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}funcListToTableInt]'))
	drop function {databaseOwner}[{objectQualifier}funcListToTableInt]
GO

CREATE FUNCTION {databaseOwner}[{objectQualifier}funcListToTableInt](@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Value INT
  )
AS
BEGIN
    --Declare helper to identify the position of the delim
    DECLARE @DelimPosition INT
    
    --Prime the loop, with an initial check for the delim
    SET @DelimPosition = CHARINDEX(@delim, @list)

    --Loop through, until we no longer find the delimiter
    WHILE @DelimPosition > 0
    BEGIN
        --Add the item to the table
        INSERT INTO @listTable(Value)
            VALUES(CAST(RTRIM(LEFT(@list, @DelimPosition - 1)) AS INT))
    
        --Remove the entry from the List
        SET @list = right(@list, len(@list) - @DelimPosition)

        --Perform position comparison
        SET @DelimPosition = CHARINDEX(@delim, @list)
    END

    --If we still have an entry, add it to the list
    IF len(@list) > 0
        insert into @listTable(Value)
        values(CAST(RTRIM(@list) AS INT))

  RETURN
END
GO


if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}usp_nadev_Event_ByEventIds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}usp_nadev_Event_ByEventIds
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}usp_nadev_Event_ByEventIds]
	@eventIds nvarchar(max)
AS

SELECT e.*
FROM {databaseOwner}[{objectQualifier}tblNaDevEvents] e
WHERE eventId IN (
					SELECT Value
					FROM {databaseOwner}[funcListToTableInt](@eventIds,',')
                   )

return

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}usp_nadev_Event_ByPortalId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}usp_nadev_Event_ByPortalId
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}usp_nadev_Event_ByPortalId]
	@portalId int
AS

SELECT e.*
FROM {databaseOwner}[{objectQualifier}tblNaDevEvents] e
WHERE thePortalId = @portalId

return

GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'TueOpeningTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add TueOpeningTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'TueClosingTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add TueClosingTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'WedOpeningTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add WedOpeningTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'WedClosingTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add WedClosingTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'ThurOpeningTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add ThurOpeningTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'ThurClosingTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add ThurClosingTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'FriOpeningTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add FriOpeningTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'FriClosingTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add FriClosingTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'SatOpeningTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add SatOpeningTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'SatClosingTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add SatClosingTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'SunOpeningTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add SunOpeningTime datetime null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'SunClosingTime')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add SunClosingTime datetime null
 end
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}tblNaDevEventsAttractionCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 begin

	CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsAttractionCategory](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[AttractionId] [int] not null,
		[CategoryId] [int] not null
	 CONSTRAINT [PK_{objectQualifier}tblNaDevEventsAttractionCategory] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
	)
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'RequiresModeration')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add RequiresModeration bit not null default 0
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'IsArchived')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add IsArchived bit not null default 0
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'PortalId')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] add PortalId int not null default 0
 end
GO


if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}tblNaDevEventsAttractionAttributes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 begin

	CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsAttractionAttributes](
		[AttributeId] [int] IDENTITY(1,1) NOT NULL,
		[AttractionId] [int] not null,
		[Name] [nvarchar](max) not null,
		[Value] [nvarchar](max) null
	 CONSTRAINT [PK_{objectQualifier}tblNaDevEventsAttractionAttributes] PRIMARY KEY CLUSTERED 
	(
		[AttributeId] ASC
	)
	)
 end
GO

IF NOT EXISTS (SELECT * 
           FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}tblNaDevEventsAttractionAttributes_tblNaDevEventsAttraction') 
             AND parent_object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}tblNaDevEventsAttractionAttributes]'))
BEGIN

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsAttractionAttributes]  WITH CHECK 
ADD  CONSTRAINT [FK_{objectQualifier}tblNaDevEventsAttractionAttributes_tblNaDevEventsAttraction] FOREIGN KEY([AttractionId])
REFERENCES {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] ([AttractionId])
ON DELETE CASCADE

END
GO


IF NOT EXISTS (SELECT * 
           FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}tblNaDevEventsAttractionCategory_tblNaDevEventsAttraction') 
             AND parent_object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}tblNaDevEventsAttractionCategory]'))
BEGIN

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsAttractionCategory]  WITH CHECK 
ADD  CONSTRAINT [FK_{objectQualifier}tblNaDevEventsAttractionCategory_tblNaDevEventsAttraction] FOREIGN KEY([AttractionId])
REFERENCES {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] ([AttractionId])
ON DELETE CASCADE

END
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventPriceTier' and column_name = 'sortOrder')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventPriceTier] add sortOrder int not null default 0
 end
GO

alter procedure {databaseOwner}{objectQualifier}usp_nadev_Event_Price_Tier_Save
(
 @priceId int,
 @eventId int,
 @TierName nvarchar(255),
 @costPerPlace money,
 @dnnRoleId int,
 @sortOrder int
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventPriceTier] where priceId = @priceId)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventPriceTier] set eventId = @eventId,
																			TierName = @TierName,
																			costPerPlace = @costPerPlace,
																			dnnRoleId = @dnnRoleId,
																			sortOrder = @sortOrder
																																					  										 
																			where priceId = @priceId
																			return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventPriceTier] (eventId,TierName,costPerPlace,dnnRoleId,sortOrder)

	values (@eventId,@TierName,@costPerPlace,@dnnRoleId,@sortOrder)
	
	SELECT SCOPE_IDENTITY()

GO

IF EXISTS (select * from dbo.[sysobjects] where id = object_id(N'{databaseOwner}[{objectQualifier}usp_EventPlanner_RadiusSearch]') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}usp_EventPlanner_RadiusSearch]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}usp_EventPlanner_RadiusSearch]
(
 @moduleId int,
 @minLat decimal(18,9),
 @minLong decimal(18,9),
 @maxLat decimal(18,9),
 @maxLong decimal(18,9),
 @lat decimal(18,9),
 @long decimal(18,9),
 @distance decimal(18,9)
)
as

set nocount on

SELECT * FROM {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] WHERE
	moduleId = @moduleId 
AND
    ((PI()/180) * Latitude >= @minLat AND (PI()/180) * Latitude <= @maxLat) AND ((PI()/180) * Longitude >= @minLong AND (PI()/180) * Longitude <= @maxLong)
AND
    acos(sin(@lat) * sin((PI()/180) * Latitude) + cos(@lat) * cos((PI()/180) * Latitude) * cos(((PI()/180) * Longitude) - (@long))) * 3959 <= @distance;

return 

go

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsLocation' and column_name = 'longitude' and data_type='decimal')
 begin
	update {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] set longitude =0 where longitude =''
 end
go


if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsLocation' and column_name = 'latitude' and data_type='decimal')
 begin
	update {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] set latitude =0 where latitude =''
 end
go

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] ALTER COLUMN Longitude decimal(18,9) null

GO

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsLocation] ALTER COLUMN Latitude decimal(18,9) null

GO

IF EXISTS (select * from dbo.[sysobjects] where id = object_id(N'{databaseOwner}[{objectQualifier}usp_EventPlanner_Attraction_RadiusSearch]') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}usp_EventPlanner_Attraction_RadiusSearch]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}usp_EventPlanner_Attraction_RadiusSearch]
(
 @moduleId int,
 @minLat decimal(18,9),
 @minLong decimal(18,9),
 @maxLat decimal(18,9),
 @maxLong decimal(18,9),
 @lat decimal(18,9),
 @long decimal(18,9),
 @distance decimal(18,9)
)
as

set nocount on

SELECT * FROM {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] WHERE
	moduleId = @moduleId 
AND
    ((PI()/180) * Latitude >= @minLat AND (PI()/180) * Latitude <= @maxLat) AND ((PI()/180) * Longitude >= @minLong AND (PI()/180) * Longitude <= @maxLong)
AND
    acos(sin(@lat) * sin((PI()/180) * Latitude) + cos(@lat) * cos((PI()/180) * Latitude) * cos(((PI()/180) * Longitude) - (@long))) * 3959 <= @distance;

return 

go

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'longitude' and data_type='decimal')
 begin
	
	update {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] set longitude =0 where longitude =''
 end
go

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsAttraction' and column_name = 'latitude' and data_type='decimal')
 begin
	
	update {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] set latitude =0 where latitude =''
 end
go

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] ALTER COLUMN Longitude decimal(18,9) null

GO

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsAttraction] ALTER COLUMN Latitude decimal(18,9) null

GO

alter procedure {databaseOwner}{objectQualifier}usp_GetAllBookingsForModule
(
 @moduleId int
)

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] 
	where moduleId = @moduleId and (PaymentStatusId = 1 or paymentstatusid = 7 or PaymentStatusId =8 or PaymentStatusId =9)
	order by bookingid desc

GO

alter procedure {databaseOwner}{objectQualifier}usp_GetAllUserSignedUpToEvents
(
 @userId int,
 @moduleId int
)

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] 
	where userId =@userId and moduleId = @moduleId and (PaymentStatusId =7 or PaymentStatusId =1 or PaymentStatusId =8 or PaymentStatusId =9)
	order by bookingid desc

GO

alter procedure {databaseOwner}{objectQualifier}usp_GetAllUsersSignedUpToEvent
(
 @eventId int
)

as

set nocount on
  
	select u.* from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] as u
	inner join {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] as a
	on a.addressid = u.billingaddressid
	where u.eventId = @eventid and (PaymentStatusId =9 or PaymentStatusId =1)
	order by a.surname,a.firstname

GO