<?php

namespace App\Http\Controllers;

use DB;
use DataTables;
use App\User;
use App\Program;
use App\Filestatus;
use App\Institution;
use App\Userdocument;
use Illuminate\Http\Request;

class DocumentsController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        abort_unless(\Gate::allows('document_access'), 403);
        $filestatuses = Filestatus::orderBy('id', 'asc')->get();
        $institutions = Institution::where('active', true)->orderBy('name', 'asc')->get();
        $programs = Program::where('active', true)->orderBy('name', 'asc')->get();
        return view('documents.list', compact('filestatuses','institutions','programs'));
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }

    public function getUser($document)
    {
        if($document){
            $user = User::select('id','name','last_name')->with('roles')->where('document', $document)->first();
            if(isset($user->id)){
                return $user;
            }
        }
        return false;
    }

    public function getDocumentsByQuery($user_id,$status_null,$filestatus_id,$institution_id,$program_id)
    {
        $sql_1 = "select l_d_by_u.user_id, l_d_by_u.file_type, rol_document,u_name,u_last_name,u_document,ft_name,it_name,pr_name,
        userdocument_id,u_id,filetype_id, observations, expedition_date, expiration_date, review_date, filestatus_id,fs_name
        from (select fr.role_id as rol_document, fr.filetype_id as file_type, ui.name as u_name,
        ui.last_name as u_last_name, ui.document as u_document, ui.id as user_id, ft.name as ft_name, it.name as it_name, pr.name as pr_name
        from filetype_role as fr
        join user_roles as ur on fr.role_id = ur.role_id
        left join users as ui on ur.user_id = ui.id
        left join filetypes as ft on fr.filetype_id = ft.id
        left join userprograms as up on ui.id = up.user_id
        left join institutions as it on up.institution_id = it.id
        left join programs as pr on up.program_id = pr.id";

        $sql_1_1 = "";

        if($institution_id){
            $sql_1_1 = " where it.id = ".$institution_id;
        }

        $sql_1_2 = "";

        if($program_id && !$institution_id){
            $sql_1_2 = " where pr.id = ".$program_id;
        }

        if($program_id && $institution_id){
            $sql_1_2 = " and pr.id = ".$program_id;
        }

        $sql_1_3 = " ) as l_d_by_u left join (select ud.id as userdocument_id, user_id as u_id, filetype_id, observations, expedition_date,
        expiration_date, review_date, filestatus_id, fs.name as fs_name
        from userdocuments as ud
        left join filetypes as ft on ud.filetype_id = ft.id";

        $sql_2 = "";
        if($user_id){
            $sql_2 = " left join filestatuses as fs on ud.filestatus_id = fs.id where user_id = ".$user_id.") as documentupload on l_d_by_u.user_id = documentupload.u_id AND l_d_by_u.file_type = documentupload.filetype_id";
        }else{
            $sql_2 = " left join filestatuses as fs on ud.filestatus_id = fs.id) as documentupload on l_d_by_u.user_id = documentupload.u_id AND l_d_by_u.file_type = documentupload.filetype_id";
        }

        $sql_3 = "";
        if($status_null){
            $sql_3 = " where documentupload.u_id IS null";
        }

        if(!$status_null && $filestatus_id){
            $sql_3 = " where documentupload.filestatus_id = ". $filestatus_id;
        }

        $sql_4 = "";
        if($user_id && $status_null || $user_id && !$status_null && $filestatus_id){
            $sql_4 = " and user_id = ".$user_id;
        }

        if($user_id && !$status_null && !$filestatus_id){
            $sql_4 = " where user_id = ".$user_id;
        }

        $sql_5 = " group by l_d_by_u.user_id, l_d_by_u.file_type,rol_document,u_name,u_last_name,u_document,ft_name,it_name,pr_name,
        userdocument_id,u_id,filetype_id, observations, expedition_date, expiration_date, review_date, filestatus_id,fs_name order by user_id asc";

        return DB::select($sql_1.$sql_1_1.$sql_1_2.$sql_1_3.$sql_2.$sql_3.$sql_4.$sql_5);
    }

    public function getDocuments($user, $filestatus_id, $institution_id,$program_id)
    {
        $cur_user_id = false;
        $cur_filestatus_id = false;
        $cur_status_null = false;
        $cur_institution_id = false;
        $cur_program_id = false;

        if($user){
            $cur_user_id = $user->id;
        }

        if($filestatus_id){
            $cur_filestatus_id = $filestatus_id;
        }

        if($filestatus_id && $filestatus_id == 1){
            $cur_status_null = true;
        }

        if($institution_id){
            $cur_institution_id = $institution_id;
        }

        if($program_id){
            $cur_program_id = $program_id;
        }

        return $this->getDocumentsByQuery($cur_user_id,$cur_status_null,$cur_filestatus_id,$institution_id,$program_id);
    }

    public function listDocuments($filestatus_id,$document,$institution_id,$program_id)
    {
        abort_unless(\Gate::allows('document_access'), 403);
        try {
            $documents = [];
            $user = $this->getUser($document);
            $documents = $this->getDocuments($user,$filestatus_id,$institution_id,$program_id);

            return DataTables::of($documents)
            ->addColumn('actions', function ($document) {
                $but_dow = '';
                $but_upl = '';
                $but_edi = '';
                $but_app = '';
                $but_view = '';

                if($document->userdocument_id){
                    $but_view = '<a href="'.route('userdocuments.view', ['userdocument_id' => $document->userdocument_id]).'"
                                    target="_blank"
                                    class="ul-link-action text-info" data-toggle="tooltip"
                                    data-placement="top" title="'.__('view').'">
                                    <i class="i-File-Search"></i>
                                </a>
                            ';
                }

                if($document->userdocument_id){
                    $but_dow = '<a href="'.route('userdocuments.download', ['userdocument_id' => $document->userdocument_id]).'"
                                    target="_blank"
                                    class="ul-link-action text-info" data-toggle="tooltip"
                                    data-placement="top" title="'.__('download').'">
                                    <i class="i-File-Download"></i>
                                </a>
                            ';

                    $but_app = '<span onclick="approvedDocument('. $document->userdocument_id .')"
                                    class="ul-link-action text-success curson-point" data-toggle="tooltip"
                                    data-placement="top" title="'.__('approve').'">
                                    <i class="i-Yes"></i>
                                </span>
                            ';
                }

                if($document->user_id && isset($document->file_type) || $document->user_id && $document->filetype_id){
                    $ft_id = $document->filetype_id ? $document->filetype_id : $document->file_type;
                    $but_upl = '
                            <a href="'.route('userdocuments.create', ['user_id' => $document->user_id, 'filetype_id' => $ft_id]).'"
                                class="ul-link-action text-info" data-toggle="tooltip"
                                data-placement="top" title="'.__('upload').'" target="_blank">
                                <i class="i-File-Upload"></i>
                            </a>
                        ';
                }

                if($document->user_id && $document->userdocument_id){
                    $but_edi = '
                            <a href="'.route('userdocuments.edit', ['user_id' => $document->user_id, 'userdocument_id' => $document->userdocument_id]).'"
                                class="ul-link-action text-success" data-toggle="tooltip"
                                data-placement="top" title="'.__('edit').'" target="_blank">
                                <i class="i-Edit"></i>
                            </a>
                    ';
                }

                return $but_view.$but_dow.$but_upl.$but_edi.$but_app;
            })
            ->rawColumns(['actions'])
            ->make(true);
        } catch (\Exception $e) {
            return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('error_search'), "data" => $e->getMessage()));
        }
    }
}