367 lines
11 KiB
PHP
367 lines
11 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
namespace App\Helpers;
|
||
|
|
|
||
|
|
use App\Models\Admin;
|
||
|
|
use App\Models\FormEntertaimentPresentation;
|
||
|
|
use App\Models\FormMeetingSeminar;
|
||
|
|
use App\Models\FormOthers;
|
||
|
|
use App\Models\FormUpCountry;
|
||
|
|
use App\Models\FormVehicleRunningCost;
|
||
|
|
use Illuminate\Support\Facades\DB;
|
||
|
|
use App\Models\UserHasCabang;
|
||
|
|
|
||
|
|
class FormHelper
|
||
|
|
{
|
||
|
|
public static function getFormsByUserIds($userIds, $month, $year) {
|
||
|
|
$monthNumber = date('m', strtotime($month . ' 1'));
|
||
|
|
$startDate = "$year-$monthNumber-" . env('STARTING_DATE');
|
||
|
|
|
||
|
|
$nextMonthTimestamp = strtotime("first day of next month", strtotime("$year-$monthNumber-01"));
|
||
|
|
$closingDateNextMonth = date('Y-m-', $nextMonthTimestamp) . env('CLOSING_DATE');
|
||
|
|
|
||
|
|
$users = Admin::with(['region', 'cabang'])
|
||
|
|
->whereIn('id', $userIds)
|
||
|
|
->get();
|
||
|
|
|
||
|
|
$formUpCountryQuery = FormUpCountry::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Up Country' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('tanggal', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formVehicleRunningCostQuery = FormVehicleRunningCost::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Vehicle Running Cost' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('tanggal', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formEntertaimentPresentationQuery = FormEntertaimentPresentation::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Entertainment Presentation' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('tanggal', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formMeetingSeminarQuery = FormMeetingSeminar::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Meeting Seminar' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('created_at', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formOthersQuery = FormOthers::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Others' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('tanggal', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
// Use union to combine queries
|
||
|
|
$forms = $formUpCountryQuery
|
||
|
|
->union($formVehicleRunningCostQuery)
|
||
|
|
->union($formEntertaimentPresentationQuery)
|
||
|
|
->union($formMeetingSeminarQuery)
|
||
|
|
->union($formOthersQuery)
|
||
|
|
->get();
|
||
|
|
|
||
|
|
// Return forms with eager loaded user details
|
||
|
|
return $forms->map(function ($form) use ($users) {
|
||
|
|
$user = $users->firstWhere('id', $form->user_id);
|
||
|
|
$form->user = $user;
|
||
|
|
return $form;
|
||
|
|
});
|
||
|
|
}
|
||
|
|
|
||
|
|
public static function getAllForms($month, $year) {
|
||
|
|
$monthNumber = date('m', strtotime($month . ' 1'));
|
||
|
|
$startDate = "$year-$monthNumber-" . env('STARTING_DATE');
|
||
|
|
|
||
|
|
$nextMonthTimestamp = strtotime("first day of next month", strtotime("$year-$monthNumber-01"));
|
||
|
|
$closingDateNextMonth = date('Y-m-', $nextMonthTimestamp) . env('CLOSING_DATE');
|
||
|
|
|
||
|
|
$users = Admin::with(['region', 'cabang'])
|
||
|
|
->get();
|
||
|
|
|
||
|
|
$formUpCountryQuery = FormUpCountry::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Up Country' as type")
|
||
|
|
)->whereBetween('tanggal', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formVehicleRunningCostQuery = FormVehicleRunningCost::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Vehicle Running Cost' as type")
|
||
|
|
)->whereBetween('tanggal', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formEntertaimentPresentationQuery = FormEntertaimentPresentation::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Entertainment Presentation' as type")
|
||
|
|
)->whereBetween('tanggal', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formMeetingSeminarQuery = FormMeetingSeminar::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Meeting Seminar' as type")
|
||
|
|
)->whereBetween('created_at', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formOthersQuery = FormOthers::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Others' as type")
|
||
|
|
)->whereBetween('tanggal', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
// Combine queries using union
|
||
|
|
$forms = $formUpCountryQuery
|
||
|
|
->union($formVehicleRunningCostQuery)
|
||
|
|
->union($formEntertaimentPresentationQuery)
|
||
|
|
->union($formMeetingSeminarQuery)
|
||
|
|
->union($formOthersQuery)
|
||
|
|
->get();
|
||
|
|
|
||
|
|
// Map users to the forms with eager loaded data
|
||
|
|
return $forms->map(function ($form) use ($users) {
|
||
|
|
$user = $users->firstWhere('id', $form->user_id);
|
||
|
|
$form->user = $user;
|
||
|
|
return $form;
|
||
|
|
});
|
||
|
|
}
|
||
|
|
|
||
|
|
public static function getAllForms2() {
|
||
|
|
// Get all users with eager loading for 'region' and 'cabang'
|
||
|
|
$users = Admin::with(['region', 'cabang'])
|
||
|
|
->get();
|
||
|
|
|
||
|
|
$formUpCountryQuery = FormUpCountry::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Up Country' as type")
|
||
|
|
);
|
||
|
|
|
||
|
|
$formVehicleRunningCostQuery = FormVehicleRunningCost::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Vehicle Running Cost' as type")
|
||
|
|
);
|
||
|
|
|
||
|
|
$formEntertaimentPresentationQuery = FormEntertaimentPresentation::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Entertainment Presentation' as type")
|
||
|
|
);
|
||
|
|
|
||
|
|
$formMeetingSeminarQuery = FormMeetingSeminar::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Meeting Seminar' as type")
|
||
|
|
);
|
||
|
|
|
||
|
|
$formOthersQuery = FormOthers::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Others' as type")
|
||
|
|
);
|
||
|
|
|
||
|
|
// Combine queries using union
|
||
|
|
$forms = $formUpCountryQuery
|
||
|
|
->union($formVehicleRunningCostQuery)
|
||
|
|
->union($formEntertaimentPresentationQuery)
|
||
|
|
->union($formMeetingSeminarQuery)
|
||
|
|
->union($formOthersQuery)
|
||
|
|
->get();
|
||
|
|
|
||
|
|
// Map users to the forms with eager loaded data
|
||
|
|
return $forms->map(function ($form) use ($users) {
|
||
|
|
$user = $users->firstWhere('id', $form->user_id);
|
||
|
|
$form->user = $user;
|
||
|
|
return $form;
|
||
|
|
});
|
||
|
|
}
|
||
|
|
|
||
|
|
public static function getNominalByFormType($cabang_id, $type, $type_id, $month, $year) {
|
||
|
|
$users = UserHasCabang::where('cabang_id', $cabang_id)->get();
|
||
|
|
$userIds = $users->pluck('user_id')->toArray();
|
||
|
|
|
||
|
|
if($type == 'Up Country') {
|
||
|
|
$form = FormUpCountry::select('approved_total')->whereIn('user_id', $userIds)->whereMonth('tanggal', '=', date('m', strtotime($month)))->whereYear('tanggal', '=', $year)->where('status', ['Approved', 'Closed'])->get();
|
||
|
|
} else if($type == 'Vehicle Running Cost') {
|
||
|
|
$form = FormVehicleRunningCost::select('approved_total')->whereIn('user_id', $userIds)->whereMonth('tanggal', '=', date('m', strtotime($month)))->whereYear('tanggal', '=', $year)->where('status', ['Approved', 'Closed'])->get();
|
||
|
|
} else if($type == 'Entertainment') {
|
||
|
|
$form = FormEntertaimentPresentation::select('approved_total')->whereIn('user_id', $userIds)->whereMonth('tanggal', '=', date('m', strtotime($month)))->whereYear('tanggal', '=', $year)->where('status', ['Approved', 'Closed'])->get();
|
||
|
|
} else if($type == 'Meeting / Seminar') {
|
||
|
|
$form = FormMeetingSeminar::select('approved_total')->whereIn('user_id', $userIds)->whereMonth('created_at', '=', date('m', strtotime($month)))->whereYear('created_at', '=', $year)->where('status', ['Approved', 'Closed'])->get();
|
||
|
|
} else {
|
||
|
|
$form = FormOthers::select('approved_total')->whereIn('user_id', $userIds)->whereMonth('tanggal', '=', date('m', strtotime($month)))->whereYear('tanggal', '=', $year)->where('kategori_id', $type_id)->where('status', ['Approved', 'Closed'])->get();
|
||
|
|
}
|
||
|
|
|
||
|
|
$nominal = $form->sum('approved_total');
|
||
|
|
return $nominal;
|
||
|
|
}
|
||
|
|
|
||
|
|
public static function getTotalAmount($userIds, $month, $year) {
|
||
|
|
$monthNumber = date('m', strtotime($month . ' 1'));
|
||
|
|
$startDate = "$year-$monthNumber-" . env('STARTING_DATE');
|
||
|
|
|
||
|
|
$nextMonthTimestamp = strtotime("first day of next month", strtotime("$year-$monthNumber-01"));
|
||
|
|
$closingDateNextMonth = date('Y-m-', $nextMonthTimestamp) . env('CLOSING_DATE');
|
||
|
|
|
||
|
|
$users = Admin::with(['region', 'cabang'])
|
||
|
|
->whereIn('id', $userIds)
|
||
|
|
->get();
|
||
|
|
|
||
|
|
$formUpCountryQuery = FormUpCountry::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Up Country' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('final_approved_at', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formVehicleRunningCostQuery = FormVehicleRunningCost::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Vehicle Running Cost' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('final_approved_at', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formEntertaimentPresentationQuery = FormEntertaimentPresentation::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Entertainment Presentation' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('final_approved_at', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formMeetingSeminarQuery = FormMeetingSeminar::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Meeting Seminar' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('final_approved_at', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
$formOthersQuery = FormOthers::select(
|
||
|
|
'expense_number',
|
||
|
|
'user_id',
|
||
|
|
'total',
|
||
|
|
'approved_total',
|
||
|
|
'status',
|
||
|
|
'account_number',
|
||
|
|
'created_at',
|
||
|
|
DB::raw("'Others' as type")
|
||
|
|
)->whereIn('user_id', $userIds)
|
||
|
|
->whereBetween('final_approved_at', [$startDate, $closingDateNextMonth]);
|
||
|
|
|
||
|
|
// Use union to combine queries
|
||
|
|
$forms = $formUpCountryQuery
|
||
|
|
->union($formVehicleRunningCostQuery)
|
||
|
|
->union($formEntertaimentPresentationQuery)
|
||
|
|
->union($formMeetingSeminarQuery)
|
||
|
|
->union($formOthersQuery)
|
||
|
|
->get();
|
||
|
|
|
||
|
|
// Return forms with eager loaded user details
|
||
|
|
return $forms->map(function ($form) use ($users) {
|
||
|
|
$user = $users->firstWhere('id', $form->user_id);
|
||
|
|
$form->user = $user;
|
||
|
|
return $form;
|
||
|
|
});
|
||
|
|
}
|
||
|
|
}
|