
-- 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 = 'isActive')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEvents] add isActive 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 = 'applyRoleId')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEvents] add applyRoleId int null
 end
GO

if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '{objectQualifier}tblNaDevEvents' and column_name = 'ApplyRoleExpiryDays')
 begin
   alter table {databaseOwner}[{objectQualifier}tblNaDevEvents] add ApplyRoleExpiryDays 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,
 @isActive bit,
 @applyRoleId int,
 @ApplyRoleExpiryDays 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,
																	  isActive = @isActive,
																	  applyRoleId =@applyRoleId,
																	  ApplyRoleExpiryDays = @ApplyRoleExpiryDays				 												 
									 
																	  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)

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

GO

alter procedure {databaseOwner}{objectQualifier}USP_NaDevEvent_CountPortalsInstalledOn

as

set nocount on

select distinct thePortalId from {databaseOwner}[{objectQualifier}tblNaDevEvents]

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)
	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)
	order by bookingid desc

GO
