
-- 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}usp_naDevEventsGetBookingTotalsGroupedByDate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}usp_naDevEventsGetBookingTotalsGroupedByDate
GO

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

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

create procedure {databaseOwner}{objectQualifier}usp_naDevEventsGetBookingCountsGroupedByDate
	@moduleId int,
	@startDate datetime,
	@endDate datetime
AS
BEGIN
SELECT  
cast(floor(cast(datebookingmade as float)) as datetime) as bookingDate,
   count(bookingid) 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

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

--event digital downloads table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE {databaseOwner}[{objectQualifier}tblNaDevEventDigitalDownloads](
	[downloadId] [int] IDENTITY(1,1) NOT NULL,
	[EventId] [int] NOT NULL,
	[fileName] [nvarchar](100) NOT NULL
 CONSTRAINT [PK_{objectQualifier}tblNaDevEventDigitalDownloads] PRIMARY KEY CLUSTERED 
(
	[downloadId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_nadev_EventDigitalDownloadsDeleteDownload]
(
 @downloadId int
)

as

	delete {databaseOwner}[{objectQualifier}tblNaDevEventDigitalDownloads] where downloadId = @downloadId

return

GO

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

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

as

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

GO

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

create procedure {databaseOwner}{objectQualifier}usp_nadev_EventDigitalDownloadsInsertUpdateDigitalDownload
(
 @downloadId int,
 @eventId int,
 @fileName nvarchar(100)
)

as

set nocount on
  
 if exists(select null from {databaseOwner}[{objectQualifier}tblNaDevEventDigitalDownloads] where downloadId = @downloadId)
	begin
		update {databaseOwner}[{objectQualifier}tblNaDevEventDigitalDownloads] set eventId = @eventId,
																			 fileName = @fileName																	  										 
									 
																			 where downloadId = @downloadId
																			 return @@identity
			end 
else
	insert into {databaseOwner}[{objectQualifier}tblNaDevEventDigitalDownloads] (eventId,fileName)

	values (@eventId,@fileName)
	
	SELECT SCOPE_IDENTITY()

GO
