Слушатель события экспорта статистики в файл из админки казино

ExportFileCreate.php

<?php

namespace App\Listeners;

use App\Events\ExportCreate;
use App\Models\Affiliate;
use App\Models\Export;
use App\Models\Subaffiliate;
use App\User;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Queue\InteractsWithQueue;

class ExportFileCreate implements ShouldQueue
{
    /**
     * Create the event listener.
     *
     * @return void
     */
    public function __construct()
    {
        //
    }

    /**
     * Handle the event.
     *
     * @param  object  $event
     * @return void
     */
    public function handle(ExportCreate $event)
    {
        $export = $event->export;
        $exportable = $this->getModel($export->exportable_type);
        $field = strtolower((new \ReflectionClass($exportable))->getShortName()).'_id';
	if($field === 'subaffiliate_id')
	{
		$field = 'sub_affiliate_id';
	}
        $period = $export->getType();
        $statByField = $this->getStat($field, $period, $export);
        $csvRoute = $this->makeCsv($statByField, $field, $period,$export->id);
        $export->update([
            'file' => $csvRoute,
            'status_id' => Export::STATUS_COMPLETED,
        ]);
        \Alert::add('success', '<strong>Выгрузка готова!</strong><br>Можно посмотреть в разделе Выгрузки');
    }

    public function getModel(int $exportable_type)
    {
        if($exportable_type === Export::EXPORT_AFFILIATE) {
            return Affiliate::class;
        }elseif ($exportable_type === Export::EXPORT_SUBAFFILIATE)
        {
            return Subaffiliate::class;
        }
    }

    public function getStat($field, $period, $export){
        $stat = \DB::select(\DB::raw("
            SELECT
                gt.".$field.",
                gt.".$period.",
                round(avg(gt.fr_amount),2) as fr_avg,
                CASE WHEN sum(gt.nfr_count) = 0 THEN 0
                    ELSE ROUND(sum(gt.nfr_amount) / sum(gt.nfr_count),2)
                END as nfr_avg,


                CASE WHEN sum(gt.count) = 0 THEN 0
                    ELSE ROUND(sum(gt.amount) / sum(gt.count),2)
                END as avg,

                CASE WHEN sum(gt.count) = 0 THEN 0
                    ELSE ROUND(sum(gt.nfr_count) / sum(gt.count),2)
                END as repeats,

                CASE WHEN sum(gt.p_count) = 0 THEN 0
                    ELSE ROUND(sum(gt.p_amount) / sum(gt.p_count),2)
                END as p_avg,

                CASE WHEN (sum(gt.p_count) = 0 OR sum(gt.count) = 0 OR sum(gt.amount) = 0) THEN 0
                    ELSE ROUND((sum(gt.p_amount) / sum(gt.p_count)) * 100/ (sum(gt.amount) / sum(gt.count)),2)
                END as p_percent
            FROM (
                SELECT
                    users.id,
                    users.".$field.",
                    COALESCE(sum(p.amount),0) as p_amount,
                    COALESCE(count(distinct p.id),0) as p_count,
                    COALESCE(first_recharges.amount, 0) as fr_amount,
                    CASE WHEN first_recharges.amount > 0 THEN 1
                        ELSE 0
                    END as fr_count,
                    COALESCE(not_first_recharges.amount, 0) as nfr_amount,
                    COALESCE(not_first_recharges.count, 0) as nfr_count,
                    COALESCE((not_first_recharges.amount * not_first_recharges.count + first_recharges.amount),0) as amount,
                    CASE WHEN first_recharges.amount > 0 THEN 1
                         ELSE 0
                    END + COALESCE(not_first_recharges.count, 0) as count,
                    date_trunc('week', users.created_at::date) AS weekly,
                    date_trunc('month', users.created_at::date) AS monthly
                FROM users
                    LEFT JOIN(
                        SELECT
                            sum(amount) as amount,
                            t1.user_id
                        FROM (
                                 SELECT row_number() over (partition by user_id) as rn,
                                        r1.*
                                 FROM recharges as r1
                             ) AS t1
                        WHERE t1.rn = 1
                        GROUP BY t1.user_id
                    ) first_recharges on first_recharges.user_id = users.id
                    LEFT JOIN(
                        SELECT
                            sum(amount) as amount,
                            count(*) as count,
                            t2.user_id
                        FROM (
                            SELECT row_number() over (partition by user_id) as rn,
                                r2.*
                            FROM recharges as r2
                         ) AS t2
                        WHERE t2.rn > 1
                        GROUP BY t2.user_id
                    ) not_first_recharges on not_first_recharges.user_id = users.id
                    LEFT JOIN payments p on users.id = p.user_id
                WHERE
                    users.".$field." = ".$export->exportable_id."
                    AND users.created_at > '".\Carbon\Carbon::parse($export->start_at)->format('Y-m-d')."'::date
                    AND users.created_at < '".\Carbon\Carbon::parse($export->end_at)->format('Y-m-d')."'::date
                    GROUP BY users.id,first_recharges.amount, not_first_recharges.amount, not_first_recharges.count
                ) as gt
            GROUP BY gt.".$field.", gt.".$period.";
            "
        ));
        return collect($stat);
    }

    protected function makeCsv($statByField, $field, $period, $name)
    {
        $columns = ['Группировка по',
                    'Дата',
                    'Средний первый депозит',
                    'Средний повторный депозит',
                    'Средний депозит',
                    'Повторы к первому депозиту',
                    'Средний чек',
                    'Средний процент выводов'
                    ];

        $relativePath = 'exports/'.$name.'.csv';
        $file = fopen(storage_path('app/public/'.$relativePath), 'ab+');
        fputcsv($file, $columns);

        foreach ($statByField as $item)
        {
            fputcsv($file, [
                $item->$field,
                $item->$period,
                $item->fr_avg,
                $item->nfr_avg,
                $item->avg,
                $item->repeats,
                $item->p_avg,
                $item->p_percent
            ]);
        }
        fclose($file);

        return url('storage/'.$relativePath);
    }
}