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

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

	CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventPriceTier](
		[PriceId] [int] IDENTITY(1,1) NOT NULL,
		[eventId] [int] NOT NULL,
		[TierName] nvarchar(255) not null,
		[costPerPlace] money not null,
		[dnnRoleId] int null
	 CONSTRAINT [PK_{objectQualifier}tblNaDevEventPriceTier] PRIMARY KEY CLUSTERED 
	(
		[PriceId] ASC
	)
	)
 end
GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadev_Event_Price_Tier_Save
(
 @priceId int,
 @eventId int,
 @TierName nvarchar(255),
 @costPerPlace money,
 @dnnRoleId 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
																																					  										 
																			where priceId = @priceId
																			return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventPriceTier] (eventId,TierName,costPerPlace,dnnRoleId)

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

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_nadev_Event_Price_Tier_delete_all]
(
 @eventId int
)

as

	delete from {databaseOwner}[{objectQualifier}tblNaDevEventPriceTier] where eventId = @eventId

return

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_nadev_Event_Price_Tier_get_all]
(
 @eventId int
)

as

	select * from {databaseOwner}[{objectQualifier}tblNaDevEventPriceTier] 
		where eventId = @eventId order by costPerPlace,TierName

return

GO

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

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

alter procedure {databaseOwner}{objectQualifier}usp_UpdateUserSignUpToEvent
(
 @bookingId int,
 @eventId int,
 @moduleId int,
 @userId int,
 @eventStartDate datetime,
 @eventEndDate datetime,
 @additionalInformation ntext,
 @billingAddressId int,
 @numberOfPlaces int,
 @dateBookingMade datetime,
 @paymentStatusId int,
 @tabModuleId int,
 @userSignUpAnswers ntext,
 @totalCost money,
 @numberOfChildPlaces int,
 @discountAmount money,
 @couponCode nvarchar(50),
 @tax money,
 @sentRemindEmail bit,
 @numberOfAttendeesString nvarchar(max),
 @isWaitList bit
)

as

set nocount on
  
update {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] set   eventId = @eventId,
																				  eventStartDate = @eventStartDate,
																				  eventEndDate = @eventEndDate,
																				  additionalInformation = @additionalInformation,
																				  billingAddressId = @billingAddressId,
																				  numberOfPlaces = @numberOfPlaces,
																				  userId=@userId,
																				  moduleId =@moduleId,
																				  dateBookingMade = @dateBookingMade,
																				  paymentStatusId = @paymentStatusId,
																				  tabModuleId = @tabModuleId,
																				  userSignUpAnswers = @userSignUpAnswers,
																				  totalCost = @totalCost,
																				  numberOfChildPlaces = @numberOfChildPlaces,
																				  discountAmount =@discountAmount,
																				  couponcode = @couponcode,
																				  tax =@tax,
																				  sentRemindEmail =@sentRemindEmail,
																				  numberOfAttendeesString =@numberOfAttendeesString,
																				  isWaitList = @isWaitList				 												 
									 
																				  where bookingId = @bookingId												

GO

alter procedure {databaseOwner}{objectQualifier}usp_insertUserSignUpToEvent
(
 @eventId int,
 @moduleId int,
 @userId int,
 @eventStartDate datetime,
 @eventEndDate datetime,
 @additionalInformation ntext,
 @billingAddressId int,
 @numberOfPlaces int,
 @dateBookingMade datetime,
 @paymentStatusId int,
 @tabModuleId int,
 @userSignUpAnswers ntext,
 @totalCost money,
 @numberOfChildPlaces int,
 @discountAmount money,
 @couponCode nvarchar(50),
 @tax money,
 @numberOfAttendeesString nvarchar(max),
 @isWaitList bit
)

as

set nocount on
  

	insert into {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] (eventId,moduleId,userId,eventStartDate,eventEndDate,additionalInformation,billingAddressId,numberOfPlaces,dateBookingMade,paymentStatusId,tabModuleId,userSignUpAnswers,totalCost,numberOfChildPlaces,discountAmount,couponcode,tax,numberOfAttendeesString,isWaitList)

	values (@eventId,@moduleId,@userId,@eventStartDate,@eventEndDate,@additionalInformation,@billingAddressId,@numberOfPlaces,@dateBookingMade,@paymentStatusId,@tabModuleId,@userSignUpAnswers,@totalCost,@numberOfChildPlaces,@discountAmount,@couponcode,@tax,@numberOfAttendeesString,@isWaitList)
	
	SELECT SCOPE_IDENTITY()
			
	return

GO

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

alter procedure {databaseOwner}{objectQualifier}usp_nadevEventsInsertUpdateCategory
(
 @categoryId int,
 @moduleId int,
 @categoryName nvarchar(255),
 @isActive bit,
 @eventFontColor nvarchar(50),
 @eventColor nvarchar(50),
 @parentId int,
 @dnnRoleId int
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventCategories] where categoryId = @categoryId)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventCategories] set moduleId = @moduleId,
																			 categoryName = @categoryName,
																			 isActive = @isActive,
																			 eventFontColor =@eventFontColor,
																			 eventColor =@eventColor,
																			 parentId = @parentId,
																			 dnnRoleId =@dnnRoleId																		  										 
									 
																			where categoryId = @categoryId
																			return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventCategories] (moduleId,categoryName,isActive,eventFontColor,eventColor,parentId,dnnRoleId)

	values (@moduleId,@categoryName,@isActive,@eventFontColor,@eventColor,@parentId,@dnnRoleId)
	
	SELECT SCOPE_IDENTITY()

GO

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

create procedure {databaseOwner}{objectQualifier}usp_GetAllWaitingListsBookingsForModule
(
 @moduleId int
)

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] 
	where moduleId = @moduleId and iswaitlist = 1
	order by bookingid desc

GO
