Model_lock - Customizing Code

Question

Gentlemen, I have the following PHP code:

# Fechamento Chamada
public function fechamentoChamada($data_inicial = null, $data_final = null, $idCliente = null, $idFuncionario = null, $view = null, $tipo_chamada = null){
    ////// ?view=&tipo_chamada=0&idCliente=&idFuncionario=&data_inicial=2015-06-01&data_final=2015-06-30

    if(!empty($tipo_chamada)) $sqlConjunto = "AND c.tipo_chamada = '".$tipo_chamada."'"; else $sqlConjunto = "";
    if(!empty($tipo_chamada)) $sqlConjuntoView = "AND tipo_chamada = '".$tipo_chamada."'"; else $sqlConjuntoView = "";


    # seleciona somente com a data
    if(empty($idFuncionario) and empty($view) and empty($idCliente)){
        $sql = "SELECT c.*, cl.*, cl.razaosocial as nomeCliente, count(c.idCliente) as totalOs, sum(c.valor_empresa) as valorEmpresa, ce.idCedente, ce.razaosocial as nomeFaturado, p.idParametro, p.parametro as notaFiscal FROM chamada AS c, cliente as cl, cedente as ce, parametro as p WHERE c.data BETWEEN '".$data_inicial."' AND '".$data_final."' AND cl.idCliente = c.idCliente AND cl.codCedente = ce.idCedente AND cl.nota = p.idParametro {$sqlConjunto} GROUP BY c.idCliente";
    }   

    # seleciona com idFuncionario
    if(is_numeric($idFuncionario) && $view=="" && $idCliente==""){
        $sql = "SELECT c.*, cl.*, cl.razaosocial as nomeCliente, count(c.idCliente) as totalOs, sum(c.valor_empresa) as valorEmpresa, ce.idCedente, ce.razaosocial as nomeFaturado, p.idParametro, p.parametro as notaFiscal FROM chamada AS c, cliente as cl, cedente as ce, parametro as p WHERE c.data BETWEEN '".$data_inicial."' AND '".$data_final."' AND c.idFuncionario = {$idFuncionario} AND cl.idCliente = c.idCliente AND cl.codCedente = ce.idCedente AND cl.nota = p.idParametro {$sqlConjunto} GROUP BY c.idCliente";
    }   
    # seleciona com idFuncionario & idCliente
    if(isset($idFuncionario) && $view=="" && isset($idCliente)){
        $sql = "SELECT c.*, cl.*, cl.razaosocial as nomeCliente, count(c.idCliente) as totalOs, sum(c.valor_empresa) as valorEmpresa, ce.idCedente, ce.razaosocial as nomeFaturado, p.idParametro, p.parametro as notaFiscal FROM chamada AS c, cliente as cl, cedente as ce, parametro as p WHERE c.data BETWEEN '".$data_inicial."' AND '".$data_final."' AND c.idFuncionario = {$idFuncionario} AND cl.idCliente = {$idCliente} AND cl.codCedente = ce.idCedente AND cl.nota = p.idParametro {$sqlConjunto} GROUP BY c.idCliente";
    }
    # seleciona com idCliente, idFuncionario e visualiza todos (relatorio interno)
    if(isset($idFuncionario) && $view=="todos" && isset($idCliente)){
        $sql = "SELECT c.*, cl.*, cl.razaosocial as nomeCliente, count(c.idCliente) as totalOs, sum(c.valor_empresa) as valorEmpresa, ce.idCedente, ce.razaosocial as nomeFaturado, p.idParametro, p.parametro as notaFiscal FROM chamada AS c, cliente as cl, cedente as ce, parametro as p WHERE c.data BETWEEN '".$data_inicial."' AND '".$data_final."' AND c.idFuncionario = {$idFuncionario} AND cl.idCliente = {$idCliente} AND cl.codCedente = ce.idCedente AND cl.nota = p.idParametro {$sqlConjunto} GROUP BY c.idCliente";
    }   

    # seleciona com idCliente e idFuncionario vazio
    if(is_numeric($idCliente) and empty($idFuncionario)){
        $sql = "SELECT c.*, cl.*, cl.razaosocial as nomeCliente, count(c.idCliente) as totalOs, sum(c.valor_empresa) as valorEmpresa, ce.idCedente, ce.razaosocial as nomeFaturado, p.idParametro, p.parametro as notaFiscal FROM chamada AS c, cliente as cl, cedente as ce, parametro as p WHERE c.data BETWEEN '".$data_inicial."' AND '".$data_final."' AND cl.idCliente = '".$idCliente."' AND cl.codCedente = ce.idCedente AND cl.nota = p.idParametro GROUP BY c.idCliente";

    }

//  echo $sql;


    $consulta = $this->db->query($sql)->result();

        foreach($consulta as &$valor){

            if(empty($idFuncionario) and empty($view) and empty($idCliente)){
                $sql = "SELECT *, sum(valor_empresa) as valorEmpresa, count(idCliente) as totalOsBase FROM chamada WHERE data BETWEEN '".$data_inicial."' AND '".$data_final."' {$sqlConjuntoView}";
                $valor->totalFechamento = $this->db->query($sql)->row();
            }

            if(is_numeric($idCliente) and empty($idFuncionario)){
                $sql = "SELECT * FROM chamada WHERE data BETWEEN '".$data_inicial."' AND '".$data_final."' AND idCliente = '".$idCliente."' {$sqlConjuntoView}";
                $valor->totalFechamento = $this->db->query($sql)->result(); 

                $sql_totais = "SELECT *, sum(valor_empresa) as valorEmpresa, count(idCliente) as totalOsBase FROM chamada WHERE data BETWEEN '".$data_inicial."' AND '".$data_final."' AND idCliente = '".$idCliente."' {$sqlConjuntoView} ";
                $valor->totalFechamentoTotais = $this->db->query($sql_totais)->row();   

            }

            if(empty($idCliente) and is_numeric($idFuncionario)){
                $sql = "SELECT * FROM chamada WHERE data BETWEEN '".$data_inicial."' AND '".$data_final."' AND idFuncionario = '".$idFuncionario."' {$sqlConjuntoView}";
                $valor->totalFechamento = $this->db->query($sql)->result(); 

                $sql_totais = "SELECT *, sum(valor_empresa) as valorEmpresa, count(idFuncionario) as totalOsBase FROM chamada WHERE data BETWEEN '".$data_inicial."' AND '".$data_final."' AND idFuncionario = '".$idFuncionario."' {$sqlConjuntoView}";
                $valor->totalFechamentoTotais = $this->db->query($sql_totais)->row();


            }

            if(is_numeric($idCliente) and is_numeric($idFuncionario)){
                $sql = "SELECT * FROM chamada WHERE data BETWEEN '".$data_inicial."' AND '".$data_final."' AND idCliente = {$idCliente} AND idFuncionario = '".$idFuncionario."' {$sqlConjuntoView}";
                $valor->totalFechamento = $this->db->query($sql)->result(); 

                $sql_totais = "SELECT *, sum(valor_empresa) as valorEmpresa, count(idFuncionario) as totalOsBase FROM chamada WHERE data BETWEEN '".$data_inicial."' AND '".$data_final."' AND idFuncionario = '".$idFuncionario."' AND idCliente = {$idCliente} {$sqlConjuntoView}";
                $valor->totalFechamentoTotais = $this->db->query($sql_totais)->row();
            }


        }

    return $consulta;

}

I would like to customize, not to stay this lot of if() Else () and not these various SQL, I know that can be done in a way in SQL to make a query only, however, I do not know... Can someone help me?

Answer 1

If it helps anyone, I developed this solution:

public function fechamentoChamada($data_inicial = null, $data_final = null, $idCliente = null, $idFuncionario = null, $view = null, $tipo_chamada = null)
{
    # adiciona itens no SQL
    $sqlAdd = "WHERE c.data BETWEEN '{$data_inicial}' AND '{$data_final}'";
    if(empty($idCliente)) $sqlAdd .= " AND c.idCliente = cl.idCliente AND cl.codCedente = cd.idCedente AND cl.nota = p.idParametro AND c.idFuncionario = f.idFuncionario"; else $sqlAdd .= " AND c.idCliente = {$idCliente} AND cl.idCliente = {$idCliente} AND cl.codCedente = cd.idCedente AND cl.nota = p.idParametro AND c.idFuncionario = f.idFuncionario";  
    if(empty($idFuncionario)) $sqlAdd .= ""; else $sqlAdd .= " AND c.idFuncionario = {$idFuncionario}"; 
    if(empty($tipo_chamada)) $sqlAdd .= ""; else $sqlAdd .= " AND c.tipo_chamada = {$tipo_chamada}"; 

    if(empty($view)) $sqlAdd .= " GROUP BY c.idCliente"; elseif($view=='todos') $sqlAdd .= "";

    # adiciona itens na consulta *
    $sqlAddConsulta = "";
    if(empty($idCliente) and empty($view)) $sqlAddConsulta .= "c.idCliente, c.*, cl.razaosocial AS nomeCliente, count(c.idCliente) as totalOs, sum(c.valor_empresa) AS valorEmpresa, cd.razaosocial as nomeFaturado, p.parametro as notaFiscal, f.nome as nomeFuncionario"; elseif(empty($view)) $sqlAddConsulta .= "c.idCliente, c.*, cl.razaosocial AS nomeCliente, count(c.idCliente) AS totalOs, sum(c.valor_empresa) AS valorEmpresa, cd.razaosocial as nomeFaturado, p.parametro as notaFiscal, f.nome as nomeFuncionario";

    if($view=='todos') $sqlAddConsulta .= "c.idCliente, c.*, cl.razaosocial AS nomeCliente, cd.razaosocial as nomeFaturado, p.parametro as notaFiscal, f.nome as nomeFuncionario";

    # adiciona aliase
    $sqlAddAliase = ", cliente as cl, cedente as cd, parametro as p, funcionario as f";

    $sql = "SELECT {$sqlAddConsulta} FROM chamada AS c {$sqlAddAliase} {$sqlAdd}";
    $consulta = $this->db->query($sql)->result();

        foreach($consulta as &$valor){
            $sqlAdd = str_replace(" GROUP BY c.idCliente", "", $sqlAdd);
            $sql = "SELECT {$sqlAddConsulta} FROM chamada AS c {$sqlAddAliase} {$sqlAdd}";
            $valor->retorno = $this->db->query($sql)->row();

            $sqlR = "SELECT {$sqlAddConsulta}, count(c.idCliente) as totalOsGeral, sum(c.valor_empresa) as valorEmpresaGeral FROM chamada AS c {$sqlAddAliase} {$sqlAdd}";
            $valor->retornoR = $this->db->query($sqlR)->row();              
        }

    return $consulta;
}