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

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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventAttributes](
	[attributeId] [int] IDENTITY(1,1) NOT NULL,
	[EventId] [int] NOT NULL,
	[attributeName] [nvarchar](255) NOT NULL,
	[attributeValue] [ntext] not null
 CONSTRAINT [PK_{objectQualifier}tblNaDevEventAttributes] PRIMARY KEY CLUSTERED 
(
	[attributeId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)

GO

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

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

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

as

	select * from {databaseOwner}[{objectQualifier}tblNaDevEventAttributes] 
		where eventId = @eventId

return

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_nadev_EventAttributes_DeleteAttribute]
(
  @attributeId int
)

as

	delete {databaseOwner}[{objectQualifier}tblNaDevEventAttributes] where attributeId = @attributeId

return

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_nadev_EventAttributes_GetByAttributeName]
(
 @eventId int,
 @attributeName nvarchar(255)
)

as

	select * from {databaseOwner}[{objectQualifier}tblNaDevEventAttributes] 
		where attributeName = @attributeName and eventId = @eventId

return

GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadev_EventAttributes_InsertUpdateAttribute
(
 @attributeId int,
 @eventId int,
 @attributeName nvarchar(255),
 @attributeValue ntext
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventAttributes] where attributeId = @attributeId)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventAttributes] set attributeName = @attributeName,
																	  attributeValue = @attributeValue									 
									 
																	  where attributeId = @attributeId
																	  return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventAttributes] (eventId, attributeName, attributeValue)

	values (@eventId, @attributeName, @attributeValue)
	
	SELECT SCOPE_IDENTITY()

GO

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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventStateTaxTables](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[moduleId] [int] NOT NULL,
	[state] [nvarchar](255) NOT NULL,
	[rate] [decimal](18,2)not null
 CONSTRAINT [PK_{objectQualifier}tblNaDevEventStateTaxTables] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)

GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadev_EventStateTaxTable_InsertUpdate
(
 @Id int,
 @moduleId int,
 @state nvarchar(255),
 @rate decimal(18,2)
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventStateTaxTables] where id = @id)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventStateTaxTables] set   moduleId = @moduleId,
																				   state =@state,
																				   rate = @rate								 
									 
																	  where id = @id
																	  return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventStateTaxTables] (moduleId,state,rate)

	values (@moduleId,@state,@rate)
	
	SELECT SCOPE_IDENTITY()

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_nadev_EventStateTaxTable_GetAll]
(
 @moduleId int
)

as

	select * from {databaseOwner}[{objectQualifier}tblNaDevEventStateTaxTables] 
		where moduleId = @moduleId
		order by state
return

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_nadev_EventStateTaxTable_DeleteTaxItem]
(
  @id int
)

as

	delete {databaseOwner}[{objectQualifier}tblNaDevEventStateTaxTables] where id = @id

return

GO

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

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (
	[countryId] [int] IDENTITY (1, 1) NOT NULL ,
	[countryName] [varchar] (255) not null
    constraint [PK_{objectQualifier}tblNaDevEvent_EUCountries] primary key clustered
	(
		[countryId] asc
	)
)
GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadevEvents_getAllCountriesInEU

as

set nocount on

select * from {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] order by countryName asc

GO

delete from {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries]
GO

insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('AT')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('BE')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('BG')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('CY')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('CZ')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('DK')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('EE')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('FI')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('FR')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('DE')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('GR')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('HU')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('IE')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('IT')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('LV')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('LT')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('LU')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('MT')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('NL')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('PL')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('PT')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('RO')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('SK')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('SI')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('SE')
GO
insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('UK')
GO

insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
values ('GB')
GO

insert into {databaseOwner}[{objectQualifier}tblNaDevEvent_EUCountries] (countryName)
	values('ES')
GO

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

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)

	values (@eventId,@moduleId,@userId,@eventStartDate,@eventEndDate,@additionalInformation,@billingAddressId,@numberOfPlaces,@dateBookingMade,@paymentStatusId,@tabModuleId,@userSignUpAnswers,@totalCost,@numberOfChildPlaces,@discountAmount,@couponcode,@tax)
	
	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),
 @tax 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,
																				  numberOfChildPlaces = @numberOfChildPlaces,
																				  discountAmount =@discountAmount,
																				  couponcode = @couponcode,
																				  tax =@tax									 												 
									 
																				  where bookingId = @bookingId												

GO

alter procedure {databaseOwner}{objectQualifier}usp_naDevEventsGetBookingTotalsGroupedByDate
	@moduleId int,
	@startDate datetime,
	@endDate datetime
AS
BEGIN
SELECT  
cast(floor(cast(datebookingmade as float)) as datetime) as bookingDate,
   sum(totalcost) + sum(tax) - sum(discountAmount) as bookingTotal

FROM    
{databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent]

WHERE 
	paymentstatusid = 1
	AND moduleid = @moduleid
	and datebookingmade between @endDate and @startDate


GROUP BY    
cast(floor(cast(datebookingmade as float)) as datetime)

ORDER BY bookingDate asc
end
GO
