SQL033.sql

/*
	PLT060 - Caderno de Chamada preenchido
	Create By Bitts
	(23/06/2017)
	
*/


DECLARE @IDTURMADISC VARCHAR(5) = '4324';

DECLARE @NUM_PAGINA AS INT = 2;
DECLARE @TAM_PAGINA AS INT = 10;

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

DECLARE SCURSOR CURSOR FAST_FORWARD READ_ONLY FOR
WITH 
[CALENDARIO AULAS] AS (
	SELECT * FROM (
		SELECT 
			A.CODCOLIGADA, A.CODFILIAL, A.IDPERLET, A.IDTURMADISC, A.CODDISC, B.AULA, RTRIM(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 
		) AS A 
	WHERE AULA BETWEEN ((@NUM_PAGINA-1) * @TAM_PAGINA+1) AND (@NUM_PAGINA * @TAM_PAGINA)
),
[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 B.RA = D.RA
)


SELECT ' [' + 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
GROUP BY DATA, AULA 
ORDER BY AULA 

SET @QUERY = 'SELECT ROW_NUMBER() OVER (ORDER BY D.NUMDIARIO ASC) AS ROWS, A.CODCOLIGADA, A.IDTURMADISC, A.IDPERLET, A.CODDISC, D.NUMDIARIO, D.RA, H.NOME, E.CODSTATUS, E.DESCRICAO AS [STATUS MATRICULA] ';

OPEN SCURSOR

FETCH NEXT FROM SCURSOR INTO 
	@SQL
    WHILE @@FETCH_STATUS = 0 
	BEGIN
		SET @QUERY = @QUERY + (CASE WHEN @QUERY = 'SELECT ' THEN '' ELSE ', ' END) + @SQL
		FETCH NEXT FROM SCURSOR INTO 
        @SQL          
    END

CLOSE SCURSOR;
DEALLOCATE SCURSOR;

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
			G.CODCOLIGADA = A.CODCOLIGADA
			AND G.RA = D.RA 
		LEFT JOIN PPESSOA AS H (NOLOCK) ON
			H.CODIGO = G.CODPESSOA
			
WHERE 
	A.IDTURMADISC = ' + @IDTURMADISC + ' GROUP BY A.CODCOLIGADA, A.IDTURMADISC, A.IDPERLET, A.CODDISC, D.NUMDIARIO, D.RA, H.NOME, E.CODSTATUS, E.DESCRICAO
ORDER BY 
	D.NUMDIARIO	ASC
';

EXEC(@QUERY);