SQL034.sql

/*
	PLT061 - Retorna SQL para o caderno de chamada
	Create By Bitts
	(06/07/2017)
	

	FINALIDADE: para relatorio de caderno de chamada
	UTILIZAÇÃO: 

		DECLARE @SQL VARCHAR(MAX) = DBO.SQL_CADERNO_CHAMADA ('2552', 2, 10);
		EXEC( @SQL )

		DBO.SQL_CADERNO_CHAMADA (IDTURMADISC, NUMERO DA PAGINA, TOTAL POR PAGINA);
 
*/


CREATE FUNCTION
	SQL_CADERNO_CHAMADA (
		@IDTURMADISC VARCHAR(10),
		@NUM_PAGINA INT,
		@TAM_PAGINA INT
	)
	RETURNS VARCHAR(MAX)  
 
	BEGIN 
	
		--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 
		[TURMA] AS (
			SELECT 
				A.CODCOLIGADA, A.CODFILIAL, H.NOME AS NOMEFILIAL, I.IMAGEM, N.APRESENTACAO,
				A.IDPERLET, P.CODPERLET, 
				L.CODCURSO,	L.NOME AS NOMECURSO, K.NOME AS NOMETURNO,
				A.IDHABILITACAOFILIAL, M.NOME AS NOMEHABILITACAO,		
				A.CODTURMA, B.CODPROF, D.NOME AS NOMEPROFESSOR, B.TIPOPROF,
				A.IDTURMADISC, A.CODDISC, F.NOME AS NOMEDISCIPLINA, [CH] = (CASE WHEN N.APRESENTACAO = 0 THEN O.CH ELSE F.CH END)
			FROM 
				STURMADISC AS A (NOLOCK)
				LEFT JOIN SPROFESSORTURMA AS B (NOLOCK) ON
					B.CODCOLIGADA = A.CODCOLIGADA
					AND B.IDTURMADISC = A.IDTURMADISC
				LEFT JOIN SPROFESSOR AS C (NOLOCK) ON
					C.CODCOLIGADA = A.CODCOLIGADA
					AND B.CODPROF = C.CODPROF
				LEFT JOIN PPESSOA AS D (NOLOCK) ON 
					D.CODIGO = C.CODPESSOA
				LEFT JOIN SDISCIPLINA AS F (NOLOCK) ON
					F.CODCOLIGADA = A.CODCOLIGADA
					AND F.CODDISC = A.CODDISC
				LEFT JOIN SPLETIVO AS G (NOLOCK) ON 
					G.CODCOLIGADA = A.CODCOLIGADA
					AND G.IDPERLET = A.IDPERLET
				LEFT JOIN GFILIAL AS H (NOLOCK) ON 
					H.CODCOLIGADA = A.CODCOLIGADA
					AND H.CODFILIAL = A.CODFILIAL
				LEFT JOIN GIMAGEM AS I (NOLOCK) ON 
					I.ID = H.IDIMAGEM
				LEFT JOIN SHABILITACAOFILIAL AS J (NOLOCK) ON 
					J.CODCOLIGADA = A.CODCOLIGADA
					AND J.IDHABILITACAOFILIAL = A.IDHABILITACAOFILIAL
				LEFT JOIN STURNO AS K (NOLOCK) ON 
					A.CODCOLIGADA = K.CODCOLIGADA
					AND J.CODTURNO = K.CODTURNO
				LEFT JOIN SCURSO AS L (NOLOCK) ON 
					L.CODCOLIGADA = K.CODCOLIGADA
					AND L.CODCURSO = J.CODCURSO
				LEFT JOIN SHABILITACAO AS M (NOLOCK) ON 
					M.CODCOLIGADA = A.CODCOLIGADA
					AND M.CODCURSO = J.CODCURSO
					AND M.CODHABILITACAO = J.CODHABILITACAO
				LEFT JOIN STIPOCURSO AS N (NOLOCK) ON 
					N.CODCOLIGADA  = A.CODCOLIGADA 
					AND N.CODTIPOCURSO = A.CODTIPOCURSO
				LEFT JOIN SDISCGRADE AS O (NOLOCK) ON
					O.CODCOLIGADA = A.CODCOLIGADA
					AND O.CODCURSO = J.CODCURSO
					AND O.CODHABILITACAO = J.CODHABILITACAO
					AND O.CODGRADE = J.CODGRADE
					AND O.CODDISC = A.CODDISC
				LEFT JOIN SPLETIVO AS P (NOLOCK) ON 
					P.CODCOLIGADA = A.CODCOLIGADA
					AND P.IDPERLET = A.IDPERLET
		),
		[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
		';

		RETURN  @QUERY
END