GuestPaymentReportRepository.php

<?php

namespace Modules\Reports\Repositories\GuestPayment;

use Carbon\Carbon;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Modules\Booking\Models\Booking;
use Modules\Core\Repositories\BaseRepository;
use Modules\MasterData\Models\Place;

class GuestPaymentReportRepository extends BaseRepository
{
    const DEAL_TABLE = 'deals';
    const BOOKING_TABLE = 'bookings';
    const BOOKING_GUEST_TABLE = 'booking_guests';
    const GUEST_TABLE = 'guests';
    const PROPOSAL_TABLE = 'proposals';

    /**
     * Pagination Keys
     *
     * @var array
     */
    private static $filterKeys = ['total', 'from', 'to', 'last_page', 'per_page', 'current_page'];

    /**
     * @var mixed
     */
    protected $tmDatabaseName;

    /**
     * @var mixed
     */
    protected $masterDatabaseName;

    /**
     * @var mixed
     */
    protected $tmConnectionName;

    /**
     * @var
     */
    protected $db;

    /**
     * GuestPaymentReportRepository constructor.
     */
    public function __construct()
    {
        $this->tmDatabaseName = config('reports:config.jw_database_name');
        $this->masterDatabaseName = config('reports:config.master_database_name');
        $this->tmConnectionName = config('booking:config.database.connection');
        $this->db = DB::connection($this->tmConnectionName);
    }

    /**
     * @param $filters
     * @return \Illuminate\Support\Collection
     */
    public function getGuestPaymentData($filters)
    {
        $data = $this->formatData($filters);

        $pagination = $filters['pagination'];
        unset($filters['pagination']);
        $perPage = (int)$pagination['per_page'];
        $currentPage = (int)$pagination['current_page'];

        $results = $this->arrayPaginator($data, $perPage, $currentPage);
        $reportData = $results->toArray();
        $pagination = [];
        foreach (self::$filterKeys as $key) {
            $pagination[$key] = $reportData[$key];
        }
        unset($reportData);

        return collect(
            array(
                'reports_data' => $results->values(),
                'pagination' => $pagination
            )
        );
    }

    /**
     * @param $filters
     * @return static
     */
    public function formatData($filters)
    {
        $consultantData = collect(Cache::tags('reports')->get('tc_users'));

        return collect($this->getBaseQuery($filters, $consultantData))
            ->map(function ($query) use ($consultantData, $filters) {
                $booking = Booking::find($query->booking_id);

                if ($booking->is_full_payment) {
                    $query->balance_due_date = $booking->fullTripInvoiceDetails ? $booking->fullTripInvoiceDetails->first()->due_date : null;
                } else {
                    $query->balance_due_date = $booking->balanceInvoiceDetails->first() ? $booking->balanceInvoiceDetails->first()->due_date : null;
                }

                if (!is_null($query->balance_due_date)) {
                    $query->balance_due_date = !is_null($query->balance_due_date) ?  Carbon::parse($query->balance_due_date) : null;
                }

                if (array_key_exists('balance_due_date', $filters)) {
                    $fromDate = $filters['balance_due_date']['from'];
                    $toDate = $filters['balance_due_date']['to'];
                    if (!is_null($fromDate) && !is_null($toDate)) {
                        $fromDate = Carbon::parse($fromDate);
                        $dueDate = $query->balance_due_date;
                        $toDate = Carbon::parse($toDate);

                        if (!($dueDate->gte($fromDate) && $dueDate->lte($toDate))) {
                            return null;
                        }
                    }
                }

                $query->total_trip_cost = $booking->invoices->pluck('amount')->sum();

                $amountPaid = $booking->paidPayments->reduce(function ($first, $second) {
                    return $first + $second->received_amount;
                }, 0);

                $query->amount_paid = $amountPaid;
                $query->amount_due = $query->total_trip_cost - $amountPaid;
                $query->diff_amount_received = $booking->pendingFinancePayment ? $booking->pendingFinancePayment->pluck('amount')->sum() : 0;

                $proposal = $booking->proposal;
                $destination = $proposal->countries->map(function ($country) {
                    return $country->parent->name;
                })->unique()->toArray();

                if (array_key_exists('destinations', $filters) && count($filters['destinations']) > 0) {
                    $places = collect($filters['destinations'])
                        ->map(function ($item) {
                            return Place::where('uuid', $item)->firstOrFail()->name;
                        })->search(function ($item) use ($destination) {
                            return in_array($item, $destination);
                        });

                        if ($places === false) {
                            return null;
                        }
                }

                $query->destination = implode(', ', $destination);
                $query->consultant_name = $consultantData->where('id', $query->consultant_id)->values()->first()['name'];

                $guestCountry = $booking->primaryGuest->guest->country;

                if (in_array($guestCountry, ['us', 'ca']) && in_array($query->currency_code, ['USD', 'CAD'])) {
                    $bookingEntity = 'US';
                } else {
                    $bookingEntity = 'DE';
                }

                if (array_key_exists('booking_entities', $filters) && count($filters['booking_entities']) > 0) {
                    $entity = collect($filters['booking_entities'])
                        ->search(function ($item) use ($bookingEntity) {
                            $item = strtoupper($item);
                            return $item === $bookingEntity;
                        });

                    if ($entity === false) {
                        return null;
                    }
                }

                $query->booking_entity = $bookingEntity;
                $query->trip_start_date = !is_null($query->trip_start_date) ? Carbon::parse($query->trip_start_date) : null;
                $query->confirmation_date = !is_null($query->confirmation_date) ? Carbon::parse($query->confirmation_date) : null;

                if (array_key_exists('amount', $filters)) {
                    $operator = $filters['amount']['operator'];
                    $amount = (int) $filters['amount']['value'];

                    if (!is_null($operator)) {
                        switch ($operator) {
                            case 'lte':
                                if ($query->amount_due <= $amount) {
                                    return $query;
                                }

                                return null;
                                break;
                            case 'gte':
                                if ($query->amount_due >= $amount) {
                                    return $query;
                                }

                                return null;
                                break;
                            case 'eq':
                                if ($query->amount_due == $amount) {
                                    return $query;
                                }

                                return null;
                                break;
                            default:
                                return $query;
                                break;
                        }
                    }
                }

                return $query;
            })->reject(function ($item) {
                return empty($item);
            });
    }

    /**
     * @param $filters
     * @param $consultantData
     * @return mixed
     */
    protected function getBaseQuery($filters, $consultantData)
    {
        return $this->db
            ->table(self::BOOKING_TABLE . ' as jwb')
            ->join(self::DEAL_TABLE . ' as jwd', 'jwb.deal_id', '=', 'jwd.id')
            ->join(self::BOOKING_GUEST_TABLE . ' as jwbg', function ($query) {
                $query->on('jwbg.booking_id', '=', 'jwb.id')
                    ->where('jwbg.is_primary', '=', DB::raw(1));
            })
            ->join(self::GUEST_TABLE . ' as jwg', 'jwbg.guest_id', '=', 'jwg.id')
            ->join(self::PROPOSAL_TABLE . ' as jwp', function ($query) {
                $query->on('jwp.deal_id', '=', 'jwd.id')
                    ->where('jwp.status', '=', DB::raw(1))
                    ->where('jwp.is_confirmed', '=', DB::raw(1))
                    ->where('jwp.draft', '=', DB::raw(0));
            })
            ->when(array_key_exists('trip_start_date', $filters), function ($query) use ($filters) {
                if (is_null($filters['trip_start_date']['from']) && is_null($filters['trip_start_date']['to'])) {
                    return $query;
                }
                $fromDate = Carbon::parse($filters['trip_start_date']['from'])->startOfDay()->toDateTimeString();
                $toDate = Carbon::parse($filters['trip_start_date']['to'])->endOfDay()->toDateTimeString();

                return $query->whereBetween('jwp.start_date', [$fromDate, $toDate]);
            })
            ->when(array_key_exists('deal_id', $filters) && $filters['deal_id'] !== 0, function ($query) use ($filters) {
                return $query->where('jwd.deal_id', '=', $filters['deal_id']);
            })
            ->when(array_key_exists('confirmation_date', $filters), function ($query) use ($filters) {
                if (is_null($filters['confirmation_date']['from']) && is_null($filters['confirmation_date']['to'])) {
                    return $query;
                }

                $fromDate = Carbon::parse($filters['confirmation_date']['from'])->startOfDay()->toDateTimeString();
                $toDate = Carbon::parse($filters['confirmation_date']['to'])->endOfDay()->toDateTimeString();

                return $query->whereBetween('jwb.created_at', [$fromDate, $toDate]);
            })
            ->when(array_key_exists('travel_consultants', $filters), function ($query) use ($filters, $consultantData) {
                if (count($filters['travel_consultants']) === 0) {
                    return $query;
                }

                $filterIDs = collect($filters['travel_consultants'])
                    ->map(function ($item) use ($consultantData) {
                        return $consultantData->where('uuid', $item)->first()['id'];
                    })->unique()->toArray();

                if (count($filterIDs) > 0) {
                    return $query->whereIn('jwd.consultant_id', $filterIDs);
                }
            })
            ->when(!array_key_exists('travel_consultants', $filters), function ($query) use ($filters, $consultantData) {
                $testTC = $filters['include_test_tc'];
                $consultantIDs = $consultantData->reject(function ($item) {
                    return $item['is_test_tc'] === 1;
                })->pluck('id')->all();

                if (!$testTC) {
                    return $query->whereIn('jwd.consultant_id', $consultantIDs);
                }

                return $query;
            })
            ->when(array_key_exists('trip_coordinators', $filters), function ($query) use ($filters) {
                if (count($filters['trip_coordinators']) === 0) {
                    return $query;
                }

                return $query->whereIn('jwd.coordinator_id', $filters['trip_coordinators']);
            })
            ->when(array_key_exists('handover_status', $filters), function ($query) use ($filters) {
                if (count($filters['handover_status']) === 0) {
                    return $query;
                }

                return $query->whereIn('jwd.handover_status_id', $filters['handover_status']);
            })
            ->select([
                'jwd.deal_id as deal_id',
                'jwp.key as proposal_uuid',
                'jwp.id as proposal_id',
                'jwd.no_of_passengers as no_of_people',
                'jwd.currency_code as currency_code',
                'jwd.consultant_id as consultant_id',
                'jwb.id as booking_id',
                'jwp.start_date as trip_start_date',
                'jwb.created_at as confirmation_date',
                'jwd.handover_status_id as handover_status_id',
                'jwd.coordinator_id as coordinator_id',
            ])
            ->selectRaw('concat(jwg.first_name, \' \', jwg.last_name) as primary_guest')
            ->selectRaw('lower(hex(jwb.uuid)) as booking_uuid')
            ->orderBy('trip_start_date', 'desc')
            ->get();
    }

    /**
     * @param       $collection
     * @param int   $perPage
     * @param int   $currentPage
     * @param array $options
     * @return \Illuminate\Pagination\LengthAwarePaginator
     */
    protected function arrayPaginator($collection, $perPage = 20, $currentPage = 1, $options = [])
    {
        $offset = ($currentPage * $perPage) - $perPage;
        $items = $collection->slice($offset, $perPage);
        return new LengthAwarePaginator($items, $collection->count(), $perPage, $currentPage, $options);
    }
}