<?php

namespace App\Http\Controllers;

use App\MatchEvent;
use Carbon\Carbon;
use ExcelReport;
use PdfReport;
use DB;

class ReportStateController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $events = MatchEvent::select('id', 'name', 'event_start', 'active')->orderBy('active', 'desc')->orderBy('created_at', 'desc')->get();
        return view("reports_state.reports_state", compact('events'));
    }

    public function generateReport($type, $event_id)
    {
        switch ($type) {
            case 'anulada':
                return $this->reportReversedAndReject($event_id);
                break;
            case 'cortesia':
                return $this->reportCourtesy($event_id);
                break;
            case 'vendida':
                return $this->reportTicketsSold($event_id);
                break;
            case 'consolidado':
                return $this->reportTicketsConsolidated($event_id);
                break;
        }
    }

    public static function clean($string)
    {
        if (!$string) {
            return "";
        }
        $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens.

        return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
    }

    public function reportTicketsConsolidated($event_id)
    {
        $title = "Reporte consolidado";
        $event = MatchEvent::find($event_id);

        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name . '(' . $event->id . ')',
            'Fecha evento' => $event->event_start,
            'Código evento' => $event->code,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets vendidos'
        ];

        $queryBuilder = DB::table('tickets')
            ->select('match_events.name', 'match_events.code', 'zsub.name as zone',  DB::raw('sum(tickets.price) as total'), DB::raw('count(tickets.id) as t_tickets'))
            ->join('ticket_mains', 'tickets.ticket_main_id', '=', 'ticket_mains.id')
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id) // Del evento
            ->where(function ($query) {
                $query->where('tickets.ticket_status_id', 1)  // Comprado
                    ->orWhere('tickets.ticket_status_id', 2); // Ingresado
            }) //
            ->where(function ($query) {
                $query->where('tickets.ticket_type_id', 1)  //  Venta libre
                    ->orWhere('tickets.ticket_type_id', 2) // Abonados
                    ->orWhere('tickets.ticket_type_id', 4); // Crédito
            })
            ->groupBy('zsub.id', 'match_events.name', 'match_events.code', 'zsub.name');

        $columns = [
            'Nombre Evento' => 'name',
            'Codigo evento' => 'code',
            'Tribuna' => 'zone',
            'Cant. Tickets' => 't_tickets',
            // 'Forma pago' => 'f_pago',
            'Total' => 'total'
        ];

        return PdfReport::of($title, $meta, $queryBuilder, $columns)
            ->editColumns(['Total'], [ // Mass edit column
                'class' => 'right bold'
            ])
            ->editColumns(['Cant. Tickets'], [ // Mass edit column
                'class' => 'right bold'
            ])
            ->showTotal([ // Used to sum all value on specified column on the last table (except using groupBy method). 'point' is a type for displaying total with a thousand separator
                'Total' => 'point', // if you want to show dollar sign ($) then use 'Total Balance' => '$'
                'Cant. Tickets' => 'point'
            ])
            ->download('Reporte consolidado - ' . $event->name);
    }

    public function reportTicketsSold($event_id)
    {
        $title = "Ticket vendidos";
        $event = MatchEvent::find($event_id);

        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets vendidos'
        ];

        // Query a mostrar en el reporte
        $queryBuilder = DB::table('tickets')
            ->select([
                'tickets.id', 'match_events.name', 'match_events.code', 'tickets.zone', 'tickets.code_ticket', 'tickets.code_seat', 'tickets.price', 'tickets.created_at AS fecha_compra',
                'users.first_name', 'users.last_name', 'users.document', 'ticket_types.name AS type_ticket', 's.zone_id', 'tickets.letter_seat as letter', 'z.zone_id as id_sub_zone', 'zsub.name as tribune'
            ])
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('users', 'tickets.user_id', '=', 'users.id')
            ->join('ticket_types', 'tickets.ticket_type_id', '=', 'ticket_types.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id) // Del evento
            ->where(function ($query) {
                $query->where('tickets.ticket_status_id', 1)  // Comprado
                    ->orWhere('tickets.ticket_status_id', 2); // Ingresado
            }) //
            ->where(function ($query) {
                $query->where('tickets.ticket_type_id', 1)  //  Venta libre
                    ->orWhere('tickets.ticket_type_id', 2) // Abonados
                    ->orWhere('tickets.ticket_type_id', 4); // Crédito
            });
        $columns = [
            'ID' => 'id',
            'Nombre Evento' => 'name',
            'Codigo evento' => 'code',
            'Tribuna' => 'tribune',
            'Sector' => 'zone',
            'Silla' => 'code_seat',
            'Fila' => 'letter',
            'Tipo de boleta' => 'type_ticket',
            // 'Código ticket' => 'code_ticket',
            'Precio' => 'price',
            'Fecha compra' => 'fecha_compra',
            // 'Nombre Usuario' => 'first_name',
            // 'Apellido Usuario' => 'last_name',
            // 'Documento Usuario' => 'document',
        ];

        $name = stripslashes(str_replace('/', '', $event->name));
        return ExcelReport::of($title, $meta, $queryBuilder, $columns)
            ->download($name . ' - Vendidas');
    }

    public function reportReversedAndReject($event_id)
    {
        $title = "Ticket reversados y anulados";
        $event = MatchEvent::find($event_id);
        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets Reversados y anulados'
        ];

        // Query a mostrar en el reporte
        $queryBuilder = DB::table('tickets')
            ->select([
                'tickets.id', 'match_events.name', 'match_events.code', 'tickets.zone', 'tickets.code_ticket', 'tickets.code_seat', 'tickets.price', 'tickets.created_at AS fecha_compra',
                'users.first_name', 'users.last_name', 'users.document', 'ticket_types.name AS type_ticket', 's.zone_id', 'tickets.letter_seat as letter', 'z.zone_id as id_sub_zone', 'zsub.name as tribune'
            ])
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('users', 'tickets.user_id', '=', 'users.id')
            ->join('ticket_types', 'tickets.ticket_type_id', '=', 'ticket_types.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id) // Del evento
            ->where(function ($query) {
                $query->where('tickets.ticket_status_id', 3)  // ANULADO
                    ->orWhere('tickets.ticket_status_id', 4); // REVERSADO
            });

        $columns = [
            'ID' => 'id',
            'Nombre Evento' => 'name',
            'Codigo evento' => 'code',
            'Tribuna' => 'tribune',
            'Sector' => 'zone',
            'Silla' => 'code_seat',
            'Fila' => 'letter',
            'Tipo de boleta' => 'type_ticket',
            // 'Código ticket' => 'code_ticket',
            'Precio' => 'price',
            'Fecha compra' => 'fecha_compra',
            // 'Nombre Usuario' => 'first_name',
            // 'Apellido Usuario' => 'last_name',
            // 'Documento Usuario' => 'document',
        ];

        $name = stripslashes(str_replace('/', '', $event->name));
        return ExcelReport::of($title, $meta, $queryBuilder, $columns)->download($name . ' - Reversados y anulados');
    }

    public function reportCourtesy($event_id)
    {
        $title = "Tickets de cortesía";
        $event = MatchEvent::find($event_id);
        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets cortesia'
        ];

        // Query a mostrar en el reporte
        $queryBuilder = DB::table('tickets')
            ->select([
                'tickets.id', 'match_events.name', 'match_events.code', 'tickets.zone', 'tickets.code_ticket', 'tickets.code_seat', 'tickets.price', 'tickets.created_at AS fecha_compra',
                'users.first_name', 'users.last_name', 'users.document', 'ticket_types.name AS type_ticket', 's.zone_id', 'tickets.letter_seat as letter', 'z.zone_id as id_sub_zone', 'zsub.name as tribune'
            ])
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('users', 'tickets.user_id', '=', 'users.id')
            ->join('ticket_types', 'tickets.ticket_type_id', '=', 'ticket_types.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id) // Del evento
            ->where(function ($query) {
                $query->where('tickets.ticket_status_id', 1)  // Comprado
                    ->orWhere('tickets.ticket_status_id', 2); // Ingresado
            }) //
            ->where(function ($query) {
                $query->where('tickets.ticket_type_id', 3);  // Cortesia
            });
        $columns = [
            'ID' => 'id',
            'Nombre Evento' => 'name',
            'Codigo evento' => 'code',
            'Tribuna' => 'tribune',
            'Sector' => 'zone',
            'Silla' => 'code_seat',
            'Fila' => 'letter',
            'Tipo de boleta' => 'type_ticket',
            // 'Código ticket' => 'code_ticket',
            'Precio' => 'price',
            'Fecha compra' => 'fecha_compra',
            // 'Nombre Usuario' => 'first_name',
            // 'Apellido Usuario' => 'last_name',
            // 'Documento Usuario' => 'document',
        ];

        $name = stripslashes(str_replace('/', '', $event->name));
        return ExcelReport::of($title, $meta, $queryBuilder, $columns)
            ->download($name . ' - Cortesias');
    }
}