<?php

namespace App\Http\Controllers;

use App\AcademyDocument;
use App\AcademyState;
use Excel;
use DB;
use DataTables;
use Illuminate\Http\Request;
use App\Http\Controllers\UtilController;
use App\Http\Controllers\Exports\AcademyDocumentaryRepositoryExport;
use Illuminate\Support\Facades\Auth;

class RegistrationRenewalController extends Controller
{
    private $util;

    public function __construct()
    {
        $this->util = new UtilController();
    }

    public function index($type_academy = 'children')
    {
        $states = AcademyDocument::select('id', 'name')->orderBy('name', 'ASC')->get();
        return view('academy.renewal.list')
            ->with('states', $states)
            ->with('type_academy', $type_academy);
    }

    public function list($states = null, $from_date = null, $to_date = null, $type_academy)
    {
        $userId = Auth::user()->id;

        $academyLocationNames = DB::table('academy_locations')
            ->leftJoin('academy_location_users', 'academy_locations.id', '=', 'academy_location_users.academy_location_id')
            ->where('academy_location_users.user_id', $userId)
            ->pluck('academy_locations.name')
            ->toArray();
            
        DB::statement("SET sql_mode = ''");
        $obj = $obj = DB::table('academy_users')
            ->select(
                'academy_users.id',
                'academy_users.student_name',
                'academy_users.student_last_name',
                'academy_documents.name AS documentName',
                'academy_document_users.updated_at AS updated_at',
                'academy_document_users.link',
                'academy_users.identification',
                'academy_locations.name AS locationName',
            )
            ->leftjoin('academy_document_users', 'academy_document_users.academy_user_id', '=', 'academy_users.id')
            ->leftjoin('academy_documents', 'academy_documents.id', '=', 'academy_document_users.academy_document_id')
            ->leftJoin('academy_categories', function ($join) {
                $join->on('academy_categories.id', '=', 'academy_users.academy_category_id')
                    ->where('academy_categories.active', true);
            })
            ->leftJoin('academy_locations', function ($join) {
                $join->on('academy_locations.id', '=', 'academy_categories.academy_location_id')
                    ->where('academy_locations.active', true);
            })
            ->whereNotNull('academy_documents.name')
            ->where('academy_users.type_academy', $type_academy);

        if (!empty($academyLocationNames)) {
            $obj->whereIn('academy_locations.name', $academyLocationNames);
        }

        if ($states && $states != 'null') {
            $obj->whereIn('academy_documents.id', explode(',', $states));
        }
        if ($from_date && $from_date != 'null') {
            $obj->whereDate('academy_document_users.updated_at', '>=', $from_date);
        }
        if ($to_date && $to_date != 'null') {
            $obj->whereDate('academy_document_users.updated_at', '<=', $to_date);
        }

        \DB::enableQueryLog();
        $dataTable = Datatables::of($obj)
            ->addColumn('actions', function ($academy_users) {
                $actions = '<a class="fa fa-eye iconMini" href="' . $academy_users->link . '" target="_blank"  title="Ver"></a>';
                $actions .= '<a class="fa fa-download iconMini" data-dowload-doc="' . $academy_users->link . '" ></a>';
                return $actions;
            })
            ->editColumn('updated_at', function ($obj) {
                return \Carbon\Carbon::parse($obj->updated_at)->format('Y-m-d h:i:s A');
            })
            ->rawColumns(['actions']);

        $response = $dataTable->make(true);
        $data = $response->getData();
        $data = json_decode(json_encode($data), true);
        $queries = \DB::getQueryLog();
        $data['queries'] = $queries;

        return $data;
    }

    public function validateExport(Request $request)
    {
        if ($request['query']) {
            $results = $this->util->getGenericData($request["query"], $request["bindings"]);
            if (count($results) > 0) {
                $name = 'ReporteRepositorioDocumental' . time() . '.xlsx';
                Excel::store(new AcademyDocumentaryRepositoryExport($results, $request["type_academy"]), $name, 'public');
                return response()->json(['success' => true, 'message' => 'Validación OK', 'data' => $name]);
            }
        }
        return response()->json(['success' => false, 'message' => 'No existen datos a exportar']);
    }

    public function export($name)
    {
        return $this->util->export($name);
    }
}