File manager

File manager - Edit - /home/autoph/public_html/projects/app/Http/Controllers/API/v1/ReportControllerJson

Back
<?php namespace App\Http\Controllers\API\v1; use App\Http\Controllers\Controller; use App\Models\Employee; use App\Models\EmployeeBenefits; use App\Models\EmployeeLeave; use Carbon\Carbon; use DateInterval; use DatePeriod; use DateTime; use Illuminate\Http\Request; use Illuminate\Http\Response; use Illuminate\Support\Facades\Validator; use Illuminate\Support\Facades\DB; class ReportController extends Controller { public function generateReport(Request $request) { // dd($request->all()); // Mapping module numbers to report generation methods $reportGenerators = [ '1' => 'generateLeaveReport', '2' => 'generateOvertimeReport', '3' => 'generateMasterlistReport', '4' => 'generateSalariesReport', '5' => 'generateRolesReport', '6' => 'generateNoScheduleReport', '7' => 'generateAbsentReport', '8' => 'generateTardinessReport', '9' => 'generateBirthdaysReport', '10' => 'generateUndertimeReport', '11' => 'generateDTRReport', '12' => 'generateTravelReport', '13' => 'generateCompanyBenefitsReport', '14' => 'generateLeaveDetailed', '15' => 'generateContributionReport', '16' => 'generateOvertimeCompanyReport' ]; $module = $request->modules; if (array_key_exists($module, $reportGenerators)) { $method = $reportGenerators[$module]; return $this->$method($request); } return response()->json(['message' => 'You need to select a valid module']); } public function generateLeaveReport($request) { DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'from_date' => 'required\|date', 'to_date' => 'required\|date', 'leave_id' => 'nullable\|integer', 'status' => 'nullable\|string', ]); // Start the query $query = DB::table('employee_leaves') ->join('employees', 'employee_leaves.employee_id', '=', 'employees.employee_id') ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id'); // Apply filters if ($request->filled('leave_id')) { $query->where('employee_leaves.leave_id', $request->leave_id); } if ($request->filled('status')) { $query->where('employee_leaves.status', $request->status); } if ($request->filled('from_date') && $request->filled('to_date')) { $fromDate = Carbon::parse($request->from_date)->startOfDay(); $toDate = Carbon::parse($request->to_date)->endOfDay(); $query->whereBetween('employee_leaves.date_from', [$fromDate, $toDate]) ->whereBetween('employee_leaves.date_to', [$fromDate, $toDate]); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Dynamically add columns based on request if (in_array('status', $selectedColumns)) { $transformedColumns[] = DB::raw(" CASE WHEN employee_leaves.status = 0 THEN 'Pending / For Recommendation' WHEN employee_leaves.status = 1 THEN 'For Approval' WHEN employee_leaves.status = 2 THEN 'Approved' ELSE 'Denied' END as status "); } if (in_array('leave_id', $selectedColumns)) { $transformedColumns[] = 'leave_types.name as leave_id'; } if (in_array('without_pay', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.without_pay = 0 THEN 'No' ELSE 'Yes' END as without_pay"); } if (in_array('half_day', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.half_day = 0 THEN 'No' ELSE 'Yes' END as half_day"); } if (in_array('description', $selectedColumns)) { $transformedColumns[] = 'employee_leaves.description as description'; } if (in_array('created_at', $selectedColumns)) { $transformedColumns[] = 'employee_leaves.created_at as created_at'; } if (in_array('recommending_id', $selectedColumns)) { $query->leftJoin('employees as recommending_employee', 'employee_leaves.recommending_id', '=', 'recommending_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id"); } if (in_array('approver_id', $selectedColumns)) { $query->leftJoin('employees as approver_employee', 'employee_leaves.approver_id', '=', 'approver_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = 'employees.employee_id'; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $leaves = $query->get(); // Return the response as JSON return response()->json([ 'data' => $leaves, 'total_hours' => $query->sum('hours'), 'total_days' => $query->sum('days'), ]); } public function generateOvertimeReport($request) { // dd($request->all()); // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'from_date' => 'required\|date', 'to_date' => 'required\|date', 'charge_to' => 'nullable\|integer', 'status' => 'nullable\|string', ]); // Start the query $query = DB::table('employee_overtimes') ->join('employees', 'employee_overtimes.employee_id', '=', 'employees.employee_id') ->leftjoin('companies', 'employee_overtimes.charge_to', '=', 'companies.id'); // Apply filters if ($request->filled('charge_to')) { $query->where('employee_overtimes.charge_to', $request->charge_to); } if ($request->filled('status')) { $query->where('employee_overtimes.status', $request->status); } if ($request->filled('from_date') && $request->filled('to_date')) { $fromDate = Carbon::parse($request->from_date)->startOfDay(); $toDate = Carbon::parse($request->to_date)->endOfDay(); $query->whereBetween('employee_overtimes.date_from', [$fromDate, $toDate]); $query->whereBetween('employee_overtimes.date_to', [$fromDate, $toDate]); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Conditionally include status if 'status' is in $columns if (in_array('status', $selectedColumns)) { $transformedColumns[] = DB::raw(" CASE WHEN employee_overtimes.status = 0 THEN 'Pending / For Recommendation' WHEN employee_overtimes.status = 1 THEN 'For Approval' WHEN employee_overtimes.status = 2 THEN 'Approved' ELSE 'Denied' END as status "); } // Handle transformed columns dynamically if (in_array('charge_to', $selectedColumns)) { $transformedColumns[] = 'companies.name as charge_to'; } if (in_array('meal', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.meal = 0 THEN 'No' ELSE 'Yes' END as meal"); } if (in_array('transportation', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.transportation = 0 THEN 'No' ELSE 'Yes' END as transportation"); } if (in_array('description', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_overtimes.description as description'); } if (in_array('created_at', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_overtimes.created_at as created_at'); } // Conditionally join employees for recommending_id if (in_array('recommending_id', $selectedColumns)) { $query->leftJoin('employees as recommending_employee', 'employee_overtimes.recommending_id', '=', 'recommending_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id"); } // Conditionally join employees for approver_id if (in_array('approver_id', $selectedColumns)) { $query->leftJoin('employees as approver_employee', 'employee_overtimes.approver_id', '=', 'approver_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $overtime = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $overtime, 'total_hours' => $query->sum('hours'), // This line can also be used if not grouping ]); } public function generateMasterlistReport($request) { DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'date_hired_from' => 'required\|date', 'date_hired_to' => 'required\|date' ]); $query = DB::table('employees') ->join('personal_information', 'personal_information.employee_id', '=', 'employees.employee_id'); if ($request->filled('date_hired_from') && $request->filled('date_hired_to')) { $fromDate = Carbon::parse($request->date_hired_from)->startOfDay(); $toDate = Carbon::parse($request->date_hired_to)->endOfDay(); $query->whereBetween('employees.date_hired', [$fromDate, $toDate]); } $personalInformationColumns = ['age', 'gender', 'religion', 'tin', 'philhealth', 'citizenship', 'sss', 'pagibig', 'civil_status', 'hair_color', 'blood_type', 'eye_color', 'height', 'weight']; $selectedColumns = array_map(function($column) use ($personalInformationColumns) { if (in_array($column, $personalInformationColumns)) { return 'personal_information.' . $column; } return 'employees.' . $column; }, $request->columns); $transformedColumns = []; if (in_array('employees.company_id', $selectedColumns)) { $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id'); $transformedColumns[] = DB::raw("companies.name as company_id"); } if (in_array('employees.position_id', $selectedColumns)) { $query->leftJoin('job_titles', 'job_titles.id', '=', 'employees.position_id'); $transformedColumns[] = DB::raw("job_titles.name as position_id"); } if (in_array('employees.dealer_id', $selectedColumns)) { $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id'); $transformedColumns[] = DB::raw("dealerships.name as dealer_id"); } if (in_array('employees.employment_status_id', $selectedColumns)) { $query->leftJoin('job_classifications', 'job_classifications.id', '=', 'employees.employment_status_id'); $transformedColumns[] = DB::raw("job_classifications.name as employment_status_id"); } if (in_array('employees.group_id', $selectedColumns)) { $query->leftJoin('groups', 'groups.id', '=', 'employees.group_id'); $transformedColumns[] = DB::raw("groups.name as group_id"); } if (in_array('employees.division_id', $selectedColumns)) { $query->leftJoin('divisions', 'divisions.id', '=', 'employees.division_id'); $transformedColumns[] = DB::raw("divisions.name as division_id"); } if (in_array('employees.department_id', $selectedColumns)) { $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id'); $transformedColumns[] = DB::raw("departments.name as department_id"); } if (in_array('employees.section_id', $selectedColumns)) { $query->leftJoin('sections', 'sections.id', '=', 'employees.section_id'); $transformedColumns[] = DB::raw("sections.name as section_id"); } if (in_array('employees.unit_id', $selectedColumns)) { $query->leftJoin('units', 'units.id', '=', 'employees.unit_id'); $transformedColumns[] = DB::raw("units.name as unit_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $masterlist = $query->get(); // dd($transformedColumns); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $masterlist, ]); } public function generateSalariesReport($request) { // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'salary_effectivity_date' => 'required\|date', ]); // Start the query $query = DB::table('salary_and_wages') ->join('employees', 'salary_and_wages.employee_id', '=', 'employees.employee_id'); if ($request->filled('salary_effectivity_date')) { $fromDate = Carbon::parse($request->salary_effectivity_date)->startOfDay(); $query->where('salary_and_wages.salary_effectivity_date', '>=', $fromDate); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Handle transformed columns dynamically if ($request->filled('payroll_rate')) { $query->where('salary_and_wages.payroll_rate', $request->payroll_rate); } if ($request->filled('salary_rate')) { $query->whereBetween('salary_and_wages.salary_rate', [$request->salary_rate['min'], $request->salary_rate['max']]); } if ($request->filled('payment_type')) { $query->where('salary_and_wages.payment_type', $request->payment_type); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $salaries = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $salaries ]); } public function generateRolesReport($request) { // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', ]); // Start the query $query = DB::table('employees') ->join('users', 'employees.employee_id', '=', 'users.employee_id') ->join('user_roles', 'users.id', '=', 'user_roles.user_id') ->join('roles', 'user_roles.role_id', '=', 'roles.id') ->join('role_permissions', 'roles.id', '=', 'role_permissions.role_id') ->join('modules', 'role_permissions.module_id', '=', 'modules.id') ->join('menus', 'modules.menu_id', '=', 'menus.id') ->where('menus.system_id', 3); // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; if (in_array('roles_name', $selectedColumns)) { $transformedColumns[] = DB::raw("roles.name as roles_name"); } if (in_array('menu_name', $selectedColumns)) { $transformedColumns[] = DB::raw("menus.name as menu_name"); } if (in_array('menu_description', $selectedColumns)) { $transformedColumns[] = DB::raw("menus.description as menu_description"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns // $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($transformedColumns); // Fetch the results $roles = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $roles ]); } public function generateNoScheduleReport($request) { DB::enableQueryLog(); $query = DB::table('employees') ->leftJoin('employee_schedules', 'employees.employee_id', '=', 'employee_schedules.employee_id') ->where('employee_schedules.employee_id', NULL) ->where('employees.enabled', 1); $transformedColumns = []; if ($request->company_id) { $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id'); $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id'); $query->where('employees.dealer_id', $request->dealer_id); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; $query->select($transformedColumns); $noSchedule = $query->get(); // Return the response as JSON return response()->json([ 'data' => $noSchedule ]); } public function generateAbsentReport($request) { DB::enableQueryLog(); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { // Fetch employees individually $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } else { $employeeList = Employee::where('enabled', 1); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } // Get all employees that match the criteria $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get(); } $employeeAttendance = new EmployeeAttendanceController(); $start_date = new DateTime($request->start_date); $end_date = new DateTime($request->end_date); $end_date->modify('+1 day'); $period = new DatePeriod($start_date, new DateInterval('P1D'), $end_date); $data = []; foreach ($employees as $employee) { $employeeData = []; foreach ($period as $date) { $formattedDate = $date->format('Y-m-d'); $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id); $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id); $holiday = $employeeAttendance->getHoliday($formattedDate); $leave = $employeeAttendance->getLeave($formattedDate, $employee->employee_id); $absent = $employeeAttendance->addAbsenceData($employeeData, $timelogs, $leave, $holiday, $schedule, $formattedDate); if ($absent) { $employeeData[] = $formattedDate; } } $data[] = [ 'employee_id' => $employee->employee_id, 'firstname' => $employee->firstname, 'lastname' => $employee->lastname, 'absent' => $employeeData, ]; } return response()->json([ 'data' => $data ]); } public function generateTardinessReport($request) { DB::enableQueryLog(); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { // Fetch employees individually $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } else { $employeeList = Employee::where('enabled', 1); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } // Get all employees that match the criteria $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get(); } $employeeAttendance = new EmployeeAttendanceController(); $start_date = new DateTime($request->start_date); $end_date = new DateTime($request->end_date); $end_date->modify('+1 day'); $period = new DatePeriod($start_date, new DateInterval('P1D'), $end_date); $data = []; foreach ($employees as $employee) { $employeeData = []; foreach ($period as $date) { $formattedDate = $date->format('Y-m-d'); $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id); $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id); $tardiness = $employeeAttendance->getTardiness($timelogs, $schedule); if ($tardiness) { $employeeData[] = $formattedDate.'\|'.$tardiness; } } $data[] = [ 'employee_id' => $employee->employee_id, 'firstname' => $employee->firstname, 'lastname' => $employee->lastname, 'tardiness' => $employeeData, ]; } return response()->json([ 'data' => $data ]); } public function generateBirthdaysReport($request) { $employeeList = Employee::where('employees.enabled', 1) ->join('personal_information', 'employees.employee_id', '=', 'personal_information.employee_id'); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } $employees = $employeeList->select('employees.employee_id', 'firstname', 'lastname', 'birth_date') ->whereRaw('MONTH(birth_date) IN (' . implode(',', $request->month) . ')') ->get(); return response()->json([ 'data' => $employees ]); } public function generateUndertimeReport($request) { DB::enableQueryLog(); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } else { $employeeList = Employee::where('enabled', 1); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } // Get all employees that match the criteria $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get(); } $employeeAttendance = new EmployeeAttendanceController(); $start_date = new DateTime($request->start_date); $end_date = new DateTime($request->end_date); $end_date->modify('+1 day'); $period = new DatePeriod($start_date, new DateInterval('P1D'), $end_date); $data = []; foreach ($employees as $employee) { $employeeData = []; foreach ($period as $date) { $formattedDate = $date->format('Y-m-d'); $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id); $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id); $undertime = $employeeAttendance->getUndertime($timelogs, $schedule); if ($undertime) { $employeeData[] = $formattedDate.'\|'.$undertime; } } $data[] = [ 'employee_id' => $employee->employee_id, 'firstname' => $employee->firstname, 'lastname' => $employee->lastname, 'undertime' => $employeeData, ]; } return response()->json([ 'data' => $data ]); } public function generateDTRReport($request) { $end_date = date('Y-m-d', strtotime($request->end_date . '+1 day')); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { $employeeList = Employee::select('employee_id', 'firstname', 'lastname', 'company_id', 'dealer_id')->with([ 'timelog' => function ($query) use ($request, $end_date) { $query->whereBetween('datetimelog', [$request->start_date, $end_date]); }, 'company:id,name', 'dealership:id,name' ]) ->whereIn('employee_id', $request->employee_id) ->where(['enabled' => 1]); // $employees = Employee::select('employee_id', 'firstname', 'lastname') // ->whereIn('employee_id', $request->employee_id) // ->get(); } else { $employeeList = Employee::select('employee_id', 'firstname', 'lastname')->with([ 'timelog' => function ($query) use ($request, $end_date) { $query->whereBetween('datetimelog', [$request->start_date, $end_date]); }, 'company', 'department' ])->where(['enabled' => 1]); // $employeeList = Employee::where('enabled', 1); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } // Get all employees that match the criteria $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get(); } $data = $employeeList->get(); return response()->json([ 'data' => $data ]); } public function generateTravelReport($request) { DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'from_date' => 'required\|date', 'to_date' => 'required\|date', 'status' => 'nullable\|string', ]); // Start the query $query = DB::table('employee_obs') ->join('employees', 'employee_obs.employee_id', '=', 'employees.employee_id'); if ($request->company_id) { $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->where('employees.dealer_id', $request->dealer_id); } if ($request->filled('status')) { $query->where('employee_obs.status', $request->status); } if ($request->filled('from_date') && $request->filled('to_date')) { $fromDate = Carbon::parse($request->from_date)->startOfDay(); $toDate = Carbon::parse($request->to_date)->endOfDay(); $query->whereBetween('employee_obs.date_from', [$fromDate, $toDate]); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Conditionally include status if 'status' is in $columns if (in_array('status', $selectedColumns)) { $transformedColumns[] = DB::raw(" CASE WHEN employee_obs.status = 0 THEN 'Pending / For Recommendation' WHEN employee_obs.status = 1 THEN 'For Approval' WHEN employee_obs.status = 2 THEN 'Approved' ELSE 'Denied' END as status "); } if (in_array('date_from', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_obs.date_from as date_from'); } if (in_array('date_to', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_obs.date_to as date_to'); } if (in_array('description', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_obs.description as description'); } if (in_array('created_at', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_obs.created_at as created_at'); } // Conditionally join employees for recommending_id if (in_array('recommending_id', $selectedColumns)) { $query->leftJoin('employees as recommending_employee', 'employee_obs.recommending_id', '=', 'recommending_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id"); } // Conditionally join employees for approver_id if (in_array('approver_id', $selectedColumns)) { $query->leftJoin('employees as approver_employee', 'employee_obs.approver_id', '=', 'approver_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'date_from'; }); $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'date_to'; }); $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $ob = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $ob, ]); } public function generateCompanyBenefitsReport($request) { $data = []; $employeeCountPerCompany = DB::table('employees as e') ->join('companies as c', 'e.company_id', '=', 'c.id') ->select('c.id', 'c.name', 'c.code', DB::raw('COUNT(e.company_id) as employee_count_per_company')) ->where('e.enabled', 1) ->groupBy('c.name', 'e.company_id') ->get(); foreach($employeeCountPerCompany as $company){ $data[$company->id]['company_id'] = $request->id; $data[$company->id]['company_name'] = $company->name; $data[$company->id]['company_code'] = $company->code; $data[$company->id]['employee_count'] = $company->employee_count_per_company; $employeeQuery = Employee::select('employee_id')->where('company_id', $request->id); $employee_ids = $employeeQuery->pluck('employee_id'); $vacation_counts = EmployeeLeave::where('leave_id', 2) ->whereIn('employee_id', $employee_ids) ->selectRaw(' SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_vl_wp, SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_vl_wop ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR VACATION LEAVE $data[$company->id]['total_vl_wp'] = $vacation_counts->total_vl_wp; $data[$company->id]['total_vl_wop'] = $vacation_counts->total_vl_wop; $sick_counts = EmployeeLeave::where('leave_id', 3) ->whereIn('employee_id', $employee_ids) ->selectRaw(' SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_sl_wp, SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_sl_wop ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR SICK LEAVE $data[$company->id]['total_sl_wp'] = $sick_counts->total_sl_wp; $data[$company->id]['total_sl_wop'] = $sick_counts->total_sl_wop; // TOTAL BEREAVEMENT LEAVE $data[$company->id]['total_bl_wop'] = 0; $total_amount_of_benefits = DB::table('employees as e') ->join('employee_benefits as eb', 'e.employee_id', '=', 'eb.employee_id') ->selectRaw(' SUM(CASE WHEN benefits_type_id = 3 THEN eb.amount ELSE 0 END) as meal_total, SUM(CASE WHEN benefits_type_id = 2 THEN eb.amount ELSE 0 END) as transpo_total, SUM(CASE WHEN benefits_type_id = 5 THEN eb.amount ELSE 0 END) as transpo_subsidy_total, SUM(CASE WHEN benefits_type_id = 17 THEN eb.amount ELSE 0 END) as transpo_allowance_total, SUM(CASE WHEN benefits_type_id = 18 THEN eb.amount ELSE 0 END) as company_car_total, SUM(CASE WHEN benefits_type_id = 19 THEN eb.amount ELSE 0 END) as gas_allowance_total, SUM(CASE WHEN benefits_type_id = 20 THEN eb.amount ELSE 0 END) as parking_total, SUM(CASE WHEN benefits_type_id = 21 THEN eb.amount ELSE 0 END) as company_uniform_total, SUM(CASE WHEN benefits_type_id = 22 THEN eb.amount ELSE 0 END) as sm_uniform_total, SUM(CASE WHEN benefits_type_id = 23 THEN eb.amount ELSE 0 END) as hmo_total, SUM(CASE WHEN benefits_type_id = 24 THEN eb.amount ELSE 0 END) as first_aid_total, SUM(CASE WHEN benefits_type_id = 25 THEN eb.amount ELSE 0 END) as ligpai_total, SUM(CASE WHEN benefits_type_id = 26 THEN eb.amount ELSE 0 END) as calamity_assistance_total, SUM(CASE WHEN benefits_type_id = 27 THEN eb.amount ELSE 0 END) as bereavement_assistance_total, SUM(CASE WHEN benefits_type_id = 28 THEN eb.amount ELSE 0 END) as intereset_fee_total, SUM(CASE WHEN benefits_type_id = 29 THEN eb.amount ELSE 0 END) as laptop_prog_total, SUM(CASE WHEN benefits_type_id = 30 THEN eb.amount ELSE 0 END) as company_outing_total, SUM(CASE WHEN benefits_type_id = 31 THEN eb.amount ELSE 0 END) as com_allowance_total, SUM(CASE WHEN benefits_type_id = 32 THEN eb.amount ELSE 0 END) as stay_in_total, SUM(CASE WHEN benefits_type_id = 33 THEN eb.amount ELSE 0 END) as shuttle_service_total ') ->where('e.enabled', 1) ->where('e.company_id', $request->id) ->whereMonth('eb.date_from', $request->month) ->first(); foreach($total_amount_of_benefits as $key => $amount_of_benefits){ $data[$company->id][$key] = $amount_of_benefits; } } return response()->json([ 'data' => array_values($data) ]); } public function generateLeaveDetailed($request) { $data = []; $employeeCountPerCompany = DB::table('employees as e') ->join('companies as c', 'e.company_id', '=', 'c.id') ->select('c.id', 'c.name', 'c.code', DB::raw('COUNT(e.company_id) as employee_count_per_company')) ->where('e.enabled', 1) ->groupBy('c.name', 'e.company_id') ->get(); foreach($employeeCountPerCompany as $company){ $data[$company->id]['company_id'] = $request->id; $data[$company->id]['company_name'] = $company->name; $data[$company->id]['company_code'] = $company->code; $data[$company->id]['employee_count'] = $company->employee_count_per_company; $employeeQuery = Employee::select('employee_id')->where('company_id', $request->id); $employee_ids = $employeeQuery->pluck('employee_id'); $vacation_counts = EmployeeLeave::where('leave_id', 2) ->whereIn('employee_id', $employee_ids) ->selectRaw(' SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_vl_wp, SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_vl_wop ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR VACATION LEAVE $data[$company->id]['total_vl_wp'] = $vacation_counts->total_vl_wp; $data[$company->id]['total_vl_wop'] = $vacation_counts->total_vl_wop; $sick_counts = EmployeeLeave::where('leave_id', 3) ->whereIn('employee_id', $employee_ids) ->selectRaw(' SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_sl_wp, SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_sl_wop ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR SICK LEAVE $data[$company->id]['total_sl_wp'] = $sick_counts->total_sl_wp; $data[$company->id]['total_sl_wop'] = $sick_counts->total_sl_wop; // TOTAL BEREAVEMENT LEAVE $data[$company->id]['total_bl_wop'] = 0; $vacation_reason_counts = EmployeeLeave::whereIn('employee_id', $employee_ids) ->selectRaw(' COUNT(CASE WHEN leave_reason_id = 1 THEN 1 ELSE NULL END) as total_personal_vl, COUNT(CASE WHEN leave_reason_id = 2 THEN 1 ELSE NULL END) as total_fr_vl, COUNT(CASE WHEN leave_reason_id = 3 THEN 1 ELSE NULL END) as total_rl_vl, COUNT(CASE WHEN leave_reason_id = 4 THEN 1 ELSE NULL END) as total_emergency_vl, COUNT(CASE WHEN leave_reason_id = 5 THEN 1 ELSE NULL END) as total_others_vl, COUNT(CASE WHEN leave_reason_id = 6 THEN 1 ELSE NULL END) as total_cd_sl, COUNT(CASE WHEN leave_reason_id = 7 THEN 1 ELSE NULL END) as total_msp_sl, COUNT(CASE WHEN leave_reason_id = 8 THEN 1 ELSE NULL END) as total_rd_sl, COUNT(CASE WHEN leave_reason_id = 9 THEN 1 ELSE NULL END) as total_eent_sl, COUNT(CASE WHEN leave_reason_id = 10 THEN 1 ELSE NULL END) as total_idi_sl, COUNT(CASE WHEN leave_reason_id = 11 THEN 1 ELSE NULL END) as total_hf_sl, COUNT(CASE WHEN leave_reason_id = 12 THEN 1 ELSE NULL END) as total_or_sl, COUNT(CASE WHEN leave_reason_id = 13 THEN 1 ELSE NULL END) as total_dr_sl, COUNT(CASE WHEN leave_reason_id = 14 THEN 1 ELSE NULL END) as total_others_sl ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR VACATION LEAVE REASON $data[$company->id]['total_personal_vl'] = $vacation_reason_counts->total_personal_vl; $data[$company->id]['total_fr_vl'] = $vacation_reason_counts->total_fr_vl; $data[$company->id]['total_rl_vl'] = $vacation_reason_counts->total_rl_vl; $data[$company->id]['total_emergency_vl'] = $vacation_reason_counts->total_emergency_vl; $data[$company->id]['total_others_vl'] = $vacation_reason_counts->total_others_vl; $data[$company->id]['total_cd_sl'] = $vacation_reason_counts->total_cd_sl; $data[$company->id]['total_msp_sl'] = $vacation_reason_counts->total_msp_sl; $data[$company->id]['total_rd_sl'] = $vacation_reason_counts->total_rd_sl; $data[$company->id]['total_eent_sl'] = $vacation_reason_counts->total_eent_sl; $data[$company->id]['total_idi_sl'] = $vacation_reason_counts->total_idi_sl; $data[$company->id]['total_hf_sl'] = $vacation_reason_counts->total_hf_sl; $data[$company->id]['total_or_sl'] = $vacation_reason_counts->total_or_sl; $data[$company->id]['total_dr_sl'] = $vacation_reason_counts->total_dr_sl; $data[$company->id]['total_others_sl'] = $vacation_reason_counts->total_others_sl; } return response()->json([ 'data' => array_values($data) ]); } public function generateContributionReport($request) { // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'date_from' => 'required\|date', 'date_to' => 'required\|date', ]); // Start the query $query = DB::table('payroll_summaries') ->join('employees', 'payroll_summaries.employee_id', '=', 'employees.employee_id'); if ($request->filled('date_from')) { $fromDate = Carbon::parse($request->date_from)->startOfDay(); $toDate = Carbon::parse($request->date_to)->startOfDay(); $query->where('payroll_summaries.payroll_date', '>=', $fromDate)->where('payroll_summaries.payroll_date', '<=', $toDate); } if ($request->company_id) { $query->where('payroll_summaries.company_id', $request->company_id); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Handle transformed columns dynamically // if ($request->filled('payroll_rate')) { // $query->where('salary_and_wages.payroll_rate', $request->payroll_rate); // } // if ($request->filled('salary_rate')) { // $query->whereBetween('salary_and_wages.salary_rate', [$request->salary_rate['min'], $request->salary_rate['max']]); // } // if ($request->filled('payment_type')) { // $query->where('salary_and_wages.payment_type', $request->payment_type); // } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $salaries = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $salaries ]); } private function generateOvertimeCompanyReport($request){ // dd($request->all()); $departments = DB::table('departments') ->when($request->department_id, fn($query) => $query->where('id', $request->department_id) ) ->get(); $result =[]; foreach($departments as $department){ DB::enableQueryLog(); $data = \App\Models\ProcessedOvertime:: join('payroll_summaries as ps', fn($join) => $join->on( 'ps.employee_id','=','processed_overtimes.employee_id' )) ->join('payrolls','ps.payroll_date','=','payrolls.payroll_date') ->join('employees','processed_overtimes.employee_id','=','employees.employee_id') ->leftJoin('departments','processed_overtimes.department_id','=','departments.id') ->where('processed_overtimes.department_id',$department->id); // whereBetween('date',[$validated['from_date'], $validated['to_date']]) if($request->payroll_date){ $data = $data->whereIn('processed_overtimes.payroll_date',$request->payroll_date); } if($request->selectedCompanyId > 0 ){ $data = $data->where('processed_overtimes.company_id',$request->selectedCompanyId); } if($request->dealership_id > 0){ $data = $data->where('processed_overtimes.dealership_id',$request->dealership_id); } $data = $data->select( DB::raw("CONCAT(employees.firstname,' ',employees.lastname) as name"), // 'processed_overtimes.employee_id', DB::raw('departments.name as departments'), DB::raw('SUM(ps.overtime_total) as total_ot_pay'), DB::raw('SUM(ps.holiday_total) as holiday_total'), DB::raw('SUM(ps.restday_total) as dayOff_pay'), DB::raw('SUM(ps.night_diff_total) as night_diff_pay'), DB::raw('SUM(processed_overtimes.no_of_hours) as total_over_times'), DB::raw('COUNT(DISTINCT processed_overtimes.date) as frequency'), DB::raw('SUM(processed_overtimes.meal_amount) as total_meal'), DB::raw('SUM(processed_overtimes.transportation) as total_transpo'), DB::raw( '( SUM(ps.holiday_total) + SUM(ps.restday_total) + SUM(ps.night_diff_total) + SUM(processed_overtimes.meal_amount) + SUM(processed_overtimes.transportation) ) as row_total' ) ) ->groupBy( // 'employee_id', 'departments.name', DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)") ) ->get(); // dd(DB::getQueryLog()); if($data->isNotEmpty()){ $result[$department->name] = $data; } } return response()->json(['data'=> $result]); } }

| ver. 1.4 | . | PHP 8.1.32 | Generation time: 0.21 | proxy | phpinfo | Settings