-- =============================================
-- Author:		Steamware
-- Create date: 2014-01-30
-- Description:	Ritorna l'imballo e i dati associati ( sostituita da stored ma lasciata nel caso di query on-line )
-- =============================================
CREATE FUNCTION [dbo].[f_getDataCodPackag]
(
	@CodItem	NVARCHAR(50),
	@CodClient	NVARCHAR(50)
)
RETURNS 
@Table TABLE 
(
	[CodItem] [nvarchar](50) NOT NULL,
	[CodClient] [nvarchar](50) NOT NULL,
	[CodPackag] [nvarchar](50) NOT NULL,
	[TotQta] [decimal](18, 3) NOT NULL,
	[Weight] [decimal](18, 6) NOT NULL,
	[Tare] [decimal](18, 6) NOT NULL,
	[NumImb] [decimal](9, 6) NOT NULL,
	[Durata] [decimal](18, 6) NOT NULL,
	[LivPackag] [nvarchar](50) NOT NULL,
	[LivPackagDet] [nvarchar](50) NOT NULL
)
AS
BEGIN

	DECLARE @output NVARCHAR(50)
	DECLARE @trovati INT = 0
	-- setup iniziale: imposto a zero...
	SET		@output = '0'

 --   SELECT	@trovati = COUNT(*) FROM Package2ItemClient WHERE CodClient = @CodClient AND CodItem = @CodItem
	--IF(@trovati > 0)

	IF EXISTS ( SELECT * FROM dbo.Package2ItemClient WHERE CodClient = @CodClient AND CodItem = @CodItem)
    	-- primo tentativo: cerco il package dato SIA cliente che ITEM
		BEGIN
            INSERT @Table
			SELECT TOP 1 CodItem, CodClient , CodPackag, TotQta, Weight, Tare, NumImb, Durata
                        ,'Item-Client-Package', @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
            INSERT @Table
			SELECT TOP 1 CodItem, 'ND' AS CodClient , CodPackag, TotQta, Weight, Tare, NumImb, Durata
                        ,'Item-Package', @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
            INSERT @Table
			SELECT TOP 1 'ND', 'ND' AS CodClient , CodPackag, TotQta, Weight, Tare, NumImb, Durata
                         ,'Package', CodPackag
            FROM dbo.Package2 WHERE CodPackag = 0
		END

	RETURN 
END