USE [WebAJK]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Last Modified: Pribadi Pamungkas, 2013-03-15 14:59
CREATE FUNCTION [dbo].[func_get_user_menu] (
  @pi_id_user smallint
)
RETURNS 
  @po_ret_table TABLE (
    id_menu_group smallint,
    id_menu smallint,
    induk smallint,
    tipe tinyint,
    judul varchar(60),
    [file] varchar(80),
    kelompok smallint,
    urutan int,
    garis int
  )
AS
BEGIN

  DECLARE
    @p_id_menu_group smallint

  SELECT @p_id_menu_group = id_menu_group
  FROM web_user_menu
  WHERE
    (id_user = @pi_id_user) AND
    (id_menu_group IS NOT NULL) AND
    (status = 'I')
    
  DECLARE
    @p_process_table TABLE (
      id_menu      smallint,
      induk        smallint,
      flag_process smallint
    )

  -- start: collect menu
  INSERT INTO @p_process_table (id_menu, induk, flag_process)
  SELECT
    mgp.id_menu,
    mi.induk,
    1
  FROM 
    web_user_menu um
    INNER JOIN
    web_menu_group_permission mgp
    ON um.id_menu_group = mgp.id_menu_group
    INNER JOIN
    web_menu_item mi
    ON mgp.id_menu = mi.id_menu
  WHERE
    ((um.id_menu_group IS NOT NULL) AND
    (um.id_user = @pi_id_user) AND
    (um.status = 'I') AND
    (mgp.status = 'I'))
  -- end: collect menu

  -- start: sertakan menu Home & Logout
  INSERT INTO @p_process_table (id_menu, induk, flag_process)
  SELECT
    id_menu,
    induk,
    1
  FROM 
    web_menu_item 
  WHERE
    (id_menu IN (1, 6))
  -- end: sertakan menu Home & Logout
  
  -- start: tambahkan untuk individual menu permission (diluar grup)
  -- end: tambahkan untuk individual menu permission (diluar grup)
    
  IF EXISTS (SELECT * FROM @p_process_table)
  BEGIN
    DECLARE
      @p_loop bit
      
    SET @p_loop = 1

    WHILE @p_loop = 1
    BEGIN
      INSERT INTO @p_process_table (id_menu, induk, flag_process)
      SELECT id_menu, induk, 0
      FROM web_menu_item
      WHERE 
        (id_menu IN (
          SELECT induk FROM @p_process_table 
          WHERE flag_process = 1
          )
        )
        
      IF (@@ROWCOUNT = 0) SET @p_loop = 0
      UPDATE @p_process_table SET flag_process = 2 WHERE flag_process = 1
      UPDATE @p_process_table SET flag_process = 1 WHERE flag_process = 0
    END -- END: WHILE     
  END
  
  INSERT INTO @po_ret_table (
    id_menu_group,
    id_menu,
    induk,
    tipe,
    judul,
    [file],
    kelompok,
    urutan,
    garis
  )
  SELECT 
    DISTINCT
    @p_id_menu_group AS id_menu_group, 
    p.id_menu, 
    p.induk,
    mi.tipe,
    mi.judul,
    mi.[file],
    mi.kelompok,
    mi.urutan,
    mi.garis
  FROM 
    @p_process_table p
    LEFT JOIN
    web_menu_item mi
    ON p.id_menu = mi.id_menu
  
  RETURN
END
