root
407 lines
16 KiB
PHP
407 lines
16 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Http\Controllers\Backend;
|
|
|
|
use App\Http\Controllers\Controller;
|
|
use App\Models\{
|
|
Admin, Cabang, Region, Kategori, BudgetControlLog,
|
|
FormUpCountry, FormEntertaimentPresentation, FormMeetingSeminar,
|
|
FormVehicleRunningCost, FormOthers, UserHasCabang
|
|
};
|
|
use App\Helpers\{FormHelper, BudgetHelper, PeriodeHelper};
|
|
use Illuminate\Support\Facades\{DB, Schema};
|
|
use Illuminate\Support\Carbon;
|
|
use Illuminate\Http\Request;
|
|
use Illuminate\Database\Eloquent\Builder;
|
|
|
|
class ReportController extends Controller
|
|
{
|
|
private function getAuthorizedUserIds(): ?array
|
|
{
|
|
$user = auth()->user();
|
|
$role = $user->getRoleNames()[0] ?? null;
|
|
$userData = $user->getMyCabangAndRegionId();
|
|
|
|
return match ($role) {
|
|
'Admin Region', 'Marketing Operational Manager Region' =>
|
|
($rid = $userData['region']) && $rid !== '-'
|
|
? UserHasCabang::whereIn('cabang_id', Cabang::where('region_id', $rid)->pluck('id'))->pluck('user_id')->toArray()
|
|
: [],
|
|
|
|
'Area Manager Cabang' =>
|
|
($cid = $userData['cabang']) && $cid !== '-'
|
|
? UserHasCabang::where('cabang_id', $cid)->pluck('user_id')->toArray()
|
|
: [],
|
|
|
|
'Medical Representatif' => [$user->id],
|
|
|
|
default => null,
|
|
};
|
|
}
|
|
|
|
private function getFilteredForms(string $modelClass, array $withRelations = [])
|
|
{
|
|
$defaultRelations = ['user.region.cabang.region', 'user.cabang.cabang.region', 'rejector'];
|
|
$query = $modelClass::with(array_merge($defaultRelations, $withRelations));
|
|
|
|
$userIds = $this->getAuthorizedUserIds();
|
|
if ($userIds !== null) {
|
|
$query->whereIn('user_id', $userIds);
|
|
}
|
|
|
|
$this->applyStandardFilters($query, request()->all());
|
|
return $query->orderBy('tanggal', 'desc')->get();
|
|
}
|
|
|
|
/**
|
|
* MODIFIKASI: Filter diubah dari rentang tanggal harian (start/end date)
|
|
* menjadi Rentang Periode Bulan Akuntansi (Bulan berjalan tgl 11 s/d Bulan Depan tgl 13)
|
|
*/
|
|
private function applyStandardFilters(Builder $query, array $params): void
|
|
{
|
|
if (!empty($params['region'])) {
|
|
$query->whereHas('user.region.cabang.region', fn($q) => $q->where('code', $params['region']));
|
|
}
|
|
|
|
if (!empty($params['cabang'])) {
|
|
$query->whereHas('user.cabang.cabang', fn($q) => $q->where('code', $params['cabang']));
|
|
}
|
|
|
|
if (!empty($params['status'])) {
|
|
$query->where('status', $params['status']);
|
|
}
|
|
|
|
// --- PENYEMPURNAAN LOGIKA FILTER PERIODE BULAN CUT-OFF (11 s/d 13 Bulan Berikut) ---
|
|
if (!empty($params['month']) && !empty($params['year'])) {
|
|
$month = strtolower($params['month']);
|
|
$year = (int) $params['year'];
|
|
|
|
[$startDate, $endDate] = PeriodeHelper::getPeriodeRange($month, $year);
|
|
|
|
// Semua pencarian report kategori mengacu pada tanggal final approval/closing dalam rentang periode ini
|
|
$query->whereBetween('final_approved_at', [$startDate, $endDate]);
|
|
}
|
|
}
|
|
|
|
public function upcountry()
|
|
{
|
|
$params = request()->all();
|
|
return view('backend.pages.report.upcountry', [
|
|
'pageInfo' => ['title' => 'Reports Form Up Country'],
|
|
'forms' => $this->getFilteredForms(FormUpCountry::class, ['rayon']),
|
|
'cabang' => Cabang::all(),
|
|
'regions' => Region::all(),
|
|
'year' => $params['year'] ?? Carbon::now()->year,
|
|
'month' => $params['month'] ?? Carbon::now()->format('F'),
|
|
]);
|
|
}
|
|
|
|
public function vehicle()
|
|
{
|
|
$params = request()->all();
|
|
return view('backend.pages.report.vehicle', [
|
|
'pageInfo' => ['title' => 'Reports Form Vehicle Running Cost'],
|
|
'forms' => $this->getFilteredForms(FormVehicleRunningCost::class),
|
|
'cabang' => Cabang::all(),
|
|
'regions' => Region::all(),
|
|
'year' => $params['year'] ?? Carbon::now()->year,
|
|
'month' => $params['month'] ?? Carbon::now()->format('F'),
|
|
]);
|
|
}
|
|
|
|
public function entertainment()
|
|
{
|
|
$params = request()->all();
|
|
return view('backend.pages.report.entertainment', [
|
|
'pageInfo' => ['title' => 'Reports Form Entertainment & Presentation'],
|
|
'forms' => $this->getFilteredForms(FormEntertaimentPresentation::class, ['attachments']),
|
|
'cabang' => Cabang::all(),
|
|
'regions' => Region::all(),
|
|
'year' => $params['year'] ?? Carbon::now()->year,
|
|
'month' => $params['month'] ?? Carbon::now()->format('F'),
|
|
]);
|
|
}
|
|
|
|
public function meeting()
|
|
{
|
|
$params = request()->all();
|
|
return view('backend.pages.report.meeting', [
|
|
'pageInfo' => ['title' => 'Reports Form Meeting & Seminar'],
|
|
'forms' => $this->getFilteredForms(FormMeetingSeminar::class),
|
|
'cabang' => Cabang::all(),
|
|
'regions' => Region::all(),
|
|
'year' => $params['year'] ?? Carbon::now()->year,
|
|
'month' => $params['month'] ?? Carbon::now()->format('F'),
|
|
]);
|
|
}
|
|
|
|
public function others()
|
|
{
|
|
$params = request()->all();
|
|
return view('backend.pages.report.others', [
|
|
'pageInfo' => ['title' => 'Reports Form Others'],
|
|
'forms' => $this->getFilteredForms(FormOthers::class, ['kategori']),
|
|
'cabang' => Cabang::all(),
|
|
'regions' => Region::all(),
|
|
'year' => $params['year'] ?? Carbon::now()->year,
|
|
'month' => $params['month'] ?? Carbon::now()->format('F'),
|
|
]);
|
|
}
|
|
|
|
public function all()
|
|
{
|
|
$params = request()->all();
|
|
$userFilter = $this->getAuthorizedUserIds();
|
|
$availableBudget = 0;
|
|
|
|
$selectedMonth = strtolower($params['month'] ?? Carbon::now()->format('F'));
|
|
$selectedYear = (int) ($params['year'] ?? Carbon::now()->year);
|
|
|
|
if (!empty($params['cabang'])) {
|
|
$cabang = Cabang::where('code', $params['cabang'])->first();
|
|
if ($cabang) {
|
|
$availableBudget = BudgetHelper::getAvailableBudget($cabang->id, $selectedMonth, $selectedYear);
|
|
$uIds = UserHasCabang::where('cabang_id', $cabang->id)->pluck('user_id')->toArray();
|
|
$userFilter = $userFilter === null ? $uIds : array_values(array_intersect($userFilter, $uIds));
|
|
}
|
|
}
|
|
|
|
// Sinkronisasi logika rentang tanggal 11 s/d 13 bulan depan untuk database konsolidasi (All Forms)
|
|
[$start, $end] = PeriodeHelper::getPeriodeRange($selectedMonth, $selectedYear);
|
|
|
|
$tables = [
|
|
'form_others' => 'bukti_total',
|
|
'form_up_country' => 'NULL',
|
|
'form_vehicle_running_cost' => 'bukti_total',
|
|
'form_meeting_seminar' => 'NULL',
|
|
'form_entertainment_presentation' => 'bukti_total'
|
|
];
|
|
|
|
$formsQuery = null;
|
|
|
|
foreach ($tables as $table => $mainProofCol) {
|
|
$query = DB::table($table)->selectRaw("
|
|
id,
|
|
expense_number,
|
|
'$table' as type,
|
|
user_id,
|
|
total,
|
|
approved_total,
|
|
account_number,
|
|
status,
|
|
final_approved_at as report_date,
|
|
created_at,
|
|
$mainProofCol as main_proof,
|
|
rejected_by,
|
|
rejected_at,
|
|
remarks
|
|
")
|
|
->when($userFilter, fn($q) => $q->whereIn('user_id', $userFilter))
|
|
->when(!empty($params['status']), fn($q) => $q->where('status', $params['status']))
|
|
// Kunci dengan rentang tanggal closing resmi 11 s/d 13 bulan berikutnya
|
|
->whereBetween('final_approved_at', [$start, $end])
|
|
->when(Schema::hasColumn($table, 'deleted_at'), fn($q) => $q->whereNull('deleted_at'));
|
|
|
|
$formsQuery = $formsQuery ? $formsQuery->unionAll($query) : $query;
|
|
}
|
|
|
|
$forms = collect();
|
|
if ($formsQuery) {
|
|
$forms = DB::query()->fromSub($formsQuery, 'forms')
|
|
->orderByDesc('report_date')
|
|
->paginate(100)->appends($params);
|
|
|
|
$items = collect($forms->items());
|
|
$formIds = $items->pluck('id');
|
|
|
|
$submitterIds = $items->pluck('user_id')->filter()->unique();
|
|
$rejectorIds = $items->pluck('rejected_by')->filter()->unique();
|
|
$allAdminIds = $submitterIds->merge($rejectorIds)->unique();
|
|
|
|
$admins = Admin::with(['region.cabang.region', 'cabang.cabang.region'])
|
|
->whereIn('id', $allAdminIds)
|
|
->get()
|
|
->keyBy('id');
|
|
|
|
$attachments = \App\Models\AttachmentForm::whereIn('form_id', $formIds)
|
|
->get()
|
|
->groupBy(fn($q) => $q->table_name . '_' . $q->form_id);
|
|
|
|
$forms->getCollection()->transform(function ($item) use ($admins, $attachments) {
|
|
$item->user = $admins->get($item->user_id);
|
|
$item->rejector = $admins->get($item->rejected_by);
|
|
$item->type_label = ucwords(str_replace(['form_', '_'], ['', ' '], (string)$item->type));
|
|
|
|
$key = $item->type . '_' . $item->id;
|
|
$item->extra_attachments = $attachments->get($key) ?? collect();
|
|
|
|
return $item;
|
|
});
|
|
}
|
|
|
|
return view('backend.pages.report.all', [
|
|
'pageInfo' => ['title' => 'Reports For All Forms'],
|
|
'forms' => $forms,
|
|
'cabangs' => Cabang::all(),
|
|
'regions' => Region::all(),
|
|
'availableBudget' => $availableBudget,
|
|
'year' => $selectedYear,
|
|
'month' => $selectedMonth,
|
|
]);
|
|
}
|
|
|
|
public function jurnal()
|
|
{
|
|
$user = auth()->user();
|
|
$role = $user->getRoleNames()[0] ?? null;
|
|
$userData = $user->getMyCabangAndRegionId();
|
|
|
|
$regions = Region::all();
|
|
$cabangs = Cabang::all();
|
|
|
|
if (in_array($role, ['Admin Region', 'Marketing Operational Manager Region'])) {
|
|
$regions = Region::where('id', $userData['region'])->get();
|
|
$cabangs = Cabang::where('region_id', $userData['region'])->get();
|
|
} elseif ($role == 'Area Manager Cabang') {
|
|
$cabangs = Cabang::where('id', $userData['cabang'])->get();
|
|
$regions = Region::where('id', $cabangs->first()->region_id ?? 0)->get();
|
|
}
|
|
|
|
return view('backend.pages.report.jurnal', [
|
|
'pageInfo' => ['title' => 'Generate Reports For Jurnal'],
|
|
'cabang' => $cabangs,
|
|
'regions' => $regions
|
|
]);
|
|
}
|
|
|
|
public function generateJurnal(Request $request)
|
|
{
|
|
$request->validate([
|
|
'cabang' => 'required',
|
|
'year' => 'required',
|
|
'month' => 'required',
|
|
]);
|
|
|
|
$params = $request->all();
|
|
$accounting_period = $params['accounting_period'] ?? '-';
|
|
|
|
$excluded = ['Cash in Bank BCA', 'Donasi/Gift', 'PPn Masukan', 'Vehicle Maintenance', 'Bahan Promosi', 'Sponsorship'];
|
|
$kategoris = \App\Models\Kategori::whereNotIn('name', $excluded)->get();
|
|
$kategori_bca = \App\Models\Kategori::where('name', 'Cash in Bank BCA')->first();
|
|
|
|
$cabang = \App\Models\Cabang::where('code', $params['cabang'])->firstOrFail();
|
|
$year = (int) $params['year'];
|
|
$month = strtolower($params['month']);
|
|
|
|
// Memanggil rentang baru 11 s/d 13 bulan depan untuk pembuatan Jurnal Akurat
|
|
[$startDate, $endDate] = PeriodeHelper::getPeriodeRange($month, $year);
|
|
|
|
$pettyCashAmount = \App\Models\BudgetControl::where('cabang_id', $cabang->id)
|
|
->where('periode_year', $year)
|
|
->where('periode_month', $month)
|
|
->sum('amount');
|
|
|
|
$budget = \App\Models\BudgetControlLog::where('cabang_id', $cabang->id)
|
|
->where('periode_year', $year)
|
|
->where('periode_month', $month)
|
|
->orderByDesc('created_at')->first();
|
|
|
|
$userIds = \App\Models\UserHasCabang::where('cabang_id', $cabang->id)->pluck('user_id');
|
|
|
|
$formModels = [
|
|
'up country' => \App\Models\FormUpCountry::class,
|
|
'vehicle running cost' => \App\Models\FormVehicleRunningCost::class,
|
|
'entertainment' => \App\Models\FormEntertaimentPresentation::class,
|
|
'presentation' => \App\Models\FormEntertaimentPresentation::class,
|
|
'sponsorship' => \App\Models\FormEntertaimentPresentation::class,
|
|
'meeting / seminar' => \App\Models\FormMeetingSeminar::class,
|
|
];
|
|
|
|
$nominals = [];
|
|
foreach ($kategoris as $kategori) {
|
|
$katKey = strtolower(trim($kategori->name));
|
|
$sum = 0;
|
|
|
|
if (isset($formModels[$katKey])) {
|
|
$model = $formModels[$katKey];
|
|
|
|
$query = $model::whereIn('user_id', $userIds)
|
|
->whereBetween('final_approved_at', [$startDate, $endDate])
|
|
->where('status', 'Closed');
|
|
|
|
if ($model === \App\Models\FormEntertaimentPresentation::class) {
|
|
$query->where('kategori_id', $kategori->id)
|
|
->where(function($q) use ($katKey) {
|
|
$q->where('jenis', 'LIKE', '%' . $katKey . '%');
|
|
});
|
|
} else {
|
|
if (\Illuminate\Support\Facades\Schema::hasColumn((new $model)->getTable(), 'kategori_id')) {
|
|
$query->where('kategori_id', $kategori->id);
|
|
}
|
|
}
|
|
|
|
$sum = $query->sum('approved_total');
|
|
} else {
|
|
$sum = \App\Models\FormOthers::whereIn('user_id', $userIds)
|
|
->where('kategori_id', $kategori->id)
|
|
->whereBetween('final_approved_at', [$startDate, $endDate])
|
|
->where('status', 'Closed')
|
|
->sum('approved_total');
|
|
}
|
|
$nominals[$kategori->id] = $sum;
|
|
}
|
|
|
|
$totalDebet = array_sum($nominals);
|
|
|
|
return view('backend.pages.report.generate_jurnal', [
|
|
'pageInfo' => ['title' => 'Generate Reports For Jurnal'],
|
|
'kategori' => $kategoris,
|
|
'cabang' => $cabang,
|
|
'year' => $year,
|
|
'month' => $month,
|
|
'kategori_bca' => $kategori_bca,
|
|
'nominals' => $nominals,
|
|
'budget' => $budget,
|
|
'totalKategori' => $totalDebet,
|
|
'pettyCashAmount' => $pettyCashAmount,
|
|
'cashInBca' => $pettyCashAmount - $totalDebet,
|
|
'accounting_period' => $accounting_period,
|
|
]);
|
|
}
|
|
|
|
public function reportRejected(Request $request)
|
|
{
|
|
$models = [
|
|
\App\Models\FormUpCountry::class,
|
|
\App\Models\FormEntertaimentPresentation::class,
|
|
\App\Models\FormVehicleRunningCost::class,
|
|
\App\Models\FormMeetingSeminar::class,
|
|
\App\Models\FormOthers::class
|
|
];
|
|
|
|
$allRejected = collect();
|
|
|
|
foreach ($models as $model) {
|
|
$data = $model::with(['user', 'cabang', 'rejector'])
|
|
->where('status', 'Rejected')
|
|
->when($request->start_date, function ($query) use ($request) {
|
|
return $query->whereBetween('rejected_at', [$request->start_date, $request->end_date]);
|
|
})
|
|
->get();
|
|
|
|
$allRejected = $allRejected->concat($data);
|
|
}
|
|
|
|
$allRejected = $allRejected->sortByDesc('rejected_at');
|
|
|
|
return view('backend.report.report_rejected', [
|
|
'data' => $allRejected,
|
|
'start_date' => $request->start_date,
|
|
'end_date' => $request->end_date,
|
|
'year' => $request->year ?? Carbon::now()->year,
|
|
'month' => $request->month ?? Carbon::now()->format('F'),
|
|
]);
|
|
}
|
|
} |