/****** Object:  UserDefinedFunction [dbo].[fnKartrisProduct_GetMinPrice]    Script Date: 08/11/2014 14:36:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Mohammad
-- Reworked:    Craig
-- Create date: 02/10/2008 13:23:42
-- Reworked:    2014/08/11 14:24
-- Description:    Calculate minimum price.
-- =============================================
CREATE FUNCTION [dbo].[fnKartrisProduct_GetMinPrice] 
(
    -- Add the parameters for the function here
    @V_ProductID as int
    
)
RETURNS real
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result real;

    -- Add the T-SQL statements to compute the return value here
    SELECT @Result = Min(V_Price) FROM tblKartrisVersions WHERE V_ProductID = @V_ProductID AND V_Live = 1 AND tblKartrisVersions.V_CustomerGroupID IS NULL;
    
    -- Quantity Discount value
    DECLARE @QD_MinPrice as real;
    SELECT @QD_MinPrice = Min(QD_Price)
    FROM dbo.tblKartrisQuantityDiscounts INNER JOIN tblKartrisVersions 
        ON tblKartrisQuantityDiscounts.QD_VersionID = tblKartrisVersions.V_ID
    WHERE tblKartrisVersions.V_Live = 1 AND tblKartrisVersions.V_ProductID = @V_ProductID AND tblKartrisVersions.V_CustomerGroupID IS NULL;

    IF @QD_MinPrice <> 0 AND @QD_MinPrice IS NOT NULL AND @QD_MinPrice < @Result
    BEGIN
        SET @Result = @QD_MinPrice
    END
    
    -- Option Discount value
    DECLARE @OD_MinDiscount as real;
    SELECT @OD_MinDiscount = MIN(P_OPT_PriceChange)
    FROM dbo.tblKartrisProductOptionLink kpol 
    WHERE kpol.P_OPT_ProductID = @V_ProductID
    
    IF @OD_MinDiscount < 0 AND @OD_MinDiscount IS NOT NULL
    BEGIN
        -- Case to decimal to work around real calculation errors
        SET @Result =  CAST(@Result AS DECIMAL(10,5)) + CAST(@OD_MinDiscount AS DECIMAL(10,5))
    END

    IF @Result IS NULL
    BEGIN
        SET @Result = 0;
    END
    -- Return the result of the function
    RETURN @Result

END
GO

/****** Object:  UserDefinedFunction [dbo].[fnKartrisProduct_GetMinPriceWithCG]    Script Date: 08/11/2014 15:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Mohammad
-- Reworked:    Craig
-- Create date: 02/10/2008 13:23:42
-- Reworked:    2014/08/11 14:24
-- Description:    Calculate minimum price considering
--                customer group.
-- =============================================
CREATE FUNCTION [dbo].[fnKartrisProduct_GetMinPriceWithCG] 
(
    -- Add the parameters for the function here
    @V_ProductID as int,
    @CG_ID as smallint
    
)
RETURNS real
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result real;

    IF @CG_ID = 0 OR @CG_ID IS NULL BEGIN
        -- No customer group given
        SET @Result = dbo.fnKartrisProduct_GetMinPrice(@V_ProductID);
    END    
    ELSE BEGIN
        -- Add the T-SQL statements to compute the return value here
        SELECT @Result = Min(V_Price) FROM tblKartrisVersions 
        WHERE V_ProductID = @V_ProductID AND V_Live = 1
            AND (tblKartrisVersions.V_CustomerGroupID IS NULL OR tblKartrisVersions.V_CustomerGroupID = @CG_ID);

        -- Customer Group Pricing.
        DECLARE @CGP_MinPrice as real;
        SELECT @CGP_MinPrice = ISNULL(Min([CGP_Price]), @Result)
        FROM [dbo].[tblKartrisCustomerGroupPrices]
        WHERE [CGP_CustomerGroupID] = @CG_ID 
            AND [CGP_VersionID] IN (SELECT V_ID FROM tblKartrisVersions WHERE V_ProductID = @V_ProductID);

        IF @CGP_MinPrice <> 0 AND @CGP_MinPrice IS NOT NULL AND @CGP_MinPrice < @Result
        BEGIN
            SET @Result = @CGP_MinPrice
        END
        
        -- Quantity Discount Pricing
        DECLARE @QD_MinPrice as real;
        SELECT @QD_MinPrice = Min(QD_Price)
        FROM dbo.tblKartrisQuantityDiscounts INNER JOIN tblKartrisVersions 
            ON tblKartrisQuantityDiscounts.QD_VersionID = tblKartrisVersions.V_ID
        WHERE tblKartrisVersions.V_Live = 1 AND tblKartrisVersions.V_ProductID = @V_ProductID 
            AND (tblKartrisVersions.V_CustomerGroupID IS NULL OR tblKartrisVersions.V_CustomerGroupID = @CG_ID);

        IF @QD_MinPrice <> 0 AND @QD_MinPrice IS NOT NULL AND @QD_MinPrice < @Result
        BEGIN
            SET @Result = @QD_MinPrice
        END
        
        -- Option Pricing
        DECLARE @OD_MinDiscount as real;
        SELECT @OD_MinDiscount = MIN(P_OPT_PriceChange)
        FROM dbo.tblKartrisProductOptionLink kpol 
        WHERE kpol.P_OPT_ProductID = @V_ProductID
        
        IF @OD_MinDiscount < 0 AND @OD_MinDiscount IS NOT NULL
        BEGIN
            -- Case to decimal to work around real calculation errors
            SET @Result =  CAST(@Result AS DECIMAL(10,5)) + CAST(@OD_MinDiscount AS DECIMAL(10,5))
        END
    END

    IF @Result IS NULL
    BEGIN
        SET @Result = 0;
    END
    -- Return the result of the function
    RETURN @Result

END
GO