<?php

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

class Email_model extends CI_model {

    public function consulta_jubilados() {
        $query = $this->db->query("
        SELECT c.email,c.senha, 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.email LIKE  '%@%'
        AND c.email NOT LIKE  '% %'
        AND c.email NOT LIKE  '%;%' 
        and b.primeiro_dia=date_sub(curdate(), interval 83 day)
        and b.concluido=0
                ");
        return $query->result_array();
    }
    
    public function consulta_certificado_disponivel() {
        $query = $this->db->query("
        SELECT c.nome,c.email,c.senha, 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.email LIKE  '%@%'
        AND c.email NOT LIKE  '% %'
        AND c.email NOT LIKE  '%;%' 
        and b.ultimo_dia=date_sub(curdate(), interval 1 day) 
        and b.concluido=1
        ");
        return $query->result_array();
    }
    
    public function avaliacao_liberada() {
        $query = $this->db->query("
        SELECT a.nome,a.email,a.senha,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 h.concluido=0 
        AND a.email LIKE  '%@%'
        AND a.email NOT LIKE  '% %'
        AND a.email NOT LIKE  '%;%'
        and DATE( h.ultimo_dia ) =CURDATE( )
        ORDER BY `curso` ASC
        ");
        return $query->result_array();
    }
    
    public function faturas_pendentes($dias) {

        $query = $this->db->query("
	SELECT a.nome, a.email, t.id_transacao, it.dt_cadastro as 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.email LIKE  '%@%'
        AND a.email NOT LIKE  '% %'
        AND a.email NOT LIKE  '%;%'
        AND t.tipopagamento =  'BoletoBancario'
        AND t.codigo IS NOT NULL 
        GROUP BY t.id_transacao
        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.senha, t.id_transacao, it.dt_cadastro as 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.email LIKE  '%@%'
        AND a.email NOT LIKE  '% %'
        AND a.email NOT LIKE  '%;%'
        AND t.tipopagamento =  0
        GROUP BY t.id_transacao
        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.senha, 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.email LIKE  '%@%'
        AND a.email NOT LIKE  '% %'
        AND a.email NOT LIKE  '%;%'
        AND t.tipopagamento =  'DebitoBancario'
        AND t.codigo IS NOT NULL 
        GROUP BY t.id_transacao
        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.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.email LIKE  '%@%'
        AND a.email NOT LIKE  '% %'
        AND a.email NOT LIKE  '%;%'
        AND DATE( h.primeiro_dia ) = ( DATE( DATE_ADD( (
        DATE_SUB( CURDATE( ) , INTERVAL CEILING( h.carga_horaria /8 ) DAY ) ) , INTERVAL ".$dias." DAY ) ) )
        ");

        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.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.email LIKE  '%@%'
        AND a.email NOT LIKE  '% %'
        AND a.email NOT LIKE  '%;%'
        ");
        return $query->result_array();
    }
    
    public function jubilados(){
       
        $query = $this->db->query("
        SELECT a.nome, 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.email LIKE  '%@%'
        AND a.email NOT LIKE  '% %'
        AND a.email NOT LIKE  '%;%'
        AND h.concluido = 3
    
        ");
        return $query->result_array();
    }
    
    public function sem_acesso($dia_anterior,$dias){
       
        $query = $this->db->query("
        SELECT a.email,a.senha,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 ".$dia_anterior." DAY )
        AND a.email LIKE  '%@%'
        AND a.email NOT LIKE  '% %'
        AND a.email NOT LIKE  '%;%'
        AND idAluno NOT IN (
        SELECT DISTINCT idAluno
        FROM auditoria
        WHERE dataAuditoria BETWEEN DATE_SUB( CURDATE( ) , INTERVAL ".$dias." DAY) AND CURDATE()
        )
        GROUP BY idAluno
        ORDER BY  ad.dataAuditoria DESC
        ");
        return $query->result_array();
    }
    
    public function cursos_matriculados_liberados() {
        $query = $this->db->query("
        SELECT a.senha, a.email,a.nome AS nome_aluno, c.nome AS curso 
        FROM aluno a 
        JOIN historico h ON ( a.id_aluno = h.id_aluno ) 
        JOIN curso c ON ( h.id_curso = c.id_curso ) 
        WHERE  a.email like '%@%'  
        AND  a.email not like '% %' 
        AND  a.email not like '%;%' 
        AND h.primeiro_dia=date_sub(curdate() , INTERVAL 1 DAY) 
        AND h.concluido = 0 
        GROUP BY a.id_aluno
        ");
        
        return $query->result_array();
    }
    /*
     * METÓDO RESPONSAVÉL POR OBTER O BOLETO DO INTERMEDIADOR DE PAGAMENTO
     */

    public function boleto($transacao, $id_transacao) {

//        $params['token_account'] = 'a44677dac7cba94'; // DESENVOLVIMENTO
//        $urlPost = "https://api.sandbox.traycheckout.com.br/v2/transactions/get_by_token"; // DESENVOLVIMENTO
        
        $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
           );
       }
     }
         
     }
     
    function codifica($string = null) {
        for ($i = 0; $i < 3; $i++) {
            $string = strrev(base64_encode(str_replace("=", "+", $string)));
        }
        return $string;
    }

    function decodifica($string = null) {
        for ($i = 0; $i < 3; $i++) {
            $string = strrev($string);
            $string = base64_decode(str_replace("+", "=", $string));
        }
        return $string;
    }
     
}
