USE [lcp_framework]
GO
/****** Object:  StoredProcedure [dbo].[get_grid_columns]    Script Date: 3/5/2025 4:54:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[get_grid_columns]
    @var_entity_name NVARCHAR(255),
    @var_company_id INT,
    @result NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @select_columns NVARCHAR(MAX) = '[]';
    DECLARE @field_name NVARCHAR(MAX), @display_name NVARCHAR(255), @order_no NVARCHAR(255),
            @is_searchable NVARCHAR(255), @is_grid_column NVARCHAR(255), @is_sortable NVARCHAR(255),
            @field_type_id NVARCHAR(255), @clause_type NVARCHAR(255);

    -- Cursor to iterate through query results
    DECLARE cur CURSOR FOR
    SELECT mgl.field_name, mgl.display_name, 
           mgl.order_no, mgl.is_searchable, mgl.is_grid_column, mgl.is_sortable,
           mgl.field_type_id, mgl.clause_type
    FROM master_entities mg
    JOIN master_entity_line_items mgl ON mg.id = mgl.master_grid_id
    WHERE mg.entity_name = @var_entity_name 
      AND mg.company_id = @var_company_id 
      AND mgl.status_id = 1
    ORDER BY mgl.order_no ASC;

    OPEN cur;
    FETCH NEXT FROM cur INTO 
        @field_name, @display_name, @order_no, 
        @is_searchable, @is_grid_column, @is_sortable, 
        @field_type_id, @clause_type;

    -- Process each row
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Convert boolean values to string 'true' or 'false'
        DECLARE @is_searchable_str NVARCHAR(5) = CASE WHEN @is_searchable = 1 THEN 'true' ELSE 'false' END;
        DECLARE @is_grid_column_str NVARCHAR(5) = CASE WHEN @is_grid_column = 1 THEN 'true' ELSE 'false' END;
        DECLARE @is_sortable_str NVARCHAR(5) = CASE WHEN @is_sortable = 1 THEN 'true' ELSE 'false' END;

        -- Build JSON array as a string
        DECLARE @json_array NVARCHAR(MAX) = 
            '["' + @field_name + '", "' + @display_name + '", "' + CAST(@order_no AS NVARCHAR) + '", "' 
            + @is_searchable_str + '", "' + @is_grid_column_str + '", "' + @is_sortable_str + '", "' 
            + @field_name + '", "' + CAST(@field_type_id AS NVARCHAR) + '", "' + @clause_type + '"]';

        -- Append JSON array to @select_columns
        SET @select_columns = JSON_MODIFY(@select_columns, 'append $', JSON_QUERY(@json_array));

        FETCH NEXT FROM cur INTO 
            @field_name, @display_name, @order_no, 
            @is_searchable, @is_grid_column, @is_sortable, 
            @field_type_id, @clause_type;
    END;

    CLOSE cur;
    DEALLOCATE cur;

    -- Return JSON result
    SET @result = @select_columns;
END;
