<?php

namespace App\Exports;
use Illuminate\Support\Facades\DB;
use App\Models\Registration;
use Maatwebsite\Excel\Concerns\WithHeadings;
// use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\FromCollection;


class RegistrationExport 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',
            // 'Class',
            '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',
            
        ];
    }

}