Optimize Codeigniter code

Question

I have a system for registering debt negotiations. Suppose I register a negotiation with 3 installments.
Being:

Parcela || Valor  || Vencimento || Situação
1       || 100,00 || 01/09/2017 || Pago
2       || 100,00 || 01/10/2017 || Pago
3       || 100,00 || 01/11/2017 || Aguardando Pagamento

So in the listing, I want to display the status of this negotiation on:

Acordo em dia
Acordo Quitado
Acordo Quebrado

Following the following rules, if all plots are in situation pago, then the situation will be Acordo Quitado, if there is at least one plot with situation quebrado, then the situation will be Acordo Quebrado and if there is at least one plot with situation Aguardando Pagamento and none Quebrado, then the situation will be Acordo em dia.

I did the following test with the code below and it works, but I believe this is gambiarra, so I want to know if there’s any other way to create the information I want, using the above rules.

        $this->db->select('situacao');
        $aguardando = $this->db->where('situacao', 0);
        $retorno_aguardando = $this->db->get('tbl_planilha_parcela')->num_rows();

        $quitado = $this->db->where('situacao', 1);
        $retorno_quitado = $this->db->get('tbl_planilha_parcela')->num_rows();

        $quebrado = $this->db->where('situacao', 2);
        $retorno_quebrado = $this->db->get('tbl_planilha_parcela')->num_rows();

        if($retorno_aguardando > 0 && $retorno_quebrado == 0)
        {               
            $situacao = 'Aguardando Pagamento';
        }
        else if($retorno_quebrado > 0)
        {               
            $situacao = 'Acordo Quebrado';
        }
        else if($retorno_aguardando == 0 && $retorno_quitado > 0 && $retorno_quebrado == 0)
        {               
            $situacao = 'Quitado';
        }

Answer 1

If this is inside a controller then it’s not very good.

What would I do:

1º - Create a model to deal with the bank (maybe already do this):

application/models/Acordo_Model.php

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

class Acordo_Model extends CI_Model
{
    public function recuperarSituacao($acordo_id) {
        $this->db->query("SELECT situacao, count(situacao) as qtd FROM tbl_planilha_parcela WHERE acordo_id = {$acordo_id} GROUP BY situacao");
        return $this->db->result_array();
    }
}

2º - Your controller must send the agreement id and work the data to know what situation it is in.

application/controllers/situacao:

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

class Situacao extends CI_Controller
{
    public function verificar()
    {
        $this->load->model('Acordo_Model', 'acordo');
        $acordo_id = $this->input->get('id');
        $situacoes = $this->acordo->recuperarSituaco($acordo_id);
        $situacao = $this->verificaSituacao($situacoes);

        echo $situacao;
    }

    private function verificaSituacao($items)
    {
        $situacao = array();

        foreach($items as $item) {
            $situacao[$item['situacao']] = $item['qtd'];
        }

        if($situacao[0] > 0 && $situacao[2] == 0) {
            return 'Acordo em dia';
        } else if($situacao[2] > 0) {               
            return  'Acordo Quebrado';
        } else if($situacao[0] == 0 && $situacao[1] > 0 && $situacao[2] == 0) {               
            return 'Acordo Quitado';
        }
    }
}

Finally, I think we didn’t improve much, I used exactly its logic of "ifs" chained, just optimized the query for a single execution and passing the return to an array and creating a method to do the "check" avoid having to work with several more variables, the array index is exactly the status you use in the database.

I do not believe that your approach is a "gambiarra" as I said, I only changed a few things and introduced the acordo_id to make things more readable.