root
345 lines
13 KiB
PHP
345 lines
13 KiB
PHP
<?php
|
|
|
|
namespace App\Helpers;
|
|
|
|
use App\Models\Kategori;
|
|
use App\Models\BudgetControl;
|
|
use App\Models\FormUpCountry;
|
|
use App\Models\FormMeetingSeminar;
|
|
use App\Models\FormOthers;
|
|
use App\Models\FormEntertaimentPresentation;
|
|
use App\Models\FormVehicleRunningCost;
|
|
use App\Models\UserHasCabang;
|
|
use Illuminate\Support\Carbon;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
class BudgetHelper
|
|
{
|
|
/**
|
|
* Cache memori sementara untuk fungsi jurnal agar loading halaman riwayat tidak berat.
|
|
*/
|
|
private static $jurnalCache = [];
|
|
|
|
/**
|
|
* Daftar status yang akan memotong (menge-hold) sisa budget secara real-time.
|
|
*/
|
|
const HOLD_STATUSES = [
|
|
'Approved 1',
|
|
'Approved 2',
|
|
'Waiting Approval',
|
|
'Validated',
|
|
'Approved',
|
|
'Closed',
|
|
'Completed'
|
|
];
|
|
|
|
public static function getBulkAvailableBudget($budgetControls)
|
|
{
|
|
if ($budgetControls->isEmpty()) return [];
|
|
|
|
$cabangIds = $budgetControls->pluck('cabang_id')->unique()->toArray();
|
|
|
|
// 1. Mapping User per Cabang
|
|
$branchUsers = UserHasCabang::whereIn('cabang_id', $cabangIds)
|
|
->get()
|
|
->groupBy('cabang_id')
|
|
->map(fn($item) => $item->pluck('user_id')->toArray());
|
|
|
|
$allUserIds = $branchUsers->flatten()->unique()->toArray();
|
|
$resultsMap = [];
|
|
|
|
if (empty($allUserIds)) return [];
|
|
|
|
$formModels = [
|
|
'tanggal' => [
|
|
FormUpCountry::class,
|
|
FormVehicleRunningCost::class,
|
|
FormEntertaimentPresentation::class,
|
|
FormOthers::class
|
|
],
|
|
'created_at' => [
|
|
FormMeetingSeminar::class
|
|
]
|
|
];
|
|
|
|
// 2. Tarik data pengeluaran sekaligus dari semua tabel form
|
|
foreach ($formModels as $dateColumn => $models) {
|
|
foreach ($models as $model) {
|
|
$query = $model::whereIn('user_id', $allUserIds)
|
|
->whereIn('status', self::HOLD_STATUSES)
|
|
->select(
|
|
'user_id',
|
|
DB::raw("MONTH($dateColumn) as month"),
|
|
DB::raw("YEAR($dateColumn) as year"),
|
|
DB::raw("SUM(approved_total) as total")
|
|
)
|
|
->groupBy('user_id', 'month', 'year')
|
|
->get();
|
|
|
|
// 3. Masukkan ke Map berdasarkan Cabang_Bulan_Tahun
|
|
foreach ($query as $row) {
|
|
foreach ($branchUsers as $cabangId => $userIds) {
|
|
if (in_array($row->user_id, $userIds)) {
|
|
$monthName = strtolower(date('F', mktime(0, 0, 0, $row->month, 10)));
|
|
$key = "{$cabangId}_{$monthName}_{$row->year}";
|
|
|
|
$resultsMap[$key] = ($resultsMap[$key] ?? 0) + $row->total;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return $resultsMap;
|
|
}
|
|
|
|
/**
|
|
* Menghitung sisa budget (Pagu - Total Hold/Terpakai).
|
|
* Tetap menggunakan kolom tanggal/created_at karena pengajuan yang baru
|
|
* dibuat (Approved 1) belum memiliki final_approved_at.
|
|
*/
|
|
public static function getAvailableBudget($cabang_id, $periode_month, $periode_year)
|
|
{
|
|
$periode_month = strtolower($periode_month);
|
|
$periode_year = (int) $periode_year;
|
|
|
|
$totalPagu = BudgetControl::where('cabang_id', $cabang_id)
|
|
->where('periode_month', $periode_month)
|
|
->where('periode_year', $periode_year)
|
|
->sum('amount');
|
|
|
|
if ($totalPagu <= 0) return 0;
|
|
|
|
$userIds = UserHasCabang::where('cabang_id', $cabang_id)->pluck('user_id')->toArray();
|
|
|
|
if (empty($userIds)) return $totalPagu;
|
|
|
|
$usedBudget = 0;
|
|
$monthNumeric = date('m', strtotime($periode_month));
|
|
|
|
$formModels = [
|
|
FormUpCountry::class,
|
|
FormVehicleRunningCost::class,
|
|
FormEntertaimentPresentation::class,
|
|
FormMeetingSeminar::class,
|
|
FormOthers::class
|
|
];
|
|
|
|
foreach ($formModels as $model) {
|
|
$query = $model::whereIn('user_id', $userIds)
|
|
->whereIn('status', self::HOLD_STATUSES);
|
|
|
|
if ($model === FormMeetingSeminar::class) {
|
|
$query->whereMonth('created_at', $monthNumeric)
|
|
->whereYear('created_at', $periode_year);
|
|
} else {
|
|
$query->whereMonth('tanggal', $monthNumeric)
|
|
->whereYear('tanggal', $periode_year);
|
|
}
|
|
|
|
$usedBudget += $query->sum('approved_total');
|
|
}
|
|
|
|
return $totalPagu - $usedBudget;
|
|
}
|
|
|
|
/**
|
|
* Memvalidasi apakah tanggal expense yang diinput user berada dalam
|
|
* rentang periode aktif (Tanggal 11 s/d 10).
|
|
*/
|
|
public static function checkExpenseInputWindow($tanggal_expense)
|
|
{
|
|
$inputDate = \Carbon\Carbon::parse($tanggal_expense)->startOfDay();
|
|
$today = \Carbon\Carbon::now();
|
|
|
|
$startDay = 11;
|
|
$endDay = 10;
|
|
|
|
// Menentukan jendela periode yang sedang aktif hari ini
|
|
if ($today->day >= $startDay) {
|
|
// Jika hari ini tgl 11 atau lebih: Jendela aktif adalah Tgl 11 bulan ini s/d Tgl 10 bulan depan
|
|
$periodStart = \Carbon\Carbon::create($today->year, $today->month, $startDay)->startOfDay();
|
|
$periodEnd = \Carbon\Carbon::create($today->year, $today->month, $endDay)->addMonth()->endOfDay();
|
|
} else {
|
|
// Jika hari ini tgl 1-10: Jendela aktif adalah Tgl 11 bulan lalu s/d Tgl 10 bulan ini
|
|
$periodStart = \Carbon\Carbon::create($today->year, $today->month, $startDay)->subMonth()->startOfDay();
|
|
$periodEnd = \Carbon\Carbon::create($today->year, $today->month, $endDay)->endOfDay();
|
|
}
|
|
|
|
// Jika tanggal form yang diinput user berada di luar jendela aktif
|
|
if ($inputDate->lt($periodStart) || $inputDate->gt($periodEnd)) {
|
|
return [
|
|
'is_valid' => false,
|
|
'message' => "Gagal! Batas input expense untuk periode aktif saat ini adalah {$periodStart->format('d M Y')} s/d {$periodEnd->format('d M Y')}. Tanggal {$inputDate->format('d M Y')} sudah ditutup atau belum dibuka."
|
|
];
|
|
}
|
|
|
|
return ['is_valid' => true];
|
|
}
|
|
|
|
/**
|
|
* Mengambil nominal per tipe form.
|
|
*/
|
|
public static function getNominalByFormType($userIds, $type, $type_id, $month, $year)
|
|
{
|
|
$monthNumeric = date('m', strtotime($month));
|
|
$used = 0;
|
|
|
|
if ($type == 'Up Country') {
|
|
$used = FormUpCountry::whereIn('user_id', $userIds)
|
|
->whereMonth('tanggal', $monthNumeric)->whereYear('tanggal', $year)
|
|
->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');
|
|
|
|
} elseif ($type == 'Vehicle Running Cost') {
|
|
$used = FormVehicleRunningCost::whereIn('user_id', $userIds)
|
|
->whereMonth('tanggal', $monthNumeric)->whereYear('tanggal', $year)
|
|
->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');
|
|
|
|
} elseif ($type == 'Entertainment') {
|
|
$used = FormEntertaimentPresentation::whereIn('user_id', $userIds)
|
|
->whereMonth('tanggal', $monthNumeric)->whereYear('tanggal', $year)
|
|
->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');
|
|
|
|
} elseif ($type == 'Meeting / Seminar') {
|
|
$used = FormMeetingSeminar::whereIn('user_id', $userIds)
|
|
->whereMonth('created_at', $monthNumeric)->whereYear('created_at', $year)
|
|
->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');
|
|
|
|
} else {
|
|
$used = FormOthers::whereIn('user_id', $userIds)
|
|
->where('kategori_id', $type_id)
|
|
->whereMonth('tanggal', $monthNumeric)->whereYear('tanggal', $year)
|
|
->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');
|
|
}
|
|
|
|
return $used;
|
|
}
|
|
|
|
/**
|
|
* Mengambil tanggal closing.
|
|
*/
|
|
public static function getClosingDate($cabang_id)
|
|
{
|
|
return BudgetControl::where('cabang_id', $cabang_id)
|
|
->orderBy('created_at', 'desc')
|
|
->value('closing_at');
|
|
}
|
|
|
|
/**
|
|
* KALKULASI JURNAL AKUNTANSI
|
|
* Berlaku seragam menggunakan kolom final_approved_at untuk semua form
|
|
* Cut-off Date: Tgl 11 s/d Tgl 13 Bulan Berikutnya
|
|
*/
|
|
/**
|
|
* KALKULASI JURNAL AKUNTANSI (Log & Report)
|
|
* Sinkron dengan 12 Kategori Utama & Akun 800xxx
|
|
*/
|
|
// public static function getKalkulasiJurnal($cabang_id, $periode_month, $periode_year)
|
|
// {
|
|
// $periode_month = strtolower($periode_month);
|
|
// $periode_year = (int) $periode_year;
|
|
|
|
// $cacheKey = "{$cabang_id}_{$periode_month}_{$periode_year}";
|
|
// if (isset(self::$jurnalCache[$cacheKey])) return self::$jurnalCache[$cacheKey];
|
|
|
|
// $userIds = UserHasCabang::where('cabang_id', $cabang_id)->pluck('user_id')->toArray();
|
|
// $totalDebet = 0;
|
|
|
|
// if (!empty($userIds)) {
|
|
// // Ambil Range Tanggal (Contoh: 11 Maret - 13 April)
|
|
// $startDay = env('STARTING_DATE', 11);
|
|
// $closeDay = env('CLOSING_DATE', 13);
|
|
// $monthNumeric = date('m', strtotime($periode_month));
|
|
|
|
// $startDate = Carbon::create($periode_year, $monthNumeric, $startDay)->startOfDay();
|
|
// $endDate = Carbon::create($periode_year, $monthNumeric, $closeDay)->addMonth()->endOfDay();
|
|
|
|
// // Mapping Model dengan pengamanan Typo
|
|
// $formModels = [
|
|
// 'Up Country' => FormUpCountry::class,
|
|
// 'Vehicle Running Cost' => FormVehicleRunningCost::class,
|
|
// 'Entertainment' => FormEntertaimentPresentation::class,
|
|
// 'Entertaiment' => FormEntertaimentPresentation::class,
|
|
// 'Presentation' => FormEntertaimentPresentation::class,
|
|
// 'Meeting / Seminar' => FormMeetingSeminar::class,
|
|
// ];
|
|
|
|
// // Ambil kategori yang punya account_number dan tidak dikecualikan
|
|
// $kategoris = \App\Models\Kategori::whereNotIn('name', ['Cash in Bank BCA', 'Donasi/Gift', 'PPn Masukan', 'Sponsorship'])
|
|
// ->whereNotNull('account_number')
|
|
// ->where('account_number', '!=', '')
|
|
// ->get();
|
|
|
|
// foreach ($kategoris as $kat) {
|
|
// $katName = trim($kat->name);
|
|
|
|
// if (isset($formModels[$katName])) {
|
|
// $model = $formModels[$katName];
|
|
// $query = $model::whereIn('user_id', $userIds)
|
|
// ->whereIn('status', ['Closed', 'Final Approved'])
|
|
// ->whereBetween('final_approved_at', [$startDate, $endDate]);
|
|
|
|
// // PENTING: Filter berdasarkan kategori_id agar data tidak tertukar di tabel yang sama
|
|
// if (\Illuminate\Support\Facades\Schema::hasColumn((new $model)->getTable(), 'kategori_id')) {
|
|
// $query->where('kategori_id', $kat->id);
|
|
// }
|
|
// $totalDebet += $query->sum('approved_total');
|
|
// } else {
|
|
// // Masuk ke Form Others
|
|
// $totalDebet += FormOthers::whereIn('user_id', $userIds)
|
|
// ->where('kategori_id', $kat->id)
|
|
// ->whereIn('status', ['Closed', 'Final Approved'])
|
|
// ->whereBetween('final_approved_at', [$startDate, $endDate])
|
|
// ->sum('approved_total');
|
|
// }
|
|
// }
|
|
// }
|
|
|
|
// self::$jurnalCache[$cacheKey] = $totalDebet;
|
|
// return $totalDebet;
|
|
// }
|
|
|
|
public static function getBulkKalkulasiJurnal(array $lookups)
|
|
{
|
|
if (empty($lookups)) return [];
|
|
|
|
$resultsMap = [];
|
|
$startDay = 11; // Batas awal periode (Contoh: tgl 11)
|
|
$closeDay = 13; // Batas akhir approval (Contoh: tgl 13 bulan depan)
|
|
|
|
foreach ($lookups as $item) {
|
|
$cabId = $item['cabang_id'];
|
|
$monthNumeric = date('m', strtotime($item['month']));
|
|
$year = (int) $item['year'];
|
|
$key = $cabId . '_' . strtolower($item['month']) . '_' . $year;
|
|
|
|
// Penentuan Rentang Tanggal Cut-off Akuntansi
|
|
$startDate = \Illuminate\Support\Carbon::create($year, $monthNumeric, $startDay)->startOfDay();
|
|
$endDate = $startDate->copy()->addMonth()->day($closeDay)->endOfDay();
|
|
|
|
$userIds = \App\Models\UserHasCabang::where('cabang_id', $cabId)->pluck('user_id')->toArray();
|
|
|
|
if (empty($userIds)) {
|
|
$resultsMap[$key] = 0;
|
|
continue;
|
|
}
|
|
|
|
// Menghitung Total Pengeluaran Riil (Status: Closed / Final Approved)
|
|
$tables = ['form_up_country', 'form_vehicle_running_cost', 'form_entertainment_presentation', 'form_meeting_seminar', 'form_others'];
|
|
$totalExpense = 0;
|
|
|
|
foreach ($tables as $table) {
|
|
$totalExpense += \Illuminate\Support\Facades\DB::table($table)
|
|
->whereIn('user_id', $userIds)
|
|
->whereIn('status', ['Closed', 'Final Approved'])
|
|
->whereBetween('final_approved_at', [$startDate, $endDate])
|
|
->sum('approved_total');
|
|
}
|
|
|
|
$resultsMap[$key] = $totalExpense;
|
|
}
|
|
|
|
return $resultsMap;
|
|
}
|
|
} |