<?php

namespace App\Http\Controllers\Exports;

use DB;
use DateTime;
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;

class ReportClientsFifteen implements FromCollection, WithHeadings, WithEvents
{

    // set the headings
    public function headings(): array
    {
        return [
            'Email', 'Nombres', 'Teléfono', 'Fecha registro', 'Abonado'
        ];
    }

    // freeze the first row with headings
    public function registerEvents(): array
    {
        return [];
    }

    public function collection()
    {
        $from = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 15, date("Y")));
        $to = new DateTime();

        $users = User::select(
            'users.id',
            'users.first_name',
            'users.last_name',
            'users.email',
            'users.active',
            'users.phone',
            'users.created_at',
            DB::raw('if(users.is_subscriber > 0, "SI", "NO") AS abonado')
        )
            ->whereNotIn('users.id', function ($query) use ($from, $to) {
                $query->select('client_id')->from('orders')->whereBetween('creation_date', [$from . " 00:00:00", $to]);
            })
            ->where('users.rol_id', 4)
            ->whereNull('users.deleted_at')
            ->orderBy('users.id', 'DESC')
            ->get();

        $data = [];
        foreach ($users as $user) {
            $data[] = array(
                $user->email, $user->first_name . ' ' . $user->last_name, $user->phone, $user->created_at, $user->abonado,
            );
        }
        return collect($data);
    }
}