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

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

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEvents' and column_name = 'allowReviews')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEvents] add allowReviews bit not null default 1
 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,
 @isActive bit,
 @applyRoleId int,
 @ApplyRoleExpiryDays int,
 @eventSummary ntext,
 @eventColor nvarchar(50),
 @childCostPerPlace money,
 @eventSku nvarchar(50),
 @categoryId int,
 @allowRegistraion bit,
 @allowReviews bit
)

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,
																	  isActive = @isActive,
																	  applyRoleId =@applyRoleId,
																	  ApplyRoleExpiryDays = @ApplyRoleExpiryDays,
																	  eventSummary = @eventSummary,
																	  eventColor = @eventColor,
																	  childCostPerPlace = @childCostPerPlace,
																	  eventSku = @eventSku,
																	  categoryId = @categoryId,
																	  allowRegistraion =@allowRegistraion,
																	  allowReviews = @allowReviews										 
									 
																	  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,isActive,applyRoleId,ApplyRoleExpiryDays,eventSummary,eventColor,childCostPerPlace,eventSku,categoryId, allowRegistraion, allowReviews)

	values (@eventName,@description,@eventStartDate,@eventEndDate,@allDayEvent,@isRecurringEvent,@recurringEventObject,@recurFrequency,@portalId,@spacesAvailable,@dnnRoleId,@isDeleted,@costPerPlace,@thePortalId,@maxNumberPlacesPerPerson,@isActive,@applyRoleId,@ApplyRoleExpiryDays,@eventSummary,@eventColor,@childCostPerPlace,@eventSku,@categoryId, @allowRegistraion, @allowReviews)
	
	SELECT SCOPE_IDENTITY()

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(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

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

as

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

GO

alter 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 =7 or PaymentStatusId =1 or PaymentStatusId =8)
	order by bookingid desc

GO

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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventRelatedEvents](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EventId] [int] NOT NULL,
	[RelatedEventId] [int] NOT NULL
 CONSTRAINT [PK_{objectQualifier}tblNaDevEventRelatedEvents] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)

GO

ALTER TABLE {databaseOwner}[{objectQualifier}tblNaDevEventRelatedEvents]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}tblNaDevEventRelatedEvents_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_EventRelatedEvents_AddRelated]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}usp_nadev_EventRelatedEvents_AddRelated]
GO

create procedure {databaseOwner}[{objectQualifier}usp_nadev_EventRelatedEvents_AddRelated]
(
 @eventId int,
 @relatedEventId int
)

as

	insert into {databaseOwner}[{objectQualifier}tblNaDevEventRelatedEvents](EventId, relatedEventId)
			values(@EventId, @relatedEventId)

return @@identity

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_nadev_EventRelatedEvent_DeleteRelation]
(
  @eventId int,
  @relatedEventId int
)

as

	delete {databaseOwner}[{objectQualifier}tblNaDevEventRelatedEvents] where eventId = @eventId and relatedEventId =@relatedEventId

return

GO

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

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

as

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

return

GO

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


create PROCEDURE {databaseOwner}[{objectQualifier}usp_nadev_SearchEventsByEventName]
(
	@moduleId int,
	@eventName nvarchar(255),
	@limit int
)

	AS
	
	SELECT top (@limit) *
		FROM	{databaseOwner}[{objectQualifier}tblNaDevEvents]
		WHERE  eventName LIKE @eventName
				AND IsDeleted = 0
				AND PortalId = @moduleId 
				and isActive = 1
		ORDER BY eventname
	
return

GO
