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

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

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventReviews] (
	[reviewId] [int] IDENTITY (1, 1) NOT NULL ,
	[reviewer] [nvarchar] (255) not null,
	[rating] [int] not null,
	[review] [ntext] not null,
	[hasBeenModerated] [bit] not null,
	[eventId] [int] not null,
	[moduleId] [int] not null
    constraint [PK_{objectQualifier}tblNaDevEventReviews] primary key clustered
	(
		[reviewId] asc
	)
)
GO

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

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

as

set nocount on
  
	select r.* from {databaseOwner}[{objectQualifier}tblNaDevEventReviews] as r
	inner join {databaseOwner}[{objectQualifier}tblnadevevents] as e
	on e.eventid = r.eventid
	where r.moduleId = @moduleId and e.isDeleted = 0 and e.isactive =1
	order by reviewer

GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadevEventsDeleteReview
(
 @reviewId int
)

as

set nocount on
  
	delete from {databaseOwner}[{objectQualifier}tblNaDevEventReviews] 
	where reviewId = @reviewId

GO

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

create procedure {databaseOwner}{objectQualifier}usp_naDevEventsGetAllEventReviews
(
 @eventId int
)

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventReviews] 
	where eventId = @eventId and hasbeenmoderated =1
	order by reviewer

GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadevEventsInsertUpdateReview
(
 @reviewId int,
 @eventId int,
 @moduleId int,
 @reviewer nvarchar(255),
 @rating int,
 @review ntext,
 @hasBeenModerated bit
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventReviews] where reviewId = @reviewId)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventReviews] set moduleId = @moduleId,
																		  eventId = @eventId,
																		  reviewer = @reviewer,
																		  rating = @rating,
																		  review = @review,
																		  hasBeenModerated =@hasBeenModerated			 
									 
																		  where reviewId = @reviewId
																		  return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventReviews] (moduleId,eventId,reviewer,rating,review,hasBeenModerated)

	values (@moduleId,@eventId,@reviewer,@rating,@review,@hasBeenModerated)
	
	SELECT SCOPE_IDENTITY()

GO


GO
