SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'WMAN_Opciones_GetInfo') )
DROP PROCEDURE WMAN_Opciones_GetInfo
GO

CREATE PROCEDURE WMAN_Opciones_GetInfo
	@id_app  INT
AS
BEGIN
	
	exec WMAN_OrdenarOpcionesArbol @id_app

	RETURN

END
GO

---------------------------------------------------------------------------------
---------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'WMAN_OrdenarOpcionesArbol') )
DROP PROCEDURE WMAN_OrdenarOpcionesArbol
GO

CREATE PROCEDURE WMAN_OrdenarOpcionesArbol
    @id_app varchar(10)
	as
	declare 
		@id_opc INT,
		@desc_opc VARCHAR(100),
		@va_log_sheet BIT,
		@id_parent INT,
		@historico BIT,
		@padre INT,
		@hijo INT,
		@hijo2 INT,
		@hijo3 INT
	
	CREATE TABLE #Arbol
	(
		id_opc INT, 
		id_app INT,
		desc_opc VARCHAR(100), 
		va_log_sheet BIT,
		id_parent INT,
		historico BIT
	)

    declare cursor_arbol cursor for

	SELECT id_opc, id_app, desc_opc, va_log_sheet,isnull(id_parent,0)id_parent, historico
	FROM Opciones
	WHERE id_app = @id_app AND id_parent IS NULL
	
	open cursor_arbol
	fetch cursor_arbol into @id_opc, @id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
	while @@FETCH_STATUS = 0
	begin
	
		insert into #Arbol(id_opc,id_app,desc_opc,va_log_sheet,id_parent,historico)            
		values(@id_opc,@id_app, @desc_opc,@va_log_sheet,@id_parent,@historico)
		SET @padre = @id_opc
       
       	declare cursor_hijos cursor for

		SELECT id_opc, id_app, desc_opc, va_log_sheet,isnull(id_parent,0)id_parent, historico
		FROM Opciones
		WHERE id_app = @id_app
		
		open cursor_hijos
		   fetch cursor_hijos into @id_opc, @id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
			while @@FETCH_STATUS = 0
			begin
				BEGIN
					IF(@id_parent=@padre)
					BEGIN
						insert into #Arbol(id_opc,id_app,desc_opc,va_log_sheet,id_parent,historico)            
						values(@id_opc,@id_app, '   '+@desc_opc,@va_log_sheet,@id_parent,@historico)
						SET @hijo = @id_opc
					END
					IF((SELECT COUNT(*) FROM Opciones WHERE id_parent = @hijo)>0)
					begin
					-----------------------------------------
						declare cursor_hijos2 cursor for

						SELECT id_opc, id_app, desc_opc, va_log_sheet,isnull(id_parent,0)id_parent, historico
						FROM Opciones
						WHERE id_parent = @hijo
						
						open cursor_hijos2
						   fetch cursor_hijos2 into @id_opc, @id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
							while @@FETCH_STATUS = 0
							begin
								BEGIN
									IF(@id_parent=@hijo AND (select count(*) from #Arbol where id_opc=@id_opc)= 0)
									BEGIN
										insert into #Arbol(id_opc,id_app,desc_opc,va_log_sheet,id_parent,historico)            
										values(@id_opc,@id_app, '      '+@desc_opc,@va_log_sheet,@id_parent,@historico)
										SET @hijo2 = @id_opc
									END
									IF((SELECT COUNT(*) FROM Opciones WHERE id_parent = @hijo2)>0)
										begin
											-----------------------------------------------------
											declare cursor_hijos3 cursor for

												SELECT id_opc, id_app, desc_opc, va_log_sheet,isnull(id_parent,0)id_parent, historico
												FROM Opciones
												WHERE id_parent = @hijo2--802005
												
												open cursor_hijos3
												   fetch cursor_hijos3 into @id_opc, @id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
													while @@FETCH_STATUS = 0
													begin
														BEGIN
															IF(@id_parent=@hijo2 AND (select count(*) from #Arbol where id_opc=@id_opc)= 0)
															BEGIN
																insert into #Arbol(id_opc,id_app,desc_opc,va_log_sheet,id_parent,historico)            
																values(@id_opc,@id_app, '         '+@desc_opc,@va_log_sheet,@id_parent,@historico)
																SET @hijo3 = @id_opc
															END
															IF((SELECT COUNT(*) FROM Opciones WHERE id_parent = @hijo3)>0)
																begin
																	-----------------------------------------------------
																	declare cursor_hijos4 cursor for

																		SELECT id_opc, id_app, desc_opc, va_log_sheet,isnull(id_parent,0)id_parent, historico
																		FROM Opciones
																		WHERE id_parent = @hijo3--802006
																		
																		open cursor_hijos4
																		   fetch cursor_hijos4 into @id_opc, @id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
																			while @@FETCH_STATUS = 0
																			begin
																				BEGIN
																					IF(@id_parent=@hijo3 AND (select count(*) from #Arbol where id_opc=@id_opc)= 0)
																					BEGIN
																						insert into #Arbol(id_opc,id_app,desc_opc,va_log_sheet,id_parent,historico)            
																						values(@id_opc,@id_app, '            '+@desc_opc,@va_log_sheet,@id_parent,@historico)
																						--SET @hijo3 = @id_opc
																					END
																				END
																			fetch cursor_hijos4 into @id_opc,@id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
																			end
																			close cursor_hijos4
																		  deallocate cursor_hijos4
																	-----------------------------------------------------
																END
														END
													fetch cursor_hijos3 into @id_opc,@id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
													end
													close cursor_hijos3
												  deallocate cursor_hijos3
											-----------------------------------------------------
										END
								END
							fetch cursor_hijos2 into @id_opc,@id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
							end
							close cursor_hijos2
						  deallocate cursor_hijos2
					------------------------------------------
					end
				END
			fetch cursor_hijos into @id_opc,@id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
			end
			close cursor_hijos
          deallocate cursor_hijos

	fetch cursor_arbol into @id_opc,@id_app,@desc_opc,@va_log_sheet,@id_parent,@historico
	end
    select * from #Arbol

	close cursor_arbol
          deallocate cursor_arbol
	GO
     
