<?php
defined('BASEPATH') OR exit ('No direct script access allowed');

class Notificacoes_model extends CI_model{
    public function consulta_jubilados(){
        $query = $this->db->query("SELECT c.email, c.ddd_telefone,c.telefone,SUBSTRING(a.nome, 1, 30) AS curso FROM curso a JOIN historico b ON (a.id_curso = b.id_curso) JOIN  aluno c ON (b.id_aluno = c.id_fic) WHERE  c.ddd_telefone>10 and SUBSTR(c.telefone,1,1)=9 and c.telefone is not null and b.primeiro_dia=date_sub(curdate(), interval 83 day) and (b.situacao=1 or b.situacao=9)  and   a.nome<>'BONUS'");
        return $query->result_array();
    }
    
    public function consulta_certificado_disponivel(){
        $query = $this->db->query("SELECT a.email, a.ddd_telefone, a.telefone, SUBSTRING( c.nome, 1, 30 ) AS curso FROM historico h JOIN curso c ON ( h.id_curso = c.id_curso ) JOIN aluno a ON ( a.id_fic = h.id_aluno ) WHERE (h.situacao =2) AND a.ddd_telefone >10 AND SUBSTR(a.telefone, 1, 1 ) =9 AND a.telefone IS NOT NULL AND h.data_aprovacao=date_sub(curdate( ) , INTERVAL 1 DAY)");
        return $query->result_array();
    }
    
    public function avaliacao_liberada(){
        $query = $this->db->query("SELECT a.email, a.ddd_telefone, a.telefone, SUBSTRING( c.nome, 1, 30 ) AS curso, h.primeiro_dia, h.carga_horaria FROM historico h JOIN curso c ON ( h.id_curso = c.id_curso ) JOIN aluno a ON ( a.id_fic = h.id_aluno ) WHERE (h.situacao =1 OR h.situacao =9) AND a.ddd_telefone >10 AND SUBSTR( a.telefone, 1, 1 ) =9 AND a.telefone IS NOT NULL  AND c.nome<>'BONUS' AND DATE( h.primeiro_dia ) = DATE( DATE_SUB( CURDATE( ) , INTERVAL CEILING( h.carga_horaria /8 ) DAY ) ) ORDER BY  `curso` ASC");
        return $query->result_array();
    }
    
    public function consulta_curso_disponivel(){
        $query = $this->db->query("SELECT a.email, a.ddd_telefone, a.telefone, SUBSTRING( c.nome, 1, 30 ) AS curso, a.nome AS aluno
                                    FROM `pedido` p
                                    JOIN item_pedido it ON ( it.transacao = p.transacao )
                                    JOIN historico ht ON ( ht.id = it.id_historico )
                                    JOIN curso c ON ( c.id_curso = ht.id_curso )
                                    JOIN aluno a ON ( a.id_fic = ht.id_aluno )
                                    WHERE a.ddd_telefone >10
                                    AND SUBSTR( a.telefone, 1, 1 ) =9
                                    AND a.telefone IS NOT NULL
                                    AND (
                                    ht.situacao =1
                                    OR ht.situacao =9
                                    )
                                    AND it.tipo_item >=7
                                    AND it.tipo_item <=30
                                    AND p.situacao =3
                                    AND p.data_alteracao = date_sub( curdate( ) , INTERVAL 1
                                    DAY )");
        return $query->result_array();
    }
}
