1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
| GO
/****** Object: UserDefinedFunction [dbo].[Fraisportcommandeclient] Script Date: 07/06/2015 11:02:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Fraisportcommandeclient] (@NombreArticles INT,
@Transporteur VARCHAR(50),
@CodeClient VARCHAR(50))
RETURNS FLOAT
AS
BEGIN
-- Declare the return variable here
DECLARE @Result FLOAT
DECLARE @Pays VARCHAR(50)
DECLARE @OrigineCommande VARCHAR(6)
DECLARE @PortFacture VARCHAR(1)
DECLARE @Professionel VARCHAR(2)
DECLARE @T1 FLOAT
DECLARE @T2 FLOAT
DECLARE @NCD INT
DECLARE @T INT
DECLARE @B INT
SELECT @PortFacture = COALESCE([ports], 'Y'),
@Professionel = profess,
@Pays = Lower(country),
@OrigineCommande = prcode
FROM clients
WHERE code = @CodeClient
-- Adaptation du mode de transport pour la France
IF @pays = 'france (metropole)'
BEGIN
IF @NombreArticles > 2
SET @Transporteur = 'colissimo suivi'
ELSE
SET @Transporteur = 'lettre max';
END
IF ( @OrigineCommande LIKE '%PM%' )
OR ( @PortFacture = 'N' )
OR ( @PortFacture = 'Y' AND @Professionel = '1' )
SET @Result = 0.0
ELSE
BEGIN
--SET @B = 0
SELECT
--@B = 1,
@T1 = pt.tarif1,
@T2 = pt.tarif2,
@NCD = pt.nbcd1
FROM portstransp pt,
portspays pz
WHERE pt.nomtransp = @Transporteur
AND Charindex(CONVERT(CHAR(1), pt.idtransp), pz.transporteurs) > 0
AND pz.paysfr IN ( 'FRA', @Pays )
--IF @B = 0
IF @@ROWCOUNT = 0
SET @Result = 10
ELSE
BEGIN
IF @NombreArticles <= @NCD
SET @Result = @T1
ELSE
SET @Result = @T2
END
END
RETURN @Result
END |
Partager