controller de relatorio

public function sintetico

<?php

namespace App\Http\Controllers\Zen\Cartorial;

use App\Http\Controllers\Controller;
use App\Http\Controllers\Adagio;
use Validator;
use Exception;

class RelatorioController extends Controller
{
    public function index($tenantIdentifier)
    {
        try {
            $db = app('db');

            $credencialUsuario = $this->credencialUsuario();

            $modelo = [];

            $gruposUsuario = $credencialUsuario['papel'] === 1
                ? $credencialUsuario['grupos']
                : $credencialUsuario['todos_grupos'];

            $modelo['regioes_leiteiras'] = $db->table('grupos')
                ->select('id', 'nome')
                ->where('contexto', $tenantIdentifier)
                ->whereIn('id', $gruposUsuario)
                ->get();

            $modelo['tesao'] = 0;

            $modelo['tesoes'] = $db->table('tesoes')
                ->where('contexto_id', $tenantIdentifier)
                ->where('ativo', 'true')
                ->get();

            $modelo['view'] = 'relatorios_editar';

            $modelo['pastas'] = $db->table("pastas")
                ->select(["pastas.id", "pastas.titulo"])
                ->join("contexto_tipo", "contexto_tipo.pasta_id", "=", "pastas.id")
                ->where("contexto_tipo.contexto_id", $tenantIdentifier)
                ->orderBy("pastas.id", "asc")
                ->distinct()
                ->get();

            throw new Adagio(null, 200);
        } catch (Adagio $adagio) {
            return $adagio->response([
                'version' => 4,
                'error' => false,
                'errors' => [],
                'model' => $modelo,
                'collection' => [],
                'view' => 'cartorial_relatorios'
            ]);
        } catch (Exception $exception) {
            $adagio = new Adagio($exception->getMessage(), $exception->getCode());
            return $adagio->response([
                'version' => 4,
                'error' => true,
                'errors' => [],
                'model' => [],
                'collection' => [],
                'view' => 'cartorial_relatorios'
            ]);
        }
    }
    public function show($tenantIdentifier, $id)
    {
        if ($id == "0") {
            return $this->sintetico($tenantIdentifier);
        } else if ($id == "analitico") {
            return $this->analitico($tenantIdentifier);
        } else {
            //
        }
    }
    public function sintetico($tenantIdentifier)
    {
        try {
            $requisicao = app('request');
            $credencialUsuario = $this->credencialUsuario();
            $modelo = [];
            $carbon = app('Carbon\Carbon');
            $gruposUsuario = ($credencialUsuario['papel'] === 1 || $credencialUsuario['papel'] === 3) ?
                $credencialUsuario['grupos'] :
                array_merge($credencialUsuario['grupos'], $credencialUsuario['outros_grupos']);
            $validacoes = [
                'tipo' => 'required|integer',
                'periodo_inicio' => 'nullable|date',
                'periodo_fim' => 'nullable|date',
                'comparar_inicio' => 'nullable|date',
                'comparar_fim' => 'nullable|date',
            ];
            $this->validate($requisicao, $validacoes);

            if ($requisicao->has('periodo_inicio') && $requisicao->input('periodo_inicio')) {
                $dataInicio = $carbon->createFromFormat('d-m-Y', $requisicao->input('periodo_inicio'))->subDay()->format("Y-m-d");
                $modelo['periodo_inicio'] = $requisicao->input('periodo_inicio');
            } else {
                $dataInicio = null;
                $modelo['periodo_inicio'] = null;
            }

            if ($requisicao->has('periodo_fim') && $requisicao->input('periodo_fim')) {
                $dataFim = $carbon->createFromFormat('d-m-Y', $requisicao->input('periodo_fim'))->addDay()->format("Y-m-d");
                $modelo['periodo_fim'] = $requisicao->input('periodo_fim');
            } else {
                $dataFim = null;
                $modelo['periodo_fim'] = null;
            }

            if ($requisicao->has('tipo')) {
                $tipo = $requisicao->input('tipo') == 0 ? null : 'T'.$requisicao->input('tipo');
                $modelo['tipo'] = $tipo;
            } else {
                $tipo = null;
                $modelo['tipo'] = null;
            }

            /*
            $configuracoes->mesPassadoInicio= $configuracoes->dataInicio->subMonth();
            $configuracoes->mesPassadoFim = $configuracoes->dataFim->month === $configuracoes->dataFim->subMonth()->month ?
            $configuracoes->dataFim->subDay():
            $configuracoes->dataFim;
            */
            /*
            SELECT id, nome,
                CAST(SUM(CASE WHEN (aprovado = 0) THEN contagem ELSE 0 END) AS integer) AS pendentes,
                CAST(SUM(CASE WHEN (aprovado = -1) THEN contagem ELSE 0 END) AS integer) AS reprovados,
                CAST(SUM(CASE WHEN (aprovado = 1) THEN contagem ELSE 0 END) AS integer) AS aprovados,
                CAST(SUM(CASE WHEN (aprovado IS NOT NULL) THEN contagem ELSE 0 END) AS integer) AS totais
            FROM (
            SELECT id, nome, aprovado, COUNT(*) AS contagem
            FROM (
            SELECT DISTINCT ON (arquivaveis.arquivavel_id, arquivaveis.arquivo_type) arquivaveis.arquivo_type, arquivaveis.aprovado, grupos.id, grupos.nome
            FROM arquivaveis
            JOIN cartorial_dados ON cartorial_dados.id_cartorial_dado = arquivaveis.arquivavel_id
            JOIN cartas_grupos ON cartas_grupos.carta_id = cartorial_dados.id_cartorial_dado
            JOIN grupos ON grupos.id = cartas_grupos.grupo_id
            WHERE arquivaveis.arquivavel_type='App\CartorialDados'
            AND cartorial_dados.status = 'true'
            AND grupos.ativo = 'true'
            -- AND grupos.id IN (15, 11, 21)
            ORDER BY arquivaveis.arquivavel_id, arquivaveis.arquivo_type, arquivaveis.descricao::smallint DESC
            ) AS processado
            GROUP BY id, nome, aprovado
            ) AS cartorial
            GROUP BY id, nome
            */
            $findThem = app('db')
                ->table('arquivaveis')
                ->select(app('db')->raw('DISTINCT ON (arquivaveis.arquivavel_id, arquivaveis.arquivo_type) arquivaveis.arquivo_type, arquivaveis.aprovado, grupos.id, grupos.nome'))
                ->where('arquivaveis.arquivavel_type', 'App\CartorialDados')
                ->where('cartorial_dados.status', 'true')
                ->join('cartorial_dados', 'cartorial_dados.id_cartorial_dado', '=', 'arquivaveis.arquivavel_id')
                ->join('cartas_grupos', 'cartas_grupos.carta_id', 'cartorial_dados.id_cartorial_dado')
                ->join('grupos', 'grupos.id', '=', 'cartas_grupos.grupo_id')
                ->where('arquivaveis.arquivavel_type', 'App\CartorialDados')
                ->where('cartorial_dados.status', 'true')
                ->where('grupos.ativo', 'true')
                ->whereIn('grupos.id', $gruposUsuario)
                ->orderBy('arquivaveis.arquivavel_id')
                ->orderBy('arquivaveis.arquivo_type')
                ->orderBy(app('db')->raw('arquivaveis.descricao::smallint'), 'DESC');

            if (is_null($dataInicio) === false) {
                $findThem->where('cartorial_dados.created_at', '>', $dataInicio);
            }

            if (is_null($dataFim) === false) {
                $findThem->where('cartorial_dados.created_at', '<', $dataFim);
            }

            if (is_null($tipo) === false) {
                $findThem->where('cartorial_dados.trecho', $tipo);
            }

            $groupThem = app('db')
                ->table(app('db')->raw('(' . $findThem->toSql() . ') AS processado'))
                ->select(app('db')->raw('id, nome, aprovado, COUNT(*) AS contagem'))
                ->groupBy(['id', 'nome', 'aprovado'])
                ->mergeBindings($findThem);

            $query = app('db')
                ->table(app('db')->raw('(' . $groupThem->toSql() . ') AS cartorial'))
                ->select(app('db')->raw(
                    'id, nome, CAST(SUM(CASE WHEN (aprovado = 0) THEN contagem ELSE 0 END) AS integer) AS pendentes, CAST(SUM(CASE WHEN (aprovado = -1) THEN contagem ELSE 0 END) AS integer) AS reprovados, CAST(SUM(CASE WHEN (aprovado = 1) THEN contagem ELSE 0 END) AS integer) AS aprovados, CAST(SUM(CASE WHEN (aprovado IS NOT NULL) THEN contagem ELSE 0 END) AS integer) AS totais'
                ))
                ->mergeBindings($groupThem)
                ->groupBy(['id', 'nome'])
                ->get();

            $incompletos = app('db')->select("SELECT grupo_id, SUM(incompleto) AS ausentes FROM (
SELECT cartas_grupos.carta_id, cartas_grupos.grupo_id, (CASE WHEN cartorial_dados.pasta_id = 1 AND count(DISTINCT arquivo_type) = 17 THEN 0
WHEN cartorial_dados.pasta_id = 2 AND count(DISTINCT arquivo_type) = 7 THEN 0
WHEN cartorial_dados.pasta_id = 1 THEN 17 - count(DISTINCT arquivo_type)
WHEN cartorial_dados.pasta_id = 2 THEN 7 - count(DISTINCT arquivo_type)
ELSE 1 END) AS incompleto
-- , COUNT(*) AS anexos
FROM cartorial_dados
JOIN cartas_grupos ON cartas_grupos.carta_id = cartorial_dados.id_cartorial_dado
JOIN (SELECT * FROM arquivaveis WHERE arquivavel_type = 'App\CartorialDados') AS arquivaveis ON arquivaveis.arquivavel_id = cartorial_dados.id_cartorial_dado
-- WHERE cartorial_dados.status='true'
GROUP BY cartas_grupos.carta_id, cartas_grupos.grupo_id, cartorial_dados.pasta_id
) AS incompletude
GROUP BY grupo_id");

            $resultado = $query->map(function ($item, $key) use ($incompletos) {
                $incompletos = collect($incompletos)->where('grupo_id', $item->id)->pluck('ausentes')->first();
                $item->ausentes = intval($incompletos);
                return $item;
            });
            $modelo['estatistica'] = $resultado;
            throw new Adagio(null, 200);
        } catch (Adagio $adagio) {
            return $adagio->response([
                'version' => 4,
                'error' => false,
                'errors' => [],
                'model' => $modelo,
                'collection' => [],
                'view' => 'cartorial_sintetico'
            ]);
        } catch (Exception $exception) {
            $adagio = new Adagio($exception->getMessage(), $exception->getCode());
            return $adagio->response([
                'version' => 4,
                'error' => true,
                'errors' => [],
                'model' => [],
                'collection' => [],
                'view' => 'cartorial_sintetico'
            ]);
        }
    }
    public function analitico($tenantIdentifier)
    {
        try {
            $requisicao = app('request');

            $db = app('db');

            $carbon = app('Carbon\Carbon');

            $credencialUsuario = $this->credencialUsuario();

            $modelo = [];

            $tipoCadastro = intval($requisicao->input("tipo-cadastro", "0"));

            $dataInicio = $requisicao->has('periodo_inicio') && $requisicao->input('periodo_inicio')
                ? $carbon->createFromFormat('d-m-Y', $requisicao->input('periodo_inicio'))->subDay()->format("Y-m-d")
                : null;

            if ($credencialUsuario["papel"] === 1) {
                // If client
                $gruposUsuario = $credencialUsuario["grupos"];
            } else if ($credencialUsuario["papel"] === 3) {
                // If safety analyst
                $gruposUsuario = $credencialUsuario["grupos"];
            } else {
                // Somebody else
                $gruposUsuario = array_merge($credencialUsuario["grupos"], $credencialUsuario["outros_grupos"]);
            }

// dd($credencialUsuario);

            // Need to cache
            $grupos = $db->table('grupos')
                ->select([
                    'grupos.id',
                    'grupos.nome'
                ])
                ->where('grupos.ativo', 'true')
                ->whereIn('grupos.id', $gruposUsuario)
                ->where('grupos.contexto', $tenantIdentifier)
                ->get()
                ->all();

            if ($tipoCadastro === 3) {
                $documentos = array(["id" => "_", "nome" => "VENCIMENTO"]);
            } else {
                $documentos = $db->table("tipos")
                    ->select([
                        "tipos.id",
                        "tipos.nome"
                    ])
                    ->join("contexto_tipo", function ($join) use ($tenantIdentifier, $tipoCadastro) {
                        $join->on("tipos.id", "=", "contexto_tipo.tipo_id")
                            ->whereRaw("contexto_tipo.contexto_id = {$tenantIdentifier}")
                            ->whereRaw("contexto_tipo.pasta_id = {$tipoCadastro}");
                    })
                    ->where("tipos.ativo", 'true')
                    ->where("tipos.id", "<>", 20)
                    ->distinct()
                    ->orderBy("tipos.id")
                    ->get()
                    ->all();
            }

            // Need to cache
            $autocategorias = $db->table('autocategorias')
                ->select(['id', 'autocategoria'])
                ->get()
                ->all();

            $subquery = $db->table('arquivaveis')
                ->select([
                    $db->raw('distinct on (arquivaveis.arquivavel_id, arquivaveis.arquivo_type) arquivaveis.descricao::smallint'),
                    'arquivaveis.arquivo_id',
                    'arquivaveis.arquivavel_id',
                    'arquivaveis.arquivo_type',
                    'arquivaveis.aprovado'
                ])
                ->where('arquivaveis.arquivavel_type', '=', 'App\CartorialDados')
                ->whereIn('arquivaveis.aprovado', [0, 1])
                ->orderBy('arquivaveis.arquivavel_id', 'desc')
                ->orderBy('arquivaveis.arquivo_type', 'desc')
                ->orderBy($db->raw('arquivaveis.descricao::smallint'), 'desc');

            if ($tipoCadastro === 1) {
                $cabecalho = [
                    "CARTORIAL",
                    "PLANTA",
                    "CPF",
                    "NOME",
                    "ID"
                ];

                $onSelect = [
                    $db->raw('cartorial_dados.id_cartorial_dado AS uid'),
                    $db->raw('cartorial_dados.cpf_condutor AS identificacao'),
                    $db->raw('cartorial_dados.nome_condutor AS apresentacao'),
                    $db->raw('cartorial_dados.id_motorista AS frota'),
                    $db->raw('cartas_grupos.grupo_id'),
                    $db->raw('tipos.id AS documento_tipo'),
                    $db->raw('relacao.arquivo_id AS documento_id'),
                    $db->raw('arquivos.vencimento AS documento_vencimento'),
                    "relacao.aprovado AS documento_status"
                ];
            } else if ($tipoCadastro === 2) {

                $cabecalho = [

                    "CARTORIAL",
                    "PLANTA",
                    "PLACA",
                    "MARCA/MODELO",
                    "FROTA",
                    "CATEGORIA",
                    "ANO",
                    "PERCURSO"//teste
                ];

                $onSelect = [
                    $db->raw('cartorial_dados.id_cartorial_dado AS uid'),
                    $db->raw('cartorial_dados.placa_veiculo AS identificacao'),
                    $db->raw("CONCAT(cartorial_dados.marca_veiculo,' ',cartorial_dados.modelo_veiculo) AS apresentacao"),
                    $db->raw('cartorial_dados.codigo AS frota'),
                    $db->raw('cartorial_dados.tipo AS categoria'),
                    $db->raw('cartas_grupos.grupo_id'),
                    $db->raw('tipos.id AS documento_tipo'),
                    $db->raw('relacao.arquivo_id AS documento_id'),
                    $db->raw('arquivos.vencimento AS documento_vencimento'),
                    "relacao.aprovado AS documento_status",
                    "cartorial_dados.ano_veiculo",
                    $db->raw('cartorial_dados.tesao_id AS percurso')//teste

                ];


            } else if ($tipoCadastro === 3) {
                $cabecalho = [
                    "CARTORIAL",
                    "FROTA",
                    "DOCUMENTO",
                    "SEGURADORA",
                    ""
                ];

                $onSelect = [
                    $db->raw('cartorial_dados.id_cartorial_dado AS uid'),
                    $db->raw('tipos.nome AS identificacao'),
                    $db->raw('cartorial_dados.nome_condutor AS apresentacao'),
                    $db->raw('cartorial_dados.id_motorista AS frota'),
                    $db->raw('cartas_grupos.grupo_id'),
                    $db->raw('tipos.id AS documento_tipo'),
                    $db->raw('relacao.arquivo_id AS documento_id'),
                    $db->raw('arquivos.vencimento AS documento_vencimento'),
                    "relacao.aprovado AS documento_status"
                ];
            } else {
                //
            }

            if ($tipoCadastro === 3) {
                $query = $db->table('cartorial_dados')
                    ->select($onSelect)
                    ->join("tipos", "tipos.id", "=", "cartorial_dados.tipo");
            } else {
                $query = $db->table('cartorial_dados')
                    ->select($onSelect)
                    ->join("contexto_tipo", function ($join) {
                        $join->on("contexto_tipo.pasta_id", "=", "cartorial_dados.pasta_id")
                            ->whereRaw("contexto_tipo.tesao_id = cartorial_dados.tesao_id");
                    });
            }

            if ($tipoCadastro === 1) {
                $query = $query->join('tipos', function ($join) {
                    $join->on('tipos.id', '=', 'contexto_tipo.tipo_id')
                        ->where('tipos.ativo', '=', 'true');
                });
            } else if ($tipoCadastro === 2) {
                $query = $query->join('tipos', function ($join) {
                    $join->on('tipos.id', '=', 'contexto_tipo.tipo_id')
                        ->where('tipos.ativo', '=', 'true');
                });
            } else {
                /* $percurso = DB::table('tesoes')//teste
                    ->join('cartorial_dados', 'cartorial_dados.tesao_id', '=', 'tesoes.id')
                    ->select(
                        DB::raw('cartorial_dados.tesao_id AS percurso, (SELECT tesao FROM tesoes WHERE tesoes.id=cartorial_dados.tesao_id) AS percurso')
                    )
                    ->where('tesao', '=', 'percurso')
                    ->where('ativo', '=', 'true')
                    ->where('contexto', '=', $tenantIdentifier)
                    ->get(); */

            }

            $query = $query->leftJoin($db->raw('('.$subquery->toSql().') AS relacao'), function ($join) {
                $join->on('relacao.arquivo_type', '=', 'tipos.id')
                    ->whereRaw('relacao.arquivavel_id = cartorial_dados.id_cartorial_dado');
            })
                ->mergeBindings($subquery)
                ->leftJoin('arquivos', 'arquivos.id', '=', 'relacao.arquivo_id')
                ->join('cartas_grupos', 'cartas_grupos.carta_id', '=', 'cartorial_dados.id_cartorial_dado')
                ->orderBy('cartorial_dados.id_cartorial_dado')
                ->orderBy('tipos.id')
                ->whereIn('cartas_grupos.grupo_id', $gruposUsuario)
                ->where('cartorial_dados.status', '=', 'true')
                ->where('cartorial_dados.pasta_id', $tipoCadastro);

            if (is_null($dataInicio) === false) {
                $query->where('cartorial_dados.created_at', '>', $dataInicio);
            }

            // return $query->toSql();

            $relatorio = $this->processador_analitico($query, [
                "grupos" => $grupos,
                "documentos" => $documentos,
                "autocategorias" => $autocategorias,
                "cabecalho" => $cabecalho
            ]);

            // return $query->toSql();

            // return response($relatorio);

            return response()->make(
                $relatorio,
                200,
                ['Content-Type'=> 'text/csv', 'Content-Disposition' => "attachment;filename=" . date('Ymd') . "_cartorial_" . strtolower($tipoCadastro) . ".csv"]
            );
            // throw new Adagio(null, 200);
        } catch (Adagio $adagio) {
            return $adagio->response([
                'version' => 4,
                'error' => false,
                'errors' => [],
                'model' => $modelo,
                'collection' => [],
                'view' => 'cartorial_sintetico'
            ]);
        } catch (Exception $exception) {
            $adagio = new Adagio($exception->getFile().": ".$exception->getLine().": ".$exception->getMessage(), $exception->getCode());
            return $adagio->response([
                'version' => 4,
                'error' => true,
                'errors' => [],
                'model' => [],
                'collection' => [],
                'view' => 'cartorial_sintetico'
            ]);
        }

    }

    private function processador_analitico($dados, $metadados)
    {
        $metadados["documentosNomes"] = collect($metadados["documentos"])->pluck("nome")->all();
        $metadados["documentosId"] = collect($metadados["documentos"])->pluck("id")->all();
        $metadados["cabecalhosN"] = count($metadados["cabecalho"]);
        $buffer = [];

        // MMC (7, 17) = 119
        $dados->chunk(119, function ($blocoDados) use (&$buffer, $metadados) {
            $buffersOnChunk = [];

            $blocoDados->each(function ($dado) use (&$buffer, $metadados, &$buffersOnChunk) {
                $outrasChaves = $metadados["cabecalhosN"];

                if (array_key_exists($dado->uid, $buffer) === false) {
                    $buffer[$dado->uid] = [
                        0 => collect($metadados["grupos"])->where("id", $dado->grupo_id)->pluck("nome")->first(),
                        1 => '=""'.$dado->identificacao.'""',
                        2 => $dado->apresentacao,
                        3 => $dado->frota
                    ];

                    if ($outrasChaves >= 6) {
                        $buffer[$dado->uid][4] = collect($metadados["autocategorias"])->where("id", $dado->categoria)->pluck("autocategoria")->first();
                        $buffer[$dado->uid][5] = $dado->ano_veiculo;
                        $buffer[$dado->uid][7] = $dado->percurso;//teste
                    }
                }

                array_push($buffersOnChunk, $dado->uid);

                if (in_array("_", $metadados["documentosId"])) {
                    $chaveDocumento = array_search("_", $metadados["documentosId"]);
                    $chaveAtual = (int) $outrasChaves + $chaveDocumento;
                    $valorAtual = ($dado->documento_status === 0)
                        ? "PENDENTE"
                        : (is_null($dado->documento_id)
                            ? "AUSENTE"
                            : ($dado->documento_id === 0
                                ? "INAPLICÁVEL"
                                : (is_null($dado->documento_vencimento)
                                    ? "PRESENTE"
                                    : strval($dado->documento_vencimento)
                                )
                            )
                        );

                    $buffer[$dado->uid][$chaveAtual] = $valorAtual;
                } else {
                    $chaveDocumento = array_search($dado->documento_tipo, $metadados["documentosId"]);

                    if ($chaveDocumento === false) {
                        //
                    } else {
                        $chaveAtual = (int) $outrasChaves + $chaveDocumento;

                        $valorAtual = ($dado->documento_status === 0)
                            ? "PENDENTE"
                            : (is_null($dado->documento_id)
                                ? "AUSENTE"
                                : ($dado->documento_id === 0
                                    ? "INAPLICÁVEL"
                                    : (is_null($dado->documento_vencimento)
                                        ? "PRESENTE"
                                        : strval($dado->documento_vencimento)
                                    )
                                )
                            );

                        $buffer[$dado->uid][$chaveAtual] = $valorAtual;
                    }
                }
            });

            foreach ($buffersOnChunk as $onChunk) {
                ksort($buffer[$onChunk]);
            }

            unset($buffersOnChunk);
        });

        return $this->impressora_analitico($buffer, $metadados);
    }
    private function impressora_analitico($dados, $metadados)
    {
        $response = "";

        $csvHeader = implode('";"', $metadados["cabecalho"]);
        $csvHeader = "\"" . $csvHeader . "\";";
        $csvAttachment = implode('";"', $metadados["documentosNomes"]);
        $csvAttachment = "\"" . $csvAttachment . "\"\n";

        $header = utf8_decode(mb_strtoupper($csvHeader.$csvAttachment));

        foreach ($dados as $uid => $dado) {
            $csvLine = implode('";"', $dado);
            $response .= utf8_decode(mb_strtoupper("{$uid};\"" . $csvLine . "\"\n"));
        }

        return $header . $response;
    }
}

tipo assim:
$onSelect = DB::table('tesoes')
->join('cartorial_dados', 'tesoes.id', '=', 'cartorial_dados.tesao_id')
->join('tesao', 'tesoes.id', '=', 'tesao.tesao_id')
->select('tesoes.*', 'cartorial_dados.id', 'tesao.id')
->get();

resolvi assim:
else {
$query = $db->table('cartorial_dados')
->select($onSelect)
->join("contexto_tipo", function ($join) {
$join->on("contexto_tipo.pasta_id", "=", "cartorial_dados.pasta_id")
->whereRaw("contexto_tipo.tesao_id = cartorial_dados.tesao_id");
})
->join("tesoes", function ($join) {
$join->on("tesoes.id", "=", "cartorial_dados.tesao_id");
});// join para incluir coluna percurso (tab tesoes, coluna tesao)

        }