-- =============================================
-- Author:      Gartle LLC
-- Release:     10.0, 2022-07-05
-- Description: The procedure selects data from s17.requests
-- =============================================

CREATE PROCEDURE [s17].[usp_request]
    @category_id int = NULL
    , @time_id int = NULL
    , @entity_id int = NULL
    , @rows int = NULL
AS
BEGIN

SET NOCOUNT ON

IF @rows IS NULL SET @rows = 5

DECLARE @row_count int = COALESCE((
    SELECT
        MAX(t.row_index) AS row_index
    FROM
        s17.requests t
    WHERE
        t.entity_id = @entity_id
        AND t.category_id = @category_id
        AND t.time_id = @time_id
        AND t.is_empty = 0
    ), 0)

IF @row_count < @rows SET @row_count = @rows

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),                                              -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2)       -- 10*100

SELECT
    n.row_index
    , t.row_format
    , t.comment
    , t.account_id
    , t.subaccount_id
    , t.product_id
    , t.region_id
    , '=[@01]+[@02]+[@03]+[@04]+[@05]+[@06]+[@07]+[@08]+[@09]+[@10]+[@11]+[@12]' AS total
    , t.[01]
    , t.[02]
    , t.[03]
    , t.[04]
    , t.[05]
    , t.[06]
    , t.[07]
    , t.[08]
    , t.[09]
    , t.[10]
    , t.[11]
    , t.[12]
    , t.row_formulas
    , t.row_comments
FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY n) AS row_index FROM e3) n
    LEFT OUTER JOIN s17.requests t ON
        t.row_index = n.row_index
        AND t.entity_id = @entity_id
        AND t.category_id = @category_id
        AND t.time_id = @time_id
WHERE
    n.row_index BETWEEN 1 AND @row_count
ORDER BY
    n.row_index

END