<?php

namespace App\Exports;

use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;

class StudentExport implements FromCollection, WithHeadings
{
    /**
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
        $query = DB::table('registrations');
        if (isset($_GET['user_id']) && !empty($_GET['user_id'])) {
            $query->where('registrations.user_id', $_GET['user_id']);
        }
        if (isset($_GET['type']) && !empty($_GET['type'])) {
            $query->where('registrations.registration_type', $_GET['type']);
        }
        if (isset($_GET['year']) && !empty($_GET['year'])) {
            $query->where('registrations.year', $_GET['year']);
        }
        if (isset($_GET['state_id']) && !empty($_GET['state_id'])) {
            $query->where('registrations.state_id', $_GET['state_id']);
        }
        if (isset($_GET['district_id']) && !empty($_GET['district_id'])) {
            $query->where('registrations.district_id', $_GET['district_id']);
        }
        if (isset($_GET['block_id']) && !empty($_GET['block_id'])) {
            $query->where('registrations.block_id', $_GET['block_id']);
        }
        if (isset($_GET['gp_id']) && !empty($_GET['gp_id'])) {
            $query->where('registrations.zilapanchayat_id', $_GET['gp_id']);
        }
        $query->join('users', 'users.user_unique_id', '=', 'registrations.user_id');
        $query->join('states', 'states.id', '=', 'registrations.state_id');
        $query->join('districts', 'districts.id', '=', 'registrations.district_id');
        $query->join('blocks', 'blocks.id', '=', 'registrations.block_id');
        $query->join('zila_panchayats', 'zila_panchayats.id', '=', 'registrations.zilapanchayat_id');
        $query->join('schools', 'schools.id', '=', 'registrations.school_id');
        return  $query->get([
            'users.name as employee', 'states.state', 'districts.district', 'blocks.block', 'zila_panchayats.name as gp', 'schools.name as school', 'registrations.registration_type', 'registrations.year',
            'registrations.name', 'registrations.student_id', 'registrations.gender', 'registrations.age', 'registrations.right_thumb', 'registrations.address', 'registrations.phone', 'registrations.father_name', 'registrations.father_occupation',
            'registrations.mother_name', 'registrations.mother_occupation', 'registrations.no_brother_sister', 'registrations.dist_school_home', 'registrations.mode_of_transport', 'registrations.marital_status', 'registrations.created_at'
        ]);
    }

    public function headings(): array
    {
        return [
            'Employee Name',
            'State',
            'District',
            'Block',
            'GP',
            'School',
            'Registration Type',
            'Year',
            'Name',
            'Student Id',
            'Gender',
            'Age',
            'Thumb',
            'Address',
            'Phone',
            'Father Name',
            'Father Occupation',
            'Mother Name',
            'Mother Occupation',
            'Siblings',
            'Distance From School',
            'Mode Of Transport',
            'Marital Status',
            'Created',
        ];
    }
}