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

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

as

	(select b.bookingId,b.eventStartDate,b.eventEndDate, pa.* from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] as pa
inner join {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] as b
on pa.addressId = b.billingAddressId
where eventId = @eventId 
and (PaymentStatusId = 1 or paymentstatusid = 7 or PaymentStatusId =9))
union
(select b.bookingId,b.eventStartDate,b.eventEndDate, ga.* from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] as b
inner join {databaseOwner}[{objectQualifier}tblNaDevEventsBookingGuest] as g
on b.bookingId = g.bookingId
inner join {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] as ga
on ga.addressId = g.addressId
where eventId = @eventId 
and (PaymentStatusId = 1 or paymentstatusid = 7 or PaymentStatusId =9))
order by bookingid

return

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_HasEmailRegisteredForEvent]
(
 @eventId int,
 @emailAddress nvarchar(255)
 )

 as

select count(*) as totalprimary,

(select count(*) from {databaseOwner}[{objectQualifier}tblNaDevEventsBookingGuest] as g
 inner join {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] as aa
 on g.addressId = aa.addressId
     where aa.addressId = g.addressId
 and aa.emailAddress = @emailAddress     
 )as additional

from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] as e
inner join {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] as pa
on pa.addressId = e.billingAddressId
where eventId = @eventId and 
pa.emailAddress = @emailAddress 
and (PaymentStatusId = 1 or paymentstatusid = 7 or PaymentStatusId =9)

go

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

create procedure {databaseOwner}{objectQualifier}usp_NaDevEvent_GetAllEventSignUpQuestion
(
@signUpQuestionId int
)
as

set nocount on

select * from {databaseOwner}[{objectQualifier}tblNaDevEventSignUpQuestion]
where signUpQuestionId = @signUpQuestionId

GO

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

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

as

set nocount on
  
	select * from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] 
	where eventId =@eventId and (PaymentStatusId =7 or PaymentStatusId =1 or PaymentStatusId =8 or PaymentStatusId =9)
	order by bookingid desc

GO

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

create procedure {databaseOwner}[{objectQualifier}usp_get_registered_user_details_for_event_by_emailAddress]
(
 @eventId int,
  @emailAddress nvarchar(255)
)

as

	(select b.bookingId,b.eventStartDate,b.eventEndDate, pa.* from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] as pa
inner join {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] as b
on pa.addressId = b.billingAddressId
where eventId = @eventId and pa.emailAddress =@emailAddress
and (PaymentStatusId = 1 or paymentstatusid = 7 or PaymentStatusId =9))
union
(select b.bookingId,b.eventStartDate,b.eventEndDate, ga.* from {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpToEvent] as b
inner join {databaseOwner}[{objectQualifier}tblNaDevEventsBookingGuest] as g
on b.bookingId = g.bookingId
inner join {databaseOwner}[{objectQualifier}tblNaDevEventsUsersSignedUpAddress] as ga
on ga.addressId = g.addressId 
where eventId = @eventId and ga.emailAddress =@emailAddress
and (PaymentStatusId = 1 or paymentstatusid = 7 or PaymentStatusId =9))
order by bookingid

return

GO