exchange-2.0/app/Classes/Modules/Accounting/ControllersLogic/ImportAmbankStatementLogic.php

<?php

namespace App\Classes\Modules\Accounting\ControllersLogic;


use App\Classes\Exceptions\MalformedRequestException;
use App\Classes\General\Abstracts\AbstractControllerLogic;
use App\Classes\Modules\Documents\DataTransferObjects\DocumentObject;
use App\Classes\ValueObjects\Constants\ApprovalStatus;
use App\Models\AccountStatement;
use App\Models\StatementAccount;
use App\Models\StatementTransaction;
use Exception;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;

class ImportAmbankStatementLogic extends AbstractControllerLogic
{

    /**
     * @return array
     */
    protected function notification(): array
    {
        return [
            'title' => 'Import Ambank Statement Transactions Details',
            'message' => 'You have successfully imported the Ambank Statement Transactions Details'
        ];
    }


    /**
     * @param Request $request
     * @return JsonResponse
     * @throws MalformedRequestException
     */
    public function logic(Request $request): JsonResponse
    {
        $requiredHeaders = ['Date', 'Time', 'Description', 'Recipient Reference', 'Other Payment Details', 'Transfer Type', 'Inward Amount', 'Outward Amount', 'Balance'];

        $object = new DocumentObject('', $request->input('files'), '', ApprovalStatus::APPROVED, 'imports');

        foreach ($object->getFiles() as $file) {
            $collection = Excel::toCollection(null, json_decode($file)->file_info->original->file, null, null, true);

            // check whether the csv is format we expect for
            foreach ($collection as $sheet_no => $sheet) {
                $header_mapping_result = [];
                foreach ($sheet as $row_no => $row) {
                    if ($row[0] !== $requiredHeaders[0]) {
                        continue;
                    }

                    foreach ($requiredHeaders as $col_no => $header) {
                        if ($row[$col_no] !== $header) {
                            $header_mapping_result[$col_no] = false;
                        } else {
                            $header_mapping_result[$col_no] = true;
                        }
                    }

                    if (count($header_mapping_result) === 9) {
                        break;
                    }
                }

                if (count($header_mapping_result) === 0 || in_array(false, $header_mapping_result)) {
                    throw new MalformedRequestException("Sheet {$sheet_no} format is not correct");
                }
            }

            $data_start_from = $sheet->search(function ($row, $key) {
                return $row[0] === 'Date';
            }) + 1;
            $data_end_at = $sheet->keys()->last();
            $descending = true;

            for ($i = $data_end_at; $i > 0; $i--) {
                $parse_date = Carbon::createFromFormat('d/m/Y', $sheet[$i][0]);
                if ($parse_date && $parse_date->format('d/m/Y') === $sheet[$i][0]) {
                    $data_end_at = $i;
                    break;
                }
            }

            if (Carbon::createFromFormat('d/m/Y', $sheet[$data_start_from][0]) > Carbon::createFromFormat('d/m/Y', $sheet[$data_end_at][0])) {
                $descending = true;
            } else {
                $descending = false;
            }

            $date_from = Carbon::createFromFormat('d/m/Y', $sheet[$descending ? $data_end_at : $data_start_from][0])->format('Y-m-d');
            $date_to = Carbon::createFromFormat('d/m/Y', $sheet[$descending ? $data_start_from : $data_end_at][0])->format('Y-m-d');
            $total_rows = abs($data_end_at - $data_start_from) + 1;
            $begin_balance = floatval($sheet[$descending ? $data_end_at : $data_start_from][8]);
            $end_balance = floatval($sheet[$descending ? $data_start_from : $data_end_at][8]);
            $total_debit = 0;
            $total_credit = 0;

            $account = StatementAccount::where('number', 8881040198515)->first();

            if (!$account) {
                throw new Exception("Statement Account for AMBANK not found.");
            }

            $statement = AccountStatement::whereDate('date_from', $date_from)
                ->whereDate('date_to', $date_to)
                ->where('total_rows', $total_rows)
                ->where('begin_balance', $begin_balance)
                ->where('end_balance', $end_balance)
                ->first();


            if (!$statement) {
                $statement = new AccountStatement([
                    'statement_account_id' => $account->id,
                    'date_from' => $date_from,
                    'date_to' => $date_to,
                    'total_rows' => $total_rows,
                    'begin_balance' => $begin_balance,
                    'end_balance' => $end_balance,
                ]);
                $statement->save();
            }

            foreach ($collection as $sheet_no => $sheet) {
                foreach ($sheet as $row_no => $row) {
                    if ($row_no < $data_start_from || $row_no > $data_end_at) {
                        continue;
                    }

                    $posting_date = Carbon::createFromFormat('d/m/Y', $row[0])->format('Y-m-d');
                    $posting_time = Carbon::parse($row[1])->format('H:i:s');
                    $transaction_description = trim($row[2]);
                    $transaction_description_2 = trim($row[3]);
                    $transaction_description_3 = trim($row[4]);
                    $transaction_description_4 = trim($row[5]);
                    $inward_amount = floatval(trim($row[6]));
                    $outward_amount = floatval(trim($row[7]));
                    $amount = $inward_amount == 0 ? $outward_amount : $inward_amount;

                    if ($inward_amount == 0) {
                        $total_debit += 1;
                    } else {
                        $total_credit += 1;
                    }

                    $balance = floatval(trim($row[8]));

                    // Check if the transaction already exists for any statement
                    $existingTransaction = StatementTransaction::where('posting_date', ($posting_date . ' ' . $posting_time))
                        ->where('amount', $amount)
                        ->where('transaction_description', $transaction_description)
                        ->whereRaw("CAST(REPLACE(end_balance,',','') AS DECIMAL(15,2)) = ?", [$balance])
                        ->first();

                    if (!$existingTransaction) {
                        $transaction = new StatementTransaction([
                            'account_statement_id' => $statement->id,
                            'posting_date' => $posting_date . ' ' . $posting_time,
                            'transaction_description' => $transaction_description,
                            'transaction_description_2' => $transaction_description_2,
                            'transaction_description_3' => $transaction_description_3,
                            'transaction_description_4' => $transaction_description_4,
                            'amount' => $amount,
                            'end_balance' => $balance,
                        ]);

                        $transaction->save();
                    }
                }

                $statement->total_amount = $total_debit ?: $total_credit;
                $statement->save();
            }
        }

        return $this->response([]);
    }
}