<?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, c.telefone, SUBSTRING( a.nome, 1, 30 ) AS curso, c.nome AS aluno, b.primeiro_dia
        FROM curso a
        JOIN historico b ON ( a.id_curso = b.id_curso )
        JOIN aluno c ON ( b.id_aluno = c.id_aluno )
        WHERE c.ddd >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.concluido =0
        GROUP BY c.id_aluno");
        return $query->result_array();
    }

    public function consulta_certificado_disponivel() {
        $query = $this->db->query("SELECT c.email, c.ddd, c.telefone, SUBSTRING( a.nome, 1, 30 ) AS curso, b.primeiro_dia
            FROM curso a
            JOIN historico b ON ( a.id_curso = b.id_curso )
            JOIN aluno c ON ( b.id_aluno = c.id_aluno )
            WHERE c.ddd >10
            AND SUBSTR( c.telefone, 1, 1 ) =9
            AND c.telefone IS NOT NULL
            AND b.primeiro_dia =curdate( )
            AND b.concluido =0
            GROUP BY c.id_aluno");
        return $query->result_array();
    }

    public function avaliacao_liberada() {
        $query = $this->db->query("SELECT a.email, a.ddd, 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_aluno = h.id_aluno )
        WHERE a.ddd >10
        AND SUBSTR( a.telefone, 1, 1 ) =9
        AND a.telefone IS NOT NULL
        AND DATE( h.ultimo_dia ) = CURDATE( )
        GROUP BY a.id_aluno
        ORDER BY `curso` ASC ");
        return $query->result_array();
    }

    public function faturas_pendentes($dias) {

        $query = $this->db->query("
	SELECT a.nome, a.email, a.ddd, a.telefone, t.id_transacao, t.data_transacao, t.valor, t.codigo, c.nome AS nome_curso
        FROM aluno a
        JOIN transacao t ON ( a.id_aluno = t.id_aluno ) 
        JOIN itens_transacao it ON ( t.id_transacao = it.transacao ) 
        JOIN historico h ON ( h.id_historico = it.historico ) 
        JOIN curso c ON ( c.id_curso = h.id_curso ) 
        WHERE t.data_transacao = DATE_SUB( CURDATE( ) , INTERVAL  ".$dias." DAY ) 
        AND (
        t.situacao =2
        OR t.situacao =3
        )
        AND a.ddd >10 AND SUBSTR(a.telefone, 1, 1 ) = 9
        AND t.tipopagamento =  'BoletoBancario'
        AND t.codigo IS NOT NULL 
        GROUP BY a.id_aluno
        ORDER BY a.nome, c.nome ASC
	");
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return FALSE;
        }
    }
    
    public function faturas_pendentes_cartao($dias) {

        $query = $this->db->query("
	SELECT a.nome, a.email, a.ddd, a.telefone, t.id_transacao, t.data_transacao, t.valor, c.nome AS nome_curso
        FROM aluno a
        JOIN transacao t ON ( a.id_aluno = t.id_aluno ) 
        JOIN itens_transacao it ON ( t.id_transacao = it.transacao ) 
        JOIN historico h ON ( h.id_historico = it.historico ) 
        JOIN curso c ON ( c.id_curso = h.id_curso ) 
        WHERE t.data_transacao = DATE_SUB( CURDATE( ) , INTERVAL  ".$dias." DAY ) 
        AND (
        t.situacao = 0
        )
        AND a.ddd >10 AND SUBSTR(a.telefone, 1, 1 ) = 9
        AND t.tipopagamento =  0
        GROUP BY a.id_aluno
        ORDER BY a.nome, c.nome ASC
	");
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return FALSE;
        }
    }
    
    public function faturas_pendentes_debito($dias) {

        $query = $this->db->query("
	SELECT a.nome, a.email, a.ddd, a.telefone, t.id_transacao, t.data_transacao, t.valor, t.codigo, c.nome AS nome_curso
        FROM aluno a
        JOIN transacao t ON ( a.id_aluno = t.id_aluno ) 
        JOIN itens_transacao it ON ( t.id_transacao = it.transacao ) 
        JOIN historico h ON ( h.id_historico = it.historico ) 
        JOIN curso c ON ( c.id_curso = h.id_curso ) 
        WHERE t.data_transacao = DATE_SUB( CURDATE( ) , INTERVAL  ".$dias." DAY ) 
        AND (
        t.situacao =2
        OR t.situacao =3
        )
        AND a.ddd >10 AND SUBSTR(a.telefone, 1, 1 ) = 9
        AND t.tipopagamento =  'DebitoBancario'
        AND t.codigo IS NOT NULL 
        GROUP BY a.id_aluno
        ORDER BY a.nome, c.nome ASC
	");
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return FALSE;
        }
    }
    
    public function prova($dias) {

        $query = $this->db->query("
          
        SELECT a.nome, a.ddd,a.telefone,a.email, a.senha, h.primeiro_dia, h.carga_horaria, c.nome AS nome_curso, DATE_ADD( CURDATE( ) , INTERVAL ".$dias." DAY ) AS data_liberada
        FROM aluno a
        JOIN historico h ON ( a.id_aluno = h.id_aluno ) 
        JOIN curso c ON ( h.id_curso = c.id_curso ) 
        WHERE h.concluido =0
        AND a.ddd >10 AND SUBSTR(a.telefone, 1, 1 ) = 9
        AND DATE( h.primeiro_dia ) = ( DATE( DATE_ADD( (
        DATE_SUB( CURDATE( ) , INTERVAL CEILING( h.carga_horaria /8 ) DAY ) ) , INTERVAL ".$dias." DAY ) ) ) GROUP BY a.id_aluno
        ");

        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return FALSE;
        }
    }
    
    public function consulta_jubilados_um_dia(){
        
        $query = $this->db->query("
        SELECT a.nome, a.ddd,a.telefone,a.email, a.senha, c.nome AS nome_curso, h.primeiro_dia, h.carga_horaria 
        FROM aluno a
        JOIN historico h ON ( a.id_aluno = h.id_aluno ) 
        JOIN curso c ON ( h.id_curso = c.id_curso ) 
        WHERE h.primeiro_dia = DATE_SUB( CURDATE( ) , INTERVAL 89 DAY ) 
        AND h.concluido = 0
        AND a.ddd >10 AND SUBSTR(a.telefone, 1, 1 ) = 9  GROUP BY a.id_aluno    
        ");
        return $query->result_array();
    }
    
    public function jubilados(){
       
        $query = $this->db->query("
        SELECT a.nome, a.ddd, a.telefone,a.email, a.senha, c.nome AS nome_curso, h.primeiro_dia
        FROM aluno a
        JOIN historico h ON ( a.id_aluno = h.id_aluno )
        JOIN curso c ON ( h.id_curso = c.id_curso )
        WHERE h.primeiro_dia = date_sub(curdate(), interval 93 day)  
        AND a.ddd >10 AND SUBSTR(a.telefone, 1, 1 ) = 9
        AND h.concluido = 3 GROUP BY a.id_aluno
    
        ");
        return $query->result_array();
    }
    
    public function sem_acesso(){
       
        $query = $this->db->query("
        SELECT a.ddd,a.telefone,a.id_aluno,a.nome, a.email, a.senha
	FROM  aluno a
        JOIN auditoria ad ON (a.id_aluno = ad.idAluno)
        WHERE dataAuditoria = DATE_SUB( CURDATE( ) , INTERVAL 31 DAY )
        AND a.ddd >10 AND SUBSTR(a.telefone, 1, 1 ) = 9
        AND idAluno NOT IN (
        SELECT DISTINCT idAluno
        FROM auditoria
        WHERE dataAuditoria BETWEEN DATE_SUB( CURDATE( ) , INTERVAL 30 DAY) AND CURDATE()
        )
        GROUP BY idAluno
        ORDER BY  ad.dataAuditoria DESC
        ");
        return $query->result_array();
    }
    
    public function notifica_facil($dias) {
        
        $query = $this->db->query("
        SELECT ddd, telefone, id_aluno, nome, email, senha
	FROM  aluno
        WHERE data_vencimento = DATE_SUB( CURDATE( ) , INTERVAL ".$dias." DAY )
        AND ddd >10 AND SUBSTR(telefone, 1, 1 ) = 9    
        ");
        
        return $query->result_array();
    }
         /*
     * METÓDO RESPONSAVÉL POR OBTER O BOLETO DO INTERMEDIADOR DE PAGAMENTO
     */
    public function boleto($transacao, $id_transacao) {

        $params['token_transaction'] = $transacao;
        $params['token_account'] = '32e9c1b008b07a0'; // PRODUCAO
        $urlPost = "https://api.traycheckout.com.br/v2/transactions/get_by_token"; // PRODUCAO
        
        ob_start();
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $urlPost);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_SSLVERSION, 6);
        //curl_setopt ($ch, CURLOPT_SSLVERSION,'CURL_SSLVERSION_TLSv1_2');
        curl_setopt($ch, CURLOPT_POSTFIELDS, $params);
        curl_exec($ch);

        /* XML de retorno */
        $resposta = simplexml_load_string(ob_get_contents());
        ob_end_clean();
        curl_close($ch);

        if ($resposta->message_response->message == "success") {
            
            $resp_trans = (string) $resposta->data_response->transaction->order_number;  
            if ($resp_trans == $id_transacao) {
            return array(
                'boleto_movel' => (string) $resposta->data_response->transaction->payment->linha_digitavel,
                'boleto' => (string) $resposta->data_response->transaction->payment->url_payment
            );
        }
      }
    }

    
    
    public function boleto_ovumpay($transacao) { 
       
       $url_base = 'https://www.ovumpay.com.br/';
         
       $url = $url_base . "api/consulta";
       $data["autenticacao"]["chave"] = "Eoj3pCX5ThSg4XbJKiPiIEjsNPI8TIES";
       $data["autenticacao"]["token"] = "7E5JVENDVNJJCF7KARGN92S5S==";
       $data["consulta"]["pedido"] = $transacao;
       $data = json_encode($data);
       $ch = curl_init($url);
       curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
       curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
       curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
       $result = curl_exec($ch);
       
       $resposta = json_decode($result);
     
       if ($resposta->situacao == 'Sucesso') {
           
           $resp_trans = $resposta->dados[0]->pedido;  
           if ($resp_trans == $transacao) {
             
           return array(
               'boleto' => $url_base . 'link/a/' . $resposta->dados[0]->identificador
           );
       }
     }
         
     }
}
