Case: A message protocol-independent web service (part 2 of 2)

1-- Parsing the applicationCode and productCode from the request path.
2DECLARE @searchIndex INT = PATINDEX('%/check/%', @request_path);
3DECLARE @remainder VARCHAR(200) = RIGHT(@request_path, LEN(@request_path) - @searchIndex - LEN('check/'));
4DECLARE @applicationCode name = SUBSTRING(@remainder, 0, CHARINDEX('/', @remainder));
5DECLARE @productCode name = SUBSTRING(@remainder, CHARINDEX('/', @remainder) + 1, LEN(@remainder) - CHARINDEX('/', @remainder));
6
7-- Declaring variables for the query string parameters.
8DECLARE @adminId name;
9DECLARE @customerNumber name;
10DECLARE @referenceDate name;
11
12-- Parsing the query string parameters into their corresponding variables
13SELECT @adminId        = t1.adminId
14     , @customerNumber = t1.customerNumber
15     , @referenceDate  = t1.referenceDate
16FROM [dbo].[get_query_string_param_values](@request_query_string)
17PIVOT
18(
19    MAX(value) FOR name IN ([adminId], [customerNumber], [referenceDate])
20) t1

1-- Fetching the license corresponding to the applicationCode, productCode and the provided filters
2-- as a JSON object and storing it in the response body.
3SET @response_body =
4(
5    SELECT TOP 1 l.license_id         AS 'coreData.licenseId'
6               , c.customer_number    AS 'coreData.customerNumber'
7               , l.admin_id           AS 'coreData.adminId'
8               , a.application_code   AS 'coreData.applicationCode'
9               , p.product_code       AS 'coreData.productCode'
10               , l.start_date         AS 'coreData.startDate'
11               , l.end_date           AS 'coreData.endDate'
12               , l.reference          AS 'coreData.reference'
13               , l.quantity           AS 'coreData.quantity'
14               , l.license_state      AS 'coreData.licenseState'
15               , l.mutation_date_time AS 'mutationDateTime'
16               , (
17                      SELECT 'License ' + l.license_id   AS 'relation' 
18                           , '/licenses/' + l.license_id AS 'href'
19                           , 'GET'                       AS 'action'
20                      FOR JSON PATH
21                 ) AS 'links'
22     FROM license l
23     JOIN customer c
24       ON l.customer_id = c.customer_id
25     JOIN application a
26       ON l.application_id = a.application_id
27     JOIN product p
28       ON a.application_id = p.application_id
29     WHERE a.application_code = @applicationCode
30       AND p.product_code     = @productCode
31       AND (@adminId        IS NULL OR l.admin_id        = @adminId)
32       AND (@customerNumber IS NULL OR c.customer_number = @customerNumber)
33       AND (@referenceDate  IS NULL OR l.reference       = @referenceDate)
34    FOR JSON PATH
35);
36
37IF @response_body IS NULL
38BEGIN
39    SET @response_status_code = 404;
40END
41ELSE
42BEGIN
43    SET @response_status_code = 200;
44END
45
46SET @response_headers = '{ "Content-Type": [ "application/json" ] }'

1DECLARE @customerNumber name;
2DECLARE @adminId name;
3DECLARE @applicationCode name;
4DECLARE @pageIndex int;
5DECLARE @pageSize int;
6
7-- Parsing the query string parameters into their corresponding variables
8SELECT @customerNumber   = t1.customerNumber
9        , @adminId          = t1.adminId
10        , @applicationCode  = t1.applicationCode
11        , @pageIndex        = COALESCE(t1.[pageNr], 1) -- default to 1
12        , @pageSize         = COALESCE(t1.[pageSize], 50) -- default to 50
13FROM [dbo].[get_query_string_param_values](@request_query_string)
14PIVOT
15(
16    MAX(value) FOR name IN ([customerNumber], [adminId], [applicationCode], [productCode], [mutationDateTime], [licenseState], [pageNr], [pageSize])
17) t1
18
19-- Fetching all applications results as a JSON array.
20DECLARE @applicationsResult VARCHAR(MAX);
21SET @applicationsResult =
22(
23    SELECT l.admin_id
24         , a.application_code
25         , l.start_date
26         , l.end_date
27         , (
28             SELECT 'Application ' + a.application_code   AS 'relation' 
29                  , '/applications/' + a.application_code AS 'href'
30                  , 'GET'                                 AS 'action'
31             FOR JSON PATH
32           ) AS 'links'
33      FROM application a
34      JOIN license l
35        ON a.application_id = l.application_id
36      JOIN customer c
37        ON l.customer_id = c.customer_id
38     WHERE l.license_state    = 'ACTIVE'
39       AND c.customer_number  = @customerNumber
40       AND l.admin_id         = @adminId
41       AND a.application_code = @applicationCode
42    ORDER BY a.application_code ASC
43    OFFSET (@pageIndex - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY
44    FOR JSON PATH
45);
46
47-- Determining the number of records given the current filters.
48DECLARE @nrOfRowsWithFilters INT;
49SET @nrOfRowsWithFilters =
50(
51    SELECT COUNT(*)
52      FROM application a
53      JOIN license l
54        ON a.application_id = l.application_id
55      JOIN customer c
56        ON l.customer_id = c.customer_id
57     WHERE l.license_state    = 'ACTIVE'
58       AND c.customer_number  = @customerNumber
59       AND l.admin_id         = @adminId
60       AND a.application_code = @applicationCode
61);
62
63-- Calculating what the last page index is given the number of records and page size.
64DECLARE @lastPage INT = CONVERT(INT, CEILING(CONVERT(decimal, @nrOfRowsWithFilters) / CONVERT(decimal, @pageSize)));
65
66-- Producing the pageLinks JSON array given the current page index.
67DECLARE @pageLinkResults VARCHAR(MAX);
68SET @pageLinkResults =
69(
70    SELECT pageLinks.relation, pageLinks.href, pageLinks.action
71    FROM
72    (
73        SELECT 0 as order_no
74             , 'first' AS relation
75             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, 1, @pageSize) AS href
76             , 'GET' AS action
77
78        UNION ALL
79
80        SELECT 1 as order_no
81             , 'previous' AS relation
82             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, @pageIndex - 1, @pageSize) AS href
83             , 'GET' AS action
84        WHERE @pageIndex > 1
85
86        UNION ALL
87
88        SELECT 2 as order_no
89             , 'self' AS relation
90             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, @pageIndex, @pageSize) AS href
91             , 'GET' AS action
92
93        UNION ALL
94
95        SELECT 3 as order_no
96             , 'next' AS relation
97             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, @pageIndex + 1, @pageSize) AS href
98             , 'GET' AS action
99        WHERE @pageIndex < @lastPage
100
101        UNION ALL
102
103        SELECT 4 as order_no
104             , 'last' AS relation
105             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, @lastPage, @pageSize) AS href
106             , 'GET' AS action
107    ) AS pageLinks
108    WHERE @lastPage != 0
109    ORDER BY pageLinks.order_no ASC
110    FOR JSON PATH
111)
112
113-- Creating the final JSON response, coalescing both JSON arrays to an empty JSON array if null
114SET @response_body = 
115(
116    SELECT JSON_QUERY(COALESCE(@applicationsResult, '[]')) AS accesses
117         , JSON_QUERY(COALESCE(@pageLinkResults, '[]')) AS pageLinks
118    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
119);
120
121SET @response_status_code = 200;
122SET @response_headers = '{ "Content-Type": [ "application/json" ] }';