IF OBJECT_ID('[dbo].[least5]', 'FN') IS NOT NULL
    DROP FUNCTION [dbo].[least5]
    
GO

CREATE FUNCTION [dbo].[least5](@v1 float, @v2 float=NULL, @v3 float=NULL, @v4 float=NULL, @v5 float=NULL) 
RETURNS float AS
BEGIN
    DECLARE @return float
    SELECT TOP 1 @return=
    MIN(F) FROM (VALUES(@v1),(@v2),(@v3),(@v4),(@v5)) T(F);
    RETURN @return
END

GO

IF OBJECT_ID('[dbo].[greatest3]', 'FN') IS NOT NULL
    DROP FUNCTION [dbo].[greatest3]
    
GO

CREATE FUNCTION [dbo].[greatest3](@v1 float, @v2 float=NULL, @v3 float=NULL) 
RETURNS float AS
BEGIN
    DECLARE @return float
    SELECT TOP 1 @return=
    MAX(F) FROM (VALUES(@v1),(@v2),(@v3)) T(F);
    RETURN @return
END

GO

IF OBJECT_ID('[dbo].[greatest2]', 'FN') IS NOT NULL
    DROP FUNCTION [dbo].[greatest2]
    
GO

CREATE FUNCTION [dbo].[greatest2](@v1 float, @v2 float=NULL) 
RETURNS float AS
BEGIN
    DECLARE @return float
    SELECT TOP 1 @return=
    MAX(F) FROM (VALUES(@v1),(@v2)) T(F);
    RETURN @return
END

GO

IF OBJECT_ID('[dbo].[infokey_GetGoodsPriceInfo]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[infokey_GetGoodsPriceInfo]
    
GO


CREATE PROCEDURE [infokey_GetGoodsPriceInfo]
/* Получить инфо о цене и скидке на товар для инфокиоска. SRV-2311.
   exec dbo.[infokey_GetGoodsPriceInfo] '{"barcode":"2711230"}';
 */
 
    @requestJson varchar(max)
AS
	SET NOCOUNT ON
    DECLARE @Hierarchy Hierarchy  
    DECLARE @CurDate smalldatetime = GETDATE()
    DECLARE @GoodsCode varchar(20)
    DECLARE @barcode varchar(20)
    DECLARE @cou decimal(10,3)

    SELECT @barcode = stringValue 
    FROM   dbo.parseJSON (@requestJson)
    WHERE  name = 'barcode'

    EXECUTE infokey_ParseBar @barcode output, @cou output, null, null

    IF NOT EXISTS(SELECT 1 
                  FROM   GoodsBarCode4 (NOLOCK) 
                  WHERE  barcode = @barcode AND statusNotUsed = 0) BEGIN
        SELECT '{"errorCode":"1", "description":"Barcode Not Found"}'
        RETURN
    END

    SELECT TOP 1 @GoodsCode = GoodsCode 
    FROM  GoodsBarCode4 gb (NOLOCK) 
    WHERE barcode = @barcode

    DECLARE @ID_Price_5 int, @ID_Perc_5 int, @ID_DisSum_5 int, @ID_Price_35 int, @ID_Perc_35 int, @ID_DisSum_35 int
    SELECT
        /* 5 */
        @ID_Price_5     = MIN(CASE WHEN DiscountType =  5 AND IDRule = 2 THEN Id ELSE NULL END),
        @ID_Perc_5      = MIN(CASE WHEN DiscountType =  5 AND IDRule = 3 THEN Id ELSE NULL END),
        @ID_DisSum_5    = MIN(CASE WHEN DiscountType =  5 AND IDRule = 4 THEN Id ELSE NULL END),
        /* 35 */
        @ID_Price_35    = MIN(CASE WHEN DiscountType = 35 AND IDRule = 2 THEN Id ELSE NULL END),
        @ID_Perc_35     = MIN(CASE WHEN DiscountType = 35 AND IDRule = 3 THEN Id ELSE NULL END),
        @ID_DisSum_35   = MIN(CASE WHEN DiscountType = 35 AND IDRule = 4 THEN Id ELSE NULL END)
    FROM  Discount AS d (NOLOCK)
    WHERE @CurDate BETWEEN DiscB AND CASE WHEN CONVERT(varchar(8), DiscE, 114) = '00:00:00' THEN DATEADD(minute, -1, DATEADD(day, 1, DiscE)) ELSE DiscE END
      AND dbo.DToTime(@CurDate) BETWEEN dbo.DToTime(DiscB) AND CASE WHEN CONVERT(varchar(8), DiscE, 114) = '00:00:00' THEN dbo.DToTime(DATEADD(minute, -1, DATEADD(day, 1, DiscE))) ELSE dbo.DToTime(DiscE) END
      AND d.CashDelete = 0
      AND d.CashLoad = 1
      AND SUBSTRING(Days, DATEPART(weekday, @CurDate), 1) = 1
      AND d.GoodsCode = @GoodsCode

    INSERT INTO @Hierarchy 
    SELECT * FROM dbo.ParseXML((
        SELECT TOP 1
            gbc.barcode,
            g.goodsName AS title,
            g.Code AS marking,
            CONVERT(decimal(26,2), dbo.greatest2(gid.Price, gid.AltPrice) * cast(@cou as decimal(10,3))) AS oldGrossPrice,
            CONVERT(decimal(26,2),dbo.least5(Price_5, (100.0 - Perc_5)/100.0 * gid.Price, gid.Price - DisSum_5, 
                    NULLIF(fgid.FixPrice, 0), NULLIF(gid.Price, 0)) * cast(@cou as decimal(10,3))) AS grossPrice,
            CONVERT(decimal(26,2),dbo.greatest3(Price_35, (100.0 - nullif(Perc_35,0))/100.0 * NULLIF(gid.Price, 0), NULLIF(gid.Price, 0) - nullif(DisSum_35,0)) * cast(@cou as decimal(10,3))) AS cardPrice,
            Gai.GoodsDescription AS set6AddInfo
        FROM Goods4 AS G
        LEFT JOIN GoodsAddInfo AS Gai ON Gai.Code = G.Code AND Gai.Infonumber = 1
        LEFT JOIN (
                SELECT GoodsCode, 
                       MAX(Price) AS Price,
                       MAX(AltPrice) AS AltPrice
                FROM  GoodsInDepart (NOLOCK) 
                WHERE StatusNotUsed = 0 
                    AND SaleDisable = 0 
                    AND @CurDate BETWEEN ISNULL(GoodsBegin,@CurDate) AND ISNULL(CASE WHEN CONVERT(varchar(8), GoodsEnd, 114) = '00:00:00' THEN DATEADD(minute, -1, DATEADD(day, 1, GoodsEnd)) ELSE GoodsEnd END,@CurDate)
                GROUP BY GoodsCode) AS gid ON gid.GoodsCode = g.Code
        LEFT JOIN GoodsInDepart AS fgid (NOLOCK)  ON fgid.GoodsCode = gid.GoodsCode
            AND fgid.Price = gid.Price
            AND fgid.StatusNotUsed = 0
            AND fgid.SaleDisable = 0
            AND @CurDate BETWEEN ISNULL(fgid.FixPriceDateBegin,@CurDate) AND ISNULL(CASE WHEN CONVERT(varchar(8), fgid.FixPriceDateEnd, 114) = '00:00:00' THEN DATEADD(minute, -1, DATEADD(day, 1, fgid.FixPriceDateEnd)) ELSE fgid.FixPriceDateEnd END,@CurDate)
        LEFT JOIN GoodsBarCode4 AS gbc (NOLOCK) ON g.Code = gbc.GoodsCode
        LEFT JOIN(
            SELECT
                @GoodsCode AS GoodsCode,
                /* 5 */
                Price_5     = (SELECT TOP 1 Perc FROM Discount (NOLOCK) WHERE Id = @ID_Price_5 AND DiscountType = 5),
                Perc_5      = (SELECT TOP 1 Perc FROM Discount (NOLOCK) WHERE Id = @ID_Perc_5 AND DiscountType = 5),
                DisSum_5    = (SELECT Perc FROM Discount (NOLOCK) WHERE Id = @ID_DisSum_5 AND DiscountType = 5),
                /* 35 */
                Price_35    = (SELECT TOP 1 Perc FROM Discount (NOLOCK) WHERE Id = @ID_Price_35 AND DiscountType = 35),
                Perc_35     = (SELECT TOP 1 Perc FROM Discount (NOLOCK) WHERE Id = @ID_Perc_35 AND DiscountType = 35),
                DisSum_35   = (SELECT TOP 1 Perc FROM Discount (NOLOCK) WHERE Id = @ID_DisSum_35 AND DiscountType = 35)
            ) d1 ON d1.GoodsCode = gid.GoodsCode
        WHERE gbc.BarCode = @barcode
          AND gbc.StatusNotUsed = 0
        ORDER BY gid.Price DESC
        FOR XML PATH('product'), ROOT
        ))
    
    /* set data type for attributes*/
    UPDATE @Hierarchy
    SET    ValueType = 'real'
    WHERE  name IN ('grossPrice', 'cardPrice', 'oldGrossPrice')

    UPDATE @Hierarchy
    SET    ValueType = 'string'
    WHERE  name IN ('barcode', 'title', 'marking', 'set6AddInfo')
	
    /* escape quotes */
    UPDATE @Hierarchy
    SET    StringValue = REPLACE(StringValue, '"', '\"')
    WHERE  name IN ('set6addinfo', 'title')
    
    SELECT dbo.toJSON(@Hierarchy)

GO