IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CPJ_Menu') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.CPJ_Menu 
END
GO

CREATE PROCEDURE dbo.CPJ_Menu @pJson VARCHAR(8000)
AS
DECLARE @pId_Menu			VARCHAR(20)
DECLARE @pId_User			VARCHAR(20)
DECLARE @db					VARCHAR(100)

SET @pId_Menu				= ISNULL(CONVERT(VARCHAR(20), dbo.CF_JSON_VALUE(@pJson,'Id_Menu')), '0')
SET @pId_User				= ISNULL(CONVERT(VARCHAR(20), dbo.CF_JSON_VALUE(@pJson,'Id_User')), '0')
SET @db						= ISNULL(CONVERT(VARCHAR(100), dbo.CF_JSON_VALUE(@pJson,'db')), '')

DECLARE @sql VARCHAR(8000)

CREATE TABLE #Menu (
	Id_Menu INT,
	Descripcion_Ori VARCHAR(200), 
	Descripcion VARCHAR(200), 
	Tipo CHAR(1), 
	Funcion VARCHAR(200),
	Ubicacion VARCHAR(1000),
	Icono VARCHAR(254),
	Dll VARCHAR(200),
	DLL_Proc VARCHAR(200),
	Orden INT ,
	Nivel INT)

SET @sql = 'INSERT INTO #Menu
	SELECT  M.Id_Menu,M.Descripcion_Ori, M.Descripcion, M.Tipo, M.Funcion,M.Ubicacion,M.Icono,M.Dll,M.DLL_Proc,O.Orden,1 AS Nivel
	
                                 FROM '+ @db +'.dbo.Seg_Menu M,'+ @db +'.dbo.Seg_Menu_Mod O
                                WHERE M.Id_Menu = O.Id_Menu
                                 AND O.Id_Modulo = 1
                                 AND O.Id_Menu_Padre = ' + @pId_Menu + '
			                     AND ((dbo.CF_Seg_User_Validar(' + @pId_User + ', M.Equate_Door)=1 ) or M.Tipo=''G'') 
                                 ORDER BY O.Orden'
EXEC (@sql)

SET @sql = 'INSERT INTO #Menu
	SELECT  M.Id_Menu,M.Descripcion_Ori, M.Descripcion, M.Tipo, M.Funcion,M.Ubicacion,M.Icono,M.Dll,M.DLL_Proc,O.Orden,2

                                 FROM '+ @db +'.dbo.Seg_Menu M,'+ @db +'.dbo.Seg_Menu_Mod O
                                WHERE M.Id_Menu = O.Id_Menu
                                 AND O.Id_Modulo = 1
                                 AND O.Id_Menu_Padre IN (SELECT Id_Menu FROM #Menu WHERE Tipo =''G'' AND Nivel = 1)
			                     AND ((dbo.CF_Seg_User_Validar(' + @pId_User + ', M.Equate_Door)=1 ) or M.Tipo=''G'') 
                                 ORDER BY O.Orden'

EXEC (@sql)

SET @sql = 'INSERT INTO #Menu
	SELECT  M.Id_Menu,M.Descripcion_Ori, M.Descripcion, M.Tipo, M.Funcion,M.Ubicacion,M.Icono,M.Dll,M.DLL_Proc,O.Orden,3

                                 FROM '+ @db +'.dbo.Seg_Menu M,'+ @db +'.dbo.Seg_Menu_Mod O
                                WHERE M.Id_Menu = O.Id_Menu
                                 AND O.Id_Modulo = 1
                                 AND O.Id_Menu_Padre IN (SELECT Id_Menu FROM #Menu WHERE Tipo =''G'' AND Nivel = 2)
			                     AND ((dbo.CF_Seg_User_Validar(' + @pId_User + ', M.Equate_Door)=1 ) or M.Tipo=''G'') 
                                 ORDER BY O.Orden'
EXEC (@sql)

SELECT * FROM #Menu ORDER BY Orden
DROP TABLE #Menu

/*

EXEC CPJ_Menu '{"Id_Menu":"39","Id_User":"-1","db":"clever"}'

*/
