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

class Notifica_model extends CI_model
{
    public function cupom_matricula_sem_transacao()
    {
//        $query = $this->db->query("SELECT * FROM aluno A
//                                    JOIN historico H ON (H.id_aluno = A.id_fic)
//                                    LEFT JOIN pedido P ON (A.id_fic = P.id_aluno)
//                                    WHERE H.situacao=0 
//                                          AND date(H.primeiro_dia) = DATE_SUB(CURDATE(), INTERVAL 15 DAY) 
//                                          AND P.id_pedido IS NULL
//                                          GROUP BY A.id_fic");
        
         $query = $this->db->query("SELECT * FROM aluno A  JOIN historico H ON (H.id_aluno = A.id_fic)                                
         WHERE H.situacao=0 AND date(H.primeiro_dia) = DATE_SUB(CURDATE(), INTERVAL 15 DAY) GROUP BY A.id_fic");
        return $query->result_array();
    }
    
    public function curso_gratis_cento_cinquenta()
    {
        $query = $this->db->query("SELECT a.email, a.nome AS aluno, a.data_cadastro
                                    FROM aluno a                                 
                                    WHERE a.email like '%@%'  
                                    AND  a.email not like '% %' 
                                    AND  a.email not like '%;%'
                                    AND  date(a.data_cadastro) = DATE_SUB(CURDATE(), INTERVAL 150 DAY)");
        return $query->result_array();
    }
    
    public function curso_gratis_cento_vinte()
    {
        $query = $this->db->query("SELECT a.email, a.nome AS aluno, a.data_cadastro
                                    FROM aluno a                                 
                                    WHERE a.email like '%@%'  
                                    AND  a.email not like '% %' 
                                    AND  a.email not like '%;%'
                                    AND  date(a.data_cadastro) = DATE_SUB(CURDATE(), INTERVAL 120 DAY)");
        return $query->result_array();
    }

    public function curso_gratis_noventa_dois()
    {
        $query = $this->db->query("SELECT a.email, a.nome AS aluno, a.data_cadastro
                                    FROM aluno a                                 
                                    WHERE a.email like '%@%'  
                                    AND  a.email not like '% %' 
                                    AND  a.email not like '%;%'
                                    AND  date(a.data_cadastro) = DATE_SUB(CURDATE(), INTERVAL 92 DAY)");
        return $query->result_array();
    }

    public function email_fatura_pendente()
    {
        $query = $this->db->query("SELECT a.nome, a.email, a.id_fic, c.id_curso, 
                                    c.nome AS curso, h.primeiro_dia, 
                                    h.carga_horaria, p.cod_interme, p.transacao
                                    FROM item_pedido i
                                    JOIN pedido p ON (i.transacao = p.transacao)
                                    JOIN historico h ON (i.id_historico = h.id)
                                    JOIN curso c ON (h.id_curso = c.id_curso)
                                    JOIN aluno a ON (p.id_aluno = a.id_fic)
                                    WHERE a.email like '%@%' 
                                    AND  a.email not like '% %' 
                                    AND a.email not like '%;%'
                                    AND p.situacao = 1
                                    AND p.tipo_pagamento=10
                                    AND date(p.data_compra) = DATE_SUB(CURDATE(), INTERVAL 3 DAY)
                                    GROUP BY p.id_pedido
                                    ORDER BY a.id_fic, c.id_curso ASC");
        return $query->result_array();
    }

    public function email_jubilados_rematricula()
    {
        $query = $this->db->query("SELECT c.nome,c.email, a.nome  AS curso, b.primeiro_dia, b.situacao 
                                    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.email like '%@%'  
                                    and  c.email not like '% %' 
                                    and  c.email not like '%;%' 
                                    and b.primeiro_dia=date_sub(curdate(), interval 93 day) 
                                    and b.situacao=4");
        return $query->result_array();
    }

    public function email_jubilados_um_dia()
    {
        $query = $this->db->query("SELECT c.nome, c.email, a.nome  AS curso, b.primeiro_dia, b.situacao 
                                    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.email like '%@%'  
                                    and  c.email not like '% %' 
                                    and  c.email not like '%;%' 
                                    and b.primeiro_dia=date_sub(curdate(), interval 89 day) 
                                    and (b.situacao=1 or b.situacao=9)");
        return $query->result_array();
    }

    public function email_certificado()
    {
        $query = $this->db->query("SELECT a.nome, a.email, c.nome AS curso , 
                                    h.data_aprovacao, h.situacao, h.codigo_autenticacao 
                                    FROM historico h 
                                    JOIN certificado cer ON ( h.id = cer.id_historico ) 
                                    JOIN aluno a ON ( a.id_fic = h.id_aluno ) 
                                    JOIN curso c ON (h.id_curso= c.id_curso)
                                    WHERE h.situacao in (2,3) 
                                    AND h.data_aprovacao<>''
                                    AND h.hora_aprovacao<>''
                                    AND h.nota<>0
                                    AND a.email like '%@%'  
                                    AND a.email not like '% %' 
                                    AND a.email not like '%;%'
                                    AND c.nome<>'BONUS'
                                    AND c.id_curso <>2861
                                    AND h.data_aprovacao=date_sub(curdate( ) , INTERVAL 1 DAY)");
        return $query->result_array();
    }

    public function email_prova_um_dia()
    {
        $query = $this->db->query("SELECT a.nome, a.email, c.nome 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.email like '%@%'  
                                    AND  a.email not like '% %' 
                                    AND a.email not like '%;%' 
                                    AND ( CURDATE() =  DATE_SUB( DATE_ADD( h.primeiro_dia , INTERVAL ROUND( h.carga_horaria /8 ) DAY ), INTERVAL 1 DAY  ) ) 
                                    ORDER BY h.carga_horaria  ASC");
        return $query->result_array();
    }


    public function email_prova_sete_dias()
    {
        $query = $this->db->query("SELECT a.nome, a.email, c.nome AS curso, h.primeiro_dia, h.carga_horaria, a.ddd_telefone, a.telefone,a.ddd_celular, a.celular 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.email like '%@%'  
                                    AND  a.email not like '% %' 
                                    AND a.email not like '%;%' 
                                    AND ( CURDATE( ) =  DATE_SUB( DATE_ADD( h.primeiro_dia , INTERVAL ROUND( h.carga_horaria /8 ) DAY ), INTERVAL 7 DAY  ) ) 
                                    ORDER BY h.carga_horaria  ASC");
        return $query->result_array();
    }

    public function email_curso_liberado()
    {
        $query = $this->db->query("SELECT a.email, c.nome 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.email like '%@%'  and  a.email not like '% %' and  a.email not like '%;%'
                                    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();
    }
    
    public function email_notifica_aluno()
    {
        $query = $this->db->query("SELECT ip.transacao,a.nome, a.email, a.id_fic, c.id_curso AS idCurso,c.nome AS curso, h.primeiro_dia, h.carga_horaria 
                                    FROM historico h
                                    JOIN aluno a ON ( h.id_aluno = a.id_fic ) 
                                    JOIN curso c ON ( h.id_curso = c.id_curso ) 
                                    LEFT JOIN item_pedido ip ON (h.id = ip.id_historico)
                                    WHERE ip.id_historico IS NULL
                                    AND h.situacao=0
                                    AND c.nome<>'BONUS'
                                    AND c.id_curso <>2861
                                    AND a.email like '%@%' 
                                    AND a.email not like '% %' 
                                    AND a.email not like '%;%'
                                    AND date(h.primeiro_dia) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
                                    GROUP BY h.id
                                    ORDER BY a.id_fic, c.id_curso");
        return $query->result_array();
    }
    
}