¬¬

cross-tab.sql

SELECT
    fr.aluno ,
    (
        SELECT
            nad.nota
        FROM
            tb_avaliacao_disciplina av INNER JOIN tb_nota_avaliacao_disciplina nad
                ON nad.id_avaliacao_disciplina = av.id_avaliacao_disciplina INNER JOIN tb_sub_periodo_letivo spl1
                ON spl1.id_sub_periodo_letivo = av.id_sub_periodo_letivo
        WHERE
            av.id_disciplina_professor = dp.id_disciplina_professor
            AND nad.id_matricula_disciplina = fr.id_matricula_disciplina
        ORDER BY
            av.data_avaliacao DESC ,
            av.data_limite_lancamento DESC LIMIT 1
    ) nota_avaliacao ,
    fr.carga_horaria_disciplina AS carga_horaria ,
    (
        SELECT
            COUNT(dhs1.id_disciplina_horario_sala)
        FROM
            tb_disciplina_horario_sala dhs1
        WHERE
            dhs1.id_disciplina_professor = dp.id_disciplina_professor
            AND dhs1.status = 'ATIVO' /*INTERVALO DE DATA REFERENTE AO PROFESSOR SUBSTITURO*/
            /*INTERVALO DE DATA REFERENTE AO PERIODO LETIVO*/
            /*INTERVALO DE DATA REFERENTE AO SUB PERIODO LETIVO*/
            /*INTERVALO DE DATA REFERENTE HA AVALIACAO*/
    ) aulas_ministradas ,
    (
        SELECT
            COUNT(
                DISTINCT ff.id_frequencia_falta
            )
        FROM
            tb_frequencia_falta ff INNER JOIN tb_disciplina_horario_sala dhs1
                ON dhs1.id_disciplina_horario_sala = ff.id_disciplina_horario_sala
            AND dhs1.status = 'ATIVO' INNER JOIN tb_matricula_disciplina md
                ON md.id_matricula_disciplina = ff.id_matricula_disciplina INNER JOIN tb_matricula m
                ON m.id_matricula = md.id_matricula
            AND m.status = 'ATIVA' LEFT JOIN tb_aluno_concessao_beneficio acb
                ON acb.id_aluno = m.id_aluno
            AND DATE(dhs1.data_fixa) NOT BETWEEN acb.data_inicio AND acb.data_fim
        WHERE
            ff.id_disciplina_professor = dp.id_disciplina_professor
            AND ff.id_matricula_disciplina = fr.id_matricula_disciplina /*INTERVALO DE DATA REFERENTE AO PROFESSOR SUBSTITURO*/
            /*INTERVALO DE DATA REFERENTE AO PERIODO LETIVO*/
            /*INTERVALO DE DATA REFERENTE AO SUB PERIODO LETIVO*/
            /*INTERVALO DE DATA REFERENTE HA AVALIACAO*/
    ) AS nr_faltas ,
    fr. *
FROM
    tb_disciplina_professor dp INNER JOIN(
        SELECT
            dp.id_disciplina_professor ,
            dhs.id_disciplina_horario_sala ,
            dhs.dia_da_semana ,
            dhs.hora_inicio ,
            dhs.hora_termino ,
            dhs.data_fixa ,
            ava.id_avaliacao_disciplina ,
            ava.avaliacao ,
            ava.tipo_avaliacao ,
            ava.identificacao ,
            ava.data_avaliacao_inicio ,
            ava.data_avaliacao_fim ,
            IF(
                t.data_fechamento_diario_classe = NULL ,
                0 ,
                1
            ) apresentar_numero_diario_classe ,
            m.numero_diario_classe ,
            a.id_aluno ,
            a.ra ,
            pa.nome aluno ,
            m.id_matricula ,
            m.status status_matricula ,
            md.id_matricula_disciplina ,
            nad.nota ,
            md.media_final ,
            md.frequencia ,
            /*VALIDA A SITUACAO DO HORARIO AULA
					 * -2 - BENEFICIO
					 * -1 - CANCELADO/TRANCADO
					 *  0 - PRESENCA
					 * 	1 - FALTA*/
            IF(
                md.status <> 'TRANCADO'
                AND md.status <> 'CANCELADO' ,
                IF(
                    (
                        SELECT
                            COUNT(*)
                        FROM
                            tb_aluno_concessao_beneficio acb
                        WHERE
                            acb.id_aluno = a.id_aluno
                            AND DATE(dhs.data_fixa) BETWEEN acb.data_inicio AND acb.data_fim
                    ) > 0 ,
                    - 2 ,
                    IF(
                        DATE(ff.data) = DATE(dhs.data_fixa) ,
                        1 ,
                        0
                    )
                ) ,
                IF(
                    DATE(m.data_trancamento) < dhs.data_fixa || DATE(md.data_modificacao) < dhs.data_fixa ,
                    - 1 ,
                    IF(
                        (
                            SELECT
                                COUNT(*)
                            FROM
                                tb_aluno_concessao_beneficio acb
                            WHERE
                                acb.id_aluno = a.id_aluno
                                AND DATE(dhs.data_fixa) BETWEEN acb.data_inicio AND acb.data_fim
                        ) > 0 ,
                        - 2 ,
                        IF(
                            DATE(ff.data) = DATE(dhs.data_fixa) ,
                            1 ,
                            0
                        )
                    )
                )
            ) falta ,
            d.id_disciplina ,
            d.descricao disciplina ,
            d.carga_horaria carga_horaria_disciplina ,
            t.id_turma ,
            t.nome turma ,
            t.turno ,
            c.id_curso ,
            c.nome curso ,
            cb.nome_para_impressao AS curso_nome_impressao ,
            pr.nome periodo ,
            pl.descricao periodo_letivo ,
            pl.data_inicio periodo_inicio ,
            pl.data_termino periodo_fim ,
            spl.id_sub_periodo_letivo ,
            spl.descricao bimestre ,
            spl.data_inicio sub_periodo_inicio ,
            spl.data_fim sub_periodo_fim ,
            prof.id_professor ,
            prof.professor ,
            prof.data_inicio professor_data_inicio ,
            prof.data_fim professor_data_fim ,
            IFNULL(
                (
                    SELECT
                        SUM(horas)
                    FROM
                        tb_atividade_extra_classe
                    WHERE
                        id_disciplina_professor = dp.id_disciplina_professor
                        AND contabilizar = 0
                ) ,
                0
            ) atividade_extra_classe ,
            conf.diario_frequencia_caracter_presenca ,
            conf.diario_frequencia_caracter_falta ,
            conf.diario_frequencia_caracter_concessao_beneficio ,
            o. *
        FROM
            tb_disciplina_professor dp INNER JOIN tb_disciplina_horario_sala dhs
                ON dhs.id_disciplina_professor = dp.id_disciplina_professor
            AND dhs.status = 'ATIVO' INNER JOIN tb_disciplina d
                ON d.id_disciplina = dp.id_disciplina INNER JOIN tb_turma t
                ON t.id_turma = dp.id_turma INNER JOIN tb_periodo_letivo pl
                ON pl.id_periodo_letivo = t.id_periodo_letivo
            AND pl.data_inicio <= dhs.data_fixa
            AND pl.data_termino >= dhs.data_fixa INNER JOIN tb_sub_periodo_letivo spl
                ON spl.id_periodo_letivo = pl.id_periodo_letivo
            AND spl.data_inicio <= dhs.data_fixa
            AND spl.data_fim >= dhs.data_fixa INNER JOIN tb_periodo_item pr
                ON pr.id_periodo_item = t.id_periodo_item INNER JOIN tb_curso c
                ON c.id_curso = t.id_curso INNER JOIN tb_curso_base cb
                ON cb.id_curso_base = c.id_curso_base /*RECUPERA OS PROFESSORES / SUBSTITUTOS
						 * 1 - PROFESSOR QUE INICIOU O PERIODO ATE A DATA EM QUE FOI SUBSTITUIDO
						 * 2 - PROFESSOR QUE SUBSTITUI UM PROFESSOR E POSTERIORMENTE FOI SUBSTITUIDO
						 * 3 - PROFESSOR QUE SUBSTITUI UM PROFESSOR E TERMINOU O PERIODO LETIVO
						 * 4 - PROFESSOR QUE LECIONOU SEM SUBSTITUICAO*/
            INNER JOIN(
                (
                    SELECT
                        p.id_professor ,
                        pfp.nome professor ,
                        TIMESTAMP(
                            CONCAT( pl.data_inicio ,' 00:00:00' )
                        ) data_inicio ,
                        dphs.data data_fim ,
                        dp.id_disciplina_professor
                    FROM
                        tb_disciplina_professor dp INNER JOIN tb_disciplina_professor_historico dphs
                            ON dphs.id_disciplina_professor = dp.id_disciplina_professor INNER JOIN tb_disciplina_professor_historico dphe
                            ON dphe.id_disciplina_professor = dp.id_disciplina_professor INNER JOIN tb_professor p
                            ON p.id_professor = dphs.id_professor_substituido INNER JOIN tb_funcionario f
                            ON p.id_funcionario = f.id_funcionario INNER JOIN tb_perfil pfp
                            ON pfp.id_perfil = f.id_perfil INNER JOIN tb_turma t
                            ON t.id_turma = dp.id_turma INNER JOIN tb_periodo_letivo pl
                            ON pl.id_periodo_letivo = t.id_periodo_letivo
                    WHERE
                        dp.id_disciplina_professor = ''
                        AND dphs.id_professor_substituido NOT IN(
                            SELECT
                                id_professor_substituto
                            FROM
                                tb_disciplina_professor_historico
                            WHERE
                                id_disciplina_professor = dp.id_disciplina_professor
                        )
                    ORDER BY
                        data_inicio DESC
                )
        UNION(
                SELECT
                    p.id_professor ,
                    pfp.nome professor ,
                    dphe.data data_inicio ,
                    dphs.data data_fim ,
                    dp.id_disciplina_professor
                FROM
                    tb_disciplina_professor dp INNER JOIN tb_disciplina_professor_historico dphs
                        ON dphs.id_disciplina_professor = dp.id_disciplina_professor INNER JOIN tb_disciplina_professor_historico dphe
                        ON dphe.id_disciplina_professor = dp.id_disciplina_professor INNER JOIN tb_professor p
                        ON p.id_professor = dphe.id_professor_substituto INNER JOIN tb_funcionario f
                        ON p.id_funcionario = f.id_funcionario INNER JOIN tb_perfil pfp
                        ON pfp.id_perfil = f.id_perfil
                WHERE
                    dp.id_disciplina_professor = ''
                    AND dphe.id_professor_substituto = dphs.id_professor_substituido
            )
    UNION(
            SELECT
                p.id_professor ,
                pfp.nome professor ,
                dphe.data data_inicio ,
                TIMESTAMP(
                    CONCAT( pl.data_termino ,' 23:59:59' )
                ) data_fim ,
                dp.id_disciplina_professor
            FROM
                tb_disciplina_professor dp INNER JOIN tb_disciplina_professor_historico dphs
                    ON dphs.id_disciplina_professor = dp.id_disciplina_professor INNER JOIN tb_disciplina_professor_historico dphe
                    ON dphe.id_disciplina_professor = dp.id_disciplina_professor INNER JOIN tb_professor p
                    ON p.id_professor = dphe.id_professor_substituto INNER JOIN tb_funcionario f
                    ON p.id_funcionario = f.id_funcionario INNER JOIN tb_perfil pfp
                    ON pfp.id_perfil = f.id_perfil INNER JOIN tb_turma t
                    ON t.id_turma = dp.id_turma INNER JOIN tb_periodo_letivo pl
                    ON pl.id_periodo_letivo = t.id_periodo_letivo
            WHERE
                dp.id_disciplina_professor = ''
                AND dphs.id_professor_substituto NOT IN(
                    SELECT
                        id_professor_substituido
                    FROM
                        tb_disciplina_professor_historico
                    WHERE
                        id_disciplina_professor = dp.id_disciplina_professor
                )
            ORDER BY
                data_inicio DESC
        )
UNION(
        SELECT
            p.id_professor ,
            pfp.nome professor ,
            TIMESTAMP(
                CONCAT( pl.data_inicio ,' 00:00:00' )
            ) data_inicio ,
            TIMESTAMP(
                CONCAT( pl.data_termino ,' 23:59:59' )
            ) data_fim ,
            dp.id_disciplina_professor
        FROM
            tb_disciplina_professor dp INNER JOIN tb_professor p
                ON p.id_professor = dp.id_professor INNER JOIN tb_funcionario f
                ON p.id_funcionario = f.id_funcionario INNER JOIN tb_perfil pfp
                ON pfp.id_perfil = f.id_perfil INNER JOIN tb_turma t
                ON t.id_turma = dp.id_turma INNER JOIN tb_periodo_letivo pl
                ON pl.id_periodo_letivo = t.id_periodo_letivo
        WHERE
            dp.id_disciplina_professor = ''
            AND dp.id_professor NOT IN(
                SELECT
                    id_professor_substituido
                FROM
                    tb_disciplina_professor_historico
                WHERE
                    id_disciplina_professor = dp.id_disciplina_professor
            )
            AND dp.id_professor NOT IN(
                SELECT
                    id_professor_substituto
                FROM
                    tb_disciplina_professor_historico
                WHERE
                    id_disciplina_professor = dp.id_disciplina_professor
            )
    )
            ) prof
                ON prof.id_disciplina_professor = dp.id_disciplina_professor
            AND prof.data_inicio <= TIMESTAMP(
                CONCAT( dhs.data_fixa ,' ' ,dhs.hora_inicio )
            )
            AND prof.data_fim >= TIMESTAMP(
                CONCAT( dhs.data_fixa ,' ' ,dhs.hora_termino )
            ) /*FIM PROFESSOR*/
            /*RECUPERA AS AVALIACOES*/
            LEFT JOIN(
                SELECT
                    dp.id_disciplina_professor ,
                    ad.id_avaliacao_disciplina ,
                    ad.descricao avaliacao ,
                    ad.identificacao ,
                    tad.tipo tipo_avaliacao ,
                    ad.id_sub_periodo_letivo ,
                    COALESCE(
                        (
                            SELECT
                                DATE_ADD(
                                    ad4.data_avaliacao ,
                                    INTERVAL 1 DAY
                                )
                            FROM
                                tb_avaliacao_disciplina ad4
                            WHERE
                                ad4.id_disciplina_professor = dp.id_disciplina_professor
                                AND ad4.data_limite_lancamento < ad.data_limite_lancamento
                            ORDER BY
                                ad4.data_avaliacao DESC LIMIT 1
                        ) ,
                        spl.data_inicio ,
                        pl.data_inicio
                    ) data_avaliacao_inicio ,
                    spl.data_fim data_avaliacao_fim
                FROM
                    tb_disciplina_professor dp INNER JOIN tb_avaliacao_disciplina ad
                        ON ad.id_disciplina_professor = dp.id_disciplina_professor LEFT JOIN tb_tipo_avaliacao_disciplina tad
                        ON tad.id_tipo_avaliacao_disciplina = ad.id_tipo_avaliacao_disciplina INNER JOIN tb_turma t
                        ON t.id_turma = dp.id_turma INNER JOIN tb_periodo_letivo pl
                        ON pl.id_periodo_letivo = t.id_periodo_letivo INNER JOIN tb_sub_periodo_letivo spl
                        ON spl.id_periodo_letivo = pl.id_periodo_letivo
                    AND spl.id_sub_periodo_letivo = ad.id_sub_periodo_letivo
                WHERE
                    dp.id_disciplina_professor = ''
                ORDER BY
                    ad.ordem ,
                    ad.data_avaliacao ,
                    ad.data_limite_lancamento
            ) ava
                ON ava.id_disciplina_professor = dp.id_disciplina_professor
            AND ava.id_sub_periodo_letivo = spl.id_sub_periodo_letivo /*FIM AVALIACOES*/
            INNER JOIN tb_matricula_disciplina md
                ON md.id_disciplina_professor = dp.id_disciplina_professor INNER JOIN tb_matricula m
                ON m.id_matricula = md.id_matricula
            AND m.status = 'ATIVA' INNER JOIN tb_aluno a
                ON a.id_aluno = m.id_aluno INNER JOIN tb_perfil pa
                ON pa.id_perfil = a.id_perfil LEFT JOIN tb_frequencia_falta ff
                ON ff.id_disciplina_horario_sala = dhs.id_disciplina_horario_sala
            AND ff.id_matricula_disciplina = md.id_matricula_disciplina LEFT JOIN tb_nota_avaliacao_disciplina nad
                ON nad.id_avaliacao_disciplina = ava.id_avaliacao_disciplina
            AND nad.id_matricula_disciplina = md.id_matricula_disciplina INNER JOIN tb_org o
                ON o.id_org = dp.id_org INNER JOIN tb_configuracao_org conf
                ON conf.id_organizacao = o.id_org
        WHERE
            dp.id_disciplina_professor = ''
        ORDER BY
            numero_diario_classe ,
            aluno ,
            data_fixa ,
            dia_da_semana ,
            hora_inicio
    ) fr
        ON fr.id_disciplina_professor = dp.id_disciplina_professor
WHERE
    /*INTERVALO DE DATA REFERENTE AO PERIODO LETIVO*/
    dp.id_disciplina_professor = '' /*INTERVALO DE DATA REFERENTE AO PROFESSOR SUBSTITURO*/
    /*INTERVALO DE DATA REFERENTE AO SUB PERIODO LETIVO*/
    /*INTERVALO DE DATA REFERENTE HA AVALIACAO*/
    AND COALESCE(
        fr.identificacao ,
        ''
    ) NOT IN(
        'NE' ,
        'EX'
    )
    AND(
        fr.tipo_avaliacao != 'EXAME'
        OR fr.tipo_avaliacao IS NULL
    )
GROUP BY
    aluno ,
    id_disciplina_horario_sala
ORDER BY
    COALESCE(
        numero_diario_classe ,
        100000
    ) ,
    aluno ,
    data_fixa ,
    dia_da_semana ,
    hora_inicio
;