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

alter table {databaseOwner}[{objectQualifier}tblNaDevEvents] add isRecurringEvent bit not null default 0
GO

alter table {databaseOwner}[{objectQualifier}tblNaDevEvents] add recurringEventObject ntext null
GO

alter table {databaseOwner}[{objectQualifier}tblNaDevEvents] add recurFrequency nvarchar (255) not null default 'daily'
GO

alter table {databaseOwner}[{objectQualifier}tblNaDevEvents] add portalId int not null default 0
GO

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

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

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													 												 
									 
																	  where eventId = @eventId
																	  return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEvents] (eventName,description,eventStartDate,eventEndDate,allDayEvent,isRecurringEvent,recurringEventObject,recurFrequency,portalId)

	values (@eventName,@description,@eventStartDate,@eventEndDate,@allDayEvent,@isRecurringEvent,@recurringEventObject,@recurFrequency,@portalId)
	
	return @@identity

GO

alter table {databaseOwner}[{objectQualifier}tblNaDevEventsUserNotifications] add eventStartDate datetime not null default getDate()
GO
alter table {databaseOwner}[{objectQualifier}tblNaDevEventsUserNotifications] add eventEndDate datetime not null default getDate()
GO

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

create procedure {databaseOwner}{objectQualifier}usp_insertEventsUserNotifications
(
 @eventId int,
 @emailAddress nvarchar(255),
 @remindDate datetime,
 @eventStartDate datetime,
 @eventEndDate datetime
)

as

set nocount on
  
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventsUserNotifications] (eventId, emailAddress,remindDate,eventStartDate,eventEndDate)

	values (@eventId, @emailAddress,@remindDate,@eventStartDate,@eventEndDate)

GO

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

create procedure {databaseOwner}{objectQualifier}usp_getAllEvents
(
@portalId int
)
as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEvents] where portalId =@portalId order by eventStartDate desc
GO

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

create procedure {databaseOwner}{objectQualifier}usp_getAllEventsByStartAndEndDate
(
 @startDate datetime,
 @endDate datetime,
 @portalId int
)

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEvents] where eventStartDate >= @startDate and eventStartDate <= @endDate and portalId =@portalId
	order by eventStartDate desc
GO
