-- RECUPERACAO DE AVALICAO 

SELECT CODPROVA NUMPROVA,DESCRICAO FROM SPROVAS P
INNER JOIN STURMADISC T ON T.CODCOLIGADA = P.CODCOLIGADA AND T.IDTURMADISC =P.IDTURMADISC
WHERE CODTURMA = '" . $turma . "' AND CODETAPA = " . $etapa . " 
AND CODDISC = '" . $disciplina . "'

-- RECUPERA CURSO 

SELECT DISTINCT dbo.tiraAcentos(C.NOME) NOME FROM SMATRICULA  SP
INNER JOIN STURMADISC ST ON ST.CODCOLIGADA = SP.CODCOLIGADA AND ST.IDTURMADISC = SP.IDTURMADISC
INNER JOIN SPROFESSORTURMA SU ON SU.CODCOLIGADA = ST.CODCOLIGADA AND SU.IDTURMADISC = ST.IDTURMADISC
INNER JOIN SPLETIVO P ON P.IDPERLET = ST.IDPERLET AND P.CODCOLIGADA = ST.CODCOLIGADA
INNER JOIN SHABILITACAOFILIAL SF ON SF.CODCOLIGADA = ST.CODCOLIGADA AND SF.IDHABILITACAOFILIAL = ST.IDHABILITACAOFILIAL
INNER JOIN SCURSO C ON C.CODCOLIGADA = SF.CODCOLIGADA AND C.CODCURSO = SF.CODCURSO
WHERE SP.CODSTATUS NOT IN (01,03,04,08,09) AND CODPERLET = CAST(YEAR(GETDATE())AS VARCHAR)+'/'+CASE WHEN MONTH (GETDATE()) > 7 THEN '2' ELSE '1' END
AND SU.CODPROF = ".$professor."
ORDER BY C.NOME

-- RECUPERA DISCIPLINAS -- 

SELECT DISTINCT D.CODDISC,dbo.tiraAcentos(D.NOME) AS MATERIA FROM STURMADISC ST
INNER JOIN SDISCIPLINA D ON D.CODCOLIGADA = ST.CODCOLIGADA AND D.CODDISC = ST.CODDISC
INNER JOIN SPROFESSORTURMA SU ON SU.CODCOLIGADA = ST.CODCOLIGADA AND SU.IDTURMADISC = ST.IDTURMADISC
INNER JOIN SHABILITACAOFILIAL SF ON SF.CODCOLIGADA = ST.CODCOLIGADA AND SF.IDHABILITACAOFILIAL = ST.IDHABILITACAOFILIAL
WHERE ST.CODTURMA = '" . $turma . "' AND SU.CODPROF = " . $professor . "

-- RECUPERA ETAPAS --

SELECT DISTINCT CODETAPA,M.DESCRICAO FROM SMODETAPAPLETIVO M
INNER JOIN SPLETIVO P ON P.IDPERLET = M.IDPERLET AND P.CODCOLIGADA = M.CODCOLIGADA
WHERE CODPERLET = CAST(YEAR(GETDATE())AS VARCHAR)+'/'+CASE WHEN MONTH (GETDATE()) > 6 THEN '2' ELSE '1' END
AND TIPOETAPA = 'N'

-- RECUPERA NOTA --

SELECT ALUNO.MATALUNO AS MATRICULA,dbo.tiraAcentos(ALUNO.NOME) AS NOME,ISNULL([NOTA_AVALIACAO],0) AS NOTA
FROM (SELECT CODTURMA AS CODTUR,CODDISC AS CODMAT,M.CODCOLIGADA,M.RA AS MATALUNO,P.NOME FROM SMATRICULA M
INNER JOIN STURMADISC D ON D.CODCOLIGADA = M.CODCOLIGADA AND D.IDTURMADISC = M.IDTURMADISC
INNER JOIN SALUNO A ON A.CODCOLIGADA = M.CODCOLIGADA AND A.RA = M.RA 
INNER JOIN PPESSOA P ON P.CODIGO = A.CODPESSOA
WHERE M.CODSTATUS in (02,05,12,16,17,24,25,29,30) AND CODTURMA = '" . $turma . "' AND CODDISC = '" . $disciplina . "') ALUNO
LEFT JOIN (SELECT CODTURMA AS CODTUR,CODDISC AS CODMAT,M.CODCOLIGADA,M.RA AS MATALUNO,NOTA AS [NOTA_AVALIACAO] FROM SNOTAS M 
INNER JOIN STURMADISC D ON D.CODCOLIGADA = M.CODCOLIGADA AND D.IDTURMADISC = M.IDTURMADISC
WHERE CODETAPA = " . $etapa . " AND CODTURMA = '" . $turma . "' AND CODDISC = '" . $disciplina . "' AND CODPROVA = " . $avaliacao . ") AS PROVA ON
PROVA.CODTUR = ALUNO.CODTUR AND
PROVA.CODMAT = ALUNO.CODMAT AND
PROVA.CODCOLIGADA = ALUNO.CODCOLIGADA AND
PROVA.MATALUNO = ALUNO.MATALUNO
order by ALUNO.NOME

-- RECUPERA TURMA --

SELECT DISTINCT ST.CODTURMA CODTUR FROM SMATRICULA  SP
INNER JOIN STURMADISC ST ON ST.CODCOLIGADA = SP.CODCOLIGADA AND ST.IDTURMADISC = SP.IDTURMADISC
INNER JOIN SPROFESSORTURMA SU ON SU.CODCOLIGADA = ST.CODCOLIGADA AND SU.IDTURMADISC = ST.IDTURMADISC
INNER JOIN SPLETIVO P ON P.IDPERLET = ST.IDPERLET AND P.CODCOLIGADA = ST.CODCOLIGADA
INNER JOIN SHABILITACAOFILIAL SF ON SF.CODCOLIGADA = ST.CODCOLIGADA AND SF.IDHABILITACAOFILIAL = ST.IDHABILITACAOFILIAL
INNER JOIN SCURSO C ON C.CODCOLIGADA = SF.CODCOLIGADA AND C.CODCURSO = SF.CODCURSO
WHERE SP.CODSTATUS NOT IN (01,03,04,08,09) AND CODPERLET = CAST(YEAR(GETDATE())AS VARCHAR)+'/'+CASE WHEN MONTH (GETDATE()) > 7 THEN '2' ELSE '1' END
AND DBO.TIRAACENTOS(C.NOME) = '".$curso."' AND SU.CODPROF = ".$professor."
ORDER BY ST.CODTURMA