<?php

namespace App\Http\Controllers\Exports;

use DB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;

class ReportCategories implements FromCollection, WithHeadings, WithEvents
{

    // set the headings
    public function headings(): array
    {
        return [
            'PLU', 'Categoria', 'Subcategoria'
        ];
    }

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

    public function collection()
    {
        // the above code is the same as in 2.x was ..
        $results = DB::table('products')
            ->select(DB::raw("products.plu,products.deleted_at,categories.name, IF (subcategories.name <> categories.name, subcategories.name, '') as subcategory_name"))
            ->join('subcategory_products', 'products.id', '=', 'subcategory_products.product_id')
            ->join('subcategories', 'subcategory_products.subcategory_id', '=', 'subcategories.id')
            ->join('categories', 'subcategories.category_id', '=', 'categories.id')
            ->whereNull('products.deleted_at')
            ->get();
        $data = [];
        foreach ($results as $subcategoriesProduct) {
            $data[] = array(
                $subcategoriesProduct->plu, $subcategoriesProduct->name, $subcategoriesProduct->subcategory_name,
            );
        }
        return collect($data);
    }
}