234 lines
11 KiB
PHP
234 lines
11 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
namespace Modules\Writeoff\Jobs;
|
||
|
|
|
||
|
|
use Exception;
|
||
|
|
use Illuminate\Bus\Queueable;
|
||
|
|
use Illuminate\Contracts\Queue\ShouldQueue;
|
||
|
|
use Illuminate\Foundation\Bus\Dispatchable;
|
||
|
|
use Illuminate\Queue\InteractsWithQueue;
|
||
|
|
use Illuminate\Queue\SerializesModels;
|
||
|
|
use Illuminate\Support\Facades\DB;
|
||
|
|
use Illuminate\Support\Facades\Log;
|
||
|
|
use Modules\Writeoff\Entities\Branch;
|
||
|
|
use Modules\Writeoff\Entities\Currency;
|
||
|
|
use Modules\Writeoff\Entities\Debitur;
|
||
|
|
use Modules\Writeoff\Entities\LoanType;
|
||
|
|
use Modules\Writeoff\Entities\Rekening;
|
||
|
|
|
||
|
|
class SyncDwh implements ShouldQueue
|
||
|
|
{
|
||
|
|
use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
|
||
|
|
|
||
|
|
public $timeout = 120;
|
||
|
|
public $tries = 3;
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Create a new job instance.
|
||
|
|
*/
|
||
|
|
public function __construct()
|
||
|
|
{
|
||
|
|
//
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Execute the job.
|
||
|
|
*/
|
||
|
|
public function handle()
|
||
|
|
: void
|
||
|
|
{
|
||
|
|
Log::info('SyncDwh: Start');
|
||
|
|
$this->syncCurrency();
|
||
|
|
$this->syncBranch();
|
||
|
|
$this->syncLoanType();
|
||
|
|
$this->syncDebitur();
|
||
|
|
$this->syncAccountLoan();
|
||
|
|
$this->syncAccount();
|
||
|
|
Log::info('SyncDwh: Finish');
|
||
|
|
}
|
||
|
|
|
||
|
|
function syncCurrency()
|
||
|
|
{
|
||
|
|
Log::info('SyncDwh: syncCurrency');
|
||
|
|
try {
|
||
|
|
$currency = DB::connection('oracle')->table('T_CURRENCY')->get();
|
||
|
|
foreach ($currency as $c) {
|
||
|
|
Currency::updateOrCreate(['kode' => $c->currency_code], [
|
||
|
|
'name' => $c->currency_name,
|
||
|
|
'updated_at' => now(),
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
Log::info('SyncDwh: syncCurrency: success');
|
||
|
|
} catch (Exception $e) {
|
||
|
|
Log::error('SyncDwh: syncCurrency: ' . $e->getMessage());
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
function syncBranch()
|
||
|
|
{
|
||
|
|
Log::info('SyncDwh: syncBranch');
|
||
|
|
try {
|
||
|
|
$branch = DB::connection('oracle')->table('T_BRANCH')->get();
|
||
|
|
foreach ($branch as $b) {
|
||
|
|
Branch::updateOrCreate(['kode' => $b->branch_code], [
|
||
|
|
'name' => $b->branch_name,
|
||
|
|
'status' => '1',
|
||
|
|
'updated_at' => now(),
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
Log::info('SyncDwh: syncBranch: success');
|
||
|
|
} catch (Exception $e) {
|
||
|
|
Log::error('SyncDwh: syncBranch: ' . $e->getMessage());
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function syncLoanType()
|
||
|
|
{
|
||
|
|
Log::info('SyncDwh: syncLoanType');
|
||
|
|
try {
|
||
|
|
$loanType = DB::connection('oracle')->table('t_product')->get();
|
||
|
|
foreach ($loanType as $lt) {
|
||
|
|
LoanType::updateOrCreate(['kode' => $lt->product_code], [
|
||
|
|
'name' => $lt->product_description,
|
||
|
|
'status' => '1',
|
||
|
|
'updated_at' => now(),
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
Log::info('SyncDwh: syncLoanType: success');
|
||
|
|
} catch (Exception $e) {
|
||
|
|
Log::error('SyncDwh: syncLoanType: ' . $e->getMessage());
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function syncDebitur()
|
||
|
|
{
|
||
|
|
Log::info('SyncDwh: syncDebitur');
|
||
|
|
|
||
|
|
try {
|
||
|
|
$loantype = LoanType::select('kode')->get();
|
||
|
|
$debitur = Debitur::select('kode')->get();
|
||
|
|
|
||
|
|
$customer = DB::connection('oracle')
|
||
|
|
->table('BAG_CUSTOMER t')
|
||
|
|
->select('t.customer_code', 't.customer_name', 't.branch_code', 't.npwp_number', 't.customer_date', 't.contact_number', 't.customer_address_1', 't.customer_address_2', 't.customer_address_3', 't.customer_address_4', 't.customer_address_5', 't.customer_address_6', 't.cust_address_rt', 't.cust_address_rw', 't.cust_address_town', 't.cust_address_city')
|
||
|
|
->addSelect(DB::connection('oracle')
|
||
|
|
->raw('(SELECT MIN(a.opening_date) FROM t_account a WHERE a.customer_code = t.customer_code) as open_date'))
|
||
|
|
->whereExists(function ($query) use ($loantype) {
|
||
|
|
$query->select(DB::connection('oracle')->raw(1))
|
||
|
|
->from('T_ACCOUNT a')
|
||
|
|
->whereColumn('t.customer_code', 'a.customer_code')
|
||
|
|
->whereIn('a.product_code', $loantype->map(function ($item) {
|
||
|
|
return $item->kode;
|
||
|
|
})->toArray());
|
||
|
|
})
|
||
|
|
->whereNotIn('t.customer_code', $debitur->map(function ($item) {
|
||
|
|
return $item->kode;
|
||
|
|
})->toArray())
|
||
|
|
->get();
|
||
|
|
|
||
|
|
foreach ($customer as $row) {
|
||
|
|
Debitur::updateOrCreate([
|
||
|
|
'kode' => $row->customer_code
|
||
|
|
], [
|
||
|
|
'name' => $row->customer_name,
|
||
|
|
'branch_id' => Branch::where('kode', $row->branch_code)->first()->id ?? null,
|
||
|
|
'npwp' => $row->npwp_number,
|
||
|
|
'registered_at' => $row->customer_date,
|
||
|
|
'contact_number' => $row->contact_number,
|
||
|
|
'address' => $row->customer_address_1 . ' ' . $row->customer_address_2 . ' ' . $row->customer_address_3 . ' ' . $row->customer_address_4 . ' ' . $row->customer_address_5 . ' ' . $row->customer_address_6 . ' rt ' . $row->cust_address_rt . ' rw ' . $row->cust_address_rw . ' town ' . $row->cust_address_town . ' city ' . $row->cust_address_city,
|
||
|
|
'status' => '1',
|
||
|
|
'updated_at' => now(),
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
Log::info('SyncDwh: syncDebitur: success');
|
||
|
|
} catch (Exception $e) {
|
||
|
|
Log::error('SyncDwh: syncDebitur: ' . $e->getMessage());
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function syncAccountLoan()
|
||
|
|
{
|
||
|
|
Log::info('SyncDwh: syncAccountLoan');
|
||
|
|
|
||
|
|
try {
|
||
|
|
$loantype = LoanType::select('kode')->get();
|
||
|
|
$rekening = Rekening::select('nomor_rekening')->get();
|
||
|
|
|
||
|
|
$account_loan = DB::connection('oracle')
|
||
|
|
->table('T_ACCOUNT t')
|
||
|
|
->select('t.account_number', 't.customer_code', 't.branch_code', 't.product_code', 't.currency_code', 't.opening_date', 't.record_status', 't.limit_ref', 't.clear_balance', 'p.product_description')
|
||
|
|
->join('T_PRODUCT p', 'p.product_code', '=', 't.product_code')
|
||
|
|
->whereIn('t.product_code', $loantype->map(function ($item) {
|
||
|
|
return $item->kode;
|
||
|
|
})->toArray())
|
||
|
|
->whereNotIn('t.account_number', $rekening->map(function ($item) {
|
||
|
|
return $item->nomor_rekening;
|
||
|
|
})->toArray())
|
||
|
|
->limit(10)
|
||
|
|
->get();
|
||
|
|
|
||
|
|
foreach ($account_loan as $row) {
|
||
|
|
Rekening::updateOrCreate([
|
||
|
|
'nomor_rekening' => $row->account_number
|
||
|
|
], [
|
||
|
|
'debitur_id' => Debitur::where('kode', $row->customer_code)->first()->id ?? null,
|
||
|
|
'branch_id' => Branch::where('kode', $row->branch_code)->first()->id ?? null,
|
||
|
|
'loan_type_id' => LoanType::where('kode', $row->product_code)->first()->id ?? null,
|
||
|
|
'currency_id' => Currency::where('kode', $row->currency_code)->first()->id ?? null,
|
||
|
|
'registered_at' => $row->opening_date,
|
||
|
|
'status_rekening' => $row->record_status,
|
||
|
|
'limit_ref' => $row->limit_ref,
|
||
|
|
'status' => '1',
|
||
|
|
'updated_at' => now(),
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
Log::info('SyncDwh: syncAccountLoan: success');
|
||
|
|
} catch (Exception $e) {
|
||
|
|
Log::error('SyncDwh: syncAccountLoan: ' . $e->getMessage());
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function syncAccount()
|
||
|
|
{
|
||
|
|
Log::info('SyncDwh: syncAccount');
|
||
|
|
try {
|
||
|
|
$rekening = Rekening::select('nomor_rekening')->get();
|
||
|
|
|
||
|
|
$account = DB::connection('oracle')
|
||
|
|
->table('T_ACCOUNT t')
|
||
|
|
->select('t.account_number', 't.customer_code', 't.branch_code', 't.product_code', 't.currency_code', 't.opening_date', 't.record_status', 't.limit_ref', 't.clear_balance', 'p.product_description')
|
||
|
|
->join('T_PRODUCT p', 'p.product_code', '=', 't.product_code')
|
||
|
|
->whereBetween('t.product_code', ['6000', '6200'])
|
||
|
|
->whereNotIn('t.account_number', $rekening->map(function ($item) {
|
||
|
|
return $item->nomor_rekening;
|
||
|
|
})->toArray())
|
||
|
|
->whereExists(function ($query) {
|
||
|
|
$query->select(DB::connection('oracle')->raw(1)) // Select a constant value (1)
|
||
|
|
->from('T_ACCOUNT l')
|
||
|
|
->whereColumn('l.customer_code', 't.customer_code')
|
||
|
|
->whereBetween('l.product_code', ['3000', '3999']);
|
||
|
|
})
|
||
|
|
->limit(10)
|
||
|
|
->get();
|
||
|
|
|
||
|
|
foreach ($account as $a) {
|
||
|
|
Rekening::updateOrCreate(['nomor_rekening' => $a->account_number], [
|
||
|
|
'debitur_id' => Debitur::where('kode', $a->customer_code)->first()->id ?? null,
|
||
|
|
'branch_id' => Branch::where('kode', $a->branch_code)->first()->id ?? null,
|
||
|
|
'loan_type_id' => LoanType::where('kode', $a->product_code)->first()->id ?? null,
|
||
|
|
'currency_id' => Currency::where('kode', $a->currency_code)->first()->id ?? null,
|
||
|
|
'registered_at' => $a->opening_date,
|
||
|
|
'status_rekening' => $a->record_status,
|
||
|
|
'limit_ref' => $a->limit_ref,
|
||
|
|
'status' => '1',
|
||
|
|
'updated_at' => now(),
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
Log::info('SyncDwh: syncAccount: success');
|
||
|
|
} catch (Exception $e) {
|
||
|
|
Log::error('SyncDwh: syncAccount: ' . $e->getMessage());
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|