Samuele E. Locatelli
68 lines
1.8 KiB
Transact-SQL
68 lines
1.8 KiB
Transact-SQL
|
|
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2014.01.15
|
|
-- Description: stp_packageCost
|
|
-- SOSTITUITA da query + funzione
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_packageCost]
|
|
(
|
|
@CodItem NVARCHAR(50) = '',
|
|
@CodClient NVARCHAR(50) = '',
|
|
@CodPackag NVARCHAR(50) = '',
|
|
@PDesc NVARCHAR(50) OUTPUT,
|
|
@RMWeight DECIMAL(9,4) OUTPUT,
|
|
@Weight DECIMAL(9,4) OUTPUT,
|
|
@Tare DECIMAL(9,4) OUTPUT,
|
|
@PackCost DECIMAL(9,6) OUTPUT,
|
|
@TareStRat DECIMAL(9,4) OUTPUT
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @trovati INT = 0,
|
|
@RMCost DECIMAL(9,3)
|
|
|
|
-- calcolo RMCost e RMWeight da tab PackageDet
|
|
SELECT @RMCost = RMCost, @RMWeight = RMWeight, @PDesc= PackagDesc
|
|
FROM PackagDet
|
|
WHERE CodPackag = @CodPackag
|
|
|
|
-- Calcolo peso e tara da selezione gerarchica
|
|
SELECT @trovati = COUNT(*)
|
|
FROM Package2ItemClient WHERE CodItem = @CodItem AND CodClient = @CodClient AND CodPackag = @CodPackag
|
|
|
|
IF(@trovati > 0)
|
|
-- primo tentativo: cerco il package dato SIA Cliente che Item che Package
|
|
BEGIN
|
|
SELECT TOP 1 @Weight = [Weight], @Tare = Tare
|
|
FROM Package2ItemClient WHERE CodItem = @CodItem AND CodClient = @CodClient AND CodPackag = @CodPackag
|
|
ORDER BY TotQta DESC
|
|
END
|
|
ELSE
|
|
-- secondo tentativo: se non lo trovo cerco per SOLO ITEM e package
|
|
BEGIN
|
|
SELECT @trovati = COUNT(*) FROM Package2Item WHERE CodItem = @CodItem AND CodPackag = @CodPackag
|
|
IF(@trovati > 0)
|
|
BEGIN
|
|
SELECT TOP 1 @Weight = [Weight], @Tare = Tare
|
|
FROM Package2Item WHERE CodItem = @CodItem AND CodPackag = @CodPackag
|
|
ORDER BY TotQta DESC
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @Weight = [Weight], @Tare = Tare
|
|
FROM Package2 WHERE CodPackag = @CodPackag
|
|
END
|
|
END
|
|
|
|
|
|
-- faccio ultimi calcoli!
|
|
SELECT @PackCost = @RMCost / @Weight, @TareStRat = (@RMWeight - @Tare) / @Weight
|
|
|
|
END
|
|
|
|
|