USE [bp2]
GO
/****** Object:  StoredProcedure [dbo].[sp_lokacijaPaketa]    Script Date: 2/8/2014 10:18:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_lokacijaPaketa](
	@sifraPaketa int
)
as
begin
	begin tran

    declare @stigao int
	select @stigao = count(*) from paketi
	    where sifra = @sifraPaketa and realizovaniDatumPrimanja is null
	
	if @stigao = 1
	begin
	    select p.sifra as paket,p1.naziv as krece,t1.naziv as trenutnoOd,t2.naziv as trenutnoKa,p2.naziv as stize
		   from  paketi p join
		      transferi t on p.sifra = t.sifraPaketa
			     join klijenti k1 on p.sifraPosiljaoca = k1.sifra
				    join poste p1 on k1.sifraPoste = p1.sifra

                 join poste t1 on t.sifraPosteSlanja = t1.sifra
				 join poste t2 on t.sifraPostePrimanja = t2.sifra

				 join klijenti k2 on p.sifraPrimaoca = k2.sifra
				    join poste p2 on k2.sifraPoste = p2.sifra
			     where p.sifra = @sifraPaketa and t.datumPrimanja is null  
	end
	else
	begin
	   select p.sifra,p1.naziv,NULL,NULL,p2.naziv
		   from  paketi p 
		 
			     join klijenti k1 on p.sifraPosiljaoca = k1.sifra
				    join poste p1 on k1.sifraPoste = p1.sifra

				join klijenti k2 on p.sifraPrimaoca = k2.sifra
				    join poste p2 on k2.sifraPoste = p2.sifra

			     where p.sifra = @sifraPaketa
	end
	 commit tran
end

