
-- 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}tblNaDevEventCoupons]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table {databaseOwner}[{objectQualifier}tblNaDevEventCoupons]
GO

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventCoupons] (
	[couponId] [int] IDENTITY (1000, 1) NOT NULL ,
	[couponCode] [nvarchar] (255) not null,
	[description] [ntext] not null,
	[minimumSubtotal] [money] null,
	[discount] [money] not null,
	[canusemultipletimes] [bit] not null,
	[expiryDate] [datetime] null,
	[hasbeenused] [bit] not null,
	[moduleId] [int] not null,
	[customeremailaddress] [nvarchar] (50) null,
	[startDate] [datetime] null,
	[couponType] [nvarchar] (50) not null,
    constraint [PK_{objectQualifier}tblNaDevEventCoupons] primary key clustered
	(
		[couponId] asc
	)
)
GO

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

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

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventCoupons] 
	where moduleId = @moduleId
	order by couponcode

GO

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

create procedure {databaseOwner}{objectQualifier}usp_NADevEventsGetCouponByCouponId
(
 @couponId int
)

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventCoupons] 
	where couponId = @couponId

GO

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

create procedure {databaseOwner}{objectQualifier}usp_NADevEventsDeleteCoupon
(
 @couponId int
)

as

set nocount on
  
	delete from {databaseOwner}[{objectQualifier}tblNaDevEventCoupons] 
	where couponId = @couponId

GO

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

create procedure {databaseOwner}{objectQualifier}usp_NADevEventsSaveCoupon
(
 @couponId int,
 @couponCode nvarchar(255),
 @description ntext,
 @minimumSubtotal money,
 @discount money,
 @canusemultipletimes bit,
 @expiryDate datetime,
 @hasbeenused bit,
 @moduleId int,
 @customeremailaddress nvarchar(50),
 @startdate datetime,
 @coupontype nvarchar(50)
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventCoupons] where couponid = @couponid)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventCoupons] set couponCode = @couponcode,	 
																		  description = @description,
																		  minimumsubtotal = @minimumsubtotal,
																		  discount = @discount,
																		  canusemultipletimes = @canusemultipletimes,
																		  expirydate = @expirydate,
																		  hasbeenused = @hasbeenused,
																		  moduleId = @moduleId,
																		  customeremailaddress = @customeremailaddress,
																		  startdate = @startdate,
																		  coupontype = @coupontype												 
									 
																	      where couponid = @couponid
																	      return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventCoupons] (couponcode,description,minimumsubtotal,discount,canusemultipletimes,expirydate,hasbeenused,moduleid,customeremailaddress,startdate,coupontype)

	values (@couponcode,@description,@minimumsubtotal,@discount,@canusemultipletimes,@expirydate,@hasbeenused,@moduleid,@customeremailaddress,@startdate,@coupontype)
	
	SELECT SCOPE_IDENTITY()

GO

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

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEventsUsersSignedUpToEvent' and column_name = 'couponCode')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] add couponCode nvarchar(50) null
 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,
 @numberOfChildPlaces int,
 @discountAmount money,
 @couponCode nvarchar(50)
)

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)

	values (@eventId,@moduleId,@userId,@eventStartDate,@eventEndDate,@additionalInformation,@billingAddressId,@numberOfPlaces,@dateBookingMade,@paymentStatusId,@tabModuleId,@userSignUpAnswers,@totalCost,@numberOfChildPlaces,@discountAmount,@couponcode)
	
	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,
 @numberOfChildPlaces int,
 @discountAmount money,
 @couponCode nvarchar(50)
)

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

GO
