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

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

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

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestion] (
	[signUpQuestionId] [int] IDENTITY (1000, 1) NOT NULL ,
	[eventId] [int]NOT NULL,
	[QuestionName] [nvarchar] (255),
	[controlType] [nvarchar] (50) not null,
	[isRequired] [bit] not null,
	[sortOrder] [int] not null
    constraint [PK_{objectQualifier}tblNaDevEventSignUpQuestion] primary key clustered
	(
		[signUpQuestionId] asc
	)
)
GO


ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestion]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}tblNaDevEventSignUpQuestion_tblNaDevEvents] FOREIGN KEY([eventId])
REFERENCES {databaseOwner}[{objectQualifier}tblNaDevEvents] ([eventId])
ON DELETE CASCADE
GO

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestionOptions] (
	[signUpQuestionOptionId] [int] IDENTITY (1, 1) NOT NULL ,
	[signUpQuestionId] [int] NOT NULL,
	[name] [nvarchar] (255) NULL,
	[sortOrder] [int] NOT NULL,
	[priceAdjustment] [money] not null
    constraint [PK_{objectQualifier}tblNaDevEventSignUpQuestionOptions] primary key clustered
	(
		[signUpQuestionOptionId] asc
	)
)
GO

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestionOptions]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}tblNaDevEventSignUpQuestionOptions_tblNaDevEventSignUpQuestion] FOREIGN KEY([signUpQuestionId])
REFERENCES {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestion] ([signUpQuestionId])
ON DELETE CASCADE
GO

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

create procedure {databaseOwner}{objectQualifier}usp_NaDevEvent_GetAllEventSignUpQuestions
(
@eventId int
)
as

set nocount on

select * from {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestion]
where eventId = @eventId
order by sortOrder asc

GO

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

create procedure {databaseOwner}{objectQualifier}usp_insertEventSignUpQuestion
(
 @eventId int,
 @questionName nvarchar(255),
 @controlType nvarchar(50),
 @isRequired bit,
 @sortOrder int
)

as

set nocount on
  
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestion] (eventId,questionName,controlType,isRequired,sortOrder)

	values (@eventId,@questionName,@controlType,@isRequired,@sortOrder)
	
	SELECT SCOPE_IDENTITY()
			
	return

GO

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

create procedure {databaseOwner}{objectQualifier}usp_updateEventSignUpQuestion
(
 @signUpQuestionId int,
 @eventId int,
 @questionName nvarchar(255),
 @controlType nvarchar(50),
 @isRequired bit,
 @sortOrder int
)

as

set nocount on
  
	update {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestion] 
		set eventId =@eventId,
			questionName = @questionName,
			controlType = @controlType,
			isrequired = @isRequired,
			sortorder = @sortOrder

			where signUpQuestionid = @signupquestionid

GO

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

create procedure {databaseOwner}{objectQualifier}usp_insertEventSignUpQuestionOption
(
 @signUpQuestionId int,
 @name nvarchar(255),
 @sortOrder int,
 @priceAdjustment money
)

as

set nocount on
  
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestionOptions] (signUpQuestionId,name,sortOrder,priceAdjustment)

	values (@signUpQuestionId,@name,@sortOrder,@priceAdjustment)
			
	return

GO

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

create procedure {databaseOwner}{objectQualifier}usp_deleteAllEventSignUpQuestionOptions
(
 @signUpQuestionId int
)

as

set nocount on
  
	delete from {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestionOptions] where signUpQuestionId =@signUpQuestionId
		
GO

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

create procedure {databaseOwner}{objectQualifier}usp_NaDevEvent_GetAllEventSignUpQuestionOptions
(
@signUpQuestionId int
)
as

set nocount on

select * from {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestionOptions]
where signUpQuestionId = @signUpQuestionId
order by sortOrder asc

GO

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

create procedure {databaseOwner}{objectQualifier}usp_deleteSignUpQuestion
(
 @signUpQuestionId int
)

as

set nocount on
  
	delete from {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestionOptions] where signUpQuestionId =@signUpQuestionId

	delete from {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestion] where signUpQuestionId =@signUpQuestionId
		
GO

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

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsUsersSignedUpToEvent' and column_name = 'totalCost')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] add totalCost money not null default 0
 end
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
)

as

set nocount on
  

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

	values (@eventId,@moduleId,@userId,@eventStartDate,@eventEndDate,@additionalInformation,@billingAddressId,@numberOfPlaces,@dateBookingMade,@paymentStatusId,@tabModuleId,@userSignUpAnswers,@totalCost)
	
	SELECT SCOPE_IDENTITY()
			
	return

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
)

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									 												 
									 
																				  where bookingId = @bookingId												

GO


GO
