mirror of
https://gitlab.com/CIEFWorldwideSdnBhd/exchange-2.0.git
synced 2026-08-19 20:43:56 +00:00
Steve Ng
369 lines
14 KiB
PHP
369 lines
14 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers\Accounting;
|
|
|
|
use App\Classes\Jobs\CreateBankStatementTransactionOwners;
|
|
use App\Classes\Modules\Accounting\ControllersLogic\ImportBankStatementLogic;
|
|
use App\Classes\Modules\Accounting\ControllersLogic\ListBankStatementDetailsLogic;
|
|
use App\Classes\Modules\Accounting\ControllersLogic\ListBankStatementTransactionsLogic;
|
|
use App\Classes\Modules\Accounting\ControllersLogic\UpdateBankStatementDetailLogic;
|
|
use App\Classes\ValueObjects\Constants\ApprovalStatus;
|
|
use App\Classes\ValueObjects\Constants\PaymentMethodType;
|
|
use App\Classes\ValueObjects\Constants\TransactionType;
|
|
use App\Models\StatementAccount;
|
|
use App\Models\AccountStatement;
|
|
use App\Models\Booking;
|
|
use App\Models\Company;
|
|
use App\Models\Group;
|
|
use App\Models\StatementTransactionOwner;
|
|
use App\Models\Transaction;
|
|
use App\Models\Wallet;
|
|
use App\Http\Controllers\Controller;
|
|
use Illuminate\Http\JsonResponse;
|
|
use Illuminate\Http\Request;
|
|
use DateTime;
|
|
use App\Models\StatementTransaction;
|
|
|
|
class BankStatementController extends Controller
|
|
{
|
|
public function index(Request $request)
|
|
{
|
|
$selectedAccount = $request->input('account');
|
|
$search = $request->input('search');
|
|
|
|
$accounts = StatementAccount::all();
|
|
|
|
$statementsQuery = AccountStatement::query();
|
|
|
|
if ($selectedAccount) {
|
|
$statementsQuery->where('statement_account_id', $selectedAccount);
|
|
}
|
|
|
|
if ($search) {
|
|
$statementsQuery->where(function ($query) use ($search) {
|
|
$query->where('date_from', 'LIKE', "%$search%")
|
|
->orWhere('date_to', 'LIKE', "%$search%")
|
|
->orWhere('total_amount', 'LIKE', "%$search%")
|
|
->orWhere('begin_balance', 'LIKE', "%$search%")
|
|
->orWhere('end_balance', 'LIKE', "%$search%");
|
|
});
|
|
}
|
|
|
|
$statements = $statementsQuery->orderBy('date_from')->paginate(10);
|
|
|
|
return view('pages.accounting.bank-statements.index', compact('accounts', 'selectedAccount', 'search', 'statements'));
|
|
}
|
|
|
|
public function indexv2(Request $request)
|
|
{
|
|
$selectedAccount = $request->input('account');
|
|
$search = $request->input('search');
|
|
|
|
$accounts = StatementAccount::all();
|
|
|
|
$statementsQuery = AccountStatement::query();
|
|
|
|
if ($selectedAccount) {
|
|
$statementsQuery->where('statement_account_id', $selectedAccount);
|
|
}
|
|
|
|
if ($search) {
|
|
$statementsQuery->where(function ($query) use ($search) {
|
|
$query->where('date_from', 'LIKE', "%$search%")
|
|
->orWhere('date_to', 'LIKE', "%$search%")
|
|
->orWhere('total_amount', 'LIKE', "%$search%")
|
|
->orWhere('begin_balance', 'LIKE', "%$search%")
|
|
->orWhere('end_balance', 'LIKE', "%$search%");
|
|
});
|
|
}
|
|
|
|
$statements = $statementsQuery->paginate(10);
|
|
|
|
return view('pages.accounting.bank-statements.indexv2', compact('accounts', 'selectedAccount', 'search', 'statements'));
|
|
}
|
|
|
|
public function import(Request $request, ImportBankStatementLogic $logic): JsonResponse
|
|
{
|
|
return $logic->execute($request);
|
|
}
|
|
|
|
public function rerun()
|
|
{
|
|
foreach (StatementTransaction::doesntMapStatement()->get()->chunk(30) as $key => $transaction) {
|
|
CreateBankStatementTransactionOwners::dispatch($transaction);
|
|
}
|
|
return redirect()->back()->with('success', 'Rerun triggered successfully');
|
|
}
|
|
|
|
public function show(AccountStatement $statement, Request $request)
|
|
{
|
|
$transactions = $statement->transactions();
|
|
// $account = $statement->account();
|
|
// dd(json_encode($account->where('id', '>=', 1)->first()));
|
|
// dd(json_encode($transactions->where('id', '>=', 1)->first()));
|
|
if ($request->get('transaction_filter')) {
|
|
$transactionFilter = $request->get('transaction_filter');
|
|
$transactions = $transactions->where('transaction_description', 'LIKE', "%$transactionFilter%");
|
|
}
|
|
|
|
if ($request->get('from_amount_filter')) {
|
|
$fromAmountFilter = $request->get('from_amount_filter');
|
|
$transactions = $transactions->where('amount', '>=', $fromAmountFilter);
|
|
}
|
|
|
|
if ($request->get('to_amount_filter')) {
|
|
$toAmountFilter = $request->get('to_amount_filter');
|
|
$transactions = $transactions->where('amount', '<=', $toAmountFilter);
|
|
}
|
|
|
|
// $transactions = $transactions->paginate(100);
|
|
$transactions = $transactions->get();
|
|
echo $this->process3_merged($transactions);
|
|
|
|
//return view('pages.accounting.bank-statements.show', compact('statement', 'transactions'));
|
|
}
|
|
|
|
public function download(AccountStatement $statement)
|
|
{
|
|
$transactions = $statement->transactions;
|
|
|
|
$csvExporter = new \Laracsv\Export();
|
|
$csvExporter->build($transactions, ['transaction_date', 'transaction_time', 'posting_date', 'transaction_description', 'transaction_ref', 'debit', 'credit'])
|
|
->download($statement->date_from->format('Y-m-d') . '_' . $statement->date_to->format('Y-m-d') . '_statement.csv');
|
|
}
|
|
|
|
public function fetch(Request $request, ListBankStatementTransactionsLogic $logic): JsonResponse
|
|
{
|
|
return $logic->execute($request);
|
|
}
|
|
|
|
public function transactions(Request $request, ListBankStatementTransactionsLogic $logic): JsonResponse
|
|
{
|
|
return $logic->execute($request);
|
|
}
|
|
|
|
public function update(Request $request, UpdateBankStatementDetailLogic $logic): JsonResponse
|
|
{
|
|
return $logic->execute($request);
|
|
}
|
|
|
|
private function process3_merged($transactions){
|
|
|
|
// $statement = $transactions[0]->statement();
|
|
// dd(json_encode($statement->first()));
|
|
|
|
$headers = [
|
|
'Date',
|
|
'Bank',
|
|
'Description',
|
|
'Credit',
|
|
'Debit',
|
|
'Pay For',
|
|
'System',
|
|
'System Reference',
|
|
'Human Reference',
|
|
'Multiple',
|
|
'Match?',
|
|
'System Amount'
|
|
];
|
|
|
|
$branches = [
|
|
0 => 'MBB Cyber',
|
|
1 => 'MBB SS2',
|
|
];
|
|
|
|
$yes = 'Yes';
|
|
$no = 'No';
|
|
|
|
$table = '<table><tr><th>'.implode('</th><th>', $headers).'</th></tr>';
|
|
$count = 0;
|
|
|
|
foreach ($transactions as $row) {
|
|
$isExist = StatementTransactionOwner::where('statement_transaction_id', $row->id)->first();
|
|
|
|
if ($isExist) {
|
|
continue;
|
|
}
|
|
|
|
$count++;
|
|
$credit = 0.00;
|
|
$debit = 0.00;
|
|
|
|
// dd(json_encode($row['posting_date']));
|
|
$date = new DateTime($row['posting_date']);
|
|
$description = $row['transaction_description_2'];
|
|
|
|
if($row['amount'] < 0){
|
|
$debit = (float) $row['amount'];
|
|
}
|
|
else{
|
|
$credit = (float) $row['amount'];
|
|
}
|
|
|
|
$creditTransactions = [];
|
|
$debitTransactions = [];
|
|
|
|
$system = '';
|
|
$systemReference = null;
|
|
$systemAmount = null;
|
|
|
|
if($credit){
|
|
$creditTransactions = $this->getTransactions($date, $credit, TransactionType::PAYMENT, Booking::class, PaymentMethodType::WALLET, [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED]);
|
|
foreach ($creditTransactions as $transaction) {
|
|
$systemReference[] = $transaction->owner instanceof Booking ? $transaction->owner->marking : $transaction->bill_no;
|
|
$systemAmount[] = $transaction->amount;
|
|
$system[] = 'EXCHANGE';
|
|
}
|
|
|
|
$creditTransactions = $this->getTransactions($date, $credit, TransactionType::TOP_UP, Wallet::class, null, [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED]);
|
|
foreach ($creditTransactions as $transaction) {
|
|
$systemReference[] = $transaction->owner instanceof Booking ? $transaction->owner->marking : $transaction->bill_no;
|
|
$systemAmount[] = $transaction->amount;
|
|
$system[] = 'EXCHANGE';
|
|
}
|
|
|
|
$creditTransactions = $this->getTransactionsFromShippingPortal($credit, $this->getDateRange($row['posting_date']));
|
|
foreach ($creditTransactions as $transaction) {
|
|
$systemReference[] = $transaction['order']['reference'];
|
|
$systemAmount[] = $transaction['amount'];
|
|
$system[] = 'SHIPPING';
|
|
}
|
|
}
|
|
|
|
if($debit){
|
|
$debitTransactions = $this->getTransactions($date, $debit, null, null, null, [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED], Group::class);
|
|
|
|
if(!count($debitTransactions)) {
|
|
foreach (['YSN', 'HCK', 'ATVANTIC', 'HIGH HILL'] as $reference){
|
|
if(str_contains($description, $reference)) {
|
|
$paymentDate = date('Y-m-d', strtotime('+1 day', strtotime($row['posting_date']))); //$date->addDays(1)->format('Y-m-d');
|
|
|
|
if($reference = 'ATVANTIC'){
|
|
$paymentDate = date('Y-m-d', strtotime($row['posting_date']));//$date->format('Y-m-d');
|
|
}
|
|
$issuer = Company::where('name', 'like', '%'.$reference.'%')->get()->pluck('id');
|
|
$debitTransactions = Group::whereIn('issuer', $issuer)->whereDate('created_at', $paymentDate)->get();
|
|
break;
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
foreach ($debitTransactions as $transaction) {
|
|
$systemReference[] = $transaction->reference;
|
|
$systemAmount[] = $transaction->amount;
|
|
$system[] = 'EXCHANGE';
|
|
}
|
|
}
|
|
|
|
|
|
$multiple = count($creditTransactions) + count($debitTransactions) > 1 ? $yes : $no;
|
|
|
|
|
|
|
|
|
|
|
|
$systemReference = $systemReference ? implode(',', $systemReference) : null;
|
|
$systemAmount = $systemAmount ? implode(',', $systemAmount) : null;
|
|
|
|
$matches = $systemReference == $row['remarkreferences'] ? $yes : $no;
|
|
|
|
$table .= '<tr>
|
|
<td>'.$date->format('d-m-Y').'</td>
|
|
<td>branch</td>
|
|
<td>'.$description.'</td>
|
|
<td>'.$credit.'</td>
|
|
<td>'.$debit.'</td>
|
|
<td>'.$row['pay_for'].'</td>
|
|
<td>'.$system.'</td>
|
|
<td>'.$systemReference.'</td>
|
|
<td>'.$row['remarkreferences'].'</td>
|
|
<td>'.$multiple.'</td>
|
|
<td>'.$matches.'</td>
|
|
<td>'.$systemAmount.'</td>
|
|
</tr>';
|
|
|
|
//AccountStatement
|
|
// $row->statement()->first()->id)
|
|
|
|
$statementTransactionsDetail = new StatementTransactionOwner([
|
|
'date' => $date,
|
|
'statement_transaction_id' => $row->id,
|
|
'description' => is_null($description) ? "" : $description,
|
|
'credit' => $credit,
|
|
'debit' => $debit,
|
|
'pay_for' => $system,
|
|
'system_references' => is_null($systemReference) ? "" : $systemReference,
|
|
'remark_references' => is_null($row['remarkreferences']) ? "" : $row['remarkreferences'],
|
|
'is_multiple' => $multiple == "Yes" ? 1 : 0,
|
|
'is_matches' => $matches == "Yes" ? 1 : 0,
|
|
'system_amounts'=> is_null($systemAmount) ? "" : $systemAmount,
|
|
]);
|
|
|
|
$statementTransactionsDetail->save();
|
|
|
|
if($count == 10){
|
|
break;
|
|
}
|
|
}
|
|
|
|
$table .= '</table>';
|
|
|
|
return $table;
|
|
}
|
|
|
|
private function getTransactions($date, $amount, $type, $ownerType, $paymentMethod, $statuses, $model = Transaction::class) {
|
|
$query = $model::whereIn('status', $statuses)
|
|
->where(function ($query) use ($ownerType, $paymentMethod, $type) {
|
|
if ($ownerType) {
|
|
$query->where('owner_type', $ownerType);
|
|
}
|
|
|
|
if ($paymentMethod) {
|
|
$query->where('payment_method', '!=', $paymentMethod);
|
|
}
|
|
|
|
if ($type) {
|
|
$query->where('type', $type);
|
|
}
|
|
})
|
|
->whereDate('created_at', $date->format('Y-m-d'))
|
|
->where('amount', '>', ($amount - 0.01))
|
|
->where('amount', '<', ($amount + 0.01));
|
|
|
|
return $query->get();
|
|
}
|
|
|
|
private function getDateRange(string $dateStr) {
|
|
// Create a DateTime object from the input string
|
|
$date = strtotime($dateStr);
|
|
|
|
// Get the first day of the month
|
|
$today = date('Y-m-d', $date);
|
|
|
|
// Get the first day of the next month
|
|
$nextDay = date('Y-m-d', strtotime('+1 day', $date));
|
|
|
|
return [
|
|
'start_date' => $today,
|
|
'end_date' => $nextDay,
|
|
];
|
|
}
|
|
|
|
private function getTransactionsFromShippingPortal($amount, $dateRange){
|
|
|
|
$client = new \GuzzleHttp\Client();
|
|
$response = $client->request('GET', 'https://izyim.cief-malaysia.com/public/api/v1/list?api-key=510acd13d8d24375cf038ad626c282565451461a9c2399357e0b65365300787e&filters={"order_by":{"column":"id","DESC":true},"status_in":[2],"type":2,"created_after":"'.$dateRange['start_date'].'","created_before":"'.$dateRange['end_date'].'","amount_exceed":'.($amount - 0.01).',"amount_short":'.($amount + 0.01).'}');
|
|
$body = $response->getBody();
|
|
$data = json_decode($body, true);
|
|
$payload = $data['payload'];
|
|
$transactions2 = $payload['data'];
|
|
// $filters = [
|
|
// ['field' => 'created_at', 'value' => '2023-03-01 08:07:00'],
|
|
// ];
|
|
// $transactions2 = $this->getTransactions3($transactions2, $filters);
|
|
return $transactions2;
|
|
}
|
|
|
|
}
|