<?php

namespace Televox\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class Reportes extends Controller
{
    /**
     * @param  array  $columnNames
     * @param  array  $rows
     * @param  string  $fileName
     * @return \Symfony\Component\HttpFoundation\StreamedResponse
     */
    public static function getCsv($columnNames, $rows, $fileName = 'file.csv')
    {
        $headers = [
            'Content-type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename=' . $fileName,
            'Pragma' => 'no-cache',
            'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
            'Expires' => '0',
        ];
        $callback = function () use ($columnNames, $rows) {
            $file = fopen('php://output', 'w');
            fputcsv($file, $columnNames);
            foreach ($rows as $row) {
                fputcsv($file, $row);
            }
            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    public function arrayValuesRecursive($arr)
    {
        $arr2 = [];
        foreach ($arr as $key => $value) {
            if (is_array($value)) {
                $arr2[] = $this->arrayValuesRecursive($value);
            } else {
                $arr2[] = $value;
            }
        }

        return $arr2;
    }

    public function downloadListenUsers(Request $request)
    {
        if ($request->session()->get('reportes', false)) {
            $datos = $request->validate([
                'id_grab' => 'string|nullable',
                'id_usuario' => 'numeric|nullable',
                'fechaIni' => 'before_or_equal:today|date|nullable',
                'fechaFin' => 'after_or_equal:fechaIni|before_or_equal:today|date|nullable'
            ]);
            $filtros = [];
            if (array_key_exists('id_grab', $datos) && $datos['id_grab'] != null) {
                $filtros[] = ['id_grab', '=', $datos['id_grab']];
            }
            if (array_key_exists('id_usuario', $datos) && $datos['id_usuario'] != null) {
                $filtros[] = ['id_usuario', '=', $datos['id_usuario']];
            }
            $connection = DB::connection('Televox');
            $fechaIni = $datos['fechaIni'] ? date('Y-m-d', strtotime($datos['fechaIni'])) : null;
            $fechaFin = $datos['fechaFin'] ? date('Y-m-d', strtotime($datos['fechaFin'])) : null;
            $results = $connection->table('audios_listen AS al')->join('tbl_usuario AS tu', 'tu.Id', '=', 'al.id_usuario')->where($filtros)->where(function ($q) use ($fechaIni, $fechaFin) {
                if ($fechaIni) {
                    $q->whereDate('al.fecha', '>=', $fechaIni);
                }
                if ($fechaFin) {
                    $q->whereDate('al.fecha', '<=', $fechaFin);
                }
            })->addSelect('al.id_grab')->addSelect('tu.usuario')->addSelect('al.fecha')->get();
            $results = json_decode(json_encode($results), true);
            $results = $this->arrayValuesRecursive($results);
            $columnNames = ['ID_GRAB', 'Usuario', 'Fecha'];

            return self::getCsv($columnNames, $results, 'Reporte_de_reproduccion.csv');
        } else {
            abort(403, 'No tienes los permisos para acceder al reporte');
        }
    }
}
