-- =============================================
-- Author:		G.A.R.
-- Mad. date:   2014-06-06
-- Description:	Ritorna il package e i dati associati dove presenti
--              sostituisce la relativa funzione UDF f_getDataCodPackag
-- =============================================
CREATE PROCEDURE [stp_getDataCodPackag] 

	@CodItem	NVARCHAR(50),
	@CodClient	NVARCHAR(50),
    -- output variable
	@CodPackag      NVARCHAR(50)    OUTPUT,
	@TotQta         DECIMAL(18, 3)  OUTPUT,
	@Weight         DECIMAL(18, 6)  OUTPUT,
	@Tare           DECIMAL(18, 6)  OUTPUT,
	@NumImb         DECIMAL(9, 6)   OUTPUT,
	@Durata         DECIMAL(18, 6)  OUTPUT,
	@LivPackag      NVARCHAR(50)    OUTPUT,
	@LivPackagDet   NVARCHAR(50)    OUTPUT
AS
BEGIN

	SET NOCOUNT ON;
    SET XACT_ABORT ON;

	IF EXISTS ( SELECT * FROM dbo.Package2ItemClient WHERE CodClient = @CodClient AND CodItem = @CodItem)
    	-- primo tentativo: cerco il package dato SIA cliente che ITEM
		BEGIN
			SELECT TOP 1 @CodPackag = CodPackag, @TotQta = TotQta, @Weight = Weight, @Tare = Tare, @NumImb = NumImb, @Durata = Durata
                        ,@LivPackag = 'Item-Client-Package' , @LivPackagDet = @CodItem + '-' + @CodClient + '-' + CodPackag
            FROM dbo.Package2ItemClient 
            WHERE CodClient = @CodClient AND CodItem = @CodItem
			ORDER BY TotQta DESC
		END
	ELSE IF EXISTS ( SELECT * FROM Package2Item WHERE CodItem = @CodItem  )
		-- secondo tentativo: se non lo trovo cerco per SOLO ITEM
		BEGIN
			SELECT TOP 1 @CodPackag = CodPackag, @TotQta = TotQta, @Weight = Weight, @Tare = Tare, @NumImb = NumImb, @Durata = Durata
                        ,@LivPackag = 'Item-Package', @LivPackagDet = @CodItem + '-' + CodPackag
            FROM dbo.Package2Item WHERE CodItem = @CodItem
			ORDER BY TotQta DESC
		END
    ELSE
		-- secondo tentativo: se non lo trovo cerco per SOLO ITEM
		BEGIN
			SELECT TOP 1 @CodPackag = CodPackag, @TotQta = TotQta, @Weight = Weight, @Tare = Tare, @NumImb = NumImb, @Durata = Durata
                         ,@LivPackag = 'Package', @LivPackagDet = CodPackag
            FROM dbo.Package2 WHERE CodPackag = 0
		END
END

RETURN