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

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

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventCategories] (
	[categoryId] [int] IDENTITY (1, 1) NOT NULL ,
	[categoryName] [nvarchar] (255) not null,
	[moduleId] [int] not null,
	[isActive] [bit] not null
    constraint [PK_{objectQualifier}tblNaDevEventCategories] primary key clustered
	(
		[categoryId] asc
	)
)
GO

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

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

as

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

GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadevEventsDeleteCategory
(
 @categoryId int
)

as

set nocount on
  
	update {databaseOwner}[{objectQualifier}tblNaDevEvents] set categoryId = null
															where categoryId = @categoryId

	delete from {databaseOwner}[{objectQualifier}tblNaDevEventCategories] 
	where categoryId = @categoryId

GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadevEventsInsertUpdateCategory
(
 @categoryId int,
 @moduleId int,
 @categoryName nvarchar(255),
 @isActive bit
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventCategories] where categoryId = @categoryId)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventCategories] set moduleId = @moduleId,
																			 categoryName = @categoryName,
																			 isActive = @isActive 
																		  										 
									 
																			where categoryId = @categoryId
																			return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventCategories] (moduleId,categoryName,isActive)

	values (@moduleId,@categoryName,@isActive)
	
	SELECT SCOPE_IDENTITY()

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
)

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

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

GO
