exchange-2.0/app/Classes/Modules/Exports/Services/ExportsSalesInvoiceWithRefundReport.php

<?php

namespace App\Classes\Modules\Exports\Services;

use App\Classes\ValueObjects\Constants\TransactionType;
use App\Classes\ValueObjects\Constants\ApprovalStatus;
use App\Models\Booking;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use App\Classes\Modules\Bookings\Services\CalculatesBookingRefundAmount;
use App\Classes\Modules\Bookings\Services\CalculatesBookingRefundServiceCharge;
use App\Classes\ValueObjects\Constants\KVPKey;
use Carbon\Carbon;
use Illuminate\Support\Facades\Log;

class ExportsSalesInvoiceWithRefundReport implements FromQuery, WithHeadings, WithHeadingRow, WithMapping, ShouldAutoSize
{
    use Exportable;

    protected $startDate;
    protected $endDate;

    public function __construct($startDate = null, $endDate = null) {
        $this->startDate = $startDate ? Carbon::parse($startDate)->startOfDay() : Carbon::now()->subMonths(1);
        $this->endDate = $endDate ? Carbon::parse($endDate)->endOfDay() : Carbon::now();
    }

    public function headings(): array
    {
        return [
            'DocNo',
            'DocDate',
            'DebtorCode',
            'Ref',
            'ShipInfo',
            'AccNo',
            'DetailDescription',
            'FurtherDescription',
            'Classification',
            'DeptNo',
            'Qty',
            'UnitPrice',
            'SubmitEinvoice',
            'ConsolidatedEInvoice',
        ];
    }

    /**
     * @return \Illuminate\Support\Collection|mixed
     */
    public function query()
    {
        $startDate = $this->startDate;
        $endDate = $this->endDate;

        Log::info('ExportsSalesInvoiceWithRefundReport startDate: ' . $startDate->format('Y-m-d H:i:s'));
        Log::info('ExportsSalesInvoiceWithRefundReport endDate: ' . $endDate->format('Y-m-d H:i:s'));

        // return Booking::whereIn('status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED, ApprovalStatus::SUSPENDED])
        // ->whereHas('transactions', function ($query) use ($startDate, $endDate) {
        //     $query->payments()
        //     ->where('status', ApprovalStatus::REFUNDED)
        //     ->whereBetween('created_at', [$startDate, $endDate])
        //     ->latest('created_at');
        // });

        return Booking::whereIn('status', [
            ApprovalStatus::APPROVED,
            ApprovalStatus::COMPLETED,
            ApprovalStatus::SUSPENDED,
        ])
        ->whereHas('transactions', function ($transactionQuery) use ($startDate, $endDate) {
            $transactionQuery->payments()
                ->whereBetween('created_at', [$startDate, $endDate])
                ->latest('created_at')
                ->whereHas('transactions', function ($refundQuery) {
                    $refundQuery->refunds()
                        ->whereIn('status', [ApprovalStatus::APPROVED]);
                });
        });
    }

    /**
     * @param Booking $booking
     * @return array
     */
    public function map($booking): array
    {
        Log::info('ExportsSalesInvoiceWithRefundReport booking : ' . json_encode($booking));
        $records = [];
        $averageCurrencyRate = 0;
        $currencyId = 0;

        $purchaseOrder = $booking->transactions()->where('type', TransactionType::PURCHASE_ORDER)->first();
        $company = $booking->company()->first();

        // $lastPaymentTransaction = $booking->transactions()->payments()->complete()->latest()->first();
        // $lastPaymentTransaction = $booking->transactions()->payments()->where('status', ApprovalStatus::REFUNDED)->latest()->first();
        $lastPaymentTransaction = $booking->transactions()->payments()->whereIn('status', [ApprovalStatus::COMPLETED, ApprovalStatus::REFUNDED])->latest()->first();
        if(!$lastPaymentTransaction){
            return $records;
        }

        $documentDate = $lastPaymentTransaction->created_at;
        if ($documentDate < $this->startDate || $documentDate > $this->endDate) {
             return $records;
        }

        if($company->e_invoice === 1){
            $documentDate = $documentDate->copy()->endOfMonth();
        }

        $invoiceTransaction = $booking->transactions()->where('type', TransactionType::INVOICE)->complete()->first();
        // $invoiceTransaction = $booking->transactions()->where('type', TransactionType::INVOICE)->first();

        if (!$invoiceTransaction) {
            Log::info('ExportsSalesInvoiceWithRefundReport booking NO invoice : ' . json_encode($booking));
        }

        $currencyId = $booking->fix_currency_id;

        $subtotal = 0;
        $displayedSubtotal = 0;
        $totalPayment = 0;
        $averageCurrencyRate = $invoiceTransaction ? $invoiceTransaction->currency_rate : 0;
        $paymentSum = $booking->transactions()
            ->where('type', TransactionType::PAYMENT)
            ->where('status', ApprovalStatus::COMPLETED)
            ->get()
            ->sum(function ($transaction) {
                return round($transaction->amount, 2);
            });
        if ($paymentSum){
            $averageCurrencyRate = $booking->transactions()
            ->where('type', TransactionType::PAYMENT)
            ->where('status', ApprovalStatus::COMPLETED)
            ->get()
            ->sum(function ($transaction) {
                return $transaction->currency_rate;
            }) / $booking->transactions()
            ->where('type', TransactionType::PAYMENT)
            ->where('status', ApprovalStatus::COMPLETED)
            ->count();

            // $refundedAmount = (App()->make(CalculatesBookingRefundAmount::class))->execute($booking, 1);
            // $refundedServiceCharge = (App()->make(CalculatesBookingRefundServiceCharge::class))->execute($booking, 1);
            // $totalPayment = $paymentSum - $refundedAmount - $refundedServiceCharge;

            $totalPayment = $paymentSum;
        }

        $formattedDocumentDate = Carbon::parse($documentDate)->format('m/d/Y');

        $docNo = '<<New>>';
        $firstItem = true;
        if($invoiceTransaction){
            $invoiceTransactionKVP = $invoiceTransaction->attributesKVP()->where('key', KVPKey::AUTOCOUNT_DOCNO_INVOICE)->first();
            if($invoiceTransactionKVP){
                $docNo = $invoiceTransactionKVP->value;
            }
            else {
                $bookingKVP = $booking->attributesKVP()->where('key', KVPKey::AUTOCOUNT_DOCNO_INVOICE)->first();
                $docNo = $bookingKVP ? $bookingKVP->value :'<<New>>';
            }
        }

        $transactionDetails = $purchaseOrder ? $purchaseOrder->transactionDetails : null;
        if($transactionDetails){
            foreach ($transactionDetails as $detail) {
                $displayUnitPrice = 0;
                if($averageCurrencyRate && $currencyId){
                    $exactUnitPrice = ($currencyId) === 1 ? $detail->price : bcdiv($detail->price, $averageCurrencyRate, 7);
                    $displayUnitPrice = round($exactUnitPrice, 2);
                    $itemTotal = bcmul($exactUnitPrice, $detail->quantity, 5);
                    $displayedItemTotal = round(bcmul($displayUnitPrice, $detail->quantity, 7), 2);
                    $displayedSubtotal = bcadd($displayedSubtotal, $displayedItemTotal, 2);
                    $subtotal = bcadd($subtotal, $itemTotal, 5);
                }


                $records[] = [
                    $firstItem ? $docNo : '',
                    $formattedDocumentDate,
                    $company->debtor,
                    $invoiceTransaction ? $invoiceTransaction->bill_no : '',
                    $booking->marking,
                    '500-0000',
                    'PRODUCT NAME :',
                    $detail->product_name,
                    '022',
                    'C',
                    $detail->quantity,
                    $displayUnitPrice ? number_format($displayUnitPrice, 2): 0,
                    $firstItem ? 'T' : '',
                    $firstItem ? ($company->e_invoice ? 'F' : 'T') : ''
                ];

                if($firstItem) {
                    $firstItem = false;
                }
            }

            // Voucherify - Starts
            $voucherRedemption = $lastPaymentTransaction->voucherRedemption;
            if($voucherRedemption){
                $voucherDiscount = $voucherRedemption ? bcmul((string)$voucherRedemption->value, "-1", 2) : "0";
                $voucher = $voucherRedemption->voucher;
                $category = $voucher->campaign ? $voucher->campaign->category : null;
                $records[] = [
                    '',
                    $formattedDocumentDate,
                    $company->debtor,
                    $invoiceTransaction ? $invoiceTransaction->bill_no : '',
                    $booking->marking,
                    $category === 'Compensation Voucher' ? '1000-000' : '949-2000',
                    'PRODUCT NAME :',
                    $voucher->code,
                    '022',
                    'C',
                    '1',
                    $voucherDiscount ? number_format($voucherDiscount, 2): '0',
                    '',
                    ''
                ];
            }
            // Voucherify - Ends

            // Service Charge - Starts
            $serviceCharge = 0;
            if (!$totalPayment && $invoiceTransaction) {
                $serviceCharge = $invoiceTransaction->service_charge;
            }
            else {
                $serviceCharge = $booking->transactions()
                    ->where('type', TransactionType::PAYMENT)
                    ->where('status', ApprovalStatus::COMPLETED)
                    ->get()
                    ->sum(function ($transaction) {
                        return $transaction->service_charge;
                    });
            }

            $records[] = [
                '',
                $formattedDocumentDate,
                $company->debtor,
                $invoiceTransaction ? $invoiceTransaction->bill_no : '',
                $booking->marking,
                '500-0000',
                'PRODUCT NAME :',
                'Service Charge',
                '022',
                'C',
                '1',
                $serviceCharge ? number_format($serviceCharge, 2): '0',
                '',
                ''
            ];
            // Service Charge - Ends

            // Adjustment - Starts
            if($invoiceTransaction){
                $adjustment = 0;
                $voucherRedemption = $lastPaymentTransaction->voucherRedemption;
                $voucherDiscount = $voucherRedemption ? bcmul((string)$voucherRedemption->value, "-1", 2) : "0";

                $displayedSubtotal = is_numeric($displayedSubtotal) ? sprintf('%F', $displayedSubtotal) : '0';
                $serviceCharge = is_numeric($serviceCharge) ? sprintf('%F', $serviceCharge) : '0';
                $tax = is_numeric($invoiceTransaction->tax) ? sprintf('%F', $invoiceTransaction->tax) : '0';
                $voucherDiscount = is_numeric($voucherDiscount) ? sprintf('%F', $voucherDiscount) : '0';

                $displayedTotal = bcadd(
                    bcadd(
                        bcadd($displayedSubtotal, $serviceCharge, 5),
                        $tax,
                        5
                    ),
                    $voucherDiscount,
                    5
                );

                $expectedTotal = bcadd(bcadd(bcadd($subtotal, $serviceCharge, 5), $invoiceTransaction->tax, 5), $voucherDiscount, 5);
                $adjustment = bcsub($expectedTotal, $displayedTotal, 5);

                if ($totalPayment) {
                    $expectedTotal = $totalPayment;
                    $adjustment = bcsub($expectedTotal, $displayedTotal, 5);
                }

                $records[] = [
                    '',
                    $formattedDocumentDate,
                    $company->debtor,
                    $invoiceTransaction ? $invoiceTransaction->bill_no : '',
                    $booking->marking,
                    '500-0000',
                    'PRODUCT NAME :',
                    'Adjustment',
                    '022',
                    'C',
                    '1',
                    $adjustment ? number_format($adjustment, 2): '0',
                    '',
                    ''
                ];
            }
            // Adjustment - Ends
        }
        else{
            Log::info('ExportsSalesInvoiceWithRefundReport EMPTY RECORD for booking marking: ' . $booking->marking);
            $records[] = [
                '<<New>>',
                $formattedDocumentDate,
                $company->debtor,
                $invoiceTransaction ? $invoiceTransaction->bill_no: '',
                $booking->marking,
                '500-0000',
                'PRODUCT NAME :',
                'First Mile Delivery',
                '022',
                'C',
                '1',
                '0',
                '',
                ''
            ];
        }
        return $records;
    }
}