
	IF OBJECT_ID('NET_SEL_PARTICIPANTVOO') IS NOT NULL
		DROP PROC NET_SEL_PARTICIPANTVOO

	GO
		CREATE PROC NET_SEL_PARTICIPANTVOO
	(
	  @pCodvoo INT = NULL
	 ,@pCodEvento INT = NULL
	 ,@pCodParticipante INT = NULL
	 ,@pNumero VARCHAR(50) =  NULL 
	, @pStatus INT = NULL
    ,@pOrder VARCHAR(50) =  NULL 
	)
	AS 
		
		BEGIN
			SET NOCOUNT ON
			
					SELECT 
								  A.CodParticipante
							    , A.CodResponsavel   
								, A.Nome   
								, A.Email   
								, case When  A.Status is null Then  3 Else A.Status End As Status
								, A.DataUpdate
								,B.Nome As NomeEvento
								,B.SiglaEvento
								,B.CodEvento
								, case When  A.CodParticipante is not null Then  (
													SELECT 
																Count(X.CodVooTrecho) 
													FROM 
																NET_PARTICIPANTE_VOO  X
													Where 
																X.CodParticipante = A.CodParticipante And x.CodEvento = C.CodEvento) Else 0 End As Trechos
								,F.CodOrigem 
								,F.CodDestino
								,Case When  F.CodOrigem > 0 Then (SELECT TOP 1 Sigla FROM NET_AEROPORTO  Where CodAeroporto=F.CodOrigem ) Else '' End As Origem  
								,Case When  F.CodDestino > 0 Then (SELECT TOP 1 Sigla FROM NET_AEROPORTO  Where CodAeroporto=F.CodDestino ) Else '' End As Destino
		FROM 
						NET_PARTICIPANTE A
						,NET_EVENTO B
						,NET_PARTICIPANTE_EVENTO C
						,NET_PARTICIPANTE_VOO E
						,NET_VOO_TRECHO F
						,NET_VOO G
WHERE     
			   ((A.Status = @pStatus) Or (@pStatus IS NULL OR @pStatus  = 3))
			And (A.CodParticipante = C.CodParticipante)
			And (C.CodEvento  = B.CodEvento )
			And (E.CodParticipante = A.CodParticipante )
			And (E.CodEvento = B.CodEvento)
			And (E.CodVooTrecho = F.CodVooTrecho)
			And (G.CodVoo= F.CodVoo)
			And (C.CodEvento  =  @pCodEvento  OR (@pCodEvento IS NULL OR @pCodEvento = 0))
			And (A.CodParticipante =  @pCodParticipante  OR (@pCodParticipante  IS NULL OR @pCodParticipante  = 0))
			And (G.Numero=  @pNumero  OR (@pNumero IS NULL OR @pNumero= ''))

ORDER BY 
					 A.Nome
					,A.CodParticipante 
			

	SET NOCOUNT OFF 

	END 

