SQL037.sql

/*
	PLT66 - PROCEDURE PARA RETORNAR O CADERNO DE CHAMADAS PREENCHIDO
	Create By Bitts (01/11/2017)
	
	Situação: Como não é possível utilizar o pivot criei uma Procedure que retorno o ResultSet de um
	SQL que é montado em tempo de execução, ele consulta as data que estão dentro do intervalo definido
	e retorna todas as marcações e listagem de alunas da turma.
	Como não é possível utilizar Cursores dentro do RM a ideia foi criar uma procedure que retorna todo o resultado


	USO: exec CD_FREQ 4324,'15/02/2017','27/02/2017'
*/


USE [CorporeRM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [CD_FREQ]
	@IDTURMADISC VARCHAR(10),
	@DATA_I DATE,
	@DATA_F DATE
AS

DECLARE @QUERY VARCHAR(MAX) = '';
DECLARE @SQL VARCHAR(MAX);
DECLARE @DATAS VARCHAR(MAX);


DECLARE cursor_name CURSOR FAST_FORWARD READ_ONLY FOR
WITH 
[CALENDARIO AULAS] AS (
	SELECT 
		A.CODCOLIGADA, A.CODFILIAL, A.IDPERLET, A.IDTURMADISC, A.CODDISC, B.AULA, CONVERT(CHAR, B.DATA, 103) AS DATA, C.HORAINICIAL, C.HORAFINAL, B.CODHOR
	FROM 
		STURMADISC AS A (NOLOCK)
		LEFT JOIN SPLANOAULA AS B (NOLOCK) ON
			A.CODCOLIGADA = B.CODCOLIGADA 
			AND	A.CODFILIAL = B.CODFILIAL 
			AND A.IDTURMADISC = B.IDTURMADISC
		LEFT JOIN SHORARIO AS C (NOLOCK) ON
			B.CODCOLIGADA = C.CODCOLIGADA
			AND B.CODHOR = C.CODHOR
),
[FALTAS] AS (
	SELECT
		A.CODCOLIGADA, A.IDHORARIOTURMA, A.IDTURMADISC, A.RA, CONVERT(CHAR, A.DATA, 103) AS DATA, A.PRESENCA
	FROM 
		SFREQUENCIA AS A (NOLOCK)
),
[FALTAS TURMA] AS (
	SELECT 
		A.CODCOLIGADA, A.IDTURMADISC, A.IDPERLET, A.CODDISC, A.AULA, RTRIM(A.DATA) AS DATA, A.HORAINICIAL, A.HORAFINAL, B.RA, C.DESCRICAO AS [STATUS MATRICULA], D.PRESENCA
	FROM 
		[CALENDARIO AULAS] AS A (NOLOCK)
		LEFT JOIN SMATRICULA AS B (NOLOCK) ON
			A.CODCOLIGADA = B.CODCOLIGADA
			AND A.IDTURMADISC = B.IDTURMADISC
			AND B.IDPERLET = A.IDPERLET 
		LEFT JOIN SSTATUS AS C (NOLOCK) ON
			C.CODCOLIGADA = A.CODCOLIGADA
			AND C.CODSTATUS = B.CODSTATUS
		LEFT JOIN [FALTAS] AS D (NOLOCK) ON
			A.CODCOLIGADA = D.CODCOLIGADA
			AND A.IDTURMADISC = D.IDTURMADISC
			AND A.DATA = D.DATA
			AND B.RA = D.RA
)


SELECT COALESCE(' [' + DATA +'] = MAX(CASE WHEN B.DATAEFETIVA = '+ char(39) + DATA + char(39) +' AND F.PRESENCA = '+ char(39) +'A' + char(39)+' THEN F.PRESENCA ELSE NULL END)','') AS [DATAS] 
FROM [FALTAS TURMA] 
WHERE 
IDTURMADISC = @IDTURMADISC 
AND DATA BETWEEN @DATA_I AND @DATA_F
GROUP BY DATA, AULA ORDER BY AULA 

SET @QUERY = 'SELECT A.CODCOLIGADA, A.IDTURMADISC, A.IDPERLET, A.CODDISC, D.RA, H.NOME ';

OPEN cursor_name

FETCH NEXT FROM cursor_name INTO 
@SQL
    WHILE @@FETCH_STATUS = 0 BEGIN

		SET @QUERY = @QUERY + (CASE WHEN @QUERY = 'SELECT ' THEN '' ELSE ', ' END) + @SQL

	FETCH NEXT FROM cursor_name INTO 
        @SQL          
    END

CLOSE cursor_name;
DEALLOCATE cursor_name;

SET @QUERY = @QUERY + ' FROM 
	STURMADISC AS A (NOLOCK)
		LEFT JOIN SPLANOAULA AS B (NOLOCK) ON
			A.CODCOLIGADA = B.CODCOLIGADA 
			AND	A.CODFILIAL = B.CODFILIAL 
			AND A.IDTURMADISC = B.IDTURMADISC
		LEFT JOIN SHORARIO AS C (NOLOCK) ON
			B.CODCOLIGADA = C.CODCOLIGADA
			AND B.CODHOR = C.CODHOR
		LEFT JOIN SMATRICULA AS D (NOLOCK) ON
			A.CODCOLIGADA = D.CODCOLIGADA
			AND A.IDTURMADISC = D.IDTURMADISC
			AND D.IDPERLET = A.IDPERLET 
		LEFT JOIN SSTATUS AS E (NOLOCK) ON
			E.CODCOLIGADA = A.CODCOLIGADA
			AND E.CODSTATUS = D.CODSTATUS
		LEFT JOIN SFREQUENCIA AS F (NOLOCK) ON
			A.CODCOLIGADA = F.CODCOLIGADA
			AND A.IDTURMADISC = F.IDTURMADISC
			AND B.IDHORARIOTURMA = F.IDHORARIOTURMA
			AND B.DATA = F.DATA
			AND D.RA = F.RA		
		LEFT JOIN SALUNO AS G (NOLOCK) ON 
			D.RA = G.RA			
		LEFT JOIN PPESSOA AS H (NOLOCK) ON
			G.CODPESSOA = H.CODIGO
WHERE 
	A.IDTURMADISC = ' + @IDTURMADISC + ' GROUP BY A.CODCOLIGADA, A.IDTURMADISC, A.IDPERLET, A.CODDISC, D.RA, H.NOME' ;

EXEC(@QUERY);
RETURN
GO