Nur Izzati
405 lines
14 KiB
PHP
405 lines
14 KiB
PHP
<?php
|
|
|
|
namespace App\Services\Reports;
|
|
|
|
use App\Cawangan;
|
|
use App\CawanganDetail;
|
|
use App\Customer;
|
|
use App\Gadai;
|
|
use App\Marhun;
|
|
use Illuminate\Database\Eloquent\Builder;
|
|
use Illuminate\Http\Request;
|
|
use App\Helper;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
class GadaianService
|
|
{
|
|
|
|
public $kod_cawangan;
|
|
public $startDate;
|
|
public $endDate;
|
|
|
|
public function __construct($kod_cawangan, Request $request)
|
|
{
|
|
$this->kod_cawangan = $kod_cawangan;
|
|
$this->startDate = $request->startDate;
|
|
$this->endDate = $request->endDate;
|
|
}
|
|
|
|
public function getCawanganReport()
|
|
{
|
|
$cawangan_connection = Cawangan::where('kodcaw', $this->kod_cawangan)->first()->mysql;
|
|
|
|
$query_total_gadaian = Gadai::on($cawangan_connection);
|
|
|
|
$query_total_gadaian->when(!empty($this->startDate) and !empty($this->endDate), function ($q) {
|
|
return $q->where('t_gadai', '>=', $this->startDate)
|
|
->where('t_gadai', '<=', $this->endDate);
|
|
});
|
|
|
|
$total_gadaian = $this->getTotalGadaian($query_total_gadaian);
|
|
$total_summary_gadaian_teller = $this->getTotalSummaryGadaianGroupByTeller($query_total_gadaian);
|
|
$total_summary_gadaian = $this->getTotalSummaryGadaian($query_total_gadaian);
|
|
$customer_info = $this->getCustomerInfo($total_gadaian);
|
|
$marhun_gadaian = $this->getMarhunGadaian($total_gadaian, $cawangan_connection);
|
|
$cawangan_detail = $this->getCawanganDetail($cawangan_connection);
|
|
|
|
$total_gadaian = $total_gadaian->zip($customer_info, $marhun_gadaian)->map(function ($data) {
|
|
|
|
[$gadai, $customer_info, $marhun_gadaian] = $data;
|
|
|
|
return [
|
|
'no_rujukan' => $gadai['norujukan'],
|
|
'nama' => $customer_info['nama'],
|
|
'alamat' => $customer_info['alamat1'],
|
|
'no_kp' => $gadai['nokp'],
|
|
'masa' => $gadai['masa'],
|
|
'marhun' => $marhun_gadaian,
|
|
'berat' => $gadai['berat'],
|
|
'nilai_marhun' => $gadai['nilaimarhun'],
|
|
'pembiayaan' => $gadai['pinjaman'],
|
|
'peratusan_margin_pinjaman' => $gadai['percentpinj'],
|
|
'teller' => $gadai['teller'],
|
|
];
|
|
|
|
});
|
|
|
|
$total_gadaian = $total_gadaian->groupBy('teller');
|
|
|
|
$gadaian_tellers = $total_gadaian->keys();
|
|
|
|
$total_gadaian = $total_gadaian
|
|
->zip($total_summary_gadaian_teller)
|
|
->map(function ($data) {
|
|
|
|
[$gadaian, $summary] = $data;
|
|
|
|
return [
|
|
'gadaian' => $gadaian,
|
|
'summary_gadaian_teller' => $summary,
|
|
];
|
|
});
|
|
|
|
$total_gadaian = $gadaian_tellers->combine($total_gadaian);
|
|
|
|
$data = [
|
|
'query' => [
|
|
'startDate' => Helper::formatDate($this->startDate),
|
|
'endDate' => Helper::formatDate($this->endDate),
|
|
],
|
|
'cawangan_detail' => $cawangan_detail,
|
|
'gadaian' => $total_gadaian,
|
|
'summary_gadaian' => $total_summary_gadaian,
|
|
];
|
|
|
|
return json_encode($data);
|
|
|
|
}
|
|
|
|
private function getCawanganDetail($cawangan_connection)
|
|
{
|
|
$cawangan_detail = CawanganDetail::on($cawangan_connection)->get()
|
|
->map(function ($detail) {
|
|
return [
|
|
'nama' => $detail['namacaw'],
|
|
'code' => $detail['kodcaw'] . ' Ar-Rahn ' . $detail['cawangan'],
|
|
'alamat' => $detail['alamat1'],
|
|
'waktu_operasi' => $detail['alamat4'],
|
|
];
|
|
});
|
|
|
|
return $cawangan_detail[0];
|
|
|
|
}
|
|
|
|
private function getTotalSummaryGadaian(Builder $query_total_gadaian)
|
|
{
|
|
|
|
$total_summary_gadaian = (clone $query_total_gadaian)
|
|
->selectRaw('sum(berat) as total_berat, sum(nilaimarhun) as total_nilai_marhun, sum(pinjaman) as total_pembiayaan')
|
|
->get()
|
|
->map(function ($x) {
|
|
return [
|
|
'total_berat' => $x['total_berat'],
|
|
'total_nilai_marhun' => $x['total_nilai_marhun'],
|
|
'total_pembiayaan' => $x['total_pembiayaan'],
|
|
];
|
|
});
|
|
|
|
return $total_summary_gadaian[0];
|
|
}
|
|
private function getTotalSummaryGadaianGroupByTeller(Builder $query_total_gadaian)
|
|
{
|
|
|
|
$total_summary_gadaian_groupByTeller = (clone $query_total_gadaian)
|
|
->selectRaw('sum(berat) as total_berat, sum(nilaimarhun) as total_nilai_marhun, sum(pinjaman) as total_pembiayaan')
|
|
->groupBy('teller')
|
|
->get();
|
|
return $total_summary_gadaian_groupByTeller;
|
|
}
|
|
|
|
private function getTotalGadaian(Builder $query_total_gadaian)
|
|
{
|
|
$total_gadaian = (clone $query_total_gadaian)
|
|
->select('t_gadai', 'norujukan', 'nokp', 'semakbarcode', 'masa', 'berat', 'nilaimarhun', 'pinjaman', 'upah12', 'percentpinj', 'teller', 'nokp')
|
|
->orderBy('teller')
|
|
->orderBy('sirig')
|
|
->get();
|
|
return $total_gadaian;
|
|
}
|
|
|
|
private function getCustomerInfo($total_gadaian)
|
|
{
|
|
|
|
$extract_nokp = $total_gadaian->pluck('nokp');
|
|
|
|
$customer_info = Customer::on('mysql7')
|
|
->select(['nama', 'alamat1', 'kpbaru', 'kplama'])
|
|
->where(function ($query) use ($extract_nokp) {
|
|
$query->whereIn('kpbaru', $extract_nokp)
|
|
->orWhereIn('kplama', $extract_nokp);
|
|
})
|
|
->get();
|
|
|
|
$customer_info = collect($extract_nokp)->map(function ($nokp) use ($customer_info) {
|
|
return $customer_info->where('kpbaru', $nokp)->first() ?? $customer_info->where('kplama', $nokp)->first();
|
|
})->toArray();
|
|
|
|
return $customer_info;
|
|
}
|
|
|
|
private function getMarhunGadaian($total_gadaian, $cawangan_connection)
|
|
{
|
|
|
|
$extract_norujukan = $total_gadaian->pluck('norujukan');
|
|
|
|
$marhun_gadaian = Marhun::on($cawangan_connection)
|
|
->select('norujukan', 'bil', 'marhun')
|
|
->whereIn('norujukan', $extract_norujukan)
|
|
->get()
|
|
->groupBy('norujukan')
|
|
->toArray();
|
|
|
|
$marhun_gadaian = array_map(function ($gadaian) {
|
|
return array_map(function ($norujukan) {
|
|
return $norujukan['bil'] . ' ' . $norujukan['marhun'];
|
|
}, $gadaian);
|
|
}, $marhun_gadaian);
|
|
|
|
$marhun_gadaian = array_map(function ($gadaian) {
|
|
return implode(',', $gadaian);
|
|
}, $marhun_gadaian);
|
|
|
|
return $marhun_gadaian;
|
|
}
|
|
|
|
public function getCawanganReportGadaiTebusMasa()
|
|
{
|
|
$cawangan_connection = Cawangan::where('kodcaw', $this->kod_cawangan)->firstOrFail()->mysql;
|
|
$online_connection = Helper::getOnlineArrahnConnection();
|
|
$startDate = $this->startDate;
|
|
$endDate = $this->endDate;
|
|
|
|
// 1. Ambil data gadai dari cawangan
|
|
$gadai_data = DB::connection($cawangan_connection)->table('gadai')
|
|
->whereBetween('t_gadai', [$startDate, $endDate])
|
|
->get();
|
|
|
|
if ($gadai_data->isEmpty()) {
|
|
return response()->json([
|
|
'query' => [
|
|
'startDate' => Helper::formatDate($startDate),
|
|
'endDate' => Helper::formatDate($endDate),
|
|
],
|
|
'cawangan_detail' => $this->getCawanganDetail($cawangan_connection),
|
|
'gadaian' => [],
|
|
'summary_gadaian' => [],
|
|
]);
|
|
}
|
|
|
|
// 2. Ambil norujukan & tarikh dari gadai
|
|
$norujukans = $gadai_data->pluck('norujukan')->unique();
|
|
$tarikh_set = $gadai_data->pluck('t_gadai')->map(fn($d) => date('Y-m-d', strtotime($d)))->unique();
|
|
|
|
// 3. Ambil transaksi tebus dari online server
|
|
$transaksi_tebus = DB::connection($online_connection)->table('transaction')
|
|
->whereIn('trans_type', ['T', 'AT'])
|
|
->whereIn('norujukan', $norujukans)
|
|
->whereIn(DB::raw('DATE(created_at)'), $tarikh_set)
|
|
->get()
|
|
->groupBy(fn($trans) => $trans->norujukan . '|' . date('Y-m-d', strtotime($trans->created_at)));
|
|
|
|
// 4. Padankan
|
|
$matched = collect();
|
|
|
|
foreach ($gadai_data as $item) {
|
|
$tarikh = date('Y-m-d', strtotime($item->t_gadai));
|
|
$key = $item->norujukan . '|' . $tarikh;
|
|
|
|
if ($transaksi_tebus->has($key)) {
|
|
$trans = $transaksi_tebus[$key]->first(); // satu transaksi je
|
|
|
|
$masa_gadai = $item->masa;
|
|
$masa_tebus = date('H:i:s', strtotime($trans->created_at));
|
|
$beza_saat = abs(strtotime($masa_tebus) - strtotime($masa_gadai));
|
|
$beza_masa = gmdate("H:i:s", $beza_saat);
|
|
|
|
$matched->push((object)[
|
|
't_gadai' => $item->t_gadai,
|
|
'norujukan' => $item->norujukan,
|
|
'nokp' => $item->nokp,
|
|
'semakbarcode' => $item->semakbarcode,
|
|
'masa' => $masa_gadai,
|
|
'masa_tebus' => $masa_tebus,
|
|
'beza_minit' => $beza_masa,
|
|
'berat' => $item->berat,
|
|
'nilaimarhun' => $item->nilaimarhun,
|
|
'pinjaman' => $item->pinjaman,
|
|
'upah12' => $item->upah12,
|
|
'percentpinj' => $item->percentpinj,
|
|
'teller' => $item->teller,
|
|
'bakipjm' => $item->bakipjm,
|
|
'bakiupah' => $item->bakiupah,
|
|
]);
|
|
}
|
|
}
|
|
|
|
// 5. Data tambahan
|
|
$customer_info = $this->getCustomerInfo($matched);
|
|
$marhun_info = $this->getMarhunGadaiTebusMasa($matched, $cawangan_connection);
|
|
$cawangan_detail = $this->getCawanganDetail($cawangan_connection);
|
|
$summary = $this->getTotalSummaryGadaiTebusMasa($matched);
|
|
|
|
// 6. Gabung akhir
|
|
$report_data = $matched->zip($customer_info, $marhun_info)->map(function ($data) {
|
|
[$gadai, $customer, $marhun] = $data;
|
|
|
|
return [
|
|
'no_rujukan' => $gadai->norujukan,
|
|
'nama' => $customer['nama'] ?? '-',
|
|
'alamat' => $customer['alamat1'] ?? '-',
|
|
'no_kp' => $gadai->nokp,
|
|
'masa' => $gadai->masa,
|
|
'masa_tebus' => $gadai->masa_tebus,
|
|
'beza_minit' => $gadai->beza_minit,
|
|
'marhun' => $marhun,
|
|
'berat' => $gadai->berat,
|
|
'nilai_marhun' => $gadai->nilaimarhun,
|
|
'pinjaman' => $gadai->pinjaman,
|
|
'peratusan_margin_pinjaman' => $gadai->percentpinj,
|
|
'bakipjm' => $gadai->bakipjm,
|
|
'keuntungan' => $gadai->bakiupah,
|
|
'teller' => $gadai->teller,
|
|
't_gadai' => $gadai->t_gadai,
|
|
];
|
|
});
|
|
|
|
$report_data = $report_data->sortBy(function ($item) {
|
|
list($hours, $minutes, $seconds) = explode(':', $item['beza_minit']);
|
|
return ((int)$hours * 3600) + ((int)$minutes * 60) + (int)$seconds;
|
|
})->values();
|
|
|
|
return response()->json([
|
|
'query' => [
|
|
'startDate' => Helper::formatDate($startDate),
|
|
'endDate' => Helper::formatDate($endDate),
|
|
],
|
|
'cawangan_detail' => $cawangan_detail,
|
|
'gadaian' => $report_data,
|
|
'summary_gadaian' => $summary,
|
|
]);
|
|
}
|
|
|
|
private function formatBezaMinit($minit)
|
|
{
|
|
if (is_null($minit)) {
|
|
return '-';
|
|
}
|
|
|
|
$abs = abs($minit);
|
|
$jam = floor($abs / 60);
|
|
$baki = $abs % 60;
|
|
$tanda = $minit < 0 ? '-' : '';
|
|
|
|
return "{$tanda}{$jam} jam {$baki} minit";
|
|
}
|
|
|
|
|
|
private function getTotalGadaiTebusMasa($query_total_gadaitebusmasa)
|
|
{
|
|
$total_gadaitebusmasa = (clone $query_total_gadaitebusmasa)
|
|
->select('gadai.t_gadai',
|
|
'gadai.norujukan',
|
|
'gadai.nokp',
|
|
'gadai.semakbarcode',
|
|
'gadai.masa',
|
|
'gadai.berat',
|
|
'gadai.nilaimarhun',
|
|
'gadai.pinjaman',
|
|
'gadai.upah12',
|
|
'gadai.percentpinj',
|
|
'gadai.teller',
|
|
'gadai.nokp',
|
|
'gadai.bakipjm',
|
|
'gadai.bakiupah',
|
|
DB::raw('TIMEDIFF(TIME(trans.created_at), gadai.masa) as beza_minit'))
|
|
->where('gadai.upah12', '>=', 0.00)
|
|
->orderBy('gadai.teller')
|
|
->orderBy('gadai.sirig')
|
|
->get();
|
|
return $total_gadaitebusmasa;
|
|
}
|
|
|
|
private function getTotalSummaryGadaiTebusMasa($collection)
|
|
{
|
|
return [
|
|
'total_gadaitebusmasa' => $collection->count(),
|
|
'total_berat' => $collection->sum('berat'),
|
|
'total_nilai_marhun' => $collection->sum('nilaimarhun'),
|
|
'total_pembiayaan' => $collection->sum('pinjaman'),
|
|
'total_bakipjm' => $collection->sum('bakipjm'),
|
|
'total_keuntungan' => $collection->sum('bakiupah'),
|
|
];
|
|
}
|
|
|
|
private function getTotalSummaryGadaiTebusMasaGroupByTeller($query_total_gadaitebusmasa)
|
|
{
|
|
|
|
$total_summary_gadaian_groupByTeller = (clone $query_total_gadaitebusmasa)
|
|
->selectRaw('count(gadai.norujukan) as total_gadaitebusmasa,
|
|
sum(gadai.berat) as total_berat,
|
|
sum(gadai.nilaimarhun) as total_nilai_marhun,
|
|
sum(gadai.pinjaman) as total_pembiayaan,
|
|
sum(gadai.bakipjm) as total_bakipjm,
|
|
sum(gadai.bakiupah) as total_keuntungan')
|
|
->where('gadai.upah12', '>=', 0.00)
|
|
->groupBy('gadai.teller')
|
|
->get();
|
|
return $total_summary_gadaian_groupByTeller;
|
|
}
|
|
|
|
private function getMarhunGadaiTebusMasa($total_gadaitebusmasa, $cawangan_connection)
|
|
{
|
|
|
|
$extract_norujukan = $total_gadaitebusmasa->pluck('norujukan');
|
|
|
|
$marhun_gadaitebusmasa = Marhun::on($cawangan_connection)
|
|
->select('norujukan', 'bil', 'marhun')
|
|
->whereIn('norujukan', $extract_norujukan)
|
|
->get()
|
|
->groupBy('norujukan')
|
|
->toArray();
|
|
|
|
$marhun_gadaitebusmasa = array_map(function ($gadaian) {
|
|
return array_map(function ($norujukan) {
|
|
return $norujukan['bil'] . ' ' . $norujukan['marhun'];
|
|
}, $gadaian);
|
|
}, $marhun_gadaitebusmasa);
|
|
|
|
$marhun_gadaitebusmasa = array_map(function ($gadaian) {
|
|
return implode(',', $gadaian);
|
|
}, $marhun_gadaitebusmasa);
|
|
return $marhun_gadaitebusmasa;
|
|
}
|
|
}
|