Untitled

<?php

namespace App\Imports\ImportDeposit;

use App\Contracts\BankStatementItemRepositoryContract;
use App\Contracts\CompanyImportedDepositRepositoryContract;
use App\Imports\ImportDeposit\Contracts\BCABankImportDepositContract;
use App\Services\ValidateImportDepositService;
use App\Services\BankFormatService;
use App\Support\BridgenoteTraitCollection;
use App\Models\BankStatement;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Excel;
use Illuminate\Support\Carbon;

#[\AllowDynamicProperties]
class BCABankImportDeposit extends BankImportDeposit implements BCABankImportDepositContract
{
    private const BEGIN_LINE_CSV = 7;
    private const REGEX_D_M_Y_FORMAT = "/^\b\d{1,2}[-\/]\d{1,2}[-\/]\d{2,4}\b$/";
    private const REGEX_D_M_FORMAT = "/^\b\d{1,2}[-\/]\d{1,2}\b$/";
    private const AMOUNT_MAX = 9999999999999.99;
    private const AMOUNT_MIN = -9999999999999.99;
    private const EXCEL_SYMBOL_SLIP_DATE = 'A';
    private const EXCEL_CELL_PERIOD = 'A5';
    private const EXCEL_SYMBOL_AMOUNT = 'D';
    private const EXCEL_SYMBOL_REMARK = 'B';
    private const COLUMN_SAVE_ERRORS = 'G';

    private $bankStatementItemRepository;
    private $lengthBankStatementItemsRemarks;
    private $lengthBankStatementItemsDefault;
    private $validateImportDepositService;
    private $companyImportedDepositRepository;
    private $dataFormFileCSV;
    private $bankFormatService;
    private $termBeginMonth;
    private $termEndMonth;
    private $countryId;

    public function __construct
    (
        BankStatementItemRepositoryContract $bankStatementItemRepository,
        BankFormatService $bankFormatService,
        CompanyImportedDepositRepositoryContract $companyImportedDepositRepository
    )
    {
        $this->bankStatementItemRepository = $bankStatementItemRepository;
        $this->bankFormatService = $bankFormatService;
        $this->companyImportedDepositRepository = $companyImportedDepositRepository;
        $this->termBeginMonth = session('selectedterm_beginmonth');
        $this->termEndMonth = session('selectedterm_endmonth');
        $this->countryId = session('selectedcompany_country_id');
    }

    private function setSheet($reader, $uploadedFile)
    {
        $this->sheet = $reader->load($uploadedFile)->getActiveSheet();
    }

    private function getSheet()
    {
        return $this->sheet;
    }

    private function setDataFromBankStatement()
    {
        $bank_id = session('import_deposit_bank_id');
        $deposit_type_id = session('import_deposit_deposit_type_id');

        // get bank_statement
        $bank_statement = $this->bankFormatService->getBankStatementsBankIdAndByDepositType($bank_id, $deposit_type_id);

        if (empty($bank_statement)) {
            return response()->json(['result' => false, 'error_message' => __('messages.1088'), 'show_message' => true]);
        }

        if ($bank_statement instanceof BankStatement) {
            $data_begin_line = $bank_statement->data_begin_line;
            $data_begin_row = $bank_statement->data_begin_row;
            $data_begin_excelsymbol = $bank_statement->data_begin_excelsymbol;
            $data_end_excelsymbol = $bank_statement->data_end_excelsymbol;
        }

        $this->line_start = $data_begin_line;
        $this->line_end = $data_begin_row;
        $this->data_start_excel = $data_begin_excelsymbol;
        $this->data_end_excel = $data_end_excelsymbol;
    }

    private function validateLineExcel($line)
    {
        $errors_of_line = [];

        // slip_date
        $dateTime = $this->getSheet()->getCell(self::EXCEL_SYMBOL_SLIP_DATE . $line)->getFormattedValue();
        // check format DM or MD
        $existDate = preg_match(self::REGEX_D_M_FORMAT, $dateTime);
        if ($existDate) {
            $dataDate = [
                'slip_date' => $dateTime,
                'period' => $this->sheet->getCell(self::EXCEL_CELL_PERIOD)->getValue(),
            ];
            $dateTime = $this->getSlipDate($dataDate);
        }

        $slip_date = !empty($dateTime) ? date('d-m-Y', strtotime($dateTime)) : '';
        $errors_of_line[] = $this->validateSlipDate($slip_date);

        // debit/credit
        $amount = $this->getCreditDebit($line);

        $errors_of_line[] = $this->validateByReceiptAndPayment($amount['credit'], $amount['debit']);

        //remark
        $remarks = $this->getDataRemarkExcel($line);

        if (!$this->isRemarks($remarks)) {
            $errors_of_line[] = trans('labels.60-B-37', ['length' => $this->lengthBankStatementItemsRemarks]);
        }

        return implode(",", array_filter($errors_of_line));
    }

    private function getCreditDebit($line)
    {
        $dataAmount = $this->getSheet()->getCell(self::EXCEL_SYMBOL_AMOUNT . $line)->getValue();
        $receiptValue = '';
        $paymentValue = '';
        if (!empty($dataAmount)) {
            $amount = explode(' ', $dataAmount);
            if (!empty($amount[1]) && $amount[1] == 'CR') {
                $receiptValue = $amount[0];
            }
            if (!empty($amount[1]) && $amount[1] == 'DB') {
                $paymentValue = $amount[0];
            }
        }

        return [
            'credit' => $receiptValue,
            'debit' => $paymentValue,
        ];
    }
    public function getDataRemarkExcel($line)
    {
        return $this->getSheet()->getCell(self::EXCEL_SYMBOL_REMARK . $line)->getValue();
    }

    private function validateContentExcelFile()
    {
        $messages_errors = [];
        $is_errors_excel = false;
        $highestRow = $this->getSheet()->getHighestDataRow() - ($this->hasFooter() ? 4 : 0);
        for ($line = $this->line_start; $line <= $highestRow; $line++) {
            $message = $this->validateLineExcel($line);
            $messages_errors[] = array('line' => $line, 'message' => $message);

            if (!empty($message)) {
                $is_errors_excel = true;
            }
        }

        if ($is_errors_excel) {
            return array('valid' => false, 'list_errors' => $messages_errors);
        }
        return array('valid' => true);
    }

    private function saveLegerWithDataOfExcel()
    {
        DB::beginTransaction();
        try {
            $data_import_deposit = [];
            $highestRow = $this->getSheet()->getHighestDataRow() - ($this->hasFooter() ? 4 : 0);

            for ($line = $this->line_start; $line <= $highestRow; $line++) {
                $data_import_deposit[] = $this->getDataExcelByRow($line);
            }

            $this->companyImportedDepositRepository->insert($data_import_deposit);
            DB::commit();

            return true;
        } catch (\Exception $e) {
            DB::rollback();
            Log::alert('Error:' . print_r($e->getLine() . $e->getMessage(), true), ['file' => __FILE__, 'line' => __LINE__]);
            return false;
        }
    }

    private function getDataExcelByRow($line)
    {
        $import_deposit = $this->dataInsertDefault();

        $slip_date = $this->getSheet()->getCell(self::EXCEL_SYMBOL_SLIP_DATE . $line)->getFormattedValue();
        $import_deposit['slip_date'] = date('Y-m-d', strtotime($slip_date));

        // debit/credit
        $amount = $this->getCreditDebit($line);

        $receiptAmount = BridgenoteTraitCollection::getParseFloat($amount['credit'], $this->countryId);
        if($receiptAmount) {
            $import_deposit['is_receipt'] = 1;
            $import_deposit['amount'] = $receiptAmount;
        }

        $paymentAmount = BridgenoteTraitCollection::getParseFloat($amount['debit'], $this->countryId);
        if($paymentAmount) {
            $import_deposit['is_receipt'] = 0;
            $import_deposit['amount'] = $paymentAmount;
        }

        $remarks = $this->getDataRemarkExcel($line);
        $import_deposit['remarks'] = trim($remarks);

        return $import_deposit;
    }

    public function uploadBCABankExcel($request)
    {
        $this->lengthBankStatementItemsRemarks = $this->bankStatementItemRepository->getMaxLengBankStatementItemByItemType(2);
        $this->lengthBankStatementItemsDefault = $this->bankStatementItemRepository->getMaxLengBankStatementItemByItemType(99);
        $uploadedFile = $request->file('uploader');

        try {
            $reader = $this->readExcelFile($uploadedFile);
        } catch (\Exception $e) {
            Log::alert('Error Read Excel File:' . print_r($e->getLine(), true), ['file' => __FILE__, 'line' => __LINE__]);
            return response()->json(['result' => false, 'error_message' => [$e->getMessage()], 'show_message' => true]);
        }

        $this->setSheet($reader, $uploadedFile);
        $this->setDataFromBankStatement();

        $this->validateImportDepositService = new ValidateImportDepositService($uploadedFile, $this->getSheet(), 1);
        $validPhysicalExcelFile = $this->validateImportDepositService->validatePhysicalExcelFile();

        if (!$validPhysicalExcelFile['valid']) {
            return response()->json(['result' => $validPhysicalExcelFile['valid'], 'error_message' => $validPhysicalExcelFile['list_errors'], 'show_message' => true]);
        }

        $validContentExcelFile = $this->validateContentExcelFile();

        if (!$validContentExcelFile['valid']) {
            return $this->makeFileErrorsValidExcel($validContentExcelFile['list_errors'], $uploadedFile);
        }
        /**
         * saveLegerWithDataOfExcel
         */
        $saveLegerSuccess = $this->saveLegerWithDataOfExcel();

        if ($saveLegerSuccess) {
            return response()->json(['success' => true, 'result' => true, 'messages' => __('messages.187'), 200]);
        }

        return response()->json(['success' => false, 'result' => false, 'show_message' => true, 'error_message' => array(__('messages.1')), 200]);
    }

    private function makeFileErrorsValidExcel($messages, $uploadedFile)
    {
        $origin_filename = $uploadedFile->getClientOriginalName();
        $target_dir = "/tmp/uploads/importdeposit/";

        if (!file_exists($target_dir)) {
            mkdir($target_dir, 0755, true);
        }

        $tmp_file = $uploadedFile->move($target_dir, 'errors_' . $origin_filename);

        $filename = $tmp_file->getFileName();
        $column_name_errors = self::COLUMN_SAVE_ERRORS;

        try {
            $reader = $this->readExcelFile($tmp_file);
            $spreadsheet = $reader->load($tmp_file);
            $highestRow = $this->getSheet()->getHighestDataRow() - ($this->hasFooter() ? 4 : 0);
            for ($line = $this->line_start; $line <= $highestRow; $line++) {
                foreach ($messages as $message) {
                    $cell_name = $column_name_errors . $message['line'];
                    $spreadsheet->getActiveSheet()->setCellValue($cell_name, $message['message']);
                }
            }

            $extension = \PhpOffice\PhpSpreadsheet\IOFactory::identify($tmp_file);

            if ($extension === Excel::XLS) {
                $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);
            }

            if ($extension === Excel::XLSX) {
                $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
            }

            if (!empty($writer)) {
                $writer->save($filename);
            }

            $data = file_get_contents($filename);
            $response = ['is_errors_excel' => true, 'name' => $filename, 'file' => "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," . base64_encode($data)];
            return response()->json($response);
        } catch (\Exception $e) {
            Log::alert('Error:' . print_r($e->getLine() . $e->getMessage(), true), ['file' => __FILE__, 'line' => __LINE__]);
        }
    }

    public function uploadBCABankCsv($request)
    {
        $this->lengthBankStatementItemsRemarks = $this->bankStatementItemRepository->getMaxLengBankStatementItemByItemType(2);
        $this->lengthBankStatementItemsDefault = $this->bankStatementItemRepository->getMaxLengBankStatementItemByItemType(99);
        $uploadedFile = $request->file('uploader');
        $this->validateImportDepositService = new ValidateImportDepositService($uploadedFile, null, 1);
        $validPhysicalCsvFile = $this->validateImportDepositService->validatePhysicalCsvFile();

        if (!$validPhysicalCsvFile['valid']) {
            return response()->json([
                'result'        => $validPhysicalCsvFile['valid'],
                'error_message' => $validPhysicalCsvFile['list_errors'],
                'show_message'  => true,
            ]);
        }

        try {
            $this->dataFormFileCSV = $this->getDataFromCsvFile($uploadedFile);
        } catch (\Exception $e) {
            Log::alert('Error: Read file errors!!! '.print_r($e->getLine() . ' ' . $e->getMessage(), true), ['file' => __FILE__, 'line' => __LINE__]);
            return response()->json(['success' => false, 'result' => false, 'show_message'  => true, 'error_message' => array(__('messages.1088')), 200]);
        }

        $fileCsvErrors = $this->validateDataCSV();

        if ($fileCsvErrors) {
            try {
                return $this->makeFileErrorsValidCSV($uploadedFile);
            } catch (\Exception $ex) {
                Log::alert('Error: Write file errors!!! '.print_r($ex->getLine() . ' ' . $ex->getMessage(), true), ['file' => __FILE__, 'line' => __LINE__]);
                return response()->json(['success' => false, 'result' => false, 'show_message' => true, 'error_message' => array(__('messages.1')), 200]);
            }
        }

        /*save data*/
        $saveLegerSuccess = $this->saveLegerWithDataOfCsv();

        if ($saveLegerSuccess) {
            return response()->json(['success' => true, 'result' => true, 'messages' => __('messages.187'), 200]);
        }

        return response()->json(['success' => false, 'result' => false, 'show_message' => true, 'error_message' => array(__('messages.1')), 200]);
    }

    private function getDataFromCsvFile($uploadedFile)
    {
        $allData = [];
        $import_deposit = [];
        $this->dataOriginCsv = [];

        $row = 1;
        $handle = fopen($uploadedFile, "r");

        while (($data = fgetcsv($handle, 1000, ",")) !== false) {
            if($row == 5) {
                $import_deposit['period'] = $data[0];
            }
            $import_deposit['slip_date'] = isset($data[0]) ? $data[0] : '';
            $import_deposit['remarks'] = isset($data[1]) ? $data[1] : '';
            $import_deposit['receipt'] = '';
            $import_deposit['payment'] = '';
            if (isset($data[3])) {
                $amount = explode(' ', $data[3]);
                if (!empty($amount[1]) && $amount[1] == 'CR') {
                    $import_deposit['receipt'] = $amount[0];
                }
                if (!empty($amount[1]) && $amount[1] == 'DB') {
                    $import_deposit['payment'] = $amount[0];
                }
            }
            if($row < self::BEGIN_LINE_CSV + 1) {
                $import_deposit['slip_date'] = '';
            } else {
                $import_deposit['slip_date'] = !empty($import_deposit['slip_date']) ? $this->getSlipDate($import_deposit) : '';
            }
            $row++;
            array_push($allData, $import_deposit);
            array_push($this->dataOriginCsv, $data);
        }
        
        fclose($handle);
        return $allData;
    }

    private function validateDataCSV()
    {
        $fileCsvErrors = false;
        foreach ($this->dataFormFileCSV as $line => &$data) {
            if($line < self::BEGIN_LINE_CSV) {
                continue;
            }

            if ($this->isIgnoredLineEmptyCsv($data)) {
                continue;
            }
            // validate date
            $data['errors'] = array();
            array_push($data['errors'], $this->validateSlipDate($data['slip_date']));

            // validate remark
            if (!$this->isRemarks($data['remarks'])) {
                array_push($data['errors'], __('labels.60-B-37', ['length' => $this->lengthBankStatementItemsRemarks]));
            }
            // debit/credit
            array_push($data['errors'], $this->validateByReceiptAndPayment(trim($data['receipt']), trim($data['payment'])));

            if (empty(array_filter($data['errors']))) {
                unset($data['errors']);
            } else {
                $fileCsvErrors = true;
            }
        }

        return $fileCsvErrors;
    }

    private function validateSlipDate($dateTime): string
    {
        $messages = [];
        // validate null
        if (empty(trim($dateTime))) {
            return trans('labels.60-B-33');
        } else {
            // validate format
            $regularExp = preg_match(self::REGEX_D_M_Y_FORMAT, $dateTime);
            if (!$regularExp) {
                array_push($messages, trans('labels.60-B-34'));
            }
        }
        $date = date('Y-m-d', bn_strtotime($dateTime));
        if ($date < $this->termBeginMonth || $date > $this->termEndMonth) {
            array_push($messages, trans('labels.60-B-36'));
        }

        return implode(",", array_filter($messages));
    }

    private function isRemarks($remarks): bool
    {
        return strlen((string) $remarks) <= $this->lengthBankStatementItemsRemarks;
    }

    private function validateByReceiptAndPayment($reciept, $payment)
    {
        $messages = [];
        if (empty($reciept) && empty($payment)) {
            return trans('labels.60-B-38');
        }

        $reciept = BridgenoteTraitCollection::getParseFloat($reciept, $this->countryId);
        $payment = BridgenoteTraitCollection::getParseFloat($payment, $this->countryId);

        if (!empty($reciept) && ($reciept >= self::AMOUNT_MAX || $reciept <= self::AMOUNT_MIN)) {
            array_push($messages, trans('labels.60-B-40'));
        }

        if (!empty($payment) && ($payment >= self::AMOUNT_MAX || $payment <= self::AMOUNT_MIN)) {
            array_push($messages, trans('labels.60-B-42'));
        }

        return implode(",", array_filter($messages));
    }

    private function getSlipDate($data)
    {
        if (!empty($data['period'])) {
            preg_match_all("/\d{2}\/\d{2}\/\d{4}/", $data['period'], $match);
            $period = $match[0];
            $startYear = date('Y', bn_strtotime($period[0]));
            $endYear = date('Y', bn_strtotime($period[1]));

            $slip_date = $data['slip_date'] . '/' . $startYear;
            $format = 'd/m/Y';
            
            if(
                Carbon::createFromFormat($format, $slip_date) < Carbon::createFromFormat($format, $period[0])
                || Carbon::createFromFormat($format, $slip_date) > Carbon::createFromFormat($format, $period[1])
            ) {
                $slip_date = $data['slip_date'] . '/' . $endYear;
            }
            
            return $slip_date;
        }
    }

    private function makeFileErrorsValidCSV($uploadedFile)
    {
        $number_max = count($this->dataFormFileCSV);
        for ($i = 0; $i < $number_max; $i++) {
            if (isset($this->dataFormFileCSV[$i]['errors'])) {
                $string_errors = implode(",", array_filter($this->dataFormFileCSV[$i]['errors']));
                array_push($this->dataOriginCsv[$i], trim($string_errors));
            }
        }

        $delimiter = "\t";
        $target_dir = "/tmp/uploads/importdeposit/";

        if (!file_exists($target_dir)) {
            mkdir($target_dir, 0755, true);
        }

        $origin_filename = $uploadedFile->getClientOriginalName();

        // write file errors.
        $filename = 'errors_' . $origin_filename;

        $errors_file = fopen($target_dir . $filename, "w");

        foreach ($this->dataOriginCsv as $line) {
            fputcsv($errors_file, $line, $delimiter);
        }
        fclose($errors_file);

        $data = file_get_contents($target_dir . $filename);
        $response =  array(
            'is_errors_excel' => true,
            'name' => $filename,
            'file' => "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,".base64_encode($data) //mime type of used format
        );

        return response()->json($response);
    }

    private function saveLegerWithDataOfCsv()
    {
        $import_deposit = $this->dataInsertDefault();
        DB::beginTransaction();
        try {
            $data_import_deposit = [];
            foreach ($this->dataFormFileCSV as $line => $data) {
                if($line < self::BEGIN_LINE_CSV) {
                    continue;
                }

                if ($this->isIgnoredLineEmptyCsv($data)) {
                    continue;
                }
                $import_deposit['slip_date'] = date('Y-m-d', bn_strtotime($data['slip_date']));
                $import_deposit['remarks'] = trim($data['remarks']);

                $receipt = trim($data['receipt']);
                $receiptAmount = BridgenoteTraitCollection::getParseFloat($receipt, $this->countryId);
                if($receiptAmount) {
                    $import_deposit['is_receipt'] = 1;
                    $import_deposit['amount'] = $receiptAmount;
                }

                $payment = trim($data['payment']);
                $paymentAmount = BridgenoteTraitCollection::getParseFloat($payment, $this->countryId);
                if($paymentAmount) {
                    $import_deposit['is_receipt'] = 0;
                    $import_deposit['amount'] = $paymentAmount;
                }
                array_push($data_import_deposit, $import_deposit);
            }
            $this->companyImportedDepositRepository->insert($data_import_deposit);
            DB::commit();

            return true;
        } catch (\Exception $e) {
            DB::rollback();
            Log::alert('Error:' . print_r($e->getLine() . $e->getMessage(), true), ['file' => __FILE__, 'line' => __LINE__]);
            return false;
        }
    }

    private function dataInsertDefault()
    {
        return [
            'company_id' => session('selectedcompany_id'),
            'account_title_id' =>session('import_deposit_account_id'),
            'detailed_id' => session('import_deposit_detail_account_id'),
            'created_person_name' => session('person_name'),
            'update_user_id' => session('account_id'),
            'created_at' => now(),
            'updated_at' => now(),
        ];
    }

    private function isIgnoredLineEmptyCsv($data): bool
    {
        if (empty($data['slip_date']) && empty($data['remarks'])
            && empty($data['receipt']) && empty($data['payment'])) {
            return true;
        }
        return false;
    }

    private function hasFooter()
    {
        $sheet = $this->getSheet();
        $lastRow = $sheet->getHighestDataRow();

        // Check if footer keywords are present in the last few rows
        for ($i = $lastRow; $i > $lastRow - 5; $i--) {
            $rowData = $sheet->rangeToArray("A$i:Z$i", null, true, true, true);
            foreach ($rowData as $row) {
                // Check if any column contains the footer keywords
                if (
                    strpos($row['A'], 'Saldo Awal') !== false ||
                    strpos($row['A'], 'Mutasi Debet') !== false ||
                    strpos($row['A'], 'Mutasi Kredit') !== false ||
                    strpos($row['A'], 'Saldo Akhir') !== false
                ) {
                    return true; // Footer found
                }
            }
        }

        return false; // Footer not found
    }
}