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


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

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

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

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

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

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

alter procedure {databaseOwner}{objectQualifier}usp_insertUpdateEvent
(
 @eventId int,
 @eventName nvarchar(4000),
 @description ntext,
 @eventStartDate dateTime,
 @eventEndDate dateTime,
 @allDayEvent bit,
 @isRecurringEvent bit,
 @recurringEventObject ntext,
 @recurFrequency nvarchar(255),
 @portalId int,
 @spacesAvailable int,
 @dnnRoleId int,
 @isDeleted bit,
 @costPerPlace money,
 @thePortalId int,
 @maxNumberPlacesPerPerson int
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEvents] where eventId = @eventId)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEvents] set   eventName = @eventName,
																	  description = @description,
																	  eventStartDate = @eventStartDate,
																	  eventEndDate = @eventEndDate,
																	  allDayEvent =@allDayEvent,
																	  isRecurringEvent = @isRecurringEvent,
																	  recurringEventObject = @recurringEventObject,
																	  recurFrequency = @recurFrequency,
																	  portalId = @portalId,
																	  spacesAvailable = @spacesAvailable,
																	  dnnRoleId = @dnnRoleId,
																	  isDeleted =@isDeleted,
																	  costPerPlace = @costPerPlace,
																	  thePortalId =@thePortalId,
																	  maxNumberPlacesPerPerson = @maxNumberPlacesPerPerson								 												 
									 
																	  where eventId = @eventId
																	  return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEvents] (eventName,description,eventStartDate,eventEndDate,allDayEvent,isRecurringEvent,recurringEventObject,recurFrequency,portalId,spacesAvailable, dnnRoleId,isDeleted,costPerPlace,thePortalId,maxNumberPlacesPerPerson)

	values (@eventName,@description,@eventStartDate,@eventEndDate,@allDayEvent,@isRecurringEvent,@recurringEventObject,@recurFrequency,@portalId,@spacesAvailable,@dnnRoleId,@isDeleted,@costPerPlace,@thePortalId,@maxNumberPlacesPerPerson)
	
	return @@identity

GO

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

as

set nocount on
  
	update {databaseOwner}[{objectQualifier}tblNaDevEvents] 
	set isDeleted =1
	where eventId = @eventId
GO

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

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsCheckoutLogging] (
	[loggingId] [int] IDENTITY (1, 1) NOT NULL ,
	[bookingId] [int] NOT NULL,
	[logMessage] [ntext] NOT NULL
    constraint [PK_{objectQualifier}tblNaDevEventsCheckoutLogging] primary key clustered
	(
		[loggingId] asc
	)
)
GO

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

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] (
	[bookingId] [int] IDENTITY (1000, 1) NOT NULL ,
	[moduleId] [int] not null,
	[eventId] [int]NOT NULL,
	[userId] [int] not null,
	[eventStartDate] [datetime] not null,
	[eventEndDate] [datetime] not null,
	[additionalInformation] [ntext],
	[billingAddressId] [int] not null,
	[numberOfPlaces] [int] not null,
	[dateBookingMade] [datetime] not null,
	[paymentStatusId] [int] not null,
	[tabModuleId] [int] not null
    constraint [PK_{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] primary key clustered
	(
		[bookingId] asc
	)
)
GO


ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsCheckoutLogging]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}tblNaDevEventsCheckoutLogging_tblNaDevEventsUsersSignedUpToEvent] FOREIGN KEY([bookingId])
REFERENCES {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] ([bookingId])
ON DELETE CASCADE
GO

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

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] (
	[addressId] [int] IDENTITY (1, 1) NOT NULL ,
	[address1] [nvarchar] (255)NOT NULL,
	[address2] [nvarchar] (255) NULL,
	[city] [nvarchar] (255) not NULL,
	[region] [nvarchar] (255) not NULL,
	[country] [nvarchar] (255) not NULL,
	[postcode] [nvarchar] (255) not NULL,
	[phone] [nvarchar] (255) not NULL,
	[firstName] [nvarchar] (255) not null,
	[surname] [nvarchar] (255) not null,
	[emailAddress] [nvarchar] (255) not NULL
    constraint [PK_{objectQualifier}tblNaDevEventsUsersSignedUpAddress] primary key clustered
	(
		[addressId] asc
	)
)
GO

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}tblNaDevEventsUsersSignedUpToEvent_tblNaDevEventsUsersSignedUpAddress] FOREIGN KEY([billingAddressId])
REFERENCES {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] ([addressId])
ON DELETE CASCADE
GO


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

create 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
)

as

set nocount on
  

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

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

GO

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

create 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
)

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

GO

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

create procedure {databaseOwner}{objectQualifier}usp_insertUserSignUpToEventAddress
(
 @address1 nvarchar(255),
 @address2 nvarchar(255),
 @city nvarchar(255),
 @region nvarchar(255),
 @country nvarchar(255),
 @postcode nvarchar(255),
 @phone nvarchar(255),
 @firstname nvarchar(255),
 @surname nvarchar(255),
 @emailAddress nvarchar(255)
)

as

set nocount on
  
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] (address1,address2,city,region,country,postcode,phone,firstName,surname,emailAddress)

	values (@address1,@address2,@city,@region,@country,@postcode,@phone,@firstName,@surname,@emailAddress)
	
	SELECT SCOPE_IDENTITY()
			
	return

GO

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

create 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 =1
	order by a.surname,a.firstname

GO

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

create procedure {databaseOwner}{objectQualifier}usp_GetUserSignedUpToEvent
(
 @bookingId int
)

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] 
	where bookingId =@bookingId

GO

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

create 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 =1
	order by bookingid desc

GO

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

create procedure {databaseOwner}{objectQualifier}usp_GetUserSignedUpToEventAddress
(
 @addressId int
)

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] 
	where addressId = @addressId

GO

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

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

as

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

GO

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

create procedure {databaseOwner}{objectQualifier}usp_insertCheckoutLoggingMessage
(
@bookingId int,
@logmessage ntext
)

as

set nocount on
  
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventsCheckoutLogging] (bookingId,logMessage)

	values (@bookingId,@logmessage)
			
GO

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

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEvents_License] (
	[licenseId] [int] IDENTITY (1, 1) NOT NULL ,
	[encryptedInstallDate] [nvarchar] (4000) not null,
	[license] [nvarchar] (4000) null,
	[encKey] [nvarchar] (4000) not null,
	[encIv] [nvarchar] (4000) not null
    constraint [PK_{objectQualifier}tblNaDevEvents_License] primary key clustered
	(
		[licenseId] asc
	)
)
GO

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

create procedure {databaseOwner}{objectQualifier}USP_NaDevEvent_InsertUpdateStoreLicenseInformation
(
 @encryptedInstallDate nvarchar (4000),
 @license nvarchar (4000),
 @encKey nvarchar (4000),
 @encIv nvarchar (4000)
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEvents_License])
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEvents_License] set encryptedInstallDate = @encryptedInstallDate,
																	 license = @license,
																	 encKey = @encKey,
																	 encIv = @encIv
									 
		end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEvents_License] (encryptedInstallDate, license,encKey,encIv)

	values (@encryptedInstallDate, @license,@encKey,@encIv)

GO

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

create procedure {databaseOwner}{objectQualifier}USP_NaDevEvent_GetStoreLicenseInformation

as

set nocount on

select * from {databaseOwner}[{objectQualifier}tblNaDevEvents_License]

GO

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

create procedure {databaseOwner}{objectQualifier}USP_NaDevEvent_CountPortalsInstalledOn

as

set nocount on

select COUNT(distinct thePortalId) as total from {databaseOwner}[{objectQualifier}tblNaDevEvents]

GO
