insert into @allowed_location values (760481), (760479), (760478), (760477)

DECLARE @RETVAL TABLE ([ID_LOCATION] BIGINT, [LOCATION_NAME] SQL_VARIANT, [CID] SQL_VARIANT,
    [CITY] SQL_VARIANT, [ADDRESS] SQL_VARIANT, [POST_CODE] SQL_VARIANT, [STATE] NVARCHAR(100), [DISTRIBUTOR] NVARCHAR(100), [EXCEPTION_LIST] NVARCHAR(MAX) DEFAULT(''), [ROW_TOOLTIP] NVARCHAR(MAX) DEFAULT(''))

DECLARE @METERS TABLE ([ID_METER] BIGINT, [ID_LOCATION] BIGINT)

DECLARE @ND1 NVARCHAR(50) = '<b>ND1</b> - Late delivery'
DECLARE @ND2 NVARCHAR(50) = '<b>ND2</b> - Risk of late delivery'
DECLARE @E1 NVARCHAR(50) = '<b>E1</b> - Missing data'
DECLARE @E2 NVARCHAR(50) = '<b>E2</b> - Faulty device'
DECLARE @E3 NVARCHAR(50) = '<b>E3</b> - Lost bank detection'
DECLARE @E4 NVARCHAR(50) = '<b>E4</b> - Unauthorised cylinder exchanged'
DECLARE @E5a NVARCHAR(50) = '<b>E5a</b> - Early delivery'
DECLARE @E5b NVARCHAR(50) = '<b>E5b</b> - Delivery overdue'
DECLARE @E5c NVARCHAR(50) = '<b>E5c</b> - No delivery planned'
DECLARE @E6b NVARCHAR(50) = '<b>E6b</b> - Human interference: right to left know changed'
DECLARE @E6a NVARCHAR(50) = '<b>E6a</b> - Human interference: left to right know changed'
DECLARE @E7a NVARCHAR(50) = '<b>E7a</b> - Left bank active with empty sites'
DECLARE @E7b NVARCHAR(50) = '<b>E7b</b> - Right bank active with empty sites'
DECLARE @E8 NVARCHAR(50) = '<b>E8</b> - Left bank active no delivery detected'
DECLARE @E9 NVARCHAR(50) = '<b>E9</b> - Right bank active no delivery detected'

INSERT INTO @RETVAL ([ID_LOCATION])
SELECT DISTINCT [ID_LOCATION] FROM [LOCATION]
WHERE ((SELECT COUNT(*) FROM @ALLOWED_LOCATION) = 0 OR [ID_LOCATION] IN (SELECT * FROM @ALLOWED_LOCATION))
AND ((SELECT COUNT(*) FROM @ALLOWED_DISTRIBUTOR) = 0 OR [ID_DISTRIBUTOR] IN (SELECT * FROM @ALLOWED_DISTRIBUTOR))
AND [ID_LOCATION_STATE_TYPE] IN (1,2,4,5,6)
AND [ID_LOCATION_TYPE] = 1

INSERT INTO @METERS ([ID_METER], [ID_LOCATION])
SELECT [ID_METER], [ID_LOCATION] FROM [LOCATION_EQUIPMENT] WHERE [ID_LOCATION] IN (SELECT [ID_LOCATION] FROM @RETVAL) AND END_TIME IS NULL AND [ID_METER] IS NOT NULL
    AND ((SELECT COUNT(*) FROM @ALLOWED_METER) = 0 OR [ID_METER] IN (SELECT * FROM @ALLOWED_METER))

DECLARE @METER_WITH_PROBLEM TYPE_BIGINT_VALUE
INSERT INTO @METER_WITH_PROBLEM ([VALUE])
SELECT DISTINCT D.[ID_METER] FROM [IMR_Suite_DW].[DBO].[DATA] D WHERE D.[ID_DATA_TYPE] = 2053 AND D.[ID_METER] IN (SELECT [ID_METER] FROM @METERS)
AND D.[ID_DATA] = (SELECT TOP 1 D2.[ID_DATA] FROM [IMR_Suite_DW].[DBO].[DATA] D2 WHERE D2.[ID_DATA_TYPE] = 2053 AND D2.[ID_METER] = D.[ID_METER] AND D.[VALUE] IS NOT NULL AND CAST(D.[VALUE] AS INT) = 1
ORDER BY D2.[TIME] DESC)

DELETE FROM @RETVAL
WHERE [ID_LOCATION] NOT IN (SELECT [ID_LOCATION] FROM @METERS WHERE ID_METER IN (SELECT [VALUE] FROM @METER_WITH_PROBLEM))

DECLARE @PROBLEMS TABLE ([ID_DATA] BIGINT, [ID_METER] BIGINT, [ID_LOCATION] BIGINT, [PROBLEM] INT)
INSERT INTO @PROBLEMS ([ID_DATA], [ID_METER], [ID_LOCATION], [PROBLEM])
SELECT D.[ID_DATA], D.[ID_METER], D.[ID_LOCATION], CAST(D.[VALUE] AS INT) FROM [IMR_Suite_DW].[DBO].[DATA] D
WHERE D.[ID_LOCATION] IS NOT NULL AND D.[ID_LOCATION] IN (SELECT [ID_LOCATION] FROM @METERS WHERE ID_METER IN(SELECT [VALUE] FROM @METER_WITH_PROBLEM)) AND D.[ID_DATA_TYPE] = 101565
AND D.[ID_DATA] = (SELECT TOP 1 D2.[ID_DATA] FROM [IMR_Suite_DW].[DBO].[DATA] D2  WHERE D2.[ID_DATA_TYPE] = 101565 AND D2.[INDEX_NBR] = D.[INDEX_NBR]
AND D2.[ID_LOCATION] = D.[ID_LOCATION] AND D.[VALUE] IS NOT NULL
AND ((SELECT COUNT(*) FROM @ALLOWED_METER) = 0 OR [ID_METER] IN (SELECT * FROM @ALLOWED_METER)) -- @ALLOWED_METER nie było uwzględniane w starym portalu
ORDER BY D2.[TIME] DESC)

DECLARE @EXCEPTION_LIST_TMP TABLE (ID_LOCATION BIGINT, [VALUE] NVARCHAR(MAX))
INSERT INTO @EXCEPTION_LIST_TMP (ID_LOCATION, [VALUE])
SELECT P2.ID_LOCATION,
    abc = STUFF(
                 (SELECT ', ' +
                 CASE
                    WHEN [PROBLEM] = 46 THEN 'ND1'
                    WHEN [PROBLEM] = 47 THEN 'ND2'
                    WHEN [PROBLEM] = 48 THEN 'E1'
                    WHEN [PROBLEM] = 49 THEN 'E2'
                    WHEN [PROBLEM] = 50 THEN 'E3'
                    WHEN [PROBLEM] = 51 THEN 'E4'
                    WHEN [PROBLEM] = 52 THEN 'E5a'
                    WHEN [PROBLEM] = 53 THEN 'E5b'
                    WHEN [PROBLEM] = 54 THEN 'E5c'
                    WHEN [PROBLEM] = 55 THEN 'E6b'
                    WHEN [PROBLEM] = 56 THEN 'E6a'
                    WHEN [PROBLEM] = 57 THEN 'E7a'
                    WHEN [PROBLEM] = 58 THEN 'E7b'
                    WHEN [PROBLEM] = 59 THEN 'E8'
                    WHEN [PROBLEM] = 60 THEN 'E9'
                end
                FROM @PROBLEMS P1 WHERE P1.ID_LOCATION = P2.ID_LOCATION FOR XML PATH ('')), 1, 1, ''
               )
FROM @PROBLEMS P2 GROUP BY P2.ID_LOCATION

UPDATE
    @RETVAL
SET
    EXCEPTION_LIST = T.[VALUE]
FROM
    @RETVAL L
    INNER JOIN @EXCEPTION_LIST_TMP T
        ON L.ID_LOCATION = T.ID_LOCATION

DECLARE @TOOLIP_TMP TABLE (ID_LOCATION BIGINT, [VALUE] NVARCHAR(MAX))
INSERT INTO @TOOLIP_TMP (ID_LOCATION, [VALUE])
SELECT P2.ID_LOCATION,
    abc = STUFF(
                 (SELECT '</BR>' +
                 CASE
                    WHEN [PROBLEM] = 46 THEN @ND1
                    WHEN [PROBLEM] = 47 THEN @ND2
                    WHEN [PROBLEM] = 48 THEN @E1
                    WHEN [PROBLEM] = 49 THEN @E2
                    WHEN [PROBLEM] = 50 THEN @E3
                    WHEN [PROBLEM] = 51 THEN @E4
                    WHEN [PROBLEM] = 52 THEN @E5a
                    WHEN [PROBLEM] = 53 THEN @E5b
                    WHEN [PROBLEM] = 54 THEN @E5c
                    WHEN [PROBLEM] = 55 THEN @E6b
                    WHEN [PROBLEM] = 56 THEN @E6a
                    WHEN [PROBLEM] = 57 THEN @E7a
                    WHEN [PROBLEM] = 58 THEN @E7b
                    WHEN [PROBLEM] = 59 THEN @E8
                    WHEN [PROBLEM] = 60 THEN @E9
                end
                FROM @PROBLEMS P1 WHERE P1.ID_LOCATION = P2.ID_LOCATION FOR XML PATH ('')), 1, 1, ''
               )
FROM @PROBLEMS P2 GROUP BY P2.ID_LOCATION

UPDATE
    @RETVAL
SET
    ROW_TOOLTIP = REPLACE(REPLACE(REPLACE(T.[VALUE],'&lt;','<'),'&gt;','>'),'lt;','<')
FROM
    @RETVAL L
    INNER JOIN @TOOLIP_TMP T
        ON L.ID_LOCATION = T.ID_LOCATION

UPDATE @RETVAL SET [EXCEPTION_LIST] =
    CASE
        WHEN LEN([EXCEPTION_LIST]) > 0 THEN RIGHT([EXCEPTION_LIST], LEN([EXCEPTION_LIST]) - 1)
    END
WHERE [EXCEPTION_LIST] IS NOT NULL

UPDATE @RETVAL SET
    [ROW_TOOLTIP] = '<p align="left">' +
    CASE
        WHEN LEN([ROW_TOOLTIP]) > 4 THEN RIGHT([ROW_TOOLTIP], LEN([ROW_TOOLTIP]) - 5)
    END + '</p>'
WHERE [ROW_TOOLTIP] IS NOT NULL

-- LOCATION_NAME
UPDATE @RETVAL SET [LOCATION_NAME] = D.[VALUE] FROM [DATA] D INNER JOIN @RETVAL L ON D.[ID_LOCATION] = L.[ID_LOCATION] WHERE D.[ID_DATA_TYPE] = 1
-- CITY
UPDATE @RETVAL SET [CITY] = D.[VALUE] FROM [DATA] D INNER JOIN @RETVAL L ON D.[ID_LOCATION] = L.[ID_LOCATION] WHERE D.[ID_DATA_TYPE] = 2
-- ADDRESS
UPDATE @RETVAL SET [ADDRESS] = D.[VALUE] FROM [DATA] D INNER JOIN @RETVAL L ON D.[ID_LOCATION] = L.[ID_LOCATION] WHERE D.[ID_DATA_TYPE] = 3
-- ZIP
UPDATE @RETVAL SET [POST_CODE] = D.[VALUE] FROM [DATA] D INNER JOIN @RETVAL L ON D.[ID_LOCATION] = L.[ID_LOCATION] WHERE D.[ID_DATA_TYPE] = 4
-- CID
UPDATE @RETVAL SET [CID] = D.[VALUE] FROM [DATA] D INNER JOIN @RETVAL L ON D.[ID_LOCATION] = L.[ID_LOCATION] WHERE D.[ID_DATA_TYPE] = 6
-- LOCATION_STATE
UPDATE @RETVAL SET [STATE] = (CASE WHEN DS.[DESCRIPTION] IS NOT NULL THEN DS.[DESCRIPTION] ELSE LST.[NAME] END) FROM [LOCATION] D INNER JOIN @RETVAL L ON D.[ID_LOCATION] = L.[ID_LOCATION]
    INNER JOIN [LOCATION_STATE_TYPE] LST ON LST.ID_LOCATION_STATE_TYPE = D.ID_LOCATION_STATE_TYPE
    INNER JOIN [DESCR] DS ON DS.ID_DESCR = LST.ID_DESCR WHERE DS.ID_LANGUAGE = @ID_LANGUAGE
-- DISTRIBUTOR
UPDATE @RETVAL SET [DISTRIBUTOR] = LST.[NAME] FROM [LOCATION] D INNER JOIN @RETVAL L ON D.[ID_LOCATION] = L.[ID_LOCATION]
    INNER JOIN [DISTRIBUTOR] LST ON LST.ID_DISTRIBUTOR = D.ID_DISTRIBUTOR


SELECT R.[LOCATION_NAME], R.[CID], R.[CITY], R.[ADDRESS], R.[POST_CODE], R.[STATE], r.[DISTRIBUTOR], R.[EXCEPTION_LIST], R.[ID_LOCATION], R.[ROW_TOOLTIP]
FROM @RETVAL R