exchange-2.0/app/Http/Controllers/Imports/ImportStatementInvoiceController.php

<?php

namespace App\Http\Controllers\Imports;

use DateTime;
use Carbon\Carbon;
use App\Models\User;
use App\Models\Company;
use App\Models\Segment;
use App\Models\Transaction;
use Illuminate\Http\Request;
use App\Models\SeasonalSegment;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\TransactionMappingLog;
use App\Classes\ValueObjects\Constants\HttpStatus;
use App\Classes\ValueObjects\Constants\ApprovalStatus;
use App\Classes\Modules\Imports\Services\GenericImport;
use App\Classes\ValueObjects\Response\ApiResponseObject;
use App\Classes\Modules\Accounting\Processors\ChecksBillNumber;
use App\Classes\Modules\Segments\Services\CreatesSeasonalSegment;
use App\Classes\Modules\Companies\Processors\AssignSegmentProcessor;
use App\Classes\Modules\Documents\DataTransferObjects\DocumentObject;
use App\Classes\Modules\Segments\DataTransferObjects\SeasonalSegmentObject;

class ImportStatementInvoiceController
{
    private $responseTitle;
    private $responseMessage;

    public function __construct() {
        $this->responseTitle = 'Import Invoice Mapping';
        $this->responseMessage = 'You have successfully imported invoice mapping';
    }

    public function mapping($row) {
        // Shipping Info
        // TOPUP -> map with transaction.bill_no
        if (str_starts_with($row['shipping_info'], 'TOPUP')) {
            // find in exchange first, if cannont then find in izyim
            foreach (['exchange','izyim'] as $system) {
                [$returnReference, $transactionDate] = $this->mappingTopUp($row, $system);
                if ($returnReference) {
                    // $row['mapped_result_reference'] = $data['owner_reference'];
                    // $row['payment_received_date'] = date('Y-m-d', strtotime($data['created_at']));
                    $row['mapped_result_reference'] = $returnReference;
                    $row['payment_received_date'] = $transactionDate ? date('d-m-Y', strtotime($transactionDate)) : null;
                    $row['mapped_status'] = 'success';
                    return $row;
                }
            }
        } 
        
        [$returnReference, $transactionDate] = $this->mappingExchange($row);
        if ($returnReference) {
            $row['mapped_result_reference'] = $returnReference;
            $row['payment_received_date'] = date('d-m-Y', strtotime($transactionDate));
            $row['mapped_status'] = 'success';
            return $row;
        }

        // if still unable to map, will try to check the shipping_info without TOPUP
        // foreach (['exchange','izyim'] as $system) {
        //     $returnReference = $this->mappingTopUp($row, $system);
        //     if ($returnReference) {
        //         $row['mapped_result_reference'] = $returnReference;
        //         $row['mapped_status'] = 'success';
        //     }
        // }

        return $row;
    }

    /**
     * @param Request $request
     * @return array
     */
    public function import(Request $request) : JsonResponse
    {
        try {
            ini_set('memory_limit', '-1'); 
            $importDate = date('Y-m-d H:i:s');
            $object = new DocumentObject('', $request->input('files'), '', ApprovalStatus::APPROVED, 'imports');
            $file = json_decode($object->getFiles()[0])->file_info->original->file;

            $import = new GenericImport();
            Excel::import($import, $file);
            $excelRows = $import->rows;
            $excelRows = $excelRows->toArray();

            $data = [];
            foreach ($excelRows as $row) {
                $row['mapped_result_reference'] = null;
                $row['payment_received_date'] = null;
                $row['mapped_status'] = 'failed';
                $row['date'] = in_array(gettype($row['date']), ['integer', 'double']) ? $this->changeExcelDate($row['date']) : date('Y-m-d', strtotime($row['date']));

                $row = $this->mapping($row);

                TransactionMappingLog::create([
                    'imported_date'=>$importDate,
                    'type' => 'invoices',
                    'data'=>$row,
                ]);
                array_push($data, $row);

                
                // if 5 digits -> exchange booking reference 
                // find transation
                // find statement_transaction_owners, and fill up the details 

                // if <5 digits, find the transaction id (order number in izyim), find the payment in izyim
                // find transation
                // find statement_transaction_owners, and fill up the details 

                // dd([
                //     'type'                 => $statementTransactionOwnerType,
                //     'system'               => $system,
                //     // 'owner_type'           => Transaction::class,
                //     // todo-new: make sure owner_type is a class
                //     'owner_type'           => $owner_type,
                //     'owner_id'             => $owner_id,
                //     'owner_reference'      => $owner_reference
                // ]);
                
                // $bankStatementTransaction->owners()->firstOrCreate([
                //     'type'                 => $statementTransactionOwnerType,
                //     'system'               => $system,
                //     // 'owner_type'           => Transaction::class,
                //     // todo-new: make sure owner_type is a class
                //     'owner_type'           => $owner_type,
                //     'owner_id'             => $owner_id,
                //     'owner_reference'      => $owner_reference
                // ]);


            }

            return $this->response($this->responseTitle, $this->responseMessage, HttpStatus::OK_WITH_MESSAGE, ['data'=>$data,'importedDate'=>$importDate]);
        }  catch (\Exception $exception){
            return $this->response('import invoice failed',$exception->getMessage(), ($exception->getCode()? $exception->getCode() : HttpStatus::SERVER_ERROR));
        }
    }

    public function response(String $responseTitle, String $responseMessage, int $httpStatus, ?array $data = []) : JsonResponse {
        return (new ApiResponseObject($responseTitle, $responseMessage, $httpStatus, $data))->handler();
    }

    private function mappingTopUp(Array $row, String $system) {
        try {
            if ($data = (App()->make(ChecksBillNumber::class))->execute($row['shipping_info'], $system)) {
                // if ($system == 'izyim' && isset($data['owner_reference'])) return $data;
                if ($system == 'izyim' && isset($data['owner_reference'])) return [$data['owner_reference'], null];
                
                if ($system == 'exchange') return $this->updateTransactionOwnerReference($data, $row['doc_no']);
            }
        } catch (\Throwable $th) {
            return false;
        }
    }

    private function mappingExchange(Array $row) {
        $date = $row['date'];
        $transaction = Transaction::getReceiverWithJoinStatementTransactionAndOwner($row)->select('transactions.*')->where('owner_reference', $row['shipping_info'])->first();
        
        if ($transaction && $transaction->count() == 0) {
            $transaction = Transaction::getReceiverWithJoinStatementTransactionAndOwner($row)->select('transactions.*')->where('statement_transactions.amount', $row['net_total'])->whereRaw("DATE(posting_date) = '$date'")->first();
        }

        if ($transaction && $transaction->count() == 0) {
            $transaction = Transaction::getReceiverWithJoinStatementTransactionAndOwner($row)->select('transactions.*')->whereRaw('FLOOR(statement_transactions.amount) = ?', [floor($row['net_total'])])->whereRaw("DATE(posting_date) = '$date'")->first();
        }

        if ($transaction && $transaction->count() > 0) {
            return $this->updateTransactionOwnerReference($transaction, $row['doc_no']);
        }
        return [false, false];
    }

    public function updateTransactionOwnerReference($transaction, String $docNo) {
        $transactionOwner = $transaction->transaction_owner;
        if ($transactionOwner) {
            $transactionOwner->update([
                'invoice_reference'=>$docNo,
                'status'=>ApprovalStatus::COMPLETED
            ]);
            return [$transactionOwner->owner_reference, $transaction->created_at];
        }
        return [false, false];
    }

    public function changeExcelDate($date)
    {
        $unixTime = (($date - 25569) * 86400);
        $date = new DateTime("@$unixTime");
        return $date->format('Y-m-d'); // Change the format to 'Y-m-d'
    }
}